За огромну већину корисника Екцел-а, када им се у глави појави реч „филтрирање података“, само уобичајени класични филтер са картице Подаци – Филтер (Подаци — Филтер):
Такав филтер је позната ствар, без сумње, и у већини случајева ће то учинити. Међутим, постоје ситуације када морате да филтрирате по великом броју сложених услова у неколико колона одједном. Уобичајени филтер овде није баш згодан и желим нешто моћније. Такав алат би могао бити напредни филтер, посебно уз мало „завршавање турпијама“ (према традицији).
Основа
Да бисте започели, уметните неколико празних линија изнад табеле са подацима и копирајте заглавље табеле тамо – ово ће бити опсег са условима (означен жутом бојом ради јасноће):
Мора постојати најмање једна празна линија између жутих ћелија и оригиналне табеле.
Управо у жуте ћелије треба да унесете критеријуме (услове), према којима ће се потом извршити филтрирање. На пример, ако треба да изаберете банане у московском „Ауцхану“ у ИИИ кварталу, онда ће услови изгледати овако:
Да бисте филтрирали, изаберите било коју ћелију у опсегу са изворним подацима, отворите картицу Датум И кликните на Додатно (Подаци — Напредно). У прозору који се отвори, опсег са подацима би већ требало да буде аутоматски унет и остаје нам само да наведемо опсег услова, односно А1:И2:
Имајте на уму да се опсег услова не може доделити „са маргином“, тј. не можете да изаберете екстра празне жуте линије, јер празну ћелију у опсегу услова Екцел перципира као одсуство критеријума, а целу празну линију као захтев да се сви подаци приказују неселективно.
прекидач Копирајте резултат на другу локацију ће вам омогућити да филтрирате листу не баш тамо на овом листу (као код обичног филтера), већ да испразните изабране редове у други опсег, који ће затим морати да се наведе у пољу Ставите резултат у опсег. У овом случају не користимо ову функцију, одлазимо Листа филтера на месту и кликните на дугме OK. Изабрани редови ће бити приказани на листу:
Додавање макроа
"Па, где је овде погодност?" питаш и бићеш у праву. Не само да морате рукама да унесете услове у жуте ћелије, већ и отворите оквир за дијалог, унесете опсеге, притисните OK. Тужно, слажем се! Али „све се мења када дођу ©“ – макрои!
Рад са напредним филтером може се знатно убрзати и поједноставити коришћењем једноставног макроа који ће аутоматски покренути напредни филтер када се унесу услови, односно мењају било коју жуту ћелију. Кликните десним тастером миша на картицу тренутног листа и изаберите команду Изворни текст (Изворни код). У прозору који се отвори копирајте и налепите следећи код:
Приватни под Радни лист_Цханге(БиВал Таргет Ас Ранге) Ако није Интерсецт(Таргет, Ранге("А2:И5")) није ништа онда у случају грешке Настави Следећи АцтивеСхеет.СховАллДата Ранге("А7").ЦуррентРегион.АдванцедФилтер Ацтион:=клФилтерИнПла :=Распон("А1").ЦуррентРегион Енд Иф Енд Суб Ова процедура ће се аутоматски покренути када се промени било која ћелија на тренутном радном листу. Ако адреса промењене ћелије падне у жути опсег (А2:И5), онда овај макро уклања све филтере (ако их има) и поново примењује проширени филтер на табелу изворних података почевши од А7, односно све ће бити филтрирано тренутно, одмах након уноса следећег услова:
Дакле, све је много боље, зар не? 🙂
Имплементација сложених упита
Сада када се све филтрира у ходу, можемо да уђемо мало дубље у нијансе и да раставимо механизме сложенијих упита у напредном филтеру. Поред уноса тачних подударања, можете користити различите џокер знакове (* и ?) и знакове математичке неједнакости у низу услова да бисте применили приближну претрагу. Велика и мала слова нису битна. Ради јасноће, сумирао сам све могуће опције у табели:
| Критеријум | Резултат |
| гр* или гр | све ћелије почевши од GrIe Grуво, Grапфруит, Grанат итд |
| = лук | све ћелије тачно и само са речју Лук, односно тачно подударање |
| *лив* или *лив | ћелије које садрже Лив како подвући, тј ОЛивКоји, Ливep, premaЛив итд |
| =п*в | речи које почињу са П и завршава са В ie Ппрвив, Петерв итд |
| као | речи које почињу са А и даље садржи СIe Апелсin, АНанас, Asai итд |
| =*с | речи које се завршавају на С |
| =???? | све ћелије са текстом од 4 знака (слова или бројеви, укључујући размаке) |
| =м??????н | све ћелије са текстом од 8 знакова који почињу са М и завршава са НIe Мандарин, Манксиозностн итд |
| =*н??а | све речи које се завршавају са А, где је 4. слово са краја НIe Снопнikа, premaнozа итд |
| >=е | све речи које почињу са Э, Ю or Я |
| <>*о* | све речи које не садрже слово О |
| <>*вицх | све речи осим оних које се завршавају на ХИВ-а (на пример, филтрирајте жене по средњем имену) |
| = | све празне ћелије |
| <> | све непразне ћелије |
| > = 5000 | све ћелије чија је вредност већа или једнака 5000 |
| 5 или =5 | све ћелије са вредношћу 5 |
| >=3 | све ћелије са датумом после 18. марта 2013. (укључиво) |
Суптилне тачке:
- Знак * означава било који број било ког карактера, а ? – било који лик.
- Логика у обради текстуалних и нумеричких упита је мало другачија. Тако, на пример, ћелија услова са бројем 5 не значи да тражимо све бројеве који почињу са пет, већ је ћелија услова са словом Б једнака Б*, односно тражиће било који текст који почиње словом Б.
- Ако текстуални упит не почиње знаком =, онда можете ментално ставити * на крај.
- Dates must be entered in the US format month-day-year and through a fraction (even if you have Excel and regional settings).
Логички спојеви И-ИЛИ
Сматра се да су услови написани у различитим ћелијама, али у истом реду, међусобно повезани логичким оператором И (И):
Оне. филтер банане за мене у трећем кварталу, тачно у Москви и у исто време из Ауцхана.
Ако треба да повежете услове са логичким оператором OR (ИЛИ), онда их само треба унети у различите редове. На пример, ако треба да пронађемо све поруџбине менаџера Волине за московске брескве и све поруџбине за лук у трећем тромесечју у Самари, онда се то може навести у низу услова на следећи начин:
Ако треба да наметнете два или више услова за једну колону, онда можете једноставно да дуплирате заглавље колоне у опсегу критеријума и унесете други, трећи итд. испод њега. услови. Тако, на пример, можете да изаберете све трансакције од марта до маја:
Генерално, након „завршетка са датотеком“, напредни филтер испада сасвим пристојан алат, на неким местима ништа лошији од класичног аутофилтера.
- Суперфилтер на макроима
- Шта су макрои, где и како уметнути макро код у Висуал Басиц
- Паметне табеле у Мицрософт Екцел-у