VLOOKUP în Excel, partea 2 Utilizând VLOOKUP fără o bază de date
Într-un articol recent, am introdus funcția Excel numită VLOOKUP și a explicat cum ar putea fi folosit pentru a prelua informații dintr-o bază de date într-o celulă într-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, 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 sa există în baza de date, altfel VLOOKUP ne întoarce o eroare. În acest articol, vom examina o modalitate de a folosi VLOOKUP unde identificatorul nu trebuie să existe în baza de date la toate. Este aproape ca și cum VLOOKUP poate adopta o abordare "destul de aproape este destul de bun" pentru a returna datele pe care le căutăm. În anumite circumstanțe, acest lucru este posibil exact ce ne trebuie.
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 progresiv 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 în valoare 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ă în prezent, aceasta 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 utilizată în celula B3 pentru a calcula comisionul total datorat agentului de vânzări este o multiplicare simplă a lui B1 și B2).
Celula B2 conține singura parte interesantă a acestei foi de lucru - formula care determină ce rată a comisionului să folosească: cea de mai jos pragul de 30.000 de dolari, sau unul de mai sus pragul. Această formulă face uz de funcția Excel numită DACĂ. Pentru acei cititori care nu sunt familiarizați cu IF, funcționează astfel:
DACĂ(condiție, valoare dacă este adevărată, valoare dacă este falsă)
Unde condiție este o expresie care se evaluează la oricare dintre ele Adevărat sau fals. În exemplul de mai sus, condiție este expresia B1
După cum puteți vedea, folosind un total de vânzări de 20.000 de dolari ne dă o rată de comision de 20% în celula B2. Dacă vom introduce o valoare de 40.000 $, vom obține o rată de comision diferită:
Deci, foaia noastră 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 de dolari, 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 inițiale IF ( valoare dacă este falsă) este acum o întreagă funcție IF în sine. Aceasta se numește a funcție 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 nuci și șuruburi 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 înrăutățește! Ce se întâmplă 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 dolari, au dreptul la 60% comision?
Acum, formula din celula B2, în timp ce este corectă, a devenit practic imposibil de citit. Nimeni nu ar trebui să scrie formulări în care funcțiile sunt imbricate în patru niveluri adânci! Desigur, trebuie să existe o cale mai simplă?
Cu siguranță există. VLOOKUP la salvare!
Să redesemnăm un pic foaia de lucru. Vom păstra toate aceleași figuri, dar o vom organiza într-un mod nou, și mai mult tabular cale:
Ia-ți o clipă și verifică-ți că noul Tabelul evaluării funcționează exact la fel ca și seria de praguri de mai sus.
Conceptual, ceea ce suntem pe cale să facem 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 a creat într-adevăr vânzări care sunt nu una dintre cele cinci valori din tabelul de rata ($ 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 dolari au nu apar în tabelul de rată. Să vedem dacă VLOOKUP poate rezolva oricum problema noastră ...
Selectăm celula B2 (locația pe care dorim să o punem), apoi inserați funcția VLOOKUP de la Formulele fila:
Argumentele funcției caseta pentru VLOOKUP apare. 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. Am plasat cursorul în Lookup_value apoi faceți clic pe o dată pe celula B1:
Apoi trebuie să specificăm la VLOOKUP ce tabelă să caute aceste date înăuntru. În acest exemplu, este tabelul ratei, desigur. Am plasat cursorul în Table_array câmp și apoi evidențiați întregul tabel al ratei - cu excepția rubricilor:
În continuare, trebuie să specificăm ce coloană din tabel conține informațiile pe care le 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șa că, prin urmare, introducem a 2 în Col_index_num camp:
În cele din urmă, introduceți o valoare în Range_lookup camp.
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 să fie întotdeauna setată la FALS, dar cu această altă utilizare a VLOOKUP, trebuie fie să îl lăsăm necompletat, fie să introduceți o valoare ADEVĂRAT. Atunci când utilizați VLOOKUP, este vital să faceți alegerea corectă pentru acest parametru final.
Pentru a fi explicit, vom introduce o valoare Adevărat în Range_lookup camp. 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 O.K butonul și Excel construiesc formula noastră 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 Range_lookup este parametrul FALS, valoarea trecută în primul parametru (Lookup_value) trebuie sa să fie prezente î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 suficient de bun". Dar ce înțelegem prin "destul de aproape"? Să folosim un exemplu: Când căutăm o rată a comisionului la un total de vânzări de 34.988 $, formula noastră VLOOKUP ne va trimite o valoare de 30%, care este răspunsul corect. De ce a ales rândul din tabel conținând 30%? Ce înseamnă, de fapt, "destul de aproape" în acest caz? Să fim preciși:
Cand Range_lookup este setat sa ADEVĂRAT (sau omis), VLOOKUP va arăta în coloana 1 și se va potrivi cea mai mare valoare care nu este mai mare decât Lookup_value parametru.
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ă practiciți cu VLOOKUP, fișierul exemplu din acest articol poate fi descărcat de aici.
