Фильтрация данных в Excel — просто и полнофункционально

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

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

1. Как добавить группу в Excel

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

1,1 Команда фильтра на вкладке «Данные»

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

1,2 Команда фильтра на вкладке «Главная»

Выберите любую ячейку в нужном диапазоне или таблице и нажмите «Главная» → «Сортировка и фильтр» → «Фильтр».

1,3 Добавить группу с помощью сочетания клавиш

Выберите любую ячейку в нужном диапазоне или таблице и нажмите «Ctrl» + «Shift» + «L», чтобы добавить фильтр.

После выполнения любой из описанных выше операций в заголовках столбцов выбранных ячеек появятся стрелки раскрывающихся списков.

2. Как применить фильтр в Excel — с одним или несколькими критериями

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

2,1 Apply filter to one column (one criteria)

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

1. 1) Щелкните стрелку раскрывающегося списка в заголовке столбца.
2. 2) Укажите требуемое условие фильтрации.
3. 3) Нажмите кнопку «OK», чтобы начать фильтрацию. См. снимок экрана:
   

Теперь фильтр применён к столбцу C: отображаются все данные, соответствующие условиям фильтрации, а остальные — скрыты.

После применения фильтра стрелка раскрывающегося списка меняется на значок фильтра .

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

2,2 Применение фильтра с несколькими критериями к нескольким столбцам (несколько критериев)

2,2.1 Последовательное применение фильтра с несколькими критериями к нескольким столбцам

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

После применения фильтра к нескольким столбцам стрелки раскрывающихся списков в отфильтрованных столбцах заменяются значками фильтров.

2,2.2 Одновременное применение фильтра с несколькими критериями к нескольким столбцам

Описанный выше метод предполагает последовательное применение фильтра к каждому столбцу и поддерживает только условия типа «И». Ниже мы представляем подходы, которые позволяют не только фильтровать сразу несколько столбцов, но и комбинировать условия «И» и «ИЛИ».

Допустим, у вас есть таблица данных, подобная той, что показана на снимке экрана ниже, и вы хотите отфильтровать строки по нескольким столбцам, используя следующие критерии: «Product = AAA-1» и («Order > 80» или «Total Price > 10000»). Воспользуйтесь одним из приведённых ниже методов, чтобы выполнить эту задачу.

2,2.2,1 Применение фильтра к нескольким столбцам с помощью функции расширенного фильтра

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

1. Сначала задайте критерии на листе, как показано на снимке экрана ниже.

Примечание: для критериев «И» размещайте значения в разных ячейках одной строки, а значения критериев «ИЛИ» — в отдельной строке.

2. Нажмите «Данные» > «Расширенный», чтобы активировать функцию расширенного фильтра.

3. В диалоговом окне «Расширенный фильтр» выполните следующую настройку.

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

2,2.2,2 Простое применение фильтра к нескольким столбцам с помощью замечательного инструмента

Поскольку критерии «И» и «ИЛИ» в условии фильтрации сложно настроить описанным выше способом, мы настоятельно рекомендуем воспользоваться функцией «Суперфильтр» из набора «Kutools для Excel». С её помощью вы с лёгкостью сможете применять фильтры к нескольким столбцам, комбинируя условия «И» и «ИЛИ» прямо в Excel.

1. После установки Kutools для Excel перейдите в меню «KUTOOLS PLUS» и выберите «Супер фильтр».

Затем на правой стороне листа появится область «Супер фильтр».

По умолчанию в области «Суперфильтр» добавлены две пустые группы критериев, связанные отношением «ИЛИ». Внутри каждой группы критерии объединены отношением «И». Вы можете изменить логическую связь между группами в соответствии с вашими задачами.

2. В разделе «Суперфильтр» настройте условия фильтрации следующим образом.

2,1) Установите флажок «Указано», нажмите кнопку , чтобы выбрать исходный диапазон или таблицу, которую нужно отфильтровать;

2,2) В разделе «Связь» Раскрывающийся список выберите «ИЛИ»;

3,3) Щёлкните первую пустую строку в первой группе, а затем укажите критерии в соответствии с вашими требованиями;

Совет: первый раскрывающийся список предназначен для заголовков столбцов, второй — для выбора типа фильтра («Текст», «Число», «Дата», «Год», «Формат текста» и т.д.), третий — для указания типа критерия, а последнее текстовое поле — для ввода значения критерия.

Как в приведённом выше примере, здесь я последовательно выбираю «Product» → «Text» → «Equals» из трёх раскрывающихся списков, а затем ввожу «AAA-1» в текстовое поле. См. снимок экрана:

2,4) Продолжайте создавать остальные критерии; критерии «ИЛИ» необходимо создавать в Новая группа. Как показано на приведённом ниже снимке экрана, все критерии созданы. Вы можете удалить пустой критерий из групп.

2,5) Нажмите кнопку «Фильтр», чтобы Начать фильтрацию.

