HotXLSのテキスト比較：Excelのword sort順をDelphiで再現

HotXLS Delphi Componentはv2.384.67から、2つのテキスト値をExcel 16と同じやり方で比較します。大文字小文字を無視し、Windowsユーザーロケールの「word sort」順で比較する。CompareStringWにNORM_IGNORECASEフラグを付けた結果どおりの順序です。ハイフンとアポストロフィは最初のパスでスキップされ、同点を崩すときだけ効いてきます。だから="a-b">"ab"はTRUE。一方、それ以外の記号は数字と英字より前に並ぶので、="a~b"<"ab"もTRUEです。この同じ順序が今は比較演算子、> / <のクライテリア、範囲の並べ替え、VLOOKUPを駆動します

「照合順序の不一致」というタイトルのバグ報告は来ません。来るのはこういう報告です。COUNTIF(A:A,">M")がサーバー上ではExcelより2行多く数える。帳票サービスが並べ替えた価格表でX-100がExcelなら置かない場所に来る。VLOOKUP("ABC",...)が、列に明らかにabcがあるのに#N/Aを返す。3つとも同じ疑問に由来します。両オペランドがテキストのとき、どちらが小さいのか。Excelには正確な答えがあり、それはたいていのDelphiコードが与える答えではありません。そしてv2.384.67より前のHotXLSは、どのコードパスが尋ねたかによって3つの異なる答えを返していました

Excelは2つのテキスト文字列を比較するとき、どんなルールを使うのか

Excelはテキストを、ユーザーロケールのword sortで大文字小文字を無視して比較します。word sortはWindows NLS比較関数のデフォルト照合順序です。英字はコードポイントではなく言語上の順序で比較され、アクセント付き文字は基底文字の隣に置かれ、2つの文字は特別扱いを受けます。ハイフン-とアポストロフィ'は最初のパスでは無視されるので、co-opとcoopは隣同士に並び、文字列の残りが同点のときだけその有無が順序を決めます。それ以外の記号はすべて意味を持ち、数字より前に並びます。そして数字は英字より前です

表は、それが実務で何を意味するかを、Delphi開発者がいちばん手を伸ばしそうな2つの比較の隣に示したものです。Excelの列は、Excel 16がIF(A<B,...)について返した判定です。HotXLSはv2.384.67からこれを再現します

A vs B	Excel 16 / HotXLS	CompareStr（順序比較）	CompareText
"a-b" vs "ab"	大きい	小さい	小さい
"a'b" vs "ab"	大きい	小さい	小さい
"a~b" vs "ab"	小さい	大きい	大きい
"a_b" vs "ab"	小さい	小さい	大きい
"ab" vs "AB"	等しい	大きい	等しい
"é" vs "f"	小さい	大きい	大きい
"Z" vs "f"	大きい	小さい	大きい

見落としやすい帰結が2つあります。第1に、ハイフンのタイブレーク役は="a-b"="ab"がFALSEであることを意味します。文字列としては並べ替えで隣り合うほど近いのに、等しくはない。第2に、等しさの判定は大文字小文字を完全に無視するので、ab、AB、Abは比較の対象としては同じキーです。20個のテスト単語をExcelのRange.Sortで並べると、a b、a.b、a_b、a~b、a0、a1b、ab / AB / Ab、ab-、a'b、a-b、-ab、ab1、abc、b、e、é、f、Zの順になります。abグループ内では、無視される文字の位置が順序を決めます

Excelのテキスト順はどうやって特定したのか

Excelのテキスト順は、ドキュメントではなく実測で特定しました。Excelのドキュメントは照合順序の名前を挙げてくれないからです。テストでは、ASCIIの記号、数字、大文字小文字両方の英字、スペース、é、ß、ä、漢字、全角文字、ノーブレークスペースから4,000組のランダムな文字列ペアを生成しました。長さは0から4で、半数は互いに僅差のペアとして組みます。Excel 16に各ペアでIF(A<B,-1,IF(A=B,0,1))を評価させ、その判定を、フラグの組み合わせを替えたWindows比較APIの結果と突き合わせました

• NORM_IGNORECASEだけ（デフォルトのword sort、ユーザーロケール）：本物の不一致はゼロ。唯一の7件の差異は、内容が'だけのセルでした。Excelはこれをテキストプレフィックス文字として消費するので、照合順序の差ではなくサンプリングの副産物です
• NORM_IGNORECASEにSORT_STRINGSORTを加えると41件の不一致。string sortはハイフンとアポストロフィを普通の記号として扱います。まさにExcelが持っていない挙動です
• NORM_IGNOREWIDTHを加えると、今度は別の仕方で間違います。同じ文字の全角と半角が等しいと見なされるのに、Excelは両者を区別するからです

