Funkce VLOOKUP v Excelu – Příručka pro začátečníky: Syntaxe a příklady

Obsah

Funkce VLOOKUP v Excelu – obecný popis a syntaxe
- Syntaxe funkce VLOOKUP
Příklady VLOOKUP
Přesná nebo přibližná shoda ve funkci VLOOKUP
- Příklad 1: Nalezení přesné shody pomocí funkce VLOOKUP
- Příklad 2: Použití funkce VLOOKUP k nalezení přibližné shody
SVYHLEDAT v Excelu – to si musíte zapamatovat!

Dnes začínáme sérii článků popisujících jednu z nejužitečnějších funkcí Excelu − VPR (VLOOKUP). Tato funkce je zároveň jednou z nejsložitějších a nejméně pochopených.

V tomto tutoriálu na VPR Základy se pokusím popsat co nejjednodušeji, aby byl proces učení pro nezkušené uživatele co nejpřehlednější. Kromě toho prostudujeme několik příkladů se vzorci aplikace Excel, které demonstrují nejčastější případy použití funkce VPR.

Funkce VLOOKUP v Excelu – obecný popis a syntaxe

Tak co to je VPR? No, v první řadě je to funkce Excelu. Co dělá? Vyhledá vámi zadanou hodnotu a vrátí odpovídající hodnotu z druhého sloupce. Technicky vzato, VPR vyhledá hodnotu v prvním sloupci daného rozsahu a vrátí výsledek z jiného sloupce na stejném řádku.

V nejběžnější aplikaci je funkce VPR vyhledá v databázi daný jedinečný identifikátor a extrahuje z databáze některé informace s ním související.

První písmeno v názvu funkce VPR (VLOOKUP) znamená Вvertikální (Vvertikální). Podle toho se dá rozlišit VPR od GPR (HLOOKUP), který hledá hodnotu v horním řádku rozsahu − Гhorizontální (Hhorizontálně).

funkce VPR k dispozici v Excelu 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP a Excel 2000.

Syntaxe funkce VLOOKUP

funkce VPR (VLOOKUP) má následující syntaxi:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Jak vidíte, funkce VPR v aplikaci Microsoft Excel má 4 možnosti (nebo argumenty). První tři jsou povinné, poslední je nepovinné.

lookup_value (lookup_value) – Hodnota, která se má hledat. Může to být hodnota (číslo, datum, text) nebo odkaz na buňku (obsahující vyhledávací hodnotu) nebo hodnota vrácená nějakou jinou funkcí aplikace Excel. Například tento vzorec bude hledat hodnotu 40:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

Pokud je vyhledávací hodnota menší než nejmenší hodnota v prvním sloupci hledaného rozsahu, funkce VPR ohlásí chybu #AT (#N/A).

table_array (tabulka) – dva nebo více sloupců dat. Pamatujte, funkce VPR vždy hledá hodnotu v prvním sloupci rozsahu uvedeného v argumentu table_array (stůl). Zobrazitelný rozsah může obsahovat různá data, jako je text, data, čísla, logické hodnoty. Funkce nerozlišuje velká a malá písmena, což znamená, že velká a malá písmena jsou považována za stejná. Náš vzorec tedy bude hledat hodnotu 40 v buňkách z A2 na A15, protože A je první sloupec rozsahu A2:B15 uvedeného v argumentu table_array (stůl):
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

col_index_num (číslo_sloupce) je číslo sloupce v daném rozsahu, ze kterého bude vrácena hodnota v nalezeném řádku. Sloupec zcela vlevo v daném rozsahu je 1, druhý sloupec je 2, třetí sloupec je 3 a tak dále. Nyní si můžete přečíst celý vzorec:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)
Vzorec hledající hodnotu 40 v dosahu A2: A15 a vrátí odpovídající hodnotu ze sloupce B (protože B je druhý sloupec v rozsahu A2:B15).

