Delphi 中 XLOOKUP 与 XMATCH 的二分查找模式

在 A1:A7 的键 1、3、5、5、5、7、9 上，公式 XMATCH(5, A1:A7, 0, 2) 返回 3。只把最后一个参数改成 -1，同一个公式返回 5。把第一个版本指向一列被别人按别的字段重新排过序的数据，一个在表上明明白白看得见的 5 也可能回来一个 #N/A。三个答案都对。XMATCH 的最后一个参数，与 XLOOKUP 的第六个参数一样，一身兼两职：一条关于优先取哪个重复值的指令，和一份关于数据如何排序的承诺

把人带到这里的那份报告很少提查找模式。它说的是一份生成的工作簿在少数几行上与某人的预期对不上，而这些行有共同点：一个重复键、一个不得不挑邻居的近似匹配，或者一列在公式写完之后顺序变了的数据。下面几节一次讲一条规则，并给出每个公式的精确结果

XLOOKUP 接受哪些 match_mode 和 search_mode 值？

HotXLS——面向 Delphi 和 C++Builder 的原生表格组件——经一个共享查找例程求值 XLOOKUP 和 XMATCH，例程只接受恰好四种匹配模式和四种查找模式。match_mode 可以是 0（精确）、-1（精确或下一个更小）、1（精确或下一个更大）或 2（通配符）。search_mode 可以是 1（从头扫到尾）、-1（从尾扫到头）、2（对升序数据二分查找）或 -2（对降序数据二分查找）。其他任何数字返回 #VALUE!；不会钳到最近的合法模式。省略参数时选匹配模式 0 和查找模式 1，多数手写公式用的就是这组

在那之前先查参数个数。XLOOKUP 收三到六个参数，XMATCH 收两到四个，超出范围的调用在求值开始前就是 #VALUE!。模式参数本身是普通的工作表表达式，所以 HotXLS 把每个转成数字，NaN、无穷大或超出 32 位整数范围的值返回 #VALUE!，然后朝零截断。1.5 变成查找模式 1，-1.5 变成 -1，2.9 变成 2。模式来自算术计算出的单元格时截断会咬人：显示为 2、实际存成 1.9999999 的值选中的是线性扫描。模式直接写字面量，或者给算出来的套一层 ROUND

下面的程序写一张小费率表和五个查找，正好把这些规则都踩一遍。注释给出每个公式单元格在 Recalculate 之后持有的值，依据即上述规则

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Rates');          // Sheets[1]
    Sheet.Cells[1, 1].Value := 10;  Sheet.Cells[1, 2].Value := 0.25;
    Sheet.Cells[2, 1].Value := 20;  Sheet.Cells[2, 2].Value := 0.20;
    Sheet.Cells[3, 1].Value := 30;  Sheet.Cells[3, 2].Value := 0.15;
    Sheet.Cells[4, 1].Value := 40;  Sheet.Cells[4, 2].Value := 0.10;

    // 默认：match_mode 0，search_mode 1
    Sheet.Cells[1, 4].Formula := 'XLOOKUP(30,A1:A4,B1:B4)';               // 0.15
    // 精确或下一个更小，对升序键二分查找
    Sheet.Cells[2, 4].Formula := 'XLOOKUP(35,A1:A4,B1:B4,"none",-1,2)';   // 0.15
    // 1.5 截断成 search_mode 1
    Sheet.Cells[3, 4].Formula := 'XLOOKUP(40,A1:A4,B1:B4,"none",0,1.5)';  // 0.10
    // 3 不是查找模式
    Sheet.Cells[4, 4].Formula := 'XLOOKUP(40,A1:A4,B1:B4,"none",0,3)';    // #VALUE!
    // 通配符匹配跑不了二分查找
    Sheet.Cells[5, 4].Formula := 'XLOOKUP("4*",A1:A4,B1:B4,"none",2,2)';  // #VALUE!

    if Book.Recalculate = lxOk then
      Book.SaveAs('lookup-modes.xlsx');
  finally
    Book.Free;
  end;
end;

为什么 search_mode 2 会漏掉表上明明存在的键？

查找模式 2 告诉 HotXLS 查找向量已按升序排好，引擎信以为真。核实这句话要把整列扫一遍，而这正是二分查找存在要省掉的开销，所以下降过程每次探测都对半缩区间，落到哪就算哪。Microsoft 对 XLOOKUP 和 XMATCH 写的是同一份契约：二分模式要求数据已排序，否则结果无效。ISO/IEC 29500-1 第 18.17 节，即 SpreadsheetML 公式条款，描述了更老的 LOOKUP 和 VLOOKUP 及其各自的升序要求；XLOOKUP 和 XMATCH 出现得晚，以 _xlfn.XLOOKUP 和 _xlfn.XMATCH 挂在未来函数前缀下存进文件

