1Aug
V nedávném článku jsme představili funkci aplikace Excel nazvanou VLOOKUP a vysvětlili, jak by ji bylo možné využít k načtení informací z databáze do buňky v místním pracovním listu. V tomto článku jsme zmínili, že pro VLOOKUP existují dvě možnosti využití a pouze jedna z nich se zabývá dotazováním databází.V tomto článku, druhém a posledním z řady VLOOKUP, zkoumáme toto další méně známé použití funkce VLOOKUP.
Pokud jste tak ještě neučinili, přečtěte si prosím první článek VLOOKUP - tento článek předpokládá, že mnohé pojmy vysvětlené v tomto článku jsou čtenáři již známy.
Při práci s databázemi je VLOOKUP předán "jedinečný identifikátor", který slouží k identifikaci záznamu dat, který chceme v databázi najít( např. Kód produktu nebo identifikační číslo zákazníka).Tento jedinečný identifikátor musí mít v databázi, jinak nám VLOOKUP vrátí chybu. V tomto článku budeme zkoumat způsob použití VLOOKUP, kde identifikátor nemusí vůbec existovat v databázi. Je to téměř tak, jako by VLOOKUP mohl přijmout přístup "dost blízko a je dost dobrý", abychom vrátili data, která hledáme. Za určitých okolností je to
přesně , co potřebujeme.Tento článek ilustrujeme příkladem reálného světa - výpočtem provizí, které jsou generovány na základě údajů o prodeji. Začneme velmi jednoduchým scénářem a postupně jej budeme složitější, dokud jediným racionálním řešením problému nebude použití VLOOKUPu. Počáteční scénář v naší fiktivní společnosti funguje takto: Pokud prodejce vytvoří v daném roce více než 30 000 dolarů za prodej, provize, které vydělávají z tohoto prodeje, činí 30%.Jinak jejich provize činí pouze 20%.Zatím se jedná o velmi jednoduchý pracovní list:
Pro použití tohoto pracovního listu prodává prodejce své údaje o prodeji v buňce B1 a vzorec v buňce B2 vypočítává správnou sazbu provize, kterou mají nárok na příjem, což se používá v buňce B3 pro výpočetcelková provize, kterou má prodejce dlužit( což je jednoduché násobení B1 a B2).
Buňka B2 obsahuje pouze zajímavou část tohoto pracovního listu - vzorec pro určení míry provize, kterou použijete: jednu pod prahovou hodnotu 30 000 USD nebo jednu nad práh. Tento vzorec využívá funkci Excel nazývanou AS .Pro ty čtenáře, kteří nejsou obeznámeni s IF, funguje to takto:
IF( podmínka , hodnota true, hodnota if false )
Kde podmínka je výraz, který vyhodnocuje buď true nebo false .Ve výše uvedeném příkladu je podmínka výrazem B1 & B5 , který lze přečíst jako "Is B1 méně než B5?" Nebo jiným způsobem uvedeným "Celkový prodej je nižší než prahová hodnota".Pokud je odpověď na tuto otázku "ano"( pravda), pak použijeme hodnotu , pokud je pravdivý parametr funkce, konkrétně B6 v tomto případě - sazba provize, pokud celkový prodej byl pod prahovou hodnotou .Pokud je odpověď na otázku "ne"( false), použijeme hodnotu , pokud je parametr falešného parametru , tedy B7 v tomto případě - míra provize, pokud celkový prodej byl nad prahem .
Jak můžete vidět, pomocí prodejní částky ve výši 20 000 USD nám dáváme provizi ve výši 20% v buňce B2.Pokud zadáme hodnotu 40 000 USD, dostaneme jinou sazbu provize:
Takže naše tabulka funguje.
Pojďme to složitější.Představme druhou hranici: Pokud prodejce vydělá více než 40 000 USD, pak se jejich sazba provize zvýší na 40%:
Snadno pochopitelné v reálném světě, ale v buňce B2 se náš vzorec stává složitějším. Pokud se podíváte blíže na vzorec, uvidíte, že třetí parametr původní funkce IF( hodnota , pokud je false ), je nyní celá IF funkce sama o sobě.Toto se nazývá vnořená funkce ( funkce uvnitř funkce).Je dokonale platný v aplikaci Excel( dokonce funguje!), Ale je těžší číst a rozumět.
Nebudeme jít do matic a šroubů, jak a proč to funguje, ani zkoumáme nuance vnořených funkcí.Toto je návod na VLOOKUP, nikoliv na aplikaci Excel obecně.
Každopádně se to zhoršuje! A co když se rozhodneme, že pokud vydělávají více než 50 000 dolarů, pak mají nárok na 50% provizi a pokud vydělávají více než 60 000 dolarů, mají nárok na 60% provizi?
Nyní vzorec v buňce B2, když je správný, se stal prakticky nečitelným. Nikdo by neměl psát vzorce, kde jsou funkce vnořené do čtyř úrovní hluboko! Určitě musí existovat jednodušší cesta?
Určitě je. VLOOKUP na záchranu!
Letzme redesign listu trochu. Budeme uchovávat všechny stejné postavy, ale zorganizovat je novým způsobem, více tabulkové způsobem:
Chvilku si ověřte, že nový Tabulka hodnot pracuje přesně stejně jako série výše uvedených prahů.
Koncepčně, to, co se chystáme udělat, je použít VLOOKUP pro vyhledání prodeje prodejce celkem( od B1) v tabulce sazeb a vrátit nám odpovídající sazbu provize. Všimněte si, že prodejce skutečně vytvořil tržby, které nejsou ani , jedna z pěti hodnot v tabulce sazeb( $ 0, $ 30,000, $ 40,000, $ 50,000 nebo $ 60,000).Mohli vytvořit tržby ve výši 34.988 dolarů.Je důležité si uvědomit, že $ 34,988 nezobrazuje ani v tabulce sazby. Podívejme se, zda VLOOKUP může vyřešit náš problém. ..
Vybíráme buňku B2( umístění, kterou chceme dát do vzorce) a potom vložíme funkci VLOOKUP z karty :
Zobrazí se pole Argument Argumenty pro VLOOKUP.Argumenty( parametry) vyplňujeme jeden po druhém, počínaje hodnotou Lookup_value , což je v tomto případě celkový prodej z buňky B1.Umístíme kurzor do pole Lookup_value a potom jednou klikneme na buňku B1:
Dále musíme zadat do VLOOKUPu, jakou tabulku vyhledáváme tato data. V tomto příkladu je to samozřejmě tabulka sazby. Umístíme kurzor do pole Table_array a pak zvýrazníme celou tabulku sazeb - s výjimkou nadpisů :
Dále musíme určit, který sloupec v tabulce obsahuje informace, které chceme, aby se náš vzorec vrátil. V tomto případě chceme rychlost komisionu, která se nachází v druhém sloupci tabulky, a proto do pole 2 _CZ_index_num zadáme 2 :
Nakonec zadáme hodnotu do pole Range_lookup .
Důležité: Použití tohoto pole odlišuje dva způsoby použití VLOOKUP.Chcete-li použít VLOOKUP s databází, musí být tento konečný parametr Range_lookup vždy nastaven na FALSE , ale s tímto dalším použitím VLOOKUPu musíme buď nechat prázdné nebo zadat hodnotu TRUE .Při použití funkce VLOOKUP je důležité, abyste správně zvolili tento konečný parametr.
Abychom byli explicitní, do pole Range_lookup zadáme hodnotu true .Také by bylo dobré nechat to prázdné, protože je to výchozí hodnota:
Dokončili jsme všechny parametry. Nyní klikneme na tlačítko OK a aplikace Excel sestaví pro nás náš vzorec VLOOKUP:
Pokud experimentujeme s několika různými celkovými částkami prodeje, můžeme se ujistit, že vzorec funguje.
Závěr
Ve verzi VLOOKUP "databáze", kde parametr Range_lookup je FALSE , hodnota předaná v prvním parametru( Lookup_value ) musí být přítomna v databázi. Jinými slovy, hledáme přesnou shodu.
Ale v tomto dalším použití VLOOKUPu nejsme nutně hledá přesnou shodu. V takovém případě je "dost blízko".Ale co tím myslíme "dostatečně blízko"?Použijeme příklad: Při hledání provize na prodejní částce 34.988 dolarů nám náš vzorec VLOOKUP vrátí hodnotu 30%, což je správná odpověď.Proč si zvolil řádek v tabulce obsahující 30%?Co vlastně znamená v tomto případě "dostatečně blízko"?Přesněji řečeno:
Když je Range_lookup nastaven na TRUE ( nebo vynechána), VLOOKUP bude ve sloupci 1 vypadat a nejlépe odpovídat , než je parametr Lookup_value .
Je také důležité poznamenat, že pro tento systém funguje tabulka musí být tříděna ve vzestupném pořadí ve sloupci 1 !
Pokud chcete pracovat s VLOOKUP, ukázkový soubor ilustrovaný v tomto článku si můžete stáhnout zde.
