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

С помощью обычных средств самого популярного редактора таблиц MS Excel можно составить наглядную диаграмму работы над проектами, которая поможет отследить, как продвигается каждая задача и сколько ещё осталось сделать.

диаграмма в Excel

EDHAR/Shutterstock.com

Если вы управляете проектами, важно всегда знать, сколько у вас задач и к какому сроку их нужно выполнить. А если составить наглядную диаграмму, будет проще объяснять ключевые моменты жизни проектов команде, участвующей в работе, а также другим заинтересованным лицам.

Для управления проектами часто используются специальные программы, но и в привычном MS Excel можно создать понятное и доступное расписание.

Диаграмма работы над проектами показывает, каким образом события и задачи связаны друг с другом во времени. Хорошо составленная диаграмма отражает важную информацию о выполнении конкретного задания и о развитии проекта в целом, помогает увидеть общую картину.

Чтобы создать такую диаграмму в MS Excel (версии 2007 года и далее), нужно сделать всего 10 шагов.

Что есть что на этой диаграмме? Ключевые сведения показаны с помощью разноцветных линий и планок:

  • линия текущего дня — красная вертикальная линия, которая показывает, где вы находитесь сегодня;
  • сроки задач — серые горизонтальные планки, которые показывают, сколько времени займёт выполнение каждой задачи;
  • готовность задач — зелёные горизонтальные линии, которые показывают, на какой стадии находится выполнение задачи и сколько ещё осталось сделать.

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

Подробную видеоинструкцию (на английском языке) можно посмотреть здесь, она займёт полчаса вашего времени.

Шаг 1. Начиная, думайте о результате

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

Шаг 2. Создайте таблицу с данными

Таблица с данными для диаграммы

Таблица с данными для диаграммы

Чтобы построить график, сначала нужно оформить таблицу. В примере проект разделён на четыре фазы, в каждой из которых есть задачи.

Сформируйте таблицу под графиком. На 30-й строке располагаются заголовки столбцов.

Разберёмся, что означают данные в таблице:

  • Фаза: «Фаза 1», «Фаза 2», «Фаза 3»,«Фаза 4» или «Сегодня». Показывает, какой стадии проекта соответствует событие.
  • Тип: «Фаза», «Задача», «Сегодня». Показывает тип события, разграничивая задачи и фазы исполнения проекта.
  • Дата начала: дата старта события.
  • Событие: показывает наименование события в диаграмме.
  • Сроки: показывает, сколько дней занимает выполнение задачи.
  • Готовность в процентах: демонстрирует степень выполнения для каждой задачи и фазы, 0% — задача не начата, 100% — завершена. Этот показатель для категории «Фаза» не связан с завершённостью задач и выставляется оценочно. Чтобы решить, на сколько процентов завершена та или иная фаза, нужно применять свои собственные правила.
  • Готовность в днях: срок, за который был достигнут показатель готовности события в процентах. У задачи, которая отнимает 10 дней и которая завершена на 50%, этот показатель будет равен пяти дням (10 дней × 50%).
  • Высота: значение высоты важно для оформления, потому что определяет, как высоко или низко будет событие отражаться в карте расписания. Значения можете указывать любые, но лучше брать небольшие числа, приближенные к нулю. В примере использован диапазон от +25 до −25. Если высота положительная, событие отразится на графике выше горизонтальной оси, если отрицательное — ниже оси.

Шаг 3. Создайте диаграмму с осями X и Y, добавьте в неё первые данные из столбца «Сроки»

Создаем диаграмму

Диаграмма

Пора создать диаграмму, чтобы визуализировать данные о событиях из таблицы, сделанной на предыдущем этапе. Используйте точечную диаграмму с осями X и Y, потому что она позволяет свободно располагать базовые координаты на обеих осях.

Следуйте инструкции:

  • Выберите в основном меню вкладку «Вставка».
  • Далее выберите «Диаграммы» → «Точечная» → «Точечная  с маркерами», перед вами появится пустая диаграмма.
  • Зацепите края диаграммы и измените её размеры и положение так, чтобы поле диаграммы охватывало диапазон от ячейки B4 до ячейки К26 (нажмите и удерживайте клавишу Alt, чтобы выровнять область диаграммы точно по границам ячеек).
  • Кликните правой кнопкой мышки на диаграмме и выберите пункт меню «Выбрать данные».

