Календарно-сетевой график в месяцах в MS EXCEL
Создадим в MS EXCEL простейший календарно-сетевой график с шагом равным 1 месяц, затем сделаем связи между отдельными работами (тип связи Конец-Начало), потом введем запаздывание, отобразим % выполнения и наконец — прогноз завершения работы в случае ее отклонения от плана.
Под «календарно-сетевым графиком» будем понимать график работ проекта, состоящий из 2-х частей:
- календарный план работ проекта (перечень работ с датами начала, окончания и связями между работами)
- соответствующая плану диаграмма Ганта.
Совет: В данной статье диаграмма выполнена с помощью Условного форматирования (далее УФ). Для тех, кто мало знаком с этим инструментом MS EXCEL рекомендуется прочитать вот эту статью про УФ.
Примечание: в другой статье про диаграмму Ганта данная диаграмма построена с помощью инструмента Диаграмма.
Простейший график
Построим график именно в месяцах, т.к. в EXCEL он получается очень компактным и проще соотнести длительность работы (в месяцах) с ее началом и концом (не требуется дополнительных вычислений, как например для недель, когда в разных месяцах укладывается разное количество недель). Конечно, такая детализация не очень удобна для коротких проектов, но, чтобы укрупненно продемонстрировать прогресс проектов (с длительностью от 1 года), такой подход вполне будет приемлемым.
Для построения простейшего графика, изображенного выше, нам потребуются следующие исходные данные:
- Длительность каждой работы
- Начало каждой работы (связи сделаем позже)
Месяц окончания каждой работы будем вычислять с помощью формулы =C14+D14-1 (к началу прибавим длительность) и скорректируем на 1, т.к. в начальный месяц работа также выполняется.
Чтобы чуть украсить график, можно для разных работ задать разные цвета. Для этого потребуется создать столько правил УФ, сколько разных цветов у нас будет на графике. Формула также чуть усложнится, также добавится столбец с кодом цвета.
Текущий месяц также можно отразить на графике с помощью УФ. На картинке выше текущий месяц выделен штриховкой — это июль 2022 года.
График со связями работ
Простейший график строить легко, но он не очень полезен. Чаще всего работы связаны между собой. Смоделируем связь Конец-Начало.
Добавим столбец с кодами работ и столбец для указания предшественника. Также в столбце, где указано начало работы вставим формулу:
Первая часть формулы просматривает столбец Предшественник («предшеств.», столбец С) и если находит пустую ячейку (нет предшественника, т.е. первая работа проекта), то устанавливает начало этой работы равной месяцу начала проекта (задается вручную в отдельной ячейке С6). Таких начальных работ может быть несколько.
Вторая часть формулы ИНДЕКС($F$14:$F$19;ПОИСКПОЗ(C14;$A$14:$A$19;0))+1 ищет в столбце А код предшественника, указанный в столбце С. После нахождения нужной строки, берет срок конца работы-предшественника (столбец F) и подставляет его в начало работы (столбец Е).
Примечание: Срок конца работы-предшественника (столбец F) вычисляется с помощью опять же столбца Е (также добавлен 1 месяц, чтобы работа-последователь начиналась без пересечения, т.е. с последующего месяца, как показано на диаграмме выше).
Обратите внимание, что циклической ссылки не возникает, хотя формально мы используем значения столбца Е (начало работы), чтобы вычислять опять же начало, но уже другой работы. Все дело в том, что это разные ячейки. Например, для вычисления начала работы с кодом 20 (ячейка Е15) мы использовали значение начала работы с кодом 10, которое находится в ячейке Е14. Главное — не делать перекрестных ссылок, например, когда предшественником работы 20 является работа 30, а у работы 30 предшественником является работа 20 (даже опосредованно, через другие работы).
График со связями работ и запаздыванием
Сделаем следующий шаг — введем запаздывание, т.е. работа-последователь теперь сможет начаться не сразу после окончания предшественника, а с некой заданной нами задержкой.
На рисунке выше показано как выглядит задержка 2 месяца работы 20, относительно работы 10. Формула, с помощью которой это реализовано, почти полностью совпадает с предыдущей формулой, которую мы использовали для вычисления начала работы-последователя
Теперь в столбце G у нас введена величина запаздывания, которая может быть отрицательной величиной. В этом случае работа-последователь начнет выполняться еще до завершения работы-предшественника.
График со связями работ, запаздыванием и прогрессом выполнения работ
Планирование работ — это половина дела, необходимо еще и отслеживать выполнение планов. Прогресс отслеживания завершения работы не сложно реализовать в MS EXCEL с помощью Условного форматирования.
Сначала, чтобы не перегружать количеством правил УФ, избавимся от кодов цветов — сделаем представление планов работ одним (голубым) цветом. Выполненные работы будем отмечать темно-синим цветом. Всего у нас будет 2 правила УФ: один для плана, другой для факта выполненных работ.
Нам особо ничего не придется переделывать на листе — столбец с кодом цвета теперь будет использоваться для хранения прогресса выполненных работ в месяцах. Фактически мы будем указывать сколько месяцев из общей длительности работы нужно закрасить другим цветом.
Формула в правиле УФ очевидна = И(I$11>=$E14;I$11<=$E14+$H14-1)
Закрашиваться будут только те ячейки (месяцы), которые уже начались (>=E14) у данной работы И выполнены (<=E14+H14-1).
Примечание: Конечно, выполнение работы корректнее отслеживать с помощью метода освоенного объема, но и данный упрощенный подход позволяет оценить прогресс выполнения работы.
Если значение выполнения работы больше чем ее длительность, то это означает, что имело место увеличение плановой длительности.
Процент выполнения работ
Иногда указывать прогресс выполнения задачи удобнее в процентах от общего объема работ (в нашем случае — от ее общей длительности). Этот вариант также реализован в файле примера на другом листе.
Если раньше на диаграмме ганта у нас были пустые ячейки, то теперь у нас введена довольно длинная формула:
Она может вернуть 4 разных значения:
- -1: ячейка (соответствующий месяц) лежит вне периода выполнения работы, закрашивается белым;
- 0: работа в этом месяце еще не выполнена, ячейка закрашивается салатовым цветом;
- от 1 до 99: работа в этом месяце выполнена частично, ячейка закрашивается гистограммой пропорционально значению (50 соответствует половине закрашенной ячейки);
- 100: работа в этом месяце выполнена полностью, ячейка полностью синим цветом.
Для выделения текущей даты используется вертикальная линия, ее нужно двигать вручную.
График со связями работ, запаздыванием, прогрессом выполнения работ и прогнозом окончания
Работы не всегда выполняются четко по плану, чаще фактическая длительность больше, чем плановая. По мере выполнения работы зачастую становится понятно насколько ее окончание сдвинется. Смоделируем эту ситуацию в нашем графике, добавив еще один столбец — Прогноз. В этом столбце будем указывать сколько еще месяцев дополнительно к плановой длительности нам потребуется, чтобы завершить весь объем работы.
Пусть изначально плановый график выглядел так. Предполагалось, что длительность первой работы (самая верхняя строка) скорее составит 7 месяцев, а последующая работа начнется сразу после ее окончания.
После 8 месяцев выполнения проекта стало понятно, что в плановый срок первую работу выполнить не удалось. Необходимо обновить планы, но при этом нужно сохранить изначальную информацию о длительности работы.
Как видно из рисунка, длительность первой работы (самая верхняя строка) скорее составит теперь не 7 месяцев как планировалось, а 9 (+2 месяца указано в столбце «прогн», т.е. прогноз). Эти дополнительные 2 месяца выделены темно-оранжевым цветом. После добавления этих двух месяцев изменятся сроки начала связанных работ. Теперь последующая работа должна начаться с 11 месяца, а не с 9.
Прогноз реализован с помощью еще одного правила УФ:
График можно усложнять и дальше: добавлять новые типы связей, учитывать ресурсы/ финансы, использовать более точный расчет процента выполнения и др. Если такая потребность появляется на практике, то это как раз то самое время, когда необходимо воспользоваться специализированной программой.
А мы далее рассмотрим создание графика с детализацией по дням, он больше подходит для более коротких проектов.
Как сделать план график в excel
Календарные графики в Excel (диаграмма Ганта)
Способ 1. Быстрый — используем условное форматирование
При помощи условного форматирования мы можем заставить Excel заливать ячейку любым выбранным цветом, если она по дате попадает между началом и концом этапа. Проще всего для этого использовать логическую функцию И , которая в данном случае проверяет обязательное выполнение обоих условий (5 января позже, чем 4-е и раньше, чем 8-е):
Пример такой диаграммы можно взять тут.
Способ 2. Долгий, но привычный — используем диаграмму
Итак, имеем таблицу с перечислением этапов проекта, датами начала и конца и длительносями каждого этапа:
Задача — построить стандартными средствами диаграмму-календарный график, как на рисунке:
Поехали, по шагам:
Выделим исходные данные для диаграммы — диапазон A2:B13 и выберем в меню Вставка — Диаграмма , тип — Линейчатая с накоплением:
Нажимаем на кнопку Далее и уходим на 2-й шаг Мастера диаграмм. На вкладке Диапазон данных выбираем Ряды в столбцах :
На вкладке Ряд жмем кнопку Добавить , устанавливаем курсор в поле Значения и выделяем ячейки с длительностями этапов ( C2:C13 ):
Жмем кнопку Далее и на третьем шаге Мастера на вкладке Легенда снимаем флажок Добавить легенду . Все — жмем Готово . Должно получиться примерно следующее:
Не пугайтесь — все идет по плану — нужно лишь «довести до ума» нашу диаграмму. Для этого щелкаем правой кнопкой мыши по вертикальной оси с названиями этапов и выбираем в контекстном меню Формат оси :
На вкладке Шкала в открывшемся окне ставим две «галочки» — Обратный порядок категорий и Пересечение с осью Y в максимальной категории . Жмем ОК. Теперь избавимся от синих столбцов. Сделайте двойной щелчок по любому из них и в открывшемся окне выберите невидимую рамку и прозрачную заливку. Должно получиться следующее:
Уже похоже на правду, верно? Осталось правильно настроить диапазон отображаемых на диаграмме данных. Для этого необходимо узнать реальное содержимое ячеек с которых начинается и на которых заканчивается временная шкала (желтая и зеленая ячейки в таблице). Дело в том, что Excel только отображает в ячейке дату как день-месяц-год, а на самом деле любую дату хранит в ячейке как количество дней, прошедших с 1.1.1900 до текущей даты. Выделите желтую и зеленую ячейки и по-очереди попробуйте установить для них Общий формат (меню Формат — Ячейки). Получится 38350 и 38427, соответственно. Накинем на дату окончания еще денька три — получим 38340. Запомните эти числа.
Осталось щелкнуть правой кнопкой мыши по горизонтальной оси времени и выбрать Формат оси и ввести эти числа на вкладку Шкала :
После нажатия ОК диаграмма примет требуемый вид:
Осталось «навести блеск» — настроить цвета, шрифты, подписи осей и прочее — с этим, я думаю, Вы и без моих советов справитесь. 🙂
Знаете ли Вы, что, когда некоторые исследователи, пытающиеся примирить релятивизм и эфирную физику, говорят, например, о том, что космос состоит на 70% из «физического вакуума», а на 30% — из вещества и поля, то они впадают в фундаментальное логическое противоречие. Это противоречие заключается в следующем.
Вещество и поле не есть что-то отдельное от эфира, также как и человеческое тело не есть что-то отдельное от атомов и молекул его составляющих. Оно и есть эти атомы и молекулы, собранные в определенном порядке. Также и вещество не есть что-то отдельное от элементарных частиц, а оно состоит из них как базовой материи. Также и элементарные частицы состоят из частиц эфира как базовой материи нижнего уровня. Таким образом, всё, что есть во вселенной — это есть эфир. Эфира 100%. Из него состоят элементарные частицы, а из них всё остальное. Подробнее читайте в FAQ по эфирной физике.
Диаграмма выполнения плана
Для визуализации классического сценария «план-факт» существует огромное количество всевозможных вариантов диаграмм, некоторые из которых я уже разбирал. Сегодня хочу добавить к этому списку и в ваш инструментарий ещё один весьма прикольный, на мой взгляд, вариант:
Надеюсь, тут всё очевидно и не требует подробных комментариев (как и должно быть у хорошей визуализации):
- план отмечается горизонтальной синей линией — здесь она сделана с помощью «усов» погрешностей (error bars);
- перевыполнение плана свыше этой линии выделяется зелёным цветом, недовыполнение — красным;
- аккуратные подписи данных рядов и отклонения от плана по оси Х добавляют информативности, но не мешают восприятию.
Как вариант, можно заменить красные прямоугольники невыполненного плана на пустышки с пунктиром по контуру — тоже будет наглядно:
Построить такие диаграммы очень легко — давайте пошагово разберём весь процесс.
Подготовка данных
Предполагаем, что в качестве исходных данных мы отталкиваемся от следующей таблицы:
Во-первых, имеет смысл сразу конвертировать эту таблицу в динамическую «умную», что позволит в дальнейшем не задумываться о её размерах при добавлении новых менеджеров-строк. Делаем это через команду Главная — Форматировать как таблицу (Home — Format as Table) или сочетанием клавиш Ctrl + T .
Во-вторых, следующая хитрость в том, что строить диаграмму мы будем не по имеющимся столбцам, а по дополнительным, в которых с помощью несложных формул рассчитаем числовые ряды для будущей диаграммы:
- Столбец Отклонение от плана — вычисляет отклонение от плана в процентах по классической формуле =[Факт]/[План]-1.
- Столбец Подписи оси Х — склеивает имя менеджера с отклонением от плана. Для добавления между ними переноса строки, используем функцию СИМВОЛ (CHAR) с кодом 10, которая заменяет Alt + Enter и функцию ТЕКСТ (TEXT) , чтобы показать отклонение от плана в виде округленных до целого процентов:
Построение диаграммы
Дальше создаем и оформляем диаграмму по следующему алгоритму:
- Выделяем в нашей умной таблице 4 последних столбца и строим Гистограмму с накоплением (Stacked Chart) на вкладке Вставка (Insert) .
- Заливаем столбики рядов данных в соответствующие цвета: серый, зеленый и красный.
- Добавляем подписи данных на вкладке Конструктор — Добавить элемент диаграммы — Метки данных (Design — Add Chart Element — Data Labels) .
- Добавляем на диаграмму ряд План (проще всего это сделать, скопировав исходные ячейки С3:С12 и вставив их затем из буфера прямо в диаграмму):
Вместо красной заливки можно отформатировать прямоугольники невыполненного плана в виде «пустышек» с пунктирным контуром — по желанию.
Ссылки по теме
- Диаграмма «план-факт» в Excel
- Подсветка заданных столбцов на диаграмме (условное форматирование диаграммы)
- Диаграмма-термометр в Excel
Диаграмма «План-Факт»
Редкий менеджер в своей практике не сталкивается с необходимостью наглядного представления достигнутых результатов по сравнению с запланированными изначально. В разных компаниях я встречал много подобных диаграмм, называющихся «План-Факт», «Actual vs Budget» и т.д. Иногда их строят примерно так: Неудобство такой диаграммы в том, что зритель должен попарно сравнивать столбцы плана и факта друг с другом, пытаясь удержать всю картину в голове, и гистограмма здесь, по-моему, не лучший вариант. Если уж и строить такую визуализацию, то однозначно нагляднее использовать графики для плана и факта. Но тогда перед нами встает задача наглядного попарного сравнения точек за одинаковые периоды и выделения разницы между ними. Давайте попробуем применить несколько удобных техник для этого.
Способ 1. Полосы повышения-понижения
Это наглядные прямоугольники, соединяющие попарно точки графиков плана и факта на нашей диаграмме. Причем их цвет зависит от того, выполнили мы план или нет, а размер показывает — на сколько:
Включаются такие полосы на вкладке Конструктор — Добавить элемент диаграммы — Полосы повышения/понижения (Design — Add Chart Element — Up/Down Bars) в Excel 2013 или на вкладке Макет — Полосы повышения-понижения (Layout — Up-Down Bars) в Excel 2007-2010. По умолчанию они будут черно-белые, но можно легко изменить их цвет, щелкнув по ним правой кнопкой мыши и выбрав команду Формат полос повышения/понижения (Format Up/Down Bars) . Очень рекомендую использовать полупрозрачную заливку, т.к. сплошная закрывает сами исходные графики. К сожалению нет легкого встроенного способа регулировать ширину полос — для этого придется воспользоваться небольшим трюком.
- Выделите построенную диаграмму
- Нажмите сочетание клавиш Alt+F11, чтобы попасть в редактор Visual Basic
- Нажмите сочтетание клавиш Ctrl+G, чтобы открыть панель прямого ввода команд и отладки Immediate
- Скопируйте и вставьте туда вот такую команду: ActiveChart.ChartGroups(1).GapWidth = 30 и нажмите Enter:
Само собой, параметром (30) можно поиграться, чтобы получить нужную вам ширину экспериментальным путем.
Способ 2. Диаграмма с заливкой зоны между линиями плана и факта
Этот способ предполагает наглядную заливку (можно со шриховкой, например) области между графиками плана и факта:
Весьма эффектно, не так ли? Давайте попробуем это реализовать.
Для начала добавьте к нашей таблице еще один столбец (назовем его, допустим, Разница), где посчитаем формулой разность между фактом и планом:
Теперь выделим одновременно столбцы с датами, планом и разницей (удерживая Ctrl) и построим диаграмму с областями с накоплением, используя вкладку Вставка (Insert) :
На выходе должно получиться примерно так:
Следующим шагом выделим ряды План и Факт, скопируем их (Ctrl+C) и добавим в нашу диаграмму вставкой (Ctrl+V) — в нашем «бутерброде в разрезе» сверху должны появиться два новых «слоя»:
Теперь переключим тип диаграммы для этих двух добавленных слоев в график. Для этого выделите по очереди каждый ряд, щелкните по нему правой кнопкой мыши и выберите команду Изменить тип диаграммы для ряда (Change Series Chart Type) . В старых версиях Excel 2007-2010 дальше можно выбрать нужный тип диаграммы (График с маркерами), а в новом Excel 2013 появится диалоговое окно со всеми рядами, где нужный тип выбирается для каждого ряда из выпадающих списков:
После нажатия на ОК увидим уже похожую на то, что нам нужно картину:
Легко сообразить, что осталось только выделить синюю область и поменять у нее цвет заливки на прозрачный Нет заливки (No Fill) . Ну, и навести общий блеск: добавить подписи, заголовок, удалить лишние элементы в легенде и т.д.
По-моему, это сильно лучше чем столбики, нет?
Ссылки по теме
- Как быстро добавить новые данные в диаграмму копированием
- Диаграмма-шкала (bullet chart) для отображения KPI
- Видеоурок по созданию проектной диаграммы Ганта в Excel