Ako používať VLOOKUP v programe Excel

VLOOKUP je jedným z najužitočnejších funkcií programu Excel a je tiež jedným z najmenej rozoznaných. V tomto článku demystifikujeme VLOOKUP ako skutočný príklad. Vytvoríme použiteľnú šablónu faktúry pre fiktívnu spoločnosť.

VLOOKUP je funkcia Excel .V tomto článku sa predpokladá, že čitateľ má predbežné pochopenie funkcií aplikácie Excel a môže používať základné funkcie ako SUM, AVERAGE a TODAY.Vo svojom najčastejším používaní je VLOOKUP databázovou funkciou , čo znamená, že pracuje s databázovými tabuľkami - alebo jednoducho, uvádza zoznam vecí v pracovnom hárku programu Excel. Aké veci? No, akákoľvek vec .Môžete mať pracovný hárok, ktorý obsahuje zoznam zamestnancov, produktov, zákazníkov alebo CD v zbierke CD alebo hviezdy na nočnej oblohe. Nezáleží na tom.

Tu je príklad zoznamu alebo databázy. V tomto prípade je to zoznam produktov, ktoré naša fiktívna firma predáva:

Zvyčajne takto vymenované zoznamy obsahujú nejaký jedinečný identifikátor pre každú položku v zozname. V tomto prípade je jedinečný identifikátor v stĺpci "Kód položky".Poznámka: Ak má funkcia VLOOKUP pracovať s databázou / zoznamom, tento zoznam

musí mať stĺpec obsahujúci jedinečný identifikátor( alebo "kľúč" alebo "ID") a tento stĺpec musí byť prvý stĺpec v tabuľke.Vzorová databáza uvedená vyššie spĺňa toto kritérium.

Najťažšia časť používania VLOOKUPu je presne pochopenie toho, prečo je. Takže poďme sa pozrieť, či môžeme získať to jasné ako prvé:

VLOOKUP načíta informácie z databázy / zoznamu na základe dodanej inštancie jedinečného identifikátora.

Vo vyššie uvedenom príklade by ste vložili funkciu VLOOKUP do inej tabuľky s kódom položky a vrátili by ste buď popis príslušnej položky, jej cenu alebo jej dostupnosť( jej množstvo v sklade), ako je to popísané vo vašompôvodný zoznam. Ktoré z týchto informácií vás odovzdá späť?No, toto sa rozhodnete pri vytváraní vzorca.

Ak je všetko, čo potrebujete, je jedna informácia z databázy, bolo by veľa problémov ísť na vytvorenie vzorca s funkciou VLOOKUP v nej. Typicky by ste používali tento typ funkcií v tabuľke s opakovane použiteľným použitím, napríklad v šablóne. Zakaždým, keď niekto zadá platný kód položky, systém získa všetky potrebné informácie o príslušnej položke.

Vytvorme si príklad toho: šablónu faktúry , že môžeme opakovane používať v našej fiktívnej spoločnosti.

Najprv začneme program Excel a vytvoríme si prázdnu faktúru:

Takto to funguje: Osoba, ktorá používa šablónu faktúry, vyplní sériu kódov položiek v stĺpci "A" a systém načíta všetky položkyopis a cenu z našej databázy produktov. Tieto informácie sa použijú na výpočet celkového počtu riadkov pre každú položku( za predpokladu, že zadáme platné množstvo).

Na účely zachovania tohto príkladu jednoduchú databázu produktov nájdeme na samostatnom hárku v rovnakom zošite:

V skutočnosti je pravdepodobnejšie, že databáza produktov bude umiestnená v samostatnom zošite. Funkcia VLOOKUP má veľký rozdiel, čo naozaj nezáleží na tom, či je databáza umiestnená na rovnakom hárku, inom hárku alebo úplne inom zošite.

Takže sme vytvorili našu databázu produktov, ktorá vyzerá takto:

Aby sme mohli otestovať vzorec VLOOKUP, ktorý sa chystáme napísať, najprv zadáme platný kód položky do bunky A11 našej prázdnej faktúry:

Ďalej smepresuňte aktívnu bunku do bunky, v ktorej chceme uložiť informácie získané z databázy pomocou VLOOKUP.Je zaujímavé, že je to krok, ktorý väčšina ľudí nesprávne.Ďalšie vysvetlenie: Chystáme sa vytvoriť vzorec VLOOKUP, ktorý načíta opis, ktorý zodpovedá kódu položky v bunke A11.Kde chceme, aby sa tento popis dal, keď ho dostaneme? V bunke B11 samozrejme. Takže tam píšeme VLOOKUP vzorec: v bunke B11.Vyberte bunku B11.

