Aplikace a definice této funkce Excelu
VLOOKUP je funkce Excelu, pomocí které může uživatel vyhledávat a vyhodnocovat obsah tabulky. Tato funkce je k dispozici ve verzích aplikace Excel 2007 pro Windows a Mac.
Co je VLOOKUP?
Možná použití VLOOKUP je zde vysvětlena na příkladu: V tomto jste velkým fanouškem literatury, a proto jste si vytvořili vlastní tabulku v Excelu, ve které můžete pečlivě seřadit knihy, které jste nasbírali. Každá práce je zadána s informacemi o následujících kategoriích:
-
autor
-
titul
-
Číslo stránky
-
Rok vydání
Nyní byste chtěli dát příteli tip na knihu, který by si vzal s sebou na příští schůzku. Bohužel můžete myslet pouze na autora, nikoli na název knihy. Zde vstupuje do hry VLOOKUP, protože pomocí této vstupní hodnoty dokáže jedním tahem vyhodit informace, které hledáte.
Jak se VLOOKUP používá?
Než vůbec pomyslíme na formulování vzorců, mělo by být určeno, kde bude vstupní pole a různá výstupní pole umístěna později. Chcete -li to provést, má smysl vytvořit samostatnou tabulku, která je zpočátku prázdná a umožňuje tak prostor pro uvedené informace. Pokud navrhnete tuto novou tabulku podle příkladu stávající tabulky, budete mít výhodu úspory času později.
Na tomto základě lze vzorec VLOOKUP vytvořit buď ručně, nebo automaticky vygenerovat v Excelu. Pro začátečníky stojí za to použít druhý přístup, abyste postupně poznali strukturu a účinek vzorce. K tomu je na kartě „Vzorce“ vybráno tlačítko pro „Vložit funkci“. VLOOKUP je skrytý v okně, které se otevře. Po potvrzení se znovu otevře okno, ve kterém lze vyplnit čtyři parametry vzorce. Tyto jsou:
-
Kritérium vyhledávání
-
matice
-
Sloupcový index
-
Area_reference
Surový koncept vzorce tedy vypadá takto:
= VLOOKUP (vyhledávací kritérium, matice, index sloupce, odkaz na rozsah)
a v jedné možné aplikaci takto:
= VLOOKUP (H3; A3: E40; 5)
Kritérium vyhledávání
Aby funkce věděla, která hodnota by měla být použita jako výchozí bod, je řádek, který byl vybrán jako vstupní pole o dva kroky dříve, uveden v poli „Kritérium hledání“. V našem příkladu je zde uvedeno jméno autora knihy „Phillip Pulmann“. Díky tomu je vzorec flexibilní a nemusí se znovu upravovat, jakmile se zadaná hodnota změní.
matice
Vstupní pole „Matice“ popisuje tabulku, ve které lze nalézt výstupní informace. Tato speciální matice tedy obsahuje také sloupce pro název knihy, číslo stránky a rok vydání.
Matice je zcela vybrána jednou bez nadpisů od levého horního po pravý dolní okraj. Excel tak ví, který obsah je třeba při hodnocení zohlednit.
Sloupcový index
Vstupní pole pro „index sloupce“ vyzve uživatele, aby definoval sloupec matice, ve kterém je uvedena pouze požadovaná hodnota. Přiřazení sloupců je číslováno chronologicky. To znamená, že první sloupec tabulky obdrží hodnotu 1, druhý hodnotu 2 atd. V našem případě to odpovídá sloupcovému indexu 1 pro autora, indexu sloupce 2 pro nadpis, indexu sloupce 3 pro číslo stránky a sloupcový index 4 za rok vydání.
Aby byla tabulka co nejflexibilnější, lze místo čísla propojit záhlaví sloupce. To má tu výhodu, že vzorec lze také bez problémů přenést do jiných řádků, protože záhlaví sloupců lze pokaždé flexibilně přizpůsobit.
Pozornost: VLOOKUP čte matici zleva doprava, a proto musí být index sloupce umístěn napravo od sloupce pro vyhledávací kritérium, aby ho funkce zohlednila!
Area_reference
Parametr "Range_Lookup" doplňuje vzorec VLOOKUP zadáním přesnosti, s jakou je tabulka vyhodnocována. Liší se však od výše uvedených složek vzorce, protože je volitelný. Pokud je hodnota 0 zadána jako „nesprávná“, Excel vyhledá pouze hodnotu, která byla zadána jako kritérium vyhledávání. S hodnotou 1 pro „true“ však hledání zjevných hodnot pokračuje, pokud se nepodařilo najít přesnou hodnotu.
Zadání tohoto parametru je volitelné, protože hodnota 1 je nastavena ve výchozím nastavení. Toto nastavení bude užitečné později v pokročilém VLOOKUP s více kritérii vyhledávání.
Sloučení
Jakmile jsou nastaveny všechny potřebné parametry, lze použít VLOOKUP. Po zadání vyhledávacího kritéria a potvrzení funkce se hledaná hodnota objeví v řádku, který byl definován jako výstupní pole.
V našem příkladu se nyní zobrazí název knihy „Zlatý kompas“, který odpovídá zadanému autorovi. Aby bylo možné rychle zjistit číslo stránky a rok vydání, není třeba dělat nic jiného, než přetáhnout stávající vzorec VLOOKUP do následujících buněk. To je tak snadné, protože index sloupce VLOOKUP byl spojen s nadpisem sloupce první tabulky a druhá tabulka je také strukturována ve stejném pořadí.
V případě, že by se tabulky od sebe navzájem lišily nebo by přesto došlo k chybě, lze vzorec VLOOKUP změnit také ručně. Chcete -li to provést, musí být předposlední číslice indexu sloupce přiřazena ke sloupci nové hodnoty, která má být vydána.
VLOOKUP s více kritérii vyhledávání
Často se stává, že jediné kritérium vyhledávání nestačí k přesnému vyhodnocení velké tabulky aplikace Excel. Pak má smysl spustit VLOOKUP s několika kritérii vyhledávání. K tomu musí být stávající vzorec doplněn o další funkci IF. Tímto způsobem lze během aplikace zohlednit až osm různých vyhledávacích kritérií.
VLOOKUP ve více excelových tabulkách
Pokud vyhledávací kritérium nelze nalézt pouze v jedné tabulce, ale případně také v jiné, lze vzorec VLOOKUP odpovídajícím způsobem upravit. Za tímto účelem musí být funkce if a ISERROR umístěny před stávající vzorec. K tomu je zapotřebí pět parametrů:
-
Kritérium vyhledávání
-
Matrix1 a Matrix 2
-
Sloupec index1 a sloupec index2
Výsledek vypadá takto:
= IF (ISERROR (VLOOKUP (kritérium hledání, matice1, sloupec-index1, 0));
VLOOKUP (vyhledávací kritérium; matice2; sloupcový index2,0); VLOOKUP (vyhledávací kritérium; matice1; sloupcový index1;))
a v jedné možné aplikaci takto:
= IF (ISERROR (VLOOKUP (E5, A5: B9,2, 0)), VLOOKUP (E5, A13: B17,2, 0), VLOOKUP (E5, A5: B9,2, 0))
Kritérium vyhledávání slouží k vložení hodnoty, která má být hledána, do dvou tabulek. Matrix1 a Matrix2 definují příslušné oblasti buněk obou tabulek. Sloupec index1 a sloupec index2 slouží k podrobnějšímu definování, které sloupce příslušných tabulek je třeba prohledávat.
Pokud se hledaná hodnota vyskytuje v obou tabulkách, Excel vygeneruje výsledek z první tabulky. Pokud však hodnota není nalezena v žádné ze dvou tabulek, zobrazí se chybová zpráva. Výhodou vzorce je, že dva seznamy nemusí mít stejnou strukturu nebo stejnou velikost.
Přiřaďte hodnoty kategoriím pomocí VLOOKUP
Další funkce VLOOKUP umožňuje automatické rozdělení uvedených hodnot na písmena a predikáty podle vašeho výběru. V našem předchozím příkladu by měl být pro typ knihy vložen další sloupec tabulky. Knihy o délce až 50 stran by měly spadat do žánru povídky, přičemž k novele jsou přiřazeny knihy od 51 do 150 stran a k románu od 151 stran. Aby to bylo možné, není ve VLOOKUPU vyžadován žádný další vzorec, pouze použití složených závorek „{}“. Hotový vzorec vypadá takto:
= VLOOKUP (B1; {1. "Povídka"; 51. "Novella"; 151. "Román"}; 2)
Obsah složených závorek označuje matici, která definuje oblast příslušného typu knihy. Přiřazení délky strany příslušnému rodu je proto uloženo v závorkách. Vzorec používá dvojice hodnot, z nichž každá je oddělena bodem. Matice {1. "Povídka"; 51. "Novella"; 151. "Román"} se čte takto:
„Od 1 ukaž povídku, od 51 uběhne novela, od 151 ukáže román.“
Tuto matici lze snadno přizpůsobit různým úkolům. To se týká na jedné straně velikosti a počtu matric a také jejich označení. Místo jednotlivých písmen je tedy možné ve výsledku vygenerovat řetězce nebo čísla. Stačí upravit písmena ve vzorci.
VLOOKUP na více pracovních listech
Další funkce VLOOKUP umožňuje svým uživatelům propojit obsah, který se nachází v různých tabulkách. V našem případě může být tato možnost užitečná, když jsou informace nejprve seřazeny v různých listech a poté aktualizovány v souhrnné tabulce.
Představte si, že kromě svých knih uvedete do tabulky aplikace Excel také své sebrané filmy. Poté obě kolekce zkombinujete do jedné velké tabulky.
Výhoda tohoto postupu spočívá nejen ve zvýšeném pořadí, ale také v zamezení potenciálních chyb. Pokud chcete vytvořit nový záznam nebo aktualizovat stávající, nemusíte hledat ve velké tabulce, ale místo toho můžete přistupovat k těm menším. Hodnoty jsou poté automaticky přeneseny do souhrnné tabulky aplikace Excel. Tím je přepisování ve velké tabulce nadbytečné, což se v nejlepším případě vyhne nešťastnému pohybu a následnému řetězení chybových zpráv.
Jak vypadá vzorec?
Tato funkce je opět umožněna vložením jiného vzorce. Při hledání s více kritérii byl vyžadován další vzorec IF, práce s více listy vyžaduje NEPŘÍMÝ vzorec. To umožňuje zadat pro matici VLOOKUP rozsah z jiné tabulky.
= VLOOKUP (vyhledávací kritérium; NEPŘÍMÝ (matice); index sloupce; odkaz na rozsah)
Pozornost: Tento vzorec bude fungovat, pouze pokud mají jednotlivé tabulky v různých listech stejné názvy jako záhlaví sloupců obecné tabulky. Celé tabulky lze pojmenovat v poli „Název“ vlevo nahoře nad mřížkou buněk. Tabulky, které již byly pojmenovány, lze zobrazit pomocí kombinace kláves Ctrl + F3.
Řešení vznikajících chybových zpráv
Práce s propojenými excelovými tabulkami může vést k nežádoucím problémům. To zahrnuje zejména výstup nesprávných hodnot. V případě, že je na výstupu špatná hodnota 0, nastal v nastavení Excelu malý problém, který lze rychle opravit.
Běžná chybová zpráva #NV je na druhé straně záměrnou funkcí VLOOKUP, která uživateli naznačuje, že požadovaná hodnota není k dispozici. Tato poznámka může být navržena odlišně pomocí vzorce.
VLOOKUP - přehled
VLOOKUP je užitečná funkce Excelu, kterou lze použít k vyhledávání a vyhodnocování tabulek. Jeho výhody jsou evidentní v jeho uživatelsky přívětivé a flexibilní aplikaci. Z této funkce může mít prospěch každý, kdo pravidelně pracuje s tabulkami aplikace Excel. Ať už je to soukromý sběratel, který vytváří vlastní malé tabulky, nebo velká společnost, která zpracovává podstatně významnější datové sady.
Pokud na druhé straně stále máte nezodpovězené požadavky, které VLOOKUP nemohl splnit, můžete se těšit na další možnost Excelu: Microsoft nabízí uživatelům Excelu 365 nový XLOOKUP od začátku 2022-2023. Toto staví na kompetencích VLOOKUP a doplňuje je o další, někdy i jednodušší, funkce. V tomto bodě se proto také otevírá nová rutina v hodnocení dat.
