Funkcia VLOOKUP v Exceli – Príručka pre začiatočníkov: Syntax a príklady

Obsah

Funkcia VLOOKUP v Exceli – všeobecný popis a syntax
- Syntax funkcie VLOOKUP
Príklady VLOOKUP
Presná alebo približná zhoda vo funkcii VLOOKUP
- Príklad 1: Nájdenie presnej zhody pomocou funkcie VLOOKUP
- Príklad 2: Použitie funkcie VLOOKUP na nájdenie približnej zhody
VLOOKUP v Exceli – toto si musíte zapamätať!

Dnes začíname sériou článkov popisujúcich jednu z najužitočnejších funkcií Excelu − VPR (VLOOKUP). Táto funkcia je zároveň jednou z najzložitejších a najmenej pochopených.

V tomto návode na VPR Základy sa pokúsim položiť čo najjednoduchšie, aby bol proces učenia čo najprehľadnejší pre neskúsených používateľov. Okrem toho si preštudujeme niekoľko príkladov so vzorcami programu Excel, ktoré budú demonštrovať najbežnejšie prípady použitia funkcie VPR.

Funkcia VLOOKUP v Exceli – všeobecný popis a syntax

Tak čo to je VPR? No v prvom rade je to funkcia Excelu. Čo robí? Vyhľadá zadanú hodnotu a vráti zodpovedajúcu hodnotu z druhého stĺpca. Technicky vzaté, VPR vyhľadá hodnotu v prvom stĺpci daného rozsahu a vráti výsledok z iného stĺpca v tom istom riadku.

V najbežnejšej aplikácii je funkcia VPR vyhľadá v databáze daný jedinečný identifikátor a vytiahne z databázy niektoré informácie, ktoré s ním súvisia.

Prvé písmeno v názve funkcie VPR (VLOOKUP) znamená Вvertikálne (Vvertikálne). Podľa toho sa dá rozlíšiť VPR z GPR (HLOOKUP), ktorý hľadá hodnotu v hornom riadku rozsahu − Гhorizontálne (Hhorizontálne).

Funkcie VPR dostupné v Exceli 2013, Exceli 2010, Exceli 2007, Exceli 2003, Exceli XP a Exceli 2000.

Syntax funkcie VLOOKUP

Funkcie VPR (VLOOKUP) má nasledujúcu syntax:

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

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

Ako vidíte, funkcia VPR v programe Microsoft Excel má 4 možnosti (alebo argumenty). Prvé tri sú povinné, posledné je voliteľné.

lookup_value (lookup_value) – Hodnota, ktorú treba hľadať. Môže to byť hodnota (číslo, dátum, text) alebo odkaz na bunku (obsahujúci vyhľadávaciu hodnotu) alebo hodnota vrátená inou funkciou Excelu. Napríklad tento vzorec bude hľadať hodnotu 40:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

Ak je vyhľadávaná hodnota menšia ako najmenšia hodnota v prvom stĺpci vyhľadávaného rozsahu, funkcia VPR ohlási chybu #AT (#N/A).

table_array (tabuľka) – dva alebo viac stĺpcov údajov. Pamätajte, funkcia VPR vždy hľadá hodnotu v prvom stĺpci rozsahu uvedeného v argumente table_array (tabuľka). Zobraziteľný rozsah môže obsahovať rôzne údaje, ako napríklad text, dátumy, čísla, boolovské hodnoty. Funkcia nerozlišuje veľké a malé písmená, čo znamená, že veľké a malé písmená sa považujú za rovnaké. Takže náš vzorec bude hľadať hodnotu 40 v bunkách z A2 na A15, pretože A je prvý stĺpec rozsahu A2:B15 uvedeného v argumente table_array (tabuľka):
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

col_index_num (číslo_stĺpca) je číslo stĺpca v danom rozsahu, z ktorého sa vráti hodnota v nájdenom riadku. Stĺpec úplne vľavo v danom rozsahu je 1, druhý stĺpec je 2, tretí stĺpec je 3 a tak ďalej. Teraz si môžete prečítať celý vzorec:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)
Vzorec hľadajúci hodnotu 40 v rozsahu A2: A15 a vráti zodpovedajúcu hodnotu zo stĺpca B (pretože B je druhý stĺpec v rozsahu A2:B15).

