График с накоплением в excel что это

Создание блочной диаграммы

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

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

1. Рассчитайте значения квартилей на основе исходного набора данных.
2. Вычислите разницу между квартилями.
3. Создайте гистограмму с накоплением из диапазонов квартилей.
4. Преобразуйте гистограмму в блочную диаграмму.

В нашем примере исходный набор источник содержит три столбца. Каждый столбец включает по 30 элементов из следующих диапазонов:

• Столбец 1 (2013 г.): 100–200
• Столбец 2 (2014 г.): 120–200
• Столбец 3 (2015 г.): 100–180

• Шаг 1. Вычисление значений квартилей
• Шаг 2. Вычисление разницы между квартилями
• Шаг 3. Создание гистограммы с накоплением
• Шаг 4. Преобразование гистограммы с накоплением в блочную диаграмму
  • Скрытие нижнего ряда данных
  • Создание усов для построения блочной диаграммы
  • Закрашивание центральных областей

  Шаг 1. Вычисление значений квартилей

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

  Для этого создайте вторую таблицу и заполните ее следующими формулами:

  Значение	Формула
  Минимальное значение	МИН(диапазон_ячеек)
  Первый квартиль	КВАРТИЛЬ.ВКЛ (диапазон_ячеек; 1)
  Медиана	КВАРТИЛЬ.ВКЛ (диапазон_ячеек; 2)
  Третья квартиль	КВАРТИЛЬ.ВКЛ (диапазон_ячеек; 3)
  Максимальное значение	МАКС(диапазон_ячеек)

  

  В результате должна получиться таблица, содержащая нужные значения. При использовании примера данных получаются следующие квартили:

  Шаг 2. Вычисление разницы между квартилями

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

  • первым квартилем и минимальным значением;
  • медианой и первым квартилем;
  • третьим квартилем и медианой;
  • максимальным значением и третьим квартилем.
  1. Для начала создайте третью таблицу и скопируйте в нее минимальные значения из последней таблицы.
  2. Вычислите разницу между квартилями с помощью формулы вычитания Excel (ячейка1 — ячейка2) и заполните третью таблицу значениями разницы.

  Для примера данных третья таблица выглядит следующим образом:

  Шаг 3. Создание гистограммы с накоплением

  Данные в третьей таблице хорошо подходят для построения блочной диаграммы. Создадим гистограмму с накоплением, а затем изменим ее.

  

  Выделите все данные в третьей таблице и щелкните Вставка >Вставить гистограмму >Гистограмма с накоплением.

  

  Пока диаграмма не похожа на блочную, так как Excel по умолчанию рисует столбцы с накоплением на основе наборов данных по горизонтали, а не по вертикали.

  

  Советы:

  Чтобы переименовать столбцы, в области Подписи горизонтальной оси (категории) щелкните Изменить, выберите в третьей таблице диапазон ячеек с нужными именами категорий и нажмите кнопку ОК.

  График должен выглядеть так, как показано ниже. В этом примере также было изменено название диаграммы, а легенда скрыта.

  

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

  Скрытие нижнего ряда данных

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

  Выберите нижнюю часть столбцов.

  Примечание: При щелчке одного из столбцов будут выбраны все вхождения того же ряда.

  

  Щелкните Формат >Текущий фрагмент >Формат выделенного фрагмента. В правой части появится панель Формат.

  

  На вкладке Заливка панели Формат выберите пункт Нет заливки. Нижний ряд данных будет скрыт.

  Создание усов для построения блочной диаграммы

  Далее нужно заменить верхний и второй снизу ряды (выделены на рисунки темно-синим и оранжевым) линиями ( усами ).

  

  1. Выберите верхний ряд данных.
  2. На вкладке Заливка панели Формат выберите пункт Нет заливки.
  3. На ленте щелкните Конструктор >Добавить элемент диаграммы >Предел погрешностей >Стандартное отклонение.

  

