Utilizzo della funzione CERCA.VERT ed esempi specifici

La funzione CERCA.VERT in Excel è estremamente utile per cercare dati. Ecco cosa devi sapere sull'utilizzo della funzione CERCA.VERT in Excel .

Desideri controllare un valore specifico in un foglio di calcolo Excel e cercare informazioni al riguardo. L'esecuzione di un CERCA.VERT ti fa risparmiare uno sforzo significativo in questo caso. È uno dei modi migliori per eseguire una query verticale in Microsoft Excel. L'articolo ti spiegherà il significato, l'utilizzo e gli esempi illustrativi della funzione CERCA.VERT in Microsoft Excel.

• Queste sono le funzioni di base di Excel che devi comprendere

Come utilizzare la funzione CERCA.VERT

• Cos'è la funzione CERCA.VERT?
• Formula della funzione CERCA.VERT in Excel 2016, 2013, 2010, 2007, 2003
• Video tutorial sull'utilizzo della funzione CERCA.VERT
• Esempio 1: utilizzare la funzione CERCA.VERT per valutare le valutazioni degli studenti e del personale:
• Esempio 2: utilizzare CERCA.VERT per il rilevamento assoluto per ottenere dati
• Esempio 3: utilizzare CERCA.VERT per estrarre i dati
• Esempio 4: utilizzare la funzione CERCA.VERT su 2 diversi fogli Excel
• Per cosa utilizzare la funzione CERCA.VERT?
• Cose da ricordare sulla funzione CERCA.VERT

Cos'è la funzione CERCA.VERT?

La funzione CERCA.VERT è una funzione di ricerca dati in Excel. Presumo che tu abbia esperienza in Excel e che tu possa utilizzare funzioni di base come SOMMA, MEDIA e OGGI. Una delle funzioni più comuni di CERCA.VERT è una funzione dati, ovvero opera su tabelle di database o, più semplicemente, elenchi di elementi. L'elenco è molto vario. Puoi creare una tabella sul personale della tua azienda, sulle categorie di prodotti, sugli elenchi dei clienti o su qualsiasi altra cosa. E di seguito è riportato un elenco dei prodotti che l'azienda A vende sul mercato:

Le tabelle del database hanno spesso identificatori per ciascun prodotto. Nella tabella dati sopra, il segno identificativo speciale è la colonna "Codice Articolo". Nota: per poter utilizzare la funzione CERCA.VERT, la tabella dati deve avere una colonna contenente segni identificativi come codice o ID e deve essere la prima colonna come la tabella sopra.

CERCA.VERT è probabilmente la funzione più famosa di Excel, ma per ragioni sia buone che cattive. Il lato positivo è che la funzione CERCA.VERT è facile da usare e fa qualcosa di molto utile. Soprattutto per i nuovi utenti, guardare la funzione CERCA.VERT scansionare la tabella, trovare una corrispondenza e restituire un risultato accurato è estremamente emozionante. L'uso competente della funzione CERCA.VERT è una competenza essenziale, dai principianti agli utenti esperti di Excel.

Il lato negativo è che la funzione CERCA.VERT è limitata e ha valori predefiniti pericolosi. A differenza di INDICE e CONFRONTA (o CERCAX), la funzione CERCA.VERT necessita di una tabella completa con i valori di ricerca nella prima colonna. Ciò rende difficile l'utilizzo della funzione CERCA.VERT con più criteri. Inoltre, il comportamento di corrispondenza predefinito di CERCA.VERT facilita l'ottenimento di risultati errati. Ma non preoccuparti! La chiave per utilizzare con successo la funzione CERCA.VERT è padroneggiare le nozioni di base.

CERCA.VERT ha 4 argomenti: lookup_value, table_array, column_index_num e range_lookup. Lookup_value è il valore da cercare e table_array è l'intervallo di dati verticale all'interno del quale effettuare la ricerca. La prima colonna di table_array deve contenere i valori di ricerca da cercare. L'argomento column_index_num è il numero di colonna del valore da recuperare, in cui la prima colonna di table_array è la colonna 1.

