Шаблон Excel - План-график проекта (Project Schedule Template) ~ Трюки и приемы в Microsoft Excel

Представляем вашему вниманию простой шаблон Плана-графика проекта без лишних наворотов, типичных для диаграммы Ганта. Шаблон был разработан для создания визуально-привлекательных обзоров хода проекта и с описанием поставленных задач. Шаблон Плана-графика подходит для использования в презентациях Power Point.

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

Шаблон Excel — План-график проекта (Project Schedule Template)

Представленный шаблон обеспечивает простое решение для подготовки отчетов и обзоров состояния проекта. Шаблон будет полезен для использования в презентациях или проектах, где отсутствует необходимость использования диаграмм Ганта. Шаблон Плана графика проекта включает в себя пустые листы для еженедельного и ежемесячного отслеживания проектов. Также в файле Excel приложен пример использования, который вы видите на скриншоте выше. Наш шаблон предполагает, что вы обладаете знаниями о том как можно отформатировать таблицу с помощью заливок и изменения направления текста.

Расскажите о нашем шаблоне своим друзьям!

Шаблон для ведения проекта - Различные инструменты Excel - Excel - Каталог статей - Perfect Excel

Как вы уже догадались, я разработал Excel шаблон для ведения проектов.

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

Часто в корпоративной среде бывает, что компания готова купить MS Project только для некоторых сотрудников, что в общем-то лишено смысла. Постоянно выгружать в HTML или Excel отчёты из MS Project и рассылать по почте всем заинтересованным лицам довольно утомительное дело. На моей памяти так не делал никто, хотя мы (IT отдел) предлагали это в качестве опции. Бесплатного просмотрщика файлов MS Project Майкрософт не выпускает. Третьи компании, насколько я знаю, тоже (но есть платные).

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

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

Нечто бесплатное, но предельно простое и убогое.

Нечто забюрократизированное и неудобоваримое, типа "Комплект шаблонов проектных документов из 55 файлов".

Нечто реально удобное, но за деньги.

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

Мой шаблон для тех, кто хочет избежать крайностей, сохранить контроль над проектом и избежать лишних расходов.

Двух-уровневая структура этапов проекта

Сроки для плана и для факта

Возможность перекрытия соседних пунктов плана

Учёт завершения пункта плана в процентах

Учёт затрат денежных средств по этапу (план/факт)

Оценка необходимого и достигнутого качества работ

Возможность обозначить вехи (milestones) проекта

Возможность связать конкретный этап с риском (risk) или проблемой (issue)

Серьёзная автоматизация при планировании сроков.

Диаграмма Ганта

Масштабирование по времени

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

Цветовое выделение выходных и праздников

Планирование необходимого количества ресурсов

Учёт наличествующего количества ресурсов

Сравнение плана и факта

Статус проекта (board)

Светофорные индикаторы: график, бюджет, качество, ресурсы, риски, проблемы

Количественные и аналитические показатели

Календарный график в Excel

Лист в MS Excel для создания и оформления календарных графиков с подсчетом продолжительности работ и потребности в рабочих кадрах.
Достоинством данного графика является автоматическое "построение" полосок и заполнение количества рабочих для каждого вида работ.
В книге не использованы макросы, все сделано только при помощи формул.

Љ «Ґ­¤ а­л© Ја дЁЄ.xls

Очень полезная програ для тех, кто не хочет иметь заморочки с МР или др мощными инструментами, а также для оперативного руководства небольшими подразделениями - звеньями, бригадами, отдельными локальными объектами. Годится и для составления наборов работ (физобъемов) на небольшие сроки и захватки

29 декабря 2010 в 18:41

Спасибо вам, Токарев, большое.
За вашу Балку и за этот файл.
Тут бы ещё машины бы как-нибудь и материалы считались вообще круче МС проджекта был бы :)

78079 74747 64298 64297 64296 64295 64294 64293 64292 64291

Комментарии могут оставлять только зарегистрированные участники
Авторизоваться

