Фильтр - это быстрый и легкий способ поиска подмножества данных и работы с ними в списке. В отфильтрованном списке отображаются только строки, отвечающие условиям. В отличие от сортировки, фильтр не меняет порядок записей в списке. При фильтрации временно скрываются строки, которые не требуется отображать .
Строки, отобранные при фильтрации, можно редактировать, форматировать, создавать на их основе диаграммы, выводить их на печать, не изменяя порядок строк и не перемещая их.
При фильтрации выбираются только необходимые данные, а остальные данные скрываются. Таким образом, отображается только то, что вы хотите увидеть, и это можно сделать одним щелчком.
При фильтрации данные никак не изменяются. Как только фильтр удален, все данные появляются снова в том же виде, в каком они были до применения фильтра.
В Excel доступны две команды для фильтрации списков:
- Автофильтр , включая фильтр по выделенному, для простых условий отбора.
- Расширенный фильтр для более сложных условий отбора.
Автофильтр
Для включения Автофильтра нужно выделить любую ячейку в таблице, затем на вкладке Данные в группе Сортировка и фильтр нажать большую кнопку :
После этого в шапке таблицы справа от каждого заголовка столбца появится кнопка со стрелкой вниз :
Щелчок по стрелке открывает меню со списком для соответствующего столбца. В списке содержатся все элементы столбца в алфавитном или числовом порядке (в зависимости от типа данных), так что можно быстро найти требуемый элемент:
Если нам нужен фильтр только для одного столбца, то можно не выводить кнопки со стрелкой для остальных столбцов. Для этого перед нажатием кнопки выделяем несколько ячеек нужного столбца вместе с заголовком.
Фильтрация по точному значению
Включаем Автофильтр , щелкаем по кнопке со стрелкой и выбираем из раскрывшегося списка какое-нибудь значение. Для того, чтобы быстро выделить все элементы столбца или снять выделение со всех элементов, щелкните по пункту (Выделить все) :
При этом все строки, в поле которых не содержится выбранное значение, скрываются.
При выполнении лабораторной работы, выделяем результат фильтрации, копируем на другое место листа и подписываем.
Для выключения Автофильтра нужно еще раз нажать кнопку .
Для отмены действия фильтра, не выходя из режима фильтрации, щелкаем по кнопке и выбираем из раскрывшегося списка пункт (Выделить все) . При этом появляются скрытые фильтром строки таблицы.
Признаки фильтрации данных
Фильтры скрывают данные. Именно для этого они и предназначены. Однако, если о фильтрации данных не известно, может возникнуть впечатление, что некоторые данные пропущены. Вы могли, например, открыть чей-нибудь отфильтрованный лист или даже забыть, что сами ранее применили фильтр. Поэтому когда на листе есть фильтры, можно обнаружить различные визуальные указатели и сообщения.
(находится слева внизу окна). Исходное состояние:
Сразу после фильтрации данных итог применения фильтра отображаются в левом нижнем углу строки состояния. Например, “Найдено записей: 2 из 11 ”:
Номера строк . По прерывистым номерам строк можно сказать, что некоторые строки скрыты, а изменившийся цвет номеров видимых строк указывает на то, что выделенные строки являются результатом отбора фильтра.
Вид стрелок . Изменение стрелки автофильтра в отфильтрованном столбце на указывает на то, что данный столбец отфильтрован.
”
“” - это еще один универсальный фильтр, который можно применять к столбцам с числами или датами.
“” - это очень условное название. На самом деле возможности этого фильтра гораздо шире. С помощью этого фильтра можно находить или первые элементы или последние элементы (наименьшие или наибольшие числа либо даты). И, вопреки названию фильтра, получаемые результаты не ограничиваются первыми 10 элементами или последними 10 элементами. Число отображаемых элементов можно выбирать от 1 до 500.
” позволяет также отбирать данные по проценту от общего числа строк в столбце. Если в столбце содержится 100 чисел и требуется просмотреть наибольшие пятнадцать, то выбираем 15 процентов.
Фильтр можно использовать для нахождения продуктов с наибольшими или наименьшими ценами, для определения списка сотрудников, нанятых последними по времени, или для просмотра списка студентов с наилучшими или наихудшими отметками. Чтобы применить фильтр “” к столбцу данных (только числа или даты!!! ), щелкаем в столбце стрелку и выбираем пункт Числовые фильтры далее :
После этого откроется диалоговое окно Наложение условия по списку :
В диалоговом окне выбираем число (строк или процентов), наибольших или наименьших , элементов списка или % от количества элементов .
Создание собственных настраиваемых фильтров
Например, нам нужно вывести только строки с должностями, начинающимися с буквы ‘Д ’. Для этого щелкаем по стрелке автофильтра в первом столбце и выбираем Текстовые фильтры , затем пункт начинается с… :
При этом появится диалоговое окно (какой бы пункт справа вы бы ни выбирали, все равно появится одно и то же диалоговое окно.):
В поле Должность выбираем – начинается с , справа вводим д :
В окне есть подсказка:
Знак вопроса “? ” обозначает один любой знак.
Знак “* ” обозначает последовательность любых знаков.
Выбрать из списка нужные данные можно при помощи фильтрации, то есть путем сокрытия всех строк списка, кроме тех, которые удовлетворяют заданным критериям. Чтобы воспользоваться функцией фильтрации, необходимо установить табличный курсор на одну из ячеек заголовка списка (в нашей таблице это диапазон А1:U11) и вызвать команду Данные/Фильтр/Автофильтр . После ее активизации в правом нижнем углу каждой ячейки заголовка появится маленький квадратик со стрелкой раскрывающегося списка.
Рассмотрим приемы работы с автофильтром на следующем примере. Давайте определим, сколько представителей сильного пола работает на предприятии. Нажмите кнопку фильтрации, расположенную в ячейке с заголовком Пол, и в открывшемся списке выберите букву М (мужчина). В строке состояния появится сообщение Фильтр: отбор (рис. 4.20). Все строки, которые не удовлетворяют заданному критерию, будут скрыты. Стрелка на кнопке списка окрасится в синий цвет, указывая на то, что для данного поля включен автофильтр.
Рис. 4.20. Использование автофильтра для отбора записей по признаку "М" (мужчина)
Если же требуется уточнить, сколько среди этих мужчин начальников, нажмите также кнопку автофильтра в ячейке Должность и выберите в соответствующем ей списке слово Начальник. В строке состояния появится сообщение о том, сколько строк удовлетворяет заданному критерию: Найдено записей: 2 из 10 (то есть ответ будет дан сразу же). Результат изображен на рис. 4.21.
Чтобы отменить фильтрацию по определенному столбцу, достаточно открыть список автофильтра в этом столбце и выбрать пункт Все. Однако если функция фильтрации задана для нескольких столбцов, вам придется повторить эту операцию несколько раз. В этом случае лучше воспользоваться командой Данные/Фильтр/Отобразить все .
Рис. 4.21.
Рабочий лист после фильтрации списка сотрудников по критерию "мужчина - начальник"
Функция фильтрации будет работать как следует, если вы будете внимательными при занесении данных. В частности, нужно следить за тем, чтобы в начале и в конце текстовых данных не было лишних пробелов. На экране они не заметны, но могут привести к ошибочным результатам, а на их выявление тратится много времени.
При фильтрации выполняется отбор данных, точно отвечающих заданному критерию. Поэтому, если вместо слова "Начальник" с столбце встречается слово "Начальник_", то есть с пробелом в конце, Excel воспринимает эти значения как разные. Чтобы избавиться от несоответствий такого рода, скопируйте в буфер обмена ячейку со словом "Начальник", активизируйте фильтр для выборки по признаку "Начальник_" и замените неправильные значения содержимым буфера.
Что делать, если из большого объема информации вам нужно найти нужные данные, относящийся к определенному человеку или дате? А затем, к примеру, скопировать на отдельный лист? Проще всего сделать автофильтр в Excel. Особенность работы фильтра — скрыть не удовлетворяющий вашим требованиям объем информации и оставить только запрашиваемые фильтром данные. Выводимые фильтром строки можно форматировать, распечатывать, редактировать и т.д.
Так же вы когда-нибудь применяли расширенный фильтр? Довольно удобный инструмент!
Итак, как сделать автофильтр в Excel (отобрать только нужные ячейки)?
Выделить нужные ячейки с конкретным значением или по цвету, или по условию можно двумя простыми способами:
1. Пройдя по пути на ленте “Данные” — “Сортировка и Фильтр” — “Автофильтр” (см. картинку вначале статьи )
2. Нажатием правой кнопки мыши после выделения таблицы, в открывшемся меню фильтр — “Фильтр”
Основные условия использования автофильтра:
- Возможность работы только с целостным списком. Т.е. если списка два, то фильтр применить не получится.
- Вы можете отфильтровать по двум столбцам. Но сначала необходимо отфильтровать один, затем второй.
- Фильтровать данные можно так же по цвету или по содержанию какой-то части фразы.
- С числовыми значениями можно делать очень широкий спектр задач
Расширенный фильтр. Как сделать фильтр по условию в ячейке
Расширенный фильтр применяется для более сложных условий отбора. Воспользуйтесь им, если
— нужно задать большое число условий сразу
— нужно вывести получивший список в отдельное место.
Суть расширенного фильтра в том, что в соседней таблице (т2) вы указываете все условия фильтрации (т1) и выводите их в новую таблицу т3.
Прежде чем работать с расширенным автофильтром, вам необходимо будет подготовить таблицу. Т.е. заголовки в имеющейся таблице перенести в отдельное место и в отдельном столб
Для того, чтобы его найти откройте вкладку «Данные», раздел «Сортировка и Фильтр, нажмите кнопку «Дополнительно», в открывшемся окне заполните исходный диапазон (если нужно).
Диапазон условий нужно тоже заполнить (т2). Если необходимо скопировать в другое место, выделите соответствующую галочку. Вторая таблица — это как раз таблица 2 — таблица условий, третья — то что получиться.
Фильтрация данных в списке - это выбор данных по заданному критерию, т.е. это операция, которая позволяет выделить нужные данные среди имеющихся.
С помощью фильтров можно выводить и просматривать только те данные, которые удовлетворяют определенным условиям. Excel позволяет быстро и удобно просматривать требуемые данные из списка с помощью простой команды - «Автофильтрация». Более сложные запросы к базе данных можно реализовать при помощи команды «Расширенный фильтр».
Автофильтрация
Для того, чтобы произвести автофильтрацию, необходимо изначально скопировать на новый лист «Автофильтрация» исходную базу данных с листа «Подсчет данных по формулам». Затем установим курсор в область списка и выполним команду «Данные» - «Фильтр» - «Автофильтр». По этой команде Excel помещает раскрывающиеся списки непосредственно в имена столбцов списка. Щелкнув по стрелке, можно ознакомиться со списком возможных критериев выбора. Если кнопка была использована для назначения фильтра, то стрелка окрашивается в синий цвет. Существуют следующие варианты списка критериев:
· «Все» - выбираются все записи;
· «Первые 10» - в диалоговом окне «Наложение условия по списку» выбрать определенное кол-во наименьших или наибольших элементов списка, которое необходимо отобразить;
· «Значения» - будут выбраны только те записи, которые в данном столбце создают указанное значение;
· «Условие» - выбираются записи по формируемому пользователем условию в диалоговом окне «Пользовательский автофильтр»;
· «Пустые» - предъявляются строки, не содержащие данные в колонке;
· «Непустые» - предъявляются только те записи, которые содержат непустые строки в колонке.
В данном случае необходимо сформировать следующие условия для проведения операции «Автофильтрация»: для поля «Льготы» нужно задать значение «Ветеран или Инвалид», а для поля «Количество членов семьи» нужно задать условие - «Больше или равно 3»». В соответствии с тем, что одновременно установлены фильтры в двух столбцах, то фильтрация записей будет выполнена по двум условиям одновременно, то есть в итоге будут отобрана льготы Ветеран и Инвалид, количество членов семьи которых больше или равно 3. В итоге были найдены квартиросъемщики, которые удовлетворяют вышеизложенным условиям. Данный результат представлен на рисунке Таблица 4 «Автофильтрация».
Расширенный фильтр
Фильтрация с помощью расширенного фильтра осуществляется с помощью команды: «Данные» - «Фильтр» - «Расширенный фильтр».
Для использования команды «Расширенный фильтр», надо сначала создать таблицу критериев, которую далее разместим на том же рабочем листе «Расширенный фильтр», что и исходная таблица «Подсчет данных по формулам», но так, чтобы не скрывать лист во время фильтрации.
В «Расширенном фильтре» также как и в «Автофильтре» существует несколько вариантов видов критерия, такие как:
Критерий сравнения включает операции следующего типа:
· точного значения;
· значения, формируемого с помощью операторов отношения;
· шаблона значения, включающего символы или
Множественный критерий - критерий, формируемый в нескольких столбцах.
· Если критерии указываются в каждом столбце на одной строке, то они считаются связанными условием И.
· Если критерии записаны в нескольких строках, то они считаются связанными условием ИЛИ.
Вычисляемый критерий - представляет собой формулу, записанную в строке области условий, которая возвращает логическое значение «ИСТИНА» или «ЛОЖЬ».
Фильтрация {выборка) данных в таблице позволяет отображать только те строки, содержимое ячеек которых отвечает заданному условию или нескольким условиям. С помощью фильтров пользователь может в удобной для себя форме выводить или удалять (скрывать) записи списка.
В отличие от сортировки данные при фильтрации не переупорядочиваются, а лишь скрываются те записи, которые не отвечают заданным критериям выборки.
Отобранные записи можно форматировать или удалять, копировать в отдельную область таблицы, распечатывать, а также использовать для последующих вычислений или построения диаграмм.
Фильтрация данных в электронных таблицах может выполняться двумя способами: с помощью автофильтра или расширенного фильтра.
Фильтрация данных с использованием автофильтра. Для выполнения этой операции нужно:
1) установить курсор внутри таблицы;
2) ввести команду меню ДАННЫЕÞФильтрÞАвтофильтр;
3) щелчком мыши по кнопке со стрелкой раскрыть список столбца, по которому будет производиться выборка;
4) указать требуемые значения или выбрать строку "условие" и задать критерии выборки в диалоговом окне Пользовательский автофильтр.
Условия для отбора записей в определенном столбце могут состоять из двух самостоятельных частей, соединенных логической связкой И/ИЛИ.
Каждая часть условия может включать:
· значение, которое может выбираться из списка или содержать шаблонные символы подстановки. В качестве символов подстановки используются звездочка * - для указания произвольного количества символов или вопросительный знак? - для замены одного символа;
· оператор отношения (сравнения). При задании критериев выборки могут использоваться следующие операторы сравнения:
Равно <> Не равно
< Меньше < = Меньше или равно
> Больше > = Больше или равно
Для восстановления всех строк исходной таблицы нужно щелкнуть мышью по кнопке со стрелкой (синего цвета) и в раскрывшемся списке выбрать строку "все" или выполнить команду ДАННЫЕÞФильтрсÞОтобразить все.
Для отмены режима фильтрации нужно установить курсор внутри таблицы и снова ввести команду меню ДАННЫЕÞФильтр ÞАвтофильтр (убрать переключатель).
Фильтрация данных с использованием расширенного фильтра. Расширенный фильтр позволяет формировать множественные критерии выборки и осуществлять более сложную фильтрацию данных электронной таблицы с заданием набора условий отбора по нескольким столбцам.
Для фильтрации записей списка расширенный фильтр обеспечивает использование двух типов критериев:
· критериев сравнения;
· вычисляемых критериев.
Фильтрация записей с использованием расширенного фильтра выполняется с помощью команды меню ДАННЫЕÞФильтрÞРасширенный фильтр.
Важной особенностью этого режима является то, что до выполнения самой команды фильтрации необходимо сформировать специальную область для задания условий фильтрации данных -диапазон условий отбора (интервал критериев).
Диапазон условий должен содержать строку с заголовками столбцов и несколько строк для задания условий отбора. Обычно для создания диапазона условий вначале копируют в отдельное место (на другом или на том же самом рабочем листе - как правило, выше исходной таблицы) строку с заголовками столбцов, затем в расположенные ниже строки вводят критерии выборки по отдельным столбцам.
Между значениями условий отбора и таблицей должна находиться, как минимум, одна пустая строка.
Если критерии отбора (условия фильтрации) вводятся в одной строке для разных столбцов, то они считаются связанными условием "И". Если критерии отбора записываются в разных строках, то они считаются связанными условием "ИЛИ".
После формирования диапазона условий с критериями выборки записей устанавливают курсор внутри таблицы, вводят команду ДАННЫЕÞФильтрÞРасширенный фильтр и в диалоговом окне Расширенный фильтр указывают диапазон ячеек таблицы и адрес или имя сформированного заранее диапазона условий.
Записи можно фильтровать на месте или одновременно с выполнением фильтрации копировать в указанную область на текущем рабочем листе.
Чтобы скопировать отфильтрованные строки в другую область листа, следует установить переключатель Скопировать результаты в другое место, перейти в поле Поместить результат в диапазон и указать верхнюю левую ячейку области вставки отобранных данных.