Vyhledávací lišta v Excelu | Excelové triky

V dnešním videu si ukážeme excelový trik, kterým nejen že zaujmete své kolegy a nadřízené, ale rovněž si tím zrychlíte a zefektivníte práci v Excelu. Řeč je o vyhledávací liště, pomocí které můžete ve zdrojové tabulce vyhledávat, podle toho, co do vyhledávací lišty napíšete.  A když vydržíte až do konce videa, tak si ukážeme, jak vyhledávací lištu zformátovat, tak aby bylo na první pohled patrné, že se jedná o vyhledávací lištu.  

Excelový soubor ke stažení

Vyhledávací lišta v Excelu

V excelovém sešitu máme zdrojovou tabulku s produkty. Z této zdrojové tabulky bychom chtěli do vedlejší tabulky vyfiltrovat data podle produktů. K vyhledání produktů použijeme vyhledávací lištu z prvku Vývojáře a novou dynamickou funkci FILTER, která je dostupná pro předplatitele Microsoft 365 a Office 2021.

Začneme tím, že do excelového listu vložíme vyhledávací lištu. Na to ale nejprve musíme mít aktivovanou kartu Vývojáře. Pokud kartu Vývojáře nemáte v Excelu aktivovanou, tak to jednoduše uděláte tak, že kliknete pravým tlačítkem myši na horní lištu v Excelu a vyberete Přizpůsobit pás karet. 

Vyhledávací lišta 1

V příkazech vyberete Všechny karty a mezi nimi najdete kartu Vývojář. Kartu označíte a vyberete Přidat. Vývojář se tím přidá do horní lišty v Excelu a následně to celé potvrdíte.

Vyhledávací lišta 2

Když máme kartu Vývojáře v liště, tak můžeme přidat vyhledávací lištu. V kartě Vývojáře vybereme Vložit a v ovládacích prvcích X vybereme Textové pole. Textové pole bude sloužit jako naše vyhledávací lišta. 

Stejně jako ostatní prvky vývojáře se i textové pole do listu Excelu kreslí. Nakreslíme tedy pole a podle potřeby upravíme jeho velikost. Rovnou propojíme textové pole s ovládací buňkou, která bude ovládat vyhledávací lištu. Na rozdíl od ostatních prvků Vývojáře se textové pole neupravuje přes Formát prvku, ale musíme na pole kliknout pravým tlačítkem myši a vybrat Vlastnosti. 

Zhruba uprostřed tabulky najdeme pole, které se jmenuje Linked Cell, tedy propojená buňka. Klikneme do tohoto pole a do tohoto pole musíme propojenou buňku napsat ručně. Řekněme, že ovládací buňkou bude třeba buňka N1. Napíšeme tedy N1 a následně vypneme tabulku křížkem.

Zkusíme zda propojená buňka funguje. Než ale budeme moci do textového pole cokoliv napsat, tak musíme vypnout režim pro úpravy. Musíme pole označit a na kartě Vývojáře odkliknout Režim návrhu. 

Teď budeme moci do textového pole psát. Zkusíme napsat třeba Košile a vidíme, že cokoliv, co napíšeme do textového pole se okamžitě propíše do propojené buňky.

Základem řešení bude nová dynamická funkce FILTER. Do tabulky napíšeme funkci FILTER, ve které nejprve označíme pole, které chceme filtrovat. Do výsledně tabulky chceme filtrovat celou zdrojovou tabulku se všemi sloupci, takže označíme celou zdrojovou tabulku.

Vyhledávací lišta 8

V parametru zahrnuje musíme ověřit logickou podmínku, podle které se bude tabulka filtrovat. Tento logický test bude založený na funkci HLEDAT, anglicky funkce SEARCH. Tato funkce ověří, že se ve sloupci s produkty nachází hledané slovo ve vyhledávací liště. Začneme s funkcí HLEDAT. Prvním parametrem funkce HLEDAT je, co hledáme. Hledáme to, co je ve vyhledávací liště, což se zobrazí v propojené buňce N1. A kde toto slovo nebo část slova hledáme? To hledáme ve sloupci s produkty.