Infine, range_lookup controlla il comportamento di ricerca di una corrispondenza. Se range_lookup è TRUE, la funzione CERCA.VERT esegue una corrispondenza approssimativa. Se range_lookup è FALSE, la funzione CERCA.VERT eseguirà una corrispondenza esatta. È importante sottolineare che range_lookup è facoltativo e il valore predefinito è TRUE, quindi CERCA.VERT eseguirà una corrispondenza approssimativa per impostazione predefinita.

Formula della funzione CERCA.VERT in Excel

La funzione CERCA.VERT in Excel ha la seguente formula:

=CERCA.VERT(valore_ricerca,array_tabella,num_indice_col ,[ricerca_intervallo] )

Lì dentro:

• CERCA.VERT: è il nome della funzione
• I parametri in grassetto sono obbligatori.
• lookup_value: valore utilizzato per la ricerca
• table_array: Tabella contenente il valore da cercare, messo in valore assoluto preceduto dal segno $, ad esempio: $A$3:$E$40.
• col_index_num: ordine della colonna contenente il valore di ricerca su table_array. Ad esempio, nella tabella $A$3:$E$40, la colonna B contiene il valore da cercare, quindi col_index_num qui sarà 2; tabella $C$3:$F$40, la colonna E contiene il valore di ricerca, quindi col_index_num qui è 3.
• range_lookup: è l'intervallo di ricerca, TRUE equivale a 1 (ricerca relativa), FALSE equivale a 0 (ricerca assoluta). Questo parametro non è sempre richiesto nella formula.

La ricerca relativa può essere applicata solo quando i valori da cercare in table_array sono stati disposti in ordine (ascendente o discendente o in ordine alfabetico). Con tali tabelle è possibile utilizzare la ricerca relativa, che è simile all'utilizzo di una funzione IF infinita . Per i valori che non possono essere cercati o che non sono ordinati, utilizzare la ricerca assoluta per trovare il valore esatto.

Video tutorial sull'utilizzo della funzione CERCA.VERT

Esempio 1: funzione CERCA.VERT per valutare le valutazioni degli studenti e del personale

Supponiamo di avere una trascrizione dello studente come segue:

E la tabella regolamentare della classifica è la seguente:

Ora utilizzeremo la funzione CERCA.VERT per inserire le valutazioni per gli studenti. Noterai che la tabella della classifica è stata organizzata in ordine dal basso all'alto (da debole a buono), quindi in questo caso possiamo utilizzare la ricerca relativa.

In Excel queste 2 tabelle sono presentate come segue:

Qui il valore da rilevare è nella colonna C, a partire dalla riga 6. L'intervallo di ricerca è $A$18:$B$21, l'ordine della colonna contenente il valore rilevato è 2.

Nella cella D6, inserisci la formula: =CERCA.VERT($C6,$A$18:$B$21,2,1). Questa è una formula di ricerca relativa, puoi eseguire una ricerca assoluta se lo desideri (o perché la classifica non è ordinata) aggiungendo 0 alla formula in questo modo: =VLOOKUP($C6 ,$A$18:$B$21,2, 0). Premere Invio.

Clicca sulla cella D6, appare un quadratino nell'angolo in basso a destra, cliccaci sopra e trascina verso il basso la tabella per copiare la formula di valutazione per gli studenti rimanenti.

A quel punto, abbiamo i risultati dell'utilizzo della funzione CERCA.VERT per classificare il rendimento accademico degli studenti come segue:

Ti stai chiedendo perché $ deve essere utilizzato prima di C6? $ manterrà fissa la colonna C, modificando le righe solo quando trascini la formula lungo la tabella. C'è anche $A$18:$B$21 per aiutare a correggere la tabella delle regole di classificazione, facendo in modo che non cambi quando trascini la formula.

Esempio 2: la funzione CERCA.VERT cerca in modo assoluto per ottenere dati

Supponiamo di avere una tabella dati dei dipendenti in cui sono archiviati il ​​codice del dipendente, il nome completo e la posizione. Un'altra tabella memorizza il codice del dipendente, la città natale e il livello di istruzione. Ora vuoi inserire le informazioni sulla città natale e il livello di istruzione di ciascun dipendente, cosa dovresti fare?