Pokud je hodnota argumentu col_index_num (číslo_sloupce) menší než 1pak VPR ohlásí chybu #HODNOTA! (#HODNOTA!). A pokud je více než počet sloupců v rozsahu table_array (tabulka), funkce vrátí chybu #REF! (#ODKAZ!).

range_lookup (range_lookup) – určuje, co hledat:
- přesná shoda, argument se musí rovnat NEPRAVDIVÉ (NEPRAVDIVÉ);
- přibližná shoda, argument se rovná PRAVÝ KÓD (PRAVDA) nebo není uvedeno vůbec.
Tento parametr je volitelný, ale velmi důležitý. Později v tomto tutoriálu dále VPR Ukážu vám několik příkladů vysvětlujících, jak psát vzorce pro nalezení přesných a přibližných shod.

Příklady VLOOKUP

Doufám, že funkce VPR bude vám trochu jasnější. Nyní se podívejme na některé případy použití VPR ve vzorcích s reálnými daty.

Jak používat SVYHLEDAT k vyhledávání v jiném listu aplikace Excel

V praxi vzorce s funkcí VPR se zřídka používají k vyhledávání dat na stejném listu. Častěji než ne budete hledat a získávat odpovídající hodnoty z jiného listu.

Aby bylo možné používat VPR, hledat v jiném listu Microsoft Excel, Musíte v argumentu table_array (tabulka) zadejte název listu s vykřičníkem následovaným rozsahem buněk. Například následující vzorec ukazuje, že rozsah A2: B15 je na listu s názvem Sheet2.

=VLOOKUP(40,Sheet2!A2:B15,2)

=ВПР(40;Sheet2!A2:B15;2)

Název listu samozřejmě není nutné zadávat ručně. Stačí začít psát vzorec, a když dojde na argument table_array (tabulka), přepněte na požadovaný list a myší vyberte požadovaný rozsah buněk.

Vzorec zobrazený na obrázku níže hledá text „Produkt 1“ ve sloupci A (je to 1. sloupec rozsahu A2:B9) na listu. Ceny.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)

=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

Pamatujte, že při hledání textové hodnoty ji musíte uzavřít do uvozovek („“), jak se to obvykle dělá ve vzorcích aplikace Excel.

Pro argumentaci table_array (tabulka) je žádoucí vždy používat absolutní odkazy (se znakem $). V tomto případě zůstane rozsah hledání při kopírování vzorce do jiných buněk nezměněn.

Hledejte v jiném sešitu pomocí funkce VLOOKUP

Fungovat VPR pracoval mezi dvěma sešity aplikace Excel, je třeba zadat název sešitu v hranatých závorkách před názvem listu.

Například níže je vzorec, který hledá hodnotu 40 na listu Sheet2 v knize Čísla.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

Zde je nejjednodušší způsob, jak vytvořit vzorec v aplikaci Excel VPRkterý odkazuje na jiný sešit:

Otevřete obě knihy. Není to povinné, ale je jednodušší vytvořit vzorec tímto způsobem. Nechcete zadávat název sešitu ručně, že? Navíc vás ochrání před náhodnými překlepy.
Začněte psát funkci VPRa když dojde na hádku table_array (tabulka), přepněte do jiného sešitu a vyberte v něm požadovaný rozsah hledání.

Níže uvedený snímek obrazovky ukazuje vzorec s hledáním nastaveným na rozsah v sešitu Ceník.xlsx na listu Ceny.

funkce VPR bude fungovat, i když zavřete hledaný sešit a na řádku vzorců se zobrazí úplná cesta k souboru sešitu, jak je znázorněno níže:

Pokud název sešitu nebo listu obsahuje mezery, musí být uzavřen v apostrofech:

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)

=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

Jak používat pojmenovaný rozsah nebo tabulku ve vzorcích pomocí funkce VLOOKUP

Pokud plánujete používat stejný rozsah vyhledávání ve více funkcích VPR, můžete vytvořit pojmenovaný rozsah a zadat jeho název do vzorce jako argument table_array (stůl).

