Как создать диаграмму выполнения плана в Excel

Microsoft Excel открывает перед пользователем множество возможностей для работы с электронными таблицами и визуализации данных в них. Один из методов графического отображения значений в выделенном диапазоне – создание диаграммы. В рамках этого материала я покажу решение для тех юзеров, кому нужно создать диаграмму выполнения плана, чтобы удобно просматривать недовыполнение, перевыполнение и изначальное значение по каждому выполняющему этого плана (это может быть сотрудник, учащийся или любая другая персона).

Подготовка таблицы и создание вспомогательных столбцов

Минимальный необходимый набор значений в таблице для создания диаграммы выполнения плана: перечень сотрудников, сам план и итог его выполнения. Отталкиваясь от этого, уже можно добавить необходимое количество вспомогательных столбцов с расчетами, вокруг которых и будет строиться диаграмма. Это основной этап подготовки, поскольку от него зависит правильность построения графиков.

В первую очередь вашу таблицу нужно переформатировать в умную, если это еще не было сделано ранее. Такое действие поможет организовать автоматическую подстановку всех данных, а также обеспечит автоматическое расширение и добавление новых значений в диаграмму, если вы будете пополнять список сотрудников время от времени. Для этого выделите всю таблицу и разверните «‎Форматировать как таблицу». Выберите для себя подходящий вариант оформления.
На следующем скриншоте вы видите стандартное конвертирование в умную таблицу. Именно такой формат и будет использоваться для дальнейшего расширения столбцов и заполнения данных.
Создайте первый столбец, который будет отвечать за процент отклонения от плана. Этот столбец не участвует в расчетах, а только нужен для отображения информации‎, которую мы еще скомпонуем при помощи дополнительного столбца.
В качестве формулы для него задайте =B2/C2-1. Такая формула делит итог на план и выводит значение в процентах благодаря тому, что вы указали -1 или -100% в конце.
Изначально формат ячеек не совсем подходит, поскольку отображает десятичные дроби. Для этого выделите ячейки и переведите их формат в процентный.
Если нужно добавить знаки после запятой, чтобы получить более детальные сведения, используйте кнопки в том же меню редактирования формата. В итоге у вас получится столбик, в котором показан процент отклонения от плана как положительный, так и отрицательный.
Второй столбец будет отображать имя сотрудника и процент отклонения от плана вместе под самим графиком. Назовите его соответствующе и переходите далее.
В качестве формулы сначала введите =A2&, где A2 – имя сотрудника, а & – соединительный знак для склейки нескольких введенных значений.
Формулу нужно доработать до вида =A2&(СИМВОЛ(10)&(ТЕКСТ(D2;"0%"))). Функция СИМВОЛ здесь используется для добавления сочетания клавиш Alt + Enter для переноса новой строки, а ТЕКСТ – для отображения нашего отклонения от плана в процентах, а не в изначальном виде десятичной дроби.
В итоге эта функция обеспечит вывод фамилии и рядом с ней – процент по выполнению плана. Это позволит без лишних трудностей далее добавить такую информацию в нашу диаграмму.
Следующий вспомогательный столбец с формулой уже немного проще. Он нужен для подсчета базового значения, то есть в графике будет выведен либо стандартный план и уже область перевыполнения, либо итог и выше него – недостача по плану. Выглядит формула как =МИН(B2;C2). Она рассчитывает минимальное значение из указанного диапазона и выводит его в качестве результата.
Следующий вспомогательный столбец отвечает за недостачу по выполненному плану. Если такая есть, он выводит разницу, если нет – показывает пустое значение.
Для этого понадобится формула, которая имеет вид =ЕСЛИ(B2>C2;B2-C2;НД()). В ЕСЛИ мы считаем план и итог, и если план получается больше итогового значения, то отнимаем его и выводим недостачу. В противном случае используем функцию НД(), которая выведет в ячейке пустое значение, не отображаемое в диаграмме.
Примерно по такому же принципу начинаем строить формулу в последнем вспомогательном столбце, где снова считаем =ЕСЛИ(C2>B2), то есть если итоговая сумма больше плана работ.
Дописываем формулу до =ЕСЛИ(C2>B2;C2-B2;НД()), указывая действие при соответствии условия или выводя снова пустое значение.

Обратите внимание на то, что в текстовом описании формул я использовал номера ячеек, а на скриншотах – их названия в умной таблице. Это поможет вам разобраться в том, какие данные используются в расчетах: подставляйте вместо моих ячеек свои для получения нужного результата. Если с первого раза что-то не понятно, попробуйте прочитать инструкцию еще раз, сравнить вводимые данные и посмотреть на иллюстрации, пытаясь понять, за что какое значение отвечает.

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

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Создание диаграммы выполнения плана

Остался самый простой этап – построить диаграмму по нашей таблице. Происходит это практически в автоматическом режиме, поскольку все значения уже подсчитаны и остается только визуализировать их. Давайте пошагово разберемся с этим процессом и дальнейшим редактированием диаграммы.

Выделите последние четыре вспомогательных столбца таблицы, начиная от подписей для графиков и заканчивая перевыполнением плана. Перейдите на вкладку «‎Вставка» и раскройте список всех диаграмм.
Выберите раздел «‎Гистограмма» и найдите гистограмму с накоплением. Этот вариант как раз больше всего подходит для нашей задачи.
После добавления диаграммы вы уже видите, что она прекрасно работает. Сейчас синим показано итоговое значение, желтым – недостача по плану, а серым – перевыполнение.
Можете поменять цвет каждого блока на удобный, щелкнув по нему правой кнопкой мыши и из контекстного меню выбрав новый вариант заливки.
У вас может получиться что-то похожее, как на следующем скриншоте, или свой вариант.
Немного не хватает информативности на данных графиках, поэтому можете кликнуть по кнопке с плюсом справа и добавить элемент диаграммы «‎Метка данных».
Теперь мы видим, какая недостача или перевыполнение по плану у каждого сотрудника, числовое значение итога и саму недостачу с перевыполнением.
Как уже было сказано выше, таблица и диаграмма будут расширяться автоматически при вводе новых данных. Это видно на следующем изображении. Я добавил нового сотрудника, после чего график со всеми необходимыми данными прогрузился автоматически.

Я постарался максимально в деталях и просто описать процесс подготовки таблицы и создания по ней диаграммы. Надеюсь, вы поняли каждый этап и без проблем смогли повторить все действия, оптимизировав диаграмму и таблицу под свои цели.