Share
UN VLOOKUP, abbreviazione di "vertical lookup" è una formula in Microsoft Excel per abbinare i dati di due elenchi. Invece di saltare tra fogli di calcolo e digitare i dati corrispondenti, puoi scrivere una formula CERCA.VERT per automatizzare il processo.
La combinazione di due elenchi è una situazione perfetta per utilizzare un CERCA.VERT. A sinistra (nell'immagine sopra), abbiamo le informazioni sullo spostamento dei dipendenti. Vogliamo aggiungere il titolo di lavoro del dipendente ai dati di turno. Con un elenco separato di dipendenti e i loro titoli di lavoro, possiamo scrivere una formula VLOOKUP per inserire il titolo da una lista di ricerca.
Un VLOOKUP di successo ha bisogno di tre cose:
CERCA.VERT è una formula di Microsoft Excel che è essenziale per lavorare con più serie di dati. In questo tutorial, ti insegnerò come padroneggiarlo e usarlo.
Esempio di formula VLOOKUP utilizzata per cercare i dati dei dipendenti.Utilizzando l'esempio sopra, ora ho scritto una formula VLOOKUP che cerca l'ID del dipendente e inserisce il titolo del lavoro nei dati dello spostamento. Poiché entrambi i fogli hanno un ID dipendente, Excel può cercare il titolo di lavoro corrispondente. La parte migliore di VLOOKUP è che ora posso trascinare verso il basso la stessa formula e cercherà ogni titolo di lavoro univoco.
Prima di andare avanti, assicurati di Scarica l'allegato gratuito seguire. È un esempio di cartella di lavoro di esempio che ho creato, che utilizzeremo per illustrare questo tutorial.
Per il modo più veloce per imparare le basi della formula CERCA.VERT, controlla lo screencast qui sotto. Il video illustra vari esempi della formula CERCA.VERT, utilizzando la cartella di lavoro di esempio.
Continua a leggere per seguire le istruzioni scritte e apprendi alcune tecniche aggiuntive che non sono incluse nello screencast.
Utilizzare le schede "Ingredient Orders" e "Supplier List" per questo esempio di Excel VLOOKUP.
Diciamo che gestiamo un ristorante e stiamo ordinando i nostri ordini settimanali ai fornitori. Gli chef ci hanno fornito un elenco di ingredienti da ordinare e abbiamo bisogno di inserire informazioni sul fornitore.
Ci sono tre pezzi che dobbiamo aggiungere per ogni ordine:
In questa cartella di lavoro ci sono due schede:
Nella parte superiore, abbiamo i dati dei nostri ordini, che si trovano nella scheda "Ordini degli ingredienti". Di seguito è riportato "Elenco fornitori", che fungerà da nostro elenco di ricerca.Il campo comune tra i due tavoli è il Ingrediente scheda. Usiamolo per cercare ognuno dei tre campi e aggiungerlo alla lista degli ordini.
Sul Ingredienti scheda, facciamo clic nella prima cella del fornitore vuota, F5, e premere il segno di uguale per avviare la formula VLOOKUP. Quindi, digita " VLOOKUP ( " per iniziare la formula.
= VLOOKUP (
Ricorda che la nostra chiave primaria, la parte di dati che appare in entrambe le liste, è la ingrediente, quindi lo useremo per la ricerca. O fai clic sulla cella B5, o digitarla nella formula. Successivamente, aggiungi una virgola dopo "B5" in modo che possiamo inserire la parte successiva della formula.
= VLOOKUP (B5,
Ora, dobbiamo dare alla formula la nostra lista di ricerca. Con la formula ancora aperta, fare clic su Elenco fornitori scheda. Ora, fai clic sulla cella A3, quindi fai clic e trascina per evidenziare e selezionare A3 a G13, l'intera tabella di consultazione. Assicurati e premi il Tasto F4 sulla tastiera per rendere la formula un riferimento assoluto (ne parleremo più avanti). Infine, inserisci un'altra virgola.
Puntare Excel all'elenco di ricerca.Dopo aver inserito la virgola per la cella di ricerca, passare le schede e puntare Excel all'elenco di ricerca. Fare clic e trascinare tra le celle A3 e G3 per selezionare i dati da cercare. Assicurati e premi F4 sulla tastiera durante questo passaggio per creare un riferimento assoluto, che bloccherà le celle da utilizzare per la ricerca.
= CERCA.VERT (B5, 'Elenco fornitori'! $ A $ 3: $ G $ 13,
In seguito, dovremo indicare a Excel da quale colonna estrarre. Ricorda che il nostro primo elemento da inserire è il Fornitore nome, che si trova nella seconda colonna dell'elenco di ricerca. Aggiungi il numero 2 alla formula per estrarre dalla seconda colonna della ricerca e un'altra virgola.
= CERCA CERCA (B5, 'Elenco fornitori'! $ A $ 3: $ G $ 13,2,
Infine, aggiungeremo FALSE per una ricerca esatta, quindi chiudere le parentesi:
= VLOOKUP (B5, 'Elenco fornitori'! $ A $ 3: $ G $ 13,2, FALSE)
La nostra formula sta funzionando perfettamente, inserendo il fornitore per le patate. La buona notizia è che non è necessario riscrivere la stessa formula più e più volte, basta fare doppio clic nell'angolo in basso a destra della cella F5 (puoi vedere la piccola scatola verde sull'angolo della cella) Per estendere la formula verso il basso (come mostrato sopra).
La formula funziona perfettamente! Ok, passiamo ora a inserire i dati per gli altri due campi: il numero di telefono del fornitore e il giorno di consegna.
Poiché abbiamo usato un riferimento assoluto, possiamo sostanzialmente riutilizzare la stessa formula che abbiamo scritto con un piccolo aggiustamento. Aggiungiamo il numero di telefono del fornitore successivo.
Inizieremo copiando e incollando la cella F5 (la nostra cella Fornitore) a cella G5. Di solito uso solo le scorciatoie da tastiera Ctrl + C e Ctrl + V per copiare e incollare l'intera cella.All'inizio, questo non funzionerà e vedrai un N / A nella cella.
Copia e incolla la ricerca con le regolazioni in Excel.È molto più facile copiare e incollare la nostra formula in un'altra cella, ma richiede qualche ritocco. All'inizio, Excel cercherà la cella C5 per la ricerca, ma è necessario regolarlo su "B5" nella barra della formula. Una volta che lo facciamo, la ricerca funzionerà - quasi.
Dovremo passare alla barra della formula e modificare la prima parte della formula C5 indietro a B5. Quando abbiamo spostato il formula su una colonna, Excel ha aggiornato altre parti della formula. Stavamo ricevendo un "N / A" perché Excel stava tentando di abbinare la quantità (Colonna C) al nostro elenco di ricerca, ma il nostro elenco di ricerca non include la quantità.
Finora, la nostra formula dovrebbe essere così:
= VLOOKUP (B5, 'Elenco fornitori'! $ A $ 3: $ G $ 13,2, FALSE)
Tuttavia, nota nello screenshot in alto che stiamo tirando il bit sbagliato di dati nella colonna "Telefono". Stiamo ancora tirando la seconda colonna nella lista di ricerca con il "2" nella formula. Abbiamo bisogno di cambiare questo numero di colonna con il telefono di contatto del nostro fornitore.
Torniamo indietro e controlliamo l'elenco di ricerca. Noterai che il numero di telefono si trova nella settima colonna nell'elenco di ricerca. Torniamo alla nostra formula di Excel e aggiorniamo la ricerca da estrarre dalla settima colonna.
Abbiamo bisogno di aggiornare il "2" nella nostra formula di Excel in un "7" per tirare dalla settima colonna nel nostro elenco di ricerca.Tutto quello che dobbiamo fare è aggiornare la sezione della colonna della nostra lista di ricerca da un "2" a un "7" e ora funziona benissimo!
Si noti che il telefono del fornitore funziona alla grande ora che abbiamo aggiornato la nostra formula per utilizzare la settima colonna dall'elenco di ricerca. Ora, trascina la formula verso il basso per aggiornarla per tutte le celle.La nostra formula finale per la ricerca del numero di telefono del fornitore:
= VLOOKUP (B5, 'Elenco fornitori'! $ A $ 3: $ G $ 13,7, FALSE)
Infine, lavoriamo nella colonna del giorno di consegna del fornitore. Copia e incolla cella G5 alla cella H5. Di nuovo, dovremo correggere la nostra formula cambiando C5 di nuovo a B5 usare l'ingrediente come chiave primaria. Quindi, aggiorna il "7" a un "5" per usare la quinta colonna dalla nostra tabella di ricerca.
È stata applicata la formula di ricerca del giorno di consegna fornitore finale.La nostra formula finale per il giorno di consegna del fornitore:
= VLOOKUP (B5, 'Elenco fornitori'! $ A $ 3: $ G $ 13,5, FALSE)
Questo è tutto! Abbiamo scritto fondamentalmente una formula e l'abbiamo ottimizzata per ottenere tutti i dati necessari per il nostro prossimo ordine.
Quindi, hai scritto la formula CERCA.VERT seguendo le istruzioni passo-passo, ma non funziona ancora. Excel prende le cose alla lettera, quindi dobbiamo stare attenti con i dati e la formula CERCA.VERT. Diamo un'occhiata a diverse idee per correggere una formula VLOOKUP che semplicemente non funziona.
Uno dei problemi più comuni con VLOOKUP è quando ci sono più corrispondenze nell'elenco di ricerca. Quando utilizzi un CERCA.VERT, esso corrisponderà al primo elemento presente nell'elenco.
Il nostro VLOOKUP dice che Carrie Richard è il nostro direttore del marketing, ma sappiamo che è stata recentemente promossa al presidente della compagnia. Perché la nostra ricerca non funziona??
Ops, sembra che ci fossero due elenchi per Carrie Richard nella lista di ricerca - uno per il direttore del marketing e uno per il presidente. Una volta eliminata la riga "Director of Marketing" dall'elenco di ricerca, i nostri dati sono corretti.Il punto di CERCA.VERT è cercare un elemento corrispondente su un elenco. L'elenco di ricerca non dovrebbe contenere duplicati per il chiave primaria, l'elemento che stai utilizzando per abbinare. In caso contrario, Excel viene confuso e mostra solo la prima corrispondenza.
Un altro problema comune è che i nostri dati potrebbero non corrispondere effettivamente agli occhi di Excel. Dati con uno spazio prima che si dice abbiano uno spazio "iniziale", mentre i dati con uno spazio dopo che ha uno "spazio finale".
Questo VLOOKUP è scritto perfettamente, ma non funziona. È possibile vedere chiaramente il nome nella lista di ricerca, ma Excel non restituisce una corrispondenza. Continua a leggere per scoprire perché.Se il pezzo di dati corrispondente ha uno spazio prima o dopo, Excel vede questi due pezzi di dati completamente diversi e non restituirà una corrispondenza. Le viste di Excel "_Andrew", "Andrew_" e "Andrew" come tre pezzi unici di dati che non verranno confrontati in CERCA.VERT.
Risulta che c'è uno "spazio finale", o un singolo spazio dopo il nome nella cella. È impossibile da vedere, ma può interrompere un CERCA.VERT perché Excel considera "Alyssa Reddall" e "Alyssa Reddall (spazio)" in modo diverso. Una volta eliminato lo spazio, CERCA.VERT funzionerà normalmente.Se i tuoi dati hanno spazi iniziali o finali, usa il TRIM funzione in Excel per pulirlo. VLOOKUP dovrebbe funzionare perfettamente dopo averlo utilizzato per rimuovere gli spazi iniziali e finali.
Come forse saprai, puoi trascinare una cella verso il basso dal manico per incollare la formula in altre celle. Tuttavia, questo a volte interrompe il nostro CERCA.VERT perché i riferimenti di cella cambiano.
In questo screenshot, ho trascinato il VLOOKUP in basso nell'elenco per applicarlo ad altre celle. Tuttavia, si noti che il riferimento di ricerca è cambiato da A2 a B15, da A16 a B29, quindi perché la formula non funziona. La mia lista di ricerca è in realtà in A2 a B15, quindi la ricerca è ora interrotta.Man mano che le formule vengono tirate verso il basso, il riferimento per la tabella di ricerca viene rimosso dall'allineamento. All'improvviso, la tabella di ricerca manca di righe dalla tabella di ricerca e il nostro CERCA.VERT non funziona.
La correzione è di rendere la formula un riferimento assoluto, in modo che quando si trascina la formula verso il basso, l'elenco a cui punta non cambia. Fare clic nella cella in cui è stato scritto il CERCA.VERT, quindi fare clic in qualche punto nel riferimento all'elenco di ricerca. Quindi, premere F4 . Noterai che la formula cambia per includere i simboli del dollaro.
Si noti che la formula (mostrata nella parte superiore di questo screenshot nella barra della formula) ora include i simboli del dollaro nel riferimento all'elenco di ricerca, il che dimostra che utilizza un riferimento assoluto. Ora, quando trascino la formula verso il basso, blocca la formula per l'elenco di ricerca e funziona perfettamente.CERCA.VERT è una di quelle formule essenziali per essere utenti produttivi di Excel. Semplicemente non c'è abbastanza tempo per cercare manualmente i dati e ridigitarli più e più volte, quindi le formule come VLOOKUP sono importanti per imparare.
Se hai problemi con l'uso della formula CERCA.VERT, sentiti libero di lasciare un commento qui sotto per la risoluzione dei problemi.
Accentsconagua