Как да използвате функцията ФИЛТЪР на Excel

Office 365 носи някои страхотни функции като XLOOKUP, SORT и FILTER.

При филтриране на данни в Excel, в света преди Office 365, ние разчитахме предимно на вградени филтри на Excel или най-много напреднали филтри или сложни формули SUMPRODUCT.Това често е сложно решение, ако трябва да филтрирате част от набора от данни.

Но с новата функция FILTER вече е много лесно бързо да филтрирате части от набор от данни въз основа на условия.

В този урок ще ви покажа колко страхотна е новата функция FILTER и някои полезни неща, които можете да правите с нея.

Но преди да вляза в примера, нека да разгледаме набързо синтаксиса на функцията FILTER.

Ако искате да получите тези нови функции в Excel, можетеНадстройте до Office 365(Присъединете се към вътрешната програма за достъп до всички функции/формули)

Функция за филтър на Excel – Синтаксис

Следва синтаксисът на функцията FILTER:

=ФИЛТЪР(масив,включи,[ако_празен])

• масив - това е диапазонът от клетки, където имате данните и искате да филтрирате някои данни от тях
• include - Това е условието, което казва на функцията кой запис да филтрира
• [ако_празно] – Това е незадължителен параметър, където можете да посочите какво да върне, ако функцията FILTER не намери резултати.По подразбиране (когато не е посочено) връща #CALC!грешка

Сега нека разгледаме някои невероятни примери за филтърни функции и какво може да направи, което преди беше много сложно без него.

Пример 1: Филтриране на данни въз основа на едно условие (регион)

Да предположим, че имате набор от данни като този по-долу и искате да филтрирате само всички записи в Съединените щати.

Ето формулата FILTER, която прави това:

=FILTER($A$2:$C$11,$B$2:$B$11="САЩ")

Горната формула приема набора от данни като масив и условието е $B$2:$B$11=”US”

Това условие ще накара функцията FILTER да провери всяка клетка в колона B (клетки с диапазона) и ще филтрира само онези записи, които отговарят на това условие.

Също така в този пример поставям оригиналните и филтрирани данни на един и същ лист, но можете също да ги поставите в отделни листове или дори работни книги.

Функцията за филтриране връща динамичен масив от резултати (което означава, че вместо да връща стойност, тя връща масив, който прелива в други клетки).

За това трябва да имате област, където резултатът е празен.Вече има нещо във всяка клетка в диапазона (E2:G5 в този пример) и функцията ще ви даде грешка #SPILL.

Освен това, тъй като това е динамичен масив, не можете да промените част от резултата.Можете да изтриете целия диапазон с резултата или клетка E2 (където е въведена формулата).И двете ще изтрият целия масив с резултати.Но не можете да промените нито една клетка (или да я изтриете).

Във формулата по-горе аз кодирах твърдо стойността на диапазона, но можете също да я поставите в клетка и да препоръчате тази клетка със стойността на диапазона.

Например в примера по-долу имам стойността на диапазона в клетка I2 и след това я препращам във формулата:

=FILTER($A$2:$C$11,$B$2:$B$11=I1)

Това прави формулата още по-полезна, сега просто трябва да промените стойността на диапазона в клетка I2 и филтърът ще се промени автоматично.

Можете също да имате падащо меню в клетка I2, където можете просто да направите избор и незабавно ще актуализира филтрираните данни.

Пример 2: Филтриране на данни въз основа на един критерий (по-голям или по-малък от)

Можете също да използвате оператори за сравнение във функциите за филтриране и да извлечете всички записи, по-големи или по-малки от определена стойност.

Например, да предположим, че имате набора от данни, показан по-долу, и искате да филтрирате всички записи с продажби над 10000 XNUMX.

Следната формула може да направи това:

=FILTER($A$2:$C$11,($C$2:$C$11>10000))

Аргументът на масива се отнася до целия набор от данни, в този случай условието ($C$2:$C$11>10000).

Формулата проверява всеки запис за стойността в колона C.Ако стойността е по-голяма от 10000, тя се филтрира, в противен случай се игнорира.

Ако искате да получите всички записи по-малко от 10000, можете да използвате следната формула:

=FILTER($A$2:$C$11,($C$2:$C$11<10000))

Можете също да станете по-креативни с формулата FILTER.Например, ако искате да филтрирате първите три записа въз основа на продажбите, можете да използвате следната формула:

=FILTER($A$2:$C$11,($C$2:$C$11>=LARGE(C2:C11,3)))

Горната формула използва функцията LARGE, за да получи третата по големина стойност в набора от данни.След това използвайте тази стойност в условие на функция FILTER, за да получите всички записи с продажби, по-големи или равни на третата най-голяма стойност.

Пример 3: Филтриране на данни с помощта на множество условия (И)

Да предположим, че имате следния набор от данни и искате да филтрирате всички записи в Съединените щати със стойност на продажбите, по-голяма от 10000 XNUMX.

Това е условие И, трябва да проверите две неща - регионът трябва да е в САЩ и продажбите трябва да са над 10000 XNUMX.Ако е изпълнено само едно условие, резултатите не трябва да се филтрират.

Ето формулата за филтриране, която ще филтрира записи с САЩ като регион и с повече от 10000 XNUMX продажби:

=FILTER($A$2:$C$11,($B$2:$B$11="US")*($C$2:$C$11>10000))

請注意，標準（稱為包含參數）是 ($B$2:$B$11=”US”)*($C$2:$C$11>10000)

Тъй като използвам две условия и трябва и двете да са верни, използвах оператора за умножение, за да комбинирам двете условия.Това връща масив от 0 и 1, където 1 се връща само ако са изпълнени и двете условия.

Ако няма съвпадащи записи, функцията ще върне #CALC!грешка.

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

=FILTER($A$2:$C$11,($B$2:$B$11="USA")*($C$2:$C$11>10000),"Nothing Found")

Тук използвах "not found" като трети параметър, който се използва, когато не е намерен съвпадащ запис.

Пример 4: Филтриране на данни с помощта на множество критерии (ИЛИ)

Можете също да промените параметъра "съдържа" във функцията FILTER, за да проверите за условия ИЛИ (където всяко дадено условие може да е вярно).

Например, да предположим, че имате набора от данни, показан по-долу, и искате да филтрирате записи, където държавата е Съединените щати или Канада.

Ето формулата, за да направите това:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+($B$2:$B$11="Canada"))

Имайте предвид, че в горната формула просто добавям двете условия с помощта на оператора за добавяне.Тъй като всяко от тези условия връща масив от TRUE и FALSE, мога да добавя комбиниран масив, който ще бъде TRUE, ако едно от условията е изпълнено.

Друг пример може да бъде, когато искате да филтрирате всички записи, където държавата е Съединените щати или стойността на продажбите е над 10000 XNUMX.

Следната формула ще направи това:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+(C2:C11>10000))

ЗАБЕЛЕЖКА: Когато използвате условието И във функцията ФИЛТЪР, използвайте оператора за умножение (*), а когато използвате условието ИЛИ, използвайте оператора за събиране (+).

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

Можете да използвате формули във функцията FILTER за филтриране и извличане на записи със стойности над или под средните.

Например, да приемем, че имате набора от данни, показан по-долу, и искате да филтрирате всички записи със стойност на продажбите над средната.

Можете да направите това със следната формула:

=FILTER($A$2:$C$11,C2:C11>AVERAGE(C2:C11))

Отново, за под средното можете да използвате следната формула:

=FILTER($A$2:$C$11,C2:C11<AVERAGE(C2:C11))

Пример 6: Филтрирайте само четни записи (или нечетни записи)

Ако трябва бързо да филтрирате и извлечете всички записи в четни или нечетни редове, можете да използвате функцията FILTER, за да го направите.

За да направите това, трябва да проверите номера на реда във функцията FILTER и да филтрирате само номерата на редове, които отговарят на условието за номер на ред.

Да предположим, че имате набор от данни като по-долу и аз искам само да извлека дори записи от този набор от данни.

Ето формулата, за да направите това:

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=0)

Горната формула използва функцията MOD за проверка на номера на реда (предоставен от функцията ROW) на всеки запис.

Формулата MOD(ROW(A2:A11)-1,2)=0 връща TRUE, когато номерът на реда е четен и FALSE, когато е нечетен.Обърнете внимание, че извадих 2 от частта ROW(A11:A1), защото първият запис е във втория ред, което коригира номерата на редовете, за да третира втория ред като първи запис.

По същия начин можете да филтрирате всички нечетни записи със следната формула:

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=1)

Пример 7: Сортиране на филтрирани данни с помощта на формула

Използването на функцията FILTER с други функции ни позволява да направим повече.

Например, ако използвате функцията FILTER за филтриране на набор от данни, можете да използвате функцията SORT, за да получите сортирани резултати.

Да предположим, че имате набор от данни, както е показано по-долу, и искате да филтрирате всички записи с продажби над 10000 XNUMX.Можете да използвате функцията SORT с тази функция, за да гарантирате, че получените данни са сортирани според продажбите.

Следната формула ще направи това:

=SORT(FILTER($A$2:$C$11,($C$2:$C$11>10000)),3,-1)

Горната функция използва функцията FILTER, за да получи данните в колона C с продажби, по-големи от 10000 XNUMX.След това използвайте масива, върнат от функцията FILTER във функцията SORT, за да сортирате данните според продажбите.

Вторият параметър във функцията SORT е 3, който трябва да сортира според третата колона.Четвъртият параметър е -1, който сортира данните в низходящ ред.

Това са 7 примера за използване на функцията FILTER в Excel.

Надявам се, че сте намерили този урок полезен.