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

У подавляющего большинства юзеров 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 (включительно)

Интересно почитать: Как в excel связать ячейки на одном листе

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

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

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

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

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

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

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

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

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

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

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

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

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

Интересно почитать: Как задать в excel формат ячейки

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

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

Срезы

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

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

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

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

Конструктор

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Интересно почитать: Excel как объединить текст в ячейках в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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