HotXLSのExcelワイルドカード：COUNTIF、MATCH、DSUM

HotXLS Delphi Componentは、同じパターン文字列を4通りに読み分けます。Excel 16がそうするからです。COUNTIFとSUMIFでは、クライテリアに*か?も含まれない限り、テキストa~bはリテラルです。MATCHとXLOOKUPのワイルドカードモードでは、チルダは常にエスケープなので、a~bはabを見つけます。DSUMなどのデータベース関数では、素のテキストは「前方一致」を意味します。そして全セル一致のFindは、最後の*へのバックトラックが必要です。HotXLSはこれらの実測に基づくルールに、v2.384.52、v2.384.60、v2.384.64から従っています

この分野のバグ報告は、ワイルドカードには一言も触れません。来るのはこういう話です。サーバー生成のレポートのカウントが、同じファイルをExcelで再計算した場合より数行少ない。チルダ入りの品番がある数式では見つかり、次の数式では無視される。原因は、パターンがどこでも同じ意味を持つと決めつけるマッチャーです。Excelはそのようには動かないので、キャッシュ結果をExcelと一致させねばならないエンジンも、そうはいきません。v2.384.52より前のHotXLSは、すべてのクライテリアをDOS式のファイルマスクへ通していました。日常的なパターンは合っていて、エッジケースが静かに間違っていたのです

なぜ1つのパターン文字列がExcelで4つの意味を持つのか

1つのパターン文字列が4つの意味を持つのは、Excelが4つの機能から4つのマッチングルールを継承したまま、統一してこなかったからです。クライテリア関数（COUNTIF、SUMIF、AVERAGEIFと*IFSファミリー）は、クライテリアごとにワイルドカードを適用するかを決めます。ルックアップ関数（match type 0のMATCH、match_mode 2のXLOOKUP）は常に適用します。データベース関数（DSUM、DCOUNTAなど）はAdvanced Filterに従い、そこでは裸の単語が前方一致です。Findダイアログには独自の全セル一致と部分一致のモードがあります。次の表は、a~b、ab、AB、abc、abcb、a*b、axbを含む1つの列に対し、各パターンにどのセルが一致するかを、全関数をデフォルトの大文字小文字を無視するモードで並べたものです

パターン	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP mode 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の行はもう1つの罠を示しています。abcはDSUMでは一致し、COUNTIFでは一致しません。データベース関数が黙って*を付け足すからです。ab、a*b、=abのDSUMの欄はExcel 16での実行からそのまま取ったものです。a~bのDSUMの欄は、同じ前方一致ルールから導かれます。付け足された*によってクライテリアはワイルドカードパターンになり、そこでは~bはエスケープされたbだからです

COUNTIFはいつワイルドカードモードに切り替わるのか

COUNTIFがワイルドカードモードに切り替わるのは、クライテリアのテキストが*か?を含むときだけです。エスケープの有無は問いません。どちらの文字もなければ、Excelはクライテリアを各セルと文字列全体として大文字小文字を無視して比較し、チルダはただのチルダです。だからCOUNTIF(A1:A7,"a~b")は、文字どおりa~bを保持するセルを数えます。星を1つ足すと意味が反転します。"a~b*"ではチルダが今度はbをエスケープし、パターンは「abの後に何でも」と読め、セルa~bはもう数えられません。HotXLSはこのルールをv2.384.52から両エンジンで適用してきました。lxCalcの1つのクライテリアマッチャーを、COUNTIF、SUMIF、AVERAGEIF、COUNTIFS、SUMIFS、AVERAGEIFSとデータベース関数が共有する形です

ワイルドカードモードの中でのエスケープの規則は、Excelの他の場所と同じです。~は次の文字が何であれリテラルにし、~bはb、~~はチルダ1つを意味します。パターンの末尾のチルダは捨てられるので、"a*~"は"a*"と同じ振る舞いをします。角括弧は決して特別ではありません。"[x]"というクライテリアは、[x]という3文字を保持するセルを数え、"[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 16では、数値、論理値、エラー値、空セルも含まれます。素の"<>"はまったく別の問いです。これは「空セルでない」を意味するので、空セルをスキップしつつ、=""のような数式が返す空のテキストも含めて、すべての値を数えます。古いHotXLSのコードはテキストセルは正しく数えたものの、数値が駄目でした。Variantの不等号比較がDelphiに'ab'を数値へ変換させ、変換が例外を上げ、ハンドラーがそれを「不一致」として飲み込み、数値セルが静かにカウントから漏れていたのです。空オペランドが通常の比較で何と等しいかを含む、空セルの話の側面は、HotXLSが比較チェーン、空セル、SUMIFを扱う仕組みで説明しています

a~bを検索したのに、MATCHはabを見つけるのはなぜか

