Modalità di ricerca binaria XLOOKUP e XMATCH in Delphi

Sulle chiavi 1, 3, 5, 5, 5, 7, 9 in A1:A7, la formula XMATCH(5, A1:A7, 0, 2) restituisce 3. Cambiate solo l'ultimo argomento in -1 e la stessa formula restituisce 5. Puntate la prima versione su una colonna che qualcuno ha riordinato per un campo diverso, e un 5 ben visibile sul foglio può tornare come #N/A. Tutte e tre le risposte sono corrette. L'ultimo argomento di XMATCH, come il sesto argomento di XLOOKUP, è due cose insieme: un'istruzione su quale duplicato preferire, e una promessa su come i dati sono ordinati

Il report che porta qui menziona raramente le modalità di ricerca. Dice che un workbook generato disagrees con ciò che qualcuno si aspettava su una manciata di righe, e quelle righe condividono qualcosa: una chiave ripetuta, una corrispondenza approssimata che ha dovuto scegliere un vicino, o una colonna di lookup il cui ordine è cambiato dopo che la formula era stata scritta. Le sezioni qui sotto passano le regole una alla volta, con il risultato esatto che ogni formula produce

Quali valori di match_mode e search_mode accetta XLOOKUP?

HotXLS, il componente fogli di calcolo nativo per Delphi e C++Builder, valuta XLOOKUP e XMATCH attraverso una unica routine di lookup condivisa che accetta esattamente quattro modalità di corrispondenza e quattro modalità di ricerca. match_mode può essere 0 (esatta), -1 (esatta o successiva minore), 1 (esatta o successiva maggiore) o 2 (wildcard). search_mode può essere 1 (scansione dalla prima all'ultima), -1 (scansione dall'ultima alla prima), 2 (ricerca binaria su dati ascendenti) o -2 (ricerca binaria su dati discendenti). Qualunque altro numero restituisce #VALUE!; niente viene serrato nella modalità legale più vicina. Omettere gli argomenti seleziona match mode 0 e search mode 1, cioè ciò che usa la maggior parte delle formule scritte a mano

I conteggi degli argomenti vengono controllati prima di tutto ciò. XLOOKUP prende da tre a sei argomenti e XMATCH da due a quattro, e una chiamata fuori da questi intervalli è un #VALUE! prima che la valutazione cominci. Gli argomenti di modalità sono esse stesse espressioni di foglio ordinarie, quindi HotXLS converte ognuna in numero, restituisce #VALUE! per NaN, infinito o un valore fuori dall'intervallo degli interi a 32 bit, e poi tronca verso zero. 1.5 diventa search mode 1, -1.5 diventa -1, e 2.9 diventa 2. Il troncamento morde quando la modalità viene da una cella calcolata con aritmetica: un valore che si visualizza come 2 ma è conservato come 1.9999999 seleziona una scansione lineare. Scrivete la modalità come literal, o avvolgetene una calcolata in ROUND

Il programma seguente scrive una piccola tabella di tariffe e cinque lookup che esercitano quelle regole. I commenti danno il valore che ogni cella formula contiene dopo Recalculate, ricavato dalle regole sopra

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Rates');          // Sheets[1]
    Sheet.Cells[1, 1].Value := 10;  Sheet.Cells[1, 2].Value := 0.25;
    Sheet.Cells[2, 1].Value := 20;  Sheet.Cells[2, 2].Value := 0.20;
    Sheet.Cells[3, 1].Value := 30;  Sheet.Cells[3, 2].Value := 0.15;
    Sheet.Cells[4, 1].Value := 40;  Sheet.Cells[4, 2].Value := 0.10;

    // Predefiniti: match_mode 0, search_mode 1
    Sheet.Cells[1, 4].Formula := 'XLOOKUP(30,A1:A4,B1:B4)';               // 0.15
    // Esatta o successiva minore, ricerca binaria su chiavi ascendenti
    Sheet.Cells[2, 4].Formula := 'XLOOKUP(35,A1:A4,B1:B4,"none",-1,2)';   // 0.15
    // 1.5 si tronca a search_mode 1
    Sheet.Cells[3, 4].Formula := 'XLOOKUP(40,A1:A4,B1:B4,"none",0,1.5)';  // 0.10
    // 3 non è una modalità di ricerca
    Sheet.Cells[4, 4].Formula := 'XLOOKUP(40,A1:A4,B1:B4,"none",0,3)';    // #VALUE!
    // La corrispondenza a wildcard non può girare su una ricerca binaria
    Sheet.Cells[5, 4].Formula := 'XLOOKUP("4*",A1:A4,B1:B4,"none",2,2)';  // #VALUE!

    if Book.Recalculate = lxOk then
      Book.SaveAs('lookup-modes.xlsx');
  finally
    Book.Free;
  end;
