Секционные диаграммы

Простая секционная диаграмма

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

Исходные данные

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

Обычная гистограмма

Результат, как легко сообразить, не блещет наглядностью. Города сливаются в единое целое, сильно затрудняя восприятие и сравнение стран между собой. Как же улучшить картину?

Для начала исходную таблицу лучше немного преобразовать:

Преобразованная таблица

Что изменилось:

  • Названия стран из колонки А копируются в заголовки столбцов
  • Данные из одного общего столбца разносятся по разным - каждая страна теперь отдельно

Вроде бы, мелочи, но если теперь выделить всю таблицу (A1:G20) и построить гистограмму с накоплением на вкладке Вставка - Гистограмма (Insert - Stacked Chart), то картина на выходе получается совсем другая:

Секционная диаграмма

Столбцы-города каждой страны теперь являются для диаграммы разными рядами данных и отображаются, соответственно, разными цветами. А выделение стран вместе с городами дает нам красивые сгруппированные двухуровневые подписи к оси X.

Диаграмма с горизонтально расположенными столбцами (Линейчатая или Bar Chart) тоже в таком варианте смотрится приятно:

Линейчатая секционная диаграмма

Аналогичным образом можно легко визуализировать и динамику, например, по годам для разных товаров:

Динамика по годам на секционной диаграмме

Сводная секционная диаграмма

Если ваша исходная таблица содержит не финальные итоговые цифры, а отдельные сделки и больше похожа на такую:

Исх.данные для сводной

... то нужно будет сначала построить сводную таблицу аналогичного вида, а уж потом по ней строить диаграмму.

Пойдем на небольшую хитрость: продублируем столбец со странами и дадим ему имя, например, Страна2 (т.к. Excel не позволяет использовать одинаковые имена столбцов при построении отчетов сводных таблиц, чтобы отличать столбцы друг от друга):

Дублируем столбец

Теперь построим по нашим данным сводную таблицу на вкладке Вставка - Сводная таблица (Insert - Pivot Table):

  • Страну и Город закинем в область строк (Row Labels)
  • Страну2 в область столбцов (Column Labels)
  • Продажи - в область значений (Values)

Как легко догадаться, на выходе мы получаем тот же необходимый нам вид таблицы со "ступеньками" данных по каждой стране. Останется перейти на вкладку Параметры (Options) или Анализ (Analyze) и построить гистограмму с накоплением, аналогичную описанным выше:

Секционная сводная таблица и диаграмма

Ссылки по теме




22.08.2017 11:48:43
во второй колонке не должно быть цифр ( ну например, номера магазинов) или же они должны быть сохранены как тест, иначе не получится диаграмма
24.09.2017 16:04:28
Тогда надо будет руками подправить адреса диапазонов данных и подписей (правой кнопкой мыши по диаграмме - Изменить данные).
18.09.2017 16:18:45
Николай, подскажите, пожалуйста, как выделить сразу все столбцы в смежной диаграмме?
24.09.2017 16:03:29
Боюсь, что никак - это ее недостаток, к сожалению.
10.10.2018 12:12:27
Николай, добрый день!
Есть следующая таблица (верхние ячейки с видами топлива изначально объединены, в чате, к сожалению, не поддерживается такой формат, пришлось повторить в каждой ячейке):
Аи92Аи92Аи92Аи95Аи95Аи95
Город2017 год2018 год2019 год2017 год2018 год2019 год
Владимир3,473,744,081,871,911,89
Иваново3,633,864,291,861,822,41
Калуга3,83,934,361,111,471,58
Кострома3,193,223,411,992,242,24
Понятно, что видов ресурсов намного больше, как собственно и городов, это лишь 1/1000 часть таблицы.
Нужно построить гистограмму с накоплением видов топлива, сгруппированную по городам и годам, т.е. на каждый город по три накопительных элемента ресурсов на каждый год.
Есть варианты трансформировать таблицу штатными средствами, или только вручную менять её структуру, после чего строить диаграмму?
Вручную довольно затратно по времени.
В данном виде таблицу не принимает сводная таблица.
Если коротко, то как из верхней таблицы сделать вот это штатными средствами Excel?:
ГородГодАи92Аи95
Владимир2017 год3,471,87
2018 год3,781,91
2019 год4,082,2
Иваново2017 год3,631,86
2018 год3,741,82
2019 год3,92,82
Калуга2017 год3,81,11
2018 год3,821,47
2019 год41,8
Кострома2017 год3,191,99
2018 год3,382,24
2019 год3,412,7
16.11.2018 10:20:22
Очень помогла статья. Отличный материал! Спасибо.
Наверх