如果一份含有隱藏列的活頁簿，SUBTOTAL(109, ...) 與 SUBTOTAL(9, ...) 回傳的是同一個數字，那麼這兩者之中必然有一個是錯的。HotXLS 是 Delphi 與 C++Builder 適用的原生 Excel 試算表元件，在 2.197.0 版之前正是這樣的行為，因為它的計算引擎完全沒有辦法向工作表詢問某一列是否被隱藏
這個症狀很少是以「公式代碼有問題」這種錯誤回報的形式出現，而是以數字對不上的形式出現：伺服器上的批次工作計算出一個總計，使用者在 Excel 中開啟同一份檔案並套用篩選，兩個數字之間的差異，剛好等於被篩選掉那些列的加總。沒有人會懷疑到聚合函式頭上，因為儲存格裡的公式字串，兩邊完全一模一樣。差異完全出在求值器被允許看到的東西不同
為何 SUBTOTAL 109 會把隱藏列也算進去？
因為在大多數引擎設計裡，負責求值公式的那一層，根本無從得知列的可見性狀態。HotXLS 正是教科書等級的案例：lxCalc.pas 裡的計算引擎，是透過單一一個 TXLSGetValue 回呼來取得儲存格值，這個回呼只會針對一組（工作表、列、欄）三元組回答一個值，別無其他。可見性是儲存在列記錄上的一項呈現屬性，而這個記錄的任何部分，都沒有被傳遞到呼叫鏈裡去。因此，這個引擎只有一條聚合路徑，而 SUBTOTAL 函式代碼表的兩個半部，都解析到同一條路徑上。這並不是一種捨入誤差等級的小缺陷：它正是這張表之所以存在後半部分的全部理由。以 ISO/IEC 29500-1 發行的 ECMA-376 第 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 參照是否解析到外部活頁簿的那個暫存欄位如出一轍。2.197.0 版又加了第二個這樣的欄位。引擎新增了一個回呼型別 TXLSIsRowHidden，宣告為一個接受（工作表索引、列）並回傳布林值的函式，儲存在 FIsRowHidden 中，再加上一個暫時性的 FIgnoreHiddenRows 旗標。當函式代碼落在 101 到 111 之間時，這個旗標會在 CalcSubtotalFunc 的進入點被啟用；對於選擇排除隱藏列的 AGGREGATE 選項代碼，則會在 CalcAggregateFunc 的進入點啟用。之後，每一個儲存格走訪迴圈，都會檢查這個旗標，一旦被設定，就跳過該列，每個迴圈只需要多加一行程式碼
// The shape repeated in all six cell-walk loops
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
// ... fold Cells[rr, cc] into the accumulator ...
end;
end;啟用旗標的程式碼裡，有兩個細節，承載著整套方案的正確性。旗標是先儲存、之後再還原，而不是單純設定後又清除，因為 SUBTOTAL 的引數，可能包含一個會執行自己求值過程的運算式，而此時外層聚合仍然在呼叫堆疊上，這段巢狀執行的工作，絕不能繼承或破壞外層的閘門狀態。而還原動作放在 finally 區塊裡，是因為 CalcSubtotalFunc 有好幾個因錯誤代碼而提前結束的出口；如果一個旗標在錯誤回傳之後仍然保持啟用狀態，就會悄悄破壞重新計算順序中，下一個完全不相關的公式
prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
FIgnoreHiddenRows := True;
try
// aggregate over Item.Child[2] .. Item.Child[ChildCount]
// every Exit path below is covered by the finally
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;Assigned 檢查，正是讓這項變更保持相容性的關鍵。HotXLS 為計算器的建構函式，擴充了一個預設為 nil 的第三個參數，所以任何用舊有雙引數呼叫方式來建構 TXLSCalculator 的程式碼，依然能編譯通過，也依然會得到原本包含隱藏列的舊行為。既有 API 的外形完全沒有任何改變
隱藏列這個位元資訊實際上是從哪裡來的？
來自工作表，經由兩種不同的來源，因為 HotXLS 內部帶有兩套活頁簿引擎。舊式 BIFF 這一側，是從 TXLSRowInfoList.GetHidden 取得答案，經由 TXLSWorkbook.GetRowHidden 抵達。OOXML 這一側，則是從 TXLSXWorksheet.GetRowHidden 取得答案，經由 TXLSXWorkbook.GetCalcRowHidden 抵達。兩者都在建構時接進計算器裡，與它們所對應的儲存格值回呼並列。列的編號慣例，正是這類橋接程式碼通常會出錯的地方，所以值得明確說清楚。計算器交給回呼的是從 0 起算的列號，與 TXLSGetValue 已經在使用的座標一致。而 XLSX 工作表的列隱藏對照表，卻是以從 1 起算的列號為鍵，正如 Excel 為列編號的方式，也正是公開的 RowHidden[ARow] 屬性所呈現的方式。因此 XLSX 橋接程式碼，在查找之前會先加一，而 BIFF 橋接程式碼則不會，因為 TXLSRowInfoList 本來就已經是從 0 起算的。兩座橋接程式碼，都把超出合法範圍的工作表索引或列號視為可見，所以一次超出邊界的查詢，會退化成舊有的「包含隱藏列」答案，而不是遺失資料
對已套用篩選的活頁簿而言，改變了什麼
這正是引發技術支援問題單的情況。在 HotXLS 中透過 ApplyAutoFilter 套用自動篩選，會評估欄位條件，並隱藏所有不符合的資料列，這恰好就是使用者點擊篩選下拉選單時 Excel 所做的事。在 v2.197.0 之前，這些隱藏列對使用者而言不可見，對計算引擎而言卻完全可見，所以伺服器端的 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; // hides the non-matching rows
Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
Book.Recalculate;
// The cell value now agrees with what Excel shows for the same filter,
// and VisibleRows tells you how many rows fed into it
Book.SaveAs('orders-filtered.xlsx');
finally
Book.Free;
end;
end;手動隱藏的運作方式也相同，因為 RowHidden[ARow] := True，正是篩選功能所寫入的同一種狀態。這種等價關係，在 Excel 中是刻意設計的，現在在 HotXLS 中也同樣成立。有一個後果，值得寫進你隨產生的活頁簿一併出貨的任何文件裡：以代碼 109 算出來的總計，是一個依視圖而定的數字，所以收件人只要清除篩選，這個數字就會跟著改變。當一份報表必須陳述一個固定數字、不受讀者對視圖做了什麼影響時，代碼 9 才是正確的選擇──而且一直都是。篩選、驗證與表格，在 資料驗證、自動篩選與表格一文 中有一併討論。由於隱藏列不會動到任何公式，所以它本身也不會弄髒相依圖，如果你仰賴 針對髒污子圖做增量重新計算 來讓大型活頁簿保持反應靈敏，這一點值得了解
AGGREGATE 選項代碼，以及一個仍未解決的限制
AGGREGATE 是帶有第二個政策引數的 SUBTOTAL，HotXLS 在 CalcAggregateFunc 中處理它。這個選項引數編碼了幾個獨立的開關：範圍內巢狀的 SUBTOTAL 與 AGGREGATE 呼叫是否要跳過、隱藏列上的值是否要跳過，以及錯誤值是否要被抑制而非往外傳播。HotXLS 會為選項代碼 2、3、6 與 7 啟用共用的隱藏列閘門，並為選項代碼 4 到 7 抑制錯誤值。函式代碼引數接著會選出聚合方式，做法與 SUBTOTAL 完全相同，包括把變異數、標準差與乘積路由到它們各自的歸約器。目前仍留有一個已記錄在案的缺口，在這裡先說清楚，總比在正式環境中才被發現來得好：與較低選項代碼相關聯的「忽略巢狀 SUBTOTAL」語意，在 HotXLS 中尚未實作。要偵測某個被參照範圍內存在巢狀 SUBTOTAL，需要標記求值器的遞迴狀態，讓內層聚合能向外層聚合表明自己的身分，這是一項比隱藏列閘門更大的變更。實務上這個暴露面很小，因為真實的活頁簿幾乎總是把 SUBTOTAL 公式放在其他 SUBTOTAL 公式所聚合的範圍之外。如果你的產生器確實會建構出重疊的聚合範圍，請不要仰賴這些較低的選項代碼來替你去除重複計算
隨這次修復一併出貨的引數個數守門機制
2.197.0 版還在同一個分派器裡，補上了一個驗證缺口，設計理由與促成暫存欄位那次修復相同：把檢查放在一個只需要寫一次的地方。大約 280 個內建函式主體，各自對照 Item.ChildCount 自行驗證自己的引數個數，這使得「引數過多」這種情況，完全沒有一致的邊界可言。像 =SIN(1,2) 這樣的呼叫，會走到一個只檢查第一個引數、忽略多餘引數的函式主體，並回傳一個看似合理的數字，而 Excel 在這裡回傳的卻是 #VALUE!。HotXLS 早就在其函式登錄表中，儲存了每個內建函式所宣告的引數個數，以 THashFunc.ArgsCnt 對外呈現，並用 -1 標記像 SUM、IF 或 CONCAT 這類可變引數個數的函式。2.197.0 版透過一個新的 TXLSFormula.FuncArgsCntByPtg 屬性，把這項資訊往前傳遞，並在主要分派器 GetValueItemFunc 的最前端，加上了一道閘門
lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
lProvidedArgs := Item.ChildCount - 1; // Child[0] is the function node
if lProvidedArgs > lDeclaredArgs then
begin
Result := lxErrorValue; // =SIN(1,2) now yields #VALUE!
Exit;
end;
end;這道守門機制只拒絕引數過多的情況，刻意對引數過少的情況隻字不提。省略一個結尾的選用引數，在 Excel 中對 VLOOKUP、SUBSTITUTE 以及一長串其他函式而言都是合法的，所以一個對稱的檢查，反而會為了抓出不正確的公式，而連帶破壞掉本來正確的公式。未知的識別碼，會被回報為可變引數個數，因而完全跳過這道閘門，這正是讓使用者自訂函式不受影響的關鍵；如果你有註冊自己的函式，公式引擎與自訂函式指南 中所描述的行為，不會受到這次變更影響。把「引數過少」的情況集中處理，是另一項獨立的工作，因為那 280 個函式主體，各自有自己的錯誤代碼語意，必須逐一檢視，而不能一概而論
本文所描述的計算引擎、兩套活頁簿外觀介面，以及供應資料給它們的自動篩選與列可見性 API，都是 HotXLS Delphi 試算表元件 的一部分，隨附 Delphi 與 C++Builder 的完整原始碼，且執行它的機器上不需要安裝 Excel