CERCA.VERT in Excel, parte 2 Utilizzo di VLOOKUP senza un database
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 CERCA.VERT 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 rendiamo più complesso, fino a quando l'unica soluzione razionale al problema è l'utilizzo di CERCA.VERT. 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:
Per utilizzare questo foglio di lavoro, il venditore inserisce le cifre di vendita nella cella B1 e la formula nella cella B2 calcola la tariffa di commissione corretta che è autorizzata a ricevere, che viene utilizzata nella cella B3 per calcolare la commissione totale dovuta al venditore (che è una semplice moltiplicazione di B1 e B2).
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 quella sopra la soglia. Questa formula utilizza la funzione di Excel chiamata SE. Per quei lettori che non hanno familiarità con IF, funziona così:
SE(condizione, valore se vero, valore se falso)
Dove il condizione è un'espressione che valuta entrambi vero o falso. Nell'esempio sopra, il condizione è l'espressione B1
Come puoi vedere, l'utilizzo di un totale di 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:
Quindi il nostro foglio di calcolo sta funzionando.
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 a una commissione del 60%?
Ora la formula nella cella B2, mentre corretta, è diventata praticamente illeggibile. Nessuno dovrebbe scrivere formule in cui le funzioni sono nidificate a quattro livelli di profondità! Sicuramente ci deve essere un modo più semplice?
C'è certamente. CERCA CERCA in soccorso!
Ridefiniamo un po 'il foglio di lavoro. Manterremo 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 un fatturato di $ 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. Compiliamo gli argomenti (parametri) uno per uno, iniziando dal 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 evidenziare 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:
Se sperimentiamo con pochi importi totali di vendita diversi, possiamo accertarci che la formula funzioni.
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:
quando range_lookup è impostato per VERO (o omesso), VLOOKUP apparirà nella colonna 1 e corrisponderà il valore più alto che non è maggiore di il Valore di ricerca parametro.
È anche importante notare che questo sistema funziona, 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.
