HotXLS for Delphi 的 AGGREGATE 選項與巢狀閘門

四個儲存格就足以讓自寫的求值器把 =AGGREGATE(9,7,A1:A4) 算成 20，而 Excel 顯示 40。A1 放 10，A2 放 20 但整列隱藏，A3 放 =1/0，A4 放 =SUBTOTAL(9,A1:A2)，這個群組小計值為 30。選項 7 代表略過隱藏列、略過錯誤值、並把巢狀小計當成普通數值，所以 Excel 加總 10 和 30。會冒出 20，是因為外層呼叫的「略過隱藏列」設定在 A4 計算自身值時還開著，A4 因此縮水成 10。要得到 40 得做到兩件事：把三個選項位元在正確的位置解碼，並確保被參照的公式永遠看不到參照它的那條公式的選項

AGGREGATE 的 options 0 到 7 到底選了什麼

options 引數是一個三位元矩陣，三個位元彼此獨立。bit 0（值 1）略過隱藏列，bit 1（值 2）略過錯誤值，bit 2（值 4）則停止略過自身公式為 SUBTOTAL 或 AGGREGATE 的儲存格，因為對低位碼來說，略過它們才是預設行為。這段話有兩個地方很容易讀反。隱藏列位元是最低位元，所以 AGGREGATE(9,1,...) 是套用篩選後的總和，AGGREGATE(9,2,...) 才是容忍錯誤的那個。而巢狀策略的方向又跟前兩個位元相反：只有碼 4 到 7 會把巢狀聚合當成普通數值。ECMA-376 Part 1 §18.17.7 定義的 SUBTOTAL，在碼 1–11 與 101–111 之間有同樣的「含或不含隱藏列」之分；儲存在 OOXML 裡帶 _xlfn. 前綴的 AGGREGATE，把這個區分折進了 options 引數，而 Microsoft 為這個函數公布的表格，就是求值引擎必須滿足的契約

選項	隱藏列	錯誤值	巢狀 SUBTOTAL / AGGREGATE
0	含入	往外傳遞	忽略
1	忽略	往外傳遞	忽略
2	含入	忽略	忽略
3	忽略	忽略	忽略
4	含入	往外傳遞	含入
5	忽略	往外傳遞	含入
6	含入	忽略	含入
7	忽略	忽略	含入

0 到 7 以外的任何碼都會被以 #VALUE! 拒絕，Excel 也是這麼做的

為什麼解碼出錯的總是單一位元的碼

照表格的「轉述」而不是照表格本身寫出來的解碼，往往長成這樣：碼 4 以上忽略錯誤、碼 2、3、6、7 略過隱藏列，巢狀策略整個不見。這種版本在正式環境裡可以活很久。多數人實際會用到的組合——隱藏列加錯誤——在兩種讀法下都落在碼 3 和 7，所以互換只會在單一位元的碼上現形。這時 AGGREGATE(9,1,A1:A4) 會回傳未過濾的總和，AGGREGATE(9,2,...) 則一邊略過隱藏列、一邊照樣讓 #DIV/0! 傳出去。實際的活頁簿很少單獨用碼 1 和 2，這也正是為什麼對照官方表格做審查，比等客戶檔案上門更能抓到問題

HotXLS 把 options 解碼成三組集合歸屬測試，表格每一欄一組，在 AGGREGATE 呼叫開始時一口氣指定三個結果，結束時還原成原本生效的設定。這個指定是刻意無條件的。如果寫成「選項要求時才把旗標打開」，那麼坐落在 SUBTOTAL(109, ...) 裡、碼為 4 的 AGGREGATE 會繼承外層的隱藏列設定，而不是把它清掉。下面的示意碼把同一條規則呈現給要自己寫或自己審求值器的人

// 給自己求值器的示意解碼：TAggPolicy 與
// DecodeAggregateOptions 是你自己定的名稱，不是 HotXLS API
type
  TAggPolicy = record
    IgnoreHidden, IgnoreErrors, SkipNested: Boolean;
  end;

function DecodeAggregateOptions(OptCode: Integer; out P: TAggPolicy): Boolean;
begin
  Result := (OptCode >= 0) and (OptCode <= 7);   // 其他值一律 #VALUE!
  if not Result then
    Exit;
  P.IgnoreHidden := OptCode in [1, 3, 5, 7];       // bit 0
  P.IgnoreErrors := OptCode in [2, 3, 6, 7];       // bit 1
  P.SkipNested   := OptCode in [0, 1, 2, 3];       // bit 2 為 0
end;

同一套隱藏列規則在 SUBTOTAL 這邊的行為，包括篩選隱藏列與手動隱藏列的比較，請見SUBTOTAL 與 AGGREGATE 隱藏列那篇文章

為什麼外層 AGGREGATE 會滲進它參照的公式

