Отображение разницы на гистограмме и линейчатой диаграмме с группировкой - Microsoft Excel для начинающих > Спринг Офис
Спринг Офис    

Отображение разницы на гистограмме и линейчатой диаграмме с группировкой - Microsoft Excel для начинающих


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

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

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

Обзор

Гистограмма с группировкой или линейчатая диаграмма с группировкой оказываются отличным решением, когда нужно сравнить два ряда данных по множеству категорий. В примере, приведённом выше, мы сравниваем План (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);_(* ""_);_(@_)

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

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

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

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

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

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

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: https://www.excelcampus.com/charts/variance-clustered-column-bar-chart/
Перевел: Антон Андронов
Правила перепечатки
Еще больше уроков по Microsoft Excel



Отзывы (через Facebook):

Оставить отзыв с помощью аккаунта FaceBook:

Спринг Офис - все об офисных принадлежностях и офисных приложениях. Хотите понять, зачем нужны термоэтикетки, или как удалить переносы строк в Excel? Это к нам!