1Aug
In un recente articolo, abbiamo introdotto la funzione Excel denominata VLOOKUP e 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 identificativo univoco deve esistere 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 di ciò di cui abbiamo bisogno.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:
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 calcolarela 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 quello sopra la soglia. Questa formula utilizza la funzione Excel denominata IF .Per i lettori che non hanno familiarità con IF, funziona in questo modo:
IF( condizione , valore se true, valore se false )
Dove la condizione è un'espressione che valuta true o false .Nell'esempio sopra, la condizione è l'espressione B1 & lt; B5 , che può essere letta 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 il vero parametro della funzione, vale a dire B6 in questo caso, il tasso di commissione se il totale delle vendite era sotto la soglia. Se la risposta alla domanda è "no"( falso), allora usiamo il valore se falso parametro della funzione, vale a dire B7 in questo caso - il tasso di commissione se il totale delle vendite era sopra la soglia.
Come potete 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.
Facciamolo diventare 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 osservate attentamente la formula, vedrete che il terzo parametro della funzione IF originale( il valore se falso ) è ora un'intera funzione IF a sé stante. Questa funzione è chiamata nidificata ( 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.
Ad ogni modo, 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?
Ora la formula nella cella B2, se 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?
Certamente lo è.CERCA CERCA in soccorso!
Ridefiniamo un po 'il foglio di lavoro. Conserveremo tutti gli stessi dati, ma organizzeremo in un modo nuovo, un altro tabulare modo:
Prenditi un momento e verifica tu stesso che il nuovo Rate Table funzioni 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 tasso di commissione corrispondente. Si noti che il venditore potrebbe aver effettivamente creato vendite che 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 fa non visualizzati 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 dalla scheda Formule :
La funzione Argomenti box per VLOOKUP.Riempiamo gli argomenti( parametri) uno per uno, iniziando con Lookup_value , che è, in questo caso, il totale delle vendite dalla cella B1.Posizioniamo il cursore nel campo Lookup_value e poi facciamo clic una volta sulla cella B1:
Successivamente dobbiamo specificare a CERCA.VERT la tabella in cui cercare questi dati. In questo esempio, è la tabella dei tassi, ovviamente. Posizioniamo il cursore nel campo della tabella e quindi evidenziamo 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 la percentuale di commissione, che si trova nella seconda colonna della tabella, quindi inseriamo un 2 nel campo di Col_index_num:
Infine inseriamo un valore nel campo Range_lookup .
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, è necessario lasciarlo vuoto o immettere un valore di TRUE .Quando si utilizza VLOOKUP, è fondamentale effettuare la scelta corretta per questo parametro finale.
Per essere esplicito, inseriremo un valore true nel campo Range_lookup .Sarebbe anche opportuno lasciarlo vuoto, poiché questo è il valore predefinito:
Abbiamo completato tutti i parametri. Ora facciamo clic sul pulsante OK 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 Range_lookup parametro è FALSE , il valore passato nel primo parametro( Lookup_value ) deve 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? Precisare:
Quando Range_lookup è impostato su TRUE ( o omesso), VLOOKUP cercherà nella colonna 1 e corrisponderà a il valore più alto non superiore a il parametro Lookup_value.
È anche importante notare che per far funzionare questo sistema, la tabella deve essere ordinata in ordine ascendente sulla colonna 1 !
Se desideri esercitarti con VLOOKUP, il file di esempio illustrato in questo articolo può essere scaricato da qui.
