Расширенный фильтр и мало магии

У подавляющего большинства юзеров Excel при слове "фильтрация данных" в голове всплывает лишь обыденный традиционный фильтр с вкладки Данные — Фильтр (Data — Filter) :

Таковой фильтр — штука обычная, спору нет, и для большинства случаев полностью сойдет. Но бывают ситуации, когда необходимо проводить отбор по большенному количеству сложных критерий сходу по нескольким столбцам. Обыденный фильтр здесь не весьма комфортен и охото чего-то помощнее. Таковым инвентарем может стать расширенный фильтр (advanced filter), в особенности с маленькой "доработкой ратфилем" (по традиции).

База

Для начала вставьте над вашей таблицей с данными несколько пустых строк и скопируйте туда шапку таблицы — это будет спектр с критериями (выделен для наглядности желтоватым):

Меж желтоватыми ячейками и начальной таблицей непременно обязана быть хотя бы одна пустая строчка.

Конкретно в желтоватые ячейки необходимо ввести аспекты (условия), по которым позже будет произведена фильтрация. К примеру, если необходимо отобрать бананы в столичный "Ашан" в III квартале, то условия будут смотреться так:

Чтоб выполнить фильтрацию выделите всякую ячейку спектра с начальными данными, откройте вкладку Данные и нажмите клавишу Добавочно (Data — Advanced) . В открывшемся окне должен быть уже автоматом введен спектр с данными и нам остается лишь указать спектр критерий, т.е. A1:I2:

Направьте внимание, что спектр критерий недозволено выделять "с припасом", т.е. недозволено выделять излишние пустые желтоватые строчки, т.к. пустая ячейка в спектре критерий воспринимается Excel как отсутствие аспекта, а целая пустая строчка — как просьба вывести все данные без разбора.

Переключатель Скопировать итог в другое пространство дозволит фильтровать перечень не прямо здесь же, на этом листе (как обыденным фильтром), а выгрузить отобранные строчки в иной спектр, который тогда необходимо будет указать в поле Поместить итог в спектр. В этом случае мы эту функцию не используем, оставляем Фильтровать перечень на месте и нажимаем ОК. Отобранные строчки отобразятся на листе:

Добавляем макрос

"Ну и где же здесь удобство?" — спросите вы и будете правы. Не достаточно того, что необходимо руками вводить условия в желтоватые ячейки, так к тому же открывать диалоговое окно, вводить туда спектры, нажимать ОК. Обидно, согласен! Но "все изменяется, когда приходят они ©" — макросы!

Работу с расширенным фильтром можно в разы убыстрить и упростить при помощи обычного макроса, который будет автоматом запускать расширенный фильтр при вводе критерий, т.е. изменении хоть какой желтоватой ячейки. Щелкните правой клавишей мыши по ярлыку текущего листа и изберите команду Начальный текст (Source Code) . В открывшееся окно скопируйте и вставьте вот таковой код:

Эта процедура будет автоматом запускаться при изменении хоть какой ячейки на текущем листе. Если адресок модифицированной ячейки попадает в желтоватый спектр (A2:I5), то данный макрос снимает все фильтры (если они были) и поновой применяет расширенный фильтр к таблице начальных данных, начинающейся с А7, т.е. все будет фильтроваться одномоментно, сходу опосля ввода еще одного условия:

Так все еще лучше, правда? 🙂

Реализация сложных запросов

Сейчас, когда все фильтруется "на лету", можно мало углубиться в аспекты и разобрать механизмы наиболее сложных запросов в расширенном фильтре. Кроме ввода четких совпадений, в спектре критерий можно употреблять разные знаки подстановки (* и ?) и знаки математических неравенств для реализации ориентировочного поиска. Регистр знаков роли не играет. Для наглядности я свел все вероятные варианты в таблицу:

