Техническая статья

HotXLS: замыкания LAMBDA и LET в формулах Delphi

В ячейке =NetOf(B2, C2), B2 держит 1250, C2 держит 0.19, Excel показывает 1012.5, а в книге нет ни VBA-модуля, ни надстройки. NetOf живёт в диспетчере имён: это определённое имя, чей текст — LAMBDA(amount, rate, amount*(1-rate)). Серверный вычислитель, трактующий каждое определённое имя как диапазон или константу, не находит здесь ничего вызываемого, и ячейка возвращается как #NAME?

Чтобы эта ячейка считалась правильно, мало парсера, принимающего слово LAMBDA. Классический вычислитель электронных таблиц знает пять видов значений: числа, текст, логические значения, ошибки и ссылки на ячейки со всем перечисленным. LAMBDA добавляет шестой — значение функции, несущее имена параметров, тело и имена, видимые в месте написания. LET — вторая половина истории, потому что именно в LET пользователи связывают эти функции с короткими локальными именами и вызывают их несколькими токенами позже, часто тремя уровнями глубже

Три места, где может разрешиться имя функции

Встречая вызов имени, которое не является встроенной функцией, движок формул HotXLS пробует три источника в фиксированном порядке. Первый — текущая область LET или LAMBDA: если f — локальная привязка с замыканием, f(21) его применяет. Второй — определённое имя книги, чей текст начинается с LAMBDA, поэтому NetOf(1250, 0.19) компилирует тело имени и применяет его к двум аргументам. Третий — обработчик пользовательских функций, который вы, возможно, уже используете для кастомных функций Delphi; он описан в статье пользовательские функции в движке формул HotXLS. Только когда не подошли все три, ячейка становится #NAME?

Порядок играет в одной конкретной ситуации. Локальное имя всегда побеждает имя книги с тем же написанием, даже когда локальная привязка держит обычное число. Тогда поиск по определённым именам пропускается целиком, вызов уходит прямо в обработчик пользовательских функций, и книга с LAMBDA того же имени для этого вызова не опрашивается никогда

Диаграмма трёх путей разрешения вызова HotXLS в Delphi, пробуемых по порядку для неизвестного имени функции вроде f(21): локальная привязка области LET или LAMBDA, держащая замыкание; определённое имя книги, чей текст RefersTo начинается с LAMBDA; и классический обработчик пользовательских функций, тогда как локальная привязка, держащая число, никогда не применяется как функция, а вызов заканчивается #NAME?, когда имя не забрал ни один обработчик пользовательских функций
HotXLS пробует сначала локальную область, затем именованные LAMBDA, затем классический обработчик пользовательских функций и отказывается вызывать привязку без замыкания

Сторона Delphi коротка. Именованная функция — просто определённое имя, добавленное через TXLSXDefinedNames.Add, а замыканию внутри одной формулы не нужна никакая регистрация:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Model');

    // Add с двумя аргументами = область книги, именно её ищет вызов по имени
    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)';

    // Замыкание, связанное и применённое внутри одной формулы: 42
    Sheet.Cells[3, 2].Formula := 'LET(double, LAMBDA(x, x*2), double(21))';

    // bump захватывает 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))';

    // Постфиксное применение, вообще без имени: 42
    Sheet.Cells[5, 2].Formula := 'LAMBDA(x, x*2)(21)';

    Book.Recalculate;
    Book.SaveAs('lambda-model.xlsx');
  finally
    Book.Free;
  end;
end;

Последняя ячейка показывает форму, о которой склонны забывать. Excel позволяет применить LAMBDA-выражение напрямую, дописав список аргументов сразу за ним, и HotXLS разбирает LAMBDA(x, x*2)(21) так же. Парсер принимает завершающий список аргументов только после того, что может произвести функцию: имени, LET или IF-выражения, либо другого применения. Запись (1+2)(3) падает на компиляции, а не на вычислении

Что помнит замыкание и почему побеждают параметры

Вычисление LAMBDA-выражения в HotXLS производит значение-замыкание: имена параметров, копию тела, лист, на котором оно написано, и снимок всех привязок LET или LAMBDA, видимых в тот момент. В четвёртой ячейке выше bump создаётся, когда base уже связана со 100, поэтому замыкание несёт с собой base = 100. Когда bump(5) выполняется внутри внутреннего LET, тело всё ещё видит эту сотню, хотя внутренний LET ничего не знает об имени base

