Ingeniørfamilien (engineering family) i Excel læses som det nemmeste hjørne af funktionsreferencen. DEC2BIN omdanner et tal til en binær streng. HEX2DEC omdanner det tilbage. IMSUM lægger to komplekse tal sammen. Hver af dem ligner en formateringsøvelse. Det er de ikke. Bag disse navne sidder en ti-bit toerkonvolementkodning (ten-bit two's complement encoding), som de fleste udviklere ikke har rørt siden et kursus i computerarkitektur, et format til komplekse tal, der udelukkende lever inde i strenge, og bitvise operatorer, der lydløst vil overskride (overflow) et 64-bit heltal, hvis du skifter (shift) før du tjekker. En regnearksmotor, der reproducerer Excel nøjagtigt, kan ikke runde noget af det af
Funktionerne er opdelt i tre grupper, og hver gruppe skjuler en forskellig fælde. Grundtalskonvertering (Base conversion) handler om negative tal og tærskler pr. grundtal (per-base thresholds). Kompleks aritmetik handler om parsing og formatering af en streng. Bitvise operationer handler om at forblive inden for grænserne af Int64. Denne artikel gennemgår hver gruppe, som HotXLS implementerer dem, med de regnearkskald, du rent faktisk ville skrive
Grundtalskonvertering og ti-bit toerkonvolement
Fremadretningen er den del, alle forventer. DEC2BIN(9) giver "1001", og et valgfrit andet argument venstreudfylder (left-pads) resultatet til en fast bredde. Fælden er negativt input. Excel skriver ikke et minustegn. Det koder værdien som en ticifret toerkonvolementstreng i målgrundtallet (target base), hvilket er grunden til, at DEC2BIN(-5,10) returnerer "1111111011" frem for noget med et fortegn. Plads-argumentet ignoreres, når værdien er negativ, fordi kodningen allerede er fastlåst til ti cifre
Ti cifre er et fast budget, og det budget sætter det repræsenterbare interval pr. grundtal. I binær er størrelsen, der vipper over i den negative halvdel, 512, og ombrydningsmodulet (wrap modulus) er 1024, så en binær streng har kun fortegn, når den er præcis ti tegn lang, og dens værdi er mindst 512. Den samme idé skalerer med grundtallet. Oktal bruger en halv tærskel på 2^29 og et fuldt modul på 2^30. Heksadecimal bruger 2^39 og 2^40. HotXLS-læseren anvender nøjagtig denne regel: den akkumulerer cifrene, og kun når strengen er ti tegn bred, og den akkumulerede værdi sidder på eller over den halve tærskel, trækker den det fulde modul fra for at gendanne værdien med fortegn. En streng med ni tegn er altid ikke-negativ, uanset hvor stor den er
Koderen (encoderen) er spejlbilledet. En ikke-negativ værdi konverteres ciffer for ciffer og nul-udfyldes (zero-padded) eventuelt til den ønskede bredde, og den afvises, hvis den overskrider (overflows) grundtallets positive loft, eller hvis den ønskede bredde er for smal til at rumme den. En negativ værdi bringes først inden for intervallet ved at lægge det fulde modul til, hvilket omdanner den til en værdi, hvis grundtalsrepræsentation altid er ti cifre, og derefter udsendes cifrene med foranstillede nuller for at fylde bredden. Den ene delte intervalkontrol (range check), de symmetriske nedre og øvre grænser pr. grundtal, er det, der holder DEC2BIN, DEC2OCT og DEC2HEX konsistente med hinanden ved deres kanter
Det efterlader konverteringerne på tværs af grundtal, dem som HEX2BIN og OCT2HEX, der skifter grundtal uden at passere gennem decimal i funktionsnavnet. Implementeringen har ikke en separat rutine for hvert ordnet par. Den parser inputstrengen til en decimalværdi med fortegn ved hjælp af kildegrundtallet, og formaterer derefter denne decimalværdi til destinationsgrundtallet. Decimal er omdrejningspunktet. Én parse-rutine og én formateringsrutine, sammensat, dækker enhver kombination, og fordi begge halvdele deler den samme ticifrede konvention med fortegn, overlever en negativ værdi rejsen med sit fortegn intakt
Komplekse tal er strenge, så arbejdet er parsing
Excel har ingen kompleks datatype. En kompleks værdi er strengen "a+bi", og enhver funktion i IM-familien tager disse strenge ind og leverer én tilbage. COMPLEX opbygger strengen ud fra en reel og en imaginær del. IMSUM, IMSUB, IMPRODUCT og IMDIV parser deres argumenter, udfører aritmetikken på de numeriske dele og formaterer resultatet tilbage til en streng. Det numeriske arbejde er bachelor-algebra. Vanskeligheden ligger udelukkende i at omdanne teksten til to kommatal på pålidelig vis, og det er her, den interne parser tjener sine penge
To detaljer i den parser er nemme at få forkert. Den første er den blotte imaginære enhed. Strengen "i" betyder en gange i, ikke nul og ikke en fejl, så når koefficienten foran suffikset er tom eller er et enkeltstående plustegn, skal parseren læse det som værdien 1, og et enkeltstående minus som -1. Spring det over, og IMSUM("i","i") ophører med at være 2i. Den anden er videnskabelig notation, der kolliderer med fortegnet, der adskiller de reelle og imaginære dele. Parseren finder den adskiller ved at scanne efter et plus eller minus, men et tal skrevet som "1.5E-3" indeholder et minus, der hører til eksponenten. Scanningen nægter derfor at behandle et plus eller minus som adskilleren, når tegnet umiddelbart før det er e eller E. Uden den vagt ville den reelle del blive revet over i to ved eksponentfortegnet, og parsen ville fejle på fuldstændig gyldigt input
Selve suffikset bevares i stedet for at blive normaliseret. Excel accepterer både i og j, og HotXLS husker, hvilken det inputtede brugte, så det formaterede resultat bærer det samme bogstav. Formatering anvender derefter de konventionelle forkortelser (shorthands): en imaginær del på én udskrives som blot suffikset, minus én som -i, en imaginær del på nul kollapser til en almindelig reel del, og en reel del på nul slipper den foranstillede 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 transcendentale komplekse funktioner, herunder IMSQRT, IMEXP, IMLN og IMPOWER, fungerer ikke i rektangulære koordinater. De konverterer den parsede værdi til polær form, anvender operationen på modulus (modulus) og argument, og konverterer tilbage. En kvadratrod halverer argumentet og tager roden af modulus. En potens multiplicerer argumentet og hæver modulus. At gøre det på nogen anden måde ville betyde at genudlede hver identitet i rektangulær form, hvilket både er mere kode og mindre numerisk stabilt nær grene-skæringerne (branch cuts)
Bitvise operatorer og overskridelsen, du skal tjekke først
Excel 2013 tilføjede BITAND, BITOR, BITXOR, BITLSHIFT og BITRSHIFT. Operanderne (operands) er begrænsede: hver skal være et ikke-negativt heltal, der ikke er større end 2^48 minus 1, og ethvert fraktionelt eller negativt argument er en numerisk fejl. Det loft er generøst nok til at dække ethvert realistisk flag-sæt, mens man forbliver godt inde i det nøjagtigt repræsenterbare interval for en double, hvilket betyder noget, fordi Excel overdrager ethvert numerisk argument som en kommatal-værdi
Shift-funktionerne bærer den ene ordensregel, der for alvor bider. Et venstreskift (left shift) kan producere en værdi, der er langt større end dets input, og hvis du udfører shl først og inspicerer resultatet bagefter, har du allerede overskredet Int64 (overflow), og testen er meningsløs. Tjekket skal komme før skiftet. HotXLS sammenligner operanden mod loftet skiftet til højre med shift-beløbet, og kun hvis operanden passer, udfører den selve venstreskiftet. En skiftestørrelse (shift magnitude) ud over 53 bits afvises blankt, og et negativt skift vender simpelthen retningen, så BITLSHIFT med et negativt antal opfører sig som et højreskift. Princippet generaliserer langt forbi denne ene funktion: når der eksisterer en vagt for at forhindre overflow, skal den køre på inputtene, aldrig på det resultat, den var ment til at 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 funktioner og _xlfn navne-præfikset
De bitvise operatorer og en lang liste over andre post-2007 tilføjelser interagerer med en navngivningsordning, der intet har at gøre med, hvad de beregner, og alt at gøre med, hvordan Excel gemmer dem. Det originale binære regnearksformat tildelte hver indbygget funktion et numerisk slot i en fast tabel. Funktioner, der er opfundet, efter at den tabel blev frosset, har intet slot. For at gemme en sådan funktion i en fil og få en moderne Excel til at genkende den, skrives navnet med et _xlfn.-præfiks, så BITAND gemmes som _xlfn.BITAND på disken, selvom brugeren kun nogensinde skriver BITAND
Hagen (catch) er, at reglen ikke er ensartet. Nogle nyere funktioner fik tabel-slots og skrives bare (bare), mens et par ældre skjulte funktioner (legacy hidden functions) også skrives uden et præfiks på trods af deres alder. HotXLS opretholder en eksplicit hvidliste (whitelist) over, hvilke navne der har brug for præfikset, tilføjer det ved skrivning og fjerner det ved læsning, så den formeltekst, du indstiller og læser tilbage, altid er det rene Excel-vendte navn. Du sætter =BITLSHIFT(5,2), filen indeholder _xlfn.BITLSHIFT, og værdien kommer tilbage som 20 uanset. Præfikset er en lagringsdetalje, der aldrig bør lække ind i de formler, du arbejder med i kode
At sætte det sammen i et regneark
Den offentlige overflade for alt dette er lille. Opret en TXLSXWorkbook, tilføj et regneark, og skriv enten en formel i en celle gennem Cells[Row, Col].Formula og genberegn, eller evaluer et udtryk direkte med regnearkets Calculate-metode, som kompilerer formlen mod det ark og returnerer en Variant. Eksemplerne ovenfor bruger Calculate, fordi det viser resultatet af et enkelt ingeniør-kald uden den omgivende ark-tilstand, men de samme funktioner evaluerer identisk inde i rigtige celleformler, når projektmappen (workbook) genberegner
Kodningerne er den del at have i tankerne, ikke kald-stederne (call sites). En binær streng har kun fortegn ved ti cifre og kun forbi den halve tærskel for dets grundtal. Et komplekst tal er tekst, en tom imaginær koefficient er én, og parseren træder over e'et i en eksponent. Et venstreskift tjekkes, før det skifter. Få disse fire kendsgerninger rigtigt, og ingeniørfamilien stopper med at være en kilde til off-by-a-sign overraskelser
Hvis du forbinder (wiring) din egen domænematematik til den samme motor, er mekanikken i at registrere en handler og returnere værdier dækket i vores artikel om at udvide formelmotoren med tilpassede funktioner, og når de formler skal nå på tværs af ark ved navn i stedet for ved celleadresse, viser gennemgangen af definerede navne og tværgående formler (cross-sheet formulas), hvordan referencerne løses. Ingeniørfunktionerne beskrevet her leveres som en del af HotXLS-regnearkskomponenten til Delphi og C++Builder, sammen med API'erne til læsning, skrivning og beregning, der dækkes andre steder på denne blog