Komórka zawiera =NetOf(B2, C2), B2 trzyma 1250, C2 trzyma 0,19, Excel pokazuje 1012,5, a skoroszyt nie ma modułu VBA ani dodatku. NetOf mieszka w Menedżerze nazw: to nazwa zdefiniowana, której tekstem jest LAMBDA(amount, rate, amount*(1-rate)). Serwerowy kalkulator, który traktuje każdą nazwę zdefiniowaną jako zakres albo stałą, nie znajduje tam niczego, co dałoby się wywołać, i komórka wraca jako #NAME?
Poprawne wyliczenie takiej komórki wymaga więcej niż parsera akceptującego słowo LAMBDA. Klasyczny ewaluator arkusza zna pięć rodzajów wartości: liczby, tekst, wartości logiczne, błędy i odwołania do komórek trzymających tamte. LAMBDA dodaje szósty: wartość funkcyjną, która niesie nazwy swoich parametrów, swoje ciało i nazwy widoczne w miejscu, gdzie została napisana. LET to druga połowa historii, bo w LET użytkownicy wiążą te funkcje z krótkimi lokalnymi nazwami i wołają je kilka tokenów później, często trzy poziomy w głąb
Trzy miejsca, gdzie nazwa funkcji może się rozwiązać
Gdy silnik formuł HotXLS spotyka wywołanie nazwy, która nie jest funkcją wbudowaną, próbuje trzech źródeł w stałej kolejności. Pierwszym jest bieżący zakres LET albo LAMBDA: jeśli f jest lokalnym wiązaniem trzymającym domknięcie, f(21) je stosuje. Drugim jest zdefiniowana nazwa skoroszytu, której tekst zaczyna się od LAMBDA, więc NetOf(1250, 0.19) kompiluje ciało tej nazwy i stosuje je do dwóch argumentów. Trzecim jest obsługiwacz funkcji użytkownika, którego być może już używasz do własnych funkcji Delphi, opisany w funkcjach własnych w silniku formuł HotXLS. Dopiero gdy wszystkie trzy zawiodą, komórka staje się #NAME?
Kolejność ma znaczenie w jednej konkretnej sytuacji. Lokalna nazwa zawsze wygrywa z nazwą skoroszytu o tej samej pisowni, nawet gdy lokalne wiązanie trzyma zwykłą liczbę. W takim wypadku wyszukiwanie nazw zdefiniowanych jest całkowicie pomijane i wywołanie idzie prosto do obsługiwacza funkcji użytkownika, a LAMBDA skoroszytu o tej samej nazwie nie jest dla tego wywołania nigdy pytana
Strona Delphi jest krótka. Nazwana funkcja to po prostu zdefiniowana nazwa dodana przez TXLSXDefinedNames.Add, a domknięcie wewnątrz jednej formuły nie potrzebuje żadnej rejestracji:
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Model');
// Dwuargumentowe Add = zakres skoroszytu, tego szuka wywołanie po nazwie
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)';
// Domknięcie związane i zastosowane wewnątrz jednej formuły: 42
Sheet.Cells[3, 2].Formula := 'LET(double, LAMBDA(x, x*2), double(21))';
// bump przechwytuje 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))';
// Zastosowanie przyrostkowe, w ogóle bez nazwy: 42
Sheet.Cells[5, 2].Formula := 'LAMBDA(x, x*2)(21)';
Book.Recalculate;
Book.SaveAs('lambda-model.xlsx');
finally
Book.Free;
end;
end;
Ostatnia komórka pokazuje postać, o której ludzie zapominają. Excel pozwala zastosować wyrażenie LAMBDA bezpośrednio, pisząc listę argumentów zaraz po nim, i HotXLS parsuje LAMBDA(x, x*2)(21) tak samo. Parser akceptuje tę końcową listę argumentów tylko po czymś, co mogłoby wytworzyć funkcję, jak nazwa, wyrażenie LET albo IF, albo inne zastosowanie. Napisanie (1+2)(3) zawodzi w czasie kompilacji, a nie w czasie wyliczania
Co domknięcie pamięta i dlaczego parametry wygrywają
Wyliczenie wyrażenia LAMBDA w HotXLS wytwarza wartość domknięcia: nazwy parametrów, kopię ciała, arkusz, na którym zostało napisane, i migawkę każdego wiązania LET albo LAMBDA widocznego w tej chwili. W czwartej komórce wyżej bump powstaje, gdy base jest już związane z 100, więc domknięcie ciągnie za sobą base = 100. Gdy bump(5) biegnie wewnątrz wewnętrznego LET, ciało nadal widzi tamto 100, mimo że wewnętrzne LET nie wie nic o base z nazwy
Wywołanie dzieje się potem w trzech krokach. Najpierw wyliczane są wyrażenia argumentów, w zakresie wołającego. Potem na stos schodzi przechwycone środowisko, na nim parametry, i wyliczane jest ciało. Wyszukiwania biegną od góry w dół, więc parametr o nazwie rate zasłania przechwycone wiązanie o nazwie rate, a przechwycone wiązanie zasłania cokolwiek, co wołający akurat związał pod tą samą nazwą. Gdy ciało zwróci albo zawiedzie, zakres jest ścięty z powrotem do miejsca, gdzie wywołanie się zaczęło
Wyliczanie argumentów przed zainstalowaniem jakiegokolwiek wiązania brzmi jak szczegół, dopóki nie napiszesz LET(x, 10, f, LAMBDA(x, x+1), f(x*2)). Argument x*2 musi znaczyć zewnętrzne x, co daje 20, a wynik to 21. Gdyby parametr był instalowany najpierw, argument czytałby parametr, który ma dopiero zdefiniować. Reguła, która z tego wypada, jest tą, której oczekują użytkownicy Excela: to, co liczy nazwana funkcja, zależy od jej argumentów i od tego, co przechwyciła w chwili zapisu, a pozostałe lokalne nazwy wołającego nigdy nie przeciekają do jej środka
Dlaczego f(21) zawodzi, gdy f trzyma liczbę?
Lokalne wiązanie trzymające coś innego niż domknięcie nigdy nie jest stosowane jako funkcja. LET(f, 3, f(21)) nie mnoży 3 przez 21 i nie spada też do nazwy skoroszytu zwanej f. Wywołanie idzie dalej do obsługiwacza funkcji użytkownika, a jeśli żaden handler nie rości sobie nazwy, komórka kończy jako #NAME?. Ten wynik jest właściwy dla arkusza kalkulacyjnego: literówka, która po cichu zamieniła wywołanie funkcji w mnożenie, wytworzyłaby wiarygodną liczbę, której nikt by nie zakwestionował
Zastosowanie przyrostkowe jest jeszcze bardziej rygorystyczne. Cokolwiek siedzi przed listą argumentów, jest wyliczane, a jeśli wynik nie jest domknięciem, komórka dostaje #VALUE!, więc LET(g, 3, (g)(21)) i LET(g, 3, g(21)) zawodzą z różnymi błędami. Pozostałe porażki możesz odtworzyć w roboczym skoroszycie:
- Zła liczba argumentów:
LET(add, LAMBDA(a, b, a+b), add(1))zwraca #VALUE!, zanim wyrażenie argumentu zostanie wyliczone - Błędy wewnątrz argumentów podróżują jako wartości: w
LET(safe, LAMBDA(x, IFERROR(x, 0)), safe(1/0))#DIV/0! jest związane zx, a IFERROR wewnątrz ciała zamienia je w 0 - Odwołania zostają odwołaniami: argument taki jak
A1dociera do ciała jako odwołanie do komórki, więc funkcje przykładające wagę do tożsamości odwołania, ISREF wśród nich, widzą odwołanie, a nie wartość komórki - Definicja LAMBDA przyjmuje do 253 parametrów, ten sam sufit, który dokumentuje Excel dla LAMBDA
- Rozbiegana rekurencja się zatrzymuje: nazwana lambda wołająca samą siebie dzieli domyślny sufit 256 zagnieżdżonych poziomów ze zwykłym zagnieżdżaniem formuł, a ponadten sufit wywołanie kończy się błędem limitu zasobów zamiast wyczerpać stos Delphi
// Log to twoja własna rutyna logująca, nie część HotXLS
procedure ProbeLambdaErrors(Sheet: TXLSXWorksheet; Book: TXLSXWorkbook);
begin
Sheet.Cells[2, 4].Formula := 'LET(f, 3, f(21))'; // #NAME? bez handlera
Sheet.Cells[3, 4].Formula := 'LET(g, 3, (g)(21))'; // #VALUE!, przyrostkowo
Sheet.Cells[4, 4].Formula := 'LET(add, LAMBDA(a, b, a+b), add(1))'; // #VALUE!, arność
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;
Wiązania LET w HotXLS: zagnieżdżanie i reguły kształtu
HotXLS wylicza LET od lewej do prawej: każde wiązanie jest wyliczane ze wszystkimi wcześniejszymi wiązaniami w zakresie, odkładane na stos, a ostatni argument jest wyliczany pod kompletnym zbiorem. Więc LET(a, 1, b, a+1, LET(c, b*2, c)) zwraca 4, a wewnętrzne LET widzi i a, i b. Gdy zewnętrzne LET się kończy, jego wiązania znikają, co znaczy, że sąsiednia formuła w następnej komórce startuje z pustego zakresu
Dwie reguły kształtu są wymuszane, zanim cokolwiek pobiegnie. LET potrzebuje nieparzystej liczby argumentów, co najmniej trzech, więc LET(a, 1) to #VALUE!. I każdy slot nazwy musi być gołą nazwą: LET(A1, 5, A1*2) jest odrzucane, bo A1 to odwołanie do komórki, podczas gdy krótkie nazwy takie jak x albo ab, które zapisują też litery kolumn, są wewnątrz LET i LAMBDA czytane jako nazwy lokalne. Wiązanie do zakresu zachowuje zakres, więc LET(r, A1:A10, ROWS(r)) zwraca 10, a SUM(r) sumuje komórki
Te same wartości domknięć zasilają funkcje pomocnicze zbudowane dla nich: MAP, REDUCE, SCAN, MAKEARRAY, BYROW i BYCOL. MAP(A2:A6, LAMBDA(v, v*2)) rozlewa pięć podwojonych wartości, a REDUCE(0, A2:A6, LAMBDA(acc, v, acc+v)) zawija je w jedną sumę. Przechwycone środowisko podróżuje do tych wywołań dokładnie tak jak przy wywołaniu bezpośrednim, więc stopa związana w LET jest nadal widoczna wewnątrz ciała MAP
Przecinki, średniki i to, co zapisuje SaveAs
Parser formuł HotXLS traktuje przecinek i średnik jako separatory argumentów wymiennie. Tekst formuły przychodzi z miejsc, których nie kontrolujesz: ze zgłoszenia wsparcia pisanego na maszynie z niemiecką lokalizacją, z dokumentacji używającej kanonicznych przecinków Excela, ze skryptu generującego łańcuchy formuł, ze starszego kodu HotXLS używającego wyłącznie średników. SUM(A1,A2) i SUM(A1;A2) kompilują się do tego samego, i tak samo LAMBDA napisana w dowolny z tych sposobów
Wyjątkiem są wstawione w linii stałe tablicowe. Wewnątrz nawiasów klamrowych średnik zachowuje swoje Excelowe znaczenie separatora wierszy, więc {1,2;3,4} to tablica dwa na dwa, nie lista czterech argumentów. Jeśli generujesz formuły ze średnikami jako separatorami argumentów, trzymaj stałe tablicowe w składni Excela, i oba światy nigdy się nie zderzą
Cell.Formula zwraca tekst, który przypisałeś. Gdy SaveAs zapisuje plik XLSX, średniki użyte jako separatory argumentów poza nawiasami klamrowymi są zapisywane jako przecinki, bo format plików OOXML przechowuje formuły w przecinkowej składni Excela, a funkcje, które Excel trzyma pod prefiksem _xlfn., dostają ten prefiks na wyjściu. Formuła wczytana z pliku i nietknięta jest zapisywana z powrotem ze swojego oryginalnego tekstu XML
Odczyt nazwanych lambd ze skoroszytów klientów
Skoroszyty autorstwa w Excelu trzymają nazwy LAMBDA tak, jak je pisze Excel: z prefiksem _xlfn. na nazwie funkcji i często _xlpm. na parametrach. HotXLS zdejmuje te prefiksy, gdy wylicza nazwę, ale TXLSXDefinedName.Formula zwraca tekst definicji takim, jakim dostarczył go plik. Sprawdzenie napisane jako Copy(Name.Formula, 1, 6) = 'LAMBDA' przechodzi dla nazw utworzonych w kodzie i gubi każdą nazwę, która wyszła z Excela. Ta rozbieżność łatwo umyka, bo wyliczone wartości są poprawne w obu wypadkach i tylko twój własny kod audytu się nie zgadza
// IsLambdaDefinition to pomocnik, który dopisujesz sam, nie API HotXLS
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 to twoja własna rutyna logująca
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;
Sprawdzenie zakresu w tej pętli tam jest, bo wywołanie po nazwie w HotXLS szuka wołanego wśród nazw poziomu skoroszytu. Trzymaj nazwane funkcje w zakresie skoroszytu. Nazwa LAMBDA o zakresie arkusza wciąż da się odczytać przez FindByName(AName, ASheetIndex), ale funkcja, którą ludzie wołają z kilku arkuszy, należy do skoroszytu, a reguły zakresowania za tym stojące są rozłożone w nazwach zdefiniowanych i formułach między arkuszami
Późniejsza edycja lambdy jest tania. Przypisanie nowego tekstu do TXLSXDefinedName.Formula unieważnia graf zależności, więc następne Recalculate go przebudowuje i ponownie wylicza komórki wołające funkcję; mechanika jest w przyrostowym przeliczaniu i grafie zależności. Komórka, której formuła wylicza się do gołego domknięcia, jak =LAMBDA(x, x*2) bez wywołania, nie dostaje w ogóle buforowanej wartości, bo nie ma liczby ani tekstu do zapisania; Excel pokazuje dla tej samej komórki #CALC!. Dla raportów, które muszą otwierać się w narzędziach bez wsparcia LAMBDA, zamroź wyniki przez Sheet.Cells.ConvertFormulasToValues po ostatnim Recalculate, co zachowuje każdą buforowaną wartość i wyrzuca tekst formuły
LAMBDA, LET, funkcje pomocnicze i API nazw zdefiniowanych płyną wszystkie w tej samej natywnej bibliotece dla Delphi i C++Buildera, która czyta i zapisuje XLS, XLSX i ODS bez zainstalowanego Excela. Pełna lista funkcji formuł jest na stronie komponentu arkusza kalkulacyjnego HotXLS