В ячейке =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 того же имени для этого вызова не опрашивается никогда
Сторона 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, а захваченная привязка скрывает всё, что вызывающий случайно связал под тем же именем. Когда тело возвращает результат или падает, область срезается обратно до точки, где вызов начался
Вычислять аргументы до установки каких-либо привязок звучит как мелочь, пока вы не напишете 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