Затем вызов происходит в три шага. Сначала вычисляются выражения аргументов — в области вызывающего. Потом заталкивается захваченное окружение, поверх него параметры, и вычисляется тело. Поиск идёт сверху вниз, поэтому параметр с именем rate скрывает захваченную привязку с именем rate, а захваченная привязка скрывает всё, что вызывающий случайно связал под тем же именем. Когда тело возвращает результат или падает, область срезается обратно до точки, где вызов начался

Диаграмма вызова замыкания LAMBDA в HotXLS в Delphi: значение замыкания несёт свой параметр, тело и базовую привязку, захваченную из окружающего LET; вызов кладёт захваченное окружение перед аргументом v = 5, тело вычисляется в 105, а стек областей усекается обратно до входной отметки внутри защитного finally
Вызов замыкания кладёт захваченное окружение раньше привязок аргументов, и именно этот порядок заставляет параметры затенять внешние имена

Вычислять аргументы до установки каких-либо привязок звучит как мелочь, пока вы не напишете LET(x, 10, f, LAMBDA(x, x+1), f(x*2)). Аргумент x*2 обязан означать внешний x, что даёт 20, и результат — 21. Если бы параметр устанавливался первым, аргумент читал бы тот параметр, который сам сейчас определит. Правило, которое отсюда выпадает, — то самое, которого ждут пользователи Excel: что вычисляет именованная функция, зависит от её аргументов и от того, что она захватила при написании, а прочие локальные имена вызывающего в неё не просачиваются никогда

Почему f(21) падает, когда f держит число?

Локальная привязка, держащая что-то кроме замыкания, никогда не применяется как функция. LET(f, 3, f(21)) не умножает 3 на 21 и не откатывается к имени книги с именем f. Вызов переходит к обработчику пользовательских функций, и если ни один обработчик имя не забрал, ячейка заканчивается как #NAME?. Для электронной таблицы это правильный исход: опечатка, молча превратившая вызов функции в умножение, выдала бы правдоподобное число, в котором никто не усомнится

Постфиксное применение строже. Что бы ни стояло перед списком аргументов, оно вычисляется, и если результат — не замыкание, ячейка получает #VALUE!, поэтому LET(g, 3, (g)(21)) и LET(g, 3, g(21)) падают с разными ошибками. Остальные сбои можно воспроизвести в черновой книге:

  • Неверное число аргументов: LET(add, LAMBDA(a, b, a+b), add(1)) возвращает #VALUE! ещё до вычисления выражения аргумента
  • Ошибки внутри аргументов путешествуют как значения: в LET(safe, LAMBDA(x, IFERROR(x, 0)), safe(1/0)) ошибка #DIV/0! связывается с x, и IFERROR внутри тела превращает её в 0
  • Ссылки остаются ссылками: аргумент вроде A1 доходит до тела как ссылка на ячейку, поэтому функции, которым важна идентичность ссылки — ISREF среди них, — видят ссылку, а не значение ячейки
  • Определение LAMBDA принимает до 253 параметров — тот же потолок, что документирует Excel для LAMBDA
  • Ушедшая вразнос рекурсия останавливается: именованная лямбда, вызывающая себя, делит с обычной вложенностью формул потолок по умолчанию в 256 вложенных уровней, а дальше вызов завершается ошибкой лимита ресурсов вместо исчерпания стека Delphi
// Log — ваша собственная процедура логирования, не часть HotXLS
procedure ProbeLambdaErrors(Sheet: TXLSXWorksheet; Book: TXLSXWorkbook);
begin
  Sheet.Cells[2, 4].Formula := 'LET(f, 3, f(21))';                       // #NAME? без обработчика
  Sheet.Cells[3, 4].Formula := 'LET(g, 3, (g)(21))';                     // #VALUE!, постфикс
  Sheet.Cells[4, 4].Formula := 'LET(add, LAMBDA(a, b, a+b), add(1))';     // #VALUE!, арность
  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 в HotXLS: вложенность и частичность

HotXLS вычисляет LET слева направо: каждая привязка вычисляется при всех более ранних привязках в области видимости, заталкивается, а последний аргумент вычисляется уже при полном наборе. Поэтому LET(a, 1, b, a+1, LET(c, b*2, c)) возвращает 4, и внутренний LET видит и a, и b. Когда внешний LET заканчивается, его привязки исчезают, а значит формула-сосед в следующей ячейке стартует с пустой области

