Если вы работаете в отделе продаж и ваша жизнь сосредоточена на целях, этот метод построения графиков для вас. А если нет, все равно прочтите его, чтобы узнать несколько интересных приемов построения графиков в Excel.
В этом сообщении в блоге я покажу вам супер-способ создать динамическую целевую линию на диаграмме Excel, которая поможет вам отслеживать свою эффективность в течение нескольких месяцев. Что-то вроде того, что показано ниже:
Целевая линия управляется полосой прокрутки, и, как если бы цель была достигнута (или превышена) в любой из месяцев, она выделяется зеленым цветом.
Создание динамической целевой линии в гистограмме Excel
Эта диаграмма состоит из 3 частей:
- Гистограмма
- Целевая линия (горизонтальная пунктирная линия)
- Полоса прокрутки (для управления целевым значением)
Гистограмма
У меня есть данные, как показано ниже:
Ячейки B2: B13 содержат все значения, а C2: C13 показывает значение, только если оно превышает целевое значение (в ячейке F2). Если значение ниже целевого значения, отображается # N / A. Теперь нам нужно нанести эти значения на кластерную диаграмму.
- Выберите весь набор данных (A1: C13)
- Перейдите в меню «Вставка» -> «Диаграммы» -> «Кластерная столбчатая диаграмма».
- Выберите любую полосу для значений «Выше целевого», щелкните правой кнопкой мыши и выберите «Форматировать ряд данных».
- В разделе Series Option измените значение Series Overlap на 100%.
- Это создает диаграмму, где все значения, превышающие целевое, выделяются другим цветом (вы можете проверить это, изменив целевое значение)
Целевая линия
Здесь позвольте мне показать вам умный способ создания целевой линии с использованием полос погрешностей.
- Выберите диаграмму и перейдите в Дизайн -> Выбрать данные.
- В диалоговом окне «Выбор источника данных» нажмите «Добавить».
- В поле «Редактировать серию» введите «Имя серии» как «Целевая линия», а в поле «Значение серии» выберите ячейку с целевым значением.
- Гистограмма будет вставлена только для первой точки данных (январь).
- Выберите эту панель данных, щелкните правой кнопкой мыши и выберите Изменить тип диаграммы ряда.
- Измените его тип диаграммы на точечную диаграмму. Это превратит полосу в одну точку в январе.
- Выберите точку данных и перейдите в Дизайн -> Макеты диаграмм -> Добавить элемент диаграммы -> Полосы ошибок -> Дополнительные параметры полос ошибок.
- В Excel 2010 выберите точку данных и перейдите в Макет -> Анализ -> Полосы ошибок -> Дополнительные параметры полос ошибок.
- В Excel 2010 выберите точку данных и перейдите в Макет -> Анализ -> Полосы ошибок -> Дополнительные параметры полос ошибок.
- Вы заметите горизонтальные полосы погрешностей по обе стороны от точки разброса. Выберите эту горизонтальную полосу ошибок, а затем в разделе «Параметры панели ошибок» выберите «Пользовательский» и нажмите «Указать значение».
- Задайте положительное значение как 11 и отрицательное значение как 0 (вы можете использовать хит и пробу, чтобы увидеть, какое значение подходит для вашей диаграммы)
- Выберите точку данных разброса, щелкните правой кнопкой мыши и выберите «Форматировать данные ряда». Перейдите в раздел «Параметры маркера» и выберите «Тип маркера» как «Нет». Это удаляет точку данных, и у вас остается только панель ошибок (которая является вашей целевой линией).
- Обратите внимание, что шкала ошибок будет меняться всякий раз, когда вы меняете целевое значение. Просто отформатируйте его, чтобы он стал пунктирной линией, и измените его цвет, чтобы он выглядел лучше.
Полоса прокрутки
- Создайте полосу прокрутки и выровняйте ее по диаграмме. Совместите полосу прокрутки с диаграммой. Щелкните здесь, чтобы узнать, как создать полосу прокрутки в Excel.
- Сделайте максимальное значение полосы прокрутки равным максимальному значению в вашей диаграмме и свяжите значение полосы прокрутки с любой ячейкой (я использовал G2)
- В ячейке с целевым значением используйте формулу = 500-G2 (500 — максимальное значение на диаграмме).
- Это необходимо для того, чтобы ваше целевое значение теперь перемещалось вместе с полосой прокрутки.
Вот и все!! Теперь, когда вы перемещаете полосу прокрутки и меняете целевые значения, полосы, соответствующие целевому, автоматически выделяются другим цветом.
