技術文章

在 Delphi 中的 HotXLS 增量公式重新計算

一欄 10,000 條公式,每條都讓上面那格的值成長——這大概是試算表能裝的最便宜模型。在一個「遇到被參照公式就當場算」的評估器裡,總數仍然正確,但算術帳單不對:最後一個儲存格拖著 9,999 個祖先一起求值,它上面那格拖 9,998 個,單單一輪完整計算,10,000 次乘法就要付出約 5,000 萬次求值。改動頂端一個成長率,這個評估器又得把整張帳單付一遍,因為它沒有記住哪個儲存格讀哪個

為什麼編輯一個儲存格,代價可能是整本活頁簿的一輪計算

有兩種偷懶做法會造成這種行為,多數手工打造的公式引擎都中了其中一種。遞迴式偷懶把每個被參照的公式都重新求值一遍、無視任何已快取的值,於是鏈變成平方級工作量,循環參照變成堆疊溢位。全輪式偷懶安全些:按某個固定順序把每條公式求值,直到什麼都不再變化為止。它正確,但成本跟著活頁簿大小走,而不是跟著編輯大小走

Excel 用它的計算鏈避開了這兩者:一個公式儲存格的順序,讓編輯只需標記一小組儲存格、只走過受影響的尾段。HotXLS 的 XLSX 引擎用同樣的想法,把一張明確的相依圖放在 TXLSXWorkbook.Recalculate 後面。第一次呼叫從每個工作表的每條公式建立圖形並全部求值;之後每次呼叫只求值「自上一輪以來值有變的儲存格」的下游公式,外加一小串每輪都必須執行的公式

Recalculate 如何把一次編輯變成一輪有序計算

每個公式儲存格成為一個節點,邊從公式讀取的每個公式儲存格指向讀取它的那條公式。SUM(Model!B2:B500) 這樣的參照會為矩形內的每個公式儲存格各產生一條邊;跨多個工作表的 3D 參照會對這串工作表的每一個進行比對。純常數根本不是節點;它們只在作為編輯起點時才有意義

對儲存格寫 Value,寫入當下幾乎什麼都不做:活頁簿把該儲存格的位址排進佇列,就這樣。Recalculate 開始時,參照涵蓋任何佇列位址的公式都被標為髒,標記沿著邊流向那條公式的所有相依項,再流向它們的相依項。從未呼叫過 Recalculate 的活頁簿不保留圖形,寫入只花一次 nil 檢查,所以只讀寫值的程式碼完全不必為這個功能付費

圖形的髒部分接著用 Kahn 演算法排序。讓這一輪變便宜的關鍵細節在於入度怎麼數:只有髒的先行項才算數。乾淨的先行項已持有有效的快取值,公式永遠不必等它。這一輪進行中,引用其他公式儲存格的公式直接讀那格的快取值而不是重新求值;排序保證快取已是最新。每條髒公式恰好求值一次

HotXLS Workbook.Recalculate 在 Delphi 執行一輪的流程圖:從髒旗標收集與拓撲排序,到求值與循環回報
每一趟都收集髒集合、做拓撲排序、對每個受影響的儲存格求值一次;關閉迭代計算時,參照循環會以 lxErrorRef 回來,而不是繞圈

下面的程式碼把開頭那條鏈建出來,並編輯它的唯一輸入。HotXLS 的工作表索引從 1 開始,所以 Inputs 是工作表 1、Model 是工作表 2;Cells 的列與欄同樣以 1 為基底

uses
  SysUtils, lxCalc, lxHandleX;   // lxOk 宣告於 lxCalc

var
  Book: TXLSXWorkbook;
  Inputs, Model: TXLSXWorksheet;
  Row: Integer;
  Last: Variant;
