Come utilizzare VLOOKUP in Excel

0
204

invoice_database

VLOOKUP è una delle funzioni più utili di Excel ed è anche una delle meno conosciute. In questo articolo, demistificare VLOOKUP come esempio di vita reale. Creeremo un utilizzabile Modello di fattura per un'azienda fittizia.

VLOOKUP è un Excel funzione. Questo articolo supporrà che il lettore abbia già una conoscenza approfondita delle funzioni di Excel e che possa utilizzare funzioni di base come SOMMA, MEDIA e OGGI. Nel suo utilizzo più comune, VLOOKUP è a Banca dati funzione, nel senso che funziona con le tabelle del database – o più semplicemente, liste di cose in un foglio di lavoro di Excel. Che tipo di cose? Bene, qualunque genere di cose. Potresti avere un foglio di lavoro che contiene un elenco di dipendenti, prodotti, clienti o CD nella tua collezione di CD o stelle nel cielo notturno. Non importa davvero.

Ecco un esempio di un elenco o di un database. In questo caso è un elenco di prodotti che la nostra azienda fittizia vende:

503x210xdatabase.png.pagespeed.gp + jp + JW + pj + js + rj + RP + RW + ri + CP + md.ic.u3M88bovhQ

Di solito elenchi come questo hanno una sorta di identificatore univoco per ciascun elemento dell'elenco. In questo caso, l'identificatore univoco si trova nella colonna “Codice articolo”. Nota: affinché la funzione VLOOKUP funzioni con un database / elenco, tale elenco dovere avere una colonna contenente l'identificatore univoco (o “chiave” o “ID”) e quella colonna deve essere la prima colonna nella tabella. Il nostro database di esempio sopra soddisfa questo criterio.

La parte più difficile dell'utilizzo di VLOOKUP è capire esattamente a cosa serve. Quindi vediamo se possiamo prima chiarirlo:

VLOOKUP recupera le informazioni da un database / elenco in base a un'istanza fornita dell'identificatore univoco.

Nell'esempio sopra, inseriresti la funzione VLOOKUP in un altro foglio di calcolo con un codice articolo e ti restituirebbe la descrizione dell'articolo corrispondente, il suo prezzo o la sua disponibilità (la sua quantità “In stock”) come descritto nell'originale elenco. Quale di queste informazioni ti restituirà? Bene, devi decidere questo quando crei la formula.

Se tutto ciò di cui hai bisogno è un'informazione dal database, sarebbe molto difficile andare a costruire una formula con una funzione VLOOKUP al suo interno. In genere si utilizza questo tipo di funzionalità in un foglio di calcolo riutilizzabile, ad esempio un modello. Ogni volta che qualcuno inserisce un codice articolo valido, il sistema recupera tutte le informazioni necessarie sull'elemento corrispondente.

Facciamo un esempio di questo: An Modello di fattura che possiamo riutilizzare più volte nella nostra società fittizia.

Innanzitutto iniziamo Excel e creiamo noi stessi una fattura vuota:

fattura

Funzionerà così: la persona che utilizza il modello di fattura inserirà una serie di codici articolo nella colonna “A” e il sistema recupererà la descrizione e il prezzo di ciascun articolo dal nostro database dei prodotti. Tali informazioni verranno utilizzate per calcolare il totale della riga per ciascun articolo (presupponendo che immettiamo una quantità valida).

Allo scopo di mantenere semplice questo esempio, individueremo il database del prodotto su un foglio separato nella stessa cartella di lavoro:

selectsheet

In realtà, è più probabile che il database del prodotto si trovi in ​​una cartella di lavoro separata. Fa poca differenza con la funzione VLOOKUP, a cui non importa davvero se il database si trova sullo stesso foglio, su un foglio diverso o su una cartella di lavoro completamente diversa.

Quindi, abbiamo creato il nostro database di prodotti, che assomiglia a questo:

503x210xdatabase.png.pagespeed.gp + jp + JW + pj + js + rj + RP + RW + ri + CP + md.ic.u3M88bovhQ

Per testare la formula VLOOKUP che stiamo per scrivere, inseriamo innanzitutto un codice articolo valido nella cella A11 della nostra fattura vuota:

codice articolo

Successivamente, spostiamo la cella attiva nella cella in cui vogliamo che le informazioni recuperate dal database da VLOOKUP vengano archiviate. È interessante notare che questo è il passo che molte persone sbagliano. Per spiegare ulteriormente: Stiamo per creare una formula VLOOKUP che recupererà la descrizione che corrisponde al codice articolo nella cella A11. Dove vogliamo inserire questa descrizione quando la otteniamo? Nella cella B11, ovviamente. Ecco dove scriviamo la formula VLOOKUP: nella cella B11. Seleziona la cella B11 ora.

