Отображение разницы на гистограмме и линейчатой диаграмме с группировкой

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

Гистограмма с группировкой и с отображением разницы:

Линейчатая диаграмма с группировкой и с отображением разницы:

Обзор

Гистограмма с группировкой или линейчатая диаграмма с группировкой оказываются отличным решением, когда нужно сравнить два ряда данных по множеству категорий. В примере, приведённом выше, мы сравниваем План (Budget) и Факт (Actual) по множеству регионов. Стандартная диаграмма с группировкой показывает итоги в каждом ряду по категориям, но не даёт информации о разнице. Пользователь вынужден рассчитывать разницу самостоятельно.

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

Требования к данным

Для любой диаграммы очень важным является создать правильную структуру данных прежде, чем приступать к построению графика. На рисунке ниже показано, как данные должны быть организованы на рабочем листе. Это форма простого отчёта, в котором один столбец содержит имена категорий (Region) и два столбца отведено для рядов данных (Budget и Actual).

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

Требования к диаграмме

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

Диаграмма использует две различные оси: сравниваемые ряды данных построены на вспомогательной оси, а разница – на основной. В результате диаграмма с накоплением (разница) располагается позади диаграммы с группировкой (план и факт).

Как это делается

Вычисление данных

Первым делом добавляем в таблицу с данными три столбца с расчётами.

• Точка отсчёта разницы (Base Variance) – точка отсчёта для построения разницы рассчитывается, как минимальное значение из двух наборов данных в каждой строке таблицы.

  =МИН(C4:D4)
=MIN(C4:D4)

  Так мы получаем значение для построения базовой линии диаграммы с накоплением. Эта часть графика будет скрыта за диаграммой с группировкой.

  

• Положительная разница (Positive Var) – рассчитывается, как разница между рядом 1 и рядом 2 (факт и бюджет), и изображается на графике, как положительный результат.

  =ЕСЛИ(E4>0;E4;"")
=IF(E4>0,E4,"")

  Условие ЕСЛИ (IF) возвращает пустое значение, если разница отрицательна. Пустое значение и подпись для него не будут показаны на графике.

  

• Отрицательная разница (Negative Var) – такое же простое вычисление, как и в случае с положительной разницей, но для того, чтобы получить положительное значение при отрицательной разнице, мы использовали функцию вычисления модуля числа ABS.

  =ЕСЛИ(E5<0;ABS(E5);"")
=IF(E5<0,ABS(E5),"")

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

  

Как создать диаграмму

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

1. Первым делом создаём гистограмму с накоплением и строим в ней пять рядов данных.
2. Ряд 1 (Actual) и Ряд 2 (Budget) должны быть построены по вспомогательной оси. Кликните правой кнопкой мыши по столбцу ряда данных Actual на графике и нажмите Формат ряда данных (Format Data Series).В разделе настроек Параметры ряда (Series Options) поставьте флажок Построить ряд (Plot Series On) в положение По вспомогательной оси (Secondary Axis).

   Повторите эту операцию для ряда данных Budget.

3. Для рядов данных 1 и 2 измените тип диаграммы на Гистограмма с группировкой (Clustered Column). Выделите ряд данных Actual на графике или в выпадающем списке Элементы диаграммы (Chart Elements), который находится на вкладке Макет (Layout) в разделе Текущий фрагмент (Current selection). Диаграмма должна быть выделена, чтобы группа вкладок Работа с диаграммами (Chart Tools) появилась на Ленте.На вкладке Конструктор (Design) нажмите кнопку Изменить тип диаграммы (Change Chart Type) и выберите тип Гистограмма с группировкой (Clustered Column).

   С этого момента можно видеть, как наша диаграмма начинает приобретать нужную форму. Ряды данных Actual (Факт) и Buget (План) теперь показаны в виде столбцов, расположенных рядом для удобства сравнения. Ряд данных, отображающий разницу, виден позади, как столбец с накоплением.

   

4. В диалоговом окне Формат ряда данных (Format Data Series) в разделе Параметры ряда (Series Options) настройте Боковой зазор (Gap Width) для обоих графиков. Этот параметр регулирует зазор между столбцами. Уменьшите его значение, и ширина столбцов увеличится, а зазор между категориями уменьшится.
5. Настраиваем формат диаграммы. Настройки форматирования, заданные по умолчанию, выглядят не очень привлекательно. Мы можем кое-что исправить, чтобы сделать нашу диаграмму более презентабельной:
   • Переместим легенду в верхнюю часть области построения диаграммы и удалим из нее 3 позиции с разницей.
   • Добавим название диаграммы.
   • Удалим подписи осей.
   • Настроим цвета границы и заливки для столбцов.
   • Удалим горизонтальные линии сетки.

   

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

   _(* # ##0_);_(* (# ##0);_(* ""_);_(@_)
_(* #,##0_);_(* (#,##0);_(* ""_);_(@_)

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

   

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

Подведём итог

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

Что думаете о прочитанном? Может быть Вы используете другой тип диаграммы для отображения разницы?

Оставляйте комментарии!