Jak používat VLOOKUP v aplikaci Excel

VLOOKUP je jedna z nejužitečnějších funkcí aplikace Excel a je také jedním z nejméně pochopitelných. V tomto článku demystifikujeme VLOOKUP jako příklad z reálného života. Vytvoříme použitelnou šablonu faktury pro fiktivní společnost.

VLOOKUP je funkce aplikace Excel .Tento článek předpokládá, že čtenář již má absolutní znalost funkcí aplikace Excel a může používat základní funkce jako SUM, AVERAGE a TODAY.V nejběžnějším použití je VLOOKUP databáze funkce, což znamená, že pracuje s databázovými tabulkami - nebo jednodušeji, uvádí seznam věcí v listu aplikace Excel. Jaké věci? No, nějaké věci .Můžete mít pracovní list, který obsahuje seznam zaměstnanců, produktů, zákazníků nebo CD ve sbírce CD nebo hvězdy na noční obloze. To na tom nezáleží.

Zde je příklad seznamu nebo databáze. V tomto případě je to seznam produktů, které naše fiktivní firma prodává:

Obvykle jsou takto seznamovány nějaké jedinečné identifikátory pro každou položku v seznamu. V tomto případě je jedinečný identifikátor ve sloupci "Kód položky".Poznámka: Pro funkci VLOOKUP pro práci s databází / seznamem musí tento seznam

obsahovat sloupec obsahující jedinečný identifikátor( nebo "klíč" nebo "ID") a tento sloupec musí být první sloupec v tabulce.Naše vzorová databáze výše splňuje toto kritérium.

Nejtěžší částí použití VLOOKUP je přesné pochopení, proč je. Takže se podíváme, zda nejdřív dostaneme to jasně:

VLOOKUP načte informace z databáze / seznamu na základě dodané instance jedinečného identifikátoru.

Ve výše uvedeném příkladu vložte funkci VLOOKUP do jiné tabulky s kódem položky a vrátí se vám buď popis příslušné položky, její cena nebo její dostupnost( její množství "Skladem"), jak je popsáno ve vašempůvodním seznamu. Které z těchto informací vás povede zpět? No, můžete se rozhodnout, když vytváříte vzorec.

Pokud vše, co potřebujete, je pouze jedna informace z databáze, bylo by spousta potíží jít na to, abyste vytvořili vzorec s funkcí VLOOKUP.Obvykle byste použili tento druh funkcí v opakovaně použitelné tabulce, například v šabloně.Pokaždé, když někdo zadá platný kód položky, systém načte všechny potřebné informace o odpovídající položce.

Pojďme si vytvořit příklad tohoto: šablony faktury , že můžeme znovu a znovu v naší fiktivní společnosti.

Nejdříve spustíme aplikaci Excel a vytvoříme si prázdnou fakturu:

Takhle to funguje: Osoba, která používá šablonu faktury, vyplní řadu kódů položek ve sloupci "A" a systém načte všechny položkypopis a cenu z naší databáze produktů.Tyto informace budou použity k výpočtu celkového počtu řádků pro každou položku( za předpokladu, že zadáme platné množství).

Za účelem zachování tohoto příkladu jednoduše nalezneme databázi produktů na samostatném listu ve stejném sešitu:

Ve skutečnosti je pravděpodobnější, že databáze produktů bude umístěna v samostatném sešitu. Funkce VLOOKUP dělá malý rozdíl, což se opravdu nezajímá, pokud je databáze umístěna na stejném listu, jiném listu nebo úplně jiném sešitu.

Takže jsme vytvořili databázi produktů, která vypadá takto:

Abychom mohli otestovat vzorec VLOOKUP, který se chystáme napsat, nejprve zadáme platný kód položky do buňky A11 naší prázdné faktury:

Dále jsmepřesuňte aktivní buňku do buňky, ve které chceme informace uložené v databázi z VLOOKUPu uložit. Je zajímavé, že se jedná o krok, kterým se většina lidí pokazí.Další vysvětlení: Chystáme se vytvořit vzorec VLOOKUP, který načte popis, který odpovídá kódu položky v buňce A11.Kde chceme tento popis dát, když ho dostaneme? V buňce B11 samozřejmě.Takže tam píšeme vzorec VLOOKUP: v buňce B11.Vyberte buňku B11.

