Pokročilý filtr aplikace Excel - Kompletní průvodce s příklady

Sledujte video - pokročilý filtr aplikace Excel

Rozšířený filtr aplikace Excel je jednou z nejvíce podceňovaných a málo využívaných funkcí, se kterými jsem se setkal.

Pokud pracujete s aplikací Excel, jsem si jist, že jste ji použili (nebo jste alespoň slyšeli o běžném filtru Excelu). Rychle filtruje datovou sadu na základě výběru, zadaného textu, čísla nebo jiných podobných kritérií.

V této příručce vám ukážu skvělé věci, které můžete dělat pomocí pokročilého filtru aplikace Excel.

Ale nejprve … Co je pokročilý filtr aplikace Excel?

Rozšířený filtr aplikace Excel - jak název napovídá - je pokročilou verzí běžného filtru. Můžete to použít, když potřebujete použít k filtrování sady dat složitější kritéria.

Zde jsou některé rozdíly mezi běžným filtrem a rozšířeným filtrem:

• Zatímco běžný datový filtr bude filtrovat stávající datovou sadu, můžete použít pokročilý filtr Excelu k extrahování datové sady také do jiného umístění.
• Rozšířený filtr aplikace Excel vám umožňuje používat složitá kritéria. Pokud například máte data o prodeji, můžete filtrovat data podle kritéria, kde je obchodní zástupce Bob a region je buď Severní nebo Jižní (uvidíme, jak to udělat v příkladech). Podpora Office k tomu má dobré vysvětlení.
• Rozšířený filtr aplikace Excel můžete použít k extrahování jedinečných záznamů z vašich dat (více o tom za sekundu).

POKROČILÝ FILTR EXCEL (příklady)

Nyní se podívejme na příklad použití rozšířeného filtru v aplikaci Excel.

Příklad 1 - Extrahování jedinečného seznamu

Pomocí rozšířeného filtru aplikace Excel můžete rychle extrahovat jedinečné záznamy ze sady dat (nebo jinými slovy odstranit duplikáty).

V aplikaci Excel 2007 a novějších verzích existuje možnost odebrat duplikáty z datové sady. To ale mění vaši stávající datovou sadu. Chcete -li zachovat původní data neporušená, musíte vytvořit kopii dat a poté použít možnost Odebrat duplikáty. Rozšířený filtr aplikace Excel vám umožní vybrat umístění a získat jedinečný seznam.

Podívejme se, jak pomocí pokročilých filtrů získat jedinečný seznam.

Předpokládejme, že máte datovou sadu, jak je uvedeno níže:

Jak vidíte, v této sadě dat jsou duplicitní záznamy (zvýrazněny oranžově). Důvodem může být chyba při zadávání dat nebo výsledek kompilace dat.

V takovém případě můžete použít nástroj Excel Advanced Filter k rychlému získání seznamu všech jedinečných záznamů na jiném místě (aby vaše původní data zůstala nedotčena).

Zde jsou kroky k získání všech jedinečných záznamů:

• Vyberte celou sadu dat (včetně záhlaví).
• Přejít na kartu Data -> Třídit a filtrovat -> Pokročilé. (Můžete také použít klávesovou zkratku - Alt + A + Q). Otevře se dialogové okno Rozšířený filtr.
• V dialogovém okně Rozšířený filtr použijte následující podrobnosti:
  • Akce: Vyberte možnost „Kopírovat do jiného umístění“. To vám umožní určit umístění, kde můžete získat seznam jedinečných záznamů.
  • Rozsah seznamu: Ujistěte se, že odkazuje na datovou sadu, ze které chcete najít jedinečné záznamy. Také se ujistěte, že jsou zahrnuty záhlaví v datové sadě.
  • Rozsah kritérií: Nechte to prázdné.
  • Kopírovat do: Zadejte adresu buňky, kam chcete získat seznam jedinečných záznamů.
  • Kopírovat pouze jedinečné záznamy: Zaškrtněte tuto možnost.
• Klikněte na OK.

To vám okamžitě poskytne seznam všech jedinečných záznamů.

Pozor: Když k získání jedinečného seznamu používáte Pokročilý filtr, ujistěte se, že jste vybrali také záhlaví. Pokud tak neučiníte, bude považovat první buňku za záhlaví.

Příklad 2 - Použití kritérií v rozšířeném filtru aplikace Excel

Získávání jedinečných záznamů je jednou z mnoha věcí, které můžete s pokročilým filtrem Excelu provádět.

Jeho primární užitečnost spočívá v jeho schopnosti umožnit použití komplexních kritérií pro filtrování dat.

Zde mám na mysli složitá kritéria. Předpokládejme, že máte datovou sadu, jak je uvedeno níže, a chcete rychle získat všechny záznamy, kde jsou tržby vyšší než 5 000 a regionem jsou USA.

Zde je návod, jak můžete použít pokročilý filtr Excelu k filtrování záznamů na základě zadaných kritérií:

• Prvním krokem při použití rozšířeného filtru aplikace Excel se složitými kritérii je určit kritéria. Chcete -li to provést, zkopírujte záhlaví a vložte je někam do listu.
• Zadejte kritéria, pro která chcete data filtrovat. V tomto případě, protože chceme získat všechny záznamy pro USA s tržbami více než 5 000, zadejte „USA“ do buňky pod Oblast a> 5 000 do buňky pod Prodej. To by nyní bylo použito jako vstup v Pokročilém filtru k získání filtrovaných dat (jak je znázorněno v dalších krocích).
• Vyberte celou sadu dat (včetně záhlaví).
• Přejděte na kartu Data -> Třídit a filtrovat -> Pokročilé. Otevře se dialogové okno Rozšířený filtr.
• V dialogovém okně Rozšířený filtr použijte následující podrobnosti:
  • Akce: Vyberte možnost „Kopírovat do jiného umístění“. To vám umožní určit umístění, kde můžete získat seznam jedinečných záznamů.
  • Rozsah seznamu: Ujistěte se, že odkazuje na datovou sadu, ze které chcete najít jedinečné záznamy. Také se ujistěte, že jsou zahrnuty záhlaví v datové sadě.
  • Rozsah kritérií: Zadejte kritéria, která jsme vytvořili v krocích výše. V tomto případě by to bylo F1: I3.
  • Kopírovat do: Zadejte adresu buňky, kam chcete získat seznam jedinečných záznamů.
  • Kopírovat pouze jedinečné záznamy: Zaškrtněte tuto možnost.
• Klikněte na OK.

To by vám okamžitě poskytlo všechny záznamy, kde je region USA a tržby jsou více než 5 000.

Výše uvedený příklad je případ, kdy se filtrování provádí na základě dvou kritérií (USA a tržby vyšší než 5 000).

Rozšířený filtr aplikace Excel vám umožňuje vytvořit mnoho různých kombinací kritérií.

Zde je několik příkladů, jak můžete tyto filtry konstruovat.

Pomocí kritérií AND

Pokud chcete použít kritéria AND, musíte je zadat pod záhlaví.

Například:

• Filtrování záznamů, když je region USA A obchodním zástupcem je Joe.
• Chcete -li filtrovat záznamy, když je region USA A hodnota prodeje je větší než 5 000.
• Když je region USA A tržby se zaznamenávají po 31-03-2017.

Použití kritérií NEBO

Pokud chcete použít kritéria NEBO, musíte určit kritéria ve stejném sloupci.

Například:

• Filtrování záznamů, když je region USA NEBO region je Asie.
• Filtrování záznamů, když je obchodní zástupce Bob NEBO Martha.

Už jste si museli uvědomit, že když máme kritéria ve stejném řádku, je to an A kritéria, a když to máme v různých řádcích, je to an NEBO kritéria.

Příklad 3 - Použití znaků WILDCARD v rozšířeném filtru v aplikaci Excel

Rozšířený filtr aplikace Excel také umožňuje použití zástupných znaků při vytváření kritérií.

V aplikaci Excel jsou tři zástupné znaky:

1. * (hvězdička) - Představuje libovolný počet znaků. Například ex* může znamenat excel, exceles, příklad, expert atd.
2. ? (otazník) - Představuje jeden jediný znak. Například Tr? Mp může znamenat Trump nebo Tramp.
3. ~ (vlnovka) - Používá se k identifikaci zástupných znaků (~, *,?) V textu.

Nyní se podívejme, jak můžeme tyto zástupné znaky použít k pokročilému filtrování v aplikaci Excel.

• Chcete -li filtrovat záznamy, kde název obchodního zástupce začíná od J.

Všimněte si, že * představují libovolný počet znaků. Takže každý zástupce s názvem začínajícím na J by byl filtrován podle těchto kritérií.

Podobně můžete použít i další dva zástupné znaky.

Poznámka: Pokud používáte Office 365, měli byste se podívat na funkci FILTR. Dokáže spoustu věcí, které pokročilý filtr dokáže pomocí jednoduchého vzorce.

POZNÁMKA:

1. Pamatujte, že záhlaví v kritériích by měla být přesně stejná jako v datové sadě.
2. Při kopírování do jiných umístění nelze pokročilé filtrování vrátit zpět.