Теперь в исходном диапазоне данных отображаются только соответствующие записи — остальные скрыты. См. снимок экрана:

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

Нажмите, чтобы узнать больше об этой функции.

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

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

3,1 Фильтрация текстовых значений

3,1.1Фильтрация текстовых ячеек по определенным критериям (начинается с, заканчивается на, содержит и т.д.)

На самом деле встроенный оператор фильтрации «Текстовые фильтры» предлагает множество удобных критериев для эффективной работы с текстовыми данными. Допустим, вам нужно отфильтровать ячейки, начинающиеся с определённого символа — например, «J». Выполните следующие шаги, чтобы получить нужный результат.

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

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

3. Нажмите «Текстовые фильтры» > «Начинается с».

4. В диалоговом окне «Пользовательский автофильтр» введите нужный символ (в данном случае — J) в текстовое поле и нажмите «OK».

Совет: при необходимости вы можете добавить ещё один критерий с условием «И» или «ИЛИ».

Теперь в столбце D отображаются все ячейки, начинающиеся с символа J, как показано на приведённом ниже снимке экрана.

3,1.2 Фильтрация с помощью С учетом регистра

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

3,1.2,1 Фильтрация определённого текста с использованием С учетом регистра с помощью формулы и команды фильтрации

Допустим, вы хотите отфильтровать все заглавные буквы в тексте — например, «TEXT TOOLS» — из столбца B. Выполните следующие действия.

1. Создайте вспомогательный столбец рядом с исходным диапазоном данных (в данном случае вспомогательным будет столбец D). Введите приведённую ниже формулу во вторую ячейку и нажмите клавишу «Enter». Затем выделите ячейку с результатом и перетащите её маркер автозаполнения вниз, чтобы заполнить остальные ячейки.

=EXACT(B2,UPPER(B2))

Примечание. Эта формула позволяет определить ячейки, содержащие исключительно заглавные или строчные буквы: если в ячейке только заглавные символы — результат «ИСТИНА», в противном случае — «ЛОЖЬ».

2. Выделите столбцы B и D (столбец C также будет выделен — это не имеет значения), затем щёлкните «Д анные» > «Фильтр», чтобы применить фильтр к этим столбцам.

3. Перейдите к столбцу B (столбцу с текстом, который вы хотите отфильтровать), и выполните следующие настройки.

Теперь в столбце B отображаются только варианты «text tools» — как в верхнем, так и в нижнем регистре.

4. Щёлкните стрелку раскрывающегося списка в столбце D, снимите флажок «Выделить всё», установите флажок рядом с элементом «ИСТИНА» и нажмите кнопку «ОК».

Таким образом, все ячейки в столбце B, содержащие слово «TEXT TOOLS», написанное заглавными буквами, отфильтрованы, как показано на снимке экрана ниже.

3,1.2,2 Простая фильтрация ячеек с учётом С учетом регистра с помощью замечательного инструмента

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

1. Выделите диапазон столбца, в котором нужно отфильтровать текст, и нажмите «KUTOOLS PLUS» > «Специальный фильтр» > «Специальный фильтр».

2. В диалоговом окне «Специальный фильтр» задайте следующие параметры.

3. Затем появится диалоговое окно «Kutools для Excel» с указанием количества найденных ячеек, подлежащих фильтрации. Нажмите кнопку «ОК».

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

Нажмите, чтобы узнать больше об этой функции.

3,1.3 Фильтрация по Длина текста

Если вы хотите отфильтровать ячейки по длине текста — например, оставить только те, где текст состоит ровно из 10 символов, — как это сделать? Ниже приведены три простых метода, которые помогут вам справиться с этой задачей.

3,1.3,1 Фильтрация ячеек по Длина текста с помощью команды фильтрации

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

1. Выделите диапазон ячеек, которые нужно отфильтровать (в данном случае — B1:B27), а затем примените фильтр к этому столбцу, выбрав «Данные» > «Фильтр».

2. Щёлкните стрелку раскрывающегося списка в заголовке столбца и выберите «Текстовые фильтры» > «Установить условия фильтрации». См. снимок экрана:

3. В  диалоговом окне «Пользовательский автофильтр» выберите условие «равно», введите 10 знаков вопроса (?) в качестве шаблона в текстовое поле и нажмите кнопку «ОК».

Совет: эти 10 знаков вопроса означают, что будет выполняться поиск текстовой строки длиной 10 символов.

Теперь все ячейки, в которых длина текста составляет 10 символов (включая пробелы), немедленно отфильтрованы по длине строки.

3,1.3,2 Фильтрация ячеек по Длина текста с помощью формулы и команды фильтрации

Кроме того, вы можете использовать функцию ДЛСТР (LEN) для определения длины текста в каждой ячейке, а затем применить фильтр, чтобы отобрать нужные ячейки на основе полученного результата.

