技術記事

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

D1にa~b、D2にabがあるとき、=COUNTIF(D1:D2,"a~b")は1を返し、数えるセルはD1です。=MATCH("a~b",D1:D2,0)は2を返し、これはD2を指します。同じパターン文字列、同じ2つのセルなのに、Excelはどの関数がパターンを読むかで違うセルを選びます。キャッシュした結果をExcelと一致させたいスプレッドシートエンジンは、この分岐に加えて2つの規則も再現しなければなりません。DSUMのようなデータベース関数は裸のテキストを前方一致として扱い、全セル一致のFindは最後の*へのバックトラックを必要とします

これに遭遇したレポートは、ワイルドカードについてめったに語りません。サーバー生成のブックが、デスクトップで再計算した同じファイルより2行少なく数えるとか、チルダ入りの型番がある式では見つかり、次の式では無視されると報告されるだけです。すべてのパターンをDOS風ファイルマスクに通せば日常的なケースは正しいので、ズレが顕在化するのは、実製品のコードが~や[を含み始めてからです

1つのパターン文字列、4つのExcel規則

Excelは照合規則を統一しませんでした。機能ごとに独自の規則を持ち込んだからです。クライテリア関数(COUNTIF、SUMIF、AVERAGEIFと*IFS系)は、クライテリアごとにワイルドカードを適用するかを決めます。ルックアップ関数(一致タイプ0のMATCH、match_mode 2のXLOOKUP)は常に適用します。データベース関数(DSUM、DCOUNTAなど)はAdvanced Filterの規則を継承し、裸の単語は前方一致です。Findダイアログには全セル一致と部分一致のモードがあります。HotXLS Delphi Componentは、クラシックエンジンとXLSXエンジンの両方で、各ファミリーをそれぞれの規則で評価します。表は、a~b、ab、AB、abc、abcb、a*b、axbを含む1列に対して、各パターンがどのセルに一致するかを示します。すべての関数がデフォルトの大文字小文字を無視するモードです

パターンCOUNTIF / SUMIFMATCH(…,0) / XLOOKUP mode 2DSUMのクライテリアFind、全セル一致、ワイルドカード有効
abab, ABab, ABab, AB, abc, abcbab, AB
a*ba~b, ab, AB, abcb, a*b, axbCOUNTIFと同じ全エントリー、abcを含むCOUNTIFと同じ
a~ba~bのみab, ABab, AB, abc, abcbab, AB
a~*ba*bのみa*bのみa*bのみa*bのみ
=abab, AB非該当ab, AB非該当

2つの行にはもう一度目を凝らす価値があります。a~bの行では、COUNTIFとMATCHが違うセルに着地します。手打ちのコードにチルダが入る頻度は、誰が見積もるより高いものです。a*bの行では、abcはDSUMでは一致しCOUNTIFでは一致しません。データベース関数がクライテリアに黙って*を付け足すからです。a~bのDSUMの結果も、この付け足された星から導かれます。星が付いた時点でクライテリアはワイルドカードパターンになり、~bはエスケープされたただのbになるのです

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

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

HotXLSのワイルドカードゲートの図。COUNTIFとSUMIFはクライテリアに星か疑問符があるときだけワイルドカードを適用するので、a~bはリテラルのセルを数えて1を返します。一方、match type 0のMATCHとmatch_mode 2のXLOOKUPは常にワイルドカードモードなので、a~bは位置2のabを見つけます
ゲートこそがすべての違いです。COUNTIFはチルダをエスケープとして扱う前に星か疑問符を尋ね、MATCHは尋ねません。だから同じパターン文字列が、あるセルを数え、別のセルを見つけるのです

ワイルドカードモード内のエスケープ規則は短いです。~は次の文字が何であれリテラルにするので、~bはb、~~はチルダ1個を意味します。パターンの末尾にあるチルダは捨てられるので、"a*~"は"a*"と同じ振る舞いをします。角括弧は決して特殊文字ではありません。"[x]"というクライテリアは、3文字の[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は数値セルも数えるので、例の最後の2行は7と8を返します。空セル側の話題、つまり通常の比較で空のオペランドが何と等しいかを含めて、HotXLSが比較チェーン、空セル、SUMIFを扱う方法で解説しています

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

一致タイプ0のMATCHとmatch_mode 2のXLOOKUPは常にワイルドカードモードです。パターンに*も?なくても、チルダはエスケープとして働きます。冒頭の2セル範囲では、MATCH("a~b",D1:D2,0)は2を返します。a~bだけを保持する範囲では、同じ呼び出しは#N/Aを返します。リテラルのa~bをルックアップするには"a~~b"が必要です。ルックアップキーがユーザー入力や外部システム由来でチルダを含みうるなら、数式を組み立てる前にすべてのチルダを2重化してください。たった1つのStringReplaceが、後で「行が消えた」と説明する手間より安上がりです

HotXLSはこの2つの判断を分けて保持します。MATCH、XLOOKUP、クライテリア関数は1つのバックトラックマッチャーを共有し、エスケープ処理も末尾チルダの規則も同じです。違うのは手前のゲートです。クライテリアの経路はまず、テキストが*か?を含むかを尋ねます。ルックアップの経路は決して尋ねません。両方から使われる単一の「パターン照合」エントリポイントは、どちらにゲートを置いても片方のファミリーを必ず間違えます。ワイルドカードルックアップにはもう1つ条件があります。match_mode 2とバイナリサーチモードの組み合わせでXLOOKUPが#VALUE!を返す件は、XLOOKUPとXMATCHの検索モードに関するHotXLSガイドで扱っています

DSUMのクライテリア:裸のテキストは前方一致

DSUMと他のデータベース関数は、先頭に=、<、>のないテキストクライテリアを前方一致として読み、ワイルドカードも有効のままです。これがAdvanced Filterの規則で、COUNTIFとの違いは意図的なものです。abc、ab、xab、ABを保持するName列では、クライテリアabはabc、ab、ABに一致し、=abはabとABだけに一致します。<>abはエントリー全体の不等比較で、a*bとa?も前方一致パターン、>abは通常のテキスト比較です。xabがabに一致することはありません。規則は前方一致であって部分一致ではないからです

実装側で厄介なのは、演算子を剥がしてパースするクライテリアパーサーには、abと=abが同じ等値テストに見えることです。両者を区別するのは生のクライテリアテキストだけです。そこでHotXLSは、パース済みの条件を信じる前に生テキストを見ます。最初の文字が=、<、>のいずれでもないテキストクライテリアには*を付け足してワイルドカードマッチャーへ回し、それ以外はエントリー全体の比較を保ちます

DSUMのクライテリアルールのHotXLSの図。裸のテキストクライテリアには星が付け足され前方一致として照合されるので、abはab、AB、abc、abcbに届き、=abはエントリー全体を比較し、<>abは両方を除外し、チルダ星はリテラルのa*bとして生き残ります。このときのDSUM合計は30、6、121、32です
Excelはデータベース関数にAdvanced Filterのルールを継承させました。裸のテキストは前方一致、先頭の等号や不等号はエントリー全体の比較です。HotXLSはパース済みの条件を信じる前に、生のクライテリアテキストを検査します

コードでクライテリア範囲を組むとき、エンジン固有の落とし穴が1つあります。XLSXエンジンでは、文字列'=ab'をTXLSXCell.Valueに代入するとテキストとして格納され、クライテリアセルにまさに必要な動きです。クラシックのTXLSWorkbookエンジンは、=で始まる文字列値を、アポストロフィを前置きしない限り数式としてコンパイルします。実行時に組み立てるクライテリア範囲には、ミスの起きにくいXLSXエンジンが賢い選択です。下のループは5つのクライテリアを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 (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;

>abのような順序クライテリアは、また別の規則に従います。素のコードポイント比較では、~がUnicodeでどの字母よりも上にいるため、"a~b">"ab"はTRUEになります。Excelは記号を字母の前に置くのでFALSEを返します。Windows上のHotXLSは、>と<のテキストクライテリアも、通常のテキスト比較もソートも、現在のユーザーロケールのシステム語順照合で評価します。3者が互いに、そしてExcelと一致するのはそのためです

Delphiの全セル一致Find:最後の星へのバックトラック

全セル一致Findの手近な近道は、Replaceの背後にある部分一致マッチャーを流用し、一致がセル全体を覆うよう要求することです。普通のデータで破綻します。部分一致マッチャーはパターンを使い切った時点で返るので、abcbに対するa*bはabで止まり、4文字のうち2文字しか消費しておらず、セルは拒否されます。HotXLSは全セル一致Findを専用のマッチャーで回します。「パターンは終わったがテキストが残った」をもう1つの不一致として扱い、最後の星から再試行するのです。これでa*bはabcbに、a?b*bはaxbybに、「セル内容全体に一致する」を付けたExcel Findと同じように一致します

全セル一致ワイルドカードFindのバックトラックを示すHotXLSの図。パターンa*bはセルabcbのaとbを消費し、パターンを使い切った時点で止まるマッチャーはセルを拒否します。HotXLSのマッチャーは、パターンが終わってもテキストが残っていればそれをもう1つの不一致として扱い、セル全体が一致するまで最後の星から再試行します
全セル一致では、パターンを使い切っても一致は完了していません。残ったテキストをもう1つの不一致として扱うことで、マッチャーは最後の星へ戻ります。これがa*bをabcbへ届かせる仕組みで、ExcelのFindと同じです

ExcelのFindは、全セル一致でも部分一致でも、~を「次の任意の1文字」のエスケープとして扱います。a~bはabを見つけ、a~~bはa~bを見つけ、末尾のチルダは無視されるので、q~はqと同じです。~*、~?、~~しか知らないマッチャーだと、a~bはテキストa~bを見つけることになり、正反対のセルへ行き着きます。HotXLSは、数式でもFindとReplaceでも、チルダの後の任意の文字をエスケープします

XLSXエンジンでの呼び出しは、TXLSXFindOptionsをセットしたTXLSXWorksheet.FindTextです。lxfUseWildcardsが*、?、~を有効化し、lxfWholeCellはセル全体の一致を要求し、lxfMatchCaseは比較を大文字小文字を区別するものにします。lxfUseWildcardsがなければ、星を含むすべての文字がリテラルです。Findが見るのはテキスト値だけです。数値セルはスキップされ、数式セルはlxfSearchFormulasが設定されていない限りスキップされ、設定されていれば数式テキストが検索されます。lxfBackwardとlxfByColumnsは走査順を変えます。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のウィンドウを受け取ります。選択範囲内検索をプログラムから行う版で、境界に0を渡せば開放のままになります。クラシックエンジンは、同じ照合規則を3つのブーリアンを持つオーバーロード、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]"は1文字のセルすべてに一致してしまいます
  • チルダのエスケープが存在しないので、"a~*b"はリテラルのアスタリスクに一致できません
  • 閉じ括弧がないマスクのような不正形は例外を上げ、呼び出し側がそれを「一致なし」として飲み込むと、クライテリアのタイプミスが誤った合計に化けます
  • ルックアップ側でも、~*、~?、~~に限ったエスケープ集合では足りません。MATCH("a~b",…,0)はabを見つける必要があり、リテラルのa~bではいけないのです

ブックが素の英数字データに*と?を使うだけなら、どのマッチャーでも結果は同じで、この話は無縁です。角括弧、チルダ、"<>text"の下の混在型の列、裸の単語で書かれたDSUMクライテリア。ここが、簡単なマッチャーの合計がExcelからズレる場所であり、上のコードが確かめるケースでもあります。保存されたフィルタは、Excelがクライテリアをどう格納するか、どう比較するかという別の疑問を持ち出します。これはBIFF8 AutoFilterのDOPERクライテリアに関するHotXLSの記事で扱っています

HotXLSは、COUNTIF、MATCH、XLOOKUP、DSUMと関数ライブラリの残りを、クラシックエンジンとXLSXエンジンの両方で、DelphiとC++Builderからネイティブに評価します。マシンにExcelのインストールは不要です。エディション、ドキュメント、トライアルビルドは、HotXLS Delphiスプレッドシートコンポーネントのページに一覧があります