HotXLS, nativna Delphi i C++Builder komponenta tabelarnog proračuna, evaluira XLOOKUP i XMATCH kroz jedno deljeno jezgro pretrage. To jezgro prihvata četiri režima poklapanja (-1, 0, 1, 2) i četiri režima pretrage (-2, -1, 1, 2), pokreće logaritamski binarni descent kad god je apsolutni režim pretrage 2, i odbacuje svaku drugu kombinaciju sa greškom formule
Bug report koji vas ovde šalje nikad ne kaže "režim pretrage". Kaže da radna sveska generisana na serveru pokazuje drugačiji broj nego isti fajl otvoren u Excel-u, na možda četiri reda od devet hiljada. Ta četiri reda uvek imaju nešto zajedničko: dupliran ključ pretrage, ili približno poklapanje koje je moralo izabrati suseda, ili kolonu pretrage koju je neko sortirao po drugačijoj koloni prošle nedelje. Funkcije pretrage su mesto gde formula engine prestaje da bude aritmetika i počinje da bude ugovor, a ugovor ima klauzule koje većina pozivalaca nikad ne pročita
Koje brojeve režima XLOOKUP zapravo prihvata?
Tačno četiri od svakog, i ništa drugo. HotXLS validira match_mode naspram -1, 0, 1 i 2, i search_mode naspram -2, -1, 1 i 2 pre nego što dodirne ijednu ćeliju, a bilo koja druga vrednost vraća #VALUE! umesto da se stegne na najbliži legalan režim. Četiri režima poklapanja su 0 za tačno, -1 za tačno ili sledeće manje, 1 za tačno ili sledeće veće, i 2 za wildcard; četiri režima pretrage su 1 za linearno skeniranje unapred, -1 za linearno skeniranje unazad, 2 za binarnu pretragu preko rastućih podataka, i -2 za binarnu pretragu preko opadajućih podataka. Njihovo izostavljanje bira režim poklapanja 0 i režim pretrage 1, uparivanje koje koristi skoro svaka realna formula. Broj argumenata se čuva na isti način: XLOOKUP uzima od tri do šest argumenata, a XMATCH od dva do četiri, i sve van tih opsega je #VALUE! pre nego što evaluacija 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 provera vredna znanja. Argumenti režima stižu kao izrazi radnog lista, tako da ih HotXLS prisiljava na broj, odbija NaN i beskonačnost, a zatim zahteva da broj bude jednak svojoj zaokruženoj vrednosti. XLOOKUP(x, A:A, B:B, "none", 0, 1.5) je #VALUE!, ne skriven režim pretrage 2. To je bitno kad režim dolazi iz ćelije koju je proizveo proračun sa puno zaokruživanja, što je češće u generisanim radnim sveskama nego u ručno pisanim
Zašto search_mode 2 daje pogrešan odgovor na nesortiranim podacima?
Zato što radi tačno ono što ste tražili. Režim pretrage 2 govori engine-u da je vektor pretrage već u rastućem redosledu, a binarna pretraga ne može proveriti tu tvrdnju bez O(n) prolaza koji bi uništio razlog za njeno korišćenje. HotXLS zato veruje pozivaocu, prepolovi interval, i vrati šta god descent iznedri. Na nesortiranom ulazu odgovor nije greška, već tiho pogrešan, i ovo je kršenje ugovora, a ne defekt u engine-u
Microsoft dokumentuje istu asimetriju za XLOOKUP i XMATCH: binarni režimi zahtevaju sortirane podatke i inače proizvode nevažeće rezultate. ISO 29500-1 klauzula 18.17, koja definiše gramatiku formula SpreadsheetML, nosi starije opise LOOKUP i VLOOKUP sa sopstvenim zahtevom rastućeg redosleda, a XLOOKUP i XMATCH nastaju dovoljno kasnije od tog teksta da putuju u fajlu kao _xlfn.XLOOKUP i _xlfn.XMATCH pod konvencijom budućih funkcija. Drugačija generacija, ista pogodba: pozivalac obezbeđuje invarijantu redosleda, engine obezbeđuje 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 otkaz je potpuno mehanički. Descent proba srednju ćeliju, čita 10, odluči da je 10 manje od 40, odbaci levu polovinu uključujući red koji je zapravo držao 40, proba 30, ponovo odbaci, i ostane bez intervala. Excel se ponaša isto, što je poenta: reprodukovanje pogrešnog odgovora je zahtev kompatibilnosti, ne ljubaznost. Premisa redosleda je takođe stroža od "brojevi rastu", jer komparator prvo rangira vrednosti po vrsti, redom brojevi, pa tekst, pa boolean, pa vrednosti greške, pa prazne, i samo poredi unutar vrste posle toga. Kolona numeričkih šifri delova koja ima tri ćelije koje umesto toga skladište tekst nije rastuća pod tim komparatorom bez obzira kako izgleda na ekranu, i binarni režimi će je rado pogrešno pročitati
Gde slete dupli ključevi?
Na determinisanom kraju niza duplikata, a koji kraj zavisi od režima pretrage, a ne od sreće. Kad binarni descent pogodi jednak ključ pod režimom pretrage 2, beleži poziciju i zatim nastavlja sužavanje ulevo, tako da je rezultat najniži indeks niza; pod režimom pretrage -2, preko opadajućih podataka, beleži poziciju i sužava udesno, tako da je rezultat najviši indeks. Linearni režimi su jednostavniji: režim pretrage 1 vraća prvi pogodak idući unapred, režim pretrage -1 prvi pogodak idući unazad. Ovo je detalj koji proizvodi neslaganje na četiri reda iz uvodnog pasusa, jer radna sveska čiji su ključevi jedinstveni daje identične odgovore pod sva četiri režima pretrage i sakriva razliku kroz svaki test koji ste napisali iz čistog uzoračkog fajla. Dodajte jednu dupliranu šifru klijenta u produkcione podatke i režimi počinju da se ne slažu tačno na redovima koji su se duplirali: ništa se nije promenilo u engine-u, ulaz je jednostavno prestao da bude 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 runner-up?
Držeći najboljeg kandidata uz pretragu tačnog poklapanja, i vraćajući ga samo ako se tačan pogodak ne pojavi. HotXLS tretira match_mode -1 kao "najveću vrednost koja nije veća od cilja" i match_mode 1 kao "najmanju vrednost koja nije manja", i oba se razrešavaju preko cele skenirane oblasti umesto zaustavljanjem na prvom prihvatljivom susedu. U binarnoj putanji ista ideja proizlazi iz descent-a besplatno: svaki korak koji promaši ili nedomaši ažurira kandidata, tako da je konačni kandidat granični element pored pozicije gde 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 unutrašnji uslov pažljivo, jer tu živi razrešenje izjednačenja. Nova ćelija zamenjuje stojećeg kandidata samo kad je strogo bolja, nikad kad mu je samo jednaka, tako da se među nekoliko ćelija koje drže istu vrednost runner-up-a čuva ona prva sretnuta u redosledu skeniranja: najniži indeks pod skeniranjem unapred, najviši pod skeniranjem unazad. Ako XLOOKUP i XMATCH ne pronađu ni tačan pogodak ni prihvatljivog suseda, XLOOKUP pada nazad na svoj argument if_not_found kad je dostavljen, a na #N/A kad nije, dok XMATCH uvek daje #N/A
Zašto wildcard-ovi i binarna pretraga ne mogu koegzistirati
Zato što wildcard šablon nije pozicija u poretku. Režim poklapanja 2 pita da li se ćelija poklapa sa maskom, a poklapanje maske odgovara da ili ne; binarni descent treba trosmeran odgovor koji mu kaže koju polovinu da zadrži. Nema odbranjivog načina da se pita da li ACME-* leži levo ili desno od date ćelije, tako da HotXLS unapred odbacuje match_mode 2 kombinovan sa search_mode 2 ili -2 sa #VALUE! umesto da nagađa redosled i proizvodi uverljiv besmisao. Dve putanje takođe upoređuju vrednosti drugačije, što pojačava podelu: linearno skeniranje odlučuje jednakost poređenjem teksta neosetljivim na velika/mala slova, ili poklapanjem maske kad su wildcard-ovi uključeni, dok binarni descent odlučuje jednakost tražeći od komparatora redosleda nulu. To je namerno, a ne slučajnost slojevanja, pošto binarna putanja sme koristiti samo relaciju kojom zaista navigira. Ako vam trebaju wildcard-ovi, koristite režim pretrage 1 ili -1 i prihvatite linearni trošak, isti kompromis koji praćenje zavisnosti iza inkrementalnog ponovnog izračunavanja dizajnirano je da drži van vaše kritične putanje
Greške oblika: dvodimenzionalni opsezi i neusklađeni povratni vektori
Obe funkcije zahtevaju istinski jednodimenzionalan opseg pretrage. Ako dostavljen opseg prostire se preko više od jednog reda i više od jedne kolone istovremeno, HotXLS vraća #VALUE! umesto da bira osu u vaše ime, a opseg jednog reda ili jedne kolone se čita duž svoje duge ose. XLOOKUP dodaje drugo pravilo oblika: povratni opseg mora biti tačno onoliko dug koliko i opseg pretrage duž ose poklapanja, tako da je vertikalna pretraga preko 500 redova uparena sa povratnim opsegom od 499 redova greška, a ne off-by-one tiho razrešen na poslednjem redu. Kad je povratni opseg širi od jedne kolone za vertikalnu pretragu, ili viši od jednog reda za horizontalnu, XLOOKUP vraća čitav pogođeni isečak kao niz i on se preliva u susedne ćelije pod istim pravilima kao ostale funkcije dinamičkog niza, opisane u članku o opsezima prelivanja i dinamičkim nizovima. To je istinski korisno za izvlačenje čitavog zapisa iz tabele jednom formulom, i takođe je najbrži način da se prepiše kolona koju ste nameravali sačuvati
Biranje režima kad niko ne gleda ekran
Generisanje na strani servera zaslužuje strožiju politiku od interaktivnog korišćenja, jer nema čoveka koji bi primetio da zbir izgleda pogrešno. Odbranjiva podrazumevana vrednost je režim pretrage 1 sa režimom poklapanja 0: linearno, tačno, nezavisno od redosleda, i nemoguće poništiti ponovnim sortiranjem lista. Posegnite za režimom pretrage 2 samo tamo gde ista putanja koda takođe proizvela redosled, u istom pokretanju, preko iste kolone, i zapišite tu zavisnost pored formule, jer binarna pretraga na koloni sortiranoj po drugačijem ključu je najjeftiniji mogući način da se izračuna uveren pogrešan broj. Kad je pretraga zaista vruća, a podaci zaista sortirani, isplata je stvarna: descent čita reda veličine log n ćelija umesto n, a svako od tih čitanja prolazi kroz punu rezoluciju ćelije radne sveske, tako da je ušteda veća nego što broj instrukcija sugeriše
Ako je oblik problema bliži pravilu domena nego pretrazi, callback u sopstveni Pascal kod, kako je pokriveno u članku o prilagođenim funkcijama radnog lista, obično će pobediti bilo koji lukavi raspored ugrađenih. Implementacije XLOOKUP i XMATCH ovde opisane isporučuju se sa standardnom HotXLS Delphi komponentom tabelarnog proračuna, čija stranica proizvoda nosi kompletnu referencu podržanih funkcija za Delphi i C++Builder