Функција Екцел филтера - објашњено са примерима + видео

Гледајте видео - Примери функција Екцел ФИЛТЕРА

Оффице 365 доноси неке сјајне функције - као што су КСЛООКУП, СОРТ и ФИЛТЕР.

Што се тиче филтрирања података у Екцелу, у свету пре Оффице 365, највише смо зависили од уграђеног филтера Екцел или максимално од Напредног филтера или сложених формула СУМПРОДУЦТ. У случају да сте морали да филтрирате део скупа података, то је обично било сложено решење (нешто што сам овде описао).

Али са новом функцијом ФИЛТЕР, сада је заиста лако брзо филтрирати део скупа података на основу услова.

И у овом водичу ћу вам показати колико је нова функција ФИЛТЕР сјајна и неке корисне ствари које можете учинити са овим.

Али пре него што пређем на примере, хајде да брзо научимо о синтакси функције ФИЛТЕР.

У случају да желите да добијете ове нове функције у Екцелу, можете надоградите на Оффице 365 (придружите се инсајдерском програму да бисте добили приступ свим функцијама/формулама)

Функција Екцел филтера - Синтакса

Испод је синтакса функције ФИЛТЕР:

= ФИЛТЕР (низ, укључи, [иф_емпти])

• арраи - ово је опсег ћелија у којима имате податке и желите да филтрирате неке податке из њих
• укључују - ово је услов који говори функцији које записе треба филтрирати
• [иф_емпти] - ово је опционални аргумент у којем можете навести шта да вратите у случају да функција ФИЛТЕР не пронађе резултате. Подразумевано (када није наведено), враћа #ЦАЛЦ! грешка

Хајде сада да погледамо неке невероватне примере функција филтера и ствари које може да уради, а које су некада биле прилично сложене у његовом одсуству.

Кликните овде да преузмете датотеку Пример и пратите је

Пример 1: Филтрирање података на основу једног критеријума (регион)

Претпоставимо да имате скуп података као што је приказано испод и желите да филтрирате све записе само за САД.

Испод је формула ФИЛТЕР која ће то учинити:

= ФИЛТЕР ($ А $ 2: $ Ц $ 11, $ Б $ 2: $ Б $ 11 = "УС")

Горња формула користи скуп података као низ и услов је $ Б $ 2: $ Б $ 11 = ”УС”

Овај услов би учинио да функција ФИЛТЕР провери сваку ћелију у колони Б (ону која има регион) и да се филтрирају само они записи који одговарају овом критеријуму.

Такође, у овом примеру, имам оригиналне податке и филтриране податке на истом листу, али можете их имати и у посебним листовима или чак радним свескама.

Функција филтера враћа резултат који је динамички низ (што значи да уместо да врати једну вредност, враћа низ који се прелива у друге ћелије).

Да би ово функционисало, морате имати област у којој би резултат био празан. У било којој од ћелија у овој области (Е2: Г5 у овом примеру) већ постоји нешто у себи, функција ће вам дати грешку #СПИЛЛ.

Такође, пошто је ово динамички низ, не можете променити део резултата. Можете избрисати цео опсег који има резултат или ћелију Е2 (где је унета формула). Обоје ће избрисати цео резултујући низ. Али не можете променити ниједну ћелију (или је избрисати).

У горњој формули, ја сам тешко кодирао вредност региона, али је можете поставити и у ћелију, а затим референцирати ту ћелију која има вредност региона.

На пример, у доњем примеру, ја имам вредност региона у ћелији И2 и то се онда позива у формули:

= ФИЛТЕР ($ А $ 2: $ Ц $ 11, $ Б $ 2: $ Б $ 11 = И1)

Ово чини формулу још кориснијом и сада можете једноставно променити вредност региона у ћелији И2 и филтер би се аутоматски променио.

Такође можете имати падајући мени у ћелији И2 где можете једноставно извршити одабир и он ће тренутно ажурирати филтриране податке.

Пример 2: Филтрирање података на основу једног критеријума (више или мање од)

Такође можете користити упоредне операторе у оквиру функције филтрирања и издвојити све записе који су више или мање од одређене вредности.

На пример, претпоставимо да имате скуп података као што је приказано у наставку и желите да филтрирате све записе где је вредност продаје већа од 10000.

Формула испод може то учинити:

= ФИЛТЕР ($ А $ 2: $ Ц $ 11, ($ Ц $ 2: $ Ц $ 11> 10000))

Аргумент низа односи се на цео скуп података, а услов је у овом случају ($ Ц $ 2: $ Ц $ 11> 10000).

Формула проверава сваки запис за вредност у колони Ц. Ако је вредност већа од 10000, филтрира се, иначе се занемарује.

У случају да желите да сви записи буду мањи од 10000, можете користити доњу формулу:

= ФИЛТЕР ($ А $ 2: $ Ц $ 11, ($ Ц $ 2: $ Ц $ 11 <10000))

Такође можете постати креативнији помоћу формуле ФИЛТЕР. На пример, ако желите да филтрирате прва три записа на основу вредности продаје, можете користити доњу формулу:

= ФИЛТЕР ($ А $ 2: $ Ц $ 11, ($ Ц $ 2: $ Ц $ 11> = ВЕЛИКИ (Ц2: Ц11,3)))

Горња формула користи функцију ЛАРГЕ да добије трећу највећу вредност у скупу података. Ова вредност се затим користи у критеријумима функције ФИЛТЕР за добијање свих записа у којима је вредност продаје већа или једнака трећој највећој вредности.

Кликните овде да преузмете датотеку Пример и пратите је

Пример 3: Филтрирање података са више критеријума (И)

Претпоставимо да имате низ података у наставку и желите да филтрирате све записе за САД у којима је вредност продаје већа од 10000.

Ово је И услов у коме морате да проверите две ствари - регион треба у САД и продаја мора бити већа од 10000. Ако је испуњен само један услов, резултати се не би требали филтрирати.

Испод је формула ФИЛТЕР која ће филтрирати записе са САД -ом као регионом и продајом више од 10000:

= ФИЛТЕР ($ А $ 2: $ Ц $ 11, ($ Б $ 2: $ Б $ 11 = "УС")*($ Ц $ 2: $ Ц $ 11> 10000))

Имајте на уму да је критеријум (који се назива аргумент укључивања) ($ Б $ 2: $ Б $ 11 = ”УС”)*($ Ц $ 2: $ Ц $ 11> 10000)

Пошто користим два услова и потребно ми је да оба буду тачна, употребио сам оператор множења да бих спојио ова два критеријума. Ово враћа низ 0 и 1, где се 1 враћа само када су испуњена оба услова.

У случају да нема записа који испуњавају критеријуме, функција би вратила #ЦАЛЦ! грешка.

А у случају да желите да вратите нешто што значи (уместо грешке), можете користити формулу као што је приказано испод:

= ФИЛТЕР ($ А $ 2: $ Ц $ 11, ($ Б $ 2: $ Б $ 11 = "УСА")*($ Ц $ 2: $ Ц $ 11> 10000), "Ништа није пронађено")

Овде сам користио „Није пронађено“ као трећи аргумент, који се користи када се не нађу записи који одговарају критеријумима.

Пример 4: Филтрирање података са више критеријума (ИЛИ)

Такође можете изменити аргумент „укључи“ у функцији ФИЛТЕР да бисте проверили критеријуме ИЛИ (где било који од наведених услова може бити тачан).

На пример, претпоставимо да имате скуп података као што је приказано у наставку и желите да филтрирате записе у којима је земља САД или Канада.

Испод је формула која ће то учинити:

= ФИЛТЕР ($ А $ 2: $ Ц $ 11, ($ Б $ 2: $ Б $ 11 = "УС")+($ Б $ 2: $ Б $ 11 = "Канада"))

Имајте на уму да сам у горњој формули једноставно додао два услова користећи оператор сабирања. Пошто сваки од ових услова враћа низ ТРУЕ и ФАЛСЕс, могу да додам да добијем комбиновани низ где је ТРУЕ ако је неки од услова испуњен.

Други пример може бити када желите да филтрирате све записе где је или држава САД или је вредност продаје већа од 10000.

Формула испод ће то учинити:

= ФИЛТЕР ($ А $ 2: $ Ц $ 11, ($ Б $ 2: $ Б $ 11 = "УС")+(Ц2: Ц11> 10000))

Напомена: Када користите критеријуме АНД у функцији ФИЛТЕР, користите оператор множења (*), а када користите критеријуме ИЛИ, користите оператор сабирања (+).

Пример 5: Филтрирање података за записе изнад/испод просека

Можете користити формуле унутар функције ФИЛТЕР за филтрирање и издвајање записа где је вредност изнад или испод просека.

На пример, претпоставимо да имате скуп података као што је приказано у наставку и желите да филтрирате све записе где је вредност продаје изнад просека.

То можете учинити помоћу следеће формуле:

= ФИЛТЕР ($ А $ 2: $ Ц $ 11, Ц2: Ц11> СРЕДЊИ (Ц2: Ц11))

Слично, за испод просека можете користити доњу формулу:

= ФИЛТЕР ($ А $ 2: $ Ц $ 11, Ц2: Ц11<>

Кликните овде да преузмете датотеку Пример и пратите је

Пример 6: Филтрирање само ЕВЕНСКИХ записа (или ОДД бројева)

У случају да требате брзо филтрирати и издвојити све записе из редова парних бројева или редова непарних бројева, то можете учинити помоћу функције ФИЛТЕР.

Да бисте то урадили, потребно је да проверите број реда у оквиру функције ФИЛТЕР и филтрирате само бројеве редова који задовољавају критеријуме за број редова.

Претпоставимо да имате скуп података као што је приказано испод, а ја само желим да извучем парне записе из овог скупа података.

Испод је формула која ће то учинити:

= ФИЛТЕР ($ А $ 2: $ Ц $ 11, МОД (РОВ (А2: А11) -1,2) = 0)

Горња формула користи функцију МОД за проверу броја реда сваког записа (који је дат функцијом РОВ).

Формула МОД (РОВ (А2: А11) -1,2) = 0 враћа ТРУЕ када је број реда паран и ФАЛСЕ када је непаран. Имајте на уму да сам одузео 1 од дела РОВ (А2: А11) јер се први запис налази у другом реду, и ово прилагођава број реда како би други ред узео у обзир као први запис.

Слично, можете филтрирати све записе са непарним бројевима користећи формулу испод:

= ФИЛТЕР ($ А $ 2: $ Ц $ 11, МОД (РОВ (А2: А11) -1,2) = 1)

Пример 7: Сортирајте филтриране податке помоћу формуле

Коришћење функције ФИЛТЕР са другим функцијама омогућава нам да урадимо много више.

На пример, ако филтрирате скуп података помоћу функције ФИЛТЕР, можете користити функцију СОРТ са њом да бисте добили резултат који је већ сортиран.

Претпоставимо да имате скуп података као што је приказано у наставку и желите да филтрирате све записе у којима је вредност продаје већа од 10000. Можете да користите функцију СОРТ са функцијом да бисте били сигурни да су добијени подаци сортирани на основу вредности продаје.

Формула испод ће то учинити:

= СОРТ (ФИЛТЕР ($ А $ 2: $ Ц $ 11, ($ Ц $ 2: $ Ц $ 11> 10000)), 3, -1)

Горња функција користи функцију ФИЛТЕР за добијање података где је вредност продаје у колони Ц већа од 10000. Овај низ који враћа функција ФИЛТЕР се затим користи унутар функције СОРТ за сортирање ових података на основу продајне вредности.

Други аргумент у функцији СОРТ је 3, који треба да се сортира на основу треће колоне. Четврти аргумент је -1 који треба сортирати ове податке по опадајућем редоследу.

Кликните овде за преузимање датотеке примера

Дакле, ово је 7 примера за коришћење функције ФИЛТЕР у Екцелу.

Надам се да вам је овај водич био користан!

Можда ће вам се допасти и следећи Екцел водичи:

1. Како филтрирати ћелије подебљаним обликовањем фонтова у Екцелу
2. Оквир за претрагу динамичког Екцел филтера
3. Како филтрирати податке у изведеној табели у програму Екцел