Contenuti
Di recente abbiamo dedicato un articolo a una delle più utili funzioni di Excel chiamata VPR e ha mostrato come può essere utilizzato per estrarre le informazioni richieste da un database in una cella del foglio di lavoro. Abbiamo anche menzionato che ci sono due casi d'uso per la funzione VPR e solo uno di loro si occupa di query di database. In questo articolo imparerai un altro modo meno noto per utilizzare la funzione VPR in Excel.
Se non l'hai ancora fatto, assicurati di leggere l'ultimo articolo sulla funzione VPR, perché tutte le informazioni di seguito presuppongono che tu abbia già familiarità con i principi descritti nel primo articolo.
Quando si lavora con database, funzioni VPR viene passato un identificatore univoco, che viene utilizzato per identificare le informazioni che vogliamo trovare (ad esempio, un codice prodotto o un numero di identificazione del cliente). Questo codice univoco deve essere presente nel database, altrimenti VPR segnalerà un errore. In questo articolo, esamineremo questo modo di utilizzare la funzione VPRquando l'ID non esiste affatto nel database. Come se la funzione VPR è passato alla modalità approssimativa e sceglie quali dati fornirci quando vogliamo trovare qualcosa. In determinate circostanze, questo è esattamente ciò che è necessario.
Un esempio dalla vita. Impostiamo il compito
Illustriamo questo articolo con un esempio di vita reale: calcolare le commissioni in base a un'ampia gamma di parametri di vendita. Inizieremo con un'opzione molto semplice e poi la complicheremo gradualmente fino a quando l'unica soluzione razionale al problema sarà usare la funzione VPR. Lo scenario iniziale per il nostro compito fittizio è il seguente: se un venditore guadagna più di $ 30000 in un anno, la sua commissione è del 30%. In caso contrario, la commissione è solo del 20%. Mettiamolo sotto forma di una tabella:
Il venditore inserisce i propri dati di vendita nella cella B1 e la formula nella cella B2 determina il tasso di commissione corretto che il venditore può aspettarsi. A sua volta, il tasso risultante viene utilizzato nella cella B3 per calcolare la commissione totale che il venditore dovrebbe ricevere (semplicemente moltiplicando le celle B1 e B2).
La parte più interessante della tabella è contenuta nella cella B2: questa è la formula per determinare il tasso di commissione. Questa formula contiene una funzione di Excel chiamata IF (SE). Per quei lettori che non hanno familiarità con questa funzione, spiegherò come funziona:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Condizione è un argomento di funzione che assume il valore di entrambi VERO CODICE (VERO), o FALSO (FALSO). Nell'esempio sopra, l'espressione B1
È vero che B1 è minore di B5?
Oppure puoi dirlo diversamente:
È vero che l'importo totale delle vendite dell'anno è inferiore al valore soglia?
Se rispondiamo a questa domanda SI (TRUE), quindi la funzione ritorna valore se vero (valore se VERO). Nel nostro caso, questo sarà il valore della cella B6, ovvero il tasso di commissione quando le vendite totali sono inferiori alla soglia. Se rispondiamo alla domanda NO (FALSO) quindi ritorna valore se falso (valore se FALSO). Nel nostro caso, questo è il valore della cella B7, ovvero il tasso di commissione quando le vendite totali sono superiori alla soglia.
Come puoi vedere, se prendiamo le vendite totali di $ 20000, otteniamo una commissione del 2% nella cella B20. Se inseriamo un valore di $ 40000, il tasso di commissione cambierà del 30%:
Ecco come funziona la nostra tavola.
Complichiamo il compito
Rendiamo le cose un po' più difficili. Fissiamo un'altra soglia: se il venditore guadagna più di $ 40000, la commissione aumenta al 40%:
Tutto sembra essere semplice e chiaro, ma la nostra formula nella cella B2 diventa notevolmente più complicata. Se guardi da vicino la formula, vedrai che il terzo argomento della funzione IF (IF) si è trasformato in un'altra funzione a tutti gli effetti IF (SE). Questa costruzione è chiamata annidamento di funzioni l'una nell'altra. Excel consente felicemente questi costrutti e funzionano anche, ma sono molto più difficili da leggere e capire.
Non approfondiremo i dettagli tecnici, perché e come funziona, e non entreremo nelle sfumature della scrittura di funzioni nidificate. Dopotutto, questo è un articolo dedicato alla funzione VPR, non una guida completa a Excel.
In ogni caso, la formula si complica! E se introducessimo un'altra opzione per una commissione del 50% per quei venditori che realizzano più di $ 50000 di vendite. E se qualcuno ha venduto più di $ 60000, pagherà una commissione del 60%?
Ora la formula nella cella B2, anche se è stata scritta senza errori, è diventata del tutto illeggibile. Penso che siano pochi quelli che vogliono usare formule con 4 livelli di annidamento nei loro progetti. Ci deve essere un modo più semplice?!
E c'è un modo! La funzione ci aiuterà VPR.
Applichiamo la funzione CERCA.VERT per risolvere il problema
Cambiamo un po' il design del nostro tavolo. Manterremo tutti gli stessi campi e dati, ma li disporremo in un modo nuovo e più compatto:
Prenditi un momento e assicurati che il nuovo tavolo Tariffario include gli stessi dati della precedente tabella delle soglie.
L'idea principale è usare la funzione VPR per determinare la tariffa tariffaria desiderata secondo la tabella Tariffario a seconda del volume delle vendite. Si prega di notare che il venditore può vendere merce per un importo che non è uguale a una delle cinque soglie nella tabella. Ad esempio, potrebbe vendere per $ 34988, ma non esiste un tale importo. Vediamo come funziona VPR può affrontare una situazione del genere.
Inserimento di una funzione CERCA.VERT
Seleziona la cella B2 (dove vogliamo inserire la nostra formula) e trova VLOOKUP (VLOOKUP) nella Libreria delle funzioni di Excel: Formule (formule) > Libreria delle funzioni (Libreria delle funzioni) > Ricerca e riferimento (Riferimenti e array).
Viene visualizzata una finestra di dialogo Argomenti della funzione (Argomenti di funzione). Compiliamo i valori degli argomenti uno per uno, a cominciare da Valore di ricerca (Valore di ricerca). In questo esempio, questo è l'importo totale delle vendite dalla cella B1. Posiziona il cursore nel campo Valore di ricerca (Valore_ricerca) e selezionare la cella B1.
Successivamente, è necessario specificare le funzioni VPRdove cercare i dati Nel nostro esempio, questa è una tabella Tariffario. Posiziona il cursore nel campo Matrice di tabella (Tabella) e selezionare l'intera tabella Tariffariofatta eccezione per le intestazioni.
Successivamente, dobbiamo specificare da quale colonna estrarre i dati utilizzando la nostra formula. Siamo interessati al tasso di commissione, che si trova nella seconda colonna della tabella. Pertanto, per l'argomento Col_indice_num (Column_number) inserire il valore 2.
E infine, introduciamo l'ultimo argomento: Ricerca_intervallo (Ricerca_intervallo).
Importante: è l'uso di questo argomento che fa la differenza tra i due modi di applicare la funzione VPR. Quando si lavora con i database, l'argomento Ricerca_intervallo (range_lookup) deve sempre avere un valore FALSO (FALSE) per cercare una corrispondenza esatta. Nel nostro uso della funzione VPR, dobbiamo lasciare vuoto questo campo o inserire un valore VERO CODICE (VERO). È estremamente importante scegliere correttamente questa opzione.
Per renderlo più chiaro, introduciamo VERO CODICE (VERO) nel campo Ricerca_intervallo (Ricerca_intervallo). Anche se, se lasci il campo vuoto, questo non sarà un errore, poiché VERO CODICE è il suo valore predefinito:
Abbiamo inserito tutti i parametri. Ora premiamo OKed Excel crea una formula per noi con una funzione VPR.
Se sperimentiamo diversi valori per l'importo totale delle vendite, ci assicureremo che la formula funzioni correttamente.
Conclusione
Quando la funzione VPR funziona con database, argomento Ricerca_intervallo (range_lookup) deve accettare FALSO (FALSO). E il valore inserito come Valore di ricerca (Valore_ricerca) deve esistere nel database. In altre parole, sta cercando una corrispondenza esatta.
Nell'esempio che abbiamo esaminato in questo articolo, non è necessario ottenere una corrispondenza esatta. Questo è il caso quando la funzione VPR deve passare alla modalità approssimativa per restituire il risultato desiderato.
Per esempio: Vogliamo determinare quale tariffa utilizzare nel calcolo della commissione per un venditore con un volume di vendita di $ 34988. Funzione VPR ci restituisce un valore del 30%, che è assolutamente corretto. Ma perché la formula ha selezionato la riga contenente esattamente il 30% e non il 20% o il 40%? Cosa si intende per ricerca approssimativa? Cerchiamo di essere chiari.
Quando l'argomento Ricerca_intervallo (interval_lookup) ha un valore VERO CODICE (TRUE) o omesso, funzione VPR scorre la prima colonna e seleziona il valore più grande che non supera il valore di ricerca.
Punto importante: Affinché questo schema funzioni, la prima colonna della tabella deve essere ordinata in ordine crescente.