Vytvoření vyhledávacího pole v Excelu zlepšuje funkčnost vašich tabulek tím, že usnadňuje filtrování a rychlý přístup ke konkrétním datům. Tato příručka popisuje několik metod implementace vyhledávacího pole pro různé verze aplikace Excel. Ať už jste začátečník nebo pokročilý uživatel, tyto kroky vám pomohou nastavit dynamické vyhledávací pole pomocí funkcí, jako je funkce FILTR, podmíněné formátování a různé vzorce.

• Snadno vytvořte vyhledávací pole pomocí funkce FILTER
  (k dispozici v Excelu 2019 a novějších, Excel pro Microsoft 365)
• Vytvořte vyhledávací pole pomocí Podmíněné formátování
  (k dispozici ve všech verzích aplikace Excel)
• Vytvořte vyhledávací pole pomocí kombinace vzorců
  (k dispozici ve všech verzích aplikace Excel)

Snadno vytvořte vyhledávací pole pomocí funkce FILTER

• Tato funkce automaticky aktualizuje výstup při změně vašich dat.
• Funkce FILTER může vrátit libovolný počet výsledků, od jednoho řádku až po tisíce, v závislosti na tom, kolik záznamů ve vaší datové sadě odpovídá kritériím, která jste nastavili.

Zde vám ukážu, jak pomocí funkce FILTER vytvořit vyhledávací pole v Excelu.

Krok 1: Vložte textové pole a nakonfigurujte vlastnosti

1. Přejít na Vývojka klepněte na kartu Vložit > Text Box (ovládání ActiveX).
   Tip: Pokud je Vývojka karta se na pásu karet nezobrazuje, můžete ji povolit podle pokynů v tomto kurzu: Jak zobrazit / zobrazit kartu vývojáře na pásu karet aplikace Excel?
   
2. Kurzor se změní na křížek a potom je třeba přetáhnout kurzor, abyste nakreslili textové pole na místo v listu, kam chcete textové pole umístit. Po nakreslení textového pole uvolněte myš.
   
3. Klikněte pravým tlačítkem na textové pole a vyberte Nemovitosti z kontextové nabídky.
   
4. v Nemovitosti propojte textové pole s buňkou zadáním odkazu na buňku do pole Propojená buňka pole. Například zadáním "J2" zajišťuje, že všechna data zadaná do textového pole se automaticky aktualizují v buňce J2 a naopak.
   
5. Klepněte na tlačítko Režim návrhu pod Vývojka pro ukončení režimu návrhu.
   

Textové pole nyní umožňuje zadat text.

Krok 2: Použijte funkci FILTER

1. Před použitím funkce FILTER zkopírujte původní řádek záhlaví do nové oblasti. Zde umístím řádek záhlaví pod vyhledávací pole.
   Tip: Tento přístup umožňuje uživatelům jasně vidět výsledky pod stejnými záhlavími sloupců jako původní data.
   
2. Vyberte buňku pod prvním záhlavím (např I5 v tomto příkladu), zadejte do něj následující vzorec a stiskněte vstoupit klíč k získání výsledku.

   =FILTER(Sheet2!$A$5:$G$281,Sheet2!$B$5:$B$281=J2,"No data found")

   
   Jak je znázorněno na výše uvedeném snímku obrazovky, protože textové pole nyní nemá žádný vstup, vzorec zobrazí výsledek "Nenalezena žádná data" v I5.

Výsledek: Otestujte vyhledávací pole

Nyní otestujeme vyhledávací pole. V tomto příkladu, když zadám jméno zákazníka do vyhledávacího pole, budou odpovídající výsledky filtrovány a okamžitě zobrazeny.

Vytvořte vyhledávací pole pomocí podmíněného formátování

Podmíněné formátování lze použít ke zvýraznění dat, která odpovídají hledanému výrazu, čímž se nepřímo vytvoří efekt vyhledávacího pole. Tato metoda nefiltruje data, ale vizuálně vás navede do příslušných buněk. Tato část vám ukáže, jak vytvořit vyhledávací pole pomocí podmíněného formátování v aplikaci Excel.

Krok 1: Vložte textové pole a nakonfigurujte vlastnosti

