Ключом к использованию расширенной фильтрации является знание способов настройки диапазона условий. Пользователю предоставлена достаточная свобода, однако некоторые моменты не вполне интуитивно понятны. Вам будет предложено множество примеров, которые помогут понять процесс создания диапазона условий, позволяющего извлечь необходимую информацию.
Примечание
Принцип использования отдельного диапазона условий для расширенного фильтра возник вместе с появлением первой версии Lotus 1-2-3, еще два десятилетия назад. Позднее этот метод был реализован в приложении Excel и никогда не изменялся, даже несмотря на тот факт, что определение условий расширенного фильтра – одно из самых непростых заданий при работе в Excel. Хотя следует отметить, что использования стандартных средств фильтрации Excel вполне достаточно для решения подавляющего большинства задач.
Определение одного критерия
Примеры, приведенные в этом подразделе, имеют общее условие отбора записей. Другими словами, выбор необходимой записи определяется содержимым только одного столбца.
Примечание
Для выполнения подобной фильтрации можно использовать стандартные средства.
Чтобы выбрать только те записи, которые в определенных полях содержат конкретные значения, введите имя столбца в первую строку диапазона условий, а соответствующее значение – во вторую строку. К примеру, диапазон условий (А1:А2), показанный на рисунке, позволяет отобрать только те записи, которые в столбце Кол-во комнат содержат значение 4.
Обратите внимание, что диапазон условий не обязательно должен включать заголовки всех столбцов. Но если вы периодически используете разные наборы условий, гораздо удобнее перечислить все заголовки полей в первой строке диапазона условий.
Использование операторов сравнения
Чтобы правильно определить условия поиска, используйте операторы сравнения. Например, можно задать отбор тех записей, которые соответствуют одному из следующих условий:
• квартиры, которые имеют не менее четырех комнат;
• квартиры, имеющие площадь менее 60 квадратных метров;
• квартиры по цене не более 200 тысяч рублей.
Чтобы выбрать записи, которые относятся к квартирам, имеющим как минимум четыре комнаты, введите в ячейку А1 строку Кол-во комнат, а в ячейку А2 – значение >=4.
В таблице перечислены операторы, которые используются для сравнения текстовых или числовых значений. В том случае, если ни один из этих операторов сравнения не указан явно, по умолчанию Excel использует знак равенства (=).
Использование символов макроподстановки
Условия, представленные в виде текста, могут также содержать два символа макроподстановки: звездочка (*) соответствует любому количеству произвольных символов; вопросительный знак (?) соответствует любому, но только одному символу.
В таблице приведены примеры условий, содержащих текстовые значения. Некоторые из них нечасто встречаются на практике. Например, чтобы выбрать записи, содержащие единственный символ, в качестве условия необходимо ввести формулу (см. последнюю запись в таблице).
Примечание
Имейте в виду, что при сравнении текстовых значений регистр символов не учитывается. Например, символы се* могут соответствовать как слову Семеныч, так и слову СЕКРЕТНО.
Определение множества условий
Достаточно часто возникает потребность в выборе записей, которые удовлетворяют сразу нескольким условиям и основываются на соответствии более чем одному столбцу или на соответствии нескольких значений одного и того же столбца. В таких условиях используются неявные логические операторы И или ИЛИ. Ниже приведены примеры использования нескольких условий при обращении к базе данных недвижимости:
• цена дома ниже 250 тысяч долларов, а минимальная площадь не менее 70 квадратных метров;
• однокомнатная освобожденная квартира;
• в квартире не менее четырех комнат, раздельный санузел, а площадь менее 80 квадратных метров;
• дом, находящийся в базе данных не более одного месяца, ценой выше 300 тысяч долларов;
• однокомнатная квартира, находящаяся в базе данных с марта месяца.
Чтобы объединить условия с помощью оператора И, в диапазоне условий должно использоваться несколько столбцов. На рисунке показан диапазон условий, с помощью которого отбираются записи, содержащие характеристики домов по цене ниже 250 тысяч долларов и площадью не менее 70 квадратных метров.
На следующем рисунке показан еще один пример диапазона условий, в соответствии с которым из базы данных отбираются все записи, внесенные в список в марте месяце. Обратите внимание, что одно из имен полей Дата подачи заявки появляется в диапазоне дважды, поскольку в данном случае используется следующее условие: дата занесения в список должна быть больше либо равна 1 марта И меньше либо равна 31 марта.
Предупреждение
Учтите, что условия, показанные на рисунке выше, с американскими форматами даты могут работать неправильно. Чтобы гарантировать совместимость заданных условий с различными системами дат, используйте функцию ДАТА, как, например, в следующих формулах:
=“>=”&ДАТА(2007;3;1)
=“<=”&ДАТА(2007;3;31)
Чтобы объединить условия с помощью оператора ИЛИ, диапазон условий должен содержать больше одной строки. Диапазон условий может содержать любое количество строк, и все они будут объединены оператором ИЛИ. На рисунке показан диапазон условий (A1:С3), содержащий две строки.
В этом примере после фильтрации списка на экран выводятся строки, соответствующие хотя бы одному из следующих условий:
• квартира со смежным санузлом и площадью не менее 50 кв. м;
• дом с раздельным санузлом по цене менее 210 тысяч долларов.
Примечание
Имейте в виду, что с помощью автофильтра подобный отбор выполнить невозможно.
Можно повторить условие в нескольких строках, чтобы включить один и тот же критерий в несколько групп, объединенных оператором И. Предположим, что вы ищете однокомнатную квартиру в центре, но также можете рассмотреть предложения трехкомнатных квартир в других районах с ценой меньше 250 тысяч долларов. На рисунке показано, как использовать оператор ИЛИ между критериями Регион и Цена.
Задание вычисляемых условий
Использование вычисляемых условий окажет существенную помощь при фильтрации записей. Благодаря вычисляемым условиям можно фильтровать записи на основе одного или большего количества результатов вычислений. К примеру, можно определить вычисляемый критерий, на основании которого будут отобраны записи о квартирах, цена которых ниже средней.
=Цена<СРЗНАЧ(А:А)
Обратите внимание, что в этой формуле используется ссылка на первую ячейку данных в столбце Цена. Также, при использовании вычисляемых критериев ячейка сверху не должна содержать имя поля. Можно оставить верхнюю ячейку пустой или поместить в нее произвольное описание, например Выше среднего.
Кстати, для отображения данных, которые больше или меньше среднего значения, можете использовать и стандартный фильтр.
Следующий вычисляемый критерий отображает строки, в которых цена квартиры в пересчете на квадратный метр ниже 3000 долларов. Ячейка А9 является первой в столбце цен, а Е9 – первой в столбце общей площади. Формула вычисляемого критерия в данном случае следующая:
=(А9/Е9)<3000
На рисунке показана база данных агентства недвижимости после фильтрации квартир, цена которых не превышает трех тысяч долларов за квадратный метр. В столбец I специально добавлена упомянутая выше формула, чтобы проверить точность фильтрации.
В следующем примере вычисляемый критерий содержит формулу, отображающую записи, введенные за последние 60 дней:
=В9>СЕГОДНЯ()-60
Запомните следующие правила использования вычисляемых условий.
• Формула, вычисляющая условия, – это всегда логическая формула, которая возвращает значение ИСТИНА или ЛОЖЬ.
• В ссылках на столбцы используйте ссылку на ячейку в первой строке данных, а не на ячейку, содержащую заголовок столбца.
• В вычисляемых критериях не используйте существующие заголовки полей. По существу, вычисляемый критерий формирует новое поле таблицы. Таким образом, в первой строке критерия нужно ввести новое имя поля. Если хотите, можете оставить это поле пустым.
• Можно использовать любое количество вычисляемых критериев, а также сравнивать и смешивать их с обычными критериями.
• Если формула вычисляемого критерия ссылается на ячейку вне таблицы, используйте абсолютную, а не относительную ссылку. К примеру, вместо С1 введите $С$1.
• В большинстве случаев легче добавить в таблицу новый вычисляемый столбец, чем создавать вычисляемый критерий.