XLOOKUP ja XMATCH binäärihakutilat Delphissä

HotXLS, natiivi Delphi- ja C++Builder-taulukkolaskentakomponentti, arvioi XLOOKUP:n ja XMATCH:n yhden jaetun hakuytimen kautta. Tuo ydin hyväksyy neljä täsmäystilaa (-1, 0, 1, 2) ja neljä hakutilaa (-2, -1, 1, 2), ajaa logaritmisen binäärilaskun aina kun hakutilan itseisarvo on 2, ja hylkää jokaisen muun yhdistelmän kaavavirheellä

Vikailmoitus, joka lähettää sinut tänne, ei koskaan sano "hakutila". Se sanoo, että palvelimella generoitu työkirja näyttää eri luvun kuin sama tiedosto Excelissä avattuna, ehkä neljällä rivillä yhdeksästätuhannesta. Noilla neljällä rivillä on aina jotain yhteistä: kaksoiskappaleeksi kopioitu hakuavain, tai likimääräinen täsmäys, jonka piti valita naapuri, tai hakusarake, jonka joku lajitteli eri sarakkeen mukaan viime viikolla. Hakufunktiot ovat paikka, jossa kaavamoottori lakkaa olemasta pelkkää aritmetiikkaa ja alkaa olla sopimus, ja sopimuksessa on lausekkeita, joita useimmat kutsujat eivät koskaan lue

Mitkä tilanumerot XLOOKUP todella hyväksyy?

Täsmälleen neljä kumpaakin, ei mitään muuta. HotXLS validoi match_mode:n arvoja -1, 0, 1 ja 2 vasten ja search_mode:n arvoja -2, -1, 1 ja 2 vasten ennen kuin se koskettaa yhtäkään solua, ja mikä tahansa muu arvo palauttaa #VALUE!:n sen sijaan, että se rajattaisiin lähimpään lailliseen tilaan. Neljä täsmäystilaa ovat 0 tarkalle, -1 tarkalle tai seuraavalle pienemmälle, 1 tarkalle tai seuraavalle suuremmalle, ja 2 jokerimerkille; neljä hakutilaa ovat 1 eteenpäin suuntautuvalle lineaariselle skannaukselle, -1 taaksepäin suuntautuvalle lineaariselle skannaukselle, 2 binäärihaulle nousevan datan yli, ja -2 binäärihaulle laskevan datan yli. Niiden jättäminen pois valitsee täsmäystilan 0 ja hakutilan 1, parin, jota lähes jokainen todellinen kaava käyttää. Argumenttimääriä valvotaan samalla tavalla: XLOOKUP ottaa kolmesta kuuteen argumenttia ja XMATCH kahdesta neljään, ja mikä tahansa noiden alueiden ulkopuolella on #VALUE! ennen kuin arviointi edes alkaa

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

Yksi askel aiemmin on hiljaisempi tarkistus, joka kannattaa tuntea. Tilaparametrit saapuvat työarkin lausekkeina, joten HotXLS pakottaa ne luvuiksi, kieltäytyy NaN:sta ja äärettömyydestä, ja vaatii sitten, että luku on yhtä suuri kuin sen oma pyöristetty arvo. XLOOKUP(x, A:A, B:B, "none", 0, 1.5) on #VALUE!, ei naamioitu hakutila 2. Tällä on merkitystä, kun tila tulee solusta, jonka pyöristysraskas laskenta tuotti, mikä on yleisempää generoiduissa työkirjoissa kuin käsin kirjoitetuissa

Miksi search_mode 2 antaa väärän vastauksen lajittelemattomassa datassa?

Koska se tekee täsmälleen sen, mitä pyysit. Hakutila 2 kertoo moottorille, että hakuvektori on jo nousevassa järjestyksessä, eikä binäärihaku voi vahvistaa tuota väitettä ilman O(n)-läpikäyntiä, joka tuhoaisi syyn sen käyttämiseen. HotXLS siis luottaa kutsujaan, puolittaa välin ja palauttaa sen, mihin lasku päätyy. Lajittelemattomassa syötteessä vastaus ei ole virhe, se on hiljaa väärä, ja tämä on sopimusrikkomus eikä vika moottorissa

Microsoft dokumentoi saman epäsymmetrian XLOOKUP:lle ja XMATCH:lle: binääritilat vaativat lajitellun datan ja tuottavat virheellisiä tuloksia muuten. ISO 29500-1 -lauseke 18.17, joka määrittelee SpreadsheetML-kaavakieliopin, kantaa vanhempien LOOKUP- ja VLOOKUP-kuvausten omat nousevan järjestyksen vaatimukset, ja XLOOKUP ja XMATCH tulevat kaukana tuon tekstin jälkeen sen verran, että ne kulkevat tiedostossa muodossa _xlfn.XLOOKUP ja _xlfn.XMATCH tulevaisuuden funktioiden käytännön mukaisesti. Eri sukupolvi, sama kauppa: kutsuja toimittaa järjestysinvariantin, moottori toimittaa logaritmin

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;