因為要把「略過隱藏列」傳給六、七個走訪儲存格的迴圈，最自然的做法就是在計算器物件上放一個欄位，而重新計算的一整輪裡，所有公式都由同一個計算器求值。只要欄位設定期間執行的一切都屬於設定它的那次聚合，這個設計就站得住腳。它失效的時機只有一個特定瞬間：讀取儲存格。當走訪器要求某個儲存格的值，而那個儲存格放的是還沒有快取結果的公式，活頁簿就會當場在同一個計算器上編譯並求值這條公式——此時外層設定原封不動地掛在那裡

把開頭那個範例放進這樣的引擎走一遍。碼 7 的 AGGREGATE 打開略過隱藏列，從 A1 開始。A1 給 10，A2 隱藏被跳過，A3 是錯誤被跳過。然後走訪器抵達 A4，而 A4 還沒算過。A4 的 SUBTOTAL(9,A1:A2) 開始執行，一個只對碼 101 到 111 打開略過隱藏列的 SUBTOTAL 實作（這種寫法完全合理）對碼 9 從不把它關掉。它發現設定已經是開的，跳過 A2，回傳 10。外層總和變成 10 + 10 = 20。A4 的公式裡隻字未提隱藏列

巢狀聚合設定兩個方向都會滲漏。碼 0 到 3 時它是開的，一個尊重這項設定的通用範圍走訪器，會讓 =SUM(B1:B3) 這類前置公式在 B2 恰好放著 SUBTOTAL 時把 B2 丟掉。反過來，一個結束時把巢狀略過關掉、而不是還原先前狀態的 SUBTOTAL，會讓外層 AGGREGATE 的策略在其餘走訪中失效。任何用暫時性欄位把上下文帶進遞迴走訪的求值器都有這種形狀的問題：欄位對設定它的那一層是正確的，對每一層繼承它的框架都是錯的

把策略切在讀值邊界上

HotXLS 在 AGGREGATE 讀取任何不是自己算出來的值的位置周圍都放上邊界。讀值前，兩個設定先存好並清空；讀值後，在 finally 區塊裡還原。外層聚合仍會對剛取得的儲存格套用自己的策略，因為隱藏列與巢狀儲存格的測試發生在走訪器裡、包在讀值外圍。被參照的公式則在完全沒有策略的狀態下執行，這與 Excel 一致。三個走訪器家族都經過這道邊界：SUM 家族（SUM、AVERAGE、COUNT、COUNTA、MAX、MIN）、封閉形式的化簡（PRODUCT、STDEV、VAR），以及帶 k 引數的形式（MEDIAN、LARGE、SMALL、各種百分位數與四分位數）

// 給自己求值器的示意走訪器。FPolicy、FetchCell、
// IsRowHidden 與 IsSubtotalCell 都是你自己定義的成員
function TMyEvaluator.FetchForAggregate(Sheet, Row, Col: Integer;
  var V: Variant): Integer;
var
  Saved: TAggPolicy;
begin
  Saved := FPolicy;
  FPolicy := Default(TAggPolicy);     // 被參照的公式擁有自己的策略
  try
    Result := FetchCell(Sheet, Row, Col, V);
  finally
    FPolicy := Saved;
  end;
end;

// 範圍走訪內部：外層策略包在讀值外圍套用
for R := R1 to R2 do
begin
  if FPolicy.IgnoreHidden and IsRowHidden(Sheet, R) then
    Continue;
  for C := C1 to C2 do
  begin
    if FPolicy.SkipNested and IsSubtotalCell(Sheet, R, C) then
      Continue;
    Code := FetchForAggregate(Sheet, R, C, V);
    // ... 對 Code / V 套用錯誤策略，然後累加 ...
  end;
end;

不是純範圍的引數，光清旗標還不夠。A1:A4/(B1:B4-20) 這種引數是計算出來的陣列，它的元素形狀必須保留下來，選項 6 才能只丟掉除以零的那些元素。HotXLS 讓每個被參照的範圍通過同一道邊界變成二維陣列，把失敗的儲存格存成錯誤元素而不是中途放棄，並逐元素套用算術與比較運算子。跨多張工作表的範圍，或角落顛倒的範圍，回傳 #VALUE!。超出 TXLSCalculator.FormulaArrayMemoryLimit（預設 64 MB）的範圍回傳資源上限類結果，而這類結果被刻意排除在選項 2、3、6、7 可以略過的錯誤之外。只因為公式要求忽略 #N/A 就把引擎自己的記憶體不足訊號吞掉，會得出一個看起來很有信心、其實錯誤的數字，所以寧可讓公式失敗

當 AGGREGATE 不忽略錯誤時，它回傳哪一個錯誤

回傳遇到的第一個，原樣不動。把所有錯誤壓成 #VALUE! 是最容易的捷徑，但它會讓蓋在 #DIV/0! 儲存格上的 AGGREGATE(9,4,A1:A3) 與 Excel 相牴觸——Excel 會讓 #DIV/0! 原樣通過。HotXLS 把真正的錯誤變體和七種錯誤字串（#NULL!、#DIV/0!、#VALUE!、#REF!、#NAME?、#NUM!、#N/A）都對映回各自的錯誤碼，每個走訪器回傳它記到的第一個碼。公式從未計算過的儲存格，其錯誤會以讀值回傳碼的形式送出，而不是以快取值的形式；傳播方式與快取值完全相同

