Проектная диаграмма Ганта с помощью условного форматирования
Если вам приходится часто строить графики проектов, отпусков, тренингов и т.д., то есть любых долгих и сложных многоэтапных мероприятий, то этот простой, но красивый прием придется вам ко двору. Предположим, что у нас имеется вот такая таблица:
Идея состоит в использовании условного форматирование, чтобы заставить Excel заливать ячейку заданным цветом, если она по дате попадает между началом и концом этапа. Для этого выделите весь диапазон, где должна быть диаграмма (в нашем примере - начиная с ячейки D3 и до конца таблицы) и затем жмем на вкладке Главная (Home) кнопку Условное форматирование - Создать правило (Conditional Formatting - New Rule), выбираем последний тип Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format) и вводим формулу:
По сути, эта формула делает простую вещь - функция И (AND) проверяет обязательное выполнение двух условий, чтобы дата для текущей ячейки была позже, чем дата начала этапа и раньше даты окончания. Если оба эти условия выполняются, то ячейка находится внутри этапа, т.е. должна быть залита. Нажав на кнопку Формат (Format) можно выбрать необходимый цвет.
Просто и красиво, не правда ли?
В более "навороченном" варианте такая диаграмма может менять шаг временной шкалы, учитывать наличие выходных и праздничных дней и подсвечивать выбранную строку/столбец:
Основа здесь все та же - условное форматирование. Плюс добавлены:
- задержка перед началом этапа (может быть положительно или отрицательной или =0)
- проценты выполнения по каждому этапу с их подсветкой на диаграмме
- функция РАБДЕНЬ (WORKDAY) для расчета не календарных, а рабочих дней
- подсветка праздников и выходных все тем же условным форматированием с помощью функции ДЕНЬНЕД (WEEKDAY)
- координатная подсветка текущей строки и столбца с помощью специального макроса
Microsoft Project нам так, конечно, не переплюнуть, но приблизиться к нему в визуальной части вполне можно :)
Ссылки по теме
Но почему бы Вам не попробовать переплюнуть Project не столько в визуальной части, сколько в совместном планировании и актуализации плана в подобном шаблоне по % выполнения и физ. объемам работ?
MS Project в полном объеме вообще мало кто использует, в основном - ограничиваются рисованием диаграммы Ганта. А вот задача периодического сбора актуальных данных о выполненных объемах работ от нескольких ответственных и импорт данных в Project все равно решается дополнительными дорогими надстройками (типа Turbo EPM) через таблицы Excel:( . Так может рациональнее изначально выстраивать оперативную базу данных План-Факт-Остаток по задачам проекта в файле Excel??
Подкину идею коллеге из PMI-сертифицированных.
Вместе что-нибудь придумаем.
В среде Project - это сделать намного сложнее, т. к. с ним работают только "наверху", а плановые и фактические данные проекта рождаются "внизу", теми кому роднее Excel.
Подскажите, как реализовано отображение процента выполнения этапа работ? Насколько я понимаю, это сделано через условное форматирование. Пытался разобрать пример сам, но пока не понял как.
У меня 2007 версия.
Если да, то попробуйте заменить РАБДЕНЬ на WORKDAY - возможно у вас Office (и надстройка) изначально был англоязычный, но поверх установили пакет русификации.
Для устранения данной неприятности предлагаю в режиме конструктора выбрать флажок подсветки. В свойствах дать ему оригинальное имя - у меня cbLight.
в VBA коде заменить "If NoEvents Then Exit Sub" на "If (Not cbLight.Value) Then Exit Sub"
теперь проверка будет напрямую состояния флажка ,и сбоев не будет
Как только галку дезактивируем, все работает, но уже я так полагаю график не учитывает выходные и праздники. Как это исправить? Я так же менял рабдень на workday, т.к. офис англоязычный 2007.
Уже долго мучаюсь, но ничего не выходит.
Помогите, пожалуйста!
Огромное спасибо!
Алена
заранее спасибо!
У меня вопрос: каким образом можно сделать, чтобы какие-либо даты выделять другим цветом как "dead-lines"?
Заранее благодарю за ответ.
в ver. 3.0 "Шаг временной шкалы" лучше поменять условие проверки числа с "больше 1" на "больше или равно 1". Может народ запутать.
Добавил отставание от плана, если кому нужно:
=И(A$3=C$13;B$2>=$A13;B$2<=$B13) и меня окрашиваются только по диапазону дат, не учитывая условие совпадения цифры из столбца А.
И еще, пользуясь случаем, как сделать, чтобы при окрашивании на первую из закрашиваемых ячеек, скажем, добавлялись соответстсвующие названия-картошка, капуста ..
Заранее благодарю, если что-то непонятно, спросите:)
Надеюсь вы увидите мой вопрос, смотрю отвечали в 2014 году
У меня получилось все сделать с нуля. Но проблема такая у меня проект на три года, и когда я меняю шаг временной шкалы на 30 дней, происходит сдвиг неудобный по датам,первая ячейка стоит 1.06.15 (дата начала проекта), вторая - 01.07.15,а третья уже 31.07.15,а не 1.08.15
Можно ли как-то менять шаг временной шкалы и в днях и в месяцах?
=И(D$1>=$B2;D$1<=$C2).
Попробовал даже вставить в Ваш пример, не работает
Например диаграмм построена черерез месяц, т.е. 01.01 01.02. 01.03, а работы выполняются с 02.02 по 25.02. Как решить эту проблему визуализации? нужно что бы закрашивалось. спасибо
А есть ли вариант настроить фильтры так, чтобы если скрываешь строку этапа, то и столбцы этого этапа тоже бы скрывались?
У меня Office 2019 для MacOS.
Суть вопроса - Имеется файл контроля проекта () в нем уже созданы правила условного форматирования с использованием формул для определения форматируемых ячеек.
Формулы прописана как путь к ячейке с присвоенным именем.
Теперь несколько вопросов:
- Как найти ячейку в которой прописана данная формулы для последующего его изменения?
- Как изменить формулы чтобы количество дней на выполнение задачи (как план так и факт) не увеличивалось за счет выходных дней.
Заранее спасибо за ответ.Благодарю за этот урок – очень полезно, для меня по крайней мере. Но есть один элемент, который я никак не соображу, как его добавить. Мне желательно, чтобы по окончанию заливки каждого этапа, на графике проекта, было указано название самого Завершившего этапа.
Как в вашем примере Gantt 2.0: в ячейке L9 было указано содержание ячейки B9. Соответственно, при изменении значений начала и конца этапа – название этапа перемещалось бы вместе со сроками его выполнения.
Заранее благодарю за помощь.
Николай
Можете помочь подсказать?
Я разработал на основе диаграммы Ганта
Вы можете попробовать ее без какой-либо регистрации,
Буду рад любым отзывам