HotXLS, componenta nativă de foaie de calcul pentru Delphi și C++Builder, evaluează XLOOKUP și XMATCH printr-un singur nucleu de căutare partajat. Acel nucleu acceptă patru moduri de potrivire (-1, 0, 1, 2) și patru moduri de căutare (-2, -1, 1, 2), rulează o coborâre binară logaritmică ori de câte ori modul de căutare absolut este 2, și respinge orice altă combinație cu o eroare de formulă
Raportul de bug care vă trimite aici nu spune niciodată "mod de căutare". Spune că registrul de calcul generat de server afișează un număr diferit față de același fișier deschis în Excel, pe poate patru rânduri din nouă mii. Acele patru rânduri au întotdeauna ceva în comun: o cheie de căutare duplicată, sau o potrivire aproximativă care a trebuit să aleagă un vecin, sau o coloană de căutare pe care cineva a sortat-o după o altă coloană săptămâna trecută. Funcțiile de căutare sunt locul unde un motor de formule încetează să fie aritmetică și începe să fie contract, iar contractul are clauze pe care majoritatea apelanților nu le citesc niciodată
Ce numere de mod acceptă de fapt XLOOKUP?
Exact patru din fiecare, și nimic altceva. HotXLS validează match_mode față de -1, 0, 1 și 2 și search_mode față de -2, -1, 1 și 2 înainte de a atinge vreo celulă, iar orice altă valoare returnează #VALUE! în loc să fie plafonată la cel mai apropiat mod legal. Cele patru moduri de potrivire sunt 0 pentru exact, -1 pentru exact sau următorul mai mic, 1 pentru exact sau următorul mai mare, și 2 pentru metacaracter; cele patru moduri de căutare sunt 1 pentru o scanare liniară înainte, -1 pentru o scanare liniară inversă, 2 pentru o căutare binară peste date ascendente, și -2 pentru o căutare binară peste date descendente. Omiterea lor selectează modul de potrivire 0 și modul de căutare 1, perechea pe care o folosește aproape orice formulă reală. Numărul de argumente este verificat în același mod: XLOOKUP ia de la trei la șase argumente, iar XMATCH ia de la două la patru, iar orice în afara acelor intervale este un #VALUE! înainte ca evaluarea să înceapă
// 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;Cu un pas mai devreme există o verificare mai discretă care merită cunoscută. Argumentele de mod sosesc ca expresii de foaie de calcul, așa că HotXLS le convertește la un număr, refuză NaN și infinitul, apoi cere ca numărul să fie egal cu propria sa valoare rotunjită. XLOOKUP(x, A:A, B:B, "none", 0, 1.5) este un #VALUE!, nu un mod de căutare 2 deghizat. Asta contează când modul vine dintr-o celulă pe care a produs-o un calcul intens în rotunjiri, ceea ce este mai frecvent în registre de calcul generate decât în cele scrise manual
De ce dă search_mode 2 răspunsul greșit pe date nesortate?
Pentru că face exact ceea ce ați cerut. Modul de căutare 2 spune motorului că vectorul de căutare este deja în ordine ascendentă, iar o căutare binară nu poate verifica acea afirmație fără o trecere O(n) care ar distruge motivul folosirii ei. HotXLS are deci încredere în apelant, înjumătățește intervalul și returnează orice pe unde ajunge coborârea. Pe intrare nesortată, răspunsul nu este o eroare, este tacit greșit, iar aceasta este o încălcare a contractului, nu un defect al motorului
Microsoft documentează aceeași asimetrie pentru XLOOKUP și XMATCH: modurile binare cer date sortate și produc rezultate invalide altfel. ISO 29500-1 clauza 18.17, care definește gramatica de formule SpreadsheetML, poartă descrierile mai vechi LOOKUP și VLOOKUP cu propria lor cerință de ordine ascendentă, iar XLOOKUP și XMATCH postdatează acel text suficient de mult încât circulă în fișier ca _xlfn.XLOOKUP și _xlfn.XMATCH sub convenția de funcție viitoare. Generație diferită, aceeași înțelegere: apelantul furnizează invariantul de ordonare, motorul furnizează logaritmul
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;Urmăriți a doua formulă, iar eșecul este complet mecanic. Coborârea sondează celula din mijloc, citește 10, decide că 10 este mai mic decât 40, aruncă jumătatea stângă, incluzând rândul care de fapt conținea 40, sondează 30, aruncă din nou, și rămâne fără interval. Excel se comportă la fel, ceea ce este esențial: reproducerea răspunsului greșit este o cerință de compatibilitate, nu o curtoazie. Premisa de ordonare este de asemenea mai strictă decât "numere ascendente", deoarece comparatorul clasifică valorile mai întâi după tip, în ordinea numere, apoi text, apoi booleeni, apoi valori de eroare, apoi goluri, și compară doar în interiorul unui tip după aceea. O coloană de coduri numerice de piesă care are trei celule ce stochează text în loc de numere nu este ascendentă sub acel comparator, indiferent cum arată pe ecran, iar modurile binare o vor citi greșit cu plăcere
Unde ajung cheile duplicate?
La un capăt determinist al șirului de duplicate, iar care capăt depinde de modul de căutare, nu de noroc. Când coborârea binară lovește o cheie egală sub modul de căutare 2, înregistrează poziția și apoi continuă să restrângă spre stânga, așa că rezultatul este cel mai mic index al șirului; sub modul de căutare -2, peste date descendente, înregistrează poziția și restrânge spre dreapta, așa că rezultatul este cel mai mare index. Modurile liniare sunt mai simple: modul de căutare 1 returnează prima lovitură mergând înainte, modul de căutare -1 prima lovitură mergând înapoi. Acesta este detaliul care produce discrepanța de patru rânduri din paragraful de deschidere, deoarece un registru de calcul ale cărui chei sunt unice dă răspunsuri identice sub toate cele patru moduri de căutare și ascunde diferența prin orice test ați scris dintr-un fișier eșantion curat. Adăugați un cod de client duplicat la datele de producție, iar modurile încep să nu fie de acord tocmai pe rândurile care s-au duplicat: nimic nu s-a schimbat în motor, intrarea pur și simplu a încetat să fie o mulțime și a devenit o multimulțime
// 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 runCum alege potrivirea aproximativă vice-câștigătorul?
Păstrând un cel mai bun candidat alături de căutarea de potrivire exactă și returnându-l doar dacă nu apare nicio lovitură exactă. HotXLS tratează match_mode -1 ca "cea mai mare valoare care nu este mai mare decât ținta" și match_mode 1 ca "cea mai mică valoare care nu este mai mică", iar ambele sunt rezolvate pe întreaga regiune scanată, nu oprindu-se la primul vecin acceptabil. Pe calea binară, aceeași idee reiese din coborâre gratuit: fiecare pas care depășește sau nu ajunge actualizează candidatul, așa că candidatul final este elementul de graniță de lângă poziția unde ar fi fost inserată cheia
// 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;Citiți condiția interioară cu atenție, deoarece acolo trăiește regula de departajare. O celulă nouă înlocuiește candidatul curent doar când este strict mai bună, niciodată când doar îl egalează, așa că printre mai multe celule care conțin aceeași valoare vice-câștigătoare, cea păstrată este prima întâlnită în ordinea scanării: cel mai mic index sub o scanare înainte, cel mai mare sub o scanare inversă. Dacă XLOOKUP și XMATCH nu găsesc nici o lovitură exactă, nici un vecin acceptabil, XLOOKUP revine la argumentul său if_not_found când a fost furnizat unul și la #N/A când nu a fost, în timp ce XMATCH produce întotdeauna #N/A
De ce nu pot coexista metacaracterele și căutarea binară
Pentru că un tipar cu metacaractere nu este o poziție într-o ordine. Modul de potrivire 2 întreabă dacă o celulă se potrivește cu o mască, iar potrivirea de mască răspunde da sau nu; o coborâre binară are nevoie de un răspuns în trei direcții care să îi spună ce jumătate să păstreze. Nu există niciun mod apărabil de a întreba dacă ACME-* stă la stânga sau la dreapta unei celule date, așa că HotXLS respinge match_mode 2 combinat cu search_mode 2 sau -2 din start cu #VALUE! în loc să ghicească o ordonare și să producă un nonsens plauzibil. Cele două căi compară și valorile diferit, ceea ce întărește separarea: scanarea liniară decide egalitatea printr-o comparație de text insensibilă la majuscule, sau prin potrivire de mască când metacaracterele sunt activate, în timp ce coborârea binară decide egalitatea cerând comparatorului de ordonare un zero. Aceasta este deliberată, nu un accident de stratificare, deoarece calea binară poate folosi doar relația pe care de fapt o navighează. Dacă aveți nevoie de metacaractere, folosiți modul de căutare 1 sau -1 și acceptați costul liniar, aceeași compensație pe care urmărirea dependențelor din spatele recalculării incrementale este proiectată să o țină departe de calea dumneavoastră critică
Erori de formă: intervale bidimensionale și vectori de returnare nepotriviți
Ambele funcții cer un interval de căutare cu adevărat unidimensional. Dacă intervalul furnizat se întinde pe mai mult de un rând și mai mult de o coloană în același timp, HotXLS returnează #VALUE! în loc să aleagă o axă în numele dumneavoastră, iar un interval cu un singur rând sau o singură coloană este citit de-a lungul axei sale lungi. XLOOKUP adaugă o a doua regulă de formă: intervalul de returnare trebuie să fie exact la fel de lung ca intervalul de căutare de-a lungul axei de potrivire, așa că o căutare verticală peste 500 de rânduri asociată cu un interval de returnare de 499 de rânduri este o eroare, nu o eroare de indexare rezolvată tacit la ultimul rând. Când intervalul de returnare este mai lat decât o coloană pentru o căutare verticală, sau mai înalt decât un rând pentru una orizontală, XLOOKUP returnează întreaga felie potrivită ca un array, iar aceasta se revarsă în celulele vecine sub aceleași reguli ca celelalte funcții cu array dinamic, descrise în articolul despre intervalele de revărsare și array-urile dinamice. Asta este cu adevărat util pentru a extrage o înregistrare întreagă dintr-un tabel cu o singură formulă, și este de asemenea cel mai rapid mod de a suprascrie o coloană pe care intenționați să o păstrați
Alegerea unui mod când nimeni nu urmărește ecranul
Generarea pe server merită o politică mai strictă decât utilizarea interactivă, deoarece nu există niciun om care să observe că un total pare greșit. Valoarea implicită apărabilă este modul de căutare 1 cu modul de potrivire 0: liniar, exact, independent de ordine și imposibil de invalidat prin resortarea unei foi. Apelați la modul de căutare 2 doar acolo unde aceeași cale de cod a produs și ordonarea, în aceeași rulare, peste aceeași coloană, și notați acea dependență lângă formulă, deoarece o căutare binară pe o coloană sortată după o altă cheie este cel mai ieftin mod posibil de a calcula un număr greșit cu încredere. Când căutarea este cu adevărat frecventă, iar datele cu adevărat sortate, câștigul este real: coborârea citește de ordinul log n celule în loc de n, iar fiecare din acele citiri trece printr-o rezoluție completă de celulă din registrul de calcul, așa că economia este mai mare decât sugerează numărul de instrucțiuni
Dacă forma problemei este mai aproape de o regulă de domeniu decât de o căutare, un callback în propriul dumneavoastră cod Pascal, tratat în articolul despre funcțiile personalizate de foaie de calcul, va bate de obicei orice aranjament ingenios al celor predefinite. Implementările XLOOKUP și XMATCH discutate aici vin cu componenta de foaie de calcul HotXLS pentru Delphi standard, a cărei pagină de produs conține referința completă a funcțiilor suportate pentru Delphi și C++Builder