XLOOKUP a XMATCH binárne módy hľadania v Delphi

HotXLS, natívna Delphi a C++Builder komponenta pre tabuľky, vyhodnocuje XLOOKUP a XMATCH cez jedno zdieľané jadro vyhľadávania. Toto jadro akceptuje štyri módy zhody (-1, 0, 1, 2) a štyri módy hľadania (-2, -1, 1, 2), spúšťa logaritmický binárny zostup vždy, keď je absolútny mód hľadania 2, a odmieta každú inú kombináciu s chybou vzorca

Hlásenie o chybe, ktoré vás sem privedie, nikdy nepovie „mód hľadania“. Povie, že server-generovaný zošit ukazuje iné číslo než ten istý súbor otvorený v Exceli, možno na štyroch riadkoch z deväťtisíc. Tieto štyri riadky majú vždy niečo spoločné: duplicitný kľúč vyhľadávania, alebo približnú zhodu, ktorá si musela vybrať suseda, alebo stĺpec vyhľadávania, ktorý niekto minulý týždeň zoradil podľa iného stĺpca. Vyhľadávacie funkcie sú miesto, kde formulárový engine prestáva byť aritmetikou a začína byť kontraktom, a tento kontrakt má klauzuly, ktoré si väčšina volajúcich nikdy neprečíta

Ktoré čísla módu XLOOKUP skutočne akceptuje?

Presne štyri z každého, a nič iné. HotXLS validuje match_mode voči -1, 0, 1 a 2 a search_mode voči -2, -1, 1 a 2 skôr, než sa dotkne jedinej bunky, a akákoľvek iná hodnota vráti #VALUE! namiesto toho, aby bola orezaná na najbližší legálny mód. Štyri módy zhody sú 0 pre presnú, -1 pre presnú alebo najbližšiu menšiu, 1 pre presnú alebo najbližšiu väčšiu, a 2 pre zástupné znaky; štyri módy hľadania sú 1 pre dopredný lineárny sken, -1 pre spätný lineárny sken, 2 pre binárne hľadanie nad vzostupnými dátami, a -2 pre binárne hľadanie nad zostupnými dátami. Ich vynechanie zvolí mód zhody 0 a mód hľadania 1, párovanie, ktoré používa takmer každý reálny vzorec. Počty argumentov sa strážia rovnako: XLOOKUP berie tri až šesť argumentov a XMATCH dva až štyri, a čokoľvek mimo týchto rozsahov je #VALUE! skôr, než vyhodnocovanie vôbec 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;

O krok skôr je tichšia kontrola, ktorú stojí za to poznať. Argumenty módu prichádzajú ako výrazy hárku, takže HotXLS ich prevedie na číslo, odmietne NaN a nekonečno, a potom vyžaduje, aby sa číslo rovnalo svojej vlastnej zaokrúhlenej hodnote. XLOOKUP(x, A:A, B:B, "none", 0, 1.5) je #VALUE!, nie zamaskovaný mód hľadania 2. Na tom záleží, keď mód pochádza z bunky, ktorú vyprodukoval na zaokrúhľovanie náročný výpočet, čo je bežnejšie v generovaných zošitoch než v ručne písaných

Prečo dáva search_mode 2 nesprávnu odpoveď na nezoradených dátach?

Pretože robí presne to, o čo ste požiadali. Mód hľadania 2 hovorí engine, že vektor vyhľadávania je už vo vzostupnom poradí, a binárne hľadanie nedokáže toto tvrdenie overiť bez prechodu O(n), ktorý by zničil dôvod na jeho použitie. HotXLS preto volajúcemu dôveruje, polí interval, a vráti čokoľvek, na čom zostup pristane. Na nezoradenom vstupe odpoveď nie je chyba, je ticho nesprávna, a toto je porušenie kontraktu, nie chyba v engine

Microsoft dokumentuje rovnakú asymetriu pre XLOOKUP a XMATCH: binárne módy vyžadujú zoradené dáta a inak produkujú neplatné výsledky. ISO 29500-1 klauzula 18.17, ktorá definuje gramatiku vzorcov SpreadsheetML, nesie staršie popisy LOOKUP a VLOOKUP s ich vlastnou požiadavkou vzostupného poradia, a XLOOKUP a XMATCH vznikli dostatočne po tomto texte, že v súbore cestujú ako _xlfn.XLOOKUP a _xlfn.XMATCH podľa konvencie budúcich funkcií. Iné generovanie, ten istý obchod: volajúci dodáva invariant usporiadania, engine dodáva logaritmus

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;

