XLOOKUP en XMATCH Binaire Zoekmodi in Delphi

Boven de sleutels 1, 3, 5, 5, 5, 7, 9 in A1:A7 geeft de formule XMATCH(5, A1:A7, 0, 2) als resultaat 3. Verander alleen het laatste argument naar -1 en dezelfde formule geeft 5. Richt de eerste versie op een kolom die iemand op een ander veld heeft hersorteerd, en een 5 die overduidelijk op het blad staat kan als #N/A terugkomen. Alle drie de antwoorden zijn correct. Het laatste argument van XMATCH, net als het zesde argument van XLOOKUP, is twee dingen tegelijk: een instructie over welk duplicaat je verkiest, en een belofte over hoe de data geordend is

Het rapport dat hier eindigt, noemt zoekmoden zelden. Het zegt dat een gegenereerd werkboek op een handjevol rijen afwijkt van wat iemand verwachtte, en die rijen hebben iets gemeen: een herhaalde sleutel, een benaderende match die een buur moest kiezen, of een lookup-kolom waarvan de volgorde veranderde nadat de formule was geschreven. De secties hieronder lopen de regels één voor één langs, met het exacte resultaat dat elke formule oplevert

Welke match_mode- en search_mode-waarden accepteert XLOOKUP?

HotXLS, de native Delphi- en C++Builder-spreadsheetcomponent, evalueert XLOOKUP en XMATCH door één gedeelde lookup-routine die exact vier match-modi en vier zoekmoden accepteert. match_mode mag 0 zijn (exact), -1 (exact of volgende kleinere), 1 (exact of volgende grotere) of 2 (wildcard). search_mode mag 1 zijn (scannen van eerst naar laatst), -1 (scannen van laatst naar eerst), 2 (binair zoeken over oplopende data) of -2 (binair zoeken over aflopende data). Elk ander getal geeft #VALUE!; er wordt niets bijgeschaald naar de dichtstbijzijnde legale modus. De argumenten weglaten selecteert match mode 0 en search mode 1, wat de meeste handgeschreven formules gebruiken

Argumentaantallen worden gecheckt voordat er iets van dat alles gebeurt. XLOOKUP neemt drie tot zes argumenten en XMATCH twee tot vier, en een aanroep buiten die bereiken is al #VALUE! voordat de evaluatie begint. De modusargumenten zelf zijn gewone werkbladexpressies, dus HotXLS converteert elk naar een getal, geeft #VALUE! terug voor NaN, oneindig of een waarde buiten het 32-bit-integerbereik, en kapt daarna richting nul af. 1.5 wordt search mode 1, -1.5 wordt -1, en 2.9 wordt 2. Afbreken bijt wanneer de modus uit een cel komt die door rekenen is ontstaan: een waarde die als 2 toont maar als 1.9999999 is opgeslagen, selecteert een lineaire scan. Schrijf de modus als literal, of wikkel een berekende in ROUND

Het volgende programma schrijft een klein tarieftabelletje en vijf lookups die die regels uitproberen. De commentaren geven de waarde die elke formulecel na Recalculate bevat, afgeleid uit de regels hierboven

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
    // Exact of volgende kleinere, binaire zoektocht over oplopende sleutels
    Sheet.Cells[2, 4].Formula := 'XLOOKUP(35,A1:A4,B1:B4,"none",-1,2)';   // 0.15
    // 1.5 kapt af naar search_mode 1
    Sheet.Cells[3, 4].Formula := 'XLOOKUP(40,A1:A4,B1:B4,"none",0,1.5)';  // 0.10
    // 3 is geen zoekmodus
    Sheet.Cells[4, 4].Formula := 'XLOOKUP(40,A1:A4,B1:B4,"none",0,3)';    // #VALUE!
    // Wildcard-matching kan niet op een binaire zoektocht
    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;

Waarom mist search mode 2 een sleutel die op het blad staat?

