1Aug
V nedávnom článku sme predstavili funkciu programu Excel nazvanú VLOOKUP a vysvetlili, ako ju možno použiť na načítanie 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 predpokladá, ž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, ktorý chceme nájsť v databáze( napr. Kód produktu alebo ID zákazníka).Tento jedinečný identifikátor musí mať 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í je to
presne , čo potrebujeme.Tento článok ilustrujeme príkladom skutočného sveta - výpočtom provízií, ktoré sa vytvárajú v súbore čísel predaja. Začnime 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%.Doteraz je to dosť jednoduchý pracovný hárok:
Na používanie tohto pracovného listu predajca zadá svoje údaje o predaji v bunke B1 a vzorec v bunke B2 vypočíta správnu mieru provízie, ktorú sú oprávnení prijímať, čo sa používa v bunke B3 na výpočetcelková provízia, ktorú dlhuje predávajúci( čo je jednoduché násobenie B1 a B2).
Bunka B2 obsahuje iba zaujímavú časť tohto pracovného hárka - vzorec na určenie miery komisie, ktorá sa má použiť: jedna pod prahom 30 000 USD alebo jedna nad prahovou hodnotou .Tento vzorec využíva funkciu programu Excel nazývanú IF .Pre tých čitateľov, ktorí nie sú oboznámení s IF, funguje to takto:
IF( podmienka , hodnota true, hodnota if false )
Kde podmienka je výraz, ktorý vyhodnocuje alebo true alebo false ,Vo vyššie uvedenom príklade je podmienka výrazom B1
Ako vidíte, pri použití celkových tržieb 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 náš tabuľkový procesor funguje.
Urobíme to zložitejšie. Poďme si predstaviť druhú hranicu: Ak predajca zarobí viac ako 40 000 dolárov, potom sa ich provízia zvýši na 40%:
Jednoduché, aby sme pochopili v reálnom svete, ale v bunke B2 sa náš vzorec stáva zložitejším. Ak sa pozriete na vzorec, uvidíte, že tretí parameter pôvodnej funkcie IF( hodnota , ak je falošná ), je teraz celá IF funkcia sama osebe. Toto sa nazýva 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, ani skúmame 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 trochu prepracovať pracovný hárok. Udržiavame všetky rovnaké čísla, ale usporiadame to novým spôsobom, viac tabuľky ako :
Chvíľu si overte, či nová tabuľka Rate funguje presne rovnako ako vyššie uvedené série prahov.
Koncepčne, to, čo sa chystáme urobiť, je použiť VLOOKUP, aby vyhľadal celkový predaj predajcu( z B1) v tabuľke sadzieb a vráťte nám zodpovedajúcu sadzbu provízie. Upozorňujeme, že predajca mohol skutočne vytvoriť tržby, ktoré sú nie jedna 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 nezobrazuje ani v tabuľke sadzieb. Pozrime sa, či VLOOKUP môže vyriešiť náš problém. ..
Vyberieme bunku B2( miesto, v ktorom chceme vložiť náš vzorec) a potom vložíme funkciu VLOOKUP z karty Formuláre :
Zobrazí sa okno Argument Argumenty pre VLOOKUP.Argumenty( parametre) vyplňujeme jeden po druhom, začínajúc hodnotou Lookup_value , čo je v tomto prípade celkový obrat z bunky B1.Umiestnime kurzor do poľa Lookup_value a potom raz kliknite na bunku B1:
Ďalej musíme špecifikovať VLOOKUP, akú tabuľku vyhľadáme v týchto dátach. V tomto príklade je to samozrejme tabuľka sadzieb. Umiestnime kurzor do poľa Table_array a potom zvýraznite celú tabuľku sadzieb - s výnimkou hlavičiek :
Ďalej musíme zadať, 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 do poľa Col_index_num zadáme 2 :
Nakoniec zadáme hodnotu do poľa Range_lookup .
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, Range_lookup , 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í, do poľa Range_lookup zadáme hodnotu true .Rovnako by bolo dobré nechať to prázdne, pretože je to predvolená hodnota:
Vykonali sme všetky parametre. Teraz klikneme na tlačidlo OK a program Excel vytvorí pre nás náš vzorec VLOOKUP:
Ak experimentujeme s niekoľkými rôznymi predajnými celkovými sumami, môžeme sa ubezpečiť, že vzorec funguje.
Záver
Vo verzii VLOOKUP "databázy", kde parameter Range_lookup je FALSE , hodnota prešla v prvom parametri( Lookup_value ) musí byť prítomná v databáze. Inými slovami, hľadáme presnú zhodu.
Ale v 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? Presné:
Keď je parameter Range_lookup nastavený na hodnotu TRUE ( alebo vynechané), VLOOKUP bude v stĺpci 1 vyzerať a zodpovedá najvyššej hodnote, ktorá nie je väčšia ako parameter Lookup_value .
Je tiež dôležité poznamenať, že pre tento systém funguje tabuľka musí byť zoradená vo vzostupnom poradí na stĺpci 1 !
Ak chcete pracovať s VLOOKUP, ukážkový súbor ilustrovaný v tomto článku si môžete stiahnuť tu.