技術文章

HotXLS 公式閉包:Delphi 中的 LAMBDA 與 LET

儲存格裡是 =NetOf(B2, C2),B2 放 1250,C2 放 0.19,Excel 顯示 1012.5,而活頁簿裡既沒有 VBA 模組,也沒有增益集。NetOf 住在名稱管理員裡:它是一個已定義名稱,內容是 LAMBDA(amount, rate, amount*(1-rate))。把每個已定義名稱都當成範圍或常數的伺服器端計算器,在這裡找不到任何可以呼叫的東西,儲存格於是回報 #NAME?

要把這個儲存格算對,光有一個認得 LAMBDA 這個詞的剖析器還不夠。傳統試算表求值器認得五種值:數字、文字、布林值、錯誤,以及指向存放這些值之儲存格的參照。LAMBDA 加上了第六種:一種函式值,帶著它的參數名稱、主體,以及撰寫當下可見的名稱。LET 則是故事的另外一半,因為使用者正是在 LET 裡把這些函式綁到簡短的局部名稱上,然後在幾個 token 之後呼叫它們,而且常常深達三層

函式名稱解析的三個去處

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) 會在編譯期就失敗,而不是等到求值期

閉包記住什麼,參數為何贏過外層

在 HotXLS 裡求值一個 LAMBDA 運算式會產生閉包值:參數名稱、主體的副本、它撰寫所在的工作表,以及當下可見的每一個 LET 或 LAMBDA 綁定的快照。在上面第四個儲存格裡,bump 誕生時 base 已經綁到 100,所以閉包帶著 base = 100 走。當 bump(5) 在內層 LET 裡執行時,主體仍然看得到那個 100,即使內層 LET 對 base 這個名字一無所知

呼叫接著分三步進行。先在呼叫端的作用域裡求值引數運算式;然後推入被擷取的環境,再把參數推到它上面,求值主體。查尋自頂向下,所以名為 rate 的參數會遮住被擷取的同名 rate 綁定,被擷取的綁定又會遮住呼叫端剛好用同名綁起來的任何東西。主體回傳或失敗時,作用域會被裁切回呼叫開始之處

HotXLS 在 Delphi 的 LAMBDA 閉包呼叫圖解:閉包值帶著其參數、主體,以及從外層 LET 擷取的基礎綁定;呼叫時先推入擷取的環境再推入引數 v = 5,主體求值為 105,作用域堆疊在 finally 防護內截回進入時的標記
呼叫閉包時,擷取的環境先於引數綁定推入,這個順序正是參數能遮蔽外部名稱的原因

在任何綁定安裝之前先求值引數,聽起來像個細節,直到你寫出 LET(x, 10, f, LAMBDA(x, x+1), f(x*2))。引數 x*2 指的必須是外層的 x,得 20,結果是 21。如果參數先安裝,引數就會讀到它即將定義的那個參數。由此得出的規則正是 Excel 使用者期待的:具名函式算出什麼,取決於它的引數與它撰寫當下擷取到的東西,呼叫端的其他局部名稱永遠滲不進來

為什麼 f 持有數字時 f(21) 會失敗?

持有閉包以外東西的局部綁定,永遠不會被當成函式套用。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 記載的上限相同
  • 失控的遞迴會被擋下:呼叫自己的具名 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;

HotXLS 裡的 LET 綁定:巢狀與部分求值

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 綁進來的 rate 在 MAP 主體裡依然可見

逗號、分號,以及 SaveAs 寫出什麼

HotXLS 公式剖析器把逗號與分號當成可互換的引數分隔符。公式文字來自你掌控不了的地方:德語地區機器上寫的支援工單、用 Excel 標準逗號寫的文件、產生公式字串的指令碼、從頭到尾用分號的舊 HotXLS 程式碼。SUM(A1,A2) 與 SUM(A1;A2) 編譯成同一個東西,兩種寫法的 LAMBDA 也一樣

行內陣列常數是例外。大括號內的分號保留它在 Excel 裡的列分隔符身分,所以 {1,2;3,4} 是二乘二陣列,不是四引數清單。如果你用分號當引數分隔符來產生公式,讓陣列常數維持 Excel 語法,兩者就永遠不會撞在一起

Cell.Formula 回傳你指派的文字。SaveAs 寫 XLSX 檔時,大括號外當引數分隔符用的分號會寫成逗號,因為 OOXML 檔案格式以 Excel 的逗號語法儲存公式,而 Excel 以 _xlfn. 前綴儲存的函式,寫出時也會帶上該前綴。從檔案載入且未經更動的公式,會從它原始的 XML 文字寫回

讀取客戶活頁簿裡的具名 lambda

Excel 寫出來的活頁簿,照 Excel 的方式存放 LAMBDA 名稱:函式名稱帶 _xlfn. 前綴,參數則常帶 _xlpm.。HotXLS 求值該名稱時會剝掉這些前綴,但 TXLSXDefinedName.Formula 回傳的是檔案交來的原樣定義文字。寫成 Copy(Name.Formula, 1, 6) = 'LAMBDA' 的檢查,對你在程式碼裡建的名稱會通過,對每一個出自 Excel 的名稱都會漏掉。這種不一致很容易漏看,因為無論如何算出來的值都是對的,只有你自己的稽核程式碼在唱反調

// IsLambdaDefinition 是你自己加的輔助函式,不是 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 是你自己的記錄常式
  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) 讀到,但會被多個工作表呼叫的函式屬於活頁簿,背後的範圍規則在 已定義名稱與跨工作表公式 一文中有完整的鋪陳

之後再編輯 lambda 很便宜。給 TXLSXDefinedName.Formula 指派新文字會讓相依圖失效,下一次 Recalculate 會重建它並重新求值呼叫該函式的儲存格;機制細節在 增量重新計算與相依圖 一文。公式求值出裸閉包的儲存格,例如不帶呼叫的 =LAMBDA(x, x*2),根本不會有快取值,因為沒有數字或文字可存;同一個儲存格 Excel 顯示 #CALC!。對必須在不支援 LAMBDA 的工具裡開啟的報表,在最後一次 Recalculate 之後用 Sheet.Cells.ConvertFormulasToValues 把結果凍結,保留每個快取值、丟掉公式文字

LAMBDA、LET、輔助函式與已定義名稱 API 全都在同一套原生 Delphi 與 C++Builder 程式庫裡出貨,不必安裝 Excel 就能讀寫 XLS、XLSX 與 ODS。完整的公式函式清單在 HotXLS 試算表元件頁面