Jak používat rozšířený filtr Excelu – úplný průvodce s příklady
Rozšířený filtr aplikace Excel je výkonný nástroj, který poskytuje flexibilitu nad rámec standardních funkcí filtrování a umožňuje uživatelům efektivně provádět složité úlohy filtrování. Tato příručka se podrobně zabývá funkcí Rozšířené filtry aplikace Excel, porovnává ji s běžnými filtry, poskytuje praktické příklady a nabízí úvahy pro optimální použití. Umožní vám to s jistotou zvládnout používání pokročilých filtrů.
- Extrahujte jedinečný seznam
- Filtrujte v jednom sloupci s více kritérii (odpovídají libovolným kritériím)
- Filtrujte ve více sloupcích s více kritérii
- Pokročilý filtr se zástupnými znaky
- Extrahujte pouze určité sloupce
Pokročilý filtr vs. normální filtr
Hlavní rozdíl mezi normálním filtrem aplikace Excel a rozšířeným filtrem spočívá v jejich složitosti a funkčnosti. Zatímco normální filtr nabízí přímočaré filtrování založené na kritériích v jednom sloupci v rámci původní sady dat, rozšířený filtr přesahuje tyto limity:
- Umožňuje použití více kritérií v různých sloupcích.
- Poskytování schopnosti extrahovat jedinečné hodnoty z datové sady.
- Povolení použití zástupných znaků pro flexibilnější, částečnou shodu.
- Povolení extrakce filtrovaných dat na samostatné místo.
Příklady použití pokročilého filtru
Tato část poskytne různé praktické příklady, které vám ukážou, jak používat pokročilé filtry v aplikaci Excel k dosažení různých efektů filtrování.
Extrahujte jedinečný seznam
Rozšířený filtr Excelu dokáže rychle vygenerovat seznam jedinečných hodnot z datové sady, což je úkol, který může být s normálními filtry těžkopádný. Pokud máte seznam prodejních transakcí s duplicitními řádky a chcete extrahovat seznam jedinečných řádků, funkce Rozšířený filtr v Excelu může tento úkol zjednodušit. Chcete-li to provést, postupujte podle pokynů níže.
- Přejděte na kartu "Data" a ve skupině "Třídit a filtrovat" vyberte "Upřesnit".
- V dialogovém okně "Advanced Filter" je třeba nakonfigurovat následovně.
- V sekci „Akce“ vyberte požadovanou možnost. Protože chci jedinečný seznam najít na jiném místě, zvolím možnost „Kopírovat do jiného umístění“.
- Určete sekci Seznam rozsah:
- Extrahujte jedinečnou hodnotu z jednoho sloupce:
Vyberte sloupec obsahující hodnoty, ze kterých chcete extrahovat jedinečné položky. Chcete-li například v tomto případě extrahovat jedinečná jména zákazníků, vyberte A1:A11.
- Extrahujte jedinečné řádky na základě více sloupců:
Vyberte rozsah, který zahrnuje všechny sloupce, které zvažujete. V tomto případě, protože chci extrahovat jedinečné řádky na základě jmen zákazníků, prodeje a regionu, vyberu celý rozsah A1:C11.
- Extrahujte jedinečnou hodnotu z jednoho sloupce:
- V části „Kopírovat do“ určete, kam chcete jedinečný seznam vložit.
- Zaškrtněte políčko „Pouze jedinečné záznamy“.
- Klikněte na tlačítko "OK". Viz snímek obrazovky:
Výsledek
Jak je znázorněno na obrázku níže, jedinečné řádky jsou extrahovány z původního rozsahu dat.
Filtrujte v jednom sloupci s více kritérii (odpovídají libovolným kritériím)
Filtrování dat v jednom sloupci s více kritérii vám umožňuje zobrazit řádky, které splňují kteroukoli z vašich zadaných podmínek. To může být zvláště užitečné, když pracujete s velkými datovými sadami a potřebujete zúžit informace na základě několika potenciálních shod. Zde je návod, jak toho dosáhnout pomocí funkce rozšířeného filtru aplikace Excel:
Krok 1: Připravte si data původního seznamu
Zajistěte, aby datová sada rozsahu seznamu měla jasná záhlaví sloupců, protože ta budou důležitá pro nastavení rozsahu kritérií. Zde jako příklad používám následující tabulku skóre studentů.
Krok 2: Nastavení rozsahu kritérií
- V rozsahu nad rozsahem seznamu nebo mimo něj vytvořte rozsah kritérií. Záhlaví, která zadáte v rozsahu kritérií, musí přesně odpovídat záhlavím v rozsahu seznamu, aby fungovala správně. Zde je můj rozsah kritérií umístěn nad rozsahem seznamu.
- Pod záhlavím uveďte všechna kritéria, která chcete porovnat. Každé kritérium by mělo být ve své vlastní buňce, přímo pod předchozím kritériem. Toto nastavení sdělí aplikaci Excel, aby odpovídala kterémukoli z těchto kritérií.
V tomto příkladu hledám studenty se „skóre vyšším než 95 nebo nižším než 60“, abych mohl efektivně filtrovat rozsah seznamu tak, aby zahrnoval studenty s vysokým i nízkým skóre. Každé kritérium proto zadávám do samostatných řádků pod hlavičkou Skóre. Celý rozsah kritérií je uveden níže:
Krok 3: Použijte rozšířený filtr
Nyní můžete použít pokročilý filtr k provedení úkolu následovně.
- Přejděte na kartu „Data“ a ve skupině „Řadit a filtrovat“ vyberte „Upřesnit“.
- V dialogovém okně "Advanced Filter" je třeba nakonfigurovat následovně.
- V sekci „Akce“ vyberte požadovanou možnost. Zde, protože chci najít filtrovaný výsledek na jiné místo, zvolím možnost „Kopírovat do jiného umístění“.
- V části Rozsah seznamu vyberte celý rozsah seznamu A7:D17.
- V části Rozsah kritérií vyberte celý rozsah kritérií A2:D4.
- V části „Kopírovat do“ určete, kam chcete filtrovaný výsledek vložit (zde vybírám buňku F8).
- Kliknutím na "OK" použijete filtr. Viz snímek obrazovky:
Výsledek
Potom můžete vidět, že jsou extrahovány pouze ty řádky, kde sloupec "Skóre" odpovídá některému z kritérií (>95 nebo <60).
Dejte sbohem ručnímu nastavování složitých rozsahů kritérií
Odemkněte sílu vícepodmínkového filtrování v Excelu bez složitosti! Kutools for ExcelFunkce „Superfiltr“ nabízí bezkonkurenční snadnost použití, které se nativní rozšířený filtr v Excelu prostě nemůže rovnat. Podporuje následující pokročilé filtry, které lze aktivovat jen několika kliknutími:
- Filtrujte podle více kritérií v jednom sloupci
- Filtrujte podle více kritérií ve více sloupcích
- Filtrujte data podle délky textu
- Filtrovat data podle roku/měsíce/týdne...
- Filtrovat textové řetězce podle velikosti písmen...
Zjistěte, jak může „Super Filter“ změnit váš pracovní postup. Klikněte zde pro stažení Kutools for Excel.
Kliknutím sem se dozvíte více a uvidíte, jak tuto funkci používat.
Filtrujte ve více sloupcích s více kritérii
Po pokrytí filtrování několika kritérii v jednom sloupci se nyní zaměříme na filtrování ve více sloupcích. Tato část vás provede aplikací více kritérií v různých sloupcích pomocí AND, OR a kombinované logiky AND/OR.
- Chcete-li použít logiku AND, umístěte kritéria na stejný řádek.
- Chcete-li použít logiku NEBO, umístěte kritéria na samostatné řádky.
S logikou AND (splňuje všechna kritéria)
Filtrování dat ve více sloupcích s více kritérii pomocí logiky AND znamená, že každý řádek musí splňovat všechna zadaná kritéria v různých sloupcích, aby se zobrazil. Zde je návod, jak toho dosáhnout pomocí rozšířeného filtru Excelu:
Krok 1: Připravte si data původního seznamu
Zajistěte, aby datová sada rozsahu seznamu měla jasná záhlaví sloupců, protože ta budou důležitá pro nastavení rozsahu kritérií. Zde jako příklad používám následující tabulku skóre studentů.
Krok 2: Nastavení rozsahu kritérií
- Vytvořte rozsah kritérií buď nad rozsahem seznamu, nebo od něj odděleně, zadáním záhlaví, která přesně odpovídají těm v rozsahu seznamu. Zde je můj rozsah kritérií umístěn nad rozsahem seznamu.
- Pro logiku AND uveďte všechna kritéria ve stejném řádku pod jejich odpovídajícími záhlavími. Například, pokud chci filtrovat studenty "třídy A" se skóre nad 85, pak by měl být rozsah kritérií nastaven takto:
Krok 3: Použijte rozšířený filtr
Nyní můžete použít pokročilý filtr k provedení úkolu následovně.
- Přejděte na kartu "Data" a ve skupině "Třídit a filtrovat" vyberte "Upřesnit".
- V dialogovém okně "Advanced Filter" je třeba nakonfigurovat následovně.
- V sekci „Akce“ vyberte požadovanou možnost. Zde, protože chci najít filtrovaný výsledek na jiné místo, zvolím možnost „Kopírovat do jiného umístění“.
- V části Rozsah seznamu vyberte celý rozsah seznamu A7:D16.
- V části Rozsah kritérií vyberte celý rozsah kritérií A2:D3.
- V části „Kopírovat do“ určete, kam chcete filtrovaný výsledek vložit (zde vybírám buňku F6).
- Kliknutím na "OK" použijete filtr. Viz snímek obrazovky:
Výsledek
Ve výsledku budou zobrazeny nebo zkopírovány pouze řádky, které splňují všechna kritéria v zadaných sloupcích. V našem příkladu jsou extrahováni pouze studenti ze třídy A se skóre vyšším než 85.
S logikou OR (splňujte všechna kritéria)
Chcete-li filtrovat data ve více sloupcích pomocí logiky NEBO (odpovídající jakémukoli z kritérií) v rozšířeném filtru aplikace Excel, postupujte takto:
Krok 1: Připravte si data původního seznamu
Zajistěte, aby datová sada rozsahu seznamu měla jasná záhlaví sloupců, protože ta budou důležitá pro nastavení rozsahu kritérií. Zde jako příklad používám následující tabulku skóre studentů.
Krok 2: Nastavení rozsahu kritérií
- Vytvořte rozsah kritérií buď nad rozsahem seznamu, nebo od něj odděleně, zadáním záhlaví, která přesně odpovídají těm v rozsahu seznamu. Zde je můj rozsah kritérií umístěn nad rozsahem seznamu.
- Pomocí logiky OR umístěte každou sadu kritérií pro stejný sloupec na samostatné řádky nebo uveďte každé kritérium na samostatné řádky pod odpovídající záhlaví. Například, pokud chci filtrovat studenty se skóre vyšším než 90 nebo známkami F, měl by být rozsah kritérií nastaven takto:
Krok 3: Použijte rozšířený filtr
Nyní můžete použít pokročilý filtr k provedení úkolu následovně.
- Přejděte na kartu "Data" a ve skupině "Třídit a filtrovat" vyberte "Upřesnit".
- V dialogovém okně "Advanced Filter" je třeba nakonfigurovat následovně.
- V sekci „Akce“ vyberte požadovanou možnost. Zde, protože chci najít filtrovaný výsledek na jiné místo, zvolím možnost „Kopírovat do jiného umístění“.
- V části Rozsah seznamu vyberte celý rozsah seznamu A7:D17.
- V části Rozsah kritérií vyberte celý rozsah kritérií A2:D4.
- V části „Kopírovat do“ určete, kam chcete filtrovaný výsledek vložit (zde vybírám buňku F8).
- Kliknutím na "OK" použijete filtr. Viz snímek obrazovky:
Výsledek
Tím budou vaše data filtrována na základě zadaných kritérií, která budou odpovídat všem uvedeným kritériím. Pokud řádek odpovídá některému z kritérií ve vámi zadaných sloupcích, bude zahrnut do filtrovaných výsledků.
V tomto případě filtr vrátí pouze studenty, jejichž skóre je vyšší než 90 nebo kteří mají známku F.
S logikou AND i OR
Chcete-li filtrovat data ve více sloupcích pomocí kombinace logiky AND i OR pomocí rozšířeného filtru aplikace Excel, můžete postupovat podle těchto kroků.
Krok 1: Připravte si data původního seznamu
Zajistěte, aby datová sada rozsahu seznamu měla jasná záhlaví sloupců, protože ta budou důležitá pro nastavení rozsahu kritérií. Zde jako příklad používám následující tabulku skóre studentů.
Krok 2: Nastavení rozsahu kritérií
- Vytvořte rozsah kritérií nad nebo vedle rozsahu seznamu. Zahrňte záhlaví sloupců, která přesně odpovídají záhlavím v rozsahu seznamu. Zde je můj rozsah kritérií umístěn nad rozsahem seznamu.
- Pod záhlavími zadejte kritéria pomocí kombinace logiky AND a OR.
- Pro logiku AND by kritéria z různých sloupců měla být umístěna na stejném řádku.
- Pro logiku OR by kritéria měla být umístěna na samostatných řádcích.
- Pro kombinovanou logiku AND-OR uspořádejte každou sadu podmínek NEBO do samostatných bloků řádků. V každém bloku umístěte kritéria AND na stejný řádek.
Chcete-li například filtrovat studenty ve třídě A se skóre vyšším než 90 nebo ve třídě B se známkou B, nastavte rozsah kritérií takto:
Krok 3: Použijte rozšířený filtr
Nyní můžete použít pokročilý filtr k provedení úkolu následovně.
- Přejděte na kartu "Data" a ve skupině "Třídit a filtrovat" vyberte "Upřesnit".
- V dialogovém okně "Advanced Filter" je třeba nakonfigurovat následovně.
- V sekci „Akce“ vyberte požadovanou možnost. Zde, protože chci najít filtrovaný výsledek na jiné místo, zvolím možnost „Kopírovat do jiného umístění“.
- V části Rozsah seznamu vyberte celý rozsah seznamu A7:D17.
- V části Rozsah kritérií vyberte celý rozsah kritérií A2:D4.
- V části „Kopírovat do“ určete, kam chcete filtrovaný výsledek vložit (zde vybírám buňku F8).
- Kliknutím na "OK" použijete filtr. Viz snímek obrazovky:
Výsledek
Excel zobrazí pouze řádky, které splňují vaši komplexní kombinaci kritérií.
V tomto příkladu rozšířený filtr vrátí pouze studenty se skóre vyšším než 90 ve třídě A nebo studenty se známkou B ve třídě B.
Pokročilý filtr se zástupnými znaky
Použití zástupných znaků s pokročilým filtrem Excelu umožňuje flexibilnější a výkonnější vyhledávání dat. Zástupné znaky jsou speciální znaky, které představují jeden nebo více znaků v řetězci, což usnadňuje filtrování textových vzorů. Zde jsou podrobné pokyny, jak používat rozšířený filtr se zástupnými znaky v Excelu.
Krok 1: Připravte si data původního seznamu
Zajistěte, aby datová sada rozsahu seznamu měla jasná záhlaví sloupců, protože ta budou důležitá pro nastavení rozsahu kritérií. V tomto příkladu řekněme, že máte seznam jmen a některá jména, která hledáte, mají určitý vzor pojmenování.
Krok 2: Nastavení rozsahu kritérií
- Vytvořte rozsah kritérií nad nebo vedle rozsahu seznamu. Zahrňte záhlaví sloupců, která přesně odpovídají záhlavím v rozsahu seznamu. Zde je můj rozsah kritérií umístěn nad rozsahem seznamu.
- Pod záhlavím zadejte kritéria pomocí zástupných znaků.
- *: Představuje libovolný počet znaků a lze jej použít před, za nebo v rámci řetězce.
- ?: Představuje jeden znak na určité pozici.
V tomto příkladu chci filtrovat jména, která začínají znakem „J“, takže zadám J* pod záhlavím Name rozsahu kritérií. Viz snímek obrazovky:
Krok 3: Použijte rozšířený filtr
Nyní můžete použít rozšířený filtr k filtrování všech jmen začínajících znakem J.
- Přejděte na kartu "Data" a ve skupině "Třídit a filtrovat" vyberte "Upřesnit".
- V dialogovém okně "Advanced Filter" nakonfigurujte následovně.
- V sekci „Akce“ vyberte požadovanou možnost. Zde, protože chci najít filtrovaný výsledek na jiné místo, zvolím možnost „Kopírovat do jiného umístění“.
- V části Rozsah seznamu vyberte celý rozsah seznamu A6:B11.
- V části Rozsah kritérií vyberte celý rozsah kritérií A2:B3.
- V části „Kopírovat do“ určete, kam chcete filtrovaný výsledek vložit (zde vybírám buňku D7).
- Kliknutím na "OK" použijete filtr. Viz snímek obrazovky:
Výsledek
Rozšířený filtr zobrazí pouze ty řádky ze sloupce Název, kde názvy začínají písmenem „J“, a to podle vzoru určeného zástupným znakem v rozsahu kritérií.
Extrahujte pouze určité sloupce
Použití pokročilého filtru aplikace Excel k extrahování pouze určitých sloupců je zvláště užitečné pro analýzu velkých datových sad, kde se potřebujete zaměřit pouze na určité informace.
Předpokládejme, že vaše datová sada je v rozsahu A7:D17 a chcete tato data filtrovat na základě kritérií zadaných v B2:D4 a extrahovat pouze sloupce Název, Skóre a Známka. Zde je návod, jak na to.
Krok 1: Zadejte sloupce k extrahování
Pod nebo vedle vaší datové sady napište záhlaví sloupců, které chcete extrahovat. Toto definuje rozsah "Kopírovat do", kde se zobrazí filtrovaná data. V tomto příkladu zadám záhlaví Name, Score a Grade v rozsahu F7:H7.
Krok 2: Použijte rozšířený filtr
Nyní můžete použít rozšířený filtr k filtrování pouze určitých sloupců na základě zadaných kritérií.
- Přejděte na kartu "Data" a ve skupině "Třídit a filtrovat" vyberte "Upřesnit".
- V dialogovém okně "Advanced Filter" nakonfigurujte následovně.
- V části „Akce“ vyberte možnost „Kopírovat do jiného umístění“.
- V části Rozsah seznamu vyberte celý rozsah seznamu A7:D17.
- V části Rozsah kritérií vyberte celý rozsah kritérií A2:D4.
- V části „Kopírovat do“ vyberte rozsah (F7:H7 v tomto případě), kde jste napsali záhlaví sloupců, které chcete extrahovat.
- Kliknutím na "OK" použijete filtr. Viz snímek obrazovky:
Výsledek
Můžete vidět, že výsledek extrakce obsahuje pouze zadané sloupce.
Poznámky k pokročilému filtru
- Rozsah kritérií musí mít záhlaví sloupců, která přesně odpovídají těm v rozsahu seznamu.
- Pokud jsou filtrované výsledky zkopírovány na jiné místo, funkce Zpět (Ctrl + Z) není k dispozici.
- Při použití rozšířeného filtru v Excelu nezapomeňte do výběru zahrnout záhlaví sloupců. Vynechání záhlaví může vést k tomu, že Excel bude první buňku v rozsahu mylně považovat za záhlaví, což může vést k nesprávnému filtrování.
- Filtrované výsledky se neaktualizují dynamicky; znovu použijte pokročilý filtr, abyste je obnovili po změnách dat.
- V následující tabulce jsou uvedeny operace porovnání čísel a dat, které můžete použít v kritériích rozšířeného filtru.
Operátor porovnání Význam = Rovná > Větší než < Méně než >= Větší nebo roven <= Méně nebo rovné <> Nerovná se
Související články
Zkontrolujte, zda je v listu v Excelu použit filtr
Tento kurz vám pomůže rychle zkontrolovat, zda je filtr aplikován na určitý sloupec nebo na celý list, aniž byste museli ručně kontrolovat sloupec po sloupci.
Vložit data do filtrovaného seznamu pouze přeskakování skrytých řádků
Tento kurz poskytuje podrobné kroky, jak vložit data do filtrovaných řádků v Excelu přeskočením skrytých řádků.
Zkopírujte data do jiného listu pomocí rozšířeného filtru
Tento kurz ukazuje, jak používat pokročilé filtry v Excelu ke kopírování výsledků filtru do jiného listu.
Vymazat filtry ze všech listů v aktivním sešitu
Předpokládejme, že jste vytvořili několik filtrů v různých listech sešitu aplikace Excel a nyní je chcete vymazat všechny najednou. Obvykle je potřeba zkontrolovat filtry list po listu a poté je ručně vymazat. Pokud chcete vymazat filtry ze všech listů v aktivním sešitu, vyzkoušejte metodu v tomto článku.
Obsah
- Pokročilý filtr vs. normální filtr
- Příklady použití pokročilého filtru
- Extrahujte jedinečný seznam
- Filtrujte v jednom sloupci s více kritérii (odpovídají libovolným kritériím)
- Filtrujte ve více sloupcích s více kritérii
- S logikou AND (splňuje všechna kritéria)
- S logikou OR (splňujte všechna kritéria)
- S logikou AND i OR
- Pokročilý filtr se zástupnými znaky
- Extrahujte pouze určité sloupce
- Poznámky k pokročilému filtru
- Související články
- Nejlepší kancelářské nástroje produktivity
- Komentáře
Nejlepší nástroje pro produktivitu v kanceláři
Vylepšete si své excelové dovednosti s Kutools for Excela zažijte efektivitu jako nikdy předtím. Kutools for Excel Nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času. Kliknutím sem získáte funkci, kterou nejvíce potřebujete...
Office Tab Přináší do Office rozhraní s kartami a výrazně vám usnadňuje práci
- Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
- Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
- Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!