Artykuł techniczny

LAMBDA i LET w Delphi: domknięcia formuł HotXLS

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

Diagram trzech ścieżek rozwiązania wywołania HotXLS w Delphi próbowanych po kolei dla nieznanego nazwiska funkcji jak f(21): lokalne wiązanie zakresu LET lub LAMBDA trzymające domknięcie, nazwa zdefiniowana skoroszytu, której tekst RefersTo zaczyna się od LAMBDA, i klasyczna procedura obsługi funkcji użytkownika, podczas gdy lokalne wiązanie trzymające liczbę nigdy nie jest stosowane jako funkcja, a wywołanie kończy się #NAME?, gdy żaden handler funkcji użytkownika nie rości sobie nazwy
HotXLS próbuje najpierw zakresu lokalnego, potem nazw zdefiniowanych LAMBDA, potem klasycznego handlera funkcji użytkownika i odmawia wywołania wiązania, które nie trzyma domknięcia

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

Diagram wywołania domknięcia LAMBDA HotXLS w Delphi: wartość domknięcia niesie swój parametr, ciało i bazowe wiązanie przechwycone z otaczającego LET, wywołanie odkłada przechwycone środowisko przed argumentem v = 5, ciało wylicza się do 105, a stos zakresów skraca się z powrotem do swojego znacznika wejścia w strażniku finally
Wywołanie domknięcia pcha przechwycone środowisko przed wiązaniami argumentów, i właśnie ta kolejność sprawia, że parametry zasłaniają nazwy zewnętrzne

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 z x, a IFERROR wewnątrz ciała zamienia je w 0
  • Odwołania zostają odwołaniami: argument taki jak A1 dociera 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