Ingenjörsfamiljen i Excel läses som det enklaste hörnet av funktionsreferensen. DEC2BIN förvandlar ett tal till en binär sträng. HEX2DEC vänder tillbaka det. IMSUM adderar två komplexa tal. Var och en ser ut som en formateringsövning. Det är de inte. Bakom dessa namn sitter en tio-bitars tvåkomplement-kodning som de flesta utvecklare inte har rört sedan en datorarkitektur-kurs, ett format för komplexa tal som lever helt inuti strängar, och bitvisa operatorer som tyst spiller över ett 64-bitars heltal om du skiftar innan du kollar. En kalkylbladsmotor som reproducerar Excel exakt kan inte avrunda något av detta
Funktionerna delas in i tre grupper, och varje grupp döljer en annan fälla. Baskonvertering handlar om negativa tal och tröskelvärden per bas. Komplex aritmetik handlar om att tolka och formatera en sträng. Bitvisa operationer handlar om att stanna inom gränserna för Int64. Den här artikeln går igenom varje grupp så som HotXLS implementerar den, med de kalkylbladsanrop du faktiskt skulle skriva
Baskonvertering och tio-bitars tvåkomplement
Framåtriktningen är den del alla förväntar sig. DEC2BIN(9) ger "1001", och ett valfritt andra argument vänster-utfyller resultatet till en fast bredd. Fällan är negativ inmatning. Excel skriver inte ett minustecken. Den kodar värdet som en tiosiffrig tvåkomplementsträng i målbasen, vilket är anledningen till att DEC2BIN(-5,10) returnerar "1111111011" istället för något med ett tecken. Antalet positioner-argumentet ignoreras när värdet väl är negativt, eftersom kodningen redan är låst till tio siffror
Tio siffror är en fast budget, och den budgeten sätter det representerbara intervallet per bas. Binärt är magnituden som slår över till den negativa hälften 512, och omslagningsmodulen är 1024, så en binär sträng är signerad endast när den är exakt tio tecken lång och dess värde är minst 512. Samma idé skalas med basen. Oktalt använder en halv-tröskel på 2^29 och en full modul på 2^30. Hexadecimalt använder 2^39 och 2^40. HotXLS-läsaren tillämpar exakt denna regel: den ackumulerar siffrorna, och endast när strängen är tio tecken bred och det ackumulerade värdet ligger på eller över den halva tröskeln subtraherar den den fulla modulen för att återskapa det signerade värdet. En sträng på nio tecken är alltid icke-negativ, oavsett hur stor den är
Kodaren är spegelbilden. Ett icke-negativt värde konverteras siffra för siffra och fylls valfritt med nollor till den begärda bredden, och det avvisas om det rinner över basens positiva tak eller om den begärda bredden är för smal för att rymma den. Ett negativt värde förs först in i intervallet genom att lägga till den fulla modulen, vilket gör det till ett värde vars basrepresentation alltid är tio siffror, och sedan emitteras siffrorna med inledande nollor för att fylla ut bredden. Den enda delade intervallkontrollen, de symmetriska nedre och övre gränserna per bas, är det som håller DEC2BIN, DEC2OCT och DEC2HEX konsekventa med varandra vid deras ytterligheter
Det lämnar konverteringarna över basgränser, sådana som HEX2BIN och OCT2HEX som byter bas utan att passera genom decimalt i funktionsnamnet. Implementeringen bär inte på en separat rutin för varje ordnat par. Den tolkar inmatningssträngen till ett signerat decimalt värde med hjälp av källbasen, och formaterar sedan det decimala värdet till målbasen. Decimalt är navet. En tolk-rutin och en formaterings-rutin, komponerade, täcker varje kombination, och eftersom båda halvorna delar samma tiosiffriga signerade konvention, överlever ett negativt värde resan med sitt tecken intakt
Komplexa tal är strängar, så arbetet är tolkning
Excel har ingen datatyp för komplexa tal. Ett komplext värde är strängen "a+bi", och varje funktion i IM-familjen tar emot dessa strängar och ger en tillbaka. COMPLEX bygger strängen från en reell och en imaginär del. IMSUM, IMSUB, IMPRODUCT och IMDIV tolkar sina argument, utför aritmetiken på de numeriska delarna och formaterar resultatet tillbaka till en sträng. Det numeriska arbetet är grundläggande algebra. Svårigheten ligger helt i att förvandla texten till två flyttal på ett pålitligt sätt, och det är där den interna tolkaren gör skäl för sig
Två detaljer i den tolkaren är lätta att få fel. Det första är den nakna imaginära enheten. Strängen "i" betyder ett gånger i, inte noll och inte ett fel, så när koefficienten framför suffixet är tom eller är ett ensamt plustecken måste tolkaren läsa den som värdet 1, och ett ensamt minus som -1. Hoppa över det och IMSUM("i","i") slutar vara 2i. Det andra är vetenskaplig notation som kolliderar med det tecken som skiljer de reella och imaginära delarna. Tolkaren hittar den separatorn genom att skanna efter ett plus eller minus, men ett tal skrivet som "1.5E-3" innehåller ett minus som tillhör exponenten. Skanningen vägrar därför att behandla ett plus eller minus som separatorn när tecknet omedelbart före det är e eller E. Utan det skyddet skulle realdelen slitas isär vid exponenttecknet och tolkningen skulle misslyckas på fullt giltig inmatning
Själva suffixet bevaras snarare än normaliseras. Excel accepterar både i och j, och HotXLS kommer ihåg vilken som inmatningen använde så att det formaterade resultatet bär på samma bokstav. Formateringen tillämpar sedan de konventionella förkortningarna: en imaginär del på ett skrivs ut som bara suffixet, minus ett som -i, en noll-imaginärdel kollapsar till ett vanligt reellt tal, och en noll-realdel släpper det inledande 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 transcendenta komplexa funktionerna, bland annat IMSQRT, IMEXP, IMLN och IMPOWER, fungerar inte i rektangulära koordinater. De konverterar det tolkade värdet till polär form, tillämpar operationen på absolutbeloppet (modul) och argumentet, och konverterar tillbaka. En kvadratrot halverar argumentet och tar roten ur absolutbeloppet. En potens multiplicerar argumentet och upphöjer absolutbeloppet. Att göra det på något annat sätt skulle innebära att man härleder varje identitet i rektangulär form på nytt, vilket är både mer kod och mindre numeriskt stabilt nära grensnitten
Bitvisa operatorer och överflödet (overflow) du måste kolla först
Excel 2013 lade till BITAND, BITOR, BITXOR, BITLSHIFT och BITRSHIFT. Operanderna är begränsade: var och en måste vara ett icke-negativt heltal som inte är större än 2^48 minus 1, och alla bråk- eller negativa argument är ett numeriskt fel. Det taket är generöst nog för att täcka alla realistiska flagguppsättningar samtidigt som det stannar väl innanför det exakt representerbara intervallet för en double, vilket spelar roll eftersom Excel överlämnar varje numeriskt argument som ett flyttalsvärde
Skiftfunktionerna bär på den enda ordningsregeln som genuint biter. Ett vänsterskifte kan producera ett värde mycket större än sin inmatning, och om du utför shl först och inspekterar resultatet efteråt har du redan spillt över (overflowed) Int64 och testet är meningslöst. Kontrollen måste komma före skiftningen. HotXLS jämför operanden mot taket högerskiftat med skiftmängden, och endast om operanden får plats utför den det faktiska vänsterskiftet. En skiftmagnitud bortom 53 bitar avvisas helt och hållet, och ett negativt skifte byter helt enkelt riktning, så BITLSHIFT med ett negativt antal beter sig som ett högerskifte. Principen generaliseras långt förbi denna enda funktion: när ett skydd existerar för att förhindra spill (overflow), måste det köras på inmatningarna, aldrig på resultatet det var tänkt att skydda
// 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)'); // 5Framtida funktioner och namnprefixet _xlfn
De bitvisa operatorerna och en lång lista med andra tillägg efter 2007 interagerar med ett namnschema som inte har något att göra med vad de beräknar och allt att göra med hur Excel lagrar dem. Det ursprungliga binära kalkylbladsformatet tilldelade varje inbyggd funktion en numerisk plats i en fast tabell. Funktioner som uppfanns efter att den tabellen frusits har ingen plats. För att spara en sådan funktion till en fil och få en modern Excel att känna igen den, skrivs namnet med ett _xlfn.-prefix, så BITAND lagras som _xlfn.BITAND på disk även om användaren bara någonsin skriver BITAND
Haken är att regeln inte är enhetlig. Vissa nyare funktioner gavs tabellplatser och skrivs nakna, medan några gamla dolda funktioner också skrivs utan prefix trots sin ålder. HotXLS har en explicit vitlista över vilka namn som behöver prefixet, lägger till det vid skrivning och tar bort det vid läsning, så formeltexten du ställer in och läser tillbaka är alltid det rena Excel-vända namnet. Du ställer in =BITLSHIFT(5,2), filen innehåller _xlfn.BITLSHIFT, och värdet kommer tillbaka som 20 oavsett. Prefixet är en lagringsdetalj som aldrig bör läcka in i de formler du arbetar med i kod
Att sätta ihop det i ett kalkylblad
Den offentliga ytan för allt detta är liten. Skapa en TXLSXWorkbook, lägg till ett kalkylblad, och antingen skriv en formel in i en cell via Cells[Row, Col].Formula och beräkna om, eller utvärdera ett uttryck direkt med kalkylbladets Calculate-metod, som kompilerar formeln mot det bladet och returnerar en Variant. Exemplen ovan använder Calculate eftersom den visar resultatet av ett enstaka ingenjörsanrop utan det omgivande bladtillståndet, men samma funktioner utvärderas identiskt inuti riktiga cellformler när arbetsboken räknar om
Kodningarna är den del att hålla i minnet, inte anropsplatserna. En binär sträng är signerad endast vid tio siffror och endast förbi den halva tröskeln för sin bas. Ett komplext tal är text, en tom imaginär koefficient är ett, och tolkaren kliver över e:et för en exponent. Ett vänsterskifte kontrolleras innan det skiftar. Få de fyra fakta rätt så slutar ingenjörsfamiljen att vara en källa till tecken-fel-överraskningar (off-by-a-sign)
Om du kopplar in din egen domänmatematik i samma motor täcks mekaniken för att registrera en hanterare och returnera värden i vår artikel om att utöka formelmotorn med anpassade funktioner, och när de formlerna måste sträcka sig över blad med namn snarare än med celladress visar genomgången om definierade namn och tvärbladformler hur referenserna löser sig. Ingenjörsfunktionerna som beskrivs här levereras som en del av HotXLS-kalkylbladskomponenten för Delphi och C++Builder, tillsammans med de läs-, skriv- och beräknings-API:er som täcks på andra ställen på denna blogg