Con CERCA.VERT otteniamo sempre la prima corrispondenza. Lo stesso accade con la funzione INDEX MATCH. Quindi, come facciamo a CERCA.VERT la seconda corrispondenza o la 3a o l'ennesima? In questo articolo impareremo come ottenere l'ennesima occorrenza di un valore nell'intervallo.
Formula generica
{=PICCOLO(SE(intervallo=valore,RIGA(gamma)-RIGA(prima_cella_in_intervallo)+1),n)}
Nota: questo è un formula di matrice. Devi inserirlo con CTRL + MAIUSC + INVIO.
Gamma: l'intervallo in cui si desidera cercare na posizione di valore.
Valore: il valore di cui stai cercando nla posizione nellagamma.
First_cell_in_range: la prima cella dellagamma. Se l'intervallo è A2: A10, la prima cella dell'intervallo è A2.
n: il occorrenza numero di valori.
Vediamo un esempio per chiarire le cose.
Esempio: trova la seconda corrispondenza in Excel
Quindi qui ho questo elenco di nomi nell'intervallo excel A2: A10. Ho chiamato questa gamma come nomi. Ora voglio ottenere la posizione della seconda occorrenza di "Rony" in nomi.
Nell'immagine sopra, possiamo vedere che è in 7a posizione nell'intervallo A2: A10 (nomi). Ora dobbiamo ottenere la sua posizione usando una formula excel.
Applicare la formula generica sopra in C2 per cercare la seconda occorrenza di Rony nell'elenco.
{=PICCOLO(SE(nomi = "Rony" ,RIGA(nomi)-RIGA(A2)+1),2)}
Inseriscilo con CTRL + MAIUSC + INVIO…
E abbiamo una risposta. Mostra 7, che è corretto. Se cambi il valore di n in 3 darà 8. Se cambi il valore di n maggiore dell'occorrenza del valore nell'intervallo, restituirà l'errore #NUM.
Come funziona?
Bene, è abbastanza facile. Vediamo ogni parte una per una.
SE(nomi = "Rony" ,RIGA(nomi)-RIGA(A2)+1) :
In SE, i nomi = "Rony" restituisce un array di VERO e FALSO. VERO ogni volta che una cella è nell'intervallo nomi (A2:A10) corrisponde a "Rony".{VERO;FALSO;FALSO;FALSO;FALSO;FALSO;VERO;VERO;FALSO}.
RIGA successiva(nomi)-RIGA(A2)+1:
RIGA(nomi): qui la funzione RIGA restituisce il numero di riga di ogni cella nei nomi. {2;3;4;5;6;7;8;9;10}.
RIGA(nomi)-RIGA(A2)Quindi sottraiamo il numero di riga di A2 da ciascun valore nell'array dato. Questo ci dà un array di numeri seriali a partire da 0. {0;1;2;3;4;5;6;7;8}.
RIGA(nomi)-RIGA(A2)+1: Per ottenere numeri seriali a partire da 1, aggiungiamo 1 a ciascun valore in questa matrice. Questo ci dà il numero di serie a partire da 1. {1;2;3;4;5;6;7;8;9}.
Ora abbiamo IF({VERO;FALSO;FALSO;FALSO;FALSO;FALSO;VERO;VERO;FALSO},{1;2;3;4;5;6;7;8;9}). Questo risolve in {1;FALSE;FALSE;FALSE;FALSE;FALSE;7;8;FALSE}.
Ora abbiamo la formula risolta in SMALL({1;FALSO;FALSO;FALSO;FALSO;FALSO;7;8;FALSO},2). Ora PICCOLO restituisce il secondo valore più piccolo nell'intervallo, che è 7.
Come lo usiamo?
La domanda arriva: qual è il vantaggio di ottenere un indice grezzo dell'ennesima corrispondenza? Sarebbe più utile se potessi recuperare le informazioni correlate dall'ennesimo valore. Bene, si può fare anche quello. Se vogliamo ottenere il valore dal valore della cella adiacente dell'ennesima corrispondenza nell'intervallo nomi (A2: A10).
{=INDICE(B2:B10, PICCOLO(SE(nomi = "Rony" ,RIGA(nomi)-RIGA(A2)+1),2))}
Quindi sì ragazzi, è così che potete ottenere l'ennesima corrispondenza in un intervallo. Spero di essere stato abbastanza esplicativo. Se hai dei dubbi su questo articolo o su qualsiasi altro argomento relativo a Excel/VBA, scrivi nella sezione commenti qui sotto.
Come ottenere il numero di riga sequenziale in Excel
Vlookup Primi 5 valori con valori duplicati utilizzando INDEX-MATCH in Excel
CERCA.VERT Valori multipli
Usa INDEX e MATCH per cercare il valore
Valore di ricerca con criteri multipli
Articoli popolari:
La funzione CERCA.VERT in Excel
CONTA.SE in Excel 2016
Come utilizzare la funzione SOMMA.SE in Excel