XLOOKUP- und XMATCH-Binärsuchmodi in Delphi

Über den Keys 1, 3, 5, 5, 5, 7, 9 in A1:A7 liefert die Formel XMATCH(5, A1:A7, 0, 2) eine 3. Nur das letzte Argument auf -1 geändert, liefert dieselbe Formel eine 5. Richtet man die erste Version auf eine Spalte, die jemand nach einem anderen Feld neu sortiert hat, kann eine 5, die deutlich auf dem Sheet sichtbar ist, als #N/A zurückkommen. Alle drei Antworten sind korrekt. Das letzte Argument von XMATCH ist wie das sechste Argument von XLOOKUP zwei Dinge zugleich: eine Anweisung, welchen Duplikat man bevorzugt, und ein Versprechen, wie die Daten sortiert sind

Der Report, der hierher führt, erwähnt Suchmodi selten. Er sagt, eine generierte Mappe weiche auf einer Handvoll Zeilen von dem ab, was jemand erwartete, und diese Zeilen haben etwas gemeinsam: ein wiederholter Key, eine Näherungsübereinstimmung, die einen Nachbarn wählen musste, oder eine Lookup-Spalte, deren Reihenfolge sich änderte, nachdem die Formel geschrieben war. Die Abschnitte unten gehen die Regeln einzeln durch, mit dem exakten Ergebnis, das jede Formel produziert

Welche match_mode- und search_mode-Werte akzeptiert XLOOKUP?

HotXLS, die native Delphi- und C++Builder-Spreadsheet-Komponente, wertet XLOOKUP und XMATCH durch eine gemeinsame Lookup-Routine aus, die exakt vier Match-Modi und vier Suchmodi akzeptiert. match_mode darf 0 (exakt), -1 (exakt oder nächstkleiner), 1 (exakt oder nächstgrößer) oder 2 (Wildcard) sein. search_mode darf 1 (Scan von vorne nach hinten), -1 (Scan von hinten nach vorne), 2 (Binärsuche über aufsteigende Daten) oder -2 (Binärsuche über absteigende Daten) sein. Jede andere Zahl gibt #VALUE! zurück; nichts wird in den nächstgelegenen legalen Modus gequetscht. Lässt man die Argumente weg, sind Match-Modus 0 und Suchmodus 1 gewählt, was die meisten handgeschriebenen Formeln nutzen

Die Argumentanzahl wird vor allem anderen geprüft. XLOOKUP nimmt drei bis sechs Argumente, XMATCH zwei bis vier; ein Aufruf außerhalb dieser Bereiche ist ein #VALUE!, bevor die Auswertung beginnt. Die Modus-Argumente selbst sind gewöhnliche Worksheet-Ausdrücke; HotXLS konvertiert jedes in eine Zahl, gibt #VALUE! für NaN, Unendlich oder einen Wert außerhalb des 32-Bit-Integer-Bereichs zurück und stutzt dann Richtung null. Aus 1.5 wird Suchmodus 1, aus -1.5 wird -1, und aus 2.9 wird 2. Das Stutzen beißt zu, wenn der Modus aus einer per Arithmetik berechneten Zelle kommt: Ein Wert, der als 2 angezeigt, aber als 1.9999999 gespeichert ist, wählt einen linearen Scan. Schreiben Sie den Modus als Literal oder wickeln Sie einen berechneten in ROUND

Das folgende Programm schreibt eine kleine Tarif-Tabelle und fünf Lookups, die diese Regeln austesten. Die Kommentare geben den Wert an, den jede Formelzelle nach Recalculate hält, abgeleitet aus den Regeln oben

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;

    // Defaults: match_mode 0, search_mode 1
    Sheet.Cells[1, 4].Formula := 'XLOOKUP(30,A1:A4,B1:B4)';               // 0.15
    // Exakt oder nächstkleiner, Binärsuche über aufsteigende Keys
    Sheet.Cells[2, 4].Formula := 'XLOOKUP(35,A1:A4,B1:B4,"none",-1,2)';   // 0.15
    // 1.5 wird zu search_mode 1 gestutzt
    Sheet.Cells[3, 4].Formula := 'XLOOKUP(40,A1:A4,B1:B4,"none",0,1.5)';  // 0.10
    // 3 ist kein Suchmodus
    Sheet.Cells[4, 4].Formula := 'XLOOKUP(40,A1:A4,B1:B4,"none",0,3)';    // #VALUE!
    // Wildcard-Matching kann keine Binärsuche fahren
    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;