Musíme nájsť zoznam všetkých dostupných funkcií, ktoré ponúka Excel, aby sme mohli vybrať VLOOKUP a získať pomoc pri vyplňovaní vzorca. To sa zistilo po prvom kliknutí na vzorce kartu a potom kliknutím na Vložiť funkciu :

Zobrazí sa okno, ktoré nám umožňuje vybrať niektorú z funkcií dostupných v programe Excel.

Na nájdenie toho, čo hľadáme, môžeme napísať hľadaný výraz ako "vyhľadávanie"( pretože funkcia, o ktorú nás zaujíma, je funkcia vyhľadávania ).Systém nám vráti zoznam všetkých funkcií súvisiacich s vyhľadávaním v programe Excel. VLOOKUP je druhý v zozname. Vyberte ho kliknutím na tlačidlo OK .

Položka Argumenty sa zobrazí, čo nás vyzýva k sprístupneniu všetkých argumentov ( alebo parametrov ) potrebných na dokončenie funkcie VLOOKUP.Môžete si predstaviť toto pole ako funkciu, v ktorej sa opýtate na nasledujúce otázky:

1. Aký jedinečný identifikátor hľadáte v databáze?
2. Kde je databáza?
3. Ktoré informácie z databázy spojené s jedinečným identifikátorom chcete získať pre vás?

Prvé tri argumenty sú zobrazené tučným písmom , čo naznačuje, že sú povinné argumenty( funkcia VLOOKUP je bez nich úplná a nevráti platnú hodnotu).Štvrtý argument nie je tučný, čo znamená, že je nepovinné:

Dokončíme argumenty v poradí, zhora nadol.

Prvý argument, ktorý musíme dokončiť, je argument Lookup_value .Funkcia nás musí informovať, kde nájsť jedinečný identifikátor( kód položky v tomto prípade), že by mal vrátiť opis. Musíme vybrať kód položky, ktorý sme zadali skôr( v A11).

Kliknite na ikonu výberu napravo od prvého argumentu:

Potom raz kliknite na bunku obsahujúcu kód položky( A11) a stlačte tlačidlo Zadajte :

Hodnota "A11" sa vloží do prvého argumentu.

Teraz musíme zadať hodnotu pre parameter Table_array .Inými slovami, musíme VLOOKUP povedať, kde nájsť databázu / zoznam. Kliknite na ikonu výberu vedľa druhého argumentu:

Teraz vyhľadajte databázu / zoznam a vyberte celý zoznam - neobsahuje riadok hlavičky .V našom príklade je databáza umiestnená na samostatnom pracovnom hárku, takže prvýkrát klikneme na kartu pracovného hárka:

Ďalej vyberieme celú databázu bez riadku záhlavia:

. .. a stlačte Zadajte .Rozsah buniek, ktoré reprezentujú databázu( v tomto prípade "Databáza produktov"! A2: D7 ") sa zadá automaticky pre nás do druhého argumentu.

Teraz musíme zadať tretí argument, Col_index_num .Tento argument používame na to, aby sme špecifikovali, do VLOOKUP, akú informáciu z databázy spájame s kódom položky v A11, chceli by sme sa nám vrátili. V tomto konkrétnom príklade by sme chceli vrátiť náš popis .Ak sa pozriete na pracovný hárok databázy, všimnete si, že stĺpec "Popis" je druhý stĺpec v databáze. To znamená, že do poľa Col_index_num musíme zadať hodnotu "2":

Je dôležité poznamenať, že tu nezadávame "2", pretože stĺpec "Popis" je v stĺpci B napracovný hárok. Ak sa databáza stala spustená v stĺpci K pracovného hárka, stále by sme zadali hodnotu "2" v tomto poli, pretože stĺpec "Popis" je druhý stĺpec v množine buniek, ktoré sme vybrali pri zadávaní "Table_array".

Nakoniec sa musíme rozhodnúť, či zadáme hodnotu do záverečného argumentu VLOOKUP, Range_lookup .Tento argument vyžaduje buď hodnotu pravdivú alebo false alebo by mala zostať prázdna. Pri použití VLOOKUPu s databázami( ako je to pravda 90% času), spôsob, ako sa rozhodnúť, čo vložiť do tohto argumentu, možno považovať za nasledujúce:

Ak prvý stĺpec databázy( stĺpec, ktorý obsahuje jedinečné identifikátory)je zoradený abecedne / číselne vo vzostupnom poradí, potom je možné do tohto argumentu zadať hodnotu true alebo nechať prázdne.

Ak je prvý stĺpec databázy nie zoradený alebo je zoradený v zostupnom poradí, potom musí zadať hodnotu false do tohto argumentu

Keďže prvý stĺpec našej databázy je nie zoradený,zadáme false do tohto argumentu:

To je všetko! Zadali sme všetky informácie požadované pre VLOOKUP na vrátenie hodnoty, ktorú potrebujeme. Kliknite na tlačidlo OK a všimnite si, že popis zodpovedajúci kódu položky "R99245" bol správne vložený do bunky B11:

Vzor vytvorený pre nás vyzerá takto:

Ak zadáme iný kód do bunkyA11, začneme vidieť silu funkcie VLOOKUP: Popis buniek sa zmení tak, aby zodpovedali novému kódu položky:

Môžeme vykonať podobnú sadu krokov, aby sme získali cenu položky vrátenú do bunky E11.Všimnite si, že nový vzorec musí byť vytvorený v bunke E11.Výsledok bude vyzerať nasledovne:

. .. a vzorec bude vyzerať nasledovne:

Všimnite si, že jediný rozdiel medzi dvomi vzorcami je tretí argument( Col_index_num ) sa zmenil z "2" na "3"( pretožechceme získať údaje z tretieho stĺpca v databáze).

Ak sme sa rozhodli kúpiť 2 z týchto položiek, zadáme do bunky D11 "2".Potom by sme zadali jednoduchý vzorec do bunky F11, aby sme získali celkový počet riadkov:

= D11 * E11

. .. ktorý vyzerá takto. ..

Dokončenie šablóny faktúry

Zatiaľ sme sa o VLOOKUPe veľa naučili. V skutočnosti sme sa naučili všetko, čo sa naučíme v tomto článku. Je dôležité poznamenať, že VLOOKUP môže byť použitý aj za iných okolností okrem databáz. To je menej obvyklé a môže byť pokryté v budúcich článkoch How-To Geek.

Šablóna faktúry zatiaľ nie je dokončená.Aby sme to dokončili, urobili by sme nasledovné:

1. Odstránili sme vzorový kód položky z bunky A11 a "2" z bunky D11.To spôsobí, že naše novovytvorené vzorce VLOOKUP zobrazujú chybové hlásenia:
   
   Toto môžeme odstrániť pomocou uvážlivého používania IF() a funkcií IF() .Zmena nášho vzorca z tohto. .. = VLOOKUP( A11, 'Databáza produktov'! A2: D7,2, FALSE) . .. na toto. .. = IF( IS11LANK( A11), VLOOKUP'! A2: D7,2, FALSE))
   
2. Skopírujeme vzorce do buniek B11, E11 a F11 do zvyšku riadkov položiek faktúry. Všimnite si, že ak to urobíme, výsledné vzorce už nespĺňajú správne tabuľku databáz. Mohli by sme to vyriešiť zmenou odkazov na bunky pre databázu na absolútne referencie buniek .Alternatívne - a ešte lepšie - by sme mohli vytvoriť názov rady pre celú databázu produktov( napríklad "Produkty") a použiť tento názov rozsahu namiesto odkazov na bunky. Vzorec by sa zmenil z tohto. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, 'Databáza produktov' A2: D7,2, FALSE)) . .. na toto. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, Products, 2, FALSE)) . .. a potom skopírujte vzorce do zvyšných riadkov položiek faktúry.
3. Pravdepodobne by sme zablokovali bunky, ktoré obsahujú naše vzorce( alebo skôr odomknúť iné bunky ), a potom chrániť pracovný hárok, aby sme zaistili, že naše starostlivo zostavené vzorce nie sú náhodne prepísané, keď niekto príde na vyplnenievo faktúre.
4. Uložili by sme súbor ako šablónu , takže by ho mohli znovu použiť všetci v našej spoločnosti

Ak by sme cítili skutočne šikovný, vytvorili by sme databázu všetkých našich zákazníkov v inom pracovnom hárku a potom použiliidentifikátor zákazníka zadaný v bunke F5 automaticky vyplní meno a adresu zákazníka v bunkách B6, B7 a B8.

Ak chcete pracovať s VLOOKUP, alebo jednoducho vidieť našu výslednú šablónu faktúry, môžete ju stiahnuť tu.