Excel 2007: работа с Конструктором сводных таблиц
 | Программа Excel 2007 дает бухгалтеру широкий ассортимент средств по управлению сводными таблицами. Это и параметры сводных отчетов, и свойства полей таблицы. Но есть еще один инструмент, который объединяет ряд специальных возможностей по работе со сводными отчетами. Речь идет о Конструкторе сводных таблиц. Об этом новом инструменте Excel 2007 мы и поговорим в нашей статье, а заодно посмотрим на дополнительные возможности, которые скрыты в Мастере сводных таблиц. Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства |
Завершив создание сводной таблицы, имеет смысл изменить макет и формат отчета, чтобы сделать его более привлекательным. Способов для решения такой задачи несколько, и один из них — Конструктор сводных таблиц. Познакомиться с основными возможностями этого компонента Excel и будет сейчас нашей задачей.
Работа с Конструктором сводных таблиц
Чтобы вызвать Конструктор, сделайте так:
1) поставьте активную ячейку внутрь сводной таблицы. В правой части главного меню программы появится раздел «
Работа со сводными таблицами»;2) в этом разделе щелкните на кнопке «
Конструктор». Лента основного меню примет вид, как на рис. 1. На ней появятся три группы иконок: «Макет», «Параметры стилей сводной таблицы» и «Стили сводной таблицы». Они позволяют изменить стиль оформления сводной таблицы, характер определения промежуточных итогов, откорректировать макет отчета и т. п. Посмотрим на работу основных инструментов Конструктора на конкретных примерах.
Управление итогами в сводной таблице
Сводные отчеты часто используют в качестве заготовок для выполнения различных расчетов. В таких расчетах может понадобиться сформировать промежуточные итоги по значениям ключевого поля. Эту работу можно сделать специальным инструментом «
Итоги» MS Excel, но с ней успешно справится и Конструктор сводных таблиц. Посмотрим, как это выглядит на практике. В качестве исходных данных воспользуемся уже знакомой базой по реализации товаров (рис. 2). Делаем так: 
1) формируем сводный отчет, где в области строк расположены два поля: даты и названия покупателей. В область данных переносим поле «
Сумма», указав для него операцию суммирования. Получим структуру таблицы, как на рис. 3 (см. с. 19). По логике данных в ней можно подвести итог по объемам реализации каждой группы покупателей за определенную дату; 
2) открываем Конструктор сводных таблиц;
3) щелкаем на иконке «
Промежуточные итоги» в группе иконок «Макет». Откроется список, как на рис. 4; 
4) из этого списка выбираем пункт «
Показывать все промежуточные итоги в нижней части группы». После смены значения в поле «Дата» Excel внедрит в сводный отчет строку с итогами (рис. 3).Тот же результат можно получить другим способом — через параметры поля сводной таблицы. Сделайте так:
1) щелкните правой кнопкой мыши на любом значении поля «
Дата»;2) из контекстного меню выберите пункт «
Параметры поля…». Откроется одноименное окно «Параметры поля…» (рис. 5); 
3) в группе «
Итоги» поставьте переключатель в положение «Другие», в окошке с перечнем функций выберите «Сумма»;4) нажмите «
ОК». Таблица примет форму, как на рис. 3.Для того чтобы добавить или отменить добавление итогов по строкам и столбцам сводного отчета, можно воспользоваться иконкой «
Общие итоги» (рис. 1). Щелчком на этой иконке раскрываем меню из четырех пунктов: «Отключить для строк и столбцов», «Включить для строк и столбцов», «Включить только для строк», «Включить только для столбцов». Щелчок на любом из пунктов включает или отменяет отображение соответствующих итогов.
Изменение макета сводного отчета
Возможности управления макетом сосредоточены в иконке «
Макет отчета». Щелкаем левой кнопкой на этой иконке. Раскроется меню, как на рис. 6. Здесь Excel предлагает три варианта: «Показать в сжатой форме», «Показать в форме структуры», «Показать в табличной форме». 
Вариант «
Показать в сжатой форме» используют, чтобы плотнее расположить данные по горизонтали. При этом несколько ключевых полей Excel переносит в один столбец, а структуру взаимосвязи между полями показывает с помощью отступов. Внешний вид нашей таблицы (рис. 3) в сжатом формате показан на рис. 7. 
Вариант «
Показать в форме структуры» применяют, чтобы показать структуру данных в классическом стиле сводной таблицы. Выбирая «Показать в табличной форме», вы представите отчет в традиционном табличном формате.Совет Выбирайте вариант «Показать в табличной форме», если планируете дальнейшее преобразование сводной таблицы. Этот способ лучше других приспособлен для копирования или перемещения ячеек через буфер обмена.
Добавление пустых строк в сводный отчет
В сводную таблицу можно вставить пустые строки после каждого изменения ключевого поля. Сделайте так:
1) откройте таблицу, как на рис. 3. Итоги из нее можно удалить. Для этого вызываем Конструктор, щелкаем на иконке «
Промежуточные итоги», затем из появившегося меню выбираем «Не показывать промежуточные суммы» (рис. 4);2) оставаясь внутри сводного отчета, щелкаем на иконке «
Пустые строки». Появится меню, как на рис. 8; 
3) выбираем вариант «
Вставить пустую строку после каждого элемента». Отчет примет форму, как на рис. 9. 
Чтобы удалить пустые строки, нужно обратиться к пункту «
Удалить пустую строку после каждого элемента».Возникает закономерный вопрос: для чего может пригодиться возможность добавления пустых строк в сводный отчет?
Во-первых, для улучшения внешнего вида отчета, поскольку сложная и насыщенная данными таблица с промежутками читается легче. Во-вторых, для организации специфических вычислений. Вернемся к таблице на рис. 3. Допустим, нам понадобилось посчитать общую сумму НДС по всем контрагентам за каждую дату. Делаем так:
1) открываем сводную таблицу;
2) не покидая ее, щелкаем на иконке «
Пустые строки»;3) из меню (рис. 8) выбираем «
Вставить пустую строку после каждого элемента»;4) выделяем всю сводную таблицу. Для этого вызываем меню «
Параметры», щелкаем на иконке «Выбрать» ленты «Действия». Из раскрывшегося меню выбираем пункт «Всю сводную таблицу»;5) копируем таблицу в буфер обмена («
Ctrl+C»);6) не снимая выделения, щелкаем правой кнопкой мыши, из контекстного меню выбираем «
Специальная вставка». Появится окно «Специальная вставка»;7) в окне специальной вставки ставим переключатель в положение «
Значения»;8) нажимаем «
ОК»;9) выделяем колонку «
С» («Сумма, грн.»);
10) нажимаем «
Ctrl+G». Появится окно «Переход»;11) в этом окне щелкаем на кнопке «
Выделить…». Появится окно «Выделение группы ячеек»;12) в этом окне ставим переключатель «
Выделить» в положение «пустые ячейки»;13) нажимаем «
ОК». После этого активной станет первая пустая ячейка в колонке «С». В примере на рис. 3 это ячейка «С7». В нее нужно ввести формулу для расчета НДС по значению в ячейке «С6» (сумма оборота по всем контрагентам за «03/01/2009»);14) не снимая выделения, вводим формулу. Для таблицы на рис. 3. она будет такой: «
=C6*0,2»;15) нажимаем «
Ctrl+Enter». После каждого промежуточного итога появится дополнительная строка с суммой НДС.
Назначение стиля сводному отчету
С помощью Конструктора сводных таблиц можно быстро отформатировать отчет, используя встроенную библиотеку стилей. Для этого делаем так:
1) открываем сводный отчет (рис. 3);
2) становимся внутрь сводной таблицы;
3) щелкаем на кнопке «
Конструктор»;4) на ленте Конструктора в группе «
Стили сводной таблицы» (рис. 1) щелкаем на любой иконке. Я выбрал «Стиль сводной таблицы: светлый 1». Отчет примет вид, как на рис. 10. 
Теперь все заголовки строк и колонок сводной таблицы выделены полужирным начертанием. Таким же форматированием отмечены промежуточные итоги. Но это еще не все.
В стиле таблицы записано много параметров. Некоторые из них представлены в группе иконок «
Параметры стилей сводной таблицы» (рис. 1). Сделайте так:1) щелкните на иконке «
Заголовки строк» (группа «Параметры стилей сводной таблицы»; рис. 1). Заголовки будут без полужирного начертания;2) щелкните на иконке «
Заголовки колонок». Формат заголовков колонок станет обычным;3) включите флажок «
Чередующиеся строки» — это позволит попеременно выделить каждую строку светлым и темным фоном. Каждая нечетная строка сводного отчета на рис. 3 будет выделена серым фоном;4) щелкните на иконке «
Чередующиеся столбцы». Excel выделит фоном все нечетные колонки. В нашем примере на рис. 3 это будет столбец «Сумма, грн.».Разумеется, вы можете пользоваться не только имеющимися стилями, но и создавать свои. Все это можно сделать прямо с ленты Конструктора. Подробнее о работе со стилями вы можете прочитать в одной из наших предыдущих статей по работе с Excel 2007 (например, см. «Б & К», 2009, № 16, с. 19; «Б & К», 2009, № 17, с. 11; «Б & К», 2009, № 18, с. 31).
Прочие операции со сводной таблицей
В завершающей части статьи мы рассмотрим некоторые возможности работы со сводными отчетами, которые до сих пор остались без нашего внимания. Их применение наверняка разнообразит приемы работы с Excel 2007.
Как удалить форматирование сводного отчета
Если к сводной таблице применить встроенный стиль, то не все параметры форматирования можно будет изменить вручную. Вернемся к таблице на рис. 10. С помощью стиля «
Стиль сводной таблицы: светлый 1» мы отформатировали заголовки таблицы полужирным начертанием. Сделайте так:1) поставьте активную ячейку на «
A2»;2) нажмите «
Ctrl+B», чтобы отменить полужирное начертание. Формат ячейки остался прежним.Все верно: те атрибуты форматирования, которые явно указаны в стиле, имеют более высокий приоритет. Чтобы отменить действие стиля, сделайте так:
1) поставьте указатель активной ячейки внутрь сводной таблицы;
2) щелкните на кнопке «
Конструктор» меню «Работа со сводными таблицами». В правой части ленты этого меню появится группа «Стили сводной таблицы» с галереей доступных стилей оформления.
Сортировка данных в сводном отчете
Отсортировать данные в сводном отчете можно несколькими способами, например через параметры ключевого поля. Но можно воспользоваться и лентой «
Параметры» меню «Работа со сводными таблицами». Делаем так:1) открываем сводный отчет;
2) становимся на произвольный элемент ключевого поля (при сортировке по колонке «
Дата» таблицы на рис. 10 это может быть, например, ячейка «A2»);3) щелкаем на иконке «
Сортировка» ленты «Параметры» (рис. 11). Появится окно с параметрами сортировки; 
4) в зависимости от задачи указываем тип сортировки: по возрастанию, по убыванию или вручную (рис. 12);
5) нажимаем «
ОК».Обратите внимание на возможность сортировки данных «
Вручную». В этом режиме вы сможете произвольно переместить элементы в колонке ключевого поля. Такое действие определит порядок сортировки всей сводной таблицы.
Очистка, выбор и перемещение сводного отчета
Эти возможности находятся в группе «
Действия» ленты «Параметры» (рис. 11). Делаем так:1) открываем сводную таблицу, как на рис. 10;
2) ставим указатель активной ячейки внутрь сводного отчета;
3) щелкаем на иконке «
Очистить» (рис. 11). Появится меню из двух пунктов — «Очистить все» и «Очистить фильтры»;4) выбираем вариант «
Очистить все». Сводная таблица будет удалена с рабочего листа;Важно! В данном примере Excel удалит только содержимое и макет сводной таблицы. Сам объект останется на рабочем листе в виде пустого макета. Чтобы избавиться от сводной таблицы вместе с макетом, нужно удалить соответствующие строки или колонки рабочего листа.
5) отменяем последнее действие комбинацией «Ctrl+Z». Сводный отчет снова появится на рабочем листе;
6) оставаясь внутри сводной таблицы, щелкаем на иконке «Выбрать» (рис. 11). Появится меню, изображенное на рис. 13;
7) в этом меню выбираем «
Всю сводную таблицу». Excel выделит весь сводный отчет. Через это же меню можно выделить заголовки или значения в сводной таблице;
8) не покидая сводной таблицы в группе «
Действия», щелкаем на иконке «Переместить» (рис. 11). Появится окно, как на рис. 14; 
9) в этом окне указываем новое местоположение сводного отчета. Это может быть другой лист или уже имеющийся. В последнем случае нужно указать ячейку, где будет начинаться сводная таблица.
Определив местоположение сводного отчета, нажимаем «
ОК». Excel переместит всю сводную таблицу на новое место.
Настройки вида сводной таблицы
Помимо форматирования с настройки параметров сводной таблицы, на панели «
Параметры» в группе «Показать или скрыть» предусмотрены три иконки для управления внешним видом сводной таблицы:— «
Список полей» (открывает окно «Список полей сводной таблицы»);— «
Кнопка +/-» (включает или отключает показ символов группировки в сводном отчете);— «
Заголовки полей» (включает или отключает режим отображения заголовков ключевых полей сводной таблицы).
Настройка источника данных
Итак, вы создали сводный отчет, настроили его макет и подобрали параметры форматирования. И в этот момент выясняется, что исходные данные изменились. Например, к ним были добавлены новые записи, которые не попадают в область данных сводной таблицы. Как поступить в такой ситуации? Формировать весь отчет заново? Конечно же нет. Для решения проблемы достаточно просто изменить источник данных, воспользовавшись иконкой «
Изменить источник данных» в группе «Данные» ленты «Параметры» (рис. 11).Делаем так:
1) открываем сводный отчет;
2) щелкаем на кнопке «
Параметры» меню «Работа со сводными таблицами»;3) щелкаем на иконке «
Изменить источник данных». Откроется меню из двух пунктов (рис. 15);4) выбираем вариант «
Изменить источник данных…». Откроется Мастер сводных таблиц;5) в его окне вводим новый диапазон рабочего листа;
6) нажимаем «
ОК».Говоря об управлении источниками данных, нужно отметить еще один важный момент. Если вы обратили внимание, при работе со сводными таблицами нам ни разу не встретился инструмент создания отчета по нескольким диапазонам консолидации. А для бухгалтера такая возможность в определенных ситуациях очень важна! Вот пример.
Есть две базы данных. Одна из них находится на листе «
Поступления». Здесь собраны сведения о поступлении товаров (рис. 15). Вторая база расположена на листе «Продажи». В ней записана информация об объемах продаж (рис. 16). Структура обеих таблиц понятна. В таблице «Поступления» вначале идет колонка «НаимТов» — это название поступившего ТМЦ. Затем следует столбец «Кол» — количество поступившего товара. Далее по порядку: цена за единицу, сумма поступления, номер накладной, дата поступления, название поставщика. Состав таблицы «Продажи» точно такой же. Наша задача — определить остатки товаров в количественном выражении. Фактически нам нужно поэлементно «вычесть» одну таблицу из другой, используя для этого поле «Кол». Здесь есть два момента: 1) нам нужно найти инструмент для создания отчетов из нескольких рабочих листов; 2) мы должны придумать, как выполнить операцию вычитания полей в сводной таблице (в стандартном наборе арифметических действий над полем такой операции нет). Делаем так:1) открываем лист «
Продажи»;2) выделяем колонку «
B» (поле «Кол»);3) щелкаем правой кнопкой мыши, из контекстного меню выбираем «
Вставить». Мы добавили рабочий столбец между полем «НаимТов» и «Кол»;4) в качестве заголовка колонки «
B» печатаем текст «Кол». Теперь получается, что в базе продаж у нас два поля с одинаковым заголовком. В принципе это допустимо, но может внести неразбериху. Поэтому исправим ситуацию с самого начала;5) переходим на заголовок колонки «
C» (ячейка «C2» в таблице «Продажи»);6) корректируем заголовок, дополнив его справа символом «
пробел», т. е. колонка «С» у нас будет называться «Кол »;7) в ячейку «
B2» пишем формулу «=-С2»;8) копируем формулу на всю высоту таблицы (рис. 17).
Важно! Заголовки колонок «B» на листах «Продажи» и «Поступления» должны в точности совпадать. Это важно при создании сводного отчета из нескольких рабочих листов.
С этого момента в рабочей колонке таблицы «Продажи» будут показаны сведения об объемах реализации, но со знаком «-». Теперь нам достаточно просуммировать элементы колонки «Кол» листа «Поступления» с элементами колонки «Кол» листа «Продажи», и задача будет решена. Но для этого нам понадобится инструмент для создания отчетов по нескольким диапазонам консолидации (по нескольким листам рабочей книги). Такой инструмент в Excel 2007 есть, но его нужно включить через параметры программы. Делаем так:
1) щелкаем на кнопке «Офис» программы Excel 2007;
2) в окне главного меню щелкаем на кнопке «Параметры Excel» (рис. 18). Откроется окно настроек программы (рис. 19);
3) в этом окне переходим на пункт «
Настройка». В центральной части окна появится окошко с перечнем команд MS Excel;4) щелчком мыши раскрываем список «
Выбрать команды из:», выбираем значение «Все команды» (рис. 19);5) в подробном перечне команд находим «
Мастер сводных таблиц и диаграмм» и выделяем его;6) нажимаем кнопку «
Добавить», чтобы перенести иконку Мастера на панель быстрого доступа;7) нажимаем «
ОК». Возле кнопки «Офис» на панели быстрого доступа появится значок для вызова Мастера сводных таблиц. Он отличается от используемых нами ранее средств работы со сводными отчетами тем, что содержит все режимы формирования сводных отчетов, в том числе и работы с несколькими диапазонами консолидации. Теперь можно приступать непосредственно к решению задачи. Делаем так:1) создаем новый лист, становимся в левый верхний угол этого листа;
2) щелкаем на иконке вызова Мастера сводных таблиц на панели быстрого запуска. Появится окно, как на рис. 20;
3) в этом окне ставим переключатель «
Создать таблицу на основе данных, находящихся в:» в положение «в нескольких диапазонах консолидации»;4) щелкаем «
Далее». Откроется окно второго шага работы с Мастером сводных таблиц. В нем Excel предложит два варианта: «Создать одно поле страницы» или «Создать поля страницы» в сводном отчете;5) выбираем «
Создать одно поле страницы»;6) нажимаем «
Далее». Появится окно для определения источника данных, как на рис. 21; 
7) переходим на лист «
Продажи»;8) обводим колонки «
A:B» на этом листе. Мы включили в источник данных сведения о наименовании товаров и о количестве продаж (со знаком «-»);9) в окне Мастера сводных таблиц нажимаем кнопку «
Добавить»;10) переходим на лист «
Поступления», блок «A:B» Excel выделит автоматически;11) нажимаем «
Далее»;12) на завершающем этапе Excel предложит поместить таблицу на новый или на существующий лист. Выбираем «
на существующий лист»;13) нажимаем «
Готово». На текущем листе появится сводная таблица. В ней всего одна колонка с остатками и колонка с общим итогом по остаткам. Значения в этих столбцах одинаковые, поэтому итоги лучше скрыть;14) щелкаем на пункте «
Конструктор» меню работы со сводными таблицами;15) щелкаем на иконке «
Общие итоги» в группе «Макет»;16) из раскрывшегося меню выбираем «
Отключить для строк и столбцов». Фрагмент полученного отчета по остаткам ТМЦ показан на рис. 22. Все значения в этом отчете посчитаны как разница между приходами (лист «Поступления») и продажами (лист «Продажи»). 
На этом завершим тему работы со сводными отчетами. В нашей следующей статье мы начнем работать с деловой графикой Excel и познакомимся с особенностями ее реализации в Excel 2007.
Жду ваших писем, предложений и замечаний на
bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum . Успешной работы!
