Come utilizzare una formula CERCA.VERT per ottenere dati da un elenco di Excel. Risolvi i problemi di CERCA.VERT quando le cose vanno storte. Video passo passo e cartelle di lavoro Excel con esempi di CERCA.VERT.
Video CERCA.VERT
Guarda i passaggi per creare una formula CERCA.VERT nel video di ricerca del prezzo del prodotto, mostrato di seguito. Le istruzioni scritte sono sotto il video. Scarica il file di esempio di ricerca del prezzo del prodotto da seguire insieme al video (scarica il file n. 4).
Seleziona una posizione per una tabella di ricerca
È una buona idea memorizzare ogni ricerca tabella su un foglio di lavoro separato nella cartella di lavoro. Quindi, quando aggiungi ed elimini righe nelle tabelle di ricerca, non potrai aggiungere o eliminare accidentalmente righe in qualsiasi altra tabella.
In questo esempio, la tabella di ricerca si trova su un foglio denominato Prodotti.
Crea una tabella di ricerca
Le formule di ricerca possono funzionare verticalmente, cercando valori in una colonna, oppure possono lavorare orizzontalmente, cercando valori su una riga . In questo esempio, le informazioni verranno memorizzate verticalmente, con i valori in una colonna, e successivamente utilizzeremo una formula VLookup per eseguire una ricerca verticale.
- Immettere le intestazioni nella prima riga
- La prima colonna dovrebbe contenere i valori chiave univoci su cui basare la ricerca. In questo esempio, è possibile trovare il prezzo per un codice prodotto specifico.
- Se nel foglio di lavoro sono presenti altri dati, lasciare almeno una riga vuota nella parte inferiore della tabella e una colonna vuota a destra della tabella, per separare tabella di ricerca dagli altri dati.
Nota: per semplificare il riferimento alla tabella, puoi assegnare un nome allintervallo. Ci sono istruzioni qui: Assegnazione di un nome a un intervallo ▲ TOP
Argomenti della funzione CERCA.VERT
La funzione CERCA.VERT ha quattro argomenti:
- valore_cerca : Quale valore vuoi cercare? In questo esempio, il codice del prodotto si trova nella cella A7 e desideri trovare il suo pr nome del prodotto.
- table_array: dovè la tabella di ricerca? Se utilizzi un riferimento assoluto ($ A $ 2: $ C $ 5), invece di un riferimento relativo (A2: C5), sarà più semplice copiare nella formula su altre celle. Oppure, assegna un nome alla tabella di ricerca e fai riferimento ad essa per nome.
- col_index_num: quale colonna ha il valore che desideri venga restituito? In questo esempio, i nomi dei prodotti si trovano nella seconda colonna della tabella di ricerca.
- : vuoi una corrispondenza esatta? Va bene una corrispondenza approssimativa?
Se utilizzi TRUE come ultimo argomento o ometti lultimo argomento, è possibile restituire una corrispondenza approssimativa. Questo esempio ha FALSE come ultimo argomento, quindi se il codice del prodotto non viene trovato, il risultato sarà # N / D. (Nota: Excel è piuttosto indulgente e accetterà 0 invece di FALSE e 1 invece di TRUE.) ▲ TOP
Crea una formula VLookup
Dopo aver creato la tabella di ricerca, è possibile creare altre formule nella cartella di lavoro e estrarre informazioni dallelenco dei prodotti. Ad esempio, per preparare una fattura, è possibile inserire un codice prodotto e le formule otterranno il nome o il prezzo del prodotto dalla tabella dei prodotti.
In questo esempio, la fattura viene creata su un foglio denominato Invoice. La formula CERCA.VERT dovrebbe trovare una corrispondenza esatta per il codice del prodotto e restituire il nome del prodotto.
Per creare la formula CERCA.VERT che calcola il prezzo del prodotto, segui questi passaggi:
- Seleziona il foglio fattura
- Inserisci il codice prodotto A23 nella cella A7
- Nella cella B7, avvia la formula CERCA.VERT:
= CERCA.VERT ( - Fai clic su cella A7 per inserire il suo riferimento.
- Digita una virgola per terminare il primo argomento
- Seleziona la scheda Prodotti
- Seleziona le celle A2: C5 (lelenco dei prodotti )
- Premere il tasto F4 per modificare i riferimenti di cella in assoluto. La formula ora dovrebbe essere simile a questa:
= CERCA.VERT (A7, Prodotti! $ A $ 2: $ C $ 5 - Digita una virgola per terminare il secondo argomento.
- Digita 2, che è la colonna nella tabella di ricerca che contiene il nome del prodotto.
- Digita una virgola per terminare il terzo argomento.
- Digita FALSE, per specificare che viene trovata una corrispondenza esatta per il codice del prodotto, e aggiungi la parentesi di chiusura.
- La formula ora dovrebbe apparire l ad esempio:
= CERCA.VERT (A7, Prodotti! $ A $ 2: $ C $ 5,2, FALSO) - Premere il tasto Invio per completare la formula. Verrà visualizzato il nome del prodotto.
Nota: per restituire il prezzo del prodotto, creare una formula CERCA.VERT che faccia riferimento alla colonna 3 della tabella di ricerca. Ad esempio, immettere la seguente formula nella cella C7:
= VLOOKUP (A7, Products! $ A $ 2: $ C $ 5,3, FALSE) ▲ TOP
VLOOKUP Formula per intervallo di valori
In alcune situazioni, è preferibile una corrispondenza approssimativa, quindi diversi valori restituiranno lo stesso risultato. Ad esempio, quando si valutano i documenti degli studenti, tutti i documenti con un voto di 85 o più dovrebbero ricevere un voto A.
Per visualizzare i passaggi per la creazione di questa formula, guarda il video CERCA.VERT mostrato di seguito. Le istruzioni scritte sono sotto il video.
In questo esempio, la tabella di ricerca viene creata su un foglio denominato Grades. Per creare la tabella di ricerca, immettere il punteggio minimo per ogni voto nella colonna A. Immettere il voto corrispondente nella colonna B. Ordinare i punteggi in ordine crescente.
Le celle A2: B6 sono state denominate GradeList.
I punteggi vengono inseriti in un foglio denominato Report Card, dove una formula CERCA.VERT calcola il voto.
Nella schermata qui sotto, la formula è stata copiata fino alla riga 6 e puoi vedere la formula nella cella C6.
Combina CERCA.VERT con CORRISPONDENZA
Invece di digitare il numero di colonna in una formula CERCA.VERT, utilizza la funzione CONFRONTA per trovare la colonna corretta nella tabella di ricerca. Questo ha un paio di vantaggi:
- Rende la formula flessibile, quindi è più facile copiare la formula in un foglio di lavoro.
- Può prevenire problemi se vengono aggiunte nuove colonne nella tabella di ricerca o se le colonne di ricerca vengono riorganizzate.
Questo video mostra i passaggi e ci sono istruzioni scritte per un altro esempio, sotto il video.
Il tuo browser non può mostrare questo frame. Ecco un collegamento alla pagina
CERCA.VERT con MATCH per i dettagli dellordine
In questo esempio, una formula CERCA.VERT restituirà i dettagli dellordine da una tabella di ricerca, in base al numero ID dellordine . Ecco la tabella di ricerca, denominata tblOrders.
NOTA: questo esempio si trova nella cartella di lavoro di esempio n. 1, sul foglio denominato OrdersMATCH.
Ecco il foglio di lavoro con le formule CERCA.VERT. Vogliamo la regione, la data dellordine e limporto dellordine per ogni ordine, quindi sono necessarie 3 formule VLOOKUP.
Se i numeri di colonna vengono digitati nella formula, è necessaria una formula diversa in ciascuna colonna:
- Regione: = VLOOKUP ($ B6, tblOrdersALL, 2,0)
- OrderDate: = VLOOKUP ($ B6, tblOrdersALL, 3,0)
- OrderAmt: = VLOOKUP ($ B6, tblOrdersALL, 4,0)
La funzione MATCH
Invece di digitare il numero di colonna nella formula CERCA.VERT, possiamo usare la funzione CONFRONTA. La funzione CONFRONTA trova la posizione di un elemento in un elenco e restituisce il numero di posizione.
Nella schermata seguente, la formula CONFRONTA restituisce 2 come posizione di “Regione”, nelle celle di intestazione (A1: D1) per la tabella di ricerca.
= CONFRONTA ( C5, Orders_ALL! $ A $ 1: $ D $ 1, 0)
NOTA: affinché questa tecnica funzioni correttamente, il le intestazioni nel foglio CERCA.VERT devono corrispondere esattamente alle intestazioni della tabella di ricerca. Per garantire una corrispondenza esatta, le celle di intestazione CERCA.VERT sono collegate alle celle di intestazione della tabella di ricerca.
Aggiungi CORRISPONDENZA a CERCA.VERT
Per aggiungere la funzione CONFRONTA alla formula CERCA.VERT, basta sostituire il numero di colonna digitato
= VLOOKUP ($ B6, tblOrdersALL, 2,0)
con la formula MATCH:
= VLOOKUP ($ B6, tblOrdersALL, MATCH (C5, Orders_ALL! $ A $ 1: $ D $ 1, 0), 0)
Copia la formula CERCA.VERT su
Ora, invece di aver bisogno di una formula diversa in ogni colonna, puoi copiare la formula e utilizzare la stessa formula in tutte le colonne. In ogni colonna, farà riferimento alla cella di intestazione in quella colonna e troverà la sua posizione nella tabella di ricerca.
NOTA: se stai riempiendo la formula tra colonne con una formattazione diversa, segui questi passaggi:
- Seleziona la cella con la formula che desideri copiare
- Posiziona il puntatore sul quadratino di riempimento sulla cella selezionata (quadratino nellangolo in basso a destra)
- Premi il pulsante DESTRO del mouse e trascina fino allultima cella che necessita della formula
- Rilascia il pulsante del mouse e fai clic su Riempi senza formattazione
Combina IF e CERCA.VERT
Puoi utilizzare una formula IF con una formula VLookup, per restituire valori esatti se trovati e una stringa vuota se non trovato.
Per vedere i passaggi per impostare la formula SE e CERCA.VERT, puoi guardare questo breve video. Le istruzioni scritte sono sotto il video.
Per nascondere gli errori combinando IF con CERCA.VERT, segui questi passaggi:
- Nel foglio della fattura, nella cella A8, inserisci il prodotto codice A28. Se la formula VLookup nella cella B8 ha FALSE come quarto argomento, il risultato è # N / D, perché non esiste una corrispondenza esatta per il codice prodotto nella tabella di ricerca.
- Avvolgi la formula VLookup con un Formula IF (in questo esempio lelenco dei prodotti è stato denominato), utilizzando la funzione ISNA per verificare la presenza di un errore # N / A:
- Premere il tasto Invio e la cella appare vuota. Poiché non è stata trovata alcuna corrispondenza esatta, la formula VLookup ha restituito un # N / D, quindi il risultato della funzione ISNA è TRUE. La formula IF lo ha convertito in una stringa vuota.
= IF (ISNA (VLOOKUP (A8, ProductList, 2, FALSE) ), “”, CERCA.VERT (A8, Elenco prodotti, 2, FALSO))
Combina SE.ERRORE e CERCA.VERT
Grazie a Chip Pearson per aver suggerito questa formula.
In Excel 2007, è stata introdotta una nuova funzione, SE.ERRORE . È possibile utilizzare una formula SE.ERRORE con VLookup per controllare più tabelle per un valore e restituire le informazioni correlate quando vengono trovate. In questo esempio, tre regioni, Ovest, Est e Centro, dispongono di fogli ordini. Su ogni foglio è presente un intervallo denominato: OrdersW, OrdersE e OrdersC.
In un foglio denominato Ordini, puoi inserire un ID ordine, quindi utilizzare CERCA.VERT con SE.ERRORE per controllare ogni intervallo denominato e visualizzare le informazioni sullordine selezionato.
- Nel foglio dellordine, nella cella B6, inserisci un 4 come ID ordine. Tale ordine è stato inserito nella regione centrale.
- Per controllare semplicemente la tabella della regione orientale, la formula CERCA.VERT nella cella C6 sarebbe:
= CERCA.VERT (B6, OrdersE, 2, FALSE) - Premere il tasto Invio e la formula CERCA.VERT restituisce un # N / A, perché lID ordine 4 non è nella tabella degli ordini delle regioni orientali.
- Poiché un ordine potrebbe avere è stato inserito in una delle tre regioni, è necessaria una formula che controlli ciascuna tabella.
Se lID ordine non viene trovato nella prima tabella, la formula dovrebbe controllare la seconda tabella. Se lID ordine non è nella seconda tabella, dovrebbe controllare la terza tabella. Se lID ordine non è “t nella terza tabella, nella cella dovrebbe essere visualizzato il messaggio” Non trovato “. - La formula SE.ERRORE consente di controllare un valore, quindi specificare cosa fare se viene rilevato un errore. Se utilizzi SE.ERRORE con la formula esistente, puoi visualizzare “Non trovato”, invece dellerrore # N / D:
= SE.ERRORE (CERCA.VERT (B6, OrdiniE, 2, FALSO), “Non trovato”) - Per controllare tutte e tre le tabelle, puoi seguire le formule SE.ERRORE e CERCA.VERT:
Questo controlla la tabella OrdersE e se viene trovato un errore, controlla la tabella OrdersW, quindi OrdersC. Se lID ordine non viene trovato in nessuna delle tre tabelle, nella cella viene visualizzato il messaggio Non trovato.
Puoi anche controllare più tabelle nelle versioni precedenti di Excel, dove SE.ERRORE non è disponibile, utilizzando un formula più lunga:
CERCA.VERT per valori combinati
In alcune tabelle potrebbero non essere presenti valori univoci qualsiasi colonna nella tabella di ricerca. Ad esempio, nella tabella mostrata di seguito, Giacca è elencata due volte nella colonna A. Tuttavia, esiste un solo record per ogni combinazione di giacca e taglia: Giacca Media nella riga 4 e Giacca Grande nella riga 5.
Se devi trovare il prezzo per una giacca grande, un CERCA.VERT basato solo sulla colonna A restituirà il prezzo per la prima giacca elencata (media ). Daresti un prezzo inferiore alla giacca, vendendola a 60,00 invece di 65,00.
Per creare valori di ricerca univoci, devi può inserire una nuova colonna sul lato sinistro della tabella e utilizzare una formula per combinare il prodotto e la taglia. Nella cella A2, la formula combina il valore in B2 e il carattere pipe e il valore in C2.
= B2 & “|” & C2
Copia la formula fino allultima riga di dati, in modo che ogni riga abbia un valore univoco nella colonna A.
Nota : Invece del carattere barra verticale, potresti usare un altro carattere che non è incluso nei tuoi dati.
Quindi, in una formula VLOOKUP, combina il prodotto e la dimensione come Lookup_value. Nella cella H1, la formula combina il valore in F1 e il carattere barra verticale e il valore in G1.
= VLOOKUP (F1 &” | “& G1, $ A $ 2: $ D $ 5,4, FALSE)
Nota: il prezzo ora è in colonna 4, invece della colonna 3. ▲ TOP
Risolvi i problemi con la formula CERCA.VERT
La tua formula CERCA.VERT può restituire un # N / D, anche se il valore che stai cercando sembra essere nella tabella di ricerca. Le cause più comuni sono:
Testo e numero
Spazio caratteri
Altri caratteri
Testo e numero
Una causa comune di questo errore è che uno dei valori è un numero e laltro è il testo. Ad esempio, la tabella di ricerca può contenere “123 (testo) e il valore da cercare è 123 (un numero).
- Se possibile, converti il testo in numeri, utilizzando uno dei metodi mostrati qui: Converti testo in numeri
- Se non puoi convertire i dati, puoi convertire il valore di ricerca allinterno della formula CERCA.VERT utilizzando una delle soluzioni seguenti:
- Valori di ricerca sono testo, la tabella ha numeri
- I valori di ricerca sono numeri, la tabella ha testo
- Per capire quali valori sono testo e quali numeri, vedere i passaggi dettagliati per la risoluzione dei problemi relativi al numero o al testo
1. I valori di ricerca sono Testo e la tabella contiene numeri
Se la tabella di ricerca contiene numeri e il valore da cercare è testo, utilizzare una formula simile alla seguente:
= CERCA.VERT (-A7, Prodotti!$ A $ 2: $ C $ 5,3, FALSE)
Il doppio unario (-) converte il testo in un numero e funzionerà correttamente anche se i valori di ricerca sono numeri.
2. I valori di ricerca sono Numeri e la tabella contiene testo
Se la tabella di ricerca contiene testo e il valore da cercare è numerico, utilizzare una formula simile alla seguente:
= CERCA.VERT (A7 & “”), Prodotti! $ A $ 2: $ C $ 5,3, FALSE)
OR
= CERCA.VERT (TESTO (A7, “00000”) , Products! $ A $ 2: $ C $ 5,3, FALSE)
La funzione TEXT converte un numero in testo e funzionerà correttamente anche se i valori di ricerca sono testo. Nel primo esempio, loperatore & crea una stringa di testo da un numero non formattato. Nel secondo esempio, un numero formattato con zeri iniziali (ad es. 00123) corrisponderebbe a un testo “numero” con zeri iniziali.
Per vedere i passaggi per risolvere il problema CERCA.VERT quando la tabella di ricerca contiene valori di testo, guarda questo breve video tutorial.
Spazi in un valore e non nellaltro
Unaltra potenziale causa per cui non viene trovato alcun valore corrispondente è una differenza di spazi. Uno dei valori può contenere spazi iniziali (o finali o spazi incorporati) e laltro no. Per testare i valori, puoi utilizzare la funzione LEN, per controllare la lunghezza di ciascun valore.
Ad esempio: = LEN (A7) restituirà il numero di caratteri nella cella A7. Dovrebbe essere uguale al numero di caratteri nella cella corrispondente nella tabella di ricerca.
Se possibile, rimuovere il non necessario spazi e la formula CERCA.VERT dovrebbe funzionare correttamente. Se non è possibile rimuovere gli spazi, utilizzare la funzione TAGLIA in CERCA.VERT per rimuovere gli spazi iniziali, finali o duplicati. Ad esempio:
= VLOOKUP (TRIM (A7), ProductList, 2, FALSE)
Altri caratteri
Se la funzione TRIM da sola non risolve il problema , puoi provare uno dei seguenti suggerimenti:
- Funzione SOSTITUISCI
- Funzione PULISCI
- Macro per rimuovere caratteri web
Funzione SOSTITUISCI
Usa la funzione SOSTITUISCI per rimuovere i caratteri indesiderati. Cè un esempio sul blog di Contextures: Pulisci dati Excel con TRIM e SOSTITUISCI
Funzione PULISCI
Un altro modo per risolvere i problemi di CERCA.VERT è con la funzione CLEAN, che può rimuovere alcuni caratteri indesiderati dal testo. Ci sono ulteriori informazioni sulla funzione CLEAN in questo post del blog di Contextures: 30 funzioni Excel in 30 giorni: 29 – CLEAN
Caratteri Web in un valore e non nellaltro
Se hai copiato dati da una pagina Web, potrebbe contenere uno spazio unificatore HTML (& nbsp) caratteri. David McRitchie ha scritto una macro per rimuoverli, insieme a o ther spazi caratteri
Problemi durante lordinamento della formula CERCA.VERT
Una formula CERCA.VERT può restituire inizialmente i risultati corretti, ma poi mostra risultati errati se lelenco di elementi è ordinato. Ciò può verificarsi se il riferimento al valore di ricerca include un nome di foglio. Ad esempio:
= VLOOKUP (“Order Form”! B5, Products! $ B $ 2: $ C $ 6,2, FALSE)
NOTA: questo problema può verificarsi con altre funzioni anche, come una formula di ricerca INDICE / CORRISPONDENZA.
Guarda questo video per vedere i passaggi per risolvere il problema e scarica il file di esempio dellordinamento CERCA.VERT. Le istruzioni scritte sono sotto il video.
Nomi dei fogli nel riferimento
Questo tipo di riferimento viene creato se fai clic su un altro foglio durante la creazione della formula. Non appena lo fai, Excel aggiunge il nome del foglio a tutti i riferimenti successivi nella formula.
Nella schermata sopra, Dress si trova nella cella B9 e la cella C9 mostra il prezzo corretto di $ 30.
Tuttavia, dopo aver ordinato i prodotti AZ, Dress si sposta fino alla cella B5, ma la formula nella cella C5 continua a fare riferimento alla cella B9. A causa dei nomi dei fogli nei riferimenti, Excel mantiene i riferimenti originali, invece di mantenere un riferimento alla riga corrente. La cella C5 mostra il prezzo di un maglione, invece di un vestito. ▲ INIZIO
Risolvi il problema
Per risolvere il problema, rimuovi tutti i nomi di fogli non necessari dal CERCA.VERT riferimenti di cella. Ecco la formula rivista per la cella C5:
= CERCA.VERT (B5, Prodotti! $ B $ 2: $ C $ 6,2, FALSO)
Dopo aver rimosso i nomi dei fogli non necessari, lelenco può essere ordinato in modo sicuro e per ogni elemento verranno visualizzati i risultati corretti.
Scarica i file di esempio
1. Ottieni la cartella di lavoro di esempio CERCA.VERT (Excel 2007 e versioni successive). Il file zippato è in formato xlsx e non contiene macro.
2. Per Excel 2003, scarica questa versione del file di esempio CERCA.VERT. Il file zippato è in formato xls e non contiene macro.
3. Per visualizzare il problema che può verificarsi durante lordinamento con CERCA.VERT, scarica questo file di esempio del problema relativo allordinamento di CERCA.VERT. Il file zippato è in formato xlsx e non contiene macro.
4. Ottieni la cartella di lavoro Ricerca prezzo prodotto, utilizzata nel tutorial video.Il file zippato è in formato xlsx e non contiene macro. ▲ INIZIO
Altri tutorial
CERCA.VERT Risoluzione dei problemi con numero / testo
CERCA.VERT da unaltra cartella di lavoro
CERCA.ORIZZ.
SCEGLI Funzione
Ricerca – 2 criteri
CERCA
INDICE / CONFRONTA
Confronta funzioni di ricerca
Elenco funzioni