Как сделать расширенный фильтр в excel

Использование расширенных условий фильтрации

Если данные, которые нужно отфильтровать, должны иметь сложные условия (например, тип = “фрукты” или продавец = “Белов”), можно использовать диалоговое окно ” Расширенный фильтр “.

Чтобы открыть диалоговое окно Расширенный фильтр , щелкните данные > Дополнительно.

Продавец = “Егоров” ИЛИ Продавец = “Грачев”

Тип = “Фрукты” И Продажи > 1000

Тип = “Фрукты” ИЛИ Продавец = “Грачев”

(Продажи > 6000 И Продажи 3000) ИЛИ
(Продавец = “Батурин” и Sales > 1500)

Продавец = имя со второй буквой “г”

Обзор

Действие команды Дополнительно отличается от действия команды Фильтр в некоторых важных аспектах.

Она отображает диалоговое окно Расширенный фильтр, а не меню “Автофильтр”.

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

Образец данных

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

Данные включают четыре пустых строки над диапазоном, который будет использоваться в качестве диапазона условий (a1: C4), и диапазон списка (A6: C10). Диапазон условий содержит названия столбцов и по крайней мере одну пустую строку между значениями условий и диапазоном списка.

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

Операторы сравнения

Операторы сравнения используются для сравнения двух значений. Результатом сравнения является логическое значение: ИСТИНА либо ЛОЖЬ.

= (знак «больше или равно»)

Больше или равно

Использование знака равенства для ввода текста или значения

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

где ввод — искомый текст или значение. Например:

Вводится в ячейку

Вычисляется и отображается

Учет регистра

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

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

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

Создание условий с помощью формулы

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

Формула должна возвращать результат ИСТИНА или ЛОЖЬ.

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

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

Если в формуле используется название столбца, а не относительная ссылка на ячейку или имя диапазона, в ячейке с условием будет выведено значение ошибки #ИМЯ? или #ЗНАЧ!. Вы можете пропустить эту ошибку, так как она не влияет на фильтрование диапазона списка.

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

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

Несколько условий, один столбец, любое из условий истинно

Логическое выражение: (Продавец = “Егоров” ИЛИ Продавец = “Грачев”)

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

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

Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне A6:C10.

На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.

Выполните одно из следующих действий:

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

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

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

В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$C$3.

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

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

Несколько условий, несколько столбцов, все условия истинны

Логическое выражение: (Тип = “Фрукты” И Продажи > 1000)

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

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

Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне A6:C10.

На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.

Выполните одно из следующих действий:

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

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

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

В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$C$2.

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

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

Несколько условий, несколько столбцов, любое из условий истинно

Логическая логика: (Тип = “Фрукты” ИЛИ Продавец = “Грачев”)

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

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

Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне списка A6:C10.

На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.

Выполните одно из следующих действий:

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

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

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

В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$B$3.

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

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

Несколько наборов условий, один столбец во всех наборах

