Трюк №31. Как в Excel манипулировать данными при помощи расширенного фильтра

Если вы знакомы с средством Excel Автофильтр (AutoFilter), то вы также должны знать о его ограничениях. Для более гибких возможностей манипулирования данными пригодится инструмент Excel Расширенный фильтр (Advanced Filter).

Хотя инструмент Автофильтр (AutoFilter) в чем-то ограничен, он полезен, если вы хотите отобразить только данные, отвечающие определенным критериям. Иногда, однако, бывает невозможно подобрать необходимую информацию, используя только стандартные параметры автофильтра. Разносторонний инструмент Excel Расширенный фильтр (Advanced Filter) позволяет расширить возможности манипулирования данными.

При работе с инструментом Excel Расширенный фильтр (Advanced Filter) ваша таблица должна быть настроена согласно классическому формату (об этом рассказывается в начале раздела Упрощение рабочих книг и листов).

Для применения инструмента Excel Расширенный фильтр (Advanced Filter) понадобится копия заголовков столбцов таблицы где-либо выше данных. Над таблицей данных всегда следует оставлять, по меньшей мере, три пустые строки. Чтобы гарантировать, что заголовки всегда останутся одними и теми же независимо от любых изменений, всегда ссылайтесь на них простыми ссылочными формулами, например, =А4, где А4 содержит заголовок столбца. Копируйте эту формулу столько раз, сколько заголовков столбцов в вашей таблице. Это гарантирует, что заголовки условий для расширенного фильтра будут динамическими. Непосредственно под скопированными заголовками поместите условия, которые будут использоваться расширенным фильтром. Подробнее об этом — в справке по Excel в разделе Критерии расширенного фильтра (Advanced Filters Criteria).

Нужно всегда помнить, что при использовании расширенного фильтра, если вы поместите два или более критерия непосредственно под соответствующими заголовками, они будут разделены оператором ИЛИ (OR). Если вы хотите использовать оператор И (AND), заголовки столбцов и соответствующие критерии нужно указать дважды бок о бок.

Оба предыдущих примера демонстрируют довольно простые случаи использования инструмента Расширенный фильтр (Advanced Filter), и эти задачи при необходимости можно выполнить с помощью автофильтра. Далее мы продемонстрируем несколько примеров применения расширенного фильтра в случаях, когда возможностей автофильтра не хватает.
[stextbox id=»warning»]Важно помнить, что, когда для критериев используется формула, нельзя над критериями помещать заголовок, идентичный одному из заголовков в таблице. Например, если у вас есть список числовых данных в столбце А и список начинается в ячейке А5 (а в ячейке А4 находится заголовок), а вам необходимо получить все числа, большие среднего, условие будет выглядеть так: =A5>AVERAGE($A$5:$A$500), в русской версии Excel: =A5>СРЗНАЧ($A$5:$A$500). Если этот критерий находится в ячейке А2, диапазон критериев — $А$1:$А$2, но $А$1 не может содержать тот же заголовок, что и заголовок списка. Эта ячейка должна быть либо пустой, либо с другим заголовком.
Важно также помнить, что любая формула должна возвращать значение либо ИСТИНА (TRUE), либо ЛОЖЬ (FALSE). Диапазон, указанный для функции, вычисляющей среднее, указан в абсолютном формате при помощи знаков доллара, тогда как ссылка на ячейку А5 — это относительная ссылка. Это необходимо, поскольку при применении расширенного фильтра Excel увидит, что А5 — это относительная ссылка, и будет перемещаться вниз по списку по одной записи за раз и возвращать значение либо ИСТИНА (TRUE), либо ЛОЖЬ (FALSE). Если будет возвращено значение ИСТИНА (TRUE), данное число будет извлечено из списка. Если возвращено значение ЛОЖЬ (FALSE), число не отвечает критерию и, таким образом, не будет показано.[/stextbox]
Предполагаем, что в диапазоне $А$5:А$500 с заголовком в ячейке А4 многие имена повторяются. Кроме того, предполагаем, что многие заголовки повторяются несколько раз. Перед нами поставлена задача выделить в списке все повторяющиеся имена. Для этого нужно применить расширенный фильтр и в качестве критерия следующую формулу: =COUNTIF($A$5:$A$500.A5)>1, в русской версии Excel: =СЧЁТЕСЛИ($А$5:$А$500.А5)>1.

Когда вы примените к данным начальным условиям расширенный фильтр с параметром Скопировать результат в другое место (Copy to Another Location), новый список будет содержать все имена, которые повторялись в исходном списке. Многие из этих имен и в новом списке будут повторены несколько раз, но легко отфильтровать его при помощи расширенного фильтра, на этот раз выбрав параметр Только уникальные записи (Unique Records Only). Получим список повторяющихся имен.
[stextbox id=»warning»]Те, кто использует расширенный фильтр, часто спрашивают, как заставить Excel фильтровать данные по указанным точным критериям. Если ваш критерий — это имя Dave и вы применяете расширенный фильтр на длинном списке имен, Excel вернет не только имена Dave, но также такие имена, как Davey, Dave J, Dave К и т. д. Другими словами, любое имя, начинающееся с букв Dave именно в та ком порядке, будет считаться удовлетворяющим условию. Чтобы Excel мог найти точные соответствия, в данном случае только имена Dave, введите критерий как =»=Dave».[/stextbox]