Гистограмма в 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 Основная их задача — отображать значение #Н/Д в столбце Увеличение , когда увеличения не происходит, и в столбце Уменьшение , когда не происходит уменьшения.
Далее, выделяем любую ячейку таблицы и создаем гистограмму с накоплением ( Вставка/ Диаграммы/ Гистограмма/ Гистограмма с накоплением ). После несложных дополнительных настроек границы получим окончательный вариант.
В случае только увеличения бюджета можно получить вот такую гистограмму (см. Лист с Увеличением в Файле примера ).
Для представления процентных изменений использован дополнительный ряд и Числовой пользовательский формат .
Как создать гистограмму с накоплением в Google Sheets
Столбчатая диаграмма с накоплением — это тип диаграммы, в которой используются столбцы, разделенные на несколько подстолбцов, для одновременной визуализации значений нескольких переменных.
В следующем пошаговом примере показано, как создать гистограмму с накоплением в Google Таблицах.
Шаг 1: введите данные
Предположим, мы проводим опрос и просим 100 мужчин и 100 женщин выбрать свой любимый вид спорта между бейсболом, футболом, футболом и баскетболом.
Сначала мы можем ввести результаты опроса в следующем формате:
Шаг 2: Создайте столбчатую диаграмму с накоплением
Чтобы создать гистограмму с накоплением для визуализации этих данных, мы можем выделить ячейки в диапазоне A1: C5 , а затем нажать « Вставить », а затем нажать « Диаграмма »:
Автоматически появится групповая гистограмма.
Чтобы превратить это в столбчатую диаграмму с накоплением, щелкните в любом месте диаграммы, затем щелкните три вертикальные точки в правом верхнем углу, затем нажмите « Изменить »:
На панели редактора диаграмм , которая появляется в правой части экрана, нажмите раскрывающееся меню Stacking , затем нажмите Standard :
Диаграмма будет автоматически преобразована в гистограмму с накоплением:
По оси x отображаются виды спорта, а по оси y показано, сколько мужчин и женщин выбрали каждый вид спорта в качестве своего любимого.
Примечание.Если вы хотите, чтобы каждая полоса в сумме составляла 100 %, вместо этого выберите параметр, который говорит 100 % в раскрывающемся меню « Стекирование ».
Шаг 3. Измените линейчатую диаграмму с накоплением
Не стесняйтесь нажимать на отдельные элементы диаграммы, чтобы изменить их внешний вид.
Например, мы можем изменить заголовок, цвета полос и расположение легенды:
Дополнительные ресурсы
В следующих руководствах объясняется, как создавать другие распространенные визуализации в Google Таблицах:
Как добавить итоговые значения в гистограмму с накоплением в Excel
Иногда вам может понадобиться добавить общее значение вверху каждого столбца в линейчатой диаграмме с накоплением в Excel.
В этом учебном пособии представлен пошаговый пример создания следующей гистограммы с накоплением с общим значением в верхней части каждого столбца:
Шаг 1: введите данные
Во-первых, давайте создадим следующий набор данных, который показывает общий объем продаж трех разных продуктов в течение каждого месяца в году:
Шаг 2: Рассчитайте общие значения
Далее мы будем использовать следующую формулу для расчета общего объема продаж в месяц:
=SUM( B2:E2 )
Мы можем ввести эту формулу в ячейку E2 , а затем скопировать и вставить ее в каждую оставшуюся ячейку в столбце E:
Шаг 3: Создайте столбчатую диаграмму с накоплением
Затем выделите диапазон ячеек A1:E13 , затем нажмите вкладку « Вставка » на верхней ленте, затем нажмите « Столбец с накоплением» в группе « Диаграммы ».
Будет создана следующая диаграмма:
Затем щелкните правой кнопкой мыши в любом месте диаграммы и выберите « Изменить тип диаграммы» :
В появившемся новом окне нажмите « Комбинировать », затем выберите « Столбец с накоплением» для каждого из продуктов и выберите « Линия» для «Итого», затем нажмите « ОК »:
Будет создана следующая диаграмма:
Шаг 4: Добавьте итоговые значения
Затем щелкните правой кнопкой мыши желтую линию и выберите Добавить метки данных .
Появятся следующие ярлыки:
Затем дважды щелкните любую из меток.
В появившейся новой панели отметьте кнопку рядом с « Вверху » для « Положения метки» :
Затем дважды щелкните желтую линию на графике.
В появившейся новой панели отметьте кнопку рядом с No line :
Линия будет удалена с графика, но итоговые значения останутся:
Шаг 5. Настройте диаграмму (необязательно)
Не стесняйтесь добавлять заголовок, настраивать цвета и регулировать ширину полос, чтобы сделать график более эстетичным:
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Построение гистограммы в Excel
Гистограмма – очень полезный инструмент, с помощью которого можно сделать демонстрируемую информацию более наглядной и простой для восприятия. Это особенно актуально, когда время на изучение данных в таблице ограничено, и нужно в сжатые сроки сформировать целостное представление о них. Давайте посмотрим, каким образом можно построить гистограмму в Эксель.
Строим гистограмму
Для выполнения поставленной задачи в Excel можно воспользоваться разными методами:
- использовать инструменты на ленте программы;
- воспользоваться надстройкой “Пакет анализа”;
- применить условное форматирование.
Ниже мы подробнее остановимся на каждом из этих пунктов.
Метод 1: используем инструменты на ленте программы
Это, пожалуй, самый простой способ. И вот, как он реализуется:
- Открываем (или создаем) таблицу. Выделяем любым удобным способом (например, с помощью зажатой левой кнопки мыши) ячейки, на базе которых планируется построить диаграмму.
- Переходим во вкладку “Вставка”, в группе инструментов “Диаграммы” жмем кнопку “Вставить гистограмму”.
- Раскроется перечень возможных вариантов:
- гистограмма;
- объемная гистограмма;
- линейчатая;
- объемная линейчатая.
- После клика по нужному (понравившемуся) варианту, на листе появится гистограмма. В нашем случае мы выбрали самую простую.
Гистограмма с накоплением
До того, как приступить к созданию гистограммы с накоплением, проверяем, чтобы самая верхняя левая ячейка таблицы была пустой.
Затем делаем следующее:
- Выполняем выделение таблицы, на базе которой планируем построить гистограмму. Переходим во вкладку “Вставка”, нажимаем кнопку “Вставить гистограмму” (группа “Диаграммы”) и в раскрывшемся перечне останавливаем на варианте – “Гистограмма с накоплением”.
- Гистограмма вставлена, что и требовалось.
Примечание: в гистограммах с накоплением один столбец содержит сразу несколько значений. В нашем случае – это данные по всем четырем торговым точкам за конкретную дату.
Нормированная гистограмма с накоплением
В данном случае отображается (в процентном выражении) вклад каждого значения в общем количестве.
Метод 2: пользуемся надстройкой “Пакета “анализа”
Для начала нужно включить данный пакет.
- Идем в меню “Файл”.
- В перечне слева в самом низу щелкаем по пункту “Параметры”.
- В открывшемся окне выбираем “Надстройки”. В правой части окна для параметра “Управление” ставим значение “Надстройки Excel”, после чего жмем кнопку “Перейти”.
- В появившемся окошке ставим галочку напротив надстройки “Пакет анализа” и жмем OK.
- Перейдя во вкладку “Данные” в группе инструментов “Анализ” нажимаем кнопку “Анализ данных”.
- Откроется окошко, в котором нам нужен пункт “Гистограмма“, после выбора которого щелкаем OK.
- Перед нами появится окно, в котором нужно выполнить настройки гистограммы:
- в поле “Входной интервал” указываем диапазон ячеек, по которым нужно построить гистограмму. Сделать это можно вручную, прописав адреса с помощью клавиш на клавиатуре. Также мы можем выделить нужный диапазон ячеек в самой таблице, предварительно установив курсор в поле для ввода информации.
- внизу окна ставим галочку напротив параметра “Вывод графика”.
- в группе параметров вывода можно указать, куда следует вставить созданную гистограмму. Стандартное значение – на новом листе.
- по готовности жмем кнопку OK.
- Гистограмма сформирована и добавлена на отдельном листе, как мы и хотели.
Метод 3: выполняем условное форматирование с гистограммой
Получить гистограмму можно и с помощью условного форматирования ячеек. План действий следующий:
- Для начала нужно выделить элементы, которые нужны для гистограммы.
- Находясь в главной вкладке в группе “Стили” щелкаем по кнопке “Условное форматирование”. Откроется список, в котором выбираем “Гистограмму”. Раскроется еще один перечень, где нужно определиться с вариантами заливки – градиентная или сплошная.
- В выделенных ячейках появились гистограммы, соответствующие их значениям. В нашем случае была выбрана сплошная заливка синим цветом.
Редактирование гистограммы
Вставленную на лист гистограмму можно изменить:
- Корректируем или удаляем название. Для активации режима редактирования щелкаем по названию, после чего вокруг него появится рамка. Теперь кликаем в любом месте внутри рамки и мы можем приступать к редактированию.
- Удаляем легенду (если она не нужна). Предварительно нужно ее выбрать. Первым кликом выделяем все легенды (если их несколько), вторым – конкретную легенду. Удалить легенду можно через контекстное меню, вызываемое щелчком правой кнопки мыши по ней или просто нажав клавишу Del на клавиатуре.
- Меняем положение названия гистограммы и легенды. Для этого кликаем по выбранному объекту, чтобы выделить его. Затем наводим указатель мыши на любую из появившихся границ, и когда появится характерный символ в виде стрелок в четыре стороны, зажав левую кнопку мыши перетаскиваем объект в новое место.
- Меняем размер и положение гистограммы. Чтобы изменить размер гистограммы, зажав левую кнопку мыши тянем за одну из точек, расположенных на рамке вокруг нее.Для изменения положения выполняем те же самые действия, что и в отношении названия или легенды.
- Для более детальных настроек щелкаем по любой свободной части в области диаграммы (белые участки) и в открывшемся контекстном меню выбираем пункт “Формат области диаграммы”.Появится вспомогательное окно, в котором мы можем настроить:
- заливку и границы;
- добавить различные эффекты;
- размеры и свойства;
- параметры текста.
- Настраиваем шкалы по осям X и Y. Щелкаем по подписям выбранной шкалы правой кнопкой мыши и в открывшемся контекстном меню выбираем “Формат оси”.Откроется окно, в котором мы можем выполнить соответствующие настройки.
Помимо вышеперечисленного, на ленте программы во вкладке “Конструктор”, в которой мы окажемся автоматическим после создания гистограммы, мы можем сменить стиль, выбрать один из экспресс-макетов и т.д.
Также, мы можем добавить новый элемент диаграммы, щелкнув на соответствующий значок в левом верхнем углу ленты и выбрав из списка нужный пункт.
Примечание: возможных настроек и параметров, которые можно задать для гистограммы достаточно много, и каждый пользователь сможет подобрать для себя оптимальный вариант. Мы описали лишь часть основных действий и инструментов, которые могут помочь в этом.
Заключение
Программа Excel позволяет построить различные гистограммы, которые помогают лучше и быстрее освоить информацию, особенно, когда ее слишком много, а времени, наоборот, мало. Выбор конкретного вида и метода построения диаграммы зависит от того, какие цели преследует пользователь, и каким он видит конечный результат.