График работы в excel - Мир компьютерных инноваций

Практически все люди лучше воспринимают информацию «нарисованную», а не «прослушанную». И еще лучше, если эта информация представлена образами, а не рядом цифр и показателей. Представьте себе, что малознакомый человек рассказывает о своей собаке. Он не описывает ее внешность и родословную, не уточняет масть и возраст и т. д. Воображение каждого слушателя нарисует свой образ. А когда мы уже представим себе красавца дога, окажется, что нам рассказывали о прелестном мопсе. В этой ситуации мы посмеемся, но, столкнувшись с чем-то подобным на предприятии, уже будет не до смеха.

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

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

Умение составлять график производства работ – один из важнейших навыков управленцев разного уровня. Чем точнее и детальнее составлен график, тем качественнее будут проведены все запланированные работы. Несмотря на то что «родной» отраслью графика производства работ считается строительство, руководителям всех направлений не помешает знание принципов календарного планирования.

Любую работу можно разбить на мелкие задачи. Самый простой пример – приготовление салата из свежих овощей. Казалось бы, что проще? Но и это элементарное задание можно разбить на последовательность действий. Сначала приобрести все ингредиенты, затем помыть их, нарезать и смешать, заправив соусом. Причем все действия могут быть разорваны во времени (появляются перерывы в работе), а могут быть проделаны последовательно, без разрывов во времени. Кроме того, все это может проделать один человек, а может и целая бригада поваров. Итак, последовательность действий есть. Остается рассчитать время выполнения каждого этапа и определить, сколько и какой персонал необходим для этой работы. И график производства работ у нас практически готов.

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

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

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

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

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

Далее переходим к определению необходимого оборудования, механизмов и приспособлений. В производственных отраслях для этого также существуют нормативы. И, наконец, последнее, но от этого не менее важное – это расчет необходимых для проведения работ материалов.

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

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

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

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

Программистами разрабатывается множество вспомогательных программ, призванных автоматически рассчитывать и строить график производства работ. Образец календарного плана, рассчитанного с помощью Microsoft Office Project 2010 Professional, например, легко отыскивается в интернете. Однако не каждая компания согласится потратить дополнительные средства на установку программного обеспечения и обучение персонала работе с ним. Кроме того, у каждой специализированной программы есть свои недостатки. Одна не учитывает возможности сменной работы, другая без написания макросов не согласуется с расчетом материалов, например, и т. д.

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

У этой программы масса преимуществ:

1. Она бесплатна. В том смысле, что Excel – часть стандартного пакета MS Office, который безоговорочно установлен практически на каждом компьютере.
2. Она проста. Имея минимальные знания о расчёте формул и привязке листов друг к другу, можно заниматься планированием.
3. Она наглядна. Все расчеты и результат выводятся на одном листе. А внесение изменений тут же отображается на графике.

Проектная диаграмма Ганта с помощью условного форматирования

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

Идея состоит в использовании условного форматирование, чтобы заставить Excel заливать ячейку заданным цветом, если она по дате попадает между началом и концом этапа. Для этого выделите весь диапазон, где должна быть диаграмма (в нашем примере - начиная с ячейки D3 и до конца таблицы) и затем:

• в Excel 2003 и старше: выберите в меню Формат - Условное форматирование - Формула (Format - Conditional Formatting - Formula) и введите в появившуюся строку следующую формулу:

• в Excel 2007 или новее: жмем на вкладке Главная (Home) кнопку Условное форматирование - Создать правило (Conditional Formatting - New Rule). выбираем последний тип Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format) и вводим аналогичную формулу:

  
  

По сути, эта формула делает простую вещь - функция И (AND) проверяет обязательное выполнение двух условий, чтобы дата для текущей ячейки была позже, чем дата начала этапа и раньше даты окончания. Если оба эти условия выполняются, то ячейка находится внутри этапа, т.е. должна быть залита. Нажав на кнопку Формат (Format) можно выбрать необходимый цвет.

