CERCA.VERT è una delle funzioni più utili di Excel ed è anche una delle meno comprese. In questo articolo, demistiamo VLOOKUP come esempio reale. Creeremo un modello di fattura utilizzabile per una società fittizia.

CERCA.VERT è 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 database , il che significa che funziona con le tabelle del database - o più semplicemente liste di cose in un foglio di lavoro Excel. Che tipo di cose? Bene, qualsiasi tipo . È 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 ha molta importanza.

Ecco un esempio di un elenco o di un 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, quella lista 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'oggetto corrispondente, il suo prezzo o la sua disponibilità (la sua quantità "In magazzino") come descritto nel tuo elenco originale. Quale di queste informazioni ti passerà? Bene, puoi decidere quando stai creando la formula.

Se tutto ciò che ti serve è una informazione dal database, sarebbe un grosso problema per costruire una formula con la funzione CERCA.VERT in esso. 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 oggetto valido, il sistema recupera tutte le informazioni necessarie sull'elemento corrispondente.

Creiamo un esempio di questo: un modello di fattura che possiamo riutilizzare più e più volte nel nostro 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à la descrizione e il prezzo di ciascun articolo dal nostro database di prodotti. Queste informazioni verranno utilizzate per calcolare il totale delle righe per ogni articolo (supponendo di inserire una quantità valida).

Per mantenere questo esempio semplice, localizzeremo il database del prodotto su un foglio separato nella stessa cartella di lavoro:

In realtà, è più probabile che il database del prodotto sia collocato in una cartella di lavoro separata. Fa poca differenza alla funzione CERCA.VERT, che non interessa se il database si trova sullo stesso foglio, un foglio diverso o una cartella di lavoro completamente diversa.

Così, abbiamo creato il nostro database di prodotti, che sembra In questo modo:

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

Quindi, spostiamo la cella attiva nella cella in cui vogliamo che le informazioni recuperate dal database VLOOKUP siano 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 :

Viene visualizzata una finestra che 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 a cui siamo interessati è una funzione ricerca ). Il sistema ci restituirebbe un elenco di tutte le funzioni relative alla ricerca in Excel. VLOOKUP è il secondo nell'elenco. Selezionalo un clic OK .

Appare la casella Argomenti della funzione , richiedendoci tutti gli argomenti (o parametri ) necessari per completare la funzione CERCA.VERT. Puoi pensare a questa casella come alla funzione che ci fa le seguenti domande:

1. Quale identificatore univoco stai cercando nel database?
2. Dov'è il database?
3. Quale informazione dal database, associata a l'identificatore univoco, desideri aver recuperato per te?

I primi tre argomenti vengono visualizzati in grassetto , a indicare che sono obbligatori argomenti (la funzione CERCA.VERT è incompleta senza loro e non restituiranno un valore valido). Il quarto argomento non è in grassetto, il che significa che è facoltativo:

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'identificativo 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 oggetto (A11), quindi premere Invio :

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:

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

... e premiamo Invio . L'intervallo di celle che rappresenta il database (in questo caso "Database prodotto! A2: D7") viene inserito automaticamente 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 guarda il foglio di lavoro del database, si noterà che la colonna "Descrizione" è la colonna secondo 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" è in la colonna B su quel 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 nel set di celle che abbiamo selezionato quando si specifica il " Table_array ".

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

Se la prima colonna del database (la colonna che contiene l'unico identificatori) è ordinato alfabeticamente / numericamente in ordine ascendente, quindi è possibile immettere un valore di true in questo argomento, o lasciarlo vuoto.

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 ordinati, inseriamo falso in questo argomento:

Questo è esso! 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 è simile alla seguente:

Se entriamo un codice articolo diverso nella cella A11, inizieremo a vedere la potenza della funzione CERCA.VUOTO: la cella della descrizione cambia per corrispondere al nuovo codice articolo:

Possiamo eseguire una serie di passaggi simile per ottenere il prezzo dell'articolo è ritornato 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 elementi, inseriremmo un" 2 "nella cella D11. Inseriremo quindi una formula semplice nella cella F11 per ottenere la riga totale:

= D11 * E11

... che è simile 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, faremo quanto segue:

1. 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 di ISBLANK () di Excel. Modifichiamo la nostra formula da questo ... = CERCA.VERT (A11, 'Database prodotti'! A2: D7,2, FALSE) ... a questo ... = IF (ISBLANK (A11), ", CERCA.VERTENZA (A11, 'Database prodotti'! A2: D7,2, FALSE))
2. 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 problema cambiando i riferimenti di cella per il database in riferimenti assoluti celle. In alternativa - e ancora meglio - potremmo creare un nome dell'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), ", CERCA.VERT (A11, 'Database prodotto'! A2: D7,2, FALSE)) ... a questo ... = IF (ISBLANK (A11), ", CERCA.VERT (A11, Prodotti, 2, FALSE)) ... e quindi copia le formule fino al resto delle righe dell'articolo di fatturazione.
3. probabilmente "bloccheranno" le celle che contengono le nostre formule (o meglio sbloccano le altre celle) e quindi proteggono il foglio di lavoro, al fine di garantire che le nostre formule attentamente costruite non siano accidentalmente sovrascritto quando qualcuno viene a compilare la fattura.
4. Vorremmo salvare il file come un modello , in modo che potesse essere riutilizzato da tutti nella nostra azienda

Se ci sentivamo davvero intelligente, creiamo un database di tutti i nostri clienti in un altro foglio di lavoro, 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.

Se vorrebbe esercitarsi con VLOOKUP, o semplicemente vedere la nostra fattura T risultante emplate, può essere scaricato da qui.

Come eliminare automaticamente i file in Windows

In precedenza ho scritto su un programma chiamato DropIt che sposta o copia automaticamente i file quando vengono visualizzati nuovi file all'interno di una cartella. Ciò può essere utile, ad esempio, se si dispone di spazio limitato su un disco rigido locale e si desidera spostare tutti i download su un dispositivo di archiviazione esterno.S

(How-to)

Perché è necessario installare gli aggiornamenti di Windows automaticamente

Ancora una volta sono state rilasciate patch di sicurezza urgenti per Windows, e questa volta i problemi risolti sono "potenzialmente catastrofici" con lo stack di crittografia. Questo è il motivo per cui dovresti avere gli aggiornamenti di installazione di Windows automaticamente. È possibile che tu entri in azione ogni volta che vedi la notifica di sicurezza, ma molte persone non lo fanno.

(how-to)