XLOOKUP og XMATCH binærsøkemodi i Delphi

HotXLS, den native Delphi- og C++Builder-regnearkkomponenten, evaluerer XLOOKUP og XMATCH gjennom én delt oppslagskjerne. Den kjernen aksepterer fire treffmodi (-1, 0, 1, 2) og fire søkemodi (-2, -1, 1, 2), kjører et logaritmisk binærsøk når den absolutte søkemodusen er 2, og avviser enhver annen kombinasjon med en formelfeil

Feilrapporten som sender deg hit sier aldri «søkemodus». Den sier at den serverGenererte arbeidsboken viser et annet tall enn samme fil åpnet i Excel, på kanskje fire rader av ni tusen. De fire radene har alltid noe til felles: en duplisert oppslagsnøkkel, eller et omtrentlig treff som måtte velge en nabo, eller en oppslagskolonne noen sorterte etter en annen kolonne forrige uke. Oppslagsfunksjoner er der en formelmotor slutter å være aritmetikk og begynner å være en kontrakt, og kontrakten har klausuler de fleste som kaller den aldri leser

Hvilke modustall aksepterer XLOOKUP egentlig?

Nøyaktig fire av hver, og ingenting annet. HotXLS validerer match_mode mot -1, 0, 1 og 2 og search_mode mot -2, -1, 1 og 2 før den rører en eneste celle, og enhver annen verdi returnerer #VALUE! i stedet for å bli klemt til nærmeste lovlige modus. De fire treffmodiene er 0 for eksakt, -1 for eksakt eller neste mindre, 1 for eksakt eller neste større, og 2 for jokertegn; de fire søkemodiene er 1 for et fremover-lineært søk, -1 for et bakover-lineært søk, 2 for et binærsøk over stigende data, og -2 for et binærsøk over synkende data. Å utelate dem velger treffmodus 0 og søkemodus 1, paringen nesten hver reelle formel bruker. Argumentantall håndheves på samme måte: XLOOKUP tar tre til seks argumenter og XMATCH tar to til fire, og alt utenfor de intervallene er en #VALUE! før evaluering begynner

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

Ett steg tidligere finnes en stillere sjekk verdt å kjenne til. Modusargumentene ankommer som regnearksuttrykk, så HotXLS tvinger dem til et tall, avviser NaN og uendelighet, og krever deretter at tallet er lik sin egen avrundede verdi. XLOOKUP(x, A:A, B:B, "none", 0, 1.5) er en #VALUE!, ikke en søkemodus 2 i forkledning. Det betyr noe når modusen kommer fra en celle en avrundingstung beregning produserte, som er vanligere i genererte arbeidsbøker enn i håndskrevne

Hvorfor gir search_mode 2 feil svar på usorterte data?

Fordi den gjør nøyaktig det du ba om. Søkemodus 2 forteller motoren at oppslagsvektoren allerede er i stigende rekkefølge, og et binærsøk kan ikke verifisere den påstanden uten et O(n)-pass som ville ødelegge grunnen til å bruke det. HotXLS stoler derfor på den som kaller, halverer intervallet, og returnerer hva enn søket lander på. På usorterte inndata er svaret ikke en feil, det er stille feil, og dette er et kontraktsbrudd snarere enn en defekt i motoren

Microsoft dokumenterer samme asymmetri for XLOOKUP og XMATCH: de binære modiene krever sorterte data og produserer ugyldige resultater ellers. ISO 29500-1 klausul 18.17, som definerer SpreadsheetML-formelgrammatikken, bærer de eldre LOOKUP- og VLOOKUP-beskrivelsene med sitt eget stigende-rekkefølge-krav, og XLOOKUP og XMATCH kommer så langt etter den teksten at de reiser i filen som _xlfn.XLOOKUP og _xlfn.XMATCH under fremtidsfunksjon-konvensjonen. Ulik generasjon, samme avtale: den som kaller leverer ordensinvarianten, motoren leverer logaritmen

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;

Spor den andre formelen, og feilen er fullstendig mekanisk. Søket prober midtcellen, leser 10, avgjør at 10 er mindre enn 40, forkaster venstre halvdel inkludert raden som faktisk hadde 40, prober 30, forkaster igjen, og går tom for intervall. Excel oppfører seg på samme måte, som er poenget: å reprodusere feil svar er et kompatibilitetskrav, ikke en høflighet. Ordenspremisset er også strengere enn «tall stigende», fordi komparatoren rangerer verdier etter type først, i rekkefølgen tall, deretter tekst, deretter booleaner, deretter feilverdier, deretter blanke, og sammenligner bare innenfor en type etter det. En kolonne med numeriske delkoder som har tre celler som lagrer tekst i stedet er ikke stigende under den komparatoren uansett hvordan det ser ut på skjermen, og de binære modiene vil villig mistolke den

Hvor havner duplikatnøkler?

Ved en deterministisk ende av duplikatsekvensen, og hvilken ende avhenger av søkemodusen snarere enn av flaks. Når det binære søket treffer en lik nøkkel under søkemodus 2, registrerer det posisjonen og fortsetter deretter å innsnevre mot venstre, så resultatet er den laveste indeksen i sekvensen; under søkemodus -2, over synkende data, registrerer det posisjonen og innsnevrer mot høyre, så resultatet er den høyeste indeksen. De lineære modiene er enklere: søkemodus 1 returnerer det første treffet fremover, søkemodus -1 det første treffet bakover. Dette er detaljen som produserer fire-rads-avviket fra åpningsavsnittet, fordi en arbeidsbok hvis nøkler er unike gir identiske svar under alle fire søkemodi og skjuler forskjellen gjennom hver test du skrev fra en ren eksempelfil. Legg til én duplisert kundekode i produksjonsdata, og modiene begynner å være uenige nøyaktig på radene som duplikerte: ingenting endret seg i motoren, inndataen sluttet bare å være et sett og ble et multisett

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

