HotXLS, het native Delphi- en C++Builder-spreadsheetcomponent, evalueert XLOOKUP en XMATCH via één gedeelde lookup-kern. Die kern accepteert vier matchmodi (-1, 0, 1, 2) en vier zoekmodi (-2, -1, 1, 2), draait een logaritmische binaire afdaling wanneer de absolute zoekmodus 2 is, en verwerpt elke andere combinatie met een formulefout
Het bugrapport dat je hierheen stuurt, zegt nooit "zoekmodus". Het zegt dat de server-gegenereerde werkmap een ander getal toont dan hetzelfde bestand geopend in Excel, op misschien vier rijen van de negenduizend. Die vier rijen hebben altijd iets gemeen: een gedupliceerde lookup-sleutel, of een benaderende match die een buur moest kiezen, of een lookup-kolom die iemand vorige week op een andere kolom sorteerde. Lookup-functies zijn waar een formule-engine ophoudt rekenkunde te zijn en een contract wordt, en het contract heeft clausules die de meeste aanroepers nooit lezen
Welke modusgetallen accepteert XLOOKUP eigenlijk?
Precies vier van elk, en niets anders. HotXLS valideert match_mode tegen -1, 0, 1 en 2 en search_mode tegen -2, -1, 1 en 2 voordat het ook maar één cel aanraakt, en elke andere waarde retourneert #VALUE! in plaats van geklemd te worden naar de dichtstbijzijnde legale modus. De vier matchmodi zijn 0 voor exact, -1 voor exact of eerstvolgende kleinere, 1 voor exact of eerstvolgende grotere, en 2 voor wildcard; de vier zoekmodi zijn 1 voor een lineaire scan voorwaarts, -1 voor een lineaire scan achterwaarts, 2 voor een binair zoeken over oplopende data, en -2 voor een binair zoeken over aflopende data. Weglaten selecteert matchmodus 0 en zoekmodus 1, de combinatie die bijna elke echte formule gebruikt. Argumentaantallen worden op dezelfde manier bewaakt: XLOOKUP neemt drie tot zes argumenten en XMATCH neemt twee tot vier, en alles buiten die bereiken is een #VALUE! voordat evaluatie begint
// 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;Eén stap eerder zit een stillere controle die het waard is om te kennen. De modusargumenten arriveren als werkbladexpressies, dus HotXLS dwingt ze om tot een getal, weigert NaN en oneindig, en eist vervolgens dat het getal gelijk is aan zijn eigen afgeronde waarde. XLOOKUP(x, A:A, B:B, "none", 0, 1.5) is een #VALUE!, geen zoekmodus 2 in vermomming. Dat doet ertoe wanneer de modus uit een cel komt die een afrondingsintensieve berekening produceerde, wat gewoner is in gegenereerde werkmappen dan in handgeschreven werkmappen
Waarom geeft search_mode 2 het verkeerde antwoord op ongesorteerde data?
Omdat het precies doet wat je vroeg. Zoekmodus 2 vertelt de engine dat de lookup-vector al in oplopende volgorde staat, en een binair zoeken kan die claim niet verifiëren zonder een O(n)-pas die de reden om het te gebruiken teniet zou doen. HotXLS vertrouwt daarom de aanroeper, halveert het interval, en retourneert wat de afdaling ook oplevert. Op ongesorteerde invoer is het antwoord geen fout, het is stilzwijgend fout, en dit is een contractschending, geen defect in de engine
Microsoft documenteert dezelfde asymmetrie voor XLOOKUP en XMATCH: de binaire modi vereisen gesorteerde data en produceren ongeldige resultaten wanneer die ontbreekt. ISO 29500-1 clausule 18.17, die de SpreadsheetML-formulegrammatica definieert, draagt de oudere beschrijvingen van LOOKUP en VLOOKUP met hun eigen vereiste van oplopende volgorde, en XLOOKUP en XMATCH dateren van ver genoeg na die tekst dat ze in het bestand meereizen als _xlfn.XLOOKUP en _xlfn.XMATCH onder de future-function-conventie. Andere generatie, dezelfde afspraak: de aanroeper levert de ordeninvariant, de engine levert de logaritme
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;Volg de tweede formule en het falen is volledig mechanisch. De afdaling test de middelste cel, leest 10, besluit dat 10 kleiner is dan 40, gooit de linkerhelft weg inclusief de rij die daadwerkelijk 40 bevatte, test 30, gooit opnieuw weg, en houdt geen interval meer over. Excel gedraagt zich hetzelfde, en dat is het punt: het verkeerde antwoord reproduceren is een compatibiliteitsvereiste, geen beleefdheid. De ordepremisse is ook strikter dan "getallen oplopend", omdat de vergelijker waarden eerst rangschikt op soort, in de volgorde getallen, dan tekst, dan booleans, dan foutwaarden, dan lege cellen, en pas daarna binnen een soort vergelijkt. Een kolom met numerieke onderdeelcodes waarin drie cellen tekst bevatten in plaats van getallen, is onder die vergelijker niet oplopend, ongeacht hoe het er op het scherm uitziet, en de binaire modi zullen die vrolijk verkeerd lezen
Waar landen duplicaatsleutels?
Op een deterministisch uiteinde van de duplicaatreeks, en welk uiteinde hangt af van de zoekmodus, niet van geluk. Wanneer de binaire afdaling een gelijke sleutel raakt onder zoekmodus 2, registreert hij de positie en blijft dan naar links versmallen, dus het resultaat is de laagste index van de reeks; onder zoekmodus -2, over aflopende data, registreert hij de positie en versmalt naar rechts, dus het resultaat is de hoogste index. De lineaire modi zijn eenvoudiger: zoekmodus 1 retourneert de eerste treffer voorwaarts, zoekmodus -1 de eerste treffer achterwaarts. Dit is het detail dat het verschil van vier rijen uit de openingsalinea produceert, omdat een werkmap waarvan de sleutels uniek zijn identieke antwoorden geeft onder alle vier de zoekmodi en het verschil verbergt door elke test die je vanuit een schoon voorbeeldbestand schreef. Voeg één gedupliceerde klantcode toe aan productiedata en de modi beginnen het oneens te zijn precies op de rijen die dupliceerden: er veranderde niets aan de engine, de invoer hield gewoon op een verzameling te zijn en werd een multiset
// 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 runHoe kiest een benaderende match de runner-up?
Door een beste kandidaat bij te houden naast het exacte-match-zoeken, en die alleen terug te geven wanneer geen exacte treffer verschijnt. HotXLS behandelt match_mode -1 als "de grootste waarde die niet groter is dan het doel" en match_mode 1 als "de kleinste waarde die niet kleiner is", en beide worden opgelost over het hele gescande gebied in plaats van te stoppen bij de eerste aanvaardbare buur. In het binaire pad valt hetzelfde idee gratis uit de afdaling: elke stap die overschiet of ondershoot werkt de kandidaat bij, dus de uiteindelijke kandidaat is het grenselement naast de positie waar de sleutel ingevoegd zou zijn
// 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;Lees de binnenste voorwaarde nauwkeurig, want daar zit de tiebreak. Een nieuwe cel vervangt de staande kandidaat alleen wanneer die strikt beter is, nooit wanneer die er slechts gelijk aan is, dus onder meerdere cellen die dezelfde runner-up-waarde bevatten, is degene die behouden blijft de eerste die in scanvolgorde tegengekomen wordt: de laagste index bij een voorwaartse scan, de hoogste bij een achterwaartse scan. Vindt XLOOKUP of XMATCH noch een exacte treffer noch een aanvaardbare buur, dan valt XLOOKUP terug op zijn argument if_not_found wanneer dat is opgegeven en op #N/A wanneer dat niet zo is, terwijl XMATCH altijd #N/A oplevert
Waarom kunnen wildcards en binair zoeken niet samengaan?
Omdat een wildcardpatroon geen positie in een orde is. Matchmodus 2 vraagt of een cel overeenkomt met een masker, en maskermatching antwoordt ja of nee; een binaire afdaling heeft een drieledig antwoord nodig dat vertelt welke helft te bewaren. Er is geen verdedigbare manier om te vragen of ACME-* links of rechts van een gegeven cel ligt, dus HotXLS verwerpt match_mode 2 gecombineerd met search_mode 2 of -2 vooraf met #VALUE! in plaats van een ordening te gokken en aannemelijk ogende onzin te produceren. De twee paden vergelijken waarden ook verschillend, wat de splitsing versterkt: de lineaire scan bepaalt gelijkheid met een hoofdletterongevoelige tekstvergelijking, of met maskermatching wanneer wildcards aan staan, terwijl de binaire afdaling gelijkheid bepaalt door de ordevergelijker om een nul te vragen. Dat is bewust, geen ongeluk van gelaagdheid, aangezien het binaire pad alleen de relatie mag gebruiken waarlangs het daadwerkelijk navigeert. Heb je wildcards nodig, gebruik dan zoekmodus 1 of -1 en accepteer de lineaire kost, dezelfde afweging die de afhankelijkheidsbewaking achter incrementele herberekening ontworpen is om van je kritieke pad te houden
Vormfouten: tweedimensionale bereiken en niet-overeenkomende returnvectoren
Beide functies vereisen een oprecht eendimensionaal lookup-bereik. Als het opgegeven bereik tegelijk meer dan één rij en meer dan één kolom omspant, retourneert HotXLS #VALUE! in plaats van namens jou een as te kiezen, en een bereik van één rij of één kolom wordt langs zijn lange as gelezen. XLOOKUP voegt een tweede vormregel toe: het returnbereik moet precies zo lang zijn als het lookup-bereik langs de overeenkomende as, dus een verticale lookup over 500 rijen gekoppeld aan een returnbereik van 499 rijen is een fout, geen off-by-one die stilzwijgend op de laatste rij wordt opgelost. Wanneer het returnbereik breder is dan één kolom voor een verticale lookup, of hoger dan één rij voor een horizontale, geeft XLOOKUP de hele gematchte slice terug als een array en die spilt naar de aangrenzende cellen onder dezelfde regels als de andere dynamische array-functies, beschreven in het artikel over spillbereiken en dynamische arrays. Dat is echt nuttig om een heel record uit een tabel te halen met één formule, en het is ook de snelste manier om een kolom te overschrijven die je wilde behouden
Een modus kiezen wanneer niemand naar het scherm kijkt
Server-side generatie verdient een strenger beleid dan interactief gebruik, omdat er geen mens is om op te merken dat een totaal er verkeerd uitziet. De verdedigbare standaard is zoekmodus 1 met matchmodus 0: lineair, exact, ordeonafhankelijk, en onmogelijk ongeldig te maken door een blad opnieuw te sorteren. Grijp alleen naar zoekmodus 2 waar hetzelfde codepad ook de ordening produceerde, in dezelfde run, over dezelfde kolom, en schrijf die afhankelijkheid naast de formule op, want een binair zoeken op een kolom gesorteerd op een andere sleutel is de goedkoopste manier om een zelfverzekerd verkeerd getal te berekenen. Wanneer de lookup echt heet is en de data echt gesorteerd, is de opbrengst reëel: de afdaling leest in de orde van log n cellen in plaats van n, en elk van die reads gaat door een volledige werkbladcelresolutie, dus de besparing is groter dan het instructieaantal doet vermoeden
Als de vorm van het probleem dichter bij een domeinregel ligt dan bij een lookup, zal een callback naar je eigen Pascal-code, behandeld in het artikel over aangepaste werkbladfuncties, meestal elke slimme opstelling van de ingebouwde functies verslaan. De hier besproken implementaties van XLOOKUP en XMATCH worden geleverd met het standaard HotXLS Delphi-spreadsheetcomponent, waarvan de productpagina de volledige referentie van ondersteunde functies voor Delphi en C++Builder draagt