cuntenutu
Recentemente avemu dedicatu un articulu à una di e funzioni Excel più utili chjamate VPR è hà dimustratu cumu si pò esse usatu per caccià l'infurmazioni necessarii da una basa di dati in una cellula di foglia di travagliu. Avemu ancu menzionatu chì ci sò dui casi d'usu per a funzione VPR è solu unu di elli tratta di e dumande di basa di dati. In issu articulu, vi amparà un altru modu menu cunnisciutu à aduprà a funzione VPR in Excel.
Se ùn avete micca fattu questu, allora assicuratevi di leghje l'ultimu articulu nantu à a funzione VPR, perchè tutte l'infurmazioni sottu assume chì avete digià familiarizatu cù i principii descritti in u primu articulu.
Quandu travaglia cù basa di dati, funzioni VPR un identificatore unicu hè passatu, chì hè utilizatu per identificà l'infurmazioni chì vulemu truvà (per esempiu, un codice di produttu o un numeru d'identificazione di u cliente). Stu codice unicu deve esse presente in a basa di dati, altrimenti VPR signalerà un errore. In questu articulu, avemu da fighjulà sta manera di utilizà a funzione VPRquandu l'id ùn esiste micca in a basa di dati. Cum'è se a funzione VPR cambiatu à u modu apprussimativu, è sceglie ciò chì dati à furnisce noi quandu vulemu truvà qualcosa. In certi casi, questu hè esattamente ciò chì hè necessariu.
Un esempiu da a vita. Avemu stabilitu u compitu
Illustremu stu articulu cun un esempiu di a vita reale - calculà e cumissioni basatu annantu à una larga gamma di metriche di vendita. Cuminciaremu cù una opzione assai simplice, è poi a complicà gradualmente finu à chì l'unica suluzione raziunale à u prublema hè di utilizà a funzione. VPR. U scenariu iniziale per u nostru compitu fittiziu hè u seguitu: se un venditore face più di $ 30000 in vendita in un annu, a so cumissioni hè di 30%. Altrimenti, a cumissioni hè solu 20%. Mettimu in forma di tavula:
U vinditore inserisce i so dati di vendita in a cell B1, è a formula in a cell B2 determina a tarifa di cumissioni curretta chì u vinditore pò aspittà. À u turnu, a tarifa resultanti hè aduprata in a cell B3 per calculà a cumissioni tutale chì u venditore deve riceve (simplicemente multiplicà e cellule B1 è B2).
A parte più interessante di a tavula hè cuntenuta in a cell B2 - questa hè a formula per determinà a tarifa di cumissioni. Questa formula cuntene una funzione Excel chjamata IF (IF). Per quelli lettori chì ùn sò micca familiarizati cù sta funzione, spiegheraghju cumu funziona:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
cundizioni hè un argumentu di funzione chì piglia u valore di l'una o l'altra CODICE VERA (VERU), o In (FALSE). In l'esempiu sopra, l'espressione B1
Hè vera chì B1 hè menu di B5 ?
O pudete dì diversamente:
Hè veru chì a quantità totale di vendita per l'annu hè menu di u valore di u limitu?
S'è no rispondimu sta quistione IÈ (TRUE), allora a funzione torna valore se veru (value si TRUE). In u nostru casu, questu serà u valore di a cell B6, vale à dì a tarifa di cumissioni quandu e vendite totali sò sottu à u limitu. S'è no rispondimu a quistione INNÒ (FALSE) poi torna valore se false (valore se FALSE). In u nostru casu, questu hè u valore di a cell B7, vale à dì a tarifa di cumissioni quandu e vendite totali sò sopra à u limitu.
Comu pudete vede, se pigghiamu a vendita tutale di $ 20000, avemu una tarifa di cumissioni di 2% in a cell B20. Se entremu un valore di $ 40000, allora a tarifa di cumissioni cambierà da 30%:
Questu hè cumu travaglia a nostra tavola.
Cumplichemu u compitu
Facemu e cose un pocu più difficili. Fixemu un altru limitu: se u vinditore guadagna più di $ 40000, allora a tarifa di cumissioni aumenta à 40%:
Tuttu pare esse simplice è chjaru, ma a nostra formula in a cellula B2 diventa notevolmente più cumplicata. Sè vo circate attente à a formula, vi vede chì u terzu argumentu di a funzione IF (IF) hè diventatu in una altra funzione cumpleta IF (IF). Questa custruzzione hè chjamata nidificazione di funzioni in l'altri. Excel felice permette sti custruzzioni, è ancu travaglianu, ma sò assai più difficiuli di leghje è di capiscenu.
Ùn andemu micca in i ditaglii tecnichi - perchè è cumu si travaglia, è ùn andemu micca in i sfumaturi di scrittura funzioni nidificate. Dopu tuttu, questu hè un articulu dedicatu à a funzione VPR, micca una guida cumpleta à Excel.
Qualunque sia u casu, a formula diventa più cumplicata ! E se introducemu una altra opzione per una tarifa di cumissioni di 50% per quelli venditori chì facenu più di $ 50000 in vendita. È se qualchissia hà vindutu più di $ 60000, paganu 60% di cumissioni?
Avà a formula in a cell B2, ancu s'ellu hè stata scritta senza errori, hè diventata completamente illegibile. Pensu chì ci sò pochi chì volenu aduprà formule cù 4 livelli di nidificazione in i so prughjetti. Ci deve esse un modu più faciule ?!
È ci hè un modu cusì! A funzione ci aiuterà VPR.
Applicamu a funzione VLOOKUP per risolve u prublema
Cambiemu un pocu u disignu di a nostra tavola. Manteneremu tutti i stessi campi è dati, ma i disposti in un modu novu è più compactu:
Pigliate un mumentu è assicuratevi chì a nova tavola Tabella di Tariffa include i stessi dati cum'è a tavola di limitu precedente.
L'idea principale hè di utilizà a funzione VPR per determinà a tarifa di tarifa desiderata secondu a tavula Tabella di Tariffa secondu u voluminu di vendita. Per piacè nutate chì u vinditore pò vende merchenzie per una quantità chì ùn hè micca uguale à unu di i cinque soglie in a tavula. Per esempiu, puderia vende per $ 34988, ma ùn ci hè micca una tale quantità. Videmu cumu a funzione VPR pò trattà cun tali situazioni.
Inserisce una funzione VLOOKUP
Selezziunate a cell B2 (induve vulemu inserisce a nostra formula) è truvate CERCA V (VLOOKUP) in a Biblioteca di Funzioni Excel: Formuli (formule) > Libreria di funzioni (Biblioteca di funzioni) > Ricerca è Riferimentu (Referenze è array).
Una finestra di dialogu appare Argumenti di Funzione (Argumenti di funzione). Insememu i valori di l'argumenti unu per unu, cuminciendu cù Lookup_value (Lookup_value). In questu esempiu, questu hè a quantità totale di vendite da a cell B1. Mettite u cursore in u campu Lookup_value (Lookup_value) è selezziunate a cell B1.
Dopu, avete bisognu di specificà e funzioni VPRinduve circà i dati. In u nostru esempiu, questu hè un tavulinu Tabella di Tariffa. Mettite u cursore in u campu Table_array (Table) è selezziunate tutta a tavola Tabella di Tariffaeccettu per l'intestazione.
In seguitu, avemu bisognu di specificà quale colonna per caccià e dati cù a nostra formula. Semu interessate in a tarifa di cumissioni, chì hè in a seconda colonna di a tavula. Dunque, per l'argumentu Col_index_num (Column_number) entre u valore 2.
È infine, introducemu l'ultimu argumentu - Range_lookup (Interval_lookup).
impurtante: hè l'usu di stu argumentu chì face a diffarenza trà e duie manere di applicà a funzione VPR. Quandu u travagliu cù basa di dati, l'argumentu Range_lookup (range_lookup) deve sempre avè un valore In (FALSE) per circà una corrispondenza precisa. In u nostru usu di a funzione VPR, duvemu lascià stu campu in biancu, o entre un valore CODICE VERA (VERU). Hè assai impurtante di sceglie sta opzione currettamente.
Per fà più chjaru, avemu da presentà CODICE VERA (TRUE) in u campu Range_lookup (Interval_lookup). Ancu s'è lasciate u campu in biancu, questu ùn serà micca un errore, postu chì CODICE VERA hè u so valore predeterminatu:
Avemu cumpletu tutti i paràmetri. Avà pressemu OK, è Excel crea una formula per noi cù una funzione VPR.
Se sperimentemu parechji valori diffirenti per a quantità di vendita tutale, allora avemu da esse sicuru chì a formula funziona bè.
cunchiusioni
Quandu a funzione VPR travaglia cù basa di dati, argumentu Range_lookup (range_lookup) deve accettà In (FALSE). È u valore intrutu cum'è Lookup_value (Lookup_value) deve esse in a basa di dati. In altri palori, cerca di una partita precisa.
In l'esempiu chì avemu vistu in questu articulu, ùn ci hè bisognu di ottene una partita esatta. Questu hè u casu quandu a funzione VPR deve passà à u modu apprussimativu per rinvià u risultatu desideratu.
Per esempiu: Vulemu determinà quale tarifa aduprà in u calculu di cumissioni per un venditore cù un voluminu di vendita di $ 34988. Funzione VPR ci rende un valore di 30%, chì hè assolutamente currettu. Ma perchè a formula hà sceltu a fila chì cuntene esattamente 30% è micca 20% o 40%? Cosa si intende per ricerca approssimativa? Simu chjaru.
Quandu l'argumentu Range_lookup (interval_lookup) hà un valore CODICE VERA (TRUE) o omessa, funzione VPR itera per a prima colonna è selezziunate u valore più grande chì ùn supera u valore di ricerca.
Punto importante: Perchè stu schema funziona, a prima colonna di a tavula deve esse ordinata in ordine crescente.