Аспект	Итог
гр* либо гр	все ячейки начинающиеся с Гр , т.е. Груша, Грейпфрут, Гранат и т.д.
=лук	все ячейки конкретно и лишь со словом Лук, т.е. четкое совпадение
*лив* либо *лив	ячейки содержащие лив как подстроку, т.е. Оливки, Ливер, Залив и т.д.
=п*в	слова начинающиеся с П и заканчивающиеся на В т.е. Павлов, Петров и т.д.
а*с	слова начинающиеся с А и содержащие дальше С , т.е. Апельсин, Ананас, Асаи и т.д.
=*с	слова оканчивающиеся на С
=.	все ячейки с текстом из 4 знаков (букв либо цифр, включая пробелы)
=м. н	все ячейки с текстом из 8 знаков, начинающиеся на М и заканчивающиеся на Н , т.е. Мандарин, Мангостин и т.д.
=*н??а	все слова оканчивающиеся на А , где 4-я с конца буковка Н , т.е. Брусника, Заноза и т.д.
>=э	все слова, начинающиеся с Э , Ю либо Я
<>*о*	все слова, не содержащие буковку О
<>*вич	все слова, не считая заканчивающихся на вич (к примеру, фильтр дам по отчеству)
=	все пустые ячейки
<>	все непустые ячейки
>=5000	все ячейки со значением больше либо равно 5000
5 либо =5	все ячейки со значением 5
>=3/18/2013	все ячейки с датой позднее 18 марта 2013 (включительно)

• Символ * предполагает под собой хоть какое количество всех знаков, а ? — один хоть какой знак.
• Логика в обработке текстовых и числовых запросов мало различная. Так, к примеру, ячейка условия с числом 5 не значит поиск всех чисел, начинающихся с 5, но ячейка условия с буковкой Б равносильна Б*, т.е. будет находить хоть какой текст, начинающийся с буковкы Б.
• Если текстовый запрос не начинается со знака =, то в конце можно на уровне мыслей ставить *.
• Даты нужно вводить в штатовском формате месяц-день-год и через дробь (даже если у вас российский Excel и региональные опции).

Логические связки И-ИЛИ

Условия записанные в различных ячейках, но в одной строке — числятся связанными меж собой логическим оператором И (AND) :

Т.е. фильтруй мне бананы конкретно в 3-ем квартале, конкретно по Москве и при всем этом из "Ашана".

Если необходимо связать условия логическим оператором ИЛИ (OR) , то их нужно просто вводить в различные строчки. К примеру, если нам необходимо отыскать все заказы менеджера Волиной по столичным персикам и все заказы по луку в 3-ем квартале по Самаре, то это можно задать в спектре критерий последующим образом:

Если же необходимо наложить два либо наиболее критерий на один столбец, то можно просто продублировать заголовок столбца в спектре критериев и вписать под него 2-ое, третье и т.д. условия. Вот так, к примеру, можно отобрать все сделки с марта по май:

В общем и целом, опосля "доработки ратфилем" из расширенного фильтра выходит полностью для себя солидный инструмент, местами не ужаснее традиционного автофильтра.

Фильтрация данных в Excel

• Фильтр по значению – отметить флагом нужные значения из столбца данных, которые высвечиваются понизу диалогового окна.
• Фильтр по цвету – выбор по отформатированной ячейке: по цвету ячейки, по цвету шрифта либо по значку ячейки (если установлено условное форматирование).
• Можно пользоваться строчкой резвого поиска
• Для выбора числового фильтра, текстового фильтра либо фильтра по дате (в зависимости от типа данных) избрать подобающую строчку. Покажется контекстное меню с наиболее детализированными способностями фильтрации:

1. При выбирании функции Числовые фильтры покажутся последующие варианты фильтрации: равно, больше, меньше, 1-ые 10… [Top 10…] и др.
2. При выбирании функции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит. , начинается с… и др.
3. При выбирании функции Фильтры по дате варианты фильтрации – завтра, на последующей недельке, в прошедшем месяце и др.
4. Во всех вышеперечисленных вариантах в контекстном меню содержится пункт Настраиваемый фильтр… [Custom…], используя который можно задать сразу два условия отбора, связанные отношением И [And] – одновременное выполнение 2 критерий, ИЛИ [Or] – выполнение хотя бы 1-го условия.

Если данные опосля фильтрации были изменены, фильтрация автоматом не срабатывает, потому нужно запустить функцию вновь, нажав на клавишу Повторить [Reapply] в группе Сортировка и фильтр на вкладке Данные.

Отмена фильтрации

Для того чтоб отменить фильтрацию спектра данных, довольно повторно щелкнуть по кнопочке Фильтр.

Чтоб снять фильтр лишь с 1-го столбца, довольно щелкнуть по кнопочке со стрелочкой в первой строке и в контекстном меню избрать строчку: Удалить фильтр из столбца.

Чтоб стремительно снять фильтрацию со всех столбцов нужно выполнить команду Очистить на вкладке Данные

