HotXLS, den native Delphi- och C++Builder-kalkylbladskomponenten, utvärderar XLOOKUP och XMATCH genom en delad uppslagskärna. Den kärnan accepterar fyra matchningslägen (-1, 0, 1, 2) och fyra sökningslägen (-2, -1, 1, 2), kör en logaritmisk binärnedstigning närhelst det absoluta sökningsläget är 2, och avvisar varje annan kombination med ett formelfel
Felrapporten som skickar dig hit säger aldrig "sökningsläge". Den säger att den servergenererade arbetsboken visar ett annat tal än samma fil öppnad i Excel, på kanske fyra rader av niotusen. De fyra raderna har alltid något gemensamt: en duplicerad uppslagsnyckel, eller en approximativ matchning som var tvungen att välja en granne, eller en uppslagskolumn någon sorterade efter en annan kolumn förra veckan. Uppslagsfunktioner är där en formelmotor slutar vara aritmetik och börjar vara ett kontrakt, och kontraktet har klausuler de flesta anropare aldrig läser
Vilka lägesnummer accepterar XLOOKUP egentligen?
Exakt fyra av vardera, och inget annat. HotXLS validerar match_mode mot -1, 0, 1 och 2 och search_mode mot -2, -1, 1 och 2 innan den rör en enda cell, och varje annat värde returnerar #VALUE! snarare än att klämmas fast till närmaste giltiga läge. De fyra matchningslägena är 0 för exakt, -1 för exakt eller nästa mindre, 1 för exakt eller nästa större, och 2 för jokertecken; de fyra sökningslägena är 1 för en linjär skanning framåt, -1 för en linjär skanning bakåt, 2 för en binärsökning över stigande data, och -2 för en binärsökning över fallande data. Att utelämna dem väljer matchningsläge 0 och sökningsläge 1, paret nästan varje verklig formel använder. Argumentantal poliseras på samma sätt: XLOOKUP tar tre till sex argument och XMATCH tar två till fyra, och allt utanför de intervallen är ett #VALUE! innan utvärdering börjar
// 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;Ett steg tidigare finns en tystare kontroll värd att känna till. Lägesargumenten anländer som kalkylbladsuttryck, så HotXLS tvingar dem till ett tal, vägrar NaN och oändlighet, och kräver sedan att talet är lika med sitt eget avrundade värde. XLOOKUP(x, A:A, B:B, "none", 0, 1.5) är ett #VALUE!, inte ett sökningsläge 2 i förklädnad. Det spelar roll när läget kommer från en cell en avrundningstung beräkning producerade, vilket är vanligare i genererade arbetsböcker än i handskrivna sådana
Varför ger search_mode 2 fel svar på osorterad data?
Eftersom den gör exakt vad du bad om. Sökningsläge 2 talar om för motorn att uppslagsvektorn redan är i stigande ordning, och en binärsökning kan inte verifiera det påståendet utan ett O(n)-pass som skulle förstöra anledningen till att använda den. HotXLS litar därför på anroparen, halverar intervallet, och returnerar vad nedstigningen landar på. På osorterad indata är svaret inte ett fel, det är tyst fel, och det här är ett kontraktsbrott snarare än en defekt i motorn
Microsoft dokumenterar samma asymmetri för XLOOKUP och XMATCH: de binära lägena kräver sorterad data och producerar ogiltiga resultat annars. ISO 29500-1 klausul 18.17, som definierar SpreadsheetML-formelgrammatiken, bär de äldre LOOKUP- och VLOOKUP-beskrivningarna med sitt eget stigande-ordning-krav, och XLOOKUP och XMATCH postdaterar den texten tillräckligt långt att de reser i filen som _xlfn.XLOOKUP och _xlfn.XMATCH under framtida-funktion-konventionen. Olika generation, samma affär: anroparen tillhandahåller ordningsinvarianten, motorn tillhandahåller logaritmen
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;Spåra den andra formeln och felet är helt mekaniskt. Nedstigningen sonderar mittcellen, läser 10, avgör att 10 är mindre än 40, kastar bort vänstra halvan inklusive raden som faktiskt höll 40, sonderar 30, kastar bort igen, och tar slut på intervall. Excel beter sig på samma sätt, vilket är poängen: att återge det felaktiga svaret är ett kompatibilitetskrav, inte en artighet. Ordningspremissen är också strängare än "tal stigande", eftersom komparatorn rankar värden efter typ först, i ordningen tal, sedan text, sedan booleaner, sedan felvärden, sedan tomma, och bara jämför inom en typ efter det. En kolumn med numeriska artikelkoder som har tre celler som lagrar text i stället är inte stigande under den komparatorn oavsett hur den ser ut på skärmen, och de binära lägena kommer glatt misstolka den
Var hamnar dubblettnycklar?
Vid en deterministisk ände av dubblettkörningen, och vilken ände beror på sökningsläget snarare än på tur. När binärnedstigningen träffar en lika nyckel under sökningsläge 2 registrerar den positionen och fortsätter sedan att smalna av åt vänster, så resultatet är det lägsta indexet i körningen; under sökningsläge -2, över fallande data, registrerar den positionen och smalnar av åt höger, så resultatet är det högsta indexet. De linjära lägena är enklare: sökningsläge 1 returnerar den första träffen framåt, sökningsläge -1 den första träffen bakåt. Det här är detaljen som producerar fyra-rader-avvikelsen från öppningsstycket, eftersom en arbetsbok vars nycklar är unika ger identiska svar under alla fyra sökningslägen och döljer skillnaden genom varje test du skrev från en ren exempelfil. Lägg till en duplicerad kundkod till produktionsdata och lägena börjar bli oense exakt på de rader som duplicerades: inget ändrades i motorn, indatan bara slutade vara en mängd och blev en multimängd
// 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 runHur väljer en approximativ matchning tvåan?
Genom att hålla en bästa kandidat vid sidan av den exakta matchningssökningen och returnera den bara om ingen exakt träff dyker upp. HotXLS behandlar match_mode -1 som "det största värdet som inte är större än målet" och match_mode 1 som "det minsta värdet som inte är mindre", och båda löses upp över hela det skannade området snarare än genom att stanna vid den första acceptabla grannen. I binärvägen faller samma idé ut från nedstigningen gratis: varje steg som överskjuter eller underskjuter uppdaterar kandidaten, så den slutliga kandidaten är gränselementet bredvid positionen där nyckeln skulle ha infogats
// 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;Läs det inre villkoret noga, för avgörandet vid oavgjort bor där. En ny cell ersätter den stående kandidaten bara när den är strikt bättre, aldrig när den bara är lika med den, så bland flera celler som håller samma tvåa-värde är den som behålls den första man mötte i skanningsordning: det lägsta indexet under en skanning framåt, det högsta under en skanning bakåt. Om XLOOKUP och XMATCH varken hittar en exakt träff eller en acceptabel granne faller XLOOKUP tillbaka på sitt if_not_found-argument när ett sådant tillhandahölls och till #N/A när det inte gjorde det, medan XMATCH alltid ger #N/A
Varför jokertecken och binärsökning inte kan samexistera
Eftersom ett jokerteckenmönster inte är en position i en ordning. Matchningsläge 2 frågar om en cell matchar en mask, och maskmatchning svarar ja eller nej; en binärnedstigning behöver ett tredelat svar som talar om för den vilken halva den ska behålla. Det finns inget försvarbart sätt att fråga om ACME-* ligger till vänster eller till höger om en given cell, så HotXLS avvisar match_mode 2 kombinerat med search_mode 2 eller -2 direkt med #VALUE! i stället för att gissa en ordning och producera trovärdigt nonsens. De två vägarna jämför också värden olika, vilket förstärker uppdelningen: den linjära skanningen avgör likhet med en skiftlägesokänslig textjämförelse, eller med maskmatchning när jokertecken är på, medan binärnedstigningen avgör likhet genom att fråga ordningskomparatorn efter en nolla. Det är avsiktligt snarare än en olycka i lagerbygget, eftersom binärvägen bara får använda relationen den faktiskt navigerar efter. Om du behöver jokertecken, använd sökningsläge 1 eller -1 och acceptera den linjära kostnaden, vilket är samma avvägning beroendespårningen bakom inkrementell omberäkning är designad att hålla borta från din kritiska väg
Formfel: tvådimensionella intervall och missmatchade returvektorer
Båda funktionerna kräver ett genuint endimensionellt uppslagsintervall. Om det tillhandahållna intervallet spänner över mer än en rad och mer än en kolumn samtidigt returnerar HotXLS #VALUE! snarare än att välja en axel å dina vägnar, och ett enrads- eller enkolumns-intervall läses längs sin långa axel. XLOOKUP lägger till en andra formregel: returintervallet måste vara exakt lika långt som uppslagsintervallet längs den matchande axeln, så en vertikal uppslagning över 500 rader parad med ett 499-rads-returintervall är ett fel, inte en av-med-ett-fel tyst löst på sista raden. När returintervallet är bredare än en kolumn för en vertikal uppslagning, eller högre än en rad för en horisontell, lämnar XLOOKUP tillbaka hela den matchade skivan som en array och den spiller ut i angränsande celler under samma regler som de andra dynamiska array-funktionerna, beskrivet i artikeln om spillintervall och dynamiska arrayer. Det är genuint användbart för att dra ut en hel post ur en tabell med en formel, och det är också det snabbaste sättet att skriva över en kolumn du tänkte behålla
Att välja ett läge när ingen tittar på skärmen
Servergenerering förtjänar en strängare policy än interaktiv användning, eftersom det inte finns någon människa som märker att en summa ser fel ut. Standardvalet som går att försvara är sökningsläge 1 med matchningsläge 0: linjärt, exakt, ordningsoberoende, och omöjligt att ogiltigförklara genom att sortera om ett blad. Sträck dig efter sökningsläge 2 bara där samma kodväg också producerade ordningen, i samma körning, över samma kolumn, och skriv ner det beroendet bredvid formeln, eftersom en binärsökning på en kolumn sorterad efter en annan nyckel är det billigaste möjliga sättet att beräkna ett självsäkert felaktigt tal. När uppslagningen genuint är het och datan genuint sorterad är utdelningen verklig: nedstigningen läser i storleksordningen log n celler i stället för n, och var och en av de läsningarna går genom en fullständig kalkylbladscellsupplösning, så besparingen är större än instruktionsantalet antyder
Om formen på problemet ligger närmare en domänregel än en uppslagning kommer en återanropning till din egen Pascal-kod, som täckt i artikeln om anpassade kalkylbladsfunktioner, vanligtvis slå varje smart arrangemang av de inbyggda. XLOOKUP- och XMATCH-implementationerna som diskuteras här levereras med standard-HotXLS Delphi-kalkylbladskomponenten, vars produktsida bär den fullständiga referensen över stödda funktioner för Delphi och C++Builder