Добавьте ряд данных в диапазон, нажимая на кнопку «Добавить» в открывшемся диалоговом окне.

  1. Для изменения названия «Имя ряда» поставьте в соответствующем поле значение ячейки E30 «Сроки».
  2. Для значения оси X выберите соответствующее поле и выделите ячейки C33:C46 «Дата начала».
  3. Для значения оси Y выберите соответствующее поле и выделите ячейки H33:H46 «Высота».
  4. Нажмите ОК, чтобы закрыть окно добавления данных.
  5. Нажмите ОК, чтобы закрыть окно выбора данных.

Эти действия дадут нам простую точечную диаграмму с автоматическим форматированием.

Сделаем маркеры событий зелёными:

  • Правой кнопкой мыши кликните на любом выбранном маркере данных, выберите пункт меняю «Формат ряда данных».
  • В открывшемся списке слева выберите вкладку «Параметры маркера», затем выберите встроенный тип маркера «Кристалл». Увеличьте размер до 10 пт.
  • На вкладке «Заливка маркера» выберите сплошную заливку. Измените цвет заливки на зелёный.

Теперь у нас есть точечная диаграмма, отражающая сроки. Пока зелёные маркеры демонстрируют только старт задач.

Измените название графика: дважды щёлкните на текстовом поле с названием и введите своё наименование.

Мы ещё вернемся к параметрам сроков в пятом шаге, добавим горизонтальные планки вдоль оси X, а пока продолжим форматирование диаграммы.

Шаг 4. Приводим в порядок внешний вид диаграммы и добавляем наименования событий

Таблица

Таблица с форматированием

Избавляемся от того, что в диаграмме нам не понадобится.

Ось Y. Спрячьте вертикальную ось, потому что она не несёт информационной нагрузки. Выделите диаграмму, в основном меню на вкладке «Работа с диаграммами» выберите пункт «Макет». Затем выберите «Оси» → «Основная вертикальная ось» → «Не показывать».

Горизонтальные направляющие. Они тоже бесполезны. Выделите диаграмму, в основном меню перейдите на вкладку «Макет», затем выберите пункты «Сетка» → «Горизонтальные линии сетки по основной оси» → «Не показывать».

Легенда диаграммы. Мы потом её заменим на более красивую, а пока отключаем: «Макет» → «Легенда» → «Нет».

Напоследок отображаем подписи данных для каждого маркера. В основном меню на вкладке «Макет» выбираем «Подписи данных» → «Слева».

Как изменить данные в подписи маркеров

Вводить данные о каждом событии в диаграмме долго и трудно, потому что нужно выбирать каждый маркер отдельно и переименовывать его.

Но этот процесс можно ускорить в четыре раза:

  • Кликните на подпись первого маркера (левый верхний угол диаграммы), чтобы выбрать все значения сразу. Не нажимайте на зелёный маркер, так вы выделите другие объекты!
  • Кликните повторно на подпись маркера, чтобы отредактировать единичное наименование.
  • В поле ввода формулы наберите символ =.
  • Кликните на ячейку D33, в ней записано событие «Фаза 1», нажмите Enter.
  • Повторите первые четыре шага для оставшихся подписей к каждому маркеру.

У каждого маркера своё значение

Как закрепить области

Полезно будет закрепить области на строке 28, чтобы диаграмму можно было видеть всегда при работе с документом. Первые 27 строк останутся перед глазами, даже если вы будете прокручивать остальные строки.

Чтобы закрепить области :

  • Выделите ячейку А28.
  • Выберите вкладку «Вид» в основном меню.
  • Выберите «Окно» → «Закрепить области».
  • В выпадающем меню выберите пункт «Закрепить области».

Над 28-й строкой появится чёрная горизонтальная линия. Всё, что находится выше, теперь закреплено, и при прокрутке будут двигаться только нижние строки.

Шаг 5. Добавьте планки погрешностей для отображения сроков, чтобы превратить диаграмму в наглядное расписание

Диаграмма

Диаграмма с планками