1. Создайте вспомогательный столбец рядом с исходным диапазоном данных, введите в него приведённую ниже формулу и нажмите клавишу «Enter». Затем выделите ячейку с результатом и перетащите её маркер автозаполнения вниз, чтобы заполнить остальные ячейки.

=LEN(B2)

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

2. Выделите вспомогательный столбец (включая заголовок) и нажмите «Данные» > «Фильтр», чтобы добавить фильтр.

3. Щёлкните стрелку раскрывающегося списка, снимите флажок «Выделить всё», чтобы отменить выбор всех элементов, затем установите флажок только напротив числа 10 и нажмите кнопку «ОК».

Теперь все ячейки, содержащие текст длиной 10 символов (включая пробелы), мгновенно отфильтрованы.

3,1.3,3 Простая фильтрация ячеек по Длина текста с помощью замечательного инструмента

Здесь мы рекомендуем воспользоваться утилитой «Специальный фильтр» из набора «Kutools для Excel», которая легко поможет отфильтровать ячейки по длине текста в Excel.

1. Выделите диапазон столбца, в котором нужно отфильтровать ячейки по заданной длине текста, и выберите «KUTOOLS PLUS» > «Специальный фильтр» > «Специальный фильтр». См. снимок экрана:

2. В диалоговом окне «Специальный фильтр» задайте следующие параметры.

3. Появится диалоговое окно «Kutools для Excel» с указанием количества найденных ячеек, подлежащих фильтрации. Нажмите «ОК», чтобы продолжить.

Таким образом, все ячейки, в которых длина текста соответствует значению «Фильтр по длине строки» и равна 10, отфильтрованы, как показано на снимке экрана ниже.

3,2 Фильтрация числовых значений

В Excel с помощью команды «Числовые фильтры» фильтровать числа также очень просто.

Предположим, вы хотите отфильтровать ячейки с числами от 15 000 до 20 000 в столбце (например, в столбце C, как показано на скриншоте ниже). Вы можете выполнить следующие действия для достижения этой цели.

1. Выделите диапазон столбца с числами, которые нужно отфильтровать, и нажмите «Данные» > «Фильтр», чтобы применить фильтрацию.

2. После добавления группы выполните следующие настройки.

Совет. Поскольку я хочу отфильтровать ячейки с числами в диапазоне от 15 000 до 20 000, я ввожу эти значения по отдельности в текстовое поле.

Теперь ячейки с числами в диапазоне от 15 000 до 20 000 отфильтрованы, как показано на снимке экрана ниже.

3,3 Фильтрация дат

По умолчанию встроенная функция «Фильтры дат» предоставляет множество распространённых критериев для фильтрации дат. Как видите, отсутствует встроенная опция для фильтрации дат по Неделя, выходным или рабочим дням. В этом разделе мы покажем, как выполнить такие операции.

3,3.1 Фильтрация дат по Неделя или выходным дням

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

3,3.1,1 Фильтрация дат по Неделя или выходным дням с помощью формулы и команды фильтрации

В этом разделе вы примените функцию WEEKDAY (ДЕНЬНЕД) для вычисления Неделя каждой даты, а затем примените фильтр для отбора определённых Неделя или выходных дней по мере необходимости.

1. В пустой ячейке (в данном случае D2) введите приведённую ниже формулу и нажмите клавишу «Enter». Затем выделите ячейку с результатом и перетащите маркер автозаполнения на нужные ячейки ниже, чтобы применить эту формулу.

=WEEKDAY(A2)

Совет:

Примечание. Как видите, формула возвращает числа от «1» до «7», соответствующие дням недели от воскресенья до субботы (1 — воскресенье, 7 — суббота).

2. Выделите все результаты формулы (включая ячейку с заголовком) и выберите «Данные» > «Фильтр».

3. Щёлкните стрелку раскрывающегося списка и снимите флажок «Выделить всё».

Таким образом, все выходные дни или определённые ячейки недели отфильтрованы. См. снимок экрана:

3,3.1,2 Простая фильтрация дат по Неделя или выходным дням с помощью замечательного инструмента

Если описанный выше метод покажется вам неудобным, рекомендуем воспользоваться утилитой «Специальный фильтр» из набора «Kutools для Excel» — с её помощью вы сможете всего за несколько щелчков легко отфильтровать ячейки, содержащие любую Неделю.

1. Выделите ячейки с датами, которые нужно отфильтровать по определённой неделе.

2. Щёлкните «KUTOOLS PLUS» > «Специальный фильтр» > «Специальный фильтр».

3. В диалоговом окне «Специальный фильтр» задайте следующие параметры.

4. Затем появится диалоговое окно «Kutools для Excel», в котором будет указано количество найденных ячеек, подлежащих фильтрации. Нажмите «ОК», чтобы продолжить.

Теперь все выходные дни или любые ячейки с пометкой «Неделя» отфильтрованы.

3,3.2 Фильтрация ячеек по рабочим дням с помощью замечательного инструмента