1. Přejít na Vývojka klepněte na kartu Vložit > Text Box (ovládání ActiveX).
   Tip: Pokud je Vývojka karta se na pásu karet nezobrazuje, můžete ji povolit podle pokynů v tomto kurzu: Jak zobrazit / zobrazit kartu vývojáře na pásu karet aplikace Excel?
   
2. Kurzor se změní na křížek a potom je třeba přetáhnout kurzor, abyste nakreslili textové pole na místo v listu, kam chcete textové pole umístit. Po nakreslení textového pole uvolněte myš.
   
3. Klikněte pravým tlačítkem na textové pole a vyberte Nemovitosti z kontextové nabídky.
   
4. v Nemovitosti propojte textové pole s buňkou zadáním odkazu na buňku do pole Propojená buňka pole. Například zadáním "J3" zajišťuje, že všechna data zadaná do textového pole se automaticky aktualizují v buňce J3 a naopak.
   
5. Klepněte na tlačítko Režim návrhu pod Vývojka pro ukončení režimu návrhu.
   

Textové pole nyní umožňuje zadat text.

Krok 2: Použijte podmíněné formátování pro vyhledávání dat

1. Vyberte celý rozsah dat, který chcete prohledat. Zde vybírám rozsah A3:G279.
2. Pod Domů klepněte na kartu Podmíněné formátování > Nové pravidlo.
   
3. v Nové pravidlo pro formátování dialogové okno:
   1. vybrat Pomocí vzorce určete, které buňky chcete formátovat v Vyberte typ pravidla možnosti.
   2. Zadejte následující vzorec do Formátovat hodnoty, kde je tento vzorec pravdivý box.

      =$B3=$J$3

      Zde, $ B3 představuje první buňku ve sloupci, kterou chcete porovnat s kritérii vyhledávání ve vybraném rozsahu, a 3 $ J$ je buňka propojená s vyhledávacím polem.
   3. Klepněte na tlačítko Formát určete barvu výplně pro výsledky vyhledávání.
   4. Klepněte na tlačítko OK knoflík. Viz screenshot:
      

Výsledek

Nyní otestujeme vyhledávací pole. V tomto příkladu, když zadám jméno zákazníka do vyhledávacího pole, odpovídající řádky obsahující tohoto zákazníka ve sloupci B se okamžitě zvýrazní zadanou barvou výplně.

Vytvořte vyhledávací pole s kombinacemi vzorců

Pokud nepoužíváte nejnovější verzi aplikace Excel a nechcete pouze zvýrazňovat řádky, může být užitečná metoda popsaná v této části. Pomocí kombinace vzorců aplikace Excel můžete vytvořit funkční vyhledávací pole v jakékoli verzi aplikace Excel. Postupujte podle níže uvedených kroků.

Krok 1: Vytvořte seznam jedinečných hodnot z vyhledávacího sloupce

1. V tomto případě vyberu a zkopíruji rozsah B4: B281 do nového pracovního listu.
2. Po vložení rozsahu do nového listu ponechte vložená data vybraná a přejděte na Data Kartu a vyberte Odebrat duplikáty.
   
3. V otvoru Odebrat duplikáty dialogové okno, klepněte na tlačítko OK .
   
4. A Microsoft Excel Zobrazí se okno s výzvou, které ukazuje, kolik duplikátů bylo odstraněno. Klikněte OK.
   
5. Po odstranění duplikátů vyberte všechny jedinečné hodnoty v seznamu s výjimkou záhlaví a přiřaďte tomuto rozsahu název zadáním do pole Příjmení box. Zde jsem rozsah pojmenoval jako Zákazník.
   

Krok 2: Vložte pole se seznamem a nakonfigurujte vlastnosti

1. Vraťte se na list obsahující sadu dat, kterou chcete prohledávat. Přejít na Vývojka klepněte na kartu Vložit > Rozbalovací seznam (ovládací prvek ActiveX).
   Tip: Pokud je Vývojka karta se na pásu karet nezobrazuje, můžete ji povolit podle pokynů v tomto kurzu: Jak zobrazit / zobrazit kartu vývojáře na pásu karet aplikace Excel?
   
2. Kurzor se změní na křížek a potom musíte přetáhnout kurzor, abyste nakreslili pole se seznamem na místo v listu, kam chcete umístit vyhledávací pole. Po nakreslení pole se seznamem uvolněte myš.
   
3. Klikněte pravým tlačítkem na pole se seznamem a vyberte Nemovitosti z kontextové nabídky.
   
4. v Nemovitosti podokno:
   1. Propojte pole se seznamem s buňkou zadáním odkazu na buňku do pole Propojená buňka pole. píšu ji"M2".
      Tip: Zadáním tohoto pole zajistíte, že všechna data zadaná do pole se seznamem se automaticky aktualizují v buňce M2 a naopak.
   2. v ListFillRange zadejte pole název rozsahu zadaný pro jedinečný seznam v kroku 1.
   3. Změň MatchEntry pole do 2 – fmMatchEntryNone.
   4. Zavři Nemovitosti panel.
      
5. Klepněte na tlačítko Režim návrhu pod Vývojka pro ukončení režimu návrhu.
   

Nyní můžete vybrat libovolnou položku z pole se seznamem nebo zadat text, který chcete vyhledat.

Krok 3: Aplikujte vzorce

1. Vytvořte tři pomocné sloupce sousedící s původním rozsahem dat. Viz snímek obrazovky:
   
2. V buňce (H5) pod záhlavím prvního pomocného sloupce zadejte následující vzorec a stiskněte vstoupit.

   =ROWS($B$5:B5)

   Zde B5 je buňka obsahující jméno prvního zákazníka ve sloupci, který se má prohledávat.
   
3. Dvakrát klikněte na pravý dolní roh buňky vzorce, následující buňka automaticky vyplní stejný vzorec.
   
4. V buňce (I5) pod záhlavím druhého pomocného sloupce zadejte následující vzorec a stiskněte vstoupit. A potom dvakrát klikněte na pravý dolní roh buňky vzorce, aby se buňky níže automaticky vyplnily stejným vzorcem.

   =IF(ISNUMBER(SEARCH($M$2,B5)),H5,"")

   Zde M2 je buňka spojená se seznamem.
   
5. V buňce (J5) pod záhlavím třetího pomocného sloupce zadejte následující vzorec a stiskněte vstoupit. A potom dvakrát klikněte na pravý dolní roh buňky vzorce, aby se buňky níže automaticky vyplnily stejným vzorcem.

   =IFERROR(SMALL($I$5:$I$281,H5),"") 

   
6. Zkopírujte původní řádek záhlaví do nové oblasti. Zde umístím řádek záhlaví pod vyhledávací pole.
   
7. Vyberte buňku pod prvním záhlavím (např L5 v tomto příkladu) do něj zadejte následující vzorec a stiskněte klávesu Enter.

   =IFERROR(INDEX($A$5:$G$281,$J5,COLUMNS($L$4:L4)),"")

   Zde A5: G281 je celý rozsah dat, který chcete zobrazit ve výsledné buňce.
   
8. Vyberte tuto buňku vzorce a přetáhněte ji Naplňte rukojeť doprava a poté dolů, abyste použili vzorec na odpovídající sloupce a řádky.
   
   Poznámky:

   • Protože ve vyhledávacím poli není žádný vstup, výsledky vzorce zobrazí nezpracovaná data.
   • Tato metoda nerozlišuje velká a malá písmena, což znamená, že bude odpovídat textu bez ohledu na to, zda píšete velká nebo malá písmena.

Výsledek

Nyní otestujeme vyhledávací pole. V tomto příkladu, když zadám nebo vyberu jméno zákazníka z rozbalovacího pole, budou odpovídající řádky obsahující toto jméno zákazníka ve sloupci B filtrovány a okamžitě zobrazeny v rozsahu výsledků.

Vytvoření vyhledávacího pole v Excelu může výrazně zlepšit vaši interakci s daty, takže vaše tabulky budou dynamičtější a uživatelsky přívětivější. Ať už zvolíte jednoduchost funkce FILTER, vizuální asistenci podmíněného formátování nebo všestrannost kombinací vzorců, každá metoda poskytuje cenné nástroje pro vylepšení vašich možností manipulace s daty. Experimentujte s těmito technikami a zjistěte, která nejlépe vyhovuje vašim konkrétním potřebám a datovým scénářům. Pro ty, kteří se chtějí hlouběji ponořit do možností Excelu, se náš web může pochlubit velkým množstvím výukových programů. Zde najdete další tipy a triky pro Excel.

Související články