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

Самым простым способом настройки оформления сводной таблицы является применение стилей сводной таблицы. Галерея стилей сводной таблицы занимает отдельную группу на вкладке Конструктора сводных таблиц. Выбор, создание, редактирование и удаление стилей сводных таблиц производится так же, как и стилей оформленных таблиц (см. Лабораторную работу №10).

Флажки в группе Параметры стилей сводной таблицы левее позволяют указать, какие из элементов стиля сводной таблицы следует применять к настраиваемой таблице, а какие – нет.

Помимо использования стилей, ячейки сводных таблиц можно форматировать как и любые другие – с использованием окна «Формат ячеек» или инструментов Главной вкладки ленты инструментов. Следует помнить: при изменении макета сводной таблицы некоторые изменения форматирования, внесенные вручную, могут быть утеряны. Также в сводную таблицу Microsoft Excel невозможность вставлять (и удалять из нее) ячейки, строки или столбцы. Также невозможно редактировать содержимое итоговых ячеек.

Инструменты на вкладке Параметры сводной таблицы позволяют настраивать отдельные функции сводной таблицы.

Поле ввода в группе Сводная таблица позволяет присвоить сводной таблице имя (полезно при программировании на языке VBA). Кнопка « Параметры» открывает диалоговое окно свойств сводной таблицы (в пособии не рассматривается).

Команда Создать GetPivotData из выпадающего списка этой же кнопки включает или выключает встроенную функцию «ПОЛУЧИТЬ.ДАН­НЫЕ.СВОДНОЙ.ТАБЛИЦЫ». Аргументами данной функции являются названия и значения полей сводной таблицы, позволяющие извлечь из нее содержимое конкретной ячейки (подобно функциям ВПР и ГПР в простых диапазонах ячеек). В случае, если где-то на листе создается формула со ссылкой на ячейку сводной таблицы, включенная команда Создать GetPivotData строит ссылку на основе этой функции. Если команда выключена, в формулу вставляется обычная относительная ссылка. При изменении фильтров сводной таблицы ее размер изменится, и ссылка будет указывать уже на другую ячейку. Функция же «ПОЛУЧИТЬ.ДАН­НЫЕ.СВОДНОЙ.ТАБЛИ­ЦЫ» будет всегда указывать на нужную ячейку независимо от ее положения в таблице.

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

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

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

При группировке происходит создание дополнительного поля. Например, если было выделено несколько строк поля «Дата», для группы этих строк будет создано поле «Дата2», а сама группа получит название «Группа1». Для удобства и саму группу, и созданное поле можно переименовать (группу – непосредственно в ячейке сводной таблицы, поле – в группе Активное поле вкладки Параметры сводной таблицы).

Кнопка « Группировка по полю» позволяет автоматически создать группы на основании значений поля. Поддерживаются поля с числовыми значениями или датами. Для числовых полей будет отображено окно рис. 14.24, верхнее, а для полей с датами – рис. 14.24, нижнее. В окне можно задать минимальное и максимальное значение, а также шаг (для дат – еще и выбрать единицу измерения шага). Например, если в качестве начального значения выбрать 10, в качестве конечного – 30, а шаг задать равным 5, будут созданы группы «<10», «10-15», «15-20», «20-25», «25-30» и «>30». При группировке по полю значки «» и «» не появляются, и развернуть созданные таким образом группы невозможно!

Кнопка « Разгруппировать» позволяет удалить как пользовательскую группировку, так и группировку по полю.

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

Если курсором выделена ячейка в области заголовков строк, кнопки  и  сортируют строки таблицы по возрастанию или убыванию их заголовков. Если выделены ячейки в области заголовков столбцов – будут отсортированы столбцы сводной таблицы по возрастанию или убыванию их заголовков. Если выделены ячейки в области данных, Microsoft Excel отсортирует строки своднойтаблицы по возрастанию или убыванию содержимого столбца, ячейки которого были выделены. Наконец, если в таблице выделены столбцы или строки с итогами, кнопки  и  отсортируют таблицу по возрастанию или убыванию значений этих итогов.

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

Кнопка « Источник данных» в группе Данные для сводной таблицы на основе диапазона ячеек Microsoft Excel открывает окно, аналогичное верхней части окна рис. 14.19, где можно отредактировать диапазон ячеек, по которому строится сводная таблица (в случае сводной таблицы на основе внешних данных вид у окна будет другим). Кнопка « Обновить» служит для обновления сводной таблицы, построенной на основе внешних источников данных (в пособии не рассматривается).

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

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

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

Если выделены ячейки в области данных сводной таблицы, кнопка « Итоги по» в группе Вычисления вкладки Параметры сводной таблицы позволяет выбрать тип итоговой функции (это не обязательно должно быть суммирование). Кнопка « Дополнительные вычисления» содержит ряд других полезных функций: % от суммы в строке или общей суммы, отличие от значения в какой-либо другой ячейке и т.д.

В данной работе перечислены далеко не все возможности по работе со сводными отчетами в Microsoft Excel. при желании студентам следует изучить дополнительные способы и приемы работы самостоятельно.