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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Комментарии: 30 комментариев

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

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

Теперь наконец-то нашла ответ на свой вопрос

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

Спасибо! Долго не мог поверить что такой возможности нет в стандарте.

Спасибо, товарищ!
Очень помогли!

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

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

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

Спасибо, огромное преогромное!

Как в данной диаграмме можно вывести подписи продаж по регионам в процентах

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

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

Читать еще:  Как сделать фон рисунка прозрачным в excel?

Спасибо за ответ!
Я хотела иметь в сумме абсолютные значения, а в подписях доли (проценты)

Эх, если бы это работало для сводных диаграмм…

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

Спасибо. Очень просто и доступно.

Спасибо, очень пригодилось

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

Большое спасибо! Очень долго пришлось самой возиться, как слепому котенку, чтобы получить более менее подходящий результат и вид диаграммы. Ваше объяснение очень простое и наглядное! (сделала ошибку в e-mail)

Подскажите пожалуйста! Если вообще возможно, уже всю голову сломала. Как построить нормированную гистограмму чтобы на ней отражались такие параметры: по оси икс точки (1,2,3,4,…), по оси игрек масса (10,20,30,….), на самой гистограмме чтобы для каждой точки рассматривалось 2 года (2000, 2001) и в каждом году масса разных объектов, в легенде разным цветом указаны именно объекты и чтобы именно нормированной была гистограмма…

Если я правильно понял вопрос, то предлагаю следующее решение:

См. также Excel-файл

Спасибо большое! То что надо! А как вставить подписи данных итоговых значений в линейчатой гистограмме с накоплением

Спасибо огромное за помощь! Просто и доступно.

А можно ли такое сделать для горизонтальной линейчатой диаграммы, а не для вертикальной? У меня пока не получилось.

Уже получилось, но пришлось размещать и выравнивать подписи вручную. Не очень удобно, но результат есть.

у меня года выходить на диаграмме как легенда. а как поменять местами

Метод не работает для объемной гистограммы в эксель 2007.
При попытке изменить тип диаграммы для ряда и выборе графика выдает сообщение что нельзя совмещать двухмерные и трехмерные типы диаграмм(

Читать еще:  Как сделать из текста дату excel?

Спасибо за материал!
За 1 мин в инете нашла эту статью, именно это мне и надо было!

подcкажите,пожалуйcта,как поcтупать в cлучае c отрицательными значениями?
поcтроила гиcтограмму c накоплением на оcнове таблицы c формулами (то еcть гиcтрограмма поcтоянно обновляетcя),
но отрицательные величины ображаютcя на минуcовой оcи (меньше 0 или внизу).
как отразить их вмеcте c положительными значениями (на оcи больше 0) без потери минуcа?

Альтернативы для гистограмм с накоплением – находим упущенные тренды

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

Плохие диаграммы

На рисунке выше показана гистограмма с накоплением. Я считаю её плохой диаграммой, поскольку по ней совершенно невозможно проследить тренд. На диаграмме показаны пять рядов данных и их общая сумма. Но на деле мы можем видеть тренды только по двум показателям из шести! Мы видим тренд по ряду данных North America и по общей сумме (высота всего столбца), т.к. их базисная линия ровная. Фактически, их базисной линией является ось Х со значением $0. Этот момент очень важен при создании графика, который должен показывать тенденцию развития (тренд). Наш глаз улавливает базисную линию, как начало каждого столбца, и затем сравнивает верхнюю точку каждого столбца, передвигаясь слева направо. Такой подход отлично работает для ряда данных North America и для общей суммы, но определить тренды для остальных четырёх регионов очень трудно.

Неровная базисная линия

Рисунок выше показывает, насколько трудно быстро увидеть вектор продаж в Азии (ряд Asia), поскольку базисная линия не ровная. Я подчёркиваю – быстро, т.к. наша основная цель, чтобы диаграмма легко и понятно передавала информацию и показывала тренд. Гораздо проще взглянуть на имеющиеся числа, чем пытаться по этой диаграмме понять увеличиваются или уменьшаются нужные столбцы в разные периоды времени. А если одновременно рассматривать и все прочие регионы, то столбцы ряда Asia вовсе теряются среди столбцов, расположившихся выше и ниже. Полный бардак!

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

Когда использовать диаграммы с накоплением

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

Читать еще:  Как сделать разметку страницы в excel 2016?

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

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

Вариант №1: Панель диаграмм

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

Здесь мы видим тренды по каждому региону. Тренды, которые мы не видели на диаграмме с накоплением из-за неровных базисных линий и масштаба графика. Продажи в Северной Америке (North America) и в Европе (Europe) значительно выше, чем в Азии (Asia) и в Африке (Africa), поэтому эти два тренда теряются на фоне первых двух.

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

Вариант №2: Динамическая базисная линия в гистограмме или линейчатой диаграмме с накоплением

Хорошее решение для тех случаев, когда нужно сохранить диаграмму с накоплением, или, когда не желательно отпугивать аудиторию чем-то, коренным образом, отличающимся от привычных диаграмм. Я называю это диаграммой с накоплением с динамической базисной линией, т.к. пользователь может при помощи выпадающего списка быстро выбрать, какой ряд данных поместить внизу графика. Создать такую диаграмму очень просто. Не потребуется ни работа с макросами, ни знание VBA. Всего лишь две формулы и элемент управления «Поле со списком».

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

При желании можно также увидеть взаимосвязь между рядами данных South и West во второй половине года, если переместить ряд East вверх столбца. Одним словом, данные на диаграмме нужно расположить таким образом, чтобы донести максимум информации до читателя.

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

Ссылка на основную публикацию
Adblock
detector