Якщо ви знаходитесь у відділі продажів і ваше життя - це цілі, ця техніка побудови діаграми для вас. А якщо ні, то все -таки прочитайте її, щоб навчитися цікавим трюкам Excel із створенням діаграми.
У цій публікації в блозі я покажу вам чудовий спосіб створити динамічну цільову лінію в діаграмі Excel, яка допоможе вам відстежувати свою ефективність протягом місяців. Щось, як показано нижче: 
Цільова лінія контролюється смугою прокрутки, і, якби ціль досягнута (або перевищена) у будь -який місяць, вона виділяється зеленим кольором.
Створення динамічної цільової лінії в діаграмі Excel
У цій таблиці є 3 частини:
- Гістограма
- Цільова лінія (горизонтальна пунктирна лінія)
- Смуга прокрутки (для контролю цільового значення)
Гістограма
У мене є дані, як показано нижче:
Клітинки B2: B13 мають усі значення, тоді як C2: C13 показує значення, лише якщо воно перевищує цільове значення (у комірці F2). Якщо значення нижче цільового значення, воно показує #Н/Д. Тепер нам потрібно нанести ці значення на кластерну діаграму
- Виберіть весь набір даних (A1: C13)
- Перейдіть до пункту Вставити -> Діаграми -> Кластерна колонна діаграма
- Виберіть будь -яку смужку для значень "Вище цілі" та клацніть правою кнопкою миші та виберіть Формат ряду даних
- У розділі Параметри серії змініть значення Перекриття серії на 100%
- Це створює діаграму, де всі значення, що перевищують цільове значення, виділені іншим кольором (ви можете перевірити це, змінивши цільове значення)
Цільова лінія
Тут дозвольте мені показати вам розумний спосіб створення цільового рядка за допомогою смужок помилок
- Виберіть діаграму та перейдіть до Дизайн -> Вибрати дані
- У діалоговому вікні Вибір джерела даних натисніть Додати
- У полі Редагувати серію введіть Ім'я серії як "Цільовий рядок", а в "Значення серії" виберіть клітинку "Цільове значення"
- Буде вставлено стовпчасту діаграму лише для першої точки даних (січень)
- Виберіть цю панель даних і клацніть правою кнопкою миші та виберіть Змінити тип діаграми серії
- Змініть тип діаграми на діаграму розсіювання. У січні ця панель зміниться на одну точку
- Виберіть точку даних і перейдіть до Конструктор -> Розкладки діаграм -> Додати елемент діаграми -> Панелі помилок -> Додаткові параметри панелей помилок
- Для Excel 2010 виберіть точку даних і перейдіть до Макет -> Аналіз -> Панелі помилок -> Додаткові параметри панелей помилок
- Для Excel 2010 виберіть точку даних і перейдіть до Макет -> Аналіз -> Панелі помилок -> Додаткові параметри панелей помилок
- Ви помітите горизонтальні лінії смуг помилок по обидві сторони від точки розсіювання. Виберіть цю горизонтальну панель помилок, а потім у розділі Параметри панелі помилок виберіть Користувацькі та натисніть Указати значення
- Поставте позитивне значення як 11, а від'ємне - як 0 (ви можете скористатися хітом і пробним, щоб побачити, яке значення добре виглядає для вашого діаграми)
- Виберіть точку даних Scatter і клацніть правою кнопкою миші та виберіть Формат даних серії. Перейдіть до Параметри маркерів і виберіть Тип маркера як відсутній. Це видаляє точку даних, і у вас є лише панель помилок (це ваш цільовий рядок)
- Зауважте, що ваша панель помилок змінюватиметься щоразу, коли ви змінюєте цільове значення. Просто відформатуйте його, щоб він став пунктирною лінією, і змініть його колір, щоб він виглядав краще
Панель прокрутки
- Створіть смугу прокрутки та вирівняйте її з діаграмою. Встановіть смугу прокручування разом з діаграмою. Натисніть тут, щоб дізнатися, як створити смугу прокрутки в Excel.
- Зробіть максимальне значення смуги прокрутки рівним максимальному значенню у вашій діаграмі та зв’яжіть значення смуги прокрутки з будь -якою коміркою (я використовував G2)
- У клітинці з цільовим значенням використовуйте формулу = 500-G2 (500-це максимальне значення на діаграмі)
- Це робиться для того, щоб переконатися, що ваше цільове значення тепер рухається за допомогою смуги прокрутки
Це воно!! Тепер, коли ви переміщуєте смугу прокрутки та змінюєте цільові значення, смуги, які відповідають цілі, автоматично виділяться іншим кольором.
Спробуйте самі… Завантажте файл
