Пашыраны фільтр у Excel: прыклады. Як зрабіць пашыраны фільтр у Excel і як ім карыстацца?

Многія супрацоўнікі разнастайных арганізацый, каму даводзіцца якім-небудзь чынам працаваць з Mircosot Excel, няхай гэта будзе звычайныя бухгалтары або аналітыкі, часта сутыкаюцца з неабходнасцю выбару шэрагу значэнняў з вялізнага масіва дадзеных. Для спрашчэння выканання гэтай задачы і была створана сістэма фільтрацыі.

Звычайны і пашыраны фільтр

У Excel прадстаўлены найпросты фільтр, які запускаецца з ўкладкі «Дадзеныя» - «Фільтр» (Data - Filter ў англамоўнай версіі праграмы) або пры дапамозе цэтліка на панэлі інструментаў, падобнага на конусападобную варонку для пералівання вадкасці ў ёмістасці з вузкім горлышком.

Для большасці выпадкаў дадзены фільтр з'яўляецца цалкам аптымальным варыянтам. Але, калі неабходна ажыццявіць адбор па вялікай колькасці умоў (ды яшчэ і па некалькіх слупках, радках і вочках), многія задаюцца пытаннем, як зрабіць пашыраны фільтр у Excel. У англамоўнай версіі называецца Advanced filter.

Першае выкарыстанне пашыранага фільтра

У Excel вялікая частка працы праходзіць з табліцамі. Па-першае, гэта зручна, па-другое, у адным файле можна захаваць звесткі на некалькіх старонках (ўкладках). Над асноўнай табліцай пажадана стварыць некалькі радкоў, самую верхнюю з якіх пакінуць для шапкі, менавіта ў дадзеныя радкі будуць ўпісвацца ўмовы пашыранага фільтра Excel. У далейшым фільтр напэўна будзе зьменены: калі спатрэбіцца больш умоў, заўсёды можна ўставіць у патрэбным месцы яшчэ адзін радок. Але пажадана, каб паміж вочкамі дыяпазону умоў і вочкамі зыходных дадзеных была адна незадзейнічаны радок.

Як выкарыстоўваць пашыраны фільтр у Excel, прыклады, разгледзім ніжэй.

прымяненне фільтра

У прыведзенай табліцы радкі 1 і 2 прызначаныя для дыяпазону умоў, радкі з 4 па 7 - для дыяпазону зыходных дадзеных.

Для пачатку варта ўвесці ў радок 2 адпаведныя значэння, ад якіх будзе адштурхоўвацца пашыраны фільтр у Excel.

Запуск фільтра ажыццяўляецца з дапамогай вылучэння вочак зыходных дадзеных, пасля чаго неабходна выбраць ўкладку «Дадзеныя» і націснуць кнопку «Дадаткова» (Data - Advanced адпаведна).

У якое адкрылася акне адлюструецца дыяпазон вылучаных клетак у полі «Зыходны дыяпазон». Згодна з прыведзеным прыкладу, радок прымае значэнне «$ A $ 4: $ F $ 12».

Поле «Дыяпазон умоваў» павінна запоўніцца значэннямі «$ A $ 1: $ F $ 2».

Акенца таксама змяшчае дзве ўмовы:

• фільтраваць спіс на месцы;
• скапіяваць вынік у іншае месца.

Першая ўмова дазваляе фармаваць вынік на месцы, адведзеным пад вочкі зыходнага дыяпазону. Другая ўмова дазваляе сфармаваць спіс вынікаў у асобным дыяпазоне, які варта паказаць у поле «Змясціць вынік у дыяпазон». Карыстальнік выбірае зручны варыянт, напрыклад, першы, акно «Пашыраны фільтр» у Excel зачыняецца.

Грунтуючыся на уведзеных дадзеных, фільтр сфармуе наступную табліцу.

Пры выкарыстанні ўмовы «Скапіяваць вынік у іншае месца» значэння з 4 і 5 радкоў адлюструюцца ў зададзеным карыстальнікам дыяпазоне. Зыходны дыяпазон ж застанецца без змен.

зручнасць выкарыстання

