儲存格裡是 =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 在這次呼叫中完全不會被問到
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 綁定,被擷取的綁定又會遮住呼叫端剛好用同名綁起來的任何東西。主體回傳或失敗時,作用域會被裁切回呼叫開始之處
在任何綁定安裝之前先求值引數,聽起來像個細節,直到你寫出 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 試算表元件頁面