Hvordan velger omtrentlig treff nummer to?

Ved å holde en beste kandidat ved siden av det eksakte-treff-søket og bare returnere den hvis ikke noe eksakt treff dukker opp. HotXLS behandler match_mode -1 som «den største verdien som ikke er større enn målet» og match_mode 1 som «den minste verdien som ikke er mindre», og begge løses over hele det skannede området i stedet for å stoppe ved den første akseptable naboen. I den binære stien faller samme idé ut av søket gratis: hvert steg som overskyter eller underskyter oppdaterer kandidaten, så den endelige kandidaten er grenseelementet ved siden av posisjonen hvor nøkkelen ville blitt satt inn

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

Les den indre betingelsen nøye, fordi likestillingsavgjørelsen bor der. En ny celle erstatter den stående kandidaten bare når den er strengt bedre, aldri når den bare er lik den, så blant flere celler som holder samme nummer-to-verdi er den som beholdes den første som møtes i skannerekkefølge: den laveste indeksen under et fremoverskann, den høyeste under et bakoverskann. Hvis XLOOKUP og XMATCH verken finner et eksakt treff eller en akseptabel nabo, faller XLOOKUP tilbake til sitt if_not_found-argument når ett ble levert og til #N/A når det ikke ble det, mens XMATCH alltid gir #N/A

Hvorfor kan ikke jokertegn og binærsøk sameksistere?

Fordi et jokertegnmønster ikke er en posisjon i en rekkefølge. Treffmodus 2 spør om en celle matcher en maske, og maskematching svarer ja eller nei; et binærsøk trenger et tre-veis svar som forteller det hvilken halvdel som skal beholdes. Det finnes ingen forsvarlig måte å spørre om ACME-* ligger til venstre eller til høyre for en gitt celle, så HotXLS avviser match_mode 2 kombinert med search_mode 2 eller -2 på forhånd med #VALUE! i stedet for å gjette en rekkefølge og produsere plausibelt vrøvl. De to stiene sammenligner også verdier forskjellig, som forsterker delingen: det lineære søket avgjør likhet med en versalufølsom tekstsammenligning, eller med maskematching når jokertegn er på, mens det binære søket avgjør likhet ved å spørre ordenskomparatoren om en null. Det er bevisst snarere enn en tilfeldighet ved lagdeling, siden den binære stien bare kan bruke relasjonen den faktisk navigerer etter. Hvis du trenger jokertegn, bruk søkemodus 1 eller -1 og aksepter den lineære kostnaden, som er samme avveining avhengighetssporingen bak inkrementell omberegning er designet for å holde unna den kritiske stien din

Formfeil: todimensjonale områder og manglende samsvar i returvektorer

Begge funksjonene krever et genuint endimensjonalt oppslagsområde. Hvis det angitte området spenner over mer enn én rad og mer enn én kolonne samtidig, returnerer HotXLS #VALUE! i stedet for å velge en akse på dine vegne, og et enkeltrads- eller enkeltkolonneområde leses langs sin lange akse. XLOOKUP legger til en andre formregel: returområdet må være nøyaktig like langt som oppslagsområdet langs den matchende aksen, så et vertikalt oppslag over 500 rader paret med et 499-rads returområde er en feil, ikke en av-med-én stille løst ved siste rad. Når returområdet er bredere enn én kolonne for et vertikalt oppslag, eller høyere enn én rad for et horisontalt, gir XLOOKUP tilbake hele den matchede skiven som et array, og det sprer seg inn i nabocellene under samme regler som de andre dynamiske array-funksjonene, beskrevet i artikkelen om spill-områder og dynamiske arrayer. Det er genuint nyttig for å trekke ut en hel post fra en tabell med én formel, og det er også den raskeste måten å overskrive en kolonne du mente å beholde

Å velge en modus når ingen overvåker skjermen

Server-side generering fortjener en strengere policy enn interaktiv bruk, fordi det ikke er noe menneske til å legge merke til at en sum ser feil ut. Det forsvarlige standardvalget er søkemodus 1 med treffmodus 0: lineær, eksakt, rekkefølgeuavhengig, og umulig å ugyldiggjøre ved å sortere et ark på nytt. Grip til søkemodus 2 bare der samme kodesti også produserte rekkefølgen, i samme kjøring, over samme kolonne, og skriv den avhengigheten ned ved siden av formelen, fordi et binærsøk på en kolonne sortert etter en annen nøkkel er den billigste mulige måten å beregne et selvsikkert feil tall. Når oppslaget er genuint hett og dataene genuint sorterte, er gevinsten reell: søket leser i størrelsesorden log n celler i stedet for n, og hver av de lesingene går gjennom en full arbeidsbok-celleoppløsning, så besparelsen er større enn instruksjonsantallet antyder

Hvis formen på problemet er nærmere en domeneregel enn et oppslag, vil et tilbakekall inn i din egen Pascal-kode, som dekket i artikkelen om tilpassede regnearksfunksjoner, vanligvis slå enhver smart arrangering av de innebygde. XLOOKUP- og XMATCH-implementasjonene diskutert her leveres med standard HotXLS Delphi-regnearkkomponent, hvis produktside bærer den fullstendige referansen for støttede funksjoner for Delphi og C++Builder