В большинстве случаев стандартной фильтрации вполне достаточно. Однако если вы столкнулись с ее ограничениями, может потребоваться расширенная фильтрация, намного более гибкая, чем стандартная, однако требующая дополнительной подготовительной работы. Расширенная фильтрация обеспечит вас следующими возможностями:
• использование более сложных условий фильтрации;
• использование вычисляемых условий фильтрации;
• копирование строк, которые соответствуют определенным условиям, в другой диапазон ячеек, указанный пользователем.
Расширенную фильтрацию можно применять в базах данных рабочего листа и в таблицах.
В настоящем разделе будет использована таблица агентства недвижимости, показанная на рисунке. Она состоит из 125 записей и 7 полей. Эта база данных занимает диапазон А8:G133 и имеет широкий набор различных типов данных: числовых и логических значений, текстовых строк, а также дат. Строки над таблицей используются для определения критериев.
Создание диапазона условий
Перед использованием расширенного фильтра необходимо создать диапазон условий. Он содержит информацию, которую приложение Excel использует для фильтрации. Диапазон условий должен соответствовать следующим требованиям.
• Он должен состоять, как минимум, из двух строк. Первая строка содержит все (или некоторые) заголовки полей списка. Исключение составляет случай, когда используется вычисляемый критерий. Вычисляемые критерии могут использовать пустую строку заголовков.
• Другие строки диапазона состоят из условий, определенных пользователем.
Диапазон условий может находиться в любом месте рабочего листа или даже на отдельном рабочем листе. Однако не следует помещать диапазон условий в строки, которые входят в базу данных, поскольку при фильтрации базы данных Excel может скрыть некоторые из них. В результате они не будут отображаться. Поэтому диапазон условий, как правило, располагается выше или ниже базы данных или таблицы.
На рисунке показан диапазон условий, расположенный выше базы данных – в диапазоне ячеек A1:В2. Обратите внимание, что данный диапазон условий включает не все заголовки столбцов, поскольку достаточно включить в него только те заголовки, которые используются при создании условий фильтрации.
В этом примере условия заданы только в одной строке. Поля в каждой строке диапазона условий (за исключением строки заголовка) объединены оператором И. Поэтому после применения расширенного фильтра отображаются только те строки, ячейки которых в столбце Кол-во комнат содержат число 3, а в столбце Свободна – текст ЛОЖЬ. Другими словами, отображаются только трехкомнатные освобожденные квартиры.
Иногда определение критерия в диапазоне становится затруднительным.
Применение расширенного фильтра
Для выполнения расширенной фильтрации выполните следующие действия.
1. Убедитесь, что настроили диапазон условий.
2. Выберите команду Данные→Сортировка и фильтр→Дополнительно. Откроется диалоговое окно Расширенный фильтр.
3. Если активная ячейка находилась внутри диапазона базы данных, Excel может принять эмпирическое решение относительно диапазона данных. При необходимости (если Excel не угадала, что вам нужно) этот диапазон можно изменить.
4. Задайте в окне созданный в п.1 диапазон условий.
Если был определен диапазон Критерий, программа вставит его в поле Диапазон условий. При необходимости можно изменить его.
5. Для выполнения фильтрации на месте (т.е. для сокрытия строк, которые не удовлетворят условиям) установите переключатель в положение фильтровать список на месте. Если выбрать второй вариант – скопировать результат в другое место, – нужно будет определить соответствующий диапазон ячеек в поле Поместить результат в диапазон.
6. Щелкните на ОК, и Excel отфильтрует таблицу согласно определенному вами критерию.
На рисунке показана база данных рабочего листа после применения расширенного фильтра. Теперь на экране отображаются только трехкомнатные освобожденные квартиры.
При выборе варианта скопировать результат в другое место можно определить, какие столбцы включать в копию. Перед открытием диалогового окна Расширенный фильтр скопируйте заголовки в первую строку области, в которой собираетесь разместить отфильтрованные строки. В поле Поместить результат в диапазон диалогового окна Расширенный фильтр задайте ссылку на скопированные заголовки столбцов. После этого скопированные отфильтрованные строки будут содержать только поля, заголовки которых были скопированы.
Снятие расширенного фильтра
Расширенный фильтр Excel скрывает все строки, которые не удовлетворяют заданным критериям. Для снятия расширенного фильтра выберите команду Данные→Сортировки и фильтр→Очистить.