Funzione INDICE di Excel (esempi + video)
Quando utilizzare la funzione INDICE di Excel
La funzione INDICE di Excel può essere utilizzata quando si desidera recuperare il valore da dati tabulari e si dispone del numero di riga e di colonna del punto dati. Ad esempio, nell'esempio seguente, puoi utilizzare la funzione INDICE per ottenere i voti di "Tom" in Fisica quando conosci il numero di riga e il numero di colonna nel set di dati.
Cosa restituisce
Restituisce il valore da una tabella per il numero di riga e il numero di colonna specificati.
Sintassi
=INDICE (array, row_num, [col_num])
=INDICE (array, num_riga, [num_col], [num_area])
La funzione INDICE ha 2 sintassi. Il primo viene utilizzato nella maggior parte dei casi, tuttavia, in caso di ricerche a tre vie, viene utilizzato il secondo (trattato nell'Esempio 5).
Argomenti di input
- Vettore - un intervallo di celle o una costante di matrice.
- numero_riga - il numero di riga da cui deve essere prelevato il valore.
- [num_colo] - il numero di colonna da cui deve essere prelevato il valore. Sebbene questo sia un argomento facoltativo, ma se row_num non è fornito, deve essere fornito.
- [numero_area] - (Facoltativo) Se l'argomento dell'array è costituito da più intervalli, questo numero verrà utilizzato per selezionare il riferimento da tutti gli intervalli.
Note aggiuntive (cose noiose… ma importanti da sapere)
- Se il numero di riga o il numero di colonna è 0, restituisce rispettivamente i valori dell'intera riga o colonna.
- Se la funzione INDICE viene utilizzata davanti a un riferimento di cella (come A1:) restituisce un riferimento di cella invece di un valore (vedi esempi sotto).
- Il più utilizzato insieme alla funzione MATCH.
- A differenza di CERCA.VERT, la funzione INDICE può restituire un valore a sinistra del valore di ricerca.
- La funzione INDICE ha due forme: la forma matrice e la forma di riferimento
- Il "modulo matrice" è il punto in cui si recupera un valore in base al numero di riga e colonna da una determinata tabella.
- Il "modulo di riferimento" è dove sono presenti più tabelle e si utilizza l'argomento area_num per selezionare la tabella e quindi recuperare un valore al suo interno utilizzando il numero di riga e colonna (vedere l'esempio live di seguito).
Funzione INDICE di Excel - Esempi
Ecco sei esempi di utilizzo della funzione INDICE di Excel.
Esempio 1 - Trovare i segni di Tom in fisica (una ricerca a due vie)
Supponiamo di avere un set di dati come mostrato di seguito:
Per trovare i voti di Tom in Fisica, usa la formula seguente:
=INDICE($B$3:$E$10,3,2)
Questa formula INDICE specifica l'array come $ B $ 3: $ E $ 10 che ha i segni per tutti i soggetti. Quindi usa il numero di riga (3) e il numero di colonna (2) per recuperare i voti di Tom in Fisica.
Esempio 2 - Rendere dinamico il valore LOOKUP utilizzando la funzione MATCH
Potrebbe non essere sempre possibile specificare manualmente il numero di riga e il numero di colonna. Potresti avere un enorme set di dati o potresti volerlo rendere dinamico in modo che identifichi automaticamente il nome e/o l'oggetto specificato nelle celle e dia il risultato corretto.
Qualcosa come mostrato di seguito:
Questo può essere fatto usando una combinazione dell'INDICE e della funzione CONFRONTA.
Ecco la formula che renderà dinamici i valori di ricerca:
=INDICE($B$3:$E$10,CONFRONTA($G$5,$A$3:$A$10,0),CONFRONTA($H$4,$B$2:$E$2,0))
Nella formula sopra, invece di codificare il numero di riga e il numero di colonna, viene utilizzata la funzione CONFRONTA per renderlo dinamico.
- Il numero di riga dinamico è dato dalla seguente parte della formula: CONFRONTA ($ G $ 5, $ A $ 3: $ A $ 10,0). Esegue la scansione del nome degli studenti e identifica il valore di ricerca ($ G $ 5 in questo caso). Quindi restituisce il numero di riga del valore di ricerca nel set di dati. Ad esempio, se il valore di ricerca è Matt, restituirà 1, se è Bob, restituirà 2 e così via.
- Il numero di colonna dinamico è dato dalla seguente parte della formula: CONFRONTA ($ H $ 4, $ B $ 2: $ E $ 2,0). Esegue la scansione dei nomi dei soggetti e identifica il valore di ricerca ($H$4 in questo caso). Quindi restituisce il numero di colonna del valore di ricerca nel set di dati. Ad esempio, se il valore di ricerca è Math, restituirà 1, se è Physics, restituirà 2 e così via.
Esempio 3 - Utilizzo di elenchi a discesa come valori di ricerca
Nell'esempio sopra, dobbiamo inserire manualmente i dati. Ciò potrebbe richiedere molto tempo ed essere soggetto a errori, soprattutto se si dispone di un elenco enorme di valori di ricerca.
Una buona idea in questi casi è creare un elenco a discesa dei valori di ricerca (in questo caso, potrebbero essere i nomi degli studenti e le materie) e quindi scegliere semplicemente dall'elenco. In base alla selezione, la formula aggiornerà automaticamente il risultato.
Qualcosa come mostrato di seguito:
Questo è un buon componente della dashboard in quanto puoi avere un enorme set di dati con centinaia di studenti nel back-end, ma l'utente finale (diciamo un insegnante) può ottenere rapidamente i voti di uno studente in una materia semplicemente effettuando le selezioni da la discesa.
Come fare questo:
La formula utilizzata in questo caso è la stessa utilizzata nell'Esempio 2.
=INDICE($B$3:$E$10,CONFRONTA($G$5,$A$3:$A$10,0),CONFRONTA($H$4,$B$2:$E$2,0))
I valori di ricerca sono stati convertiti in elenchi a discesa.
Ecco i passaggi per creare l'elenco a discesa di Excel:
- Seleziona la cella in cui desideri visualizzare l'elenco a discesa. In questo esempio, in G4, vogliamo i nomi degli studenti.
- Vai su Dati -> Strumenti dati -> Convalida dati.
- Nella finestra di dialogo Convalida dati, all'interno della scheda Impostazioni, selezionare Elenco dal menu a discesa Consenti.
- Nella fonte, seleziona $A$3:$A$10
- Fare clic su OK.
Ora avrai l'elenco a discesa nella cella G5. Allo stesso modo, puoi crearne uno in H4 per i soggetti.
Esempio 4 - Valori restituiti da un'intera riga/colonna
Negli esempi precedenti, abbiamo utilizzato la funzione INDICE di Excel per eseguire una ricerca a 2 vie e ottenere un valore singolo.
Ora, cosa succede se vuoi ottenere tutti i voti di uno studente. Ciò può consentire di trovare il punteggio massimo/minimo di quello studente o il punteggio totale ottenuto in tutte le materie.
In inglese semplice, vuoi prima ottenere l'intera riga di punteggi per uno studente (diciamo Bob) e poi all'interno di quei valori identificare il punteggio più alto o il totale di tutti i punteggi.
Ecco il trucco.
Nella funzione INDICE di Excel, quando si immette il numero di colonna come 0, restituirà i valori dell'intera riga.
Quindi la formula per questo sarebbe:
=INDICE($B$3:$E$10,CONFRONTA($G$5,$A$3:$A$10,0),0)
Ora questa formula. se usato così com'è, restituirebbe il #VALUE! errore. Mentre visualizza l'errore, nel backend restituisce un array che contiene tutti i punteggi per Tom - {57,77,91,91}.
Se selezioni la formula in modalità di modifica e premi F9, sarai in grado di vedere l'array che restituisce (come mostrato di seguito): 
Allo stesso modo, in base a quale sia il valore di ricerca, quando il numero di colonna è specificato come 0 (o viene lasciato vuoto), restituisce tutti i valori nella riga per il valore di ricerca
Ora per calcolare il punteggio totale ottenuto da Tom, possiamo semplicemente usare la formula sopra all'interno della funzione SUM.
=SOMMA(INDICE($B$3:$E$10, CONFRONTA($G$5,$A$3:$A$10,0),0))
Su linee simili, per calcolare il punteggio più alto, possiamo usare MAX/LARGE e per calcolare il minimo, possiamo usare MIN/SMALL.
Esempio 5 - Ricerca a tre vie utilizzando INDEX/MATCH
La funzione INDICE di Excel è costruita per gestire le ricerche a tre vie.
Che cos'è una ricerca a tre vie?
Negli esempi sopra, abbiamo usato una tabella con i punteggi per gli studenti in diverse materie. Questo è un esempio di ricerca bidirezionale poiché utilizziamo due variabili per recuperare il punteggio (nome dello studente e soggetto).
Ora, supponiamo che in un anno uno studente abbia tre diversi livelli di esami, Unit Test, Midterm e Final Examination (è quello che avevo quando ero studente).
Una ricerca a tre vie sarebbe la capacità di ottenere i voti di uno studente per una materia specifica dal livello di esame specificato. Ciò la renderebbe una ricerca a tre vie poiché ci sono tre variabili (nome dello studente, nome del soggetto e livello di esame).
Ecco un esempio di una ricerca a tre vie:
Nell'esempio sopra, oltre a selezionare il nome dello studente e il nome della materia, puoi anche selezionare il livello dell'esame. In base al livello dell'esame, restituisce il valore corrispondente da una delle tre tabelle.
Ecco la formula utilizzata nella cella H4:
=INDICE(($B$3:$E$7,$B$11:$E$15,$B$19:$E$23),CONFRONTA($G$4,$A$3:$A$7,0),CONFRONTA($H $3,$B$2:$E$2,0),IF($H$2="Unit Test",1,IF($H$2="Midterm",2,3)))
Analizziamo questa formula per capire come funziona.
Questa formula accetta quattro argomenti. INDICE è una di quelle funzioni in Excel che ha più di una sintassi.
=INDICE (array, row_num, [col_num])
=INDICE (array, num_riga, [num_col], [num_area])
Finora in tutto l'esempio sopra, abbiamo usato la prima sintassi, ma per fare una ricerca a tre vie, dobbiamo usare la seconda sintassi.
Ora vediamo ogni parte della formula basata sulla seconda sintassi.
- array - ($B$3:$E$7,$B$11:$E$15,$B$19:$E$23): invece di utilizzare un singolo array, in questo caso, abbiamo utilizzato tre array tra parentesi.
- num_riga - CONFRONTA($G$4,$A$3:$A$7,0): la funzione CONFRONTA viene utilizzata per trovare la posizione del nome dello studente nella cella $G$4 nell'elenco dei nomi dello studente.
- col_num - CONFRONTA($H$3,$B$2:$E$2,0): la funzione CONFRONTA viene utilizzata per trovare la posizione del nome del soggetto nella cella $H$3 nell'elenco dei nomi del soggetto.
- [area_num] - IF($H$2=”Unit Test”,1,IF($H$2=”Midterm”,2,3)): Il valore del numero dell'area indica alla funzione INDICE quale array selezionare. In questo esempio, abbiamo tre array nel primo argomento. Se si seleziona Unit Test dal menu a discesa, la funzione SE restituisce 1 e le funzioni INDICE selezionano il primo array dai tre array (che è $B$3:$E$7).
Esempio 6 - Creazione di un riferimento utilizzando la funzione INDEX (intervalli con nome dinamici)
Questo è un uso selvaggio della funzione INDICE di Excel.
Facciamo un semplice esempio.
Ho un elenco di nomi come mostrato di seguito:
Ora posso usare una semplice funzione INDICE per ottenere il cognome nell'elenco.
Ecco la formula:
=INDICE($A$2:$A$9;COUNTA($A$2:$A$9))
Questa funzione conta semplicemente il numero di celle che non sono vuote e restituisce l'ultimo elemento di questo elenco (funziona solo quando non ci sono spazi vuoti nell'elenco).
Ora, che cosa qui viene la magia.
Se metti la formula davanti a un riferimento di cella, la formula restituirà un riferimento di cella del valore corrispondente (invece del valore stesso).
=A2:INDICE($A$2:$A$9,CONTA.A($A$2:$A$9))
Ti aspetteresti che la formula sopra restituisca = A2: "Josh" (dove Josh è l'ultimo valore nell'elenco). Tuttavia, restituisce =A2:A9 e quindi ottieni una matrice di nomi come mostrato di seguito:
Un esempio pratico in cui questa tecnica può essere utile è nella creazione di intervalli denominati dinamici.
Questo è tutto in questo tutorial. Ho cercato di coprire i principali esempi di utilizzo della funzione INDICE di Excel. Se desideri vedere altri esempi aggiunti a questo elenco, fammelo sapere nella sezione commenti.
Nota: ho fatto del mio meglio per provare a leggere questo tutorial, ma nel caso in cui trovi errori o errori di ortografia, per favore fammi sapere 🙂
Funzione INDICE di Excel - Esercitazione video
- Funzione CERCA.VERT di Excel.
- Funzione CERCA.ORIZZ di Excel.
- Funzione INDIRETTA di Excel.
- Funzione CORRISPONDENZA di Excel.
- Funzione OFFSET di Excel.
Potrebbero piacerti anche i seguenti tutorial di Excel:
- VLOOKUP Vs. INDICE/MATCH
- Corrispondenza indice Excel
- Ricerca e restituzione di valori in un'intera riga/colonna.