a~bを検索したのにMATCHがabを見つけるのは、match type 0のMATCHとmatch_mode 2のXLOOKUPが常にワイルドカードモードだからです。パターンに*も?も含まれなくても、チルダはエスケープです。a~bとabを保持する2セルの範囲でExcel 16が確認できます。MATCH("a~b",D1:D2,0)は2を返し、a~bだけの範囲では同じ呼び出しが#N/Aを返します。リテラルのテキストa~bをルックアップするには"a~~b"と書くしかありません。その一方で、同じ2セルに対するCOUNTIF(D1:D2,"a~b")は1を返し、もう片方のセルを数えます。同じ文字列、同じ範囲、逆のセルです

だからHotXLSは2つの判定を、1つの「パターン照合」エントリーポイントの後ろにまとめず、分けて保持します。マッチャーそのものは共有です。v2.384.52から、MATCH、XLOOKUP、クライテリア関数は同じバックトラッキングのマッチャーを走らせ、エスケープ処理も末尾チルダのルールも同じです。違うのはその前のゲートです。クライテリアの経路はまず「このテキストは*か?を含むか」と尋ね、ルックアップの経路は尋ねません。2つを統合すれば片方のファミリーは直り、もう片方が壊れます。両方向とも、両エンジンでExcel 16の値と突き合わせてチェック済みです。ワイルドカードルックアップには独自の前提条件もあります。XLOOKUPは、ワイルドカード照合とバイナリサーチモードの組み合わせを拒みます。HotXLSのXLOOKUPとXMATCHのサーチモードのガイドで述べたルールです

DSUMとデータベース関数は素のテキストクライテリアをどう読むのか

DSUMなどのデータベース関数は、先頭に=、<、>のないテキストクライテリアを「前方一致」として読みます。ワイルドカードは引き続き有効です。これがAdvanced Filterのルールで、COUNTIFとは意図的に異なります。abc、ab、xab、AB、a~b、a*bを含むName列でExcel 16を実測すると、クライテリアabはabc、ab、ABに一致し、=abはabとABだけに一致し、<>abはエントリー全体の不等式、a*bとa?も前方一致のパターン、>abは通常の比較になります。v2.384.64より前のHotXLSはabに完全一致させていたため、このテストデータへのDSUMは、Excelが11を返すところで10を返していました

修正は条件パーサーを迂回する必要がありました。このパーサーはabと=abを同じ等価条件へ折り畳むからです。そこでHotXLSは、パース済みの条件を信じる前に生のクライテリアテキストを検査します。先頭の文字が=、<、>のいずれでもないテキストクライテリアには*を付け足してワイルドカードマッチャーへ回し、それ以外はエントリー全体の比較を保ちます。コードでクライテリア範囲を組み立てるときの実務的な注意を1つ。XLSXエンジンでは、文字列'=ab'をTXLSXCell.Valueへ代入するとテキストとして格納されます。一方、クラシックのTXLSWorkbookエンジンは、=で始まる値をアポストロフィを付けない限り数式としてコンパイルします

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 (begins with)
    // =ab  -> 6    ab, AB (whole entry)
    // <>ab -> 121  ab と AB 以外のすべて
    // a*b  -> 127  a*b* は abc 込みで 7 個すべてに一致
    // a~*  -> 32   字面通りの a*b のみ
  finally
    Book.Free;
  end;
end;

前方一致の修正後も残り、古いビルドでは効いてくる関連の差異が1つあります。>abのようなテキスト比較はコードポイント順を使っていましたが、Excelは記号を英字の前に置くので、"a~b">"ab"はExcelではFALSE、HotXLSではTRUEでした。v2.384.67からは、>と<のクライテリアが、通常のテキスト比較や並べ替えとともに、現在のユーザーロケールでExcelのword sort照合を使うので、両者は再び一致します

全セル一致のFindがabcbを見逃したのはなぜか

全セル一致のFindがabcbを見逃したのは、マッチャーが最後の*へバックトラックする代わりに、パターンを使い切った最初の時点で止まっていたからです。Replaceの背後にある部分一致のマッチャーは、パターンを使い切り次第すぐ返ります。全セル一致のFindはこれを再利用したうえで、一致がセル全体を覆うことを要求しました。abcbに対するa*bはabの後で止まり、4文字のうち2文字しか消費していないので拒否されたのです。v2.384.60から、全セル一致のマッチャーは独立した実装になり、「パターンは終わったがテキストは残っている」をもう1つの不一致として扱い、最後の星から再試行します。だからa*bはabcbに一致し、a?b*bはaxbybに一致します。「Match entire cell contents」にチェックを入れたExcel 16のFindと同じです

同じリリースでチルダも変わりました。Excel 16のFindは、全セル一致でも部分一致でも、~を次の任意の文字のエスケープとして扱います。a~bはabを見つけ、a~~bはa~bを見つけ、末尾のチルダは無視されるので、q~はqと同じ振る舞いをします。より古いHotXLSのマッチャーがエスケープとして認識するのは~*、~?、~~だけだったため、a~bはテキストa~bを見つけていました。なお、~1文字だけのFindパターンはExcel自身でも不安定で、空のパターンのようにどのセルにも一致します。HotXLSはこの挙動を模倣しません