Апісаны спосаб не зусім зручны, таму для ўдасканалення звычайна выкарыстоўваюць мова праграмавання VBA, з дапамогай якога складаюць макрасы, якія дазваляюць аўтаматызаваць пашыраны фільтр у Excel.

Калі карыстальнік валодае ведамі VBA, рэкамендуецца вывучыць шэраг артыкулаў дадзенай тэматыкі і паспяхова рэалізоўваць задуманае. Пры змене значэнняў вочак радкі 2, адведзенай пад Excel пашыраны фільтр, дыяпазон умоў будзе мяняцца, налады скідацца, адразу запускацца зноўку і ў неабходным дыяпазоне будуць фармавацца патрэбныя звесткі.

складаныя запыты

Апроч працы з сапраўды зададзенымі значэннямі, пашыраны фільтр у Excel здольны апрацоўваць і складаныя запыты. Такімі з'яўляюцца уведзеныя дадзеныя, дзе частка знакаў заменена падстаноўных знакамі.

Табліца сімвалаў для складаных запытаў прыведзена ніжэй.

Варта ведаць, што значок * можа азначаць любую колькасць знакаў. Гэта значыць пры Уведзеныя значэнні «п *» будуць вернутыя ўсе значэння, па-за залежнасці ад колькасці сімвалаў пасля літары «п».

Знак «?» Мае на ўвазе толькі адзін знак.

Звязка OR і AND

Варта ведаць, што звесткі, зададзеныя адным радком у «дыяпазоне умоў», расцэньваюцца запісанымі ў звязку лагічным аператарам (AND). Гэта азначае, што некалькі ўмоў выконваюцца адначасова.

Калі ж дадзеныя запісаныя ў адзін слупок, пашыраны фільтр у Excel распазнае іх звязанымі лагічным аператарам (OR).

Табліца значэнняў прыме наступны выгляд:

зводныя табліцы

Яшчэ адзін спосаб фільтравання дадзеных ажыццяўляецца з дапамогай каманды «Устаўка - Табліца - Зводная табліца» (Insert - Table - PivotTable ў англамоўнай версіі).

Згаданыя табліцы аналагічна працуюць з вылучаным загадзя дыяпазонам дадзеных і адбіраюць унікальныя значэння, каб у далейшым падвергнуць аналізу. На справе гэта выглядае як праца з выпадальным спісам унікальных палёў (да прыкладу, прозвішчаў супрацоўніка кампаніі) і дыяпазонам значэнняў, якія выдаюцца пры выбары унікальнага поля.

Нязручнасць выкарыстання зводных табліц складаецца ў неабходнасці ручной карэкціроўкі зыходных дадзеных пры змене такіх.

заключэнне

У заключэнне варта адзначыць, што вобласць прымянення фільтраў у Microsoft Excel вельмі шырокая і разнастайная. Дастаткова ўжыць фантазію і развіваць уласныя веды, уменні і навыкі.

Сам па сабе фільтр просты ва ўжыванні і засваенні, нескладана разабрацца, як карыстацца пашыраным фільтрам ў Excel, але ён прызначаны для выпадкаў, калі неабходна малая колькасць раз вырабіць адсяванне звестак для далейшай апрацоўкі. Як правіла, не прадугледжвае працу з вялікімі масівамі зьвестак з прычыны звычайнага чалавечага фактару. Тут ужо на дапамогу прыходзяць больш прадуманыя і прасунутыя тэхналогіі апрацоўкі звестак у Microsoft Excel.

Велізарнай папулярнасцю карыстаюцца макрасы, што складаюцца на мове VBA. Яны дазваляюць запусціць значная колькасць фільтраў, якія спрыяюць адбору значэнняў і высновы іх у адпаведныя дыяпазоны.

Макрасы паспяхова замяняюць шматгадзіннай праца па складанні зводнай, перыядычным і іншай справаздачнасці, замяняючы працяглы час аналізу велізарных масіваў ўсяго толькі односекундным клікам.

Выкарыстанне макрасаў апраўдана і нязручна. Любы, хто сутыкаўся з неабходнасцю прымянення, заўсёды знойдзе пры жаданні дастаткова матэрыялу для развіцця сваіх ведаў і пошуку адказаў на пытанні, якія цікавяць пытанні.