技術文章

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

寫成 =0<B2<100 的範圍判斷,在 Excel 裡每一列都回傳 FALSE,包括 B2 是 50 的那一列。Excel 先求 0<B2 得 TRUE,再問 TRUE 是否小於 100;邏輯值排在所有數字之上,所以不管 B2 裝什麼,答案都是 FALSE。空白儲存格與 SUMIF 藏著同類的驚喜:一個空儲存格同時等於 0、"" 與 FALSE,而 =SUMIF(A1:A10,">5",B1) 會把 B 欄十個儲存格加總

要在 Excel 之外重算這種活頁簿的計算引擎,必須重現這些答案,哪怕它們明顯不是作者的本意。檔案裡快取的結果,正是檢視器、郵件預覽或下游匯入在任何有人按 F9 之前看到的東西。若伺服器為範圍判斷寫出 TRUE、Excel 稍後卻顯示 FALSE,同一份報告依開啟地點不同帶著兩個數字,沒有人說得準該信哪一個。修作者的公式是作者的職責

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

Excel 對 =1<2<3 回傳 FALSE,因為六個比較運算子共用同一優先層級、由左至右結合,公式意即 (1<2)<3,內層的 TRUE 隨後在型別排名裡輸給數字 3。HotXLS Delphi Component 在它的兩套活頁簿引擎——經典的 TXLSWorkbook 與原生 XLSX 的 TXLSXWorkbook——都以同樣方式剖析比較鏈。剖析器讀一個運算元,只要下一個 token 是 =、<>、<、>、<= 或 >=,就用目前為止解析的一切當左子節點、下一個運算元當右子節點,建出比較節點,再把節點帶進下一輪。與它處理 +、- 和 & 的是同一個迴圈形狀

陷阱藏在教科書文法裡。寫成 expr = operand op expr 的遞迴下降剖析器,會為右側遞迴進自己,悄悄把運算子變成右結合。照那種讀法 =1<2<3 變成 1<(2<3),即 1<TRUE,即 TRUE。這個錯誤兩個方向都會出事:=3>2>1 在 Excel 是 TRUE、在右結合下是 FALSE,=1=1=TRUE 同樣翻面。加括號的形態怎麼解析都對,所以只測 (a<b)<c 的測試永遠抓不到它

Calculate 方法不求入儲存格、直接求值公式字串,正好用來把整張比較鏈清單在兩套引擎上快速過一遍。這個方法的深入介紹在HotXLS 公式引擎總覽一文

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

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 的答案: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;
=1<2<3 的兩棵剖析樹:右結合解析把 1<(2<3) 求值為 TRUE,由左至右折疊則把 (1<2)<3 求值為 FALSE,因為 Excel 把每個數字排在文字之下、每個文字值排在布林之下
HotXLS 兩套引擎都把比較鏈由左至右折疊——布林壓過每個數字,TRUE 輸給 3 正是鏈式範圍判斷變 FALSE 的原因

數字、文字與布林:跨型別排名

比較兩側型別不同時,Excel 不做轉換,而是給型別排名:每個數字小於每個文字值,每個文字值小於每個布林。HotXLS 把兩個運算元分類為數字、文字或布林,類別不同時只比較類別。所以 =TRUE>99999 是 TRUE、="1">2 是 TRUE、=FALSE>"zzz" 也是 TRUE。鏈式比較之所以讓人覺得任性,癥結就在這裡:TRUE<3 不是 TRUE 被強制轉成 1 再與 3 比,而是一個布林與一個數字比,布林贏

同一類別內的比較是自然比較,文字有個轉折:字串比較不分大小寫,="abc"="ABC" 在 Excel 與 HotXLS 都回傳 TRUE。日期對引擎而言是序號,所以日期永遠排在任何文字之下,包括看起來像日期的文字儲存格。匯入的日期欄若有一半以字串抵達,排序與比較就分成兩個互不相干的區段,=A2>DATE(2026,1,1) 對每一個字串儲存格都回傳 TRUE

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

空白儲存格當比較運算元時,採用另一側的型別:對數字等於 0、對文字等於 ""、對邏輯值等於 FALSE。A1 為空時,=A1=0、=A1="" 與 =A1=FALSE 在 Excel 全是 TRUE。HotXLS 在排名兩個運算元之前做同樣的代入,六個比較運算子皆然、兩套引擎皆然。兩個運算元都空白時不做代入,兩者比較為相等

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=FALSE')));  // True
  Writeln(VarToStr(Wb.Calculate('=A1<B1')));     // True:空白以 0 參與比較
  Writeln(VarToStr(Wb.Calculate('=A1<0')));      // False:0 不低於 0
end;

實務上咬人的是最後一行。算術早就把空白當 0,=A1+1 在任何引擎都是 1。比較才是捷徑出錯的地方。把「空白」留成自己那個最低排名、壓在每個數字之下的引擎,會對 =A1<0 回答 TRUE,接著 =IF(A1<0,"overdrawn","ok") 就把每個空餘額儲存格標成透支。只代入 0 或空字串也不夠:對上布林時空白會變成 0,排名同時低於 TRUE 與 FALSE,=A1=FALSE 在空儲存格上就會變 FALSE

