Technische Funktionen in Delphi: Basis-Konvertierung, Komplexe Zahlen

Die Familie der technischen Funktionen in Excel liest sich wie die einfachste Ecke der Funktionsreferenz. DEC2BIN verwandelt eine Zahl in eine Binärzeichenfolge. HEX2DEC macht das Gegenteil. IMSUM addiert zwei komplexe Zahlen. Jede einzelne sieht aus wie eine Formatierungsübung. Das sind sie aber nicht. Hinter diesen Namen verbirgt sich eine 10-Bit-Zweierkomplement-Kodierung, mit der die meisten Entwickler seit den Vorlesungen zur Rechnerarchitektur nicht mehr in Berührung gekommen sind, ein komplexes Zahlenformat, das vollständig innerhalb von Zeichenfolgen existiert, und bitweise Operatoren, die unbemerkt einen 64-Bit-Integer überlaufen lassen, wenn Sie verschieben, bevor Sie prüfen. Eine Spreadsheet-Engine, die Excel exakt nachbilden soll, darf an keiner dieser Stellen runden.

Die Funktionen unterteilen sich in drei Gruppen, und jede Gruppe verbirgt eine andere Falle. Bei der Basis-Konvertierung geht es um negative Zahlen und basis-spezifische Schwellenwerte. Bei komplexer Arithmetik geht es um das Parsen und Formatieren einer Zeichenfolge. Bitweise Operationen müssen innerhalb der Grenzen von Int64 bleiben. Dieser Artikel führt durch jede Gruppe, wie HotXLS sie implementiert, mit den Arbeitsblattaufrufen, die Sie tatsächlich schreiben würden.

Basis-Konvertierung und das 10-Bit-Zweierkomplement

Die Vorwärtsrichtung entspricht dem, was jeder erwartet. DEC2BIN(9) ergibt "1001", und ein optionales zweites Argument füllt das Ergebnis links auf eine feste Breite auf. Die Falle ist eine negative Eingabe. Excel schreibt kein Minuszeichen. Es kodiert den Wert als zehnstellige Zweierkomplement-Zeichenfolge in der Zielbasis, weshalb DEC2BIN(-5,10) den Wert "1111111011" zurückgibt und nicht etwa etwas mit einem Vorzeichen. Das Argument für die Stellen wird ignoriert, sobald der Wert negativ ist, da die Kodierung bereits auf zehn Stellen fixiert ist.

Zehn Stellen sind ein festes Budget, und dieses Budget legt den darstellbaren Bereich pro Basis fest. Im Binärsystem ist die Größe, die in die negative Hälfte umklappt, 512, und der Modulus für den Umbruch ist 1024. Eine Binärzeichenfolge ist also nur dann vorzeichenbehaftet, wenn sie genau zehn Zeichen lang ist und ihr Wert mindestens 512 beträgt. Dasselbe Prinzip gilt für die anderen Basen. Oktal verwendet einen halben Schwellenwert von 2^29 und einen vollen Modulus von 2^30. Hexadezimal verwendet 2^39 und 2^40. Der HotXLS-Reader wendet genau diese Regel an: Er akkumuliert die Ziffern, und nur wenn die Zeichenfolge zehn Zeichen lang ist und der akkumulierte Wert bei oder über dem halben Schwellenwert liegt, subtrahiert er den vollen Modulus, um den vorzeichenbehafteten Wert wiederherzustellen. Eine neunstellige Zeichenfolge ist immer nicht-negativ, unabhängig von ihrer Größe.

Der Encoder verhält sich genau umgekehrt. Ein nicht-negativer Wert wird Ziffer für Ziffer konvertiert und optional mit führenden Nullen auf die gewünschte Breite aufgefüllt. Er wird abgewiesen, wenn er die positive Obergrenze der Basis überschreitet oder wenn die gewünschte Breite zu gering ist. Ein negativer Wert wird zuerst in den Bereich gebracht, indem der volle Modulus addiert wird, was ihn in einen Wert verwandelt, dessen Darstellung in der Basis immer zehn Stellen umfasst. Die Stellen werden dann mit führenden Nullen ausgegeben, um die Breite zu füllen. Die einzige gemeinsame Bereichsprüfung – die symmetrischen unteren und oberen Grenzen pro Basis – sorgt dafür, dass DEC2BIN, DEC2OCT und DEC2HEX an ihren Grenzen konsistent bleiben.