Chcete-li vytvořit pojmenovanou oblast, jednoduše vyberte buňky a do pole zadejte příslušný název Jméno, nalevo od řádku vzorců.

Nyní si můžete zapsat následující vzorec pro zjištění ceny produktu Produkt 1:

=VLOOKUP("Product 1",Products,2)

=ВПР("Product 1";Products;2)

Většina názvů rozsahů funguje pro celý sešit aplikace Excel, takže není nutné zadávat název listu pro argument table_array (tabulka), i když vzorec a rozsah hledání jsou na různých listech. Pokud jsou v různých sešitech, musíte před názvem rozsahu zadat název sešitu, například takto:

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

=ВПР("Product 1";PriceList.xlsx!Products;2)

Takže vzorec vypadá mnohem jasněji, souhlasíte? Použití pojmenovaných rozsahů je také dobrou alternativou k absolutním odkazům, protože pojmenovaný rozsah se při kopírování vzorce do jiných buněk nezmění. To znamená, že si můžete být jisti, že rozsah hledání ve vzorci zůstane vždy správný.

Pokud převedete rozsah buněk na plnohodnotnou excelovou tabulku pomocí příkazu Tabulka (Tabulka) tab Vložení (Vložit), pak když vyberete rozsah pomocí myši, Microsoft Excel automaticky přidá názvy sloupců (nebo název tabulky, pokud vyberete celou tabulku) do vzorce.

Hotový vzorec bude vypadat nějak takto:

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

=ВПР("Product 1";Table46[[Product]:[Price]];2)

Nebo možná ještě takhle:

=VLOOKUP("Product 1",Table46,2)

=ВПР("Product 1";Table46;2)

Při použití pojmenovaných rozsahů budou odkazy ukazovat na stejné buňky bez ohledu na to, kam funkci zkopírujete VPR v rámci sešitu.

Použití zástupných znaků ve vzorcích SVYHLEDAT

Stejně jako u mnoha dalších funkcí, VPR Můžete použít následující zástupné znaky:

Otazník (?) – nahrazuje libovolný jednotlivý znak.
Hvězdička (*) – nahrazuje libovolnou sekvenci znaků.

Použití zástupných znaků ve funkcích VPR mohou být užitečné v mnoha případech, například:

Když si nepamatujete přesně text, který potřebujete najít.
Když chcete najít nějaké slovo, které je součástí obsahu buňky. Vím, že VPR vyhledává podle obsahu buňky jako celku, jako by byla volba povolena Porovnejte celý obsah buňky (Celá buňka) ve standardním Excelu.
Když buňka obsahuje na začátku nebo na konci obsahu mezery navíc. V takové situaci si můžete na dlouhou dobu lámat hlavu a snažit se přijít na to, proč vzorec nefunguje.

Příklad 1: Hledání textu, který začíná nebo končí určitými znaky

Řekněme, že chcete vyhledat konkrétního zákazníka v databázi zobrazené níže. Nepamatujete si jeho příjmení, ale víte, že to začíná na „ack“. Zde je vzorec, který bude dělat svou práci dobře:

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)

=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

Nyní, když jste si jisti, že jste našli správné jméno, můžete pomocí stejného vzorce najít částku zaplacenou tímto zákazníkem. Chcete-li to provést, stačí změnit třetí argument funkce VPR na požadované číslo sloupce. V našem případě se jedná o sloupec C (3. v rozsahu):

=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)

=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

Zde je několik dalších příkladů se zástupnými znaky:

~ Najděte jméno končící na „muž“:

=VLOOKUP("*man",$A$2:$C$11,1,FALSE)

=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ Najděte jméno, které začíná na „reklama“ a končí na „syn“:

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)

=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ V seznamu najdeme křestní jméno, které se skládá z 5 znaků:

=VLOOKUP("?????",$A$2:$C$11,1,FALSE)

=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

