Как за 10 шагов сделать диаграмму Ганта в Excel с автоматическими расчетами

Кайтен

Управляйте задачами, проектами и командой в одном месте

Попробовать бесплатно

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

В этой инструкции покажем, как построить диаграмму Ганта в Excel, чтобы она сама перестраивала полосы после изменения дат, показывала прогресс и просрочки, считала общую готовность проекта и отображала план по дням, неделям и месяцам. Для этого не понадобятся MS Project и платные надстройки. 

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

Шаг 1. Создаем таблицу задач

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

Создайте пустой файл и переименуйте лист в «Гант». Впишите заголовки в строку 6. Строчки выше пока не нужно трогать — там мы добавим панель показателей. Список заголовков должен быть таким:

Теперь добавим несколько задач для примера. Они нужны для проверки: по ним мы убедимся, что диаграмма строится и меняется вместе с датами. Заполните три колонки — B, E и F:

• нажмите на B7 и впишите название первой задачи;
• в E7 — дату начала;
• F7 — дату окончания. 

По такому же принципу заполните строки 8 и 9. В качестве примеров можно взять свои задачи или наши из таблицы:

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

Обязательно проверьте даты — они должны прижиматься к правому краю. Если прижались к левому краю, значит, Excel принял их за текст, поэтому дальше ничего не заработает. В таком случае выделите колонки E и F, задайте формат «Дата» и введите значения заново.

Диаграмма Ганта для управления проектами Детально планируйте крупные проекты в Kaiten. Управляйте ресурсами 
и трудозатратами сотрудников на одном экране.

Попробовать бесплатно

Шаг 2. Строим шкалу недель

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

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

Задайте дату, с которой начнется календарь. Впишите в ячейку A3 дату, с которой хотите видеть проект: день старта работ, начало месяца или начало квартала — что вам удобнее.

В нашем примере это 20.07.2026, и дальше в статье все даты будут от нее.

Нажмите на ячейку J6 и впишите:

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

Нажмите на K6 и впишите =J6+7. В правом нижнем углу ячейки есть маленький квадратик — маркер заполнения. Нажмите на него и, не отпуская кнопку мыши, тяните вправо до колонки BI: получится 52 недели.

Приведите шапку в порядок. Пока колонки широкие, а календарь уходит далеко за край экрана. Исправим это. Для этого выделите колонки J:BI: нажмите на букву J сверху, зажмите Shift и нажмите на BI. Дальше, не снимая выделения, сделайте три вещи:

• правой кнопкой на любую выделенную букву → «Ширина столбца» → впишите 4,6;

• еще раз правой кнопкой → «Формат ячеек» → «Все форматы» → в поле «Тип» впишите ДД.ММ, чтобы в шапке остались только число и месяц;

• на вкладке «Главная» поставьте размер шрифта 8 и выравнивание по центру.

Шаг 3. Создаем правило, которое рисует полосы

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

Выделите сетку. Правило применяется сразу ко всей области, где появятся полосы: от J7 до BI56. Это 52 недели в ширину и 50 строк в высоту — с запасом, чтобы хватило на будущие задачи.

Тянуть мышью через все колонки неудобно. Проще так: слева от строки формул есть поле с адресом текущей ячейки. Нажмите на него, впишите J7:BI56 и нажмите Enter. Выделится вся область, а активной останется J7 — она будет белой на фоне выделения. Именно для нее мы напишем формулу, а Excel уже сам растянет правило на остальные ячейки.

Создайте правило. Для этого:

• Не убирая выделение ячеек, идите по такому пути: «Главная» → «Условное форматирование» → «Создать правило».

• Откроется окно, в нем в разделе «Начертание» выберите «Классическая».
• Ниже появится второй список — выберите в нем «Использовать формулу для определения форматируемых ячеек».

Под списком появится поле для формулы. Вставьте туда:

Осталось задать цвет полосы. В строке «Форматировать с помощью» выберите «пользовательский формат…» — откроется окно «Формат ячеек». Перейдите на вкладку «Заливка» и выберите голубой. Для точного оттенка нажмите «Другие цвета», переключитесь на «Цветовые ползунки», выберите «Шестнадцатеричный цвет» и впишите 9DC3E6.

Закройте «Формат ячеек» кнопкой ОК, затем ОК в окне правила.

Что должно получиться: напротив каждой задачи появилась голубая полоса. Она начинается под неделей, в которую задача стартует, и заканчивается под той, в которую задача завершается.

Проверьте, что диаграмма работает. Поменяйте в F7 дату окончания первой задачи — полоса вытянется. Впишите в A3 дату на месяц позже — шкала и полосы уедут вправо. Потом верните обе даты обратно.

Шаг 4. Добавляем прогресс и просрочку

Сейчас полосы одинаковые: по ним видно сроки, но не видно, что уже сделано. Исправим это:

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

• Выделите H7:H56 и нажмите кнопку со значком % на вкладке «Главная» — так Excel поймет, что в колонке проценты. 