Два правила формы проверяются до всякого выполнения. LET требует нечётного числа аргументов, минимум трёх, поэтому LET(a, 1) — это #VALUE!. И каждый слот имени обязан быть простым именем: LET(A1, 5, A1*2) отвергается, потому что A1 — ссылка на ячейку, тогда как короткие имена вроде x или ab, совпадающие по написанию с буквами столбцов, внутри LET и LAMBDA читаются как локальные имена. Привязка к диапазону сохраняет диапазон, поэтому LET(r, A1:A10, ROWS(r)) возвращает 10, а SUM(r) складывает ячейки

Те же значения-замыкания питают построенные под них вспомогательные функции: MAP, REDUCE, SCAN, MAKEARRAY, BYROW и BYCOL. MAP(A2:A6, LAMBDA(v, v*2)) разливает пять удвоенных значений, а REDUCE(0, A2:A6, LAMBDA(acc, v, acc+v)) сворачивает их в одну сумму. Захваченное окружение путешествует в эти вызовы точно так же, как при прямом вызове, поэтому привязанный через LET коэффициент виден внутри тела MAP

Запятые, точки с запятой и что пишет SaveAs

Парсер формул HotXLS принимает запятую и точку с запятой как разделители аргументов взаимозаменяемо. Текст формул приходит из мест, которые вы не контролируете: тикет поддержки, написанный на машине с немецкой локалью, документация с каноническими запятыми Excel, скрипт, генерирующий строки формул, старый код HotXLS, весь писавший через точки с запятой. SUM(A1,A2) и SUM(A1;A2) компилируются в одно и то же, и LAMBDA, записанная любым способом, тоже

Исключение — встроенные константы массивов. Внутри фигурных скобок точка с запятой сохраняет своё экселевское значение разделителя строк, поэтому {1,2;3,4} — массив два на два, а не список из четырёх аргументов. Если генерируете формулы с точками с запятой в роли разделителей аргументов, держите константы массивов в экселевском синтаксисе, и коллизий не будет никогда

Cell.Formula возвращает присвоенный вами текст. Когда SaveAs пишет XLSX-файл, точки с запятой в роли разделителей аргументов вне скобок записываются как запятые, потому что формат OOXML хранит формулы в экселевской запятой-синтаксисе, а функции, которые Excel хранит под префиксом _xlfn., получают этот префикс на выходе. Формула, загруженная из файла и не тронутая, записывается обратно из своего исходного XML-текста

Чтение именованных лямбд из клиентских книг

Книги, собранные в Excel, хранят имена LAMBDA так, как их пишет Excel: с префиксом _xlfn. на имени функции и часто _xlpm. на параметрах. HotXLS срезает эти префиксы при вычислении имени, но TXLSXDefinedName.Formula возвращает текст определения так, как его выдал файл. Проверка в виде Copy(Name.Formula, 1, 6) = 'LAMBDA' проходит для имён, созданных кодом, и пропускает каждое имя, вышедшее из Excel. Это расхождение легко не заметить: вычисленные значения верны в обоих случаях, и расходится только ваш собственный аудит-код

// IsLambdaDefinition — вспомогательная функция, которую вы добавляете сами, это не 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 — ваша собственная процедура логирования
  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;

Проверка области в том цикле нужна потому, что вызов по имени в HotXLS ищет вызываемое среди имён уровня книги. Держите именованные функции в области книги. Имя LAMBDA с областью листа всё ещё можно прочитать через FindByName(AName, ASheetIndex), но функция, которую вызывают с нескольких листов, принадлежит книге, а правила областей видимости за этим расписаны в статье определённые имена и межлистовые формулы

Правка лямбды потом стоит дёшево. Присвоение нового текста TXLSXDefinedName.Formula инвалидирует граф зависимостей, поэтому следующий Recalculate пересобирает его и перевычисляет ячейки, вызывающие функцию; механика — в статье инкрементальный пересчёт и граф зависимостей. Ячейка, чья формула вычисляется в голое замыкание, например =LAMBDA(x, x*2) без вызова, вообще не получает кэшированного значения: сохранять нечего — ни числа, ни текста; Excel показывает для той же ячейки #CALC!. Для отчётов, которые обязаны открываться в инструментах без поддержки LAMBDA, заморозьте результаты через Sheet.Cells.ConvertFormulasToValues после последнего Recalculate — это сохранит каждое кэшированное значение и выбросит текст формулы

LAMBDA, LET, вспомогательные функции и API определённых имён поставляются в одной нативной библиотеке для Delphi и C++Builder, читающей и пишущей XLS, XLSX и ODS без установленного Excel. Полный список функций формул — на странице компонента электронных таблиц HotXLS