Фильтрация — это быстрый и легкий способ поиска подмножества данных и работы с ними в списке (Список. Набор строк листа, содержащий однородные данные, например база данных счетов или набор адресов и телефонов клиентов. Первая строка в этом списке содержит заголовки столбцов.). В отфильтрованном списке отображаются только строки, отвечающие условиям отбора (Условие. Ограничение, заданное для отбора записей, включаемых в список результатов запроса. Например, следующее условие отбирает только записи, значение поля «Сумма заказа» которого больше 30 000: Сумма по доходу > 30000), заданным для столбца. Microsoft Excel предоставляет две команды для фильтрации списков:

Автофильтр, включая фильтр по выделенному, для простых условий отбора;
Расширенный фильтр для более сложных условий отбора.

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

Строки, отобранные при фильтрации в Microsoft Excel, можно редактировать, форматировать и выводить на печать, а также создавать на их основе диаграммы, не изменяя порядок строк и не перемещая их.

Автофильтр

При использовании команды Автофильтр справа от подписей столбцов в фильтруемом списке появляются стрелки автофильтра .

1 --- Неотфильтрованный список

2 --- Фильтрованный список

Microsoft Excel обозначает отфильтрованные элементы голубым цветом

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

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

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

Примеры сложных условий отбора

Несколько условий для одного столбца

При наличии для одного столбца двух и более условий отбора введите эти условия отбора непосредственно друг под другом в отдельные строки. Например, следующий диапазон условий отбора отбирает строки, содержащие в столбце «Продавец» значения «Белов», «Батурин» или «Рощин».

Продавец
Белов
Батурин
Рощин

Одно условие для нескольких столбцов

Для того чтобы найти в нескольких столбцах данные, отвечающие одному условию отбора, введите все условия отбора в одну строку диапазона условий отбора. Например, следующий диапазон условий отбора возвращает все строки, содержащие значения «Продукты» в столбце «Товар», «Белов» в столбце «Продавец» и объем продаж более 1 000 руб.

Тип	Продавец	Продажи
Фрукты	Белов	>1 000

Разные условия для разных столбцов

Для того чтобы найти данные, отвечающие одному условию, в одном столбце, или отвечающие другому условию, в другом столбце, введите условия отбора в разные строки диапазона условий отбора. Например, следующий диапазон условий отбора отображает все строки, содержащие значение «Продукты» в столбце «Товар», «Белов» в столбце «Продавец», либо объем продаж, превышающий 1 000 руб.

Тип	Продавец	Продажи
Фрукты
	Белов
		>1 000

Один из двух наборов условий для двух столбцов

Для того чтобы найти строки, отвечающие одному из двух наборов условий, каждый из которых содержит условия более чем для одного столбца, введите эти условия отбора в отдельные строки. Например, следующий диапазон условий отбора отображает строки, содержащие как значение «Белов» в столбце «Продавец», так и объем продаж, превышающий 3 000 руб., а также строки по продавцу Батурину с объемами продаж более 1 500 руб.

Продавец	Продажи
Белов	>3 000
Батурин	>1 500

Более двух наборов условий для одного столбца

Для того чтобы найти строки, отвечающие более чем двум наборам условий, включите несколько столбцов с одинаковыми заголовками. Например, следующий диапазон условий отбора возвращает продаж на сумму от 5 000 до 8 000, а также продаж на сумму менее 500.

Продажи	Продажи
>5 000	<8 000
<500

Условия, создаваемые как результат выполнения формулы

В качестве условия отбора можно использовать вычисляемое значение, являющееся результатом выполнения формулы. При создании условия отбора с помощью формулы не используйте заголовок столбца в качестве заголовка условия; либо оставьте условие отбора без заголовка, либо используйте заголовок, не являющийся заголовком столбца в списке. Например, следующий диапазон условий отбора отображает строки, которые содержат в столбце C значение, превышающее среднее значение ячеек диапазона C7:C10.


=C7>СРЕДНЕЕ($C$7:$C$10)

Примечания

Формула, используемая для создания условия отбора, должна использовать для ссылки на подпись столбца (например, «Продажи») или на соответствующее поле в первой записи относительную ссылку. Все остальные ссылки в формуле должны быть абсолютными, а формула должна возвращать результат ИСТИНА или ЛОЖЬ. В примере формулы «C7» является ссылкой на поле (столбец C) первой записи (строка 7) списка.
При использовании заголовка столбца в формуле условия вместо ссылки или имени диапазона в ячейке будет выведено значение ошибки #ИМЯ? или #ЗНАЧ!. Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации.
При вычислениях Microsoft Excel не учитывает регистр букв.

