Строим диаграмму Ганта в Excel

 На дворе 2018-й год, на компьютерах - 2016-й Excel, а стандартный макет построения такой полезной и нужной проектной диаграммы Ганта в наш любимый табличный процессор до сих пор никто не добавил. Тем не менее, нас снабдили всем необходимым инструментарием для построения этого типа диаграммы. Мы уже давали Вам файл с примером построения (вот тут), а сегодня научим Вас строить диаграмму самостоятельно. 

Существует два способа построения:

1) Модификация ленточной диаграммы с накоплением;

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

Способ 1. График Ганта через модификацию Ленточной диаграммы с накоплением

1. Организация данных для построения

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

 Обратите внимание, что столбец со списком этапов не имеет заголовка в шапке (это важно, иначе Excel примет даты начала этапов за ряд данных, а не за подписи оси). Также, если Вам нужно в проекте повторить какой-то этап несколько раз, то для каждого такого раза придется создавать дополнительные 2 столбца - один с указанием дней перерыва между повторениями, а второй - с длительностью повтора этапа. В примере выше "Тестирование проекта" предполагается начать 19.09.2017, проводить в течение 2 дней, а затем после перерыва в 2 дня повторить еще раз, снова на пару дней. 

2. Создание диаграммы

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

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

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

3. Настройка диаграммы

Для начала стоит убрать с диаграммы легенду (в диаграмме Ганта ее наличие необязательно в большинстве случаев). Для этого выделите ее кликом мыши и нажмите Delete. 

Далее выделите подписи оси Y (названия этапов) и нажмите Ctrl+1 или Правая кнопка мыши - Формат Оси. В открывшейся панели инструментов найдите вкладку Параметры Оси и установите галочки напротив пунктов "в максимальной категории" и "обратный порядок категорий" (это нужно, чтобы этапы начинались сверху с самого первого, а ось подписей дат была внизу).

  

 Теперь настроим вторую ось. Выделите подписи оси X (даты) и нажмите Ctrl+1 или Правая кнопка мыши - Формат Оси. В открывшейся панели инструментов найдите вкладку Параметры Оси. Установите минимальную границу (например, дату начала первого этапа). Если ваша версия Excel не позволяет ввести дату в привычном формате, то придется ввести ее числовой эквивалент (чтобы узнать его можно на листе Excel временно перевести ячейку с датой в числовой формат, переписать значение, а затем вернуть обратно). Максимальная граница обычно достаточно удачно определяется автоматически, но при необходимости ее тоже можно задать вручную.В этой же панели можно установить цену делений (7 - для недели, 10 - для декады и т.д.).  

 Далее нужно спрятать лишние ряды (которые служат для визуализации времени начала этапов, см. рисунок ниже). Выделите ненужный ряд (кликнув на нем левой кнопкой мыши), нажмите Ctrl+1 или Правая кнопка мыши - Формат ряда данных. В открывшейся панели инструментов найдите вкладку Заливка и Границы и установите галочку "Нет заливки".  

 В оставшихся видимых рядах нужно настроить одинаковую заливку (актуально, если вы используете повтор одного и того же этапа), а также настроить ширину полосок на диаграмме (формат ряда данных - параметры ряда - перекрытие рядов: 100%, боковой зазор: 30%). 

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

 Теперь рассмотрим второй способ.

Способ 2. График Ганта через Условное форматирование

1. Организация данных для построения

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

 

2. Создание правила условного форматирования

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

 На вкладке "Главная" найдите "Условное форматирование" и нажав, выберите "Создать правило...". Выберите тип правила: "Использовать формулу...", после чего в строку, выделенную на рисунке ниже, нужно будет ввести формулу правила условного форматирования.  

В приведенном примере формула для ввода будет иметь следующий вид: 

=ИЛИ(И(G$1>=$C2;G$1<$C2+$D2);И(G$1>=$C2+$D2+$E2;G$1<$C2+$D2+$E2+$F2)). 

Разберем ее подробнее. Формулу начинается с функции ИЛИ. Она нужна тогда, когда Вы будете использовать повторяющиеся этапы в Вашем проекте. Для каждого такого этапа внутри функции ИЛИ есть отдельная конструкция с функцией И (в нашем случае один из этапов повторяется 2 раза, а значит и конструкций внутри "ИЛИ" будет 2).

Первая из них -  И(G$1>=$C2;G$1<$C2+$D2). Эта часть формулы отвечает за заливку цветом первого повторения каждого этапа. G1 - ссылка на ячейку с датой (первая дата в шапке), а C2 и D2 - ячейки с датой начала и длиной этапа соответственно. Обратите внимание, что для G1 закреплена строка (так как шапка всегда будет в одной и той же строке), а для других ячеек - столбцы (так как столбцы будут те же, а вот строки мы будем перебирать). Формулу можно прочитать как: И дата в шапке больше или равна дате начала этапа, И дата в шапке меньше даты начала + длительности этапа (а дата начала + длительность = следующий день после окончания этапа). Если условие истинно, то дата в шапке находится в пределах этапа и ячейку нужно закрасить. 

Вторая конструкция И(G$1>=$C2+$D2+$E2;G$1<$C2+$D2+$E2+$F2)) работает аналогично, только для даты начала этапа (а это уже будет не первый, а повторный запуск этапа) приходится суммировать дату начала первого раза, продолжительность, перерыв и т.д. Разумеется, Вы можете переорганизовать данные так, как Вам удобно и избежать подобной необходимости. Главное - понять принцип построения. 

В итоге вся формула работает так: ИЛИ дата в шапке попадает в первое повторение этапа, ИЛИ во второе. Если попадает - ячейка заливается цветом. 

После ввода формулы нужно нажать кнопку "Формат" и задать способ выделения ячеек (в нашем примере мы применили заливку и штриховку ячейки). Нажимаете ОК и получаете результат. 

 Выбирайте подходящий Вам способ и пользуйтесь. Удачи в Ваших проектах! 

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

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

С уважением, команда tDots.ru