Наскоро написах статия за това как да използвам обобщени функции в Excel за лесно обобщаване на големи количества данни, но тази статия взе под внимание всички данни от работния лист. Какво ще стане, ако искате да погледнете само една подгрупа от данни и да обобщите подгрупата на данните?
В Excel можете да създавате филтри върху колони, които ще скрият редове, които не съответстват на вашия филтър. Освен това можете да използвате специални функции в Excel, за да обобщавате данни само с помощта на филтрираните данни.
В тази статия ще ви запозная със стъпките за създаване на филтри в Excel и ще използвам вградени функции, за да обобщим тези филтрирани данни.
В Excel можете да създавате прости филтри и сложни филтри. Нека започнем с прости филтри. Когато работите с филтри, винаги трябва да имате един ред в горната част, който се използва за етикети. Това не е изискване за този ред, но работата с филтрите е малко по-лесно.
Нагоре, имам някои фалшиви данни и искам да създам филтър на град колона. В Excel, това е много лесно да се направи. Отидете напред и кликнете върху Данни в лентата и след това кликнете върху филтър бутон. Не е нужно да избирате данните в листа или да кликнете в първия ред.
Когато кликнете върху "Филтриране", всяка колона в първия ред автоматично ще има малък падащ бутон вдясно.
Сега продължете напред и кликнете върху стрелката за падащото меню в графата Сити. Ще видите няколко различни опции, които ще обясня по-долу.
В горната част можете бързо да сортирате всички редове с стойностите в графата Сити. Имайте предвид, че когато сортирате данните, тя ще премести целия ред, а не само стойностите в графата "Град". Това ще гарантира, че данните ви остават непокътнати точно както преди.
Също така е добра идея да добавите колона в самия фронт, наречен ID, и да го номерирате от един до толкова много редове, които имате във вашия работен лист. По този начин винаги можете да сортирате графата за идентификационни данни и да получите данните си в същия ред, в който са били първоначално, ако това е важно за вас.
Както можете да видите, всички данни в електронната таблица вече се сортират на базата на стойностите в графата Сити. Досега няма скрити редове. Сега нека да разгледаме квадратчетата за отметки в долната част на филтърния диалогов прозорец. В моя пример имам само три уникални стойности в Града на града и тези три се показват в списъка.
Продължих и не проверих два града и оставих един проверен. Сега имам само 8 реда данни, а останалите са скрити. Можете лесно да разберете, че гледате филтрирани данни, ако проверите номерата на реда в най-лявата част. В зависимост колко редове са скрити, ще видите няколко допълнителни хоризонтални линии и цветът на числата ще бъде синьо.
Сега да кажем, че искам да филтрирам на втора колона, за да намаля още повече броя на резултатите. В колона В имам общия брой членове във всяко семейство и искам да виждам резултатите само за семейства с повече от двама членове.
Вървете напред и кликнете върху стрелката за падащо меню в колона C и ще видите същите квадратчета за отметка за всяка уникална стойност в графата. В този случай обаче искаме да кликнете върху Брой филтри и след това кликнете върху По-велик от, Както можете да видите, има и куп други опции.
Ще се появи нов диалогов прозорец и тук можете да въведете стойността на филтъра. Можете също така да добавите повече от един критерий с функция AND или OR. Бихте могли да кажете, че искате редове, където стойността е по-голяма от 2 и не е равна на 5, например.
Сега съм на 5 реда данни: семейства само от Ню Орлиънс и с 3 или повече членове. Доста лесно ли е? Обърнете внимание, че можете лесно да изчистите филтър в колона, като кликнете върху падащото меню и след това върху Изчистване на филтъра от "Име на колона" връзка.
Затова става въпрос за прости филтри в Excel. Те са много лесни за използване и резултатите са доста прав. Сега нека да разгледаме сложни филтри, използващи напреднал филтър.
Ако искате да създадете по-модерни филтри, трябва да използвате напреднал филтър. Да приемем например, че искам да видя всички семейства, които живеят в Ню Орлийнс с повече от двама членове в семейството си ИЛИ всички семейства в Кларксвил с повече от 3 членове в семейството си И само тези с .edu крайния имейл адрес. Сега не можете да направите това с обикновен филтър.
За да направите това, трябва да настроите листа на Excel малко по различен начин. Отидете напред и поставете няколко реда над вашия набор от данни и копирайте етикетите на заглавията точно в първия ред, както е показано по-долу.
Сега ето как функционират разширените филтри. Първо трябва да въведете критериите си в графите в горната част и след това върху напреднал бутон под Сортиране и филтриране на Данни раздел.
И така, какво точно можем да въведем в тези клетки? Добре, нека започнем с нашия пример. Искаме само да видим данни от Ню Орлиънс или Кларксвил, така че нека ги въведем в клетки E2 и E3.
Когато въвеждате стойности в различни редове, означава OR. Сега искаме семействата на Ню Орлиънс с повече от двама членове и семейства от Кларксвил с повече от 3 членове. За да направите това, въведете >2 в C2 и >3 в С3.
Тъй като> 2 и Ню Орлиънс са на един ред, той ще бъде AND оператор. Същото важи за ред 3 по-горе. И накрая, искаме само семействата с END адрес на END. За да направите това, просто въведете * .edu както в D2, така и в D3. Символът * означава произволен брой знаци.
Щом го направите, кликнете някъде в набора от данни и след това кликнете върху напреднал бутон. Най- Списък с рангe полето автоматично ще определи вашия набор от данни, след като сте кликнали върху него преди да кликнете върху бутона Advanced.Сега кликнете върху малкия малък бутон отдясно на Диапазон на критериите бутон.
Изберете всичко от A1 до E3 и след това отново кликнете върху същия бутон, за да се върнете към диалога за разширен филтър. Кликнете върху 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. Ако имате някакви въпроси, не се колебайте да публикувате коментар. Наслади се!
