技术文章

HotXLS 的 Excel 通配符:COUNTIF、MATCH、DSUM 与 Find

D1 放 a~b、D2 放 ab 时,=COUNTIF(D1:D2,"a~b") 返回 1,数到的是 D1;=MATCH("a~b",D1:D2,0) 返回 2,指向 D2。同一段模式串,同样两个单元格,Excel 命中的却是不同的单元格,差别只在哪个函数来读这段模式。一个想让缓存结果与 Excel 一致的电子表格引擎必须复现这道分野,外加另外两条规则:DSUM 这类数据库函数把裸文本当「以之开头」,整单元格 Find 则必须回溯进最后一个 *

撞上这件事的报告很少提通配符。它们说一份服务器生成的工作簿比同一文件在桌面上重算的结果少数了两行,或者说一个带波浪号的零件号被这条公式找到、被下一条无视。把每个模式都喂给一个 DOS 风格的文件掩码,日常场景蒙得对,所以偏差只在真实产品代码开始包含 ~ 或 [ 时才现身

一段模式串,四套 Excel 规则

Excel 从未统一过自己的匹配规则,每个功能各带一套。条件函数(COUNTIF、SUMIF、AVERAGEIF 和 *IFS 家族)按单个条件决定通配符到底启不启用;查找函数(match type 0 的 MATCH、match_mode 2 的 XLOOKUP)永远启用;数据库函数(DSUM、DCOUNTA 及同伴)继承高级筛选的规则,裸词就是前缀;Find 对话框有自己的整单元格与部分匹配两档。HotXLS Delphi Component 在经典引擎和 XLSX 引擎里都按各家族自己的规则求值。下表列出一列数据(a~b、ab、AB、abc、abcb、a*b、axb)上每个模式命中的单元格,所有函数都取默认的忽略大小写模式

模式COUNTIF / SUMIFMATCH(…,0) / XLOOKUP 模式 2DSUM 条件Find,整单元格,启用通配符
abab, ABab, ABab, AB, abc, abcbab, AB
a*ba~b, ab, AB, abcb, a*b, axb同 COUNTIF全部条目,abc 也在内同 COUNTIF
a~b仅 a~bab, ABab, AB, abc, abcbab, AB
a~*b仅 a*b仅 a*b仅 a*b仅 a*b
=abab, AB不适用ab, AB不适用

有两行值得多看一眼。a~b 那一行里,COUNTIF 与 MATCH 落在不同的单元格上,而手敲的编码里波浪号出现的频率超出任何人的计划。a*b 那一行里,abc 对 DSUM 命中、对 COUNTIF 不中,因为数据库函数悄悄给条件补了一个 *。a~b 的 DSUM 条目正是这条补上的星号推出来的:星号一到位,条件就成了通配符模式,~b 只是被转义的 b

COUNTIF 什么时候切入通配符模式?

只有条件文本里含 * 或 ?(转义与否都算)时,COUNTIF 才切入通配符模式。两个字符都没有时,条件按整串与每个单元格比较,忽略大小写,波浪号只是普通字符,所以 COUNTIF(A1:A7,"a~b") 数的是字面持有 a~b 的那个单元格。一个星号就把意思翻过来:在 "a~b*" 里,波浪号转义的是 b,模式读作「ab 后跟任意内容」,a~b 单元格不再被数进去。COUNTIF、SUMIF、AVERAGEIF、COUNTIFS、SUMIFS、AVERAGEIFS 和数据库函数都过同一道闸门

HotXLS 通配符闸门示意图:COUNTIF 和 SUMIF 只在条件含星号或问号时才启用通配符,所以 a~b 数到字面量单元格返回 1,而 MATCH type 0 与 XLOOKUP 模式 2 永远处于通配符模式,所以 a~b 找到位置 2 的 ab
闸门就是全部差异:COUNTIF 要先见到星号或问号才把波浪号当转义,MATCH 从不发问,于是同一段模式串数的和找到的是两个不同单元格

通配符模式内的转义规则很短:~ 让紧随其后的字符无论是什么都成字面量,所以 ~b 就是 b、~~ 是一个波浪号;模式最末尾的波浪号被丢弃,"a*~" 表现同 "a*"。方括号永远不特殊。条件 "[x]" 数的是持有三个字符 [x] 的单元格,"[a-z]" 在普通数据上一个都数不到。拿自己的数据验证这一切最快的方法是 TXLSXWorkbook.Calculate——它对活动表求值一个公式字符串并返回 Variant;新建的工作簿会把第一个加进来的表设为活动表

uses
  System.Variants, lxHandleX;

const
  Names: array [1..7] of string = ('a~b', 'ab', 'AB', 'abc', 'abcb', 'a*b', 'axb');
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  i: Integer;

  procedure Show(const Formula: string);
  begin
    Writeln(Formula, ' = ', VarToStr(Book.Calculate(Formula)));
  end;

begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');            // 第一个表,所以是活动表
    for i := 1 to High(Names) do
    begin
      Sheet.Cells[i, 1].Value := Names[i];
      Sheet.Cells[i, 2].Value := 1 shl (i - 1);  // 1, 2, 4 ... 让 SUMIF 的合计点得出行号
    end;
    Sheet.Cells[8, 1].Value := 5;                // 一个数字;A9 保持空白

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    没有 * 或 ?:纯文本,即 a~b 单元格
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    通配符模式:ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    整串通配,abc 不在内
    Show('=SUMIF(A1:A7,"a*b",B1:B7)');  // 119  除 abc(8)之外的每一行
    Show('=COUNTIF(A1:A7,"a~*b")');     // 1    字面量 a*b
    Show('=COUNTIF(A1:A9,"<>ab")');     // 7    数字 5 和空白 A9 都计入
    Show('=COUNTIF(A1:A9,"<>")');       // 8    非空单元格
  finally
    Book.Free;
  end;
end.

「<>text」数的是什么?

"<>text" 条件数每一个不是该文本的单元格,在 Excel 里这包括数字、布尔值、错误值和空白单元格。光秃秃的 "<>" 问的则完全是另一件事:「非空白单元格」。它跳过空单元格,但数每一个值,包括 ="" 这类公式返回的空文本。Delphi 的陷阱藏在 Variant 比较里。拿 'ab' 与数值单元格比较,Delphi 会试着把 'ab' 转成数字,转换抛出异常,而把异常当「无匹配」吞掉的处理器就让数值单元格悄悄掉出了计数。HotXLS 把它们数进去,所以示例最后两行返回 7 和 8。这件事的空白单元格一面——包括普通比较里空操作数等于什么——见HotXLS 如何处理比较链、空白单元格与 SUMIF

搜 a~b 时 MATCH 为什么找到 ab?

match type 0 的 MATCH 与 match_mode 2 的 XLOOKUP 永远处于通配符模式,所以哪怕模式串里没有 * 或 ?,波浪号也是转义。在开篇那两个单元格的区域上,MATCH("a~b",D1:D2,0) 返回 2;换成只装 a~b 的区域,同一调用返回 #N/A。想查找字面文本 a~b,得写 "a~~b"。查找键来自用户输入或外部系统、可能带波浪号时,先把公式里每个波浪号都翻倍再拼进去;一行 StringReplace 的成本,比事后解释一行怎么丢了便宜得多

HotXLS 把这两个决策分开。MATCH、XLOOKUP 和条件函数共享同一个带回溯的匹配器,转义处理与尾部波浪号规则一模一样;不同的只是它前面那道闸门。条件路径先问文本里含不含 * 或 ?;查找路径从不问。两边共用一个「模式匹配」入口,无论闸门怎么设,都会修好一家、弄坏另一家。通配符查找还有一条前置条件:match_mode 2 搭配二分查找模式时 XLOOKUP 返回 #VALUE!,见HotXLS 的 XLOOKUP 与 XMATCH 搜索模式指南

DSUM 条件:裸文本意为「以之开头」

DSUM 和其他数据库函数把开头没有 =、< 或 > 的文本条件读作前缀,通配符仍然生效。这是高级筛选的规则,而且是刻意与 COUNTIF 不同。在装着 abc、ab、xab 和 AB 的 Name 列上,条件 ab 命中 abc、ab 和 AB,=ab 只命中 ab 和 AB。<>ab 是整条不等,a*b 和 a? 同样是前缀模式,>ab 则是普通文本比较。xab 永远命中不了 ab——规则是「以之开头」,不是「包含」

对实现来说别扭的地方在于:一个把运算符剥掉的条件解析器会把 ab 和 =ab 看成同一个相等测试,能区分它们的只有原始条件文本。所以 HotXLS 在信任解析出的条件之前先看这段原始文本:首字符不是 =、< 或 > 的文本条件,追加一个 * 后走通配符匹配器,其余一切保持整条比较

HotXLS 的 DSUM 条件规则示意图:裸文本条件追加星号后按前缀匹配,ab 够到 ab、AB、abc 和 abcb,等号 ab 比较整条,尖括号 ab 把两者都排除,波浪星保持为字面量 a*b,实测 DSUM 合计 30、6、121、32
Excel 为数据库函数继承了高级筛选规则:裸文本意为以之开头,前导等号或不等号则比较整条;HotXLS 在信任解析出的条件之前先查原始条件文本

用代码搭条件区域有一个引擎相关的坑。XLSX 引擎里,把字符串 '=ab' 赋给 TXLSXCell.Value 存的是文本,这正好是条件单元格需要的;经典 TXLSWorkbook 引擎会把以 = 开头的字符串值编译成公式,除非你加撇号前缀。运行时搭条件区域,XLSX 引擎是不容易出错的选择。下面的循环把五个条件依次换进 D2,每次重算同一个 DSUM

const
  Names: array [1..7] of string = ('a~b', 'ab', 'AB', 'abc', 'abcb', 'a*b', 'axb');
  Criteria: array [0..4] of string = ('ab', '=ab', '<>ab', 'a*b', 'a~*');
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  i: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Db');
    Sheet.Cells[1, 1].Value := 'Name';
    Sheet.Cells[1, 2].Value := 'Val';
    for i := 1 to High(Names) do
    begin
      Sheet.Cells[i + 1, 1].Value := Names[i];
      Sheet.Cells[i + 1, 2].Value := 1 shl (i - 1);
    end;
    Sheet.Cells[1, 4].Value := 'Name';            // 条件表头在 D1
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // 在 XLSX 引擎里保持为文本
      Writeln(Criteria[i], ' -> ',
        VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
    end;
    // ab   -> 30   ab, AB, abc, abcb(前缀匹配)
    // =ab  -> 6    ab, AB(整条比较)
    // <>ab -> 121  除 ab 和 AB 之外的一切
    // a*b  -> 127  a*b* 匹配全部七个,abc 也在内
    // a~*  -> 32   只有字面量 a*b
  finally
    Book.Free;
  end;
end;

>ab 这类排序条件走的是又一条规则。朴素的码点比较会让 "a~b">"ab" 为 TRUE,因为 ~ 在 Unicode 里排在所有字母之上;Excel 把标点排在字母之前,返回 FALSE。在 Windows 上,HotXLS 的 > 和 < 文本条件、普通文本比较与排序都按当前用户区域设置下的系统单词排序规则求值,所以三者彼此一致,也与 Excel 一致

Delphi 里的整单元格 Find:回溯进最后一个星号

整单元格 Find 一个诱人的捷径是复用 Replace 背后的部分匹配器,再要求命中覆盖整个单元格。它在普通数据上就会失败。部分匹配器模式一耗尽就返回,a*b 对 abcb 在 ab 之后停下,4 个字符吃掉 2 个,单元格被拒。HotXLS 让整单元格 Find 走一个独立的匹配器,把「模式结束、文本没完」当作又一场不匹配,从最后一个星号重试。这样 a*b 命中 abcb、a?b*b 命中 axbyb,正如勾选「单元格内容完全匹配」的 Excel Find

HotXLS 整单元格通配符 Find 回溯示意图:模式 a*b 在单元格 abcb 里吃掉 a 和 b,旧匹配器在模式耗尽处停下拒收该单元格,现行匹配器把「模式结束而文本有剩」当作又一场不匹配,从最后一个星号重试,直到整个单元格命中
模式耗尽时整单元格匹配并没完;把剩余文本当作又一场不匹配,匹配器就退回最后一个星号——a*b 就是这样够到 abcb 的,如同 Excel Find

无论整单元格还是部分匹配,Excel Find 都把 ~ 当作对任一后继字符的转义:a~b 找到 ab,a~~b 找到 a~b,尾部波浪号被无视,q~ 表现同 q。只认 ~*、~? 和 ~~ 的匹配器会让 a~b 找到文本 a~b——正好相反的单元格。Find 和 Replace 与公式一样,HotXLS 都转义波浪号之后的任意字符

XLSX 引擎里的调用是 TXLSXWorksheet.FindText 配一个 TXLSXFindOptions 集合:lxfUseWildcards 打开 *、? 和 ~;lxfWholeCell 要求整个单元格命中;lxfMatchCase 让比较区分大小写。不给 lxfUseWildcards,每个字符包括星号都是字面量。Find 只看文本值;数值单元格跳过,公式单元格也跳过,除非设了 lxfSearchFormulas,那会改搜公式文本。lxfBackward 和 lxfByColumns 改变扫描顺序。StartRow 和 StartCol 给出的锚点是含入的,所以 Find All 循环要在每个命中之后前进一列

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row, Col, NextRow, NextCol, Changed: Integer;
  Opts: TXLSXFindOptions;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Parts');
    Sheet.Cells[1, 1].Value := WideString('abc');
    Sheet.Cells[2, 1].Value := WideString('abcb');
    Sheet.Cells[3, 1].Value := WideString('a~b');
    Sheet.Cells[4, 1].Value := WideString('ab');

    Opts := [lxfUseWildcards, lxfWholeCell];
    if Sheet.FindText('a*b', Row, Col, Opts, 1, 1) then
      Writeln('a*b  whole cell -> row ', Row);   // 2:abc 被拒,abcb 靠回溯命中
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4:~b 是被转义的 b
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3:~~ 是一个字面波浪号

    // 部分匹配,Find All:锚点单元格算在内,所以每个命中之后前进一步
    NextRow := 1;
    NextCol := 1;
    while Sheet.FindText('a*b', Row, Col, [lxfUseWildcards], NextRow, NextCol) do
    begin
      Writeln('a*b  contained in row ', Row);     // 行 1, 2, 3 和 4
      NextRow := Row;
      NextCol := Col + 1;
    end;

    // 整单元格通配符替换只改写字面量 a~b
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

部分匹配循环报告全部四行,abc 也在内,因为部分模式下 a*b 只要在单元格内某处出现即可。FindTextIn 和 ReplaceTextIn 收同样的选项,外加 FirstRow、FirstCol、LastRow、LastCol 窗口——「在选定区域内查找」的程序版,任何一边传 0 表示不设界。经典引擎经一个三布尔重载暴露同样的匹配规则——TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell),外加配套的 ReplaceText 重载。它的行列结果从 1 起算,与 XLSX 版本一样跳过公式单元格和非文本值

var
  Classic: IXLSWorkbook;
  Sheet: TXLSWorksheet;
  Row, Col: Integer;
begin
  Classic := TXLSWorkbook.Create;
  Sheet := Classic.Sheets.Add;
  Sheet.Range['A1', 'A1'].Value := 'abcb';
  // MatchCase = False, UseWildcards = True, WholeCell = True
  if Sheet.FindText('a*b', Row, Col, False, True, True) then
    Writeln('found at ', Row, ',', Col);           // 1,1
  if not Sheet.FindText('a*c', Row, Col, False, True, True) then
    Writeln('a*c does not cover abcb');
end;

为什么 DOS 文件掩码不是 Excel 条件的正确匹配器

MatchesMask 这类文件掩码匹配器说的是另一门语言,尽管它与 Excel 通配符在 * 和 ? 上重合。恰恰是这点重合让它被选中,也让它的错误一直藏到数据开始有意思的那一刻:

  • [x] 被读成字符集合,所以 COUNTIF(A1:A10,"[x]") 数到的是持有 x 的单元格而不是带方括号的文本,"[a-z]" 会命中任何单个字母的单元格
  • 没有波浪号转义,"a~*b" 无法命中字面星号
  • 畸形的掩码(比如没闭合的方括号)抛出的异常很容易被调用方当「无匹配」吞掉,条件里一个笔误就此变成错误的合计
  • 查找这头,只认 ~*、~? 和 ~~ 的转义集同样不够:MATCH("a~b",…,0) 得找到 ab,而不是字面的 a~b

如果你的工作簿只在普通字母数字数据上用过 * 和 ?,这些匹配器的答案彼此一致,这一切都无关紧要。方括号、波浪号、"<>text" 下的混合类型列,以及写成裸词的 DSUM 条件,才是更简单的匹配器的合计偏离 Excel 的地方,也正是上面代码演练的那些场景。保存的筛选器引出一个相关问题:Excel 怎么存一个条件与怎么比一个条件不是一回事,HotXLS 的 BIFF8 AutoFilter DOPER 条件一文有讨论

HotXLS 在 Delphi 和 C++Builder 上原生求值 COUNTIF、MATCH、XLOOKUP、DSUM 和函数库的其余部分,经典引擎与 XLSX 引擎皆然,机器上无需安装 Excel。版本、文档与试用下载见HotXLS Delphi 电子表格组件页