Ingenjörsfamiljen (engineering family) i Excel läser man som det enklaste hörnet av funktionsreferensen. DEC2BIN gör om ett tal till en binär sträng. HEX2DEC gör om det tillbaka. 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åkomplementskodning (two's complement) som de flesta utvecklare inte har rört sedan en datorarkitekturkurs, ett komplext talformat som helt och hållet bor inuti strängar, och bitvisa operatorer som tyst spiller över ett 64-bitars heltal om du skiftar innan du kontrollerar. En kalkylblads-motor som efterliknar Excel exakt kan inte runda av något av detta.
Funktionerna är uppdelade i tre grupper, och varje grupp döljer en egen fälla. Bas-konvertering handlar om negativa tal och tröskelvärden per bas. Komplex aritmetik handlar om omvandling och formatering av en sträng. Bitvisa operationer handlar om att hålla sig inom gränserna för Int64. Denna artikel går igenom varje grupp så som HotXLS implementerar den, med de kalkylbladsanrop som du faktiskt skulle skriva.
Bas-konvertering och tio-bitars tvåkomplement
Riktningen framåt är den del alla förväntar sig. DEC2BIN(9) ger "1001", och ett valfritt andra argument vänsterfyller resultatet till en fast bredd. Fällan är negativa indata. Excel skriver inte ut ett minustecken. Det kodar värdet som en tio-siffrig tvåkomplementssträng i målbasen, vilket är anledningen till att DEC2BIN(-5,10) returnerar "1111111011" snarare än något med ett tecken. Platser-argumentet (places) ignoreras så fort värdet är negativt, eftersom kodningen redan är låst vid tio siffror.
Tio siffror är en fast budget, och den budgeten sätter det representerbara intervallet per bas. I binär form är storleken som flippar över i den negativa halvan 512, och omslagsmodulen (wrap modulus) är 1024, så en binär sträng är tecknad (signed) endast när den är exakt tio tecken lång och dess värde är minst 512. Samma idé skalas med basen. Oktala tal använder en halvtröskel på 2^29 och en full modul på 2^30. Hexadecimala tal 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 halvtröskeln subtraherar den den fulla modulen för att återställa det tecknade värdet. En nio tecken lång sträng är alltid icke-negativ, oavsett hur stor den är.
Kodaren (encoder) ä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 spiller över basens positiva tak eller om den begärda bredden är för smal för att rymma det. Ett negativt värde bringas först inom intervallet genom att addera den fulla modulen, vilket förvandlar det till ett värde vars basrepresentation alltid är tio siffror, och sedan skickas siffrorna ut med ledande nollor för att fylla ut bredden. Den gemensamma intervallkontrollen, de symmetriska nedre och övre gränserna per bas, är det som håller DEC2BIN, DEC2OCT och DEC2HEX konsekventa med varandra vid sina gränser.
Det lämnar konverteringar mellan olika baser, såsom HEX2BIN och OCT2HEX, som byter bas utan att passera decimala tal i funktionsnamnet. Implementeringen innehåller inte en separat rutin för varje ordnat par. Den tolkar indatasträngen till ett tecknat decimalt värde med hjälp av källbasen, och formaterar sedan det decimala värdet till målbasen. Decimaltalen är pivoten. En tolkningsrutin (parse routine) och en formateringsrutin, sammansatta, täcker varje kombination, och eftersom båda halvorna delar samma tio-siffriga tecknade konvention överlever ett negativt värde resan med sitt tecken intakt.
Komplexa tal är strängar, så arbetet är tolkning
Excel has no complex data type. A complex value is the string "a+bi", and every function in the IM family takes those strings in and hands one back. 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 omvandla texten till två flyttal på ett tillförlitligt sätt, och det är där den interna parsern gör sin nytta.
Två detaljer i den parsern är lätta att göra fel på. Det första är den rena 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 parsern 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 att vetenskaplig notation (scientific notation) kolliderar med det tecken som separerar den reella och imaginära delen. Parsern hittar den separatorn genom att söka efter ett plus eller minus, men ett tal skrivet som "1.5E-3" innehåller ett minus som tillhör exponenten. Sökningen vägrar därför att behandla ett plus eller minus som separator när tecknet omedelbart före det är e eller E. Utan det skyddet skulle den reella delen slitas mitt itu vid exponenttecknet och tolkningen misslyckas på helt giltiga indata.
Suffixet självt bevaras snarare än normaliseras. Excel accepterar både i och j, och HotXLS kommer ihåg vilket av dem som indatan använde så att det formaterade resultatet bär 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 imaginär del på noll kollapsar till ett rent reellt tal, och en reell del på noll tar bort det ledande 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, däribland 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 (modulus) och argumentet, och konverterar tillbaka. En kvadratrot halverar argumentet och tar roten ur absolutbeloppet. En potens multiplicerar argumentet och höjer absolutbeloppet. Att göra det på något annat sätt skulle innebära att man måste härleda varje identitet i rektangulär form på nytt, vilket innebär både mer kod och sämre numerisk stabilitet nära grensnitten (branch cuts).
Bitvisa operatorer och spilloverskottet du måste kontrollera först
Excel 2013 lade till BITAND, BITOR, BITXOR, BITLSHIFT och BITRSHIFT. Operanderna är begränsade: varje måste vara ett icke-negativt heltal som inte är större än 2^48 minus 1, och alla bråkdelar eller negativa argument utgör ett numeriskt fel. Det taket är tillräckligt generöst för att täcka varje realistisk flagguppsättning samtidigt som det håller sig väl inom det exakt representerbara intervallet för en double, vilket är viktigt eftersom Excel skickar över varje numeriskt argument som ett flyttalsvärde.
Skiftfunktionerna (shift functions) bär på den där ordningsregeln som verkligen kan ställa till det. Ett vänsterskift kan producera ett värde som är mycket större än dess indata, och om du utför shl först och inspekterar resultatet efteråt har du redan spillt över Int64 och testet är meningslöst. Kontrollen måste komma före skiftet. HotXLS jämför operanden med taket skiftat till höger med skiftmängden, och endast om operanden får plats utför den det faktiska vänsterskiftet. En skiftstorlek över 53 bitar avvisas direkt, och ett negativt skift ändrar helt enkelt riktning, så att BITLSHIFT med ett negativt antal beter sig som ett högerskift. Principen kan generaliseras långt bortom denna enda funktion: när ett skydd finns för att förhindra spill (overflow), måste det köras på indatan, 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 _xlfn-namnsprefixet
De bitvisa operatorerna och en lång lista av andra tillägg efter 2007 samverkar med ett namngivningsschema 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 uppfunnits efter att den tabellen frystes har ingen plats. För att spara en sådan funktion i en fil och få ett modernt Excel att känna igen den, skrivs namnet med prefixet _xlfn., så att BITAND lagras som _xlfn.BITAND på disken även om användaren bara skriver BITAND.
Haken är att regeln inte är enhetlig. Vissa nyare funktioner tilldelades tabellplatser och skrivs utan prefix, medan några äldre 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å att formeltexten du ställer in och läser tillbaka alltid är det rena namnet som visas i Excel. Du ställer in =BITLSHIFT(5,2), filen innehåller _xlfn.BITLSHIFT, och värdet kommer tillbaka som 20 oavsett. Prefixet är a lagringsdetalj som aldrig ska läcka in i formlerna du arbetar med i koden.
Att lägga samman det i ett kalkylblad
Den publika ytan för allt detta är liten. Skapa en TXLSXWorkbook, lägg till ett kalkylblad och antingen skriv en formel 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 det visar resultatet av ett enskilt ingenjörsanrop utan det omgivande bladets tillstånd, men samma funktioner utvärderas identiskt inuti riktiga cellformler när arbetsboken beräknas om.
Kodningarna är den del man ska ha i åtanke, inte anropsplatserna. En binär sträng är tecknad (signed) endast vid tio siffror och endast förbi halvtröskeln för sin bas. Ett komplext tal är text, en tom imaginär koefficient är ett, och parsern hoppar över exponentens e. Ett vänsterskift kontrolleras innan det skiftar. Få dessa fyra fakta rätt så slutar ingenjörsfamiljen att vara en källa till teckenrelaterade överraskningar.
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.