Как построить диаграмму план факт в эксель
Данный вопрос, в принципе, не сложен, но иногда возникают вопросы — как можно наглядно показать выполнен ли план или нет?
Представьте себе небольшую таблицу.
В последней строке уже есть необходимые данные. Со знаком минус — план не выполнен, со знаком плюс — выполнен. Можно сделать акцент на ней, но мы пойдём другим путём. Воспользуемся графическими объектами — гистограммой и графиком, где наглядно будет показан процесс.
Выделим всю таблицу, на вкладку «Вставка», в блоке кнопок «Диаграммы» выберем вариант «Гистограмма с группировкой».
На гистограмме уже видно, что за периоды: май, август, ноябрь, декабрь, — план не выполнен (отрицательное значение). Нужно выделить данные этого блока визуально, так как серый цвет не очень заметен:
- щелкнуть на ряд данных (разница);
- открыть вкладку «Макет»;
- выбрать кнопку «Заливка»;
- указать цвет (я установил красный).
Видим, столбцы стали более заметными, но они не разделены по смыслу: выполнен план или нет.
В таком случае лучше пойти по пути построения графиков.
- удаляем гистограмму;
- выделяем таблицу;
- переходим на вкладку «Вставка»;
- в блоке «Диаграммы» щёлкаем кнопку «Графики»;
- выбираем вариант «График с маркерами».
График необходимо улучшить. Удалим линию «Разница», для этого щёлкаем на серую линию и нажимаем кнопку «Delete».
Переходим на вкладку «Конструктор», слева нажимаем кнопку «Добавить элемент диаграммы». В выпавшем списке выбираем «Полосы повышения и понижения», в выпавшем списке снова выбираем «Полосы повышения и понижения».
Диаграмма примет следующий вид.
На графике появились вертикальные прямоугольники — белый (план выполнен), тёмно-серый (план не выполнен). Зальём их соответственно в зелёный и красный цвета. Щёлкаем на белый прямоугольник — выделятся все. На вкладке «Формат» щёлкаем кнопку «Заливка фигуры», выбираем зелёный цвет.
Точно также поступаем с серым прямоугольником, только цвет выбираем красный.
Добавим подписи к данным и расположить их соответственно столбцам данных.
На вкладке «Конструктор» щелкаем кнопку «Добавить элемент диаграммы», в выпадающем списке выбираем «Подписи данных», далее «Сверху». Диаграмма будет вот такой.
Вот и всё, полосы понижения и повышения отлично подходят для визуализации любых данных в положительном и отрицательном диапазоне.
Редкий менеджер в своей практике не сталкивается с необходимостью наглядного представления достигнутых результатов по сравнению с запланированными изначально. В разных компаниях я встречал много подобных диаграмм, называющихся "План-Факт", "Actual vs Budget" и т.д. Иногда их строят примерно так:
Неудобство такой диаграммы в том, что зритель должен попарно сравнивать столбцы плана и факта друг с другом, пытаясь удержать всю картину в голове, и гистограмма здесь, по-моему, не лучший вариант. Если уж и строить такую визуализацию, то однозначно нагляднее использовать графики для плана и факта. Но тогда перед нами встает задача наглядного попарного сравнения точек за одинаковые периоды и выделения разницы между ними. Давайте попробуем применить несколько удобных техник для этого.
Способ 1. Полосы повышения-понижения
Это наглядные прямоугольники, соединяющие попарно точки графиков плана и факта на нашей диаграмме. Причем их цвет зависит от того, выполнили мы план или нет, а размер показывает - на сколько:
Включаются такие полосы на вкладке Конструктор - Добавить элемент диаграммы - Полосы повышения/понижения (Design - Add Chart Element - Up/Down Bars) в Excel 2013 или на вкладке Макет - Полосы повышения-понижения (Layout - Up-Down Bars) в Excel 2007-2010. По умолчанию они будут черно-белые, но можно легко изменить их цвет, щелкнув по ним правой кнопкой мыши и выбрав команду Формат полос повышения/понижения (Format Up/Down Bars) . Очень рекомендую использовать полупрозрачную заливку, т.к. сплошная закрывает сами исходные графики.
К сожалению нет легкого встроенного способа регулировать ширину полос - для этого придется воспользоваться небольшим трюком.
Само собой, параметром (30) можно поиграться, чтобы получить нужную вам ширину экспериментальным путем.
Способ 2. Диаграмма с заливкой зоны между линиями плана и факта
Этот способ предполагает наглядную заливку (можно со шриховкой, например) области между графиками плана и факта:
Весьма эффектно, не так ли? Давайте попробуем это реализовать.
Для начала добавьте к нашей таблице еще один столбец (назовем его, допустим, Разница), где посчитаем формулой разность между фактом и планом:
Теперь выделим одновременно столбцы с датами, планом и разницей (удерживая Ctrl) и построим диаграмму с областями с накоплением, используя вкладку Вставка (Insert) :
На выходе должно получиться примерно так:
Следующим шагом выделим ряды План и Факт, скопируем их (Ctrl+C) и добавим в нашу диаграмму вставкой (Ctrl+V) - в нашем "бутерброде в разрезе" сверху должны появиться два новых "слоя":
Теперь переключим тип диаграммы для этих двух добавленных слоев в график. Для этого выделите по очереди каждый ряд, щелкните по нему правой кнопкой мыши и выберите команду Изменить тип диаграммы для ряда (Change Series Chart Type) . В старых версиях Excel 2007-2010 дальше можно выбрать нужный тип диаграммы (График с маркерами), а в новом Excel 2013 появится диалоговое окно со всеми рядами, где нужный тип выбирается для каждого ряда из выпадающих списков:
После нажатия на ОК увидим уже похожую на то, что нам нужно картину:
Легко сообразить, что осталось только выделить синюю область и поменять у нее цвет заливки на прозрачный Нет заливки (No Fill) . Ну, и навести общий блеск: добавить подписи, заголовок, удалить лишние элементы в легенде и т.д.
Таблица план-факт
Для лучшей визуализации дополнительно можно окрашивать ячейку или шрифт текста с отклонением, например, в красный и зеленый цвета.
Диаграмма план-факт
Предположим, что для план-факт графика у нас имеются помесячные данные по продажам (фактические и планируемые), а также отклонение между фактом и планом:
Разберем 2 различных варианта построения план-факт диаграмм.
Вариант 1. Добавление дополнительного ряда в виде гистограммы
Выделяем диапазон ячеек A1:M4 и добавляем стандартный график с маркерами (на панели вкладок выбираем Вставка -> График -> График с маркерами):
Добавляя подпись данных к рядам получаем окончательный вид план-факт графика:
Вариант 2. Полосы повышения-понижения
Еще одним вариантом план-факт анализа является диаграмма с использованием свойств полосы повышения-понижения.
Повторяем действия из первого примера, выделяем диапазон ячеек A1:M3 (без отклонения) и строим график с маркерами:
Во вкладке Конструктор (Excel 2013 и старше) или Макет (Excel 2007-2010) добавляем Полосы повышения-понижения:
Между линией факта и плана появились прямоугольные полосы (полосы повышения-понижения), которые показывают изменение между линиями.
При этом в зависимости от знака отклонения они окрашиваются в разные цвета (в данном примере, если факт больше плана, то в черный и наоборот, если факт меньше плана, то в белый).
Изменим цвет полосы повышения на зеленый, а полосы понижения на красный, а также сделаем их полупрозрачными, чтобы полосы не наезжали на линии графика (нажимаем правой кнопкой мыши на полосу и выбираем Формат полос повышения/понижения):
Подробно ознакомиться с шаблонами диаграмм из разобранных примеров — скачать пример.
Инструкция для Excel
- Нарисуйте в Excel гистограмму и задайте ее цвета.
- Совмещаем на диаграмме разные типы диаграмм: меню Конструктор → Изменить тип диаграммы.
Выберите тип диаграммы «Комбинированная». Для плана выберите график, а для факта – гистограмму с группировкой.
- Переходим к форматированию. Щелкните по линии плана правой кнопкой мышки и выберите в меню Формат ряда данных.
- Настраиваем параметры ряда для плана. Делаем линию невидимой – ставим галочку рядом с надписью «Нет линий».
Настраиваем вид target-черточек с помощью маркеров: тип маркера – встроенный в виде линии, со сплошной заливкой и без границ.
- Теперь можно сделать столбцы с фактом шире, уменьшив боковой зазор в параметрах ряда. И настроить название и шрифты для осей. Готово!
Инструкция для Power BI
- Выберите визуализацию «График и гистограмма с группировкой». Заполните данные для построения графика.
В параметрах оси Х отключите сцепление меток.
В результате получится ось нормального вида:
Построение диаграммы в Microsoft Excel по таблице – основной вариант создания графиков и диаграмм другого типа, поскольку изначально у пользователя имеется диапазон данных, который и нужно заключить в такой тип визуального представления.
В Excel составить диаграмму по таблице можно двумя разными методами, о чем я и хочу рассказать в этой статье.
Способ 1: Выбор таблицы для диаграммы
Откройте необходимую таблицу и выделите ее, зажав левую кнопку мыши и проведя до завершения.
Вы должны увидеть, что все ячейки помечены серым цветом, значит, можно переходить на вкладку «Вставка».
Там нас интересует блок «Диаграммы», в котором можно выбрать одну из диаграмм или перейти в окно с рекомендуемыми.
Откройте вкладку «Все диаграммы» и отыщите среди типов ту, которая устраивает вас.
Справа отображаются виды выбранного типа графика, а при наведении курсора появляется увеличенный размер диаграммы. Дважды кликните по ней, чтобы добавить в таблицу.
Предыдущие действия позволили вставить диаграмму в Excel, после чего ее можно переместить по листку или изменить размер.
Дважды нажмите по названию графика, чтобы изменить его, поскольку установленное по умолчанию значение подходит далеко не всегда.
Не забывайте о том, что дополнительные опции отображаются после клика правой кнопкой мыши по графику. Так вы можете изменить шрифт, добавить данные или вырезать объект из листа.
Для определенных типов графиков доступно изменение стилей, что отобразится на вкладке «Конструктор» сразу после добавления объекта в таблицу.
Как видно, нет ничего сложного в том, чтобы сделать диаграмму по таблице, заранее выбрав ее на листе. В этом случае важно, чтобы все значения были указаны правильно и выбранный тип графика отображался корректно. В остальном же никаких трудностей при построении возникнуть не должно.
Способ 2: Ручной ввод данных
Преимущество этого типа построения диаграммы в Экселе заключается в том, что благодаря выполненным действиям вы поймете, как можно в любой момент расширить график или перенести в него совершенно другую таблицу. Суть метода заключается в том, что сначала составляется произвольная диаграмма, а после в нее вводятся необходимые значения. Пригодится такой подход тогда, когда уже сейчас нужно составить график на листе, а таблица со временем расширится или вовсе изменит свой формат.
На листе выберите любую свободную ячейку, перейдите на вкладку «Вставка» и откройте окно со всеми диаграммами.
В нем отыщите подходящую так, как это было продемонстрировано в предыдущем методе, после чего вставьте на лист и нажмите правой кнопкой мыши в любом месте текущего значения.
Из появившегося контекстного меню выберите пункт «Выбрать данные».
Задайте диапазон данных для диаграммы, указав необходимую таблицу. Вы можете вручную заполнить формулу с ячейками или кликнуть по значку со стрелкой, чтобы выбрать значения на листе.
В блоках «Элементы легенды (ряды)» и «Подписи горизонтальной оси (категории)» вы самостоятельно решаете, какие столбы с данными будут отображаться и как они подписаны. При помощи находящихся там кнопок можно изменять содержимое, добавляя или удаляя ряды и категории.
Обратите внимание на то, что пока активно окно «Выбор источника данных», захватываемые значения таблицы подсвечены на листе пунктиром, что позволит не потеряться.
По завершении редактирования вы увидите готовую диаграмму, которую можно изменить точно таким же образом, как это было сделано ранее.
Вам остается только понять, как сделать диаграмму в Excel по таблице проще или удобнее конкретно в вашем случае. Два представленных метода подойдут в совершенно разных ситуациях и в любом случае окажутся полезными, если вы часто взаимодействуете с графиками во время составления электронных таблиц. Следуйте приведенным инструкциям, и все обязательно получится!
Читайте также: