ФУНКЦИЯ „FILTER“ В ЕКСЕЛ
Налагало ли ви се е да прилагате функция за търсене в Ексел, като например VLOOKUP или XLOOKUP, които връщат само първото намерено съвпадение? А какво ще кажете за функция, която връща всички намерени резултати?
Функцията се нарича FILTER. Подобно на филтрирането, тази функция ще остави видими всички редове, които отговарят на посочения от вас критерий. Тя работи с динамичен масив от данни и е част от Ексел офис 365.
1. Синтаксис
Нека ви запозная със синтаксиса ѝ.
- Array областта, в която се търси и от която ще се изведе резултат.
- Include „проверката“ дали в дадена колоната има равна стойност на критерия.
- If-empty съдържание, което да изпише при ненамерено съвпадение. Ако се пропусне връща грешката #CALC.
Пример 1:
Информация, с която разполагаме (1): региони, търговци, продукти, количества и цена. Желаем да извлечем всички редове със запис София. София е и критерия, изведен в клетка H2 (2). Резултатът от функцията искам да получа в клетка К2 (3).
Решение:
- За Array маркирам, целите А:Е колони.
- За втори аргумент Include селектирам колоната, в която ще се търси А:А и проверявам има ли равна стойност на критерия записан в H2.
- В If-empty записвам „Not found“, за резултат при ненамерено съвпадение.
2. Грешка #SPILL
Функциите, които връщат масиви от данни, могат да изпишат и грешка #SPILL тогава, когато няма необходимото количество празни клетки за резултата. Хубавото в случая е, че когато селектирате клетката с функция, се показва размера на областта, която трябва да се подсигури.
3. Работа с повече от 1 критерий
Подобно на стандартния филтър и тук можем да използваме логическите функции AND и OR при търсене.
- AND означава всички посочени от вас проверки да бъдат изпълнени едновременно, в рамките на един и същ ред и се представят със знака за умножение „*“.
- Докато OR ще изведе резултат, когато проверките са изпълнени дори и в различни редове и е представен със знака за събиране „+“.
Пример 2:
Необходимо е да се намерят продажбите на Иван Димитров (първи критерий) от град София (втори критерий). Тук имаме 2 критерия, които трябва да бъдат изпълнени едновременно. Желаем да извлечем всички редове със запис Иван Димитров от София. Критериите са изведени в клетка H2 и I2 (2). Резултатът от функцията искам да получа в клетка К2 (3).
Решение:
- За Array маркирам, целите А:Е колони.
- В Include искам всички проверки да бъдат изпълнени едновеременно. За целта ги отделям в скоби, между които поставям знака за умножение „*“.
- Проверявам в А колона има ли намерена стойност София (А:А=H2) И в колона B има ли търговец на име Иван Димитров (B:B=I2).
- За последен аргумент посочвам „Not found“.
Естествено можете да продължите с натрупването на условия, използвайки този принцип. Умножението означава AND – критериите да бъдат изпълнени в рамките на един и същ ред.
Пример 3:
Необходимо е да се намерят продажбите за град София или Бургас. Тук имаме работа с 2 критерия, които трябва да бъдат изпълнени дори да се намират в различни редове. Желаем да извлечем всички редове със запис София или Бургас. Критериите са изведени в клетка H2 и H3 (2). Резултатът от функцията искам да получа в клетка К2 (3).
Решение:
- За Array селектираме А:Е колони.
- В Include и двата критерия, ще са описани в скоби, между които ще постваим оператора за сбор „+“.
- Проверявам има ли в А колона стойност равна на H2 (А:А=H2) ИЛИ отново в А колона стойност равна на H3 (A:A=H3).
- За последен аргумент If_empty поставям отново „Not found“.
4. Заключение
Функцията Filter за масиви от данни има изключително лесен синтаксис. Аз бих предпочела да я използвам пред стандартен филтър, защото е динамична и лесно мога да сменя критериите.
Следваща причина да предпочета функцията е, че тя лесно може да бъде вложена в други функции.
В допълнение, е много по-мощен инструмент от VLOOKUP, защото връща всички намерени съвпадения.
По-долу във видеото демонстрирам горе описаните операции.