Разширен филтър в MS EXCEL. Разширен филтър в правилата на Excel за съвместна "работа" на няколко условия за избор

Филтрирането на данни в Excel ще покаже информацията, която интересува потребителя в определен момент. Това значително опростява процеса на работа с големи маси. Ще можете да контролирате както данните, които ще се показват в колоната, така и тези, които са изключени от списъка.

Как да добавя

Ако сте направили информация чрез раздела "Вмъкване" - "Таблица" или раздела "Начало" - "Форматиране като таблица", тогава опцията за филтриране ще бъде активирана по подразбиране. Желаният бутон се показва под формата на стрелка, която се намира в горната клетка от дясната страна.

Ако току-що сте попълнили блоковете с данни и след това сте ги форматирали като таблица, филтърът трябва да бъде включен. За да направите това, изберете целия диапазон от клетки, включително реда със заглавия, тъй като бутонът, от който се нуждаем, ще бъде добавен към горния ред. Но ако изберете блокове, започващи от клетка с данни, тогава първият ред няма да се отнася до филтрираната информация. След това отидете в раздела Данни и щракнете върху бутона Филтър.

В примера бутонът със стрелката е в заглавките и това е правилно - всички данни под него ще бъдат филтрирани.

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

Как работи

Сега нека да разгледаме как работи филтърът в Excel. Нека използваме следните данни като пример. Имаме три колони: "Име на продукта", "Категория" и "Цена", ще приложим различни филтри към тях.

Щракнете върху стрелката в горната клетка на желаната колона. Тук ще видите списък с неповтарящи се данни от всички клетки, разположени в тази колона. Ще има отметка до всяка стойност. Махнете отметките от квадратчетата за стойностите, които искате да изключите от списъка.

Например, нека оставим само плодове в "Категория". Премахнете отметката от полето "зеленчуци" и щракнете върху "OK".

За тези колони на таблицата, към които е приложен филтърът, съответната икона се появява в горната клетка.

Как да изтриете

Ако трябва да премахнете филтър за данни в Excel, щракнете върху клетката върху съответстващикона и изберете от менюто „Премахване на филтъра от (име на колона)“.

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

Използване на филтър

Числова

Нека приложим "Числово ..." към колоната "Цена". Щракнете върху бутона в горната клетка и изберете съответния елемент от менюто. От падащия списък можете да изберете условието, което искате да приложите към данните. Например ще покажем всички продукти, чиято цена е под "25". Избираме "по-малко".

В съответното поле въведете желаната стойност. За филтриране можете да приложите множество условия, като използвате логически „И“ и „ИЛИ“. При използване на "И" - трябва да са изпълнени и двете условия, при използване на "ИЛИ" - едно от посочените. Например, можете да зададете: "по-малко" - "25" - "AND" - "more" - "55". По този начин ще изключим артикули, които са в ценовия диапазон от 25 до 55.

В примера го получих така. Всички данни с "Цена" под 25 се показват тук.

Текст

"Текстов филтър"в примерната таблица, може да се приложи към колоната "Име на продукта"... Щракнете върху бутона със стрелка в горната част и изберете елемента със същото име от менюто. В падащия списък, който се отваря, например, използваме „започва с“.

Нека оставим в таблицата продукти, които започват с "ка". В следващия прозорец в полето пишем: "ka *". Щракнете върху "OK".

"*" С една дума замества поредица от знаци. Например, ако зададете условието "съдържа" - "s * l", думите ще останат: маса, стол, сокол и т.н. "?" ще замени всеки знак. Например, "b? Tone" - хляб, пъпка, бетон. Ако трябва да оставите думи, състоящи се от 5 букви, напишете "?????" ...

Така оставих тези, които ми трябваха "Имена на продукти".

По цвят на клетката

Филтърът може да бъде персонализиран според цвета на текста или цвета на клетката.

Нека направим "Филтриране по цвят"колонни клетки "Име на продукта"... Щракнете върху бутона със стрелка и изберете елемента със същото име от менюто. Да изберем червено.

В резултат на това останаха само червени продукти или по-скоро всички клетки, които бяха запълнени с избрания цвят.

