Pokročilý filtr a nějaké kouzlo

Pro drtivou většinu uživatelů Excelu, když se jim v hlavě vynoří slovo „filtrování dat“, stačí obvyklý klasický filtr ze záložky Data – Filtr (Data — Filtr):

Takový filtr je bezpochyby známá věc a ve většině případů bude stačit. Jsou však situace, kdy potřebujete filtrovat podle velkého počtu složitých podmínek ve více sloupcích najednou. Zde obvyklý filtr není příliš pohodlný a chci něco výkonnějšího. Takový nástroj by mohl být pokročilý filtr, zejména s trochou „dokončení pilníkem“ (podle tradice).

Základna

Chcete-li začít, vložte několik prázdných řádků nad svou datovou tabulku a zkopírujte tam záhlaví tabulky – bude to rozsah s podmínkami (pro jasnost zvýrazněný žlutě):

Mezi žlutými buňkami a původní tabulkou musí být alespoň jeden prázdný řádek.

Právě do žlutých buněk je potřeba zadat kritéria (podmínky), podle kterých se pak bude filtrování provádět. Pokud například potřebujete vybrat banány v Moskvě „Auchan“ ve čtvrtletí III, budou podmínky vypadat takto:

Chcete-li filtrovat, vyberte libovolnou buňku v rozsahu se zdrojovými daty a otevřete kartu Data A klikněte na tlačítko Kromě toho (Data – Pokročilé). V okně, které se otevře, by již měl být automaticky zadán rozsah s daty a budeme muset zadat pouze rozsah podmínek, tj. A1:I2:

Upozorňujeme, že rozsah podmínek nelze přidělit „s okrajem“, tj. nemůžete vybrat extra prázdné žluté řádky, protože prázdnou buňku v rozsahu podmínek Excel vnímá jako absenci kritéria a celou prázdnou řádek jako požadavek na zobrazení všech údajů bez rozdílu.

Vypínač Zkopírujte výsledek na jiné místo vám umožní filtrovat seznam ne přímo na tomto listu (jako u běžného filtru), ale uvolnit vybrané řádky do jiného rozsahu, který pak bude nutné zadat v poli Dejte výsledek do rozsahu. V tomto případě tuto funkci nepoužíváme, odcházíme Seznam filtrů na místě a klepněte na tlačítko OK. Vybrané řádky se zobrazí na listu:

Přidání makra

"No, kde je tady pohodlí?" ptáš se a budeš mít pravdu. Nejen, že musíte rukama zadávat podmínky do žlutých buněk, ale také otevřít dialogové okno, zadat tam rozsahy, stisknout OK. Smutné, souhlasím! Ale „všechno se změní, když přijdou ©“ – makra!

Práci s pokročilým filtrem lze výrazně urychlit a zjednodušit pomocí jednoduchého makra, které automaticky spustí rozšířený filtr při zadání podmínek, tedy změně libovolné žluté buňky. Klepněte pravým tlačítkem myši na záložku aktuálního listu a vyberte příkaz Zdrojový text (Zdrojový kód). V okně, které se otevře, zkopírujte a vložte následující kód:

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Is Nothing Then On Chyba Obnovit další ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Rozsah("A1").CurrentRegion End If End Sub  

Tento postup se automaticky spustí, když se změní jakákoli buňka v aktuálním listu. Pokud adresa změněné buňky spadá do žlutého rozsahu (A2:I5), pak toto makro odstraní všechny filtry (pokud existují) a znovu aplikuje rozšířený filtr na zdrojovou datovou tabulku počínaje A7, tj. vše bude filtrováno okamžitě, okamžitě po zadání následující podmínky:

Takže všechno je mnohem lepší, ne? 🙂

Implementace složitých dotazů

Nyní, když se vše filtruje za chodu, můžeme jít trochu hlouběji do nuancí a rozebrat mechanismy složitějších dotazů v pokročilém filtru. Kromě zadávání přesných shod můžete použít různé zástupné znaky (* a ?) a znaky matematické nerovnosti v řadě podmínek pro implementaci přibližného vyhledávání. Na případu postavy nezáleží. Pro přehlednost jsem všechny možné možnosti shrnul do tabulky:

Jemné body:

• Znak * znamená libovolný počet libovolných znaků a ? – jakákoliv postava.
• Logika při zpracování textových a číselných dotazů je mírně odlišná. Například buňka podmínky s číslem 5 tedy neznamená hledat všechna čísla začínající pěti, ale buňka podmínky s písmenem B se rovná B*, ​​tedy bude hledat libovolný text začínající písmenem B.
• Pokud textový dotaz nezačíná znakem =, můžete na konec v duchu umístit *.
• Dates must be entered in the US format month-day-year and through a fraction (even if you have Excel and regional settings).

Logické spojky AND-OR

Podmínky zapsané v různých buňkách, ale na stejném řádku, jsou považovány za propojené logickým operátorem И (A):

Tito. filtrovat banány pro mě ve třetím čtvrtletí, přesně v Moskvě a zároveň z Auchanu.

Pokud potřebujete propojit podmínky s logickým operátorem OR (OR), pak je stačí zadat do různých řádků. Pokud například potřebujeme najít všechny objednávky manažera Voliny na moskevské broskve a všechny objednávky na cibuli ve třetím čtvrtletí v Samaře, lze to specifikovat v řadě podmínek takto:

Pokud potřebujete uložit dvě nebo více podmínek na jeden sloupec, můžete jednoduše duplikovat záhlaví sloupce v rozsahu kritérií a zadat pod něj druhou, třetí atd. podmínky. Můžete si tedy například vybrat všechny transakce od března do května:

Obecně platí, že po „dokončení se souborem“ vychází pokročilý filtr jako docela slušný nástroj, místy o nic horší než klasický autofiltr.

• Superfiltr na makrech
• Co jsou makra, kam a jak vložit kód makra ve Visual Basicu
• Inteligentní tabulky v aplikaci Microsoft Excel