Pozrite si video - Rozšírený filter Excelu
Rozšírený filter programu Excel je jednou z najviac podceňovaných a nedostatočne využívaných funkcií, s ktorými som sa stretol.
Ak pracujete s programom Excel, som si istý, že ste ho použili (alebo ste o klasickom filtri programu Excel aspoň počuli). Rýchlo filtruje množinu údajov na základe výberu, zadaného textu, čísla alebo iných podobných kritérií.
V tejto príručke vám ukážem niekoľko skvelých vecí, ktoré môžete robiť pomocou rozšíreného filtra programu Excel.
Ale najskôr … Čo je rozšírený filter programu Excel?
Rozšírený filter programu Excel - ako naznačuje názov - je rozšírenou verziou bežného filtra. Môžete to použiť, keď potrebujete na filtrovanie množiny údajov použiť komplexnejšie kritériá.
Tu je niekoľko rozdielov medzi bežným filtrom a rozšíreným filtrom:
- Bežný filter údajov síce bude filtrovať existujúcu množinu údajov, ale pomocou rozšíreného filtra Excelu môžete množinu údajov extrahovať aj na iné miesto.
- Rozšírený filter programu Excel vám umožňuje používať komplexné kritériá. Ak máte napríklad údaje o predaji, môžete filtrovať údaje podľa kritéria, kde je obchodný zástupca Bob a oblasť je sever alebo juh (v príkladoch uvidíme, ako to urobiť). Podpora Office má na to dobré vysvetlenie.
- Rozšírený filter programu Excel môžete použiť na extrakciu jedinečných záznamov z vašich údajov (o tom viac za sekundu).
ROZŠÍRENÝ FILTER EXCEL (príklady)
Teraz sa pozrime na niekoľko príkladov použitia rozšíreného filtra v programe Excel.
Príklad 1 - Extrahovanie jedinečného zoznamu
Rozšírený filter programu Excel môžete použiť na rýchle extrahovanie jedinečných záznamov zo sady údajov (alebo inými slovami na odstránenie duplikátov).
V programe Excel 2007 a novších verziách existuje možnosť odstrániť duplikáty z množiny údajov. To však zmení váš existujúci súbor údajov. Aby boli pôvodné údaje nedotknuté, musíte vytvoriť kópiu údajov a potom použiť možnosť Odstrániť duplikáty. Rozšírený filter programu Excel vám umožní vybrať umiestnenie a získať jedinečný zoznam.Pozrime sa, ako použiť rozšírené filtre na získanie jedinečného zoznamu.
Predpokladajme, že máte množinu údajov, ako je uvedené nižšie:
Ako vidíte, v tejto množine údajov sú duplicitné záznamy (zvýraznené oranžovou farbou). Príčinou môže byť chyba pri zadávaní údajov alebo výsledok kompilácie údajov.
V takom prípade môžete použiť nástroj Rozšírený filter programu Excel na rýchle získanie zoznamu všetkých jedinečných záznamov na inom mieste (aby vaše pôvodné údaje zostali nedotknuté).
Tu sú kroky na získanie všetkých jedinečných záznamov:
- Vyberte celý súbor údajov (vrátane hlavičiek).
- Prejdite na kartu Údaje -> Zoradiť a filtrovať -> Rozšírené. (Môžete tiež použiť klávesovú skratku - Alt + A + Q). Otvorí sa dialógové okno Rozšírený filter.
- V dialógovom okne Rozšírený filter použite nasledujúce podrobnosti:
- Akcia: Vyberte možnosť „Kopírovať na iné miesto“. To vám umožní určiť miesto, kde môžete získať zoznam jedinečných záznamov.
- Rozsah zoznamu: Uistite sa, že odkazuje na množinu údajov, z ktorej chcete nájsť jedinečné záznamy. Tiež sa uistite, že sú v súbore údajov zahrnuté hlavičky.
- Rozsah kritérií: Toto nechajte prázdne.
- Kopírovať do: Zadajte adresu bunky, na ktorú chcete získať zoznam jedinečných záznamov.
- Kopírovať iba jedinečné záznamy: Začiarknite túto možnosť.
- Kliknite na tlačidlo OK.
To vám okamžite poskytne zoznam všetkých jedinečných záznamov.
Pozor: Keď na získanie jedinečného zoznamu používate rozšírený filter, uistite sa, že ste vybrali aj hlavičku. Ak to neurobíte, bude považovať prvú bunku za hlavičku.
Príklad 2 - Použitie kritérií v rozšírenom filtri programu Excel
Získanie jedinečných záznamov je jednou z mnohých vecí, ktoré môžete vykonávať s rozšíreným filtrom programu Excel.
Jeho primárna užitočnosť spočíva v schopnosti povoliť používanie komplexných kritérií na filtrovanie údajov.
Tu je to, čo mám na mysli komplexnými kritériami. Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete rýchlo získať všetky záznamy, kde sú tržby vyššie ako 5 000 a región sú USA.
Tu je návod, ako môžete použiť rozšírený filter Excelu na filtrovanie záznamov na základe zadaných kritérií:
- Prvým krokom pri použití rozšíreného filtra programu Excel so zložitými kritériami je zadanie kritérií. Za týmto účelom skopírujte hlavičky a prilepte ich niekam do pracovného hárka.
- Zadajte kritériá, podľa ktorých chcete filtrovať údaje. Keďže v tomto prípade chceme získať všetky záznamy o USA s tržbami viac ako 5 000, zadajte do bunky pod oblasťou „USA“ a do bunky pod položkou Predaj zadajte> 5 000. Toto by sa teraz použilo ako vstup v rozšírenom filtri na získanie filtrovaných údajov (ako je znázornené v ďalších krokoch).
- Vyberte celý súbor údajov (vrátane hlavičiek).
- Prejdite na kartu Údaje -> Zoradiť a filtrovať -> Rozšírené. Otvorí sa dialógové okno Rozšírený filter.
- V dialógovom okne Rozšírený filter použite nasledujúce podrobnosti:
- Akcia: Vyberte možnosť „Kopírovať na iné miesto“. To vám umožní určiť miesto, kde môžete získať zoznam jedinečných záznamov.
- Rozsah zoznamu: Uistite sa, že odkazuje na množinu údajov, z ktorej chcete nájsť jedinečné záznamy. Tiež sa uistite, že sú v súbore údajov zahrnuté hlavičky.
- Rozsah kritérií: Špecifikujte kritériá, ktoré sme vytvorili vo vyššie uvedených krokoch. V tomto prípade by to bolo F1: I3.
- Kopírovať do: Zadajte adresu bunky, na ktorú chcete získať zoznam jedinečných záznamov.
- Kopírovať iba jedinečné záznamy: Začiarknite túto možnosť.
- Kliknite na tlačidlo OK.
To by vám okamžite poskytlo všetky záznamy, kde je región USA a tržby sú viac ako 5 000.
Vyššie uvedený príklad je prípad, keď sa filtrovanie vykonáva na základe dvoch kritérií (USA a tržby vyššie ako 5 000).
Rozšírený filter Excelu vám umožňuje vytvoriť mnoho rôznych kombinácií kritérií.
Tu je niekoľko príkladov, ako môžete vytvoriť tieto filtre.
Použitie kritérií AND
Ak chcete použiť kritériá AND, musíte ich zadať pod hlavičkou.
Napríklad:
- Ak chcete filtrovať záznamy, keď je región USA a obchodný zástupca je Joe.
- Ak chcete filtrovať záznamy, keď je región USA A hodnota predaja je väčšia ako 5 000.
- Keď sú regiónom USA a tržby sa zaznamenávajú po 31-03-2017.
Použitie kritérií OR
Ak chcete použiť kritériá ALEBO, musíte kritériá zadať v rovnakom stĺpci.
Napríklad:
- Filtrovanie záznamov, keď je región USA ALEBO región je Ázia.
- Filtrovanie záznamov, keď je obchodný zástupca Bob ALEBO Martha.
Príklad 3 - Použitie znakov WILDCARD v rozšírenom filtri v programe Excel
Rozšírený filter Excelu tiež umožňuje použitie zástupných znakov pri vytváraní kritérií.
V programe Excel sú tri zástupné znaky:
- * (hviezdička) - Predstavuje ľubovoľný počet znakov. Napríklad ex* môže znamenať excel, excel, príklad, expert atď.
- ? (otáznik) - Predstavuje jeden jediný znak. Tr? Mp môže napríklad znamenať Trump alebo Tramp.
- ~ (vlnovka) - Používa sa na identifikáciu zástupných znakov (~, *,?) V texte.
Teraz sa pozrime, ako môžeme tieto zástupné znaky použiť na pokročilé filtrovanie v programe Excel.
- Na filtrovanie záznamov, kde názov obchodného zástupcu začína od J.
Upozorňujeme, že * predstavujú ľubovoľný počet znakov. Takže každý zástupca s názvom začínajúcim na J by bol filtrovaný podľa týchto kritérií.
Podobne môžete použiť aj ďalšie dva zástupné znaky.
Poznámka: V prípade, že používate Office 365, mali by ste vyskúšať funkciu FILTER. Dokáže veľa vecí, ktoré pokročilý filter dokáže pomocou jednoduchého vzorca.
POZNÁMKA:
- Nezabudnite, že hlavičky v kritériách by mali byť úplne rovnaké ako v súbore údajov.
- Pri kopírovaní na iné miesta nemožno pokročilé filtrovanie vrátiť späť.
