Как да филтрирате данни в Excel

Наскоро написах статия за това как да използвам функции за обобщение в Excel за лесно обобщаване на големи количества данни, но тази статия взе предвид всички данни в работния лист. Ами ако искате да разгледате само подмножество от данни и да обобщите подмножеството от данни?

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

Съдържание

• Създайте прости филтри в Excel
• Създайте разширени филтри в Excel
• Обобщаване на филтрирани данни

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

Създайте прости филтри в Excel

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

По-горе имам някои фалшиви данни и искам да създам филтър в колоната Град . В Excel това е наистина лесно да се направи. Продължете и щракнете върху раздела Данни в лентата и след това щракнете върху бутона Филтър . Не е нужно да избирате данните в листа или да щраквате в първия ред.

Когато щракнете върху Филтър, всяка колона в първия ред автоматично ще има добавен малък падащ бутон вдясно.

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

В горната част можете бързо да сортирате всички редове по стойностите в колоната Град. Имайте предвид, че когато сортирате данните, това ще премести целия ред, а не само стойностите в колоната Град. Това ще гарантира, че вашите данни остават непокътнати точно както са били преди.

Освен това е добра идея да добавите колона най-отпред, наречена ID, и да я номерирате от един до колкото редове имате в работния си лист. По този начин винаги можете да сортирате по колоната ID и да върнете данните си в същия ред, в който са били първоначално, ако това е важно за вас.

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

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

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

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

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

Сега имам само 5 реда данни: семейства само от Ню Орлиънс и с 3 или повече членове. Достатъчно лесно? Обърнете внимание, че можете лесно да изчистите филтър на колона, като щракнете върху падащото меню и след това върху връзката Изчистване на филтъра от „Име на колона“ .

Така че това е всичко за простите филтри в Excel. Те са много лесни за използване и резултатите са доста ясни. Сега нека да разгледаме сложните филтри с помощта на диалоговия прозорец Разширени филтри.

Създайте разширени филтри в Excel

Ако искате да създадете по-разширени филтри, трябва да използвате диалоговия прозорец Разширен филтър. Например, да приемем, че искам да видя всички семейства, които живеят в Ню Орлиънс с повече от 2 членове в семейството ИЛИ всички семейства в Кларксвил с повече от 3 членове в семейството И само тези с имейл адрес, завършващ .EDU . Сега не можете да направите това с обикновен филтър.

За да направим това, трябва да настроим Excel листа малко по-различно. Продължете и вмъкнете няколко реда над вашия набор от данни и копирайте етикетите на заглавията точно в първия ред, както е показано по-долу.

Ето как работят разширените филтри. Първо трябва да въведете критериите си в колоните в горната част и след това да щракнете върху бутона Разширени под Сортиране и филтриране в раздела Данни .

Какво точно можем да напишем в тези клетки? Добре, нека започнем с нашия пример. Искаме да видим данни само от Ню Орлиънс или Кларксвил, така че нека ги напишем в клетки E2 и E3.

Когато въвеждате стойности на различни редове, това означава ИЛИ. Сега искаме семейства от Ню Орлиънс с повече от двама членове и семейства от Кларксвил с повече от 3 члена. За да направите това, въведете >2 в C2 и >3 в C3.

Тъй като >2 и Ню Орлиънс са на един ред, това ще бъде оператор И. Същото важи и за ред 3 по-горе. И накрая, искаме само семействата с имейл адрес, завършващ .EDU. За да направите това, просто въведете *.edu в D2 и D3. Символът * означава произволен брой знаци.

След като направите това, щракнете където и да е във вашия набор от данни и след това щракнете върху бутона Разширени . Полето List Rang e автоматично ще разбере вашия набор от данни, тъй като сте щракнали върху него, преди да щракнете върху бутона Разширени. Сега щракнете върху малкия малък бутон вдясно от бутона за диапазон на критериите .

Изберете всичко от A1 до E3 и след това щракнете отново върху същия бутон, за да се върнете към диалоговия прозорец Разширен филтър. Щракнете върху OK и вашите данни вече трябва да бъдат филтрирани!

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

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

Обобщаване на филтрирани данни

Сега да кажем, че искам да обобщя броя на членовете на семейството си в моите филтрирани данни, как бих могъл да го направя? Е, нека изчистим нашия филтър, като щракнете върху бутона Изчисти в лентата. Не се притеснявайте, много е лесно да приложите разширения филтър отново, като просто щракнете върху бутона Разширени и отново щракнете върху OK.

В долната част на нашия набор от данни нека добавим клетка, наречена Total , и след това да добавим функция за сумиране, за да сумираме общия брой членове на семейството. В моя пример току-що написах =SUM(C7:C31) .

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

Опа! Вместо да покажа правилното число, 11, все още виждам, че общото е 78! Защо така? Е, функцията SUM не игнорира скритите редове, така че все още прави изчислението, използвайки всички редове. За щастие има няколко функции, които можете да използвате, за да игнорирате скрити редове.

Първият е МЕЖДИННА СУМА . Преди да използваме някоя от тези специални функции, ще искате да изчистите филтъра си и след това да въведете функцията.

След като филтърът е изчистен, продължете и въведете =SUBTOTAL( и трябва да видите падащо меню с куп опции. Използвайки тази функция, първо избирате типа функция за сумиране, която искате да използвате, като използвате число.

В нашия пример искам да използвам SUM , така че бих написал числото 9 или просто щракнете върху него от падащото меню. След това въведете запетая и изберете диапазона от клетки.

Когато натиснете Enter, трябва да видите, че стойността 78 е същата като предишната. Но ако сега приложите отново филтъра, ще видим 11!

Отлично! Точно това искаме. Сега можете да коригирате филтрите си и стойността винаги ще отразява само редовете, които се показват в момента.

Втората функция, която работи почти по същия начин като функцията SUBTOTAL, е АГРЕГАТ . Единствената разлика е, че има друг параметър във функцията AGGREGATE, където трябва да посочите, че искате да игнорирате скритите редове.

Първият параметър е функцията за сумиране, която искате да използвате, и както при SUBTOTAL, 9 представлява функцията SUM. Втората опция е, когато трябва да въведете 5, за да игнорирате скритите редове. Последният параметър е същият и е диапазонът от клетки.

Можете също така да прочетете статията ми за функциите за обобщаване, за да научите как да използвате функцията AGGREGATE  и други функции като MODE, MEDIAN, AVERAGE и др. по-подробно.

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