In questo articolo, impareremo come cercare la corrispondenza più vicina utilizzando la funzione INDICE E CONFRONTA in Excel.
Scenario:
Ad esempio, abbiamo bisogno di trovare la corrispondenza numericamente più vicina e i suoi risultati corrispondenti dalla tabella. Abbiamo bisogno di una formula fissa che aiuti a trovare la corrispondenza più vicina per ottenere il valore richiesto.
Come risolvere il problema?
Per capire prima la formula, dobbiamo rivedere un po' le seguenti funzioni
- Funzione INDICE
- Funzione CONFRONTA
Ora creeremo una formula usando le funzioni sopra. La funzione CONFRONTA restituirà l'indice della corrispondenza più vicina dall'intervallo. La funzione INDEX accetta l'indice di riga come argomento e restituisce i risultati richiesti corrispondenti.
Formula generica:
{ = INDEX (dati, MATCH (MIN (ABS (dati-valore)), ABS (dati-valore), 0))}
dati : array di valori di data
valore : dato il valore della data in cui il risultato è più vicino a
match_type : 1 ( esatto o il più piccolo ) o 0 (corrispondenza esatta) o -1 (esatto o successivo più grande)
NON usa le parentesi graffe manualmente. Bretelle ricci applicate utilizzando il Ctrl + Maiusc + Invio al posto di solo accedere.
Esempio:
Le affermazioni di cui sopra possono essere complicate da capire. Quindi capiamo questo usando la formula in un esempio
Qui abbiamo alcuni valori di data casuali e un determinato valore di data.
Intervallo denominato utilizzato per i dati (D4:D12).
Qui abbiamo usato invece l'intervallo denominato per l'array di riferimento della cella poiché è facile da capire. Dobbiamo cercare la data più vicina alla data indicata nella cella G6.
Usa la formula sopra per ottenere la data più vicina.
Usa la formula:
{ = INDICE (dati, CORRISPONDENZA (MIN (ABS (dati - G6)), ABS (dati - G6), 0)) }
NON usare le parentesi graffe manualmente. Bretelle ricci applicate utilizzando il Ctrl + Maiusc + Invio al posto di solo accedere.
Spiegazione:
- La funzione ABS restituisce l'intero positivo dopo la sottrazione eseguita sui valori di data (dati - G6).
- ABS ( data - G6 ) crea un array sottraendo tutti i valori di data dal valore di data specificato nella cella G6. L'array restituito è.
{ 160; 49 ; 105 ; 1048 ; 600 ; 6 ; 702 ; 822 ; 673 }
- MIN ( ABS ( data - G6 ) ) restituisce il valore più vicino dall'array restituito dal passaggio precedente.
= INDICE (dati, CONFRONTA ( 6 , { 160; 49 ; 105 ; 1048 ; 600 ; 6 ; 702 ; 822 ; 673 } , 0 ) )
- La funzione CONFRONTA corrisponde alla corrispondenza del valore minimo più vicino nell'array restituito e restituisce il suo indice di riga alla funzione INDICE.
- La funzione INDEX trova il valore che ha restituito l'indice ROW.
dati denominati intervallo utilizzati per l'array delle date. Ora usa Ctrl + Maiusc + Invio in luogo per il solo Invio in alto ottieni il risultato in quanto si tratta di una formula di matrice. La formula di matrice è una formula in cui una matrice di valori va dal risultato di una funzione come argomento alla funzione esterna.
Come puoi vedere dall'istantanea sopra, abbiamo ottenuto la corrispondenza più vicina dalla data indicata nella tabella. Estrarre i dettagli dalla tabella utilizzando la formula sopra indicata.
Ecco alcuni osservatori osservativi durante l'utilizzo della formula di cui sopra.
Appunti:
- La funzione restituisce l'errore #NA se l'argomento della matrice di ricerca per la funzione CONFRONTA non ha la stessa lunghezza della matrice della tabella.
- La formula restituisce un errore se lookup_value non corrisponde al valore nella tabella lookup_array.
- La funzione corrisponde al valore esatto poiché l'argomento del tipo di corrispondenza alla funzione CONFRONTA è 0.
- I valori di ricerca possono essere forniti come riferimento di cella o direttamente utilizzando il simbolo di virgolette ( ") nella formula come argomenti.
- NON usare le parentesi graffe manualmente. Bretelle ricci applicate utilizzando il Ctrl + Maiusc + Invio al posto di solo accedere oppure restituisce errore.
Spero che tu abbia capito come cercare la corrispondenza più bassa o più piccola utilizzando la funzione INDICE E CONFRONTA in Excel. Esplora altri articoli sul valore di ricerca di Excel e sulle funzioni di Excel 2019 qui. Sentiti libero di esprimere le tue domande qui sotto nella casella dei commenti. Ti aiuteremo sicuramente.
Se ti è piaciuto il nostro blog, condividilo con i tuoi amici su Facebook. E puoi anche seguirci su Twitter e Facebook. Ci piacerebbe avere tue notizie, facci sapere come possiamo migliorare, completare o innovare il nostro lavoro e renderlo migliore per te. Scrivici al sito di posta elettronica
Usa INDEX e MATCH per cercare il valore : Funzione INDEX & MATCH per cercare il valore come richiesto.
Intervallo SOMMA con INDICE in Excel : Usa la funzione INDEX per trovare la SOMMA dei valori come richiesto.
Come usare la funzione INDICE in Excel : Trova l'INDICE dell'array usando la funzione INDICE spiegata con l'esempio.
Come utilizzare la funzione CONFRONTA in Excel : Trova la CONFRONTA nell'array utilizzando il valore INDICE all'interno della funzione CONFRONTA spiegata con l'esempio.
Come usare la funzione CERCA in Excel : Trova il valore di ricerca nell'array utilizzando la funzione CERCA spiegata con l'esempio.
Come utilizzare la funzione CERCA.VERT in Excel : trova il valore di ricerca nell'array utilizzando la funzione CERCA.VERT spiegata con l'esempio.
Articoli popolari
50 scorciatoie Excel per aumentare la produttività
Modifica un elenco a discesa
Riferimento assoluto in Excel
Se con la formattazione condizionale
Se con caratteri jolly
La funzione CONCAT di Excel 2019
La funzione MAXIFS di Excel 2019