Bleiben noch die basis-übergebenden Konvertierungen wie HEX2BIN und OCT2HEX, die die Basis wechseln, ohne dass im Funktionsnamen ein Umweg über Dezimalzahlen sichtbar ist. Die Implementierung enthält keine eigene Routine für jedes geordnete Paar. Sie parst die Eingabezeichenfolge unter Verwendung der Ausgangsbasis in einen vorzeichenbehafteten Dezimalwert und formatiert diesen Dezimalwert dann in die Zielbasis. Dezimal ist der Dreh- und Angelpunkt. Eine Parse-Routine und eine Format-Routine decken in Kombination alle Kombinationen ab, und da beide Hälften dieselbe zehnstellige Vorzeichenkonvention teilen, ein negative Wert übersteht den Weg mit intaktem Vorzeichen.

Komplexe Zahlen sind Zeichenfolgen, also besteht die Arbeit aus Parsen

Excel hat keinen eigenen Datentyp für komplexe Zahlen. Ein komplexer Wert ist die Zeichenfolge "a+bi", und jede Funktion in der IM-Familie übernimmt diese Zeichenfolgen und gibt eine zurück. COMPLEX baut die Zeichenfolge aus einem Real- und einem Imaginärteil auf. IMSUM, IMSUB, IMPRODUCT und IMDIV parsen ihre Argumente, führen die Arithmetik auf den numerischen Teilen aus und formatieren das Ergebnis zurück in eine Zeichenfolge. Die numerische Arbeit entspricht grundlegendem Algebra-Wissen. Die Schwierigkeit liegt ganz darin, den Text zuverlässig in zwei Gleitkommazahlen umzuwandeln, und hier bewährt sich der interne Parser.

Zwei Details in diesem Parser kann man leicht falsch machen. Das erste ist die nackte imaginäre Einheit. Die Zeichenfolge "i" bedeutet einmal i, nicht Null und kein Fehler. Wenn also der Koeffizient vor dem Suffix leer ist oder ein einzelnes Pluszeichen darstellt, muss der Parser dies als den Wert 1 lesen (und ein einzelnes Minus als -1). Lässt man das weg, ergibt IMSUM("i","i") nicht mehr 2i. Das zweite Detail ist die wissenschaftliche Notation, die mit dem Vorzeichen kollidiert, das Real- und Imaginärteil trennt. Der Parser findet dieses Trennzeichen, indem er nach einem Plus oder Minus sucht, aber eine Zahl wie "1.5E-3" enthält ein Minus, das zum Exponenten gehört. Die Suche weigert sich daher, ein Plus oder Minus als Trennzeichen zu behandeln, wenn das Zeichen direkt davor ein e oder E ist. Ohne diesen Schutz würde der Realteil am Exponenten-Vorzeichen in zwei Hälften zerrissen, und das Parsen würde bei absolut gültigen Eingaben fehlschlagen.

Das Suffix selbst wird beibehalten, anstatt normalisiert zu werden. Excel akzeptiert sowohl i als auch j, und HotXLS merkt sich, welches Zeichen die Eingabe verwendet hat, sodass das formatierte Ergebnis denselben Buchstaben trägt. Die Formatierung wendet dann die üblichen Abkürzungen an: Ein Imaginärteil von eins wird nur als Suffix gedruckt, minus eins als -i, ein Imaginärteil von Null fällt zu einem einfachen Realteil zusammen, und bei einem Realteil von Null entfällt das führende 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;

Die transzendenten komplexen Funktionen, darunter IMSQRT, IMEXP, IMLN und IMPOWER, arbeiten nicht in kartesischen Koordinaten. Sie konvertieren den geparsten Wert in die Polardarstellung, wenden die Operation auf den Betrag (Modulus) und das Argument an und konvertieren wieder zurück. Eine Quadratwurzel halbiert das Argument und zieht die Wurzel aus dem Betrag. Eine Potenzierung multipliziert das Argument und erhöht den Betrag. Jeder andere Weg würde bedeuten, jede Identität in kartesischer Form neu herzuleiten, was sowohl mehr Code bedeutet als auch in der Nähe der Verzweigungsschnitte numerisch weniger stabil ist.

Bitweise Operatoren und der Überlauf, den Sie zuerst prüfen müssen

Mit Excel 2013 wurden BITAND, BITOR, BITXOR, BITLSHIFT und BITRSHIFT eingeführt. Die Operanden sind eingeschränkt: Jeder muss eine nicht-negative Ganzzahl kleiner oder gleich 2^48 minus 1 sein, und jedes fraktionale oder negative Argument führt zu einem Rechenfehler. Diese Obergrenze ist großzügig genug, um jeden realistischen Flagsatz abzudecken, während sie gleichzeitig weit innerhalb des exakt darstellbaren Bereichs eines Double-Werts bleibt, was wichtig ist, da Excel jedes numerische Argument als Gleitkommawert übergibt.