Supponiamo di avere una tabella dei dipendenti e delle città di origine dei dipendenti come segue:

Ora vuoi inserire le informazioni sulla città natale per i dipendenti. Nella cella D4, inserisci la formula di ricerca assoluta come segue: =Vlookup($A4,$A$16:$C$25,2,0)

Quindi premere Invio. Fai clic sul quadratino visualizzato nell'angolo della cella D4 e trascinalo verso il basso sulla tabella per copiare la formula ad altri dipendenti.

Per inserire le informazioni sulle qualifiche per i dipendenti, nella cella E4, inserire la formula di ricerca assoluta: =VLOOKUP($A4,$A$16:$C$25,3,0)

Continui a premere Invio e scorri verso il basso per copiare la formula ai restanti dipendenti, otterremo il seguente risultato:

Esempio 3: utilizzare CERCA.VERT per estrarre i dati

Continuando con il set di dati dell'esempio 2, troveremo ora le città natali di 3 dipendenti: Nguyen Hoang Anh, Tran Van Anh e Nguyen Quang Vinh. L'ho estratto in una nuova tabella F15:G18.

Farò questa ricerca sulla tabella A3:E13, dopo aver inserito la mia città natale e le mie qualifiche. Ora, inserisci la seguente formula di ricerca assoluta nella cella G16: =VLOOKUP($F16,$B$3:$E$13,3,0) > premi Invio.

Copiando la formula per i restanti 2 dipendenti otteniamo il seguente risultato:

Tieni presente che in questo esempio il valore di rilevamento è nella colonna B, quindi l'area di rilevamento viene calcolata dalla colonna B e non dalla colonna A.

Esempio 4: utilizzare la funzione CERCA.VERT su 2 diversi fogli Excel

Tornando al set di dati dell'esempio 2, dopo aver compilato le qualifiche e la città di residenza del dipendente, chiamiamo il foglio QTM.

In un altro foglio del foglio di calcolo, denominato QTM1, è necessario ottenere informazioni sulle qualifiche e sulla posizione del dipendente con l'ordine di disposizione del dipendente modificato. Questo è quando vedi il vero potere della funzione CERCA.VERT.

Per trovare i dati sulle "Qualifiche" del dipendente, inserisci la formula: =CERCA.VERT($B4,QTM!$B$3:$E$13,4,0) nella cella C4 del foglio QTM1.

Lì dentro:

• B4 è la colonna contenente il valore utilizzato per il rilevamento.
• QTM! è il nome del foglio contenente la tabella con il valore da rilevare Dopo il nome del foglio aggiungere un !
• $B$3:$E$13 è la tabella contenente i valori di ricerca e il foglio contenente la tabella (QTM).
• 4 è il numero di serie della colonna "Qualifiche", calcolato dalla colonna "Nome completo" sul foglio QTM.
• 0 è il rilevamento assoluto.

Premi Invio, quindi copia la formula per tutti i dipendenti rimanenti nella tabella, otteniamo il seguente risultato:

Per trovare i dati "Posizione" del dipendente, nella cella D4 del foglio QTM1, inserisci la formula: =CERCA.VERT($B4,QTM!$B$3:$E$13,2,0), premi Invio.

Copiando la formula per i restanti dipendenti, otteniamo quanto segue:

Per cosa utilizzare la funzione CERCA.VERT?

Per prima cosa dobbiamo trovare la risposta esatta alla domanda "per cosa utilizziamo la funzione CERCA.VERT?".

La funzione CERCA.VERT viene utilizzata per supportare la ricerca di informazioni in un campo dati o in un elenco in base agli identificatori disponibili.

Ad esempio, inserendo la funzione CERCA.VERT con un codice prodotto in un altro foglio di calcolo verranno visualizzate le informazioni sul prodotto corrispondenti a quel codice. Tali informazioni possono essere descrizione, prezzo, quantità di inventario, a seconda del contenuto della ricetta che scrivi.

Minore è la quantità di informazioni che devi trovare, più difficile sarà scrivere la funzione CERCA.VERT. In genere si utilizzerebbe questa funzione in un foglio di lavoro riutilizzabile come modello. Ogni volta che viene inserito il codice prodotto appropriato, il sistema recupererà tutte le informazioni necessarie sul prodotto corrispondente.