Ak je hodnota argumentu col_index_num (číslo_stĺpca) menšie ako 1potom VPR ohlási chybu #HODNOTA! (#HODNOTA!). A ak je to viac ako počet stĺpcov v rozsahu table_array (tabuľka), funkcia vráti chybu #REF! (#LINK!).

range_lookup (range_lookup) – určuje, čo treba hľadať:
- presná zhoda, argument sa musí rovnať FALSE (NEPRAVDA);
- približná zhoda, argument sa rovná SKUTOČNÝ KÓD (TRUE) alebo nie je špecifikovaný vôbec.
Tento parameter je voliteľný, ale veľmi dôležitý. Ďalej v tomto návode VPR Ukážem vám niekoľko príkladov vysvetľujúcich, ako písať vzorce na nájdenie presných a približných zhôd.

Príklady VLOOKUP

Dúfam, že funkcia VPR bude vám trochu jasnejšie. Teraz sa pozrime na niektoré prípady použitia VPR vo vzorcoch s reálnymi údajmi.

Ako používať funkciu VLOOKUP na vyhľadávanie v inom hárku programu Excel

V praxi vzorce s funkciou VPR sa zriedka používajú na vyhľadávanie údajov na rovnakom pracovnom hárku. Častejšie budete hľadať a získavať zodpovedajúce hodnoty z iného hárku.

Aby bolo možné používať VPR, vyhľadajte v inom hárku programu Microsoft Excel, Musíte v argumente table_array (tabuľka) zadajte názov hárka s výkričníkom, za ktorým nasleduje rozsah buniek. Napríklad nasledujúci vzorec ukazuje, že rozsah A2: B15 je na hárku s názvom Sheet2.

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

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

Názov listu samozrejme nie je potrebné zadávať ručne. Stačí začať písať vzorec, a keď príde na argument table_array (tabuľka), prepnite na požadovaný hárok a vyberte požadovaný rozsah buniek pomocou myši.

Vzorec zobrazený na obrázku nižšie hľadá text „Produkt 1“ v stĺpci A (je to 1. stĺpec rozsahu A2:B9) na pracovnom hárku ceny.

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

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

Pamätajte, že pri hľadaní textovej hodnoty ju musíte uzavrieť do úvodzoviek („“), ako sa to zvyčajne robí vo vzorcoch programu Excel.

Na argumentáciu table_array (tabuľka) je žiaduce vždy používať absolútne odkazy (so znakom $). V tomto prípade zostane rozsah vyhľadávania pri kopírovaní vzorca do iných buniek nezmenený.

Hľadajte v inom zošite pomocou funkcie VLOOKUP

K funkcii VPR pracoval medzi dvoma excelovými zošitmi, je potrebné zadať názov zošita v hranatých zátvorkách pred názvom hárku.

Napríklad nižšie je vzorec, ktorý hľadá hodnotu 40 na liste Sheet2 v knihe Čísla.xlsx:

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

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

Tu je najjednoduchší spôsob, ako vytvoriť vzorec v programe Excel pomocou VPRktorý odkazuje na iný zošit:

Otvorte obe knihy. Toto sa nevyžaduje, ale je jednoduchšie vytvoriť vzorec týmto spôsobom. Nechcete zadávať názov zošita ručne, však? Navyše vás ochráni pred náhodnými preklepmi.
Začnite písať funkciu VPRa ked pride na argument table_array (tabuľka), prepnite na iný zošit a vyberte v ňom požadovaný rozsah vyhľadávania.

Snímka obrazovky nižšie zobrazuje vzorec s vyhľadávaním nastaveným na rozsah v zošite Cenník.xlsx na liste ceny.

Funkcie VPR bude fungovať aj vtedy, keď zatvoríte hľadaný zošit a na riadku vzorcov sa zobrazí úplná cesta k súboru zošita, ako je uvedené nižšie:

Ak názov zošita alebo hárku obsahuje medzery, musí byť uzavretý v apostrofoch:

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

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

Ako používať pomenovaný rozsah alebo tabuľku vo vzorcoch pomocou funkcie VLOOKUP

