Delphi 中 HotXLS 的 SUBTOTAL 與 AGGREGATE 隱藏列處理

如果一份含有隱藏列的活頁簿，SUBTOTAL(109, ...) 與 SUBTOTAL(9, ...) 回傳同一個數字，那麼兩者之中必有一個是錯的。這是建造試算表計算引擎時最容易踩的陷阱之一，而 HotXLS——給 Delphi 與 C++Builder 的原生 Excel 試算表元件——讓它的計算引擎能向工作表詢問某列是否隱藏，從而避開這個陷阱

這個症狀很少以「公式代碼有問題」的提問形式出現，而是以數字對不上的形式出現：伺服器上的批次工作算出一個總計，使用者在 Excel 裡開啟同一份檔案並套用篩選，兩個數字的差剛好等於被篩掉那些列的加總。沒有人會懷疑到聚合函式頭上，因為儲存格裡的公式字串兩邊完全一樣。差異完全出在求值器被允許看見什麼

SUBTOTAL 109 為什麼可能把隱藏列也算進去？

因為在多數引擎設計裡，求值公式的那一層永遠無從得知列的可見性。最直覺的設計透過單一取值回呼拿儲存格值，例如 lxCalc.pas 裡的 TXLSGetValue 回呼——它只針對一個（工作表、列、欄）三元組回答一個值，其他什麼都不管。可見性是存在列記錄上的呈現屬性；如果沿呼叫鏈下去沒有別的東西跟著走，引擎最終就只有一條聚合路徑，SUBTOTAL 函式編號表的兩半都匯流到那裡。這不是捨入誤差等級的失誤：隱藏列政策正是這張表存在後半段的全部理由。以 ISO/IEC 29500-1 發佈的 ECMA-376 Part 1 在公式函式定義（§18.17.7）裡定義 SUBTOTAL，第一個引數同時選擇內層聚合與隱藏列政策。代碼 1 到 11 對應 AVERAGE、COUNT、COUNTA、MAX、MIN、PRODUCT、STDEV、STDEVP、SUM、VAR 與 VARP，並把手動隱藏列上的值算進去；代碼 101 到 111 選擇同樣十一種聚合、但排除它們。使用者敲 109 而不是 9，是對隱藏資料做出的刻意陳述；把這個區別壓扁的引擎，是無聲地推翻了那個陳述

這些函式代碼在引擎內部對應到什麼

HotXLS 在 CalcSubtotalFunc 裡解析 SUBTOTAL 的第一個引數：把代碼 101 到 111 正規化到與代碼 1 到 11 相同的內部函式識別碼，再按聚合本身分派。這個家族的大多數成員走增量式的 ExcelSum 累加器——處理 SUM、COUNT、COUNTA、MIN、MAX 與 AVERAGE 的那個。有五個走不了：STDEV、VAR、STDEVP、VARP 與 PRODUCT 需要對資料做一次閉式運算，所以 CalcSubtotalFunc 把內部代碼 12、46、193、194、183 導向獨立的歸約器 SubtotalReduceVariance。動手改任何東西之前，第一件該畫出來的就是這個分岔：兩條獨立的聚合路徑意味著兩個獨立的儲存格走訪迴圈，只給其中一個加隱藏列檢查，會產出最糟的結果——同一個範圍上 SUBTOTAL(109, ...) 尊重篩選，SUBTOTAL(107, ...) 卻不。把 AGGREGATE 算進來，HotXLS 一共六條，散布在範圍求值、純範圍蒐集與三個獨立歸約器裡

為何用一個暫存欄位、而不是六個新的函式簽章？