Zoekmodus 2 vertelt HotXLS dat de lookup-vector al oplopend gesorteerd is, en de engine gelooft dat. De claim controleren zou een volledige pass over de kolom kosten, en dat is exact de kost die een binary search bestaat om te vermijden, dus de afdaling halveert het interval bij elke sondering en geeft terug waar hij uitkomt. Microsoft documenteert hetzelfde contract voor XLOOKUP en XMATCH: de binaire modi vereisen gesorteerde data en geven anders ongeldige resultaten. ISO/IEC 29500-1 §18.17, de SpreadsheetML-formuleclausule, beschrijft de oudere LOOKUP en VLOOKUP met hun eigen oplopende-volgorde-eis; XLOOKUP en XMATCH kwamen later en staan in het bestand als _xlfn.XLOOKUP en _xlfn.XMATCH onder het future-function-prefix

De figuur traceert XLOOKUP(40, A1:A3, B1:B3, "missing", 0, 2) over de sleutels 40, 10, 30. De eerste sondering landt op rij 2, leest 10, en omdat 10 onder 40 ligt, schrijft hij rij 1 en 2 af, inclusief de rij met de 40. Rij 3 leest 30, ook onder 40, en het interval is leeg. De formule geeft "missing" terug. Met zoekmodus 1 vindt dezelfde aanroep de 40 in rij 1

"Oplopend" betekent ook iets specifieker dan het op het scherm lijkt. Het binaire pad rangschikt waarden op soort voordat het ze vergelijkt: eerst getallen, dan tekst, dan booleans, dan foutwaarden, dan lege cellen. Een kolom numerieke onderdeelscodes waarin drie cellen de codes als tekst bevatten is onder die regel niet oplopend, hoe netjes hij er ook uitziet. Tekst wordt vergeleken zonder naar hoofdletters te kijken, en op Windows komt de volgorde uit de user locale via CompareStringW, die koppeltekens en letters met accenten anders behandelt dan een byte-volgorde-sortering. Een sleutelkolom gesorteerd door een database met binaire collation kan dus exact op de codes met leestekens de volgordebelofte schenden

Lege cellen die als laatste rangschikken hebben een scheve kant. Een oplopende sleutelkolom met lege rijen eronder is nog steeds oplopend, dus zoekmodus 2 op een overdreven bereik als A2:A5001 richten is onschadelijk. Een aflopende kolom gevolgd door lege rijen is niet langer aflopend, en zoekmodus -2 loopt de lege cellen recht in

var
  R: TXLSFormulaEvaluationResult;
begin
  // B1:B3 bevat 9, 7, 5; B4:B10 zijn leeg
  R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B10,0,-2)');
  // R.Status = xlfeOk en VarIsError(R.Value): het resultaat is #N/A.
  // De eerste sondering leest de lege B5, lege cellen rangschikken boven elk getal,
  // dus de afdaling beweegt verder naar rechts en komt nooit terug

  // Beperk het bereik tot de gevulde rijen en dezelfde aanroep werkt
  R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B3,0,-2)');     // 3

  // Een lineaire scan slaat de lege cellen over en vindt de sleutel waar hij ook staat
  R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B10,0,-1)');    // 3
end;

Waar landen duplicaatsleutels?

XMATCH en XLOOKUP landen op een vast uiteinde van een duplicatenreeks, en de zoekmodus kiest het uiteinde. Onder zoekmodus 2 noteert de binaire afdaling elke gelijke sleutel die hij tegenkomt en blijft naar links vernauwen, dus het resultaat is de laagste index van de reeks. Onder zoekmodus -2, over aflopende data, noteert hij de positie en vernauwt naar rechts, wat de hoogste index geeft. De lineaire modi geven de eerste treffer in scanrichting terug: de laagste index voor zoekmodus 1, de hoogste voor zoekmodus -1

