Строим умную диаграмму Ганта в Р7-Офис

Диаграммы Ганта — наверное, один из самых полезных инструментов для управления проектами, что придумало человечество за всю свою историю. Каждый мало-мальски сложный проект состоит из некоторого множества составляющих, процессов со своим временем старта и своей длительностью. Это касается любого проекта, как задач уровня «сварить борщ» (от покупки ингредиентов до финального «настояться за ночь»), так и проектов типа «построить атомную электростанцию». Просто уровни сложности разные. И если в деле строительства атомной электростанции без сложной системы управления проектами не обойтись, то в случае не таких глобальных затей несложный контроль можно наладить в табличном редакторе, например, Р7-Офис.

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

При этом показатели прогресса процесса решения той или иной задачи в составе проекта у нас рассчитывается автоматически в зависимости от условной «даты отчета», что прописывается в поле B1. Кстати, на самом графике индикация текущего момента тоже присутствует — обратите внимание на желтую пунктирную линию. При этом из такого, достаточно громоздкого, вида наша таблица легко принимает более компактное и удобное для восприятия представление:

Приступим?

Итак, у нас есть условный список задач (пяти для примера нам хватит), а также даты их старта и завершения. Также мы предусмотрим отдельные столбцы для подсчёта общей длительности этапа, числа отработанных по задаче дней на текущую дату и прогресса в работе. Столбец «Прогресс» расположим рядом с «Этапами», так как мы планируем эту область закрепить для удобной навигации собственно по диаграмме Ганта, которая может быть и слишком длинной, чтобы поместиться на экране целиком.

И давайте сразу сгруппируем столбцы, которые мы в дальнейшем планируем сворачивать. На вкладке «Данные», выделив эти самые столбцы (на шкале, а не сами поля!), жмём «Сгруппировать».

Посчитать общую продолжительность этапа в днях достаточно просто — из даты финиша вычитаем дату старта, прибавляем единицу (день старта тоже ведь считается).

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

Великий и ужасный ЕСЛИ

Получилось? Приступим к вычислению прогресса. Для этого сначала неплохо бы ввести текущую дату:

Чтобы посчитать число отработанных дней, воспользуемся функцией ЕСЛИ. Логика её работы довольно проста. Она проверяет, соответствует ли значение в определённой ячейке (у нас это текущая дата в поле B1, и её обязательно надо закрепить в нашей формуле символами доллара — $B$1 — чтобы при протягивании она обращалась к тому же самому полю) определённому условию. А потом через точку с запятой — что выдавать, если соответствует, и что, если не соответствует.

Но нам в одной формуле нужно будет уместить два таких сравнения. Первое ЕСЛИ проверит, наступила ли текущая дата раньше, чем дата начала этапа. Если так ($B$1<C5), значит работа еще даже не началась, и формула возвращает ноль.

=ЕСЛИ($B$1<C5;0;(…))

Если же текущая дата соответствует началу этапа или ещё более поздняя, нам пригодится второе ЕСЛИ. Оно проверит, не закончился ли уже этап к дате отчета. Если это так ($B$1>D5), значит этап завершен и отработаны все дни из столбца E (соответственно, возвращается значение E5). Дописываем формулу:

