Функция за филтър на Excel - Обяснено с примери + видео

Гледайте видео - Примери за функция на филтъра на Excel

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

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

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

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

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

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

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

По -долу е синтаксисът на функцията FILTER:

= FILTER (масив, включва, [ако_празна])

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

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

Щракнете тук, за да изтеглите файла с пример и следвайте

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

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

По -долу е формулата FILTER, която ще направи това:

= ФИЛТЪР ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = "САЩ")

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

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

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

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

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

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

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

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

= ФИЛТЪР ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = I1)

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

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

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

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

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

Формулата по -долу може да направи това:

= ФИЛТЪР ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10000))

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

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

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

= ФИЛТЪР ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11 <10000))

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

= ФИЛТЪР ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> = ГОЛЯМ (C2: C11,3)))

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

Щракнете тук, за да изтеглите файла с пример и следвайте

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

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

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

По -долу е формулата FILTER, която ще филтрира записи с САЩ като регион и продажби над 10000:

= ФИЛТЪР ($ 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! грешка.

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

= ФИЛТЪР ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "САЩ")*($ C $ 2: $ C $ 11> 10000), "Нищо не е намерено")

Тук използвах „Not Found“ като трети аргумент, който се използва, когато не са намерени записи, които да отговарят на критериите.

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

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

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

По -долу е формулата, която ще направи това:

= FILTER ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US")+($ B $ 2: $ B $ 11 = "Канада"))

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

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

Формулата по -долу ще направи това:

= ФИЛТЪР ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US")+(C2: C11> 10000))

Забележка: Когато използвате критерии AND във функция FILTER, използвайте оператора за умножение (*) и когато използвате критериите OR, използвайте оператора за добавяне (+).

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

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

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

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

= ФИЛТЪР ($ A $ 2: $ C $ 11, C2: C11> СРЕДЕН (C2: C11))

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

= ФИЛТЪР ($ A $ 2: $ C $ 11, C2: C11<>

Щракнете тук, за да изтеглите файла с пример и следвайте

Пример 6: Филтриране само на ЗАПИСИ НА ЧЕТИЧКИ ЗАПИСИ (ИЛИ НЕЧЕТНИ ЗАПИСИ НА ЧИСЛА)

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

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

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

По -долу е формулата, която ще направи това:

= ФИЛТЪР ($ A $ 2: $ C $ 11, MOD (ROW (A2: A11) -1,2) = 0)

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

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

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

= ФИЛТЪР ($ A $ 2: $ C $ 11, MOD (ROW (A2: A11) -1,2) = 1)

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

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

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

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

Формулата по -долу ще направи това:

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

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

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

Щракнете тук, за да изтеглите примерния файл

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

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

Може да ви харесат и следните уроци по Excel:

1. Как да филтрирате клетки с удебелено форматиране на шрифтове в Excel
2. Поле за търсене на динамичен филтър на Excel
3. Как да филтрирате данни в обобщена таблица в Excel