HotXLS, il componente foglio di calcolo nativo per Delphi e C++Builder, valuta XLOOKUP e XMATCH attraverso un unico nucleo di lookup condiviso. Quel nucleo accetta quattro modalità di corrispondenza (-1, 0, 1, 2) e quattro modalità di ricerca (-2, -1, 1, 2), esegue una discesa binaria logaritmica ogni volta che la modalità di ricerca assoluta è 2, e rifiuta ogni altra combinazione con un errore di formula
La segnalazione di bug che ti porta qui non dice mai "modalità di ricerca". Dice che il workbook generato dal server mostra un numero diverso rispetto allo stesso file aperto in Excel, magari su quattro righe su novemila. Quelle quattro righe hanno sempre qualcosa in comune: una chiave di lookup duplicata, o una corrispondenza approssimata che ha dovuto scegliere un vicino, o una colonna di lookup che qualcuno ha ordinato per una colonna diversa la settimana scorsa. Le funzioni di lookup sono il punto in cui un motore di formule smette di essere aritmetica e inizia a essere un contratto, e il contratto ha clausole che la maggior parte dei chiamanti non legge mai
Quali numeri di modalità accetta realmente XLOOKUP?
Esattamente quattro di ciascuno, e nient'altro. HotXLS valida match_mode contro -1, 0, 1 e 2 e search_mode contro -2, -1, 1 e 2 prima di toccare anche una sola cella, e qualsiasi altro valore restituisce #VALUE! invece di essere limitato alla modalità legale più vicina. Le quattro modalità di corrispondenza sono 0 per esatta, -1 per esatta o il valore inferiore successivo, 1 per esatta o il valore superiore successivo, e 2 per wildcard; le quattro modalità di ricerca sono 1 per una scansione lineare in avanti, -1 per una scansione lineare all'indietro, 2 per una ricerca binaria su dati ascendenti, e -2 per una ricerca binaria su dati discendenti. Ometterle seleziona la modalità di corrispondenza 0 e la modalità di ricerca 1, l'accoppiamento che usa quasi ogni formula reale. I conteggi degli argomenti sono controllati allo stesso modo: XLOOKUP prende da tre a sei argomenti e XMATCH da due a quattro, e qualsiasi cosa fuori da questi intervalli è un #VALUE! prima che inizi la valutazione
// 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;Un passo prima c'è un controllo più silenzioso che vale la pena conoscere. Gli argomenti di modalità arrivano come espressioni di foglio di lavoro, quindi HotXLS li converte in un numero, rifiuta NaN e infinito, e poi richiede che il numero sia uguale al proprio valore arrotondato. XLOOKUP(x, A:A, B:B, "none", 0, 1.5) è un #VALUE!, non una modalità di ricerca 2 travestita. Questo conta quando la modalità viene da una cella che un calcolo ricco di arrotondamenti ha prodotto, il che è più comune nei workbook generati che in quelli scritti a mano
Perché search_mode 2 dà la risposta sbagliata su dati non ordinati?
Perché sta facendo esattamente ciò che gli hai chiesto. La modalità di ricerca 2 dice al motore che il vettore di lookup è già in ordine ascendente, e una ricerca binaria non può verificare quell'affermazione senza un passaggio O(n) che distruggerebbe il motivo per usarla. HotXLS quindi si fida del chiamante, dimezza l'intervallo, e restituisce qualunque cosa la discesa produca. Su input non ordinato la risposta non è un errore, è silenziosamente sbagliata, e questa è una violazione del contratto piuttosto che un difetto nel motore
Microsoft documenta la stessa asimmetria per XLOOKUP e XMATCH: le modalità binarie richiedono dati ordinati e producono risultati non validi altrimenti. ISO 29500-1 clausola 18.17, che definisce la grammatica delle formule SpreadsheetML, porta le descrizioni più vecchie di LOOKUP e VLOOKUP con il proprio requisito di ordine ascendente, e XLOOKUP e XMATCH sono posteriori a quel testo abbastanza da viaggiare nel file come _xlfn.XLOOKUP e _xlfn.XMATCH secondo la convenzione delle funzioni future. Generazione diversa, stesso patto: il chiamante fornisce l'invariante di ordinamento, il motore fornisce il logaritmo
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;Traccia la seconda formula e il fallimento è interamente meccanico. La discesa sonda la cella centrale, legge 10, decide che 10 è minore di 40, scarta la metà sinistra includendo la riga che effettivamente conteneva 40, sonda 30, scarta di nuovo, ed esaurisce l'intervallo. Excel si comporta allo stesso modo, ed è questo il punto: riprodurre la risposta sbagliata è un requisito di compatibilità, non una cortesia. La premessa di ordinamento è anche più rigida di "numeri ascendenti", perché il comparatore classifica i valori prima per tipo, nell'ordine numeri, poi testo, poi booleani, poi valori di errore, poi vuoti, e confronta all'interno di un tipo solo dopo. Una colonna di codici parte numerici che ha tre celle che contengono testo invece è non ascendente secondo quel comparatore, indipendentemente da come appare sullo schermo, e le modalità binarie la leggeranno male senza problemi
Dove atterrano le chiavi duplicate?
Su un'estremità deterministica della serie di duplicati, e quale estremità dipende dalla modalità di ricerca piuttosto che dalla fortuna. Quando la discesa binaria incontra una chiave uguale sotto la modalità di ricerca 2 registra la posizione e poi continua a restringersi verso sinistra, così il risultato è l'indice più basso della serie; sotto la modalità di ricerca -2, su dati discendenti, registra la posizione e si restringe verso destra, così il risultato è l'indice più alto. Le modalità lineari sono più semplici: la modalità di ricerca 1 restituisce il primo colpo procedendo in avanti, la modalità di ricerca -1 il primo colpo procedendo all'indietro. Questo è il dettaglio che produce la discrepanza a quattro righe del paragrafo iniziale, perché un workbook le cui chiavi sono uniche dà risposte identiche sotto tutte e quattro le modalità di ricerca e nasconde la differenza in ogni test che hai scritto da un file di esempio pulito. Aggiungi un codice cliente duplicato ai dati di produzione e le modalità iniziano a discordare precisamente sulle righe che si sono duplicate: niente è cambiato nel motore, l'input ha semplicemente smesso di essere un insieme ed è diventato un multi-insieme
// 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 runCome sceglie il vice-vincitore la corrispondenza approssimata?
Mantenendo un miglior candidato accanto alla ricerca di corrispondenza esatta e restituendolo solo se non appare alcun colpo esatto. HotXLS tratta match_mode -1 come "il valore più grande che non è maggiore del target" e match_mode 1 come "il valore più piccolo che non è più piccolo", ed entrambi vengono risolti sull'intera regione scansionata piuttosto che fermandosi al primo vicino accettabile. Nel percorso binario la stessa idea deriva gratuitamente dalla discesa: ogni passo che supera o non raggiunge aggiorna il candidato, così il candidato finale è l'elemento di confine accanto alla posizione in cui la chiave sarebbe stata inserita
// 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;Leggi attentamente la condizione interna, perché il tie-break vive lì. Una nuova cella sostituisce il candidato in carica solo quando è strettamente migliore, mai quando lo eguaglia soltanto, così tra diverse celle che contengono lo stesso valore di vice-vincitore quella mantenuta è la prima incontrata nell'ordine di scansione: l'indice più basso sotto una scansione in avanti, il più alto sotto una scansione all'indietro. Se XLOOKUP e XMATCH non trovano né un colpo esatto né un vicino accettabile, XLOOKUP ricade sul proprio argomento if_not_found quando ne è stato fornito uno e su #N/A quando non lo è stato, mentre XMATCH produce sempre #N/A
Perché wildcard e ricerca binaria non possono coesistere
Perché un pattern wildcard non è una posizione in un ordine. La modalità di corrispondenza 2 chiede se una cella corrisponde a una maschera, e la corrispondenza con maschera risponde sì o no; una discesa binaria ha bisogno di una risposta a tre vie che le dica quale metà mantenere. Non esiste un modo difendibile di chiedere se ACME-* si trova a sinistra o a destra di una data cella, quindi HotXLS rifiuta match_mode 2 combinato con search_mode 2 o -2 fin da subito con #VALUE! invece di indovinare un ordinamento e produrre un risultato plausibile ma insensato. I due percorsi confrontano anche i valori in modo diverso, il che rafforza la separazione: la scansione lineare decide l'uguaglianza con un confronto testuale case-insensitive, o con la corrispondenza a maschera quando le wildcard sono attive, mentre la discesa binaria decide l'uguaglianza chiedendo al comparatore d'ordinamento uno zero. Questo è deliberato piuttosto che un incidente di stratificazione, poiché il percorso binario può usare solo la relazione secondo cui sta effettivamente navigando. Se ti servono le wildcard, usa la modalità di ricerca 1 o -1 e accetta il costo lineare, che è lo stesso compromesso che il tracciamento delle dipendenze dietro il ricalcolo incrementale è progettato per tenere fuori dal tuo percorso critico
Errori di forma: intervalli bidimensionali e vettori di ritorno non corrispondenti
Entrambe le funzioni richiedono un intervallo di lookup genuinamente unidimensionale. Se l'intervallo fornito si estende su più di una riga e più di una colonna contemporaneamente, HotXLS restituisce #VALUE! invece di scegliere un asse per tuo conto, e un intervallo a riga singola o colonna singola viene letto lungo il proprio asse lungo. XLOOKUP aggiunge una seconda regola di forma: l'intervallo di ritorno deve essere esattamente lungo quanto l'intervallo di lookup lungo l'asse corrispondente, così un lookup verticale su 500 righe accoppiato con un intervallo di ritorno di 499 righe è un errore, non uno sfasamento di uno risolto silenziosamente all'ultima riga. Quando l'intervallo di ritorno è più largo di una colonna per un lookup verticale, o più alto di una riga per uno orizzontale, XLOOKUP restituisce l'intera fetta corrispondente come un array e questa si riversa nelle celle vicine secondo le stesse regole delle altre funzioni ad array dinamico, descritte nell'articolo su intervalli di spill e array dinamici. Questo è genuinamente utile per estrarre un intero record da una tabella con una sola formula, ed è anche il modo più rapido per sovrascrivere una colonna che intendevi mantenere
Scegliere una modalità quando nessuno guarda lo schermo
La generazione lato server merita una policy più rigida dell'uso interattivo, perché non c'è un essere umano a notare che un totale sembra sbagliato. Il default difendibile è la modalità di ricerca 1 con la modalità di corrispondenza 0: lineare, esatta, indipendente dall'ordine, e impossibile da invalidare riordinando un foglio. Ricorri alla modalità di ricerca 2 solo dove lo stesso percorso di codice ha anche prodotto l'ordinamento, nella stessa esecuzione, sulla stessa colonna, e scrivi quella dipendenza accanto alla formula, perché una ricerca binaria su una colonna ordinata da una chiave diversa è il modo più economico possibile per calcolare un numero sbagliato con sicurezza. Quando il lookup è genuinamente caldo e i dati genuinamente ordinati il guadagno è reale: la discesa legge dell'ordine di log n celle invece di n, e ognuna di quelle letture passa attraverso una risoluzione completa di cella del workbook, quindi il risparmio è maggiore di quanto suggerisca il conteggio delle istruzioni
Se la forma del problema è più vicina a una regola di dominio che a un lookup, un callback nel tuo stesso codice Pascal, come trattato nell'articolo su funzioni personalizzate di foglio di lavoro, di solito batterà qualsiasi disposizione intelligente di quelle integrate. Le implementazioni di XLOOKUP e XMATCH discusse qui sono distribuite con il componente foglio di calcolo Delphi HotXLS standard, la cui pagina prodotto porta il riferimento completo delle funzioni supportate per Delphi e C++Builder