Просто и красиво, не правда ли?

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

Основа здесь все та же - условное форматирование. Плюс добавлены:

• задержка перед началом этапа (может быть положительно или отрицательной или =0)
  
• проценты выполнения по каждому этапу с их подсветкой на диаграмме
• функция РАБДЕНЬ (WORKDAY) для расчета не календарных, а рабочих дней
• подсветка праздников и выходных все тем же условным форматированием с помощью функции ДЕНЬНЕД (WEEKDAY)
• координатная подсветка текущей строки и столбца с помощью специального макроса

Microsoft Project нам так, конечно, не переплюнуть, но приблизиться к нему в визуальной части вполне можно :)

Скачать анализы и отчеты в формате Excel

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

Шаблон телефонного справочника.
Интерактивный шаблон справочника контактов для бизнеса. Удобное управление большой базой контактов.

Складской учет в Excel скачать бесплатно.
Программа для складского учета создана исключительно с помощью функций и стандартных инструментов. Без использования макросов и программирования.

Формула рентабельности собственного капитала «ROE».
Формула, которая отображает Экономический смысл финансового показателя «ROE». Эффективный инструмент для оценки инвестиционной привлекательности предприятия.

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

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

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

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

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

График модели Бертрана.
Готовое решение построение графика модели Бертрана, по которому можно анализировать зависимость спроса и предложения в условиях демпинга цен на дуопольных рынках.

Алгоритм расшифровки ИНН.
Формула для расшифровки Индикационного налогового номера для: России, Украины и Беларуси. Поддерживаются все виды ИНН (10-ти и 12-ти значные номера) физических и юридических лиц, а также личный номер.

Факторный анализ отклонений.
Факторный анализ отклонений в маржинальном доходе предприятия с учетом показателей: материальные затраты, выручка, маржинальный доход, цена-фактор.

Табель учета рабочего времени.
Скачать табель учета рабочего времени в Excel с формулами для автозаполнения таблицы + ведение справочников для удобства работы.

Прогноз по продажам с учетом сезонности.
Составленный готовый прогноз по продажам на следующий год на основе показателей продаж предыдущего года с учетом сезонности. Графики прогноза и сезонности - прилагаются.

Прогноз показателей деятельности предприятия.
Бланк прогноза деятельности предприятия с формулами и показателями: выручка, материальные затраты, маржинальный доход, накладные расходы, прибыль, рентабельность продаж (ROS) %.

Баланс рабочего времени.
Отчет по планированию рабочего времени работников предприятия по таким временным показателям как: «календарное время», «табельное», «максимально возможное», «явочное», «фактическое».

Чувствительности инвестиционного проекта.
Анализ динамики изменений результатов в соотношении с изменениями ключевых параметров является чувствительностью инвестиционного проекта.

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

Таблица для проведения финансового анализа.
Программный инструмент выполнен в Excel предназначен для выполнения финансовых анализов предприятий.

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

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

Удобный план проекта 2 - Бизнес в стиле excel

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

Однако, нет предела совершенству, и в данной статье мы добавим в план проекта Excel дополнительный функционал.

Для данной статьи доступно видео, в котором вы сможете наблюдать за созданием плана проекта.
Смотреть видео

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

Для удобства формирования СДР выделим дополнительно 5 столбцов (A:E), где каждый столбец соответствует уровню вложенности работы. Обычно, пяти уровней вложенности достаточно, но вы легко можете скорректировать количество уровней, применительно к вашему плану проекта.

Стоблец "СДР" (F), который остался от предыдущей таблицы, будет заполняться автоматически. Для этого мы используем следующую формулу (для ячейки F9):

Функция СЦЕПИТЬ объединяет значения ячеек A9, B9, C9, D9, E9, предварительно проверив их на наличие значения. После каждого уровня ставиться точка.

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

В обоих этих случаях в ячейку будет введена формула, начинающаяся со знака равно ("="). Таким образом, определив наличие формулы в ячейке с датой начала работы, мы можем с уверенностью сказать, что связь с другими задачами имеется.