• Впишите готовность своих задач: в H7 первую, в H8 вторую и так далее. В нашем примере это 100%, 45% и 0%.

Обязательно ставьте знак процента вместе с числом, так как Excel хранит проценты долями. То есть если вы просто напишете 45 без значка процента, то это число превратится в 4500%, и полоса закрасится целиком.

Добавьте три правила. Путь тот же, что и в третьем шаге: выделите через поле адреса J7:BI56, затем «Главная» → «Условное форматирование» → «Создать правило» → «Классическая» → «Использовать формулу для определения форматируемых ячеек».

Просрочка, цвет E06666:

Последнее условие оставляет красной только ту неделю, где еще есть незакрытая работа.

Выполнено, цвет 2E75B6:

Сегодня, цвет FFE699:

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

Шаг 5. Выстраиваем правила по порядку

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

Откройте «Условное форматирование» → «Управление правилами» и выберите в списке сверху «Этот лист», иначе увидите правила только для выделенной ячейки.

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

1. просрочка, красная;
2. выполненная часть, темно-синяя;
3. план, голубая — вы создали ее первой;
4. текущая неделя, желтая.

Справа есть колонка «Остановить, если истина». Поставьте галочку у первых трех: она означает «правило сработало, дальше не проверять». Без нее следующее правило закрасит ячейку поверх.

Желтому галочка не нужна, оно и так последнее. Из-за этого желтым останутся только те ячейки текущей недели, где полосы нет.

Что должно получиться: задача со 100% — темно-синяя целиком, задача наполовину — темная в начале и голубая в хвосте, просроченная — красная. Желтая колонка видна только выше и ниже полос.

Шаг 6. Подключаем колонки, которые считаются сами

В таблице остались три незаполненные колонки — «№», «Дней» и «Статус». Вписывать в них ничего не придется: номер проставится по порядку, длительность посчитается из дат начала и окончания, а статус Excel определит сам по срокам и готовности. Сделать это помогут формулы.

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

Номер задачи, ячейка A7:

Длительность, ячейка G7.

В пустых строках формула вернет ноль — он понадобится в следующем шаге, когда будем считать общую готовность проекта. Чтобы нули не мешали читать таблицу, выделите G7:G56, откройте «Формат ячеек» → «Все форматы» и впишите в поле «Тип»:

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

Статус, ячейка I7:

Что должно получиться: в колонке A появились номера, в колонке G — длительность в днях, в колонке I — статусы, прописанные словами.

Покрасьте статусы, так таблицу можно быстрее считывать. Выделите I7:I56 и добавьте четыре правила знакомым путем: «Условное форматирование» → «Создать правило» → «Классическая» → «Использовать формулу для определения форматируемых ячеек».

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

Шаг 7. Собираем панель показателей

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

Панель собирается из двух строк: в строку 2 впишите подписи с названиями показателей, а в строку 3 под ними — формулы из таблицы ниже. Колонка A занята датой начала шкалы, поэтому начинаем с колонки B. Заполнять нужно данными из этой таблицы:

Осталось заполнить шапку:

• В A1 впишите название проекта.
• В G1 напишите слово «Сегодня:».
• В H1 — формулу =СЕГОДНЯ().
• Задайте форматы для некоторых ячеек, иначе результат будет нечитаемым: ячейке G3 поставьте процентный, ячейкам H3 и I3 — дату. Без этого готовность покажет 0,16 вместо 16%, а даты превратятся в пятизначные числа.

В конце шага у вас получится такой результат:

Шаг 8. Настраиваем файл под ежедневную работу

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

Закрепите области. Нажмите на ячейку J7, затем «Вид» → «Закрепить области». Теперь при прокрутке вправо названия задач остаются на месте, а панель показателей не уезжает вверх.

Включите фильтр, чтобы при необходимости скрывать ненужные задачи. Для этого выделите A6:I56 и нажмите «Данные» → «Фильтр». В строке заголовков появятся стрелки. Можете нажимать на них и выбирать параметры, по которым хотите отфильтровать диаграмму.

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

1. Создайте лист «Настройки» и перейдите в него.

2. Впишите в ячейку B4 заголовок «Ответственные», а ниже, начиная с B5, — фамилии сотрудников по одной в строку. 
3. В D4 впишите заголовок «Этапы», в D5 и ниже — названия этапов.

4. Вернитесь в лист «Гант», выделите D7:D56 и откройте «Данные» → «Проверка данных». 
5. В поле «Тип данных» выберите «Список».

6. Нажмите на поле «Источник».
7. Перейдите на лист «Настройки» и выделите фамилии вместе с пустыми строками под ними, до B24. Пустые ячейки в меню не попадут, зато нового сотрудника достаточно будет вписать в справочник — в списке он появится сам.

8. Снимите галочку «Выводить сообщение об ошибке» — тогда в ячейку можно будет вписать и то, чего в справочнике нет.

9. По такому же принципу настройте список этапов в ячейках C7:C56, только источником укажите ячейки с этапами.

