Esercitazione su Excel: ricerca bidirezionale con INDICE e CORRISPONDENZA approssimativa

Sommario

Scorciatoie correlate

In questo video vedremo come creare una ricerca bidirezionale con INDICE e CONFRONTA, utilizzando una corrispondenza approssimativa.

Qui abbiamo un semplice calcolatore dei costi, che cerca il costo in base alla larghezza e all'altezza di un materiale. La corrispondenza deve essere approssimativa. Ad esempio, se la larghezza è 250 e l'altezza è 325, il risultato corretto è $ 1.800.

Se la larghezza è 450 e l'altezza rimane 325, il risultato corretto è $ 3.600.

Possiamo costruire una formula che faccia questa ricerca usando INDICE e CONFRONTA.

Innanzitutto, facciamo in modo che INDEX funzioni come prova di concetto, codificando una parte della formula. Questo è un ottimo modo per assicurarti di avere l'idea giusta, prima di iniziare.

Quindi, con i dati nella nostra tabella come array e con una larghezza di 450 e un'altezza di 325, INDICE avrà bisogno di un numero di riga 3 e un numero di colonna di 4, per recuperare il valore corretto nella tabella . Funziona bene ma ovviamente non cambierà poiché i valori sono codificati.

Quindi, impostiamo le funzioni MATCH di cui abbiamo bisogno per calcolare questi valori.

Per ottenere il valore per la larghezza, che è il numero di riga in INDICE, useremo il valore di ricerca da M7 ei valori nella colonna B, come matrice di ricerca. Per il tipo di corrispondenza, vogliamo utilizzare 1 per la corrispondenza approssimativa perché i valori sono ordinati in ordine crescente. Il risultato è 4.

Per ottenere l'altezza, che è la colonna all'interno di INDICE, useremo di nuovo CONFRONTA con il valore da M8, i valori di altezza dalla riga 6. Anche in questo caso, il tipo di corrispondenza nuovamente impostato su 1 per una corrispondenza approssimativa. Il risultato è 3.

Se cambio la larghezza a 350 e l'altezza a 550, otteniamo una nuova serie di risultati.

Questi valori sono esattamente ciò di cui abbiamo bisogno per INDICE. Quindi ora copierò e incollerò semplicemente le funzioni MATCH nella formula INDEX originale.

La larghezza va per il numero di riga.

E l'altezza va per il numero di colonna.

Ora abbiamo una ricerca dinamica che calcola correttamente il costo in base a larghezza e altezza, facendo corrispondere approssimativamente.