HotXLS Delphi 的比較鏈、空白儲存格與 SUMIF 語義

HotXLS Delphi Component 對 =1<2<3 求值得到 FALSE,和 Excel 16 給的答案一樣，因為從 v2.384.3 起，它的公式解析器把比較運算子從左到右摺疊:1<2 得 TRUE,而 TRUE<3 是 FALSE,因為布林排在所有數字之上。同一版還讓空白運算元同時等於 0 和 "",並讓 SUMIF 能把單一儲存格的加總範圍撐成條件範圍的形狀。每一條聽起來都像冷知識，直到 Delphi 裡算出來的活頁簿和 Excel 開啟的同一份活頁簿說了不同的話

不一致通常始於一條憑直覺寫下的公式。有人輸入 =0<B2<100 想檢查數量在不在範圍內,Excel 對每一列都悄悄回答 FALSE,工作表就帶著這個 bug 出貨了。計算引擎沒有資格替使用者修正意圖；它的職責是產出 Excel 會產出的值，讓 HotXLS 寫進檔案的快取結果與 Excel 重算後顯示的一致。v2.384.3 之前,HotXLS 對這種範圍檢查每列都回答 TRUE,往反方向錯，伺服器上產生的報表就和桌面上開啟的同一份報表互相矛盾

為什麼 =1<2<3 在 Excel 中回傳 FALSE?

Excel 回傳 FALSE,是因為它把比較鏈讀成 (1<2)<3,內層的 TRUE 在與數字 3 的型別排序較勁中落敗。舊的 HotXLS 解析器把同一串文字讀成 1<(2<3):lxFormula.pas 裡的 TXLSSyntax.Parse_expr 解析一個運算元、看到比較 token,就為右側遞迴進 Parse_expr,這讓運算子變成右結合。得到的是 1<TRUE,而數字排在布林之下，結果就是 TRUE。這個錯誤是對稱的:=3>2>1 在 Excel 是 TRUE、在 HotXLS 曾是 FALSE,=1=1=TRUE 在 Excel 是 TRUE、修正前是 FALSE。迴歸測試 CalculateFormula_ComparisonChainsFoldLeftToRight 拿七條這類公式對著 Excel 16 的回傳值釘死，並讓每一條都跑過兩種引擎架構——classic 的 TXLSWorkbook 與 XLSX 原生的 TXLSXWorkbook,用的正是HotXLS 公式引擎總覽介紹的 Calculate 方法

const
  Formulas: array [0..6] of string = ('=1<2<3', '=3>2>1', '=(1<2)<3',
    '=1<(2<3)', '=1=1=TRUE', '=3>2>1=TRUE', '=1<2<3=FALSE');
  // Excel 16 的回傳:FALSE、TRUE、FALSE、TRUE、TRUE、TRUE、TRUE
var
  Classic: IXLSWorkbook;
  Xlsx: TXLSXWorkbook;
  i: Integer;
begin
  Classic := TXLSWorkbook.Create;
  Xlsx := TXLSXWorkbook.Create;
  try
    // TXLSXWorkbook.Calculate 對作用中工作表求值,
    // 活頁簿完全沒有工作表時回傳 Null
    Xlsx.Sheets.Add('Data');
    for i := 0 to High(Formulas) do
      Writeln(Formulas[i], '  classic=', VarToStr(Classic.Calculate(Formulas[i])),
        '  xlsx=', VarToStr(Xlsx.Calculate(Formulas[i])));
  finally
    Xlsx.Free;
  end;
end;

這個修正把 Parse_expr 改成一個迴圈，形狀與 Parse_expr1 已經用在 +、- 和 & 上的那個相同。它先用 Parse_expr1 解析第一個運算元，只要下一個 token 是 =、<>、<、>、<= 或 >=,就建立一個比較節點、把累積的左結果掛成第一個子節點、用 Parse_expr1 而非 Parse_expr 解析下一個運算元，再讓新節點成為下一輪的左結果。把遞迴改迭代有兩個容易出錯的細節，維護者筆記裡都有：累積節點必須按這個順序交棒(lChild := Item; Item := nil),而且錯誤路徑要在釋放半成品節點後 Exit,不能跌出迴圈回傳一棵懸空的樹

比較時 HotXLS 怎麼排數字、文字與布林的高低?

HotXLS 排混合型別的方式和 Excel 相同：所有數字小於所有文字，所有文字小於所有布林。lxCalc.pas 裡的 TXLSCalculator.CompareVariants 用 GetRetValueType 把兩個運算元分類到 TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue) 這個列舉，兩邊類別不同時就直接比較序數，所以這個 enum 的宣告順序就是跨型別規則。同一類別內的比較是自然比較，文字有一個 Excel 特有的轉折：兩個字串都先過 lxUpperCase,所以 ="abc"="ABC" 是 TRUE。正因為有這套排序，比較鏈的結果才無法脫離它去推理。TRUE<3 不是把 TRUE 轉型成 1,而是布林跟數字比，布林贏。日期在引擎眼裡是序號(varDate 歸類為 xlNumberValue),所以日期永遠低於任何文字，包括碰巧長得像日期的文字

空白儲存格在比較中等於什麼?