Горизонтальные планки погрешностей на диаграмме покажут, сколько времени занимает работа над каждым заданием. Чтобы их сделать:

  • Выделите график.
  • В основном меню «Работа с диаграммами» выберите вкладку «Макет».
  • В выпадающем списке в левом верхнем углу (область построения) выберите ряд данных «Сроки».
  • На вкладке «Макет» выберите пункт «Планки погрешностей» → «Дополнительные параметры планок погрешностей». Откроется диалоговое окно.
  • В выпадающем списке в левом верхнем углу выберите элемент форматирования диаграммы «Планки погрешностей по оси X».
  • В диалоговом окне для изменения горизонтальных планок поставьте флажок на значении вывода «Плюс». «Конечный стиль» → «Без точки».
  • В списке «Величина погрешности» выберите пункт «Пользовательские» и нажмите кнопку «Укажите значение». В открывшемся новом окне «Настраивание планки погрешностей» укажите «Положительное значение ошибки», для этого в соответствующем поле поставьте курсор и выделите диапазон ячеек с E33 до E74. Нажмите ОК.

Теперь от зелёных маркеров вправо отходят серые линии, они показывают, сколько времени необходимо затратить на то или иное задание. Их нужно отформатировать для большей наглядности:

  • В окне «Формат планок погрешностей» перейдите к вкладке «Цвет линий». Выберите пункт «Сплошная линия». Сделайте заливку серого цвета.
  • На вкладке «Тип линии» увеличьте ширину линии до 4 пт.

Чтобы лучше ориентироваться в диаграмме, нужно провести тонкие вертикальные линии вниз от маркеров. Для этого в выпадающем списке в левом верхнем углу выберите «Планки погрешности по оси Y». В диалоговом окне отобразятся вертикальные планки погрешностей. Выберите направление «Минус», «Конечный стиль» → «Без точки», «Величина погрешности» → «Относительное значение», введите в это поле значение 100%. Выберите цвет и толщину линий самостоятельно.

Шаг 6. Добавьте значение готовности к диаграмме

Новые данные

Добавление новых данных

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

Если задача занимает 10 дней, а выполнена на 50%, планка, отражающая завершённость, будет заполнена наполовину. Это показатель для отслеживания работы, в котором не учитываются выходные дни и праздники. Чтобы добавить новую информацию в диаграмму, следуйте инструкции ниже.

  • Кликните правой кнопкой мыши на области диаграммы, в контекстном меню выберите пункт «Выбрать данные».
  • Нажмите кнопку «Добавить», в появившемся окне определите «Имя ряда», выделив ячейку G30. Диапазон данных для осей X и Y выберите такой же, как в шаге № 3.
  • На диаграмме появятся красные маркеры, которые спрячут под собой зелёные.

Шаг 7. Форматируем показатели новых данных

Чтобы диаграмма отражалась в едином стиле, а данные не наслаивались друг на друга, форматируем маркеры:

  • В выпадающем списке в левом верхнем углу выберите «Ряд Готовность». Сразу под выпадающим списком находится кнопка «Формат выделенного фрагмента». Нажмите её, откроется диалоговое окно.
  • Измените параметры маркера. Выберите тип формы «Кристалл», установите размер 10 пт, заливку маркера выберите сплошную, зелёного цвета. Закройте диалоговое окно.
  • Не снимая выделения маркеров, нажмите в основном меню на вкладке «Макет» кнопку «Подписи данных», выберите в появившемся меню пункт «Не показывать».

Шаг 8. Добавьте планки погрешностей, чтобы отследить процент завершённости задач

Выполнение задач

Диаграмма с процентом выполнения задач

Теперь нужно добавить новые планки, чтобы с первого взгляда видеть, на каком этапе находится выполнение задач.

  • Снова выберите «Ряд Готовность» в выпадающем списке слева. Нажмите в основном меню кнопку «Планки погрешностей». Выберите в выпадающем меню пункт «Дополнительные параметры». Откроется диалоговое окно. Установите для вертикальных планок фиксированное значение, вписав в соответствующее поле ноль.
  • Переключитесь на параметры планок, расположенных параллельно оси Х. Установите направление «Плюс». «Конечный стиль» → «Без точки», «Величина погрешности» → «Пользовательские». В поле диалогового окна «Положительное значение ошибки» укажите диапазон ячеек G33-G47.
  • Поменяйте цвет линий на зелёный, выбрав сплошную заливку. Установите ширину линии 7 пт.

