In questo articolo, impareremo come cercare l'ennesima corrispondenza nella tabella utilizzando la funzione INDICE E CONFRONTA.
Scenario:
Ad esempio, dobbiamo trovare l'ennesima corrispondenza numerica e i suoi risultati corrispondenti dalla tabella. Abbiamo bisogno di una formula generale che aiuti a trovare l'ennesima corrispondenza 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ù bassa dell'intervallo. La funzione INDEX accetta l'indice di riga come argomento e restituisce i risultati richiesti corrispondenti. Questa funzione trova il
Formula generica:
= INDEX ( data , MATCH ( SMALL (range, n), range, match_type ) , col_num )
dati : array di valori nella tabella senza intestazioni
gamma : lookup_array per la corrispondenza più bassa
n : numero, ennesima corrispondenza
match_type : 1 ( esatto o il più piccolo ) o 0 (corrispondenza esatta) o -1 (esatto o successivo più grande)
col_num: numero di colonna, valore richiesto da recuperare dalla colonna della tabella.
Esempio:
Le affermazioni di cui sopra possono essere fonte di confusione da comprendere. Quindi capiamo questo usando la formula in un esempio
Qui abbiamo una tabella con i dettagli sul continente mondiale. Abbiamo bisogno di trovare il codice del paese dalla tabella data dal valore di ricerca come nome del paese.
Intervallo denominato utilizzato per l'array tabella (E5:I10) e intervallo (I5:I10).
Qui abbiamo usato invece l'intervallo denominato per l'array di riferimento della cella poiché è facile da capire. Dobbiamo cercare tutti i dettagli in cui il valore del prezzo è minimo o più basso nell'intervallo.
Usa la formula sopra per ottenere il primo dettaglio che è il capitale
Usa la formula nella cella G6:
= INDICE (tabella, CORRISPONDENZA) ( PICCOLO (gamma, 1) ,intervallo, 0 ) , 4 )
Intervalli denominati
tabella (E5:I10)
gamma (I5:I10)
Spiegazione:
- La funzione PICCOLO trova la prima corrispondenza più bassa nell'intervallo e restituisce il valore alla funzione CONFRONTA.
- La funzione CONFRONTA corrisponde alla corrispondenza esatta del valore minimo nell'intervallo di prezzo e restituisce il suo indice di riga alla funzione INDICE.
- La funzione INDEX trova il valore Capital con l'indice ROW e la 4a colonna nella tabella denominata range.
La formula restituisce tutti i risultati per il primo valore più basso. Ora copia la formula per ottenere il secondo più piccolo o il terzo più basso, devi solo cambiare l'argomento n sotto la funzione PICCOLO per ottenere risultati diversi.
Ora cambia semplicemente il valore n e ottieni i risultati come mostrato di seguito.
Come puoi vedere dall'istantanea sopra, abbiamo ottenuto tutti i dettagli che corrispondono all'ennesimo valore nella tabella. Estrarre i dettagli dalla tabella utilizzando la formula sopra indicata.
Esempio:
È inoltre possibile utilizzare la funzione LARGE per ottenere l'ennesima corrispondenza dalla prima o dall'ennesima corrispondenza più alta dell'intervallo e restituire i risultati corrispondenti.
Formula generica:
= INDEX ( data , MATCH ( LARGE (range, n), range, match_type ) , col_num )
dati : array di valori nella tabella senza intestazioni
gamma : lookup_array per la corrispondenza più alta
n : numero, ennesima corrispondenza
match_type : 1 ( esatto o il più piccolo ) o 0 (corrispondenza esatta) o -1 (esatto o successivo più grande)
col_num: numero di colonna, valore richiesto da recuperare dalla colonna della tabella.
Esempio:
Le affermazioni di cui sopra possono essere fonte di confusione da comprendere. Quindi capiamo questo usando la formula in un esempio
Qui abbiamo una tabella con i dettagli sul continente mondiale. Abbiamo bisogno di trovare il codice del paese dalla tabella data dal valore di ricerca come nome del paese.
Intervallo denominato utilizzato per l'array tabella (E5:I10) e intervallo (I5:I10).
Qui abbiamo usato invece l'intervallo denominato per l'array di riferimento della cella poiché è facile da capire. Dobbiamo cercare tutti i dettagli in cui il valore del prezzo è massimo o più alto nell'intervallo.
Usa la formula sopra per ottenere il primo dettaglio che è il capitale
Usa la formula nella cella G6:
= INDICE (tabella, CORRISPONDENZA) ( GRANDE (gamma, 1) ,intervallo, 0 ) , 4 )
Intervalli denominati
tabella (E5:I10)
campo (I5:I10)
Spiegazione:
- La funzione GRANDE trova la prima corrispondenza più alta nell'intervallo e restituisce il valore alla funzione CONFRONTA.
- La funzione CONFRONTA corrisponde all'ennesima corrispondenza esatta del valore di corrispondenza nell'intervallo di prezzo e restituisce il suo indice di riga alla funzione INDICE.
- La funzione INDEX trova il valore Capital con l'indice ROW e la 4a colonna nella tabella denominata range.
La formula restituisce tutti i risultati per il primo valore più basso. Ora copia la formula per ottenere il secondo più piccolo o il terzo più basso, devi solo cambiare l'argomento n sotto la funzione PICCOLO per ottenere risultati diversi.
Ora cambia solo il valore n e ottieni i risultati come mostrato di seguito.
Come puoi vedere dall'istantanea sopra, abbiamo ottenuto tutti i dettagli che corrispondono all'ennesimo valore 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:
- Il valore di n non può essere minore di 1 o maggiore della lunghezza dell'intervallo
- 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.
Spero che tu abbia capito come cercare l'ennesima corrispondenza nella tabella utilizzando la funzione INDICE E CONFRONTA. Esplora altri articoli sul valore di ricerca di Excel 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
Vricerca per data
Unisci nome e cognome in excel