По цвят на текста

Сега, в използвания пример, се показват само червени плодове.

Ако искате всички клетки на таблицата да са видими, но първо имаше червено, след това зелено, синьо и така нататък, използвайте сортиране в Excel. Като кликнете върху връзката, можете да прочетете статията по темата.

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

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

С разширения филтър можете да филтрирате всичко в таблицата !!! Ограничението на възможностите на този инструмент е само въображението на потребителя!

Четете четвъртата публикация от поредица от статии за създаване на бази данни в ГОСПОЖИЦА Excel и организация обработка на информация.

Можете да прочетете за това как в предишните статии от цикъла.

Филтър № 2 - Разширен филтър!

За да започнете, трябва да създадете друга таблица над базата данни, в която ще посочим данните, които са критериите за избор на разширения филтър. (Понякога е по-удобно да поставите такава маса на нов лист.)

Помните ли, когато създадохме примерната база данни, над таблицата, оставихме няколко празни реда, казвайки, че ще ни трябват по-късно, когато анализираме данните? Това време дойде.

1. Отворете файла в MS Excel.

2. Когато създавате таблица с критерии за избор на разширен филтър, препоръчвам първо да действате по формулен начин, без да мислите за задачите, които трябва да решите. копие всичкозаглавки на полета на колони от клетки A7 ... F7 до клетки A1 ... F1 - празенза таблицата с критериите е готова! Можете да започнете основната си работа.

За стабилна и безгрешна работа на филтъра трябва да има поне един празен ред между таблицата с критерии за избор и таблицата на базата данни!

Ще продължим да изучаваме усъвършенствания филтър в Excel, решавайки практически задачи на примера на работа с базата данни BD2 „Производство на метални конструкции по секция No2“.

Задача номер 5:

Показва информация за производството на греди за всички поръчки за целия период.

1. Записваме параметъра за филтриране - думата „лъч“ в колоната „Продукт“ на горната таблица с критерии за избор.

2. Активираме („изправете се с мишката“) всяка клетка в таблицата на базата данни – това ще осигури автоматично попълване на прозореца „Начален диапазон“ в падащия диалогов прозорец „Разширен филтър“.

3. Активираме разширения филтър в Excel 2003 през главното меню на програмата. Изберете: "Данни" - "Филтър" - "Разширен филтър".

4. В падащия прозорец Разширен филтър попълнете прозорците, както е показано на екранната снимка под този текст.

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

5. Резултатът от разширения филтър е на следващата екранна снимка. Разширеният филтър показа всички записи в базата данни, които съдържат думата „лъч“ в колоната „Елемент“ - задачата е изпълнена.

Регистрът на буквите не влияе на резултатите от филтрирането!

Правила за съвместна "работа" на няколко условия за подбор

Разширеният филтър в Excel ви позволява да филтрирате различни много сложни комбинации от условия на филтриране. Можете да зададете няколко различни условия за няколко колони, можете да зададете няколко условия за една колона или да зададете параметри на филтриране по формула - има много опции!

Но има само две основни правила! Всички останали случаи са различни комбинации от тези две правила.

1. Публикувани условия за подбор на един редв една или повече различни колони на таблицата с критерии, инструктирайте разширения филтър да показва редове, за които се изпълняват едновременно всички без изключениетези условия. (Параметрите за избор са свързани чрез логически оператори "И".)

2. Публикувани условия за подбор на различни линиив една или повече колони на таблицата с критерии, инструктирайте разширения филтър да покаже всички редове, за които поне единот тези условия. (Параметрите за избор са свързани чрез логически оператори „ИЛИ“.)

Нека да илюстрираме с примери действието на горните правила и да покажем как работи разширеният филтър в Excel при решаване на сложни проблеми.

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

Проблем номер 6:

Филтрирайте информацията за плочи с тегло под 0,1 тона в цялата база данни.

В резултат на работата на разширения филтър се показват всички табели от базата данни с маса под 0,1 тона (илюстрация на правило № 1).

Задача номер 7:

Показва информация за всички налични табели в базата данни, както и за всякакви други продукти с тегло над 1 тон.