Ak plánujete použiť rovnaký rozsah vyhľadávania vo viacerých funkciách VPR, môžete vytvoriť pomenovaný rozsah a zadať jeho názov do vzorca ako argument table_array (tabuľka).

Ak chcete vytvoriť pomenovaný rozsah, jednoducho vyberte bunky a do poľa zadajte príslušný názov Krstné meno, naľavo od riadka vzorcov.

Teraz si môžete zapísať nasledujúci vzorec na zistenie ceny produktu Produkt 1:

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

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

Väčšina názvov rozsahov funguje pre celý excelový zošit, takže nie je potrebné zadávať názov hárka pre argument table_array (tabuľka), aj keď vzorec a rozsah vyhľadávania sú na rôznych hárkoch. Ak sú v rôznych zošitoch, potom pred názvom rozsahu musíte zadať názov zošita, napríklad takto:

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

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

Takže vzorec vyzerá oveľa jasnejšie, súhlasíte? Použitie pomenovaných rozsahov je tiež dobrou alternatívou k absolútnym odkazom, pretože pomenovaný rozsah sa pri kopírovaní vzorca do iných buniek nezmení. To znamená, že si môžete byť istí, že rozsah vyhľadávania vo vzorci zostane vždy správny.

Ak konvertujete rozsah buniek na plnohodnotnú excelovskú tabuľku pomocou príkazu Tabuľka (Tabuľka) tab vloženie (Vložiť), potom keď vyberiete rozsah pomocou myši, Microsoft Excel automaticky pridá názvy stĺpcov (alebo názov tabuľky, ak vyberiete celú tabuľku) do vzorca.

Hotový vzorec bude vyzerať asi takto:

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

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

Alebo možno aj takto:

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

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

Pri použití pomenovaných rozsahov budú odkazy ukazovať na rovnaké bunky bez ohľadu na to, kam funkciu skopírujete VPR v pracovnom zošite.

Používanie zástupných znakov vo vzorcoch VLOOKUP

Rovnako ako u mnohých iných funkcií, VPR Môžete použiť nasledujúce zástupné znaky:

Otáznik (?) – nahrádza ľubovoľný jeden znak.
Hviezdička (*) – nahrádza ľubovoľnú sekvenciu znakov.

Používanie zástupných znakov vo funkciách VPR môžu byť užitočné v mnohých prípadoch, napr.

Keď si presne nepamätáte text, ktorý potrebujete nájsť.
Keď chcete nájsť nejaké slovo, ktoré je súčasťou obsahu bunky. Viem, že VPR vyhľadáva podľa obsahu bunky ako celku, ako keby bola táto možnosť povolená Porovnajte obsah celej bunky (Celá bunka) v štandardnom excelovom vyhľadávaní.
Keď bunka obsahuje na začiatku alebo na konci obsahu medzery navyše. V takejto situácii si môžete dlho lámať hlavu a snažiť sa prísť na to, prečo vzorec nefunguje.

Príklad 1: Hľadanie textu, ktorý začína alebo končí určitými znakmi

Povedzme, že chcete vyhľadať konkrétneho zákazníka v databáze zobrazenej nižšie. Nepamätáte si jeho priezvisko, ale viete, že to začína na „ack“. Tu je vzorec, ktorý túto prácu urobí dobre:

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

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

Teraz, keď ste si istí, že ste našli správny názov, môžete použiť rovnaký vzorec na zistenie sumy, ktorú zaplatil tento zákazník. Ak to chcete urobiť, stačí zmeniť tretí argument funkcie VPR na požadované číslo stĺpca. V našom prípade je to stĺpec C (3. v rozsahu):

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

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

Tu je niekoľko ďalších príkladov so zástupnými znakmi:

~ Nájdite meno končiace na „muž“:

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

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

~ Nájdite meno, ktoré začína na „reklama“ a končí na „syn“:

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

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

~ V zozname nájdeme prvé meno pozostávajúce z 5 znakov:

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

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

K funkcii VPR so zástupnými znakmi fungoval správne, ako štvrtý argument by ste mali vždy použiť FALSE (NEPRAVDA). Ak rozsah vyhľadávania obsahuje viac ako jednu hodnotu, ktorá sa zhoduje s hľadanými výrazmi so zástupnými znakmi, vráti sa prvá nájdená hodnota.

