Фильтрация данных с помощью Расширенного фильтра
С помощью команды Автофильтр на столбец можно наложить до двух условий. Если требуется наложить три или более условия, скопировать записи в другое место или отобрать данные на основе вычисляемого значения, используют Расширенный фильтр.
Перед применением расширенного фильтра на листе MS Excel необходимо создать таблицу условий (см. Приложение «Примеры сложных условий отбора»). Сама таблица условий состоит из строки заголовков и условий фильтрации. Список с данными, которые необходимо отфильтровать, называют таблицей исходных данных.
Требование 1: При работе с расширенным фильтром очень важно, чтобы имена полей таблицы исходных данных и таблицы условий совпадали!
Требование 2: Между таблицами обязательно должна быть хотя бы одна пустая строка (или столбец)!
Требование 3: Таблицы не должны иметь общих границ!
После создания таблицы условий с выполнением всех трех требований:
Указать любую ячейку в таблице исходных данных.
В меню Данные Фильтр Расширенный фильтр.
В
результате выделится вся таблица
исходных данных и появится диалоговое
окно Расширенный
фильтр.В окне Расширенный фильтр:
В области окна Обработка установить переключатель в положение фильтровать список на месте (чтобы показать результат фильтрации, скрыв ненужные строки) или скопировать результат в другое место (чтобы все записи, удовлетворяющие условию были выведены в указанное место листа MS Excel).
В поле Исходный диапазон должны быть указаны ячейки таблицы исходных данных, включая ячейки с именами полей.
В поле Диапазон условий должен быть указан диапазон ячеек таблицы условий, включая ячейки с именами полей.
Если включен режим скопировать результат в другое место, то в поле Поместить результат в диапазон необходимо указать адрес одной ячейки, начиная с которой вправо и вниз будет вставлена результирующая таблица.
Нажать ОК.
!!! |
Если в результате фильтрации должны быть выведены не все поля таблицы исходных данных, то на шаге 1 (или в окне Расширенный фильтр в поле Исходный диапазон) необходимо самостоятельно выделить нужный диапазон. |
Фильтрация данных с использованием вычисляемого критерия
В условия отбора расширенного фильтра может входить условие, накладываемое на возвращаемое формулой значение.
Поскольку будет использоваться инструмент Расширенный фильтр, то необходимо создать таблицу условий с соблюдением следующих требований:
Требование 1: При работе с расширенным фильтром с использованием вычисляемого критерия очень важно, чтобы имена полей таблицы исходных данных и таблицы условий НЕ совпадали!
Требование 2: Между таблицами обязательно должна быть хотя бы одна пустая строка (или столбец)!
Требование 3: Таблицы не должны иметь общих границ!
В качестве условия отбора будет выступать формула, а точнее – логическое выражение. В результате ее работы в ячейку возвращается значение или «ЛОЖЬ», или «ИСТИНА». В результате работы фильтра по заданному условию будут отобраны все записи, содержащие «ИСТИНА». Например, формула может иметь такой вид =Е5>CРЗНАЧ($E$5:$E$14). В результате применения этой формулы будут отобраны все записи, у которых значения по полю, на которое накладывается условие (в данном случае оно находится в столбце Е), превышают среднее значение по всем данным этого поля. Таким образом, таблица условий примет вид4:
-
Критерий
=Е5>CРЗНАЧ($E$5:$E$14)
После создания таблицы условий и выполнения всех трех требований:
Указать любую ячейку в таблице исходных данных.
В меню Данные Фильтр Расширенный фильтр.
В окне Расширенный фильтр:
в области окна Обработка установить переключатель, определив место вывода результата фильтрации;
в поле Исходный диапазон указать диапазон ячеек таблицы исходных данных;
в поле Диапазон условий указать диапазон ячеек таблицы условий;
при необходимости в поле Поместить результат в диапазон указать адрес одной ячейки, начиная с которой вправо и вниз будет вставлена результирующая таблица.
Нажать ОК.