что такое график с накоплением в excel
Excel-plus
Пошаговые инструкции, как использовать функции, формулы и другие инструменты табличного редактора Microsoft Excel
Что такое График с накоплением в Excel. Описание и пример.
Что такое График с накоплением в Excel. Описание и пример.
В данной статье, на примере, рассмотрим такой вид диаграммы, как График с накоплением и в чем его отличие от обычного Графика.
Предположим у нас есть таблица, в которой отражены продажи офисных стульев за год, в штуках. Продажи осуществляли два магазина: Магазин № 1 и магазин № 2.
Это будет наша исходная таблица, на основе которой мы будем строить график с накоплением.
График с накоплением в Excel.
Перед построением графика нам необходимо выделить всю таблицу. Потом в закладке Вставка находим значок Вставить график или диаграмму с областями.
Выбираем нужный нам тип диаграммы — График с накоплением.
Нажимаем и получаем График с накоплением.
На графике отразились две кривые линии. Оранжевая кривая, это продажи Магазина №2. Синяя кривая, это продажи магазина №1. Для наглядности добавим Метки данных. Вызовем настройки диаграммы нажав на нее. В настройках выберем Элементы диаграммы. Поставим галочку напротив пункта Метки данных. На нашем графики отразиться количество проданных стульев соответственно месяцу года и магазину.
Вертикальная ось диаграммы показывает количество проданных стульев. При этом оранжевая кривая на графике, которая соответствует продажам Магазина №2, берет свое начало не на отметке 260 (шт.). Это количество проданных стульев данным магазинов в январе. Оранжевая кривая берет сове начало на отметке 510 (шт.). Это суммарное количество проданных стульев двумя магазинами в январе (250 шт. +.260 шт.). То есть, для оранжевой кривой, при определении координаты по вертикальной оси, начало отсчета происходит не от нулевой отметки, а от соответствующей (нижележащей) точки синей кривой.
Построим на основе исходной таблицы простой График.
Как видно, кривые продаж Магазина №1 и Магазина №2 наложились одна на одну. Проводить анализ или презентацию используя такой график не целесообразно. График с накоплением позволяет избежать данной проблемы.
Excel-plus
Пошаговые инструкции, как использовать функции, формулы и другие инструменты табличного редактора Microsoft Excel
Диаграммы MS Excel. Гистограмма, линейчатая диаграмма. Виды этих диаграмм, описание и примеры.
Диаграммы MS Excel. Гистограмма, линейчатая диаграмма. Виды этих диаграмм, описание и примеры.
В данной статье рассмотрим следующие виды диаграмм: гистограмма, гистограмма с группировкой, объёмная гистограмма, линейчатая, линейчатая с группировкой и объемная линейчатая диаграмма.
Предположим у нас есть таблица в которой указаны виды фруктов. Указана их масса в килограммах и процент массы каждого вида фруктов от общей массы всех фруктов. Это таблица с исходными данными.
Гистограмма, гистограмма с группировкой и объемная гистограмма.
Гистограмма. Диаграмма Excel.
Построим на основе таблицы с фруктами гистограмму. Сравним количество фруктов в килограммах. В начале выделим интересующую нас область в таблице с исходными данными. Это диапазон, который включает в себя название фруктов и их количество в килограммах. Потом в закладке Вставка выберем интересующую нас диаграмму и нажмем на соответствующий значок.
Обращаю внимание: при построение гистограммы и гистограммы с группировкой выбираем один и тот же вид диаграммы в закладке Вставка. Вид гистограммы в данном случае будет зависеть от исходных данных на которых она строиться.
Excel построил гистограмму на основе выделенного диапазона ячеек в таблице с фруктами.
С помощью настроек гистограммы мы можем ее изменить, так как необходимо нам. Если нажать на любую область гистограммы левой кнопкой мыши то появиться три блока настройки диаграммы:
Выполним простейшую настройку нашей гистограммы:
После простейшей настройке гистограммы она стала более информативная. Далее, следую такому же принципу, можно настраивать диаграмму исходя из задачи, которая перед нами стоит.
Гистограмма с группировкой. Диаграмма Excel.
Теперь отразим на нашей гистограмме не только количество килограмм каждого вида фруктов, но и процент массы каждого вида фруктов от общей массы фруктов. В этот раз выберем диапазон ячеек, который будет включать в себя название фруктов, массу в килограммах и проценты. Далее, по такому же принципу, как описано выше создаем гистограмму с группировкой.
Обращаю внимание: при построение гистограммы и гистограммы с группировкой выбираем один и тот же вид диаграммы в закладке Вставка. Вид гистограммы в данном случае будет зависеть от исходных данных на которых она строиться.
Отличие этой гистограммы от предыдущей в том, что здесь отображаются два значения, килограммы и проценты. Настроим нашу диаграмму.
После настройки наша гистограмма стала более информативная.
Объемная гистограмма (с группировкой). Диаграмма Excel.
Объемная гистограмма по сути та же гистограмма и гистограмма с группировкой, с той лишь разнице, что картинка, которую увидит пользователь, при использование данного вида диаграммы будет объемной.
Пример объемной гистограммы с группировкой:
Excel в рамках одного вида диаграмм предлагает разные варианты визуализации графических данных. При этом будет меняться только картинка, которую увидит пользователь, но суть диаграммы от этого не измениться.
Линейчатая, линейчатая с группировкой и объемная линейчатая диаграмма.
В данном разделе рассмотрим другой тип диаграмм: линейчатая, линейчатая с группировкой и объемная линейчатая. Рассматривать будет на том же примере таблицы с фруктами.
Линейчатая диаграмма.
Линейчатая диаграмма отличается от гистограммы тем, что столбцы, которые отражают исходные данные из таблицы, расположены не вертикально, а горизонтально. Во всем остальном — алгоритм построения, настройки диаграммы и сама суть линейчатой диаграммы такая же, как и у гистограммы.
Линейчатая с группировкой. Диаграмма Excel.
Все то же самое, что и у гистограмма с группировкой. Построим линейчатую диаграмму с группировкой.
Объемная линейчатая (с группировкой). Диаграмма Excel.
Построим объемную линейчатую диаграмму с группировкой по аналогии с объемной гистограммой с группировкой.
Гистограмма с накоплением. Отображение итогов
Выбор того или иного вида диаграммы обусловлен в первую очередь идеей, которую вы хотите донести до аудитории. Если ваша задача сравнить некоторые сущности в определенные моменты времени, то лучший выбор – гистограмма. Например:
Скачать заметку в формате Word, примеры в формате Excel
Если же вы сравниваете не одну, а несколько сущностей, которые к тому же дают суммарный вклад в общие итоги, то наилучшим образом подойдет гистограмма с накоплением:
К сожалению, стандартные методы Excel не позволяют вывести подпись, отражающую итоговое значение по столбцу; можно отобразить значения отдельных или всех составляющих:
Небольшой трюк [1] позволяет отобразить на гистограмме с накоплением итоговые значения.
1. Добавьте в таблицу данных сумму по строкам и постройте гистограмму с накоплением на основе всех данных:
2. Выделите на диаграмме только итоговый ряд и правой кнопкой мыши выберите в меню «Изменить тип диаграммы для ряда»:
3. Выберите обычный график (без маркеров):
4. Выделите итоговый ряд (линию) и добавьте подписи данных
5. Выделите подписи данных и отформатируйте их
6. Измените расположение подписи на «Сверху»
7. Выделите нашу итоговую линию и сделайте ее невидимой:
8. Удалите строку «Итого» в легенде; для этого дважды щелкните на легенде, выделите строку «Итого», удалите ее.
[1] Идею трюка я позаимствовал в книге Холи «Excel 2007. Трюки»
32 комментария для “Гистограмма с накоплением. Отображение итогов”
Baguzin, я Вам очень благодарна!! Целую неделю билась как рыба об лед, пока не обратилась за помощью к интернету. а тут — Вы, с таким подробным понятным пошаговым объяснением. Счастья Вам!
Не поверите, постоянно использую такие диаграммы и самое «умное», что придумала — добавлять итоговые значения в WordArt. Но это трудоемко и редактировать вручную надо при изменении значений.
Теперь наконец-то нашла ответ на свой вопрос
Приветствую тебя, О Baguzin.
Респект тебе и уважуха.
Столько времени теперь появится для других трюков 🙂
Очень полезно.
Благодарю.
Спасибо! Долго не мог поверить что такой возможности нет в стандарте.
Спасибо, товарищ!
Очень помогли!
Спасибо, жаль только работает для двумерных диаграмм, может кто знает как сделать для трехмерных?
Можно, следует добавить данные с итогами к каждом столбцу, добавить подпись (у основания), затем сделать заливку и контур столбцов с итогами прозрачными. Остаётся только шкалу подправить
Спасибо, огромное преогромное!
Как в данной диаграмме можно вывести подписи продаж по регионам в процентах
Таня, если вам просто нужны проценты (при этом в сумме будет всегда 100%), то выберите другой тип диаграммы — нормированная гистограмма с накоплением. Если же вы хотите иметь в сумме абсолютные значения, а в подписях доли (проценты), задача усложняется.
1. Создайте на листе область с процентами (В10:Е16 на рис. ниже).
2. Перейдите в файле Excel к финальной диаграмме, представленной в настоящей заметке. Кликните правой кнопкой мыши на одном из столбцов диаграммы, и выберите опцию Добавить подписи данных.
3. Кликните правой кнопкой мыши на ряде с подписями данных (на рисунке ниже это ряд Регион2) и выберите опцию Формат подписей данных.
4. В окне Формат подписей данных поставьте галочку в опции Значения из ячеек.
5. В окне Диапазон меток данных выберите диапазон с процентами (в нашем примере D10:D16); кликните Ok.
6. Снимите галочку в окне Формат подписей данных напротив опции Значение.
7. Выполните пп. 5 и 6 для всех рядов подписей данных. 
Чтобы увеличить картинку, кликните на ней правой кнопкой мыши и выберите опцию Открыть картинку в новой вкладке.
См. также Excel-файл с этим примером.
Спасибо за ответ!
Я хотела иметь в сумме абсолютные значения, а в подписях доли (проценты)
Гистограмма в EXCEL с накоплением
history 17 октября 2013 г.
Сначала научимся создавать обычную гистограмму с накоплением (задача №1), затем более продвинутый вариант с отображением начального, каждого последующего изменения и итогового значения (задача №2). Причем положительные и отрицательные изменения будем отображать разными цветами.
Задача №1. Обычная гистограмма с накоплением
Создадим обычную гистограмму с накоплением.
Такая диаграмма используется для визуализации вклада каждой составляющей в общий результат. Например, вклад каждого сотрудника в общую выручку подразделения.
Решение
Создадим исходную таблицу: объемы продаж 2-х сотрудников по месяцам (см. файл примера ).
Далее, выделяем любую ячейку таблицы и создаем гистограмму с накоплением ( Вставка/ Диаграммы/ Гистограмма/ Гистограмма с накоплением ).
Как видно из рисунка выше, в третьем столбце (март) значение у второго сотрудника равно 0, которое отображается на диаграмме (красный столбец отсутствует, но значение 0 отображается).
Чтобы не отображать этот нуль его можно удалить вручную из диаграммы, кликнув по нему два раза (интервал между кликами должен быть порядка 1 сек). Но, при изменении исходных данных есть риск, что значение у второго сотрудника изменится, но отображаться уже не будет.
Для того, чтобы заменить значение 0 на #Н/Д создадим специальную таблицу для гистограммы (см. файл примера ), значения которой будут браться из исходной таблицы с помощью незамысловатой формулы =ЕСЛИ(НЕ(B8);НД();B8)
Теперь ноль не отображается.
Задача №2. Продвинутая гистограмма с накоплением
Теперь создадим гистограмму с отображением начального, каждого последующего изменения и итогового значения. Причем положительные и отрицательные изменения будем отображать разными цветами. Эту диаграмму можно использовать для визуализации произошедших изменений, например отклонений от бюджета (также см. статью Диаграмма Водопад в MS EXCEL ).
Предположим, что в начале годы был утвержден бюджет предприятия (80 млн. руб.), затем январе, июле, ноябре и декабре появились/отменились новые работы, ранее не учтенные в бюджете, которые необходимо отобразить подробнее и оценить их влияние на фактическое исполнение бюджета.
Решение
Создадим исходную таблицу (см. Лист с Изменением в Файле примера ). Отдельно введем плановую сумму бюджета и изменения по месяцам.
Эту таблицу мы НЕ будем использовать для построения диаграммы, а построим на ее основе другую таблицу, более подходящую для этих целей.
Нижеследующие строки в столбце Служебный (синий столбец на диаграмме) будем заполнять с помощью формулы =E8+ЕСЛИОШИБКА(F8;0)-ЕСЛИОШИБКА(G9;0)
Далее, выделяем любую ячейку таблицы и создаем гистограмму с накоплением ( Вставка/ Диаграммы/ Гистограмма/ Гистограмма с накоплением ). После несложных дополнительных настроек границы получим окончательный вариант.
В случае только увеличения бюджета можно получить вот такую гистограмму (см. Лист с Увеличением в Файле примера ).
Как построить гистограмму в Excel и объединить ее с графиком
Гистограмма в Excel – это способ построения наглядной диаграммы, отражающей изменение нескольких видов данных за какой-то период времени.
С помощью гистограммы удобно иллюстрировать различные параметры и сравнивать их. Рассмотрим самые популярные виды гистограмм и научимся их строить.
Как построить обновляемую гистограмму?
Имеем данные по продажам разных видов молочной продукции по каждому месяцу за 2015 год.
Построим обновляемую гистограмму, которая будет реагировать на вносимые в таблицу изменения. Выделим весь массив вместе с шапкой и кликнем на вкладку ВСТАВКА. Найдем так ДИАГРАММЫ – ГИСТОГРАММА и выберем самый первый тип. Он называется ГИСТОГРАММА С ГРУППИРОВКОЙ.
Получили гистограмму, размер поля которой можно менять. На такой диаграмме наглядно видно, например, что самые большие продажи были по молоку в ноябре, а самые маленькие – по сливкам в июне.
Если мы будем вносить в таблицу изменения, внешний вид гистограммы тоже будет меняться. Для примера вместо 1400 в январе по кефиру поставим 4000. Видим, как зеленый столбец полетел вверх.
Гистограмма с накоплением
Теперь рассмотрим, как построить гистограмму с накоплением в Excel. Еще один тип гистограмм, который позволяет отразить данные в процентном соотношении. Строится она точно так же, но выбирается другой тип.
Получаем диаграмму, на которой можно видеть, что, например, в январе больше продано молока, чем кефира или сливок. А в августе, по сравнению с другими молочными продуктами, молока было продано мало. И т.п.
Гистограммы в Excel можно изменять. Так, если мы кликнем правой кнопкой мыши в пустом месте диаграммы и выберем ИЗМЕНИТЬ ТИП, то сможем несколько ее видоизменить. Поменяем тип нашей гистограммы с накоплением на нормированную. Результатом будет та же самая диаграмма, но по оси Y будут отражены соотношения в процентном эквиваленте.
Аналогично можно производить и другие изменения гистограммы, что мы и сделали:
Как объединить гистограмму и график в Excel?
Некоторые массивы данных подразумевают построение более сложных диаграмм, которые совмещают несколько их видов. К примеру, гистограмма и график.
Рассмотрим пример. Для начала добавим к таблице с данными еще одну строку, где прописана выручка за каждый месяц. Она указана в рублях.
Теперь изменим существующую диаграмму. Кликнем в пустом месте правой кнопкой и выберем ВЫБРАТЬ ДАННЫЕ. Появится такое поле, на котором будет предложено выбрать другой интервал. Выделяем всю таблицу снова, но уже охватывая и строку с выручкой.
Excel автоматически расширил область значений по оси Y, поэтому данные по количеству продаж остались в самом низу в виде незаметных столбиков.
Но такая гистограмма неверна, потому что на одной диаграмме у нас значатся числа в рублевом и количественном эквиваленте (рублей и литров). Поэтому нужно произвести изменения. Перенесем данные по выручке на правую сторону. Кликнем по фиолетовым столбикам правой кнопкой, выберем ФОРМАТ РЯДА ДАННЫХ и отметим ПО ВСПОМОГАТЕЛЬНОЙ ОСИ.
Видим, что график сразу изменился. Теперь фиолетовый столбик с выручкой имеет свою область значения (справа).
Но это все равно не очень удобно, потому что столбики почти сливаются. Поэтому произведем еще одно дополнительное действие: кликнем правой кнопкой по фиолетовым столбцам и выберем ИЗМЕНИТЬ ТИП ДИАГРАММЫ ДЛЯ РЯДА. Появится окно, в котором выбираем график, самый первый тип.
Получаем вполне наглядную диаграмму, представляющую собой объединение гистограммы и графика. Видим, что максимальная выручка была в январе и ноябре, а минимальная – в августе.
Аналогично можно совмещать любые виды диаграмм.