Определить наличие формулы в ячейке стандартными средствами мы не можем, поэтому используем следующий макрос:

Создадим новый столбец "Начало не ранее" (G), в ячейку которого впишем формулу:

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

Для отображения символа циферблата, примените к ячейкам шрифт Wingdings, входящий в стандартную поставку всех компьютеров на базе ОС Microsoft Windows.

Составление плана проекта без наличия примечаний к работам представляется неудобным. Наименование работ отражает краткий смысл задачи, но не детальное ее понимание. Поэтому создадим отдельный лист с комментариями к работам проекта и сделаем отметку в плане проекта о наличии комментария.

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

В плане проекта выделим диапазон F9:F25 (столбец "СДР"), присвоим ему имя "сдр ".

Затем выделим диапазон J9:J25 (столбец "Задача") и присвоим ему имя "задачи ".

Создадим лист "Примечания", содержащий столбцы "СДР", "Наименование задачи" и "Примечание". Теперь сделаем так, чтобы мы могли выбрать нужную задачу из списка задач по ее номеру (СДР).

Выберем ячейку A2 (первая свободная ячейка в столбце "СДР") и перейдем на ленте во вкладку Данные – группа Работа с данными – Проверка данных. В открывшемся окне введем:

Подтвердим ввод, нажав на кнопку OK.

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

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

Введем в ячейку B2 (первая свободная ячейка в столбце "Наименование задачи") следующую формулу:

Разберем формулу по частям:

• ПОИСКПОЗ(A2;сдр;0) – находим порядковый номер вхождения значения ячейки A2 в диапазоне "сдр"
• ИНДЕКС(задачи;ПОИСКПОЗ(…)) – находим соответствующее порядковому номеру СДР наименование задачи в диапазоне "задачи"

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

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

Для создания Таблицы перейдем во вкладку Вставка – группа Таблицы – Таблица (Ctrl+T) и во всплывающем окне проверим, что указанное расположение данных соответствует нашей таблице примечаний, а также отмечена опция, что наша таблица включает заголовки.

Выделите одну из ячеек таблицы примечаний, перейдите во вкладку Конструктор, где укажите в поле Имя таблицы значение "Примечания", и подтвердите ввод клавишей Enter. Теперь к нашей таблице можно будет обращаться иначе, чем мы привыкли, работая с обычными диапазонами. Рассмотрим это на следующем этапе: создадим ссылки на примечания в плане проекта.

Создадим столбец "Примечание" (H), который заполним следующей формулой (ниже пример для первой строки плана проекта):

• ПОИСКПОЗ(F9;Примечания[СДР];0) – производим поиск номера СДР, соответствующего строке плана проекта, в столбце СДР таблицы Примечаний. Если примечание для данной задачи отсутствует, формула выдает ошибку #Н/Д, поэтому для обработки такого результата используем дополнительно обработчик ошибок – функцию ЕОШИБКА
• ЕОШИБКА(ПОИСКПОЗ(…)) – данная функция проверяет вложенные в нее вычисления на наличие ошибок (#Н/Д, #ЗНАЧ. #ССЫЛ. #ДЕЛ/0. #ЧИСЛО. #ИМЯ? или #ПУСТО!) и возвращает логическое значение (ИСТИНА / ЛОЖЬ) в зависимости от результата проверки: ИСТИНА – при наличии ошибки во вложенных вычислениях, ЛОЖЬ – при отсутствии ошибок
• ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(…));"";")") – результат функции ЕОШИБКА легко обработать функцией ЕСЛИ. Таким образом, при отсутствии ошибок (т.е. наличии примечания в таблице Примечаний для задачи плана проекта) на экран будет выведен указанный нами символ. При отсутствии ошибок не будет выведено ничего

Используя автозаполнение скопируйте введенную формулу в ячейки ниже.

