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

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

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

1. Как добавить фильтр в Excel

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

1.1 Команда «Фильтр» на вкладке «Данные»

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

1.2 Команда «Фильтр» на вкладке «Главная»

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

1.3 Добавление фильтра с помощью сочетания клавиш

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

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

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

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

2.1 Применение фильтра к одному столбцу (одно условие)

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

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

Теперь фильтр применён к столбцу 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. С её помощью вы сможете легко фильтровать несколько столбцов по условиям «И» и «ИЛИ» одновременно.

1. После установки Kutools для Excel выберите «Kutools Plus» > «Супер фильтр».

После этого панель «Супер фильтр» появится справа от рабочего листа.

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

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

2.1) Отметьте поле «Указано», нажмите кнопку для выбора исходного диапазона или таблицы, которую будете фильтровать;

2.2) В выпадающем списке «Отношение» выберите «ИЛИ»;

3.3) Щёлкните по первой пустой строке в первой группе и укажите нужные условия;

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

В нашем примере я выбираю «Product» > «Текст» > «Равно» в трёх выпадающих списках, затем ввожу «AAA-1» в текстовое поле. См. скриншот:

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

2.5) Нажмите кнопку «Фильтр» для начала фильтрации.

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

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

Подробнее об этой функции читайте здесь.

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

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

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

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

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

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

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

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

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

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

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

3.1.2 Фильтрация с учетом регистра

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

3.1.2.1 Фильтрация определённого текста с учетом регистра с помощью формулы и команды «Фильтр»

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

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

=EXACT(B2,UPPER(B2))

Примечание: Эта формула помогает определить, содержит ли ячейка только символы в верхнем регистре. Если да — результат будет «TRUE», иначе — «FALSE».

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

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

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

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

Теперь в столбце 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, чтобы легко фильтровать ячейки по длине текста.

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

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

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

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

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

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

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

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

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

Совет: В данном примере для фильтрации чисел от15000 до20000 я ввожу15000 и20000 в соответствующие поля.

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

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. Щёлкните стрелку раскрывающегося списка, отметьте только флажок рядом с опцией «TRUE», затем нажмите «ОК».

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

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 for Applications».

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

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

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

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

=HasStrike(B2)

Примечание: Если соответствующая ячейка имеет эффект зачёркнутого шрифта, возвращается «TRUE», иначе — «FALSE».

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

6. Щёлкните стрелку раскрывающегося списка > отметьте только флажок рядом с опцией «TRUE» > нажмите «ОК». См. скриншот:

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

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 for Applications» выберите «Вставка» > «Модуль», затем скопируйте приведённый ниже VBA-код в окно кода.

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

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

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

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 for Applications».

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

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

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

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

=HasFormula(C2)

Как показано на скриншоте выше, результаты — «FALSE» и «TRUE», что означает: если соответствующая ячейка содержит формулу, возвращается TRUE, иначе — FALSE.

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

6. Щёлкните стрелку раскрывающегося списка, отметьте только флажок рядом с опцией «TRUE», затем нажмите «ОК».

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

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

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

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

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

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

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

Фильтровать все ячейки с комментариями, подробнее...

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

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

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

В Excel всего3 подстановочных знака:

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

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

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

2. Нажмите «Данные» > «Дополнительно».

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

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

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

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

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

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

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

2. Щёлкните стрелку раскрывающегося списка, введите «Market» в поле поиска и нажмите «ОК».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Следующий VBA-код поможет удалить все скрытые строки на текущем листе в Excel.

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

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 с информацией о количестве удалённых скрытых строк, нажмите «ОК» для закрытия.

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

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

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

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

2. В окне «Перейти» нажмите кнопку «Выделить».

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

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

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

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

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

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

Примечания:

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

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

4. Теперь все видимые или скрытые строки удалены сразу. При этом появится диалоговое окно с информацией о количестве удалённых строк, нажмите «ОК» для завершения операции.

6. Фильтрация на нескольких листах

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

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

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

2. В окне «Microsoft Visual Basic for Applications» выберите «Вставка» > «Модуль», затем скопируйте приведённый ниже 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”.AutoFilter1, “=KTE”)» в коде означает, что фильтрация будет по столбцу A с условием =KTE, а число1 — это номер столбца A. Вы можете изменить их по своему усмотрению. Например, чтобы отфильтровать все числа больше500 в столбце B, измените строку на «Xws.Range(“B1”.AutoFilter2, “>500”)».

3. Нажмите клавишу «F5» для запуска кода.

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

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

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

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

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

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

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

Автоматически повторно применять фильтр с помощью 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».

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

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

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

8.1 Очистить фильтр из столбца

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

8.2 Очистить все фильтры на листе

Если фильтр применён к нескольким столбцам и вы хотите очистить их все одновременно, нажмите «Данные» > «Очистить».

Теперь все фильтры очищены, как показано на скриншоте ниже.

8.3 Очистить фильтры со всех листов в текущей книге

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

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

2. В открывшемся окне «Microsoft Visual Basic for Applications» выберите «Вставка» > «Модуль», затем скопируйте приведённый ниже 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 Удалить все фильтры на листе

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

Удалить все фильтры на листе, отключив функцию фильтра

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

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

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

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

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