Как да филтрирам само датите, срещу които има стойност?

Наскоро ми попадна един типичен въпрос за филтриране на данни в Excel: „Как да взема само редовете от една таблица, в които има стойност в определена колона?“. Въпросът е илюстриран на картинката по-долу.

В ляво е изходната таблица, която в случая съдържа две колони – date (дата) и amount (сума). Целта е в отделна таблица да се вземат само тези редове от таблицата, за които има сума в колоната amount.

Филтрирането на данни е сред най-често използваните функции на Excel. Но повечето потребители не познават всички възможности за филтриране на данни в Excel. По-долу ще покажа три възможни решения  с техните плюсове и минуси.

1. Обикновен филтър

Обикновено хората веднага ще се досетят за това решение. То е най-простото и най-познато за филтриране на данни в Excel. За да вземем само редовете със суми първо слагаме филтър на таблицата. Става като кликнем някъде в нея и отидем на Data > Sort & Filter и натиснем бутона с фунията:

Още по-лесно става с клавишната комбинация SHIFT + CTRL + L.

След като имаме филтъра, можем да филтрираме колоната amount като махнем отметката пред (blanks).

В случая това е лесно, но понякога в колоната има толкова много стойности, че за да стигнем до (blanks) трябва да скролваме много надолу. Затова ще покажа една хитрост, която е от полза в такъв случай. В реда за търсене над стойностите пишем една скоба. Това автоматично извежда само стойностите, в които има скоба. И тъй като те са или числа или нищо – виждаме само (blanks).

Следващата стъпка е да махнем отметката от (Select All Search Results) и да сложим такава на Add current selection to filter

Когато дадем ОК – резултатът е точно каквото ни трябва. Остава само да го копираме и поставим, където желаем

Предимства на обикновения филтър:

1. Бързо, лесно и просто решение за филтриране на данни в Excel
2. Познато на повечето потребители
3. Върши работа в голямата част от случаите

Недостатъци на обикновения филтър:

1. Много ръчна работа и цъкане по менюта. Особено, ако трябва да въведем по-сложен критерий, който обхваща няколко колони
2. Резултатите не се обновяват при промяна на данните. Трябва отново да направим филтъра и да копираме новите данни
3. Понякога таблицата остава с приложените филтри, което може да заблуди някой, че няма други стойности

2. Филтър за напреднали (Advanced filter)

Advanced filter e доста стара функционалност за филтриране на данни в Excel, но малцина потребители знаят за нея и я използват. За да я ползваме е необходима подготовка – трябва да копираме някъде имената на колоните. Най-удбоно е да е над таблицата, както е показано на картинката. Виждате също така, че е въведен и критерий в колоната amount – „>0”.

След като сме свършили тази предварителна работа отиваме на Data > Sort & Filter > Advanced (фунията със зъбно колело).

Това извежда меню, в което Ексел ни предлага да изберем какво ще правим (Action). Дали само ще филтрираме таблицата или искаме това, което сме филтрирали да го копираме и поставим на друго място. В случая желаем второто и затова избираме  Copy to another location.

След това задаваме параметрите:

List range: това е таблицата, която искаме да филтрираме. В нашия случай – А4:В27

Criteria range: това е областта, в която са зададени критериите за филтриране. В случая А1:С2

Copy to: там, където искаме да поставим филтрираните данни. Достатъчна е една клетка и в случая това е Е4

Когато дадем ОК – получаваме точно това, което искаме. Една допълнителна таблица само с дати и стойности

Предимства на напредналия филтър:

1. С едно действие правим  филтриране на данни в Excel и ги копираме на друго място
2. При това забележете на картинката по-горе – таблицата с данните остава нефилтрирана
3. Позволява залагане на по-сложни критерии, които е трудно да се постигнат с обикновен филтър
4. Не на последно място критериите, заложени във филтъра се виждат над таблицата с данните и е ясно коя колона как е филтрирана

Недостатъци на напредналия филтър:

1. Изисква предварителна подготовка
2. Залагането на филтри не е толкова интуитивно както при обикновения филтър. Малко е странна логиката на работа и често изисква много опити „проба-грешка“
3. Филтрираните стойности могат да бъдат поставени само на текущия работен лист. Ако искаме да ги „закараме“ на друг лист трябва допълнително да ги копираме и пейстнем там
4. При промяна на стойностите филтърът трябва да бъде приложен отново

3. Функцията FILTER

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

Резултатът е точно таблицата, която ни трябва:

Бързо, лесно и прецизно филтриране на данни в Excel, което ще се променя динамично с промени в данните

Предимства на функцията FILTER:

1. Изисква еднократно действие. Без копиране и пействане
2. Резултатът се променя автоматично при промяна на данните
3. Резултатната таблица може да бъде във всеки един шийт от файла, дори в друг файл

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

1. Налична е само в Ексел за Офис 365 и във десктоп версиите от 2021 нагоре
2. Може да върне грешка SPIL (повече за това – прочетете тук)

Заключение

Това бяха три начина да филтрираме данни по някакъв признак. Всеки със своите предимства и недостатъци. Разбира се може да се използат и други инструменти – Power Query, VBA, в определени случаи – дори пивот таблица. С този казус обаче нагледно се виждат ползата и елегантността на динамичните функции като FILTER.