Potřebujeme nalézt seznam všech dostupných funkcí, které má aplikace Excel k dispozici, abychom mohli vybrat VLOOKUP a získat pomoc při vyplňování vzorce. To se najde klepnutím na kartu a potom klepnutím na Vložte funkci :

Zobrazí se okno, které nám umožňuje vybrat libovolné funkce dostupné v aplikaci Excel.

Chcete-li najít ten, který hledáme, mohli bychom zadat hledaný výraz jako "vyhledávání"( protože funkce, o kterou nás zajímá, je funkce ).Systém nám vrátí seznam všech funkcí souvisejících s vyhledáváním v aplikaci Excel. VLOOKUP je druhý v seznamu. Vyberte jej kliknutím na OK .

Argument argumenty Zobrazí se pole , které nás vyzývá k dokončení funkce VLOOKUP, která vyžaduje všechny argumenty ( nebo parametry ).Můžete si myslet na toto pole jako funkci, která nás dotazuje na následující otázky:

1. Jaký jedinečný identifikátor hledáte v databázi?
2. Kde je databáze?
3. Jakou informaci z databáze přidruženou k jedinečnému identifikátoru chcete získat pro vás?

První tři argumenty jsou zobrazeny tučně , což naznačuje, že jde o povinné argumenty ( funkce VLOOKUP je bez nich neúplná a nevrátí platnou hodnotu).Čtvrtý argument není tučný, což znamená, že je nepovinné:

Dokončíme argumenty v pořadí, shora dolů.

První argument, který musíme dokončit, je argument Lookup_value .Funkce nám musí říci, kde se má najít jedinečný identifikátor( kód položky v tomto případě), že by měl vracet popis. Musíme vybrat kód položky, který jsme zadali dříve( v A11).

Klepněte na ikonu voliče napravo od prvního argumentu:

Poté klikněte jednou na buňku obsahující kód položky( A11) a stiskněte Zadejte :

Hodnota "A11" se vloží do prvního argumentu.

Potřebujeme zadat hodnotu argumentu Table_array .Jinými slovy, musíme VLOOKUP informovat, kde najít databázi / seznam. Klepněte na ikonu výběru vedle druhého argumentu:

Nyní vyhledejte databázi / seznam a vyberte celý seznam - nezahrnuje řádek .V našem příkladu je databáze umístěna na samostatném listu, proto nejprve klikneme na kartu tabulky:

Dále vybereme celou databázi bez řádku záhlaví:

