1Aug
VLOOKUP è una delle funzioni più utili di Excel ed è anche una delle meno conosciute. In questo articolo, demistiamo VLOOKUP come esempio reale. Creeremo un modello di fattura utilizzabile per un'azienda fittizia.
VLOOKUP è una funzione di Excel .In questo articolo si presuppone che il lettore abbia già una conoscenza approfondita delle funzioni di Excel e possa utilizzare le funzioni di base come SUM, MEDIA e OGGI.Nel suo uso più comune, VLOOKUP è una funzione del database , il che significa che funziona con le tabelle del database - o più semplicemente, elenca di cose in un foglio di lavoro Excel. Che tipo di cose? Bene, qualsiasi tipo di cosa .È possibile che un foglio di lavoro contenga un elenco di dipendenti, prodotti o clienti o CD nella raccolta di CD o stelle nel cielo notturno. Non importa.
Ecco un esempio di un elenco o database. In questo caso si tratta di un elenco di prodotti venduti dalla nostra società fittizia:
Solitamente liste come questa hanno una sorta di identificatore univoco per ciascun elemento nell'elenco. In questo caso, l'identificatore univoco si trova nella colonna "Codice articolo".Nota: Affinché la funzione CERCA.VERT sia in grado di funzionare con un database / elenco, l'elenco
deve 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'uso di VLOOKUP è capire esattamente a cosa serve. Quindi vediamo se possiamo chiarire prima:
VLOOKUP recupera le informazioni da un database / elenco basato su un'istanza fornita dell'identificatore univoco.
Nell'esempio sopra, dovresti inserire la funzione CERCA.VERT 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 magazzino") come descritto nel tuolista originaleQuale di queste informazioni ti passerà?Bene, devi decidere quando stai creando la formula.
Se tutto ciò che serve è un'informazione dal database, sarebbe molto difficile andare a costruire una formula con una funzione CERCA.VUOTO.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.
Creiamo un esempio di questo: un modello di fattura che riusciamo a riutilizzare più e più volte nella nostra società fittizia.
Iniziamo con Excel e creiamo una fattura vuota:
Ecco come funzionerà: la persona che utilizza il modello di fattura inserirà una serie di codici articolo nella colonna "A" e il sistema recupererà le voci di ciascun elemento.descrizione e prezzo dal nostro database di prodotti. Queste informazioni verranno utilizzate per calcolare il totale della linea per ogni articolo( supponendo di inserire una quantità valida).
Allo scopo di mantenere semplice questo esempio, localizzeremo il database del prodotto su un foglio separato nella stessa cartella di lavoro:
In realtà, è più probabile che il database del prodotto si trovi in una cartella di lavoro separata. Fa poca differenza per la funzione CERCA.VERT, che non interessa se il database si trova sullo stesso foglio, un foglio diverso o una cartella di lavoro completamente diversa.
Quindi, abbiamo creato il nostro database di prodotti, che assomiglia a questo:
Per testare la formula VLOOKUP che stiamo per scrivere, dobbiamo prima inserire un codice articolo valido nella cella A11 della nostra fattura vuota:
Successivamente,sposta la cella attiva nella cella in cui vogliamo che le informazioni recuperate dal database 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 mettere questa descrizione quando la otteniamo? Nella cella B11, ovviamente. Ecco dove scriviamo la formula VLOOKUP: nella cella B11.Seleziona la cella B11 ora.
Abbiamo bisogno di individuare l'elenco di tutte le funzioni disponibili che Excel ha da offrire, in modo che possiamo scegliere VLOOKUP e ottenere assistenza nel completare la formula. Questo si trova facendo prima clic sulla scheda Formule , quindi facendo clic su Inserisci funzione :
Appare una finestra che ci consente di selezionare una delle funzioni disponibili in Excel.
Per trovare quello che stiamo cercando, potremmo digitare un termine di ricerca come "ricerca"( perché la funzione che ci interessa è una funzione di ricerca ).Il sistema ci restituirebbe un elenco di tutte le funzioni relative alla ricerca in Excel. VLOOKUP è il secondo nell'elenco. Selezionalo e fai clic su OK .
Argomenti della funzione Viene visualizzata la finestra , che richiede tutti gli argomenti ( o parametri ) necessari per completare la funzione CERCA.VERT.Puoi pensare a questa casella come alla funzione che ci pone le seguenti domande:
- Quale identificatore univoco stai cercando nel database?
- Dov'è il database?
- Quale informazione dal database, associata all'identificativo univoco, desideri aver recuperato per te?
I primi tre argomenti sono mostrati in grassetto , a indicare che sono obbligatori ( la funzione CERCA.VERT non è completa senza di essi e non restituirà un valore valido).Il quarto argomento non è in grassetto, il che significa che è opzionale:
. Completeremo gli argomenti in ordine, dall'alto verso il basso.
Il primo argomento che dobbiamo completare è l'argomento Lookup_value. La funzione ha bisogno di dire 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).
Fare clic sull'icona del selettore a destra del primo argomento:
Quindi fare clic una volta sulla cella contenente il codice articolo( A11) e premere Immettere :
Il valore di "A11" viene inserito nel primo argomento.
Ora è necessario immettere un valore per l'argomento Tabella_array .In altre parole, dobbiamo dire a VLOOKUP dove trovare il database / elenco. Fare clic sull'icona del selettore accanto al secondo argomento:
Ora individuare il database / elenco e selezionare l'intero elenco - che non include la riga di intestazione .Nel nostro esempio, il database si trova su un foglio di lavoro separato, quindi facciamo prima clic sulla scheda del foglio di lavoro:
Successivamente selezioniamo l'intero database, esclusa la riga dell'intestazione:
. .. e premiamo Inserisci .L'intervallo di celle che rappresenta il database( in questo caso "Database 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 su VLOOKUP quale pezzo di informazione dal database, associato al nostro codice articolo in A11, desideriamo esserci restituiti. In questo particolare esempio, desideriamo avere la descrizione dell'articolo restituita. Se si consulta il foglio di lavoro del database, si noterà che la colonna "Descrizione" è la seconda colonna nel database. Ciò significa che dobbiamo inserire un valore di "2" nella casella Col_index_num:
È importante notare che non stiamo inserendo un "2" qui perché la colonna "Descrizione" si trova nella colonna B suquel foglio di lavoro. Se il database si avviava nella colonna K del foglio di lavoro, dovremmo comunque inserire un "2" in questo campo perché la colonna "Descrizione" è la seconda colonna in un insieme di celle che abbiamo selezionato quando si specifica "Tabella_array".
Infine, dobbiamo decidere se inserire un valore nell'argomento VLOOKUP finale, Range_lookup .Questo argomento richiede un vero o falso valore o dovrebbe essere lasciato vuoto. Quando si utilizza VLOOKUP con i database( come è vero il 90% delle volte), il modo per decidere cosa inserire in questo argomento può essere pensato come segue:
Se la prima colonna del database( la colonna che contiene gli identificatori univoci)è ordinato alfabeticamente / numericamente in ordine ascendente, quindi è possibile immettere un valore di true in questo argomento, o lasciarlo in bianco.
Se la prima colonna del database è non ordinata, o è ordinata in ordine decrescente, allora deve immettere un valore falso in questo argomento
Poiché la prima colonna del nostro database è non ordinata,inseriamo falso in questo argomento:
Questo è tutto! Abbiamo inserito tutte le informazioni necessarie per VLOOKUP per restituire il valore di cui abbiamo bisogno. Fare clic sul pulsante OK e notare che la descrizione corrispondente al codice articolo "R99245" è stata inserita correttamente nella cella B11:
La formula che è stata creata per noi assomiglia a questa:
Se si inserisce un codice articolo diverso nella cellaA11, inizieremo a vedere la potenza della funzione CERCA.VERT: La cella della descrizione cambia in modo che corrisponda al nuovo codice articolo:
Possiamo eseguire una serie simile di passaggi per ottenere il prezzo dell'articolo restituito nella cella E11.Si noti che la nuova formula deve essere creata nella cella E11.Il risultato sarà simile a questo:
. .. e la formula sarà simile a questa:
Si noti che l'unica differenza tra le due formule è il terzo argomento( Col_index_num ) è cambiato da un "2" a un "3"( perchévogliamo i dati recuperati dalla 3a colonna nel database).
Se decidessimo di acquistare 2 di questi articoli, inseriremmo un "2" nella cella D11.Inseriremo quindi una semplice formula nella cella F11 per ottenere la riga totale:
= D11 * E11
. .. che assomiglia a questo. ..
Completando il modello di fattura
Abbiamo imparato molto su VLOOKUP finora. In realtà, 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 coperto in futuri articoli How-To Geek.
Il nostro modello di fattura non è ancora completo. Per completarlo, faremo quanto segue:
- Rimuoveremo il codice dell'oggetto campione dalla cella A11 e il "2" dalla cella D11.Ciò farà sì che le nostre formule VLOOKUP create di recente visualizzino i messaggi di errore:
Possiamo rimediare a questo mediante l'uso giudizioso delle funzioni IF() e ISBLANK() di Excel. Cambiamo la nostra formula da questo. .. = VLOOKUP( A11, 'Database prodotti'! A2: D7,2, FALSE) . .. a questo. .. = IF( ISBLANK( A11), "", CERCA.VERT( A11, 'Database prodotti'! A2: D7,2, FALSE))
- Copieremo le formule nelle celle B11, E11 e F11 fino al resto delle righe dell'articolo della fattura. Notare che se facciamo ciò, le formule risultanti non faranno più correttamente riferimento alla tabella del database. Potremmo risolvere questo cambiando i riferimenti di cella per il database ai riferimenti di cella assoluti .In alternativa - e ancora meglio - potremmo creare un nome di intervallo per l'intero database del prodotto( come "Prodotti") e utilizzare questo nome di intervallo invece dei riferimenti di cella. La formula cambierebbe da questo. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, 'Database prodotto'! A2: D7,2, FALSE)) . .. a questo. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, Prodotti, 2, FALSE)) . .. e quindi copiano le formule fino al resto delle righe dell'elemento di fattura.
- Probabilmente "blocchiamo" le celle che contengono le nostre formule( o piuttosto sblocchiamo le altre celle ) e quindi proteggiamo il foglio di lavoro, per assicurarci che le nostre formule attentamente costruite non vengano sovrascritte accidentalmente quando qualcuno viene a riempirenella fattura
- Salveremo il file come modello , in modo che possa essere riutilizzato da tutti nella nostra azienda
Se sentissimo davvero intelligente, creiamo un database di tutti i nostri clienti in un altro foglio di lavoro, e quindi usiamo ilID cliente inserito nella cella F5 per inserire automaticamente il nome e l'indirizzo del cliente nelle celle B6, B7 e B8.
Se desideri esercitarti con VLOOKUP, o semplicemente vedere il modello di fattura risultante, può essere scaricato da qui.