Помимо фильтрации ячеек по неделям или выходным дням, утилита «Специальный фильтр» из набора «Kutools для Excel» позволяет также фильтровать их по рабочим дням.

1. Выполните те же действия, что и выше, чтобы включить утилиту «Специальный фильтр».

2. В диалоговом окне «Специальный фильтр» задайте следующие параметры.

3. Затем откроется диалоговое окно «Kutools для Excel» — нажмите «ОК», чтобы продолжить.

Теперь отображаются только ячейки с рабочими днями.

3,4 Фильтр по формату

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

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

3,4.1 Фильтрация по тексту с полужирным/курсивным форматированием

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

3,4.1,1 Текст с полужирным /курсивным форматированием с помощью формулы и команды «Фильтр»

Сочетание формулы Get.Cell и команды «Фильтр» позволяет фильтровать ячейки с жирным шрифтом в диапазоне столбца.

1. Нажмите «Формулы» > «Определить имя».

2. В диалоговом окне «Новое имя» необходимо:

=GET.CELL(20,$B2)

=GET.CELL(21,$B2)

Синтаксис формулы:

=GET.CELL(type_num, reference)

Аргументы формулы

3. Выберите пустую ячейку в той же строке, что и B2, введите в неё приведённую ниже формулу и нажмите клавишу Enter. Выделите ячейку с результатом и перетащите маркер автозаполнения вниз по столбцу, чтобы применить эту формулу к остальным ячейкам.

=Filter_Bold_Cells

4. Выделите все ячейки с результатами (включая заголовок) и выберите «Данные» > «Фильтр».

5. Нажмите стрелку раскрывающегося списка, установите флажок только напротив значения «ИСТИНА» и нажмите «ОК».

Теперь отфильтрованы все ячейки, содержащие текст полужирным или курсивом. См. снимок экрана:

3,4.1,2 Текст с полужирным или курсивным форматированием с помощью команд Поиск и замена и «Фильтр»

Вы также можете достичь этой цели, комбинируя команды «Найти и заменить» с функцией «Фильтр».

1. Выделите диапазон столбца, содержащий ячейки с полужирным или курсивным текстом, который нужно отфильтровать, и нажмите «Ctrl» + «F».

2. В диалоговом окне «Поиск и замена» выполните следующую настройку.

3. Теперь все ячейки с полужирным или курсивным текстом выделены в исходном диапазоне. Перейдите на вкладку «Главная», нажмите «Цвет заливки» и выберите нужный цвет для выделенных ячеек.

4. Снова выделите весь диапазон столбца и выберите «Данные» > «Фильтр», чтобы добавить к нему фильтрацию.

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

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

3,4.1,3 Простая Фильтр ячеек с жирным шрифтом или курсивного отформатированного текста с помощью удивительного инструмента

Как уже упоминалось выше, утилита «Специальный фильтр» из Kutools для Excel позволяет легко выполнять фильтрацию с учётом регистра, по длине текста и по датам. Здесь мы покажем, как использовать эту функцию для фильтрации ячеек, содержащих текст, отформатированный жирным шрифтом или курсивом, в Excel.

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

2. Нажмите «KUTOOLS PLUS» > «Специальный фильтр» > «Фильтр ячеек с жирным шрифтом» или «Фильтр ячеек с курсивным шрифтом». См. снимок экрана:

3. Нажмите «ОК» в появившемся диалоговом окне «Kutools для Excel», чтобы продолжить (оно покажет, сколько ячеек соответствует заданным критериям).

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

3,4.2 Фильтрация по тексту с зачеркиванием

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

3,4.2,1 Текст с Фильтр ячеек со стрижкой форматированием с помощью пользовательской функции и команды «Фильтр»

Вы можете применить пользовательскую функцию для определения ячеек с текстом, отформатированным зачеркиванием, а затем использовать команду «Фильтр» для фильтрации всех таких ячеек на основе полученных результатов.

1. Нажмите клавиши «Alt» + «F11», чтобы открыть окно Microsoft Visual Basic для приложений.

2. В окне «Microsoft Visual Basic для приложений» выберите «Вставка» > «Модуль», а затем вставьте приведённый ниже код VBA в окно редактора кода.

Function HasStrike(Rng As Range) As Boolean
HasStrike = Rng.Font.Strikethrough
End Function

3. Нажмите клавиши «Alt» + «Q», чтобы закрыть окно Microsoft Visual Basic для приложений.

4. Выделите пустую ячейку (она должна находиться в той же строке, что и ячейка, для которой выполняется расчёт), введите приведённую ниже формулу и нажмите клавишу Enter. Затем выделите ячейку с результатом и перетащите её маркер автозаполнения на ячейки ниже, чтобы применить эту формулу.

=HasStrike(B2)

Примечание. Если шрифт в соответствующей ячейке зачёркнут, функция возвращает значение «ИСТИНА», в противном случае — «ЛОЖЬ».