兩個計數函數跟隨的是 SUBTOTAL 而非 SUM 的行為。函數 2 是 COUNT：不論選項碼為何，錯誤儲存格既不計入也不傳播，因為 COUNT 只數數字。函數 3 是 COUNTA：錯誤儲存格是非空值，計為 1，除非選項碼要求略過錯誤。這種不對稱正是 Excel 在 AGGREGATE 之外對待 COUNT 和 COUNTA 的方式，一條「遇錯誤就傳播」的籠統規則會把兩個都弄錯

在一張工作表上檢查全部八個碼

開頭那四個儲存格，構成涵蓋每個碼的精簡檢查。對 0 到 7 的每個碼，把 SUM 形式和 MEDIAN 形式對 A1:A4 各求一次值，並與手算的結果比對。碼 0、1、4、5 必須把 A3 的 #DIV/0! 傳出去，因為它們都不略過錯誤。碼 2 的 SUM 給 30、MEDIAN 給 15，來自 10 和 20，巢狀的 A4 被跳過。碼 3 給 10 和 10。碼 6 給 60 和 20，因為 A4 裡的 30 這次算數了。碼 7 給 40 和 20，而沒有讀值邊界的引擎正是會把這個碼回報成 20。這項檢查只有在 AGGREGATE 執行時 A4 尚未快取才有意義，所以每個案例都要跑兩次：一次先算好前置公式，一次不先算

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 10;
    Sheet.Cells[2, 1].Value := 20;
    Sheet.Cells[3, 1].Formula := '=1/0';
    Sheet.Cells[4, 1].Formula := '=SUBTOTAL(9,A1:A2)';    // 群組小計 = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  跳過隱藏，錯誤照傳
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       隱藏、錯誤與巢狀全部跳過
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       只跳過錯誤
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       出現 20 代表有設定滲進了 A4
    Sheet.Cells[10, 1].Formula := '=AGGREGATE(12,6,A1:A4)'; // 20     10、20、30 的 MEDIAN
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

把存好的檔案用 Excel 打開、按 Ctrl+Alt+F9，就得到同一批儲存格的第二意見。想掃得更廣，就把 1 到 19 的每個函數編號對全部八個碼都跑一輪

自己打造計算器時怎麼接回呼

這兩個設定都依賴兩個回呼。TXLSCalculator.Create 在取值器之後接受一個 TXLSIsRowHidden 和一個 TXLSIsSubtotalCell，兩者預設都是 nil。缺了任何一個，表格對應的那一欄就永遠不生效，於是每個選項碼都會把隱藏列、巢狀聚合或兩者全部算進去。XLSX 與傳統 XLS 活頁簿物件會在建構子裡傳入自己的實作；在自己的資料來源上手動建立 TXLSCalculator 的程式碼，則必須兩個都提供。活頁簿以 SUBTOTAL(、AGGREGATE( 或 _xlfn.AGGREGATE( 開頭的公式辨識巢狀聚合，有無前置等號皆可，自訂資料來源應採用同一條規則

// TMySheetSource 與 FormulaAt 是你自己的型別；下面三個方法
// 簽章分別對應 TXLSGetValue、TXLSIsRowHidden 與 TXLSIsSubtotalCell
// （SheetIndex 為 1 起始，Row 與 Col 為 0 起始，與活頁簿傳入的方式一致）
function TMySheetSource.IsSubtotalCell(SheetIndex, Row, Col: Integer): Boolean;
var
  F: string;
begin
  F := FormulaAt(SheetIndex, Row, Col);
  if (F <> '') and (F[1] = '=') then
    Delete(F, 1, 1);
  Result := SameText(Copy(F, 1, 9), 'SUBTOTAL(') or
            SameText(Copy(F, 1, 10), 'AGGREGATE(') or
            SameText(Copy(F, 1, 16), '_xlfn.AGGREGATE(');
end;

// Extern 是你的宿主程式早已維護的工作表名稱表
Calc := TXLSCalculator.Create(Extern, Source.GetCellValue,
  Source.IsRowHidden, Source.IsSubtotalCell);

前綴規則對產生的活頁簿有一個後果。寫出 =ROUND(SUBTOTAL(9,B2:B9),2) 的產生器，會造出前綴測試認不出的儲存格，於是碼 0 到 3 會把它算進去，這一組就在總計裡出現兩次。讓 SUBTOTAL 或 AGGREGATE 呼叫保持在最外層，把捨入交給數字格式。完整的 Recalculate 會先算前置再算相依，聚合通常讀到的是快取值，沿相依圖做漸進重算時也維持同樣的順序。當總數看起來不對、公式文字卻看不出問題時，逐步追蹤求值過程就能分辨前置公式是在走訪中被即時計算，還是回呼根本沒接上

這裡描述的選項解碼器、讀值邊界與回呼掛鉤，都隨 HotXLS Delphi 試算表元件提供，它能從 Delphi 與 C++Builder 程式碼讀寫並重算 XLS、XLSX 與 ODS 活頁簿，機器上不需要安裝 Excel