HotXLS, die native Delphi- und C++Builder-Tabellenkalkulationskomponente, wertet XLOOKUP und XMATCH über einen gemeinsamen Lookup-Kern aus. Dieser Kern akzeptiert vier Match-Modi (-1, 0, 1, 2) und vier Suchmodi (-2, -1, 1, 2), führt einen logarithmischen Binärabstieg aus, sobald der absolute Suchmodus 2 ist, und weist jede andere Kombination mit einem Formelfehler zurück
Der Bugreport, der einen hierher führt, sagt nie "Suchmodus". Er sagt, die serverseitig erzeugte Arbeitsmappe zeige eine andere Zahl als dieselbe Datei in Excel geöffnet, auf vielleicht vier von neuntausend Zeilen. Diese vier Zeilen haben immer etwas gemeinsam: einen duplizierten Lookup-Schlüssel, oder eine Näherungsübereinstimmung, die einen Nachbarn wählen musste, oder eine Lookup-Spalte, die jemand letzte Woche nach einer anderen Spalte sortiert hat. Lookup-Funktionen sind der Punkt, an dem eine Formel-Engine aufhört, bloße Arithmetik zu sein, und zu einem Vertrag wird, und der Vertrag hat Klauseln, die die wenigsten Aufrufer je lesen
Welche Modus-Nummern akzeptiert XLOOKUP tatsächlich?
Genau vier von jedem, und sonst nichts. HotXLS validiert match_mode gegen -1, 0, 1 und 2 sowie search_mode gegen -2, -1, 1 und 2, bevor es auch nur eine Zelle berührt, und jeder andere Wert liefert #VALUE! statt auf den nächstgelegenen legalen Modus geklemmt zu werden. Die vier Match-Modi sind 0 für exakt, -1 für exakt oder nächstkleiner, 1 für exakt oder nächstgrößer und 2 für Platzhalter; die vier Suchmodi sind 1 für einen vorwärtslinearen Scan, -1 für einen rückwärtslinearen Scan, 2 für eine Binärsuche über aufsteigende Daten und -2 für eine Binärsuche über absteigende Daten. Werden sie weggelassen, wählt das Match-Modus 0 und Suchmodus 1, die Paarung, die fast jede echte Formel nutzt. Argumentanzahlen werden ebenso überwacht: XLOOKUP nimmt drei bis sechs Argumente und XMATCH zwei bis vier, und alles außerhalb dieser Bereiche ist ein #VALUE!, bevor die Auswertung überhaupt beginnt
// Shared by XLOOKUP and XMATCH, before any cell is read
if ((RequestedMatchMode <> -1) and (RequestedMatchMode <> 0) and
(RequestedMatchMode <> 1) and (RequestedMatchMode <> 2)) or
((RequestedSearchMode <> -2) and (RequestedSearchMode <> -1) and
(RequestedSearchMode <> 1) and (RequestedSearchMode <> 2)) then
begin
Result := lxErrorValue; // #VALUE!
Exit;
end;
if Abs(RequestedSearchMode) = 2 then
begin
if RequestedMatchMode = 2 then // wildcards cannot ride a binary descent
begin
Result := lxErrorValue;
Exit;
end;
// ... O(log n) descent over the lookup vector
end;Einen Schritt früher gibt es eine leisere, wissenswerte Prüfung. Die Modus-Argumente kommen als Arbeitsblattausdrücke an, sodass HotXLS sie in eine Zahl zwingt, NaN und Unendlich zurückweist und dann verlangt, dass die Zahl ihrem eigenen gerundeten Wert entspricht. XLOOKUP(x, A:A, B:B, "none", 0, 1.5) ist ein #VALUE!, kein verkappter Suchmodus 2. Das ist relevant, wenn der Modus aus einer Zelle stammt, die eine rundungslastige Berechnung erzeugt hat, was in generierten Arbeitsmappen häufiger vorkommt als in handgeschriebenen
Warum liefert search_mode 2 auf unsortierten Daten das falsche Ergebnis?
Weil es genau das tut, worum man gebeten hat. Suchmodus 2 sagt der Engine, dass der Lookup-Vektor bereits aufsteigend sortiert ist, und eine Binärsuche kann diese Behauptung nicht ohne einen O(n)-Durchlauf verifizieren, der den Grund für ihre Verwendung zunichtemachen würde. HotXLS vertraut dem Aufrufer daher, halbiert das Intervall und liefert zurück, wo auch immer der Abstieg landet. Bei unsortierter Eingabe ist die Antwort kein Fehler, sie ist still falsch, und das ist ein Vertragsbruch, kein Defekt der Engine
Microsoft dokumentiert dieselbe Asymmetrie für XLOOKUP und XMATCH: Die Binärmodi erfordern sortierte Daten und liefern andernfalls ungültige Ergebnisse. ISO 29500-1 Klausel 18.17, die die SpreadsheetML-Formelgrammatik definiert, trägt die älteren Beschreibungen von LOOKUP und VLOOKUP mit ihrer eigenen Anforderung aufsteigender Reihenfolge, und XLOOKUP und XMATCH datieren nach diesem Text weit genug, dass sie in der Datei als _xlfn.XLOOKUP und _xlfn.XMATCH nach der Future-Function-Konvention mitgeführt werden. Andere Generation, derselbe Deal: Der Aufrufer liefert die Ordnungsinvariante, die Engine liefert den Logarithmus
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Rates');
Sheet.Cells[1, 1].Value := 40; Sheet.Cells[1, 2].Value := 0.10;
Sheet.Cells[2, 1].Value := 10; Sheet.Cells[2, 2].Value := 0.25;
Sheet.Cells[3, 1].Value := 30; Sheet.Cells[3, 2].Value := 0.15;
// Forward linear scan: finds key 40 wherever it sits
Sheet.Cells[5, 1].Formula := 'XLOOKUP(40,A1:A3,B1:B3,"missing",0,1)';
// Binary ascending: the promise was broken, the key is never visited
Sheet.Cells[6, 1].Formula := 'XLOOKUP(40,A1:A3,B1:B3,"missing",0,2)';
Book.SaveAs('lookup-modes.xlsx');
finally
Book.Free;
end;
end;Verfolgt man die zweite Formel, ist der Fehlschlag vollkommen mechanisch. Der Abstieg sondiert die mittlere Zelle, liest 10, entscheidet, dass 10 kleiner als 40 ist, verwirft die linke Hälfte einschließlich der Zeile, die tatsächlich 40 hielt, sondiert 30, verwirft erneut, und dem Intervall geht der Raum aus. Excel verhält sich genauso, und genau das ist der Punkt: Die falsche Antwort zu reproduzieren, ist eine Kompatibilitätsanforderung, keine Höflichkeit. Die Ordnungsprämisse ist zudem strenger als "Zahlen aufsteigend", weil der Comparator Werte zuerst nach Art ordnet, in der Reihenfolge Zahlen, dann Text, dann Booleans, dann Fehlerwerte, dann Leerfelder, und erst danach innerhalb einer Art vergleicht. Eine Spalte mit numerischen Teilecodes, die drei Zellen mit Text statt Zahlen enthält, ist unter diesem Comparator nicht aufsteigend, egal wie es auf dem Bildschirm aussieht, und die Binärmodi lesen sie bereitwillig falsch
Wo landen duplizierte Schlüssel?
An einem deterministischen Ende des Duplikat-Laufs, und welches Ende hängt vom Suchmodus ab, nicht vom Zufall. Trifft der Binärabstieg unter Suchmodus 2 auf einen gleichen Schlüssel, merkt er sich die Position und verengt dann weiter nach links, sodass das Ergebnis der niedrigste Index des Laufs ist; unter Suchmodus -2, über absteigende Daten, merkt er sich die Position und verengt nach rechts, sodass das Ergebnis der höchste Index ist. Die linearen Modi sind einfacher: Suchmodus 1 liefert den ersten Treffer vorwärts, Suchmodus -1 den ersten Treffer rückwärts. Das ist das Detail, das die Vier-Zeilen-Diskrepanz aus dem Eingangsabsatz erzeugt, denn eine Arbeitsmappe, deren Schlüssel eindeutig sind, liefert unter allen vier Suchmodi identische Antworten und verbirgt den Unterschied in jedem Test, den man aus einer sauberen Beispieldatei geschrieben hat. Fügt man den Produktionsdaten einen doppelten Kundencode hinzu, beginnen die Modi genau bei den duplizierten Zeilen uneins zu sein: In der Engine hat sich nichts geändert, die Eingabe hat lediglich aufgehört, eine Menge zu sein, und ist zu einer Multimenge geworden
// A1:A7 holds 1, 3, 5, 5, 5, 7, 9 - ascending, with a run of three
Sheet.Cells[1, 3].Formula := 'XMATCH(5,A1:A7,0,1)'; // 3, first forward hit
Sheet.Cells[2, 3].Formula := 'XMATCH(5,A1:A7,0,-1)'; // 5, first reverse hit
Sheet.Cells[3, 3].Formula := 'XMATCH(5,A1:A7,0,2)'; // 3, lowest index of the run
// B1:B7 holds 9, 7, 5, 5, 5, 3, 1 - descending
Sheet.Cells[4, 3].Formula := 'XMATCH(5,B1:B7,0,-2)'; // 5, highest index of the runWie wählt eine Näherungsübereinstimmung den Zweitplatzierten?
Indem neben der Suche nach exaktem Treffer ein bester Kandidat mitgeführt wird und dieser nur zurückgegeben wird, wenn kein exakter Treffer auftritt. HotXLS behandelt match_mode -1 als "der größte Wert, der das Ziel nicht übersteigt" und match_mode 1 als "der kleinste Wert, der nicht kleiner ist", und beide werden über den gesamten gescannten Bereich aufgelöst, statt beim ersten akzeptablen Nachbarn zu stoppen. Im Binärpfad ergibt sich dieselbe Idee kostenlos aus dem Abstieg: Jeder Schritt, der über- oder unterschießt, aktualisiert den Kandidaten, sodass der endgültige Kandidat das Grenzelement neben der Position ist, an der der Schlüssel eingefügt worden wäre
// Linear path: refine the candidate only on a strict improvement
if (RequestedMatchMode = -1) or (RequestedMatchMode = 1) then
begin
CompareResult := CompareDynamicValues(CurrentValue, RequestedValue);
if ((RequestedMatchMode = -1) and (CompareResult <= 0) and
((CandidateIndex < 0) or
(CompareDynamicValues(CurrentValue, CandidateValue) > 0))) or
((RequestedMatchMode = 1) and (CompareResult >= 0) and
((CandidateIndex < 0) or
(CompareDynamicValues(CurrentValue, CandidateValue) < 0))) then
begin
CandidateIndex := ScanIndex;
CandidateValue := CurrentValue;
end;
end;Man lese die innere Bedingung genau, denn dort lebt die Entscheidung bei Gleichstand. Eine neue Zelle ersetzt den bisherigen Kandidaten nur, wenn sie strikt besser ist, nie wenn sie ihm bloß gleicht, sodass unter mehreren Zellen mit demselben Zweitplatzierten-Wert die zuerst in Scan-Reihenfolge angetroffene erhalten bleibt: der niedrigste Index bei Vorwärtsscan, der höchste bei Rückwärtsscan. Finden XLOOKUP und XMATCH weder einen exakten Treffer noch einen akzeptablen Nachbarn, fällt XLOOKUP auf sein if_not_found-Argument zurück, sofern eines angegeben wurde, und auf #N/A, wenn nicht, während XMATCH stets #N/A liefert
Warum Platzhalter und Binärsuche nicht koexistieren können
Weil ein Platzhaltermuster keine Position in einer Ordnung ist. Match-Modus 2 fragt, ob eine Zelle zu einer Maske passt, und Maskenabgleich beantwortet Ja oder Nein; ein Binärabstieg braucht eine dreiwertige Antwort, die ihm sagt, welche Hälfte er behalten soll. Es gibt keine vertretbare Möglichkeit zu fragen, ob ACME-* links oder rechts von einer gegebenen Zelle liegt, sodass HotXLS match_mode 2 kombiniert mit search_mode 2 oder -2 von vornherein mit #VALUE! zurückweist, statt eine Ordnung zu erraten und plausiblen Unsinn zu erzeugen. Die beiden Pfade vergleichen Werte zudem unterschiedlich, was die Trennung untermauert: Der lineare Scan entscheidet Gleichheit mit einem Text-Vergleich ohne Berücksichtigung von Groß-/Kleinschreibung, oder mit Maskenabgleich, wenn Platzhalter aktiv sind, während der Binärabstieg Gleichheit entscheidet, indem er den Ordnungs-Comparator nach einer Null fragt. Das ist beabsichtigt und kein Zufall der Schichtung, denn der Binärpfad darf nur die Relation nutzen, entlang der er tatsächlich navigiert. Wer Platzhalter braucht, nutzt Suchmodus 1 oder -1 und akzeptiert die linearen Kosten, denselben Kompromiss, den die Abhängigkeitsverfolgung hinter der inkrementellen Neuberechnung vom eigenen kritischen Pfad fernhalten soll
Formfehler: zweidimensionale Bereiche und nicht passende Rückgabevektoren
Beide Funktionen erfordern einen echt eindimensionalen Lookup-Bereich. Umfasst der angegebene Bereich gleichzeitig mehr als eine Zeile und mehr als eine Spalte, liefert HotXLS #VALUE!, statt stellvertretend eine Achse zu wählen, und ein einzeiliger oder einspaltiger Bereich wird entlang seiner langen Achse gelesen. XLOOKUP fügt eine zweite Formregel hinzu: Der Rückgabebereich muss entlang der übereinstimmenden Achse exakt so lang sein wie der Lookup-Bereich, sodass ein vertikaler Lookup über 500 Zeilen gepaart mit einem 499-zeiligen Rückgabebereich ein Fehler ist, nicht ein an der letzten Zeile still aufgelöster Off-by-one. Ist der Rückgabebereich bei einem vertikalen Lookup breiter als eine Spalte, oder bei einem horizontalen höher als eine Zeile, gibt XLOOKUP das gesamte getroffene Segment als Array zurück, und es fließt in benachbarte Zellen über, nach denselben Regeln wie die anderen dynamischen Array-Funktionen, beschrieben im Artikel zu Spill-Bereichen und dynamischen Arrays. Das ist wirklich nützlich, um mit einer Formel einen ganzen Datensatz aus einer Tabelle zu ziehen, und es ist auch der schnellste Weg, eine Spalte zu überschreiben, die man eigentlich behalten wollte
Einen Modus wählen, wenn niemand auf den Bildschirm schaut
Serverseitige Erzeugung verdient eine strengere Policy als interaktive Nutzung, weil kein Mensch bemerkt, dass eine Summe falsch aussieht. Der vertretbare Standard ist Suchmodus 1 mit Match-Modus 0: linear, exakt, ordnungsunabhängig und unmöglich durch Neusortieren eines Blatts zu invalidieren. Man greift nur dann zu Suchmodus 2, wenn derselbe Codepfad auch die Sortierung erzeugt hat, im selben Lauf, über dieselbe Spalte, und schreibt diese Abhängigkeit neben die Formel, denn eine Binärsuche auf einer nach einem anderen Schlüssel sortierten Spalte ist der günstigste Weg, eine selbstsichere falsche Zahl zu berechnen. Ist der Lookup wirklich heiß und die Daten wirklich sortiert, ist der Gewinn real: Der Abstieg liest in der Größenordnung log n Zellen statt n, und jeder dieser Lesevorgänge durchläuft eine vollständige Zellauflösung der Arbeitsmappe, sodass die Ersparnis größer ist, als die Befehlszahl vermuten lässt
Ist die Form des Problems eher eine Domänenregel als ein Lookup, schlägt ein Rückruf in eigenen Pascal-Code, behandelt im Artikel zu benutzerdefinierten Arbeitsblattfunktionen, meist jede noch so clevere Anordnung der eingebauten Funktionen. Die hier besprochenen XLOOKUP- und XMATCH-Implementierungen sind Teil der standardmäßigen HotXLS-Delphi-Tabellenkalkulationskomponente, deren Produktseite die vollständige Referenz unterstützter Funktionen für Delphi und C++Builder enthält