. .. a stiskneme Zadejte .Rozsah buněk, které reprezentují databázi( v tomto případě "Databáze produktů"! A2: D7 "), je pro nás automaticky zadán do druhého argumentu.

Nyní musíme zadat třetí argument Col_index_num .Použijeme tento argument k tomu, abychom specifikovali, do VLOOKUP, jakou informaci z databáze, spojenou s kódem položky v A11, se nám chtějí vrátit. V tomto konkrétním příkladu bychom si přáli vrátit popis položky .Pokud se podíváte na listu databáze, všimnete si, že sloupec "Popis" je druhý sloupec v databázi. To znamená, že do pole Col_index_num musíme zadat hodnotu "2":

Je důležité poznamenat, že zde nezadáváme "2", protože sloupec "Popis" je ve sloupci B naTento pracovní list. Pokud dojde ke spuštění databáze ve sloupci K listu, stále bychom zadali hodnotu "2" v tomto poli, protože sloupec "Popis" je druhý sloupec v sadě buněk, které jsme vybrali při zadání "Table_array".

Nakonec se musíme rozhodnout, zda zadáme hodnotu do konečného argumentu VLOOKUP, Range_lookup .Tento argument vyžaduje buď hodnotu true nebo false , nebo by měla být prázdná.Při použití VLOOKUPu s databázemi( jak je to pravdivé 90% času), způsob, jak se rozhodnout, co umístit v tomto argumentu, lze považovat za následující:

Pokud první sloupec databáze( sloupec obsahující jedinečné identifikátory)je řazeno abecedně / číselně ve vzestupném pořadí, pak je možné do tohoto argumentu zadat hodnotu true nebo jej ponechat prázdné.

Pokud první sloupec databáze není tříděn nebo je řazen sestupně, pak musí zadat hodnotu false do tohoto argumentu

Protože první sloupec naší databáze je , nikoli ,zadáme false do tohoto argumentu:

To je ono! Zadali jsme všechny informace požadované pro VLOOKUP, abychom vrátili hodnotu, kterou potřebujeme. Klepněte na tlačítko OK a všimněte si, že popis odpovídající kódu položky "R99245" byl správně zadán do buňky B11:

Vzorec, který byl vytvořen pro nás vypadá takto:

Pokud zadáme jiný kód položky do buňkyA11, začneme vidět výkon funkce VLOOKUP: Popis buněk se změní tak, aby odpovídal novému kódu položky:

Můžeme provést podobnou sadu kroků, abychom získali cenu položky vrácenou do buňky E11.Všimněte si, že nový vzorec musí být vytvořen v buňce E11.Výsledek bude vypadat takto:

. .. a vzorec bude vypadat takto:

Všimněte si, že jediný rozdíl mezi dvěma vzorci je třetí argument( Col_index_num ) se změnil z "2" na "3"( protožechceme, aby data byla získána ze třetího sloupce v databázi).

Pokud jsme se rozhodli koupit 2 z těchto položek, zadáme "2" do buňky D11.Pak bychom zadali jednoduchý vzorec do buňky F11, abychom získali celkový počet řádků:

= D11 * E11

. .. který vypadá takto. ..

Dokončení šablony faktury

Zatím jsme se o VLOOKUPu naučili hodně.Ve skutečnosti jsme se dozvěděli vše, co se v tomto článku dozvíme. Je důležité si uvědomit, že VLOOKUP lze použít i za jiných okolností kromě databází.To je méně obyčejné a může být pokryto v budoucích článcích How-To Geek.

Šablona faktury není dosud dokončena. Pro jeho dokončení bychom udělali následující:

1. Odstraníme ukázkový kód z buňky A11 a "2" z buňky D11.To způsobí, že nově vytvořené vzorce VLOOKUP zobrazí chybové zprávy:
   
   Tuto opravu můžeme vyřešit pomocí rozumného použití funkcí a ISDN() aplikace Excel. Změníme náš vzorec z tohoto. .. = VLOOKUP( A11, 'Databáze produktů'! A2: D7,2, FALSE) . .. k tomuto. .. = IF( IS11LANK( A11), VLOOKUP'! A2: D7,2, FALSE))
   
2. Zkopírujeme vzorce do buněk B11, E11 a F11 dolů na zbytek řádků položek faktury. Všimněte si, že pokud to uděláme, budoucí vzorce nebudou správně odkazovat na databázovou tabulku. Mohli bychom to vyřešit změnou odkazů na buňky pro databázi na absolutní reference buněk .Alternativně - a dokonce i lépe - bychom mohli vytvořit název oblasti pro celou databázi produktů( například "Produkty") a místo tohoto odkazu použijeme tento název rozsahu. Vzorec by se změnil z tohoto. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, "Databáze produktů"! A2: D7,2, FALSE)) . .. k tomuto. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, Products, 2, FALSE)) . .. a pak zkopírujte vzorce do zbývajících řádků položek faktury.
3. Pravděpodobně budeme blokovat buňky, které obsahují naše vzorce( nebo spíše odemknout další buňky ) a pak chránit pracovní list, abychom zajistili, že naše pečlivě sestavené vzorce nebudou náhodně přepsány, když někdo přijde vyplnitve faktuře.
4. Uložili bychom soubor jako šablonu , takže by jej mohli znovu použít všichni v naší firmě

Pokud bychom měli pocit, že je skutečně šikovný, vytvoříme databázi všech našich zákazníků v jiném pracovním listu a pak použijemeidentifikátor zákazníka zadaný v buňce F5 automaticky vyplní jméno a adresu zákazníka v buňkách B6, B7 a B8.

Pokud chcete pracovat s VLOOKUP, nebo prostě vidět naši výslednou šablonu faktury, můžete ji stáhnout zde.