Die Verschiebungsfunktionen enthalten die eine Reihenfolgenregel, die fatale Folgen haben kann. Eine Linksverschiebung kann einen Wert erzeugen, der weitaus größer ist als seine Eingabe. Wenn Sie die Linksverschiebung (shl) zuerst durchführen und das Ergebnis danach prüfen, haben Sie Int64 bereits überschritten und der Test ist bedeutungslos. Die Prüfung muss vor der Verschiebung erfolgen. HotXLS vergleicht den Operanden mit der um den Verschiebungsbetrag nach rechts verschobenen Obergrenze, und nur wenn der Operand passt, führt es die eigentliche Linksverschiebung aus. Eine Verschiebungsgröße über 53 Bit hinaus wird direkt abgelehnt, und eine negative Verschiebung kehrt einfach die Richtung um, sodass sich BITLSHIFT mit negativem Wert wie eine Rechtsverschiebung verhält. Das Prinzip lässt sich weit über diese eine Funktion hinaus verallgemeinern: Wenn ein Schutz existiert, um einen Überlauf zu verhindern, muss er auf die Eingaben angewendet werden, niemals auf das Ergebnis, das er schützen sollte.

// 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)');  // 5

Zukünftige Funktionen und das _xlfn-Namenspräfix

Die bitweisen Operatoren und eine lange Liste anderer Ergänzungen nach 2007 interagieren mit einem Benennungsschema, das nichts mit ihrer Berechnung zu tun hat, sondern ganz damit, wie Excel sie speichert. Das ursprüngliche binäre Arbeitsblattformat wies jeder integrierten Funktion einen numerischen Platz in einer festen Tabelle zu. Funktionen, die nach dem Einfrieren dieser Tabelle erfunden wurden, haben keinen solchen Platz. Um eine solche Funktion in einer Datei zu speichern und von einem modernen Excel erkennen zu lassen, wird der Name mit dem Präfix _xlfn. geschrieben, sodass BITAND als _xlfn.BITAND auf der Festplatte gespeichert wird, obwohl der Benutzer nur BITAND eintippt.

Der Haken ist, dass diese Regel nicht einheitlich ist. Einige neuere Funktionen erhielten Tabellenplätze und werden ungeschützt geschrieben, während einige alte versteckte Funktionen trotz ihres Alters ebenfalls ohne Präfix geschrieben werden. HotXLS führt eine explizite Whitelist darüber, welche Namen das Präfix benötigen, fügt es beim Schreiben hinzu und entfernt es beim Lesen, sodass der Formeltext, den Sie festlegen und zurücklesen, immer der saubere, für den Excel-Benutzer sichtbare Name ist. Sie setzen =BITLSHIFT(5,2), die Datei speichert _xlfn.BITLSHIFT und der Wert kommt trotzdem als 20 zurück. Das Präfix ist ein Speicherdetail, das niemals in die Formeln einfließen sollte, mit denen Sie im Code arbeiten.

Zusammenfassung in einem Arbeitsblatt

Die öffentliche Oberfläche für all das ist klein. Erstellen Sie ein TXLSXWorkbook, fügen Sie ein Arbeitsblatt hinzu und schreiben Sie entweder eine Formel über Cells[Row, Col].Formula in eine Zelle und berechnen Sie das Dokument neu, oder werten Sie einen Ausdruck direkt mit der Methode Calculate des Arbeitsblatts aus, die die Formel für diese Tabelle kompiliert und einen Variant-Wert zurückgibt. Die obigen Beispiele verwenden Calculate, weil es das Ergebnis eines einzelnen technischen Aufrufs ohne den umgebenden Tabellenzustand zeigt. Dieselben Funktionen werden jedoch innerhalb echter Zellformeln identisch ausgewertet, wenn das Arbeitsblatt neu berechnet wird.

Die Kodierungen sind der Punkt, den man im Kopf behalten sollte, nicht die Aufrufstellen. Eine Binärzeichenfolge ist nur bei zehn Stellen und nur jenseits des halben Schwellenwerts für ihre Basis vorzeichenbehaftet. Eine komplexe Zahl ist Text, ein leerer imaginärer Koeffizient entspricht eins, und der Parser übergeht das e eines Exponenten. Eine Linksverschiebung wird geprüft, bevor sie verschoben wird. Berücksichtigt man diese vier Tatsachen, ist die Familie der technischen Funktionen keine Quelle mehr für Vorzeichen-Überraschungen.

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.