De tekniske funktioner (engineering family) i Excel ligner det nemmeste hjørne af funktionsreferencen. DEC2BIN forvandler et tal til en binær streng. HEX2DEC forvandler det tilbage. IMSUM lægger to komplekse tal sammen. Hver enkelt ligner en formateringsøvelse. Det er de ikke. Bag disse navne gemmer sig en ti-bit toerkomplement-kodning, som de fleste udviklere ikke har rørt siden et kursus i computerarkitektur, et komplekst talformat, der lever helt inde i strenge, og bitvise operatorer, der lydløst vil overløbe et 64-bit heltal, hvis du skifter (shift) før du tjekker. En regnearksmotor, der genskaber Excel præcist, kan ikke runde noget af det af.
Funktionerne deler sig i tre grupper, og hver gruppe gemmer på en forskellig fælde. Basiskonvertering handler om negative tal og tærskler pr. talbase. Kompleks aritmetik handler om parsing og formatering af en streng. Bitvise operationer handler om at holde sig inden for grænserne af Int64. Denne artikel gennemgår hver gruppe, som HotXLS implementerer det, med de regnearkskald, du faktisk ville skrive.
Basiskonvertering og ti-bit toerkomplement
Den fremadrettede retning er den del, alle forventer. DEC2BIN(9) giver "1001", og et valgfrit andet argument venstre-polstrer (left-pad) resultatet til en fast bredde. Fælden er negative input. Excel skriver ikke et minustegn. Den koder værdien som en ti-cifret toerkomplement-streng i måltalsystemet, hvilket er grunden til, at DEC2BIN(-5,10) returnerer "1111111011" i stedet for noget med et fortegn. Plads-argumentet (places) ignoreres, når først værdien er negativ, fordi kodningen allerede er fastlåst på ti cifre.
Ti cifre er et fast budget, og det budget fastsætter det repræsentative interval pr. talbase. I binær er den værdi, der vipper over i den negative halvdel, 512, og wrap-modulusen er 1024, så en binær streng har kun et fortegn, når den er nøjagtig ti tegn lang, og dens værdi er mindst 512. Den samme idé skalerer med basen. Oktal bruger en halvtærskel på 2^29 og en fuld modulus på 2^30. Heksadecimal bruger 2^39 og 2^40. HotXLS-læseren anvender netop denne regel: Den akkumulerer cifrene, og først når strengen er ti tegn bred, og den akkumulerede værdi ligger på eller over halvtærsklen, trækker den den fulde modulus fra for at genvinde værdien med fortegn. En streng på ni tegn er altid ikke-negativ, uanset hvor stor den er.
Koderen er spejlbilledet. En ikke-negativ værdi konverteres ciffer for ciffer og polstres eventuelt med foranstillede nuller til den ønskede bredde, og den afvises, hvis den overløber talbasens 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 tilføje den fulde modulus, hvilket gør den til en værdi, hvis repræsentation i talbasen altid er ti cifre, og derefter udsendes cifrene med foranstillede nuller for at fylde bredden. Den enkelte fælles intervalkontrol, de symmetriske nedre og øvre grænser pr. talbase, er det, der holder DEC2BIN, DEC2OCT og DEC2HEX konsistente med hinanden ved deres ydergrænser.
Det efterlader konverteringerne på tværs af baser, såsom HEX2BIN og OCT2HEX, der ændrer talbase uden at passere gennem decimaltal i funktionsnavnet. Implementeringen indeholder ikke en separat rutine for hvert ordnet par. Den parser inputstrengen til en decimalværdi med fortegn ved hjælp af kildebasen og formaterer derefter denne decimalværdi til målbasen. Decimaltallet er omdrejningspunktet. Én parserutine og én formateringsrutine sammensat dækker enhver kombination, og fordi begge halvdele deler samme ti-cifrede konvention for negative tal, 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 en 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 een streng. Det numeriske arbejde er elementær algebra. Vanskeligheden ligger helt i at omdanne teksten til to flydende kommatal på pålidelig vis, og det er her, den interne parser viser sit værd.
To detaljer i den parser er nemme at tage fejl af. Den første er den rene imaginære enhed. Strengen "i" betyder én gang i, ikke nul og ikke en fejl, så når koefficienten foran suffikset er tom eller er et enkelt plustegn, skal parseren læse det som værdien 1, og et enkelt minus som -1. Hvis du springer det over, ophører IMSUM("i","i") med at være 2i. Den anden detalje er videnskabelig notation, der kolliderer med det tegn, der adskiller den reelle og den imaginære del. Parseren finder denne separator by scanning efter et plus eller et 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 separator, når tegnet umiddelbart før det er e or E. Uden den beskyttelse ville den reelle del blive splittet i to ved eksponenttegnet, og parsingen ville fejle på helt gyldige input.
Selve suffikset bevares frem for at blive normaliseret. Excel accepterer både i og j, og HotXLS husker, hvilken af dem inputtet brugte, så det formaterede resultat bærer samme bogstav. Formateringen anvender derefter de konventionelle forkortelser: En imaginær del på én udskrives som blot suffikset, minus én som -i, en nul imaginær del falder sammen til et rent reelt tal, og en nul reel del udelader det indledende 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 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 og argument og konverterer tilbage. En kvadratrod halverer argumentet og tager roden af modulusen. En potens multiplicerer argumentet og hæver modulusen. At gøre det på nogen anden måde ville kræve, at man udledte hver identitet i rektangulær form, hvilket er både mere kode og mindre numerisk stabilt nær grensnittene (branch cuts).
Bitvise operatorer og det overløb, du skal tjekke først
Excel 2013 tilføjede BITAND, BITOR, BITXOR, BITLSHIFT og BITRSHIFT. Operanderne er begrænsede: Hver skal være et ikke-negativt heltal, der ikke er større end 2^48 minus 1, og ethvert brøk- eller negativt argument er en numerisk fejl. Den grænse er generøs nok til at dække ethvert realistisk flag-sæt, mens den forbliver pænt inden for det nøjagtigt repræsentative interval for en double, hvilket er vigtigt, fordi Excel overfører ethvert numerisk argument som en flydende komma-værdi.
Skiftefunktionerne bærer den ene ordensregel, der for alvor gør ondt. Et venstreskift kan producere en værdi, der er langt større end dens input, og hvis du udfører shl først og inspicerer resultatet bagefter, har du allerede overløbet Int64, og testen er meningsløs. Kontrollen skal komme før skiftet. HotXLS sammenligner operanden med det øvre loft skiftet til højre med skiftemængden, og kun hvis operanden passer, udfører den det faktiske venstreskift. En skiftestørrelse ud over 53 bit afvises med det samme, og et negativt skift vender blot retningen, så BITLSHIFT med et negativt antal fungerer som et højreskift. Princippet generaliserer langt ud over denne ene funktion: Når der findes en beskyttelse mod overløb, skal den køres på inputtene, aldrig på det resultat, den var beregnet 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-navnepræfikset
De bitvise operatorer og en lang liste over andre tilføjelser efter 2007 interagerer med et navngivningsskema, der intet har at gøre med, hvad de beregner, og alt at gøre med, hvordan Excel gemmer dem. Det oprindelige binære regnearksformat tildelte hver indbygget funktion en numerisk plads i en fast tabel. Funktioner opfundet efter, at denne tabel blev fastlåst, har ingen plads. 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.
Haken er, at reglen ikke er ensartet. Nogle nyere funktioner fik tabelpladser og skrives råt, mens et par ældre skabelonfunktioner også skrives uden præfiks trods deres alder. HotXLS opretholder en eksplicit hvidliste 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 navn rettet mod Excel. Du indstiller =BITLSHIFT(5,2), filen indeholder _xlfn.BITLSHIFT, og værdien kommer tilbage som 20 uanset hvad. Præfikset er en lagringsdetalje, der aldrig bør lække ind i de formler, du arbejder med i koden.
Samling af det hele 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 via Cells[Row, Col].Formula og genberegn, eller evaluer et udtryk direkte med regnearkets Calculate-metode, som kompilerer formlen mod det pågældende ark og returnerer en Variant. Eksemplerne ovenfor bruger Calculate, fordi det viser resultatet af et enkelt teknisk kald uden den omgivende arkstatus, men de samme funktioner evalueres på samme måde inde i rigtige celleformler, når projektmappen genberegnes.
Kodningerne er den del, man skal huske, ikke kaldstederne. En binær streng har kun fortegn ved ti cifre og kun forbi halvtærsklen for sin base. Et komplekst tal er tekst, en tom imaginær koefficient er én, og parseren træder over e'et i en eksponent. Et venstreskift kontrolleres, før det skifter. Få disse fire fakta rigtige, og den tekniske funktionsfamilie holder op med at være en kilde til fortegnsfejl-overraskelser.
Hvis du forbinder din egen domæne-matematik til den samme motor, er mekanikken til registrering af en handler og returnering af værdier dækket i our article on extending the formula engine with custom functions, og når disse formler skal række på tværs af ark ved navn frem for cellereference, viser gennemgangen af definerede navne og formler på tværs af ark, hvordan referencerne løses. De tekniske funktioner, der beskrives her, leveres som en del af HotXLS regnearkskomponent til Delphi og C++Builder sammen med API'erne til læsning, skrivning og beregning, der er dækket andre steder på denne blog.