Проблемът е решен - показват се записи за всички табели от базата данни и за всички продукти по-тежки от 1 тон (илюстрация на правило № 2).

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

Обръщам вниманието ви върху необходимостта от внимателно контролиране на коректността на определяне на първоначалния диапазон на базата данни и обхвата на таблицата с условия в падащия диалогов прозорец Разширен филтър!

По-специално, в последния пример трябва да посочите: „Обхват от условия: $ A $ 1: $ Ф$3 »!

Ако има напълно празни редове в диапазона от условия или във вашата база данни, тогава разширеният филтър няма да работи!

Резултати.

Когато въвеждате думи за търсене, можете да използвате често срещани заместващи знаци и математически символи:

* - произволен брой от произволни знаци
? - всеки един знак
= - равно
< — меньше
> - още
<= — меньше или равно
> = - повече или равно
<>- не е равно

Разширеният филтър в Excel е гъвкав и информативен за използване. Критериите за подбор винаги са пред очите на потребителя под формата на таблица с критерии за подбор, което несъмнено е много удобно.

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

„Копайте по-дълбоко“ във възможностите на този инструмент – струва си да го разберете! Повишената ефективност на вашата работа многократно ще припокрие времето, прекарано в учене!

Продължение на темата съхранение и управление на големи количества информация- в следващите статии от цикъла.

умолявам се уважавайки авторска работа Абонирай се за съобщения за статии в полето в края на всяка статия или в полето в горната част на страницата!

Уважаеми читатели, пишете вашите въпроси и коментари в коментарите в долната част на страницата.

И тогава, за да може бързоизберете само данните, от които се нуждаете, укриванененужни струнимаси. По този начин филтърът позволява без изтриванеРедовете на таблицата в Excel просто ги скриват временно.

Редовете на таблицата, скрити от филтъра, не изчезват. Можете условно да си представите, че височината им става равна на нула (говорих за промяна на височината на редовете и ширината на колоните). По този начин останалите линии, които не са скрити от филтъра, са "залепени заедно", така да се каже. Резултатът е филтрирана таблица.

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

Сега нека видим как всъщност да добавите филтри към таблицата.

Как да направите (приложите) филтър към електронна таблица на Excel

Много е лесно! Да кажем, че имаме таблица със заглавия. Тъй като филтърът в таблицата на Excel е наложен върху определена част от таблицата, тази част трябва първо да бъде подчертайте... Самият Excel никога няма да отгатне какво искате, така че избираме необходимите клетки в реда със заглавките на таблицата, както е показано на фигурата по-долу.

[щракнете върху снимката, за да я увеличите]

Забележка:

Ако изберете реда ВСИЧКИ, тогава Excel автоматично ще зададе филтъра за ВСИЧКИ колони, в които има поне нещо. Ако няма нищо в колоната, тогава филтърът не се прилага.

След като сте избрали необходимите колони, трябва действително да инсталирате филтъра. Най-лесният начин е да отидете в раздела "Основно" / "Филтър и сортиране" на лентата и да кликнете върху филтъра. Ето екранна снимка от Office 2010, в други версии на Excel филтърът се прилага към таблицата по същия начин.

[щракнете върху снимката, за да я увеличите]

След като щракнете върху този бутон, към избраните клетки ще бъде добавен филтър.

Ако не изберете няколко клетки, а просто щракнете върху една, тогава филтърът се добавя към ЦЕЛИЯ ред, сякаш сте го избрали.

В моя пример от трите колони, показани на снимката, филтърът ще бъде добавен само към две, клетките на които са били предварително избрани. Можете да видите резултата на екранната снимка по-долу.

[щракнете върху снимката, за да я увеличите]

Показаният пример за активиране на филтър за таблица е най-простият. Да видим как работи.

Как да използвате филтъра в Excel

Веднага след включване на филтъра таблицата няма да се промени (освен стрелките, които се показват в заглавията на колоните). За да филтрирате някои от данните, от които се нуждаете, щракнете върху стрелката в колоната, по която искате да филтрирате. На практика изглежда като на снимката по-долу.