В конце этапа вы сможете выбирать значения из списка без ручного ввода:

Шаг 9. Настраиваем внешний вид диаграммы

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

Подпишите месяцы. Над шкалой пустует строка 5. По датам вида 20.07 и 03.08 тяжело понять, где кончается июль, поэтому поставим туда названия месяцев.

Впишите в J5:

Впишите в K5 и протяните вправо до BI5:

Вторая формула подписывает месяц один раз — в той колонке, где он сменился. Поставьте строке 5 кегль 8 и выравнивание по центру.

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

Шаг 10. Добавляем листы по дням и по месяцам

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

Решение — одна таблица задач и три листа с разным масштабом. Задачи остаются на «Ганте», два соседних листа показывают их по дням и по месяцам.

Делаем диаграмму Ганта с делением на дни

Скопируйте лист с диаграммой. В этой копии нужно поменять три вещи:

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

Для этого впишите в седьмую строку копии следующие формулы и протяните каждую до 56-й строки:

Осталась панель показателей — она тоже должна брать числа с «Ганта». Впишите в B3 формулу ='Гант'!B3 и протяните вправо до I3.

Задайте форматы ячеек. Это нужно, чтобы в таблице не было даты 00.01.1900. Выделите диапазон, откройте «Формат ячеек» → «Все форматы» и впишите код в поле «Тип». Проделайте это трижды (в конце каждого кода точку с запятой нужно вводить дважды — то есть нужно писать «;; », а не «;»):

• E7:F56 — формат ДД.ММ.ГГГГ;;
• G7:G56 — формат 0;-0;;
• H7:H56 — формат 0%;;

Переведите шкалу в дни. Для этого впишите в K6 формулу =J6+1 и протяните вправо до колонки CW — получится 92 колонки, то есть примерно квартал. Затем выделите колонки J:CW, поставьте ширину 2,3 и формат ДД: в шапке останутся только числа месяца.

Замените правила. Сейчас полосы рисуются по недельной логике, хотя колонки стали днем. Чтобы исправить это, откройте «Управление правилами» и выберите «Этот лист».

В списке четыре правила с полосами. Названий у них нет — опознавайте по цветному образцу. Выделите правило, нажмите «Изменить правило» и замените формулу целиком на формулу из таблицы:

После этого в окне справа от формулы впишите у всех четырех в колонку «Применимо к» вот этот диапазон: $J$7:$CW$56

Добавьте пятое правило с серой заливкой, оно выделит выходные дни:

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

Что должно получиться: полосы стали точными по дням, суббота с воскресеньем выделены серым, сегодняшний день — желтым.

Делаем диаграмму Ганта с делением на месяцы

Здесь все проще — скопируйте лист «По дням» и назовите копию «По месяцам». Таблица в ней уже подтягивает данные с «Ганта», ее трогать не нужно.

Замените формулы в правилах. Откройте «Управление правилами», выберите «Этот лист» и по очереди откройте каждое правило кнопкой «Изменить правило»:

Поправьте диапазоны. Правила скопировались с дневного листа и растянуты до колонки CW. Впишите у всех четырех в «Применимо к»: $J$7:$AG$56

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

Удалите лишние колонки целиком: выделите AH:CW по буквам сверху, нажмите правой кнопкой и выберите «Удалить». Лист закончится на AG.

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

Важно: новые задачи нужно вводить только на листе «Гант». Если поправить строку на листе для отображения по дням и месяцам, то формула сотрется, а строка перестанет обновляться. Вернуть ее можно копированием из соседней строки.

На этом все, диаграмма готова к работе! А вот файл с готовой диаграммой. Можете обращаться к инструкции, если хотите разобраться в настройках или что-то изменить под себя:

У такой диаграммы Ганта все равно есть недостатки

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

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

Длительность считается в календарных днях. Задача с 10 по 20 августа получит 11 дней, хотя рабочих в ней восемь. По такому плану удобно смотреть сроки, но не трудозатраты.

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

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

История правок не видна. Облако вернет вчерашнюю версию целиком, но не покажет, кто именно сдвинул срок и почему. На разборе месяц спустя доказать будет нечем.

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

Как работать с диаграммой Ганта без долгих настроек и делать это на продвинутом уровне

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

В итоге на создание и обновление диаграммы уходит в разы меньше времени по сравнению с работой в Excel.

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

Если срок одной связанной задачи увеличится, Kaiten автоматически передвинет следующие задачи с учетом рабочих дней и часов.

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

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

Для более сложного планирования можно подключить модуль «Диаграмма Ганта и Ресурсное планирование». Он позволяет:

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

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

Успешные компании уже используют Кайтен Попробуйте расширенный функционал на своем проекте бесплатно

Попробовать

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

Попробуйте построить диаграмму Ганта в Kaiten

Перенесите несколько текущих задач, расставьте сроки и сравните, сколько времени занимает обновление плана в Kaiten и в Excel. Начать можно бесплатно — без переноса всей работы команды и долгой настройки пространства.