Jäljitä toinen kaava, ja epäonnistuminen on täysin mekaaninen. Lasku luotaa keskimmäistä solua, lukee 10:n, päättää että 10 on pienempi kuin 40, hylkää vasemman puoliskon mukaan lukien rivin, joka todella piti 40:tä, luotaa 30:n, hylkää uudelleen ja loppuu väliin. Excel käyttäytyy samalla tavalla, mikä on koko pointti: väärän vastauksen toistaminen on yhteensopivuusvaatimus, ei kohteliaisuus. Järjestysperuste on myös tiukempi kuin "nousevat numerot", koska vertailija järjestää arvot ensin tyypin mukaan, järjestyksessä numerot, sitten teksti, sitten totuusarvot, sitten virhearvot, sitten tyhjät, ja vertaa vasta sen jälkeen tyypin sisällä. Numeeristen osakoodien sarake, jossa on kolme solua, jotka tallentavat tekstiä numeron sijaan, ei ole nouseva tuon vertailijan mukaan riippumatta siitä, miltä se näyttää ruudulla, ja binääritilat lukevat sen mielellään väärin

Minne kaksoiskappaleavaimet päätyvät?

Kaksoiskappalejuoksun deterministiseen päähän, ja se pää riippuu hakutilasta eikä tuurista. Kun binäärilasku osuu yhtä suureen avaimeen hakutilassa 2, se tallentaa position ja jatkaa sitten kaventamista vasemmalle, joten tulos on juoksun alin indeksi; hakutilassa -2, laskevan datan yli, se tallentaa position ja kaventaa oikealle, joten tulos on korkein indeksi. Lineaariset tilat ovat yksinkertaisempia: hakutila 1 palauttaa ensimmäisen osuman eteenpäin mentäessä, hakutila -1 ensimmäisen osuman taaksepäin mentäessä. Tämä on yksityiskohta, joka tuottaa avauskappaleen nelirivisen ristiriidan, koska työkirja, jonka avaimet ovat uniikit, antaa identtiset vastaukset kaikissa neljässä hakutilassa ja piilottaa eron jokaisessa testissä, jonka kirjoitit puhtaasta näytetiedostosta. Lisää yksi kaksoiskappaleeksi kopioitu asiakaskoodi tuotantodataan, ja tilat alkavat olla eri mieltä täsmälleen niillä riveillä, jotka kopioituivat: mikään ei muuttunut moottorissa, syöte vain lakkasi olemasta joukko ja muuttui monijoukoksi

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

Miten likimääräinen täsmäys valitsee kakkosen?

Pitämällä parhaan ehdokkaan mukana tarkan täsmäyksen haun rinnalla ja palauttamalla sen vain, jos yhtään tarkkaa osumaa ei ilmene. HotXLS kohtelee match_mode:n arvoa -1 muodossa "suurin arvo, joka ei ole suurempi kuin tavoite" ja arvoa 1 muodossa "pienin arvo, joka ei ole pienempi", ja molemmat ratkaistaan koko skannatun alueen yli sen sijaan, että pysähdyttäisiin ensimmäiseen hyväksyttävään naapuriin. Binääripolulla sama ajatus putoaa laskusta ilmaiseksi: jokainen askel, joka ylittää tai alittaa, päivittää ehdokkaan, joten lopullinen ehdokas on rajaelementti sen position vieressä, johon avain olisi lisätty

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

Lue sisempi ehto tarkasti, koska tasapelinratkaisu asuu siellä. Uusi solu korvaa nykyisen ehdokkaan vain kun se on aidosti parempi, ei koskaan kun se vain on yhtä suuri, joten useiden solujen, joissa on sama kakkosarvo, joukosta säilytettäväksi valitaan ensimmäinen, joka kohdataan skannausjärjestyksessä: alin indeksi eteenpäin skannauksessa, korkein indeksi taaksepäin skannauksessa. Jos XLOOKUP ja XMATCH eivät löydä tarkkaa osumaa eivätkä hyväksyttävää naapuria, XLOOKUP palaa if_not_found-argumenttiinsa, kun sellainen annettiin, ja #N/A:han, kun ei annettu, kun taas XMATCH tuottaa aina #N/A:n

Miksi jokerimerkit ja binäärihaku eivät voi elää rinnakkain