end;

Perché search_mode 2 manca una chiave che sta sul foglio?

La modalità di ricerca 2 dice a HotXLS che il vettore di lookup è già ordinato in modo ascendente, e il motore ci crede. Verificare l'affermazione costerebbe un passaggio completo sulla colonna, che è esattamente il costo che una ricerca binaria esiste per evitare, così la discesa dimezza l'intervallo a ogni sonda e restituisce ovunque finisca. Microsoft documenta lo stesso contratto per XLOOKUP e XMATCH: le modalità binarie richiedono dati ordinati e danno risultati non validi altrimenti. ISO/IEC 29500-1 §18.17, la clausola delle formule SpreadsheetML, descrive i più vecchi LOOKUP e VLOOKUP con il loro proprio requisito di ordine ascendente; XLOOKUP e XMATCH sono arrivati dopo e vengono conservati nel file come _xlfn.XLOOKUP e _xlfn.XMATCH sotto il prefisso delle funzioni future

La figura traccia XLOOKUP(40, A1:A3, B1:B3, "missing", 0, 2) sulle chiavi 40, 10, 30. La prima sonda atterra sulla riga 2, legge 10, e poiché 10 è sotto 40 scarta le righe 1 e 2, compresa la riga che contiene 40. La riga 3 legge 30, anch'esso sotto 40, e l'intervallo è vuoto. La formula restituisce "missing". Con la modalità di ricerca 1 la stessa chiamata trova 40 nella riga 1

«Ascendente» significa anche qualcosa di più specifico di quanto sembri sullo schermo. Il percorso binario classifica i valori per tipo prima di confrontarli: prima i numeri, poi il testo, poi i booleani, poi i valori di errore, poi i vuoti. Una colonna di codici parte numerici in cui tre celle contengono i codici come testo non è ascendente sotto quella regola, per quanto ordinata appaia. Il testo viene confrontato senza riguardo alle maiuscole, e su Windows l'ordine viene dal locale utente attraverso CompareStringW, che tratta i trattini e le lettere accentate diversamente da un ordinamento per byte. Una colonna chiave ordinata da un database con collation binaria può quindi fallire la promessa di ordinamento esattamente sui codici che contengono punteggiatura

I vuoti che classificano per ultimi hanno una conseguenza sbilanciata. Una colonna chiave ascendente con righe vuote sotto è ancora ascendente, così puntare la modalità di ricerca 2 su un intervallo sovradimensionato tipo A2:A5001 è innocuo. Una colonna discendente seguita da righe vuote non è più discendente, e la modalità di ricerca -2 cammina dritta nei vuoti

var
  R: TXLSFormulaEvaluationResult;
begin
  // B1:B3 contiene 9, 7, 5; B4:B10 sono vuote
  R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B10,0,-2)');
  // R.Status = xlfeOk e VarIsError(R.Value): il risultato è #N/A.
  // La prima sonda legge il vuoto B5, i vuoti classificano sopra ogni numero,
  // così la discesa continua verso destra e non torna mai indietro

  // Restringere l'intervallo alle righe piene e la stessa chiamata funziona
  R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B3,0,-2)');     // 3

  // Una scansione lineare salta i vuoti e trova la chiave ovunque stia
  R := Sheet.EvaluateFormulaAt(1, 4, 'XMATCH(5,B1:B10,0,-1)');    // 3
end;

Dove atterrano le chiavi duplicate?

XMATCH e XLOOKUP atterrano su un'estremità fissa di una sequenza di duplicati, ed è la modalità di ricerca a scegliere l'estremità. Sotto la modalità di ricerca 2 la discesa binaria registra ogni chiave uguale che incontra e continua a restringere verso sinistra, così il risultato è l'indice più basso della sequenza. Sotto la modalità di ricerca -2, su dati discendenti, registra la posizione e restringe verso destra, dando l'indice più alto. Le modalità lineari restituiscono il primo colpo nella direzione di scansione: l'indice più basso per la modalità di ricerca 1, il più alto per la modalità di ricerca -1

