Фильтры в таблицах Microsoft Excel — это инструмент, позволяющий из большого объёма данных выбрать и отобразить только те данные, которые нужны для решения какой-то конкретной задачи. После выполнения операции фильтрации в таблице будут отображаться только соответствующие условиям отбора строки. Строки же, которые этим условиям не соответствуют, будут скрыты.
В приложении Excel предусмотрено два вида фильтров: простой и расширенный. Простой фильтр или, как его ещё называют, автофильтр (данное название пришло из старых версий Excel) позволяет отбирать записи из базы данных в соответствии с простыми условиями. Расширенный же фильтр предоставляет возможность решения задач фильтрации данных с более сложными критериями отбора.
Рассмотрим каждый из этих фильтров подробнее.
Автофильтр
Включение/отключение режима автофильтра производится с помощью кнопки «Фильтр» на вкладке «Данные».
Также команду включения/отключения автофильтра можно найти на вкладке «Главная» в группе «Редактирование» (см. рис. ниже).
Перед включением автофильтра следует указать приложению, для каких строк и по значениям каких столбцов будет производиться фильтрация. Например, если в таблице выделить одну любую ячейку и нажать кнопку «Фильтр», в область для фильтрации войдут все строки таблицы и установка фильтров будет возможна по значениям всех столбцов таблицы, о чём будут свидетельствовать кнопки со стрелками (см. рис. ниже), появившиеся в каждой ячейке строки заголовков.
Если перед включением автофильтра выделить один или несколько столбцов, а затем нажать кнопку «Фильтр», то в область для фильтрации также войдут все строки таблицы, но установка фильтров будет возможна только по значениям этих предварительно выделенных столбцов. Свидетельством тому будут кнопки со стрелками (см. рис. ниже), появившиеся в заголовочных ячейках выбранных столбцов.
Примечание: Таблица, к которой будет применяться автофильтр, обязательно должна иметь строку с заголовками, так кнопки фильтра размещаются именно в этой строке. Если у таблицы отсутствует строка с заголовками, приложение разместит эти кнопки в самой верхней строке с данными. При этом данная строка будет исключена из области фильтрации, а потому всегда будет отображаться на экране.
Если перед включением автофильтра выделить одну или несколько строк (см. рис. ниже) и нажать кнопку «Фильтр», то в область для фильтрации войдут все строки, лежащие ниже самой верхней из выделенных строк (не выделенный диапазон строк, а именно все строки до самого низа таблицы). Строка с кнопками фильтра и строки, лежащие выше её, будут исключены из области, подлежащей фильтрации.
Если перед включением автофильтра выделить несколько ячеек (см. рис. ниже) и нажать кнопку «Фильтр», то в область для фильтрации войдут все строки, лежащие ниже самой верхней строки, содержащей выделенные ячейки. Строка с кнопками фильтра и строки, лежащие выше её, будут исключены из области, подлежащей фильтрации. Установка же фильтров будет возможна только по значениям столбцов, содержащих выделенные ячейки.
Отбор записей с помощью автофильтра
Отбор записей из таблицы производится посредством меню, открывающегося при нажатии кнопки со стрелкой (
) в заголовке того столбца, по значениям которого записи необходимо отфильтровать.
Для отбора записей по конкретным, заранее известным значениям в этом меню отображается список всех значений данного столбца (см. рис. ниже). Если столбец содержит, например, фамилии сотрудников, то фамилии будут отображены и в этом списке значений. Чтобы отобрать записи по отдельным сотрудникам, необходимо просто убрать галочку с пункта «Выделить все», а затем проставить галочки напротив конкретных фамилий.
Если отбор осуществляется по столбцу, ячейки которого имеют формат «Дата», с помощью списка имеющихся значений можно отобрать записи за конкретный год, месяц или число (см. рис. ниже).
Для отбора же записей в более широком диапазоне значений следует воспользоваться подменю, содержащим список типовых шаблонов фильтров (см. рис. ниже). Для столбца, содержащего значения в текстовом формате, вызов данного подменю происходит при выборе пункта «Текстовые фильтры», для столбца, ячейки которого имеют формат «Дата», — при выборе пункта «Фильтры по дате»; для столбца со значениями числового формата — при выборе пункта «Числовые фильтры».
Например, чтобы отобрать из таблицы все строки с фамилиями, начинающимися на букву «П», необходимо в подменю выбрать шаблон «начинается с…» (см. рис. выше), а в появившемся диалоге настройки фильтра (см. рис. ниже) в поле для указания критерия отбора вписать символ «П» и щёлкнуть по кнопке «ОК».
Кстати, при записи критерия отбора можно использовать подстановочные символы «*» и «?», первый из которых подменяет последовательность любых символов, второй — один любой символ.
Например, чтобы отобрать записи по сотрудникам, имена которых начинаются на букву «Н», можно воспользоваться шаблоном «содержит…», а в появившемся диалоге настройки фильтра в поле для указания критерия отбора вписать строку «* н* *» (см. рис. ниже). В данной строке первая «звёздочка» подменяет любое количество символов фамилии, а записанные через пробел символы «н*» подменяют имя, начинающееся с буквы «Н». Третья «звёздочка», записанная через пробел, заменяет любое количество символов отчества.
Примечание: Обратите внимание, что после установки отбора кнопка со стрелкой того столбца, по значениям которого был установлен отбор, меняет свой вид на 
.
В случае, когда в списке шаблонов фильтров отсутствует подходящий готовый шаблон, полностью удовлетворяющий Вашим требованиям, можно выбрать любой наиболее подходящий либо пункт «Настраиваемый фильтр», а затем в появившемся диалоге настройки фильтра задать свои условия отбора.
Например, если в отчёте по продажам требуется отобрать одновременно записи по товарам с самыми большими и с самыми маленькими объёмами продаж, можно выбрать имеющийся шаблон «больше…» (см. рис. ниже).
В появившемся же диалоге настройки фильтра для отбора товаров с минимальным объёмом продаж необходимо добавить условие «меньше» и указать числовые критерии, по которым будут отбираться строки. Объединить же два противоположных критерия отбора следует с помощью логического оператора «ИЛИ» (см. рис. ниже), установив переключатель «И-ИЛИ» в соответствующее положение.
Отбор записей по значениям нескольких столбцов
Отбор записей в таблице может осуществляться по значениям комбинации фильтров. Например, чтобы из списка сотрудников выбрать данные о мужчинах 1970 года рождения и старше, можно сначала отобрать сотрудников по половому признаку, используя фильтр столбца «Пол»
а затем для столбца «Год рождения» выбрать фильтр «До…» (см. рис. ниже).
Чтобы в отбор попали сотрудники 1970 года рождения и старше, в диалоге настройки фильтра необходимо выбрать условие «до или равно», указать дату «31.12.70» и нажать кнопку «ОК».
Кстати, данный отбор можно произвести и в обратном порядке: сначала отобрать сотрудников по году рождения, а уже затем — по половому признаку. При отборе по нескольким столбцам порядок установки фильтров значения не имеет.
Отмена отбора
Чтобы отменить установленный отбор по любому из столбцов, необходимо щёлкнуть по кнопке фильтра данного столбца () и в появившемся меню выбрать пункт «Снять фильтр с…» (см. рис. ниже) либо в списке значений установить галочку «Выделить все». После нажатия кнопки «ОК» отбор по данному столбцу будет отменён.
Для отмены отбора по всем столбцам сразу следует воспользоваться кнопкой «Очистить» на вкладке «Данные» в группе «Сортировка и фильтр».
Также команда отмены отбора по всем столбцам имеется на вкладке «Главная» в группе «Редактирование» (см. рис. ниже).
Расширенный фильтр
От автофильтра расширенный фильтр отличается тем, что позволяет:
- задать более сложные условия отбора;
- разместить полученные в результате отбора данные вне исходной таблицы (в другом диапазоне строк или даже на другом листе);
- из списка повторяющихся значений выбрать только уникальные.
Для отбора строк с помощью расширенного фильтра необходимо построить таблицу условий отбора, которую можно разместить на том же листе, где находится исходная таблица с данными, либо на другом листе и даже в другой книге. Заголовки столбцов таблицы условий должны совпадать с заголовками тех столбцов исходной таблицы, по значениям которых будет осуществляться отбор. Во избежание недоразумений эти заголовки лучше скопировать из исходной таблицы.
Чтобы Вы лучше могли понять, как это всё делается, рассмотрим простой пример. Например, из общего списка сотрудников (см. рис. ниже) необходимо отобрать рабочих и специалистов 1970-ого года рождения и моложе. Для создания таблицы условий вставляем в верхней части текущего листа дополнительные строки в таком количестве, чтобы между таблицей условий и исходной таблицей с данными оставалась хотя бы одна пустая строка. Затем копируем заголовки тех столбцов, по значениям которых будет происходить отбор (порядок следования заголовков в таблице условий значения не имеет). Под заголовками в нижележащих ячейках записываем условия отбора (подробнее о правилах записи этих условий поговорим чуть позже).
Кстати, если Вы решите разместить таблицу условий отбора в нижней части текущего листа, то между ней и исходной таблицей с данными также необходимо оставить хотя бы одну пустую строку. Строку заголовков исходной таблицы лучше всего закрепить, чтобы она всегда присутствовала на экране. Так будет удобнее работать.
После того, как таблица условий отбора создана, вызываем на экран окно настройки расширенного фильтра. Для этого щёлкаем по кнопке «Дополнительно» на вкладке «Данные» в группе «Сортировка и фильтр».
При появлении на экране окна настройки фильтра (см. рис. ниже) приложение попытается определить границы области с данными, подлежащими фильтрации, и выделит эту область с помощью пунктирной линии (строка заголовков исходной таблицы должна войти в эту область). Координаты выделенной области при этом будут отображены в поле «Исходный диапазон» диалога «Расширенный фильтр».
Если границы исходной таблицы были определены приложением неправильно, необходимо указать их «вручную». Для этого минимизируем окно настройки фильтра кнопкой «Свернуть» (
), находящейся в правой части поля «Исходный диапазон», щёлкаем мышью по ячейке левого верхнего угла исходной таблицы (это ячейка заголовка её первого столбца) и, не отпуская клавиши мыши, тащим курсор к ячейке правого нижнего угла таблицы (см. рис. ниже). После завершения данной операции разворачиваем окно настройки фильтра кнопкой «Развернуть» (
).
Для указания источника условий фильтрации снова минимизируем окно настройки фильтра кнопкой «Свернуть» (), но уже той, что находится в правой части поля «Диапазон условий». После чего щёлкаем мышью по ячейке левого верхнего угла таблицы условий (это ячейка заголовка её первого столбца) и, не отпуская клавиши мыши, тащим курсор к ячейке правого нижнего угла таблицы условий (см. рис. ниже). После завершения данной операции разворачиваем окно настройки фильтра кнопкой «Развернуть» ().
Внимание! При указании границ таблицы условий не следует включать в неё пустые строки! Пустая строка в таблице условий расценивается как условие «ИЛИ ПОКАЗАТЬ ВСЕ».
После того как мы указали границы исходной таблицы и таблицы условий, можно дать команду на выполнение отбора, для чего достаточно щёлкнуть по кнопке «ОК» в окне настройки расширенного фильтра. Отбор при этом будет произведён непосредственно в исходной таблице, то есть в ней будут скрыты строки, не соответствующие заданным условиям отбора.
В последующем отменить отбор, произведённый непосредственно в исходной таблице, можно будет с помощью кнопки «Очистить» на вкладке «Данные»
либо одноимённой командой из меню на вкладке «Главная» (см. рис. ниже).
Приведённый выше пример выполнения отбора с помощью расширенного фильтра является конечно же слишком простым и может быть легко реализован с помощью автофильтра. Расширенный же фильтр обычно используют, когда необходимо задать большее количество условий, а также, когда есть необходимость воспользоваться дополнительными возможностями расширенного фильтра.
Дополнительные возможности расширенного фильтра
Если в исходной таблице имеются повторяющиеся строки, получить результат отбора без повторений позволяет установка опции «Только уникальные записи» (см. рис. ниже). При включении данной опции из каждой группы повторяющихся строк в отбор попадёт только одна строка.
Ещё одной дополнительной возможностью расширенного фильтра является возможность переноса результата отбора в другое место. Строки исходной таблицы при этом останется нетронутыми, фильтрация будет произведена в оперативной памяти компьютера, а результаты фильтрации будут отображены в виде новой таблицы в том месте, которое будет указано пользователем в поле «Поместить результаты в диапазон» (см. рис. ниже) окна настройки фильтра.
Для того чтобы указать место размещения результирующей таблицы отбора, переключатель «Обработка» (см. рис. выше) необходимо установить в положение «скопировать результат в другое место» и минимизировать окно настройки фильтра кнопкой «Свернуть» (), находящейся в правой части поля «Поместить результаты в диапазон». После этого следует выделить ячейку (см. рис. ниже), которая после выполнения отбора станет левым верхним углом результирующей таблицы. Это может быть ячейка либо текущего, либо любого другого листа книги. После указания ячейки разворачиваем окно настройки фильтра кнопкой «Развернуть» ().
При описанной выше настройке результирующая таблица будет формироваться вниз и вправо от указанной ячейки, и в эту таблицу будут включены все столбцы, присутствующие в исходной таблице. Если же присутствие всех столбцов в результирующей таблице не требуется, нужно ещё до настройки фильтра в первой строке области, отводимой под результирующую таблицу, указать заголовки тех столбцов, которые должны в ней присутствовать. Во избежание недоразумений эти заголовки лучше скопировать из исходной таблицы (порядок следования заголовков значения не имеет). При применении же фильтра необходимо будет указать координаты ячеек с названиями столбцов в поле «Поместить результат в диапазон». Для этого при минимизированном окне настройки фильтра щёлкаем мышью по ячейка заголовка первого столбца будущей таблицы и, не отпуская клавиши мыши, тащим курсор к ячейке заголовка её последнего столбца (см. рис. ниже).
Правила записи условий фильтрации
Запись условия в ячейке таблицы условий может начинаться с символов операторов сравнения:
- > — больше;
- < — меньше;
- >= — больше или равно;
- <= — меньше или равно;
- <> — не равно.
Например:
На данном рисунке показан пример использования операторов сравнения в таблице условий, созданной для отбора сотрудников, принятых на работу в 2016 году и не относящихся к категории «Рабочие». Как видно из рисунка, для реализации условия «МЕЖДУ…», то есть для отбора записей, относящихся к периоду, лежащему между двумя датами, в таблице создано два столбца с одинаковыми заголовками (порядок следования заголовков, как я уже говорил ранее, значения не имеет).
Пустая ячейка в столбце таблицы условий (на рисунке выше это ячейка столбца «Пол») говорит о том, что отбор по значениям этого столбца производиться не будет. Столбцов, не содержащих условий, в таблице условий может присутствовать сколько угодно (они не влияют на результат), а вот пустых строк быть не должно. Если при указании границ таблицы условий включить в неё пустые строки, они будут расценены как условие «ИЛИ ПОКАЗАТЬ ВСЕ» и в результате будут показаны все строки.
Как объединяются условия из различных ячеек
Условия в таблице условий, записанные одной строкой, считаются объединёнными логическим оператором «И»; условия из разных строк — логическим оператором «ИЛИ». Таким образом, условия, показанные на нижеприведённом, уже знакомом Вам рисунке, можно прочитать как
((Категория="Рабочие")И(Дата рождения>="01.01.1970")) ИЛИ ((Категория="Специалисты")И(Дата рождения>="01.01.1970"))
Согласно вышеприведённой таблице условий, приложение будет отбирать строки по сотрудникам, принадлежащим к двум разным категориям, в соответствии с указанными для тех и других датами рождения. И в расширенном фильтре, и в автофильтре логический оператор «ИЛИ» позволяет отбирать данные по объектам, не имеющим одинаковых характеристик и относящимся к разным категориям, группам, видам т.д.
Особенности использования оператора «равно»
Использовать оператора «равно» («=») при записи условия отбора не требуется. Например, для отбора данных по объектам, названия которых начинаются на букву «С» достаточно просто указать данную букву в соответствующем столбце таблицы условий (см. рис. ниже).
Если же Вы хотите использовать оператор сравнения «равно», чтобы запись условия имела более привычный для Вас вид, необходимо учесть, что любая запись в Excel, начинающаяся с символа «=», воспринимается приложением как формула. Поэтому условие =Значение необходимо записывать в ячейку таблицы условий именно как формулу: ="=Значение". После ввода данной формулы в ячейку и нажатия клавиши Enter, запись условия будет примет привычный для Вас вид, и лишь в строке формул будет отображаться введённая формула (см. рис. ниже).
Использование подстановочных символов
Как и в автофильтре, в расширенном фильтре для отбора по значениям строкового типа можно использовать подстановочные символы «*» и «?», первый из которых подменяет последовательность любых символов, второй — один любой символ. Например, чтобы из списка товаров отобрать все строки, содержащие слово «Samsung», в соответствующей ячейке таблицы условий необходимо указать строку «*Samsung*».
Если же в искомой строке тоже используются символы «*» и «?», то при записи условия каждый такой символ, не являющийся подстановочным, следует предварять символом «~» («тильда»). Например, чтобы отобрать строки, где в названиях объектов используется символ «звёздочка», в соответствующей ячейке таблицы условий необходимо указать строку «*~**».
Если же в искомой строке тоже используется символ «~», то при записи условия каждый такой символ, не являющийся подстановочным, следует удваивать. Например, чтобы отобрать строки, где в названиях объектов используется символ «тильда», в соответствующей ячейке таблицы условий необходимо указать строку «*~~*».
Другие статьи по схожей тематике
