Combinando la funzione CERCA. VERT di Excel con la funzione COLONNA puoi creare una formula di ricerca che restituisce più valori da una singola riga di un database o di una tabella di dati. Scopri come creare una formula di ricerca che restituisca più valori da un singolo record di dati.
Le istruzioni in questo articolo si applicano a Excel 2019, 2016, 2013, 2010; ed Excel per Microsoft 365.
Linea inferiore
La formula di ricerca richiede che la funzione COLUMN sia nidificata all'interno di VLOOKUP. L'annidamento di una funzione implica l'inserimento della seconda funzione come uno degli argomenti per la prima funzione.
Inserisci i dati del tutorial
In questo tutorial, la funzione COLUMN viene inserita come argomento del numero di indice della colonna per VLOOKUP. L'ultimo passaggio del tutorial prevede la copia della formula di ricerca in colonne aggiuntive per recuperare valori aggiuntivi per la parte scelta.
Il primo passo in questo tutorial è inserire i dati in un foglio di lavoro Excel. Per seguire i passaggi di questo tutorial, inserisci i dati mostrati nell'immagine qui sotto nelle seguenti celle:
- Inserisci l'intervallo superiore di dati nelle celle da D1 a G1.
- Inserisci il secondo intervallo nelle celle da D4 a G10.
I criteri di ricerca e la formula di ricerca creati in questo tutorial sono inseriti nella riga 2 del foglio di lavoro.
Questo tutorial non include la formattazione di base di Excel mostrata nell'immagine, ma ciò non influisce sul funzionamento della formula di ricerca.
Crea un intervallo con nome per la tabella dati
Un intervallo denominato è un modo semplice per fare riferimento a un intervallo di dati in una formula. Anziché digitare i riferimenti di cella per i dati, digita il nome dell'intervallo.
Un secondo vantaggio dell'utilizzo di un intervallo denominato è che i riferimenti di cella per questo intervallo non cambiano mai anche quando la formula viene copiata in altre celle del foglio di lavoro. I nomi degli intervalli sono un' alternativa all'utilizzo di riferimenti di cella assoluti per evitare errori durante la copia delle formule.
Il nome dell'intervallo non include le intestazioni o i nomi dei campi per i dati (come mostrato nella riga 4), solo i dati.
-
Evidenzia celle da D5 a G10 nel foglio di lavoro.
Image -
Posiziona il cursore nella casella del nome situata sopra la colonna A, digita Table, quindi premi Enter. Le celle da D5 a G10 hanno il nome dell'intervallo di Table.
Image - Il nome dell'intervallo per l'argomento dell'array della tabella VLOOKUP viene utilizzato più avanti in questo tutorial.
Apri la finestra di dialogo CERCA. VERT
Sebbene sia possibile digitare la formula di ricerca direttamente in una cella di un foglio di lavoro, molte persone trovano difficile mantenere la sintassi dritta, specialmente per una formula complessa come quella utilizzata in questo tutorial.
In alternativa, usa la finestra di dialogo Argomenti funzione VLOOKUP. Quasi tutte le funzioni di Excel hanno una finestra di dialogo in cui ciascuno degli argomenti della funzione viene inserito su una riga separata.
-
Seleziona cella E2 del foglio di lavoro. Questa è la posizione in cui verranno visualizzati i risultati della formula di ricerca bidimensionale.
Image -
Sulla barra multifunzione, vai alla scheda Formule e seleziona Ricerca e riferimento.
Image -
Seleziona VLOOKUP per aprire la finestra di dialogo Argomenti funzione.
Image - La finestra di dialogo Argomenti funzione è dove vengono inseriti i parametri della funzione VLOOKUP.
Inserisci l'argomento del valore di ricerca
Normalmente, il valore di ricerca corrisponde a un campo di dati nella prima colonna della tabella di dati. In questo esempio, il valore di ricerca si riferisce al nome della parte di cui si desidera trovare le informazioni. I tipi di dati consentiti per il valore di ricerca sono dati di testo, valori logici, numeri e riferimenti di cella.
Riferimenti di celle assoluti
Quando le formule vengono copiate in Excel, i riferimenti di cella cambiano per riflettere la nuova posizione. Se ciò accade, D2, il riferimento di cella per il valore di ricerca, cambia e crea errori nelle celle F2 e G2.
I riferimenti di cella assoluti non cambiano quando le formule vengono copiate.
Per evitare errori, converti il riferimento di cella D2 in un riferimento di cella assoluto. Per creare un riferimento di cella assoluto, premere il tasto F4. Questo aggiunge i segni del dollaro attorno al riferimento di cella come $D$2.
-
Nella finestra di dialogo Argomenti funzione, posizionare il cursore nella casella di testo lookup_value. Quindi, nel foglio di lavoro, seleziona cella D2 per aggiungere questo riferimento di cella a lookup_value. Nella cella D2 verrà inserito il nome della parte.
Image -
Senza spostare il punto di inserimento, premere il tasto F4 per convertire D2 nel riferimento di cella assoluto $D$2.
Image - Lascia aperta la finestra di dialogo della funzione CERCA. VERT per il passaggio successivo del tutorial.
Inserisci l'argomento della matrice della tabella
Un array di tabelle è la tabella di dati che la formula di ricerca cerca per trovare le informazioni desiderate. L'array della tabella deve contenere almeno due colonne di dati.
La prima colonna contiene l'argomento del valore di ricerca (impostato nella sezione precedente), mentre la seconda colonna viene ricercata dalla formula di ricerca per trovare le informazioni specificate.
L'argomento dell'array della tabella deve essere inserito come intervallo contenente i riferimenti di cella per la tabella di dati o come nome dell'intervallo.
Per aggiungere la tabella dei dati alla funzione CERCA. VERT, posiziona il cursore nella casella di testo table_array nella finestra di dialogo e digita Tableper inserire il nome dell'intervallo per questo argomento.
Annidare la funzione COLUMN
Normalmente, CERCA. VERT restituisce solo i dati da una colonna di una tabella di dati. Questa colonna è impostata dall'argomento del numero di indice della colonna. In questo esempio, tuttavia, sono presenti tre colonne e il numero di indice della colonna deve essere modificato senza modificare la formula di ricerca. Per fare ciò, annida la funzione COLUMN all'interno della funzione VLOOKUP come argomento Col_index_num.
Quando si annidano le funzioni, Excel non apre la finestra di dialogo della seconda funzione per inserire i suoi argomenti. La funzione COLONNA deve essere inserita manualmente. La funzione COLUMN ha un solo argomento, l'argomento Riferimento, che è un riferimento di cella.
La funzione COLUMN restituisce il numero della colonna fornita come argomento di riferimento. Converte la lettera della colonna in un numero.
Per trovare il prezzo di un articolo, utilizza i dati nella colonna 2 della tabella dei dati. Questo esempio utilizza la colonna B come riferimento per inserire 2 nell'argomento Col_index_num.
-
Nella finestra di dialogo Argomenti funzione, posizionare il cursore nella casella di testo Col_index_num e digitare COLUMN(.(Assicurati di includere la parentesi tonda aperta.)
Image -
Nel foglio di lavoro, seleziona cella B1 per inserire quel riferimento di cella come argomento di riferimento.
Image - Digita una parentesi tonda chiusa per completare la funzione COLONNA.
Inserisci l'argomento di ricerca dell'intervallo VLOOKUP
L'argomento Range_lookup di VLOOKUP è un valore logico (TRUE o FALSE) che indica se VLOOKUP deve trovare una corrispondenza esatta o approssimativa con Lookup_value.
- TRUE o Omesso: VLOOKUP restituisce una corrispondenza stretta con Lookup_value. Se non viene trovata una corrispondenza esatta, CERCA. VERT restituisce il valore successivo più grande. I dati nella prima colonna di Table_array devono essere ordinati in ordine crescente.
- FALSE: VLOOKUP usa una corrispondenza esatta con Lookup_value. Se sono presenti due o più valori nella prima colonna di Matrice_tabella che corrispondono al valore di ricerca, viene utilizzato il primo valore trovato. Se non viene trovata una corrispondenza esatta, viene restituito un errore N/D.
In questo tutorial, verranno cercate informazioni specifiche su un particolare elemento hardware, quindi Range_lookup è impostato su FALSE.
Nella finestra di dialogo Argomenti funzione, posiziona il cursore nella casella di testo Range_lookup e digita False per dire a VLOOKUP di restituire una corrispondenza esatta per i dati.
Seleziona OK per completare la formula di ricerca e chiudere la finestra di dialogo. La cella E2 conterrà un errore N/D perché i criteri di ricerca non sono stati immessi nella cella D2. Questo errore è temporaneo. Verrà corretto quando i criteri di ricerca vengono aggiunti nell'ultimo passaggio di questo tutorial.
Copia la formula di ricerca e inserisci i criteri
La formula di ricerca recupera i dati da più colonne della tabella dati contemporaneamente. Per fare ciò, la formula di ricerca deve risiedere in tutti i campi da cui desideri ottenere le informazioni.
Per recuperare i dati dalle colonne 2, 3 e 4 della tabella dei dati (il prezzo, il numero di parte e il nome del fornitore), inserisci un nome parziale come Valore_ricerca.
Poiché i dati sono disposti in modo regolare nel foglio di lavoro, copia la formula di ricerca nella cella E2 in celle F2 e G2 Quando la formula viene copiata, Excel aggiorna il relativo riferimento di cella nella funzione COLONNA (cella B1) per riflettere la nuova posizione della formula. Excel non modifica il riferimento di cella assoluto (ad esempio $D$2) e l'intervallo denominato (Tabella) durante la copia della formula.
C'è più di un modo per copiare i dati in Excel, ma il modo più semplice è usare la maniglia di riempimento.
-
Seleziona cella E2, dove si trova la formula di ricerca, per renderla la cella attiva.
Image -
Trascina il quadratino di riempimento su cella G2. Le celle F2 e G2 visualizzano l'errore N/D presente nella cella E2.
Image -
Per utilizzare le formule di ricerca per recuperare informazioni dalla tabella dati, nel foglio di lavoro selezionare cella D2, digitare Widget e premere Inserisci.
Image Le seguenti informazioni vengono visualizzate nelle celle da E2 a G2.
- E2: $ 14,76 - il prezzo di un widget
- F2: PN-98769 - il numero di parte di un widget
- G2: Widgets Inc. - il nome del fornitore per i widget
-
Per testare la formula dell'array VLOOKUP, digita il nome di altre parti nella cella D2 e osserva i risultati nelle celle da E2 a G2.
Image - Ogni cella contenente la formula di ricerca contiene un dato diverso sull'elemento hardware che hai cercato.
La funzione VLOOKUP con funzioni nidificate come COLUMN fornisce un metodo potente per cercare i dati all'interno di una tabella, utilizzando altri dati come riferimento di ricerca.