Логическое выражение: ( (Продажи > 6000 И Продажи 6 000

Несколько наборов условий, несколько столбцов в каждом наборе

Логическое выражение: ( (Продавец = “Егоров” И Продажи > 3000) ИЛИ (Продавец = “Грачев” И Продажи > 1500) )

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

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

Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне списка A6:C10.

На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.

Выполните одно из следующих действий:

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

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

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

В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. В примере введите $A $1: $C $3.To, чтобы временно переместить диалоговое окно ” Расширенный фильтр “, а затем выбрать диапазон условий, нажмите кнопку Свернуть диалоговое окно .

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

Функция расширенного фильтра в Microsoft Excel

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

Использование расширенного фильтра в Excel

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

Шаг 1: Создание таблицы с условиями отбора

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

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

Шаг 2: Запуск расширенного фильтра

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

Переходим на вкладку «Данные» и на ленте в блоке инструментов «Сортировка и фильтр» жмем по «Дополнительно».

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

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

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

После этого действия исходная таблица осталась без изменений, а отфильтрованные данные выводятся в отдельную таблицу.

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Фильтрация данных в Excel

В Excel предусмотрено три типа фильтров:

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

Автофильтр

1. Выделить одну ячейку из диапазона данных.
2. На вкладке Данные [Data] найдите группу Сортировка и фильтр [Sort&Filter].
3. Щелкнуть по кнопке Фильтр [Filter] .

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

1. Выбрать условие фильтрации.

Варианты фильтрации данных

• Фильтр по значению – отметить флажком нужные значения из столбца данных, которые высвечиваются внизу диалогового окна.
• Фильтр по цвету – выбор по отформатированной ячейке: по цвету ячейки, по цвету шрифта или по значку ячейки (если установлено условное форматирование).
• Можно воспользоваться строкой быстрого поиска
• Для выбора числового фильтра, текстового фильтра или фильтра по дате (в зависимости от типа данных) выбрать соответствующую строку. Появится контекстное меню с более детальными возможностями фильтрации:

1. При выборе опции Числовые фильтры появятся следующие варианты фильтрации: равно, больше, меньше, Первые 10… [Top 10…] и др.
2. При выборе опции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит. , начинается с… и др.
3. При выборе опции Фильтры по дате варианты фильтрации – завтра, на следующей неделе, в прошлом месяце и др.
4. Во всех перечисленных выше случаях в контекстном меню содержится пункт Настраиваемый фильтр… [Custom…], используя который можно задать одновременно два условия отбора, связанные отношением И [And] – одновременное выполнение 2 условий, ИЛИ [Or] – выполнение хотя бы одного условия.

Если данные после фильтрации были изменены, фильтрация автоматически не срабатывает, поэтому необходимо запустить процедуру вновь, нажав на кнопку Повторить [Reapply] в группе Сортировка и фильтр на вкладке Данные.

Отмена фильтрации

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

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

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

Срезы

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

Создание срезов

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

Для этого нужно выполнить следующие шаги:

Выделить в таблице одну ячейку и выбрать вкладку Конструктор [Design].

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

Форматирование срезов

1. Выделить срез.
2. На ленте вкладки Параметры [Options] выбрать группу Стили срезов [Slicer Styles], содержащую 14 стандартных стилей и опцию создания собственного стиля пользователя.

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

Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.

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

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

Задание условий фильтрации

1. В диалоговом окне Расширенный фильтр выбрать вариант записи результатов: фильтровать список на месте [Filter the list, in-place] или скопировать результат в другое место [Copy to another Location].

1. Указать Исходный диапазон [List range], выделяя исходную таблицу вместе с заголовками столбцов.
2. Указать Диапазон условий [Criteria range], отметив курсором диапазон условий, включая ячейки с заголовками столбцов.
3. Указать при необходимости место с результатами в поле Поместить результат в диапазон [Copy to], отметив курсором ячейку диапазона для размещения результатов фильтрации.
4. Если нужно исключить повторяющиеся записи, поставить флажок в строке Только уникальные записи [Unique records only].

Источники:

https://support.office.com/ru-ru/article/%D0%98%D1%81%D0%BF%D0%BE%D0%BB%D1%8C%D0%B7%D0%BE%D0%B2%D0%B0%D0%BD%D0%B8%D0%B5-%D1%80%D0%B0%D1%81%D1%88%D0%B8%D1%80%D0%B5%D0%BD%D0%BD%D1%8B%D1%85-%D1%83%D1%81%D0%BB%D0%BE%D0%B2%D0%B8%D0%B9-%D1%84%D0%B8%D0%BB%D1%8C%D1%82%D1%80%D0%B0%D1%86%D0%B8%D0%B8-4c9222fe-8529-4cd7-a898-3f16abdff32b
https://lumpics.ru/advanced-filter-in-excel/
https://micro-solution.ru/excel/ranges/filter