Горизонтальные зелёные линии показывают, насколько выполнены задачи: частично или полностью .

Шаг 9. Добавьте вертикальную линию, обозначающую текущий день

Красная линия обозначает текущий день

Красная линия обозначает текущий день

Красная линия показывает, какой сегодня день, и даёт понять, насколько проект в целом укладывается в сроки.

  • Правой кнопкой вызовите контекстное меню для диаграммы. Выберите пункт «Выбрать данные».
  • Нажмите кнопку «Добавить», впишите в поле «Имя ряда» слово «Сегодня». Для значения оси Х введите диапазон ячеек C31-C32, для значения оси Y — Н31-Н32.

На графике появилось два новых маркера. Обозначьте их: выделите подписи данных и напишите в строке формул слово «Сегодня». Кликните на подписи правой кнопкой и выберите в контекстном меню пункт «Формат данных». Поставьте положение подписи «Сверху».

  • Перейдите в основном меню на вкладку «Макет».
  • Выберите в выпадающем списке слева в верхнем углу пункт «Ряд Сегодня».
  • Добавьте планки погрешностей с дополнительными параметрами, следуя инструкции, как в пятом шаге. Вам потребуется планка по вертикальной оси, с направлением «Минус», конечным стилем «Без точки», относительным значением 100%.

У вас появилась вертикальная линия, обозначающая текущий день. Измените цвет линии на красный, увеличьте размер до 3 пт.

В образце текущая дата зафиксирована, так что она не будет меняться. Но для проектов, которые активно разрабатываются, рекомендуется другой способ учёта данных. Для этого поставьте в ячейку B2 дату при помощи введения формулы = TODAY() и укажите в диалоговом окне выбора данных для значения оси Х ячейку B2 вместо диапазона, который вы указывали раньше.

Шаг 10. Добавьте текстовые блоки

Итог

Так выглядит диаграмма в итоге

Теперь диаграмме не хватает только пояснений к условным обозначениям, чтобы она была понятна всей команде.

  • Выделите диаграмму, перейдите в основном меню на вкладку «Вставка». Выберите пункт «Надпись».
  • Расположите область надписи в правом верхнем углу диаграммы. Выделите границу надписи правой кнопкой мыши, в контекстном меню выберите пункт «Формат фигуры». В открывшемся окне выберите тип заливки «Сплошная», цвет серый. Отформатируйте надпись. Можете вставлять любое количество блоков.

Теперь вы сможете сделать диаграмму за 20−30 минут, используя только всеми любимый редактор MS Excel.

www.launchexcel.com
nickinua
2015-04-16 10:38:05
как сделать диаграмму Ганта проще http://www.planetaexcel.ru/techniques/4/108/ как сделать диаграмму Ганта ещё проще http://www.planetaexcel.ru/techniques/4/74/
Dmitry Honcharenko
2015-04-16 15:09:23
Гант в Экселе - это культ карго: вроде и диаграмма есть, а работать с ней нереально. Диаграмма должна быть живой, ведь в ходе работы задачи в ней придется перемещать, удлинять и укорачивать, связывать и пересвязывать между собой - в экселе все это жутко неудобно. Другое дело, когда нужно сделать "моментальный снимок" Ганта, например чтобы отправить его клиенту - тогда реально удобно засунуть Ганта в Excel, в ПланФиксе есть такой вариант: https://blog.planfix.ru/diagramma-ganta-v-excel-iz-planfiksa/ Кстати, шаблон для отрисовки Ганта из таблички с задачами. который там приатачен, можно использовать и сам по себе, в том числе и для экспорта Ганта из других систем.
Vladimir Popov
2015-04-16 17:45:11
https://teamgantt.com/gantt/home/
Mikhail Petrakovich
Mikhail Petrakovich
2015-04-16 18:34:46
Используйте MS Project и будет счастье.
id135367201
2015-04-17 18:30:27
Ха-а! Прикольно круто! Мне такое в голову не приходило. Занятное упражнение на Excel Но практического не вижу... Если нет денег на MS Project, то построить диаграмму Гантта можно во многих бесплатных программах.
id135367201
2015-04-17 18:42:44
Действительно, иногда приходит Гантт переносить в Excel. У меня для этого есть шаблон на таблицах. Пойду попробую сделать на диаграмме по видео Планфикса...