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

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

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

Отображение разницы на диаграммах Excel

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

Отображение разницы на диаграммах Excel

Обзор

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

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

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

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

Отображение разницы на диаграммах Excel

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

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

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

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

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

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

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

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

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

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

    Отображение разницы на диаграммах Excel

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

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

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

    Отображение разницы на диаграммах Excel

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

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

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

    Отображение разницы на диаграммах Excel

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

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

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

    Отображение разницы на диаграммах Excel

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

    Отображение разницы на диаграммах Excel

    В разделе настроек Параметры ряда (Series Options) поставьте флажок Построить ряд (Plot Series On) в положение По вспомогательной оси (Secondary Axis).

    Отображение разницы на диаграммах Excel

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

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

    Отображение разницы на диаграммах Excel

    На вкладке Конструктор (Design) нажмите кнопку Изменить тип диаграммы (Change Chart Type) и выберите тип Гистограмма с группировкой (Clustered Column).

    Отображение разницы на диаграммах Excel

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

    Отображение разницы на диаграммах Excel

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

    Отображение разницы на диаграммах Excel

  5. Настраиваем формат диаграммы. Настройки форматирования, заданные по умолчанию, выглядят не очень привлекательно. Мы можем кое-что исправить, чтобы сделать нашу диаграмму более презентабельной:
    • Переместим легенду в верхнюю часть области построения диаграммы и удалим из нее 3 позиции с разницей.
    • Добавим название диаграммы.
    • Удалим подписи осей.
    • Настроим цвета границы и заливки для столбцов.
    • Удалим горизонтальные линии сетки.

    Отображение разницы на диаграммах Excel

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

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

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

    Отображение разницы на диаграммах Excel

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

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

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

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

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

09.01.2016 17:00
3036

Комментарии

Нет комментариев. Ваш будет первым!