Cuprins
Am dedicat recent un articol uneia dintre cele mai utile funcții Excel numite VPR și a arătat cum poate fi folosit pentru a extrage informațiile necesare dintr-o bază de date într-o celulă de foaie de lucru. De asemenea, am menționat că există două cazuri de utilizare pentru funcție VPR și doar unul dintre ele se ocupă de interogări de baze de date. În acest articol, veți învăța un alt mod mai puțin cunoscut de a utiliza funcția VPR în Excel.
Dacă nu ați făcut acest lucru încă, atunci asigurați-vă că citiți ultimul articol despre funcție VPR, deoarece toate informațiile de mai jos presupun că sunteți deja familiarizat cu principiile descrise în primul articol.
Când lucrați cu baze de date, funcții VPR este transmis un identificator unic, care este folosit pentru a identifica informațiile pe care dorim să le găsim (de exemplu, un cod de produs sau un număr de identificare a clientului). Acest cod unic trebuie să fie prezent în baza de date, în caz contrar VPR va raporta o eroare. În acest articol, vom analiza acest mod de utilizare a funcției VPRcând id-ul nu există deloc în baza de date. De parcă funcția VPR a trecut la modul aproximativ și alege ce date să ne furnizeze atunci când dorim să găsim ceva. În anumite circumstanțe, este exact ceea ce este necesar.
Un exemplu din viață. Noi stabilim sarcina
Să ilustrăm acest articol cu un exemplu real – calculul comisioanelor pe baza unei game largi de valori de vânzări. Vom începe cu o opțiune foarte simplă, apoi o vom complica treptat până când singura soluție rațională a problemei este utilizarea funcției VPR. Scenariul inițial pentru sarcina noastră fictivă este următorul: dacă un agent de vânzări face mai mult de 30000 USD în vânzări într-un an, atunci comisionul său este de 30%. În rest, comisionul este de doar 20%. Să o punem sub forma unui tabel:
Vânzătorul își introduce datele de vânzări în celula B1, iar formula din celula B2 determină rata de comision corectă la care se poate aștepta vânzătorul. La rândul său, rata rezultată este utilizată în celula B3 pentru a calcula comisionul total pe care ar trebui să îl primească vânzătorul (pur și simplu înmulțind celulele B1 și B2).
Cea mai interesantă parte a tabelului este conținută în celula B2 - aceasta este formula pentru determinarea ratei comisionului. Această formulă conține o funcție Excel numită IF (DACĂ). Pentru acei cititori care nu sunt familiarizați cu această funcție, voi explica cum funcționează:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Condiție este un argument al funcției care ia valoarea fiecaruia COD ADEVĂRAT (ADEVĂRAT), sau FALS (FALS). În exemplul de mai sus, expresia B1
Este adevărat că B1 este mai mic decât B5?
Sau poți spune altfel:
Este adevărat că valoarea totală a vânzărilor pentru anul este mai mică decât valoarea de prag?
Dacă răspundem la această întrebare DA (TRUE), apoi funcția revine valoare dacă este adevărată (valoare dacă este TRUE). În cazul nostru, aceasta va fi valoarea celulei B6, adică rata comisionului atunci când vânzările totale sunt sub pragul. Dacă răspundem la întrebare NU (FALSE) apoi revine valoare dacă este falsă (valoare dacă este FALS). În cazul nostru, aceasta este valoarea celulei B7, adică rata comisionului atunci când vânzările totale sunt peste prag.
După cum puteți vedea, dacă luăm vânzările totale de 20000 USD, obținem o rată de comision de 2% în celula B20. Dacă introducem o valoare de 40000 USD, atunci rata comisionului se va modifica cu 30%:
Așa funcționează masa noastră.
Ne complicăm sarcina
Să facem lucrurile puțin mai dificile. Să stabilim un alt prag: dacă vânzătorul câștigă mai mult de 40000 USD, atunci rata comisionului crește la 40%:
Totul pare să fie simplu și clar, dar formula noastră din celula B2 devine considerabil mai complicată. Dacă te uiți cu atenție la formulă, vei vedea că al treilea argument al funcției IF (IF) sa transformat într-o altă funcție cu drepturi depline IF (DACĂ). Această construcție se numește imbricarea funcțiilor unele în altele. Excel permite din fericire aceste constructe și chiar funcționează, dar sunt mult mai greu de citit și de înțeles.
Nu vom aprofunda în detaliile tehnice - de ce și cum funcționează și nu vom intra în nuanțele scrierii funcțiilor imbricate. La urma urmei, acesta este un articol dedicat funcției VPR, nu un ghid complet pentru Excel.
Oricare ar fi cazul, formula devine mai complicată! Ce se întâmplă dacă introducem o altă opțiune pentru o rată a comisionului de 50% pentru acei vânzători care câștigă mai mult de 50000 USD în vânzări. Și dacă cineva a vândut mai mult de 60000 USD, va plăti comision de 60%?
Acum formula din celula B2, chiar dacă a fost scrisă fără erori, a devenit complet ilizibilă. Cred că sunt puțini cei care doresc să folosească formule cu 4 niveluri de cuibărit în proiectele lor. Trebuie să existe o cale mai ușoară?!
Și există o astfel de cale! Funcția ne va ajuta VPR.
Aplicăm funcția CĂUTARE V pentru a rezolva problema
Să schimbăm puțin designul mesei noastre. Vom păstra aceleași câmpuri și date, dar le vom aranja într-un mod nou, mai compact:
Luați un moment și asigurați-vă că noua masă Tabelul de tarife include aceleași date ca tabelul de prag anterior.
Ideea principală este să folosiți funcția VPR pentru a determina cota tarifară dorită conform tabelului Tabelul de tarife in functie de volumul vanzarilor. Vă rugăm să rețineți că vânzătorul poate vinde bunuri pentru o sumă care nu este egală cu unul dintre cele cinci praguri din tabel. De exemplu, ar putea vinde cu 34988 USD, dar nu există o astfel de sumă. Să vedem cum funcționează VPR poate face față unei astfel de situații.
Inserarea unei funcții VLOOKUP
Selectați celula B2 (unde vrem să introducem formula noastră) și găsiți CĂUTARE (CĂUTARE V) în Biblioteca de funcții Excel: Formule (formule) > Bibliotecă de funcții (Biblioteca de funcții) > Căutare și referințe (Referințe și matrice).
Apare o casetă de dialog Argumente funcționale (Argumente ale funcției). Completam valorile argumentelor unul câte unul, începând cu Căutare_valor (Valoare_căutare). În acest exemplu, aceasta este valoarea totală a vânzărilor din celula B1. Pune cursorul în câmp Căutare_valor (Lookup_value) și selectați celula B1.
Apoi, trebuie să specificați funcțiile VPRunde să caute date. În exemplul nostru, acesta este un tabel Tabelul de tarife. Pune cursorul în câmp Table_array (Tabel) și selectați întregul tabel Tabelul de tarifecu excepția antetelor.
Apoi, trebuie să specificăm din ce coloană să extragem datele folosind formula noastră. Ne interesează rata comisionului, care se află în a doua coloană a tabelului. Prin urmare, pentru argument Col_index_num (Column_number) introduceți valoarea 2.
Și, în sfârșit, introducem ultimul argument - Range_lookup (Interval_lookup).
Important: utilizarea acestui argument este cea care face diferența între cele două moduri de aplicare a funcției VPR. Când lucrați cu baze de date, argumentul Range_lookup (range_lookup) trebuie să aibă întotdeauna o valoare FALS (FALSE) pentru a căuta o potrivire exactă. În utilizarea de către noi a funcției VPR, trebuie să lăsăm acest câmp necompletat sau să introducem o valoare COD ADEVĂRAT (ADEVĂRAT). Este extrem de important să alegeți corect această opțiune.
Pentru a fi mai clar, vă vom prezenta COD ADEVĂRAT (ADEVĂRAT) în teren Range_lookup (Interval_lookup). Deși, dacă lăsați câmpul necompletat, aceasta nu va fi o eroare, deoarece COD ADEVĂRAT este valoarea sa implicită:
Am completat toți parametrii. Acum apăsăm OK, iar Excel ne creează o formulă cu o funcție VPR.
Dacă experimentăm mai multe valori diferite pentru valoarea totală a vânzărilor, atunci ne vom asigura că formula funcționează corect.
Concluzie
Când funcţia VPR lucrează cu baze de date, argument Range_lookup (range_lookup) trebuie să accepte FALS (FALS). Și valoarea introdusă ca Căutare_valor (Lookup_value) trebuie să existe în baza de date. Cu alte cuvinte, caută o potrivire exactă.
În exemplul pe care l-am analizat în acest articol, nu este nevoie să obțineți o potrivire exactă. Acesta este cazul când funcția VPR trebuie să comute în modul aproximativ pentru a returna rezultatul dorit.
De exemplu: Dorim să stabilim ce rată să folosim în calculul comisionului pentru un agent de vânzări cu un volum de vânzări de 34988 USD. Funcţie VPR ne returnează o valoare de 30%, ceea ce este absolut corect. Dar de ce a selectat formula rândul care conține exact 30% și nu 20% sau 40%? Ce se înțelege prin căutare aproximativă? Să fim clari.
Când argumentul Range_lookup (interval_lookup) are o valoare COD ADEVĂRAT (ADEVĂRAT) sau omisă, funcție VPR iterează prin prima coloană și selectează cea mai mare valoare care nu depășește valoarea de căutare.
Punct important: Pentru ca această schemă să funcționeze, prima coloană a tabelului trebuie sortată în ordine crescătoare.