Выделите диапазон H9:H25 (столбец "Примечание"), установите шрифт Wingdings 2.

В целях самоконтроля и для предоставления информации по проекту руководству и иным внешним пользователям, следует включить в план проекта информацию о ходе выполнения работ – процент завершения и индикацию завершенности задачи.

Создадим столбец "Завершено, %" (O), установим для ячеек столбца (P9:P25) процентный числовой формат.

Создадим столбец "Завершено" (N), в котором будет автоматически отображаться индикатор завершенности работы. Введем в ячейку N9 формулу:

Таким образом, если процент завершения задачи равен 100%, результат формулы будет 1, в противном случае: -1.

Теперь применим такой формат ячеек, при котором для завершенной задачи вместо цифры "1" будет отображаться, например, флажок. Выделите диапазон N9:N25, установите формат ячеек "u;;" и примените шрифт Wingdings.

Используя автозаполнение скопируйте введенную формулу и форматирование в ячейки ниже. Теперь напротив каждой завершенной задачи будет отображаться индикатор ее завершенности.

Диаграмма Ганта (Gantt chart или ленточная диаграмма, график Ганта) является популярным типом столбчатых диаграмм (гистограмм), который используется для иллюстрации плана, графика работ по какому-либо проекту. Диаграмма Ганта используется в качестве метода планирования проектов.

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

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

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

Для начала создадим временную шкалу. Выделим для диаграммы столько столбцов, сколько нам нужно для отображения. В данном примере нам необходимо видеть задачи в пределах одного месяца. Поэтому выделяем под диаграмму 31 столбец (R:AV) – по максимальному количеству дней в месяце.

В строке заголовка (R7:AV7) укажем даты проекта:

• начальная дата диаграммы (R7) должна соответствовать дате начала проекта (J4)

• каждая последующая дата (S7, T7 и т.д.) должна зависеть от предыдущей даты, чтобы при изменении даты начала проекта они автоматически пересчитывались.

=R7+1 – для ячейки S7

Применим к оси дат следующее форматирование:

• изменим числовой формат на более лаконичный, например "ноя 05, 13", для чего введем в поле Тип шаблон "МММ ДД, ГГ "

• для экономии места повернем текст в ячейках на 90 градусов.

В строке ниже (R8:AV8) укажем кратко дни недели. Excel имеет встроенный функционал перевода даты в дни недели применив специальный формат ячейки ("ДДД "), однако такой формат возвращает двухсимвольное сокращение (например, "Вт"), что для нас будет излишне – достаточно одной буквы. Чтобы максимально сократить место сделаем следующее:

• Создадим новое имя "weekdays". Перейдем на вкладку Формулы – группа Определенные имена – Присвоить имя
• Введем имя – "weekdays" и значение диапазона:

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

• В строку R8:AV8 введем следующую формулу:

=ЛЕВСИМВ(ИНДЕКС(weekdays;ДЕНЬНЕД(R7;2));1) – для ячейки R8

• ДЕНЬНЕД(R7;2) – узнаем день недели для даты, находящейся в ячейке R7, при этом второй параметр функции ("2") означает, что понедельник является первым днем недели (Пн = 1, Вт = 2, …, Вс = 7)
• ИНДЕКС(weekdays;ДЕНЬНЕД(…)) – находим в ранее созданной переменной "weekdays" текстовое значение дня недели, соответствующее искомой дате
• ЛЕВСИМВ(ИНДЕКС(…);1) – возвращаем в ячейку первый символ дня недели

Таким образом, мы получим следующую ось дат (дата начала проекта равна 01.11.2013):

Теперь создадим универсальный механизм перемещения по временной шкале. Добавим на лист с диаграммой Ганта полосу прокрутки: на ленте перейдем на вкладку Разработчик – группа Элементы управления – Вставить – Полоса прокрутки. Нарисуем на листе над временной шкалой горизонтальную полосу.

Кликнув правой кнопкой мыши на созданной полосе прокрутке перейдем в ее свойства (Формат объекта…). Изменим следующие параметры:

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