5. Выделите все ячейки с результатами (включая заголовок) и выберите «Данные» → «Фильтр».

6. Затем нажмите стрелку раскрывающегося списка, установите флажок только напротив параметра «ИСТИНА» и нажмите «ОК». См. приведённый снимок экрана.

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

3,4.2,2 Простая фильтрация текста с Фильтр ячеек со стрижкой форматированием с помощью удивительного инструмента

С помощью утилиты «Специальный фильтр» из Kutools для Excel вы можете отфильтровать все ячейки с зачеркнутым текстом всего за несколько щелчков.

1. Выделите диапазон столбца, в котором нужно отфильтровать все ячейки с зачеркнутым текстом, и выберите «KUTOOLS PLUS» > «Специальный фильтр» > «Фильтр ячеек со штриховкой».

2. Затем откроется диалоговое окно «Kutools для Excel» с указанием количества подходящих ячеек. Нажмите «ОК», чтобы продолжить.

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

3,4.3 Фильтрация по шрифту или Цвет фона

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

3,4.3,1 Фильтрация по одному шрифту или Цвет фона с помощью команды «Фильтр»

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

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

2. Нажмите стрелку раскрывающегося списка > «Фильтровать по цвету». Отобразятся все цвета заливки ячеек и цвета шрифта, используемые в текущем столбце. Щелкните любой из представленных цветов — и таблица автоматически отфильтрует все ячейки по выбранному цвету.

3,4.3,2 Фильтрация по нескольким Цвет фона с помощью пользовательской функции и команды «Фильтр»

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

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

1. Одновременно нажмите клавиши Alt и F11.

2. В окне «Microsoft Visual Basic для приложений» выберите «Вставка» > «Модуль», а затем вставьте приведённый ниже код VBA в окно редактора кода.

Код VBA: Получить индекс Цвет фона ячейки

Function GetColor(x As Range) As Integer
GetColor = x.Interior.ColorIndex
End Function

3. Нажмите клавиши «Alt» + «Q», чтобы закрыть окно Microsoft Visual Basic для приложений.

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

5. Выделите пустую ячейку рядом с ячейкой заголовка (в данном случае — E2), введите в неё приведённую ниже формулу и нажмите клавишу «Enter». Затем выделите ячейку с результатом и перетащите маркер автозаполнения вниз, чтобы применить формулу к остальным ячейкам.

=GetColor(B2)

Примечание. Если у ячейки отсутствует цвет заливки, функция возвращает значение -4142.

6. Выделите ячейки вспомогательного столбца (включая заголовок) и выберите «Данные» > «Фильтр», чтобы применить фильтр к этому столбцу.

7. Нажмите стрелку раскрывающегося списка, чтобы открыть его, и выполните следующую настройку.

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

3,4.3,3 Легко фильтруйте по шрифту или Цвет фона с помощью потрясающего инструмента

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

1. Выделите диапазон столбца, по которому нужно фильтровать ячейки по шрифту или цвету фона, затем нажмите «Kutools Plus» > «Специальный фильтр» > «Специальный фильтр».

2. В диалоговом окне «Специальный фильтр» задайте следующие параметры.

2,1) Адрес ячейки выбранного диапазона столбца отображается в поле «Диапазон»;

2,2) В разделе «Правило фильтрации» выберите параметр «Формат»;

2,3) Выберите «Цвет фона» или «Цвет шрифта» из раскрывающегося списка;

2,4) Нажмите значок пипетки ;

2,5) В открывшемся диалоговом окне «Фильтр специальных ячеек» выберите ячейку, содержащую Цвет шрифта или Цвет фона, по которым будет выполняться фильтрация, затем нажмите «ОК».

2,6) Когда вы вернётесь в диалоговое окно «Специальный фильтр», Цвет шрифта или Цвет фона выбранной ячейки будет отображаться в текстовом поле (при необходимости вы можете изменить цвет). Нажмите кнопку «ОК», чтобы Начать фильтрацию ячейки.

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

3,4.4 Фильтр ячеек с формулами ячеек

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

3,4.4,1 Фильтр ячеек с формулами ячеек с помощью пользовательской функции и команды фильтрации

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

1. Нажмите «Alt» + «F11», чтобы открыть окно Microsoft Visual Basic для приложений.

2. В окне «Microsoft Visual Basic for Applications» выберите «Вставка» → «Модуль», а затем скопируйте приведённый ниже код VBA в окно редактора кода.

Function HasFormula(Cell)
HasFormula = Cell.HasFormula
End Function

3. Нажмите клавиши «Alt» + «Q», чтобы закрыть окно Microsoft Visual Basic для приложений.

4. Выберите пустую ячейку в той же строке, что и проверяемая ячейка, введите в неё приведённую ниже формулу и нажмите клавишу «Enter». Затем выделите ячейку с результатом и перетащите её маркер автозаполнения на нижестоящие ячейки, чтобы применить формулу ко всем нужным строкам.

