技术文章

=1<2<3 为什么是 FALSE:HotXLS 比较链与空单元格

写成 =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,同一份报告就会因打开地点不同而含两个不同的数字,而且没人说得清该信哪个。修作者的公式是作者的活儿

Excel 里 =1<2<3 为什么返回 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 与 Excel 一样为 TRUE,而把空保留为最低档会让 =A1<0 对每个空余额都为 TRUE
替换匹配另一侧操作数的类型——0、空串或 FALSE;让 Null 直接参与排序,正是把每个空余额标成透支的原因

有一个值得提前规划的后果。因为空对逻辑值按 FALSE 比较,=A1=FALSE 分不清空单元格和存着 FALSE 的单元格,Excel 和 HotXLS 皆然;=A1=0 分不清空和零。需要知道用户是否真的输入过值的模板,应该用 ISBLANK(A1) 测试。=A1="" 更弱,因为对返回空串的公式它同样为 TRUE

为什么单格求和区域的 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 使用美式(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)')));   // 第 2 行第 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 在 Delphi 和 C++Builder 下读写并重算 XLS、XLSX、ODS 和 CSV,机器上无需 Excel;上面的比较、空单元格和条件聚合规则属于两个工作簿类共享的计算引擎。支持的函数列表、版本与授权见 HotXLS Delphi spreadsheet 组件页