Obsah
Nedávno jsme věnovali článek jedné z nejužitečnějších funkcí Excelu tzv VPR a ukázal, jak jej lze použít k extrahování požadovaných informací z databáze do buňky listu. Také jsme zmínili, že existují dva případy použití funkce VPR a pouze jeden z nich se zabývá databázovými dotazy. V tomto článku se dozvíte další méně známý způsob použití funkce VPR v aplikaci Excel.
Pokud jste to ještě neudělali, pak si určitě přečtěte poslední článek o funkci VPR, protože všechny níže uvedené informace předpokládají, že jste již obeznámeni s principy popsanými v prvním článku.
Při práci s databázemi funkce VPR je předán jedinečný identifikátor, který slouží k identifikaci informací, které chceme najít (například kód produktu nebo identifikační číslo zákazníka). Tento jedinečný kód musí být přítomen v databázi, jinak VPR ohlásí chybu. V tomto článku se podíváme na tento způsob použití funkce VPRkdyž ID v databázi vůbec neexistuje. Jako by funkce VPR přepne do přibližného režimu a vybere si, jaká data nám poskytne, když budeme chtít něco najít. Za určitých okolností je to přesně to, co je potřeba.
Příklad ze života. Stanovili jsme úkol
Pojďme si tento článek ilustrovat na příkladu z reálného života – výpočtu provizí na základě široké škály prodejních metrik. Začneme velmi jednoduchou možností a pak ji budeme postupně komplikovat, až bude jediným racionálním řešením problému použití funkce VPR. Počáteční scénář našeho fiktivního úkolu je následující: pokud prodejce vydělá více než 30000 30 USD za rok, jeho provize je 20 %. V opačném případě je provize pouze XNUMX %. Uveďme to ve formě tabulky:
Prodejce zadá své údaje o prodeji do buňky B1 a vzorec v buňce B2 určí správnou sazbu provize, kterou může prodávající očekávat. Výsledná sazba se zase použije v buňce B3 k výpočtu celkové provize, kterou by měl prodejce obdržet (prostým vynásobením buněk B1 a B2).
Nejzajímavější část tabulky je obsažena v buňce B2 – to je vzorec pro stanovení sazby provize. Tento vzorec obsahuje funkci aplikace Excel s názvem IF (LI). Pro ty čtenáře, kteří tuto funkci neznají, vysvětlím, jak funguje:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Stav je argument funkce, který má hodnotu buď PRAVÝ KÓD (PRAVDA), popř NEPRAVDIVÉ (NEPRAVDIVÉ). Ve výše uvedeném příkladu výraz B1
Je pravda, že B1 je menší než B5?
Nebo to můžete říct jinak:
Je pravda, že celková výše tržeb za rok je nižší než prahová hodnota?
Pokud na tuto otázku odpovíme ANO (TRUE), pak se funkce vrátí hodnotu, pokud je pravdivá (hodnota, pokud je PRAVDA). V našem případě to bude hodnota buňky B6, tedy provizní sazba, když jsou celkové tržby pod prahem. Pokud odpovíme na otázku NE (FALSE) se poté vrátí hodnota je-li nepravda (hodnota, pokud je FALSE). V našem případě je to hodnota buňky B7, tedy provizní sazba, když jsou celkové tržby nad prahem.
Jak můžete vidět, pokud vezmeme celkový prodej 20000 2 USD, dostaneme 20% sazbu provize v buňce B40000. Pokud zadáme hodnotu 30 XNUMX $, pak se sazba provize změní o XNUMX %:
Takto funguje náš stůl.
Úkol komplikujeme
Pojďme si věci trochu ztížit. Nastavíme další práh: pokud prodejce vydělá více než 40000 40 USD, pak se sazba provize zvýší na XNUMX %:
Vše se zdá být jednoduché a jasné, ale náš vzorec v buňce B2 se znatelně zkomplikuje. Když se podíváte pozorně na vzorec, uvidíte, že třetí argument funkce IF (IF) se proměnil v další plnohodnotnou funkci IF (LI). Tato konstrukce se nazývá vnořování funkcí do sebe. Excel tyto konstrukce naštěstí umožňuje a dokonce fungují, ale je mnohem těžší je číst a pochopit.
Nebudeme se pouštět do technických detailů – proč a jak to funguje, a nebudeme zabíhat do nuancí psaní vnořených funkcí. Ostatně je to článek věnovaný funkci VPR, není úplný průvodce Excelem.
Ať je to jakkoli, vzorec se komplikuje! Co když zavedeme další možnost provize ve výši 50 % pro ty prodejce, kteří vydělávají více než 50000 60000 $? A pokud někdo prodal více než 60 XNUMX $, zaplatí XNUMX% provizi?
Nyní se vzorec v buňce B2, i když byl napsán bez chyb, stal zcela nečitelným. Myslím, že je málo těch, kteří chtějí ve svých projektech používat vzorce se 4 úrovněmi vnořování. Musí existovat jednodušší způsob?!
A takový způsob existuje! Funkce nám pomůže VPR.
K vyřešení problému použijeme funkci VLOOKUP
Pojďme trochu změnit design našeho stolu. Zachováme všechna stejná pole a data, ale uspořádáme je novým, kompaktnějším způsobem:
Udělejte si chvilku a ujistěte se, že nový stůl Hodnotit tabulku obsahuje stejná data jako předchozí prahová tabulka.
Hlavní myšlenkou je použití funkce VPR pro stanovení požadované tarifní sazby podle tabulky Hodnotit tabulku v závislosti na objemu prodeje. Upozorňujeme, že prodejce může prodat zboží za částku, která se nerovná jedné z pěti prahových hodnot v tabulce. Mohl by například prodat za 34988 dolarů, ale žádná taková částka neexistuje. Podívejme se, jak funguje VPR dokáže takovou situaci řešit.
Vložení funkce VLOOKUP
Vyberte buňku B2 (kam chceme vložit náš vzorec) a najděte VLOOKUP (VLOOKUP) v knihovně funkcí aplikace Excel: Vzorce (vzorce) > Knihovna funkcí (Knihovna funkcí) > Vyhledávání a reference (Odkazy a pole).
Zobrazí se dialogové okno Argumenty funkce (Funkční argumenty). Hodnoty argumentů vyplňujeme jeden po druhém, počínaje Vyhledávací_hodnota (Vyhledávací_hodnota). V tomto příkladu se jedná o celkovou částku prodeje z buňky B1. Umístěte kurzor do pole Vyhledávací_hodnota (Lookup_value) a vyberte buňku B1.
Dále je potřeba specifikovat funkce VPRkde hledat data. V našem příkladu se jedná o tabulku Hodnotit tabulku. Umístěte kurzor do pole Tabulka_pole (Tabulka) a vyberte celou tabulku Hodnotit tabulkukromě hlaviček.
Dále musíme určit, ze kterého sloupce se mají extrahovat data pomocí našeho vzorce. Zajímá nás sazba provize, která je ve druhém sloupci tabulky. Proto pro argument Col_index_num (Číslo_sloupce) zadejte hodnotu 2.
A nakonec uvedeme poslední argument – Range_loakup (Interval_lookup).
důležité: právě použití tohoto argumentu dělá rozdíl mezi dvěma způsoby aplikace funkce VPR. Při práci s databázemi argument Range_loakup (range_lookup) musí mít vždy hodnotu NEPRAVDIVÉ (FALSE) pro vyhledání přesné shody. V našem použití funkce VPR, musíme toto pole nechat prázdné nebo zadat hodnotu PRAVÝ KÓD (SKUTEČNÝ). Je nesmírně důležité správně zvolit tuto možnost.
Aby to bylo jasnější, představíme PRAVÝ KÓD (PRAVDA) v poli Range_loakup (Interval_lookup). I když, pokud necháte pole prázdné, nebude to chyba, protože PRAVÝ KÓD je jeho výchozí hodnota:
Vyplnili jsme všechny parametry. Nyní stiskneme OKa Excel nám vytvoří vzorec s funkcí VPR.
Pokud budeme experimentovat s několika různými hodnotami pro celkovou částku prodeje, pak se ujistíme, že vzorec funguje správně.
Proč investovat do čističky vzduchu?
Když funkce VPR pracuje s databázemi, argument Range_loakup (range_lookup) musí přijmout NEPRAVDIVÉ (NEPRAVDIVÉ). A hodnota zadaná jako Vyhledávací_hodnota (Lookup_value) musí existovat v databázi. Jinými slovy, hledá přesnou shodu.
V příkladu, na který jsme se podívali v tomto článku, není potřeba získat přesnou shodu. To je případ, kdy funkce VPR musí přepnout do přibližného režimu, aby se vrátil požadovaný výsledek.
Například: Chceme určit, jakou sazbu použít při výpočtu provize pro prodejce s objemem prodeje 34988 XNUMX USD. Funkce VPR nám vrátí hodnotu 30 %, což je naprosto správné. Proč ale vzorec vybral řádek obsahující přesně 30 % a ne 20 % nebo 40 %? Co znamená přibližné vyhledávání? Aby bylo jasno.
Když argument Range_loakup (interval_lookup) má hodnotu PRAVÝ KÓD (TRUE) nebo vynecháno, funkce VPR iteruje přes první sloupec a vybere největší hodnotu, která nepřesahuje vyhledávací hodnotu.
Důležitý bod: Aby toto schéma fungovalo, musí být první sloupec tabulky seřazen vzestupně.