=HasFormula(C2)

Как видно на приведённом выше снимке экрана, результаты «ЛОЖЬ» и «ИСТИНА» означают следующее: если соответствующая ячейка содержит формулу, возвращается значение ИСТИНА, в противном случае — ЛОЖЬ.

5. Выделите ячейки с результатами (включая заголовок) и нажмите «Данные» > «Фильтр», чтобы применить фильтрацию.

6. Нажмите стрелку раскрывающегося списка, установите флажок только напротив значения «ИСТИНА» и нажмите «OK».

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

3,4.4,2 Легко Фильтр ячеек с формулами ячейки с помощью потрясающего инструмента

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

1. Выделите диапазон, в котором нужно отфильтровать все ячейки с формулами, и нажмите «KUTOOLS PLUS» > «Специальный фильтр» > «Фильтр ячеек с формулами».

2. Появится диалоговое окно «Kutools для Excel» с указанием количества подходящих ячеек. Нажмите «OK», чтобы продолжить.

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

Кроме того, вы можете использовать функцию «Специальный фильтр» из набора «Kutools для Excel», чтобы легко фильтровать ячейки по другому форматированию, например:

Фильтрация всех Комментарий,нажмите, чтобы узнать больше…

Фильтрация всех Объединенный на основе определённого значения,нажмите, чтобы узнать больше…

3,5 Фильтрация с использованием подстановочных знаков

Иногда вы можете забыть точные критерии поиска при фильтрации. В таком случае рекомендуем использовать подстановочные знаки.

В Excel существует всего три подстановочных знака:

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

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

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

2. Нажмите «Данные» → «Расширенный фильтр».

3. В открывшемся диалоговом окне «Расширенный фильтр» выполните следующие настройки.

Теперь отфильтрованы все ячейки, оканчивающиеся на «Market». См. снимок экрана:

Фильтрация с использованием подстановочных знаков * и ~ выполняется аналогичным образом.

3,6 Фильтрация с помощью встроенного поля поиска

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

Как показано на снимке экрана ниже, вы хотите отфильтровать все ячейки, содержащие «Market». Поле поиска упростит вам выполнение этой задачи.

1. Выделите диапазон столбца, по которому нужно фильтровать данные, и нажмите «Данные» > «Фильтр», чтобы применить фильтр.

2. Нажмите стрелку раскрывающегося списка, введите «Market» в поле поиска и подтвердите выбор, нажав «OK».

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

Теперь все ячейки, содержащие «Market», отфильтрованы — как показано на снимке экрана ниже.

4. Копирование только видимых данных (скрытые и отфильтрованные данные игнорируются)

Копирование только видимых данных с помощью сочетаний клавиш

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

1. Выделите диапазон, из которого нужно скопировать только видимые ячейки. На этом этапе будут выделены как видимые, так и скрытые ячейки.

2. Одновременно нажмите клавиши «Alt» и «;» — теперь выделены только видимые ячейки.

3. Нажмите «Ctrl + C», чтобы скопировать выделенные ячейки, а затем — «Ctrl + V», чтобы вставить их.

Легко копируйте только видимые данные с помощью потрясающего инструмента

Знакомьтесь с функцией «Вставить в видимое» из набора «Kutools для Excel» — она позволяет легко копировать только видимые данные в Excel. Более того, с её помощью вы можете копировать и вставлять значения исключительно в видимые ячейки уже отфильтрованного диапазона.

1. Выделите диапазон фильтрации, из которого нужно скопировать только видимые ячейки, и нажмите «Kutools» > «Диапазон» > «Вставить в видимое» > «Все / Вставить только значения».

Для ячеек с формулами выберите «Все», чтобы скопировать и формулы, и их результаты, или выберите «Вставить только значения», чтобы скопировать лишь фактические данные.

2. В появившемся диалоговом окне «Вставить в видимый диапазон» выберите пустую ячейку для вставки скопированных данных и нажмите «OK».

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

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

5. Удаление скрытых или видимых строк после применения фильтра

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

Удаление всех скрытых строк из Текущий лист с помощью кода VBA

Приведённый ниже код VBA поможет удалить все скрытые строки с текущего листа в Excel.

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

1. На листе, содержащем строки, которые нужно удалить, одновременно нажмите «Alt + F11», чтобы открыть окно Microsoft Visual Basic for Applications.

2. В окне «Microsoft Visual Basic for Applications» выберите «Вставка» > «Модуль», затем вставьте приведённый ниже код VBA в открывшееся окно модуля.

Код VBA: удаление всех скрытых строк из Текущий лист

