Guarda il video - Cerca il secondo, il terzo o l'ennesimo valore di corrispondenza
Quando si tratta di cercare dati in Excel, ci sono due fantastiche funzioni che uso spesso: CERCA.VERT e INDICE (principalmente in combinazione con la funzione CONFRONTA).
Tuttavia, queste formule sono progettate per trovare solo la prima istanza del valore di ricerca.
Ma cosa succede se si desidera cercare il secondo, il terzo, il quarto o l'ennesimo valore.
Bene, è fattibile con un po' di lavoro extra.
In questo tutorial, ti mostrerò vari modi (con esempi) su come cercare il secondo o l'ennesimo valore in Excel.
Cerca il secondo, il terzo o l'ennesimo valore in Excel
In questo tutorial, tratterò due modi per cercare il secondo o l'ennesimo valore in Excel:
- Utilizzo di una colonna di supporto.
- Utilizzo di formule di matrice.
Iniziamo e tuffiamoci dentro.
Utilizzo della colonna di supporto
Supponi di essere un coordinatore della formazione in un'organizzazione e di disporre di un set di dati come mostrato di seguito. Vuoi elencare tutta la formazione davanti al nome di un dipendente.
Nel set di dati di cui sopra, i dipendenti hanno ricevuto formazione su diversi strumenti di Microsoft Office (Excel, PowerPoint e Word).
Ora puoi utilizzare la funzione CERCA.VERT o la combinazione INDICE/CONFRONTA per trovare la formazione completata da un dipendente. Tuttavia, restituirà solo la prima istanza corrispondente.
Ad esempio, nel caso di John, ha seguito tutti e tre gli allenamenti, ma quando cerco il suo nome con CERCA.VERT o INDICE/CONTINUA, restituirà sempre "Excel", che è il primo allenamento per il suo nome nell'elenco .
Per fare ciò, possiamo utilizzare una colonna di supporto e creare valori di ricerca univoci al suo interno.
Ecco i passaggi:
- Inserisci una colonna prima della colonna che elenca la formazione.
- Nella cella B2, inserisci la seguente formula:
=A2&COUNTIF($A$2:$A2,A2)
- Nella cella F2, inserisci la seguente formula e copia-incolla per tutte le altre celle:
=IFNA(CERCA.VERT($E2&COLONNE($F$1:F1),$B$2:$C$14,2,0),"")
La formula precedente restituirebbe la formazione per ciascun dipendente nell'ordine in cui appare nell'elenco. Nel caso in cui non sia elencata alcuna formazione per un dipendente, restituisce uno spazio vuoto.
Come funziona questa formula?
La formula CONTA.SE nella colonna di supporto rende unico il nome di ciascun dipendente aggiungendo un numero. Ad esempio, la prima istanza di John diventa John1, la seconda istanza diventa John2 e così via.
La formula CERCA.VERT ora utilizza questi nomi univoci dei dipendenti per trovare la formazione corrispondente.
Nota che $E2&COLUMNS($F$1:F1) è il valore di ricerca nella formula. Ciò aggiungerebbe un numero al nome del dipendente in base al numero di colonna. Ad esempio, quando questa formula viene utilizzata nella cella F2, il valore di ricerca diventa "John1". Nella cella G2, diventa "John2" e così via.
Utilizzo della formula di matrice
Se non desideri modificare il set di dati originale aggiungendo colonne di supporto, puoi anche utilizzare una formula di matrice per cercare il secondo, il terzo o l'ennesimo valore.
Supponiamo di avere lo stesso set di dati mostrato di seguito:
Ecco la formula che restituirà il valore di ricerca corretto:
=SE ERRORE(INDICE($B$2:$B$14,PICCOLO(SE($A$2:$A$14=$D2,RIGA($A$2:$A$14)-1,""),COLUMN($E$1 :E1))),"")
Copia questa formula e incollala nella cella E2.
Nota che questa è una formula di matrice e devi usare Ctrl + Maiusc + Invio (tieni premuti i tasti Ctrl e Maiusc e premi il tasto Invio), invece di premere solo il tasto Invio.
Clicca qui per scaricare il file di esempio.
Come funziona questa formula?
Rompiamo questa formula in parti e vediamo come funziona.
$A$2:$A$14=$D2
La parte precedente della formula confronta ogni cella in A2:A14 con il valore in D2. In questo set di dati, controlla se una cella contiene il nome "John" o meno.
Restituisce un array di TRUE di FALSE. Se la cella ha il nome "John", sarebbe True, altrimenti sarebbe False.
Di seguito è riportato l'array che otterresti in questo esempio:
{VERO;FALSO;FALSO;FALSO;FALSO;FALSO;VERO;FALSO;FALSO;FALSO;VERO;FALSO;FALSO}
Nota che ha TRUE in 1a, 7a e 111a posizione, poiché è lì che appare il nome John nel set di dati.
SE($A$2:$A$14=$D2,RIGA($A$2:$A$14)-1”,”)
La formula SE sopra utilizza l'array di VERO e FALSO e sostituisce VERO con la posizione della sua occorrenza nell'elenco (data da RIGA ($ A $ 2: $ A $ 14)-1) e FALSO con "" (vuoti). Quello che segue è l'array risultante che ottieni con questa formula IF:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Nota che 1, 7 e 11 sono la posizione dell'occorrenza di Giovanni nell'elenco.
PICCOLO(SE($A$2:$A$14=$D2,RIGA($A$2:$A$14)-1,””),COLUMENTI($E$1:E1))
La funzione PICCOLO ora seleziona il primo numero più piccolo, il secondo più piccolo, il terzo più piccolo da questa matrice. Si noti che utilizza la funzione COLONNE per generare il numero di colonna. Nella cella E2, la funzione COLONNE restituisce 1 e la funzione PICCOLO restituisce 1. Nella cella F2, la funzione COLONNE restituisce 2 e la funzione PICCOLO restituisce 7.
INDICE($B$2:$B$14,PICCOLO(SE($A$2:$A$14=$D2,RIGA($A$2:$A$14)-1,””),COLUMN($E$1:E1) ))
La funzione INDICE ora restituisce il valore dall'elenco nella colonna B in base alla posizione restituita dalla funzione PICCOLO. Quindi, nella cella E2, restituisce "Excel", che è il primo elemento in B2: B14. Nella cella F2, restituisce PowerPoint, che è il settimo elemento nell'elenco.
Poiché ci sono casi in cui ci sono solo uno o due corsi di formazione per alcuni dipendenti, la funzione INDICE restituirebbe un errore. La funzione IFERROR viene utilizzata per restituire uno spazio vuoto al posto dell'errore.
Nota che in questo esempio, ho usato i riferimenti di intervallo. Tuttavia, in esempi pratici, è utile convertire i dati in una tabella Excel. Convertendo in una tabella Excel, è possibile utilizzare riferimenti strutturati, che semplificano la creazione di formule. Inoltre, una tabella Excel può tenere conto automaticamente di eventuali nuovi elementi di formazione aggiunti all'elenco (quindi non è necessario modificare le formule ogni volta).
Cosa fai quando devi cercare il secondo, il terzo o l'ennesimo valore? Sono sicuro che ci sono più modi per farlo. Se usi qualcosa di più semplice di quello elencato qui, condividi con noi tutti nella sezione commenti.
Clicca qui per scaricare il file di esempio.