因為為了一個布林值，把新參數貫穿六個儲存格走訪函式、再加上呼叫它們的所有地方，是對一條熱路徑的大範圍改動。替代做法 HotXLS 有先例：計算器上的暫存欄位，精神與 GetRangeInfo 用來記錄「某個 3D 參照解析到外部活頁簿」的暫存欄位相同。隱藏列閘門是第二個：回呼型別 TXLSIsRowHidden，宣告成 (SheetIndex, row) 回傳 Boolean 的函式，存於 FIsRowHidden，外加暫存旗標 FIgnoreHiddenRows。函式碼落在 101 到 111 時，旗標在 CalcSubtotalFunc 進入處設起；選擇隱藏列排除的 AGGREGATE 選項碼，則在 CalcAggregateFunc 進入處設起。之後每個儲存格走訪迴圈都檢查它、設起時跳過該列，每處只花一行

// 六個儲存格走訪迴圈中重複出現的形狀
for sh := s1 to s2 do
  for rr := r1 to r2 do
  begin
    if FIgnoreHiddenRows and Assigned(FIsRowHidden) and FIsRowHidden(sh, rr) then
      Continue;
    for cc := c1 to c2 do
    begin
      // ... 把 Cells[rr, cc] 摺入累加器 ...
    end;
  end;

啟用旗標的程式碼裡，有兩個細節，承載著整套方案的正確性。旗標是先儲存、之後再還原，而不是單純設定後又清除，因為 SUBTOTAL 的引數，可能包含一個會執行自己求值過程的運算式，而此時外層聚合仍然在呼叫堆疊上，這段巢狀執行的工作，絕不能繼承或破壞外層的閘門狀態。而還原動作放在 finally 區塊裡，是因為 CalcSubtotalFunc 有好幾個因錯誤代碼而提前結束的出口；如果一個旗標在錯誤回傳之後仍然保持啟用狀態，就會悄悄破壞重新計算順序中，下一個完全不相關的公式

prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
  FIgnoreHiddenRows := True;
try
  // 彙總 Item.Child[2] .. Item.Child[ChildCount]
  // 下方每一條 Exit 路徑都由 finally 所涵蓋
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
end;

Assigned 檢查正是計算器 API 保持相容的關鍵。TXLSCalculator 建構子的第三個參數預設為 nil，所以用雙引數建計算器的程式碼照原樣編譯，得到的是「含隱藏列」的行為

隱藏列這個位元資訊實際上是從哪裡來的？

來自工作表，經由兩種不同的來源，因為 HotXLS 內部帶有兩套活頁簿引擎。舊式 BIFF 這一側，從 TXLSRowInfoList.GetHidden 取得答案，經由 TXLSWorkbook.GetRowHidden 抵達。OOXML 這一側，從 TXLSXWorksheet.GetRowHidden 取得答案，經由 TXLSXWorkbook.GetCalcRowHidden 抵達。兩者在建構時就接進計算器，與它們所對映的儲存格取值回呼並排。列號慣例正是這類橋接最容易出錯的地方，所以值得明說。計算器交給回呼的是 0 基底列號，與 TXLSGetValue 已在用的座標一致。XLSX 工作表的列隱藏映射以 1 基底列號為鍵——Excel 給列編號的方式，也是公開屬性 RowHidden[ARow] 暴露的方式。所以 XLSX 橋接在查表前加一，BIFF 橋接不加，因為 TXLSRowInfoList 本來就是 0 基底。兩座橋接都把超出有效範圍的工作表索引或列視為可見，越界查詢於是退化成「含隱藏列」的答案，而不是丟資料

對已套用篩選的活頁簿而言，改變了什麼

隱藏列政策最重要的正是這個場景。在 HotXLS 裡透過 ApplyAutoFilter 套用 AutoFilter 會評估欄位條件、隱藏每個不符合的資料列——這正是使用者在 Excel 點篩選下拉選單時發生的事。那些列對使用者不可見；由於計算引擎查詢的是同一份列狀態，伺服器端的 SUBTOTAL(109, ...) 回報的就是篩選後的總計，而不是未篩選的

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  VisibleRows: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[0];

    Sheet.SetAutoFilter('A1:E500');
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
    VisibleRows := Sheet.ApplyAutoFilter;   // 隱藏不符合的列

    Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
    Book.Recalculate;
    // 儲存格值與 Excel 對同一份篩選顯示的總計一致，
    // VisibleRows 告訴你有多少列參與了計算

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

