Den tekniske funksjonsfamilien i Excel virker som det enkleste hjørnet av funksjonsreferansen. DEC2BIN gjør et tall om til en binærstreng. HEX2DEC gjør det tilbake. IMSUM adderer to komplekse tall. Hver av dem ser ut som en enkel formateringsøvelse. Det er de ikke. Bak disse navnene ligger en ti-biters toerkonplement-koding (two's complement) de fleste utviklere ikke har rørt siden et kurs i datamaskinarkitektur, et kompleks-tall-format som lever utelukkende inni strenger, og bitvise operatorer som lydløst vil overløpe et 64-bits heltall hvis du skifter før du sjekker. En regnearkmotor som reproduserer Excel nøyaktig, kan ikke runde av noe av dette.
Funksjonene deler seg inn i tre grupper, og hver gruppe skjuler en annen felle. Basekonvertering handler om negative tall og terskler per base. Kompleks aritmetikk handler om å tolke og formatere en streng. Bitvise operasjoner handler om å holde seg innenfor grensene til Int64. Denne artikkelen går gjennom hver gruppe slik HotXLS implementerer det, med regnearkkallene du faktisk ville skrive.
Basekonvertering og ti-biters toerkonplement
Den fremadgående retningen er delen alle forventer. DEC2BIN(9) gir "1001", og et valgfritt andre argument venstrepolstrer resultatet til en fast bredde. Fellen er negativ inndata. Excel skriver ikke et minustegn. Den koder verdien som en tisifret toerkonplement-streng i målbasen, som er grunnen til at DEC2BIN(-5,10) returnerer "1111111011" i stedet for noe med et tegn. Plassargumentet (places) ignoreres når verdien er negativ, fordi kodingen allerede er låst til ti sifre.
Ti sifre er et fast budsjett, og det budsjettet setter det representerbare området per base. I binær er størrelsen som vippes over i den negative halvdelen 512, og innpakningsmodulusen er 1024, så en binærstreng er fortegnet bare når den er nøyaktig ti tegn lang og verdien er minst 512. Den samme ideen skaleres med basen. Oktal bruker en halvterskel på 2^29 og en full modulus på 2^30. Heksadesimal bruker 2^39 og 2^40. HotXLS-leseren bruker akkurat denne regelen: den akkumulerer sifrene, og bare når strengen er ti tegn bred og den akkumulerte verdien ligger på eller over halvterskelen, trekker den fra den fulle modulusen for å gjenopprette fortegnsverdien. En streng på ni tegn er alltid ikke-negativ, uansett hvor stor den er.
Koderen er speilbildet. En ikke-negativ verdi konverteres siffer for siffer og blir eventuelt nullpolstret til den forespurte bredden, og den avvises hvis den overløper basens positive tak eller hvis den forespurte bredden er for smal til å inneholde den. En negativ verdi blir først brakt inn i området ved å legge til den fulle modulusen, noe som gjør den til en verdi der baserepresentasjonen alltid er ti sifre, og deretter sendes sifrene ut med ledende nuller for å fylle bredden. Den enkle delte områdesjekken, de symmetriske nedre og øvre grensene per base, er det som holder DEC2BIN, DEC2OCT og DEC2HEX konsistente med hverandre ved sine grenser.
Det etterlater konverteringer på tvers av baser, slik som HEX2BIN og OCT2HEX som endrer base uten å gå veien om desimal i funksjonsnavnet. Implementeringen har ikke en separat rutine for hvert ordnede par. Den tolker inngangsstrengen til en signert desimalverdi ved hjelp av kildebasen, og formaterer deretter denne desimalverdien til destinasjonsbasen. Desimal er omdreiningspunktet. Én tolkerutine og én formateringsrutine sammensatt dekker enhver kombinasjon, og fordi begge halvdelene deler den samme tisifrede signerte konvensjonen, overlever en negativ verdi reisen med fortegnet intakt.
Komplekse tall er strenger, så arbeidet er tolking
Excel har ingen kompleks datatype. En kompleks verdi er strengen "a+bi", og hver funksjon i IM-familien tar disse strengene inn og leverer en tilbake. COMPLEX bygger strengen fra en reell og en imaginær del. IMSUM, IMSUB, IMPRODUCT og IMDIV tolker argumentene sine, utfører aritmetikken på de numeriske delene og formaterer resultatet tilbake til en streng. Det numeriske arbeidet er grunnleggende algebra. Vanskeligheten ligger utelukkende i å gjøre teksten om til to flyttall på en pålitelig måte, og det er her den interne parseren gjør nytte for seg.
To detaljer i den parseren er enkle å ta feil av. Den første is den rene imaginære enheten. Strengen "i" betyr én gang i, ikke null og ikke en feil, så når koeffisienten foran suffikset er tom eller er et enslig plusstegn, må parseren lese den som verdien 1, og en enslig minus som -1. Hopper du over det, slutter IMSUM("i","i") å være 2i. Det andre er vitenskapelig notasjon som kolliderer med tegnet som skiller den reelle og imaginære delen. Parseren finner den separatoren bygger på å skanne etter pluss eller minus, men et tall skrevet som "1.5E-3" inneholder en minus som tilhører eksponenten. Skanningen nekter derfor å behandle en pluss eller minus som separator når tegnet rett foran er e eller E. Uten den beskyttelsen ville den reelle delen bli revet i to ved eksponenttegnet og tolkingen ville mislykkes på helt gyldig inndata.
Selve suffikset bevares i stedet for å normaliseres. Excel godtar både i og j, og HotXLS husker hvilken inndataen brukte, slik at det formaterte resultatet bærer samme bokstav. Formateringen bruker deretter de vanlige forkortelsene: en imaginær del på én skrives ut som bare suffikset, minus én som -i, en null imaginær del faller sammen til en ren reell del, og en null reell del dropper den ledende 0+.
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Engineering');
// Negative input: a ten-bit two's complement, places argument ignored.
Sheet.Cells[1, 1].Value := Sheet.Calculate('=DEC2BIN(-5,10)'); // 1111111011
// Complex multiply on two "a+bi" strings.
Sheet.Cells[2, 1].Value := Sheet.Calculate('=IMPRODUCT("3+4i","1+2i")'); // -5+10i
finally
Book.Free;
end;
end;De transcendente komplekse funksjonene, blant dem IMSQRT, IMEXP, IMLN og IMPOWER, fungerer ikke i rektangulære koordinater. De konverterer den tolkede verdien til polar form, utfører operasjonen på modulusen og argumentet, og konverterer tilbake. En kvadratrot halverer argumentet og tar roten av modulusen. En potens multipliserer argumentet og opphøyer modulusen. Å gjøre det på noen annen måte ville bety å utlede hver identitet på nytt i rektangulær form, noe som er både mer kode og mindre numerisk stabilt nær grensesnittene.
Bitvise operatorer og overløpet du må sjekke først
Excel 2013 la til BITAND, BITOR, BITXOR, BITLSHIFT og BITRSHIFT. Operandene er begrenset: hver må være et ikke-negativt heltall som ikke er større enn 2^48 minus 1, og ethvert brøkdel- eller negativt argument er en numerisk feil. Det taket er generøst nok til å dekke ethvert realistisk flaggsett, mens det forblir godt innenfor det nøyaktig representerbare området til en double, noe som er viktig fordi Excel overleverer ethvert numerisk argument som en flyttallsverdi.
Skiftfunksjonene har den ene rekkefølgeregelen som virkelig svir. Et venstreskift kan produsere en verdi som er langt større enn inndataen, og hvis du utfører shl først og inspiserer resultatet etterpå, har du allerede overløpt Int64 og testen er meningsløs. Sjekken må komme før skiftet. HotXLS sammenligner operanden med taket skiftet til høyre med skiftmengden, og solo hvis operanden passer, utfører den det faktiske venstreskiftet. En skiftstørrelse utover 53 biter avvises kontant, og et negativt skift snur ganske enkelt retningen, så BITLSHIFT med et negativt antall oppfører seg som et høyreskift. Prinsippet generaliseres langt forbi denne ene funksjonen: når det finnes en beskyttelse for å forhindre overløp, må den kjøres på inndataene, aldri på resultatet den var ment å beskytte.
// Bitwise calls evaluate the same way through Calculate.
Sheet.Cells[3, 1].Value := Sheet.Calculate('=BITAND(13,11)'); // 9
Sheet.Cells[4, 1].Value := Sheet.Calculate('=BITLSHIFT(5,2)'); // 20
Sheet.Cells[5, 1].Value := Sheet.Calculate('=BITRSHIFT(40,3)'); // 5Fremtidige funksjoner og navneprefikset _xlfn
De bitvise operatorene og en lang liste med andre tillegg etter 2007 samhandler med et navneskjema som ikke har noe å gjøre med hva de beregner, og alt å gjøre med hvordan Excel lagrer dem. Det opprinnelige binære regnearkformatet tildelte hver innebygde funksjon en numerisk plass i en fast tabell. Funksjoner oppfunnet etter at den tabellen ble frosset, har ingen plass. For å lagre en slik funksjon i en fil og få en moderne Excel til å gjenkjenne den, skrives navnet med et _xlfn.-prefiks, så BITAND lagres som _xlfn.BITAND på disken selv om brukeren bare skriver BITAND.
Haken er at regelen ikke er ensartet. Noen nyere funksjoner fikk tabellplasser og skrives uten prefiks, mens noen få eldre skjulte funksjoner også skrives uten prefiks til tross for alderen. HotXLS har en eksplisitt hviteliste over hvilke navn som trenger prefikset, legger det til ved skriving og fjerner det ved lesing, slik at formelteksten du setter og leser tilbake, alltid er det rene Excel-rettede navnet. Du setter =BITLSHIFT(5,2), filen inneholder _xlfn.BITLSHIFT, og verdien kommer tilbake som 20 uansett. Prefikset er en lagringsdetalj som aldri bør lekke inn i formlene du jobber med i koden.
Sette det sammen i et regneark
Den offentlige overflaten for alt dette er liten. Opprett en TXLSXWorkbook, legg til et regneark, og skriv enten en formel inn i en celle via Cells[Row, Col].Formula og beregn på nytt, eller evaluer et uttrykk direkte med regnearkets Calculate-metode, som kompilerer formelen mot det arket og returnerer en Variant. Eksemplene ovenfor bruker Calculate fordi det viser resultatet av et enkelt teknisk kall uten den omgivende arktilstanden, men de samme funksjonene evalueres identisk inne i reelle celleformler når arbeidsboken beregnes på nytt.
Kodingene er delen du må huske på, ikke kallstedene. En binærstreng er signert bare ved ti sifre og bare forbi halvterskelen for sin base. Et komplekst tall er tekst, en tom imaginær koeffisient er én, og parseren går over e-en til en eksponent. Et venstreskift sjekkes før det skifter. Gjør disse fire faktaene riktig, og den tekniske familien slutter å være en kilde til fortegnsrelaterte overraskelser.
If you are wiring your own domain math into the same engine, the mechanics of registering a handler and returning values are covered in our article on extending the formula engine with custom functions, and when those formulas have to reach across sheets by name rather than by cell address, the walkthrough on defined names and cross-sheet formulas shows how the references resolve. The engineering functions described here ship as part of the HotXLS spreadsheet component for Delphi and C++Builder, alongside the reading, writing, and calculation APIs covered elsewhere on this blog.