Справка - Создание графика производства СМР в Excel

Справка – по подготовке  графика производства работ по строительству на основании  договорной цены в программе Excel

1.Вступление                                                                                                                                                                    

1.1 Было перебрано несколько вариантов решения проблемы создания календарного графика.

1.1.1 MS Project – программа хороша возможно для любого календарного  планирования кроме строительных проектов. Диаграмма Ганта и более ничего.

1.1.2 ProjectLibre – программа похожа на MS Project – но возможности скромнее,  где то на уровне все таки MS Project и не более.

1.1.3 Turbo Planner + Construction (Москва) – программа отличная – но заточена под российские сметные программы – ломаных версий нету, а реальная стоимость в районе 200 тыс. руб. Лайт версия которая раздается бесплатно – рассчитана на 5 человек и  в ней жестко задано  начало проекта.  

1.1.4 Construction Planner (Киев – разработчик Вадим Геря) – возможно будет совместная разработка с “Computer Logic” в плане модуля экспорта  из сметной программы в  сам  Construction Planner и дальнейшей проработкой  графика и всех других данных в программе. Предполагаемая стоимость 60 тыс. грн. Намечается очень достойный продукт – но при такой цене его целесообразно использовать только для крупных проектов, где это экономически оправдано.

1.1.5 SpiderProject  - сама программа для строителей – лучше не придумаешь -  есть ломаная версия под Windows ХР – но это уже вчерашний день, тем более не совсем ясен (по крайней мере для меня) механизм экспорта сметных программ в SpiderProject. В освоении требует серьезных строительных знаний и знаний самой программы.

