DelphiにおけるXLOOKUPとXMATCHのバイナリサーチモード

A1:A7 の鍵 1、3、5、5、5、7、9 の上で、数式 XMATCH(5, A1:A7, 0, 2) は 3 を返します。最後の引数だけを -1 に変えると、同じ数式は 5 を返します。最初の版を、誰かが別のフィールドで並べ替えた列に向けると、シート上に明らかに見える 5 が #N/A として返ってくることがあります。3 つの答えはすべて正しいのです。XMATCH の最後の引数は、XLOOKUP の 6 番目の引数と同じく、一度に 2 つのものです。どの重複を優先するかという指示と、データがどう並んでいるかという約束

ここに至らせるレポートは、search mode についてめったに言及しません。生成されたワークブックが、ほんの一握りの行で誰かの期待と食い違うと言い、それらの行は何かを共有しています。繰り返される鍵、隣人を選ばざるを得なかった近似一致、数式が書かれた後に順序が変わった検索列です。以下の節は、各数式が生む正確な結果とともに、規則を 1 つずつたどります

XLOOKUP はどの match_mode と search_mode の値を受け入れるのか

HotXLS、ネイティブな Delphi と C++Builder のスプレッドシートコンポーネントは、XLOOKUP と XMATCH を、ちょうど 4 つの match mode と 4 つの search mode を受け付ける 1 つの共有検索ルーチンで評価します。match_mode は 0（完全一致）、-1（完全一致か次に小さい）、1（完全一致か次に大きい）、2（ワイルドカード）が可能です。search_mode は 1（最初から最後へ走査）、-1（最後から最初へ走査）、2（昇順データ上のバイナリサーチ）、-2（降順データ上のバイナリサーチ）が可能です。それ以外の数は #VALUE! を返します。何も最寄りの合法モードへはクランプされません。引数を省くと match mode 0 と search mode 1 が選ばれ、ほとんどの手書き数式はこれを使います

引数の数は、そのどれよりも先に検査されます。XLOOKUP は 3 から 6 個、XMATCH は 2 から 4 個の引数を受け取り、その範囲外の呼び出しは評価が始まる前の #VALUE! です。モード引数自体は普通のワークシート式なので、HotXLS は各々を数値へ変換し、NaN、無限大、32 ビット整数範囲外の値には #VALUE! を返し、それからゼロへ向かって切り捨てます。1.5 は search mode 1 になり、-1.5 は -1 になり、2.9 は 2 になります。切り捨てが牙を剥くのは、モードが算術で計算されたセルから来るときです。2 と表示されていても 1.9999999 として格納されている値は、線形走査を選びます。モードはリテラルで書くか、計算されたものを ROUND で包んでください

次のプログラムは、小さなレートテーブルと、それらの規則を試す 5 つのルックアップを書きます。コメントは、Recalculate の後に各数式セルが保持する値を、上の規則から導いて示します

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Rates');          // Sheets[1]
    Sheet.Cells[1, 1].Value := 10;  Sheet.Cells[1, 2].Value := 0.25;
    Sheet.Cells[2, 1].Value := 20;  Sheet.Cells[2, 2].Value := 0.20;
    Sheet.Cells[3, 1].Value := 30;  Sheet.Cells[3, 2].Value := 0.15;
    Sheet.Cells[4, 1].Value := 40;  Sheet.Cells[4, 2].Value := 0.10;

    // 既定: match_mode 0、search_mode 1
    Sheet.Cells[1, 4].Formula := 'XLOOKUP(30,A1:A4,B1:B4)';               // 0.15
    // 完全一致か次に小さいもの、昇順キー上のバイナリサーチ
    Sheet.Cells[2, 4].Formula := 'XLOOKUP(35,A1:A4,B1:B4,"none",-1,2)';   // 0.15
    // 1.5 は search_mode 1 に切り捨てられる
    Sheet.Cells[3, 4].Formula := 'XLOOKUP(40,A1:A4,B1:B4,"none",0,1.5)';  // 0.10
    // 3 は search mode ではない
    Sheet.Cells[4, 4].Formula := 'XLOOKUP(40,A1:A4,B1:B4,"none",0,3)';    // #VALUE!
    // ワイルドカードマッチングはバイナリサーチでは走れない
    Sheet.Cells[5, 4].Formula := 'XLOOKUP("4*",A1:A4,B1:B4,"none",2,2)';  // #VALUE!

    if Book.Recalculate = lxOk then
      Book.SaveAs('lookup-modes.xlsx');
  finally
    Book.Free;
  end;
