Сводные таблицы Excel - отчеты

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

Поскольку отчет сводной таблицы является интерактивным, вы можете быстро внести необходимые изменения, чтобы выделить конкретные результаты, такие как тенденции данных, сводные данные и т. Д., Во время его представления. Вы также можете предоставить получателям визуальные подсказки, такие как фильтры отчетов, срезы, временная шкала, сводные диаграммы и т. Д., Чтобы они могли визуализировать нужные детали.

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

Иерархии

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

Если у вас есть встроенная структура для полей в ваших данных, например, Год-Квартал-Месяц, вложение полей для формирования иерархии позволит вам быстро развернуть / свернуть поля для просмотра сводных значений на требуемом уровне.

Например, предположим, что у вас есть данные о продажах за 2015–16 финансовый год для регионов - Востока, Севера, Юга и Запада, как показано ниже.

Создайте сводную таблицу, как показано ниже.

Как видите, это комплексный способ представления данных с использованием вложенных полей в виде иерархии. Если вы хотите отображать результаты только на уровне кварталов, вы можете быстро свернуть поле «Квартал».

Предположим, у вас есть поле даты в ваших данных, как показано ниже.

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

Создайте сводную таблицу.

Как видите, в этой сводной таблице неудобно выделять важные данные.

Сгруппируйте сводную таблицу по полю даты. (Вы узнали о группировке в главе - Исследование данных с помощью инструментов сводных таблиц в этом руководстве).
Поместите поле «Продавец» в область «Фильтры».
Отфильтруйте метки столбца по Восточному региону.

Фильтр отчетов

Предположим, вам нужен отчет по каждому продавцу отдельно. Вы можете сделать это следующим образом -

Убедитесь, что у вас есть поле «Продавец» в области «Фильтры».
Щелкните сводную таблицу.
Щелкните вкладку АНАЛИЗ на ленте.
Щелкните стрелку рядом с параметром в группе сводной таблицы.
В раскрывающемся списке выберите Показать страницы фильтра отчета.

В Show Report Filter Pagesпоявится диалоговое окно. Выберите поле Продавец и нажмите ОК.

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

Рабочий лист будет назван по значению поля, которое отображается на вкладке рабочего листа.

Слайсеры

Еще одна сложная функция сводных таблиц - это слайсер, который можно использовать для визуальной фильтрации полей.

Щелкните сводную таблицу.
Щелкните вкладку АНАЛИЗ.
Нажмите «Вставить слайсер» в группе «Фильтр».
Нажмите Order Date, Quarters and Yearsв диалоговом окне "Вставить срезы". Будут созданы три слайсера - дата заказа, кварталы и год.
Отрегулируйте размеры срезов, добавив больше столбцов для кнопок на срезах.
Также создайте срезы для полей «Продавец» и «Регион».
Выберите стили слайсера, чтобы поля даты были сгруппированы по одному цвету, а два других поля имели разные цвета.
Снимите флажок Линии сетки.

Как видите, у вас есть не только интерактивный отчет, но и интересный, понятный.

Временная шкала в сводной таблице

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

Создайте сводную таблицу с продавцом в области ROWS и регионом в области COLUMNS.
Вставьте временную шкалу для поля Дата заказа.
Отфильтруйте временную шкалу, чтобы отобразить данные за 5 месяцев, с ноября 2015 года по март 2016 года.

ДИЗАЙН Команды

В PIVOTTABLE TOOLS - DESIGN Команды на ленте предоставляют вам параметры для форматирования сводной таблицы, включая следующие:

Layout
Параметры стиля сводной таблицы
Стили сводной таблицы

Макет

Вы можете иметь макет сводной таблицы в соответствии с вашими предпочтениями для следующего:

Subtotals
Общие итоги
Макет отчета
Пустые строки

PivotTable Layout – Subtotals

У вас есть возможность отображать Subtotalsили нет. По умолчанию промежуточные итоги отображаются в верхней части группы.

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

Щелкните сводную таблицу.
Щелкните вкладку ДИЗАЙН на ленте.
Щелкните Промежуточные итоги в группе Параметры макета.
Щелкните Показать все промежуточные итоги внизу группы.

Теперь промежуточные итоги появятся внизу каждой группы.

Если вам не нужно сообщать промежуточные итоги, вы можете выбрать - Не показывать промежуточные итоги.

Общие итоги

Вы можете выбрать отображение общих итогов или нет. У вас есть четыре возможных комбинации -

Отключено для строк и столбцов
Включено для строк и столбцов
Включено только для строк
Включено только для столбцов

По умолчанию это вторая комбинация - Вкл для строк и столбцов.

Макет отчета

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

Компактная форма.
Наброски Форма.
Табличная форма.

Вы также можете выбрать, повторять ли все метки элементов или нет, если они повторяются несколько раз.

Макет отчета по умолчанию - это компактная форма, с которой вы знакомы.

Compact Form

Компактная форма оптимизирует сводную таблицу для удобства чтения. В двух других формах также отображаются заголовки полей.

Нажмите на Show в контурной форме.

Нажмите Show в табличной форме.

Рассмотрим следующий макет сводной таблицы, в котором поле «Месяц» вложено в поле «Регион» -

Как видите, метки месяца повторяются, и это значение по умолчанию.

Щелкните "Не повторять метки элементов". Метки месяца будут отображаться только один раз, а сводная таблица выглядит ясной.

Пустые строки

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

Нажмите Insert Blank Line after Each Item.

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

У вас есть следующие параметры стиля сводной таблицы -

Заголовки строк
Заголовки столбцов
Полосатые ряды
Чередующиеся столбцы

По умолчанию установлены флажки для заголовков строк и столбцов. Эти параметры предназначены для отображения специального форматирования для первой строки и первого столбца соответственно. Установите флажокBanded Rows.

Установите флажок Чередующиеся столбцы.

Стили сводной таблицы

Вы можете выбрать несколько стилей сводной таблицы. Выберите тот, который подходит для вашего отчета. Например, если вы выберете Pivot Style Dark 5, вы получите следующий стиль для сводной таблицы.

Условное форматирование в сводной таблице

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

Сводные диаграммы

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