Календарно-сетевой график в неделях в MS EXCEL

history

Построим Календарно-сетевой график с временным шагом 1 неделя. Наш график будет отображать: длительность работ, объем выполненных работ и прогноз длительности (в случае отклонения от планового завершения). В графике есть возможность связать работы между собой (тип связи конец-начало с запаздыванием).

О построении календарных графиков в EXCEL

Начнем с разочарований: EXCEL не предназначен для ведения календарных графиков - для этого нужно использовать специализированные программы. В EXCEL можно только вести простенькие графики, изредка показывая их руководству. Но, реализовать различные связи между работами, полноценное планирование ресурсов и ведение нескольких базовых графиков проекта в EXCEL практически невозможно (без написания соответствующих VBA программ, что не разумно, т.к. все уже давно написано в специализированных программах).

Но, конечно, несложные графики в EXCEL построить можно, как это сделать - продемонстрировано во многих статьях, в том числе и на сайте excel2.ru:

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

Совет: В данной статье диаграмма выполнена с помощью Условного форматирования (далее УФ). Для тех, кто мало знаком с этим инструментом MS EXCEL рекомендуется прочитать вот эту статью про УФ.

Построение графика

Для построения графика, изображенного выше, нам потребуются следующие исходные данные:

  • Длительность каждой работы
  • Предшественник работы
  • Запаздывание относительно окончания работы-предшественника
  • % выполнения
  • прогноз завершения работы (в случае ее отклонения)

Всего используется 2 правила УФ: одно для отображения плановой длительности (зеленая заливка), другое - для прогноза завершения работы (оранжевая).

Процент выполнения реализован с помощью символа похожего на квадратик. Этот символ есть в шрифте Webdings, ему соответствует буква "g". Соответствующая формула введена во все ячейки диаграммы Ганта =ЕСЛИ(И($L15>=N$6;$J15<=N$6);"g";"")

Так как почти все работы связаны (тип связи Конец-Начало), то для вычисления начала работ используется формула =ЕСЛИ(ЕПУСТО(C15);$C$7;ИНДЕКС($M$15:$M$18;ПОИСКПОЗ(C15;$A$15:$A$18;0))+7+$G15)

Таким образом, вводить начало и конец работы не нужно, это вычисляемые столбцы. Достаточно задать связи и длительность работы, а также дату начала проекта. В столбце Запаздывание вводится поправка в днях, если нужно чтобы работа начиналась чуть позже или раньше окончания работы-предшественника. Столбец Прогноз содержит дополнительные дни, которые необходимо добавить к первоначальной длительности работы, чтобы ее завершить (в него вводятся значения если в ходе выполнения работы произошла задержка).

При построении графиков с шагом 1 неделя существует несколько сложностей:

  • т.к. начало и конец работы указываются в днях, то нужно определить как отображать на диаграмме работы, которые начинаются/заканчиваются в середине недели (в данной статье принято, что если работа содержит хотя бы 3 рабочих дня недели, то эта неделя относится к периоду выполнения работы, если 2 или 1 - то не относится. Поэтому, задавать длительность работ менее 7 дней не нужно, работа может не отобразиться на диаграмме. В этом случае используйте файл с графиком, в котором шаг планирования равен 1 дню);
  • отнесение недель к месяцу требует установления аналогичного правила: если 3 или более рабочих дня недели относятся к месяцу, то и вся неделя относится к месяцу;
  • из-за предыдущего правила длительность месяцев получается разной - в одних получается 4, а в других 5 недель. Эта проблема решается формулами в строках 6, 7 и 12;
  • из-за особенности отображения текстовых строк, названия месяцев требуется выводить по несколько символов в ячейке (если в соседней ячейке справа есть формула или значение, то текстовая строка обрезается на границе ячейки, чтобы не было наложения 2х значений друг на друга). Это реализовано формулой =ПСТР(ВПР(N8;служ!$B$4:$C$15;2;0);(N12-1)*ВПР(N8;служ!$B$4:$D$15;3;0)+1;ВПР(N8;служ!$B$4:$D$15;3;0))

В строке 14 на диаграмме указаны даты, которые выпадают на понедельники. В строке 13 - названия месяцев. В строке 12 ведется подсчет недель месяца. В строке 11 - сквозная нумерация недель проекта. В строке 10 напротив текущей недели отображается красный маркер.

Для удобства внизу графика выведен календарь, который показывает выбранный месяц проекта (он также выделяется Условным форматированием).

Календарь реализован одной формулой массива. Как это сделать показано в этой статье.

Результат

Очевидно, что даже такой относительно простой график получился перенасыщен формулами. Можно легко нарушить его работу, случайно удалив формулу.

Если появились вопросы - пишите в группу https://vk.com/excel2ru

 

 

 

 

 

 



Комментарии

Только для авторизованных пользователей

(только для авторизованных пользователей)

© Copyright 2013 - 2024 Excel2.ru. All Rights Reserved