Sub RemoveHiddenRows()
    Dim xRow As Range
    Dim xRg As Range
    Dim xRows As Range
    On Error Resume Next
    Set xRows = Intersect(ActiveSheet.Range("A:A").EntireRow, ActiveSheet.UsedRange)
    If xRows Is Nothing Then Exit Sub
        For Each xRow In xRows.Columns(1).Cells
            If xRow.EntireRow.Hidden Then
                If xRg Is Nothing Then
                    Set xRg = xRow
                Else
                    Set xRg = Union(xRg, xRow)
                End If
            End If
        Next
        If Not xRg Is Nothing Then
            MsgBox xRg.Count & " hidden rows have been deleted", , "Kutools for Excel"
            xRg.EntireRow.Delete
        Else
            MsgBox "No hidden rows found", , "Kutools for Excel"
        End If
End Sub

3. Нажмите клавишу «F5», чтобы запустить код.

4. Появится диалоговое окно «Kutools для Excel» с указанием количества удалённых скрытых строк. Нажмите «OK», чтобы закрыть его.

Теперь удалены все скрытые строки — как автоматически, так и вручную скрытые.

Удаление видимых строк после фильтрации с помощью функции «Перейти»

Если вы хотите удалить только видимые строки в заданном диапазоне, воспользуйтесь функцией «Перейти».

1. Выделите диапазон фильтрации, из которого нужно удалить видимые строки, и нажмите клавишу «F5», чтобы открыть диалоговое окно «Перейти».

2. В диалоговом окне «Перейти» нажмите кнопку «Специальная вставка».

3. В диалоговом окне «Специальная вставка» выберите опцию «Только видимые ячейки» и нажмите «OK».

4. Теперь выбраны все видимые ячейки. Щелкните правой кнопкой мыши, выберите диапазон и нажмите «Удалить строку» в контекстном меню.

Затем удаляются все видимые ячейки.

Легко удаляйте скрытые или видимые строки после фильтрации с помощью удивительного инструмента

Вышеуказанные методы утомительны и отнимают много времени. Воспользуйтесь функцией «Удалить скрытые строки/столбцы» из надстройки «Kutools для Excel»! С её помощью вы легко удалите скрытые или видимые строки не только в выбранном диапазоне, но и на текущем листе, в нескольких выбранных листах или во всей книге. Выполните следующие действия.

1. Выделите диапазон, из которого нужно удалить все скрытые или видимые строки.

Примечания:

2. Нажмите «Kutools» > «Delete» > «Удалить скрытые строки и столбцы».

3. В диалоговом окне «Удалить скрытые строки/столбцы» выполните следующую настройку.

4. Затем все видимые и скрытые строки удаляются одновременно, после чего появляется диалоговое окно с указанием количества удалённых строк — нажмите «OK», чтобы завершить операцию.

6. Фильтрация данных сразу по нескольким листам

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

Предположим, ваша книга содержит три листа, как показано на рисунке ниже, и вы хотите одновременно отфильтровать данные на всех трёх листах по условию «Product = KTE». Приведённый ниже код VBA поможет вам легко справиться с этой задачей.

1. Нажмите «Alt» + «F11», чтобы открыть окно Microsoft Visual Basic для приложений.

2. В окне «Microsoft Visual Basic for Applications» выберите «Insert» > «Module», затем скопируйте приведённый ниже код VBA в окно модуля.

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

Sub apply_autofilter_across_worksheets()
'Updateby Extendoffice 20210518
    Dim xWs As Worksheet
    On Error Resume Next
    For Each xWs In Worksheets
        xWs.Range("A1").AutoFilter 1, "=KTE"
    Next
End Sub

Примечание: строка «Xws.Range(“A1”).AutoFilter 1, “=KTE”» в коде означает, что вы будете фильтровать данные в столбце A по критерию =KTE, где число 1 соответствует номеру столбца A. Вы можете изменить эти параметры в соответствии со своими задачами. Например, если вы хотите отфильтровать все числа, превышающие 500, в столбце B, замените эту строку на «Xws.Range(“B1”).AutoFilter 2, “>500”».

3. Нажмите клавишу «F5», чтобы запустить код.

Указанные столбцы теперь одновременно отфильтрованы на всех листах в текущей книге. Результаты — ниже.

7. Повторное применение фильтра после внесения изменений в данные

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

Повторное применение фильтра вручную с помощью команды «Reapply»

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

Нажмите «Data» > «Reapply», чтобы повторно применить фильтр к текущему листу.

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

Автоматическое повторное применение фильтра с помощью кода VBA

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

1. На листе, содержащем фильтр, который нужно применять автоматически, щелкните правой кнопкой мыши по ярлыку листа и выберите «Просмотреть код».

2. В открывшемся окне «Microsoft Visual Basic for Applications» скопируйте приведённый ниже код VBA в окно редактора кода.

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

Private Sub Worksheet_Change(ByVal Target As Range)
   Sheets("Sheet2").AutoFilter.ApplyFilter
End Sub

Примечание: в коде «Sheet2» — это имя Текущий лист. Вы можете изменить его по своему усмотрению.

3. Нажмите клавиши «Alt» + «Q», чтобы закрыть окно Microsoft Visual Basic for Applications.

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