Ecco perché i report di discrepanza si addensano su righe particolari. Un file campione con chiavi uniche dà la stessa risposta sotto tutte e quattro le modalità di ricerca, così ogni test costruito su di esso passa. Appena i dati di produzione ripetono un codice cliente, le modalità iniziano a discordare proprio sulle righe ripetute, e il codice che ha scritto le formule non è cambiato affatto

// A1:A7 contiene 1, 3, 5, 5, 5, 7, 9 (ascendente, una sequenza di tre)
Sheet.Cells[1, 3].Formula := 'XMATCH(5,A1:A7,0,1)';   // 3, primo colpo in avanti
Sheet.Cells[2, 3].Formula := 'XMATCH(5,A1:A7,0,-1)';  // 5, primo colpo all'indietro
Sheet.Cells[3, 3].Formula := 'XMATCH(5,A1:A7,0,2)';   // 3, indice più basso della sequenza

// B1:B7 contiene 9, 7, 5, 5, 5, 3, 1 (discendente)
Sheet.Cells[4, 3].Formula := 'XMATCH(5,B1:B7,0,-2)';  // 5, indice più alto della sequenza

Come la corrispondenza approssimata sceglie il vice

Quando nessuna cella eguaglia il valore cercato, la modalità -1 restituisce il valore più grande non maggiore del target e la modalità 1 restituisce il più piccolo non minore. Nelle modalità lineari HotXLS scansisce l'intero vettore e tiene un miglior candidato, così la corrispondenza approssimata funziona su dati non ordinati. Una cella sostituisce il candidato in carica solo quando è strettamente più vicino, mai quando lo eguaglia soltanto. Fra parecchie celle che contengono lo stesso valore di vice, quella tenuta è la prima incontrata nell'ordine di scansione

Prendete le chiavi 10, 30, 20, 30, 50, 40 in A1:A6. XMATCH(35, A1:A6, -1, 1) restituisce 2: il primo 30 diventa il candidato, e il secondo 30 lo eguaglia soltanto. Girate la scansione con XMATCH(35, A1:A6, -1, -1) e il risultato è 4, perché scandendo all'indietro si incontra prima l'altro 30. XMATCH(35, A1:A6, 1, 1) restituisce 6, dato che 40 batte 50 come valore più piccolo sopra 35. Su dati ordinati le modalità binarie raggiungono lo stesso vicino in una manciata di sonde: su 10, 20, 30, 40, 50, XMATCH(35, A1:A5, -1, 2) restituisce 3 e XMATCH(35, A1:A5, 1, 2) restituisce 4

Se non spunta né un colpo esatto né un vicino accettabile, XLOOKUP restituisce il suo argomento if_not_found quando ne è stato fornito uno e #N/A quando no. XMATCH non ha argomento di fallback e restituisce sempre #N/A

Perché wildcard e ricerca binaria non possono coesistere

Un pattern wildcard non ha posizione in un ordine di ordinamento. La modalità 2 chiede se una cella calza una maschera, che è una domanda sì o no, mentre una discesa binaria richiede una risposta a tre vie che dica quale metà tenere. Non c'è modo difendibile di dire se ACME-* stia a sinistra o a destra di una data cella, così HotXLS restituisce #VALUE! per la modalità 2 combinata con la modalità di ricerca 2 o -2 e non indovina. Usate la modalità di ricerca 1 o -1 con le wildcard e accettate il costo lineare

La modalità 2 applica sempre le regole wildcard: * sta per qualunque sequenza di caratteri, ? per un carattere, e ~ rende literal il carattere successivo. XLOOKUP("PN~*", ...) trova quindi il testo PN* in sé, il che conta quando i codici prodotto contengono davvero asterischi. Nella modalità 0 la scansione lineare confronta il testo senza riguardo alle maiuscole, e il percorso binario tratta due valori come uguali quando il suo confronto d'ordinamento riferisce un pareggio. Anche quel confronto è case-insensitive, ma è lo stesso ordinamento consapevole del locale descritto sopra, che è l'unica relazione che una ricerca binaria possa legittimamente usare. I fogli ricchi di lookup plasmano anche ciò che il tracciamento delle dipendenze dietro il ricalcolo incrementale deve rivalutare dopo una modifica

Errori di forma e fette di ritorno in spill

Entrambe le funzioni richiedono un intervallo di lookup unidimensionale. Un intervallo che copre più di una riga e più di una colonna allo stesso tempo restituisce #VALUE!; HotXLS non sceglie un asse per vostro conto. XLOOKUP aggiunge una seconda regola: l'intervallo di ritorno deve avere la stessa lunghezza dell'intervallo di lookup lungo l'asse della corrispondenza. Un lookup verticale su 500 righe abbinato a un intervallo di ritorno di 499 righe è un errore, e l'ultima riga non viene mai scartata in silenzio. Quando l'intervallo di lookup è una singola cella, la forma dell'intervallo di ritorno decide se il lookup conta come verticale o orizzontale

Un intervallo di ritorno più largo di una colonna, per un lookup verticale, fa restituire a XLOOKUP l'intera riga corrispondente come array. Va in spill nelle celle vicine sotto le stesse regole delle altre funzioni ad array dinamico, trattate nell'articolo su intervalli di spill e array dinamici. Le celle vuote dentro una fetta in spill tornano come 0. Tirare fuori un intero record da una tabella con una formula è comodo, ed è anche il modo più rapido di sovrascrivere una colonna che per caso stava a destra della formula

Scegliere una modalità per workbook che nessuno guarda

La generazione lato server merita una policy più rigida dell'uso interattivo, perché nessuna persona guarda il risultato prima che parta. Lasciate lì match mode 0 con search mode 1: la corrispondenza è esatta, la scansione ignora l'ordine delle righe, e riordinare il foglio non può cambiare la risposta. La modalità di ricerca 2 appartiene solo dove lo stesso codice ha prodotto l'ordinamento, nella stessa esecuzione, sulla stessa colonna. Una ricerca binaria su una colonna ordinata per una chiave diversa è il modo più economico di calcolare con sicurezza un numero sbagliato

Quando spostate davvero un lookup in modalità binaria, controllate l'ordinamento voi stessi nel punto in cui emettete la formula. La funzione qui sotto non fa parte di HotXLS; è una guardia da aggiungere al vostro codice. Per molti lookup esatti contro una stessa tabella, la proprietà del workbook RecalcLookupCache lascia che Recalculate riusi la prima scansione di un intervallo per i lookup successivi contro di esso. La cache si spegne da sola quando il passaggio contiene OFFSET, INDIRECT, riferimenti circolari o tabelle dati What-If, e i risultati sono gli stessi comunque

// Una guardia vostra, non una API HotXLS: conferma che una colonna chiave
// numerica sia ascendente prima di emettere una formula search_mode 2 che ci conta
function KeysAscending(Sheet: TXLSXWorksheet; Col, FirstRow,
  LastRow: Integer): Boolean;
var
  Row: Integer;
  Prev, Cur: Variant;
begin
  Result := False;
  Prev := Sheet.Cells[FirstRow, Col].Value;
  if not VarIsNumeric(Prev) then Exit;
  for Row := FirstRow + 1 to LastRow do
  begin
    Cur := Sheet.Cells[Row, Col].Value;
    if (not VarIsNumeric(Cur)) or (Cur < Prev) then Exit;  // testo, vuoto o fuori ordine
    Prev := Cur;
  end;
  Result := True;
end;

// Foglio Rates: chiavi in A2:A5001, tariffe in B2:B5001
UseBinary := KeysAscending(Rates, 1, 2, 5001);
Book.RecalcLookupCache := True;
for Row := 2 to OrderCount + 1 do
  if UseBinary then
    Orders.Cells[Row, 5].Formula := Format(
      'XLOOKUP(B%d,Rates!$A$2:$A$5001,Rates!$B$2:$B$5001,0,-1,2)', [Row])
  else
    Orders.Cells[Row, 5].Formula := Format(
      'XLOOKUP(B%d,Rates!$A$2:$A$5001,Rates!$B$2:$B$5001,"missing")', [Row]);

La guardia è deliberatamente conservativa: una singola cella vuota o di testo rimanda l'intera colonna al percorso lineare. Su una colonna ordinata e calda il guadagno della modalità binaria è reale, circa log₂ n letture per lookup invece di fino a n, il che per 5.000 chiavi significa 13 sonde al massimo

Quando un lookup è davvero una regola di business in traveste, come una tariffa a scaglioni con eccezioni, un callback nel vostro codice Pascal di solito è più chiaro di qualunque arrangiamento di funzioni integrate; l'articolo sulle funzioni personalizzate di foglio di lavoro mostra come registrarne una. XLOOKUP, XMATCH e il resto del motore di formule descritto qui fanno parte del componente di fogli di calcolo HotXLS per Delphi, e la sua pagina prodotto elenca le funzioni di foglio supportate per Delphi e C++Builder