Koska jokerimerkkikuvio ei ole positio järjestyksessä. Täsmäystila 2 kysyy, täsmääkö solu maskiin, ja maskin täsmääminen vastaa kyllä tai ei; binäärilasku tarvitsee kolmisuuntaisen vastauksen, joka kertoo sille, kumpi puolisko säilytetään. Ei ole puolustettavaa tapaa kysyä, sijaitseeko ACME-* tietyn solun vasemmalla vai oikealla puolella, joten HotXLS hylkää match_mode:n arvon 2 yhdistettynä search_mode:n arvoon 2 tai -2 heti #VALUE!:llä sen sijaan, että arvaisi järjestyksen ja tuottaisi uskottavan hölynpölyn. Kaksi polkua vertaavat arvoja myös eri tavoin, mikä vahvistaa jaon: lineaarinen skannaus päättää yhtäsuuruuden kirjainkoosta riippumattomalla tekstivertailulla, tai maskin täsmäyksellä kun jokerimerkit ovat käytössä, kun taas binäärilasku päättää yhtäsuuruuden kysymällä järjestysvertailijalta nollaa. Tämä on tarkoituksellista eikä kerrostuksen sattumaa, koska binääripolku saa käyttää vain sitä relaatiota, jota se todella navigoi. Jos tarvitset jokerimerkkejä, käytä hakutilaa 1 tai -1 ja hyväksy lineaarinen kustannus, mikä on sama kompromissi, jonka asteittaisen uudelleenlaskennan takana oleva riippuvuudenseuranta on suunniteltu pitämään pois kriittiseltä polultasi

Muotovirheet: kaksiulotteiset alueet ja epätäsmäävät paluuvektorit

Molemmat funktiot vaativat aidosti yksiulotteisen hakualueen. Jos annettu alue kattaa useamman kuin yhden rivin ja useamman kuin yhden sarakkeen samaan aikaan, HotXLS palauttaa #VALUE!:n sen sijaan, että valitsisi akselin puolestasi, ja yhden rivin tai yhden sarakkeen alue luetaan sen pitkää akselia pitkin. XLOOKUP lisää toisen muotosäännön: paluualueen täytyy olla täsmälleen yhtä pitkä kuin hakualue täsmäävällä akselilla, joten pystysuuntainen haku 500 rivin yli yhdistettynä 499 rivin paluualueeseen on virhe, ei hiljaa viimeisellä rivillä ratkaistu yhden poikkeama. Kun paluualue on leveämpi kuin yksi sarake pystysuuntaiselle haulle, tai korkeampi kuin yksi rivi vaakasuuntaiselle, XLOOKUP antaa takaisin koko täsmätyn siivun taulukkona, ja se leviää naapurisoluihin samojen sääntöjen mukaan kuin muut dynaamiset taulukkofunktiot, kuvattu artikkelissa leviämisalueet ja dynaamiset taulukot. Se on aidosti hyödyllistä koko tietueen poimimiseen taulukosta yhdellä kaavalla, ja se on myös nopein tapa kirjoittaa yli sarake, jonka aioit säilyttää

Tilan valitseminen kun kukaan ei katso ruutua

Palvelinpuolen generointi ansaitsee tiukemman käytännön kuin interaktiivinen käyttö, koska ketään ihmistä ei ole huomaamassa, että summa näyttää väärältä. Puolustettava oletus on hakutila 1 täsmäystilalla 0: lineaarinen, tarkka, järjestyksestä riippumaton ja mahdoton mitätöidä lajittelemalla arkki uudelleen. Käytä hakutilaa 2 vain siellä, missä sama koodipolku myös tuotti järjestyksen, samalla ajolla, saman sarakkeen yli, ja kirjoita tuo riippuvuus muistiin kaavan viereen, koska binäärihaku sarakkeessa, joka on lajiteltu eri avaimen mukaan, on halvin mahdollinen tapa laskea itsevarma väärä luku. Kun haku on aidosti kuuma ja data aidosti lajiteltu, hyöty on todellinen: lasku lukee suuruusluokkaa log n solua n:n sijaan, ja jokainen niistä luvuista käy läpi täyden työkirjasolun ratkaisun, joten säästö on suurempi kuin käskymäärä antaisi olettaa

Jos ongelman muoto on lähempänä toimialuesääntöä kuin hakua, takaisinkutsu omaan Pascal-koodiisi, käsitelty artikkelissa mukautetut työarkkifunktiot, voittaa yleensä minkä tahansa fiksun sisäänrakennettujen funktioiden järjestelyn. Tässä käsitellyt XLOOKUP- ja XMATCH-toteutukset toimitetaan vakiona HotXLS Delphi -taulukkolaskentakomponentin mukana, jonka tuotesivu kantaa täyden tuettujen funktioiden viitteen Delphille ja C++Builderille