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

330928 24.10.2012 Скачать пример

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

gantt-cond-formatting1.png

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

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

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

    gantt-cond-formatting3.png

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

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

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

gantt-cond-formatting4.png

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

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

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

Ссылки по теме


08.01.2013 05:31:17
Красивое решение для расчета и визуализации графика:!:
Но почему бы Вам не попробовать переплюнуть Project не столько в визуальной части, сколько в совместном планировании и актуализации плана в подобном шаблоне по % выполнения и физ. объемам работ?
MS Project в полном объеме вообще мало кто использует, в основном - ограничиваются рисованием диаграммы Ганта. А вот задача периодического сбора актуальных данных о выполненных объемах работ от нескольких ответственных и импорт данных в Project все равно решается дополнительными дорогими надстройками (типа Turbo EPM) через таблицы Excel:( . Так  может рациональнее изначально выстраивать оперативную базу данных План-Факт-Остаток по задачам проекта в файле Excel??
11.01.2013 18:47:25
Почему бы и нет :)
Подкину идею коллеге из PMI-сертифицированных.
Вместе что-нибудь придумаем.
12.01.2013 06:14:15
Могу описать Вам  базовый, востребованный в УП сценарий, который красиво можно реализовать с новыми возможностями сводных таблиц Excel 2013.
В среде Project - это сделать намного сложнее, т. к. с ним работают только "наверху", а плановые и фактические данные проекта рождаются "внизу", теми кому роднее Excel.:)
Использую подобную (с условным форматированием) диаграмму для работы.
Подскажите, как реализовано отображение процента выполнения этапа работ? Насколько я понимаю, это сделано через условное форматирование. Пытался разобрать пример сам, но пока не понял как.
18.04.2013 00:02:37
Еще одно условие, которое накладывается поверх построенных синих прямоугольников-этапов.
Разобрался, спасибо.
18.05.2013 19:15:10
В скачаном примере не работает функция РАБДЕНЬ. И никак не разберусь в чем проблема.
19.05.2013 13:10:12
Версия Excel у вас какая?
20.05.2013 16:43:24
Николай, спасибо за оперативность.
У меня 2007 версия.
26.05.2013 09:52:49
Скорее всего проблема с функцией РАБДЕНЬ из надстройки Пакет Анализа. Он у вас подключен? Проверить можно по Файл - Параметры - Надстройки - Перейти.

Если да, то попробуйте заменить РАБДЕНЬ на WORKDAY - возможно у вас Office (и надстройка) изначально был англоязычный, но поверх установили пакет русификации.
28.05.2013 20:44:37
Николай, большое Вам спасибо.
29.05.2013 12:01:51
Заработало? :)
В последнем примере макрос подсветки постоянно дает сбой. Причина тому, что нет проверки состояния флажка. Особенно, если как обычно с план-графиками - открыл, посмотрел, закрыл.
Для устранения данной неприятности предлагаю в режиме конструктора выбрать флажок подсветки. В свойствах дать ему оригинальное имя - у меня cbLight.
в VBA коде заменить "If NoEvents Then Exit Sub" на "If (Not cbLight.Value) Then Exit Sub"

теперь проверка будет напрямую состояния флажка ,и сбоев не будет
02.08.2013 15:22:26
Так же скачал пример, но при активной галке "Измерять длительность задержки в рабочих днях (с учетом выходных и праздников)", в столбцах D и F появляется "#NAME?", а сам график ничего не отражает кроме заголовков столбцов как  "#NAME?".
Как только галку дезактивируем, все работает, но уже я так полагаю график не учитывает выходные и праздники. Как это исправить? Я так же менял рабдень на workday, т.к. офис англоязычный 2007.  
04.08.2013 16:10:24
Не факт, что нужно менять РАБДЕНЬ на WORKDAY - это зависит не только от языка Office, но и от установленных языковых пакетов. Надо смотреть и пробовать оба варианта.
05.08.2013 07:24:15
Поработаю, попробую исправить.
24.09.2013 16:19:53
А как в самом простом (1-м) варианте сделать учет выходных?
Уже долго мучаюсь, но ничего не выходит.
Помогите, пожалуйста!

