In questo articolo impareremo come estrarre l'indice di colonna dalla tabella in Excel.
Scenario:
Molte volte quando si lavora con dati sparsi a lungo, è necessario prima pulire i dati prima di lavorarci. Ci vuole tempo e volevi solo estrarre alcune query. Per estrarre i dati, generalmente utilizzi la funzione CERCA.VERT. Ma quando abbiamo dati lunghi con molti campi di colonna, allora diventa disordinato perché la funzione CERCA.VERT richiede l'indice di colonna corretto come numero, altrimenti la formula restituisce un errore. Proprio lì, di seguito è la spiegazione della formula per questo tipo di problema.
Come risolvere il problema?
Per questo useremo la funzione MATCH. Se non hai sentito parlare di questa funzione, abituati. È una funzione di ricerca excel che restituisce l'indice del valore di ricerca nell'array. Qui abbiamo bisogno di ottenere il numero di indice del nome della colonna. Quindi procederemo al passaggio successivo Come cercare i valori nelle tabelle utilizzando la funzione CONFRONTA. Di seguito è la formula generica
Formula generica:
| =CONFRONTA(nome_colonna, intestazione_tabella, 0) |
nome_colonna: valore di ricerca
table_header: array di intestazioni di tabella
0: cerca la corrispondenza esatta
Esempio :
Tutti questi potrebbero essere fonte di confusione da capire. Comprendiamo la formula con spiegazione ed esempio. Qui abbiamo una tabella dati nel foglio 1 ($A$1:$U$9995). Quindi abbiamo l'intestazione della tabella come A1:U1 (la prima riga della tabella).
Vediamo la formula sottostante da applicare sulla tabella.
Usa la formula
| =CONFRONTA(C4,Foglio1!$A$1:$U$1,0) |
Spiegazione:
- La funzione CONFRONTA cerca il valore nella cella C4 "ID ordine"
- Foglio1!$A$1:$U$1 è l'argomento dell'array di ricerca.
- 0 viene fornito l'argomento per cercare la corrispondenza esatta.
Come puoi vedere, l'indice della colonna dell'ID ordine nella tabella è il secondo. Ma è molto irritante usare l'intestazione della tabella come abbreviazione completa, quindi usiamo l'intervallo denominato per l'array dell'intestazione della tabella. Scopri di più sugli intervalli denominati qui. L'intervallo denominato utilizzato per l'intestazione della tabella (Foglio1!$A$1:$U$1) è "intestazione".
Usa la formula.
| =CONFRONTA(C4,intestazione,0) |
Qui l'istantanea sopra spiega due cose. Il primo è che l'indice di stato della colonna nella tabella è 11 e il secondo è l'intervallo denominato "intestazione" che funziona correttamente. Copia la formula per i nomi delle colonne rimanenti usando Ctrl + D o trascinando verso il basso dal bordo inferiore destro la cella utilizzata.
Qui abbiamo tutta la colonna Index. Ora possiamo usare per dare come input alla funzione VLOOKUP come mostrato di seguito.
Indice CONFRONTA nella funzione CERCA.VERT:
Ora abbiamo la soluzione per come ottenere l'indice di colonna della tabella. Possiamo usare la formula come input per la funzione CERCA.VERT. Ad esempio, abbiamo bisogno del nome del prodotto acquistato dal nome del cliente "Pete Kriz".
Usa la formula:
| =CERCA.VERT(D10,Tabella,CONFRONTA(E9,intestazione,0),0) |
Nota: assicurati che il valore di ricerca in D10 (Pete Kriz) debba essere nella prima colonna della tabella.
Come puoi vedere, la formula restituisce il nome del prodotto dal nome del cliente nella tabella. Generalmente non usiamo MATCH con la funzione VLOOKUP perché il valore di ricerca deve essere nella prima colonna, cosa che difficilmente accade. Quindi usiamo la combinazione della funzione INDICE e CONFRONTA. Ulteriori informazioni su Come cercare il valore utilizzando la funzione INDICE e CONFRONTA.
Ecco tutte le note di osservazione sull'uso della formula.
Appunti:
- La formula funziona sia per testo che per numeri.
- La funzione restituisce l'errore #NA se l'argomento della matrice di ricerca per la funzione CONFRONTA non ha la stessa lunghezza della matrice della tabella.
- La formula restituisce un errore se lookup_value non corrisponde al valore nella tabella lookup_array.
- La funzione corrisponde al valore esatto poiché l'argomento del tipo di corrispondenza per la funzione CONFRONTA è 0.
- Utilizzare l'argomento -1 per minore di, 0 per corrispondenza esatta e 1 per maggiore di la corrispondenza di ricerca.
- 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 estrarre l'indice della colonna dalla tabella in Excel. Esplora altri articoli sul valore di ricerca di Excel e sulle funzioni di Excel 2019 qui. Se ti è piaciuto il nostro blog, condividilo con i tuoi amici su Facebook. E puoi anche seguirci su Twitter e Facebook. Ci piacerebbe avere tue notizie, facci sapere come possiamo migliorare, completare o innovare il nostro lavoro e renderlo migliore per te. Scrivici al sito di posta elettronica.
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 usare la funzione INDICE in Excel : Trova l'INDICE di un array usando la funzione INDEX spiegata con un 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.
Articoli popolari:
Come usare la funzione SE in Excel : l'istruzione IF in Excel controlla la condizione e restituisce un valore specifico se la condizione è VERA o restituisce un altro valore specifico se FALSO.
Come utilizzare la funzione CERCA.VERT in Excel : questa è una delle funzioni più utilizzate e popolari di excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.
Come utilizzare la funzione CONTA.SE in Excel : Conta i valori con le condizioni utilizzando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.
Come utilizzare la funzione SOMMA.SE in Excel : Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.