图里追踪的是 XLOOKUP(40, A1:A3, B1:B3, "missing", 0, 2) 在键 40、10、30 上的过程。第一次探测落在第 2 行，读到 10，10 低于 40，于是丢弃第 1、2 行——装着 40 的那行也在内。第 3 行读到 30，同样低于 40，区间空了。公式返回 "missing"。换查找模式 1，同一个调用在第 1 行找到 40

“升序”的含义也比屏幕上看起来更具体。二分路径比较之前先按种类给值排位：数字、文本、布尔、错误值、空格依次在后。一列数字零件编码里有三个单元格把编码存成了文本，按这条规则就不算升序，不管看上去多整齐。文本比较不区分大小写；Windows 上顺序经 CompareStringW 来自用户区域设置，对连字符和带重音字母的处理与按字节排序不同。由二进制排序规则的数据库排好的键列，恰恰可能在带标点的那些编码上违背排序承诺

空白排最后带来一个不对称的后果。升序键列下面跟几行空的，仍是升序，所以把查找模式 2 指向 A2:A5001 这种超尺寸区域无害。降序列后面跟空行就不再是降序了，查找模式 -2 会一头扎进空白里

var
  R: TXLSFormulaEvaluationResult;
begin
  // B1:B3 装 9、7、5；B4:B10 为空
  R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B10,0,-2)');
  // R.Status = xlfeOk 且 VarIsError(R.Value)：结果是 #N/A。
  // 第一次探测读到空白 B5，空白排在所有数字之上，
  // 于是下降一路向右，再也不回头

  // 把范围裁到有数据的行，同样的调用就能用
  R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B3,0,-2)');     // 3

  // 线性扫描跳过空白，键在哪儿都能找到
  R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B10,0,-1)');    // 3
end;

重复键落在哪儿？

XMATCH 和 XLOOKUP 落在一串重复值的固定一端，端点由查找模式挑。查找模式 2 下，二分下降记录遇到的每个相等键、持续向左收窄，结果是该串的最低索引。查找模式 -2 下、对降序数据，记录位置后向右收窄，给出最高索引。线性模式返回扫描方向的第一个命中：查找模式 1 是最低索引，-1 是最高索引

这就是为什么差异报告总聚在某些行上。键唯一的样例文件在四种查找模式下答案相同，基于它建的每个测试都过。生产数据一出现重复的客户编码，各模式恰好就在重复的那些行上开始打架，而写公式的代码一行都没改过

// A1:A7 装 1、3、5、5、5、7、9（升序，一串三个）
Sheet.Cells[1, 3].Formula := 'XMATCH(5,A1:A7,0,1)';   // 3，正向第一个命中
Sheet.Cells[2, 3].Formula := 'XMATCH(5,A1:A7,0,-1)';  // 5，反向第一个命中
Sheet.Cells[3, 3].Formula := 'XMATCH(5,A1:A7,0,2)';   // 3，该串的最低索引

// B1:B7 装 9、7、5、5、5、3、1（降序）
Sheet.Cells[4, 3].Formula := 'XMATCH(5,B1:B7,0,-2)';  // 5，该串的最高索引

近似匹配怎么挑出第二名

没有单元格等于查找值时，匹配模式 -1 返回不大于目标的最大值，匹配模式 1 返回不小于目标的最小值。线性模式下 HotXLS 扫完整条向量、维护一个当前最佳候选，所以近似匹配在未排序数据上也能用。单元格只有严格更近才替换在位候选，打平绝不换。几个单元格持有同一个第二名值时，保留的是扫描顺序里先遇到的那个

拿 A1:A6 的键 10、30、20、30、50、40 来说。XMATCH(35, A1:A6, -1, 1) 返回 2：第一个 30 当上候选，第二个 30 只能打平。用 XMATCH(35, A1:A6, -1, -1) 反向扫，结果是 4，因为反向先遇到另一个 30。XMATCH(35, A1:A6, 1, 1) 返回 6，因为做 35 之上最小值，40 胜过 50。排序数据上二分模式几次探测就到同一个邻居：在 10、20、30、40、50 上，XMATCH(35, A1:A5, -1, 2) 返回 3，XMATCH(35, A1:A5, 1, 2) 返回 4

精确命中和可接受的邻居都没出现时，XLOOKUP 返回 if_not_found 参数——给了的话——没给就返回 #N/A。XMATCH 没有回退参数，永远返回 #N/A

通配符与二分查找为何不能共存

