Introduzione alla funzione CERCA.VERT
La funzione CERCA.VERT in Excel è senza dubbio la funzione più utilizzata e parlata. In qualsiasi colloquio di lavoro che richiede competenze di Excel, questa è la domanda più posta se sai come utilizzare la funzione CERCA.VERT.
La funzione CERCA.VERT rientra nella categoria di ricerca delle funzioni excel. La V in CERCA.VERT sta per verticale. La funzione viene utilizzata per cercare dati strutturati verticalmente. Per la ricerca orizzontale, esiste una funzione chiamata CERCA.ORIZZ.
La funzione CERCA.VERT di Excel cerca la prima corrispondenza di un dato valore chiamato valore di ricerca, in una tabella di dati e restituisce il valore dall'indice di colonna specificato. La corrispondenza può essere approssimativa o esatta.
Sebbene VLOOKUP sia facile da usare, ha i suoi limiti e i suoi sfondi. Discuteremo di tutto, compresi i punti di forza e di debolezza della funzione.
Sintassi:
=CERCA.VERT(valore_ricerca, matrice_tabella, numero_indice_col, [ricerca_intervallo])
Che cos'è il valore di ricerca?
Valore di ricerca: È il valore che vuoi cercare in un array di tabelle.
Ad esempio, se stai cercando il rotolo n. 110 nella tabella dello studente, quindi è 110 è il valore di ricerca.
Matrice di tabella: La tabella da cui si desidera cercare il valore di ricerca.
Ad esempio, se stai cercando 110 nell'array della tabella B5:E17. Quindi B5: E17 è il tuo array di tabelle.
col_index_number: Il numero di colonna in Table Array da cui si desidera recuperare i risultati.
Ad esempio se in array di tabelle B5: MI17 vuoi ottenere valore dalla colonna D quindi il tuo indice di colonna sarà 3 (contando da B a D (B,C,D)).
[range_lookup]: FALSE se vuoi cercare il valore esatto, TRUE se vuoi una corrispondenza approssimativa.
Come utilizzare la funzione CERCA.VERT
Quando si dispone di dati strutturati e si desidera cercare dati in tali dati, la funzione CERCA.VERT è la soluzione.
CERCA.VERT Modalità di corrispondenza
CERCA.VERT ha due modalità di corrispondenza, corrispondenza approssimativa e corrispondenza esatta. Per impostazione predefinita, VLOOKUP esegue una corrispondenza approssimativa. Ma se vuoi forzare la funzione CERCA.VERT per fare una corrispondenza esatta puoi farlo anche tu. La maggior parte delle volte, io e io crediamo fermamente anche agli altri, proviamo a fare una corrispondenza esatta usando la funzione CERCA.VERT. Non capisco perché gli sviluppatori di Excel pensano che gli utenti vorrebbero utilizzare principalmente la corrispondenza approssimativa.
1. CERCA.VERT Corrispondenza approssimativa
=CERCA.VERT(valore_ricerca, matrice_tabella, numero_indice_col, 1)
In CERCA.VERT una corrispondenza approssimativa, se non viene trovato un valore, allora il l'ultimo valore che è inferiore al valore di ricerca viene abbinato e viene restituito il valore dall'indice di colonna specificato.
VLOOKUP per impostazione predefinita fa una corrispondenza approssimativa, se omettiamo la variabile di ricerca dell'intervallo. Una corrispondenza approssimativa è utile quando si desidera eseguire una corrispondenza approssimativa e quando hai il tuo array di tabelle ordinato in ordine crescente.
Vlookup cerca il valore e se non trova il valore nell'array di tabelle, corrisponde al valore che è inferiore a quel valore nell'array di tabelle. Capiamolo con un esempio.
Esempio 1
Abbiamo un elenco di studenti qui. Ogni studente ha ottenuto alcuni voti in un test. Ora voglio dare un voto in base ai loro voti.
Qualsiasi studente che ha ottenuto un punteggio inferiore a 40 deve essere contrassegnato con F, lo studente che ha ottenuto un punteggio compreso tra 40 e 60 deve essere contrassegnato con C e così via come mostrato nell'immagine sottostante.
Vorremmo scrivere questa formula CERCA.VERT in E2 e trascinarla verso il basso.
=CERCA.VERT(D2,$H$2:$I$6,2,VERO)
Come funziona?
Nell'esempio sopra i nostri valori di ricerca sono nella colonna D, a partire da D2. La tabella Array è H2:I6 (si noti che è ordinata in ordine crescente e assoluta). La colonna da cui riceviamo i dati è 2. E il tipo di corrispondenza che abbiamo definito approssimativo passando TRUE (niente e 1 significherà lo stesso).
Quindi esaminiamo il primo valore di ricerca D2 che contiene 40.
- CERCA.VERT cerca 40 nella prima colonna (H) della matrice di tabelle H2:I6.
- Trova 40 in seconda posizione nella cella H3.
- Ora passa alla seconda colonna da H3 a I3 e restituisce D.
In questo caso vlookup ha trovato la corrispondenza esatta. Vediamo il prossimo caso.
La seconda ricerca valuta D3 che contiene 36.
- CERCA.VERT cerca 36 nella prima colonna della matrice di tabelle H2:I6.
- CERCA.VERT non è riuscito a trovare 36 da nessuna parte nella prima colonna.
- Poiché CERCA.VERT non è riuscito a trovare 36, lo abbina all'ultimo valore trovato che è inferiore al valore di ricerca.
- Il primo valore inferiore a 36 è 0, quindi restituisce F che è correlato a 0.
Questo accade per ogni caso qui. Per 92, l'ultimo valore che è inferiore a 92 è 90. Quindi viene restituito A per la corrispondenza approssimativa.
2. CERCA.VERT Corrispondenza esatta
Questa forma più utilizzata di VLOOKUP e può anche essere molto utile. Quando vuoi cercare il valore esatto nella prima colonna dell'array di tabelle, usi la corrispondenza esatta. Ad esempio, se stai eseguendo una ricerca da un ID di quello che vorresti di più, desideri una corrispondenza esatta invece di qualsiasi valore che sia inferiore a questo valore.
Per forzare CERCA.VERT a una corrispondenza esatta in Excel, passiamo 0 o FALSE come parametro range_lookup.
=CERCA.VERT(valore_ricerca, matrice_tabella, numero_indice_col, 0)
Se il valore non viene trovato nella prima colonna della matrice della tabella, la formula restituirà l'errore #N/A.
Vediamo un esempio.
Esempio 2
In questo esempio, voglio scrivere solo il numero di rotolo dello studente in A2 e in B2 voglio recuperare il nome dello studente e in do2 il suo Grado. Semplice. L'array di tabelle è B5: D17. Come mai?
Quindi, per fare ciò, scrivi queste due formule nella cella rispettivamente nella cella B2 e C2.
=CERCA.VERT(A2,$B$5:$E$17,2,0)
=CERCA.VERT(A2,$B$5:$E$17,3,0)
Ora, qualunque sia l'ID che scriverai nella cella A2, la funzione VLOOKUP cercherà la corrispondenza esatta nella colonna B e restituirà il valore da un dato col_index.
Come funziona CERCA.VERT con corrispondenza esatta?
La corrispondenza esatta VLOOKUP in Excel è semplice. Cerca il valore di ricerca nella prima colonna di un dato table_array e restituisce il valore dal valore del dato numero column_index. Se il valore non viene trovato, CERCA.VERT restituisce l'errore #N/D.
Migliori usi della funzione CERCA.VERT
Vlookup ha molti usi. Discuterò alcuni casi d'uso più utili.
3. Usa VLOOKUP di Excel per la valutazione invece della complessa funzione SE nidificata
Utilizzare la corrispondenza approssimativa VLOOKUP per sostituire la funzione SE nidificata. Nell'esempio 1 abbiamo visto come possiamo usare vlookup per classificare gli studenti in base ai loro voti.
La corrispondenza approssimativa CERCA.VERT è più veloce di quella annidata, poiché utilizza internamente la ricerca binaria.
4. Usa CERCA.VERT per recuperare i dati
Questa attività più comune che può essere eseguita facilmente utilizzando la funzione CERCA.VERT in Excel. Nell'esempio 2 abbiamo fatto questo per recuperare i dati degli studenti usando il loro registro n.
5. Controlla se un valore è in un record usando Vlookup.
Il VLOOKUP può essere utilizzato per verificare se un valore è in qualche elenco o meno.
Nell'Esempio 2, quando scriviamo 152, restituisce l'errore #N/A. Se CERCA.VERT restituisce #N/A, significa che non ha trovato il valore dato da nessuna parte nei dati forniti.
Ho preparato un breve articolo su questo Puoi leggerlo qui.
Utilizzo di una formula CERCA.VERT per verificare se esiste un valore
Ottenere automaticamente l'indice delle colonne
Appartengo a quel gruppo di persone, che non vogliono ripetere lo stesso compito ancora e ancora. Quindi cambiare il numero col_index ancora e ancora mi infastidisce e lo evito sempre. In realtà evito qualsiasi tipo di modifica nelle mie formule una volta che le ho scritte, a meno che e fino a quando non ci sia altra opzione.
L'indice di colonna può essere calcolato automaticamente in Excel. Ci sono molti modi per farlo. Vediamone alcuni qui.
6. Usa la funzione CERCA.VERT con la funzione CONFRONTA
Quindi, come sappiamo, la funzione MATCH restituisce l'indice trovato del valore corrispondente.
Quindi, se abbiamo esattamente le stesse intestazioni nella posizione di ricerca dei dati di origine, che possiamo usare per ottenere col_index. Come? Lasciaci vedere…
Nei dati seguenti, vogliamo solo scrivere il numero di rotolo in G2 e vogliamo recuperare il nome, i voti e il voto dello studente in H2, qualunque sia l'intestazione che scriviamo lì.
Scrivi questa formula nella cella H2
=CERCA.VERT(G2,B2:E14,CONFRONTA(H1,B1:E1,0),0)
Ora, ogni volta che cambierai Intestazione in H1, quel valore in H2 verrà mostrato da quella colonna.
È possibile utilizzare un menu a discesa utilizzando la convalida dei dati con tutte le intestazioni della tabella, per renderlo più intuitivo.
Qui CERCA.VERT sta facendo una corrispondenza esatta. Ora CERCA.VERT sta facendo le sue solite operazioni di estrazione dei dati. La magia viene eseguita dalla funzione MATCH nella posizione col_index.
Qui CERCA.VERT vede qualsiasi valore in G2 nella prima colonna della matrice di tabelle B2: E14. Ora al posto di col_index abbiamo CONFRONTA(H1,B1:E1,0).
La funzione CONFRONTA cerca qualsiasi valore in H1 nell'intervallo SI1: MI1 e restituisce l'indice del valore di corrispondenza.
Ad esempio quando digitiamo alunno in H1 lo cerca nel range B1:E1. Si trova in C2. quindi restituisce 2. Se digitiamo Grado restituirà il grado di studente.
Nota che quando digitiamo Regione, restituisce #N/A. Perché è fuori dall'ambito della tabella. Ne parleremo più avanti nell'articolo.
Questo è il modo migliore per rendere automatico CERCA.VERT. Ci sono altri metodi per ma non sono così flessibili di questo.
7. Usa la funzione CERCA.VERT con la funzione COLONNA
La funzione di colonna restituisce il numero di colonna del riferimento fornito. E di cosa abbiamo bisogno per recuperare i dati usando VLOOKUP? Col_index. Quindi, usando la funzione COLUMN possiamo ovviamente ottenere dati da qualsiasi set di dati.
Prendiamo l'esempio sopra. Ma ora dobbiamo recuperare interi dati. Non solo una colonna.
Scrivi questa formula in H2 e copia in I2 e J2.
=CERCA.VERT($G2,$B$2:$E$14, COLONNA(B1),0)
Questo ti darà un risultato dinamico. Come? Vediamo.
Il VLOOKUP funziona come al solito. In column_index abbiamo scritto COLONNA(B1), che si tradurrà in 2, poiché abbiamo il nome dello studente nella colonna 2 dalla colonna B, restituisce il nome dello studente.
Importante: Funziona perché abbiamo la nostra tabella a partire dalla colonna B. Se la tua tabella è al centro del foglio, dovrai sottrarre o aggiungere alcuni numeri in modo che corrisponda alle tue esigenze.
Ad esempio, se la tabella precedente iniziava da C1 nella stessa struttura, tutte le formule seguenti funzionavano correttamente.
=CERCA.VERT($G2,$C$2:$F$14, COLONNA(B1),0)
=CERCA.VERT($G2,$C$2:$F$14, COLONNA(C1)-1,0)
=CERCA.VERT($G2,$C$2:$F$14, COLONNA(A1)+1,0)
8. CERCA.VERT per unire i dati
Nella maggior parte dei database c'è una query che si unisce alle tabelle, per lo più chiamata query di join ma MS Excel in nessun database, quindi non ha query di join. Ma questo non significa che non possiamo unire due tabelle se abbiamo alcune colonne comuni in due tabelle.
Quindi, quando ottieni alcuni dati da due dipartimenti della stessa azienda, vorresti unirli per analizzare quei dati.
Ad esempio, quando ottieni i voti degli studenti dagli insegnanti e quindi il record di frequenza dal dipartimento amministrativo, vorresti vedere quale studente ha ottenuto quale voto e qual è la sua percentuale di partecipazione, in un'unica posizione.
Dobbiamo unirli nella tabella sottostante.
Marks, dobbiamo ottenere da Marks Table e Presenze dai dati sulle presenze.
Scrivi sotto le formule nella cella D19 per ottenere i segni e trascina verso il basso.
=CERCA.VERT(B19,$B$2:$D$15,3,0)
Scrivi sotto la formula nella cella E19
=CERCA.VERT(B19,$G$3:$I$15,3,0)
E abbiamo unito due tabelle in un'unica tabella. Ovviamente puoi aggiungere più di due tabelle. Hai solo bisogno di ottenere i dati da tutte le tabelle che desideri in un unico posto usando VLOOKUP.
Migliori pratiche di CERCA.VERT
Quindi, tutti gli esempi precedenti abbiamo appena usato vlookup con dati grezzi. In tutti gli esempi precedenti, siamo stati attenti ai dati che stavamo utilizzando. Dovevamo stare attenti, nel fornire i riferimenti agli array di tabelle. Ma ci sono alcuni modi che possono ridurre ampiamente questo sforzo.
9. Usa riferimenti assoluti con CERCA.VERT
Potresti aver notato che in alcuni articoli ho usato intervalli assoluti (segno $ range). Bene, le furie assolute vengono utilizzate quando non vogliamo che l'intervallo venga modificato durante la copia della cella della formula in altre celle.
Ad esempio, se ho = CERCA.VERT(G2,B2: MI14,2,0),0) nella cella H2 e se lo copio o lo trascino verso il basso nella cella H3 la formula cambierà in = CERCA.VERT (G3,B3: MI15,2,0). Tutti i riferimenti sono cambiati relativamente. Qui potremmo voler cambiare il riferimento del valore di ricerca ma non l'array della tabella. Perché B2 esce o si estende e potremmo volerlo avere sempre nei nostri array di tabelle.
Quindi, per evitare ciò, usiamo riferimenti assoluti. Rendiamo assoluti quegli intervalli che non vogliamo modificare, come table_array in H2, =VLOOKUP(G2,$B$2:$E$14,2,0),0). Quando copi H2 in H3 la formula sarà = CERCA.VERT(G3,$B$2:$E$14,2,0),0). Nota che il valore di ricerca è cambiato ma non table_array.
10. Usa intervalli denominati con CERCA.VERT
Nell'esempio sopra, abbiamo usato il riferimento assoluto per correggere table_array. Sembra abbastanza sfocato e difficile da leggere. Che ne dici se puoi semplicemente scrivere? 'dati' al posto di $B$2:$E$14 e farebbe riferimento a $B$2:$E$14 sempre. Renderà ancora più facile leggere e scrivere la formula.
Quindi seleziona l'intervallo e chiamalo "dati".
Per denominare un intervallo, seleziona l'intervallo. Fare clic con il tasto destro, fare clic su Definisci nome e assegnare un nome all'intervallo desiderato. Ora solo = CERCA.VERT(G3,dati,2,0),0) la formula funzionerà esattamente come in precedenza.
Puoi leggere di più sugli intervalli denominati in Excel. Ho spiegato ogni singolo aspetto della gamma denominata qui.
11. Caratteri jolly per la corrispondenza parziale utilizzando VLOOKUP
CERCA.VERT consente la corrispondenza parziale. Sì, mentre esegui la corrispondenza esatta di CERCA.VERT puoi utilizzare gli operatori di caratteri jolly per eseguire la corrispondenza parziale. Ad esempio, se desideri cercare un valore che inizia con "Gil", puoi utilizzare l'operatore jolly * con "Gil" come "Gil*". Vediamo un esempio.
Qui voglio prendere voti di studente il cui nome inizia con Gil. Per ottenere ciò, scriverò sotto la formula.
=CERCA.VERT("*Gill",C2:D14,2,0)
Ci sono due caratteri jolly disponibili in Excel da utilizzare con la funzione CERCA.VERT.
Asterisco (*) carattere jolly. Viene utilizzato quando l'utente non conosce il numero di caratteri presenti e conosce solo alcuni caratteri di corrispondenza. Ad esempio, se l'utente conosce un nome che inizia con "Sm", allora scriverà "Sm*". Se l'utente sa che un valore di ricerca che termina con "123", allora scriverà "*123". (il carattere jolly funziona solo con i testi). E se sa che “se” entra dentro un testo allora scriverà “*se*”.
Punto interrogativo ("?"). Viene utilizzato quando l'utente conosce il numero di caratteri mancanti. Ad esempio, se voglio cercare un valore che contiene 4 caratteri ma non so cosa siano, scriverò semplicemente "????" al posto del valore di ricerca.
Vlookup restituirà il rispettivo valore del primo valore trovato che contiene esattamente quattro caratteri.
Importante: Il carattere jolly funziona solo con valori di testo. Converti i numeri in testo se desideri utilizzare i caratteri jolly con essi.
I punti deboli della funzione CERCA.VERT
CERCA.VERT è davvero una funzione potente e facile per il recupero di valori, ma ci sono molte aree in cui CERCA.VERT non è così utile. Se stai lavorando su excel, devi conoscerli anche tu e come eseguire attività che CERCA.VERT non può svolgere.
12. Impossibile recuperare il valore da sinistra di Table_Array
Il più grande difetto della funzione CERCA.VERT è che non può recuperare il valore dalla sinistra dell'array della tabella. CERCA.VERT cerca solo a destra del valore di ricerca.
Questo perché, VLOOKUP cerca dato valore di ricerca nella prima colonna di dato matrice di tabella. Non restituirà mai un valore dall'esterno della tabella. E se provi a mantenere la colonna sinistra nell'array di tabelle, diventa la prima colonna dell'array di tabelle, quindi il valore di ricerca verrà cercato in quell'intervallo. Che probabilmente genererà errori.
Per cercare i valori da sinistra di un valore di ricerca, usiamo invece INDEX-MATCH. La funzione INDEX-MATCH può cercare valori da qualsiasi colonna del foglio. In realtà, l'intervallo di ricerca e l'intervallo da cui si desidera cercare i valori sono totalmente indipendenti, il che rende la corrispondenza dell'indice altamente personalizzabile.
CERCA.VERT restituisce #N/D anche quando esiste un valore di ricerca.
L'errore #N/A viene restituito dalla funzione CERCA.VERT quando il valore di ricerca non viene trovato. Potresti trovare fastidioso quando un valore esiste nell'intervallo ma CERCA.VERT restituisce l'errore #N/D.
14. Quando i numeri sono formattati come testo
Questo accade principalmente quando i numeri sono formattati come testo. Quando si utilizza il comando CTRL+F, excel lo troverà, ma quando si tenta di utilizzare CERCA.VERT, restituisce #N/A. Questo compito è dato nella maggior parte delle interviste come domande trabocchetto.
Nell'immagine sopra, puoi vedere che 101 è proprio lì, ma la formula restituisce un errore. Come, lo gestisci? Bene, ci sono due metodi.
1. Converti il testo in numero nei dati
Puoi vedere quel tag verde nell'angolo in alto a sinistra nella colonna rollno. Indicano che c'è qualcosa di insolito nel valore della cella. Quando selezioni la cella, mostrerà che il numero è formattato come testo.
Quando fai clic sul punto esclamativo (!), ti mostrerà l'opzione Converti in numero. Seleziona tutte le celle e fai clic su questa opzione. Tutti i valori verranno convertiti in testo.
2. Converti il tuo numero di ricerca in testo nella formula
Nell'esempio sopra, abbiamo mutato i dati originali. Potresti non voler modificare nulla nei dati originali. Quindi vorresti farlo in formula invece. Per modificare il numero in testo nella formula CERCA.VERT, scrivi questo.
=CERCA.VERT(TESTO(G2,"0"),B2:D14,2,0)
Qui la formula cambia dinamicamente il numero in testo e quindi lo abbina a un determinato intervallo.
15. Testo con spazi iniziali e finali
Alcuni dati forniti dagli operatori di data entry e dati da internet non sono puliti. Possono avere spazi finali o alcuni caratteri non stampabili. Durante la ricerca utilizzando questi valori, potresti visualizzare un errore #N/A. Per evitare ciò, pulisci prima i tuoi dati. Per rimuovere gli spazi iniziali utilizzare la funzione TRIM. TRIM rimuove qualsiasi numero di spazi iniziali o finali dal testo.
Quindi, ti suggerisco di aggiungere una nuova colonna e ottenere i tuoi dati puliti lì. Quindi valore incollalo. Ora, se vuoi, puoi eliminare la colonna originale.
=TRIM(PULIRE(testo))
Gestione degli errori di VLOOKUP
Come sai, CERCA.VERT non riesce a trovare un valore, restituisce un errore #N/A. Bene, va bene ottenere l'errore #N/A, forse a volte hai intenzione di ottenerlo, come quando vuoi controllare se un valore esiste già nell'elenco o meno. Lì #N/A significa che il valore non è presente.
Ma #N/A sembra brutto. Che ne dici di ottenere un output intuitivo, come "Id not found" o "Customer not Found". Possiamo usare IFERROR con la funzione VLOOKUP per evitare #N/A.
Usa la formula seguente per intrappolare l'errore in CERCA.VERT.
=IFERROR(VLOOKUP(150,B2:E14,2,0),"Roll non trovato")
16. CERCA.VERT con criteri MULTIPLI
CERCA.VERT non può funzionare con più criteri.Sì, puoi avere un solo criterio per cercare i valori utilizzando la formula CERCA.VERT.
Ad esempio, se hai il nome e il cognome in due colonne separate.
E ora se vuoi cercare un valore il cui nome è John e Last name Dave, allora non puoi farlo normalmente.
Ma c'è una soluzione per questo. È possibile creare una colonna separata concatenando nome e cognome e quindi cercare la colonna del nome e del cognome.
C'è un Alternativa INDEX-MATCH che può cercare più criteri senza alcuna colonna d'aiuto.
17. CERCA.VERT Recupera solo il primo valore trovato
Immagina di avere dei dati nella regione centrale. Ora vuoi ottenere i primi 5 record di dati della regione centrale. Il VLOOKUP restituirà solo il primo valore centrale in tutti e cinque i record. Questo è uno sfondo importante.
Ma ciò non significa che non possiamo raggiungere questo obiettivo. Possiamo usare una formula di array complessi come di seguito.
=INDICE($C$2:$C$14,PICCOLO(SE(V2=$A$2:$A$14,RIGA($A$2:$A$14)-RIGA($A$2)+1),RIGA(1: 1)))
Scrivi questa formula e premi CTRL+MAIUSC+INVIO.
Ed è fatto.
L'ho spiegato nell'articolo come CERCA più valori in Excel.
Quindi sì ragazzi, in questo articolo ho trattato tutti i possibili aspetti della funzione CERCA.VERT secondo le mie conoscenze. Se pensi che mi sia perso qualcosa, faccelo sapere nella sezione commenti qui sotto.
Vlookup Primi 5 valori con valori duplicati utilizzando INDEX-MATCH in Excel
CERCA.VERT Valori multipli
CERCA.VERT con Dynamic Col Index
Corrispondenza parziale con la funzione CERCA.VERT
Usa CERCA.VERT da due o più tabelle di ricerca
Vlookup per data in Excel
Utilizzo di una formula CERCA.VERT per verificare se esiste un valore
Come eseguire il VLOOKUP da un diverso foglio Excel
CERCA.VERT con numeri e testo
Funzione SE, ISNA e CERCA.VERT
Funzione ISNA e CERCA.VERT
SE ERRORE e funzione CERCA.VERT