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

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

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

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

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

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

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

Гистограмма

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

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

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

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

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

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

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

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

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

Оцените статью
Vip Excel: cоветы по работе с Эксель, таблицы и формулы
Adblock
detector