HotXLS, den native Delphi- og C++Builder-regnearkskomponent, evaluerer XLOOKUP og XMATCH gennem én delt opslagskerne. Den kerne accepterer fire matchtilstande (-1, 0, 1, 2) og fire søgetilstande (-2, -1, 1, 2), kører en logaritmisk binær nedstigning, når den absolutte søgetilstand er 2, og afviser enhver anden kombination med en formelfejl
Fejlrapporten, der sender dig herhen, siger aldrig "søgetilstand". Den siger, at den serveroprettede projektmappe viser et andet tal end den samme fil åbnet i Excel, på måske fire rækker ud af ni tusind. De fire rækker har altid noget til fælles: en duplikeret opslagsnøgle, eller et approksimativt match, der måtte vælge en nabo, eller en opslagskolonne, nogen sorterede efter en anden kolonne i sidste uge. Opslagsfunktioner er, hvor en formelmotor holder op med at være aritmetik og begynder at være en kontrakt, og kontrakten har klausuler, de fleste kaldere aldrig læser
Hvilke tilstandsnumre accepterer XLOOKUP egentlig?
Præcis fire af hver, og intet andet. HotXLS validerer match_mode mod -1, 0, 1 og 2 og search_mode mod -2, -1, 1 og 2, før den rører en eneste celle, og enhver anden værdi returnerer #VALUE! frem for at blive klemt ind i den nærmeste lovlige tilstand. De fire matchtilstande er 0 for eksakt, -1 for eksakt eller næste mindre, 1 for eksakt eller næste større, og 2 for wildcard; de fire søgetilstande er 1 for en fremadgående lineær scanning, -1 for en baglæns lineær scanning, 2 for en binærsøgning over stigende data, og -2 for en binærsøgning over faldende data. Udelader man dem, vælges matchtilstand 0 og søgetilstand 1, den kombination næsten alle rigtige formler bruger. Argumentantal politiseres på samme måde: XLOOKUP tager tre til seks argumenter, og XMATCH tager to til fire, og alt uden for de intervaller er et #VALUE!, før evaluering begynder
// 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;Ét trin tidligere er der et stillere tjek værd at kende. Tilstandsargumenterne ankommer som arkudtryk, så HotXLS tvinger dem til et tal, afviser NaN og uendelighed, og kræver så, at tallet er lig sin egen afrundede værdi. XLOOKUP(x, A:A, B:B, "none", 0, 1.5) er et #VALUE!, ikke en søgetilstand 2 i forklædning. Det betyder noget, når tilstanden kommer fra en celle, en afrundingstung beregning har produceret, hvilket er mere almindeligt i genererede projektmapper end i håndskrevne
Hvorfor giver search_mode 2 det forkerte svar på usorterede data?
Fordi den gør præcis, hvad man bad om. Søgetilstand 2 fortæller motoren, at opslagsvektoren allerede er i stigende rækkefølge, og en binærsøgning kan ikke bekræfte den påstand uden en O(n)-gennemgang, der ville ødelægge grunden til at bruge den. HotXLS stoler derfor på kalderen, halverer intervallet og returnerer, hvad end nedstigningen lander på. På usorteret input er svaret ikke en fejl, det er stiltiende forkert, og dette er et kontraktbrud frem for en defekt i motoren
Microsoft dokumenterer samme asymmetri for XLOOKUP og XMATCH: de binære tilstande kræver sorterede data og producerer ugyldige resultater ellers. ISO 29500-1 klausul 18.17, der definerer SpreadsheetML-formelgrammatikken, bærer de ældre LOOKUP- og VLOOKUP-beskrivelser med deres eget krav om stigende rækkefølge, og XLOOKUP og XMATCH kommer så meget senere, at de rejser i filen som _xlfn.XLOOKUP og _xlfn.XMATCH under future-function-konventionen. Anden generation, samme aftale: kalderen leverer ordningsinvarianten, motoren leverer 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;Spor den anden formel, og fejlen er helt mekanisk. Nedstigningen sonderer midtercellen, læser 10, afgør at 10 er mindre end 40, kasserer venstre halvdel inklusive den række, der faktisk holdt 40, sonderer 30, kasserer igen, og løber tør for interval. Excel opfører sig på samme måde, hvilket er pointen: at gengive det forkerte svar er et kompatibilitetskrav, ikke en høflighed. Ordningspræmissen er også strengere end "tal stigende", fordi komparatoren rangerer værdier efter type først, i rækkefølgen tal, så tekst, så booleans, så fejlværdier, så blanke, og først sammenligner inden for en type derefter. En kolonne af numeriske delkoder, der har tre celler, der gemmer tekst i stedet, er ikke stigende under den komparator, uanset hvordan den ser ud på skærmen, og de binære tilstande vil gerne fejllæse den
Hvor lander duplikatnøgler?
Ved en deterministisk ende af duplikatserien, og hvilken ende afhænger af søgetilstanden frem for held. Når den binære nedstigning rammer en lig nøgle under søgetilstand 2, registrerer den positionen og fortsætter så med at indsnævre til venstre, så resultatet er det laveste indeks i serien; under søgetilstand -2, over faldende data, registrerer den positionen og indsnævrer til højre, så resultatet er det højeste indeks. De lineære tilstande er enklere: søgetilstand 1 returnerer det første hit fremad, søgetilstand -1 det første hit baglæns. Det er den detalje, der producerer fire-rækkers-uoverensstemmelsen fra åbningsafsnittet, fordi en projektmappe, hvis nøgler er unikke, giver identiske svar under alle fire søgetilstande og skjuler forskellen gennem hver test, man skrev fra en ren testfil. Tilføj én duplikeret kundekode til produktionsdata, og tilstandene begynder at være uenige på præcis de rækker, der duplikerede: intet ændrede sig i motoren, input holdt bare op med at være et sæt og blev til en multimængde
// 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 runHvordan vælger approksimativt match runner-up'en?
Ved at holde en bedste kandidat ved siden af den eksakte matchsøgning og kun returnere den, hvis intet eksakt hit dukker op. HotXLS behandler match_mode -1 som "den største værdi, der ikke er større end målet", og match_mode 1 som "den mindste værdi, der ikke er mindre", og begge løses over hele det scannede område frem for ved at stoppe ved den første acceptable nabo. På den binære sti falder samme idé ud af nedstigningen gratis: hvert trin, der overskyder eller underskyder, opdaterer kandidaten, så den endelige kandidat er grænseelementet ved siden af positionen, hvor nøglen ville være blevet indsat
// 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 den indre betingelse nøje, for tie-break bor der. En ny celle erstatter kun den stående kandidat, når den er strengt bedre, aldrig når den blot er lig med den, så blandt flere celler, der holder samme runner-up-værdi, er den, der beholdes, den første mødt i scanningsrækkefølge: den laveste indeks under en fremadgående scanning, den højeste under en baglæns. Finder XLOOKUP og XMATCH hverken et eksakt hit eller en acceptabel nabo, falder XLOOKUP tilbage på sit if_not_found-argument, når ét blev leveret, og til #N/A, når det ikke blev, mens XMATCH altid giver #N/A
Hvorfor kan wildcards og binærsøgning ikke sameksistere?
Fordi et wildcard-mønster ikke er en position i en rækkefølge. Matchtilstand 2 spørger, om en celle matcher en maske, og maskematch svarer ja eller nej; en binær nedstigning behøver et tre-vejs svar, der fortæller den, hvilken halvdel den skal beholde. Der er ingen forsvarlig måde at spørge, om ACME-* ligger til venstre eller til højre for en given celle, så HotXLS afviser match_mode 2 kombineret med search_mode 2 eller -2 straks med #VALUE! frem for at gætte en ordning og producere plausibelt nonsens. De to stier sammenligner også værdier forskelligt, hvilket forstærker adskillelsen: den lineære scanning afgør lighed med en case-insensitiv tekstsammenligning, eller med maskematch når wildcards er slået til, mens den binære nedstigning afgør lighed ved at spørge ordningskomparatoren om nul. Det er bevidst frem for et lag-uheld, da den binære sti kun må bruge den relation, den faktisk navigerer efter. Behøver man wildcards, brug søgetilstand 1 eller -1, og acceptér den lineære omkostning, hvilket er samme afvejning, afhængighedssporingen bag inkrementel genberegning er designet til at holde uden for din kritiske sti
Formfejl: todimensionelle intervaller og uoverensstemmende returvektorer
Begge funktioner kræver et genuint endimensionelt opslagsinterval. Hvis det leverede interval spænder over mere end én række og mere end én kolonne på samme tid, returnerer HotXLS #VALUE! frem for at vælge en akse på dine vegne, og et enkeltrække- eller enkeltkolonne-interval læses langs sin lange akse. XLOOKUP tilføjer en anden formregel: returintervallet skal være nøjagtig så langt som opslagsintervallet langs den matchende akse, så et vertikalt opslag over 500 rækker parret med et returinterval på 499 rækker er en fejl, ikke en off-by-one, der stiltiende løses ved sidste række. Er returintervallet bredere end én kolonne for et vertikalt opslag, eller højere end én række for et horisontalt, afleverer XLOOKUP hele det matchede udsnit som et array, og det spilder ind i naboceller under samme regler som de andre dynamiske array-funktioner, beskrevet i artiklen om spill-intervaller og dynamiske arrays. Det er genuint nyttigt til at trække en hel post ud af en tabel med én formel, og det er også den hurtigste måde at overskrive en kolonne, man havde tænkt sig at beholde
Valg af tilstand, når ingen holder øje med skærmen
Server-side generering fortjener en strengere politik end interaktiv brug, fordi der ikke er noget menneske til at bemærke, at en sum ser forkert ud. Det forsvarlige standardvalg er søgetilstand 1 med matchtilstand 0: lineær, eksakt, ordningsuafhængig, og umulig at ugyldiggøre ved at omsortere et ark. Grib kun efter søgetilstand 2, hvor samme kodesti også producerede ordningen, i samme kørsel, over samme kolonne, og skriv den afhængighed ned ved siden af formlen, fordi en binærsøgning på en kolonne sorteret efter en anden nøgle er den billigste mulige måde at beregne et sikkert forkert tal på. Når opslaget genuint er hedt, og dataene genuint er sorterede, er gevinsten reel: nedstigningen læser i størrelsesordenen log n celler i stedet for n, og hver af de læsninger går gennem en fuld projektmappe-cellopløsning, så besparelsen er større, end instruktionsantallet antyder
Er formen på problemet tættere på en domæneregel end på et opslag, vil et callback ind i din egen Pascal-kode, som dækket i artiklen om brugerdefinerede arkfunktioner, som regel slå enhver smart arrangering af de indbyggede. XLOOKUP- og XMATCH-implementeringerne diskuteret her leveres med den standard HotXLS Delphi regnearkskomponent, hvis produktside bærer den fulde funktionsreference for Delphi og C++Builder