Questo articolo parlerà di come ottenere tutti i valori abbinati da una tabella e recuperarli in celle diverse. Questo è simile alla ricerca di più valori.
Formula generica
{=INDEX(names,SMALL(IF(groups=group_name,ROW(names)-MIN(ROW(names))+1),COLUMNS(expanding ranges))), “--List Ends--”)}
Troppe funzioni e variabili!!!. Vediamo quali sono queste variabili.
Nomi: Questa è la lista dei nomi.
Gruppi: L'elenco dei gruppi a cui appartengono anche questi nomi.
Nome del gruppo: il riferimento del nome del gruppo.
Gamma in espansione: questo è un intervallo in espansione che viene utilizzato per ottenere un numero crescente quando viene copiato a destra.
Esempio: estrarre i nomi dei dipendenti in colonne diverse in base alla loro azienda.
Supponiamo che tu abbia una tabella di dipendenti raggruppata in base alla loro azienda. La prima colonna contiene i nomi dei dipendenti e la seconda colonna contiene il nome dell'azienda.
Ora abbiamo bisogno di ottenere il nome di ogni dipendente in colonne diverse in base alla loro azienda. In altre parole, dobbiamo separarli.
Qui, ho chiamato A2:A10 come Impiegato e B2:B10 come Azienda, in modo che la formula sia facile da leggere.
Scrivi questa formula di matrice in F2. Usa CTRL+MAIUSC+INVIO per inserire questa formula.
{=INDEX(Impiegato,SMALL(IF(Azienda=$E2,ROW(Impiegato)-MIN(ROW(Impiegato))+1),COLUMNS($E$1:E1))), “--Fine elenco--” )}
Copia questa formula in tutte le celle. Estrarrà ogni singolo nome nelle diverse colonne in base al loro gruppo.
Come puoi vedere nell'immagine sopra, ogni dipendente è segregato in celle diverse.
Allora, come funziona questa formula?
Per capire la formula, diamo un'occhiata alla formula in G2
Che è =IFERROR(INDEX(Dipendente,PICCOLO(SE(Azienda=$E3,RIGA(Dipendente)-MIN(RIGA(Dipendente))+1),COLUMNS($E$1:F2))),"--Fine della lista--")
I meccanismi sono semplici e quasi uguali a più formule VLOOKUP. Il trucco è ottenere il numero di indice di ciascun dipendente da diversi gruppi e passarlo alla formula INDICE. Questo viene fatto da questa parte della formula.
SE(Società=$E3,RIGA(Dipendente)-MIN(RIGA(Dipendente))+1):
Questa parte restituisce un array di indici e false per il nome dell'azienda in $E3, che contiene "Rankwatch".
{FALSO;2;FALSO;4;FALSO;FALSO;7;FALSO;9}.
Come? Abbattiamolo dall'interno.
Qui abbiniamo il nome dell'azienda in $E3 con ogni valore in Gamma aziendale (Azienda=$E3).
Questo restituisce un array di true e false.{FALSE;VERO;FALSO;VERO;FALSO;FALSO;VERO;FALSO;VERO}.
Ora la funzione SE esegue le istruzioni VERO per VERO, che è ROW(Employee)-MIN(ROW(Dipendente))+1. Questa parte restituisce questa parte restituisce un array di indici a partire da 1 fino al numero di dipendenti {1;2;3;4;5;6;7;8;9}. La funzione if preleva i valori solo per VERO che a sua volta ci dà {FALSO;2;FALSO;4;FALSO;FALSO;7;FALSO;9}.
La formula attuale è semplificata in
=SE.ERRORE(INDICE(Impiegato,PICCOLO({FALSO;2;FALSO;4;FALSO;FALSO;7;FALSO;9},COLONNE($E$1:F2))),"--Fine della lista--"). Come sappiamo, la piccola funzione restituisce l'ennesimo valore più piccolo da un array. COLONNE($E$1:F2) questo restituisce 2. La funzione PICCOLO restituisce il secondo valore più piccolo dall'array sopra, che è 4.
Ora la formula è semplificata =IFERROR(INDICE(Dipendente,4),"--Fine della lista--"). Ora, la funzione INDICE restituisce semplicemente il quarto nome da dipendente matrice che ci dà "Sam”.
Quindi sì, ecco come estrarre i nomi dai gruppi in colonne diverse usando la funzione INDEX, SMALL, ROW, COLUMNS e IF. Se hai qualche dubbio su questa funzione o se non funziona per te, fammi sapere la sezione commenti qui sotto.
Download file:
Articoli Correlati:
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