Sledujte druhý vzorec a zlyhanie je úplne mechanické. Zostup preskúma strednú bunku, prečíta 10, rozhodne, že 10 je menšie než 40, zahodí ľavú polovicu vrátane riadku, ktorý skutočne obsahoval 40, preskúma 30, znovu zahodí, a interval mu dôjde. Excel sa správa rovnako, a to je celý zmysel: reprodukovanie nesprávnej odpovede je požiadavka kompatibility, nie zdvorilosť. Predpoklad usporiadania je tiež prísnejší než „čísla vzostupne“, pretože komparátor najprv radí hodnoty podľa druhu, v poradí čísla, potom text, potom booleany, potom chybové hodnoty, potom prázdne, a až potom porovnáva vnútri druhu. Stĺpec numerických kódov dielov, ktorý má tri bunky ukladajúce text namiesto toho, nie je pod týmto komparátorom vzostupný bez ohľadu na to, ako vyzerá na obrazovke, a binárne módy ho ochotne nesprávne prečítajú

Kam pristanú duplicitné kľúče?

Na deterministickom konci behu duplicít, a ktorý koniec záleží od módu hľadania, nie od šťastia. Keď binárny zostup narazí na rovnaký kľúč pod módom hľadania 2, zaznamená pozíciu a potom pokračuje v zužovaní doľava, takže výsledkom je najnižší index behu; pod módom hľadania -2, nad zostupnými dátami, zaznamená pozíciu a zužuje sa doprava, takže výsledkom je najvyšší index. Lineárne módy sú jednoduchšie: mód hľadania 1 vráti prvý zásah smerom dopredu, mód hľadania -1 prvý zásah smerom dozadu. Toto je detail, ktorý produkuje nesúlad na štyroch riadkoch z úvodného odseku, pretože zošit, ktorého kľúče sú unikátne, dáva identické odpovede pod všetkými štyrmi módmi hľadania a skryje rozdiel v každom teste, ktorý napíšete z čistého vzorového súboru. Pridajte jeden duplicitný kód zákazníka do produkčných dát a módy si prestanú súhlasiť presne na tých riadkoch, ktoré sa zdvojili: v engine sa nič nezmenilo, vstup jednoducho prestal byť množinou a stal sa multimnožinou

// 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 run

Ako približná zhoda vyberá druhého v poradí?

Udržiavaním najlepšieho kandidáta popri hľadaní presnej zhody a jeho vrátením iba vtedy, keď sa neobjaví žiadny presný zásah. HotXLS chápe match_mode -1 ako „najväčšia hodnota, ktorá nie je väčšia než cieľ“ a match_mode 1 ako „najmenšia hodnota, ktorá nie je menšia“, a oba sa riešia nad celým prehľadaným regiónom namiesto zastavenia sa pri prvom prijateľnom susedovi. V binárnej ceste rovnaká myšlienka vypadne zo zostupu zadarmo: každý krok, ktorý prestrelí alebo nedostrelí, aktualizuje kandidáta, takže konečný kandidát je hraničný prvok vedľa pozície, kam by bol kľúč vložený

// 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;

Čítajte vnútornú podmienku pozorne, pretože rozhodovanie o remíze žije práve tam. Nová bunka nahradí stojaceho kandidáta iba vtedy, keď je striktne lepšia, nikdy keď sa mu iba rovná, takže spomedzi viacerých buniek s rovnakou hodnotou druhého v poradí je ponechaná tá, ktorá sa stretne prvá v poradí skenovania: najnižší index pri dopredom skene, najvyšší pri spätnom. Ak XLOOKUP a XMATCH nenájdu ani presný zásah, ani prijateľného suseda, XLOOKUP padne späť na svoj argument if_not_found, keď bol dodaný, a na #N/A, keď nebol, zatiaľ čo XMATCH vždy vráti #N/A

Prečo zástupné znaky a binárne hľadanie nemôžu koexistovať

