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

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

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

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

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

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

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

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

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

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

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

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

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

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

Получилось так:

или так:


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

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

  1. Наталья

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

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

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

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

  3. Валерий

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

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

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

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

  6. Добрый день!

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

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

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

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

  7. Анжелика

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

  8. Татьяна

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

  9. Татьяна

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

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

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

  12. Бахтияр

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

    1. Бахтияр

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

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

  14. Татьяна

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

  15. Валерия

    Добрый день!

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

Добавить комментарий

Ваш адрес email не будет опубликован.