Aplikácia a definícia tejto funkcie programu Excel
VLOOKUP je funkcia programu Excel, pomocou ktorej môže používateľ vyhľadávať a hodnotiť obsah tabuľky. Táto funkcia je k dispozícii vo verziách programu Excel 2007 pre Windows a Mac.
Čo je VLOOKUP?
Možné použitia programu VLOOKUP je potrebné vysvetliť na príklade: V tomto ste veľkým fanúšikom literatúry, a preto ste si vytvorili vlastnú tabuľku programu Excel, v ktorej môžete starostlivo triediť knihy, ktoré ste zhromaždili. Ku každému dielu sú zadané informácie o nasledujúcich kategóriách:
-
autor
-
titul
-
Číslo strany
-
Rok vydania
Teraz by ste chceli dať priateľovi tip na knihu, ktorý by si vzal so sebou na ďalšie stretnutie. Žiaľ, môžete myslieť iba na autora, nie na názov knihy. Tu vstupuje do hry VLOOKUP, pretože pomocou tejto vstupnej hodnoty dokáže naraz vyhodiť hľadané informácie.
Ako sa používa VLOOKUP?
Predtým, ako sa zamyslíte nad formuláciou vzorcov, malo by byť určené, kde bude vstupné pole a rôzne výstupné polia umiestnené neskôr. Za týmto účelom má zmysel vytvoriť samostatnú tabuľku, ktorá je na začiatku prázdna, a teda umožňuje priestor pre uvedené informácie. Ak navrhnete tento nový stôl podľa príkladu existujúceho stola, budete mať neskôr výhodu úspory času.
Na tomto základe môže byť vzorec VLOOKUP vytvorený ručne alebo automaticky vygenerovaný programom Excel. Začiatočníkom stojí za to použiť posledný prístup, aby ste postupne spoznali štruktúru a účinok vzorca. Za týmto účelom je na karte „Vzorce“ vybraté tlačidlo „Vložiť funkciu“. VLOOKUP je skrytý v okne, ktoré sa otvorí. Po potvrdení sa opäť otvorí okno, v ktorom je možné vyplniť štyri parametre vzorca. Sú to tieto:
-
Kritérium vyhľadávania
-
matica
-
Stĺpcový index
-
Area_reference
Pôvodný koncept vzorca preto vyzerá takto:
= VLOOKUP (kritérium vyhľadávania, matica, index stĺpca, odkaz na rozsah)
a v jednej z možných aplikácií takto:
= VLOOKUP (H3; A3: E40; 5)
Kritérium vyhľadávania
Aby funkcia vedela, ktorá hodnota by sa mala použiť ako počiatočný bod, riadok, ktorý bol vybratý ako vstupné pole pred dvoma krokmi, je uvedený v poli „Kritérium vyhľadávania“. V našom prípade je tam uvedené meno autora knihy „Phillip Pulmann“. Vďaka tomu je vzorec flexibilný a nemusí sa znova upravovať, akonáhle sa zadaná hodnota zmení.
matica
Vstupné pole "Matica" popisuje tabuľku, v ktorej sa nachádzajú informácie, ktoré sa majú odoslať. Táto špeciálna matica teda obsahuje aj stĺpce pre názov knihy, číslo strany a rok vydania.
Matica sa raz úplne vyberie bez nadpisov od ľavého horného po pravý dolný okraj. Excel týmto spôsobom vie, ktorý obsah je potrebné vziať do úvahy pri hodnotení.
Index stĺpca
Vstupné pole pre „index stĺpca“ vyzve užívateľa, aby definoval stĺpec matice, v ktorom je uvedená iba požadovaná hodnota. Priradenie stĺpcov je číslované chronologicky. To znamená, že prvý stĺpec tabuľky dostane hodnotu 1, druhý hodnotu 2 atď. V našom prípade to zodpovedá stĺpcovému indexu 1 pre autora, stĺpcovému indexu 2 pre nadpis, stĺpcovému indexu 3 pre číslo stránky a stĺpcový index 4 za rok vydania.
Aby bola tabuľka čo najpružnejšia, je možné namiesto čísla prepojiť záhlavie stĺpca. To má výhodu v tom, že vzorec je možné bez problémov preniesť aj do iných riadkov, pretože záhlavie stĺpcov je možné vždy flexibilne prispôsobiť.
Pozor: VLOOKUP číta maticu zľava doprava, a preto musí byť index stĺpca umiestnený napravo od stĺpca pre kritérium vyhľadávania, aby ho funkcia zohľadnila!
Area_reference
Parameter "Range_Lookup" dopĺňa vzorec VLOOKUP zadaním presnosti, s akou sa tabuľka hodnotí. Líši sa však od vyššie uvedených zložiek vzorca, pretože je voliteľný. Ak je hodnota 0 zadaná ako „nesprávna“, program Excel vyhľadá iba hodnotu, ktorá bola zadaná ako kritérium vyhľadávania. S hodnotou 1 pre „true“ však hľadanie zrejmých hodnôt pokračuje, ak sa presnú hodnotu nepodarilo nájsť.
Zadanie tohto parametra je voliteľné, pretože hodnota 1 je predvolene nastavená. Toto nastavenie bude užitočné neskôr v rozšírenom VLOOKUP s viacerými kritériami vyhľadávania.
Spájanie
Akonáhle sú nastavené všetky potrebné parametre, je možné použiť VLOOKUP. Po zadaní kritéria vyhľadávania a potvrdení funkcie sa hľadaná hodnota zobrazí v riadku, ktorý bol definovaný ako výstupné pole.
V našom prípade sa teraz zobrazí názov knihy „Zlatý kompas“, ktorý zodpovedá zadanému autorovi. Aby bolo možné rýchlo zistiť číslo strany a rok vydania, nie je potrebné urobiť nič iné, ako pretiahnutie existujúceho vzorca VLOOKUP do nasledujúcich buniek. Je to tak jednoduché, pretože index stĺpcov VLOOKUP bol prepojený s nadpisom stĺpca prvej tabuľky a druhá tabuľka je tiež štruktúrovaná v rovnakom poradí.
V prípade, že sa tabuľky budú navzájom líšiť alebo že napriek všetkému dôjde k chybe, vzorec VLOOKUP je možné zmeniť aj ručne. Za týmto účelom musí byť predposledná číslica pre index stĺpca priradená k stĺpcu novej hodnoty, ktorá má byť výstupom.
VLOOKUP s viacerými kritériami vyhľadávania
Často sa stáva, že jedno kritérium vyhľadávania nestačí na presné vyhodnotenie veľkej tabuľky programu Excel. Potom má zmysel spustiť VLOOKUP s niekoľkými kritériami vyhľadávania. Za týmto účelom musí byť existujúci vzorec doplnený o ďalšiu funkciu IF. Týmto spôsobom je možné počas aplikácie zohľadniť až osem rôznych vyhľadávacích kritérií.
VLOOKUP vo viacerých excelových tabuľkách
Ak sa kritérium vyhľadávania nenachádza iba v jednej tabuľke, ale možno aj v druhej, je možné vzorec VLOOKUP zodpovedajúcim spôsobom upraviť. Za týmto účelom je potrebné pred existujúci vzorec umiestniť funkciu if aj funkciu ISERROR. Na to je potrebných päť parametrov:
-
Kritérium vyhľadávania
-
Matrix1 a Matrix 2
-
Stĺpec index1 a stĺpec index2
Výsledok vyzerá takto:
= IF (ISERROR (VLOOKUP (kritérium vyhľadávania, matica1, stĺpec-index1, 0));
VLOOKUP (kritérium vyhľadávania; matica2; stĺpcový index2,0); VLOOKUP (vyhľadávacie kritérium; matica1; stĺpcový index1;))
a v jednej z možných aplikácií takto:
= IF (ISERROR (VLOOKUP (E5, A5: B9,2, 0)), VLOOKUP (E5, A13: B17,2, 0), VLOOKUP (E5, A5: B9,2, 0))
Kritérium vyhľadávania sa používa na vloženie hodnoty, ktorá sa má hľadať, do dvoch tabuliek. Matrix1 a Matrix2 definujú príslušné oblasti buniek dvoch tabuliek. Stĺpec index1 a stĺpec index2 slúžia na podrobnejšie definovanie stĺpcov príslušných tabuliek.
Ak sa hľadaná hodnota vyskytuje v oboch tabuľkách, Excel vygeneruje výsledok z prvej tabuľky. Ak sa však hodnota nenachádza v žiadnej z dvoch tabuliek, zobrazí sa chybové hlásenie. Výhodou vzorca je, že dva zoznamy nemusia mať rovnakú štruktúru alebo rovnakú veľkosť.
Priraďte hodnoty kategóriám pomocou VLOOKUP
Dodatočná funkcia VLOOKUP umožňuje automatické rozdelenie uvedených hodnôt na písmená a predikáty podľa vášho výberu. V našom predchádzajúcom príklade by mal byť pre typ knihy vložený ďalší stĺpec tabuľky. Do žánru poviedky by mali spadať knihy s dĺžkou až 50 strán, pričom k novele sú priradené knihy od 51 do 150 strán a k románu od 151 strán. Aby to bolo možné, vo VLOOKUPE nie je potrebný žiadny ďalší vzorec, iba použitie zložených zátvoriek „{}“. Hotový vzorec vyzerá takto:
= VLOOKUP (B1; {1. "Poviedka"; 51. "Novella"; 151. "Román"}; 2)
Obsah zložených zátvoriek označuje maticu, ktorá definuje oblasť príslušného typu knihy. Priradenie dĺžky strany k príslušnému rodu je preto umiestnené v zátvorkách. Vzorec používa dvojice hodnôt, pričom každá je oddelená bodom. Matica {1. „Poviedka“; 51. „Novella“; 151. „Román“} sa číta takto:
"Od 1 predstavte krátky príbeh, od 51 ukážku novely, od 151 ukážku románu."
Túto maticu je možné ľahko prispôsobiť rôznym úlohám. To sa týka na jednej strane veľkosti a počtu matríc, ako aj ich označenia. Namiesto jednotlivých písmen je teda možné vo výsledku zobrazovať reťazce alebo čísla. Stačí len upraviť písmená vo vzorci.
VLOOKUP do viacerých pracovných hárkov
Ďalšia funkcia VLOOKUP umožňuje svojim používateľom prepojiť obsah, ktorý sa nachádza v rôznych tabuľkách. V našom prípade môže byť táto možnosť užitočná vtedy, keď sú informácie najskôr zoradené v rôznych pracovných hárkoch a potom aktualizované v súhrnnej tabuľke.
Predstavte si, že okrem svojich kníh uvediete v programe Excel aj svoje zozbierané filmy. Obe kolekcie potom spojíte do jednej veľkej tabuľky.
Výhoda tohto postupu spočíva nielen vo zvýšenom poradí, ale aj v predchádzaní potenciálnym chybám. Ak chcete vytvoriť nový záznam alebo aktualizovať existujúci, nemusíte hľadať vo veľkej tabuľke, ale môžete pristupovať k menšej tabuľke. Hodnoty sa potom automaticky prenesú do súhrnnej tabuľky programu Excel. Prepisovanie vo veľkej tabuľke je preto nadbytočné, čo v najlepšom prípade zabráni nešťastnému pohybu a následnému reťazeniu chybových hlásení.
Ako vyzerá vzorec?
Táto funkcia je opäť možná vložením iného vzorca. Pri vyhľadávaní podľa viacerých kritérií je potrebný ďalší vzorec IF, práca s viacerými pracovnými listami vyžaduje NEPRIAMY vzorec. To umožňuje pre maticu VLOOKUP zadať rozsah z inej tabuľky.
= VLOOKUP (kritérium vyhľadávania; NEPRIAMY (matica); index stĺpca; odkaz na rozsah)
Pozor: Tento vzorec bude fungovať iba vtedy, ak majú jednotlivé tabuľky v rôznych hárkoch rovnaké názvy ako názvy stĺpcov všeobecnej tabuľky. Celé tabuľky je možné pomenovať v poli „Názov“ vľavo hore nad mriežkou bunky. Tabuľky, ktoré už boli pomenované, je možné zobraziť pomocou kombinácie klávesov Ctrl + F3.
Riešenie vznikajúcich chybových hlásení
Práca s prepojenými tabuľkami programu Excel môže viesť k nechceným problémom. To zahŕňa najmä výstup nesprávnych hodnôt. V prípade, že je na výstupe nesprávna hodnota 0, existuje malý problém v nastaveniach programu Excel, ktorý je možné rýchlo opraviť.
Bežné chybové hlásenie #NV je na druhej strane zámernou funkciou VLOOKUP, ktorá používateľovi naznačuje, že požadovaná hodnota nie je k dispozícii. Táto poznámka môže byť navrhnutá inak pomocou vzorca.
VLOOKUP - prehľad
VLOOKUP je užitočná funkcia programu Excel, ktorú je možné použiť na vyhľadávanie a vyhodnocovanie tabuliek. Jeho výhody sú zrejmé z užívateľsky prívetivej a flexibilnej aplikácie. Z tejto funkcie môže mať prospech každý, kto pravidelne pracuje s tabuľkami programu Excel. Či už je to súkromný zberateľ, ktorý vytvára svoje vlastné malé tabuľky, alebo veľká spoločnosť, ktorá spracováva podstatne zásadnejšie súbory údajov.
Ak na druhej strane stále máte nezodpovedané požiadavky, ktoré VLOOKUP nedokázal splniť, môžete sa tešiť na ďalšiu možnosť Excelu: Microsoft ponúka používateľom Excelu 365 nový XLOOKUP od začiatku rokov 2022-2023. Toto stavia na kompetenciách VLOOKUPU a dopĺňa ich o ďalšie, niekedy dokonca jednoduchšie, funkcie. Preto sa v tomto mieste otvára aj nová rutina pri vyhodnocovaní údajov.
