HotXLS, izvorna komponenta preglednic za Delphi in C++Builder, vrednoti XLOOKUP in XMATCH prek enega samega skupnega iskalnega jedra. To jedro sprejme štiri načine ujemanja (-1, 0, 1, 2) in štiri načine iskanja (-2, -1, 1, 2), izvede logaritmičen binaren sestop, kadar koli je absolutna vrednost načina iskanja 2, in zavrne vsako drugo kombinacijo z napako formule
Poročilo o hrošču, ki vas pripelje sem, nikoli ne omeni "način iskanja". Pravi, da strežniško ustvarjen delovni zvezek prikazuje drugačno število kot ista datoteka, odprta v Excelu, na morda štirih vrsticah od devet tisoč. Te štiri vrstice imajo vedno nekaj skupnega: podvojen iskalni ključ, ali približno ujemanje, ki je moralo izbrati soseda, ali iskalni stolpec, ki ga je nekdo prejšnji teden razvrstil po drugem stolpcu. Iskalne funkcije so mesto, kjer formulski pogon preneha biti aritmetika in postane pogodba, pogodba pa ima klavzule, ki jih večina klicateljev nikoli ne prebere
Katera števila načina XLOOKUP dejansko sprejme?
Natanko štiri od vsakega, in nič drugega. HotXLS preveri match_mode proti -1, 0, 1 in 2 ter search_mode proti -2, -1, 1 in 2, preden se dotakne ene same celice, vsaka druga vrednost pa vrne #VALUE! namesto da bi bila priklenjena na najbližji zakonit način. Štirje načini ujemanja so 0 za natančno, -1 za natančno ali naslednje manjše, 1 za natančno ali naslednje večje in 2 za nadomestne znake; štirje načini iskanja pa so 1 za linearni pregled naprej, -1 za linearni pregled nazaj, 2 za binarno iskanje nad naraščajočimi podatki in -2 za binarno iskanje nad padajočimi podatki. Izpustitev izbere način ujemanja 0 in način iskanja 1, kombinacijo, ki jo uporablja skoraj vsaka prava formula. Število argumentov je nadzorovano na enak način: XLOOKUP sprejme od tri do šest argumentov, XMATCH pa od dva do štiri, karkoli izven teh obsegov pa je #VALUE!, preden se vrednotenje sploh zač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;En korak prej je tišje preverjanje, vredno poznati. Argumenti načina pridejo kot izrazi delovnega lista, zato jih HotXLS pretvori v število, zavrne NaN in neskončnost, nato pa zahteva, da se število ujema s svojo lastno zaokroženo vrednostjo. XLOOKUP(x, A:A, B:B, "none", 0, 1.5) je #VALUE!, ne prikrit način iskanja 2. To je pomembno, kadar način pride iz celice, ki jo je proizvedel izračun, poln zaokroževanja, kar je pri strežniško ustvarjenih delovnih zvezkih pogostejše kot pri ročno pisanih
Zakaj search_mode 2 na nerazvrščenih podatkih da napačen odgovor?
Ker počne natanko to, kar ste zahtevali. Način iskanja 2 pogonu pove, da je iskalni vektor že v naraščajočem vrstnem redu, binarno iskanje pa te trditve ne more preveriti brez prehoda O(n), ki bi uničil razlog za njegovo uporabo. HotXLS zato zaupa klicatelju, prepolovi interval in vrne, karkoli sestop pripelje. Na nerazvrščenem vhodu odgovor ni napaka, temveč tiho napačen, to pa je kršitev pogodbe, ne napaka v pogonu
Microsoft dokumentira isto asimetrijo za XLOOKUP in XMATCH: binarna načina zahtevata razvrščene podatke in sicer proizvedeta neveljavne rezultate. ISO 29500-1 klavzula 18.17, ki opredeljuje slovnico formul SpreadsheetML, nosi starejša opisa LOOKUP in VLOOKUP z njuno lastno zahtevo po naraščajočem vrstnem redu, XLOOKUP in XMATCH pa sledita temu besedilu dovolj pozno, da v datoteki potujeta kot _xlfn.XLOOKUP in _xlfn.XMATCH po konvenciji za prihodnje funkcije. Različna generacija, ista pogodba: klicatelj priskrbi invarianto vrstnega reda, pogon priskrbi logaritem
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;Sledite drugi formuli, in napaka je povsem mehanska. Sestop preveri srednjo celico, prebere 10, ugotovi, da je 10 manjše od 40, zavrže levo polovico, vključno z vrstico, ki je dejansko nosila 40, preveri 30, spet zavrže, in mu zmanjka intervala. Excel se obnaša enako, kar je bistvo: ponovitev napačnega odgovora je zahteva za združljivost, ne vljudnost. Predpostavka o vrstnem redu je tudi strožja od "naraščajoča števila", ker primerjalnik najprej razvrsti vrednosti po vrsti, po vrstnem redu števila, nato besedilo, nato logične vrednosti, nato vrednosti napak, nato prazne, in znotraj vrste primerja šele nato. Stolpec numeričnih šifr delov, ki ima tri celice, shranjene kot besedilo namesto števila, ni naraščajoč po tem primerjalniku, ne glede na to, kako izgleda na zaslonu, binarna načina pa ga bosta veselo napačno prebrala
Kje pristanejo podvojeni ključi?
Na determinističnem koncu podvojenega niza, kateri konec pa je odvisen od načina iskanja, ne od sreče. Ko binarni sestop zadene enak ključ pod načinom iskanja 2, zabeleži položaj in nato nadaljuje zoženje v levo, tako da je rezultat najnižji indeks niza; pod načinom iskanja -2, nad padajočimi podatki, zabeleži položaj in zoži v desno, tako da je rezultat najvišji indeks. Linearna načina sta preprostejša: način iskanja 1 vrne prvi zadetek naprej, način iskanja -1 prvi zadetek nazaj. To je podrobnost, ki proizvede štirivrstično neskladje z začetka, ker delovni zvezek, katerega ključi so edinstveni, da enake odgovore pod vsemi štirimi načini iskanja in skrije razliko skozi vsak test, ki ste ga napisali iz čiste vzorčne datoteke. Dodajte eno podvojeno šifro stranke v produkcijske podatke, in načini se začnejo ne strinjati natanko na tistih vrsticah, ki so se podvojile: v pogonu se ni nič spremenilo, vhod je preprosto prenehal biti množica in postal multimnožica
// 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 ujemanje izbere drugouvrščenega?
Tako da ob iskanju natančnega ujemanja hrani najboljšega kandidata in ga vrne le, kadar se ne pojavi natančen zadetek. HotXLS obravnava match_mode -1 kot "največjo vrednost, ki ni večja od cilja" in match_mode 1 kot "najmanjšo vrednost, ki ni manjša", oba pa se razrešita čez celotno pregledano regijo, ne z ustavitvijo pri prvem sprejemljivem sosedu. Na binarni poti ista zamisel izpade iz sestopa zastonj: vsak korak, ki preseže ali podceni, posodobi kandidata, tako da je končni kandidat mejni element poleg položaja, kamor bi bil ključ vstavljen
// 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;Pozorno preberite notranji pogoj, ker tam živi razrešitev izenačenja. Nova celica zamenja obstoječega kandidata le, kadar je strogo boljša, nikoli kadar mu je le enaka, zato je med več celicami, ki nosijo isto drugouvrščeno vrednost, obdržana tista, ki je prva srečana v vrstnem redu pregleda: najnižji indeks pri pregledu naprej, najvišji pri pregledu nazaj. Če XLOOKUP in XMATCH ne najdeta niti natančnega zadetka niti sprejemljivega soseda, se XLOOKUP zateče k svojemu argumentu if_not_found, kadar je bil podan, in k #N/A, kadar ni bil, medtem ko XMATCH vedno vrne #N/A
Zakaj nadomestni znaki in binarno iskanje ne moreta soobstajati
Ker vzorec z nadomestnimi znaki ni položaj v vrstnem redu. Način ujemanja 2 vpraša, ali se celica ujema z masko, ujemanje mask pa odgovori z da ali ne; binarni sestop potrebuje tridelen odgovor, ki mu pove, katero polovico obdržati. Ni zagovorljivega načina, da bi vprašali, ali ACME-* leži levo ali desno od dane celice, zato HotXLS vnaprej zavrne match_mode 2 v kombinaciji z search_mode 2 ali -2 z #VALUE! namesto da bi ugibal vrstni red in proizvedel verjeten nesmisel. Obe poti tudi primerjata vrednosti drugače, kar utrjuje delitev: linearni pregled odloči enakost s primerjavo besedila brez razlikovanja velikosti črk ali z ujemanjem mask, kadar so nadomestni znaki vklopljeni, medtem ko binarni sestop odloči enakost tako, da primerjalnik vrstnega reda vpraša za ničlo. To je namerno, ne naključje slojenja, saj binarna pot sme uporabiti le relacijo, po kateri dejansko navigira. Če potrebujete nadomestne znake, uporabite način iskanja 1 ali -1 in sprejmite linearni strošek, kar je isti kompromis, ki ga skuša odmakniti sledenje odvisnosti za postopno preračunavanje od vaše kritične poti
Napake oblike: dvodimenzionalni obsegi in neujemajoči se vrnitveni vektorji
Obe funkciji zahtevata resnično enodimenzionalen iskalni obseg. Če dani obseg hkrati zajema več kot eno vrstico in več kot en stolpec, HotXLS vrne #VALUE! namesto da bi izbral os namesto vas, obseg z eno vrstico ali enim stolpcem pa se bere vzdolž svoje daljše osi. XLOOKUP doda drugo pravilo oblike: vrnitveni obseg mora biti natanko tako dolg, kot je iskalni obseg vzdolž ujemajoče se osi, tako da je navpično iskanje čez 500 vrstic, uparjeno z vrnitvenim obsegom 499 vrstic, napaka, ne tiho razrešena napaka za ena na zadnji vrstici. Kadar je vrnitveni obseg širši od enega stolpca za navpično iskanje ali višji od ene vrstice za vodoravno, XLOOKUP vrne celotno ujemajočo se rezino kot polje, ki se razlije v sosednje celice po istih pravilih kot druge funkcije dinamičnih polj, opisane v članku o obsegih razlivanja in dinamičnih poljih. To je resnično koristno za izvlek celotnega zapisa iz tabele z eno formulo, hkrati pa je tudi najhitrejši način, da prepišete stolpec, ki ste ga nameravali obdržati
Izbira načina, ko nihče ne gleda zaslona
Strežniško ustvarjanje si zasluži strožjo politiko kot interaktivna raba, ker ni človeka, ki bi opazil, da je vsota videti napačna. Zagovorljiv privzeti izbor je način iskanja 1 z načinom ujemanja 0: linearen, natančen, neodvisen od vrstnega reda in nemogoče ga je razveljaviti s ponovnim razvrščanjem lista. Segnite po načinu iskanja 2 le tam, kjer je ista koda hkrati proizvedla vrstni red, v istem zagonu, nad istim stolpcem, in to odvisnost zapišite poleg formule, ker je binarno iskanje po stolpcu, razvrščenem po drugem ključu, najcenejši možen način za izračun samozavestno napačnega števila. Kadar je iskanje resnično vroče in podatki resnično razvrščeni, je donos resničen: sestop bere reda log n celic namesto n, vsako od teh branj pa gre skozi polno razrešitev celice delovnega zvezka, zato je prihranek večji, kot bi nakazovalo število ukazov
Če je oblika problema bližje domenskemu pravilu kot iskanju, bo povratni klic v vašo lastno kodo Pascal, kot je opisano v članku o funkcijah delovnega lista po meri, ponavadi premagal katero koli iznajdljivo razporeditev vgrajenih. Tukaj obravnavani implementaciji XLOOKUP in XMATCH dostavlja standardna komponenta preglednic HotXLS Delphi, katere stran izdelka nosi celoten referenčni pregled podprtih funkcij za Delphi in C++Builder