手動隱藏的運作方式相同，因為 RowHidden[ARow] := True 正是篩選寫入的同一種狀態。這個等價在 Excel 是刻意設計，在 HotXLS 也成立。有一個後果值得寫進隨產生活頁簿出貨的任何文件裡：用代碼 109 算出的總計是個依檢視而變的數字，收件人一清篩選它就變。當報告必須給出不隨讀者操作檢視而變的固定數字時，代碼 9 才是正確選擇，而且一直都是。篩選、資料驗證與表格在資料驗證、AutoFilter 與表格一文中一併說明。隱藏列不碰任何公式，所以它自己也不會弄髒依賴圖——如果你靠髒子圖上的增量重算讓大型活頁簿保持回應性，這一點值得知道

AGGREGATE 選項代碼

AGGREGATE 是帶第二個政策引數的 SUBTOTAL，HotXLS 在 CalcAggregateFunc 處理它。選項引數編碼了幾個獨立開關：範圍內巢狀的 SUBTOTAL 與 AGGREGATE 呼叫是否跳過、隱藏列上的值是否跳過、錯誤值是抑制還是往外傳。HotXLS 為選項碼 2、3、6、7 設起共用的隱藏列閘門，為選項碼 4 到 7 抑制錯誤值。函式編號引數之後選擇聚合的方式與 SUBTOTAL 完全一致，包括變異數、標準差與積走各自的歸約器。真實活頁簿幾乎總是把 SUBTOTAL 公式放在其他 SUBTOTAL 所聚合範圍之外；如果你的產生器造出互相重疊的聚合範圍，請把內層總計排除在外層範圍之外，而不是指望選項碼替你去重

同一個分派器裡的引數個數守門

同一個分派器還帶著引數個數檢查，設計理由與暫存欄位背後的那個相同：把檢查放在只需要寫一次的地方。大約 280 個內建函式主體，若讓每個自行對照 Item.ChildCount 驗證自己的引數個數，「引數過多」這種情況就沒有一致的邊界，而這個陷阱又容易漏看：=SIN(1,2) 這種呼叫會抵達一個只看第一個引數、無視多餘引數的函式主體，回傳一個看似合理的數字，而 Excel 回傳的是 #VALUE!。HotXLS 把每個內建的宣告引數個數存在函式註冊表裡，以 THashFunc.ArgsCnt 暴露，-1 標記 SUM、IF、CONCAT 這類可變引數函式；它經由 TXLSFormula.FuncArgsCntByPtg 屬性傳遞，並在主分派器 GetValueItemFunc 頂端的一道閘門裡檢查

lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
  lProvidedArgs := Item.ChildCount - 1;   // Child[0] 是函式節點
  if lProvidedArgs > lDeclaredArgs then
  begin
    Result := lxErrorValue;               // =SIN(1,2) 得到 #VALUE!
    Exit;
  end;
end;

這道閘門只拒絕引數過多，刻意對引數過少隻字不提。省略結尾的選用引數，在 Excel 裡對 VLOOKUP、SUBSTITUTE 與一長串函式都合法，對稱的檢查會為了抓錯公式而弄壞正確公式。未知識別碼回報為可變引數個數、完全跳過閘門，這正是自訂函式不受它影響的原因；如果你註冊自己的函式，公式引擎與自訂函式指南所述的行為不受影響。引數過少的情況仍由各函式主體自行處理，因為每個主體都有自己的錯誤碼語意

本文所描述的計算引擎、兩套活頁簿外觀介面，以及供應資料給它們的自動篩選與列可見性 API，都是 HotXLS Delphi 試算表元件 的一部分，隨附 Delphi 與 C++Builder 的完整原始碼，且執行它的機器上不需要安裝 Excel