VLOOKUP v Exceli, časť 2 Použitie VLOOKUP bez databázy
V nedávnom článku sme predstavili funkciu programu Excel nazvanú VLOOKUP a vysvetlil, ako sa dá použiť na získavanie informácií z databázy do bunky v miestnom pracovnom hárku. V tomto článku sme uviedli, že pre VLOOKUP existovali dve spôsoby použitia a iba jedna z nich sa zaoberala dotazovaním databáz. V tomto článku, druhom a poslednom z radu VLOOKUP, skúmame toto iné menej známe použitie funkcie VLOOKUP.
Ak ste tak ešte neurobili, prečítajte si prvý článok VLOOKUP - tento článok predpokladá, že mnohé pojmy vysvetlené v tomto článku sú už čitateľovi známe.
Pri práci s databázami je VLOOKUP odovzdaný "jedinečný identifikátor", ktorý slúži na identifikáciu záznamu, ktorý chceme nájsť v databáze (napr. Kód produktu alebo ID zákazníka). Tento jedinečný identifikátor musieť existujú v databáze, inak VLOOKUP nám vráti chybu. V tomto článku budeme skúmať spôsob používania VLOOKUP, kde identifikátor v databáze vôbec nemusí existovať. Je to takmer tak, akoby VLOOKUP mohol prijať prístup "dosť blízko a je dosť dobrý" na vrátenie údajov, ktoré hľadáme. Za určitých okolností to je presne čo potrebujeme.
Tento článok ilustrujeme príkladom skutočného sveta - výpočtom provízií, ktoré sa vytvárajú na základe množstva údajov o predaji. Začnime s veľmi jednoduchým scenárom a postupne ho zjednodušíme, až kým jediným racionálnym riešením problému nie je použitie VLOOKUP. Počiatočný scenár v našej fiktívnej spoločnosti funguje takto: Ak predajca v danom roku vytvorí viac ako 30 000 dolárov v hodnote predaja, provízia, ktorú zarobí z tohto predaja, je 30%. V opačnom prípade je ich provízia len 20%. Zatiaľ je to dosť jednoduchý pracovný hárok:
Na použitie tohto pracovného listu predajca zadá svoje údaje o predaji v bunke B1 a vzorec v bunke B2 vypočíta správnu sadzbu provízie, ktorú sú oprávnení prijímať a ktorá sa používa v bunke B3 na výpočet celkovej provízie, ktorú dlhuje predávajúci (čo je jednoduché násobenie B1 a B2).
Bunka B2 obsahuje iba zaujímavú časť tohto pracovného listu - vzorec, ktorý rozhoduje o tom, ktorá sadzba provízií sa má použiť: ten nižšie hranicu 30 000 dolárov alebo jednu vyššie prahu. Tento vzorec využíva funkciu programu Excel nazvanú IF. Pre tých čitateľov, ktorí nie sú oboznámení s IF, funguje takto:
IF (stav, hodnota, ak je pravda, hodnota, ak je nepravdivá)
Kde podmienka je výraz, ktorý sa hodnotí buď pravdivý alebo nepravdivý. Vo vyššie uvedenom príklade podmienka je výraz B1
Ako vidíte, pri použití celkovej sumy predaja vo výške 20 000 USD nám v bunke B2 vzniká provízia vo výške 20%. Ak zadáme hodnotu 40 000 USD, získame inú sadzbu provízií:
Takže naša tabuľka funguje.
Zjednodušte to. Predstavme druhú prahovú hodnotu: ak predajca zarobí viac ako 40 000 USD, potom sa sadzba provízie zvýši na 40%:
Jednoduché na pochopenie v reálnom svete, ale v bunke B2 sa náš vzorec stáva zložitejším. Ak sa pozriete pozorne na vzorec, uvidíte, že tretí parameter pôvodnej funkcie IF ( hodnota, ak je nepravdivá) je teraz celá IF funkcia sama osebe. Toto sa nazýva a vnorená funkcia (funkcia v rámci funkcie). Je to úplne platné v programe Excel (dokonca funguje!), Ale je ťažšie čítať a chápať.
Nebudeme ísť do matice a skrutiek toho, ako a prečo to funguje, a ani nebudeme skúmať nuansy vnorených funkcií. Toto je výukový program pre VLOOKUP, nie pre aplikáciu Excel vo všeobecnosti.
Každopádne sa to zhoršuje! A čo keď sa rozhodneme, že ak získajú viac ako 50 000 dolárov, majú nárok na 50% províziu a ak získajú viac ako 60 000 dolárov, majú nárok na províziu vo výške 60%?
Teraz vzorec v bunke B2, zatiaľ čo je správny, sa stal prakticky nečitateľným. Nikto by nemal písať vzorce, kde sú funkcie vnorené do štyroch úrovní! Určite musí existovať jednoduchšia cesta?
Určite je. VLOOKUP na záchranu!
Poďme trochu prepracovať pracovný hárok. Zachováme všetky rovnaké čísla, ale usporiadame to novým spôsobom a viac tabuľkový spôsobom:
Venujte chvíľku a overte si, že nový Hodnotiaca tabuľka pracuje presne rovnako ako vyššie uvedené série prahových hodnôt.
Koncepčne, to, čo sa chystáme urobiť, je použiť VLOOKUP na vyhľadanie predajného predaja celkom (z B1) v tabuľke sadzieb a vráťte nám zodpovedajúcu sadzbu provízie. Upozorňujeme, že predajca pravdepodobne vytvoril predaj, ktorý je nie jednu z piatich hodnôt v tabuľke sadzieb ($ 0, $ 30,000, $ 40,000, $ 50,000 alebo $ 60,000). Môžu vytvoriť tržby vo výške 34.988 USD. Je dôležité poznamenať, že 34.988 dolárov robí nie v tabuľke sadzieb. Pozrime sa, či VLOOKUP môže vyriešiť náš problém ...
Vyberieme bunku B2 (miesto, kde chceme vložiť náš vzorec) a potom vložíme funkciu VLOOKUP z vzorca Záložka:
Funkčné argumenty box pre VLOOKUP. Argumenty (parametre) vyplňujeme jeden po druhom, počnúc od lookup_value, čo je v tomto prípade celkový predaj z bunky B1. Kurzor umiestnime do okna lookup_value a kliknite raz na bunku B1:
Ďalej je potrebné špecifikovať, do VLOOKUP, akú tabuľku vyhľadáme v týchto údajoch. V tomto príklade je samozrejme tabuľka sadzieb. Kurzor umiestnime do okna table_array a potom zvýraznite celú tabuľku sadzieb - s výnimkou nadpisov:
Ďalej musíme určiť, ktorý stĺpec v tabuľke obsahuje informácie, ktoré chceme, aby sa náš vzorec vrátil k nám. V tomto prípade chceme sadzbu provízie, ktorá sa nachádza v druhom stĺpci tabuľky, a preto zadáme hodnotu a 2 do Col_index_num lúka:
Nakoniec zadáme hodnotu v argument typ lúka.
Dôležité: Použitie tohto poľa odlišuje dva spôsoby použitia VLOOKUP. Ak chcete použiť VLOOKUP s databázou, tento konečný parameter, argument typ, musí byť vždy nastavené FALSE, ale s týmto ďalším použitím VLOOKUP, musíme buď nechať prázdne alebo zadajte hodnotu TRUE. Pri používaní VLOOKUP je dôležité, aby ste urobili správnu voľbu pre tento konečný parameter.
Aby sme boli explicitní, zadáme hodnotu pravdivý v argument typ lúka. Rovnako by bolo dobré nechať to prázdne, pretože je to predvolená hodnota:
Vykonali sme všetky parametre. Teraz kliknite na tlačidlo OK a Excel vytvorí pre nás náš vzorec VLOOKUP:
Ak experimentujeme s niekoľkými rôznymi predajnými celkovými sumami, môžeme sa uspokojiť s tým, že vzorec funguje.
záver
V "databázovej" verzii VLOOKUP, kde argument typ parameter je FALSE, hodnota prešla v prvom parametri (lookup_value) musieť byť v databáze. Inými slovami, hľadáme presnú zhodu.
Ale pri tomto ďalšom používaní VLOOKUPu nemusíme nutne hľadať presnú zhodu. V tomto prípade je "dostatočne blízko dosť dobrý". Čo však myslíme "dosť blízko"? Použite príklad: Pri vyhľadávaní sadzby provízie z predaja vo výške 34.988 dolárov nám náš vzorec VLOOKUP vráti hodnotu 30%, čo je správna odpoveď. Prečo si zvolil riadok v tabuľke obsahujúci 30%? Čo v skutočnosti znamená "dosť blízko" v tomto prípade? Buďme presní:
Kedy argument typ je nastavené na TRUE (alebo vynechané), VLOOKUP bude vyzerať v stĺpci 1 a bude sa zhodovať najvyššiu hodnotu, ktorá nie je väčšia lookup_value parameter.
Je tiež dôležité poznamenať, že tento systém funguje, tabuľka musí byť zoradená vo vzostupnom poradí v stĺpci 1!
Ak by ste chceli pracovať s VLOOKUP, ukážkový súbor ilustrovaný v tomto článku si môžete stiahnuť tu.
