Реклама ООО Таймвэб
Реклама ООО Таймвэб
Реклама ООО Таймвэб

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

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

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

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

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

  1. В первую очередь вашу таблицу нужно переформатировать в умную, если это еще не было сделано ранее. Такое действие поможет организовать автоматическую подстановку всех данных, а также обеспечит автоматическое расширение и добавление новых значений в диаграмму, если вы будете пополнять список сотрудников время от времени. Для этого выделите всю таблицу и разверните «‎Форматировать как таблицу». Выберите для себя подходящий вариант оформления.Форматирование таблицы в умную для создания диаграммы выполнения плана в Microsoft Excel

  2. На следующем скриншоте вы видите стандартное конвертирование в умную таблицу. Именно такой формат и будет использоваться для дальнейшего расширения столбцов и заполнения данных.Результат форматирования таблицы в умную для создания диаграммы выполнения плана в Microsoft Excel

  3. Создайте первый столбец, который будет отвечать за процент отклонения от плана. Этот столбец не участвует в расчетах, а только нужен для отображения информации‎, которую мы еще скомпонуем при помощи дополнительного столбца.Создание первого вспомогательного столбца для создания диаграммы выполнения плана в Microsoft Excel

  4. В качестве формулы для него задайте =B2/C2-1. Такая формула делит итог на план и выводит значение в процентах благодаря тому, что вы указали -1 или -100% в конце.Ввод формулы первого вспомогательного столбца для создания диаграммы выполнения плана в Microsoft Excel

  5. Изначально формат ячеек не совсем подходит, поскольку отображает десятичные дроби. Для этого выделите ячейки и переведите их формат в процентный.Перевод формата ячейки для создания диаграммы выполнения плана в Microsoft Excel

  6. Если нужно добавить знаки после запятой, чтобы получить более детальные сведения, используйте кнопки в том же меню редактирования формата. В итоге у вас получится столбик, в котором показан процент отклонения от плана как положительный, так и отрицательный.Добавление знаков после запятой в столбце для создания диаграммы выполнения плана в Microsoft Excel

  7. Второй столбец будет отображать имя сотрудника и процент отклонения от плана вместе под самим графиком. Назовите его соответствующе и переходите далее.Создание столбца с подписями для создания диаграммы выполнения плана в Microsoft Excel

  8. В качестве формулы сначала введите =A2&, где A2 – имя сотрудника, а – соединительный знак для склейки нескольких введенных значений.Ввод формулы столбца с подписями для создания диаграммы выполнения плана в Microsoft Excel

  9. Формулу нужно доработать до вида =A2&(СИМВОЛ(10)&(ТЕКСТ(D2;"0%"))). Функция СИМВОЛ здесь используется для добавления сочетания клавиш Alt + Enter для переноса новой строки, а ТЕКСТ – для отображения нашего отклонения от плана в процентах, а не в изначальном виде десятичной дроби. Завершение создание столбца с подписями для создания диаграммы выполнения плана в Microsoft Excel

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

  11. Следующий вспомогательный столбец с формулой уже немного проще. Он нужен для подсчета базового значения, то есть в графике будет выведен либо стандартный план и уже область перевыполнения, либо итог и выше него – недостача по плану. Выглядит формула как =МИН(B2;C2). Она рассчитывает минимальное значение из указанного диапазона и выводит его в качестве результата.Столбец с базовыми значениями для создания диаграммы выполнения плана в Microsoft Excel

  12. Следующий вспомогательный столбец отвечает за недостачу по выполненному плану. Если такая есть, он выводит разницу, если нет – показывает пустое значение.Создание столбца недостачи для создания диаграммы выполнения плана в Microsoft Excel

  13. Для этого понадобится формула, которая имеет вид =ЕСЛИ(B2>C2;B2-C2;НД()). В ЕСЛИ мы считаем план и итог, и если план получается больше итогового значения, то отнимаем его и выводим недостачу. В противном случае используем функцию НД(), которая выведет в ячейке пустое значение, не отображаемое в диаграмме.Формула столбца недостачи для создания диаграммы выполнения плана в Microsoft Excel

  14. Примерно по такому же принципу начинаем строить формулу в последнем вспомогательном столбце, где снова считаем =ЕСЛИ(C2>B2), то есть если итоговая сумма больше плана работ.Запись формулы перевыполнения для создания диаграммы выполнения плана в Microsoft Excel

  15. Дописываем формулу до =ЕСЛИ(C2>B2;C2-B2;НД()), указывая действие при соответствии условия или выводя снова пустое значение.Полная формула перевыполнения для создания диаграммы выполнения плана в Microsoft Excel

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

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

Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Подписаться

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

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

  1. Выделите последние четыре вспомогательных столбца таблицы, начиная от подписей для графиков и заканчивая перевыполнением плана. Перейдите на вкладку «‎Вставка» и раскройте список всех диаграмм.Переход к вставке графика для создания диаграммы выполнения плана в Microsoft Excel

  2. Выберите раздел «‎Гистограмма» и найдите гистограмму с накоплением. Этот вариант как раз больше всего подходит для нашей задачи.Переход ко всем графикам для создания диаграммы выполнения плана в Microsoft Excel

  3. После добавления диаграммы вы уже видите, что она прекрасно работает. Сейчас синим показано итоговое значение, желтым – недостача по плану, а серым – перевыполнение.Вставка графика для создания диаграммы выполнения плана в Microsoft Excel

  4. Можете поменять цвет каждого блока на удобный, щелкнув по нему правой кнопкой мыши и из контекстного меню выбрав новый вариант заливки.Изменение цветов для создания диаграммы выполнения плана в Microsoft Excel

  5. У вас может получиться что-то похожее, как на следующем скриншоте, или свой вариант.Результат изменения цветов для создания диаграммы выполнения плана в Microsoft Excel

  6. Немного не хватает информативности на данных графиках, поэтому можете кликнуть по кнопке с плюсом справа и добавить элемент диаграммы «‎Метка данных».Добавление меток данных для создания диаграммы выполнения плана в Microsoft Excel

  7. Теперь мы видим, какая недостача или перевыполнение по плану у каждого сотрудника, числовое значение итога и саму недостачу с перевыполнением.Результат создания диаграммы выполнения плана в Microsoft Excel

  8. Как уже было сказано выше, таблица и диаграмма будут расширяться автоматически при вводе новых данных. Это видно на следующем изображении. Я добавил нового сотрудника, после чего график со всеми необходимыми данными прогрузился автоматически.Автоматическое расширение графика после создания диаграммы выполнения плана в Microsoft Excel

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

Наши постоянные авторы и читатели делятся лайфхаками, основанными на личном опыте. Полная свобода самовыражения.

Комментарии

Ваш баланс 10 ТК
1 ТК = 1 ₽
О том, как заработать и потратить Таймкарму, читайте в этой статье
Чтобы потратить Таймкарму, зарегистрируйтесь на нашем сайте
С помощью соцсетей
У меня нет аккаунта Зарегистрироваться
С помощью соцсетей
У меня уже есть аккаунт Войти
Инструкции по восстановлению пароля высланы на Ваш адрес электронной почты.
Пожалуйста, укажите email вашего аккаунта
Войти в Комьюнити
Регистрация в Комьюнити
Восстановление пароля