Расширенный фильтр Excel 2007
 | Безусловно, лидером среди инструментов для фильтрации баз данных в Excel является автофильтр. Однако в практике бухгалтера бывают ситуации, когда его возможностей недостаточно для решения поставленной задачи. Для таких (пусть и нечастых) случаев разработчики Excel 2007 оставили в арсенале этой программы старый испытанный инструмент — расширенный фильтр. Как работать с этим инструментом, в чем его преимущества и недостатки, какую пользу может получить бухгалтер от расширенного фильтра, вы узнаете из этой статьи. Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства |
Автофильтр — не единственное средство для отбора записей из базы данных Excel. Другим мощным инструментом для решения этой задачи является расширенный фильтр. Несмотря на то что оба эти инструмента решают одну задачу, принципы работы у них разные. Да и возможности их существенно отличаются. В чем состоит это отличие, каковы преимущества и область применения расширенного фильтра — мы сейчас и разберемся.
Для чего нужен расширенный фильтр
Вернемся к базе данных из нашей предыдущей статьи. Это реестр контрагентов, фрагмент которого показан на рис. 1. Представим себе такую задачу: нам нужно выбрать из этой базы записи о контрагентах «Фирма „КХК“», «ЧП „Матадор“» и «ООО „ЕКСС“». С помощью автофильтра решить такую задачу в принципе можно, но уже неудобно. И вот почему.
В окне настройки пользовательского автофильтра с помощью логической операции «
ИЛИ» можно связать только два условия. В нашем случае их уже три: мы собираемся отобрать записи, в которых поле «НаимПредпр» содержит текст «Фирма „КХК“», или «ЧП „Матадор“», или «ООО „ЕКСС“». Единственный способ справиться с задачей — щелкнуть на списке автофильтра в колонке «НаимПредпр» и установить флажки, как показано на рис. 2. Однако здесь есть свои недостатки. Например, отобрав данные, мы не видим условий фильтрации, а обращаться каждый раз к списку автофильтра неудобно. Кроме того, чтобы перенастроить список, придется каждый раз раскрывать меню автофильтра и заново настраивать его. И все же это не самое главное.Попробуем усложнить задачу и выбрать из базы не только определенные предприятия, но и дополнительно наложить ограничения на значение в поле «
ОбщСум» по каждому контрагенту. Конкретно по нашей базе: я хочу выбрать все сделки с «Фирма „КХК“», сумма которых превышает 500 грн., все сделки с «ООО „ЕКСС“», сумма которых больше 1000 грн., и сделки по «ЧП „Матадор“» на сумму от 100 до 400 грн. Решить такую задачу с помощью автофильтра уже не удастся. Например, мы указали в поле «НаимПредпр» значение «Фирма „КХК“», а в поле «ОбщСум» выбрали «500». Excel тут же отфильтрует базу, записи о контрагентах «ООО „ЕКСС“» и «ЧП „Матадор“» станут недоступны. Даже если воспользоваться ручной настройкой параметров автофильтра (рис. 2), проблема все равно останется, так как с помощью автофильтра мы не можем связать операцией « ИЛИ » несколько условий для обработки различных полей (колонок) базы данных. 
Этого недостатка лишен расширенный фильтр, который позволяет применять более сложные условия для отбора записей. И хотя расширенный фильтр, на мой взгляд, не так удобен в работе, иногда это единственный способ реализовать сложный запрос к базе данных.
Как вызвать расширенный фильтр
Иконка для вызова расширенного фильтра находится в группе «
Сортировка и фильтр» меню «Данные» (рис. 3). Однако, прежде чем воспользоваться расширенным фильтром, нужно внести определенные изменения в исходную базу данных. Сделайте так: 
1) откройте файл с реестром (рис. 1);
2) добавьте не менее трех строк перед заголовками базы данных. Эти строки называются областью критериев, здесь будут расположены условия фильтрации;
3) в первой строке области критериев продублируйте заголовки базы (наименования полей). Имена заголовков в области критериев и в базе данных должны полностью совпадать!
4) ниже, начиная со второй строки, введите условия для отбора записей. Количество условий должно быть таким, чтобы между заголовками базы данных и областью критериев находилась хотя бы одна пустая строка. Пример базы контрагентов с подключенным расширенным фильтром показан на рис. 4. В этой базе область критериев находится в блоке «A1:G4». Заголовки базы находятся в строке «6». Заголовки области критериев расположены в строке «1» рабочего листа;
5) вызовите меню «Данные»;
6) в группе «Сортировка и фильтр» щелкните на иконке «Дополнительно». Появится окно с параметрами расширенного фильтра, как на рис. 5;
7) в поле «Диапазон условий:» введите адрес блока, где расположена область критериев. В примере на рис. 5 это блок «A1:G4»;
8) в поле «Исходный Диапазон:» укажите адрес базы данных. На рис. 5 база данных расположена в ячейках «A6:G600»;
9) установив параметры, нажмите «ОК». База будет отфильтрована.
Как видите, расширенный фильтр отличается от автоматического как по способу определения условий, так и по параметрам (рис. 5). Именно в такой последовательности мы и рассмотрим эти отличия: вначале разберемся со способами определения условий, а затем перейдем к параметрам.
Определение условий для расширенного фильтра
Логика работы условий расширенного фильтра зависит от того, где они расположены в пределах области критериев. Здесь можно выделить такие варианты.
О
дно условие в одной колонкеЭто наиболее простой случай работы расширенного фильтра. Чтобы поработать с ним, делаем так:
1) в ячейку «
C2» вводим значение «ООО „ЕКСС“»;2) щелкаем на иконке «
Дополнительно» группы «Сортировка и фильтр» (рис. 3). Появится окно, как на рис. 5;3) в нем параметр «
Исходный диапазон:» оставляем без изменений;4) щелкаем на поле «
Диапазон условий:»;5) на рабочем листе выделяем блок «
A1:G2»;6) нажимаем «
ОК». На экране остались данные только для фирмы «ООО „ЕКСС“».Вывод
. Чтобы ввести простое условие фильтрации, достаточно указать нужное значение в соответствующей колонке области критериев.Продолжим наш эксперимент:
1) чтобы показать на экране все записи, в меню «
Данные» щелкаем на иконке «Фильтр» (рис. 3);2) щелкаем на иконке «
Дополнительно», вызываем расширенный фильтр;3) в окне настроек (рис. 5) параметр «
Исходный диапазон:» оставляем без изменений;4) в поле «
Диапазон условий:» вводим значения «A1:G3 » — размер области критериев мы увеличили на одну строку;5) нажимаем «
ОК». Excel покажет на экране все записи базы данных.Важно!
В области критериев расширенного фильтра не должно быть пустых значений. При сравнении записей Excel считает, что пустое значение является частью любой ячейки, поэтому расширенный фильтр выберет все записи из базы данных.Попробуем изменить состав области критериев. Делаем так:
1) становимся в ячейку «
C3». Вводим в нее значение « » (пробел);2) остальные параметры оставляем без изменений: размер области критериев равен трем строкам, т. е. рассчитан на два условия;
3) нажимаем «
ОК». Расширенный фильтр отработал правильно: на экране остались только данные по контрагенту «ООО „ЕКСС“» (рис. 4).Пару слов о местоположении области критериев и базы данных. В принципе нет никаких ограничений, где они должны быть расположены. В нашем примере с реестром область критериев мы разместили над базой данных. В этом есть свой плюс — можно всегда видеть условия фильтрации. Но есть и минус — при указании диапазона с данными нельзя ввести просто идентификаторы колонок: чтобы не заботиться о размере базы и свободно добавлять в ней новые записи. В реальной работе вы можете поставить область с критериями где-нибудь рядом с базой. Например, справа. Для случая с реестром это может быть, скажем, диапазон «
L1:R1» или любой другой. Главное, чтобы в нем были названия полей, чтобы эти названия совпадали с заголовками в базе данных и чтобы область критериев и данные находились на одном листе.И еще интересный момент. При работе с расширенным фильтром удобно пользоваться именованными диапазонами. Напомню, что вставить (или исправить существующее) имя диапазона можно через закладку «
Определенные имена» на ленте меню «Формулы». На этой закладке есть иконка «Диспетчер имен». Именно в этом инструменте сосредоточены все возможности для работы с именами: создание, удаление, корректировка. Так вот, возвратимся к расширенному фильтру. В Excel есть три предопределенных названия диапазонов, которые использует расширенный фильтр: «Критерии» — область для критериев, «База_данных» — диапазон на рабочем листе, где находится база, а также «Извлечь» — название диапазона, куда нужно поместить результат работы фильтра (об этом мы поговорим чуть позже). Вы можете заранее, до работы с фильтром, создать эти имена через «Диспетчер имен». И тогда при обращении к расширенному фильтру его параметры «Исходный диапазон:», «Диапазон: условий:» и «Поместить результат в диапазон:» Excel заполнит автоматически по данным именованных диапазонов.
Несколько условий в одной колонке
Возвращаемся к базе данных, изображенной на рис. 4. Делаем так:
1) в ячейку «
C2» вводим значение «ООО „ЕКСС“», в ячейку «C3» пишем «Фирма „ЕКО“»;2) через иконку «
Дополнительно» вызываем расширенный фильтр;3) в поле «
Диапазон условий:» вводим координаты «A1:G3»;4) нажимаем «
ОК». На экране остались данные по двум контрагентам: «ООО „ЕКСС“» и «Фирма „ЕКО“».Важно! Несколько условий в пределах одного столбца (поля данных) расширенный фильтр связывает операцией «
ИЛИ».То есть в нашем случае расширенный фильтр выбрал все записи, относящиеся к контрагентам «ООО „ЕКСС“» ИЛИ «Фирма „ЕКО“» (рис. 6).
Несколько условий в разных колонках
Изменим наполнение области критериев, немного усложнив задачу. Теперь делаем так:
1) в ячейку «
C2» вводим значение «ООО „ЕКСС“», в ячейку «E2» пишем выражение «>1000»;2) через иконку «
Дополнительно» вызываем расширенный фильтр;3) в поле «
Диапазон условий:» вводим координаты «A1:G2 » (в области критериев у нас одна строка для условий);4) нажимаем «
ОК». На экране остались всего две записи по контрагенту: «ООО „ЕКСС“»: номер 41 за 18.11.2009 на сумму 1500 грн. и номер 44 от 20.11.2009 на сумму 1176 грн. (рис. 7).
Важно!
Несколько условий в разных колонках, но в пределах одной строки расширенный фильтр связывает операцией «И».В нашем случае он выбрал все записи по такому условию: «контрагент равен «ООО „ЕКСС“» И сумма операции больше 1000 грн.».
Несколько условий в разных колонках и строках
Откорректируем условия таким образом:
1) в ячейке «
C2» оставляем значение «ООО „ЕКСС“»;2) выражение «
>1000» из ячейки «E2» переносим в «E3»;3) вызываем расширенный фильтр;
4) в поле «
Диапазон условий:» вводим координаты «A1:G3 » (теперь область критериев должна охватывать три строки — для условий);5) нажимаем «
ОК». На экране остались все записи по контрагенту: «ООО „ЕКСС“». Это сработало условие в ячейке «C2». Далее в выборке видим записи по остальным контрагентам, сумма операции в которых более 1000 грн. Это результат работы условия «>1000» в ячейке «E3».Важно!
Расширенный фильтр Excel последовательно обрабатывает выражения в области «Диапазон условий:». Все, что записано в одной строке, но в разных колонках, он объединяет операцией «И», а выражения в разных строчках он соединяет логикой «ИЛИ».
Несколько наборов условий для разных колонок
Движемся дальше. На этот раз попробуем записать в разные колонки области критериев по нескольку условий для отбора записей. В качестве примера выберем из нашего реестра все операции с контрагентами «ООО „ЕКСС“» и «Фирма „ЕКО“», сумма по которым в поле «
ОбщСум» превышает 1000 грн. Делаем так:1) в ячейку «
C2» вводим «ООО „ЕКСС“», в ячейку «C3» вводим «Фирма „ЕКО“»;2) в ячейки «
E2» и «E3» записываем условие «>1000». В данном случае оно одинаковое;3) вызываем расширенный фильтр;
4) в поле «
Диапазон условий:» вводим координаты «A1:G3 » (в области критериев у нас три строки для двух условий);5) нажимаем «
ОК». На экране остались все записи по контрагентам «ООО „ЕКСС“» и «Фирма „ЕКО“», у которых значение в поле «ОбщСум» больше 1000 грн. (рис. 8). 
В нашем случае правило отбора записей из реестра можно условно представить так: (НаимПредпр=«ООО „ЕКСС“» И ОбщСум<1000) ИЛИ (НаимПредпр=«Фирма „ЕКО“ И ОбщСум<1000).
Обратите внимание, что в нашем запросе ограничение для поля «ОбщСум» указывать нужно дважды, для каждого контрагента в отдельности. Если, скажем, опустить это ограничение для элемента «Фирма „ЕКО“», то в результат обработки попадут все записи реестра об этом контрагенте.
Несколько наборов условий для одной колонки
Нам осталось попробовать последний вариант — применить к одной колонке несколько условий для отбора записей. На практике такая задача встречается очень часто. Например, когда нужно указать не конкретную величину, а диапазон значений для некоторого поля. Вернемся к предыдущему примеру. Допустим, нам понадобилось выбрать из реестра операции по тем же контрагентам «ООО „ЕКСС“» и «Фирма „ЕКО“», оставить ограничение по сумме в поле «
ОбщСум» (более 1000 грн.), но выборку сделать за период с «10/11/09» по «20/11/09». Как раз при определении последнего условия у нас и возникнет проблема. Ограничить интервал времени нетрудно: нужно ввести два условия — «<20/11/09» и «>10/11/09». Сложность в другом. Оба эти условия придется поставить в колонке «Дата» и продублировать их для каждого контрагента. Получается, что в одну ячейку мы должны записать два выражения. А этого сделать, разумеется, нельзя. Зато можно воспользоваться рабочей колонкой в области критериев.Важно!
Состав заголовков в области базы данных и в области критериев может отличаться. Главное, чтобы совпадали названия колонок. В случае когда для одной колонки нужно применить несколько условий фильтрации, вы можете смело продублировать ее в области критериев, а затем в каждом из полученных столбцов записать конкретные выражения.Посмотрим, как это выглядит практически. Делаем так:
1) в ячейку «
K1» пишем заголовок «Дата». Это будет дополнительный столбец в области критериев, в него мы запишем одно из условий выбора записей по датам;2) заполняем области критериев, как показано на рис. 9: в ячейки «
B2» и «B3» пишем «>10/11/09» — это нижняя граница временного интервала. В ячейку «C2» вводим «ООО „ЕКСС“», в ячейку «C3» вводим «Фирма „ЕКО“». В «E2» и «E3» записываем условие «>1000». А в дополнительной колонке «Дата» в ячейки «K2» и «K3» вводим ограничение на верхнюю границу временного интервала. Это будет выражение «<20/11/09»;3) вызываем расширенный фильтр;
4) в поле «
Диапазон условий:» вводим координаты «A1:K3 ». Мы обязательно должны захватить дополнительную колонку!5) нажимаем «
ОК». На экране остались всего три записи по контрагентам «ООО „ЕКСС“» и «Фирма „ЕКО“» за период с 11/11/09 по 19/11/09, у которых значение в поле «ОбщСум» превышает 1000 грн. (рис. 9).
Использование подстановочных знаков
Расширенный фильтр допускает использование подстановочных знаков. С их помощью можно сформировать текстовый шаблон, который будет сравниваться с содержимым соответствующего поля базы данных. Работать с подстановочными знаками можно и в автофильтре, но в нем вы можете указать только один шаблон для конкретной колонки. В расширенном фильтре появляется возможность определить несколько разных шаблонов, поставить их в разные ячейки области критериев и тем самым построить правило для отбора записей практически неограниченной сложности. Чаще всего при построении шаблонов используют подстановочные знаки, приведенные в таблице.
Подстановочные знаки для работы с расширенным фильтром
| Подстановочный знак | Что означает в шаблоне |
| « ?» (знак вопроса) | Любой одиночный символ |
| « *» (звездочка) | Любая подстрока |
| «~» (тильда), за которой указан один из знаков « ?», «*» или «~» | Указывает, что в соответствующей позиции шаблона должен стоять специальный символ « ?», «*» или «~» |
Например, шаблон «
?2010» позволяет отобрать все строки длиной 5 символов, которые заканчиваются на «2010». Чтобы выбрать из реестра всех контрагентов, в названии которых встречается слово «Фирма», можно использовать шаблон «*фирма*», и т. д.
Использование формул
При работе с расширенным фильтром в качестве условий отбора можно использовать формулы. На практике такая потребность возникает нечасто, но помнить о ней не помешает. Вот пример, когда может пригодиться фильтр при помощи формул: при выборе записей из базы данных нужно проанализировать текстовое поле с учетом регистра. Напомню, что сам по себе расширенный фильтр при сравнении текстовых строк регистр символов не учитывает. Для решения этой задачи нужно в качестве условия записать формулу для сравнения текстового поля с образцом. Для реестра на рис. 4 эта формула может выглядеть так: «
=СОВПАД (C7;"ООО ""ЕКСС""")». Обилие кавычек в этой формуле связано с названием предприятия — «ООО „ЕКСС“». Дело в том, что вторым параметром функции «=СОВПАД()» является текстовая строка. Эту строку нужно взять в кавычки. А для того чтобы Excel правильно обработал кавычки внутри самого названия, их нужно продублировать.
Параметры работы расширенного фильтра
С условиями отбора мы разобрались. Пора вернуться к параметрам работы расширенного фильтра. Их немного, все настройки представлены в окне, изображенном на рис. 5. Вот их назначение:
— «
Фильтровать список на месте» — предписывает расширенному фильтру просто скрывать строки, не удовлетворяющие условиям отбора. Этот режим работает подобно автофильтру;— «
Скопировать результат в другое место» — копирует отфильтрованные данные на другой рабочий лист или на другое место текущего рабочего листа;— «
Исходный диапазон:» — в этом поле указан адрес блока с базой данных для фильтрации;— «
Диапазон условий:» — содержит диапазон ячеек на рабочем листе с условиями отбора;— «
Поместить результат в диапазон:» — определяет диапазон ячеек, куда копируются отобранные записи. Это поле активно, если выбран переключатель «Скопировать результат в другое место».Кроме того, расширенный фильтр позволяет отобрать уникальные (т. е. неповторяющиеся) записи из базы данных. Для этого нужно в качестве исходного диапазона указать одну колонку и включить флажок «
Только уникальные записи» (рис. 5). Условие отбора при этом указывать не нужно . Посмотрим, как это работает на примере.
Копирование результатов фильтрации в отдельный диапазон
В качестве примера скопируем из базы реестра (рис. 4) в отдельную область рабочего листа записи, относящиеся к фирме «ООО „Колорит“». Причем нас будут интересовать только информация о дате, названии предприятия, общая сумма и сумма НДС. Делаем так:
1) открываем базу реестра. Заполняем область критериев — в ячейку «
C2» вводим значение «ООО „Колорит“»;2) копируем в буфер обмена заголовки базы данных (ячейки «
A6:G6»);3) вставляем содержимое буфера обмена начиная с ячейки «
L6:R6». Здесь мы поместим результат работы расширенного фильтра;4) в блоке «
L6:R6» удаляем лишние заголовки, оставляем «Дата», «НаимПредпр», «ОбщСум» и «НДС»;5) ставим указатель активной ячейки внутрь основной базы данных;
6) щелкаем на иконке «
Дополнительно» (рис. 3). Появится окно настроек расширенного фильтра (рис. 5);7) в параметре «
Исходный диапазон:» указываем область с данными, это ячейки «A6:G600»;8) щелкаем на поле «
Диапазон условий:»;9) на рабочем листе выделяем блок «
A1:G2» — у нас указано только одно условие;10) включаем флажок «
Скопировать результат в другое место». Станет доступным поле «Поместить результат в диапазон:»;11) выделяем блок ячеек «
L6:O6» — это заголовки «Дата», «НаимПредпр», «ОбщСум», «НДС». Мы их только что создали;12) нажимаем «
ОК». На рабочем листе в диапазоне «L7:O10» появились четыре записи по фирме «ООО „Колорит“» (рис. 10). 
Важно!
Расширенный фильтр позволяет скопировать в отдельную область рабочего листа не всю базу, а только некоторые ее колонки. Для этого нужно в отдельном блоке напечатать заголовки этих колонок и указать его в качестве параметра «Поместить результат в диапазон:». По умолчанию расширенный фильтр копирует все поля базы данных.Для бухгалтера возможность копировать результаты работы расширенного фильтра в отдельные блоки рабочего листа может быть очень полезна. Например, у вас есть база по определенным операциям за год, а вам нужно получить из нее отдельные базы данных по кварталам. Используя расширенный фильтр, вы решите такую задачу в считанные секунды. Правда, с одним ограничением: область для копирования и основная база данных расширенного фильтра должны находиться на одном рабочем листе. Безусловно, в этом есть определенное неудобство, но при хороших навыках работы с буфером обмена разнести данные по отдельным листам не составит никакого труда.
Отбор уникальных записей
Теперь задача будет такой. Я хочу из нашего реестра получить перечень всех организаций. Разумеется, что каждая фирма должна фигурировать в перечне только один раз. Делаем так:
1) открываем реестр. Удаляем из него область критериев (строки 1 — 5);
2) удаляем все колонки, оставив только «
НаимПредпр». Таким образом, мы получили список контрагентов, но список этот с повторяющимися значениями;3) ставим указатель активной ячейки внутрь списка;
4) щелкаем на иконке «
Дополнительно» (рис. 3). Появится окно настроек расширенного фильтра (рис. 5);5) в параметре «
Исходный диапазон» указываем область с данными. Если список не содержит пустых строк, Excel автоматически подставит его координаты;6) поле «
Диапазон условий:» оставляем пустым;7) включаем флажок «
Только уникальные записи» (рис. 5);8) нажимаем «
ОК». На рабочем листе останутся только шесть строк: «ООО „ЕКСС“», «ООО „Колорит“», «Фирма „ЕКО“», «Фирма „КХК“», «Фирма „Эталон“» и «ЧП „Матадор“». Остальные элементы будут скрыты. Остается только скопировать список в нужное место или напечатать его.И последнее. Как и в случае с автофильтром, сортировку можно применять совместно с расширенным фильтром. Чтобы сортировка отработала правильно, курсор активной ячейки нужно предварительно установить внутрь области с базой данных.
Вот, пожалуй, и все, что нужно знать о работе с расширенным фильтром. Надеюсь, что материал этой статьи выручит вас в трудную минуту и поможет справиться с задачей, когда автофильтр окажется бессилен… А мы в следующем номере перейдем к очень актуальной для бухгалтера теме — работе с режимом «
Итоги».
Жду ваших писем, вопросов и предложений на
bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum . Успешной работы!
