Предположим, вам необходимо отразить на одной диаграмме результаты продаж по нескольким отделам, но планы продаж (столбец Цель) у этих отделов отличаются, как и пограничные значения эффективности - "плохие продажи, средние и отличные" (столбцы В-D).
Обычная диаграмма с этим не справится, но есть хитрости в оформлении, которые помогут сделать график визуально понятным и показать все необходимые данные.
Для начала построим обычную гистограмму с накоплением для всех отделов:
Иногда при построении диаграммы оси на графике путаются, в нашем случае необходимо, чтобы каждый столбик соответствовал отделу. Для этого переходим на вкладку Конструктор и нажимаем "Строка/столбец":
Теперь каждому отделу соответствует свой столбец.
Следующим шагом размещаем все элементы графика на нужных местах. Сначала выделяем любой из маркеров данных ряда "Цель" (в таблице это F2:F5), и выбираем «Изменить тип диаграммы ряда». Для этого ряда выбираем тип - График с маркерами.
Ряд "Факт" располагаем по вспомогательной оси (выделите ряд данных, нажмите правой кнопкой, выберите Формат ряда данных и в открывшемся окне выберите "По вспомогательной оси"):
Удаляем вспомогательную ось справа, выделив ее и нажав кнопку Delete.
Далее необходимо настроить отображение ряда "Цель", выделяем его, нажав на любую из точек ряда и переходим в меню "Формат ряда данных". Нам необходимо удалить линии, соединяющие маркеры - переходим на вкладку заливка -> Линия -> Нет линий:
Для настройки ряда Цель на этой же вкладке переходим в раздел Маркер -> Параметры маркера -> Тип маркера "Тире" и размер 18, цвет заливки маркера меняем на бордовый и убираем заливку границ:
После настройки ряда данных "Цель" переходим к настройке ряда "Факт" и рядов с обозначением шкалы выплнения плана "плохо-средне-отлично":
- Выставляем боковой зазор 300%;
- Меняем заливку столбца "Факт" на темно-синий;
- Меняем заливку столбцов "плохо-средне-отлично" на логику светофора.
Вносим финальные правки в оформление диаграммы:
- Убираем название диаграммы;
- Меняем цвет шрифта;
- Убираем сетку;
- Меняем расположение легенды;
- Добавляем подпись к столбцу "Факт".
Подписывайтесь, чтобы первыми получать самые интересные публикации! Ещё больше полезных советов и лайфхаков Excel – в моем Telegram-канале!
Для удобства - статья навигатор по каналу: Путеводитель по каналу