Фильтрование списка

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

Выберите ячейку в списке, который требуется отфильтровать.

В меню Данные укажите на пункт Фильтр, а затем выберите команду Автофильтр.

Отбор по наименьшему или наибольшему значению

В столбце, содержащем числа, нажмите кнопку со стрелкой и выберите вариант (Первые 10...).
В среднем поле выберите вариант наибольших или наименьших.
В поле слева введите количество.
В поле справа выберите вариант элементов списка.

Отбор строк списка, содержащих определенный текст

В столбце, содержащем числа, нажмите кнопку со стрелкой и выберите вариант (Условие...).
В поле слева выберите вариант равно, не равно, содержит или не содержит.
В поле справа введите искомый текст.

Чтобы найти текстовые строки, содержащие несколько последовательностей определенных знаков, используйте подстановочные знаки.

Инструкции

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

Используйте	Чтобы найти
? (знак вопроса)	Один любой знак Пример: условию «бар?н» соответствуют результаты «барин» и «барон»
* (звездочка)	Любое количество символов Пример: условию «*-восток» соответствуют результаты «северо-восток» и «юго-восток»
~ (тильда), за которой следует ?, * или ~	Вопросительный знак, звездочку или тильду. Пример: условию «ан91~?» соответствует результат «ан91?»

Чтобы добавить дополнительное условие, выберите вариант И или ИЛИ и повторите предыдущий шаг.

Отбор пустых ячеек или ячеек со значениями

В столбце, содержащем числа, нажмите кнопку со стрелкой и выберите вариант (Пустые) или (Непустые).

Примечание. Условия Пустые и Непустые доступны, только если в столбце содержатся пустые ячейки.

Отбор чисел, которые больше или меньше указанного значения

В столбце, содержащем числа, нажмите кнопку со стрелкой и выберите вариант (Условие...).
В поле слева выберите условие больше, меньше, больше или равно или меньше или равно.
В поле справа введите значение.
Чтобы добавить дополнительное условие, выберите вариант И или ИЛИ и повторите предыдущий шаг.

Отбор чисел, которые равны или не равны указанному значению

В столбце, содержащем числа, нажмите кнопку со стрелкой и выберите вариант (Условие...).
В поле слева выберите вариант равно или не равно.
В поле справа введите значение.
Чтобы добавить дополнительное условие, выберите вариант И или ИЛИ и повторите предыдущий шаг.

Отбор текстовых строк по их началу или концу

В столбце, содержащем числа, нажмите кнопку со стрелкой и выберите вариант (Условие...).
В поле слева выберите вариант начинается с, не начинается с, заканчивается на или не заканчивается на.
В поле справа введите искомый текст.

Инструкции

Используйте	Чтобы найти
? (знак вопроса)	Один любой знак Пример: условию «бар?н» соответствуют результаты «барин» и «барон»
* (звездочка)	Любое количество символов Пример: условию «*-восток» соответствуют результаты «северо-восток» и «юго-восток»
~ (тильда), за которой следует ?, * или ~	Вопросительный знак, звездочку или тильду. Пример: условию «ан91~?» соответствует результат «ан91?»

Чтобы добавить дополнительное условие, выберите вариант И или ИЛИ и повторите предыдущий шаг.

Отбор наибольших или наименьших чисел в процентном соотношении

В столбце, содержащем числа, нажмите кнопку со стрелкой и выберите вариант (Первые 10...).
В среднем поле выберите вариант наибольших или наименьших.
В поле слева введите количество.
В поле справа выберите вариант % от количества элементов.

Примечания

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

Отбор строк с помощью расширенного фильтра

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

Как это может выглядеть

Тип	Продавец	Продажи

Тип	Продавец	Продажи
Напитки	Рощин	5122
Мясо	Белов	450
Фрукты	Батурин	6328
Фрукты	Белов	6544

Введите в строки под заголовками столбцов требуемые критерии отбора.

Примеры условий

Несколько условий для одного столбца

Продавец
Белов
Батурин
Рощин

Одно условие для нескольких столбцов

Тип	Продавец	Продажи
Фрукты	Белов	>1 000

Разные условия для разных столбцов

Тип	Продавец	Продажи
Фрукты
	Белов
		>1 000

Один из двух наборов условий для двух столбцов

Продавец	Продажи
Белов	>3 000
Батурин	>1 500

Более двух наборов условий для одного столбца

Продажи	Продажи
>5 000	<8 000
<500

Условия, создаваемые как результат выполнения формулы


