In questo articolo, impareremo come cercare i valori nella tabella 2d utilizzando una funzione INDICE-CONFRONTA-CONFRONTA in Excel.
Scenario:
Si supponga di dover eseguire più ricerche da una tabella con centinaia di colonne. In questi casi, l'utilizzo di formule diverse per ogni ricerca richiederà troppo tempo. Che ne dici di creare una formula di ricerca dinamica che puoi cercare tramite l'intestazione fornita. Sì, possiamo farlo. Questa formula è chiamata formula INDEX MATCH MATCH, o diciamo una formula di ricerca 2d.
Come risolvere il problema?
Per capire prima la formula, dobbiamo rivedere un po' le seguenti funzioni
- Funzione INDICE
- Funzione CONFRONTA
La funzione INDEX restituisce il valore in corrispondenza di un determinato indice in una matrice.
La funzione CONFRONTA restituisce l'indice della prima apparizione del valore in una matrice (array a dimensione singola).
Ora creeremo una formula usando le funzioni sopra. La funzione Match restituirà l'indice del valore di ricerca1 nel campo dell'intestazione della riga. E un'altra funzione CONFRONTA restituirà l'indice del valore di ricerca2 nel campo dell'intestazione della colonna. I numeri di indice verranno ora inseriti nella funzione INDEX per ottenere i valori sotto il valore di ricerca dai dati della tabella 2D.
Formula generica:
= INDEX ( data , MATCH ( lookup_value1, row_headers, 0 , MATCH ( lookup_value2, column_headers, 0 ) ) )
Dati: array di valori all'interno della tabella senza intestazioni
lookup_value1 : valore da cercare in row_header.
intestazioni_riga : matrice indice di riga da cercare.
lookup_value1 : valore da cercare in column_header.
intestazioni_colonne : colonna Matrice indice da cercare.
Esempio:
Le affermazioni di cui sopra possono essere complicate da capire. Quindi capiamo questo usando la formula in un esempio
Qui abbiamo un elenco dei punteggi ottenuti dagli studenti con la loro lista delle materie. Abbiamo bisogno di trovare il punteggio per uno specifico studente (Gary) e soggetto (studi sociali) come mostrato nell'istantanea qui sotto.
Il valore Studente1 deve corrispondere all'array Row_header e il valore Subject2 deve corrispondere all'array Column_header.
Usa la formula nella cella J6:
= INDICE ( tabella , CONFRONTA ( J5, riga, 0 , CONFRONTA ( J4, colonna, 0 ) ) )
Spiegazione:
- La funzione CONFRONTA corrisponde al valore Studente nella cella J4 con l'array di intestazione di riga e restituisce la sua posizione 3 come un numero.
- La funzione CONFRONTA corrisponde al valore Oggetto nella cella J5 con l'array di intestazione di colonna e restituisce la sua posizione 4 come numero.
- La funzione INDEX prende il numero di indice di riga e colonna e cerca nei dati della tabella e restituisce il valore corrispondente.
- L'argomento del tipo MATCH è fisso su 0. Poiché la formula estrarrà la corrispondenza esatta.
Qui i valori della formula sono dati come riferimenti di cella e row_header, table e column_header sono dati come intervalli denominati.
Come puoi vedere nell'istantanea sopra, abbiamo ottenuto il punteggio ottenuto dallo studente Gary in Soggetto Scienze Sociali come 36 .
Dimostra che la formula funziona bene e per i dubbi vedere le note sottostanti per capire.
Ora useremo la corrispondenza approssimativa con le intestazioni di riga e le intestazioni di colonna come numeri. La corrispondenza approssimativa prende solo i valori numerici poiché non è possibile applicarla ai valori di testo
Qui abbiamo un prezzo di valori in base all'altezza e alla larghezza del prodotto. Dobbiamo trovare il prezzo per una specifica altezza (34) e larghezza (21) come mostrato nell'istantanea qui sotto.
Il valore Altezza1 deve corrispondere all'array Row_header e il valore Larghezza2 deve corrispondere all'array Column_header.
Usa la formula nella cella K6:
= INDICE (dati, CONFRONTA (K4, Altezza, 1 , CONFRONTA ( K5, Larghezza, 1 ) ) )
Spiegazione:
- La funzione CONFRONTA corrisponde al valore Altezza nella cella K4 con l'array di intestazione di riga e restituisce la sua posizione 3 come un numero.
- La funzione CONFRONTA corrisponde al valore Larghezza nella cella K5 con l'array di intestazione di colonna e restituisce la sua posizione 2 come numero.
- La funzione INDEX prende il numero di indice di riga e colonna e cerca nei dati della tabella e restituisce il valore corrispondente.
- L'argomento del tipo CONFRONTA è fisso su 1. Poiché la formula estrarrà la corrispondenza approssimativa.
Qui i valori della formula sono dati come riferimenti di cella e row_header, data e column_header dati come intervalli denominati come menzionato nell'istantanea sopra.
Come puoi vedere nell'istantanea sopra, abbiamo il prezzo ottenuto dall'altezza (34) & Larghezza (21) come 53.10. Dimostra che la formula funziona bene e per i dubbi vedere le note sotto per una maggiore comprensione.
Appunti:
- La funzione restituisce l'errore #NA se l'argomento dell'array di ricerca per la funzione CONFRONTA è un array 2D che è il campo di intestazione dei dati…
- La funzione corrisponde al valore esatto poiché l'argomento del tipo di corrispondenza per la funzione CONFRONTA è 0.
- I valori di ricerca possono essere forniti come riferimento di cella o direttamente utilizzando il simbolo di virgolette ( ") nella formula come argomenti.
Spero che tu abbia capito come utilizzare la ricerca nella tabella 2D utilizzando la funzione INDICE E CONFRONTA in Excel. Esplora altri articoli nel valore di ricerca di Excel qui. Sentiti libero di esprimere le tue domande qui sotto nella casella dei commenti. Ti aiuteremo sicuramente.
Usa INDEX e MATCH per cercare il valore : Funzione INDEX & MATCH per cercare il valore come richiesto.
Intervallo SOMMA con INDICE in Excel : Usa la funzione INDEX per trovare la SOMMA dei valori come richiesto.
Come utilizzare la funzione SOMMA in Excel : Trova la SOMMA dei numeri utilizzando la funzione SOMMA spiegata con l'esempio.
Come usare la funzione INDICE in Excel : Trova l'INDICE dell'array usando la funzione INDICE spiegata con l'esempio.
Come utilizzare la funzione CONFRONTA in Excel : Trova la CONFRONTA nell'array utilizzando il valore INDICE all'interno della funzione CONFRONTA spiegata con l'esempio.
Come usare la funzione CERCA in Excel : Trova il valore di ricerca nell'array utilizzando la funzione CERCA spiegata con l'esempio.
Come utilizzare la funzione CERCA.VERT in Excel : trova il valore di ricerca nell'array utilizzando la funzione CERCA.VERT spiegata con l'esempio.
Come utilizzare la funzione CERCA.ORIZZ in Excel : Trova il valore di ricerca nell'array utilizzando la funzione CERCA.ORIZZ spiegata con l'esempio.
Articoli popolari
50 scorciatoie Excel per aumentare la produttività
Modifica un elenco a discesa
Riferimento assoluto in Excel
Se con la formattazione condizionale
Se con caratteri jolly
Vricerca per data
Unisci nome e cognome in excel
