技術文章

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 那格就不再入計。HotXLS 在 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 不同。Name 欄放著 abc、ab、xab 與 AB 時,準則 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,樣式用盡就停手的比對器會拒絕該格;HotXLS 比對器把「樣式用盡、文字尚餘」算一次不匹配,從最後一顆星重試,直到覆蓋整格
整格比對在樣式用盡時還沒完;把剩餘文字算一次不匹配,比對器就會退回最後一顆星,a*b 便是這樣觸及 abcb,與 Excel Find 一致

Excel Find 不分整格或部分模式,都把 ~ 當成其後任一字元的跳脫:a~b 找到 ab,a~~b 找到 a~b,結尾的波浪號被忽略,q~ 行同 q。只認得 ~*、~? 與 ~~ 的比對器,會讓 a~b 找到文字 a~b,恰好是相反的那格。HotXLS 在 Find、Replace 與公式裡一樣,跳脫波浪號之後的任何字元

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 spreadsheet component 產品頁