Как построить гистограмму с накоплением в excel по данным таблицы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Как построить гистограмму с переменной шириной столбцов
• Что такое диаграмма-торнадо и как ее построить в Excel
• Несколько вариантов построения диаграмм «план-факт»

Гистограмма в EXCEL с накоплением

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

Примечание : Для начинающих пользователей советуем прочитать статью Основы построения диаграмм в MS EXCEL , в которой рассказывается о базовых настройках диаграмм. О других типах диаграмм можно прочитать в статье Основные типы диаграмм в MS EXCEL .

Задача №1. Обычная гистограмма с накоплением

Создадим обычную гистограмму с накоплением.

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

Решение

Создадим исходную таблицу: объемы продаж 2-х сотрудников по месяцам (см. файл примера ).

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

В итоге получим:

Добавьте, если необходимо, подписи данных и название диаграммы .

Как видно из рисунка выше, в третьем столбце (март) значение у второго сотрудника равно 0, которое отображается на диаграмме (красный столбец отсутствует, но значение 0 отображается).

Чтобы не отображать этот нуль его можно удалить вручную из диаграммы, кликнув по нему два раза (интервал между кликами должен быть порядка 1 сек). Но, при изменении исходных данных есть риск, что значение у второго сотрудника изменится, но отображаться уже не будет.

Чтобы 0 не отображался, используем тот факт, что значение ошибки #Н/Д (нет данных) , содержащееся в исходной таблице, не будет отображаться на диаграмме (если в ячейке таблицы содержится текстовое значение или любое другое значение ошибки кроме #Н/Д (например, #ДЕЛ/0!, #ЗНАЧ!, #ИМЯ! и др.), то оно будут интерпретировано как 0, который отобразится на диаграмме!).

Для того, чтобы заменить значение 0 на #Н/Д создадим специальную таблицу для гистограммы (см. файл примера ), значения которой будут браться из исходной таблицы с помощью незамысловатой формулы =ЕСЛИ(НЕ(B8);НД();B8)

Теперь ноль не отображается.

СОВЕТ : Для начинающих пользователей EXCEL советуем прочитать статью Основы построения диаграмм в MS EXCEL , в которой рассказывается о базовых настройках диаграмм, а также статью об основных типах диаграмм .

Задача №2. Продвинутая гистограмма с накоплением

Теперь создадим гистограмму с отображением начального, каждого последующего изменения и итогового значения. Причем положительные и отрицательные изменения будем отображать разными цветами. Эту диаграмму можно использовать для визуализации произошедших изменений, например отклонений от бюджета (также см. статью Диаграмма Водопад в MS EXCEL ).

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

Красные столбцы отображают увеличение бюджета за счет новых работ, а зеленые — уменьшение за счет экономии или отмены работ.

Решение

Создадим исходную таблицу (см. Лист с Изменением в Файле примера ). Отдельно введем плановую сумму бюджета и изменения по месяцам.

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

Первую строку (плановый бюджет) заполним плановой суммой, в столбцах Увеличение и Уменьшение введем значение #Н/Д используя формулу =НД() .

Нижеследующие строки в столбце Служебный (синий столбец на диаграмме) будем заполнять с помощью формулы =E8+ЕСЛИОШИБКА(F8;0)-ЕСЛИОШИБКА(G9;0)

В случае увеличения бюджета эта формула будет возвращать бюджет до увеличения, а в случае уменьшения бюджета — бюджет после уменьшения.

Формулы в столбцах Увеличение и Уменьшение практически идентичны =ЕСЛИ(B10>0;B10;НД()) и =ЕСЛИ(B10 Основная их задача — отображать значение #Н/Д в столбце Увеличение , когда увеличения не происходит, и в столбце Уменьшение , когда не происходит уменьшения.

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

В случае только увеличения бюджета можно получить вот такую гистограмму (см. Лист с Увеличением в Файле примера ).

Для представления процентных изменений использован дополнительный ряд и Числовой пользовательский формат .

Как построить гистограмму с накоплением в excel по данным таблицы

Многие сталкивались с проблемой, когда внешний вид диаграммы казался простым для построения, но оказывалось на деле это не так. Гистограмма (или Линейчатая) с накоплением — казалось бы что может быть проще? Однако, подписать итоговые значения на раз-два удается далеко не каждому, т.к. стандартно в программе это пока (может, будет когда-нибудь) не предусмотрено. Здесь есть маленький секрет!

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

По данным таблицы строиться Гистограмма с накоплением:

• Выделить всю таблицу с исходными данными.
• На вкладке Вставка [Insert] в группе Диаграммы [Charts] выбрать Вставить гистограмму [Insert Column Chart] и тип Гистограмма с накоплением [Stacked Column].
• На вкладке Конструктор [Design] в группе Данные [Data] воспользоваться командой Строка/Столбец [Switch Row/Column].

Осталось лишь настроить ВНЕШНИЙ ВИД ДИАГРАММЫ:

• Изменить тип диаграммы для ряда Итого – щелкнуть правой кнопкой мыши по ряду и выбрать Изменить тип диаграммы для ряда [Change Series Chart Type], сменить на тип диаграммы на График — График [Line – Line].
• Выделить ряд Итого и задать цвет линии Нет контура [No Outline]
• Добавить подписи данных – выделить диаграмму, нажать кнопку Элементы диаграммы [Chart Elements] и выбрать Подписи данных [Data Labels], затем В центре[Center].
• Изменить расположение подписей данных для ряда Итого – щелкнуть правой кнопкой мыши по подписи данных и выбрать Формат подписей данных [Format Data Labels]. В Параметрах подписи [Label options] в группе Положение метки [Label Position] задать Сверху[Top].
• Удалить из легенды Итого – выделить в легенде Итого и нажать Delete.

Оставьте комментарий!

На сообщение “Гистограмма с накоплением. Подписываем итоговое значение.” комментариев 17

1. Люба :
   29.03.2015 (15:35) Как всё просто, если сделать как написано. Спасибо огромнейшее за то, что поделились!
2. Нортон :
   30.03.2015 (10:28) Не получается, пишет, что невозможно изменить тип диаграммы ��
3. Ольга Кулешова :
   31.03.2015 (01:33) Нортон, присмотритесь, есть подозрение, что вы используете объемную гистограмму с накоплением, НО нужно выбрать изначально плоскую
4. Катя :
   03.04.2015 (21:23) Я тоже сперва хотела сделать красивее в объеме, но увидела, что надо плоское. Спасибо!
5. Алексей :
   20.04.2015 (15:25) Как у Вас всё просто и быстро получается, но теперь и у меня, благодаря ВАМ!
6. Эльвира :
   25.04.2015 (18:23) Спасибо, взяла себе на вооружение ��
7. Валентина :
   27.05.2015 (16:15) Какое простое и изящное решение.
8. Пётр :
   13.06.2015 (15:59) Спасибо, мне очень пригодится в работе.
9. Олег :
   25.08.2015 (15:25) Прекрасное дополнение к существующим вариантам диаграмм.
10. Ярослав :
    02.09.2015 (11:36) Изящное решение!Браво!
11. Марина :
    15.09.2015 (17:47) Обожаю наглядные диаграммы
12. Pingvinka :
    30.09.2015 (19:12) Диаграммы — одна из важных составляющих отчетов. Я раньше подобное строила ужасно, вспоминаю с содрогпним: сперва гистограмму, а потом цифры через вставку текста
13. НаталиКо :
    01.10.2015 (13:53) Спасибочки! Очень люблю строить диаграммы и особенно когда всё просто!
14. Тамара :
    05.11.2015 (18:35) Можно сразу строить комбинированную диаграмму
15. Василий :
    27.11.2015 (19:56) Где же Вы были раньше… Я бы столько не мучился ��
16. Ольга Кулешова :
    10.12.2015 (20:42) Тамара, строить комбинированную можно только с 2013 версии, но много людей работает и в 2010 и в 2007 версиях!
17. Дмитрий :
    08.09.2016 (17:55) Огогошеньки! Где Вы были раньше? Неужели мои страдания с такими диаграммами завершатся? ��

Гистограмма с накоплением. Отображение итогов

Выбор того или иного вида диаграммы обусловлен в первую очередь идеей, которую вы хотите донести до аудитории. Если ваша задача сравнить некоторые сущности в определенные моменты времени, то лучший выбор – гистограмма. Например:

Скачать заметку в формате Word, примеры в формате Excel

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

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

Небольшой трюк [1] позволяет отобразить на гистограмме с накоплением итоговые значения.

1. Добавьте в таблицу данных сумму по строкам и постройте гистограмму с накоплением на основе всех данных:

2. Выделите на диаграмме только итоговый ряд и правой кнопкой мыши выберите в меню «Изменить тип диаграммы для ряда»:

3. Выберите обычный график (без маркеров):

4. Выделите итоговый ряд (линию) и добавьте подписи данных

5. Выделите подписи данных и отформатируйте их

6. Измените расположение подписи на «Сверху»

7. Выделите нашу итоговую линию и сделайте ее невидимой:

8. Удалите строку «Итого» в легенде; для этого дважды щелкните на легенде, выделите строку «Итого», удалите ее.

[1] Идею трюка я позаимствовал в книге Холи «Excel 2007. Трюки»

36 комментариев для “Гистограмма с накоплением. Отображение итогов”

Baguzin, я Вам очень благодарна!! Целую неделю билась как рыба об лед, пока не обратилась за помощью к интернету. а тут — Вы, с таким подробным понятным пошаговым объяснением. Счастья Вам!

Спасибо огромное! Не поверите, постоянно использую такие диаграммы и самое «умное», что придумала — добавлять итоговые значения в WordArt. Но это трудоемко и редактировать вручную надо при изменении значений. Теперь наконец-то нашла ответ на свой вопрос

Приветствую тебя, О Baguzin.
Респект тебе и уважуха.
Столько времени теперь появится для других трюков ��
Очень полезно.
Благодарю.

Благодуха, сэнсэй)))
Спасибо! Долго не мог поверить что такой возможности нет в стандарте.
Спасибо, товарищ!
Очень помогли!