[щракнете върху снимката, за да я увеличите]

Значението на филтъра:

е, че Excel ще остави само онези редове в таблицата, които в ТАЗИ (с конфигурирания филтър) колона съдържат клетка с избраната стойност. Други линии ще бъдат скрити.

За да премахнете филтрирането (без да премахвате филтъра!) Просто отметнете всички квадратчета. Същият ефект ще се наблюдава, ако филтърът бъде напълно премахнат - таблицата ще се върне в първоначалния си вид.

Прилагане на множество филтри към таблица в Excel

Филтрите в таблицата на Excel могат да се комбинират. Прилагат се множество филтри според логическия принцип "И". Какво означава това, нека да видим пример.

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

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

С получената проба можете да извършвате допълнителни операции. Например, можете да копирате тези редове и да ги прехвърлите в друга таблица. Като алтернатива можете да филтрирате тези редове, които искате да изтриете напълно! И след това, след като премахнете филтъра, след като ги премахнете, ще получите таблица, изчистена от ненужни данни.

Сега гледайте видео, в което показвам реален пример за филтриране на данни в ценоразпис от повече от 15 000 реда.

Нека обобщим

Ако трябва да работите с големи таблици в Excel и трябва да можете бързо да избирате определени данни, тогава използването на филтри ще ви спести много време. Колкото по-голяма е таблицата, толкова по-голяма е ползата от прилагането на филтри.

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

Можете да изтеглите файла, на примера на който смятах да работя с филтри

Филтрирането на данни в Excel включва два филтъра: Автофилтър и Разширен филтър. Да предположим, че имате голям набор от данни, но от целия масив трябва да видите или изберете данни, които се отнасят до конкретна дата, конкретно лице и т.н. Има филтри за това. За тези, които се сблъскват с този инструмент за първи път, филтърът не изтрива, а скрива записи, които не отговарят на условията за филтриране, които сте посочили за тях.

Първият е автофилтър, предназначен за най-простите операции - маркиране на записи с конкретна стойност (например подчертаване само на записи, свързани с Леброн Джеймс), данни, лежащи в определен диапазон (или над средното, или топ десет) или клетки / шрифтове с определен цвят (между другото, много удобно). Съответно е много лесно да го използвате. Просто трябва да изберете данните, които искате да видите филтрирани. След това командата "Данни" / "Филтър". Списъчно поле ще се появи на всяка горна клетка на горната таблица, там вече е лесно да се разбере всяка команда, лесна е за научаване и обяснение, надявам се, че няма нужда от повече, само нюансите на използването на автофилтъра:

1) Работи само с обхват без прекъсване. Вече няма да е възможно да филтрирате два различни списъка на един лист.

2) Най-горният ред на таблицата автоматично се присвоява като заглавие и не участва във филтрирането.

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

Практично приложение в работата: например работите върху този списък, за да намерите грешка или да проверите данни. След като приложите автофилтъра, можете да преминете през цялата таблица един по един, като последователно маркирате данните, които вече са били прегледани. Бутоните "Изчистване" и "Повторно прилагане" определят външния вид на таблицата след прилагане на условията. След това, след като приключите работата с таблицата, можете да върнете шрифтовете обратно в първоначалния им вид, без да променяте самите данни. Между другото, някои са объркани от факта, че всички записи в таблицата изчезват след прилагане на каквито и да е условия. Е, погледнете по-отблизо, посочили сте условия, при които няма записи, отговарящи на тези условия. Фактът, че таблицата е филтрирана, е, че номерата на редовете на таблицата са маркирани в синьо.

Сега да преминем към разширения филтър. Той се различава от автофилтъра по по-фина настройка, но и по по-голям избор при филтриране на данни. В частност:

1) Задава толкова условия, колкото е необходимо.

2) Позволява да изберете клетки с уникални (неповтарящи се) данни. Това често е необходимо при работа с данни и опцията се справя отлично с проблема.

3) Позволява ви да копирате резултата от филтъра на отделно място, без да докосвате основния масив.