Fungovat VPR se zástupnými znaky fungoval správně, jako čtvrtý argument byste měli vždy použít NEPRAVDIVÉ (NEPRAVDIVÉ). Pokud rozsah hledání obsahuje více než jednu hodnotu, která odpovídá hledaným výrazům se zástupnými znaky, bude vrácena první nalezená hodnota.

Příklad 2: Kombinujte zástupné znaky a odkazy na buňky ve vzorcích SVYHLEDAT

Nyní se podívejme na trochu složitější příklad, jak pomocí funkce vyhledávat VPR podle hodnoty v buňce. Představte si, že sloupec A je seznam licenčních klíčů a sloupec B je seznam jmen, která vlastní licenci. Navíc máte v buňce C1 část (několik znaků) jakéhosi licenčního klíče a chcete zjistit jméno vlastníka.

To lze provést pomocí následujícího vzorce:

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)

=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

Tento vzorec vyhledá hodnotu z buňky C1 v daném rozsahu a vrátí odpovídající hodnotu ze sloupce B. Všimněte si, že v prvním argumentu používáme k propojení textového řetězce znak ampersand (&) před a za odkazem na buňku.

Jak můžete vidět na obrázku níže, funkce VPR vrátí „Jeremy Hill“, protože jeho licenční klíč obsahuje sekvenci znaků z buňky C1.

Všimněte si, že argument table_array (tabulka) na snímku obrazovky výše obsahuje název tabulky (Tabulka7) namísto určení rozsahu buněk. To jsme udělali v předchozím příkladu.

Přesná nebo přibližná shoda ve funkci VLOOKUP

A nakonec se podívejme blíže na poslední argument, který je pro funkci zadán VPR - range_lookup (zobrazení_intervalu). Jak bylo zmíněno na začátku lekce, tento argument je velmi důležitý. Ve stejném vzorci s jeho hodnotou můžete získat úplně jiné výsledky PRAVÝ KÓD (PRAVDA) nebo NEPRAVDIVÉ (NEPRAVDIVÉ).

Nejprve zjistíme, co Microsoft Excel znamená přesné a přibližné shody.

Pokud argument range_lookup (vyhledání_rozsahu) se rovná NEPRAVDIVÉ (FALSE), vzorec hledá přesnou shodu, tj. přesně stejnou hodnotu, jaká je uvedena v argumentu lookup_value (hledaná_hodnota). Pokud je v prvním sloupci rozsahu tschopné_pole (tabulka) narazí na dvě nebo více hodnot, které odpovídají argumentu lookup_value (hledaná_hodnota), pak bude vybrána první. Pokud nejsou nalezeny žádné shody, funkce ohlásí chybu #AT (#N/A). Například následující vzorec ohlásí chybu #AT (#N/A), pokud v rozsahu A2:A15 není žádná hodnota 4:
=VLOOKUP(4,A2:B15,2,FALSE)
=ВПР(4;A2:B15;2;ЛОЖЬ)
Pokud argument range_lookup (vyhledání_rozsahu) se rovná PRAVÝ KÓD (TRUE), vzorec hledá přibližnou shodu. Přesněji nejprve funkce VPR hledá přesnou shodu, a pokud není nalezena, vybere přibližnou. Přibližná shoda je největší hodnota, která nepřesahuje hodnotu zadanou v argumentu. lookup_value (hledaná_hodnota).

Pokud argument range_lookup (vyhledání_rozsahu) se rovná PRAVÝ KÓD (TRUE) nebo není zadáno, pak by hodnoty v prvním sloupci rozsahu měly být seřazeny vzestupně, tedy od nejmenší po největší. Jinak funkce VPR může vrátit chybný výsledek.

Abychom lépe pochopili důležitost volby PRAVÝ KÓD (PRAVDA) nebo NEPRAVDIVÉ (NEPRAVDA), podívejme se na některé další vzorce s funkcí VPR a podívejte se na výsledky.

Příklad 1: Nalezení přesné shody pomocí funkce VLOOKUP

Jak si pamatujete, chcete-li hledat přesnou shodu, čtvrtý argument funkce VPR mělo by záležet NEPRAVDIVÉ (NEPRAVDIVÉ).