空白儲存格當比較運算元用時：對面是數字，它等於 0;對面是文字，它等於 "";從 v2.384.53 起，對面是邏輯值，它等於 FALSE。所以 A1 空著時,=A1=0、=A1="" 與 =A1=FALSE 全是 TRUE。TXLSCalculator.CompareVarValues 服務全部六個比較運算子，它在呼叫 CompareVariants 之前先替換空白：恰好一邊是 Null 時，夥伴是字串就換成 WideString(''),夥伴是布林就換成 False,其餘換成 0。兩邊都空白時仍然直接比、彼此相等。算術路徑一直以來都把空白變成 0,這就是 =A1+1 得 1 的原因，但 CompareVariants 過去讓 Null 自成一個最低排序，低於所有數字，而比較運算子直接用了那個排序

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['B1', 'B1'].Value := 5;      // A1 刻意留空

  Writeln(VarToStr(Wb.Calculate('=A1=0')));    // True
  Writeln(VarToStr(Wb.Calculate('=A1=""')));   // True
  Writeln(VarToStr(Wb.Calculate('=A1<B1')));   // True:空白當 0 來比
  Writeln(VarToStr(Wb.Calculate('=A1<0')));    // False;v2.384.3 之前是 True
end;

實務上真正咬人的是最後一列。舊排序下，空白比所有數字都小，連負數也照小，於是 =IF(A1<0,"overdrawn","ok") 把每個空白的餘額儲存格都標成透支，而任何使用者都會說是零的儲存格,=A1=0 卻是 FALSE。v2.384.3 之後還剩一個邊界：替換只在 0 和空字串之間二選一，空白跟布林比就被換成 0,而 0 排在 TRUE 和 FALSE 之下，空 A1 上的 =A1=FALSE 因此算出 FALSE。從 HotXLS 2.384.53 起,XLS 與 XLSX 兩個引擎都照 Excel 的做法，空白對邏輯值一律視為 FALSE:A1 空著時,=A1=FALSE 與 =A1<TRUE 回傳 TRUE,=A1=TRUE 回傳 FALSE。這也意味著比較無法區分空白與 FALSE,Excel 和 HotXLS 都一樣；工作表需要這個區分時，用 ISBLANK 或 =A1="" 來測

為什麼 SUMIF 的加總範圍只有一格時回傳 0?

SUMIF 回傳 0,是因為 HotXLS 把迭代夾在兩個範圍中較小的那個，而 Excel 保留條件範圍的形狀、只拿加總範圍的左上角儲存格。所以在 Excel 裡 =SUMIF(A1:A10,">5",B1) 意味著 B1:B10,許多手工搭的範本就靠這個便利。共用的工作函式 TXLSCalculator.GetValueItemRange2 過去把列數與欄數縮到值範圍的大小，例子就退化成拿 A1 對 B1 的單次測試。v2.384.3 拿掉了這個夾限：迴圈現在沿著條件範圍走，並按相同位移從加總範圍的左上角取值。因為 CalcSumIF 與 CalcAverageIF 都呼叫這個工作函式,AVERAGEIF 得到同樣的放大，而加總範圍大於條件範圍時也基於同一理由被裁成條件形狀。中間的條件引數是值類引數，外側兩個是參照類,隱含交集與引數類別一文談的就是這個區分

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    for Row := 1 to 10 do
    begin
      Sheet.Cells[Row, 1].Value := Row;          // 條件欄:1..10
      Sheet.Cells[Row, 2].Value := Row * 100;    // 金額:100..1000
    end;
    Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)';     // 單一儲存格加總範圍
    Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // 明確的加總範圍
    if Book.Recalculate = lxOk then
      // D1 與 D2 都是 4000(600+700+800+900+1000);v2.384.3 之前 D1 是 0
      Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
  finally
    Book.Free;
  end;
end;

INDIRECT 與 YEARFRAC:兩個安靜些的修正

INDIRECT 現在尊重它的第二個引數，有效參照之後還跟著文字就是錯誤，不再被無視。a1 為 FALSE 時，文字按絕對 R1C1 解析，所以 =INDIRECT("R2C3",FALSE) 讀的是 C2;舊程式碼無視這個旗標，把 "R2" 讀成第 R 欄第 2 列，悄悄回傳錯的儲存格。旗標按其 variant 型別分派(布林、數字或文字),因為把字串 variant 直接轉成 Double 會丟例外。相對 R1C1 文字如 R[1]C[1] 回傳 #REF!,因為 INDIRECT 沒有公式儲存格原點可供解析；帶尾隨字元的 A1 文字，如 "B2 junk",同樣回傳 #REF!。YEARFRAC 在 basis 0 下現在套用 DAYS360 早已實作的 NASD 二月最後一日規則：兩個日期都是二月最後一天時，結束日變成 30;然後起始日是二月最後一天時也變成 30。從 2024-02-29 到 2025-02-28,現在算出 360 天、分數恰好 1,而先前的 Days360US 算的是 359

這些修正保證了什麼，教訓又是什麼?

比較鏈行為由一個拿兩個引擎對照 Excel 16 實測值的測試保證，而這個測試存在，是因為修正的第一版說明寫錯了。v2.384.3 的 release note 一開始說從左到右摺疊讓 =1<2<3 變 TRUE——這恰好是舊右結合解析器產出的答案，與 Excel 和新程式碼的回傳正好相反。沒有人真的跑過那個例子；它是憑「1 小於 2 小於 3」的直覺寫下的。note 隨後修正，七條公式的測試在後續 commit 補上，從中得出的規則對每個撰寫試算表語義文件的人都適用：把期望值寫下來之前，先在 Excel 裡跑一遍例子。空白運算元替換與 SUMIF 放大遵循同一套 Excel 行為，含 v2.384.53 起的空白對布林情況；還需要跳過篩選或隱藏列的條件式聚合，則遵循SUBTOTAL 與 AGGREGATE 隱藏列一文裡的另一套規則

HotXLS 是原生 Delphi 與 C++Builder 試算表元件，不需安裝 Excel 就能讀取、重算與寫出 XLS、XLSX、ODS 與 CSV,本文描述的比較、空白與 SUMIF 規則都住在兩種活頁簿架構共用的計算引擎裡。完整函式清單與授權選項見HotXLS Delphi 試算表元件產品頁