Диаграмма сгорания задач (Burndown Chart)

Диаграмма сгорания задач или Burndown Chart - это простая и удобная визуализация прогресса по любому проекту, ограниченному во времени. 

По горизонтальной оси диаграммы сгорания откладывается время (дата или номер периода), а по вертикальной - количество работы (часов, задач), которую осталось сделать на эту дату. Причём обычно строится две линии - идеальная (если работать равномерно по плану по N часов в день) и реальная. Отклонение одной линии от другой и их наклон наглядно показывают опережение и отставания от графика, не застряли ли мы на "плато" и т.п. Также по этому графику можно рассчитать среднюю скорость выполнения задач и прогнозную дату завершения всего проекта (на графике выше отображена пунктиром). 

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

Давайте рассмотрим как построить диаграмму сгорания в Excel на следующем примере. Предположим, что мы с вами должны выполнить с 1 по 30 декабря 2025 г. некий проект общей длительностью 150 часов. С ходу очевидно, что мы должны работать над проектом по 5 часов в день, чтобы к концу месяца с ним закончить. Этот момент в нашей модели расчёта реализуется формулой в столбце Осталось план, где мы делим общий объем работы на количество периодов и умножаем на номер текущего периода: 

В реальности, конечно, ровно по 5 часов в день работать у нас не получится - в какие-то дни мы посвятим ему больше времени, в какие-то - меньше, в некоторые - не будем работать над ним совсем. Это в нашей модели символизирует столбец Сделано. С учётом реальности, объем оставшейся работы на конец каждого дня считается в столбце Осталось факт, как общий объем за вычетом всего уже сделанного:

Два технических момента вдогон:

• чтобы линии плана и факта стартовали из одной точки, добавляется дополнительный "нулевой период", где объем равен исходному и по плану, и по факту;
• чтобы не отображалась та часть фактического графика, которая относится к будущему, в формулу добавляется проверка с помощью ЕСЛИ (IF) и для будущих точек выводится ошибка #НД! с помощью функции НД (NA) - Excel ячейки с ошибками на графике не отображает.

Чтобы рассчитать прогнозируемую дату завершения проекта с учётом реального среднего темпа работ, нам необходимо выполнить сглаживание всех точек по ряду Осталось факт в прямую, т.е. линейную аппроксимацию. Такое в Microsoft Excel можно сделать с помощью функции ПРЕДСКАЗ (FORECAST). Первый аргумент этой функции - это номер периода, для которого идёт расчёт. Второй - диапазон прогнозируемых данных, т.е. все имеющиеся значения по столбцу Осталось факт. Третий - номера периодов для этих значений:

Чтобы в формуле автоматически учитывались и новые значения объема выполненных работ в столбце Сделано, лучше заменить второй и третий аргументы на функции ДВССЫЛ (INDIRECT) либо СМЕЩ (OFFSET). Обе эти функции позволяют ссылаться на диапазон, формируемый динамически - от начала таблицы до последнего заполненного периода:

После этого останется лишь выделить столбцы Дата, Осталось план, Осталось факт и Прогноз и построить обычную диаграмму-линию на вкладе Вставка - Диаграммы - График (Insert - Charts - Line), а затем настроить её внешний вид.

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

• Диаграмма выполнения плана в Excel
• Проектная диаграмма Ганта в Power Query
• Календарь этапов проекта в Excel