Príklad 2: Skombinujte zástupné znaky a odkazy na bunky vo vzorcoch VLOOKUP

Teraz sa pozrime na trochu zložitejší príklad vyhľadávania pomocou funkcie VPR podľa hodnoty v bunke. Predstavte si, že stĺpec A je zoznam licenčných kľúčov a stĺpec B je zoznam mien, ktoré vlastnia licenciu. Okrem toho máte v bunke C1 časť (niekoľko znakov) akéhosi licenčného kľúča a chcete zistiť meno vlastníka.

To možno vykonať pomocou nasledujúceho vzorca:

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

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

Tento vzorec vyhľadá hodnotu z bunky C1 v danom rozsahu a vráti zodpovedajúcu hodnotu zo stĺpca B. Všimnite si, že v prvom argumente používame na prepojenie textového reťazca znak ampersand (&) pred a za odkazom na bunku.

Ako môžete vidieť na obrázku nižšie, funkcia VPR vráti „Jeremy Hill“, pretože jeho licenčný kľúč obsahuje sekvenciu znakov z bunky C1.

Všimnite si, že argument table_array (tabuľka) na snímke obrazovky vyššie obsahuje názov tabuľky (Tabuľka7) namiesto určenia rozsahu buniek. To je to, čo sme urobili v predchádzajúcom príklade.

Presná alebo približná zhoda vo funkcii VLOOKUP

A nakoniec sa pozrime bližšie na posledný argument, ktorý je pre funkciu zadaný VPR - range_lookup (zobrazenie_intervalu). Ako bolo spomenuté na začiatku lekcie, tento argument je veľmi dôležitý. V rovnakom vzorci s jeho hodnotou môžete získať úplne odlišné výsledky SKUTOČNÝ KÓD (PRAVDA) alebo FALSE (NEPRAVDA).

Po prvé, poďme zistiť, čo Microsoft Excel znamená presné a približné zhody.

Ak argument range_lookup (range_lookup) sa rovná FALSE (FALSE), vzorec hľadá presnú zhodu, teda presne rovnakú hodnotu, ako je uvedená v argumente lookup_value (vyhľadávacia_hodnota). Ak je v prvom stĺpci rozsahu tschopný_pole (tabuľka) narazí na dve alebo viac hodnôt, ktoré zodpovedajú argumentu lookup_value (hľadaná_hodnota), potom sa vyberie prvá. Ak sa nenájdu žiadne zhody, funkcia ohlási chybu #AT (#N/A). Napríklad nasledujúci vzorec ohlási chybu #AT (#N/A), ak v rozsahu A2:A15 nie je žiadna hodnota 4:
=VLOOKUP(4,A2:B15,2,FALSE)
=ВПР(4;A2:B15;2;ЛОЖЬ)
Ak argument range_lookup (range_lookup) sa rovná SKUTOČNÝ KÓD (TRUE), vzorec hľadá približnú zhodu. Presnejšie, najprv funkcia VPR hľadá presnú zhodu, a ak sa nenájde, vyberie približnú. Približná zhoda je najväčšia hodnota, ktorá nepresahuje hodnotu uvedenú v argumente. lookup_value (vyhľadávacia_hodnota).

Ak argument range_lookup (range_lookup) sa rovná SKUTOČNÝ KÓD (TRUE) alebo nie je zadané, potom by sa hodnoty v prvom stĺpci rozsahu mali zoradiť vzostupne, to znamená od najmenšej po najväčšiu. V opačnom prípade funkcia VPR môže vrátiť chybný výsledok.

Aby sme lepšie pochopili dôležitosť výberu SKUTOČNÝ KÓD (PRAVDA) alebo FALSE (FALSE), pozrime sa na ďalšie vzorce s funkciou VPR a pozrite sa na výsledky.

Príklad 1: Nájdenie presnej zhody pomocou funkcie VLOOKUP

Ako si pamätáte, na vyhľadanie presnej zhody je štvrtý argument funkcie VPR malo by záležať FALSE (NEPRAVDA).

