VLOOKUP v Excelu, část 2 Použití VLOOKUP bez databáze
V nedávném článku jsme představili funkci Excel nazvanou VLOOKUP a vysvětlil, jak může být použita k načtení informací z databáze do buňky v místní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 konečném z řady VLOOKUP, zkoumáme toto jiné méně známé použití pro funkci 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é z konceptů vysvětlených 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í existují 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 kdyby VLOOKUP mohl přijmout přístup "dost blízko k tomu, že je dost", abychom vrátili data, která hledáme. Za určitých okolností to je přesně tak 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čněme velmi jednoduchým scénářem a postupně jej zkomplikujeme, dokud jediným racionálním řešením tohoto problému není použití VLOOKUPu. Počáteční scénář v naší fiktivní společnosti funguje takto: Pokud prodejce vytvoří více než 30 000 dolarů za prodej v daném roce, provize, které vydělávají z tohoto prodeje, činí 30%. V opačném případě je jejich provize pouze 20%. Zatím je to docela jednoduchý pracovní list:
Chcete-li použít tento list, prodejce zadá své údaje o prodeji v buňce B1 a vzorec v buňce B2 vypočítá správnou sazbu provize, kterou mají nárok na příjem, který se používá v buňce B3 pro výpočet celkové provize, kterou má dlužník je jednoduché násobení B1 a B2).
Buňka B2 obsahuje pouze zajímavou část tohoto pracovního listu - vzorec pro rozhodnutí o tom, jakou měnovou sazbu použijete: jeden níže hranice 30 000 dolarů nebo jedna výše prahu. Tento vzorec využívá funkci Excel nazvanou LI. Pro ty čtenáře, kteří nejsou s IF známí, funguje to takto:
LI(stav, hodnota, pokud je pravda, hodnota, pokud je nepravdivá).
Kde stav je výraz, který se hodnotí buď skutečný nebo Nepravdivé. Ve výše uvedeném příkladu stav je výraz B1
Jak můžete vidět, pomocí prodejních celků ve výši 20 000 dolarů nám dáváme provizi ve výši 20% v buňce B2. Pokud zadáme hodnotu 40 000 USD, získáme jinou sazbu provize:
Takže náš tabulkový procesor funguje.
Zkusme to složitější. Uveďme druhou hranici: Pokud prodejce vydělá více než 40 000 dolarů, pak sazba sazby 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 pozorně na vzorec, uvidíte, že třetí parametr původní funkce IF ( hodnota, pokud je nepravdivá) je nyní celá IF funkce sama o sobě. Toto se nazývá a 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 ořechů a šroubů, jak a proč to funguje, ani nebudeme zkoumat nuance vnořených funkcí. Toto je návod na VLOOKUP, nikoliv na aplikaci Excel obecně.
Každopádně 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, zatímco správný, se stal prakticky nečitelný. Nikdo by neměl psát vzorce, kde jsou funkce vnořené do čtyř úrovní hluboko! Určitě musí existovat jednodušší způsob?
Určitě je. VLOOKUP na záchranu!
Přeměňme si list trochu. Budeme uchovávat všechny stejné postavy, ale zorganizovat je novým způsobem, více tabelární způsob:
Udělejte si chvíli a ověřte si, že nový Tabulka sazby funguje přesně stejně jako série prahových hodnot výše.
Koncepčně, 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é jsou ne jednu z pěti hodnot v tabulce sazeb ($ 0, $ 30,000, $ 40,000, $ 50,000 nebo $ 60,000). Mohly vytvořit tržby ve výši 34.988 dolarů. Je důležité si uvědomit, že 34.988 dolarů ne v tabulce sazby. Podívejme se, jestli 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žit funkci VLOOKUP z Vzorce záložka:
The Funkční argumenty Zobrazí se okno pro VLOOKUP. Argumenty (parametry) vyplňujeme jeden po druhém, počínaje zadáním příkazu Vyhledávací_hodnota, což je v tomto případě celkový prodej z buňky B1. Kurzor umístíme do okna Vyhledávací_hodnota pole a potom jednou klikněte na buňku B1:
Dále musíme specifikovat, do VLOOKUPu, jakou tabulku vyhledáváme tato data. V tomto příkladu je samozřejmě tabulka sazby. Kurzor umístíme do okna Table_array pole a pak zvýrazněte 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 míru provize, která se nachází v druhém sloupci tabulky, a proto zadáme hodnotu a 2 do Col_index_num pole:
Nakonec zadáme hodnotu v Range_lookup pole.
Důležité: Použití tohoto pole odlišuje dva způsoby použití VLOOKUP. Chcete-li použít VLOOKUP s databází, tento konečný parametr, Range_lookup, musí být vždy nastaveno na NEPRAVDIVÉ, ale s tímto dalším použitím VLOOKUPu musíme buď nechat prázdné nebo zadat hodnotu SKUTEČNÝ. Pokud používáte VLOOKUP, je důležité, abyste správně zvolili tento konečný parametr.
Abychom byli explicitní, zadáme hodnotu skutečný v Range_lookup pole. Také by bylo dobré nechat to prázdné, protože je to výchozí hodnota:
Dokončili jsme všechny parametry. Nyní klikněte na tlačítko OK a Excel sestaví náš vzorec VLOOKUP pro nás:
Pokud experimentujeme s několika různými prodejními částkami, můžeme se ujistit, že vzorec funguje.
Závěr
V "databázi" verze VLOOKUP, kde Range_lookup parametr je NEPRAVDIVÉ, hodnota předaná v prvním parametru (Vyhledávací_hodnota). musí být v databázi. Jinými slovy, hledáme přesnou shodu.
Ale v tomto dalším použití VLOOKUPu nejsme nutně hledá přesný zápas. 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 za prodejní částku 34.988 dolarů nám náš vzorec VLOOKUP vrátí hodnotu 30%, což je správná odpověď. Proč zvolil řádek v tabulce obsahující 30%? Co ve skutečnosti znamená v tomto případě "dostatečně blízko"? Buďme přesní:
Když Range_lookup je nastaveno na SKUTEČNÝ (nebo vynechána), VLOOKUP bude vypadat ve sloupci 1 a bude odpovídat nejvyšší hodnota, která není větší než Vyhledávací_hodnota parametr.
Důležité je také poznamenat, že tento systém funguje, tabulka musí být ve sloupci 1 seřazeny ve vzestupném pořadí!
Pokud chcete pracovat s VLOOKUP, ukázkový soubor ilustrovaný v tomto článku si můžete stáhnout zde.
