Фильтрация данных с помощью автофильтра
Используйте функцию автофильтра для поиска, отображения или скрытия значений в одном или нескольких столбцах данных. Вы можете отфильтровать выбор из списка или выполнить поиск, чтобы найти нужные данные. При фильтрации данных целые строки будут скрыты, если значения в одном или нескольких столбцах не соответствуют критериям фильтрации.
Чтобы применить автофильтр, выполните следующие действия.
- Выделите данные, которые нужно отфильтровать.
- Щелкните Данные >Фильтр.
- Щелкните стрелку
в заголовке столбца и решите, хотите ли вы выбрать определенные значения или выполнить поиск. 
- Выберите определенные значения: Снимите флажок (Выбрать все), чтобы очистить все поля проверка, а затем проверка поля для конкретных значений, которые вы хотите увидеть.
- Поиск значений: В поле Поиск введите текст или числа, которые вы ищете.
- Чтобы применить фильтр, нажмите кнопку ОК.
Совет: Если вы хотите применить к данным предопределенный или настраиваемый фильтр, см. статью Фильтрация данных в диапазоне или таблице.
Советы для расширения области поиска
При поиске данных можно использовать «?» для представления любого отдельного символа, или «*» для представления ряда символов.
Например, чтобы найти все элементы, относящиеся к велосипедам, в поле Поиск введите слово»велосипеды». В фильтре отображаются все элементы со словом «велосипеды», такие как велосипеды, дорожные велосипеды и горные велосипеды.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Краткое руководство: фильтрация данных с помощью автофильтра
Фильтруя данные на листе, можно быстро найти нужные значения. Возможна фильтрация по одному или по нескольким столбцам данных. При фильтрации можно указывать не только то, что должно отображаться, но и то, что следует исключить. Можно задать фильтрацию на основе выбранных в списке параметров или создать специальные фильтры, которые нацелены именно на нужные вам данные.
При фильтрации можно проводить поиск текста и чисел, используя поле Поиск в интерфейсе фильтра.
Если при фильтрации данных значения в одном или нескольких столбцах не удовлетворяют условиям фильтрации, такие строки будут скрыты целиком. Можно применить фильтр по числовым и текстовым значениям или же по цвету ячеек, в которых для фона или текста задано форматирование цветом.
Процедура
Выделите данные, которые требуется отфильтровать.
- На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Фильтр.
- Щелкните стрелку в заголовке столбца, чтобы отобразить список, в котором можно выбрать значения фильтра. Примечание. В зависимости от типа данных в столбце Microsoft Excel отображает в списке числовые фильтры или текстовые фильтры.
Фильтрация выбором значений или поиском
Выбор значения из списка и поиск — самый быстрый способ фильтрации. Если щелкнуть стрелку в столбце, в котором включена фильтрация, в списке будут показаны все значения, присутствующие в этом столбце.
1. Используйте поле Поиск для ввода текста или чисел, которые следует найти
2. Установите и снимите флажки для отображения значений, найденных в столбце данных
3. Используйте расширенные критерии для поиска значений, которые соответствуют определенным условиям
- Чтобы выбрать значения для списка, снимите флажок (Выделить все). При этом снимаются все флажки. Затем выберите только те значения, которые хотите видеть, и нажмите кнопку ОК, чтобы посмотреть результаты.
- Чтобы найти текст в столбце, введите текст или числа в поле Поиск. Также можно использовать подстановочные знаки, например звездочку (*) или знак вопроса (?). Нажмите клавишу ВВОД, чтобы получить результаты.
Фильтрация данных с указанием условий
Указывая условия, вы можете создавать собственные фильтры, которые отбирают данные именно так, как вы хотите. Для этого применяется конструирование фильтров. Оно должно быть знакомо вам, если вы когда-нибудь делали запросы к базе данных.
- Наведите указатель мыши на числовые фильтры или текстовые фильтры в списке. Откроется меню, в котором можно задавать фильтрацию по различным условиям.
- Выберите условие, а затем выберите или введите критерии. Нажмите кнопку И кнопка для сочетания критериев (то есть два или более критериев, все из которых должны быть выполнены), или кнопку Или, чтобы потребовать выполнения только одного из критериев.
- Нажмите кнопку OK, чтобы применить фильтр и получить ожидаемые результаты.
Дальнейшие действия
- Поэкспериментируйте с фильтрами над текстовыми и числовыми данными — примените различные встроенные условия проверки, такие как равно, не равно, содержит, больше, и меньше. Дополнительные сведения см. в разделе Фильтрация данных в диапазоне или таблице. Примечание. Некоторые из этих условий применяются только к тексту, а другие — только к числам.
- Создайте свой фильтр, использующий несколько критериев. Дополнительные сведения см. в статье Фильтрация с помощью расширенных критериев.
- Узнайте, как использовать Фильтр уникальных значений или удаление повторяющихся значений.
Фильтрация данных в диапазоне или таблице
Используйте автофильтр или встроенные операторы сравнения, такие как «больше» и «первые 10» в Excel, чтобы показать нужные данные и скрыть остальные. После фильтрации данных в диапазоне ячеек или таблицы можно повторно применить фильтр, чтобы получить актуальные результаты, или очистить фильтр для повторного отображения всех данных.
Используйте фильтры, чтобы временно скрывать некоторые данные в таблице и видеть только те, которые вы хотите.
Фильтрация диапазона данных
- Выберите любую ячейку в диапазоне данных.
- Выберите Фильтр>данных .
- Щелкните стрелку в заголовке столбца.
- Выберите Текстовые фильтры или Числовые фильтры, а затем выберите сравнение, например Между.
- Введите условия фильтрации и нажмите кнопку ОК.
Фильтрация данных в таблице
При вводе данных в таблицу в заголовки ее столбцов автоматически добавляются элементы управления фильтрацией.
- Щелкните стрелку в заголовке столбца, содержимое которого вы хотите отфильтровать.
- Снимите флажок (Выберите все) и выберите поля, которые нужно отобразить.
- Нажмите кнопку ОК. Стрелка заголовка столбца меняется на значок фильтра . Щелкните этот значок, чтобы изменить или очистить фильтр.
Статьи по теме
Отфильтрованные данные отображают только те строки, которые соответствуют указанным условия, а также скрывают строки, которые не нужно отображать. После фильтрации данных можно копировать, находить, редактировать, форматировать, диаграммы и печатать подмножество отфильтрованных данных без их переупорядочения или перемещения.
Вы также можете фильтровать по нескольким столбцам. Фильтры являются аддитивными, что означает, что каждый дополнительный фильтр основан на текущем фильтре и еще больше сокращает подмножество данных.
Примечание: При использовании диалогового окна Найти для поиска отфильтрованных данных выполняется поиск только для отображаемых данных; данные, которые не отображаются, не выполняется поиск. Чтобы выполнить поиск по всем данным, очистите все фильтры.
Дополнительные сведения о фильтрации
Два типа фильтров
С помощью автофильтра можно создать два типа фильтров: по значению списка или по условиям. Каждый из этих типов фильтров является взаимоисключающим для каждого диапазона ячеек или таблицы столбцов. Например, можно фильтровать по списку чисел или критерию, но не по обоим; Вы можете фильтровать по значку или по пользовательскому фильтру, но не по обоим.
Повторное применение фильтра
Чтобы определить, применяется ли фильтр, обратите внимание на значок в заголовке столбца:
- Стрелка раскрывающегося списка означает, что фильтрация включена, но не применяется. При наведении указателя мыши на заголовок столбца с включенной, но не примененной фильтрацией, на экране отображается подсказка «(Отображение всех)».
- Кнопка «Фильтр» означает, что применяется фильтр. При наведении указателя мыши на заголовок отфильтрованного столбца на экране отображается фильтр, примененный к нему, например «Равно красному цвету ячейки» или «Больше 150».
При повторном использовании фильтра результаты отображаются по следующим причинам:
- Данные были добавлены, изменены или удалены в диапазон ячеек или столбца таблицы.
- значения, возвращаемые формулой, изменились, и лист был пересчитан.
Не смешивать типы данных
Для достижения наилучших результатов не следует смешивать типы данных, такие как текст и число, а также число и дата в одном столбце, так как для каждого столбца доступен только один тип команды фильтра. Если существует сочетание типов данных, отображаемая команда является типом данных, который встречается чаще всего. Например, если столбец содержит три значения, хранящиеся как число, и четыре в виде текста, отображается команда «Фильтры текста «.
Фильтрация данных в таблице
При вводе данных в таблицу в заголовки ее столбцов автоматически добавляются элементы управления фильтрацией.
- Выделите данные, которые нужно отфильтровать. На вкладке Главная выберите Формат как таблица, а затем выберите Формат как таблица.
- В диалоговом окне Создание таблицы можно выбрать, есть ли в таблице заголовки.
- Выберите Таблица с заголовками, чтобы преобразовать верхнюю строку в заголовки таблицы. Данные в этой строке не будут фильтроваться.
- Не выбирайте поле проверка, если вы хотите Excel в Интернете добавить заполнители (которые можно переименовать) над данными таблицы.
- Нажмите кнопку ОК.
- Чтобы применить фильтр, щелкните стрелку в заголовке столбца и выберите параметр фильтрации.
Фильтрация диапазона данных
Если вы не хотите форматировать данные в виде таблицы, можно также применить фильтры к диапазону данных.
- Выделите данные, которые нужно отфильтровать. Для достижения наилучших результатов столбцы должны содержать заголовки.
- На вкладке Данные выберите Фильтр.
Параметры фильтрации для таблиц или диапазонов
Можно применить общий фильтр, выбрав пункт Фильтр, или настраиваемый фильтр, зависящий от типа данных. Например, при фильтрации чисел отображается пункт Числовые фильтры, для дат отображается пункт Фильтры по дате, а для текста — Текстовые фильтры. Применяя общий фильтр, вы можете выбрать для отображения нужные данные из списка существующих, как показано на рисунке:
Выбрав параметр Числовые фильтры вы можете применить один из перечисленных ниже настраиваемых фильтров.
В этом примере, чтобы отобрать регионы, в которых сумма продаж за март была меньше 6000, можно применить настраиваемый фильтр:
Ниже рассказывается, как это сделать.
- Щелкните стрелку фильтра рядом с полем Число фильтров > марта >меньше и введите 6000.
- Нажмите кнопку ОК. Excel в Интернете применяет фильтр и отображает только регионы с продажами ниже 6000 долл. США.
Аналогичным образом можно применить фильтры по дате и текстовые фильтры.
Очистка фильтра из столбца
- Нажмите кнопку Фильтр рядом с заголовком столбца, а затем выберите Очистить фильтр из .
Удаление всех фильтров из таблицы или диапазона
- Выберите любую ячейку в таблице или диапазоне и на вкладке Данные нажмите кнопку Фильтр . При этом фильтры будут удалены из всех столбцов в таблице или диапазоне и отобразятся все данные.
Фильтрация по набору верхних или нижних значений
- Щелкните ячейку в диапазоне или таблице, которую хотите отфильтровать.
- На вкладке Данные выберите Фильтр.
- Выберите стрелку
в столбце, содержав содержимое, которое требуется отфильтровать. - В разделе Фильтр выберите Выбрать один, а затем введите критерии фильтра.
- Фильтры можно применить только к одному диапазону ячеек на листе за раз.
- Когда фильтр применяется к столбцу, в других столбцах в качестве фильтров можно использовать только значения, видимые в текущем отфильтрованном диапазоне.
- В окне фильтра отображаются только первые 10 000 уникальных записей списка.
Фильтрация по конкретному числу или диапазону чисел
- Щелкните ячейку в диапазоне или таблице, которую хотите отфильтровать.
- На вкладке Данные выберите Фильтр.
- Выберите стрелку в столбце, содержав содержимое, которое требуется отфильтровать.
- В разделе Фильтр выберите Выбрать один, а затем введите критерии фильтра.
- В поле рядом с всплывающим меню введите число, которое хотите использовать.
- В зависимости от сделанного выбора вам может быть предложено выбрать дополнительные условия.
- Фильтры можно применить только к одному диапазону ячеек на листе за раз.
- Когда фильтр применяется к столбцу, в других столбцах в качестве фильтров можно использовать только значения, видимые в текущем отфильтрованном диапазоне.
- В окне фильтра отображаются только первые 10 000 уникальных записей списка.
- Вместо фильтрации можно использовать условное форматирование, которое позволяет четко выделить верхние или нижние числовые значения среди данных.
Фильтрация по цвету шрифта, цвету ячеек или наборам значков
Данные можно быстро фильтровать на основании визуальных условий, таких как цвет шрифта, цвет ячейки или наборы значков. Можно также фильтровать ячейки по наличию в них формата, примененных стилей или условного форматирования.
- В диапазоне ячеек или столбце таблицы щелкните ячейку с определенным цветом, цветом шрифта или значком, по которому вы хотите выполнить фильтрацию.
- На вкладке Данные выберите Фильтр.
- Выберите стрелку в столбце, содержав содержимое, которое требуется отфильтровать.
- В разделе Фильтр во всплывающем меню По цвету щелкните Цвет ячейки, Цвет шрифта или Значок ячейки и выберите цвет.
Фильтрация пустых ячеек
Это возможно только в случае, если фильтруемый столбец содержит пустую ячейку.
- Щелкните ячейку в диапазоне или таблице, которую хотите отфильтровать.
- На панели инструментов Данные выберите Фильтр.
- Выберите стрелку в столбце, содержав содержимое, которое требуется отфильтровать.
- В области (Выделить все) прокрутите список вниз и установите флажок (Пустые).
- Фильтры можно применить только к одному диапазону ячеек на листе за раз.
- Когда фильтр применяется к столбцу, в других столбцах в качестве фильтров можно использовать только значения, видимые в текущем отфильтрованном диапазоне.
- В окне фильтра отображаются только первые 10 000 уникальных записей списка.
Фильтрация для поиска определенного текста
- Щелкните ячейку в диапазоне или таблице, которую хотите отфильтровать.
- На вкладке Данные выберите Фильтр.
- Выберите стрелку в столбце, содержавом содержимое, которое требуется отфильтровать.
- В разделе Фильтр выберите Выбрать один, а затем во всплывающем меню выполните одно из следующих действий.
| Цель фильтрации диапазона | Операция |
|---|---|
| Строки с определенным текстом | Содержит или Равно. |
| Строки, не содержащие определенный текст | Не содержит или Не равно. |
| Задача | Операция |
|---|---|
| Фильтрация столбца или выделенного фрагмента таблицы при истинности обоих условий | И. |
| Фильтрация столбца или выделенного фрагмента таблицы при истинности одного из двух или обоих условий | Или. |
Фильтрация по началу или окончанию строки текста
- Щелкните ячейку в диапазоне или таблице, которую хотите отфильтровать.
- На панели инструментов Данные выберите Фильтр.
- Выберите стрелку в столбце, содержав содержимое, которое требуется отфильтровать.
- В разделе Фильтр выберите Выбрать один, а затем во всплывающем меню выполните одно из следующих действий.
| Условие фильтрации | Операция |
|---|---|
| Начало строки текста | Начинается с. |
| Окончание строки текста | Заканчивается на. |
| Ячейки, которые содержат текст, но не начинаются с букв | Не начинаются с. |
| Ячейки, которые содержат текст, но не оканчиваются буквами | Не заканчиваются. |
| Задача | Операция |
|---|---|
| Фильтрация столбца или выделенного фрагмента таблицы при истинности обоих условий | И. |
| Фильтрация столбца или выделенного фрагмента таблицы при истинности одного из двух или обоих условий | Или. |
Использование подстановочных знаков для фильтрации
При создании условий можно использовать подстановочные знаки.
- Щелкните ячейку в диапазоне или таблице, которую хотите отфильтровать.
- На панели инструментов Данные выберите Фильтр.
- Выберите стрелку в столбце, содержав содержимое, которое требуется отфильтровать.
- В разделе Фильтр выберите Выбрать один и выберите любой параметр.
- В текстовом поле введите свои условия, используя подстановочные знаки. Например, чтобы в результате фильтрации найти оба слова «год» и «гид», введите г?д.
- Выполните одно из указанных ниже действий.
| Используемый знак | Чтобы найти |
|---|---|
| ? (вопросительный знак) | Любой символ Пример: условию «стро?а» соответствуют результаты «строфа» и «строка» |
| Звездочка (*) | Любое количество символов Пример: условию «*-восток» соответствуют результаты «северо-восток» и «юго-восток» |
| Тильда (~) | Вопросительный знак или звездочка Например, там~? находит «там?» |
Удаление и повторное применение фильтра
Выполните одно из указанных ниже действий.
Удаление определенных условий фильтрации
Щелкните стрелку в столбце, который содержит фильтр, а затем выберите Очистить фильтр.
Удаление всех фильтров, примененных к диапазону или таблице
Выберите столбцы диапазона или таблицы, к которым применены фильтры, а затем на вкладке Данные выберите Фильтр.
Удаление или повторное применение стрелок фильтра в диапазоне или таблице
Выберите столбцы диапазона или таблицы, к которым применены фильтры, а затем на вкладке Данные выберите Фильтр.
Дополнительные сведения о фильтрации
При фильтрации данных отображаются только те данные, которые соответствуют вашим условиям. Данные, которые не соответствуют этим критериям, скрыты. После фильтрации данных можно копировать, находить, редактировать, форматировать, чартировать и печатать подмножество отфильтрованных данных.
Таблица с примененным фильтром «Первые 4 элемента»
Фильтры являются аддитивными. Это означает, что каждый дополнительный фильтр основан на текущем фильтре и еще больше сокращает подмножество данных. Сложные фильтры можно создать, отфильтровав по нескольким значениям, нескольким форматам или нескольким критериям. Например, можно отфильтровать все числа больше 5, которые также ниже среднего. Но некоторые фильтры (верхний и нижний десять, выше и ниже среднего) основаны на исходном диапазоне ячеек. Например, при фильтрации десяти верхних значений вы увидите первые десять значений всего списка, а не первые десять значений подмножества последнего фильтра.
В Excel можно создать три типа фильтров: по значениям, по формату или по условиям. Но каждый из этих типов фильтров является взаимоисключающим. Например, можно фильтровать по цвету ячейки или по списку чисел, но не по обоим. Вы можете фильтровать по значку или по пользовательскому фильтру, но не по обоим.
Фильтры скрывают посторонние данные. Таким образом, вы можете сосредоточиться только на том, что вы хотите видеть. В отличие от этого, при сортировке данных данные переупорядочены в определенном порядке. Дополнительные сведения о сортировке см. в разделе Сортировка списка данных.
При фильтрации учитывайте следующие рекомендации.
- В окне фильтра отображаются только первые 10 000 уникальных записей списка.
- Вы можете выполнять фильтрацию по нескольким столбцам. Когда фильтр применяется к столбцу, в других столбцах в качестве фильтров можно использовать только значения, видимые в текущем отфильтрованном диапазоне.
- Фильтры можно применить только к одному диапазону ячеек на листе за раз.
Примечание: При использовании функции Найти для поиска отфильтрованных данных выполняется поиск только для отображаемых данных; данные, которые не отображаются, не выполняется поиск. Чтобы выполнить поиск по всем данным, очистите все фильтры.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Расширенный фильтр в Excel и примеры его возможностей
Вывести на экран информацию по одному / нескольким параметрам можно с помощью фильтрации данных в Excel.
Для этой цели предназначено два инструмента: автофильтр и расширенный фильтр. Они не удаляют, а скрывают данные, не подходящие по условию. Автофильтр выполняет простейшие операции. У расширенного фильтра гораздо больше возможностей.
Автофильтр и расширенный фильтр в Excel
Имеется простая таблица, не отформатированная и не объявленная списком. Включить автоматический фильтр можно через главное меню.
- Выделяем мышкой любую ячейку внутри диапазона. Переходим на вкладку «Данные» и нажимаем кнопку «Фильтр».
- Рядом с заголовками таблицы появляются стрелочки, открывающие списки автофильтра.
Если отформатировать диапазон данных как таблицу или объявить списком, то автоматический фильтр будет добавлен сразу.
Пользоваться автофильтром просто: нужно выделить запись с нужным значением. Например, отобразить поставки в магазин №4. Ставим птичку напротив соответствующего условия фильтрации:
Сразу видим результат:
Особенности работы инструмента:
- Автофильтр работает только в неразрывном диапазоне. Разные таблицы на одном листе не фильтруются. Даже если они имеют однотипные данные.
- Инструмент воспринимает верхнюю строчку как заголовки столбцов – эти значения в фильтр не включаются.
- Допустимо применять сразу несколько условий фильтрации. Но каждый предыдущий результат может скрывать необходимые для следующего фильтра записи.
У расширенного фильтра гораздо больше возможностей:
- Можно задать столько условий для фильтрации, сколько нужно.
- Критерии выбора данных – на виду.
- С помощью расширенного фильтра пользователь легко находит уникальные значения в многострочном массиве.
Как сделать расширенный фильтр в Excel
Готовый пример – как использовать расширенный фильтр в Excel:
- Создадим таблицу с условиями отбора. Для этого копируем заголовки исходного списка и вставляем выше. В табличке с критериями для фильтрации оставляем достаточное количество строк плюс пустая строка, отделяющая от исходной таблицы.
- Настроим параметры фильтрации для отбора строк со значением «Москва» (в соответствующий столбец таблички с условиями вносим = «=Москва»). Активизируем любую ячейку в исходной таблице. Переходим на вкладку «Данные» — «Сортировка и фильтр» — «Дополнительно».
- Заполняем параметры фильтрации. Исходный диапазон – таблица с исходными данными. Ссылки появляются автоматически, т.к. была активна одна из ячеек. Диапазон условий – табличка с условием.
- Выходим из меню расширенного фильтра, нажав кнопку ОК.
В исходной таблице остались только строки, содержащие значение «Москва». Чтобы отменить фильтрацию, нужно нажать кнопку «Очистить» в разделе «Сортировка и фильтр».
Как пользоваться расширенным фильтром в Excel
Рассмотрим применение расширенного фильтра в Excel с целью отбора строк, содержащих слова «Москва» или «Рязань». Условия для фильтрации должны находиться в одном столбце. В нашем примере – друг под другом.
Заполняем меню расширенного фильтра:
Получаем таблицу с отобранными по заданному критерию строками:
Выполним отбор строк, которые в столбце «Магазин» содержат значение «№1», а в столбце стоимость – «>1 000 000 р.». Критерии для фильтрации должны находиться в соответствующих столбцах таблички для условий. На одной строке.
Заполняем параметры фильтрации. Нажимаем ОК.
Оставим в таблице только те строки, которые в столбце «Регион» содержат слово «Рязань» или в столбце «Стоимость» — значение «>10 000 000 р.». Так как критерии отбора относятся к разным столбцам, размещаем их на разных строках под соответствующими заголовками.
Применим инструмент «Расширенный фильтр»:
Данный инструмент умеет работать с формулами, что дает возможность пользователю решать практически любые задачи при отборе значений из массивов.
- Результат формулы – это критерий отбора.
- Записанная формула возвращает результат ИСТИНА или ЛОЖЬ.
- Исходный диапазон указывается посредством абсолютных ссылок, а критерий отбора (в виде формулы) – с помощью относительных.
- Если возвращается значение ИСТИНА, то строка отобразится после применения фильтра. ЛОЖЬ – нет.
Отобразим строки, содержащие количество выше среднего. Для этого в стороне от таблички с критериями (в ячейку I1) введем название «Наибольшее количество». Ниже – формула. Используем функцию СРЗНАЧ.
Выделяем любую ячейку в исходном диапазоне и вызываем «Расширенный фильтр». В качестве критерия для отбора указываем I1:I2 (ссылки относительные!).
В таблице остались только те строки, где значения в столбце «Количество» выше среднего.
Чтобы оставить в таблице лишь неповторяющиеся строки, в окне «Расширенного фильтра» поставьте птичку напротив «Только уникальные записи».
Нажмите ОК. Повторяющиеся строки будут скрыты. На листе останутся только уникальные записи.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры