In un recente articolo, abbiamo introdotto la funzione di Excel chiamata VLOOKUP e ha spiegato come potrebbe essere utilizzato per recuperare informazioni da un database in una cella in un foglio di lavoro locale. In quell'articolo abbiamo menzionato che c'erano due usi per VLOOKUP, e solo uno di questi si occupava di interrogare i database. In questo articolo, il secondo e il finale della serie VLOOKUP, esaminiamo questo altro uso meno noto per la funzione CERCA.VERT.
Se non lo hai già fatto, leggi il primo articolo di VLOOKUP - questo articolo assumerà che molti dei concetti spiegati in quell'articolo siano già noti al lettore.
Quando si lavora con i database, VLOOKUP riceve un "identificativo univoco" che serve a identificare quale record di dati desideriamo trovare nel database (ad esempio un codice prodotto o un ID cliente). Questo identificatore univoco dovere esistono nel database, altrimenti VLOOKUP ci restituisce un errore. In questo articolo, esamineremo un modo di utilizzare VLOOKUP in cui l'identificatore non deve necessariamente esistere nel database. È quasi come se VLOOKUP potesse adottare un approccio "abbastanza vicino è abbastanza buono" per restituire i dati che stiamo cercando. In determinate circostanze, questo è Esattamente ciò che ci serve.
Illustreremo questo articolo con un esempio del mondo reale: quello di calcolare le commissioni generate su un insieme di cifre di vendita. Iniziamo con uno scenario molto semplice, e quindi progressivamente lo renderemo più complesso, fino a quando l'unica soluzione razionale al problema è l'utilizzo di VLOOKUP. Lo scenario iniziale della nostra società fittizia funziona così: se un venditore crea più di $ 30.000 di vendite in un determinato anno, la commissione che guadagna su quelle vendite è del 30%. Altrimenti la loro commissione è solo del 20%. Finora questo è un foglio di lavoro piuttosto semplice:
La cella B2 contiene l'unica parte interessante di questo foglio di lavoro, la formula per decidere quale tasso di commissione utilizzare: quello sotto la soglia di $ 30.000 o l'una sopra la soglia. Questa formula utilizza la funzione di Excel chiamata SE. Per quei lettori che non hanno familiarità con IF, funziona in questo modo:
IF(condition,value if true,value if false)
Dove il condizione è un'espressione che valuta entrambi vero o falso. Nell'esempio sopra, il condizione è l'espressione B1, che può essere letto come "È B1 inferiore a B5?" o, in altre parole, "Le vendite totali sono inferiori alla soglia". Se la risposta a questa domanda è "sì" (vero), allora usiamo il valore se vero parametro della funzione, cioè B6 in questo caso - il tasso di commissione se il totale delle vendite è stato sotto la soglia. Se la risposta alla domanda è "no" (falso), allora usiamo il valore se falso parametro della funzione, cioè B7 in questo caso - il tasso di commissione se il totale delle vendite è stato sopra la soglia.
Come puoi vedere, l'utilizzo di un totale delle vendite di $ 20.000 ci dà un tasso di commissione del 20% nella cella B2. Se inseriamo un valore di $ 40.000, otteniamo un diverso tasso di commissione:
Rendiamolo più complesso. Introduciamo una seconda soglia: se il venditore guadagna più di $ 40.000, il loro tasso di commissione aumenta al 40%:
Abbastanza facile da capire nel mondo reale, ma nella cella B2 la nostra formula diventa sempre più complessa. Se osservi attentamente la formula, vedrai che il terzo parametro della funzione IF originale (il valore se falso) ora è un'intera funzione IF a sé stante. Questo è chiamato a funzione annidata (una funzione all'interno di una funzione). È perfettamente valido in Excel (funziona anche!), Ma è più difficile da leggere e capire.
Non entreremo nel merito di come e perché funzioni, né esamineremo le sfumature delle funzioni annidate. Questo è un tutorial su CERCA.VERT, non su Excel in generale.
Comunque, peggiora! Che dire quando decidiamo che se guadagnano più di $ 50.000 hanno diritto a una commissione del 50% e se guadagnano più di $ 60.000 hanno diritto al 60% di provvigione?
C'è certamente. CERCA CERCA in soccorso!
Ridisegniamo un po 'il foglio di lavoro. Conserveremo tutte le stesse cifre, ma organizzeremo in un modo nuovo, un altro di tabella modo:
Prenditi un momento e verifica di persona che il nuovo Tabella dei tassi funziona esattamente come la serie di soglie sopra.
Concettualmente, ciò che stiamo per fare è utilizzare VLOOKUP per cercare il totale delle vendite del venditore (da B1) nella tabella dei tassi e restituire a noi il corrispondente tasso di commissione. Si noti che il venditore potrebbe aver effettivamente creato vendite che lo sono non uno dei cinque valori nella tabella dei tassi ($ 0, $ 30.000, $ 40.000, $ 50.000 o $ 60.000). Potrebbero aver creato vendite per $ 34.988. È importante notare che $ 34,988 lo fa non compaiono nella tabella dei tassi. Vediamo se VLOOKUP può risolvere il nostro problema comunque …
Selezioniamo la cella B2 (la posizione in cui vogliamo inserire la nostra formula), quindi inseriamo la funzione CERCA.VERT dal formule tab:
Il Argomenti della funzione appare la finestra per CERCA.VERT. Riempiamo gli argomenti (parametri) uno per uno, iniziando con Valore di ricerca, che è, in questo caso, il totale delle vendite dalla cella B1. Mettiamo il cursore nel Valore di ricerca campo e quindi fare clic una volta sulla cella B1:
Successivamente, dobbiamo specificare su CERCA la tabella in cui cercare questi dati. In questo esempio, è la tabella dei tassi, ovviamente. Mettiamo il cursore nel Matrice di tabella campo, quindi evidenzia l'intera tabella dei tassi - escludendo le intestazioni:
Successivamente dobbiamo specificare quale colonna della tabella contiene le informazioni che vogliamo che la nostra formula ci restituisca. In questo caso vogliamo il tasso di commissione, che si trova nella seconda colonna della tabella, quindi entriamo quindi a 2 nel Col_index_num campo:
Finalmente inseriamo un valore nel range_lookup campo.
Importante: è l'uso di questo campo che differenzia i due modi di utilizzare VLOOKUP. Per utilizzare VLOOKUP con un database, questo parametro finale, range_lookup, deve sempre essere impostato su FALSE, ma con questo altro uso di CERCA.VERT, dobbiamo lasciarlo vuoto o inserire un valore di VERO. Quando si utilizza VLOOKUP, è fondamentale effettuare la scelta corretta per questo parametro finale.
Per essere espliciti, inseriremo un valore di vero nel range_lookup campo. Sarebbe anche opportuno lasciarlo vuoto, poiché questo è il valore predefinito:
Abbiamo completato tutti i parametri. Ora facciamo clic sul ok pulsante, e Excel crea per noi la nostra formula VLOOKUP:
Conclusione
Nella versione "database" di VLOOKUP, dove il range_lookup il parametro è FALSE, il valore passato nel primo parametro (Valore di ricerca) dovere essere presente nel database In altre parole, stiamo cercando una corrispondenza esatta.
Ma in questo altro uso di VLOOKUP, non stiamo necessariamente cercando una corrispondenza esatta. In questo caso, "abbastanza vicino è abbastanza buono". Ma cosa intendiamo per "abbastanza vicino"? Facciamo un esempio: quando cerchiamo un tasso di commissione su un totale di vendite di $ 34.988, la nostra formula VLOOKUP ci restituirà un valore del 30%, che è la risposta corretta. Perché ha scelto la riga nella tabella contenente il 30%? Cosa, in effetti, significa "abbastanza vicino" in questo caso? Precisiamo:
When Range_lookup is set to TRUE (or omitted), VLOOKUP will look in column 1 and match the highest value that is not greater than the Lookup_value parameter.
È anche importante notare che per far funzionare questo sistema, la tabella deve essere ordinata in ordine crescente sulla colonna 1!
Se desideri esercitarti con VLOOKUP, il file di esempio illustrato in questo articolo può essere scaricato da qui.