Dat is waarom de discrepantiemeldingen clustvormen op bepaalde rijen. Een voorbeeldbestand met unieke sleutels geeft onder alle vier de zoekmodi hetzelfde antwoord, dus elke test die erop is gebouwd slaagt. Zodra productiedata een klantcode herhaalt, beginnen de modi te verschillen op precies de herhaalde rijen, en de code die de formules schreef is helemaal niet gewijzigd

// A1:A7 bevat 1, 3, 5, 5, 5, 7, 9 (oplopend, een reeks van drie)
Sheet.Cells[1, 3].Formula := 'XMATCH(5,A1:A7,0,1)';   // 3, eerste treffer vooruit
Sheet.Cells[2, 3].Formula := 'XMATCH(5,A1:A7,0,-1)';  // 5, eerste treffer achteruit
Sheet.Cells[3, 3].Formula := 'XMATCH(5,A1:A7,0,2)';   // 3, laagste index van de reeks

// B1:B7 bevat 9, 7, 5, 5, 5, 3, 1 (aflopend)
Sheet.Cells[4, 3].Formula := 'XMATCH(5,B1:B7,0,-2)';  // 5, hoogste index van de reeks

Hoe benaderend matchen de naaste kandidaat kiest

Als geen enkele cel gelijk is aan de lookup-waarde, geeft match mode -1 de grootste waarde terug die niet groter is dan het doel en match mode 1 de kleinste waarde die niet kleiner is. In de lineaire modi scant HotXLS de hele vector en houdt een beste kandidaat vast, dus benaderend matchen werkt ook op ongeordende data. Een cel vervangt de zittende kandidaat alleen als hij strikt dichterbij is, nooit als hij slechts gelijk staat. Van meerdere cellen met dezelfde naaste waarde wordt de eerste in scanvolgorde gehouden

Neem de sleutels 10, 30, 20, 30, 50, 40 in A1:A6. XMATCH(35, A1:A6, -1, 1) geeft 2: de eerste 30 wordt de kandidaat, en de tweede 30 staat er alleen maar gelijk aan. Draai de scan om met XMATCH(35, A1:A6, -1, -1) en het resultaat is 4, want achterwaarts scannen ontmoet de andere 30 eerst. XMATCH(35, A1:A6, 1, 1) geeft 6, want 40 verslaat 50 als de kleinste waarde boven 35. Op gesorteerde data bereiken de binaire modi dezelfde buur in een handjevol sondes: over 10, 20, 30, 40, 50 geeft XMATCH(35, A1:A5, -1, 2) 3 en XMATCH(35, A1:A5, 1, 2) 4

Als noch een exacte treffer noch een acceptabele buur opduikt, geeft XLOOKUP zijn if_not_found-argument terug wanneer dat is meegegeven en #N/A wanneer niet. XMATCH heeft geen fallback-argument en geeft altijd #N/A terug

Waarom kunnen wildcards en binair zoeken niet samengaan?

Een wildcardpatroon heeft geen positie in een sorteervolgorde. Match mode 2 vraagt of een cel in een mask past, wat een ja-of-nee-vraag is, terwijl een binaire afdaling een driedelig antwoord nodig heeft dat zegt welke helft behouden blijft. Er is geen verdedigbare manier om te zeggen of ACME-* links of rechts van een gegeven cel ligt, dus HotXLS geeft #VALUE! terug voor match mode 2 gecombineerd met search mode 2 of -2 en gokt niet. Gebruik search mode 1 of -1 met wildcards en accepteer de lineaire kost

Match mode 2 past altijd wildcardregels toe: * staat voor elke reeks tekens, ? voor één teken, en ~ maakt het volgende teken literal. XLOOKUP("PN~*", ...) vindt dus de tekst PN* zelf, wat ertoe doet wanneer productcodes echt asterisks bevatten. In match mode 0 vergelijkt de lineaire scan tekst hoofdletteronafhankelijk, en het binaire pad behandelt twee waarden als gelijk wanneer zijn ordeningsvergelijking een gelijke stand meldt. Die vergelijking is ook hoofdletteronafhankelijk, maar het is de zojuist beschreven locale-bewuste ordening, en dat is de enige relatie die een binaire zoektocht rechtmatig mag gebruiken. Lookup-rijke bladen bepalen ook wat de dependency tracking achter incrementele herberekening na een bewerking opnieuw moet evalueren