8. Очистка или удаление фильтра

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

8,1 Очистка фильтра из столбца

После применения фильтра к столбцу, если вы хотите его очистить, щелкните значок фильтра и выберите «Clear Filter From “Header name”» в раскрывающемся меню.

8,2 Очистка всех фильтров на листе

Если вы применили фильтр к нескольким столбцам и хотите очистить их все сразу, выберите «Data» > «Clear».

Все фильтры будут сброшены, как показано на рисунке ниже.

8,3 Очистка фильтров со всех листов в Текущая книга

Допустим, вы применили фильтры на нескольких листах книги и хотите мгновенно очистить их все. Приведённый ниже код VBA — именно то, что вам нужно.

1. Откройте книгу, из которой требуется очистить все фильтры, и одновременно нажмите клавиши «Alt» + «F11».

2. В открывшемся окне «Microsoft Visual Basic for Applications» выберите «Insert» > «Module», затем скопируйте приведённый ниже код VBA в окно модуля.

Код VBA: очистка фильтров со всех листов в Текущая книга

Sub Auto_Open()
'Updated by Extendoffice 20201113
    Dim xAF As AutoFilter
    Dim xFs As Filters
    Dim xLos As ListObjects
    Dim xLo As ListObject
    Dim xRg As Range
    Dim xWs As Worksheet
    Dim xIntC, xF1, xF2, xCount As Integer
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each xWs In Application.Worksheets
        xWs.ShowAllData
        Set xLos = xWs.ListObjects
        xCount = xLos.Count
        For xF1 = 1 To xCount
         Set xLo = xLos.Item(xF1)
         Set xRg = xLo.Range
         xIntC = xRg.Columns.Count
         For xF2 = 1 To xIntC
            xLo.Range.AutoFilter Field:=xF2
         Next
        Next
    Next
    Application.ScreenUpdating = True
End Sub

3. Нажмите клавишу «F5», чтобы запустить код — все фильтры будут удалены со всех листов текущей книги.

8,4 Удаление всех фильтров на листе

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

Удаление всех фильтров на листе путём отключения функции фильтрации

Нажмите «Data» > «Filter», чтобы отключить фильтрацию (кнопка «Filter» перестанет быть выделенной).

Удаление всех фильтров на листе с помощью сочетания клавиш

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

На листе с фильтрами, которые нужно удалить, одновременно нажмите клавиши «Ctrl» + «Shift» + «L».

Все фильтры на текущем листе немедленно удаляются.

Лучшие инструменты повышения продуктивности в Office

🤖	KUTOOLS AI Помощник: Преобразуйте Анализ данных с помощью:Интеллектуального выполнения   |  Генерации кода|  Создания пользовательские формулы  |  Анализа данных и построения диаграмм|  Вызова Расширенные функции…
	Популярные функции:Поиск, выделение или Отметить дубликаты   |  Удалить пустые строки   |  Объединить столбцы или ячеек без потери данных   |  Округление без использования формул…
	Супер ПОИСК:VLookup по нескольким критериям  |  VLookup по нескольким значениям  |   VLookup по нескольким листам   |   Распознавание нечетких соответствий…
	Расширенный раскрывающийся список:Быстрое создание выпадающего списка   |  Зависимый выпадающий список   |  Выпадающий список с множественным выбором…
	Управление столбцами:Добавление заданного количества столбцов|Перемещение столбцов|Переключение видимости скрытых столбцов|Сравнение диапазонов и столбцов…
	Избранные функции:Сетка фокусировки   |  Просмотр дизайна   |Улучшенная строка формулы   | Управление рабочими книгами и листами   |  Библиотека ресурсов(автотекст)|  Выбор даты   |  Объединить листы  |  Шифрование/Расшифровать ячейки   | Отправка писем по списку   |  Супер фильтр   |   Специальный фильтр(Фильтр ячеек с жирным шрифтом/курсив/зачёркивание…) …
	Лучшие наборы инструментов 15:12 Текстовыеинструменты(Добавить текст,Удалить определенные символы, …)|   50+Типыдиаграмм(Диаграмма Ганта, …)|   40+ Практические формулы(Рассчитать возраст на основе даты рождения, …)|   19 Инструментывставки(Вставить QR-код,Вставка изображения по пути, …)|   12 Инструментыпреобразования(Преобразовать в слова,Конвертация валют, …)|   7 Объединить и разделитьИнструменты(Расширенное объединение строк,Разделить ячейки, …)|… и многое другое

Используйте Kutools на предпочитаемом языке — поддержка английского, испанского, немецкого, французского, китайского и ещё 40+ языков!

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

Подробнее о Kutools для Excel…Бесплатная загрузка…

---

Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу

• Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
• Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
• Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!

Подробнее о Office Tab…Бесплатная загрузка…

---

Все надстройки Kutools — один установщик

Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.

• Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
• Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
• Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
• 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
• Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек

Подробнее о Kutools for Office…Бесплатная загрузка…