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

В этом сообщении в блоге я покажу вам супер-способ создать динамическую целевую линию на диаграмме Excel, которая поможет вам отслеживать свою эффективность в течение нескольких месяцев. Что-то вроде того, что показано ниже:

Целевая линия управляется полосой прокрутки, и, как если бы цель была достигнута (или превышена) в любой из месяцев, она выделяется зеленым цветом.

Создание динамической целевой линии в гистограмме Excel

Эта диаграмма состоит из 3 частей:

  • Гистограмма
  • Целевая линия (горизонтальная пунктирная линия)
  • Полоса прокрутки (для управления целевым значением)

Гистограмма

У меня есть данные, как показано ниже:

Ячейки B2: B13 содержат все значения, а C2: C13 показывает значение, только если оно превышает целевое значение (в ячейке F2). Если значение ниже целевого значения, отображается # N / A. Теперь нам нужно нанести эти значения на кластерную диаграмму.

  • Выберите весь набор данных (A1: C13)
  • Перейдите в Вставка -> Диаграммы -> Кластерная столбчатая диаграмма.
  • Выберите любую полосу для значений «Выше целевого», щелкните правой кнопкой мыши и выберите «Форматировать ряд данных».
  • В разделе Series Option измените значение Series Overlap на 100%.
  • Это создает диаграмму, где все значения, превышающие целевое, выделяются другим цветом (вы можете проверить это, изменив целевое значение)

Целевая линия

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

  • Выберите диаграмму и перейдите в Дизайн -> Выбрать данные.
  • В диалоговом окне «Выбор источника данных» нажмите «Добавить».
  • В поле «Редактировать серию» введите «Имя серии» как «Целевая линия» и в поле «Значение серии» выберите ячейку целевого значения.
  • Гистограмма будет вставлена ​​только для первой точки данных (январь).
  • Выберите эту панель данных, щелкните правой кнопкой мыши и выберите Изменить тип диаграммы ряда.
  • Измените его тип диаграммы на точечную диаграмму. Это превратит полосу в одну точку в январе.
  • Выберите точку данных и перейдите в Дизайн -> Макеты диаграмм -> Добавить элемент диаграммы -> Полосы ошибок -> Дополнительные параметры полос ошибок.
    • В Excel 2010 выберите точку данных и перейдите в Макет -> Анализ -> Полосы ошибок -> Дополнительные параметры полос ошибок.
  • Вы заметите горизонтальные полосы погрешностей по обе стороны от точки разброса. Выберите эту горизонтальную полосу ошибок, а затем в разделе «Параметры панели ошибок» выберите «Пользовательский» и нажмите «Указать значение».
  • Задайте положительное значение как 11 и отрицательное значение как 0 (вы можете использовать хит и пробу, чтобы увидеть, какое значение подходит для вашей диаграммы)
  • Выберите точку данных разброса, щелкните правой кнопкой мыши и выберите «Форматировать данные ряда». Перейдите в раздел «Параметры маркера» и выберите «Тип маркера» как «Нет». Это удаляет точку данных, и у вас остается только панель ошибок (которая является вашей целевой линией).
  • Обратите внимание, что шкала ошибок будет меняться всякий раз, когда вы меняете целевое значение. Просто отформатируйте его, чтобы он превратился в пунктирную линию, и измените его цвет, чтобы он выглядел лучше.

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

  • Создайте полосу прокрутки и выровняйте ее по диаграмме. Совместите полосу прокрутки с диаграммой. Щелкните здесь, чтобы узнать, как создать полосу прокрутки в Excel.
  • Сделайте максимальное значение полосы прокрутки равным максимальному значению в вашей диаграмме и свяжите значение полосы прокрутки с любой ячейкой (я использовал G2)
  • В ячейке с целевым значением используйте формулу = 500-G2 (500 — максимальное значение на диаграмме).
  • Это необходимо для того, чтобы ваше целевое значение теперь перемещалось вместе с полосой прокрутки.

Это оно!! Теперь, когда вы перемещаете полосу прокрутки и меняете целевые значения, полосы, соответствующие целевому, автоматически выделяются другим цветом.

Создание динамической целевой линии в гистограммах Excel