Delphi 中 XLOOKUP 與 XMATCH 的二進位搜尋模式

對 A1:A7 裡的鍵值 1, 3, 5, 5, 5, 7, 9，公式 XMATCH(5, A1:A7, 0, 2) 回傳 3。只把最後一個引數改成 -1，同一條公式回傳 5。把第一種寫法指向一欄被人按別的欄位重新排序過的資料，明明就在工作表上的 5，卻可能回來變成 #N/A。三個答案都對。XMATCH 的最後一個引數，一如 XLOOKUP 的第六個引數，同時是兩件事：一則關於偏好哪個重複值的指示，以及一則關於資料如何排序的承諾

把人帶到這裡的報告很少提到搜尋模式。它說的是：某份產生出來的活頁簿在少數幾列上與某人的預期不一致，而這些列有共同點：一個重複的鍵、一次不得不挑鄰居的近似相符，或一欄在公式寫好之後才被改變順序的查閱欄。以下各節一次講一條規則，並附上每條公式產生的確切結果

XLOOKUP 接受哪些 match_mode 與 search_mode 值？

HotXLS 這個原生 Delphi 與 C++Builder 試算表元件，透過一條共用的查閱常式求值 XLOOKUP 與 XMATCH，它恰好接受四種相符模式與四種搜尋模式。match_mode 可以是 0（完全相符）、-1（完全相符或次小值）、1（完全相符或次大值）或 2（萬用字元）。search_mode 可以是 1（從頭掃到尾）、-1（從尾掃到頭）、2（對遞增資料做二進位搜尋）或 -2（對遞減資料做二進位搜尋）。其他任何數字回傳 #VALUE!；不會被夾進最接近的合法模式。省略引數時選的是 match mode 0 加 search mode 1，多數手寫公式用的就是這一組

引數個數最先檢查。XLOOKUP 收三到六個引數，XMATCH 收兩到四個，超出範圍的呼叫在求值開始前就是 #VALUE!。模式引數本身是一般的儲存格運算式，所以 HotXLS 把每個轉成數字，對 NaN、無限大或超出 32 位元整數範圍的值回傳 #VALUE!，然後向零截斷。1.5 變成 search mode 1，-1.5 變成 -1，2.9 變成 2。當模式來自算術算出來的儲存格時，截斷就會咬人：顯示為 2、實際存成 1.9999999 的值選到的是線性掃描。模式直接寫成字面值，或把算出來的包一層 ROUND

下面的程式寫出一張小型費率表與五個查閱公式，正好演練那些規則。註解給出 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 不是合法的搜尋模式
    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 查閱向量已經遞增排序，而引擎信了這句話。驗證這個宣稱得整欄掃一遍，恰恰是二進位搜尋存在要省掉的成本，所以下降過程每探測一次就把區間砍半，走到哪算哪。Microsoft 為 XLOOKUP 與 XMATCH 記錄了同一份契約：二進位模式要求資料已排序，否則結果無效。ISO/IEC 29500-1 §18.17——SpreadsheetML 公式條款——描述較舊的 LOOKUP 與 VLOOKUP 時也各有遞增順序要求；XLOOKUP 與 XMATCH 出現得晚，在檔案裡以未來函數前綴存成 _xlfn.XLOOKUP 與 _xlfn.XMATCH

這張圖追蹤 XLOOKUP(40, A1:A3, B1:B3, "missing", 0, 2) 對鍵值 40, 10, 30 的過程。第一次探測落在第 2 列，讀到 10，因為 10 低於 40，它丟棄第 1、2 列——包括放著 40 的那一列。第 3 列讀到 30，同樣低於 40，區間清空。公式回傳 "missing"。同樣的呼叫換成 search mode 1，就能在第 1 列找到 40

「遞增」在螢幕上看起來的樣子，也比它實際的意義寬鬆。二進位路徑在比較之前先按值的種類排高低：數字最前，然後文字，然後布林，然後錯誤值，最後空白。一欄數字型的料號編碼，其中三個儲存格把編碼存成文字，在這條規則下就不是遞增，不管看起來多整齊。文字比較不分大小寫，在 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 2 下，二進位下降會記下遇到的每個相等鍵並持續向左收窄，所以結果是這一串重複的最低索引。Search mode -2 對遞減資料則記下位置並向右收窄，給出最高索引。線性模式回傳掃描方向上的第一個命中：search mode 1 是最低索引，search mode -1 是最高索引

