Î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, 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 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. 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 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 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ă î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ă din această foaie de lucru - formula care determină ce rată de comisioane să utilizeze: 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<>, care poate fi citit ca "Este B1 mai puțin 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 dacă este adevărat parametru al funcției, și anume B6 în acest caz - rata comisionului dacă totalul vânzărilor a fost de mai jos pragul. Dacă răspunsul la întrebare este "nu" (false), atunci vom folosi valoare dacă este falsă parametru al funcției, și anume B7 în acest caz - rata comisionului dacă totalul vânzărilor a fost de mai sus pragul.
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, tabelul nostru 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 IF originale ( 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 nucile și buloanele cum și de ce funcționează acest lucru, ș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ă?
Cu siguranță există. VLOOKUP pentru salvare!
Să redesemnăm foaia de lucru puțin. 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 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 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 nu 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 din Formulele fila:
Argumente funcționale 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 de rată, desigur. Am plasat cursorul în Table_array câmp și apoi evidențiați întregul tabel de rată - cu excepția rubricilor:
Apoi trebuie să specificăm ce coloană din tabel conține informațiile pe care le dorim ca formula noastră să ne revină la noi. În acest caz, dorim rata comisionului, care se găsește în a doua coloană din tabel, 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ă alegeți corect acest parametru final.
Pentru a fi explicit, vom introduce o valoare Adevărat în Range_lookup camp. De asemenea, ar fi bine să o lăsați necompletată, 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 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ă 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ă faceți o practică cu VLOOKUP, fișierul exemplu ilustrat în acest articol poate fi descărcat de aici.