通配符模式在排序顺序里没有位置。匹配模式 2 问的是单元格是否符合掩码，是与否的二元问题；二分下降需要的是三分答案，告诉他该留哪一半。ACME-* 在某个给定单元格的左边还是右边，没有任何站得住的说法，所以匹配模式 2 配查找模式 2 或 -2 时，HotXLS 返回 #VALUE!，不猜。通配符配查找模式 1 或 -1，接受线性开销

匹配模式 2 永远套通配符规则：* 代表任意一串字符，? 代表一个字符，~ 让下一个字符按字面处理。于是 XLOOKUP("PN~*", ...) 找的是 PN* 这个文本本身——产品编码真带星号时这很要紧。匹配模式 0 下线性扫描不区分大小写比较文本；二分路径在排序比较报告打平时把两个值当相等。那个比较同样不分大小写，但它就是上文说的区域感知排序，也是二分查找唯一可以合法使用的关系。查找密集的工作表还决定了增量重算背后的依赖跟踪在一次编辑后要重算什么

形状错误与溢出的返回切片

两个函数都要一维查找区域。同时跨多行又跨多列的区域返回 #VALUE!；HotXLS 不会替你挑轴。XLOOKUP 另有一条规则：返回区域在匹配轴上的长度必须与查找区域相同。500 行的纵向查找配 499 行的返回区域是错误，最后一行绝不会被悄悄丢掉。查找区域是单个单元格时，返回区域的形状决定这次查找算纵向还是横向

纵向查找时返回区域宽过一列，XLOOKUP 会把整行匹配结果作为数组交回。它按与其他动态数组函数相同的规则溢出到相邻单元格，见溢出区域与动态数组一文。溢出切片里的空单元格以 0 返回。一条公式从表里拖出整条记录很方便，但这也是把恰好排在公式右边的一列覆盖掉的最快方式

给没人盯着的工件簿选模式

服务端生成该比交互使用执行更严的政策，因为结果出门前没有任何人看。那里保持匹配模式 0 加查找模式 1：匹配精确，扫描不管行序，重新排序工作表也改变不了答案。查找模式 2 只属于排序由同一段代码、在同一次运行里、对同一列产出的场合。对按别的键排好序的列做二分查找，是算出一个自信满满的错误数字最省钱的办法

真要把查找切到二分模式，就在发出公式的那一处自己检查排序。下面的函数不是 HotXLS 的一部分，是给你自己代码准备的守卫。对同一张表的多次精确查找，工作簿属性 RecalcLookupCache 让 Recalculate 把对某区域的首次扫描复用到后续查找上。处理过程里出现 OFFSET、INDIRECT、循环引用或 What-If 数据表时缓存自动关闭，两种方式结果一致

// 你自己的守卫，不是 HotXLS API：在发出依赖
// search_mode 2 的公式前，确认数字键列是升序
function KeysAscending(Sheet: TXLSXWorksheet; Col, FirstRow,
  LastRow: Integer): Boolean;
var
  Row: Integer;
  Prev, Cur: Variant;
begin
  Result := False;
  Prev := Sheet.Cells[FirstRow, Col].Value;
  if not VarIsNumeric(Prev) then Exit;
  for Row := FirstRow + 1 to LastRow do
  begin
    Cur := Sheet.Cells[Row, Col].Value;
    if (not VarIsNumeric(Cur)) or (Cur < Prev) then Exit;  // 文本、空白或乱序
    Prev := Cur;
  end;
  Result := True;
end;

// Rates 工作表：键在 A2:A5001，费率在 B2:B5001
UseBinary := KeysAscending(Rates, 1, 2, 5001);
Book.RecalcLookupCache := True;
for Row := 2 to OrderCount + 1 do
  if UseBinary then
    Orders.Cells[Row, 5].Formula := Format(
      'XLOOKUP(B%d,Rates!$A$2:$A$5001,Rates!$B$2:$B$5001,0,-1,2)', [Row])
  else
    Orders.Cells[Row, 5].Formula := Format(
      'XLOOKUP(B%d,Rates!$A$2:$A$5001,Rates!$B$2:$B$5001,"missing")', [Row]);

这个守卫刻意保守：一个空白或文本单元格就让整列退回线性路径。在排好序、访问频繁的列上，二分模式的收益是真的，每次查找约 log₂ n 次读取而不是最多 n 次，5000 个键意味着至多 13 次探测

查找其实是化了妆的业务规则时——比如带例外的阶梯费率——回调进你自己的 Pascal 代码通常比内置函数的任何排布都清楚；自定义工作表函数一文讲了怎么注册一个。XLOOKUP、XMATCH 和这里描述的公式引擎其余部分都是 HotXLS Delphi 表格组件的一部分，产品页列出了支持 Delphi 和 C++Builder 的工作表函数