Ukončíme funkce a potvrdíme. Funkce FILTER vrátila chybu. Proč? Protože funkce HLEDAT vrátí jedničku na řádcích, kde našla slovo Košile. Funkce FILTER ale umí v parametru zahrnuje pracovat pouze s pravdou nebo nepravdou. Takže musíme funkci HLEDAT zabalit do funkce JE.ČISLO, která jedničky převede na pravdy a ostatní řádky na nepravdy. Funkci ukončíme a potvrdíme funkci FILTER.

Vyhledávací lišta 10

Funkce FILTER vrátila ze zdrojové tabulky všechny záznamy pro produkt Košile. Vyhledávací lišta bude fungovat i pro část slov. Můžeme tak vyhledat například Bo, což okamžitě začne filtrovat tabulku pro produkty Polobotky a Botasky. 

Vyhledávací lišta 11

Vyhledávací lišta tak filtruje i podle části slov nebo písmen, které se nacházejí i uprostřed nebo na konci slova. Když slovo dopíšeme na Bota, tak vyhledávací lišta vyfiltruje tabulku pouze pro produkt botasky.

Vyhledávací lišta 12

Pokud vás funkce FILTER zaujala a chtěli byste se s ní naučit pracovat, tak na Akademii Excelu máme online kurz Excel 365 Masterclass, který je celý zaměřený na práci s dynamickými poli a funkcemi, mezi kterými je i funkce FILTER. V kurzu vás naučíme efektivně využívat nová dynamická pole a dynamické funkce jako SROVNAT.SVISLE, FILTER, UNIQUE nebo funkci LAMBDA a funkci LET. 

Než se pustíme do formátování vyhledávací lišty, tak bychom měli ještě ošetřit případ, že budeme vyhledávat slovo, které v tabulce není. V takovém případě by funkce FILTER vrátila chybovou hlášku. Vrátíme se tedy k funkci FILTER a vyplníme i poslední nepovinný parametr, pokud nenalezeno. A v případě, že budeme hledat produkt, který ve sloupci s produkty není, tak chceme vrátit slovo Nenalezeno. 

Vyhledávací lišta 13

Funkci potvrdíme a zkusíme vyhledat produkt, který v tabulce nemáme. Třeba Župany, napíšeme písmeno ž, a tabulka vrátí produkty bižuterie, jakmile ale napíšeme ŽU, tak funkce FILTER vrátí slovo Nenalezeno.

Vyhledávací lištu můžeme trochu zformátovat, aby bylo na první pohled patrné, že se jedná o vyhledávací lištu. Do excelového listu vložíme tvar obdélníků. Klikneme na lištu Vložení a najdeme Obrazce a obdélník. Pokud chceme nakreslit čtverec, tak musíme předtím, než začneme tvar kreslit, zmáčknout klávesu SHIFT a držet ji po celou dobu kreslení. Tím se nakreslí čtverec. Čtverec přizpůsobíme velikosti vyhledávací lišty. 

Můžeme změnit barvu čtverce, a to tak, že na čtverec klikneme a na liště Formát obrazce vybereme Výplň a vybereme třeba světle zelenou a tu samou barvu vybereme i pro ohraničení obrazce. 

Teď klikneme na lištu Vložení a najdeme Ikony a ve vyhledávací liště najdeme lupu. 

Vyhledávací lišta 16

Vybereme si ikonu, která se nám líbí a vložíme ji do sešitu.  Ikonu přizpůsobíme velikosti čtverce a vložíme lupu do čtverce. Teď je patrné, že se jedná o vyhledávací lištu. 

Vyhledávací lišta 17

Pokud bychom chtěli upravit i samotnou vyhledávací lištu, tak musíme opět na kartě Vývojáře zapnout Režim návrhu a označit lištu. Teď ji můžeme opět formátovat. Tvar i lištu zformátujeme podle potřeby a jsme hotovi.

MOHLO BY VÁS ZAJÍMAT

3 komentáře

  1. Dobrý den!
    Bohužel tentokráte nejde přehrát Vaše video protože se mi stále vnucuje přehrávání “Video Placeholder”. Zkoušel jsem YouTube a je to stejné.

    S pozdravem

    Oldřich Svoboda, senior😒

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *