Keď sa drvivej väčšine používateľov Excelu vynorí v hlave slovo „filtrovanie údajov“, stačí bežný klasický filter z karty Údaje – Filter (Údaje – Filter):
Takýto filter je nepochybne známa vec a vo väčšine prípadov to bude stačiť. Sú však situácie, keď potrebujete filtrovať podľa veľkého počtu zložitých podmienok vo viacerých stĺpcoch naraz. Zvyčajný filter tu nie je príliš pohodlný a chcem niečo výkonnejšie. Takýto nástroj by mohol byť pokročilý filter, najmä s malým „dokončením pilníkom“ (podľa tradície).
Základňa
Ak chcete začať, vložte niekoľko prázdnych riadkov nad tabuľku s údajmi a skopírujte tam hlavičku tabuľky – bude to rozsah s podmienkami (pre prehľadnosť zvýraznený žltou):
Medzi žltými bunkami a pôvodnou tabuľkou musí byť aspoň jeden prázdny riadok.
Práve do žltých buniek je potrebné zadať kritériá (podmienky), podľa ktorých sa potom bude filtrovať. Napríklad, ak potrebujete vybrať banány v moskovskom „Auchan“ v štvrťroku III, podmienky budú vyzerať takto:
Ak chcete filtrovať, vyberte ľubovoľnú bunku v rozsahu so zdrojovými údajmi a otvorte kartu dátum A kliknite na ikonu Okrem toho (Údaje – pokročilé). V okne, ktoré sa otvorí, by už mal byť automaticky zadaný rozsah s údajmi a budeme musieť zadať iba rozsah podmienok, teda A1:I2:
Upozorňujeme, že rozsah podmienok nemožno prideliť „s okrajom“, tj nemôžete vybrať extra prázdne žlté riadky, pretože prázdnu bunku v rozsahu podmienok Excel vníma ako absenciu kritéria a celú prázdnu riadok ako požiadavku na zobrazenie všetkých údajov bez rozdielu.
prepínač Skopírujte výsledok na iné miesto vám umožní filtrovať zoznam nie priamo na tomto hárku (ako pri bežnom filtri), ale uvoľniť vybraté riadky do iného rozsahu, ktorý potom bude potrebné zadať v poli Vložte výsledok do rozsahu. V tomto prípade túto funkciu nepoužívame, odchádzame Zoznam filtrov na mieste a kliknite na tlačidlo OK. Vybraté riadky sa zobrazia na hárku:
Pridanie makra
"No, kde je tu pohodlie?" pýtaš sa a budeš mať pravdu. Nielenže musíte zadávať podmienky do žltých buniek rukami, ale aj otvoriť dialógové okno, zadať tam rozsahy, stlačiť OK. Smutné, súhlasím! Ale „všetko sa zmení, keď prídu ©“ – makrá!
Práca s pokročilým filtrom sa dá výrazne urýchliť a zjednodušiť pomocou jednoduchého makra, ktoré automaticky spustí rozšírený filter pri zadaní podmienok, teda zmene ľubovoľnej žltej bunky. Kliknite pravým tlačidlom myši na kartu aktuálneho hárka a vyberte príkaz Zdrojový text (Zdrojový kód). V okne, ktoré sa otvorí, skopírujte a prilepte nasledujúci kód:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Is Nothing Then On Error Obnoviť ďalší ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Rozsah("A1").CurrentRegion End If End Sub Tento postup sa automaticky spustí, keď sa zmení ktorákoľvek bunka v aktuálnom hárku. Ak adresa zmenenej bunky spadá do žltého rozsahu (A2:I5), potom toto makro odstráni všetky filtre (ak existujú) a znova použije rozšírený filter na tabuľku zdrojových údajov počnúc A7, tj všetko bude filtrované okamžite, okamžite po zadaní nasledujúcej podmienky:
Takže všetko je oveľa lepšie, nie? 🙂
Implementácia zložitých dopytov
Teraz, keď sa všetko filtruje za behu, môžeme ísť trochu hlbšie do nuancií a rozobrať mechanizmy zložitejších dopytov v rozšírenom filtri. Okrem zadávania presných zhôd môžete použiť rôzne zástupné znaky (* a ?) a znaky matematickej nerovnosti v rade podmienok na implementáciu približného vyhľadávania. Na prípade postavy nezáleží. Pre prehľadnosť som všetky možné možnosti zhrnul do tabuľky:
| kritérium | Výsledok |
| gr* alebo gr | všetky bunky začínajúce na GrIe Grucho, Grapefruit, Granat a tak ďalej |
| = cibuľa | všetky bunky presne a len so slovom Luk, teda presná zhoda |
| *liv* alebo *liv | bunky obsahujúce Liv ako podčiarknuť, tj ОLivŽe, Livep, PodľaLiv a tak ďalej |
| =p*v | slová začínajúce na П a končiace na В ie Пprvýв, Пéterв a tak ďalej |
| a*s | slová začínajúce na А a ďalej obsahuje СIe Аpelсin, Аnanaс, Asai a tak ďalej |
| =*s | slová končiace na С |
| =???? | všetky bunky s textom o 4 znakoch (písmená alebo čísla vrátane medzier) |
| =m????n | všetky bunky s textom s 8 znakmi začínajúcimi na М a končiace na НIe Мandariн, Мúzkosťн a tak ďalej |
| =*n??a | všetky slová končiace na А, kde je 4. písmeno od konca НIe Lúčнikа, Podľaнozа a tak ďalej |
| >=e | všetky slová začínajúce na Э, Ю or Я |
| <>*o* | všetky slová, ktoré neobsahujú písmeno О |
| <>*vich | všetky slová okrem tých, ktoré končia na HIV (napríklad filtrujte ženy podľa stredného mena) |
| = | všetky prázdne bunky |
| <> | všetky neprázdne bunky |
| > = 5000 | všetky bunky s hodnotou väčšou alebo rovnou 5000 |
| 5 alebo =5 | všetky bunky s hodnotou 5 |
| >=3 | všetky bunky s dátumom po 18. marci 2013 (vrátane) |
Jemné body:
- Znak * znamená ľubovoľný počet ľubovoľných znakov a ? - ľubovoľná postava.
- Logika spracovania textových a numerických dotazov je mierne odlišná. Takže napríklad bunka podmienky s číslom 5 neznamená hľadať všetky čísla začínajúce päťkou, ale bunka podmienky s písmenom B sa rovná B*, teda bude hľadať akýkoľvek text začínajúci písmenom B.
- Ak textový dotaz nezačína znakom =, potom môžete v duchu dať * na koniec.
- Dátumy musia byť zadané v americkom formáte mesiac-deň-rok a cez zlomky (aj keď máte Excel a miestne nastavenia).
Logické spojky AND-OR
Podmienky napísané v rôznych bunkách, ale v rovnakom riadku, sa považujú za vzájomne prepojené logickým operátorom И (A):
Tie. filtrovať banány pre mňa v treťom štvrťroku, presne v Moskve a zároveň z Auchanu.
Ak potrebujete prepojiť podmienky s logickým operátorom OR (OR), potom ich stačí zadať do rôznych riadkov. Napríklad, ak potrebujeme nájsť všetky objednávky manažéra Volina na moskovské broskyne a všetky objednávky na cibuľu v treťom štvrťroku v Samare, potom to možno špecifikovať v rozsahu podmienok takto:
Ak potrebujete uložiť dve alebo viac podmienok na jeden stĺpec, môžete jednoducho duplikovať hlavičku stĺpca v rozsahu kritérií a zadať pod ňu druhú, tretiu atď. podmienky. Môžete si teda napríklad vybrať všetky transakcie od marca do mája:
Vo všeobecnosti po „dokončení so súborom“ vychádza pokročilý filter ako celkom slušný nástroj, miestami nie horší ako klasický automatický filter.
- Superfilter na makrách
- Čo sú makrá, kde a ako vložiť kód makra vo Visual Basicu
- Inteligentné tabuľky v programe Microsoft Excel