Vormfouten en gespilde return-slices

Beide functies hebben een ééndimensionaal lookup-bereik nodig. Een bereik dat tegelijk meer dan één rij en meer dan één kolom beslaat geeft #VALUE!; HotXLS kiest geen as voor je. XLOOKUP voegt een tweede regel toe: het return-bereik moet langs de matchende as even lang zijn als het lookup-bereik. Een verticale lookup over 500 rijen gecombineerd met een return-bereik van 499 rijen is een fout, en de laatste rij wordt nooit stilletjes gedropt. Als het lookup-bereik één cel is, bepaalt de vorm van het return-bereik of de lookup als verticaal of horizontaal geldt

Een return-bereik breder dan één kolom, bij een verticale lookup, laat XLOOKUP de hele matchende rij als array teruggeven. Hij spillt in de naburige cellen onder dezelfde regels als de andere dynamic array-functies, behandeld in het artikel over spill-bereiken en dynamic arrays. Lege cellen binnen een gespelde slice komen als 0 terug. Een volledige record met één formule uit een tabel trekken is handig, en het is ook de snelste manier om een kolom die toevallig rechts van de formule stond te overschrijven

Een modus kiezen voor werkboeken die niemand in de gaten houdt

Generatie aan de serverkant verdient een strenger beleid dan interactief gebruik, want niemand bekijkt het resultaat voordat het de deur uit gaat. Laat match mode 0 met search mode 1 daar gewoon staan: de match is exact, de scan negeert de rijvolgorde, en het hersorteren van het blad kan het antwoord niet veranderen. Search mode 2 hoort alleen waar dezelfde code de ordening produceerde, in dezelfde run, over dezelfde kolom. Een binaire zoektocht over een kolom die op een andere sleutel is gesorteerd, is de goedkoopste manier om met overtuiging een fout getal uit te rekenen

Als je een lookup toch naar binaire modus omzet, check de ordening dan zelf op het punt waar je de formule uitzendt. De functie hieronder is geen onderdeel van HotXLS; het is een guard om aan je eigen code toe te voegen. Voor vele exacte lookups tegen één tabel laat de werkboek-property RecalcLookupCache Recalculate de eerste scan van een bereik hergebruiken voor latere lookups erop. De cache schakelt zichzelf uit wanneer de pass OFFSET, INDIRECT, circulaire verwijzingen of What-If-datatabellen bevat, en de resultaten zijn hoe dan ook gelijk

// Je eigen guard, geen HotXLS-API: bevestig dat een numerieke sleutelkolom
// oplopend is voordat je een search_mode 2-formule uitzendt die erop leunt
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;  // tekst, leeg of uit de orde
    Prev := Cur;
  end;
  Result := True;
end;

// Rates-blad: sleutels in A2:A5001, tarieven 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]);

De guard is met opzet conservatief: één enkele lege of tekstcel stuurt de hele kolom terug naar het lineaire pad. Op een gesorteerde, vaak gebruikte kolom is de winst van binaire modus echt: zo'n log₂ n leesacties per lookup in plaats van tot n, wat bij 5.000 sleutels hoogstens 13 sondes betekent

Als een lookup in werkelijkheid een bedrijfsregel in vermomming is, zoals een getrapt tarief met uitzonderingen, dan is een callback naar je eigen Pascal-code meestal duidelijker dan elke opstelling van ingebouwde functies; het artikel over custom werkbladfuncties laat zien hoe je er één registreert. XLOOKUP, XMATCH en de rest van de hier beschreven formule-engine maken deel uit van de HotXLS Delphi spreadsheetcomponent, en zijn productpagina zet de ondersteunde werkbladfuncties voor Delphi en C++Builder op een rij