Pretože vzor so zástupným znakom nie je pozícia v poradí. Mód zhody 2 sa pýta, či bunka zodpovedá maske, a zhoda masky odpovedá áno alebo nie; binárny zostup potrebuje trojstavovú odpoveď, ktorá mu povie, ktorú polovicu ponechať. Neexistuje obhájiteľný spôsob, ako sa spýtať, či ACME-* leží naľavo alebo napravo od danej bunky, takže HotXLS rovno odmieta match_mode 2 kombinovaný s search_mode 2 alebo -2 s #VALUE! namiesto toho, aby hádal usporiadanie a produkoval vierohodne vyzerajúci nezmysel. Tieto dve cesty tiež porovnávajú hodnoty odlišne, čo posilňuje toto rozdelenie: lineárny sken rozhoduje o rovnosti porovnaním textu nerozlišujúcim veľkosť písmen, alebo zhodou masky, keď sú zapnuté zástupné znaky, zatiaľ čo binárny zostup rozhoduje o rovnosti opýtaním sa usporiadacieho komparátora na nulu. Je to zámerné, nie náhoda vrstvenia, keďže binárna cesta smie použiť iba reláciu, podľa ktorej vlastne navigujeme. Ak potrebujete zástupné znaky, použite mód hľadania 1 alebo -1 a akceptujte lineárnu cenu, čo je ten istý kompromis, ktorý má sledovanie závislostí za inkrementálnym prepočtom navrhnuté udržať mimo vašej kritickej cesty

Chyby tvaru: dvojrozmerné rozsahy a nezhodné návratové vektory

Obe funkcie vyžadujú skutočne jednorozmerný rozsah vyhľadávania. Ak dodaný rozsah pokrýva viac než jeden riadok a viac než jeden stĺpec naraz, HotXLS vráti #VALUE! namiesto toho, aby vybral os za vás, a jednoriadkový alebo jednostĺpcový rozsah sa číta pozdĺž svojej dlhej osi. XLOOKUP pridáva druhé pravidlo tvaru: návratový rozsah musí byť presne taký dlhý ako rozsah vyhľadávania pozdĺž zodpovedajúcej osi, takže vertikálne vyhľadávanie nad 500 riadkami spárované s 499-riadkovým návratovým rozsahom je chyba, nie chyba o jedna ticho vyriešená na poslednom riadku. Keď je návratový rozsah širší než jeden stĺpec pri vertikálnom vyhľadávaní, alebo vyšší než jeden riadok pri horizontálnom, XLOOKUP vráti celý zhodujúci sa výrez ako pole a to sa rozleje do susediacich buniek podľa rovnakých pravidiel ako ostatné funkcie dynamických polí, opísané v článku o rozsahoch rozliatia a dynamických poliach. To je skutočne užitočné na vytiahnutie celého záznamu z tabuľky jedným vzorcom, a je to zároveň najrýchlejší spôsob, ako prepísať stĺpec, ktorý ste chceli zachovať

Voľba módu, keď nikto nesleduje obrazovku

Server-side generovanie si zaslúži prísnejšiu politiku než interaktívne použitie, pretože niet človeka, ktorý by si všimol, že súčet vyzerá zle. Obhájiteľné predvolené nastavenie je mód hľadania 1 s módom zhody 0: lineárne, presné, nezávislé od poradia, a nemožné znehodnotiť opätovným zoradením hárku. Siahnite po móde hľadania 2 iba tam, kde ten istý úsek kódu zároveň vyprodukoval aj usporiadanie, v tom istom behu, nad tým istým stĺpcom, a túto závislosť si zapíšte vedľa vzorca, pretože binárne hľadanie nad stĺpcom zoradeným podľa iného kľúča je najlacnejší možný spôsob, ako vypočítať sebavedomo nesprávne číslo. Keď je vyhľadávanie skutočne časté a dáta skutočne zoradené, prínos je reálny: zostup číta rádovo log n buniek namiesto n, a každé z týchto čítaní prechádza plným rozlíšením bunky zošitu, takže úspora je väčšia, než naznačuje počet inštrukcií

Ak je tvar problému bližšie k doménovému pravidlu než k vyhľadávaniu, callback do vlastného Pascal kódu, ako je pokryté v článku o vlastných funkciách hárku, zvyčajne porazí akékoľvek šikovné usporiadanie vstavaných funkcií. Implementácie XLOOKUP a XMATCH diskutované tu sú súčasťou štandardnej HotXLS Delphi komponenty pre tabuľky, ktorej stránka produktu nesie úplnú referenciu podporovaných funkcií pre Delphi a C++Builder