begin
  Book := TXLSXWorkbook.Create;
  try
    Inputs := Book.Sheets.Add('Inputs');   // Sheets[1]
    Model  := Book.Sheets.Add('Model');    // Sheets[2]

    Inputs.Cells[2, 2].Value := 0.05;      // Inputs!B2 裡的成長假設
    Model.Cells[2, 2].Formula := 'Inputs!B2*1000';
    for Row := 3 to 10000 do
      Model.Cells[Row, 2].Formula :=
        'B' + IntToStr(Row - 1) + '*(1+Inputs!$B$2)';

    if Book.Recalculate <> lxOk then      // 建立相依圖,求值 9,999 條公式
      Exit;

    Inputs.Cells[2, 2].Value := 0.07;      // 排入一個位址
    if Book.Recalculate = lxOk then        // 以 Inputs!B2 為輸入的公式重跑(這裡:全部 9,999 條)
      Last := Model.Cells[10000, 2].Value; // 快取結果,像任何值一樣讀取

    // 一次性讀取:RecalcCell 先跑 Recalculate,再回傳該儲存格
    // (工作表 2 = Model,列 10000,欄 2)
    Book.RecalcCell(2, 10000, 2, Last);
  finally
    Book.Free;
  end;
end;

在這個特定模型裡,每條公式都讀 Inputs!$B$2,所以第二輪還是碰了全部 9,999 條;圖形無法縮小一個真正影響全域的編輯。省下來的部分出現在多輸入的模型:情境迴圈只寫三四個儲存格,活頁簿其餘部分保留快取值

相依圖範例:HotXLS 中一個被編輯的輸入儲存格,只把下游的 Delphi 模型公式標為待重算
編輯 Inputs!B2 播下一個髒節點;髒沿著先行到相依的邊向下流,只有這個子圖重跑

寫值很便宜,寫公式會重建圖形

只有值編輯保持增量。任何可能改變節點或邊集合的操作,都把整張圖標為過期,下一次 Recalculate 從頭重建並求值每條公式。這涵蓋:指派 Formula、以 Null 或空值清掉公式儲存格、新增、搬移或刪除工作表、插入或刪除列與欄、編輯已定義名稱、更動 what-if 資料表。註冊或取消註冊使用者函式、或指派 OnUserFunction,會保留圖形但把每個節點標為髒

對報表產生器而言,實務上的形狀是這樣的迴圈:公式布局一次、呼叫 Recalculate,從此只碰值。每個客戶都重寫公式範本再重算的迴圈,每次迭代都拿到一次完整重建,而這正是相依圖存在的目的要避開的成本。真的需要改範本時,把所有公式編輯集中到一次 Recalculate 之前,讓重建只發生一次。情境迴圈本身隨後就只寫值

// 模型建好一次;已呼叫過 Recalculate
for I := 0 to High(Scenarios) do
begin
  Inputs.Cells[2, 2].Value := Scenarios[I].Growth;    // 只寫值
  Inputs.Cells[3, 2].Value := Scenarios[I].Discount;
  if Book.RecalcNeeded then          // 有儲存格排隊或為髒時為 True
    if Book.Recalculate <> lxOk then
      Break;
  Results[I] := Model.Cells[10000, 2].Value;
end;

// 只有還有未處理項目時,才讓 SaveAs 重算
Book.RecalcBeforeSave := xrmIfDirty;
Book.SaveAs('scenarios.xlsx');

RecalcBeforeSave 預設是 xrmManual,儲存期間從不重算。xrmIfDirty 是產生檔案該用的設定:只有圖形過期或有髒公式時才重算,所以迴圈裡已重算過的活頁簿原樣寫出,而忘了某次編輯的活頁簿也不會帶著過期快取值被存檔

哪些 Excel 函式每一輪都強制重算?

呼叫 NOW、TODAY、RAND、RANDBETWEEN、RANDARRAY、OFFSET、INDIRECT、CELL、INFO 或 GETPIVOTDATA 的公式在 HotXLS 屬於易變(volatile):每一輪開始時都被標為髒,無論上游有沒有變,它們的所有相依項也一樣。時鐘與隨機函式易變的理由很顯然;OFFSET 與 INDIRECT 易變,是因為它們讀哪些儲存格要到執行時期才從引數值算出來,靜態走訪公式無從得知該畫哪些邊

同樣的規則也適用於:圖形建構器無法釘到具體矩形的參照、透過其他名稱指向自己的已定義名稱、動態陣列溢位參照,以及 what-if 資料表。已定義名稱整體而言不是問題:建構器會把名稱展開成它指向的範圍,所以走活頁簿或工作表範圍名稱的公式拿得到真實的邊。這些名稱如何解析,已定義名稱與跨工作表公式一文有說明