selectdescription

Dobbiamo individuare l'elenco di tutte le funzioni disponibili che Excel ha da offrire, in modo da poter scegliere VLOOKUP e ottenere assistenza per il completamento della formula. Questo si trova facendo prima clic su formule scheda e quindi facendo clic Inserisci funzione:

formule

Viene visualizzata una finestra che ci consente di selezionare una delle funzioni disponibili in Excel.

insertfunctionbox

Per trovare quello che stiamo cercando, potremmo digitare un termine di ricerca come “ricerca” (perché la funzione che ci interessa è un consultare funzione). Il sistema ci restituirebbe un elenco di tutte le funzioni relative alla ricerca in Excel. VLOOKUP è il secondo nell'elenco. Selezionalo con un clic ok.

findlookup

Il Argomenti di funzione appare la finestra, che ci richiede di tutti argomenti (o parametri) necessario per completare la funzione VLOOKUP. Puoi pensare a questa casella come alla funzione che ci pone le seguenti domande:

  1. Quale identificatore univoco stai cercando nel database?
  2. Dov'è il database?
  3. Quale informazione del database, associata all'identificatore univoco, desideri avere recuperato per te?

Vengono visualizzati i primi tre argomenti in grassetto, indicando che lo sono obbligatorio argomenti (la funzione VLOOKUP è incompleta senza di essi e non restituirà un valore valido). Il quarto argomento non è in grassetto, nel senso che è facoltativo:

funcarguments

Completeremo gli argomenti in ordine, dall'alto verso il basso.

Il primo argomento che dobbiamo completare è il Valore di ricerca discussione. La funzione ha bisogno di noi per dirgli dove trovare l'identificatore univoco (il codice articolo in questo caso) che dovrebbe restituire la descrizione di. Dobbiamo selezionare il codice articolo inserito in precedenza (in A11).

Fai clic sull'icona del selettore a destra del primo argomento:

funcarguments1

Quindi fare clic una volta sulla cella contenente il codice articolo (A11) e premere accedere:

selectarg1

Il valore di “A11” è inserito nel primo argomento.

Ora dobbiamo inserire un valore per Matrice di tabella discussione. In altre parole, dobbiamo dire a VLOOKUP dove trovare il database / l'elenco. Fai clic sull'icona del selettore accanto al secondo argomento:

funcarguments2

Ora trova il database / elenco e seleziona l'intero elenco – esclusa la riga di intestazione. Nel nostro esempio, il database si trova su un foglio di lavoro separato, quindi prima facciamo clic sulla scheda del foglio di lavoro:

selectsheet

Quindi selezioniamo l'intero database, esclusa la riga di intestazione:

640x284xselectarg2.png.pagespeed.gp + jp + JW + pj + js + rj + RP + RW + ri + CP + md.ic.4EDJIujZoM

… e premere accedere. L'intervallo di celle che rappresenta il database (in questo caso “Database del prodotto”! A2: D7 “) viene inserito automaticamente per noi nel secondo argomento.

Ora dobbiamo inserire il terzo argomento, Col_index_num. Usiamo questo argomento per specificare a VLOOKUP quali informazioni dal database, associate al nostro codice articolo in A11, desideriamo averci restituito. In questo esempio particolare, desideriamo avere l'articolo descrizione tornato da noi. Se guardi il foglio di lavoro del database, noterai che la colonna “Descrizione” è la secondo colonna nel database. Ciò significa che dobbiamo inserire un valore di “2” in Col_index_num scatola:

arg3

È importante notare che qui non stiamo inserendo un “2” perché la colonna “Descrizione” è nella B colonna su quel foglio di lavoro. Se il database si è avviato nella colonna K del foglio di lavoro, inseriremmo ancora un “2” in questo campo perché la colonna “Descrizione” è la seconda colonna nel set di celle che abbiamo selezionato quando si specifica “Table_array”.

Infine, dobbiamo decidere se inserire un valore nell'argomento VLOOKUP finale, range_lookup. Questo argomento richiede a vero o falso valore, o dovrebbe essere lasciato in bianco. Quando si utilizza VLOOKUP con i database (come è vero il 90% delle volte), il modo per decidere cosa inserire in questo argomento può essere considerato come segue:

Se la prima colonna del database (la colonna che contiene gli identificatori univoci) è ordinata in ordine alfabetico / numerico in ordine crescente, è possibile inserire un valore di vero in questo argomento o lasciarlo vuoto.

Se la prima colonna del database è non ordinati o ordinati in ordine decrescente, quindi tu dovere inserisci un valore di falso in questo argomento

Come è la prima colonna del nostro database non ordinati, entriamo falso in questo argomento:

Arg4

Questo è tutto! Abbiamo inserito tutte le informazioni richieste per VLOOKUP per restituire il valore di cui abbiamo bisogno. Clicca il ok pulsante e nota che la descrizione corrispondente al codice articolo “R99245” è stata correttamente inserita nella cella B11:

509x149xdescfilledin.png.pagespeed.gp + jp + JW + pj + js + rj + RP + RW + ri + CP + md.ic.oZ5mPW4wRY

La formula che è stata creata per noi si presenta così:

formula

Se inseriamo un diverso codice articolo nella cella A11, inizieremo a vedere la potenza della funzione VLOOKUP: la cella della descrizione cambia per corrispondere al nuovo codice articolo:

CHANGECODE

Possiamo eseguire una serie simile di passaggi per ottenere gli articoli prezzo restituito nella cella E11. Si noti che la nuova formula deve essere creata nella cella E11. Il risultato sarà simile al seguente:

2ndformula

… e la formula sarà simile a questa:

2ndformula

Nota che l'unica differenza tra le due formule è il terzo argomento (Col_index_num) è stato modificato da “2” a “3” (perché vogliamo che i dati vengano recuperati dalla terza colonna nel database).

Se decidessimo di acquistare 2 di questi articoli, inseriremmo un “2” nella cella D11. Inserire quindi una semplice formula nella cella F11 per ottenere il totale della linea:

= D11 * E11

… che assomiglia a questo …

linecomplete

Completamento del modello di fattura

Finora abbiamo imparato molto su VLOOKUP. In effetti, abbiamo imparato tutto ciò che impareremo in questo articolo. È importante notare che VLOOKUP può essere utilizzato in altre circostanze oltre ai database. Questo è meno comune e potrebbe essere trattato nei futuri articoli How-To Geek.

Il nostro modello di fattura non è ancora completo. Per completarlo, faremo quanto segue:

  1. Rimuoveremmo il codice dell'articolo di esempio dalla cella A11 e il “2” dalla cella D11. Ciò farà sì che le nostre formule VLOOKUP appena create visualizzino messaggi di errore:
    errori

    Possiamo rimediare con un uso oculato di Excel SE() e È VUOTO() funzioni. Cambiamo la nostra formula da questo … = VLOOKUP (A11, “Database prodotti”! A2: D7,2, FALSE)…a questo…= IF (ISBLANK (A11), “”, VLOOKUP (A11, “Database prodotti”! A2: D7,2, FALSE))

  2. Copieremo le formule nelle celle B11, E11 e F11 fino al resto delle righe degli articoli della fattura. Se lo facciamo, le formule risultanti non faranno più correttamente riferimento alla tabella del database. Potremmo risolvere questo problema modificando i riferimenti di cella per il database assoluto riferimenti di cella. In alternativa – e ancora meglio – potremmo creare un nome dell'intervallo per l'intero database dei prodotti (come “Prodotti”) e utilizzare questo nome di intervallo anziché i riferimenti di cella. La formula cambierebbe da questo … = IF (ISBLANK (A11), “”, VLOOKUP (A11, “Database prodotti”! A2: D7,2, FALSE))…a questo… = SE (ISBLANK (A11),””, VLOOKUP (A11, Prodotti, 2, FALSE))…e poi copia le formule fino al resto delle righe degli articoli della fattura.
  3. Probabilmente “bloccheremo” le celle che contengono le nostre formule (o meglio sbloccare il altro celle) e quindi proteggere il foglio di lavoro, al fine di garantire che le nostre formule accuratamente costruite non vengano sovrascritte accidentalmente quando qualcuno viene a compilare la fattura.
  4. Vorremmo salvare il file come a modello, in modo che possa essere riutilizzato da tutti nella nostra azienda

Se ci sentissimo veramente intelligente, creeremmo un database di tutti i nostri clienti in un altro foglio di lavoro, quindi utilizzeremo l'ID cliente inserito nella cella F5 per compilare automaticamente il nome e l'indirizzo del cliente nelle celle B6, B7 e B8.

i clienti

Se desideri esercitarti con VLOOKUP o semplicemente vedere il nostro modello di fattura risultante, può essere scaricato da qui.