Расширенный фильтр предоставляет широкие возможности поиска и фильтрации. Он позволяет не только применять операции И, ИЛИ, но и составлять вычисляемые критерии. Кроме того, отфильтрованные данные могут быть скопированы в заданный диапазон рабочего листа.

При работе с расширенным фильтром создаются три области:

1. Исходный диапазон. Область, где хранятся исходные данные. Содержит диапазон, подлежащий фильтрации, т.е. исходную таблицу обязательно вместе с шапкой таблицы.

2. Диапазон условий. Область на рабочем листе, где задаются критерии поиска информации. Диапазон условий применяется для задания условия отбора записей и формируется из строки заголовков полей, которые будут ключевыми при отборе записей, и строки или строк критериев.

При создании диапазона условий необходимо пользоваться следующими рекомендациями:

В верхней строке диапазона критериев должны быть расположены имена, в точности, совпадающие с заголовками столбцов исходного диапазона. Можно перечислить все имена, но это необязательно, достаточно указать только те, которые определяют условия отбора. Точное соответствие имен полей исходного диапазона и имен, указанных в диапазоне условий, проще всего обеспечить копированием из самой таблицы.

Ниже должна располагаться хотя бы одна строка, где задаются условия. В условия отбора расширенного фильтра может входить несколько условий, накладываемых на один столбец, несколько условий, накладываемых одновременно на несколько столбцов, а также условия, накладываемые на вычисляемое формулой значение.

Для объединения критериев с помощью логического И нужно указать задаваемые критерии в одной строке, а для объединения критериев с помощью «логического ИЛИ» следует представить критерии в разных строках. Следует также учитывать, что в случае необходимости вместе с критерием в ячейку надлежит ввести оператор сравнения. Для обозначения точного соответствия поля записи заданному критерию при задании критериев знак равенства (=) не используется.

Выходной диапазон. Область, в которую Excel копирует выбранные из таблицы данные. Этот диапазон должен быть расположен на том же листе, что и исходный. Извлеченную информацию можно поместить на другой лист копированием результата фильтрации. Задание выходного диапазона необязательно, т. к. существует опция «Фильтровать список на месте». В этом случае фильтрация преобразует исходную таблицу в набор записей, удовлетворяющих условиям выбора.

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

Рассмотрим порядок выбора из таблицы Учет движения материалов на складах с помощью Расширенного фильтра материалов, у которых приход превысил величину 200, а остаток на конец месяца составил меньше чем 15.

1. Создайте копию листа Сортировка и назовите его «Расш_фильтр».

2. Сформируйте диапазон условий, для чего скопируйте заголовок полей исходного диапазона, которые будут ключевыми при отборе записей, и заполните строки критериев:

- cкопируйте D2 в A10; F2 – в B10;

- в A11 запишите критерий: >200, а в В11 критерий: <15.

3. Сформируйте выходной диапазон, скопировав заголовки полей исходного диапазона, которые необходимо отобразить в выходном диапазоне:

- выделите и скопируйте в буфер диапазон A2:F2;

- вставьте его содержимое в А13:F13.

4. Установите курсор в любую ячейку исходного диапазона.

5. Выберите команду [Дополнительно] в группе [Сортировка и фильтр]. В диалоговом окне [Расширенный фильтр] (рисунок 21) проделайте следующие действия:

Рисунок 21 – Окно Расширенный фильтр

· в группе [Обработка] выберите переключатель [Скопировать результат в другое место]. В этом случае исходная таблица останется нетронутой, а отобранные записи будут помещены в выходной диапазон. (При выборе переключателя [Фильтровать список на месте] не удовлетворяющие критерию записи будут скрыты в исходной таблице);

· установите курсор в поле [Исходный диапазон] и введите ссылку на диапазон исходной таблицы, включая шапку (A2:F7). Ввод ссылок во всех полях данного диалогового окна лучше всего осуществлять путем выделения указателем мыши нужного диапазона;

· установите курсор в поле [Диапазон условий] и введите ссылку на диапазон условий (A10:В11);

· так как выбран переключатель [Скопировать результат в другое место], перейдите к полю [Поместить результат в диапазон] и введите ссылку на выходной диапазон (А13:F20);

· установите флажок [Только уникальные записи], если не хотите, чтобы одинаковые записи повторялись (будет выводиться только первая из всех, удовлетворяющих критерию, одинаковых записей); нажмите кнопку [ОК].

Результаты работы [Расширенного фильтра] сразу же отобразятся на рабочем листе (рисунок 22). Причем, если в окне [Расширенный фильтр] установлен переключатель [Фильтровать список на месте], то подобно фильтру команда [Расширенный фильтр] скроет все строки, которые не удовлетворяют фильтру и отметит номера отобранных строк синим цветом, а в строке состояния будет отображено число найденных записей.

Рисунок 22 – Результат расширенного фильтра по условиям, связанным логическим И

Примечание. При каждом выполнении команды [Расширенный фильтр] Excel просматривает полный список, а не текущее множество ранее отфильтрованных строк. Вследствие этого не обязательно использовать команду [Показать все перед изменением фильтра].

Выберите из таблицы «Учет движения материалов на складах» только данные о материалах, у которых остаток на конец месяца меньше 10 или больше 50.

Как формируются диапазоны условий в этом случае показано на рисунке 23.

Рисунок 23 – Результат расширенного фильтра условиям, связанным

Подведение итогов

Часто бывает необходимо посчитать промежуточные и общие итоги в таблице. При этом таблица должна быть отсортирована по столбцам, которые являются группировочными признаками. Команда [Промежуточные итоги] на вкладке [Данные] в группе [Структура], добавляет строки промежуточных итогов для каждой группы элементов. Допускается использование различных функций (например, среднего значения, количества строк или пустых ячеек, стандартное отклонение и т.д.) для вычисления итогов в пределах каждой группы.

В диалоговом окне [Промежуточные итоги] поле при каждом изменении в используется для указания столбца, по которому следует сгруппировать данные для подведения итогов; поле [Операция] позволяет использовать различные функции; поле [Добавить итоги по]: позволяет отметить все поля, по которым будут подводиться итоги. Установка флажка [Заменить текущие итоги] позволяет заменить в таблице уже существующие итоги. Установленный флажок [Конец страницы] между группами автоматически вставляет конец страницы перед каждой группой данных, для которой вычисляются итоги. Флажок [Итоги] под данными дает возможность поместить строки промежуточных и общих итогов под соответствующими данными. Кнопка [Убрать все] позволяет восстановить исходный вид экрана до подведения итогов.

Используя возможности по автоматическому подведению итогов, рассчитаем частные (по каждому складу) и общие итоги в таблице «Учет движения материалов на складах».

Отсортируйте таблицу по столбцу «№ склада».

Выделите диапазон таблицы вместе с шапкой, т.е. A2:F7.

Откройте окно формирования итогов командой «Данные►Итоги».

Заполните диалоговое окно как показано на рисунке 24 и нажмите [ОК].

Рисунок 24 – Окно Промежуточные итоги

В результате подведения итогов таблица примет вид, представленный на рисунке 25.

Рисунок 25 – Таблица 1 с итогами

Кроме итоговых строк программа сформировала структуру (см. слева от таблицы), которая согласована с группировкой данных для вычисления промежуточных и общих итогов. Знак «-» означает, что можно спрятать строки, относящиеся к группе, оставив только промежуточный или общий результат, (щелкнув по кнопке [-]). Кнопки  также служат для управления отображением структуры. Например, кнопка  оставляет в таблице только промежуточные и общие итоги, скрывая содержимое таблицы.