這就是為什麼不一致報告總聚在某些列上。鍵值唯一的樣本檔案在四種搜尋模式下給出相同答案，所以從它建出的每個測試都過。一旦正式資料重複了某個客戶編碼，各模式恰恰在重複的那些列上開始各說各話，而寫公式的程式碼一個字都沒改

// A1:A7 存放 1, 3, 5, 5, 5, 7, 9 - 遞增，含三個連續值
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 成為候選，第二個 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 問的是某個儲存格合不合遮罩，是非題；二進位下降需要的是指出該留哪一半的三選一答案。要說 ACME-* 落在某個儲存格的左邊還是右邊，沒有任何站得住腳的說法，所以 HotXLS 對 match mode 2 搭配 search mode 2 或 -2 的組合直接回傳 #VALUE!，不瞎猜。用萬用字元就配 search mode 1 或 -1，接受線性成本

Match mode 2 永遠套用萬用字元規則：* 代表任意一串字元，? 代表一個字元，~ 讓下一個字元按字面處理。所以 XLOOKUP("PN~*", ...) 找的是 PN* 這串文字本身——產品編碼真的帶星號時這就很要緊。Match mode 0 的線性掃描不分大小寫地比較文字；二進位路徑在其順序比較回報平手時視兩值相等。那個比較同樣不分大小寫，但它是前述同一種語系感知順序，而這正是二進位搜尋唯一能正當使用的關係。查閱密集的工作表也決定著增量重算背後的依賴追蹤在編輯之後得重算什麼

形狀錯誤與溢出的傳回範圍

兩個函式都需要一維的查閱範圍。同時跨多列又多欄的範圍回傳 #VALUE!；HotXLS 不替你挑軸。XLOOKUP 多一條規則：傳回範圍在比對軸向上的長度必須與查閱範圍相同。500 列的垂直查閱配上 499 列的傳回範圍就是錯誤，最後一列絕不會被悄悄丟掉。查閱範圍是單一儲存格時，傳回範圍的形狀決定這次查閱算垂直還是水平

垂直查閱配上一欄以上的傳回範圍，XLOOKUP 會把整個匹配列當陣列交回來，並按與其他動態陣列函式相同的規則溢入相鄰儲存格，詳見溢出範圍與動態陣列一文。溢出切片裡的空儲存格回傳 0。一條公式撈出整筆記錄很方便，但這也是蓋掉恰好排在公式右邊那一欄的最快方法

給沒人盯著的活頁簿挑模式

伺服器端產生值得比互動使用更嚴的規矩，因為出貨之前沒有人看結果。那裡維持 match mode 0 加 search mode 1：比對是完全相符，掃描不看列順序，重新排序工作表也改變不了答案。Search mode 2 只該用在同一段程式碼、同一輪執行、對同一欄造出順序的地方。對一欄按別的鍵排好的欄做二進位搜尋，是算出一個自信錯誤數字的最便宜方法

真要把某個查閱切到二進位模式，就在寫出公式的那個點自己檢查順序。下面的函式不是 HotXLS 的一部分，是加在你自己程式碼裡的防護。對同一張表的多次完全相符查閱，活頁簿屬性 RecalcLookupCache 能讓 Recalculate 把第一次掃描重用給後續查閱。掃描過程含 OFFSET、INDIRECT、循環參照或 What-If 資料表時，快取會自行關閉，兩種方式的結果相同

// Your own guard, not a HotXLS API: confirm a numeric key column is
// 遞增，再寫出依賴它的 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]);

這個防護刻意保守：一個空白或文字儲存格就把整欄打回線性路徑。在已排序的熱點欄上，二進位模式的收益是真的：每次查閱約 log₂ 次讀取，而不是最多 n 次——5000 個鍵意味著至多 13 次探測

當查閱其實是喬裝的業務規則——例如帶例外的級距費率——回呼到你自己的 Pascal 程式碼通常比任何內建函式排列都清楚；自訂工作表函數一文示範怎麼註冊一個。XLOOKUP、XMATCH 與本文描述的其餘公式引擎都屬於 HotXLS Delphi 試算表元件，其產品頁列出 Delphi 與 C++Builder 支援的工作表函數