Obsah
Nedávno sme venovali článok jednej z najužitočnejších funkcií Excelu tzv VPR a ukázal, ako sa dá použiť na extrahovanie požadovaných informácií z databázy do bunky pracovného hárka. Tiež sme spomenuli, že existujú dva prípady použitia funkcie VPR a iba jeden z nich sa zaoberá databázovými dotazmi. V tomto článku sa dozviete ďalší menej známy spôsob využitia funkcie VPR v programe Excel.
Ak ste to ešte neurobili, určite si prečítajte posledný článok o funkcii VPR, pretože všetky nižšie uvedené informácie predpokladajú, že už poznáte princípy opísané v prvom článku.
Pri práci s databázami funkcie VPR odovzdá sa jedinečný identifikátor, ktorý slúži na identifikáciu informácií, ktoré chceme nájsť (napríklad kód produktu alebo identifikačné číslo zákazníka). Tento jedinečný kód musí byť prítomný v databáze, inak VPR ohlási chybu. V tomto článku sa pozrieme na tento spôsob použitia funkcie VPRkeď ID v databáze vôbec neexistuje. Ako keby funkcia VPR prepne do približného režimu a vyberá si, aké údaje nám poskytne, keď chceme niečo nájsť. Za určitých okolností je to presne to, čo je potrebné.
Príklad zo života. Stanovili sme si úlohu
Ilustrujme tento článok na príklade z reálneho života – výpočet provízií na základe širokej škály predajných metrík. Začneme veľmi jednoduchou možnosťou a potom ju budeme postupne komplikovať, až kým jediným racionálnym riešením problému nebude použiť funkciu VPR. Počiatočný scenár našej fiktívnej úlohy je nasledovný: ak predajca zarobí viac ako 30000 30 dolárov za rok, jeho provízia je 20 %. V opačnom prípade je provízia len XNUMX%. Uveďme to vo forme tabuľky:
Predajca zadá svoje údaje o predaji do bunky B1 a vzorec v bunke B2 určí správnu sadzbu provízie, ktorú môže predajca očakávať. Výsledná sadzba sa zase použije v bunke B3 na výpočet celkovej provízie, ktorú by mal predajca dostať (jednoduchým vynásobením buniek B1 a B2).
Najzaujímavejšia časť tabuľky sa nachádza v bunke B2 – ide o vzorec na určenie sadzby provízie. Tento vzorec obsahuje funkciu Excelu s názvom IF (IF). Pre tých čitateľov, ktorí túto funkciu nepoznajú, vysvetlím, ako funguje:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Podmienka je argument funkcie, ktorý má hodnotu oboch SKUTOČNÝ KÓD (PRAVDA), príp FALSE (NEPRAVDA). Vo vyššie uvedenom príklade výraz B1
Je pravda, že B1 je menej ako B5?
Alebo to môžete povedať inak:
Je pravda, že celkový objem predaja za rok je nižší ako prahová hodnota?
Ak odpovieme na túto otázku ÁNO (TRUE), potom sa funkcia vráti hodnotu, ak je pravdivá (hodnota, ak je TRUE). V našom prípade to bude hodnota bunky B6, teda provízna sadzba, keď sú celkové tržby pod prahovou hodnotou. Ak odpovieme na otázku NO (FALSE) potom sa vráti hodnota, ak je nepravda (hodnota, ak je FALSE). V našom prípade je to hodnota bunky B7, teda provízna sadzba, keď sú celkové tržby nad prahovou hodnotou.
Ako vidíte, ak vezmeme celkový predaj 20000 2 USD, v bunke B20 dostaneme províziu 40000 %. Ak zadáme hodnotu 30 XNUMX USD, sadzba provízie sa zmení o XNUMX %:
Takto funguje náš stôl.
Komplikujeme úlohu
Poďme si veci trochu sťažiť. Stanovme si ďalšiu hranicu: ak predajca zarobí viac ako 40000 40 USD, provízia sa zvýši na XNUMX %:
Všetko sa zdá byť jednoduché a jasné, ale náš vzorec v bunke B2 sa výrazne skomplikuje. Ak sa pozriete pozorne na vzorec, uvidíte, že tretí argument funkcie IF (IF) sa zmenil na ďalšiu plnohodnotnú funkciu IF (IF). Táto konštrukcia sa nazýva vnorenie funkcií do seba. Excel tieto konštrukcie našťastie umožňuje a dokonca fungujú, no je oveľa ťažšie ich čítať a pochopiť.
Nebudeme sa vŕtať v technických detailoch – prečo a ako to funguje, a nebudeme sa venovať nuansám písania vnorených funkcií. Koniec koncov, toto je článok venovaný funkcii VPR, nie je úplný sprievodca Excelom.
V každom prípade sa vzorec skomplikuje! Čo ak zavedieme ďalšiu možnosť provízie vo výške 50 % pre tých predajcov, ktorí zarobia viac ako 50000 60000 USD. A ak niekto predal viac ako 60 XNUMX dolárov, zaplatí XNUMX% províziu?
Teraz sa vzorec v bunke B2, aj keď bol napísaný bez chýb, stal úplne nečitateľný. Myslím si, že je málo tých, ktorí chcú vo svojich projektoch používať vzorce so 4 úrovňami vnorenia. Musí existovať jednoduchší spôsob?!
A existuje taký spôsob! Funkcia nám pomôže VPR.
Na vyriešenie problému použijeme funkciu VLOOKUP
Poďme trochu zmeniť dizajn nášho stola. Zachováme všetky rovnaké polia a údaje, ale usporiadame ich novým, kompaktnejším spôsobom:
Urobte si chvíľu a uistite sa, že nový stôl Tabuľka cien obsahuje rovnaké údaje ako predchádzajúca tabuľka prahov.
Hlavnou myšlienkou je použitie funkcie VPR určiť požadovanú tarifnú sadzbu podľa tabuľky Tabuľka cien v závislosti od objemu predaja. Upozorňujeme, že predajca môže predať tovar za sumu, ktorá sa nerovná jednej z piatich prahových hodnôt v tabuľke. Napríklad by mohol predať za 34988 XNUMX dolárov, ale taká suma neexistuje. Pozrime sa, ako funguje VPR dokáže riešiť takúto situáciu.
Vloženie funkcie VLOOKUP
Vyberte bunku B2 (kam chceme vložiť náš vzorec) a nájdite VLOOKUP (VLOOKUP) v knižnici funkcií programu Excel: vzorca (vzorce) > Knižnica funkcií (Knižnica funkcií) > Vyhľadanie a referencia (Odkazy a polia).
Zobrazí sa dialógové okno Argumenty funkcie (Funkčné argumenty). Hodnoty argumentov vypĺňame jeden po druhom, počnúc Lookup_value (Hľadaná_hodnota). V tomto príklade ide o celkovú sumu predaja z bunky B1. Umiestnite kurzor do poľa Lookup_value (Lookup_value) a vyberte bunku B1.
Ďalej je potrebné špecifikovať funkcie VPRkde hľadať údaje. V našom príklade ide o tabuľku Tabuľka cien. Umiestnite kurzor do poľa Table_array (Tabuľka) a vyberte celú tabuľku Tabuľka cienokrem hlavičiek.
Ďalej musíme určiť, z ktorého stĺpca sa majú extrahovať údaje pomocou nášho vzorca. Zaujíma nás výška provízie, ktorá je v tabuľke v druhom stĺpci. Preto pre argument Col_index_num (Číslo_stĺpca) zadajte hodnotu 2.
A nakoniec uvádzame posledný argument – Range_loakup (Interval_lookup).
dôležité: práve použitie tohto argumentu robí rozdiel medzi dvoma spôsobmi aplikácie funkcie VPR. Pri práci s databázami argument Range_loakup (range_lookup) musí mať vždy hodnotu FALSE (FALSE), ak chcete vyhľadať presnú zhodu. Pri našom používaní funkcie VPR, musíme toto pole nechať prázdne alebo zadať hodnotu SKUTOČNÝ KÓD (PRAVDA). Je mimoriadne dôležité správne vybrať túto možnosť.
Aby to bolo jasnejšie, predstavíme SKUTOČNÝ KÓD (PRAVDA) v teréne Range_loakup (Interval_lookup). Aj keď, ak necháte pole prázdne, nebude to chyba, pretože SKUTOČNÝ KÓD je jeho predvolená hodnota:
Vyplnili sme všetky parametre. Teraz stlačíme OKa Excel nám vytvorí vzorec s funkciou VPR.
Ak experimentujeme s niekoľkými rôznymi hodnotami celkovej sumy predaja, potom sa uistíme, že vzorec funguje správne.
záver
Keď je funkcia VPR pracuje s databázami, argument Range_loakup (range_lookup) musí prijať FALSE (NEPRAVDA). A hodnota zadaná ako Lookup_value (Lookup_value) musí existovať v databáze. Inými slovami, hľadá presnú zhodu.
V príklade, na ktorý sme sa pozreli v tomto článku, nie je potrebné získať presnú zhodu. To je prípad, keď funkcia VPR musíte prepnúť do približného režimu, aby sa vrátil požadovaný výsledok.
Napríklad: Chceme určiť, akú sadzbu použiť pri výpočte provízie pre predajcu s objemom predaja 34988 XNUMX USD. Funkcia VPR nám vráti hodnotu 30 %, čo je úplne správne. Prečo však vzorec vybral riadok obsahujúci presne 30 % a nie 20 % alebo 40 %? Čo znamená približné vyhľadávanie? Aby bolo jasné.
Keď argument Range_loakup (interval_lookup) má hodnotu SKUTOČNÝ KÓD (TRUE) alebo vynechaná, funkcia VPR iteruje cez prvý stĺpec a vyberie najväčšiu hodnotu, ktorá nepresahuje vyhľadávanú hodnotu.
Dôležité upozornenie: Aby táto schéma fungovala, prvý stĺpec tabuľky musí byť zoradený vzostupne.