S tímto tématem se setkáte na našich kurzech ExcelTown.
Vzhledem k uvolnění opatření kolem koronaviru předpokládáme, že se kurzy budou normálně konat.

Autor: Miroslav Lorenc

V následujících příkladech si ukážeme, jak využít vyhledávací funkce Excelu k zobrazení dílčích informací o zaměstnancích. Představte si situaci, že máme velkou tabulku - např. evidenci zaměstnanců firmy. V takové tabulce budou údaje o jménu, příjmení, rodném čísle, bydlišti, zdravotní pojišťovně a mnohé další. 

První varianta vyhledávání

Vedle této “velké” tabulky si připravíme “malou” tabulku, do které budeme vypisovat vybrané údaje o zaměstnanci. K vyhledávání údajů pomocí jednoznačného identifikátoru slouží např. funkce SVYHLEDAT (VLOOKUP).

Dejme tomu, že na základě zadaného osobního čísla chceme vypsat jméno a příjmení zaměstnance. K tomu můžeme využít funkci SVYHLEDAT. 

Funkce SVYHLEDAT(L2;A:J;2;0) vezme osobní číslo 1003 uvedené v buňce L2, vyhledá jej v prvním sloupci velké tabulky (tu máme ve sloupcích A:J) a vrátí nám údaj uvedený ve druhém sloupci tabulky - tedy křestní jméno.

Obdobně bychom mohli najít k danému osobnímu číslu příjmení - SVYHLEDAT(L2;A:J;3;0).

Pokud chceme vypsat jméno i příjmení najednou, můžeme použít funkce CONCATENATE, CONCAT nebo jednoduše spojit jméno, mezeru a příjmení pomocí textového operátoru & (na klávesnici napíšete levým ALTem + 38 na numerické klávesnici nebo pravým ALTem + c):

  • =SVYHLEDAT(L2;A:J;2;0)&" "&SVYHLEDAT(L2;A:J;3;0).

Pro případ, že by nebylo osobní číslo v L2 vyplněno, uzavřeme celý výraz do funkce KDYŽ. Celý vzorec v buňce L2 by tedy mohl vypadat takto:

  • =KDYŽ(L2="";"";SVYHLEDAT(L2;A:J;2;0)&" "&SVYHLEDAT(L2;A:J;3;0))

Takto to funguje skvěle. 

Druhá varianta vyhledávání

Tuhle se nás ale paní na školení ptala (a my jsme rádi, když vás Excel baví a ptáte se 🙂 ), jak to zařídit, aby si mohla vybrat sloupec, který jí z velké tabulky zajímá a Excel jí vypsal pro daný záznam údaj z tohoto sloupce.

I to samozřejmě jde. Pojďme tedy upravit vyhledávací (“malou”) tabulku následovně: v buňce M1 budeme mít postupně různé názvy sloupců z “velké” tabulky a do sloupce M se nám tedy budou (na základě zadaných osobních čísel) vypisovat údaje z vybraného sloupce. Pokud v M1 vybereme Rodné číslo, vypíší se k osobním číslům rodná čísla, když v M1 vybereme Mobil, vypíší se čísla mobilních telefonů.

Nejprve si do tabulky připravíme rozbalovací seznamy, pomocí kterých budeme zadávat, kteří zaměstnanci nás zajímají a jaký údaj o nich chceme vypsat. Rozevírací seznam v buňce vytvoříte pomocí Data - Datové nástroje - Ověření dat. Zde zvolíme Povolit: Seznam a jako zdroj uvedeme adresu buněk, kde jsou ve velké tabulce osobní čísla. Dále můžeme zadat Zprávu při zadávání a Chybové hlášení

Obdobně pro buňku M1 vybereme jako zdroj dat pro rozevírací seznam oblast A1:J1.

V buňce je tedy možné vybírat si z názvů sloupců:

Nyní již přistoupíme k zapsání vzorce do M2.

Pro vyhledávání ve velké tabulce použijeme funkci INDEX. Protože chceme měnit vyhledávaný sloupec podle údaje v M1 a podle osobního čísla v L2, vnoříme do funkce INDEX dvě funkce POZVYHLEDAT. Celou funkci ještě vnoříme do funkce IFERROR, abychom nedostávali chybovou hlášku předtím, než vybereme osobní číslo a požadovaný sloupec. Celý zápis by mohl vypadat takto:

=IFERROR(INDEX(A:J;POZVYHLEDAT(L2;A:A;0);POZVYHLEDAT($M$1;$A$1:$J$1;0));"")

 Ke stažení

Zdrojové obrázky, video i soubor Excelu jsou ke stažení v podkladech ke článku.

S tímto tématem se setkáte na našich kurzech ExcelTown.
Vzhledem k uvolnění opatření kolem koronaviru předpokládáme, že se kurzy budou normálně konat.

Napsat komentář

Vaše emailová adresa nebude publikována.

*

smazat formulářOdeslat komentář