Огромное спасибо!
25.09.2013 14:50:40
Нужно сначала добавить в таблицу длительность каждого этапа. И дату окончания считать не прибавлением длительности к дате начала, а с помощью функции РАБДЕНЬ. Но это как раз и получится второй вариант.
27.01.2014 11:59:17
Добрый день Николай, помогите советом, я использую Ваш шаблон для проектов в Эксель, но размер файла огромен, не получается ни убрать миллион строк вниз ни другим способом уменьшить размер файла. Что делать?
Алена
28.01.2014 10:53:37
Пришлите файлик - посмотрю, ага?
29.09.2014 15:25:01
Добрый день! все мучаюсь...а если нужно подготовить график отпусков на год по всем сотрудникам (чтобы был на одном листе, все 12 месяцев), причем у каждого может быть несколько периодов отпусков. как лучше реализовать такой вид графика отпусков, либо проще сделать макросами с формами?
заранее спасибо!
24.10.2014 16:40:05
Добрый день! Во-первых, хотела бы поблагодарить Вас за такой информативный и полезный сайт.
У меня вопрос: каким образом можно сделать, чтобы какие-либо даты выделять другим цветом как "dead-lines"?
Заранее благодарю за ответ.
09.12.2014 12:35:03
Добрый день! А как мне сделать такое условное форматирование на участке 365 ячеек вправо и на 1000 строк вниз?
22.12.2014 02:18:53
Добрый день! Спасибо! Очень грамотное решение!
в ver. 3.0 "Шаг временной шкалы"  лучше поменять условие проверки числа с "больше 1" на "больше или равно 1". Может народ запутать.
Добавил отставание от плана, если кому нужно: ссылка
13.01.2015 15:17:33
Добрый день! Помогите пожалуйста..не хватает знаний:cry:   Как сделать календарный график, но так чтобы один день занимал 2 ячейки -до 14.00, после 14.00 (обеденный)..и соответственно не в ручную числа проставлять на месяц. числа на месяц должны идти в строку.В дальнейшем это будет диаграмма ганта с помощью условного форматирования (хотя может я хочу невозможного..) спасибо если кто-нибудь откликнется  
13.01.2015 16:58:11
"В более "навороченном" варианте такая диаграмма может менять шаг временной шкалы,..." - Боже!!! ну как же это сделать??????:?:
18.06.2015 13:57:05
Добрый день! помогите кто может:) делаю график на основе условного форматирования, и что-то явно делаю не так, а вот что-не пойму. вот пример   задача, чтобы окрашивались ячейки по трем условиям-первое-при совпадении числа из столбца А с числом из столбца С(внизу,начиная с 13 строки) и при попадании даты из верхней строки дат в диапазон из нижней части таблицы. Пишу формулу так
=И(A$3=C$13;B$2>=$A13;B$2<=$B13) и меня окрашиваются только по диапазону дат, не учитывая условие совпадения цифры из столбца А.
И еще, пользуясь случаем, как сделать, чтобы при окрашивании на первую из закрашиваемых ячеек, скажем, добавлялись соответстсвующие названия-картошка, капуста ..
Заранее благодарю, если что-то непонятно, спросите:)
27.07.2015 17:37:14
Добрый вечер, Николай!!
Надеюсь вы увидите мой вопрос, смотрю отвечали в 2014 году :)
У меня получилось все сделать с нуля. Но проблема такая у меня проект на три года, и когда я меняю шаг временной шкалы на 30 дней, происходит сдвиг неудобный по датам,первая ячейка стоит 1.06.15 (дата начала проекта), вторая - 01.07.15,а третья уже 31.07.15,а не 1.08.15
Можно ли как-то менять шаг временной шкалы и в днях и в месяцах?
30.07.2015 14:28:55
не ответит видимо никто.....
09.09.2015 00:28:02
Коллеги, подработал чуть график Gantt 3.0. Теперь отображается DeadLine по каждой позиции. Кому нужно - отпишитесь на адрес artkor22@mail.ru.
28.12.2015 00:42:13
Все вроде просто, но пробовал почему то не получается. Ячейки не выделяет. Где ошибка.
=И(D$1>=$B2;D$1<=$C2).
Попробовал даже вставить в Ваш пример, не работает :(
12.08.2016 18:03:45
У меня тоже не работало пока я не понял что мои диапазон ячеек немного другой. Проверте, начинается ли он с первых-вторых строк, и такие-ли столбцы.
MEP
17.07.2017 06:30:55
Столкнулся с проблемой, что если временной интервал диаграммы больше чем срок исполнения работ, то ячейка не закрашивается.
Например диаграмм построена черерез месяц, т.е. 01.01 01.02. 01.03, а работы выполняются с 02.02 по 25.02. Как решить эту проблему визуализации? нужно что бы закрашивалось. спасибо