Formula generica
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Sommario
Per trovare la corrispondenza più vicina nei dati numerici, puoi usare INDICE e CONFRONTA, con l'aiuto delle funzioni ABS e MIN. Nell'esempio mostrato, la formula in F5, copiata verso il basso, è:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
dove viaggio (B5: B14) e costo (C5: C14) sono denominati intervalli.
In F5, F6 e F7, la formula restituisce il costo del viaggio più vicino a 500, 1000 e 1500, rispettivamente.
Nota: questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO, tranne che in Excel 365.
Spiegazione
Al centro, questa è una formula INDICE e CONFRONTA: CONFRONTA individua la posizione della corrispondenza più vicina, alimenta la posizione a INDICE e INDICE restituisce il valore in quella posizione nella colonna Viaggio. Il duro lavoro viene svolto con la funzione MATCH, che è accuratamente configurata per abbinare la "differenza minima" in questo modo:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Facendo le cose passo dopo passo, il valore di ricerca viene calcolato con MIN e ABS in questo modo:
MIN(ABS(cost-E5)
Innanzitutto, il valore in E5 viene sottratto dal costo dell'intervallo denominato (C5: C14). Questa è un'operazione di matrice e poiché ci sono 10 valori nell'intervallo, il risultato è un array con 10 valori come questo:
(899;199;250;-201;495;1000;450;-101;500;795)
Questi numeri rappresentano la differenza tra ogni costo in C5: C15 e il costo nella cella E5, 700. Alcuni valori sono negativi perché un costo è inferiore al numero in E5. Per convertire i valori negativi in valori positivi, utilizziamo la funzione ABS:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
che restituisce:
(899;199;250;201;495;1000;450;101;500;795)
Stiamo cercando la corrispondenza più vicina, quindi usiamo la funzione MIN per trovare la differenza più piccola, che è 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Questo diventa il valore di ricerca all'interno di MATCH. L'array di ricerca viene generato come prima:
ABS(cost-E5) // generate lookup array
che restituisce lo stesso array che abbiamo visto prima:
(899;199;250;201;495;1000;450;101;500;795)
Ora abbiamo ciò di cui abbiamo bisogno per trovare la posizione della corrispondenza più vicina (differenza minima) e possiamo riscrivere la parte CORRISPONDENZA della formula in questo modo:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Con 101 come valore di ricerca, CONFRONTA restituisce 8, poiché 101 si trova all'ottava posizione nella matrice. Infine, questa posizione viene alimentato nella INDEX come argomento fila, con l'intervallo denominato viaggio come matrice:
=INDEX(trip,8)
e INDICE restituisce l'ottavo viaggio nella gamma, "Spagna". Quando la formula viene copiata nelle celle F6 e F7, trova la corrispondenza più vicina a 1000 e 1500, "Francia" e "Thailandia" come mostrato.
Nota: se c'è un pareggio, questa formula restituirà la prima corrispondenza.
Con XLOOKUP
La funzione XLOOKUP fornisce un modo interessante per risolvere questo problema, perché un tipo di corrispondenza di 1 (corrispondenza esatta o successiva più grande) o -1 (corrispondenza esatta o successiva più piccola) non richiede l'ordinamento dei dati. Ciò significa che possiamo scrivere una formula come questa:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Come sopra, usiamo il valore assoluto di (cost-E5) per creare un array di ricerca:
(899;199;250;201;495;1000;450;101;500;795)
Quindi configuriamo XLOOKUP per cercare zero, con il tipo di corrispondenza impostato su 1, per la corrispondenza esatta o il successivo più grande. Forniamo l'intervallo denominato viaggio come la matrice di ritorno, in modo che il risultato è "Spagna" come prima.