• Выберите один из нарисованных пределов погрешностей.
• Откройте вкладку Параметры предела погрешностей и в области Формат задайте следующие параметры:
  • В разделе Направление установите переключатель Минус.
  • Для параметра Конечный стиль задайте значение Без точки.
  • Для параметра Величина погрешности укажите процент, равный 100.

  

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

Закрашивание центральных областей

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

1. Выберите верхнюю часть блочной диаграммы.
2. На вкладке «Заливка & линии» на панели «Формат» нажмите кнопку «Сплошная заливка».
3. Выберите цвет заливки.
4. Щелкните Сплошная линия на этой же вкладке.
5. Выберите цвет контура, а также ширину штриха.
6. Задайте те же значения для других областей блочной диаграммы. В результате должна получиться блочная диаграмма.

12 нестандартных диаграмм Excel

В этой статье собрали некоторые необычные диаграммы Excel со ссылками на краткие инструкции по их построению.

Диаграмма по мотивам Wall Street Journal

Какое-то время назад для того, чтобы показать неограниченность рисования в Excel, делал ряд статей, а файлы Excel к ним не прикладывал. Решил, что пора «рассекретить» хитрые диаграммы. Встретил диаграмму, созданную дизайнерами Wall Street Journal. И тут же ее воспроизвел в Excel.

Диаграмма темпов роста в Excel

Как в Excel создать диаграмму с динамикой темпов роста, где изменения показателей показаны стрелками? Все просто — рисуем столбцы и добавляем к ним полосы повышения и понижения. Плюс рисуем графики с накоплением — первый для роста, второй график — уровень подписи.

Диаграмма сравнения роста показателей

Как в Excel показать сравнить рост двух различных показателей? Можно «просто» сравнить данные. Правда, если они не сопоставимы, то особо ничего не увидишь. Но есть еще один способ, пожалуй, самый наглядный: методом базисной подстановки, который любит The Economist.

Диаграмма ABC-анализа

Про ABС-анализ слышали, наверное, все. Методику анализа приводить не буду. Привожу решение с визуализацией по мотивам журнала The Economist.

График структуры и перехода

Какое-то время назад в журнале The Economist увидел диаграмму с переходами по структуре от периода к периоду и сделал её в Excel. А недавно и для проекта в Power BI вместе с ABC-анализом. Файл Excel прикладываю для изучения.

Диаграмма с горизонтальной «зеброй»

Как построить в Excel диаграмму журнального качества с зеброй вместо горизонтальных линий? Есть несколько способов, с одним из которых можно ознакомиться благодаря приложенному файлу. Диаграмма построена по мотивам Wall Street Journal.

Как цветом показать на графике рост или падение последнего значения

Итак, задача: показать тенденцию последнего месяца (квартала, года, периода). В стандартных графиках можно настроить такое представление, чтобы на конце линии была красная стрелка, если у нас спад, и зеленая — если подъем.

Диаграмма по мотивам The Economist

Обнаружил в журнале The Economist за октябрь 2014 года интересную гистограмму с меткой на столбцах. Тут же ее воспроизвел в Excel — вроде все получилось, кроме шрифтов.

Линейчатая диаграмма с эффектами

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

Диаграмма в виде колб

Как-то давно я делал отчет с инфографикой. Первая колба — год, наполнение — сколько дней в году прошло. Следующие колбы — это контрольные показатели. 100% — план за год, наполнение — как выполнен факт. Смотрим, сколько прошло времени и как мы сработали.

Спарклайны и микрографики

Как мы делаем отчеты? В виде здоровых (и нездоровых, кстати, тоже) таблиц. Что из них понятно людям, имеющим малый опыт работы с большими объемами данных? Ничего. Далее рассмотрен пример, как показать структуру продаж за месяц и за год, и тут же показать динамику по месяцам и годам.

Полоса прокрутки в Excel

Напоследок предлагаем способ, как показать все диаграммы отчета, используя полосы прокрутки в Excel.

График с накоплением в excel что это

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Гистограмма в 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 Основная их задача — отображать значение #Н/Д в столбце Увеличение , когда увеличения не происходит, и в столбце Уменьшение , когда не происходит уменьшения.

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

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

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