Как сделать фильтр в Excel: Простые шаги для эффективной работы

Фильтры в Excel — это мощный инструмент, позволяющий пользователям упрощать анализ данных, облегчая процесс поиска и наблюдения за определенными значениями в больших таблицах. Основная цель фильтров заключается в том, чтобы предоставить эффективный способ управления большими объемами информации. Когда данные становятся многослойными и обширными, возможность отсеивания ненужной информации становится критически важной.

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

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

Чтобы узнать больше о фильтрации в Excel, используйте ссылки ниже.

Что такое фильтр в Excel?

Фильтр Excel , он же Автофильтр , — это быстрый способ отобразить только информацию, необходимую в данный момент времени, и скрыть все остальные данные. 

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

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

Что лучше – фильтр или сортировка?

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

• Для текстовых значений: Сортировать от А до Я, Сортировать от Я до А,  Сортировать по цвету.
• Для чисел: Сортировать от меньшего к большему, Сортировать по возрастанию,  Сортировать по цвету.
• Для дат: Сортировать от старых к новым, Сортировать от новых к старым, и Сортировать по цвету.

Разница между сортировкой и фильтром в Excel заключается в следующем:

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

Что нужно сделать в начале

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

Убедитесь, что данные организованы в виде четкой таблицы; это означает наличие столбцов с заголовками, которые помогают в фильтрации информации.

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

3 способа поставить фильтр в Excel

Поставить фильтр в таблице Excel по столбцам можно несколькими способами.

1. На вкладке меню Данные нажмите кнопку Фильтр.

2. На вкладке Главная, в группе Редактирование, нажмите Сортировка и фильтр > Фильтр.

3. Используйте сочетание клавиш Excel для включения/выключения фильтра: Ctrl+Shift+L

Какой бы из этих методов вы ни использовали, в каждой ячейке заголовка появятся стрелки раскрывающегося списка:

Таким образом, установка фильтров в Excel является простым и интуитивным процессом.

Кроме того, вы можете сделать фильтр и при помощи формулы, как описано в этой статье: Мастерство фильтрации: Как использовать функцию ФИЛЬТР в Excel.

Как использовать фильтр в Excel

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

Чтобы отфильтровать данные в Excel, выполните следующие действия:

1. Щелкните стрелку раскрывающегося списка для столбца, который вы хотите отфильтровать.
2. Снимите флажок Выбрать все для быстрой отмены выбора всех данных.
3. Установите флажки рядом с данными, которые вы хотите отобразить, и нажмите «ОК».

Например, вот как мы можем отфильтровать данные, чтобы видеть продажи только в регионе Запад:

Фильтр применяется к столбцу D, временно скрывая все регионы продаж, кроме Запад.

Стрелка раскрывающегося списка в отфильтрованном столбце изменится на кнопку «Фильтр», а при наведении курсора на эту кнопку отобразится подсказка на экране, указывающая, какие фильтры поставлены:

Совет. Чтобы быстро узнать количество отфильтрованных/видимых строк, просто взгляните на строку состояния Excel .

Как сделать несколько фильтров

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

Например, мы можем показать только продажи молочного шоколада в регионах Запад и Юг. При созданиинескольких фильтров в Excel кнопка фильтра появляется в каждом из отфильтрованных столбцов:

Фильтр пустых/непустых ячеек

Чтобы отфильтровать данные в Excel, пропуская пробелы или непустые значения, выполните одно из следующих действий:

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

В результате вы будете видеть только те строки, которые содержат какое-либо значение в этом столбце.

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

Примечания:

• Флажок Пустые появится только в столбцах, содержащих хотя бы одну пустую ячейку. Иначе вы его просто не увидите.
• Если вы хотите удалить пустые строки на основе какого-либо ключевого столбца, вы можете отфильтровать непустые строки в этом столбце, выбрать отфильтрованные строки, щелкнуть правой кнопкой мыши по выделенному фрагменту и нажать Удалить строку. Если вы хотите удалить только те строки, которые полностью пусты, и оставить строки с некоторым содержимым и некоторыми пустыми ячейками, ознакомьтесь со статьей 6 способов — как безопасно удалить лишние пустые строки в Excel.

Как использовать фильтр в Excel

Помимо основных параметров фильтрации, описанных выше, автофильтр в Excel предоставляет ряд расширенных инструментов, которые помогут вам фильтровать определенные типы данных, такие как текст, числа или даты.

