Excel filtr je jednou z nejpoužívanějších funkcí při práci s daty. V tomto blogovém příspěvku vám ukážu, jak vytvořit vyhledávací pole dynamického filtru aplikace Excel tak, aby filtrovalo data podle toho, co do vyhledávacího pole napíšete.
Něco, jak je uvedeno níže:
Existuje dvojí funkce - můžete vybrat název země z rozevíracího seznamu, nebo můžete data zadat ručně do vyhledávacího pole a zobrazí se vám všechny odpovídající záznamy. Když například zadáte „I“, zobrazí se vám všechny názvy zemí s abecedou I.
Podívejte se na video - Vytvoření dynamického vyhledávacího pole filtru Excel
Vytvoření vyhledávacího pole dynamického filtru aplikace Excel
Tento filtr Dynamic Excel lze vytvořit ve 3 krocích:
- Získání jedinečného seznamu položek (v tomto případě zemí). To by bylo použito při vytváření rozevíracího seznamu.
- Vytvoření vyhledávacího pole. Zde jsem použil Combo Box (ActiveX Control).
- Nastavení dat. Zde bych použil tři pomocné sloupce se vzorci k extrahování odpovídajících dat.
Takto vypadají nezpracovaná data:
UŽITOČNÝ TIP: Téměř vždy je vhodné převést data do tabulky aplikace Excel. To lze provést výběrem libovolné buňky v datové sadě a použitím klávesové zkratky Control + T.
Krok 1 - Získání jedinečného seznamu položek
- Vyberte všechny země a vložte jej do nového listu.
- Vyberte seznam zemí -> Přejít na data -> Odebrat duplikáty.
- V dialogovém okně Odebrat duplikáty vyberte sloupec, ve kterém máte seznam, a klikněte na Ok. Tím odstraníte duplikáty a získáte jedinečný seznam, jak je uvedeno níže:
- Dalším krokem je vytvoření pojmenovaného rozsahu pro tento jedinečný seznam. Udělat toto:
- Přejděte na kartu Vzorec -> Definovat jméno
- V dialogovém okně Definovat jméno:
- Název: CountryList
- Rozsah: Sešit
- Odkazuje na: = UniqueList! $ A $ 2: $ A $ 9 (Seznam mám na samostatné kartě s názvem UniqueList v A2: A9. Můžete odkazovat všude tam, kde se nachází váš jedinečný seznam)
POZNÁMKA: Pokud používáte metodu „Odebrat duplikáty“ a rozšiřujete svá data o další záznamy a nové země, budete muset tento krok zopakovat znovu. Alternativně můžete také vytvořit vzorec, aby byl tento proces dynamický.
Krok 2 - Vytvoření vyhledávacího pole dynamického filtru Excel
Aby tato technika fungovala, museli bychom vytvořit „vyhledávací pole“ a propojit ho s buňkou.
K vytvoření tohoto filtru vyhledávacího pole můžeme použít Combo Box v Excelu. Tímto způsobem, kdykoli zadáte cokoli do pole se seznamem, projeví se to také v buňce v reálném čase (jak je uvedeno níže).
Zde jsou kroky, jak toho dosáhnout:
- Přejděte na kartu Vývojář -> Ovládací prvky -> Vložit -> Ovládací prvky ActiveX -> Kombinované pole (Ovládací prvky ActiveX).
- Pokud nemáte kartu vývojáře viditelnou, postupujte podle následujících pokynů.
- Pokud nemáte kartu vývojáře viditelnou, postupujte podle následujících pokynů.
- Klikněte kdekoli na listu. Vloží pole se seznamem.
- Klepněte pravým tlačítkem na pole se seznamem a vyberte Vlastnosti.
- V okně Vlastnosti proveďte následující změny:
- Propojená buňka: K2 (můžete vybrat libovolnou buňku, kde chcete, aby zobrazovala vstupní hodnoty. Tuto buňku použijeme při nastavování dat).
- ListFillRange: CountryList (toto je pojmenovaný rozsah, který jsme vytvořili v kroku 1. Tím by se zobrazily všechny země v rozevíracím seznamu).
- MatchEntry: 2-fmMatchEntryNone (to zajišťuje, že slovo není automaticky dokončeno při psaní)
- Když je vybráno pole se seznamem, přejděte na kartu Vývojář -> Ovládací prvky -> Klikněte na Režim návrhu (tím se dostanete z režimu návrhu a nyní můžete do pole se seznamem psát cokoli. Nyní se cokoli, co zadáte, projeví v buňce K2 v reálném čase)
Krok 3 - Nastavení dat
Nakonec vše propojíme pomocí pomocných sloupců. K filtrování dat zde používám tři pomocné sloupce.
Pomocný sloupec 1: Zadejte sériové číslo všech záznamů (v tomto případě 20). K tomu můžete použít vzorec ROWS ().
Pomocný sloupec 2: V pomocném sloupci 2 kontrolujeme, zda text zadaný do vyhledávacího pole odpovídá textu v buňkách ve sloupci země.
To lze provést pomocí kombinace funkcí IF, ISNUMBER a SEARCH.
Zde je vzorec:
= IF (ISNUMBER (HLEDAT ($ K $ 2, D4)), E4, "")
Tento vzorec vyhledá obsah ve vyhledávacím poli (které je propojeno s buňkou K2) v buňce s názvem země.
Pokud existuje shoda, tento vzorec vrátí číslo řádku, jinak vrátí prázdné místo. Pokud má například pole se seznamem hodnotu „USA“, všechny záznamy se zemí jako „USA“ budou mít číslo řádku a všechny ostatní budou prázdné („“)
Pomocný sloupec 3: V pomocném sloupci 3 potřebujeme shromáždit všechna čísla řádků ze sloupce Pomocník 2. K tomu můžeme použít kombinaci IFERROR a MALÉ vzorce. Zde je vzorec:
= IFERROR (MALÉ ($ F $ 4: $ F $ 23, E4), "")
Tento vzorec shromažďuje všechna odpovídající čísla řádků dohromady. Pokud má například pole se seznamem hodnotu US, všechna čísla řádků s číslem „USA“ se spojí dohromady.
Když máme čísla řádků naskládaná dohromady, stačí data z těchto čísel řádků extrahovat. To lze snadno provést pomocí indexového vzorce (vložte tento vzorec na místo, kam chcete data extrahovat. Zkopírujte jej do buňky vlevo nahoře, kam chcete data extrahovat, a poté jej přetáhněte dolů a doprava).
= IFERROR (INDEX ($ B $ 4: $ D $ 23, $ G4, COLUMNS ($ I $ 3: I3)), "")
Tento vzorec má 2 části:
INDEX - To extrahuje data na základě čísla řádku.
IFERROR - Pokud nejsou k dispozici žádná data, vrátí se prázdné.
Zde je snímek toho, co nakonec získáte:
Combo Box je rozbalovací a také vyhledávací pole. Původní sloupce dat a pomocné sloupce můžete skrýt a zobrazit pouze filtrované záznamy. Můžete také mít nezpracovaná data a pomocné sloupce v jiném listu a vytvořit tento dynamický filtr Excelu v jiném listu.
Buďte kreativní! Vyzkoušejte některé varianty
Můžete to zkusit a přizpůsobit svým požadavkům. Možná budete chtít vytvořit více filtrů Excelu místo jednoho. Můžete například chtít filtrovat záznamy, kde obchodní zástupce je Mike a země je Japonsko. To lze provést přesně podle stejných kroků s určitou úpravou vzorce ve pomocných sloupcích.
Další variantou může být filtrování dat začínajících znaky, které zadáte do pole se seznamem. Když například zadáte „I“, možná budete chtít extrahovat země začínající na I (ve srovnání se současným konstruktem, kde by vám také poskytl Singapur a Filipíny, protože obsahuje abecedu I).
Jako vždy, většina mých článků je inspirována otázkami/odpověďmi mých čtenářů. Rád bych získal vaši zpětnou vazbu a učil se od vás. Nechte své myšlenky v sekci komentáře.
Poznámka: V případě, že používáte Office 365, můžete pomocí funkce FILTR rychle filtrovat data při psaní. Je to jednodušší než metoda ukázaná v tomto tutoriálu.
