Как да филтрираме данните в Excel
Наскоро написах статия за това как да използвам обобщени функции в Excel за лесно обобщаване на големи количества данни, но тази статия взе под внимание всички данни в работния лист. Какво ще стане, ако искате само да погледнете подмножество от данни и да обобщите подмножеството от данни?
В Excel можете да създавате филтри на колони, които да скриват редове, които не съвпадат с вашия филтър. В допълнение, можете също да използвате специални функции в Excel за обобщаване на данни, използвайки само филтрираните данни.
В тази статия ще ви преведа през стъпките за създаване на филтри в Excel, а също и чрез използване на вградени функции за обобщаване на филтрираните данни.
Създайте прости филтри в Excel
В Excel можете да създавате прости филтри и сложни филтри. Да започнем с прости филтри. Когато работите с филтри, винаги трябва да имате един ред в горната част, който се използва за етикети. Това не е изискване за този ред, но прави работата с филтри малко по-лесна.
По-горе имам фалшиви данни и искам да създам филтър на град колона. В Excel това наистина е лесно. Давай напред и кликни върху Данни в лентата и след това кликнете върху филтър бутон. Не е нужно да избирате данните на листа или да щраквате в първия ред.
Когато кликнете върху Филтър, всяка колона в първия ред автоматично ще има добавен малък падащ бутон вдясно.
Сега продължете напред и кликнете върху стрелката в падащото меню в графата Град. Ще видите няколко различни опции, които ще обясня по-долу.
В горната част можете бързо да сортирате всички редове по стойностите в графата Град. Обърнете внимание, че когато сортирате данните, той ще премести целия ред, а не само стойностите в графата Град. Това ще гарантира, че данните ви ще останат непокътнати точно както преди.
Също така е добра идея да добавите колона в най-предната част, наречена ID, и да я въведете от едно до много редове, които имате в работния лист. По този начин винаги можете да сортирате по колона ИД и да получите обратно данните си в същия ред, както първоначално, ако това е важно за вас.
Както виждате, всички данни в електронната таблица сега са сортирани въз основа на стойностите в графата Град. Досега не са скрити никакви редове. Сега нека разгледаме отметките в долната част на диалоговия прозорец на филтъра. В моя пример имам само три уникални стойности в графата Град и тези три се показват в списъка.
Отидох напред и не маркирах два града и оставих един да се провери. Сега имам само 8 реда данни, а останалите са скрити. Лесно можете да кажете, че търсите филтрирани данни, ако проверите номерата на редовете в най-лявата част. В зависимост от броя на скритите редове ще видите няколко допълнителни хоризонтални линии и цветът на номерата ще бъде син.
Сега нека да кажем, че искам да филтрирам втората колона, за да намалим още броя на резултатите. В колона В имам общия брой на членовете във всяко семейство и искам да видя резултатите само за семейства с повече от двама членове.
Продължете и кликнете върху стрелката на падащото меню в колона С и ще видите същите квадратчета за всяка уникална стойност в колоната. В този случай обаче искаме да кликнем върху Брой филтри и след това кликнете върху По-велик от. Както виждате, има и много други опции.
Ще се появи нов диалогов прозорец и тук можете да въведете стойността на филтъра. Можете също да добавите повече от един критерий с функция AND или OR. Може да се каже, че искате редове, където стойността е по-голяма от 2 и не е равна на 5, например.
Сега съм само на 5 реда данни: семейства само от Ню Орлиънс и с 3 или повече членове. Достатъчно лесно ли е? Обърнете внимание, че можете лесно да изчистите филтър в колона, като кликнете върху падащото меню и след това върху бутона Изчистване на филтъра от „Име на колоната“ връзка.
Така че това е за прости филтри в Excel. Те са много лесни за използване и резултатите са доста прав. Сега нека разгледаме сложните филтри, които използват напреднал диалогов прозорец с филтри.
Създайте разширени филтри в Excel
Ако искате да създадете по-разширени филтри, трябва да използвате напреднал диалогов прозорец за филтриране. Например, да кажем, че исках да видя всички семейства, които живеят в Ню Орлиънс с повече от 2 члена в семейството си ИЛИ всички семейства в Кларксвил с повече от 3 члена в семейството им И само тези с .EDU краен имейл адрес. Сега не можете да направите това с прост филтър.
За да направите това, трябва да настроим Excel лист малко по-различно. Продължете и вмъкнете няколко реда над набора от данни и копирайте етикетите на заглавията точно в първия ред, както е показано по-долу.
Ето как работят разширените филтри. Трябва първо да въведете критериите си в колоните в горната част и след това да кликнете върху напреднал бутон под Сортиране и филтриране на Данни раздел.
И така, какво точно можем да напишем в тези клетки? Добре, да започнем с нашия пример. Искаме само да видим данни от Ню Орлиънс или Кларксвил, така че нека да ги въведем в клетки Е2 и Е3.
Когато въвеждате стойности на различни редове, това означава OR. Сега искаме семейства в Ню Орлиънс с повече от двама членове и семейства Кларксвил с повече от 3 членове. За да направите това, въведете > 2 в С2 и > 3 в С3.
Тъй като> 2 и New Orleans са в един и същи ред, той ще бъде оператор AND. Същото важи и за ред 3 по-горе. И накрая, искаме само семействата с краен имейл адрес .EDU. За да направите това, просто въведете * .edu както в D2, така и в D3. Символът * означава произволен брой знаци.
След като направите това, кликнете някъде в набора от данни и след това кликнете върху напреднал бутон. Най- Списък RangПолето автоматично ще определи вашия набор от данни, откакто сте кликнали в него, преди да щракнете върху бутона Разширени. Сега кликнете върху малкия малък бутон вдясно на Обхват на критериите бутон.
Изберете всичко от A1 до E3 и след това отново кликнете върху същия бутон, за да се върнете в диалоговия прозорец Advanced Filter (Разширен филтър). Кликнете върху „OK“ и данните ви сега трябва да бъдат филтрирани!
Както виждате, сега имам само 3 резултата, които отговарят на всички тези критерии. Имайте предвид, че етикетите за обхвата на критериите трябва да съвпадат точно с етикетите за набора от данни, за да може това да работи.
Очевидно можете да създадете много по-сложни заявки, като използвате този метод, така че играйте с него, за да получите желаните резултати. И накрая, нека да поговорим за прилагането на функции за сумиране във филтрирани данни.
Обобщаване на филтрираните данни
Да речем, че искам да обобщя броя на членовете на семейството на филтрираните ми данни, как бих го направил? Е, нека да изчистим филтъра, като кликнем върху ясно на лентата. Не се притеснявайте, много е лесно да приложите разширения филтър отново, като просто кликнете върху бутона Разширени и отново щракнете върху OK.
В долната част на нашия набор от данни, нека добавим клетка, наречена Обща сума и след това добавете функция сума, за да обобщим общите членове на семейството. В моя пример току-що написах = SUM (С7: C31).
Така че, ако погледна всички семейства, имам общо 78 членове. Сега нека да продължим и да приложим нашия разширен филтър и да видим какво ще се случи.
Опа! Вместо да покаже правилния номер, 11, все още виждам, че общият брой е 78! Защо така? Е, функцията SUM не игнорира скритите редове, така че все още прави изчисленията с всички редове. За щастие, има няколко функции, които можете да използвате, за да игнорирате скрити редове.
Първото е SUBTOTAL. Преди да използваме някоя от тези специални функции, ще искате да изчистите филтъра си и след това да въведете функцията.
След като филтърът се изчисти, продължете и въведете = SUBTOTAL ( и трябва да се появи падащо меню с куп опции. Използвайки тази функция, първо избирате вида на функцията за сумиране, която искате да използвате, като използвате номер.
В нашия пример искам да използвам SUM, така че бих въвела номер 9 или просто щракнете върху него от падащото меню. След това въведете запетая и изберете диапазона от клетки.
Когато натиснете Enter, трябва да видите стойността на 78 е същата като преди. Въпреки това, ако сега приложите филтъра отново, ще видим 11!
Отличен! Точно това искаме. Сега можете да коригирате филтрите си и стойността винаги ще отразява само редовете, които се показват в момента.
Втората функция, която работи почти същата като функцията SUBTOTAL КОМПЛЕКСНО. Единствената разлика е, че има още един параметър в функцията AGGREGATE, където трябва да посочите, че искате да игнорирате скритите редове.
Първият параметър е функцията за сумиране, която искате да използвате и както при SUBTOTAL, 9 представлява функцията SUM. Втората опция е мястото, където трябва да въведете 5, за да игнорирате скритите редове. Последният параметър е един и същ и е диапазонът от клетки.
Можете също така да прочетете статията ми за обобщените функции, за да научите как да използвате функцията AGGREGATE и други функции като MODE, MEDIAN, AVERAGE и др..
Надяваме се, че тази статия ви дава добра отправна точка за създаване и използване на филтри в Excel. Ако имате някакви въпроси, можете да публикувате коментар. Наслади се!
