Настройка макета сводной таблицы

На базе одного и того же диапазона исходных данных можно построить сводные таблицы различной формы. Форма сводной таблицы определяется ее макетом, который настраивается после ее вставки при помощи специальной области задач (рис. 14.21). Одновременно с областью задач на лист добавляется заготовка сводной таблицы ­– несколько смежных наборов ячеек, обведенных толстой синей рамкой.

Если область задач не отображается, включить ее можно кнопкой « Список полей» в группе Показать вкладки Параметры сводной таблицы.

В области задач располагается область с полями (поскольку сводная таблица создается на базе списка, заголовки столбцов распознаются в качестве полей). Рядом перечислены области макета, доступные для назначения полей:

Ø Фильтр отчета – создается выпадающий список, который располагается над сводной таблицей и позволяет фильтровать сводную таблицу по любым значениям и комбинациям значений этого поля (полей);

Ø Строка – значения размещаются вдоль левого края сводной таблицы в качестве заголовков строк, имеется возможность скрывать определенные строки;

Ø Столбец – элементы размещаются вдоль верхнего края сводной таблицы в качестве заголовков столбцов, их также можно скрывать;

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

Компоновку области задач можно изменить при помощи выпадающего списка кнопки «».

Для настройки макета сводной таблицы нужно перетащить мышью поля из общего списка области задач в области «Строка», «Столбец», «Страница» или «Данные» там же (либо в соответствующие места заготовки сводной таблицы, помеченные надписью «Перетащите сюда поля нужного типа»). Поля, которые были перенесены в какую либо из областей, отмечаются в общем списке полей жирным цветом и установленным флажком. Снятие флажка убирает поле из списка, в который оно было вставлено.

В каждой из областей макета можно разместить одно или несколько полей. Наличие нескольких полей в области «Строка» и «Столбец» приведет к автоматической группировке столбцов или строк сводной таблицы (не следует добавлять сюда слишком много полей, иначе размер сводной таблицы в развернутом виде может оказаться слишком большим). Использование двух полей вместо одного доказано на рис. 14.22. За основу взята сводная таблица, показанная в примере на рис. 14.18.

Если добавить более одного поля в список «Значения», то на пересечении каждого столбца и строки таблицы будут отображаться несколько ячеек с данными. Эти ячейки могут быть расположены как вертикально (одна над другой, рис. 14.22, г), так и горизонтально (рис. 14.22, д). Способ задается при помощи автоматически добавляемого дополнительного поля «Значения», которое может быть расположено либо в области строк (тогда различные значения расположатся в разных строках, т.е. друг над другом), либо в области столбцов.

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

Наконец, команда «Параметры поля…» или «Параметры полей значений…» позволяет открыть диалоговое окно для тонкой настройки поведения поля при формировании сводной таблицы. Работа с этими диалоговыми окнами в работе рассматриваться не будет.

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

                      а)                                                                           б)

 

                     в)                                                                                   г)

                                                                          д)

Рис. 14.22 Использование двух полей в областях:
а – страницы, б – столбца; в – строки; г – данных («Значения» – в строках), д – данных («Значения» – в столбцах)

Ø Кнопка « Промежуточные итоги» позволяет управлять отображением промежуточных итогов в группах строк (группы строк образуются, если в область строк добавлено более одного поля). Пример таких групп строк показан на рис. 14.22, в. Можно отображать промежуточные итого сверху (в строке с названием группы), снизу в отдельной строке или вообще отключить отображение промежуточных итогов. Команда Включать отобранные фильтром элементы в итоги применяется только к сводным таблицам на основе внешнего источника данных, и поэтому неактивна.

Ø Кнопка « Общие итоги» позволяет включать и выключать отображение строки и столбца итогов (поз. 12 и 10 на рис. 14.18). Возможные варианты: не отображать ни строку, ни столбец, отображать только строку, только столбец или отображать одновременно и то, и другое;

Ø Кнопка « Макет отчета» позволяет выбрать одно из трех представлений сводной таблицы (рис. 14.23):

o Сжатое (рис. 14.23, а, применяется по умолчанию) – в случае групп строк (два и более полей в области строк) и название групп, и отдельные строки группы записываются друг над другом в один столбец; ширина других столбцов сводной таблицы также по возможности уменьшается. Поскольку в одном столбце в э том случае перечисляются данные из разных полей, Microsoft Ecxel предлагает для такого комбинированного столбца заголовок «Названия строк» (которое можно отредактировать);

o В представлении структуры (рис. 14.23, б) название группы строк и сами строки группы располагаются одновременно и в разных столбцах, и в разных строках. В строке с названием группы при этом по умолчанию отображается промежуточный итог группы (можно переключить кнопкой « Промежуточные итоги»). По умолчанию в этом и предыдущем представлении Microsoft Excel выключает отображение границ ячеек (скрывает сетку);

                 а)                                            б)                                   в)

Рис. 14.23 Различие макетов сводной таблицы

o В табличном представлении (рис. 14.23, в) название группы строк и сами строки группы также располагаются в разных столбцах, при этом первая строка группы располагается в той же строке, что и название группы. В связи с этим промежуточные итоги группы отображаются только внизу группы (даже если в меню кнопки « Промежуточные итоги» выбрано Показывать все промежуточные итоги в заголовке группы). В этом представлении отображение границ ячеек по умолчанию включено.

Также в выпадающем меню кнопки « Макет отчета» можно включить или выключить дублирование названия группы в каждой строке (актуально для представлений структуры и табличного; на рис. 14.23, б или в это значило бы, что дата будет продублирована левее названия каждого из магазинов).

Ø Кнопка « Пустые строки» позволяет включить или выключить добавление пустой строки после каждой группы строк в сводной таблице для визуального отделения групп строк друг от друга.

Также изменить макет сводной таблицы позволяют кнопки « Кнопки +/–» (отжатие кнопки позволяет скрыть значки «» и «» группировки строк) и « Заголовки полей» (отжатие кнопки скрывает заголовки строк и столбцов с кнопками , оставляя на их месте пустые ячейки) на вкладке Параметров сводной таблицы.

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

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

Двойной щелчок по ячейке с итоговыми данными в сводной таблице сгенерирует новый лист, где будет отображена развернутая таблица, отражающая, все исходные и промежуточные данные, формирующие итоговое значение в этой ячейке. Команда Отобразить страницы фильтра отчета… из выпадающего меню кнопки « Параметры» в левой части вкладки Параметры сводной таблицы генерирует в книге новые листы, каждый из которых содержит копию сводной таблицы для своего значения поля из области страниц. Если в этой области содержится несколько полей, будет отображено диалоговое окно для выбора нужного поля.