end;

search_mode 2 がシート上にある鍵を見逃すのはなぜか

search mode 2 は HotXLS に、検索ベクトルがすでに昇順に並んでいると告げ、エンジンはそれを信じます。主張の検査は列全体の 1 パスを要し、それはバイナリサーチが存在して回避するまさにそのコストなので、降下はプローブごとに区間を半分にし、終わったどこからでも返ります。Microsoft は XLOOKUP と XMATCH について同じ契約を文書化しています。バイナリモードはソートされたデータを要求し、そうでなければ無効な結果を与えるのです。SpreadsheetML の数式条項である ISO/IEC 29500-1 §18.17 は、より古い LOOKUP と VLOOKUP を、それぞれの昇順要件とともに記述します。XLOOKUP と XMATCH は後から来て、future-function プレフィックスの下で _xlfn.XLOOKUP と _xlfn.XMATCH としてファイルに格納されます

図は、鍵 40、10、30 の上の XLOOKUP(40, A1:A3, B1:B3, "missing", 0, 2) をなぞります。最初のプローブは 2 行目に着地し、10 を読み、10 は 40 未満なので 40 を保持する行を含む 1、2 行目を捨てます。3 行目は 30 を読みます。これも 40 未満で、区間は空になります。数式は "missing" を返します。search mode 1 なら同じ呼び出しは 1 行目で 40 を見つけます

「昇順」はまた、画面上の見た目より具体的な何かを意味します。バイナリ経路は値を比較する前に種類でランク付けします。数値が最初、次にテキスト、次にブーリアン、次にエラー値、次に空白です。3 つのセルがコードをテキストとして保持する数値部品コードの列は、どれほど整って見えても、この規則の下では昇順ではありません。テキストは大文字小文字を区別せずに比較され、Windows では順序がユーザーロケールから CompareStringW を通して来ます。これはハイフンとアクセント付き文字を、バイト順ソートとは違って扱います。バイナリ照合順序のデータベースでソートされた鍵列は、句読点を含むまさにそのコードで、順序の約束に失敗し得ます

空白が最下位にランクされることには、偏った帰結があります。下に空行を伴う昇順の鍵列はそれでも昇順なので、search mode 2 を A2:A5001 のような過大な範囲に向けるのは無害です。空行が続く降順の列はもはや降順ではなく、search mode -2 はまっすぐ空白の中へ歩みます

var
  R: TXLSFormulaEvaluationResult;
begin
  // B1:B3 は 9、7、5 を保持。B4:B10 は空
  R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B10,0,-2)');
  // R.Status = xlfeOk で VarIsError(R.Value): 結果は #N/A。
  // 最初のプローブは空の B5 を読み、空はすべての数の上にランクされるので、
  // 降下は右へ動き続けて二度と戻らない

  // 範囲を埋まった行に切り詰めれば同じ呼び出しが機能する
  R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B3,0,-2)');     // 3

  // 線形走査は空をスキップし、鍵がどこにいても見つける
  R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B10,0,-1)');    // 3
end;

重複鍵はどこに着地するのか

XMATCH と XLOOKUP は重複ランの固定された端に着地し、search mode がその端を選びます。search mode 2 の下では、バイナリ降下は出会った各等しい鍵を記録し、左への絞り込みを続けるので、結果はランの最低インデックスです。search mode -2 の下では、降順データの上で、位置を記録し右へ絞り込み、最高インデックスを与えます。線形モードは走査方向の最初のヒットを返します。search mode 1 なら最低インデックス、search mode -1 なら最高インデックスです