Warum verfehlt search_mode 2 einen Key, der auf dem Sheet liegt?

Suchmodus 2 sagt HotXLS, dass der Lookup-Vektor bereits aufsteigend sortiert ist, und die Engine glaubt es. Die Behauptung zu prüfen, würde einen vollen Durchlauf über die Spalte kosten, genau jene Kosten, deren Vermeidung die Binärsuche existiert; der Abstieg halbiert also das Intervall bei jedem Probe und liefert, wo immer er endet. Microsoft dokumentiert denselben Vertrag für XLOOKUP und XMATCH: Die Binärmodi verlangen sortierte Daten und liefern sonst ungültige Ergebnisse. ISO/IEC 29500-1 §18.17, die SpreadsheetML-Formelklausel, beschreibt die älteren LOOKUP und VLOOKUP mit ihrer eigenen Aufsteigend-Anforderung; XLOOKUP und XMATCH kamen später und werden in der Datei als _xlfn.XLOOKUP und _xlfn.XMATCH unter dem Future-Function-Präfix gespeichert

Die Abbildung verfolgt XLOOKUP(40, A1:A3, B1:B3, "missing", 0, 2) über die Keys 40, 10, 30. Der erste Probe landet auf Zeile 2, liest 10, und da 10 unter 40 liegt, verwirft er die Zeilen 1 und 2, eingeschlossen der Zeile mit der 40. Zeile 3 liest 30, ebenfalls unter 40, und das Intervall ist leer. Die Formel gibt "missing" zurück. Mit Suchmodus 1 findet derselbe Aufruf die 40 in Zeile 1

„Aufsteigend“ bedeutet auch etwas Spezifischeres, als es auf dem Bildschirm aussieht. Der binäre Pfad rankt Werte nach Art, bevor er sie vergleicht: zuerst Zahlen, dann Text, dann Booleans, dann Fehlerwerte, dann Blanks. Eine Spalte numerischer Teilenummern, in der drei Zellen die Nummern als Text halten, ist unter dieser Regel nicht aufsteigend, so ordentlich sie auch aussieht. Text wird ohne Rücksicht auf Groß-/Kleinschreibung verglichen, und unter Windows kommt die Ordnung über CompareStringW aus dem User-Locale, das Bindestriche und akzentuierte Buchstaben anders behandelt als eine Byte-Order-Sortierung. Eine von einer Datenbank mit binärer Kollation sortierte Key-Spalte kann das Ordnungsversprechen also genau bei den Codes brechen, die Zeichensetzung enthalten

Blanks, die zuletzt rangieren, haben eine einseitige Konsequenz. Eine aufsteigende Key-Spalte mit leeren Zeilen darunter ist weiterhin aufsteigend; Suchmodus 2 auf einen überdimensionierten Bereich wie A2:A5001 zu richten, ist also harmlos. Eine absteigende Spalte, auf die leere Zeilen folgen, ist nicht mehr absteigend, und Suchmodus -2 läuft schnurgerade in die Blanks hinein

var
  R: TXLSFormulaEvaluationResult;
begin
  // B1:B3 hält 9, 7, 5; B4:B10 sind leer
  R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B10,0,-2)');
  // R.Status = xlfeOk und VarIsError(R.Value): das Ergebnis ist #N/A.
  // Der erste Probe liest das leere B5, Blanks rangieren über jeder Zahl,
  // also wandert der Abstieg weiter nach rechts und kommt nie zurück

  // Bereich auf die gefüllten Zeilen stutzen, und derselbe Aufruf funktioniert
  R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B3,0,-2)');     // 3

  // Ein linearer Scan überspringt die Blanks und findet den Key, wo immer er ist
  R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B10,0,-1)');    // 3
end;

Wo landen duplizierte Schlüssel?

XMATCH und XLOOKUP landen an einem festen Ende eines Duplikat-Laufs, und der Suchmodus wählt das Ende. Unter Suchmodus 2 notiert der Binärabstieg jeden gleichwertigen Key, dem er begegnet, und verengt weiter nach links; das Ergebnis ist der niedrigste Index des Laufs. Unter Suchmodus -2, über absteigende Daten, notiert er die Position und verengt nach rechts, was den höchsten Index gibt. Die linearen Modi liefern den ersten Treffer in Scanrichtung: den niedrigsten Index für Suchmodus 1, den höchsten für Suchmodus -1

