V nedávnom článku sme predstavili funkciu programu Excel nazvanú VLOOKUP a vysvetlil, ako sa dá použiť na získanie 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, druhý a posledný v sérii VLOOKUP, skúmame toto iné, menej známe použitie pre funkciu VLOOKUP.
Ak ste tak ešte neurobili, prečítajte si prvý článok VLOOKUP - tento článok bude predpokladať, že mnohé pojmy vysvetlené v tomto článku sú čitateľovi už známe.
Pri práci s databázami je VLOOKUP odovzdaný "jedinečný identifikátor", ktorý slúži na identifikáciu záznamu údajov, 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 nemusí v databáze vôbec existovať. Je to takmer 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 budeme ilustrovať príkladom skutočného sveta - výpočtom provízií, ktoré sa vytvárajú na základe množiny údajov o predaji. Začneme s veľmi jednoduchým scenárom a postupne ho zjednodušíme, 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ú získajú z tohto predaja, je 30%. V opačnom prípade je ich provízia len 20%. Zatiaľ je to dosť jednoduchý pracovný hárok:
Bunka B2 obsahuje iba zaujímavú časť tohto pracovného hárka - vzorec, ktorý rozhoduje o tom, ktorá sadzba provízií sa má použiť: tá, ktorá sa má použiť 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(condition,value if true,value if false)
Kde podmienka je výraz, ktorý sa hodnotí buď pravdivý alebo nepravdivý, Vo vyššie uvedenom príklade podmienka je výraz B1, ktorý možno prečítať ako "Is B1 menej ako B5?", alebo inak povedané: "Sú celkové tržby nižšie ako prahová hodnota". Ak je odpoveď na túto otázku "áno" (pravda), potom použijeme ak je pravda parameter funkcie, menovite B6 v tomto prípade - miera provízie, ak bola celková suma predaja nižšie prahu. Ak je odpoveď na otázku "nie" (falošná), potom použijeme hodnota, ak je nepravdivá parameter funkcie, menovite B7 v tomto prípade - miera provízie, ak bola celková suma predaja vyššie prahu.
Ako vidíte, použitím 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í:
Zjednodušte to. Predstavme si druhú hranicu: 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 návod na VLOOKUP, nie v programe 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í! Iste musí existovať jednoduchšia cesta?
Určite je. VLOOKUP na záchranu!
Poďme redesign listu trochu. Zachováme všetky rovnaké čísla, ale organizujeme ich 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 chceme urobiť, je použiť VLOOKUP na vyhľadanie predajného predaja celkom (z B1) v tabuľke sadzieb a vrátiť 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 dať 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, začínajúc lookup_value, čo je v tomto prípade celkový predaj z bunky B1. Umiestnime kurzor do poľa lookup_value a kliknite raz na bunku B1:
Ďalej je potrebné špecifikovať do VLOOKUPu akú tabuľku vyhľadávame tieto údaje. V tomto príklade je samozrejme tabuľka sadzieb. Umiestnime kurzor do poľa table_array a potom zvýraznite celú tabuľku sadzieb - s výnimkou položiek:
Ď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 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á na FALSE, ale s týmto ďalším použitím VLOOKUP, musíme buď nechať prázdne alebo zadajte hodnotu TRUE, Ak používate VLOOKUP, je nevyhnutné, 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 vytvára náš vzorec VLOOKUP pre nás:
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í:
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 tiež dôležité poznamenať, že pre tento systém pracovať, tabuľka sa musí zoradiť vo vzostupnom poradí v stĺpci 1!
Ak by ste chceli pracovať s VLOOKUP, vzorový súbor ilustrovaný v tomto článku si môžete stiahnuť tu.
