Die Zelle liest =NetOf(B2, C2), B2 hält 1250, C2 hält 0,19, Excel zeigt 1012,5, und die Arbeitsmappe enthält kein VBA-Modul und kein Add-in. NetOf wohnt im Name Manager: Es ist ein definierter Name, dessen Text LAMBDA(amount, rate, amount*(1-rate)) lautet. Ein serverseitiger Rechner, der jeden definierten Namen als Bereich oder Konstante behandelt, findet dort nichts Aufrufbares, und die Zelle kommt als #NAME? zurück
Diese Zelle richtig zu berechnen verlangt mehr als einen Parser, der das Wort LAMBDA akzeptiert. Ein klassischer Tabellenkalkulations-Evaluator kennt fünf Arten von Wert: Zahlen, Text, Booleans, Fehler und Verweise auf Zellen, die solche Werte halten. LAMBDA fügt einen sechsten hinzu, einen Funktionswert, der seine Parameternamen, seinen Rumpf und die Namen trägt, die an der Schreibstelle sichtbar waren. LET ist die andere Hälfte der Geschichte, denn LET ist der Ort, an dem Benutzer diese Funktionen an kurze lokale Namen binden und sie wenige Tokens später aufrufen, oft drei Ebenen tief
Drei Stellen, an denen ein Funktionsname auflösen kann
Trifft die HotXLS-Formel-Engine auf einen Aufruf eines Namens, der keine eingebaute Funktion ist, probiert sie drei Quellen in fester Reihenfolge. Die erste ist der aktuelle LET- oder LAMBDA-Scope: Ist f eine lokale Bindung, die eine Closure hält, wendet f(21) sie an. Die zweite ist ein definierter Name im Arbeitsbuch, dessen Text mit LAMBDA beginnt, also kompiliert NetOf(1250, 0.19) den Rumpf des Namens und wendet ihn auf die zwei Argumente an. Die dritte ist der Handler für nutzerdefinierte Funktionen, den Sie vielleicht schon für eigene Delphi-Funktionen nutzen, beschrieben in eigene Funktionen in der HotXLS-Formel-Engine. Erst wenn alle drei vergeblich waren, wird die Zelle zu #NAME?
Die Reihenfolge ist in einer bestimmten Situation wichtig. Ein lokaler Name gewinnt immer gegen einen Arbeitsbuch-Namen mit derselben Schreibweise, auch wenn die lokale Bindung eine gewöhnliche Zahl hält. In dem Fall wird die Suche nach definierten Namen komplett übersprungen, der Aufruf geht direkt an den Handler für nutzerdefinierte Funktionen, und ein Arbeitsbuch-LAMBDA desselben Namens wird für diesen Aufruf nie konsultiert
Die Delphi-Seite ist kurz. Eine benannte Funktion ist schlicht ein definierter Name, hinzugefügt mit TXLSXDefinedNames.Add, und eine Closure innerhalb einer einzigen Formel braucht gar keine Registrierung:
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Model');
// Add mit zwei Argumenten = Arbeitsbuch-Scope, dort sucht call-by-name
Book.DefinedNames.Add('NetOf', 'LAMBDA(amount, rate, amount*(1-rate))');
// 1250 * (1 - 0.19) = 1012.5
Sheet.Cells[2, 2].Formula := 'NetOf(1250, 0.19)';
// Eine Closure, in einer Formel gebunden und angewendet: 42
Sheet.Cells[3, 2].Formula := 'LET(double, LAMBDA(x, x*2), double(21))';
// bump erfasst base = 100; bump(5) = 105, step*2 = 210
Sheet.Cells[4, 2].Formula :=
'LET(base, 100, bump, LAMBDA(v, v+base), LET(step, bump(5), step*2))';
// Postfix-Anwendung, ganz ohne Name: 42
Sheet.Cells[5, 2].Formula := 'LAMBDA(x, x*2)(21)';
Book.Recalculate;
Book.SaveAs('lambda-model.xlsx');
finally
Book.Free;
end;
end;
Die letzte Zelle zeigt eine Form, die gern vergessen wird. Excel erlaubt es, einen LAMBDA-Ausdruck direkt anzuwenden, indem man die Argumentliste unmittelbar dahinter schreibt, und HotXLS parst LAMBDA(x, x*2)(21) auf dieselbe Weise. Der Parser akzeptiert diese nachgestellte Argumentliste nur nach etwas, das eine Funktion erzeugen könnte, etwa einem Namen, einem LET- oder IF-Ausdruck oder einer weiteren Anwendung. (1+2)(3) zu schreiben scheitert also schon beim Kompilieren, nicht erst bei der Auswertung
Was eine Closure behält, und warum Parameter gewinnen
Die Auswertung eines LAMBDA-Ausdrucks in HotXLS produziert einen Closure-Wert: die Parameternamen, eine Kopie des Rumpfs, das Blatt, auf dem er geschrieben wurde, und einen Snapshot jeder LET- oder LAMBDA-Bindung, die in diesem Moment sichtbar war. In der vierten Zelle oben wird bump erzeugt, während base bereits an 100 gebunden ist, also trägt die Closure base = 100 mit sich. Läuft bump(5) innerhalb des inneren LET, sieht der Rumpf weiterhin diese 100, obwohl das innere LET nichts von base dem Namen nach weiß
Ein Aufruf passiert dann in drei Schritten. Die Argumentausdrücke werden zuerst ausgewertet, im Scope des Aufrufers. Dann wird die erfasste Umgebung geschoben, darauf die Parameter, und der Rumpf wird ausgewertet. Lookups suchen von oben nach unten, also überschattet ein Parameter namens rate eine erfasste Bindung namens rate, und eine erfasste Bindung überschattet alles, was der Aufrufer zufällig unter demselben Namen gebunden hat. Kehrt der Rumpf zurück oder scheitert, wird der Scope auf den Punkt des Aufrufbeginns zurückgeschnitten
Die Argumente auszuwerten, bevor irgendeine Bindung installiert ist, klingt nach einem Detail, bis man LET(x, 10, f, LAMBDA(x, x+1), f(x*2)) schreibt. Das Argument x*2 muss das äußere x meinen, was 20 ergibt, und das Ergebnis ist 21. Würde man den Parameter zuerst installieren, läse das Argument den Parameter, den es gerade selbst definieren will. Die Regel, die dabei herausfällt, ist die, die Excel-Benutzer erwarten: Was eine benannte Funktion berechnet, hängt von ihren Argumenten und dem ab, was sie beim Schreiben erfasst hat, und die anderen lokalen Namen des Aufrufers sickern nie hinein
Warum scheitert f(21), wenn f eine Zahl hält?
Eine lokale Bindung, die etwas anderes als eine Closure hält, wird nie als Funktion angewendet. LET(f, 3, f(21)) multipliziert nicht 3 mit 21, und es fällt auch nicht auf einen Arbeitsbuch-Namen namens f zurück. Der Aufruf wandert zum Handler für nutzerdefinierte Funktionen weiter, und wenn kein Handler den Namen beansprucht, endet die Zelle als #NAME?. Dieses Ergebnis ist das richtige für eine Tabellenkalkulation: Ein Tippfehler, der still einen Funktionsaufruf in eine Multiplikation verwandelt, würde eine plausible Zahl produzieren, die niemand anzweifelt
Postfix-Anwendung ist noch strenger. Was vor der Argumentliste sitzt, wird ausgewertet, und ist das Ergebnis keine Closure, bekommt die Zelle #VALUE!, also scheitern LET(g, 3, (g)(21)) und LET(g, 3, g(21)) mit unterschiedlichen Fehlern. Weitere Fälle, die Sie in einem Wegwerf-Arbeitsbuch reproduzieren können:
- Falsche Argumentzahl:
LET(add, LAMBDA(a, b, a+b), add(1))gibt #VALUE! zurück, bevor der Argumentausdruck überhaupt ausgewertet wird - Fehler in Argumenten reisen als Werte: In
LET(safe, LAMBDA(x, IFERROR(x, 0)), safe(1/0))wird das #DIV/0! anxgebunden, und das IFERROR im Rumpf verwandelt es in 0 - Referenzen bleiben Referenzen: Ein Argument wie
A1erreicht den Rumpf als Zellreferenz, also sehen Funktionen, denen Referenzidentität wichtig ist, ISREF eingeschlossen, eine Referenz statt des Zellwerts - Eine LAMBDA-Definition akzeptiert bis zu 253 Parameter, dieselbe Obergrenze, die Excel für LAMBDA dokumentiert
- Ausufernde Rekursion stoppt: Ein benanntes Lambda, das sich selbst aufruft, teilt sich eine Vorgabe-Obergrenze von 256 verschachtelten Ebenen mit der gewöhnlichen Formelverschachtelung, und darüber hinaus endet der Aufruf mit einem Ressourcengrenzen-Fehler, statt den Delphi-Stack aufzubrauchen
// Log ist die eigene Logging-Routine, kein Teil von HotXLS
procedure ProbeLambdaErrors(Sheet: TXLSXWorksheet; Book: TXLSXWorkbook);
begin
Sheet.Cells[2, 4].Formula := 'LET(f, 3, f(21))'; // #NAME? ohne Handler
Sheet.Cells[3, 4].Formula := 'LET(g, 3, (g)(21))'; // #VALUE!, Postfix
Sheet.Cells[4, 4].Formula := 'LET(add, LAMBDA(a, b, a+b), add(1))'; // #VALUE!, Arität
Sheet.Cells[5, 4].Formula := 'LET(safe, LAMBDA(x, IFERROR(x, 0)), safe(1/0))'; // 0
Book.Recalculate;
Log(Sheet.Cells[2, 4].FormattedText);
Log(Sheet.Cells[3, 4].FormattedText);
Log(Sheet.Cells[4, 4].FormattedText);
Log(Sheet.Cells[5, 4].FormattedText);
end;
LET-Bindungen in HotXLS, verschachtelt und partiell
HotXLS wertet LET von links nach rechts aus: Jede Bindung wird mit allen früheren Bindungen im Scope ausgewertet, geschoben, und das letzte Argument wird unter dem vollständigen Satz ausgewertet. LET(a, 1, b, a+1, LET(c, b*2, c)) gibt also 4 zurück, und das innere LET sieht sowohl a als auch b. Ist das äußere LET fertig, sind seine Bindungen weg, was bedeutet, dass eine Nachbarformel in der nächsten Zelle aus einem leeren Scope startet
Zwei Formregeln werden durchgesetzt, bevor irgendwas läuft. LET braucht eine ungerade Anzahl von Argumenten, mindestens drei, also ist LET(a, 1) ein #VALUE!. Und jeder Namensslot muss ein schlichter Name sein: LET(A1, 5, A1*2) wird abgelehnt, weil A1 eine Zellreferenz ist, während kurze Namen wie x oder ab, die auch Spaltenbuchstaben schreiben, innerhalb von LET und LAMBDA als lokale Namen gelesen werden. Eine Bindung an einen Bereich behält den Bereich, also gibt LET(r, A1:A10, ROWS(r)) 10 zurück, und SUM(r) addiert die Zellen
Dieselben Closure-Werte speisen die Helferfunktionen, die dafür gebaut wurden: MAP, REDUCE, SCAN, MAKEARRAY, BYROW und BYCOL. MAP(A2:A6, LAMBDA(v, v*2)) verschüttet fünf verdoppelte Werte, und REDUCE(0, A2:A6, LAMBDA(acc, v, acc+v)) faltet sie zu einer Summe zusammen. Die erfasste Umgebung reist in diese Aufrufe genauso mit wie bei einem direkten Aufruf, also ist eine per LET gebundene Rate weiterhin im MAP-Rumpf sichtbar
Komma, Semikolon und was SaveAs schreibt
Der HotXLS-Formel-Parser nimmt das Komma und das Semikolon als Argumenttrenner austauschbar. Formeltext kommt aus Quellen, die Sie nicht kontrollieren: ein Support-Ticket von einer Maschine mit deutschem Locale, Dokumentation, die die kanonischen Excel-Kommas verwendet, ein Skript, das Formelstrings generiert, älterer HotXLS-Code, der durchweg Semikolons verwendete. SUM(A1,A2) und SUM(A1;A2) kompilieren zu demselben Ding, und ein LAMBDA, das in beiden Schreibweisen geschrieben ist, ebenfalls
Inline-Array-Konstanten sind die Ausnahme. Innerhalb von geschweiften Klammern behält das Semikolon seine Excel-Bedeutung als Zeilentrenner, also ist {1,2;3,4} ein Zwei-mal-zwei-Array, keine Liste mit vier Argumenten. Generieren Sie Formeln mit Semikolons als Argumenttrennern, halten Sie Array-Konstanten in der Excel-Syntax, und die beiden kollidieren nie
Cell.Formula gibt den Text zurück, den Sie zugewiesen haben. Schreibt SaveAs eine XLSX-Datei, werden Semikolons als Argumenttrenner außerhalb von Klammern als Kommas geschrieben, denn das OOXML-Dateiformat speichert Formeln in der Komma-Syntax von Excel, und Funktionen, die Excel unter einem _xlfn.-Präfix ablegt, bekommen dieses Präfix auf dem Weg hinaus. Eine Formel, die aus einer Datei geladen und nicht angefasst wurde, wird aus ihrem originalen XML-Text zurückgeschrieben
Benannte Lambdas aus Kunden-Arbeitsmappen lesen
In Excel erstellte Arbeitsmappen speichern LAMBDA-Namen so, wie Excel sie schreibt: mit einem _xlfn.-Präfix auf dem Funktionsnamen und oft _xlpm. auf den Parametern. HotXLS streift diese Präfixe ab, wenn es den Namen auswertet, aber TXLSXDefinedName.Formula liefert den Definitionstext so, wie die Datei ihn geliefert hat. Eine Prüfung der Form Copy(Name.Formula, 1, 6) = 'LAMBDA' besteht für Namen, die Sie im Code erzeugt haben, und verfehlt jeden Namen, der aus Excel kam. Diese Diskrepanz ist leicht zu übersehen, denn die berechneten Werte stimmen ohnehin in beiden Fällen, und nur der eigene Audit-Code meckert
// IsLambdaDefinition ist ein selbst geschriebener Helfer, keine HotXLS-API
function IsLambdaDefinition(const Text: WideString): Boolean;
var
S: WideString;
begin
S := UpperCase(Trim(Text));
if (S <> '') and (S[1] = '=') then
Delete(S, 1, 1);
if Copy(S, 1, 6) = '_XLFN.' then
Delete(S, 1, 6);
Result := Copy(S, 1, 7) = 'LAMBDA(';
end;
procedure AuditLambdas(const FileName: string);
var
Book: TXLSXWorkbook;
Name: TXLSXDefinedName;
I: Integer;
begin
// Log ist die eigene Logging-Routine
Book := TXLSXWorkbook.Create;
try
if Book.Open(FileName) = 1 then
begin
for I := 0 to Book.DefinedNames.Count - 1 do
begin
Name := Book.DefinedNames[I];
if IsLambdaDefinition(Name.Formula) and (Name.SheetIndex >= 0) then
Log('Sheet-scoped lambda, not callable by name: ' + Name.Name);
end;
Book.Recalculate;
Log(VarToStr(Book.Sheets[1].Cells[2, 2].Value));
end;
finally
Book.Free;
end;
end;
Die Scope-Prüfung in dieser Schleife existiert, weil call-by-name in HotXLS den Aufgerufenen unter den Arbeitsbuch-Namen sucht. Halten Sie benannte Funktionen im Arbeitsbuch-Scope. Einen blattbezogenen LAMBDA-Namen können Sie weiterhin über FindByName(AName, ASheetIndex) lesen, aber eine Funktion, die aus mehreren Blättern aufgerufen wird, gehört ins Arbeitsbuch, und die Scoping-Regeln dahinter legen definierte Namen und blattübergreifende Formeln dar
Ein Lambda später zu bearbeiten ist billig. Einen neuen Text TXLSXDefinedName.Formula zuzuweisen, invalidiert den Abhängigkeitsgraph, also baut das nächste Recalculate ihn wieder auf und wertet die Zellen neu aus, die die Funktion aufrufen; die Mechanik steht in inkrementelle Neuberechnung und der Abhängigkeitsgraph. Eine Zelle, deren Formel zu einer nackten Closure auswertet, etwa =LAMBDA(x, x*2) ohne Aufruf, bekommt gar keinen gecachten Wert, denn es gibt keine Zahl und keinen Text zu speichern; Excel zeigt für dieselbe Zelle #CALC!. Für Berichte, die in Werkzeugen ohne LAMBDA-Unterstützung geöffnet werden müssen, frieren Sie die Ergebnisse mit Sheet.Cells.ConvertFormulasToValues nach dem letzten Recalculate ein, was jeden gecachten Wert behält und den Formeltext verwirft
LAMBDA, LET, die Helferfunktionen und die Defined-Name-API sind alle Teil derselben nativen Delphi- und C++Builder-Bibliothek, die XLS, XLSX und ODS liest und schreibt, ohne dass Excel installiert ist. Die vollständige Formelfunktionsliste steht auf der HotXLS-Spreadsheet-Komponentenseite