VLOOKUP v Excelu, část 2: Použití VLOOKUP bez databáze

Video: VLOOKUP v Excelu, část 2: Použití VLOOKUP bez databáze

Video: VLOOKUP v Excelu, část 2: Použití VLOOKUP bez databáze
Video: Google Alerts RSS feed trick 2024, Březen
VLOOKUP v Excelu, část 2: Použití VLOOKUP bez databáze
VLOOKUP v Excelu, část 2: Použití VLOOKUP bez databáze
Anonim

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 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 získává VLOOKUP "jedinečný identifikátor", který slouží k identifikaci záznamu, který chceme v databázi najít (např. Kód produktu nebo ID 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 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í 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č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%. 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).
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 jedinou 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:

IF(condition,value if true,value if false)

Kde stav je výraz, který hodnotí buď skutečný nebo Nepravdivé. Ve výše uvedeném příkladu stav je výraz B1, který lze přečíst jako "Je B1 méně než B5?", nebo jiným způsobem, "Jsou celkové prodeje nižší než práh". Pokud je odpověď na tuto otázku "ano" (pravda), pak použijeme pokud je pravda parametr funkce, jmenovitě B6 v tomto případě - sazba provize, pokud byl celkový prodej níže prahu. Pokud je odpověď na otázku "ne" (nepravda), pak použijeme hodnota, pokud je falešná parametr funkce, jmenovitě B7 v tomto případě - sazba provize, pokud byl celkový prodej výše prahu.

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.
Takže náš tabulkový procesor funguje.

Zkusme to složitější. Představme druhou prahovou hodnotu: Pokud obchodník vydělá více než 40 000 USD, jejich sazba provize se zvýší na 40%:

Image
Image

Snadno pochopitelné v reálném světě, ale v buňce B2 je náš vzorec stále složitější. Pokud se podíváte pozorně na vzorec, uvidíte, že třetí parametr původní funkce IF ( hodnota, pokud je falešná) je nyní celá IF funkce sama o sobě. Toto se nazývá a vnořené funkce (funkce v rámci 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 výukový program pro VLOOKUP, nikoliv pro 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é na čtyřech úrovních! Určitě musí existovat jednodušší cesta?
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é na čtyřech úrovních! Určitě musí existovat jednodušší cesta?

Určitě je. VLOOKUP na záchranu!

Přeměňme si list trochu. Budeme uchovávat všechny stejné postavy, ale organizovat je novým způsobem, více tabelární způsob:

Image
Image

Udělejte si chvíli a ověřte si, že nový Tabulka hodnot pracuje přesně jako série výše uvedených prahových hodnot.

Koncepčně, to, co se chystáme udělat, je použít VLOOKUP, aby vyhledal celkový prodejní prodejce (od B1) v tabulce sazeb a vrátil 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). Mohli 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žíme funkci VLOOKUP z Vzorce záložka:

Image
Image

The Funkční argumenty Zobrazí se pole 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:

Image
Image

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 sazby - s výjimkou okruhů:

Image
Image

Dále musíme určit, který sloupec v tabulce obsahuje informace, které chceme, aby se náš vzorec vrátil k nám. V tomto případě chceme míru provize, která se nachází ve druhém sloupci tabulky, a proto zadáme a 2 do Col_index_num pole:

Image
Image

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í, Range_lookup, musí být vždy nastavena na NEPRAVDIVÉ, ale s tímto dalším používáním VLOOKUPu musíme buď nechat prázdné nebo zadat hodnotu SKUTEČNÝ. Při použití funkce 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:

Image
Image

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.
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 nemusíme nutně hledat 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"? Buďme přesní:

When Range_lookup is set to TRUE (or omitted), VLOOKUP will look in column 1 and match the highest value that is not greater than the Lookup_value parameter.

Je také důležité poznamenat, že pro tento systém fungovat, 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.

Doporučuje: