HotXLS, izvorna Delphi i C++Builder komponenta proračunske tablice, vrednuje XLOOKUP i XMATCH kroz jednu zajedničku jezgru pretrage. Ta jezgra prihvaća četiri načina poklapanja (-1, 0, 1, 2) i četiri načina pretrage (-2, -1, 1, 2), pokreće logaritamski binarni spust kad god je apsolutni način pretrage 2, i odbija svaku drugu kombinaciju s greškom formule
Prijava greške koja vas ovdje dovodi nikad ne kaže "način pretrage". Kaže da radni sveščić generiran na poslužitelju pokazuje drugačiji broj nego ista datoteka otvorena u Excelu, na možda četiri retka od devet tisuća. Ta četiri retka uvijek imaju nešto zajedničko: dupliciran ključ pretrage, ili približno poklapanje koje je moralo odabrati susjeda, ili stupac pretrage koji je netko prošli tjedan sortirao po drugom stupcu. Funkcije pretrage su mjesto gdje modul formula prestaje biti aritmetika i počinje biti ugovor, a ugovor ima klauzule koje većina pozivatelja nikad ne pročita
Koje brojeve načina XLOOKUP zapravo prihvaća?
Točno četiri od svakog, i ništa drugo. HotXLS provjerava match_mode protiv -1, 0, 1 i 2, a search_mode protiv -2, -1, 1 i 2 prije nego dotakne ijednu stanicu, a bilo koja druga vrijednost vraća #VALUE! umjesto da bude stegnuta u najbliži legalni način. Četiri načina poklapanja su 0 za točno, -1 za točno ili sljedeće manje, 1 za točno ili sljedeće veće, i 2 za wildcard; četiri načina pretrage su 1 za naprijedno linearno pretraživanje, -1 za obrnuto linearno pretraživanje, 2 za binarnu pretragu preko uzlaznih podataka, i -2 za binarnu pretragu preko silaznih podataka. Izostavljanje njih bira način poklapanja 0 i način pretrage 1, sparivanje koje koristi gotovo svaka stvarna formula. Broj argumenata provjerava se na isti način: XLOOKUP uzima tri do šest argumenata, a XMATCH dva do četiri, a sve izvan tih raspona je #VALUE! prije nego vrednovanje počne
// 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;Jedan korak ranije postoji tiša provjera vrijedna spomena. Argumenti načina stižu kao izrazi radnog lista, pa ih HotXLS pretvara u broj, odbija NaN i beskonačnost, i zatim zahtijeva da broj bude jednak svojoj vlastitoj zaokruženoj vrijednosti. XLOOKUP(x, A:A, B:B, "none", 0, 1.5) je #VALUE!, ne prerušeni način pretrage 2. To je bitno kad način dolazi iz stanice koju je proizveo izračun s puno zaokruživanja, što je češće u generiranim radnim sveščićima nego u ručno pisanima
Zašto search_mode 2 daje pogrešan odgovor na nesortiranim podacima?
Zato što radi upravo ono što ste zatražili. Način pretrage 2 govori modulu da je vektor pretrage već uzlazno poredan, a binarna pretraga ne može provjeriti tu tvrdnju bez O(n) prolaza koji bi uništio razlog za njeno korištenje. HotXLS stoga vjeruje pozivatelju, prepolovljuje interval, i vraća gdje god spust sleti. Na nesortiranom ulazu odgovor nije greška, tiho je pogrešan, i ovo je kršenje ugovora, a ne nedostatak modula
Microsoft dokumentira istu asimetriju za XLOOKUP i XMATCH: binarni načini zahtijevaju sortirane podatke i inače proizvode nevaljane rezultate. ISO 29500-1 klauzula 18.17, koja definira gramatiku formula SpreadsheetML, nosi starije opise LOOKUP i VLOOKUP s njihovim vlastitim zahtjevom uzlaznog poretka, a XLOOKUP i XMATCH nastali su dovoljno kasnije od tog teksta da putuju u datoteci kao _xlfn.XLOOKUP i _xlfn.XMATCH pod konvencijom budućih funkcija. Drugačija generacija, isti dogovor: pozivatelj dostavlja invarijantu poretka, modul dostavlja logaritam
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;Pratite drugu formulu i neuspjeh je posve mehanički. Spust ispituje srednju stanicu, čita 10, odlučuje da je 10 manje od 40, odbacuje lijevu polovicu uključujući redak koji je zapravo držao 40, ispituje 30, ponovno odbacuje, i ostaje bez intervala. Excel se ponaša isto, i to je poanta: reproduciranje pogrešnog odgovora je zahtjev kompatibilnosti, a ne ljubaznost. Premisa poretka je i stroža od "brojevi uzlazno", jer komparator prvo rangira vrijednosti po vrsti, redoslijedom brojevi, zatim tekst, zatim buleani, zatim vrijednosti greške, zatim praznine, i tek onda uspoređuje unutar vrste. Stupac numeričkih šifri dijelova koji ima tri stanice koje umjesto toga pohranjuju tekst nije uzlazan pod tim komparatorom bez obzira kako izgleda na ekranu, a binarni će ga načini rado pogrešno pročitati
Gdje slijeću duplirani ključevi?
Na determinističkom kraju niza duplikata, a koji kraj ovisi o načinu pretrage, a ne o sreći. Kad binarni spust pogodi jednak ključ pod načinom pretrage 2, bilježi poziciju i zatim nastavlja sužavati na lijevo, tako da je rezultat najniži indeks niza; pod načinom pretrage -2, preko silaznih podataka, bilježi poziciju i sužava na desno, tako da je rezultat najviši indeks. Linearni načini su jednostavniji: način pretrage 1 vraća prvi pogodak idući naprijed, način pretrage -1 prvi pogodak idući unatrag. Ovo je detalj koji proizvodi neslaganje od četiri retka iz uvodnog odlomka, jer radni sveščić čiji su ključevi jedinstveni daje identične odgovore pod sva četiri načina pretrage i sakriva razliku kroz svaki test koji ste napisali iz čiste primjerne datoteke. Dodajte jednu dupliciranu šifru kupca u produkcijske podatke i načini počinju neslaganje upravo na retcima koji su se duplicirali: ništa se nije promijenilo u modulu, ulaz je jednostavno prestao biti skup i postao multiskup
// 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 runKako približno poklapanje bira vice-šampiona?
Držeći najboljeg kandidata uz pretragu točnog poklapanja, i vraćajući ga samo ako se ne pojavi točan pogodak. HotXLS tretira match_mode -1 kao "najveću vrijednost koja nije veća od cilja", a match_mode 1 kao "najmanju vrijednost koja nije manja", i oboje se razrješava preko cijele pretražene regije, a ne zaustavljanjem na prvom prihvatljivom susjedu. U binarnom putu ista ideja proizlazi iz spusta besplatno: svaki korak koji promaši prema gore ili dolje ažurira kandidata, tako da je konačni kandidat granični element pored pozicije gdje bi ključ bio umetnut
// 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;Pročitajte unutarnji uvjet pažljivo, jer tamo živi razrješavanje izjednačenja. Nova stanica zamjenjuje trenutnog kandidata samo kad je strogo bolja, nikad kad mu je tek jednaka, pa se od nekoliko stanica koje drže istu vice-šampionsku vrijednost zadržava ona prva susretnuta u redoslijedu pretrage: najniži indeks pod naprijednom pretragom, najviši pod obrnutom. Ako XLOOKUP i XMATCH ne pronađu ni točan pogodak ni prihvatljivog susjeda, XLOOKUP pada natrag na svoj argument if_not_found kad je dostavljen, a na #N/A kad nije, dok XMATCH uvijek daje #N/A
Zašto wildcardovi i binarna pretraga ne mogu koegzistirati
Zato što wildcard uzorak nije pozicija u poretku. Način poklapanja 2 pita poklapa li se stanica s maskom, a poklapanje maske odgovara da ili ne; binarnom spustu treba trosmjeran odgovor koji govori koju polovicu zadržati. Ne postoji obranjiv način pitati leži li ACME-* lijevo ili desno od dane stanice, pa HotXLS unaprijed odbija match_mode 2 kombiniran s search_mode 2 ili -2 s #VALUE! umjesto nagađanja poretka i proizvodnje uvjerljivih besmislica. Dva puta također uspoređuju vrijednosti drugačije, što pojačava razdvajanje: linearno pretraživanje odlučuje jednakost usporedbom teksta neosjetljivom na velika i mala slova, ili poklapanjem maske kad su wildcardovi uključeni, dok binarni spust odlučuje jednakost pitajući komparator poretka za nulu. To je namjerno, a ne slučajnost slojevanja, jer binarni put smije koristiti samo relaciju kroz koju zapravo navigira. Ako trebate wildcardove, koristite način pretrage 1 ili -1 i prihvatite linearni trošak, isti kompromis koji praćenje ovisnosti iza prirasnog ponovnog izračuna osmišljeno je čuvati izvan vaše kritične putanje
Pogreške oblika: dvodimenzionalni rasponi i neusklađeni povratni vektori
Obje funkcije zahtijevaju istinski jednodimenzionalni raspon pretrage. Ako dostavljeni raspon obuhvaća više od jednog retka i više od jednog stupca istovremeno, HotXLS vraća #VALUE! umjesto da bira os umjesto vas, a raspon jednog retka ili jednog stupca čita se duž svoje duge osi. XLOOKUP dodaje drugo pravilo oblika: povratni raspon mora biti točno onoliko dugačak koliko i raspon pretrage duž podudarajuće osi, tako da je vertikalna pretraga preko 500 redaka sparena s povratnim rasponom od 499 redaka greška, a ne tiho razriješeno odstupanje za jedan na posljednjem retku. Kad je povratni raspon širi od jednog stupca za vertikalnu pretragu, ili viši od jednog retka za horizontalnu, XLOOKUP vraća cijeli poklopljeni isječak kao niz, i on se prelijeva u susjedne stanice pod istim pravilima kao ostale dinamičke funkcije niza, opisane u članku o rasponima prelijevanja i dinamičkim nizovima. To je istinski korisno za izvlačenje cijelog zapisa iz tablice jednom formulom, a i najbrži je način prepisivanja stupca koji ste namjeravali zadržati
Odabir načina kad nitko ne gleda ekran
Generiranje na strani poslužitelja zaslužuje stroniju politiku od interaktivne uporabe, jer nema čovjeka koji bi primijetio da ukupna vrijednost izgleda pogrešno. Obranjiv zadani izbor je način pretrage 1 s načinom poklapanja 0: linearan, točan, neovisan o poretku, i nemoguć za poništiti ponovnim sortiranjem lista. Posegnite za načinom pretrage 2 samo tamo gdje isti put koda proizveo i poredak, u istom pokretanju, preko istog stupca, i zapišite tu ovisnost pored formule, jer je binarna pretraga na stupcu sortiranom po drugom ključu najjeftiniji mogući način izračunavanja samouvjerenog pogrešnog broja. Kad je pretraga istinski vruća, a podaci istinski sortirani, isplata je stvarna: spust čita otprilike log n stanica umjesto n, a svako od tih čitanja prolazi kroz puno razrješavanje stanice radnog sveščića, tako da je ušteda veća nego što broj instrukcija sugerira
Ako je oblik problema bliži pravilu domene nego pretrazi, povratni poziv u vaš vlastiti Pascal kod, kako je pokriveno u članku o prilagođenim funkcijama radnog lista, obično će nadmašiti bilo koji lukavi raspored ugrađenih. Implementacije XLOOKUP i XMATCH opisane ovdje isporučuju se sa standardnom HotXLS Delphi komponentom proračunske tablice, čija stranica proizvoda nosi punu referencu podržanih funkcija za Delphi i C++Builder