2つ目の手作業のチェックでは、厄介な20単語から取った190組すべてのペアと、同じ列に対するExcelのRange.Sortの結果を比較しました。両方とも素のNORM_IGNORECASEのword sortと一致します。この190の判定と並べ替え後の順序は、今ではHotXLSの回帰スイートの一部で、クラシックのTXLSWorkbookエンジンとXLSXネイティブのTXLSXWorkbookエンジンの両方で走ります

なぜCompareTextと順序比較は間違えるのか

CompareTextと順序比較がExcelの順序を外すのは、UTF-16のコードユニットを比較するからで、コードポイント順では記号が英字の前後の恣意的な位置に散らばります。ハイフンはU+002D、アポストロフィはU+0027で、どちらもすべての英字より下です。だから順序比較は"a-b"を"ab"より小さいと判定し、ハイフンをタイブレーカーとして扱いません。チルダU+007Eはすべての英字より上なので、"a~b"は大きく出ます。Excelの正反対です。Delphi RTLのCompareTextはa..zだけを大文字へ折り畳んでからコードユニットを比較するので、歪みがもう1つ加わります。アンダースコアU+005Fは大文字と小文字の間にあり、大文字への折り畳みによって"a_b"は"ab"より下から上へ移動するのです。どちらの関数も、éがeとfの間に属することは知りません

いつものDelphiの道具立ては、線の両側に散らばっています:

• CompareStr、文字列の<演算子、TComparer<string>.Default（中でCompareStrを呼ぶ）は順序比較かつ大文字小文字を区別します。比較器なしのTArray.Sort<string>はZをfより前に置きます
• CompareTextとSameTextは、ASCIIだけの大文字小文字折り畳みの後で順序比較します
• Windows上のDelphi RTLのAnsiCompareTextとWideCompareTextはCompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...)を呼びます。Excelと一致するのと同じ呼び出しです。デフォルト設定（UseLocale True、CaseSensitive False）のソート済みTStringListはAnsiCompareTextを通るので、これもExcelと一致します
• POSIXターゲットではDelphi RTLがAnsiCompareTextをICU照合器へ回します。これは別のアルゴリズムで、記号の規則も異なります。Free PascalのWindows上のAnsiCompareTextはANSIコードページへ変換してからCompareStringAを呼ぶので、そのページで表せない文字は失われます

つまりロケール対応のRTL関数がWindowsで正しいのは、契約ではなく実装のたまものです。Excelの順序が必要なコードは、APIを明示的に呼ぶほうが安全です。HotXLSの内部も同じ混在でした。比較演算子は両文字列を大文字化してコードポイントを比較し、クライテリア関数の> / <分岐はDelphiの大文字小文字を区別するVariant比較を使い、VLOOKUP / HLOOKUPもその大文字小文字を区別するVariant比較でテキストを照合していました。だからVLOOKUP("ABC",A1:A20,1,FALSE)はabcを見つけられなかったのです。範囲の並べ替えだけはWideCompareTextを使っていました。3つの経路、3つの順序です

HotXLS v2.384.67で何が変わったのか

v2.384.67から、HotXLSの計算と並べ替えの経路にあるテキスト対テキストの比較は、1つの関数を通ります。lxStandard.pasのXlsCompareTextで、CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...)を呼んでCSTR_EQUALを引きます。呼び出し側は、6つの比較演算子、配列数式での要素ごとの比較、COUNTIF系クライテリアとデータベース関数の>、<、>=、<=分岐、VLOOKUPとHLOOKUP（完全一致と近似一致）、動的配列関数とXLOOKUP / XMATCHの背後にある順序付けヘルパー、そして両エンジンの範囲の並べ替えです。範囲の並べ替えも同じ関数に経由させることで、並べ替え順と比較順が再び乖離できないことが保証されます。テキストの近似VLOOKUPは、列がルックアップの比較順で並べ替えられているときにだけ意味を持つので、これは重要です

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculateはアクティブシートに対して評価する
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True：ハイフンは同点だけ崩す
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False：同点は崩されるが等しくはない
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True：記号が先
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True：大文字小文字は無視
  finally
    Book.Free;
  end;
end;