Срезы

Срезы – это те же фильтры, но вынесенные в отдельную область и имеющие комфортное графическое представление. Срезы являются не частью листа с ячейками, а отдельным объектом, набором клавиш, размещенным на листе Excel. Внедрение срезов не подменяет автофильтр, но, благодаря комфортной визуализации, упрощает фильтрацию: все примененные аспекты видны сразу. Срезы были добавлены в Excel начиная с версии 2010.

Создание срезов

В Excel 2010 срезы можно употреблять для сводных таблиц, а в версии 2013 существует возможность сделать срез для хоть какой таблицы.

Для этого необходимо выполнить последующие шаги:

Выделить в таблице одну ячейку и избрать вкладку Конструктор [Design].

1. В диалоговом окне отметить поля, которые желаете включить в срез и надавить OK.

Форматирование срезов

1. Выделить срез.
2. На ленте вкладки Характеристики [Options] избрать группу Стили срезов [Slicer Styles], содержащую 14 обычных стилей и опцию сотворения собственного стиля юзера.

1. Избрать клавишу с пригодным стилем форматирования.

Чтоб удалить срез, необходимо его выделить и надавить кнопку Delete.

Расширенный фильтр

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

Расширенный фильтр в Excel и примеры его способностей

Вывести на экран информацию по одному / нескольким характеристикам можно при помощи фильтрации данных в Excel.

Для данной нам цели предназначено два инструмента: автофильтр и расширенный фильтр. Они не убирают, а скрывают данные, не пригодные по условию. Автофильтр делает простые операции. У расширенного фильтра еще больше способностей.

Автофильтр и расширенный фильтр в Excel

Имеется обычная таблица, не отформатированная и не объявленная перечнем. Включить автоматический фильтр можно через основное меню.

1. Выделяем мышкой всякую ячейку снутри спектра. Перебегаем на вкладку «Данные» и жмем клавишу «Фильтр».
2. Рядом с заголовками таблицы возникают стрелочки, открывающие списки автофильтра.

Если отформатировать спектр данных как таблицу либо объявить перечнем, то автоматический фильтр будет добавлен сходу.

Воспользоваться автофильтром просто: необходимо выделить запись с необходимым значением. К примеру, показать поставки в магазин №4. Ставим птичку напротив соответственного условия фильтрации:

Сходу лицезреем итог:

Индивидуальности работы инструмента:

1. Автофильтр работает лишь в неразрывном спектре. Различные таблицы на одном листе не фильтруются. Даже если они имеют однотипные данные.
2. Инструмент принимает верхнюю строку как заглавия столбцов – эти значения в фильтр не врубаются.
3. Допустимо использовать сходу несколько критерий фильтрации. Но любой предшествующий итог может скрывать нужные для последующего фильтра записи.

У расширенного фильтра еще больше способностей:

1. Можно задать столько критерий для фильтрации, сколько необходимо.
2. Аспекты выбора данных – на виду.
3. При помощи расширенного фильтра юзер просто находит неповторимые значения в многострочном массиве.

Как создать расширенный фильтр в Excel

Готовый пример – как употреблять расширенный фильтр в Excel:

1. Сделаем таблицу с критериями отбора. Для этого копируем заглавия начального перечня и вставляем выше. В табличке с аспектами для фильтрации оставляем достаточное количество строк плюс пустая строчка, отделяющая от начальной таблицы.
2. Настроим характеристики фильтрации для отбора строк со значением «Москва» (в соответственный столбец таблички с критериями вносим = «=Москва»). Активизируем всякую ячейку в начальной таблице. Перебегаем на вкладку «Данные» — «Сортировка и фильтр» — «Добавочно».
3. Заполняем характеристики фильтрации. Начальный спектр – таблица с начальными данными. Ссылки возникают автоматом, т.к. была активна одна из ячеек. Спектр критерий – табличка с условием.
4. Выходим из меню расширенного фильтра, нажав клавишу ОК.

В начальной таблице остались лишь строчки, содержащие значение «Москва». Чтоб отменить фильтрацию, необходимо надавить клавишу «Очистить» в разделе «Сортировка и фильтр».

Как воспользоваться расширенным фильтром в Excel

Разглядим применение расширенного фильтра в Excel с целью отбора строк, содержащих слова «Москва» либо «Рязань». Условия для фильтрации должны находиться в одном столбце. В нашем примере – друг под другом.