Deshalb häufen sich die Diskrepanz-Reports auf bestimmten Zeilen. Eine Beispieldatei mit eindeutigen Keys liefert unter allen vier Suchmodi dieselbe Antwort, jeder daraus gebaute Test besteht also. Sobald Produktionsdaten einen Kundencode wiederholen, fangen die Modi an, genau auf den wiederholten Zeilen zu streiten, und der Code, der die Formeln schrieb, hat sich überhaupt nicht geändert

// A1:A7 hält 1, 3, 5, 5, 5, 7, 9 (aufsteigend, ein Lauf von drei)
Sheet.Cells[1, 3].Formula := 'XMATCH(5,A1:A7,0,1)';   // 3, erster Treffer vorwärts
Sheet.Cells[2, 3].Formula := 'XMATCH(5,A1:A7,0,-1)';  // 5, erster Treffer rückwärts
Sheet.Cells[3, 3].Formula := 'XMATCH(5,A1:A7,0,2)';   // 3, niedrigster Index des Laufs

// B1:B7 hält 9, 7, 5, 5, 5, 3, 1 (absteigend)
Sheet.Cells[4, 3].Formula := 'XMATCH(5,B1:B7,0,-2)';  // 5, höchster Index des Laufs

Wie die Näherungsübereinstimmung den Zweitplatzierten wählt

Wenn keine Zelle dem Lookup-Wert gleicht, liefert Match-Modus -1 den größten Wert, der nicht größer als das Ziel ist, und Match-Modus 1 den kleinsten Wert, der nicht kleiner ist. In den linearen Modi scannt HotXLS den ganzen Vektor und hält einen besten Kandidaten fest; Näherungsübereinstimmung funktioniert also auch auf unsortierten Daten. Eine Zelle verdrängt den amtierenden Kandidaten nur, wenn sie strikt näher ist, nie, wenn sie nur gleichzieht. Unter mehreren Zellen mit demselben Zweitplatzierten-Wert bleibt die erste, der in Scanrichtung begegnet wird

Nehmen wir die Keys 10, 30, 20, 30, 50, 40 in A1:A6. XMATCH(35, A1:A6, -1, 1) liefert 2: Die erste 30 wird zum Kandidaten, und die zweite 30 kommt nur an sie heran. Dreht man den Scan mit XMATCH(35, A1:A6, -1, -1) um, ist das Ergebnis 4, denn der Rückwärtsscan begegnet der anderen 30 zuerst. XMATCH(35, A1:A6, 1, 1) liefert 6, da 40 als kleinster Wert über 35 die 50 schlägt. Auf sortierten Daten erreichen die Binärmodi denselben Nachbarn mit einer Handvoll Probes: Über 10, 20, 30, 40, 50 liefert XMATCH(35, A1:A5, -1, 2) eine 3 und XMATCH(35, A1:A5, 1, 2) eine 4

Stellt sich weder ein exakter Treffer noch ein akzeptabler Nachbar ein, gibt XLOOKUP sein if_not_found-Argument zurück, wenn eines geliefert wurde, sonst #N/A. XMATCH hat kein Fallback-Argument und liefert immer #N/A

Warum Platzhalter und Binärsuche nicht koexistieren können

Ein Wildcard-Muster hat keine Position in einer Sortierordnung. Match-Modus 2 fragt, ob eine Zelle auf eine Maske passt, eine Ja-oder-Nein-Frage, während ein Binärabstieg eine dreiteilige Antwort braucht, die sagt, welche Hälfte bleiben soll. Es gibt keinen vertretbaren Weg zu sagen, ob ACME-* links oder rechts einer gegebenen Zelle liegt; HotXLS gibt für Match-Modus 2 kombiniert mit Suchmodus 2 oder -2 deshalb #VALUE! zurück und rät nicht. Nutzen Sie Suchmodus 1 oder -1 mit Wildcards und akzeptieren Sie die linearen Kosten

Match-Modus 2 wendet immer die Wildcard-Regeln an: * steht für eine beliebige Zeichenfolge, ? für ein Zeichen, und ~ macht das nächste Zeichen literal. XLOOKUP("PN~*", ...) findet also den Text PN* selbst, was zählt, wenn Produktcodes wirklich Asterisken enthalten. In Match-Modus 0 vergleicht der lineare Scan Text case-insensitiv, und der binäre Pfad behandelt zwei Werte als gleich, wenn sein Ordnungsvergleich ein Unentschieden meldet. Auch dieser Vergleich ist case-insensitiv, aber es ist dieselbe locale-bewusste Ordnung von oben, die einzige Relation, die eine Binärsuche legitim nutzen darf. Lookup-lastige Sheets bestimmen außerdem, was das Dependency-Tracking hinter inkrementeller Neuberechnung nach einem Edit neu auswerten muss