食い違いの報告が特定の行に固まるのはこのためです。ユニークな鍵のサンプルファイルは、4 つの search mode すべての下で同じ答えを与えるので、そこから作られたすべてのテストは通ります。本番データが顧客コードを繰り返し始めると、モードは正確にその繰り返された行の上で食い違い始め、数式を書いたコードはまったく変わっていないのです

// A1:A7 は 1、3、5、5、5、7、9（昇順、3 連のラン）
Sheet.Cells[1, 3].Formula := 'XMATCH(5,A1:A7,0,1)';   // 3、前方の最初のヒット
Sheet.Cells[2, 3].Formula := 'XMATCH(5,A1:A7,0,-1)';  // 5、後方の最初のヒット
Sheet.Cells[3, 3].Formula := 'XMATCH(5,A1:A7,0,2)';   // 3、ランの最低インデックス

// B1:B7 は 9、7、5、5、5、3、1（降順）
Sheet.Cells[4, 3].Formula := 'XMATCH(5,B1:B7,0,-2)';  // 5、ランの最高インデックス

近似一致はどう準優勝を選ぶのか

どのセルも検索値に等しくないとき、match mode -1 はターゲットより大きくない最大の値を返し、match mode 1 は小さくない最小の値を返します。線形モードでは、HotXLS はベクトル全体を走査し、最良候補を保持するので、近似一致はソートされていないデータの上でも機能します。セルが現職の候補を置き換えるのは、厳密により近いときだけで、単に同点のときではありません。同じ準優勝値を保持する複数のセルの中で、保たれるのは走査順で最初に出会ったものです

A1:A6 の鍵 10、30、20、30、50、40 を取ります。XMATCH(35, A1:A6, -1, 1) は 2 を返します。最初の 30 が候補になり、2 つ目の 30 は同点にすぎません。XMATCH(35, A1:A6, -1, -1) で走査をひっくり返すと結果は 4 です。後方へ走査すると、もう片方の 30 に先に出会うからです。XMATCH(35, A1:A6, 1, 1) は 6 を返します。35 より上の最小の値としては 40 が 50 に勝つからです。ソートされたデータの上では、バイナリモードは一握りのプローブで同じ隣人に到達します。10、20、30、40、50 の上で、XMATCH(35, A1:A5, -1, 2) は 3 を返し、XMATCH(35, A1:A5, 1, 2) は 4 を返します

完全なヒットも受け入れ可能な隣人も現れないとき、XLOOKUP は供給されていれば if_not_found 引数を、そうでなければ #N/A を返します。XMATCH にはフォールバック引数がなく、常に #N/A を返します

ワイルドカードとバイナリサーチが共存できない理由

ワイルドカードパターンはソート順の中に位置を持ちません。match mode 2 はセルがマスクに合うかを問います。これは Yes か No かの問いであり、バイナリ降下は、どちらの半分を保つかを告げる 3 値の答えを要します。ACME-* があるセルの左にいるか右にいるかを述べる擁護可能な方法はなく、HotXLS は match mode 2 と search mode 2 か -2 の組み合わせに #VALUE! を返し、推測しません。ワイルドカードには search mode 1 か -1 を使い、線形のコストを受け入れてください

match mode 2 は常にワイルドカード規則を適用します。* は任意の文字の連なりを、? は 1 文字を表し、~ は次の文字をリテラルにします。だから XLOOKUP("PN~*", ...) はテキスト PN* 自身を見つけます。製品コードが本当にアスタリスクを含むときに重要です。match mode 0 では、線形走査はテキストを大文字小文字を区別せずに比較し、バイナリ経路は、順序比較が同点を報告するとき 2 つの値を等しいと扱います。その比較も大文字小文字を区別しませんが、それは上で記述した同じロケール対応の順序であり、バイナリサーチが正当に使ってよい唯一の関係です。ルックアップの多いシートは、インクリメンタル再計算の背後の依存関係追跡が、編集の後に何を再評価しなければならないかをも形作ります

形状エラーとスピルする戻りスライス