XLSXエンジンでの検索は、TXLSXFindOptionsセット付きのTXLSXWorksheet.FindTextです。lxfUseWildcardsが*、?、~を有効にし、lxfWholeCellはセル全体の一致を要求し、lxfMatchCaseは比較を大文字小文字を区別させます。lxfUseWildcardsがなければ、星を含むすべての文字がリテラルです。Findが見るのはテキスト値だけです。数値セルはスキップされ、数式セルもlxfSearchFormulasが設定されていない限りスキップされます。設定されていれば数式テキストが検索されます。StartRowとStartColが与えるアンカーは包含的なので、Find Allのループは各ヒットの1列先へ進めます

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：~~はリテラルのチルダ1つ

    // 部分一致の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を含む4行すべてを見つけます。部分一致モードではa*bがセル内のどこかに現れれば足りるからです。FindTextInとReplaceTextInは同じオプションに加えてFirstRow、FirstCol、LastRow、LastColのウィンドウを取り、選択範囲内を検索するプログラム版に相当します。クラシックエンジンは同じ規則を、3つのブール値を取るオーバーロード、TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell)と、対応するReplaceTextオーバーロードで公開します。行と列の結果は1始まりです:

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マスクのマッチャーは何を間違えたのか

古いマッチャーは特殊文字を間違えていました。DOSのファイルマスクは、Excelのワイルドカードとは別の言語だからです。v2.384.52より前、クライテリア関数とデータベース関数は、すべてのパターンをMatchesMask、つまりlxMasksユニットのファイルマスクマッチャーへ渡していました。構文はよくあるケースでExcelと重なるので問題は隠れていましたが、実データが面白くなる場所で乖離します:

• [x]は文字セットとして読まれていました。だからCOUNTIF(A1:A10,"[x]")は括弧付きテキストではなくxを保持するセルを数え、"[a-z]"は1文字のセルに何でも一致しました
• チルダのエスケープは存在しなかったので、"a~*b"はリテラルのアスタリスクに一致できませんでした
• 閉じ忘れた括弧のような不正なマスクは例外を上げ、呼び出し側がそれを「不一致」として飲み込みました。クライテリアのタイプミスが、静かに間違った合計へ化ける形です
• ルックアップ側では、MATCHとXLOOKUPがエスケープとして扱うのは~*、~?、~~だけでした。だからMATCH("a~b",…,0)はabではなくリテラルのa~bを見つけていたのです

ワークブックが*と?を素の英数字データにしか使っていないなら、結果はすでに正しく、変わりません。括弧、チルダ、"<>text"での混在型の列、あるいは裸の単語で書かれたDSUMのクライテリアが含まれるなら、v2.384.64以降で再計算すると合計が変わることがあります。新しい合計こそ、Excelが表示するものです。Excelがクライテリアをどう保存するかとどう比較するかの区別は、保存済みフィルターでも顔を出します。BIFF8 AutoFilter DOPERクライテリアのHotXLS記事で論じています

クイックリファレンス：HotXLSにおけるExcelワイルドカードのルール

• COUNTIF、SUMIF、AVERAGEIFと*IFSファミリーは、クライテリアに*か?があるときだけワイルドカードを使います。なければ文字列全体を大文字小文字を無視して比較し、~はリテラルです（v2.384.52から）
• match type 0のMATCHとmatch_mode 2のXLOOKUPは常にワイルドカードを使うので、a~bはabを見つけ、リテラルにはa~~bが必要です（v2.384.52から）
• ワイルドカードモードでは~は次の任意の文字をエスケープし、末尾の~は捨てられます。[と]は普通の文字です
• "<>text"は数値、論理値、エラー、空セルを数えます。素の"<>"は空でないセルを数え、=""の結果も含みます
• DSUMなどのデータベース関数は素のテキストを「前方一致」として扱います。=textと<>textはエントリー全体を比較します（v2.384.64から）
• lxfUseWildcardsとlxfWholeCell付きの全セル一致Findはバックトラックするので、a*bはabcbに一致します。FindとReplaceは~を任意の文字のエスケープとして扱います（v2.384.60から）
• >と<クライテリアでのテキスト順は、Excelのword sort照合に従い、記号が英字の前に来ます（v2.384.67から）

数式エンジンにおけるExcel互換とは、ほとんどがこの種のエッジケースです。ドキュメントからの推測ではなく、Excelに対する実測で決めています。HotXLSはCOUNTIF、MATCH、XLOOKUP、DSUMと関数ライブラリの残りを、Excelをインストールせずに、DelphiとC++Builderでネイティブに評価します。クラシックエンジンでもXLSXエンジンでもです。詳細、エディション、トライアルダウンロードは、HotXLS Delphi spreadsheet component pageをご覧ください