На этом шаге мы рассмотрим расширенную фильтрацию.
Если необходимо выполнить операцию, выходящую за рамки автофильтра, придется прибегнуть к помощи средств расширенной фильтрации. Расширенный фильтр гораздо более гибкий, чем автофильтр, однако при его использовании требуется выполнять больше подготовительных действий. Расширенный фильтр позволяет выполнить следующее:
- Определить более сложный критерий фильтрации.
- Установить вычисляемый критерий фильтрации.
- Перемещать копии строк, отвечающих определенному критерию, в другое место.
Установка диапазона критериев
Прежде чем использовать средство расширенной фильтрации, необходимо задать диапазон критериев. Диапазон критериев - это специально отведенная область рабочего листа, отвечающая определенным требованиям. Диапазон критериев используется Excel для фильтрации списка и должен отвечать следующим требованиям:
- Состоять, по крайней мере, из двух строк, первая из которых должна содержать все или некоторые названия полей списка.
- Остальные строки должны содержать критерии фильтрации.
Хотя Вы можете отвести для диапазона критериев любое место на рабочем листе, желательно не задействовать при этом строки, используемые списком. Поскольку некоторые из строк списка будут скрыты при фильтрации, то может оказаться, что Ваш диапазон критериев стал невидимым после фильтрации. Поэтому размещайте диапазон критериев над или под списком. На рисунке 1 показан диапазон критериев, расположенный в ячейках А1:D2 над списком, который его использует. Обратите внимание, что в диапазоне критериев указаны не все названия полей списка. Поля, не используемые при фильтрации, можно не помещать в диапазон критериев.
Рис.1. Диапазон критериев списка
Чтобы выполнить фильтрацию, выберите команду Данные | Фильтр | Расширенный фильтр. Появится диалоговое окно Расширенный фильтр (рис. 2).
Рис.2. Диалоговое окно Расширенный фильтр
Определите диапазон списка и диапазон критериев и убедитесь, что установлен переключатель Фильтровать список на месте. Щелкните на кнопке ОК, и список будет отфильтрован по заданным критериям.
Множественный критерий отбора
Если в диапазоне критериев используется несколько строк, критерии в каждой строке соединены оператором ИЛИ. На рисунке 3 показан диапазон критериев A1:D3 с двумя строками критериев.
Рис.3. В этом диапазоне критериев содержится два набора критериев
В этом примере строки отфильтрованного списка отвечают следующим требованиям:
- В поле Месяц содержится значение Январь И в поле Товар - значение Товар В.
- ИЛИ в поле Месяц содержится значение Февраль. И при этом значения в поле Всего превышают 1000.
Это пример фильтрации, которая не может быть выполнена с помощью автофильтра. Диапазон критериев может иметь любое количество строк, каждая из которых соединена одна с другой оператором ИЛИ.
Типы критериев
Элементы, которые указываются в диапазоне критериев, можно разделить на следующие типы:
- Текстовые или числовые критерии. При фильтрации используется сравнение с числом или строковой (текстовой) константой с помощью операторов, таких как равно (=), больше чем (>), не равно (<>) и т.д.
- Вычисляемые критерии. Фильтрация, использующая вычисления любого рода.
Текстовые или числовые критерии
В таблице 1 представлен список операторов сравнения, которые можно использовать в текстовых или числовых критериях.
| Оператор | Тип сравнения |
|---|---|
| = | Равно |
| > | Больше чем |
| >= | Больше либо равно |
| < | Меньше |
| <= | Меньше либо равно |
| <> | Не равно |
В таблице 2 показаны примеры строковых (текстовых) критериев.
| Критерий | Действие |
|---|---|
| >K | Cлова, начинающиеся с букв от Л до Я |
| <>C | Все слова, кроме начинающихся с буквы С |
| ="Январь" | Все слова "Январь" |
| См* | Слова, начинающиеся с букв "См" |
| с*с | Слова, начинающиеся с буквы "с" и заканчивающиеся буквой "c" |
| c?с | Слова из трех букв, начинающиеся с буквы "с" и заканчивающиеся буквой "c" |
Вычисляемый критерий
Вычисляемый критерий может сделать операцию фильтрации более гибкой. Вычисляемый критерий основывается на одном или нескольких вычислениях. На рисунке 4 показан пример простого списка, состоящего из кодов проектов, даты начала, даты окончания и количества привлеченных к проекту ресурсов.
Рис.4. Этот список будет отфильтрован с использованием вычисляемого критерия
Над списком в диапазоне А1:А2 помещен диапазон критериев. Однако обратите внимание на то, что в диапазоне критериев используются не названия полей списка, а название нового поля. Вычисляемый критерий, по существу, вычисляет для списка новое поле. Поэтому следует помещать название поля в первую строку диапазона критериев.
В ячейке А2 содержится следующая формула: = С5 - В5 + 1 >= 30
Эта формула возвращает логическое значение Истина или Ложь. Формула ссылается на ячейки с данными, расположенными в первой строке списка, следующей после строки заголовков. Когда список будет отфильтрован, в нем будут отображены только те проекты, продолжительность которых (Дата_окончания - Дата_начала + 1) больше либо равна 30 дням. Другими словами, операция сравнения основана на вычислениях.
Если необходимо отфильтровать список так, чтобы были показаны только проекты, которые используют ресурсы больше среднего, примените следующую формулу: = D5 > СРЗНАЧ(D:D)
Отфильтрованный в результате список будет содержать только те строки, в которых значение в поле Ресурсы превосходит среднее значение этого же поля.
При использовании вычисляемых критериев рекомендуется помнить некоторые правила:
- Не используйте заголовки полей списка в диапазоне критериев. Создайте новый заголовок или просто оставьте пустую ячейку.
- Можно использовать любое количество вычисляемых критериев, а также сочетания вычисляемых критериев с невычисляемыми.
- Не обращайте внимание на значения, возвращаемые формулами в диапазоне критериев. Они ссылаются на первую строку списка.
- Если Ваша вычисляемая формула ссылается на значение вне списка, используйте абсолютные, а не относительные ссылки. Например, вместо C1 используйте ссылку $C$1.
- При создании формул вычисляемых критериев используйте первую строку списка (не строку заголовков). Используйте относительные, а не абсолютные ссылки. Например вместо $C$5 используйте ссылку C5.
Другие возможности расширенной фильтрации
В диалоговом окне Расширенный список представлены еще две опции:
- Скопировать результат в другое место.
- Только уникальные записи.
Копирование отобранных строк
Если Вы выберите переключатель Скопировать результат в другое место в диалоговом окне Расширенный фильтр, то отобранные строки будут скопированы в другое место активного рабочего листа или на другой лист. Место определяется в поле Поместить результат в диапазон. Обратите внимание, что при использовании этой опции сам список не фильтруется.
Отображение только уникальных строк
При выборе опции Только уникальные записи все одинаковые строки, отвечающие определенному критерию, будут скрыты. Если Вы не установили диапазон критериев, то в списке будут скрыты все одинаковые строки.
На следующем шаге мы рассмотрим использование в списках функций баз данных.