両関数とも、1 次元の検索範囲が要ります。同時に複数行と複数列にまたがる範囲は #VALUE! を返します。HotXLS はあなたの代わりに軸を選びません。XLOOKUP は 2 つ目の規則を足します。戻り範囲は、マッチング軸に沿って検索範囲と同じ長さでなければなりません。500 行の縦検索に 499 行の戻り範囲を組ませるのはエラーであり、最終行が静かに落とされることは決してありません。検索範囲が 1 つのセルであるときは、戻り範囲の形状が、検索を縦と数えるか横と数えるかを決めます

縦検索に対して 1 列より広い戻り範囲は、XLOOKUP にマッチした行全体を配列として返させます。他の動的配列関数と同じ規則の下で隣のセルへスピルします。スピル範囲と動的配列の記事で扱います。スピルしたスライスの中の空セルは 0 として返ります。1 つの数式でテーブルから完全なレコードを引き出すのは便利で、同時に、数式の右にたまたま座っていた列を上書きする最速の方法でもあります

誰も見ないワークブックのためのモード選び

サーバー側の生成は、対話使用より厳格なポリシーに値します。結果が出荷される前に誰も見ないからです。そこでは match mode 0 と search mode 1 のままにしてください。一致は完全で、走査は行順を無視し、シートの並べ替えは答えを変えられません。search mode 2 が属するのは、同じコードが同じ実行の中で、同じ列の上に順序を生んだ場所だけです。別の鍵でソートされた列の上のバイナリサーチは、自信たっぷりの間違った数字を計算する最安の方法です

ルックアップをバイナリモードへ切り替えるときは、数式を出す場所で自分で順序を確認してください。下の関数は HotXLS の一部ではなく、自分のコードに足すガードです。1 つのテーブルに対する多数の完全一致ルックアップには、ワークブックプロパティの RecalcLookupCache が、Recalculate に範囲の最初の走査を、それに対する後続のルックアップへ再利用させます。キャッシュは、パスが OFFSET、INDIRECT、循環参照、What-If データテーブルを含むとき自分で切れ、結果はどちらでも同じです

// 自分で書くガード（HotXLS API ではない）: 数値キー列が昇順であることを、
// それに依存する search_mode 2 の数式を出す前に確認する
function KeysAscending(Sheet: TXLSXWorksheet; Col, FirstRow,
  LastRow: Integer): Boolean;
var
  Row: Integer;
  Prev, Cur: Variant;
begin
  Result := False;
  Prev := Sheet.Cells[FirstRow, Col].Value;
  if not VarIsNumeric(Prev) then Exit;
  for Row := FirstRow + 1 to LastRow do
  begin
    Cur := Sheet.Cells[Row, Col].Value;
    if (not VarIsNumeric(Cur)) or (Cur < Prev) then Exit;  // テキスト、空白、順序外
    Prev := Cur;
  end;
  Result := True;
end;

// Rates シート: 鍵は A2:A5001、レートは B2:B5001
UseBinary := KeysAscending(Rates, 1, 2, 5001);
Book.RecalcLookupCache := True;
for Row := 2 to OrderCount + 1 do
  if UseBinary then
    Orders.Cells[Row, 5].Formula := Format(
      'XLOOKUP(B%d,Rates!$A$2:$A$5001,Rates!$B$2:$B$5001,0,-1,2)', [Row])
  else
    Orders.Cells[Row, 5].Formula := Format(
      'XLOOKUP(B%d,Rates!$A$2:$A$5001,Rates!$B$2:$B$5001,"missing")', [Row]);

ガードは意図的に保守的です。1 つの空白かテキストのセルが、列全体を線形経路へ送り戻します。ソートされたホットな列の上では、バイナリモードの見返りは現実のものです。ルックアップごとに最大 n 回の代わりに約 log₂ n 回の読み取りで、5,000 鍵なら最大 13 プローブを意味します

ルックアップが実は変装したビジネスルールのとき、たとえば例外付きの段階レートのときは、組み込み関数のどんな配置よりも、自分の Pascal コードへのコールバックのほうが通常明快です。カスタムワークシート関数の記事が登録方法を示します。ここで記述した XLOOKUP、XMATCH、数式エンジンの残りは、HotXLS Delphi スプレッドシートコンポーネントの一部であり、その製品ページは Delphi と C++Builder 向けの対応ワークシート関数を列挙しています