Excel 2007: настройка параметров сводных таблиц
 | При создании сводных отчетов основное внимание традиционно уделяют приемам формирования макета, способу группировки полей и определению параметров вычислений. И это правильно: указанные моменты при работе со сводными таблицами действительно ключевые. Но не единственные. Сводные отчеты обладают целым рядом свойств и настроек, без которых эффективное использование этого инструмента проблематично. Что это за параметры, как их сможет использовать бухгалтер в своей работе, мы рассмотрим в этой статье. Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства |
Завершая тему по работе со сводными таблицами, нам осталось разобраться в таких моментах: 1) как настроить формат полей сводной таблицы; 2) какими свойствами обладает сводный отчет; 3) что позволяет сделать Конструктор сводных таблиц; 4) как управлять источниками данных при формировании сводной таблицы. Все эти вопросы мы подчиним единой цели — акцентировать ваше внимание на тех (порой малоиспользуемых) возможностях, которые способны повысить эффективность работы со сводными отчетами Excel 2007. Заодно остановимся на тех интересных новинках, которые появились в последней редакции этой программы. Начнем мы с параметров полей сводного отчета.
Настройка параметров полей сводного отчета
Состав параметров поля сводной таблицы зависит от того, является ли это поле ключевым, или оно расположено в области значений. Это вполне объяснимо: над этими элементами выполняются совершенно разные операции. Начнем с полей, которые расположены в области данных.
Параметры поля в области данных
Часть настроек поля в области данных нам уже знакома. Мы неоднократно строили сводные отчеты и работали в окне «
Параметры поля значений». В нем мы выбирали способ обработки значений ключевого поля, т. е. операцию (сумма, количество значений, максимум, минимум и т. д.). Так что повторяться не будем. Но одного вопроса мы еще не коснулись. Речь идет о форматировании значений поля в области данных. Проблема заключается в том, что при каждом обновлении таблицы она восстанавливает свой формат, принятый по умолчанию. Чтобы показать это на примере, сделаем так:1) открываем сводный отчет. Я выбрал таблицу, изображенную на рис. 1 на с. 32. Сейчас в ней все данные оформлены шрифтом
Arial обычного начертания, размером 8 пт. Формат представления чисел: для стоимостных показателей — два знака после запятой, для количественных показателей — ноль знаков после запятой; 
2) выделяем блок ячеек «
C3:E3» (объемы продаж в гривнях для ООО «Эталон»);3) не снимая выделения, щелкаем на блоке правой кнопкой мыши;
4) из контекстного меню выбираем «
Формат ячеек…». Появится одноименное окно «Формат ячеек»;5) в этом окне переходим на закладку «
Число» и выбираем вариант «Числовой» с тремя десятичными знаками после запятой;6) в окне «
Формат ячеек» нажимаем «ОК». Значения в блоке «C3:E3» приобрели новое форматирование;7) опять щелкаем правой кнопкой мыши в области сводной таблицы;
8) из контекстного меню выбираем «
Обновить». Форматирование таблицы пропало, внешний вид ячеек стал таким же, как до внесенных нами изменений.Важно! По умолчанию при обновлении сводной таблицы ячейки отчета получают параметры форматирования, характерные для стиля «
Обычный». Конкретные значения этих параметров зависят от настроек в вашей версии программы Excel.Снять указанную проблему очень просто — нужно определить параметры форматирования непосредственно в сводном отчете. Вот как это сделать:
1) ставим указатель активной ячейки на поле, для которого нужно изменить параметры форматирования. Например, на ячейку «
C3»;2)
щелкаем правой кнопкой мыши. Появится контекстное меню, как на рис. 2; 
3) из этого меню выбираем пункт «
Параметры полей значений». Появится окно «Параметры поля значений», как на рис. 3 (на с. 33); 
4) в этом окне щелкаем на кнопке «
Числовой формат». Появится знакомое окно «Формат значений». Правда, в усеченном варианте: в нем представлена единственная закладка — «Число»;5) выбираем параметры форматирования для выбранного значения в сводном отчете (в нашем случае — это объемы реализации в гривнях). Согласно примеру в окне «
Формат ячеек» нужно выбрать числовой формат с округлением до трех знаков;6) нажимаем «
ОК». Форматирование данных в сводном отчете изменилось;7) щелкаем правой кнопкой мыши в области сводной таблицы;
8) из контекстного меню выбираем «
Обновить». Форматирование таблицы осталось без изменений.Важно! Чтобы изменить числовой формат для данных сводного отчета, пользуйтесь параметрами полей сводной таблицы.
Параметры ключевого поля
С параметрами поля, которое расположено в области данных, мы разобрались. Они сосредоточены в окне «
Параметры поля значений». Здесь можно указать названия (заголовок) поля, определить характер вычислений для этого поля, установить числовой формат. Посмотрим, какие возможности предлагает Excel при работе с ключевыми полями сводной таблицы. Делаем так:1) открываем сводный отчет, я выбрал таблицу, изображенную на рис. 4 (см. с. 33). В ней значениями являются «
Сумма, грн.» и «Кол., шт.». Ключевыми полями этой таблицы являются «Дата» и «Покупатель»; 
2) щелкаем правой кнопкой мыши на любой ячейке в колонке «
Покупатель» Например, на «A3». Появится контекстное меню, как на рис. 5 (см. с. 33); 
3) из предложенного меню выбираем «
Параметры поля…». Появится окно, как на рис. 6 (см. с. 34). В этом окне нам предстоит определить параметры для ключевого поля. 
Как видим, форма окна изменилась. Теперь в нем две закладки: «
Промежуточные итоги и фильтры» и «Разметка и печать».Начнем с первой закладки. Группа «
Итоги» объединяет параметры для определения итогов при смене ключевого поля. По умолчанию переключатель установлен в положение «Автоматические». Это означает, что в сводном отчете будут показаны итоги при каждой смене значения ключевого поля. Сами итоги Excel посчитает по той же функции, которая используется для расчета значений этого поля в сводной таблице. Например, в сводной таблице объемы реализации в стоимостном выражении мы посчитали с помощью функции «Сумма». Эта же функция будет использована для определения итогов по полю «Сумма».Установив переключатель в положение «
нет», можно отказаться от подведения итогов.В положении «
другие» вы можете выбрать функцию для расчета итогов в сводном отчете. Перечень доступных функций стандартный — сумма, количество значений, максимум и т. д.На закладке «
Разметка и печать» (рис. 7 на с. 34) можно видоизменить представление сводной таблицы. Так, отчет можно представить в виде структуры или в виде таблицы. Выбрав способ представления в виде структуры, можно показать отчет в обычной или сжатой форме (флажок «отобразить подписи из следующего поля в том же столбце»). Здесь же есть возможность включить или подавить отображение промежуточных итогов. 
Флажок «
Пустая строка после каждой подписи» внедряет дополнительные строки в сводный отчет, подобно работе промежуточных итогов. Флажок «Отображать пустые элементы» включает или отменяет вывод элементов с пустым значением ключевого поля.Подробнее с работой этих параметров мы познакомимся чуть позже, когда будем рассматривать работу с Конструктором сводных таблиц. А пока подведем небольшой итог:
1) в
Excel можно настроить поля сводного отчета. Перечень доступных параметров для такой настройки зависит от того, является поле ключевым или оно расположено в области значений;2) для поля в области значений можно указать характер вычислений, а также его форматирование;
3) форматировать данные в сводном отчете нужно через параметры полей. В противном случае настройки формата могут пропасть после обновления сводной таблицы;
4) среди параметров форматирования полей в области значений доступны только настройки представления чисел;
5) для ключевых полей можно определить способ подведения промежуточных итогов и макет сводного отчета.
Теперь вернемся к четвертому пункту наших выводов. Бросается в глаза тот факт, что окно «
Формат ячеек» при работе с полями в области значений представлено единственной закладкой для форматирования чисел. И это не случайно. Делаем так:1) выделяем в отчете на рис. 1 любой блок ячеек (например, «
C3:E3»);2) нажимаем «
Ctrl+B» (присвоить полужирное начертание);3) через контекстное меню обновляем таблицу. Параметры полужирного начертания исчезли. Возникает вопрос — а как быть, если нужно отформатировать сводный отчет произвольным образом?
Важно! Чтобы получить возможность изменять любые характеристики форматирования сводной таблицы, нужно изменить ее параметры.
Параметры сводной таблицы
Начнем с интерфейса. Как только активная ячейка попадает в область сводного отчета, в главном меню программы появляется отдельный пункт меню «
Работа со сводными таблицами». Щелкаем на этом пункте левой кнопкой мыши, переходим в режим работы со сводными таблицами. Здесь доступны два раздела: «Параметры» и «Конструктор» (рис. 8). Нас сейчас интересует раздел «Параметры». Посмотрим на его состав подробнее. 
Лента меню раздела параметров состоит из восьми групп иконок. Первая группа — «
Сводная таблица» (рис. 8). Именно она отвечает за настройку основных параметров сводного отчета. Вверху в группе расположено поле «Имя:» с названием сводной таблицы. Сюда можно напечатать любой текст. Правда, на практике такая возможность нужна крайне редко. Ниже поля «Имя:» находится кнопка «Параметры». Щелкаем на ней левой кнопкой мыши. Откроется окно, изображенное на рис. 9. В этом окне пять закладок.
Закладка «Разметка и формат»
Здесь собраны параметры, которые отвечают за внешний вид сводного отчета. Вот ее основные элементы.
Параметр «
Объединить и выровнять по центру ячейки с подписями» (рис. 9) работает в тех случаях, когда в области данных сводного отчета расположено несколько полей. Применительно к таблице на рис. 1 этот параметр объединит ячейки в колонке «Покупатель» таким образом: «A3:A4», «A5:A6» и т. д. Наименование покупателя в объединенных ячейках будет расположено по центру (рис. 10):— флажок «
Отступ для названий строк в сжатой форме» позволяет указать отступ строк в области заголовка строки при отображении отчета в компактном формате. Уровень отступа может принимать значения от «0» до «127»;— флажок «
Отображать поля в области фильтра отчета» управляет расположением полей в области фильтра сводной таблицы. При варианте «Вниз, затем поперек» эти поля будут расположены вертикально. Для варианта «поперек, затем вниз» характерно горизонтальное расположение полей;— флажок «
Число полей фильтра отчета в столбце» регулирует количество полей, которые могут быть расположены в пределах одного столбца в области фильтра. 
Группа «
Формат» объединяет четыре параметра:— флажок «
Для ошибок отображать» позволяет ввести текст, который будет показан в сводном отчете вместо сообщения об ошибке. Например, вы можете включить этот фалажок и ввести в специальное окошко текст «!». Теперь все ячейки с ошибками будут отмечены восклицательным знаком;— флажок «
Для пустых ячеек отображать», здесь можно указать значение, которые Excel поставит в сводном отчете на место пустых ячеек.Совет Если вы используете функции «
Счет()» или другие, связанные с подсчетом значений, включите флажок «Для пустых ячеек отображать» и введите в специальное окошко символ «0» (ноль). Помните, что некоторые функции Excel не любят пустых значений в таблице.Следующие два параметра имеют непосредственное отношение к форматированию сводной таблицы:
— флажок «Автоматически изменять ширину столбцов при обновлении» включает режим автоподбора ширини колонок под имеющиеся значения сводного отчета. Такая возможность выглядит привлекательно, так как избавляет от необходимости периодически менять ширину столбцов, когда в них не помещяются данные. Но есть и обратная сторона медали;
Совет Не используйте режим «
Автоматически изменять ширину столбцов при обновлении», если ваша сводная таблица подготовлена к печати. Скорее всего, вам все равно придется восстанавливать ширину колонок, чтобы скомпоновать документ под заданный размер страницы.— флажок «Сохранять форматирование ячеек при обновлении» позволяет запомнить параметры форматирования сводного отчета. Когда флажок включен, при обновлении сводной таблицы Excel не изменит ее текущее форматирование.
Совет Влючайте флажок «Сохранять форматирование ячеек при обновлении» каждый раз после создания сводной таблицы. Опыт показывает, что сразу после получения сводного отчета вы его отформатируете, чтобы придать таблице подобаюший вид. Обидно, когда после обновления отчета вся ваша работа по форматированию пропадет впустую.
Закладка «Итоги и фильтры»
Внешний вид окна этой закладки показан на рис. 11. Первой в окне расположена группа «
Общие итоги». Она состоит из двух параметров: «Показывать общие итоги для строк» и «Показывать общие итоги для столбцов». Когда включен первый параметр, в конце сводной таблицы Excel вставит дополнительную колонку, где покажет итоговые значения по каждой строке. При включенном параметре «Показывать общие итоги для столбцов» внизу сводной таблицы появится новая строка с итогами по колонкам. Чтобы посмотреть на работу этих параметров, делаем так: 
1) открываем сводную таблицу, как на рис. 1;
2) ставим активную ячейку внутрь сводного отчета;
3) щелкаем на пункте меню «
Параметры», затем на иконке «Параметры» группы «Сводная таблица»;4) в открывшемся окне «
Параметры сводной таблицы» переходим на закладку «Итоги и фильтры»;5) включаем флажки «
Показывать общие итоги для строк», «Показывать общие итоги для столбцов». Отчет примет форму, как на рис. 12 (см. с. 37). 
Параметры группы «
Фильтры» управляют режимом расчета промежуточных итогов в сводной таблице. Если включить флажок «Промежуточные суммы по отобранным фильтром элементам страницы», в промежуточных итогах будут учитываться отфильтрованные элементы отчета. Если отключить этот флажок, отфильтрованные элементы Excel учитывать не будет.При включенном флажке «
Разрешить несколько фильтров для поля» в промежуточных итогах будут учтены все значения, в том числе и скрытые в результате фильтрации. При отключенном флажке в расчете итогов будут участвовать только видимые элементы сводной таблицы.Группа параметров «
Сортировка» состоит из единственного флажка: «Использовать списки при сортировке». Если флажок включен, Excel сможет сделать сортировку строк сводного отчета по спискам пользователя. В таком списке, например, могут быть перечислены названия дней недели.Совет Если вы не пользуетесь специфическими правилами сортировки, отключите флажок «
Использовать списки при сортировке». Особенно при работе с большими таблицами. Это существенно ускорит обработку данных.
Закладка «Вывод»
Параметры этой закладки организованы в группы: «
Экран» (в некоторых версиях Excel 2007 она называется «Отображение») и «Сортировка списка полей». В группе «Экран» есть семь параметров (рис. 13). Вот основные из них: 
— флажок «
Показывать кнопки развертывания и свертывания» включает или отключает отображение кнопок с символами «+» или «-». Эти кнопки появляются, когда в области строк сводного отчета находится несколько полей или когда к элементам строк или колонок применялась ручная группировка значений. При распечатке такого отчета кнопки «+» и «-» не нужны: функциональной роли они не играют, а общий вид документа портят. Чтобы увидеть действие параметра «Показывать кнопки развертывания и свертывания», делаем так:1) открываем сводную таблицу, как на рис. 12;
2) в область «
названия строк» добавляем поле «Дата». В области «Значения» оставляем только поле «Сумма, грн». Теперь в сводном отчете появились символы группировки (рис. 14 на с. 38);
3) ставим активную ячейку внутрь сводного отчета;
4) через иконку «
Параметры» вызываем окно «Параметры сводной таблицы»;5) переходим на закладку «
Вывод», отключаем флажок «Показывать кнопки развертывания и свертывания». Символы группировки значений исчезли (рис. 14);Совет Снимите флажок «
Показывать кнопки развертывания и свертывания» перед распечаткой документа — его внешний вид от этого только выиграет.— флажок «Показывать контекстные всплывающие подсказки» работает только при работе с документом на экране. Чтобы посмотреть на действие этого параметра, сделайте так:
1) откройте таблицу, как на рис. 12;
2) наведите указатель мыши на любое поле внутри сводной таблицы. Через некоторое время появится всплывающее окно с описанием этого поля;
3) откройте окно «Параметры сводной таблицы» на закладке «Вывод».
4) отключите флажок «Показывать контекстные всплывающие подсказки»;
5) снова поставьте указатель мыши на любое поле внутри сводной таблицы. Всплывающая подсказка не появится.
Дополнительный параметр «Показывать свойства во всплывающих подсказках» включает или отключает отображение подробной информации о поле, на котором находится указатель мыши;
— флажок «Показывать названия полей и списки фильтров» влияет на заголовки сводной таблицы. Его работу покажем на таком примере:
1) откройте сводную таблицу (например, как на рис. 14);
2) войдите в окно «Параметры сводной таблицы»;
3) переключитесь на закладку «Вывод»;
4) отключите флажок «Показывать названия полей и списки фильтров». Отчет примет форму, как на рис. 15.
Этот параметр на практике применяют нечасто. Он может быть полезен, когда используются длинные имена полей, из-за чего шапка таблицы становится ненаглядной;
— флажок «
Классический макет сводной таблицы» несет сугубо эргономическую функцию. Включив этот флажок, появится возможность перетаскивать поля в отчет (или из области отчета) сводной таблицы.А вот параметр «
Показывать элементы без данных в строках» для бухгалтера может оказаться весьма полезным. Его действие таково. Если включить этот флажок, то сводную таблицу можно получить не заполняя область данных. Если флажок «Показывать элементы без данных в строках» отключить, то без поля в области данных сводную таблицу построить нельзя. Флажок «Показывать элементы без данных в столбцах» работает аналогично, но его действие распространяется на колонки сводной таблицы.Совет Используйте параметр «
Показывать элементы без данных в строках» для того, чтобы получить полный список значений ключевого поля из базы данных.Пример.
Есть база данных, как на рис. 1. Объем файла большой, но меня интересует перечень товаров, которые содержатся в этой базе. Делаем так:1) на отдельном листе вставляем сводную таблицу;
2) вызываем окно «
Список полей сводной таблицы»;3) в область строк этого окна ставим поле «
НаимТов»;4) щелкаем на кнопке «
Параметры» (раздел меню по работе со сводными таблицами);5) щелкаем на кнопке «
Параметры» в группе иконок «Сводная таблица»;6) на закладке «
Вывод» включаем флажок «Показывать элементы без данных в строках». В отдельной колонке сводного отчета появится перечень товаров из базы данных;7) выделяем сводную таблицу;
8) копируем ее в буфер обмена («
Ctrl+C»);9) вызываем меню «
Главная»;10) щелкаем на значке списка иконки «
Вставить», из появившегося меню выбираем «Специальная вставка…»;11) в появившемся окне специальной вставки ставим переключатель «
Вставить» в положение «Значения»;12) нажимаем «
ОК». Список значений готов.В списке на рис. 16 нет полей в области данных, однако есть заголовок списка (колонка «
Покупатель») и перечень из четырех элементов. Иногда этот перечень нужно скрыть, оставив только заголовки сводного отчета. Для этого достаточно включить флажок «Показывать подписи элементов при отсутствии полей в области значений». 
Последним параметром на закладке «
Вывод» является переключатель «Сортировка списка полей». Он может быть установлен в одно из двух положений: «от А до Я» — сортировать по алфавиту или «как в источнике данных» — сохранить порядок следования записей как в исходной базе. Такое значение принято по умолчанию. Изменять его, как правило, не нужно.
Закладка «Печать»
Как бы там ни было, а печатная форма документа еще долго будет основной в работе бухгалтера. Поэтому параметры печати сводного отчета разработчики
Excel 2007 вынесли на отдельную закладку (рис. 17). Она так и называется — «Печать». На закладке всего три элемента: 
— параметр «
Печатать кнопки развертывания и свертывания, если они отображаются в сводной таблице» работает в том случае, когда в сводном отчете есть кнопки развертывания и свертывания групп элементов (значки со знаком «+» или «-»). Разумеется, что на печатной копии такие элементы обычно не нужны. Отключите параметр «Печатать кнопки развертывания и свертывания, если они отображаются в сводной таблице», и эти элементы печататься не будут;— флажок «
Повторять названия строк на каждой странице печати» — очень важный параметр. Читать таблицу без заголовков неудобно. Включите этот флажок, и на каждой странице Excel повторит заголовки строк сводного отчета;— параметр «
Печатать заголовки» позволяет включить или отключить повторение заголовков полей строк, столбцов и подписей элементов строк и столбцов на каждой странице печатного отчета.Важно! Для печати заголовков нужно также настроить параметры печати документа Excel. Для этого откройте раздел «
Разметка страницы» главного меню, затем щелкните на пункте «Параметры страницы», в открывшемся окне перейдите на закладку «Лист» и укажите значения в полях «сквозные строки» и (или) «сквозные столбцы».
Закладка «Данные»
Параметры этой закладки (рис. 18 на с. 40) влияют на способ хранения данных с обновлением сводного отчета:
— флажок «
Сохранять исходные данные вместе с файлом» при включенном состоянии обеспечивает хранение данных из внешнего источника прямо внутри рабочей книги. Это увеличивает объем файла Excel, зато гарантирует быстрое обновление данных сводного отчета;— флажок «
Разрешить отображение деталей» включает или отключает возможность получить подробный отчет по элементам в области данных сводной таблицы. Работу этого параметра проще показать на примере:1) откройте сводную таблицу, как на рис. 19 (см. с. 40);
2) сделайте активной ячейку «
С5» (объем реализации на сумму 236,20 грн. для ЧП «Лапина» за «03/01/2009»);3) откройте окно «
Параметры сводной таблицы»;4) перейдите на закладку «
Данные»;5) включите флажок «
Разрешить отображение деталей»;6) в окне «
Параметры сводной таблицы» нажмите «ОК»;7) сделайте двойной щелчок мышью на ячейке «
С5». Excel создаст новый рабочий лист и перенесет в него те записи исходной базы данных, которые связаны с выбранным элементом отчета (рис. 19).Если флажок «
Разрешить отображение деталей» отключить, после двойного щелчка в области данных Excel сообщит о невозможности корректировать сводную таблицу;Совет Работая со сводным отчетом, оставляйте включенным флажок «
Разрешить отображение деталей». Возможность быстро раскрыть итоговое значение, увидеть его связь с исходными данными для бухгалтера порой вещь незаменимая.— параметр «Обновление при открытии файла» имеет смысл всегда оставлять включенным. В этом случае каждый раз после открытия файла вы можете быть уверены, что числа в сводном отчете будут заново пересчитаны, т. е. они будут актуальны;
Важно! Параметр «
Обновление при открытии файла» действует индивидуально для каждой сводной таблицы.— параметр «Число элементов, сохраняемых для каждого поля» я бы отнес к категории наиболее значимых. В зависимости от его установки сводный отчет может работать с различным количеством уникальных значений ключевого поля.
Совет Если вы работаете с большой таблицей, где много разных значений ключевого поля, установите параметр «
Число элементов, сохраняемых для каждого поля» в состояние «Максимум». В этом случае в сводном отчете Excel 2007 сможет хранить до 1 048 576 различных значений ключа.На этом мы завершим работу с параметрами сводных таблиц. А в следующий раз поработаем с Конструктором сводных таблиц и познакомимся с настройкой источников данных для сводных отчетов.
Жду ваших писем, предложений и замечаний на
bk@id.factor.ua, nictomkar@rambler.ru иили на форуме редакции www.bk.factor.ua/forum . Успешной работы!