HotXLS 空白運算元代入:空的 A1 與 0 及空文字比較為相等,空白對上布林時以 FALSE 比較,所以 =A1=FALSE 為 TRUE、與 Excel 一致;把空白留成最低排名則會讓 =A1<0 對每個空餘額都成立
代入配合另一個運算元的型別:0、空字串或 FALSE——未經排名的原始 Null 正是把每個空餘額標成透支的元兇

有一個後果值得先想好。因為空白對邏輯值以 FALSE 比較,=A1=FALSE 分不出空儲存格與真存了 FALSE 的儲存格,Excel 與 HotXLS 皆然;=A1=0 分不出空與零。需要知道使用者是否真的輸入過值的範本,該用 ISBLANK(A1) 測試。=A1="" 這種寫法更弱,因為回傳空字串的公式也會讓它成立

為什麼單一儲存格加總範圍的 SUMIF 蓋住整欄?

Excel 保留準則範圍的形狀,只借加總範圍的左上角,所以 =SUMIF(A1:A10,">5",B1) 意即 B1:B10。大量手寫範本依賴這個簡寫而不自知。直覺的實作——把走訪夾在兩個範圍中較小的那個——會把例子縮成 A1 對 B1 的單一測試、回傳 0。HotXLS 走訪準則範圍,按相同的列與欄偏移從加總範圍左上角逐格取值。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)';  // 明寫加總範圍
    Sheet.Cells[3, 4].Formula := '=AVERAGEIF(A1:A10,">5",B1)';  // 同樣的放大
    if Book.Recalculate = lxOk then
      // D1 與 D2:4000(600+700+800+900+1000);D3:800
      Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ',
        VarToStr(Sheet.Cells[2, 4].Value), ' ', VarToStr(Sheet.Cells[3, 4].Value));
  finally
    Book.Free;
  end;
end;
HotXLS 的 SUMIF 與 AVERAGEIF 放大:=SUMIF(A1:A10,">5",B1) 走訪十列準則範圍、按對應偏移讀 B1 到 B10 得 4000,夾到單格加總範圍則回傳 0
Excel 只借加總範圍的左上角、保留準則形狀,範本裡寫 B1 意即 B1:B10——SUMIF 與 AVERAGEIF 走完全部十個偏移、同樣方式修剪過大的範圍

自己寫的範本,把加總範圍完整寫出來。引擎尊重簡寫是因為既有檔案依賴它,但公式裡的 B1 在下一位維護者眼中讀起來就是單一儲存格,而那個人會合理地以為只加了一筆金額。中間的準則以單一值求值、兩側參數則是參照,這種參數類別的分野在隱含交集與參數類別一文有解釋。還得跳過篩選或隱藏列的條件總計,規則又不同,見SUBTOTAL 與 AGGREGATE 的隱藏列一文

INDIRECT 參照樣式與 YEARFRAC basis 0

兩個較小的函式帶著同一類陷阱。INDIRECT 有選用的第二個參數 a1,為 FALSE 時文字是 R1C1 參照:=INDIRECT("R2C3",FALSE) 讀的是 C2。忽略旗標的剖析器把「R2」讀成第 R 欄第 2 列——那是一個真實存在的儲存格,公式於是從錯的地方回傳一個煞有介事的值。HotXLS 把旗標讀成布林、數字(零代表 FALSE)或文字「FALSE」,並在解析之前先轉換 R1C1 文字,R[1]C[1] 這類帶括號偏移以持有公式的儲存格為基準。有效參照之後還殘留其他內容的文字,如 "B2 junk",回傳 #REF! 而非默默用 B2

basis 0 的 YEARFRAC 使用 US(NASD)30/360 天數計算,包含 DAYS360 也套用的二月末日規則:兩個日期都是二月最後一天時,結束日變成 30,接著起算日在二月最後一天時也變成 30。從 2024-02-29 到 2025-02-28 得 360 天、比例恰為 1。跳過那些規則的樸素 30/360 得 359 天與 0.99722,差異出現在跨越二月底的計息上

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['C2', 'C2'].Value := 'row 2, column 3';

  Writeln(VarToStr(Wb.Calculate('=INDIRECT("R2C3",FALSE)')));   // row 2, column 3
  Writeln(VarToStr(Wb.Calculate('=ISERROR(INDIRECT("B2 junk"))')));  // True: #REF!
  Writeln(VarToStr(Wb.Calculate(
    '=YEARFRAC(DATE(2024,2,29),DATE(2025,2,28),0)')));           // 1
end;

記錄或測試這類行為時,期望值取自 Excel 本身,而不是公式看起來在說的話。「1 小於 2 小於 3」暗示 =1<2<3 是 TRUE,那恰恰是右結合剖析器給的答案、與 Excel 的回傳相反。一張公式配 Excel 顯示值的簡表,就能抓住本文全部四個案例:比較鏈、空白、SUMIF 放大與二月天數

HotXLS 在機器上沒有 Excel 的前提下,以 Delphi 與 C++Builder 讀取、重算並寫出 XLS、XLSX、ODS 與 CSV,上述比較、空白儲存格與條件加總規則都屬於兩個活頁簿類別共用的計算引擎。支援的函式清單、版本與授權在HotXLS Delphi spreadsheet component 頁面