=C7>СРЕДНЕЕ($C$7:$C$10)

Примечания

Формула, используемая для создания условия отбора, должна использовать для ссылки на подпись столбца (например, «Продажи») или на соответствующее поле в первой записи относительную ссылку. Все остальные ссылки в формуле должны быть абсолютными, а формула должна возвращать результат ИСТИНА или ЛОЖЬ. В примере формулы «C7» является ссылкой на поле (столбец C) первой записи (строка 7) списка.
При использовании заголовка столбца в формуле условия вместо ссылки или имени диапазона в ячейке будет выведено значение ошибки #ИМЯ? или #ЗНАЧ!. Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации.
При вычислениях Microsoft Excel не учитывает регистр букв.

Будут отобраны все строки с ячейками, содержащими текст, начинающийся с заданной последовательности знаков. Например, при вводе условия Бел будут отобраны строки с ячейками, содержащими слова Белов, Беляков и Белугин. Чтобы получить точное соответствие отобранных значений заданному образцу, например текст, следует ввести следующую формулу:="=текст"

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

Подстановочные знаки

Используйте	Чтобы найти
? (знак вопроса)	Один любой знак Пример: условию «бар?н» соответствуют результаты «барин» и «барон»
* (звездочка)	Любое количество символов Пример: условию «*-восток» соответствуют результаты «северо-восток» и «юго-восток»
~ (тильда), за которой следует ?, * или ~	Вопросительный знак, звездочку или тильду. Пример: условию «ан91~?» соответствует результат «ан91?»

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

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

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

Фильтр для уникальных записей

Выберите столбец или ячейку в списке, который требуется отфильтровать.
В меню Данные выберите команду Фильтр, а затем — команду Расширенный фильтр.
Выполните одно из следующих действий:
- чтобы отфильтровать список на месте, как и при использовании функции Автофильтр, выберите вариант фильтровать список на месте;
- чтобы скопировать результат действия фильтра в другое место, выберите вариант скопировать результат в другое место и введите в поле Поместить результат в диапазон ссылку на ячейки.
  
  Чтобы выделить ячейку, нажмите кнопку свертывания диалогового окна , чтобы временно скрыть его. Выделите ячейку на листе и нажмите кнопку развертывания диалогового окна .
Установите флажок Только уникальные записи.

Поиск записи с использованием формы данных

Чтобы переместиться на одну запись, нажмите стрелку полосы прокрутки диалогового окна. Чтобы переместиться на 10 записей, щелкните полосу прокрутки между стрелками.
Чтобы переместиться к следующей записи в списке, нажмите кнопку Далее. Чтобы переместиться к предыдущей записи в списке, нажмите кнопку Назад.
Чтобы задать условия поиска или условия сравнения, нажмите кнопку Критерии и введите условия в форму. Чтобы найти записи, соответствующие этим условиям, нажимайте кнопки Далее и Назад. Чтобы вернуться к форме, нажмите кнопку Правка.

Подстановочные знаки, используемые в условиях

Используйте	Чтобы найти
? (знак вопроса)	Один любой знак Пример: условию «бар?н» соответствуют результаты «барин» и «барон»
* (звездочка)	Любое количество символов Пример: условию «*-восток» соответствуют результаты «северо-восток» и «юго-восток»
~ (тильда), за которой следует ?, * или ~	Вопросительный знак, звездочку или тильду. Пример: условию «ан91~?» соответствует результат «ан91?»

Удаление фильтров

Чтобы удалить фильтр для одного столбца списка, нажмите кнопку со стрелкой рядом со столбцом, а затем нажмите кнопку Все.
Чтобы удалить фильтры для всех столбцов списка, выберите пункт Фильтр в меню Данные, а затем — команду Показать все.
Чтобы удалить автофильтр из списка, выберите пункт Фильтр в меню Данные, а затем — команду Автофильтр.

Подстановочные знаки

Используйте	Чтобы найти
? (знак вопроса)	Один любой знак Пример: условию «бар?н» соответствуют результаты «барин» и «барон»
* (звездочка)	Любое количество символов Пример: условию «*-восток» соответствуют результаты «северо-восток» и «юго-восток»
~ (тильда), за которой следует ?, * или ~	Вопросительный знак, звездочку или тильду. Пример: условию «ан91~?» соответствует результат «ан91?»

Фильтрация

Фильтрование списка

Отбор строк с помощью расширенного фильтра

Фильтр для уникальных записей

Поиск записи с использованием формы данных

Рекомендации по созданию списка на листе книги

Удаление фильтров

Подстановочные знаки