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

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

Правила фильтрации с помощью расширенного фильтра:

1. Вставить несколько строк выше списка.

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

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

4. Ниже списка, отступив строку, необходимо скопировать имена столбцов, которые нужно вывести.

5. Указать ячейку в фильтруемом списке.

6. В пункте меню Данные выбрать пункт Фильтр, затем команду Расширенный фильтр.

7. В диалоговом окне установите переключатель Обработка в положение Фильтровать список на месте, чтобы скрыть ненужные строки (рис. 8).

Рис. 8. Диалоговое окно Расширенный фильтр

с обработкой Фильтровать список на месте

Чтобы результат фильтрации поместить в другое место, необходимо в диалоговом окне Расширенный фильтр выбрать Скопировать результат в другое место, указать поле Поместить результат в диапазон, затем верхнюю левую ячейку области вставки для вывода всех полей списка (рис.9).

Рис. 9. Диалоговое окно Расширенный фильтр

с обработкой Скопировать результат в другое место

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

8. Ввести в поле Диапазон критериев ссылку на диапазон условий отбора, включая заголовки.

Условия отбора расширенного фильтра.

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

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

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

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

Формула, используемая для создания условия отбора, должна использовать относительные ссылки на соответствующие поля первой записи списка. Все остальные ссылки в формуле должны быть абсолютными. Например, условие отбора =F7 > СРЗНАЧ($E$7:$F$21) выводит на экран строки, имеющие в столбце F значения большие, чем среднее значение величин в ячейках F7:F21. Формула должна возвращать результат ИСТИНА или ЛОЖЬ.

Таким образом, поиск с помощью расширенного фильтра предполагает следующее.

1. Подготовить диапазон критериев для расширенного фильтра:

– первая строка должна содержать заголовки полей, по которым будет производиться отбор (точное соответствие заголовкам полей списка);

– условия критерия записываются в пустые строки под подготовительной строкой заголовка.

2. Поместить указатель в список (или выделить весь список).

3. Выполнить команду Данные – Фильтр – Расширенный фильтр.

4. В диалоговом окне Расширенный фильтр задать необходимые параметры.

5. Нажать на кнопку ОК.

Пример 2. В исходной базе данных (рис. 1), используя Расши ренный фильтр, показать записи о проданном товаре в январе в количестве от 10 до 42 шт.

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

Рис. 10. Подготовка условий отбора

Далее необходимо выполнить команду Данные – Фильтр – Расширенный фильтр. В результате появится диалоговое окно Расширенный фильтр, в котором необходимо указать параметры: Обработка, Исходный диапазон, Диапазон условий, Поместить результат в диапазон (рис. 11).

Рис. 11. Пример использования расширенного фильтра

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

Рис. 12. Результат выполнения расширенного фильтра

MS Excel предоставляет широкие возможности для проведения анализа данных, находящихся в списке. К средствам анализа относятся:

 обработка списка с помощью различных формул и функций;

 построение диаграмм и использование карт MS Excel;

 проверка данных рабочих листов и рабочих книг на наличие ошибок;

 структуризация рабочих листов;

 автоматическое подведение итогов;

 консолидация данных;

 сводные таблицы.

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