CERCA.VERT è una delle funzioni più utili di Excel ed è anche una delle meno conosciute. In questo articolo, demistiamo VLOOKUP come esempio reale. Creeremo un utilizzo Modello di fattura per una società fittizia.
CERCA.VERT è un Excel funzione. 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 è un Banca dati funzione, il che significa 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. È 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 di un database. In questo caso è un elenco di prodotti che la nostra società fittizia vende:
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, tale elenco deve avere una colonna contenente l'identificativo 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 retrieves information from a database/list based on a supplied instance of the unique identifier.
Nell'esempio sopra, dovresti inserire la funzione CERCA.VERT in un altro foglio di lavoro 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 nell'originale elenco. Quale di queste informazioni ti passerà? Bene, devi decidere quando stai creando la formula.
Se tutto ciò di cui hai bisogno è una 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: An Modello di fattura che possiamo riutilizzare più e più volte nella nostra società fittizia.
Per prima cosa iniziamo Excel e creiamo noi stessi una fattura vuota:
Allo scopo di mantenere semplice questo esempio, individueremo il database del prodotto su un foglio separato nella stessa cartella di lavoro:
Quindi, abbiamo creato il nostro database di prodotti, che assomiglia a questo:
Abbiamo bisogno di trovare 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 sul formule scheda, quindi facendo clic Inserisci funzione:
Per trovare quello che stiamo cercando, potremmo digitare un termine di ricerca come "ricerca" (perché la funzione a cui siamo interessati è un consultare funzione). Il sistema ci restituirebbe un elenco di tutte le funzioni relative alla ricerca in Excel. VLOOKUP è il secondo nell'elenco. Seleziona un clic ok.
Il Argomenti della funzione appare la casella, richiedendoci per tutto il argomenti (o parametri) necessari per completare la funzione CERCA.VERT. Puoi pensare a questa scatola come alla funzione che ci fa 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, indicando che lo sono obbligatorio argomenti (la funzione CERCA.VERT non è completa senza di essi e non restituirà un valore valido). Il quarto argomento non è audace, il che significa che è facoltativo:
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:
Quindi fare clic una volta sulla cella contenente il codice articolo (A11) e premere accedere:
Ora dobbiamo inserire un valore per il Matrice di tabella discussione. In altre parole, dobbiamo dire a VLOOKUP dove trovare il database / elenco. Fai clic sull'icona del selettore accanto al secondo argomento:
Successivamente selezioniamo l'intero database, esclusa la riga dell'intestazione:
… e premere accedere. 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 l'oggetto descrizione tornato a noi. Se si guarda il foglio di lavoro del database, si noterà che la colonna "Descrizione" è la secondo colonna nel database. Ciò significa che dobbiamo inserire un valore di "2" nel Col_index_num scatola:
È importante notare che non stiamo inserendo un "2" qui perché la colonna "Descrizione" è nel B colonna su quel foglio di lavoro. Se il database è successo per iniziare nella colonna K del foglio di lavoro, inseriremmo ancora un "2" in questo campo perché la colonna "Descrizione" è la seconda colonna del set di celle che abbiamo selezionato quando si specificava "Tabella_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 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:
If the first column of the database (the column that contains the unique identifiers) is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true into this argument, or leave it blank.
If the first column of the database is not sorted, or it’s sorted in descending order, then you must enter a value of false into this argument
Come la prima colonna del nostro database è non ordinati, entriamo falso in questo argomento:
Questo è tutto! Abbiamo inserito tutte le informazioni necessarie per VLOOKUP per restituire il valore di cui abbiamo bisogno. Clicca il ok pulsante e si noti che la descrizione corrispondente al codice articolo "R99245" è stata inserita correttamente nella cella B11:
Se entriamo a diverso codice articolo nella cella A11, inizieremo a vedere la potenza della funzione CERCA.VERT: La cella della descrizione cambia per corrispondere al nuovo codice articolo:
Possiamo eseguire una serie di passaggi simili per ottenere l'oggetto prezzo restituito nella cella E11. Si noti che la nuova formula deve essere creata nella cella E11. Il risultato sarà simile a questo:
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 formula semplice nella cella F11 per ottenere la riga totale:
=D11*E11
… che assomiglia a questo …
Completamento del 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, faremmo quanto segue:
-
Rimuoveremmo il codice dell'oggetto campione dalla cella A11 e il "2" dalla cella D11. Ciò causerà le nostre formule VLOOKUP appena create per visualizzare i messaggi di errore:
Image Possiamo rimediare a questo con l'uso giudizioso di Excel SE() e È VUOTO() funzioni. Cambiamo la nostra formula da questo … = CERCA.VERT (A11, 'Database prodotti'! A2: D7,2, FALSE) …a questo… = IF (ISBLANK (A11), "", VLOOKUP (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 a assoluto riferimenti di cella. In alternativa - e ancora meglio - potremmo creare un nome dell'intervallo per l'intero database del prodotto (ad esempio "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 dell'articolo di fatturazione.
- Probabilmente "blocchiamo" 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 attentamente costruite non vengano sovrascritte accidentalmente quando qualcuno viene a compilare la fattura.
- Vorremmo salvare il file come modello, in modo che possa essere riutilizzato da tutti nella nostra azienda
Se ci sentissimo veramente intelligente, creiamo un database di tutti i nostri clienti in un altro foglio di lavoro e quindi utilizziamo l'ID cliente inserito nella cella F5 per inserire automaticamente il nome e l'indirizzo del cliente nelle celle B6, B7 e B8.