Примечания:

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

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

Фильтр текстовых данных

Если вы хотите отфильтровать текстовый столбец по чему-то конкретному, вы можете воспользоваться рядом расширенных возможностей, предоставляемых текстовыми фильтрами Excel , например:

• Фильтровать ячейки, которые начинаются или заканчиваются определенным символом(ами).
• Фильтровать ячейки, которые содержат или не содержат заданный символ или слово в любом месте текста.
• Фильтровать ячейки, которые точно равны или не равны указанным символам.

Как только вы создадите фильтр в столбце, содержащем текстовые значения, пункт Текстовые фильтры автоматически появятся в меню «Автофильтр»:

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

Например, чтобы отфильтровать черный шоколад любого вида, сделайте следующее:

1. Щелкните стрелку раскрывающегося списка в заголовке столбца и выберите Текстовые фильтры.
2. В раскрывающемся меню выберите нужный параметр ( в данном примере — «Содержит…» ).
3. Появится диалоговое окно. В поле справа введите текст или выберите нужный элемент из выпадающего списка.
4. Нажмите ОК.

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

Другими полезными критериями являются 'начинается с' и 'заканчивается на', которые упрощают поиск по заданным параметрам. 

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

Фильтр столбца по 2 условиям

Чтобы отфильтровать данные в Excel по двум текстовым критериям, выполните указанные выше действия для настройки первого условия, а затем выполните следующие действия:

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

Например, вот как можно отфильтровать товары, содержащие либо Молочный, либо С орехами:

Как создать фильтр в Excel с подстановочными знаками

Если вы не помните точный поисковый запрос или хотите отфильтровать строки с похожей информацией, вы можете создать фильтр с одним из следующих подстановочных знаков:

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

Как фильтровать числа в Excel

Числовые фильтры Excel позволяют манипулировать числовыми данными различными способами, в том числе:

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

На скриншоте ниже показаны все варианты числовых фильтров, доступных в Excel.

Допустим, у вас есть таблица с данными о продажах. Если вам нужно отфильтровать записи, где сумма продаж превышает 100 единиц, следует выбрать критерий "Больше чем" и ввести значение 100. Таким образом, Excel отобразит только те строки, которые соответствуют этому критерию, позволяя быстро оценить продуктивность товаров или сотрудников.

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

Например, чтобы создать фильтр, отображающий только заказы размером от 50 до 100 единиц, выполните следующие действия:

1. Щелкните стрелку автофильтра в заголовке столбца и выберите Числовые фильтры.
2. Выберите подходящий оператор сравнения из списка, в данном примере — Между… .
3. В диалоговом окне Пользовательский автофильтр введите значения нижней и верхней границ. По умолчанию Excel предлагает использовать "Больше или равно"и "Меньше или равно". Вы можете изменить их на "Больше чем"и "Меньше, чем", если вы не хотите включать пороговые значения.
4. Нажмите ОК.

В результате отображаются только заказы от 50 до 100 единиц:

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

Как фильтровать даты в Excel

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

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

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

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

Предустановленные фильтры по датам можно найти на вкладке "Данные". После выбора столбца с датами, в меню фильтрации предлагаются варианты, такие как "Сегодня", "Последние 7 дней" и "Этот месяц". Эти фильтры облегчают извлечение информации за определенные периоды без необходимости ручного ввода данных.

Для более точной настройки фильтрации Excel позволяет устанавливать собственные критерии.

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

На рисунке ниже показаны все доступные фильтры дат:

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

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

Например, чтобы отобразить все записи за июнь 2024 года, нажмите «Между…» и настройте фильтр следующим образом:

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

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

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

Как фильтровать по цвету в Excel

Если данные на вашем листе отформатированы вручную или с помощью условного форматирования, вы также можете отфильтровать эти данные по цвету.

При нажатии на стрелку раскрывающегося списка автофильтра отобразится фильтр по цвету с одним или несколькими вариантами в зависимости от того, какое форматирование применено к столбцу:

• Фильтр по цвету ячейки
• Фильтр по цвету шрифта
• Фильтр по значку ячейки

Например, если вы отформатировали ячейки в определенном столбце с тремя различными цветами фона (зеленый, красный и оранжевый) и хотите отобразить только оранжевые ячейки, вы можете сделать это следующим образом:

1. Щелкните стрелку фильтра в ячейке заголовка и укажите Фильтр по цвету.
2. Щелкните нужный цвет — в данном примере зеленый.

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

Как фильтровать в Excel с помощью поиска

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

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

Фильтр Excel немедленно покажет вам все элементы, соответствующие этому условию. Чтобы отобразить только эти строки, нажмите кнопку ОК в меню автофильтра Excel или клавишу Enter на клавиатуре.

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

В этом примере мы добавляем шоколад с орехами к уже отфильтрованному черному шоколаду:

Это было довольно быстро, не правда ли? Всего несколько щелчков мышкой!

Фильтр по выбранному значению ячейки или формату

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

Вот как это сделать:

1. Щелкните правой кнопкой мыши ячейку, содержащую значение, цвет или значок, по которому вы хотите отфильтровать данные.
2. В контекстном меню выберите Фильтр.
3. Выберите нужный вариант: значение, цвет, цвет шрифта, значок.

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

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

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

1. Нажмите «Повторить»  в меню Главная > Редактирование > Сортировка и фильтр.

2. На вкладке Данные также есть кнопка Повторить, как вы видите на скриншоте ниже.

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

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

1. Выберите любую отфильтрованную ячейку, а затем нажмите Ctrl + А для выбора всех отфильтрованных данных, включая заголовки столбцов.

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

2. Используйте Ctrl+С для копирования выбранных данных.
3. Перейдите на другой лист/книгу, выберите верхнюю левую ячейку целевого диапазона и нажмите Ctrl+V для вставки отфильтрованных данных.

Примечание. Обычно, когда вы копируете отфильтрованные данные в другое место, отфильтрованные строки опускаются. В некоторых редких случаях, в основном в очень больших книгах, Excel может копировать скрытые строки в дополнение к видимым. Чтобы этого не произошло, выберите диапазон отфильтрованных ячеек и нажмите Alt + ; для выбора только видимых ячеек, игнорируя скрытые строки. 

Если вы не привыкли использовать сочетания клавиш, вы можете использовать меню Главная >Редактирование  > Найти и выделить > Выделить группу ячеек... > Только видимые ячейки.

Как очистить фильтр

Удаление фильтров в Excel — это простая процедура, которая позволяет пользователю вернуть данные к первоначальному виду. Существует несколько способов очистить фильтры, и в данной статье мы обсудим методы, которые обеспечат быстрый и эффективный способ выполнения этой задачи.

Чтобы удалить фильтр в определенном столбце, нажмите кнопку фильтра в заголовке столбца, а затем используйте Снять фильтрацию с <Имя столбца>:

Как удалить все фильтры

Чтобы удалить все фильтры на листе Excel, выполните одно из следующих действий:

• В меню Данные > Сортировка и фильтр  нажмите Очистить.
• В меню Главная > Редактирование  нажмите Сортировка и фильтр > Очистить.

Нажатие этой кнопки немедленно уберет все примененные фильтры и покажет полный набор данных.

Почему фильтр не работает 

При работе с фильтрами в Excel пользователи могут столкнуться с несколькими распространенными проблемами, которые могут затруднять анализ данных. 

Одной из частых проблем является отсутствие возможности фильтровать определенные данные. Это может происходить из-за того, что данные находятся в объединенных ячейках или не имеют четкой структуры. Чтобы устранить это, следует разбить объединенные ячейки и убедиться, что данные организованы в виде таблицы, что позволит Excel корректно распознать и применить фильтры.

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

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

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

Если это не помогло и ваши фильтры Excel по-прежнему не работают, очистите все фильтры в электронной таблице, а затем примените их заново. Как это сделать – подробно описано выше.

Мы постарались подробно рассказать, как сделать фильтр в программе Excel.  Эффективное использование этого инструмента может стать ключевым шагом к более успешному анализу и управлению данными в любом профессиональном контексте.

Excel-магия: Как выделить ячейки на раз-два!

Как выбрать определенные строки и столбцы в Excel

Как выделить столбцы и строки в Excel

Мастерство сортировки в Excel: по алфавиту и не только

Мастерство фильтрации: Как использовать функцию ФИЛЬТР в Excel

Создаем последовательности в Excel: числа, даты, формулы и хитрости

Сортировка по дате в Excel: по возрастанию, по месяцам, автоматически

Функция СЧЕТ в Excel: как подсчитать количество ячеек с числами