=ЕСЛИ($B$1<C5;0;ЕСЛИ($B$1>D5;E5;

Если ни одно из этих условий не выполняется, значит этап сейчас в процессе выполнения. В этом случае формула считает, сколько дней прошло от начала этапа до введённой в качестве текущей даты: $B$1-C5+1. Напомним, единица добавляется, чтобы включить в расчет и сам день старта.

=ЕСЛИ($B$1<C5;0;ЕСЛИ($B$1>D5;E5;$B$1-C5+1))

На финише не забываем проставить две закрывающие скобки (завершили аргумент вложенного ЕСЛИ и «большого» ЕСЛИ). И протягиваем вниз.

Отлично, а теперь просчитаем прогресс. Если делать по-простому, то формулы вида F5 (число отработанных дней) разделить на E5 (полная длительность этапа) достаточно. Но представьте, что будет, если у нас не для всех задач определены точные сроки (и продорлжительность, соответственно)? Возникнет ошибка деления на ноль, которая нам не нужна. Так что с помощью той же ЕСЛИ организуем проверку как раз на этот случай. Если в «Дней всего» ноль, вернуть ноль, если не ноль — разделить отработанные на полную длительность. Выглядеть это будет так:

=ЕСЛИ(E5=0; 0; F5/E5)

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

Делаем ось времени красиво

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

Дальше можно протянуть её за уголок ячейки достаточно далеко вправо, но тогда мы получим что-то такое:

Нам этого не хочется, так что поколдуем с форматом. В поле первой проставленной даты либо в контекстном меню, либо в выпадающем списке на вкладке «Главная» выбираем «Другие форматы», затем — выбираем категорию «Особый».

А потом убираем /ММ/ГГ, оставив только ДД, чтобы сэкономить место и сделать график более компактным.

И протянем вправо. Всё равно ряд дат получается довольно длинным — ячейки слишком широкие по умолчанию, так что попробуем их уменьшить. Выделим столбцы на строке заголовков (клик на первый в раду, а потом через Shift — на последний; как вариант — то же самое с клавиатуры). Вызываем контекстное меню строки заголовков (а не какой-нибудь ячейки). И через пункты «Задать ширину столбца» и «Особая ширина столбца» задаём три символа.

Так лучше, не правда ли?

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

Теперь займёмся днями недели — эта шкала лишней точно не будет, плюс, на ней можно визуально отметить выходные. Здесь нам пригодится, во-первых, функция ДЕНЬНЕД, которая возвращает нам порядковый номер дня недели в зависимости от значения даты в соответствующей строке. В нашем случае — ДЕНЬНЕД(G2; 2), где 2 после точки с запятой просигнализирует редактору, что неделя у нас с понедельника стартует, а не с воскресения, как в западной традиции.

Но номер дня недели — это только полдела. Воспользуемся функцуией ВЫБОР, которая в зависимости от значения в ячейке выбирает соответствующее слово из списка. А список будет состоять из сокращений дней недели — «Пн» «Вт» «Ср» «Чт» «Пт» «Сб» «Вс». И да, кавычки здесь обязательны. В итоге должно получиться что-то такое:

=ВЫБОР(ДЕНЬНЕД(G2; 2); «Пн» «Вт» «Ср» «Чт» «Пт» «Сб» «Вс»)

Протягиваем получившуюся формулу по ряду и отцентрируем для красоты.

Условное форматирование — залог наглядности!

Для начала закончим со шкалой с днями недели — надо визуально выделить выходные. Выделим всю строку с днями недели (начиная с G3) и на вкладке «Главная» в меню кнопки условного форматирования выберем «Формула».

Здесь воспользуемся функцией ИЛИ. Она применяет выбранное пользователем условное форматирование (мы выберем розоватую заливку и красный шрифт для заметности), если значение в ячейке соответствует хотя бы одному из условий. Из двух в нашем случае. Нас интересует шестой или седьмой день недели. Условия сформулируем с помощью уже известной нам формулы ДЕНЬНЕД(G2; 2), только номер строки закрепим символом доллара.

=ИЛИ(ДЕНЬНЕД(G$2; 2)=6; ДЕНЬНЕД(G$2; 2)=7)

Результат:

А где же диаграмма?

Действительно, пора заняться главным. Разумеется, тоже с помощью условного форматирования по формуле. Выделяем весь диапазон, в котором расположится график. Можно мышкой через зажатый Shift, выделив последовательно левую верхнюю и правую нижнюю ячейки — теперь у нас эта область таблицы довольно компактна, так что скроллить не придётся. А понимать, какие ячейки надо закрашивать, будем через функцию И (определяет истинность, соответствие заданному критерию).

Сначала закрасим ярко-зелёным уже пройденную часть проекта. Нам нужно проверить, попадает ли дата календаря в G2 и далее (запишем как G$2, где символ доллара — для закрепления строки в формуле) в период от старта до количества уже отработанных дней. Чтобы покраситься, G$2 должно быть больше или равно дате старта, которая записана в поле С5 (в формуле — $C5, нам же надо закрепить столбец), и в то же время меньше просуммированных даты старта и количества уже отработанных дней — ($C5+$F5). По итогу выражение примет такой вид:

=И(G$2>=$C5; G$2<($C5+$F5))

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

Что ж, всё покрасилось правильно:

А теперь можно, не снимая выделения, покрасить ещё не выполненную часть проекта в более светлый зелёный. Действовать будем по тому же принципу, что и в случае с закраской уже пройденного пути. Только теперь нам нужно, чтобы дата для закраски попадала в интервал между днём, следующим за текущим (что уже закрашен ярким зелёным) и датой финиша задачи. То есть, условие первое: G$2 больше или равно (к дате старта прибавляем отработанные дни) $C5+$F5. Также G$2 должно быть меньше или равно $D5. Получится:

=И(G$2>=($C5+$F5); G$2<=$D5)

Чтобы более чётко разделить задачи, можно применить обрамление в виде горизонтальных линий.

А теперь займёмся идентификацией текущей даты. Тоже через условное форматирование и формулу. Благо, она будет совсем короткой: =G$2=$B$1. То есть, если данные о дате из второй строки (которая зафиксирована «долларом») соответствуют значению в поле B1 (тут зафиксировать надо и строку и столбец, чтобы формула обращалась только к одной ячейке) — применим условное форматирование. Мы выберем в качестве такового желтый пунктир.

Теперь текущую дату на графике легко опознать:

Проверим, меняется ли процент исполнения и сам график в зависимости от введённой даты.

Значит, всё работает правильно:)