Трекер задач в Excel: строим систему управления через Power Query и DAX (плюс готовый шаблон с диаграммой Ганта)

Всем привет! На связи канал «Аналитика на стероидах». В своей первой статье я рассказывал про настройку аналитики для селлеров маркетплейсов на базе Google Sheets. Сегодня мы немного сместим фокус с продаж на менеджмент, но останемся в нашей любимой табличной среде.

Знакомая картина: проект растет, задач становится больше, и в какой-то момент обычный список дел перестает работать. Команда начинает смотреть в сторону популярных систем вроде Битрикс24, Яндекс Трекер или Kaiten. Инструменты отличные, но давайте честно: для небольших команд, отделов или личных проектов их внедрение часто избыточно.

В то же время вести дела в обычной «плоской» таблице Excel - прямой путь к хаосу. Опечатки, сломанные формулы и абсолютное непонимание, кто сейчас в графике, а кто отстает.

Но ведь Excel давно перестал быть просто электронным листом в клеточку, правда? Сегодня в нем можно запилить мини-приложение только встроенными инструментами без монструозного VBA и макросов. В итоге у нас получится вот такая красота:

Любая хорошая система начинается с правильного ввода данных. В нашем файле за это отвечают два стартовых листа.

Здесь живет базовая информация: Работники, Заказчики, Виды работ, Статусы и Регионы. Главный нюанс этого листа — каждый список оформлен как Умная таблица (Ctrl + T). Это позволяет системе автоматически подхватывать новых сотрудников в выпадающие списки без перенастройки формул.

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

Это рабочее место менеджера. Журнал, куда вносятся все вводные. Чтобы пример в файле был «живым» и наглядным, для генерации тестовых данных я применил формулы с генератором случайных чисел, чтобы было видно как меняются итоговые дашборды при обновлении данных.

Как заполнять:

Я не люблю статьи, где самое вкусное прячут в самом конце под семью замками. Поэтому забрать файл можно прямо здесь.
Для корректной работы нужно дать файлу разрешения, когда их спросит Excel.
Также возможно потребуется включить надстройку PowerPivot, если она отключена:

Чтобы плоская табличка превратилась в умный инструмент, я выстроил архитектуру по принципу «завода»:

Обычно в Excel календарь Ганта рисуют руками: пишут дату и тянут её вправо до бесконечности. Это неудобно: проект вырос — надо опять тянуть. В нашем файле календарь «живой».

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

В исходной таблице задача занимает одну строку (начало — конец). Но для графика Ганта нам нужно видеть её прогресс в каждом конкретном дне. В Power Query мы делаем «развертывание»: если задача длится 5 дней, она превращается в 5 строк. Теперь мы можем посчитать «вес» каждого дня. Вся эта таблица записывается в модель данных. Столбец с размноженными датами называется "Задача даты".

Теперь самое интересное — как заставить таблицу понимать, что мы отстаем от графика? Для этого мы используем Модель данных и язык формул DAX.

У меня получилось три таблицы: Календарь (Dates), сами Задачи с размноженными по строкам датами и Статусы (Statuses), которые я загрузил напрямую из справочника, где каждому этапу работ соответствует свой процент выполнения.

Дневное_выполнение

Выполнение_накопленное

Отклонение от плана

Когда математика готова, мы выводим её на листы.

Это не просто раскрашенные ячейки. Здесь работает «Условное форматирование», которое опирается на наши DAX-расчеты:

Также в зависимости от даты закрашиваются столбцы выходных дней и подсвечивается сегодняшняя дата.

На этом листе мы видим сухие цифры по каждому сотруднику. Благодаря мерам DAX, мы можем мгновенно понять: тестовая Петрова молодец, у неё 9 дней задержки, зато самый большой процент выполнения задач и почти 5 дней опережения, а тестовая Константинова накопила больше 18 дней просрочки по своим задачам.

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

Чтобы диаграмма Ганта была вечной, мы используем специальный режим привязки правил в правильной области применения. Когда вы создаете правило в сводной таблице, выберите вариант: «ко всем ячейкам, содержащим значения "" для "Выполнение_%" и "Дата"». Это привяжет дизайн не к адресу ячеек (например, $D$11:$Z$100), а к структуре данных. Теперь, сколько бы задач ни добавилось, форматирование растянется само.

В большинстве Excel-шаблонов Ганта ячейка просто красится, если дата попадает в интервал. В нашем случае ячейка информативна: она не только показывает, что задача «идет», но и сразу сообщает её статус относительно дедлайна и текущего дня.

При правильном подходе (Power Query + Модель данных) это мощная среда для создания собственных инструментов, которые сэкономят десятки человекочасов переписывания графиков задач и десятки тысяч рублей на подписках в сторонних сервисах.

Конечно, этот шаблон — лишь база. Его можно докручивать бесконечно: добавлять учет стоимости часа сотрудника, интеграцию с вашей CRM для автоматической постановки задач или с Telegram-ботами для уведомлений.

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

И не забывайте подписываться на канал «Аналитика на стероидах» — там мы разбираем, как выжимать из данных максимум, не тратя на это жизнь.