Vraťme se k tabulce z úplně prvního příkladu a zjistíme, které zvíře se dokáže pohybovat rychlostí 50 míle za hodinu. Věřím, že tento vzorec vám nezpůsobí žádné potíže:

=VLOOKUP(50,$A$2:$B$15,2,FALSE)

=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

Všimněte si, že náš vyhledávací rozsah (sloupec A) obsahuje dvě hodnoty 50 – v buňkách A5 и A6. Vzorec vrací hodnotu z buňky B5. Proč? Protože při hledání přesné shody funkce VPR používá první nalezenou hodnotu, která odpovídá hledané hodnotě.

Příklad 2: Použití funkce VLOOKUP k nalezení přibližné shody

Při použití funkce VPR hledat přibližnou shodu, tj. když argument range_lookup (vyhledání_rozsahu) se rovná PRAVÝ KÓD (TRUE) nebo vynecháno, první věc, kterou musíte udělat, je seřadit rozsah podle prvního sloupce ve vzestupném pořadí.

To je velmi důležité, protože funkce VPR vrátí další největší hodnotu po dané hodnotě a poté se vyhledávání zastaví. Pokud zanedbáte správné třídění, skončíte s velmi podivnými výsledky nebo chybovou hláškou. #AT (#N/A).

Nyní můžete použít jeden z následujících vzorců:

=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)

=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)

Jak vidíte, chci zjistit, které ze zvířat má rychlost nejbližší 69 míle za hodinu. A tady je výsledek, který mi funkce vrátila VPR:

Jak vidíte, vzorec vrátil výsledek Antilopa (Antelope), jehož rychlost 61 mil za hodinu, i když seznam také obsahuje Cheetah (Gepard), který běží rychlostí 70 mil za hodinu a 70 je blíže 69 než 61, že? Proč se tohle děje? Protože funkce VPR při hledání přibližné shody vrátí největší hodnotu, která není větší než hledaná.

Doufám, že tyto příklady osvětlí práci s funkcí VPR v Excelu a už se na ni nedíváte jako na outsidera. Nyní neuškodí stručně zopakovat klíčové body látky, kterou jsme studovali, abychom si ji lépe zafixovali v paměti.

SVYHLEDAT v Excelu – to si musíte zapamatovat!

funkce VPR Excel se nemůže dívat doleva. Vždy hledá hodnotu v levém sloupci rozsahu daného argumentem table_array (stůl).
Ve funkci VPR všechny hodnoty nerozlišují velká a malá písmena, tj. malá a velká písmena jsou ekvivalentní.
Pokud je hledaná hodnota menší než minimální hodnota v prvním sloupci hledaného rozsahu, funkce VPR ohlásí chybu #AT (#N/A).
Pokud 3. argument col_index_num (číslo_sloupce) menší než 1funkce VPR ohlásí chybu #HODNOTA! (#HODNOTA!). Pokud je větší než počet sloupců v rozsahu table_array (tabulka), funkce ohlásí chybu #REF! (#ODKAZ!).
V argumentu použijte absolutní odkazy na buňky table_array (tabulka), aby při kopírování vzorce zůstal zachován správný rozsah hledání. Zkuste jako alternativu použít pojmenované rozsahy nebo tabulky v Excelu.
Při vyhledávání přibližné shody nezapomeňte, že první sloupec v hledaném rozsahu musí být seřazen vzestupně.
Nakonec si pamatujte důležitost čtvrtého argumentu. Použijte hodnoty PRAVÝ KÓD (PRAVDA) nebo NEPRAVDIVÉ (NEPRAVDA) schválně a zbavíte se mnoha bolestí hlavy.

V následujících článcích našeho výukového programu funkcí VPR v Excelu se naučíme pokročilejší příklady, jako je provádění různých výpočtů pomocí VPR, extrahování hodnot z více sloupců a další. Děkuji, že jste si přečetli tento návod a doufám, že se příští týden zase uvidíme!