Данное значение не должно быть меньше предполагаемой длительности всего проекта. Например, если проект длится 3 месяца, то необходимо указать значение 93 (31 день * 3 месяца).

• Связь с ячейкой. введем ссылку на ячейку, в которой будет содержаться текущее положение ползунка полосы прокрутки. Связанная ячейка будет возвращать текущее значение, соответствующее положению ползунка.

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

Установим связь с ячейкой R6.

После добавления полосы прокрутки изменим формулу первой даты на временной шкале (ячейка R7) на следующую:

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

Диаграмма будет располагаться в диапазоне, соответствующему количеству задач – по строкам и количеству периодов – по столбцам. В нашем случае: R9:AV25.

Выделим весь диапазон, включая временную шкалу (R7:AV25) и добавим правила условного форматирования, по которым будет строиться диаграмма. Перейдем на вкладку Главная – раздел Стили – Условное форматирование – Управление правилами…

Создадим новое правило с использованием формулы для определения форматируемых ячеек: выделение общей длительности задачи.

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

Обратите внимание на фиксирование строк и столбцов в ссылках на ячейки:

• R$7 – при применении условного форматирования Excel будет оперировать только строкой 7, на которой располагается временная шкала
• $L7, $M7 – при применении условного форматирования Excel будет оперировать только столбцами L и M, в которых введены даты начала и окончания задач

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

• ОКРУГЛ(($M7-$L7+1)*$O7;0) – переведем процент завершения задачи в календарные дни: вычислим общую длительность задачи в календарных днях и умножим на процент ее завершенности. Полученный результат округлим до целого значения
• $L7+ОКРУГЛ(…) – определим календарную дату, соответствующую проценту завершения задачи.

Создадим новое правило с использованием формулы для определения форматируемых ячеек: выделение выходных дней.

В том случае, если дата на временной шкале является субботой или воскресеньем, она будет заштрихована (подробнее о функции ДЕНЬНЕД см. раздел Создание временной шкалы данной статьи).

Создадим новое правило с использованием формулы для определения форматируемых ячеек: выделение текущей даты.

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

В диспетчере правил условного форматирования расположите созданные нами правила в следующем порядке:

• Форматирование столбцов плана проекта,
• Форматирование процента завершенности задачи,
• Форматирование общей длительности задачи
• Форматирование выходных дней,
• Форматирование текущей даты.

Перемещайте правила используя кнопки вверх и вниз.

Затем напротив каждого правила снимите флажок в графе Остановить, если истина.

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

Для добавления возможности указания вех создадим столбец "Веха" (I), в который будем вводить цифру "1", если конкретная работа является вехой.

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

Выделите ячейки столбца Веха и перейдите в меню Формат ячеек. Выберите последний пункт из списка Числовых форматов и в поле Тип введите: "¦;;". Первая часть введенного формата отвечает за формат числа больше нуля, вторая (после знака ";") – за формат отрицательного числа, третий – за формат нулевого значения.

Таким образом, при вводе в ячейку любого числа больше 0 Excel заменит его на указанный нами знак, а при вводе любого другого числа (0 и меньше) – оставит ячейку пустой.

Теперь, чтобы проставлять соответствующие отметки на диаграмме Ганта, необходимо:

• Доработать ранее рассмотренные правила условного форматирования

Ранее мы использовали следующее правило для индикации продолжительности задачи:

Видоизменим это правило так, чтобы вехи не выделялись цветовой заливкой (изменения выделены жирным шрифтом):

• Ввести в ячейки того же диапазона формулы и применить форматирование ячеек, рассмотренное выше (изменить шрифт на Verdana и применить формат "¦;;").

Введите формулу в ячейку R9 и скопируйте ее в другие ячейки диапазона или воспользуйтесь автозаполнением.

Ждем ваших предложений по функционалу, который вы хотели бы видеть в плане проекта на базе Microsoft Excel.

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