Спасибо! 2 часа искал в настройках, но так и не мог найти, пока не залез в интернет. Сложно поверить, что в 2010 этого нет �� .
Еще раз спасибо огромное )))

Спасибо, жаль только работает для двумерных диаграмм, может кто знает как сделать для трехмерных?
Спасибо, огромное преогромное!
Добрый день! Как в данной диаграмме можно вывести подписи продаж по регионам в процентах

Таня, если вам просто нужны проценты (при этом в сумме будет всегда 100%), то выберите другой тип диаграммы — нормированная гистограмма с накоплением. Если же вы хотите иметь в сумме абсолютные значения, а в подписях доли (проценты), задача усложняется.
1. Создайте на листе область с процентами (В10:Е16 на рис. ниже).
2. Перейдите в файле Excel к финальной диаграмме, представленной в настоящей заметке. Кликните правой кнопкой мыши на одном из столбцов диаграммы, и выберите опцию Добавить подписи данных.
3. Кликните правой кнопкой мыши на ряде с подписями данных (на рисунке ниже это ряд Регион2) и выберите опцию Формат подписей данных.
4. В окне Формат подписей данных поставьте галочку в опции Значения из ячеек.
5. В окне Диапазон меток данных выберите диапазон с процентами (в нашем примере D10:D16); кликните Ok.
6. Снимите галочку в окне Формат подписей данных напротив опции Значение.
7. Выполните пп. 5 и 6 для всех рядов подписей данных.

Чтобы увеличить картинку, кликните на ней правой кнопкой мыши и выберите опцию Открыть картинку в новой вкладке.
См. также Excel-файл с этим примером.