Cose da ricordare sulla funzione CERCA.VERT

Ecco un elenco di cose importanti da ricordare sulla funzione CERCA.VERT in Excel:

• Quando range_lookup viene omesso, CERCA.VERT consentirà corrispondenze non esatte, ma utilizzerà comunque corrispondenze esatte se disponibili.
• La più grande limitazione della funzione è che assume sempre il valore a destra. La funzione recupererà i dati dalle colonne a destra della prima colonna nella tabella.
• Se la colonna di ricerca contiene valori duplicati, la funzione CERCA.VERT corrisponderà solo al primo valore.
• La funzione non distingue tra maiuscole e minuscole.
• Supponiamo che esista una formula CERCA.VERT in un foglio di lavoro. In tal caso, le formule potrebbero non funzionare se si inserisce una colonna nella tabella, poiché i valori dell'indice delle colonne sono hardcoded, che non cambiano automaticamente quando le colonne vengono inserite o eliminate.
• CERCA.VERT consente l'uso di caratteri jolly, ad esempio asterischi (*) o punti interrogativi (?).
• Supponiamo che in una tabella tu stia lavorando con una funzione che contiene numeri immessi come testo. Se ottieni semplicemente numeri come testo da una colonna in una tabella, non importa. Ma se la prima colonna della tabella contiene numeri inseriti come testo, #N/A! verrà visualizzato se anche il valore di ricerca non è testo.
• Errore #N/D! Si verifica se la funzione CERCA.VERT non trova una corrispondenza per il valore_ricerca fornito.
• Errore #REF! succede se:
  • L'argomento col_index_num è maggiore del numero di colonne nell'array_tabella fornito
  • La formula ha tentato di fare riferimento a celle che non esistono.
• Errore #VALORE! succede se:
  • L'argomento col_index_num è inferiore a 1 o non è riconosciuto come valore numerico
  • L'argomento range_lookup non è riconosciuto come uno dei valori logici TRUE o FALSE.

Differenze chiave tra le funzioni CERCA.VERT e CERCA.X in Excel

Y - Sì

N -No

Vedi altro:

• Come utilizzare la funzione SE in Excel
• Come utilizzare la funzione MEDIA.SE in Excel

Dopo aver saputo utilizzare VLookup, devi anche conoscere gli errori comuni e come risolvere i problemi.

Errori comuni quando si utilizza la funzione CERCA.VERT in Excel

Il valore di ricerca è nella colonna sbagliata

Uno degli errori più comuni che incontrerai quando utilizzi una formula CERCA.VERT è che la funzione restituisce solo il valore #N/A. Questo errore si verifica quando non è possibile trovare il valore di ricerca che hai chiesto a CERCA.VERT di cercare.

In alcuni casi, è possibile utilizzare la funzione CERCA.X invece di CERCA.VERT, tuttavia, questo errore può essere facilmente corretto solo nella funzione CERCA.VERT.

Come risolvere il problema:

Il motivo per cui CERCA.VERT non ha trovato il valore desiderato potrebbe essere perché il valore non è nella tabella. Tuttavia, se restituisce tutti i valori #N/A, il valore di ricerca è testo, molto probabilmente è perché può leggere solo da sinistra a destra.

Quando crei una tabella in Excel per CERCA.VERT, assicurati di posizionare il valore di ricerca a sinistra del valore che desideri restituire. Il modo più semplice per farlo è posizionarlo nella prima colonna della tabella.

Ora la formula restituirà un valore:

Nel complesso, CERCA.VERT è un ottimo modo per interrogare i dati più velocemente in Microsoft Excel. Sebbene le query CERCA.VERT vengano eseguite verticalmente sulle colonne e presentino alcune altre limitazioni, Microsoft aggiorna continuamente le funzionalità di ricerca di Excel per espanderne l'applicabilità. Ad esempio, HLOOKUP, XLOOKUP... possono aiutarti a cercare i dati in molte direzioni diverse. LuckyTemplates.com continuerà a fornirti informazioni su come utilizzare questa funzione nei seguenti articoli.