1Aug
Într-un articol recent, am introdus funcția Excel denumită VLOOKUP și am explicat cum ar putea fi utilizată pentru a prelua informații dintr-o bază de date într-o celulă dintr-o foaie de lucru locală.În acest articol am menționat că au existat două utilizări pentru VLOOKUP și doar unul dintre ei sa ocupat de interogarea bazelor de date.În acest articol, al doilea și ultimul din seria VLOOKUP, vom examina această altă utilizare mai puțin cunoscută pentru funcția VLOOKUP.
Dacă nu ați făcut deja acest lucru, vă rugăm să citiți primul articol VLOOKUP - acest articol va presupune că multe dintre conceptele explicate în acel articol sunt deja cunoscute cititorului.
Atunci când se lucrează cu bazele de date, VLOOKUP primește un "identificator unic" care servește pentru a identifica ce înregistrare de date dorim să găsim în baza de date( de exemplu un cod de produs sau un cod de client).Acest identificator unic trebuie să fie în baza de date, altfel VLOOKUP ne întoarce o eroare.În acest articol, vom examina o modalitate de a utiliza VLOOKUP unde identificatorul nu trebuie să existe în baza de date. Este aproape ca si cum VLOOKUP poate adopta o abordare "destul de aproape este destul de bun" pentru a returna datele pe care le cautam.În anumite circumstanțe, acest lucru este
exact ceea ce avem nevoie.Vom exemplifica acest articol cu un exemplu din lumea reală - acela de a calcula comisioanele generate pe un set de cifre de vânzări. Vom începe cu un scenariu foarte simplu și apoi îl vom face treptat mai complex, până când singura soluție rațională a problemei este de a folosi VLOOKUP.Scenariul inițial din compania noastră fictivă funcționează astfel: Dacă un agent de vânzări creează vânzări de peste 30.000 de dolari într-un anumit an, comisionul pe care îl câștigă pentru aceste vânzări este de 30%.În caz contrar, comisionul lor este de numai 20%.Până acum este o foaie de lucru destul de simplă:
Pentru a utiliza această foaie de lucru, agentul de vânzări introduce cifrele de vânzări în celula B1, iar formula din celula B2 calculează rata de comision corectă pe care are dreptul să o primească, care este folosită în celula B3 pentru a calculacomisionul total pe care îl datorează agentului de vânzări( care este o multiplicare simplă a B1 și B2).
Celula B2 conține singura parte interesantă din această foaie de lucru - formula care determină ce rată a comisionului să utilizeze: cea sub pragul de 30.000 $ sau cea de peste pragul. Această formulă utilizează funcția Excel numită IF .Pentru acei cititori care nu sunt familiarizați cu IF, funcționează astfel:
IF( condiție , valoarea dacă este adevărată, valoarea dacă este falsă)
În cazul în care condiția este o expresie care evaluează fie adevărat sau false .În exemplul de mai sus, condiția este expresia B1 & B5 , care poate fi citită ca fiind "B1 mai mică decât B5?" Sau, altfel spus, "Vânzările totale sunt mai mici decât pragul".Dacă răspunsul la această întrebare este "da"( adevărat), atunci vom folosi valoarea dacă este adevărat parametrul al funcției, și anume B6 în acest caz - rata comisionului dacă totalul vânzărilor a fost sub pragul .Dacă răspunsul la întrebare este "nu"( false), atunci vom folosi valoarea dacă parametrul fals al funcției, și anume B7 în acest caz - rata comisionului dacă totalul vânzărilor a fost deasupra pragului .
După cum puteți vedea, utilizând un total de vânzări de 20.000 de dolari ne dă o rată de comision de 20% în celula B2.Dacă introducem o valoare de 40.000 $, obținem o rată de comision diferită:
Deci, foaia de calcul funcționează.
Să o facem mai complexă.Să introducem un al doilea prag: dacă agentul de vânzări câștigă mai mult de 40.000 $, atunci rata comisionului crește până la 40%:
Destul de ușor de înțeles în lumea reală, dar în celula B2 formula noastră devine tot mai complexă.Dacă vă uitați atent la formula, veți vedea că al treilea parametru al funcției IF originale( valoarea dacă este fals) este acum o întreagă funcție IF în sine. Aceasta se numește funcția imbricată ( o funcție în cadrul unei funcții).Este perfect valabil în Excel( chiar funcționează!), Dar este mai greu de citit și de înțeles.
Nu vom intra în piulițele și bolțurile cum și de ce acest lucru funcționează și nu vom examina nuanțele funcțiilor imbricate. Acesta este un tutorial pe VLOOKUP, nu pe Excel în general.
Oricum, se agravează!Dar când decidem că, dacă câștigă mai mult de 50.000 de dolari, au dreptul la comision de 50%, iar dacă câștigă mai mult de 60.000 de dolari, au dreptul la o comision de 60%?
Acum, formula din celula B2, în timp ce este corectă, a devenit practic imposibil de citit. Nimeni nu trebuie să scrie formule în care funcțiile sunt imbricate în patru niveluri adânci! Desigur, trebuie să existe o cale mai simplă?
Este cu siguranță.VLOOKUP pentru salvare!
Să redesemnăm un pic foaia de lucru. Vom păstra toate aceleași figuri, dar organizăm-o într-un mod nou, o modalitate mai amplă :
Ia-ți o clipă și verifică-te că noua tabelă funcționează exact la fel ca seria de praguri de mai sus.
Conceptual, ceea ce vom face este să folosim VLOOKUP pentru a căuta totalul vânzărilor vânzătorului( de la B1) în tabelul de rată și pentru a ne întoarce rata de comision corespunzătoare. Rețineți că agentul de vânzări poate crea într-adevăr vânzări care nu sunt și nu una dintre cele cinci valori din tabelul de rată( $ 0, $ 30,000, $ 40,000, $ 50,000 sau $ 60,000).S-ar putea să fi creat vânzări de 34.988 $.Este important să rețineți că $ 34.988 nu apare și nu în tabelul de rată.Să vedem dacă VLOOKUP ne poate rezolva problema oricum. ..
Se selectează celula B2( locația pe care dorim să o introducem) și apoi se introduce funcția VLOOKUP din fila Formule :
Se afișează caseta Argumentele Argumentele pentru VLOOKUP.Completăm argumentele( parametrii) unul câte unul, începând cu Lookup_value , care este, în acest caz, totalul vânzărilor din celula B1.Plasăm cursorul în câmpul Lookup_value și apoi facem clic o dată pe celula B1:
Apoi trebuie să specificăm pentru VLOOKUP ce tabelă să caute aceste date înăuntru. În acest exemplu, este tabelul de rată, desigur. Plasăm cursorul în câmpul Table_array și apoi evidențiază întreaga tabelă a ratei - , cu excepția titlurilor :
Apoi trebuie să specificăm ce coloană din tabel conține informațiile pe care ne dorim ca formula noastră să ni se întoarcă.În acest caz, dorim rata comisionului, care se găsește în a doua coloană a tabelului, așadar introduceți un 2 în câmpul Col_index_num :
În cele din urmă introduceți o valoare în câmpul Range_lookup .
Important: Este utilizarea acestui câmp care diferențiază cele două moduri de a utiliza VLOOKUP.Pentru a utiliza VLOOKUP cu o bază de date, acest parametru final, Range_lookup , trebuie întotdeauna să fie setat la FALSE , dar cu această altă utilizare a VLOOKUP trebuie să fie lăsat gol sau să introduceți o valoare a TRUE .Atunci când utilizați VLOOKUP, este vital să alegeți corect acest parametru final.
Pentru a fi explicit, vom introduce o valoare a true în câmpul Range_lookup .De asemenea, ar fi bine să lăsați-o goală, deoarece aceasta este valoarea implicită:
Am finalizat toți parametrii. Acum, faceți clic pe butonul OK , iar Excel își construiește formula VLOOKUP pentru noi:
Dacă experimentăm cu câteva sume diferite de vânzări, ne putem satisface că formula funcționează.
Concluzie
În versiunea "bază de date" a VLOOKUP, unde parametrul Range_lookup este FALSE , valoarea care a trecut în primul parametru Lookup_value trebuie să fie prezentă în baza de date .Cu alte cuvinte, căutăm o potrivire exactă.
Dar în această altă utilizare a VLOOKUP, nu neapărat căutăm o potrivire exactă.În acest caz, "destul de aproape este destul de bun".Dar ce înseamnă "suficient de aproape"?Să folosim un exemplu: Când căutăm o cotă de comision pe un total de vânzări de 34.988 $, formula VLOOKUP ne va întoarce o valoare de 30%, ceea ce este răspunsul corect. De ce a ales rândul din tabel care conține 30%?Ce înseamnă, de fapt, "destul de aproape" în acest caz? Să fie precis:
Când Range_lookup este setat la TRUE ( sau omis), VLOOKUP va arăta în coloana 1 și va potrivi cea mai mare valoare care nu este mai mare decât parametrul Lookup_value .
De asemenea, este important să rețineți că pentru ca acest sistem să funcționeze, tabelul trebuie sortat în ordine crescătoare în coloana 1 !
Dacă doriți să exersați cu VLOOKUP, fișierul exemplu din acest articol poate fi descărcat de aici.
