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 / 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 | 非該当 |
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、そしてデータベース関数のすべてに適用します
ワイルドカードモード内のエスケープ規則は短いです。~は次の文字が何であれリテラルにするので、~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は、パース済みの条件を信じる前に生テキストを見ます。最初の文字が=、<、>のいずれでもないテキストクライテリアには*を付け足してワイルドカードマッチャーへ回し、それ以外はエントリー全体の比較を保ちます
コードでクライテリア範囲を組むとき、エンジン固有の落とし穴が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と同じように一致します
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スプレッドシートコンポーネントのページに一覧があります