保守的方向在這裡是對的。多餘的一次求值只花一點時間;缺一條邊,意味著一份看起來完工的報告裡藏著一個過期數字。把 OFFSET 與 INDIRECT 擋在大型模型的熱路徑之外,圈限在真正需要計算位址的那幾個儲存格,因為鏈頂附近一個易變儲存格,實務上會讓整條鏈都易變

透過 OnUserFunction 或 RegisterUserFunction 提供的函式則站在另一端。它們不是易變的,所以 handler 只在餵它引數的儲存格變化時才會再次被呼叫,與任何內建函式相同。從資料庫讀匯率的 handler 於是一路回傳上一次的答案。把匯率寫進輸入儲存格、把那格當引數傳進去:圖形就看到了相依性,匯率一變,公式就精準重跑。handler 引數怎麼送達,HotXLS 公式引擎中的自訂函式一文有解釋

循環參照:lxErrorRef 或迭代計算

預設 Iterate = False 時,排序發現循環,TXLSXWorkbook.Recalculate 就回傳 lxErrorRef。循環成員是 Kahn 演算法永遠放不出來的髒節點,它們下游的髒公式也因同樣原因被擋下。這些公式保留先前的快取值,髒集合裡的其他一切照順序求值。失敗的一輪還會把圖形標為過期,所以緊接的下一個呼叫會重建圖形、把每條公式再求值一次

case Book.Recalculate of
  lxOk:
    Book.SaveAs(OutputFile);
  lxErrorRef:
    // 循環與其下游公式仍保有舊的快取值;
    // LogWarning 是你自己的常式
    LogWarning('Circular reference in model, report not written');
else
  LogWarning('Recalculation did not complete');
end;

設 Iterate := True 就打開 Excel 同款的迭代計算。引擎反覆求值循環與被它擋住的相依項,最多 IterateCount 輪(預設 100),直到沒有任何值的變動達到 IterateDelta(預設 0.001)以上。空的循環成員從 0 起步。達到輪數上限時保留最後結果、仍然回傳 lxOk——這正是產生報告時應讓迭代保持關閉的原因:真實報告模型裡的循環幾乎總是錯誤,例如總計列被圈進自己的 SUM 範圍,而你要的是一個明確的錯誤碼。只為刻意以循環建模的模型打開它,例如以含利息的平均餘額計息

有一種看起來循環的情況不會被當成循環回報。VLOOKUP、HLOOKUP、LOOKUP、MATCH、XLOOKUP 與 XMATCH 的查找陣列引數,在範圍內儲存格變化時仍會讓公式變髒,但不計入循環偵測,所以寫在 B 欄的 VLOOKUP(A1,B:B,1) 正常求值。真正的循環,公式求值追蹤器會一步一步攤開那條折回自己的參照鏈

陣列公式與多執行緒計算輪

舊式 CSE 陣列公式以整個錨定矩形作為一個節點。根公式每輪求值一次,其矩陣寫進每個成員儲存格(純量結果廣播到整個矩形),而引用矩形內任何儲存格——不只是左上錨點——的公式都從根接一條邊。動態陣列公式處理得更謹慎:每輪都跑;當溢位改變大小,Recalculate 重建圖形並重複該輪,直到溢位形狀穩定。32 輪內仍不穩定,呼叫回傳 lxErrorCalc

RecalcThreads 設成 1 以上時,每個拓撲層的獨立公式會分散到工作執行緒求值,每條執行緒各有一個計算器。資料表仍序列執行,含動態陣列的活頁簿也留在序列路徑。調高這個值之前,任何使用者函式 handler 都必須是執行緒安全;會碰共用狀態的 handler,把 RecalcThreads 維持預設 0 是較安全的選擇

相依圖、易變函式處理與迭代計算都是 HotXLS Delphi Excel Component XLSX 引擎的一部分,公式計算器、已定義名稱與其周邊的讀寫管線也在其中。整天開著定價表或合併活頁簿、不斷餵新輸入的 Delphi 與 C++Builder 應用程式,每次編輯的工作量就跟著編輯真正觸及的儲存格走