Заполняем меню расширенного фильтра:

Получаем таблицу с отобранными по данному аспекту строчками:

Выполним отбор строк, которые в столбце «Магазин» содержат значение «№1», а в столбце стоимость – «>1 000 000 р.». Аспекты для фильтрации должны находиться в соответственных столбцах таблички для критерий. На одной строке.

Заполняем характеристики фильтрации. Жмем ОК.

Оставим в таблице лишь те строчки, которые в столбце «Регион» содержат слово «Рязань» либо в столбце «Стоимость» — значение «>10 000 000 р.». Потому что аспекты отбора относятся к различным столбцам, размещаем их на различных строчках под надлежащими заголовками.

Применим инструмент «Расширенный фильтр»:

Данный инструмент умеет работать с формулами, что дает возможность юзеру решать фактически любые задачки при отборе значений из массивов.

1. Итог формулы – это аспект отбора.
2. Записанная формула возвращает итог ИСТИНА либо ЛОЖЬ.
3. Начальный спектр указывается средством абсолютных ссылок, а аспект отбора (в виде формулы) – при помощи относительных.
4. Если ворачивается значение ИСТИНА, то строчка отобразится опосля внедрения фильтра. ЛОЖЬ – нет.

Отобразим строчки, содержащие количество выше среднего. Для этого в стороне от таблички с аспектами (в ячейку I1) введем заглавие «Наибольшее количество». Ниже – формула. Используем функцию СРЗНАЧ.

Выделяем всякую ячейку в начальном спектре и вызываем «Расширенный фильтр». В качестве аспекта для отбора указываем I1:I2 (ссылки относительные!).

В таблице остались лишь те строчки, где значения в столбце «Количество» выше среднего.

Чтоб бросить в таблице только неповторяющиеся строчки, в окне «Расширенного фильтра» поставьте птичку напротив «Лишь неповторимые записи».

Нажмите ОК. Повторяющиеся строчки будут укрыты. На листе останутся лишь неповторимые записи.

Как употреблять фильтр в MS Excel. Описание и примеры.

В данной статье, на примере, разглядим как работает фильтрация в MS Excel. В качестве примера будем употреблять таблиц с данными о наличии компьютерной техники на складе магазина.

Где отыскать фильтр ?

Что бы сделать фильтр, необходимо отыскать клавишу Фильтр, которая находиться в закладке Данные.

Либо употреблять сочетание кнопок Ctrl + Shift + L. В этом случае, перед тем как надавить сочетание кнопок, необходимо выделить спектр, к которому будет применён фильтр.

Направьте внимание.

Для корректной работы фильтра, у столбцов, данные которых будут отфильтрованы, должны быть наименования (оглавление). В нашем примере это: категория продуктов, производитель и т.д. (строчка 2). Конкретно в строке с заглавием столбцов будет закреплен фильтр. Если заглавие столбцов нет, фильтр будет закреплен в верхней строке фильтруемых столбцов. В нашем примере это строчка 3. При всем этом, данные, которые находятся в данной нам строке не отфильтровываются, они остаются закрепленными на месте.

Создаем фильтр.

Избираем спектр, к которому желаем применить фильтр. В нашем примере, это вся таблица. Жмем на клавишу Фильтр. В строке 2, с заглавием столбцов, возникает значки фильтра. Если надавить на таковой значок фильтра, показаться перечень с данными, находящимися в избранном столбце. Напротив всякого элемента будет стоять галочка. Для того, чтоб отфильтровать нужные нам данные, нужно бросить галочки лишь около подходящих нам значений. В нашем примере, в столбце категория продукта, оставим лишь ноутбуки.

Нажав на Выделить все, можно или убрать галочки со всех значений, или поставить их на все значения. Если список значений в фильтре очень большенный, можно употреблять поиск и ввести интересующие нас значение в поле поиск.

Сейчас выберем подходящего нам производителя ноутбуков, к примеру НР. Повторяем выше указное действие с фильтром в столбце производитель. Получаем отфильтрованные по двум аспектам данные в таблице.

Зависимо от задачки и размера данных, фильтрацию можно продолжать сколько угодно.

Что бы возвратиться к начальному варианту таблицы, необходимо в соответственных фильтрах поставить галочки всюду. Или надавить снова на клавишу Фильтр, и все фильтры в таблице пропадут и показаться вся начальная информация.