И така, основната разлика в работата с този филтър е, че първо трябва да подготвим таблица с условия. Прави се просто. Заглавията на основната таблица се копират и вмъкват на удобно за нас място (предлагам над основната таблица). В тази таблица трябва да има толкова много редове, така че след като определите условията, да не влезете в основната таблица.

Примери за условия:

1) „L *“ - клетки, започващи с L

2) „> 5“ – данни по-големи от 5

Ако изтриете редове от филтрираната таблица, те ще бъдат изтрити, без да вземате съседи със себе си. Тези. ако таблицата е филтрирана и показва редове 26-29 и 31-25, избирането на всички редове и изтриването им няма да изтрие ред 30. Това е удобно, аз лично често го използвам при писане на макроси. Какво е предимството на това - често получаваме таблици, които трябва да бъдат приведени в работна форма, т.е. изтриване, например празни редове. Какво правим: филтрирайте таблицата, показвайки само редовете, които не ни трябват, след което изтрийте цялата таблица, включително заглавието. Ненужните редове и заглавия се премахват, а таблицата няма интервали и е единичен диапазон. Заглавен ред може да бъде добавен с прости операции за копиране от предварително подготвена област. Защо това е важно при писане на макроси? Не се знае от кой ред започват нежеланите данни и не е ясно от кой ред да започне изтриването, изтриването на цялата таблица помага за бързото решаване на този проблем.

Разширеният филтър в Excel ви дава по-голям контрол върху данните ви в електронната таблица. Той е по-труден за настройка, но много по-ефективен в действие.

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

Как да направя разширен филтър в Excel?

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

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

Алгоритъмът за прилагане на разширения филтър е прост:

Най-горната таблица е резултатът от филтриране. Долната табелка с условията е дадена за яснота до нея.

Как да използвам разширен филтър в Excel?

За да отмените действието на разширения филтър, поставете курсора някъде в таблицата и натиснете клавишната комбинация Ctrl + Shift + L или "Данни" - "Сортиране и филтриране" - "Изчистване".

С помощта на инструмента "Разширен филтър" ще намерим информация за стойностите, които съдържат думата "Задаване".

Ще добавим критерии към таблицата с условия. Например такива:

В този случай програмата ще търси цялата информация за стоки, чието име съдържа думата "Комплект".

Можете да използвате знака "=", за да намерите точната стойност. Нека добавим следните критерии към таблицата с условия:

Excel интерпретира знака "=" като сигнал: потребителят вече ще зададе формулата. За да работи програмата правилно, лентата с формули трябва да съдържа запис от вида: = "= Набор от площ 6 cl."

След използване на "Разширен филтър":

Сега нека филтрираме оригиналната таблица по условието "ИЛИ" за различни колони. Операторът OR също е наличен в инструмента за автоматично филтриране. Но там може да се използва в рамките на една колона.

Въведете критериите за избор в таблицата с условия: = "= Комплект площ 6 cl." (в колоната "Име") и = "

Моля, обърнете внимание: критериите трябва да бъдат написани под съответните заглавия на РАЗЛИЧНИ редове.

Резултат от селекцията:

Разширеният филтър ви позволява да използвате формули като критерий. Нека да разгледаме един пример.

Избор на реда с максимален дълг: = MAX (Таблица 1 [Задлъжнялост]).

Така получаваме резултатите като след изпълнение на няколко филтъра на един лист на Excel.

Как да направя няколко филтъра в Excel?

Нека създадем филтър по няколко стойности. За да направите това, ще въведете няколко критерия за избор на данни в таблицата с условия наведнъж:

Нека приложим инструмента Разширен филтър:

Сега от таблицата с избраните данни ще извлечем нова информация, избрана по други критерии. Например само пратки за 2014г.

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

За да използвате множество филтри, можете да генерирате множество таблици с условия на нови листове. Методът на изпълнение зависи от задачата, поставена от потребителя.

Как да направя филтър в Excel по редове?

По стандартния начин - нищо. Microsoft Excel избира данни само в колони. Следователно трябва да потърсите други решения.

Ето примери за разширени критерии за низове за филтриране в Excel:

За да дадем пример как работи филтърът за редове в Excel, нека създадем таблица.