型をまたぐ比較は別のルールで、変更はありません。すべての数値はすべてのテキスト値より下、すべてのテキスト値はすべての論理値より下です。比較チェーン、空オペランド、SUMIFの記事に書いたとおりです。word sortが効くのは、両オペランドがテキストのときだけです。ワイルドカードマッチも別物です。"a*"や"=ab"のようなクライテリアはパターンか等価テストで、COUNTIF、MATCH、DSUMにおけるExcelワイルドカードのガイドで扱います。ここで論じた照合順序が決めるのは、順序を比較する演算子だけです

次の例は、20個のテスト単語を1つの列に読み込み、TXLSXWorksheet.SortRangeで並べ替え、クライテリアのカウントとルックアップを確かめます。カウントは、同じ列に対してExcel 16が返した値です

const
  Words: array [0..19] of string = ('ab', 'a-b', 'a~b', 'a_b', 'AB', 'a b',
    'ab1', 'ab-', '-ab', 'abc', 'a''b', 'Ab', 'b', 'a.b', 'a1b', 'a0',
    #$00E9, 'e', 'f', 'Z');
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  i: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Words');
    for i := 0 to High(Words) do
      Sheet.Cells[i + 1, 1].Value := WideString(Words[i]);

    // 同じ列でのExcel 16の結果：11、11、14
    Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,">ab")')));
    Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,"<a-b")')));
    Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,">=AB")')));

    // v2.384.67以前は#N/A：ルックアップが大文字小文字を区別していた
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // キー1列、昇順：a b, a.b, a_b, a~b, a0, a1b, ab, AB, Ab, ...
    Sheet.SortRange(1, 1, 20, 1, [1], [False]);
    for i := 1 to 20 do
      Writeln(VarToStr(Sheet.Cells[i, 1].Value));
  finally
    Book.Free;
  end;
end;

TXLSXWorksheet.SortRangeは安定なマージソートを使うので、等しいと判定されるab、AB、Abは、並べ替え前の相対順序を保ちます。空セルはExcelと同じく、昇順でも降順でも末尾へ回ります

自分のDelphiコードでExcelの並べ替え順に合わせるには

自分のDelphiコードでExcelのテキスト順に合わせるには、CompareStringWをLOCALE_USER_DEFAULTとNORM_IGNORECASEで呼び、SORT_STRINGSORTやNORM_IGNOREWIDTHは加えません。戻り値は符号付きの比較結果ではありません。APIはCSTR_LESS_THAN（1）、CSTR_EQUAL（2）、CSTR_GREATER_THAN（3）を返し、呼び出しが失敗すると0を返します。おなじみの負 / ゼロ / 正の規約にするには2を引き、まず0を判定してください。失敗を結果と取り違えると-2、つまり黙って「小さい」になるからです

uses
  Winapi.Windows, System.SysUtils, System.Generics.Defaults,
  System.Generics.Collections;

// Excelのテキスト順：ユーザーロケールのword sort、大文字小文字を無視
function ExcelCompareText(const A, B: string): Integer;
var
  R: Integer;
begin
  R := CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE,
    PWideChar(A), Length(A), PWideChar(B), Length(B));
  if R = 0 then
    RaiseLastOSError;          // 0は失敗であって比較結果ではない
  Result := R - CSTR_EQUAL;    // 1/2/3が-1/0/1になる
end;

var
  Keys: TArray<string>;
begin
  Keys := ['abc', 'a-b', 'AB', 'a~b', '-ab', 'ab'];
  TArray.Sort<string>(Keys, TComparer<string>.Construct(
    function(const L, R: string): Integer
    begin
      Result := ExcelCompareText(L, R);
    end));
  // a~b, ab / AB（等しい。どちらの順でも）, a-b, -ab, abc
end;

TArray.Sortは安定ではないので、abとABのように等しいと判定されるキーは、どちらの順で出るかわかりません。等しいキーの元の順序が重要なら、元の位置を副キーにしてインデックス配列を並べ替えてください。逆のケースもあります。列がExcelの順序に従ってはいけないこともあるのです。たとえば品番で、X-100とX100が別コードなら、コードポイント順に並ぶべきものです。TXLSXWorksheet.SortRangeにはTXLSSortCompareEventを受け取るオーバーロードがあり、シグネチャfunction(const Left, Right: Variant): Integer of objectのメソッドを、組み込み比較の代わりに使います

uses
  System.SysUtils, System.Variants, lxStandard, lxHandleX;

type
  TPartNumberOrder = class
    function Compare(const Left, Right: Variant): Integer;
  end;

function TPartNumberOrder.Compare(const Left, Right: Variant): Integer;
begin
  // カスタム比較器にも空セル（Null）が渡る：配置は自分で決める
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // 順序比較、大文字小文字を区別
end;

