Для пераважнай большасці карыстальнікаў Excel, калі ў галаве ўзнікае слова «фільтрацыя даных», толькі звычайны класічны фільтр з укладкі Дадзеныя – Фільтр (Даныя — Фільтр):
Такі фільтр, без сумневу, звыклая рэч, і ў большасці выпадкаў ён падыдзе. Аднак бываюць сітуацыі, калі патрабуецца адфільтраваць па вялікай колькасці складаных умоў адразу ў некалькіх слупках. Звычайны фільтр тут не вельмі зручны і хочацца чагосьці больш магутнага. Такі інструмент можа быць пашыраны фільтр, асабліва з невялікай “дапрацоўкай напільнікам” (паводле традыцыі).
База
Каб пачаць, устаўце некалькі пустых радкоў над табліцай даных і скапіруйце туды загаловак табліцы - гэта будзе дыяпазон з умовамі (выдзелены жоўтым для нагляднасці):
Паміж жоўтымі вочкамі і зыходнай табліцай павінен быць хаця б адзін пусты радок.
Менавіта ў жоўтыя ячэйкі трэба ўвесці крытэрыі (умовы), па якіх потым будзе ажыццяўляцца фільтраванне. Напрыклад, калі ў III квартале ў маскоўскім «Ашане» трэба выбраць бананы, то ўмовы будуць выглядаць так:
Для фільтрацыі вылучыце любую ячэйку дыяпазону з зыходнымі дадзенымі, адкрыйце ўкладку Дата І націсніце Дадаткова (Дадзеныя — Пашыраны). У якое адкрылася акне дыяпазон з дадзенымі ўжо павінен быць аўтаматычна ўведзены, і нам застанецца толькі ўказаць дыяпазон умоў, гэта значыць A1:I2:
Звярніце ўвагу, што дыяпазон умоў нельга вылучаць «з запасам», г.зн. нельга вылучаць лішнія пустыя жоўтыя радкі, таму што пустая ячэйка ў дыяпазоне ўмоў ўспрымаецца Excel як адсутнасць крытэрыю, а цэлая пустая. радок як запыт на адлюстраванне ўсіх даных без разбору.
перамыкач Скапіруйце вынік у іншае месца дазволіць вам адфільтраваць спіс не тут жа на гэтым аркушы (як са звычайным фільтрам), а выгрузіць выбраныя радкі ў іншы дыяпазон, які потым трэба будзе ўказаць у полі Пастаўце вынік у дыяпазон. У гэтым выпадку мы не выкарыстоўваем гэтую функцыю, мы сыходзім Спіс фільтраў на месцы і націсніце OK. Выбраныя радкі будуць адлюстраваны на аркушы:
Даданне макраса
«Ну дзе ж тут зручнасць?» вы спытаеце, і вы будзеце мець рацыю. Вам трэба не толькі рукамі ўвесці ўмовы ў жоўтыя вочкі, але і адкрыць дыялогавае акно, увесці туды дыяпазоны, націснуць OK. Сумна, згодны! Але «ўсё мяняецца, калі яны прыходзяць ©» - макрасы!
Працу з пашыраным фільтрам можна значна паскорыць і спрасціць з дапамогай простага макраса, які будзе аўтаматычна запускаць пашыраны фільтр пры ўводзе ўмоў, г.зн. пры змене любой жоўтай клеткі. Пстрыкніце правай кнопкай мышы на ўкладцы бягучага аркуша і абярыце каманду Зыходны тэкст (Зыходны код). У якое адкрылася акне скапіруйце і ўстаўце наступны код:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:I5")) Is Nothing Then On Error Resume Next ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange :=Дыяпазон("A1").CurrentRegion End If End Sub Гэтая працэдура будзе запускацца аўтаматычна пры змене любой ячэйкі на бягучым аркушы. Калі адрас змененай ячэйкі трапляе ў жоўты дыяпазон (A2:I5), то гэты макрас выдаляе ўсе фільтры (калі такія маюцца) і паўторна прымяняе пашыраны фільтр да табліцы зыходных дадзеных, пачынаючы з A7, г.зн. усё будзе адфільтравана імгненна, адразу пасля ўводу наступнага ўмовы:
Так што ўсё значна лепш, так? 🙂
Рэалізацыя складаных запытаў
Цяпер, калі ўсё фільтруецца на хаду, мы можам крыху паглыбіцца ў нюансы і разабраць механізмы больш складаных запытаў у пашыраным фільтры. У дадатак да ўводу дакладных супадзенняў вы можаце выкарыстоўваць розныя знакі падстаноўкі (* і ?) і матэматычныя знакі няроўнасці ў шэрагу ўмоў для рэалізацыі прыблізнага пошуку. Рэгістр характару не мае значэння. Для нагляднасці я звёў усе магчымыя варыянты ў табліцу:
| крытэрый | Вынік |
| гр* або гр | усе клеткі, пачынаючы з GrIe Grвуха, Grаперук, Grанат і г.д. |
| = цыбуля | усе клеткі дакладна і толькі са словам Лук, г.зн. дакладнае супадзенне |
| *ліў* або *ліў | клетак, якія змяшчаюць Ліў як падкрэсліць, г. зн ОЛіўШто, Ліўep, згоднаЛіў і г.д. |
| =п*в | словы, якія пачынаюцца з П і заканчваецца на В ie П1.в, Пэфірв і г.д. |
| а*с | словы, якія пачынаюцца з А і далей які змяшчае СIe Апелсin, АНанас, Asai і г.д. |
| =*s | словы, якія заканчваюцца на С |
| =???? | усе ячэйкі з тэкстам з 4 знакаў (літары або лічбы, уключаючы прабелы) |
| =m??????n | усе вочкі з тэкстам з 8 знакаў, пачынаючы з М і заканчваецца на НIe Мандарын, Мнепакойн і г.д. |
| =*н??а | усе словы, якія заканчваюцца на А, дзе 4-я літара з канца НIe Праменьнikа, згоднанozа і г.д. |
| >=е | усе словы, якія пачынаюцца з Э, Ю or Я |
| <>*о* | усе словы без літары О |
| <>*віч | усе словы, акрамя тых, якія заканчваюцца на ВІЧ (напрыклад, фільтраваць жанчын па імені па бацьку) |
| = | усе пустыя вочкі |
| <> | усе непустыя клеткі |
| > = 5000 | усе ячэйкі са значэннем большым або роўным 5000 |
| 5 або =5 | усе вочкі са значэннем 5 |
| >=3 | усе ячэйкі з датай пасля 18 сакавіка 2013 г. (уключна) |
Тонкія моманты:
- Знак * азначае любую колькасць любых сімвалаў, а ? – любы адзін знак.
- Логіка апрацоўкі тэкставых і лікавых запытаў трохі адрозніваецца. Так, напрыклад, ячэйка ўмовы з лічбай 5 не азначае пошук усіх лічбаў, якія пачынаюцца з пяці, але ячэйка ўмовы з літарай B роўная B*, гэта значыць будзе шукаць любы тэкст, які пачынаецца з літары B.
- Калі тэкставы запыт не пачынаецца са знака =, то ў канцы можна ў думках паставіць *.
- Даты трэба ўводзіць у амерыканскім фармаце месяц-дзень-год і праз дроб (нават калі ў вас ёсць Excel і рэгіянальныя налады).
Лагічныя злучнікі І-АБО
Ўмовы, запісаныя ў розных вочках, але ў адным радку, лічацца звязанымі паміж сабой лагічным аператарам И (І):
Тыя. фільтр бананы мне ў трэцім квартале, менавіта ў маскве і заадно з ашана.
Калі вам трэба звязаць умовы з дапамогай лагічнага аператара OR (OR), то іх проста трэба ўвесці ў розныя радкі. Напрыклад, калі нам трэба знайсці ўсе заказы мэнэджэра Воліна на маскоўскія персікі і ўсе заказы на лук у трэцім квартале ў Самары, то гэта можна задаць у шэрагу ўмоў наступным чынам:
Калі вам трэба навязаць два і больш умоў на адзін слупок, то вы можаце проста прадубляваць загаловак слупка ў дыяпазоне крытэрыяў і ўвесці пад ім другі, трэці і г.д. тэрміны. Так, напрыклад, вы можаце выбраць усе транзакцыі з сакавіка па май:
Увогуле, пасля «дапрацоўкі файла» пашыраны фільтр атрымліваецца цалкам прыстойным інструментам, месцамі не горшым за класічны аўтафільтр.
- Суперфільтр на макрасы
- Што такое макрасы, куды і як уставіць код макрасаў у Visual Basic
- Разумныя табліцы ў Microsoft Excel