在 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 / SUMIF | MATCH(…,0) / XLOOKUP 模式 2 | DSUM 準則 | Find,整格、萬用字元開啟 |
|---|---|---|---|---|
ab | ab, AB | ab, AB | ab, AB, abc, abcb | ab, AB |
a*b | a~b, ab, AB, abcb, a*b, axb | 與 COUNTIF 相同 | 每一列都中,abc 也算 | 與 COUNTIF 相同 |
a~b | 只有 a~b | ab, AB | ab, AB, abc, abcb | ab, AB |
a~*b | 只有 a*b | 只有 a*b | 只有 a*b | 只有 a*b |
=ab | ab, 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 與資料庫函式裡一體套用這道閘門
萬用字元模式裡的跳脫規則很短。~ 讓下一個字元無論是什麼都變字面值,所以 ~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 因此在信任剖析後的條件之前,先看那段原始文字:首字元不是 =、< 或 > 的文字準則,補上一個 * 送進萬用字元比對器;其餘一律維持整列比較
在程式裡建準則範圍有一個引擎相關的坑。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 一致
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 產品頁