1.1.6 Primevera – Программа похожа на SpiderProject (о чем то еще сказать сложно, так как последний раз пришлось с ней познакомится очень поверхностно несколько лет тому назад.

1.2 Вторая проблема – составления графика не самоцель. И чем график подробней тем 

лучше. График нужен не только Подрядчику но и Заказчику. Заказчик должен иметь механизм чтения предложенного графика. Общим механизмом может быть в принципе MS Office (возможно OpenOffice - ?).

1.3 Третий и последний момент – Excel первый кандидат на реализацию этого проекта.

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

2. Составление договорной цены. 

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

2.1 Объектные сметы к договорной цене лучше располагать изначально, в той   последовательности,  в которой будут выполняться работы.

2.2 Локальные сметы к договорной цене лучше располагать изначально,  в той   последовательности, в которой будут выполняться работы.  Имеется в виду в составе объектных смет или в составе сводного сметного расчета – если объектные сметы не применяются.

2.3 Очень желательно, разбивать локальные сметы на разделы и подразделы (исходя из видов и последовательности работ) и по возможности выводить суммы по разделам и подразделам.

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

    3. Подготовка основы для графика производства работ.

             3.1  Формируем  Расчет 1-1 к договорной цене и экспортируем его в Excel – сохраняем на 

                   диске.

            3.2  Распечатываем  саму договорную цену  или сохраняем ее в электронном виде на диске, 

                   формат не важен, так как оттуда будут взяты только исходные  данные.

           3.3  Убираем  верхнюю часть  надписей в Расчете 1-1 – до  начала таблицы с данными и 

                  формируем  свою шапку , в том числе переименовываем Расчет 1-1  в Календарный 

                  график производства работ.

         3.4    Удаляем лишние столбцы и строки  из Расчета 1-1. Остаются заглавия объектных  смет, 

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

                  сметами выделяем  цветом.

        3.5   Суммируем  данные по разделам и подразделам.  При этом необходимо учесть что 

                сумма по Расчету 1-1 отличается  от суммы сводного сметного расчета  на величину сумм 

               по главам 10-12 сводного сметного расчета, т.е. Прибыль, Админ расходы, НДС  и т.д. 

              Для пересчета разделов с учетом полной сметной стоимости применяем коэффициент -  

             сумма   глав1-12  деленная на сумму глав 1-9 ССР.  Расчет ведем от меньшего к большему – 

             по цепочке  - подраздел – раздел – локальная смета – объектная смета – сводный сметный 

             расчет (т.е. снизу - вверх).  Пересчет итогов и всех данных в цены ССР производится по 

            формуле умножения  столбца таблицы  на коэффициент.

         3.6  Дальнейшие построения производим исходя из основного критерия эффективности 

                 исполнения проекта. А критериев всего три исходя из «Проектного треугольника» - 

                Финансы, Время, Ресурсы.  Для примера можно принять, что у нас основной критерий это 

                Время (директивно – по условиям Заказчика) и следовательно работа должна быть 

               выполнена в течении 6 месяцев. По Финансам  - Заказчик не готов платить больше, чем 

               предусмотрено сметой. Остается только один источник маневра это Ресурсы – и основной 

               ресурс это численность работников. Вычисляем среднесписочную численность:

                =(F24/E9)/6 ;   

                Здесь:  F24 – общие трудозатраты по ССР; E9 – среднемесячная норма труда, 

                которая равная,  директивно установленному правительством годовому балансу рабочего 

                времени деленное на 12 месяцев;  6 – это количество месяцев на выполнение работ.

                В шапке формы в выбранной ячейке устанавливаем, обусловленную договором подряда, 

                дату начала работ.

            3.7  Заполняем колонку данными со сметной стоимость пересчитанной  в цены ССР. 

                   В некоторых ячейках столбца  при этом отображаются  ошибки. Для подавления ошибок 

                   и выполнения расчета применяется формула, которая в  окончательном виде,  выглядит 

                   следующим образом:

                                                                       =ЕСЛИОШИБКА(E1443*$G$25;"")

                    Здесь:   E1443 – значения верхней ячейки столбца  таблицы – которое потом 

                                   тиражируется  на всю  колонку – колонка берется сформированная при 

                                   копировании сметы, с данными по расчету 1-1;

                                   *$G$25  - значение  ячейки таблицы, в которой стоит коэффициент пересчета в 

                                     цены  ССР;

                                      ""  - признак пустой строки, для ячеек, в которых при пересчете будет 

                                    сгенерирована  ошибка;

                  3.8  Заполняем колонку трудозатрат  в пересчете на трудозатраты ССР. Это 

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

                         ССР отличаются от трудозатрат по локальным и объектным сметам на величину, 

                         трудозатрат линейных ИТР. Трудозатраты в днях на выполнение разделов работ 

                         вычисляются по формуле:

                                           =ЕСЛИОШИБКА(((F27/8)/37);"")

                          Здесь:    F27  - трудозатраты в человеко-часах, которые берутся из колонки 

                                          сформированной при экспорте сметы, по разделам и подразделам. Берется 

                                          верхняя ячейка колонки (при составлении формулы), потом эта формула 

                                         тиражируется на всю колонку;

                                         8 – продолжительность рабочей смены;

                                         37 – среднесписочная численность;

                                         ""  - замещение ячеек с ошибками, пустыми строками;

4.   Заполнение дополнительных колонок:

      4.1  Нумерация смет – берем из существующих локальных смет. Названия разделов и  

             подразделов добавляем через точку – типа  02-01-01.01;

      4.2  Дата начала работ по разделам   - дата начала проекта берется из шапки т.е. из 

             установок проекта. Промежуточные значения вычисляются по принципу от 

             меньшего к большему т.е. подраздел(сумма) – раздел(сумма)-локальная смета  

             (сумма) – объектная смета (сумма) – ССР (сумма).

             Начало первой работы = дата начала проекта;

             Конец первой работы   -  =РАБДЕНЬ(I1886;H1886) (значек функции возле ячейки 

            формул, оттуда берем функцию РАБДЕНЬ и в ней дату начала + продолжительность 

            работы в сменах;

            Начало второй работы =  конец первой работы + 1 и т.д. – ( возможно вводить  

            значения типа 0,1 если следующая работа начинается в день окончания   

           предыдущей или  в случае если мы выходим за пределы временного диапазона 

           предусмотренного итогами раздела/подраздела).

     4.3  Установка дат по разделам выполняется через функцию:

             мин(диапазон ячеек) типа =МИН(I1886:I2012) и  

            макс(диапазон ячеек) типа =МАКС(J1886:J2012)

            (стоя на ячейке раздела в строке формул вводим: =мин/макс(выбираем нужную 

            группу ячеек)).

    4.4  Добавляем колонку «Вехи» - здесь можно будет выставлять признак 

           окончания/начала каких то важных этапов работ. В качестве значка вехи  

           применяется зачерненный ромб (установлено через меню символы – все форматы – 

           t;; - шрифт Wingdings). Для установки вехи в ячейке следует поставить 1.

    4.5  Добавляем несколько колонок для того чтобы в дальнейшем в них отмечать 

           фактический ход выполнения проекта – Факт (Стоимость, Продолжительность, 

           Начало работ, Окончание работ, Отклонение);

    4.6 Добавляем две колонки для отметок о выполнении работ. Одна колонка – 

          выполнение в денежном выражении. Вторая колонка выполнение в долях от 

           единицы типа в % (0,05 = 5% и т.д.). Данные из этой колонки автоматически 

           отмечаются на графике – зеленой заливкой. Как связать данные из этой колонки с 

           графиком, станет ясно при рассмотрении построения графика.

           Первой ячейку колонки выполнение присваиваем значение первой ячейки 

          Фактическое выполнение работ (Стоимость), тиражируем полученную формулу до 

           конца колонки Выполнение(грн/руб/дол), выделяем всю колонку и задаем числовой 

          формат с разделением на разряды, без знаков после запятой, подправляем 

          нарушенное форматирование ячеек.

          Первой ячейке колонки Выполнение (%) присваиваем формулу  = выполнение из 

          колонки Фактическое выполнение работ (Стоимость)/ Сметная стоимость (плановая).

          Тиражируем полученную формулу до конца колонки, задаем числовой формат с 

           двумя знаками после запятой, правим нарушенное форматирование.

5.  Группировка данных

После заполнения всех колонок  данными – выделяем всю таблицу.  Верхнее меню => 

      Данные => Сгруппировать => Создание структуры. Будет выполнена автоматическая 

      группировка информации по разделам и подразделам. Построение графика  по 

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

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

     длительность меньшей одной рабочей сметы – при большом количестве таких данных 

     неизбежны ошибки и неудобно работать. Эту же процедуру можно выполнить вручную, 

     группируя разделы и подразделы (Данные => Группировать), но если выведены суммы 

     по разделам и подразделам то Excel справляется с этой задачей автоматически и очень 

     качественно, через Создание структуры.

6.  Построение календарного графика.

     6.1  Выбираем для первой ячейки дату начала графика равную  дате начала проекта 

            (ячейка в шапке проекта);

     6.2 Выбираем для второй ячейки  = первая ячейка +1

     6.3 Тиражируем данные второй ячейки (горизонтально) – до конца месяца (31 день)

     6.4 Под первой строкой графика – добавляем следующую строку – «Дни недели» - 

           применяем формулу:  =первая ячейка (дата начала графика) 

           – и к ячейке применяем форматирование. Формат ячеек => Все форматы => ДДД.    

           Тиражируем формулу из     первой ячейки «Дни недели»  горизонтально, до конца 

            месяца.

     6.5  Формируем заливку цветом рабочих дней исполнения проекта. Выделяем таблицу 

            проекта (включая даты) => Главная => Условное форматирование (из верхнего 

            меню) => Создать  правило => Использовать формулу для определения 

            форматируемых ячеек => 

                =И(S$22>=$I24;S$22<=$J24;$K24<>1)

          Здесь:  И – здесь подразумевается одновременное выполнение всех условий 

                               перечисленных в формуле;

                        S$22 – первая ячейка календаря в графике (строка 22 - постоянна );

                        $I24  -  первая ячейка в колонке начала работ(колонка I - постоянна );;

                        $J24  -  первая ячейка в колонке окончания работ(колонка J - постоянна );

                        $K24 – первая ячейка в колонке «Вехи» (колонка K - постоянна );

                        >= - больше или равно; <= - меньше или равно; <> - не равно;

             Выбираем цвет (кнопка «Формат»). Применить. ОК.

      6.6  Формируем заливку цветом рабочих дней исполнения проекта (Процентное   

            выполнение работ). 

            Выделяем таблицу проекта (включая даты) => Условное форматирование (из   

            верхнего меню) => Создать  правило => Использовать формулу для определения 

            форматируемых ячеек =>

               =И(S$22>=$I22;S$22<($I22+ОКРУГЛ(($J22-$I22+1)*$R22;0));$K22<>1) 

 

            Здесь:  И – здесь подразумевается одновременное выполнение всех условий 

                               перечисленных в формуле;

                        S$22 – первая ячейка календаря в графике (строка 22 - постоянна );

                        $I22  -  первая ячейка в колонке начала работ(колонка I - постоянна );;

                        $J22  -  первая ячейка в колонке окончания работ(колонка J - постоянна );

                        $R22 -  первая ячейка в колонке % выполнения работ (колонка R - постоянна );

                        $K22 – первая ячейка в колонке «Вехи» (колонка K - постоянна );

                        >= - больше или равно; <= - меньше или равно; <> - не равно;

             Выбираем цвет (кнопка «Формат»). Применить. ОК.

 

         6.7  Формируем заливку цветом или текстурой выходных дней.

                Выделяем таблицу проекта (включая даты) => Условное форматирование (из   

                верхнего меню) => Создать  правило => Использовать формулу для определения 

                форматируемых ячеек =>

                 =ДЕНЬНЕД(S$22;2)>5

                Здесь:  ДЕНЬНЕД – функция Excel 

                              S$22           - первая ячейка графика

                               2   - рабочая неделя начинается с понедельника

                               >5  - все что больше 5 дня (т.е. 6 и 7) в неделе считается выходными

                 Выбираем цвет (кнопка «Формат»). Применить. ОК.

        6.8  Формируем заливку цветом (Выделение) текущей даты.

                 Выделяем таблицу проекта (включая даты) => Условное форматирование (из   

                верхнего меню) => Создать  правило => Использовать формулу для определения 

                форматируемых ячеек =>

                       =S$22=СЕГОДНЯ()

               Здесь:  СЕГОДНЯ() – функция Excel 

                              S$22           - первая ячейка графика

               Выбираем цвет (кнопка «Формат»). Применить. ОК.

               Сортируем введенные форматы (формулы) в списке  форматов  для 

               удобочитаемости.

    6.9   Можно еще выставить вехи (которые стоят в колонках) на график

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

            тиражируется на весь график:

                        =ЕСЛИ(И($K22=1;S$22=$I22);1;-1)

S$22           - первая ячейка графика

$K22 – первая ячейка в колонке «Вехи» (колонка K - постоянна );

            Здесь значения ячеек те же что описаны ранее, а вот 1 это наличие вехи, -1 

            отсутствие вехи. А поскольку вех все таки меньше чем не вех то на графике будет 

            очень много -1, чтобы их убрать применяем форматирование к ячейке и заменяем 

            -1 на t;; (копируем формат вех из колонки «Вехи») через все форматы в 

             форматировании ячейки, т.е.  ячейка будет пустой. Здесь t – отметка вехи, а ; - 

             остальные ячейки пустые.

6.10   Включаем режим «Разработчика»  (Верхняя лента меню) => Файл => Параметры => 

           Настройка ленты => Настройка ленты => Основные вкладки => Разработчик 

           (отмечаем чекбокс).

6.11   Разработчик => Вставить => Полоса прокрутки (Элемент управления формы) => Выше 

           календарного графика  путем горизонтальной протяжки примерно на 15-20 клетках 

           размещаем указанную полосу прокрутки. Через свойства элемента (Правая кнопка 

           мыши) – устанавливаем в открывшейся форме протяженность графика (по 

           умолчанию стоит 100 дней), связываем с ячейкой расположенной в строке перед 

          полосой прокрутки. Первую ячейку календаря (календарь графика) связываем с 

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

          расположена перед полосой прокрутки мы связываем три компонента = полоса 

          прокрутки + календарь графика + сам график. Тем самым мы в ограниченном 

          пространстве окна 31 одного дня добиваемся перемещения всего графика.

7.  Замечания по ходу создания графика.

     7.1 По ходу создания графика необходимо как можно чаще сохранять набранную 

            информацию, так как работа не простая и потерять набранное будет очень 

            неприятно.

 

     7.2 Результат работы лучше сохранить в двух форматах как просто файл Excel, так и файл 

            шаблона.

     7.3  Неприятная процедура при составлении подобного графика, в том, что каждый раз 

            необходимо  проходить всю процедуру создания графика с ноля и практически  

            невозможно использовать шаблон. Хотя не факт можно попробовать 

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

            Excel.

 

Здесь пример(шаблон) графика производства работ в Excel