Vráťme sa k tabuľke z úplne prvého príkladu a zistime, ktoré zviera sa dokáže pohybovať rýchlosťou 50 míle za hodinu. Verím, že tento vzorec vám nespôsobí žiadne ťažkosti:

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

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

Všimnite si, že náš rozsah vyhľadávania (stĺpec A) obsahuje dve hodnoty 50 - v bunkách A5 и A6. Vzorec vráti hodnotu z bunky B5. prečo? Pretože pri hľadaní presnej zhody funkcia VPR používa prvú nájdenú hodnotu, ktorá sa zhoduje s hľadanou hodnotou.

Príklad 2: Použitie funkcie VLOOKUP na nájdenie približnej zhody

Keď použijete funkciu VPR hľadať približnú zhodu, teda keď argument range_lookup (range_lookup) sa rovná SKUTOČNÝ KÓD (TRUE) alebo vynechané, prvá vec, ktorú musíte urobiť, je zoradiť rozsah podľa prvého stĺpca vo vzostupnom poradí.

To je veľmi dôležité, pretože funkcia VPR vráti nasledujúcu najväčšiu hodnotu po danej hodnote a potom sa vyhľadávanie zastaví. Ak zanedbáte správne triedenie, dostanete sa k veľmi zvláštnym výsledkom alebo k chybovému hláseniu. #AT (#N/A).

Teraz môžete použiť jeden z nasledujúcich vzorcov:

=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)

Ako vidíte, chcem zistiť, ktoré zo zvierat má rýchlosť najbližšie 69 míle za hodinu. A tu je výsledok, ktorý mi funkcia vrátila VPR:

Ako vidíte, vzorec vrátil výsledok antilopa (Antelope), ktorého rýchlosť 61 míľ za hodinu, hoci zoznam obsahuje aj gepard (Gepard), ktorý beží rýchlosťou 70 míľ za hodinu a 70 je bližšie k 69 ako 61, však? Prečo sa to deje? Pretože funkcia VPR pri hľadaní približnej zhody vráti najväčšiu hodnotu, ktorá nie je väčšia ako hľadaná hodnota.

Dúfam, že tieto príklady osvetlia prácu s funkciou VPR v Exceli a už sa na ňu nepozeráte ako na outsidera. Teraz nezaškodí stručne zopakovať kľúčové body učiva, ktoré sme študovali, aby sme si ho lepšie zapamätali.

VLOOKUP v Exceli – toto si musíte zapamätať!

Funkcie VPR Excel sa nemôže pozerať doľava. Vždy hľadá hodnotu v ľavom stĺpci rozsahu daného argumentom table_array (tabuľka).
Vo funkcii VPR všetky hodnoty nerozlišujú veľké a malé písmená, tj malé a veľké písmená sú ekvivalentné.
Ak je hľadaná hodnota menšia ako minimálna hodnota v prvom stĺpci vyhľadávaného rozsahu, funkcia VPR ohlási chybu #AT (#N/A).
Ak 3. argument col_index_num (číslo_stĺpca) menšie ako 1funkcie VPR ohlási chybu #HODNOTA! (#HODNOTA!). Ak je väčší ako počet stĺpcov v rozsahu table_array (tabuľka), funkcia ohlási chybu #REF! (#LINK!).
V argumente použite absolútne odkazy na bunky table_array (tabuľka), aby sa pri kopírovaní vzorca zachoval správny rozsah vyhľadávania. Skúste použiť pomenované rozsahy alebo tabuľky v Exceli ako alternatívu.
Pri vyhľadávaní približnej zhody nezabudnite, že prvý stĺpec v hľadanom rozsahu musí byť zoradený vzostupne.
Nakoniec si pamätajte dôležitosť štvrtého argumentu. Použite hodnoty SKUTOČNÝ KÓD (PRAVDA) alebo FALSE (NEPRAVDA) schválne a zbavíte sa mnohých bolestí hlavy.

V nasledujúcich článkoch nášho tutoriálu funkcií VPR v Exceli sa naučíme pokročilejšie príklady, ako napríklad vykonávanie rôznych výpočtov pomocou VPR, extrahovanie hodnôt z viacerých stĺpcov a ďalšie. Ďakujem, že ste si prečítali tento tutoriál a dúfam, že sa uvidíme opäť budúci týždeň!