var
  Sheet: TXLSXWorksheet;   // 埋まったシート。行2..501、列A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // 列Aをキーに昇順
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

カスタム比較器を渡すと、HotXLSは自分の空セル処理をスキップして生のキー値を渡します。だから比較器はNullを処理しなければなりません。降順キーでは、HotXLSは比較器の戻り値を反転します。比較器が対応しない限り、空セルが先頭へ回るのもこれです。そして、こうして並べ替えた列は、Excelの近似VLOOKUPやバイナリサーチのXLOOKUPが期待する順序から外れることを忘れずに。別の順序で並べたデータでのこれらのモードの落とし穴は、XLOOKUPとXMATCHのバイナリサーチモードのガイドで扱っています

同じワークブックが別のマシンで違う並びになるのはなぜか

同じワークブックでも別のマシンでは違う並びになり得ます。Excelのテキスト順がWindowsユーザーロケールに依存し、HotXLSはその依存を意図的に踏襲しているからです。word sortは言語固有です。たとえばスウェーデン語の照合ではäはzの後に来ますが、英語とドイツ語ではaの隣です。Excelは動作環境のロケールからこれを引き継ぐので、ストックホルムの同僚が再計算したワークブックは、シカゴのデスクトップにある同じファイルとCOUNTIF(...,">y")の結果が違うことがあります。HotXLSがLOCALE_USER_DEFAULTを渡すのは、同じマシン上でExcelと結果を等しくするためです。固定ロケールを決め打ちすると、設定の違うすべてのマシンでHotXLSはExcelと食い違うことになります

サーバー側での生成には、実務上の帰結が3つあります:

• 効いてくるロケールは、プロセスが動くアカウントのものです。WindowsサービスやIISアプリケーションプールは、開発者のデスクトップと違う地域設定で動くことがあります。IDEで確認した結果が、そのまま本番の計算結果だとは限りません
• ファイルに書き込まれるキャッシュ済みの数式結果は、生成マシンのロケールを反映します。Excelは自分のロケールで再計算するので、ファイルを別の場所で開いて再計算すると値は変わり得ます。それはExcelの挙動であって、HotXLSの副産物ではありません
• ロケール同士が食い違うのは、主にアクセント付き文字、一部の言語が1文字として扱う文字の組み合わせ、非ラテン文字です。素の英単語だけのテストデータでは、問題は表面化しません

プラットフォームの境界は単純です。HotXLSはWindowsライブラリで、DelphiとC++Builder向けにWin32とWin64、LazarusとFree Pascal向けにwin32 / win64ターゲットでビルドされ、どのビルドも同じCompareStringWを呼びます。非Windowsの照合経路は別には存在しません。あるのはAPI呼び出し失敗時のフォールバックだけです。CompareStringWが0を返した場合、XlsCompareTextは再計算の途中で例外を上げる代わりに、大文字化した文字列をコードユニットで比較します。計算は走り続けますが、Excelの順序の保証はできなくなります

クイックリファレンス：HotXLSにおけるExcelのテキスト比較

• ルール：NORM_IGNORECASE付きのユーザーロケールword sort。SORT_STRINGSORTもNORM_IGNOREWIDTHも付けない。HotXLSではv2.384.67から
• -と'は同点を崩すだけ：="a-b">"ab"はTRUEで、="a-b"="ab"はFALSE
• それ以外の記号は数字の前、数字は英字の前に並ぶ：="a~b"<"ab"も="a0"<"ab"もTRUE
• 大文字小文字は常に無関係：="ABC"="abc"はTRUEで、VLOOKUP("ABC",...)はabcを見つける
• 対応済みの経路：比較演算子、配列の比較、> / <クライテリア、VLOOKUP / HLOOKUP、動的配列の順序付け、両エンジンのSortRange
• このルールの対象外：混在型（数値 < テキスト < 論理値）とワイルドカードクライテリア。どちらにも専用のルールがあります
• Delphiコードでは：CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...)を呼び、0を判定してCSTR_EQUALを引く。結果をExcelと一致させたいなら、CompareText、CompareStr、TComparer<string>.Defaultは避ける
• 結果はコードを実行するアカウントのロケールに依存します。ExcelでもHotXLSでも同じです

普通の単語はどのルールでも同じ並びになるので、照合順序の誤りを暴くのは、ハイフン入りのコード、記号、アクセント付きの名前だけです。HotXLSは今、XLSとXLSXの両エンジンで、これらすべてにExcelの答えを返します。ライセンス、対応するDelphiとC++Builderのバージョン、トライアルダウンロードの詳細は、HotXLS Delphi Excel component pageをご覧ください