Excel filter je jednou z najpoužívanejších funkcií pri práci s údajmi. V tomto blogovom príspevku vám ukážem, ako vytvoriť vyhľadávacie pole dynamického filtra programu Excel tak, aby filtrovalo údaje podľa toho, čo do vyhľadávacieho poľa napíšete.
Niečo, ako je uvedené nižšie:
Má to dvojakú funkciu - môžete si vybrať názov krajiny z rozbaľovacieho zoznamu, alebo môžete údaje zadať ručne do vyhľadávacieho poľa a zobrazí vám všetky zodpovedajúce záznamy. Keď napríklad zadáte „I“, zobrazí sa vám názov krajiny s abecedou I.
Pozrite si video - Vytvorenie dynamického vyhľadávacieho poľa filtra programu Excel
Vytvorenie vyhľadávacieho poľa dynamického filtra programu Excel
Tento filter Dynamic Excel je možné vytvoriť v 3 krokoch:
- Získanie jedinečného zoznamu položiek (v tomto prípade krajín). Toto by sa použilo pri vytváraní rozbaľovacej ponuky.
- Vytvorenie vyhľadávacieho poľa. Tu som použil kombinovaný box (ovládací prvok ActiveX).
- Nastavenie údajov. Tu by som použil tri pomocné stĺpce so vzorcami na extrahovanie zodpovedajúcich údajov.
Takto vyzerajú nespracované údaje:
UŽITOČNÝ TIP: Takmer vždy je vhodné previesť údaje do tabuľky programu Excel. Môžete to urobiť tak, že vyberiete ľubovoľnú bunku v množine údajov a použijete klávesovú skratku Ctrl + T.
Krok 1 - Získanie jedinečného zoznamu položiek
- Vyberte všetky krajiny a prilepte ich do nového pracovného hárka.
- Vyberte zoznam krajín -> Prejsť na údaje -> Odstrániť duplikáty.
- V dialógovom okne Odstrániť duplikáty vyberte stĺpec, v ktorom máte zoznam, a kliknite na tlačidlo OK. Tým sa odstránia duplikáty a poskytne vám jedinečný zoznam, ako je uvedené nižšie:
- Ďalším krokom je vytvorenie pomenovaného rozsahu pre tento jedinečný zoznam. Urobiť toto:
- Prejdite na kartu Vzorec -> Definovať meno
- V dialógovom okne Definovať meno:
- Názov: CountryList
- Rozsah: Pracovný zošit
- Vzťahuje sa na: = UniqueList! $ A $ 2: $ A $ 9 (Zoznam mám na samostatnej karte s názvom UniqueList v A2: A9. Môžete sa obrátiť na to, kde sa nachádza váš jedinečný zoznam)
POZNÁMKA: Ak používate metódu „Odstrániť duplikáty“ a rozšírite svoje údaje o ďalšie záznamy a nové krajiny, budete musieť tento krok zopakovať. Alternatívne môžete tiež vytvoriť vzorec, aby bol tento proces dynamický.
Krok 2 - Vytvorenie vyhľadávacieho poľa dynamického filtra programu Excel
Aby táto technika fungovala, bolo by potrebné vytvoriť „vyhľadávacie pole“ a prepojiť ho s bunkou.
Na vytvorenie tohto filtra vyhľadávacieho poľa môžeme použiť kombinované pole v Exceli. Týmto spôsobom sa vždy, keď do poľa so zoznamom zadáte čokoľvek, prejaví aj v bunke v reálnom čase (ako je uvedené nižšie).
Tu sú kroky, ako to urobiť:
- Prejdite na kartu Vývojár -> Ovládacie prvky -> Vložiť -> Ovládacie prvky ActiveX -> Kombinovaný box (Ovládacie prvky ActiveX).
- Ak nevidíte kartu Vývojár, postupujte takto:
- Ak nevidíte kartu Vývojár, postupujte takto:
- Kliknite kdekoľvek v pracovnom hárku. Vloží kombinovaný box.
- Kliknite pravým tlačidlom myši na pole so zoznamom a vyberte položku Vlastnosti.
- V okne Vlastnosti vykonajte nasledujúce zmeny:
- Prepojená bunka: K2 (môžete si vybrať ľubovoľnú bunku, v ktorej chcete, aby zobrazovala vstupné hodnoty. Túto bunku použijeme pri nastavovaní údajov).
- ListFillRange: CountryList (toto je pomenovaný rozsah, ktorý sme vytvorili v kroku 1. To by zobrazilo všetky krajiny v rozbaľovacom zozname).
- MatchEntry: 2-fmMatchEntryNone (to zaisťuje, že slovo nie je automaticky dokončené počas písania)
- Keď je vybraté pole so zoznamom, prejdite na kartu Vývojár -> Ovládacie prvky -> Kliknite na Režim návrhu (tým sa dostanete z režimu návrhu a do poľa so zoznamom môžete zadať čokoľvek. Teraz sa čokoľvek, čo zadáte, prejaví v bunke K2 v reálnom čase)
Krok 3 - Nastavenie údajov
Nakoniec všetko prepojíme pomocou pomocných stĺpcov. Na filtrovanie údajov tu používam tri pomocné stĺpce.
Pomocník, stĺpec 1: Zadajte sériové číslo všetkých záznamov (v tomto prípade 20). Na to môžete použiť vzorec ROWS ().
Pomocník, stĺpec 2: V pomocnom stĺpci 2 kontrolujeme, či sa text zadaný do vyhľadávacieho poľa zhoduje s textom v bunkách v stĺpci krajiny.
To sa dá dosiahnuť kombináciou funkcií IF, ISNUMBER a SEARCH.
Tu je vzorec:
= IF (ISNUMBER (VYHĽADÁVAŤ (2 000 K $, D4)), E4, „“)
Tento vzorec vyhľadá obsah vo vyhľadávacom poli (ktoré je prepojené s bunkou K2) v bunke s názvom krajiny.
Ak existuje zhoda, tento vzorec vráti číslo riadku, v opačnom prípade vráti prázdne miesto. Ak má napríklad kombinovaný box hodnotu „USA“, všetky záznamy s krajinou ako „USA“ budú mať číslo riadku a ostatné budú prázdne („“)
Pomocník stĺpec 3: V pomocnom stĺpci 3 potrebujeme získať všetky čísla riadkov zo stĺpca 2 pomocníka. K tomu môžeme použiť kombináciu, ak vzorce IFERROR a MALÉ. Tu je vzorec:
= IFERROR (MALÉ ($ F $ 4: $ F $ 23, E4), "")
Tento vzorec zoskupí všetky zodpovedajúce čísla riadkov dohromady. Ak má napríklad kombinovaný box hodnotu USA, všetky čísla riadkov, v ktorých je uvedené „USA“, sa spoja.
Teraz, keď máme čísla riadkov zoskupené, stačí extrahovať údaje z tohto čísla riadkov. To sa dá ľahko vykonať pomocou vzorca indexu (vložte tento vzorec na miesto, kam chcete extrahovať údaje. Skopírujte ho do bunky vľavo hore, kam chcete údaje extrahovať, a potom ho potiahnite nadol a doprava).
= IFERROR (INDEX ($ B $ 4: $ D $ 23, $ G4, COLUMNS ($ I $ 3: I3)), "")
Tento vzorec má 2 časti:
INDEX - Týmto sa extrahujú údaje na základe čísla riadka.
IFERROR - Ak nie sú k dispozícii žiadne údaje, vráti sa prázdne.
Tu je prehľad toho, čo nakoniec získate:
Kombinované pole je rozbaľovacie a vyhľadávacie pole. Pôvodné údaje a stĺpce pomocníka môžete skryť, aby sa zobrazovali iba filtrované záznamy. Môžete tiež mať stĺpce nespracovaných údajov a pomocníka v inom hárku a vytvoriť tento dynamický filter programu Excel v inom hárku.
Buďte kreatívni! Skúste niekoľko variácií
Môžete si to vyskúšať a prispôsobiť svojim požiadavkám. Možno budete chcieť vytvoriť viac filtrov programu Excel namiesto jedného. Môžete napríklad filtrovať záznamy, v ktorých je obchodný zástupca Mike a krajina Japonsko. To sa dá urobiť presne podľa rovnakých krokov s určitou úpravou vzorca v pomocných stĺpcoch.
Ďalšou variáciou môže byť filtrovanie údajov, ktoré začínajú znakmi, ktoré zadáte do poľa so zoznamom. Keď napríklad zadáte „I“, budete chcieť extrahovať krajiny začínajúce na I (v porovnaní so súčasným konštruktom, kde by vám tiež poskytol Singapur a Filipíny, pretože obsahuje abecedu I).
Ako vždy, väčšina mojich článkov je inšpirovaná otázkami/odpoveďami mojich čitateľov. Rád by som získal vašu spätnú väzbu a poučil sa od vás. Nechajte svoje myšlienky v sekcii komentárov.
Poznámka: V prípade, že používate Office 365, môžete pomocou funkcie FILTER rýchlo filtrovať údaje počas písania. Je to jednoduchšie ako metóda uvedená v tomto návode.
