INDICE e CONFRONTA con array multipli
Supponiamo di avere diverse tabelle con le stesse intestazioni, come mostrato di seguito: individuare l’intervallo di valori corrispondenti ai criteri specificati potrebbe risultare complesso. In questa guida vedremo come cercare un valore tra array, intervalli o gruppi multipli abbinando criteri specifici con le funzioni INDICE, CONFRONTA e SCEGLI.
Come cercare un valore all'interno di più array?
Per identificare i leader dei diversi gruppi appartenenti a vari dipartimenti, puoi innanzitutto usare la funzione SCEGLI per selezionare la tabella da cui estrarre il nome del leader. La funzione CONFRONTA individuerà quindi la posizione esatta del leader all’interno della sua tabella di riferimento. Infine, la funzione INDICE recupererà il nome del leader in base alla posizione trovata e alla colonna specifica in cui sono elencati i nomi dei leader.
Sintassi generica
=INDEX(CHOOSE())array_num,array1 ,array2 ,…),MATCH(lookup_value,lookup_array,0),column_num)
- array_num: Il numero utilizzato da CHOOSE per selezionare un array dall'elenco array1, array2, … da cui restituire il risultato.
- array1, array2, …: Gli array da cui restituire il risultato. In questo caso si riferiscono alle tre tabelle.
- lookup_value: Il valore utilizzato dalla formula combinata per individuare la posizione del relativo leader. In questo caso, si riferisce al gruppo specificato.
- lookup_array: L'intervallo di celle in cui è elencato il lookup_value. In questo caso si riferisce all'intervallo dei gruppi. Nota: È possibile utilizzare l'intervallo dei gruppi di qualsiasi dipartimento, poiché sono tutti identici; ciò che conta è ottenere la posizione numerica corrispondente.
- column_num: La colonna specificata da cui recuperare i dati.
Per conoscere il leader del Gruppo D appartenente al Dipartimento A, copiare o inserire la formula seguente nella cella G5 e premere Invioper ottenere il risultato:
=INDICE(SCEGLI())1,$B$5:$C$8 ,$B$11:$C$14 ,$B$17:$C$20),CONFRONTA(F5,$B$5:$B$8,0),2)
√ Nota: I simboli del dollaro ($) indicano riferimenti assoluti, il che significa che gli intervalli relativi a nomi e classi nella formula non cambieranno quando la formula viene spostata o copiata in altre celle. Dopo aver inserito la formula, trascinare il quadratino di riempimento verso il basso per applicarla alle celle sottostanti e modificare di conseguenza il array_num.
Spiegazione della formula
=INDEX()CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)
- CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20):La funzione CHOOSE restituisce il 1° array tra i tre indicati nella formula, ovvero $B$5:$C$8, corrispondente all’Intervallo dati del Dipartimento A.
- MATCH(F5,$B$5:$B$8,0): Il match_type 0 obbliga la funzione MATCH a restituire la posizione della prima corrispondenza di Gruppo D, il valore contenuto nella cella F5, nell'array $B$5:$B$8, che corrisponde a 4.
- INDEX()CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2) = INDEX($B$5:$C$8,4,2La funzione INDEX recupera il valore all'incrocio tra la 4ª riga e la 2ª colonna dell'intervallo $B$5:$C$8, che corrisponde a Emily.
Per evitare di modificare ogni volta il array_numNella formula, durante la copia, è possibile utilizzare la colonna D come colonna di supporto. La formula risulterebbe così:
=INDICE(SCEGLI())D5,$B$5:$C$8 ,$B$11:$C$14 ,$B$17:$C$20),CONFRONTA(F5,$B$5:$B$8,0),2)
√ Nota: I numeri 1,2,3nella colonna di supporto indicano rispettivamente array1,array2,array3all'interno della funzione SCEGLI.
Funzioni correlate
La funzione INDICE di Excel restituisce il valore visualizzato in una posizione specificata all’interno di un intervallo o di un array.
La funzione CONFRONTA di Excel cerca un valore specifico all’interno di un intervallo di celle e ne restituisce la posizione relativa.
La funzione SCEGLI restituisce un valore dall’elenco degli argomenti in base al numero di indice specificato. Ad esempio, SCEGLI(3;«Mela»;«Pesca»;«Arancia») restituisce «Arancia»: l’indice è 3 e «Arancia» è il terzo valore dell’elenco.
Formule correlate
Intervallo di valore di ricerca da un altro foglio di lavoro o cartella di lavoro
Se sai usare la funzione CERCA.VERT per cercare valori in un foglio di lavoro, non avrai alcun problema a cercarli anche in un altro foglio o in un’altra cartella.
CERCA.VERT con nome di foglio dinamico
In molti casi, per creare un riepilogo potrebbe essere necessario raccogliere dati da più fogli di lavoro. Combinando la funzione CERCA.VERT con la funzione INDIRETTO, puoi creare una formula che cerca valori specifici nei vari fogli utilizzando un nome di foglio dinamico.
Ricerca con criteri multipli tramite INDICE e CONFRONTA
Quando si gestisce un database complesso in un foglio di calcolo Excel, con numerose colonne e intestazioni di riga, individuare un elemento che soddisfi criteri multipli può rivelarsi complicato. In questi casi, la soluzione ideale è ricorrere a una formula matriciale che combini le funzioni INDICE e CONFRONTA.
I Migliori Strumenti per la Produttività in Office
Kutools per Excel – Aiuta te a distinguerti dalla folla
Kutools per Excel Vanta oltre 300 funzionalità,assicurandoti che ciò di cui hai bisogno sia sempre a portata di clic...
Office Tab – Abilita la lettura e la modifica a schede in Microsoft Office (incluso Excel)
- Passa tra decine di documenti aperti in un secondo!
- Riduci centinaia di clic del mouse ogni giorno e dì addio al “mouse hand”.
- Aumenta la tua produttività del 50 % visualizzando e modificando più documenti contemporaneamente.
- Porta l’efficienza delle schede in Office (incluso Excel), proprio come in Chrome, Edge e Firefox.