Excel 2007: фильтрация данных в сводных таблицах
 | Как-то раз мне в руки попалась книга по работе с программой PhotoShop. Размер этого издания составлял около 1000 страниц, а сама книга была посвящена одному-единственному (правда, и самому мощному) пункту меню! То же самое можно сказать и о сводных таблицах Excel 2007. Говорить об этом удивительном инструменте можно бесконечно. И сегодня, продолжая тему работы со сводными отчетами, мы коснемся особенностей фильтрации данных в сводных таблицах, а заодно посмотрим, как взаимодействуют стандартный и расширенный фильтры со сводными отчетами, и познакомимся с тонкостями и хитростями использования этих инструментов применительно к сводным таблицам. Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства |
Сводные таблицы часто используют для получения различного рода заготовок — своеобразного материала для последующей обработки. Получив такую заготовку, ее берут для дальнейшей работы: организуют вычисления, применяют к ней другие инструменты обработки данных и т. п. Как раз в последнем случае и возникает проблема. Не все средства работы с данными «дружат» со сводными таблицами. В частности, на сводный отчет нельзя наложить автофильтр, а иногда это просто необходимо. И все же кое-что в этой ситуации можно сделать, а именно воспользоваться встроенными средствами фильтрации сводных таблиц.
Фильтры в сводной таблице
Вернемся к сводному отчету, который показан на рис. 1 (см. статью «Excel 2007: сводные таблицы-2» // «Б & K», 2010, № 9). В этой таблице в области строк расположены названия контрагентов. В области данных находятся два поля — сумма по количеству реализованных товаров (поле «
Кол») и сумма по объемам продаж в гривнях (поле «Сумма»). Первое наше действие будет таким: 
1) ставим указатель активной ячейки внутрь сводного отчета;
2) щелкаем на пункте «Главная» основного меню Excel 2007;
3) в группе «Редактирование» щелкаем на иконке «Сортировка и фильтр». Раскроется меню из шести пунктов. Мы видим, что в этом меню пункт «Фильтр» недоступен.
Поэтому для отбора записей в сводном отчете нам понадобятся другие средства. И теперь наша задача — ввести в отчет дополнительное поле и воспользоваться этим полем для фильтрации записей сводной таблицы. В качестве поля для фильтра я выбрал дату. Делаем так:
1) ставим указатель активной ячейки внутрь сводной таблицы. На экране должно появиться окно «Список полей сводной таблицы»;
2) если это окно не появилось, щелкаем правой кнопкой мыши и выбираем из контекстного меню пункт «Показать список полей»;
3) в окне «Список полей сводной таблицы» в области «Выберите поля для добавления в отчет» находим элемент «Дата» и перетаскиваем его в окошко «Фильтр отчета» (рис. 2). Сводная таблица примет форму, как на рис. 3.
Важно!
Есть другой способ включить работу фильтра сводной таблицы. Для этого нужно перетащить поле фильтрации на рабочий лист в так называемую область страниц сводного отчета. Эта область расположена на одну строку выше основного раздела таблицы и хорошо видна на рис. 1 (заголовок «Перетащите сюда поля страниц»).Итак, что же изменилось после наших действий? В верхней части отчета появилось поле «Дата». Справа от этого поля находится дополнительный элемент — значок выпадающего списка. Сейчас данные сводной таблицы показывают значения, посчитанные по всем записям исходной базы данных. Об этом свидетельствует надпись «Все» в значке раскрывающегося списка. Посмотрим, как видоизменится отчет, если изменить это значение. Делаем так:
1) щелкаем на значке раскрывающегося списка справа от элемента «Дата». Появится окно, изображенное на рис. 4;
2) щелчком мыши выделяем любую дату, например «03/01/2009»;
3) нажимаем «ОК». Теперь сводный отчет выглядит, как показано на рис. 5.
Форма сводной таблицы не изменилась, а вот содержимое стало другим. Теперь все значения в отчете показывают объемы реализации по контрагентам (в количествах и в штуках), но только за один день, а точнее, за «03/01/2009». Это несложно проверить:
1) открываем лист с исходной базой данных;
2) ставим активную ячейку внутрь таблицы с данными;
3) на ленте «Главная» в разделе «Редактирование» находим иконку «Сортировка и фильтр». Щелкаем на ней левой кнопкой мыши;
4) из раскрывшегося меню выбираем пункт «Фильтр». В заголовках таблицы появятся значки выбора;
5) щелкаем на значке выбора в колонке «Дата». Откроется выпадающее меню с перечнем дат. Элементы этого меню могут быть сгруппированы по месяцам («Январь», «Февраль» и т. д.);
6) щелчком мыши убираем галочки возле названия каждой группы. Мы хотим отфильтровать данные только за один день;
7) находим значок «+» (плюс) возле названия группы, нас в данном случае интересует «Январь»;
8) щелчком мыши раскрываем его. Появится перечень всех дат за январь, которые присутствуют в базе данных;
9) ставим галочку возле даты «03/01/2009»;
10) в окне работы с фильтром нажимаем «ОК»;
11) щелкаем на значке выбора в колонке «Покупатель»;
12) в появившемся списке покупателей оставляем галочку только возле элемента «ООО «Эталон»;
13) в окне работы с фильтром нажимаем «ОК»;
14) щелчком мыши в области заголовков листа выделяем колонку «D» (в ней записаны сведения о количестве проданного товара);
15) в статусной строке считаем значение суммы по данному полю. Оно равно «25». Именно такое количество ТМЦ было продано контрагенту «ООО «Эталон» в субботу «03/01/2009». Возвращаемся к сводному отчету (рис. 5) и видим, что это же число стоит в ячейке «С5».
По умолчанию в окне управления фильтром можно указать только один элемент, но это легко изменить. Для этого в окне работы с полем фильтра (рис. 4) нужно включить флажок «Выделить несколько элементов». Слева возле каждого значения в окне управления фильтром появятся пустые квадратики. Щелчком мыши на любом квадратике ставим в нем галочку. Все отмеченные элементы будут участвовать в работе фильтра.
В Excel 2007 в область страниц можно поместить несколько полей из основной базы данных. В этом случае появится возможность формировать более сложные условия фильтрации. Например, мы могли бы поставить в область страниц два поля — «Дата» и «НомДок». Тогда при работе со сводным отчетом мы смогли бы отбирать и пересчитывать значения не только для выбранных дат, но и для любого набора документов.
Важно!
Несколько условий в фильтре сводной таблицы взаимодействует в соответствии с логической функцией «И».Допустим, мы поставили в область страниц поля «Дата» и «НомДок». В списке поля «Дата» отметили «03/01/2009», а в списке поля «НомДок» выбрали элементы «НК-001», «НК-002», «НК-015». В итоговой сводной таблице останется только один контрагент — ООО «Эталон» (с количеством продаж «25 шт.» и объемом реализации «28,25 грн.»). В расчете этих значений будут участвовать данные только по документам «НК-001», «НК-002», так как накладная «НК-015» датирована «04/01/2009», поэтому в расчетах она не участвует.
Удалить поле из фильтра можно разными способами. Вот два варианта возможных действий.
Способ 1:
1) поставить указатель мыши на удаляемое поле в области «
Фильтр отчета» окна «Список полей сводной таблицы» (рис. 2);2) удерживая левую кнопку мыши, перетащить поле на рабочий лист или в другое место окна «
Список полей сводной таблицы»;3) как только указатель примет крестообразную форму (в виде буквы «
Х»), отпустить кнопку мыши.Способ 2:
1) поставить указатель мыши на удаляемое поле в области сводной таблицы (ячейка «
A2» на рис. 3);2) удерживая левую кнопку мыши, перетащить поле на рабочий лист или внутрь окна «
Список полей сводной таблицы»;3) как только указатель примет форму буквы «
Х», отпустить кнопку мыши.Теперь делаем так:
1) оставляем в области страниц сводного отчета только поле «
Дата»;2) переходим в окно «
Список полей сводной таблицы». Сейчас поле «Покупатель» расположено в области «Названия строк»;3) находим поле «
Покупатель» в окошке «Выберите поля для добавления в отчет:» и перетаскиваем его в область страниц сводного отчета. Элемент «Покупатель» из области «Названия строк» Excel уберет автоматически.Важно!
Одно и то же поле не может одновременно находиться в области строк, колонок, данных и при этом еще участвовать в работе фильтра.Такая логика разработчиков Excel имеет резон. Дело том, что у полей в области строк и колонок есть свои фильтры (значок раскрывающегося списка в поле «Покупатель» на рис. 5). Поэтому создание двух одинаковых полей для фильтрации данных может привести к созданию противоречивых условий при отборе записей.
И все же… или альтернативные способы фильтрации сводных таблиц
С возможностями отбора записей непосредственно в сводном отчете все понятно. Инструмент в принципе работает, но обычный автофильтр все-таки гораздо интереснее. Например, в нем можно построить довольно гибкие условия фильтрации, выбрать записи по признаку «
больше», «меньше», «содержит», «не содержит» и т. д. При работе со встроенными средствами сводных таблиц такие возможности использовать нельзя. Однако есть один секрет, который поможет нам решить эту проблему. Но, прежде чем продолжить работу, перестроим сводный отчет, приведя его к форме, изображенной на рис. 6 на с. 20. В этой таблице в области строк расположено поле «НаимТов», в области колонок — «Покупатель», а в области данных я оставил единственное поле «Сумма» и указал для него операцию суммирования. В таком виде отчет больше похож на базу данных, и это как раз то, что нам нужно. Теперь наша задача — рядом со сводной таблицей построить рабочую колонку, затем перенести в эту колонку нужные данные из сводного отчета и применить к рабочей колонке автофильтр. 
Предположим, что мы решили отфильтровать таблицу по значениям «НаимТов». Это означает, что в рабочий столбец нам нужно перенести значения из колонки «А» сводного отчета (рис. 6), причем при обновлении сводной таблицы значения в рабочей колонке тоже должны обновляться. По идее добиться этого можно при помощи ссылок на соответствующие ячейки. Сделаем это:
1) ставим указатель активной ячейки на «F4», чтобы ввести формулу (предположительно это будет ссылка на ячейку «=A4»);
2) нажимаем знак «=»;
3) щелкаем на ячейке «A4». Excel 2007 вместо ожидаемой ссылки написал формулу «=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма";$A$2;"Покупа тель";"ЧП «Лапина»"; "НаимТов"; "Блокнот")». Похоже, это немного не то, чего мы ожидали. Придется применить одну хитрость. Делаем так:
1) ставим указатель активной ячейки на «F3» на уровне заголовка сводной таблицы;
2) нажимаем знак «=»;
3) щелкаем на ячейке «A3». В «F3» появилась формула «=A3» и тут же видим результат ее работы — текст «НаимТов»;
4) копируем формулу по колонке на всю высоту таблицы (рис. 7);
5) возвращаемся в ячейке «A3», вводим в нее заголовок, например «Раб»;
6) не покидая колонки «Раб» (активная ячейка должна оставаться в этом столбце!), щелкаем на пункте «Главная» основного меню, затем на иконке «Сортировка и фильтр» (группа «Редактирование»). Появится выпадающее меню, пункт «Фильтр» теперь в нем доступен;
7) щелкаем на этом пункте левой кнопкой мыши. В заголовках сводной таблицы и в колонке «Раб» появились значки выбора;
8) щелкаем на значке возле поля «Раб». Откроется стандартное меню автофильтра, изображенное на рис. 8. Дальше все по привычной схеме;
9) щелкаем на пункте «Текстовые фильтры» (в поле «Раб» записаны названия товаров, это и есть причина появления инструмента фильтрации текстовых значений). Раскроется дополнительное меню;
10) в этом меню выбираем пункт «Содержит». Появится окно пользовательского фильтра, как на рис. 9 (типичный инструмент для автофильтрации!);
11) в этом окне в качестве значения вводим, например, символы «Ск». На экране останется часть сводного отчета для товаров « Скотч» и « Скрепка канц.» (рис. 10). Автофильтр работает!
Интересный момент. Обратите внимание, что значки выбора появились во всех колонках сводного отчета. Щелкаем на любом из этих значков левой кнопкой мыши. Откроется меню автофильтра, как на рис. 8. То есть после создания вспомогательной колонки мы можем пользоваться автофильтром применительно к сводной таблице точно так, как мы это делали к базам данных.
И еще один нюанс. Сделайте так:
1) откройте сводную таблицу с рабочей колонкой, как на рис. 7;
2) щелкните на иконке «Сортировка и фильтр» (меню «Главная»). Раскроется выпадающее меню;
3) щелкните на иконке «Фильтр», чтобы отменить действие автофильтра;
4) сотрите все формулы в колонке «Раб»;
5) не покидая вспомогательной колонки, включите автофильтр. Сводная таблица примет следующий вид (рис. 7) — все возможности автофильтра снова к вашим услугам! Отсюда вывод:
Важно!
Автофильтр нельзя применить непосредственно к сводной таблице, но его можно применить к вспомогательной колонке. Желательно (но не обязательно), чтобы эта колонка была расположена рядом со сводной таблицей. Значения в рабочую колонку можно не вводить. Применив автофильтр к рабочей колонке, его действие распространится на всю сводную таблицу.
Сводная таблица и расширенный фильтр
Наш последний эксперимент коснется взаимодействия сводных таблиц с расширенным фильтром. Вернемся к отчету, изображенному на рис. 6. Делаем так:
1) добавляем в верхней части рабочего листа две (или больше) дополнительные строки;
2) копируем заголовки сводной таблицы (в нашем примере после добавления двух строк это ячейки «
A5:E5») в первую строку рабочего листа начиная с «A1». Здесь есть одна тонкость:Секрет
Выделяя заголовки сводного отчета перед копированием в буфер обмена, начинайте с ячейки «E5» (т. е. выделение блока делайте справа-налево). Если вы попытаетесь выделить блок «A5:E5» начиная с «A5», то ничего не получится. Как только вы поставите указатель на ячейку «A5» и нажмете левую кнопку мыши, Excel перейдет в режим корректировки структуры сводного отчета. Вместо того, чтобы отмечать блок, он будет перемещать поле «НаимТов» и корректировать тем самым макет сводного отчета.3) выделив заголовки, копируем их в буфер обмена;
4) переходим на ячейку «A1» и вставляем содержимое из буфера. В заголовках области критериев появились названия предприятий. Изменять эти значения нельзя! Они должны в точности совпадать с названиями заголовков основной базы данных;
5) в ячейку «A2» печатаем текст, например «Блокнот»;
6) вызываем меню «Данные»;
7) в группе «Сортировка и фильтр» на ленте этого меню щелкаем на иконке «Дополнительно». Появится окно, изображенное на рис. 11;
8) заполняем параметры «Исходный диапазон:» и «Диапазон условий:», как показано на рисунке. То есть в качестве базы выбираем содержимое сводной таблицы. Условия в нашем примере находятся над сводным отчетом в блоке «$A$1:$E$2»;
9) нажимаем «ОК». На экране осталась единственная строка — суммарный объем реализации ТМЦ «Блокнот» (рис. 12).
Важно!
Расширенный фильтр нормально работает со сводными таблицами.Подведем итоги. Сводные таблицы имеют встроенный инструмент для фильтрации записей. Для этого в сводном отчете предусмотрена специальная область страниц. В этой области можно расположить несколько полей и тем самым расширить возможности по созданию условий для отбора записей. В качестве дополнительного инструмента можно использовать окно фильтрации, которое работает с полями в области строки и колонок сводного отчета. Тем не менее возможности встроенных фильтров у сводных таблиц ограничены. Но вы можете решить проблему, применив автофильтр к дополнительной колонке, разместив эту колонку непосредственно возле сводного отчета. Значения в колонку вводить не обязательно, но их наличие сделает процесс работы с отчетом более наглядным. Помните, что к сводной таблице можно применить расширенный фильтр.
На этом мы завершим тему фильтрации данных в сводном отчете и в следующий раз посмотрим, какие способы вычисления предлагают сводные таблицы, в чем их особенности, как можно вставить формулу в сводный отчет и что это дает бухгалтеру на практике.
Жду ваших вопросов, писем, предложений и замечаний на
bk@id.factor.ua, nictomkar@rambler.ru/a> или на форуме редакции www.bk.factor.ua/forum . Успешной работы!