Formfehler und verschüttete Return-Slices

Beide Funktionen brauchen einen eindimensionalen Lookup-Bereich. Ein Bereich, der zugleich mehr als eine Zeile und mehr als eine Spalte überspannt, gibt #VALUE! zurück; HotXLS wählt keine Achse für Sie. XLOOKUP ergänzt eine zweite Regel: Der Return-Bereich muss entlang der Match-Achse dieselbe Länge haben wie der Lookup-Bereich. Ein vertikaler Lookup über 500 Zeilen, gepaart mit einem 499-Zeilen-Return-Bereich, ist ein Fehler, und die letzte Zeile wird nie stillschweigend fallen gelassen. Ist der Lookup-Bereich eine einzelne Zelle, entscheidet die Form des Return-Bereichs, ob der Lookup als vertikal oder horizontal zählt

Ein Return-Bereich, der breiter als eine Spalte ist, lässt XLOOKUP bei einem vertikalen Lookup die ganze gematchte Zeile als Array zurückgeben. Sie verschüttet in die Nachbarzellen unter denselben Regeln wie die anderen Dynamic-Array-Funktionen, behandelt im Artikel zu Spill-Bereichen und dynamischen Arrays. Leere Zellen innerhalb eines verschütteten Slices kommen als 0 zurück. Einen kompletten Datensatz mit einer Formel aus einer Tabelle zu ziehen ist bequem, und es ist auch der schnellste Weg, eine Spalte zu überschreiben, die zufällig rechts von der Formel saß

Einen Modus wählen für Mappen, die niemand beobachtet

Serverseitige Generierung verdient eine strengere Policy als die interaktive Nutzung, denn kein Mensch schaut das Ergebnis an, bevor es rausgeht. Lassen Sie dort Match-Modus 0 mit Suchmodus 1 stehen: Der Match ist exakt, der Scan ignoriert die Zeilenreihenfolge, und ein Umsortieren des Sheets kann die Antwort nicht ändern. Suchmodus 2 gehört nur dorthin, wo derselbe Code die Ordnung erzeugt hat, im selben Lauf, über dieselbe Spalte. Eine Binärsuche über eine Spalte, die nach einem anderen Key sortiert ist, ist der billigste Weg, eine selbstbewusst falsche Zahl zu berechnen

Wenn Sie einen Lookup doch auf den Binärmodus umstellen, prüfen Sie die Ordnung selbst an der Stelle, an der Sie die Formel emittieren. Die Funktion unten ist nicht Teil von HotXLS; sie ist ein Guard für Ihren eigenen Code. Für viele exakte Lookups gegen eine Tabelle lässt die Workbook-Property RecalcLookupCache das Recalculate den ersten Scan eines Bereichs für spätere Lookups gegen ihn wiederverwenden. Der Cache schaltet sich selbst ab, wenn der Durchlauf OFFSET, INDIRECT, zirkuläre Referenzen oder What-If-Datentabellen enthält, und die Ergebnisse sind ohnehin identisch

// Ihr eigener Guard, keine HotXLS-API: bestätigen Sie, dass eine numerische Key-Spalte
// aufsteigend ist, bevor Sie eine search_mode-2-Formel emittieren, die sich darauf verlässt
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;  // Text, leer oder außer der Ordnung
    Prev := Cur;
  end;
  Result := True;
end;

// Rates-Sheet: Keys in A2:A5001, Tarife in 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]);

Der Guard ist absichtlich konservativ: Eine einzige leere oder Textzelle schickt die ganze Spalte zurück auf den linearen Pfad. Auf einer sortierten, heißen Spalte ist der Ertrag des Binärmodus real, etwa log₂ n Lesezugriffe pro Lookup statt bis zu n, was bei 5.000 Keys höchstens 13 Probes bedeutet

Wenn ein Lookup in Wahrheit eine verkleidete Geschäftsregel ist, etwa ein Staffeltarif mit Ausnahmen, ist ein Callback in den eigenen Pascal-Code meist klarer als jede Anordnung von Built-in-Funktionen; der Artikel zu eigenen Worksheet-Funktionen zeigt, wie man eine registriert. XLOOKUP, XMATCH und der Rest der hier beschriebenen Formel-Engine sind Teil der HotXLS Delphi spreadsheet component, deren Produktseite die unterstützten Worksheet-Funktionen für Delphi und C++Builder auflistet