Дашборд в Excel: от сводных таблиц до готового экрана
Собираем дашборд в Excel на одном файле: лист данных, сводные, KPI, диаграммы, общие срезы, проверка итогов и безопасное обновление.
Содержание статьи
Менеджер смотрит на сумму броней за четыре дня и спрашивает, какой тип брони внёс больший вклад. Одна сводная ответит аналитику, но менеджеру нужен экран, где видны итог, движение и разрез, а фильтры не меняют смысл незаметно. Соберём такой дашборд в Excel на том же файле, что и в статье о сводных таблицах. Все числа сверены с учебной базой; названия меню сверены с документацией Microsoft 27 сентября 2026 года.
Коротко: как собрать дашборд в Excel
Разделите книгу на три роли: «Данные» хранят одну строку на бронь, «Сводные» считают проверенные агрегаты, «Дашборд» показывает только итог, динамику, разрез и дату обновления. Сначала создайте две или три сводные из одной таблицы Bookings. Затем сделайте KPI-ячейки, диаграмму по дням и сравнение типов брони. Добавьте один срез и подключите его к нужным сводным через Report Connections.
Дальше проверьте два состояния: все данные и фильтр 2+. На всех данных должно быть 23 496 броней на 1 884 807 100 ₽; с фильтром 2+ — 7 994 брони на 998 204 100 ₽. Если число в карточке не меняется вместе с графиком, не публикуйте страницу: элементы показывают разные выборки. Последний шаг — обновление источника, контроль итогов и проверка того, что пользователь не может случайно затереть расчёт.
Это не универсальный шаблон «для любого бизнеса». Наш экран отвечает на один вопрос о сумме новых бронирований, а не о прибыли, оплатах или причинах роста. Прежде чем копировать расположение блоков, сформулируйте решение, ради которого страницу будут открывать, и данные, которыми его можно обосновать.
Файл и границы учебного примера
Скачайте учебную книгу XLSX или CSV с теми же 23 496 строками. Главный лист книги называется «Брони», таблица — Bookings. Поля: ключ, московская дата оформления, сумма в рублях, число билетов и группа 1/2+. В книге есть маленькие справочные листы для Power Query; они не заменяют исходные строки.
Даты 3–6 августа 2026 года — четыре полных дня из учебной базы «Авиаперевозки». Период выбран целиком, а не случайно сэмплирован. Личные данные пассажиров в выгрузку не попали. Сумма total_amount — стоимость оформленной брони; таблиц оплат и возвратов здесь нет. Поэтому заголовок карточки должен говорить «сумма оформленных броней», а не «выручка».
Рабочая книга станет копией файла практики. Переименуйте «Брони» в «Данные» лишь если хотите; имя таблицы Bookings сохраните, иначе формулы со структурными ссылками потребуется изменить. Создайте рядом пустые листы «Сводные» и «Дашборд». Сырьё оставьте нетронутым, чтобы любую итоговую цифру можно было проследить до исходного book_ref.
| Лист | Что на нём | Что не класть |
|---|---|---|
| Данные | Исходная таблица Bookings, одна бронь на строку | Карточки KPI и ручные итоги между строк |
| Сводные | Агрегаты по дню и типу, контрольный итог | Декоративные элементы для читателя |
| Дашборд | KPI, два графика, срез, дата обновления | Скрытые ручные исправления исходных сумм |
Сначала нарисуйте экран на сетке
До диаграмм выделите на листе «Дашборд» верхнюю строку для вопроса и периода, следующий ряд — для трёх KPI, середину — для динамики по дням и сравнения групп, низ — для пояснения ограничений и контрольного итога. Это расположение соответствует порядку чтения: что произошло, когда произошло, в каком разрезе искать следующий вопрос. Если менеджеру нужны конкретные брони, добавьте ссылку на таблицу деталей, а не десятки мелких карточек.
Проверьте макет на обычной ширине окна ноутбука. Два графика рядом имеют смысл, только если у обоих читаются подписи и единицы измерения. На узком экране лучше расположить их друг под другом. Не оставляйте дефолтный заголовок «Sum of total_amount» — он ничего не сообщает о периоде и статусе брони. Хороший заголовок: «Сумма оформленных броней, 3–6 августа, ₽».
Не используйте цвет как единственный носитель смысла. Два типа брони должны различаться подписями и по возможности порядком, а не только оттенком. Нулевую линию оставьте для столбцов суммы; маленькую разницу внутри дня не преувеличивайте обрезанной шкалой.
| Зона | Левая часть | Правая часть |
|---|---|---|
| Верх | Вопрос и период | Обновлено / источник |
| KPI | Сумма, число броней | Средняя бронь |
| Середина | Сумма по дням | 1 билет / 2+ билета |
| Низ | Контрольный итог | Ограничения показателя |
Соберите расчётный лист из общего источника
На листе «Сводные» создайте отчёт A: book_date в Rows, total_amount в Values как Sum, book_ref в Values как Count. Отчёт B: ticket_bucket в Rows, те же два значения. Для перекрёстной проверки сделайте C: дата в Rows, тип в Columns, сумма в Values. Все три создавайте из Bookings, а не копируйте агрегированные таблицы как новый источник. Тогда общий срез сможет фильтровать их согласованно.
Перед оформлением убедитесь, что общий итог A, B и C равен 1 884 807 100 ₽. Количество броней в каждом отчёте должно быть 23 496. Если C даёт другую сумму, проверьте, не отфильтрован ли один тип брони. Если B даёт больше, вероятнее ошибка в подготовке данных: повторение book_ref после соединения с билетами. В скачанном файле ключ уникален.
Не скрывайте лист «Сводные» до проверки: он служит наблюдаемым мостом между строкой источника и графиком. Договоритесь, что любые будущие метрики сначала появляются и сверяются здесь. Тогда новая «красная карточка» не получит собственную формулу с забытым фильтром.
KPI-ячейки: что именно должна считать формула
Для постоянного контрольного итога исходной таблицы используйте =SUM(Bookings[total_amount]), для числа броней — =COUNTA(Bookings[book_ref]). В русской версии Excel это =СУММ(Bookings[total_amount]) и =СЧЁТЗ(Bookings[book_ref]). Формулы дают 1 884 807 100 и 23 496. Средняя бронь — отношение двух величин, около 80 218,21 ₽. Если у вас получились другие числа, сравните с этими: они посчитаны отдельным SQL-запросом по тем же строкам файла.
Но контрольный итог по Bookings не реагирует на срез сводной. Если KPI на дашборде должен меняться при фильтре, привяжите его к итоговому значению соответствующей сводной. Надёжнее не писать адрес вроде D8 вручную: при новых днях итог сдвинется. Введите = и выберите нужную ячейку сводной: Excel может построить GETPIVOTDATA с именем поля и опорной ячейкой. Проверьте конкретную созданную формулу в вашей локали.
Разделите видимый KPI и независимый контроль. Видимый KPI следует фильтру, контрольная сумма по исходной таблице остаётся неизменной и помогает понять, что фильтр активен. Подпишите обе роли, иначе пользователь решит, что одна из цифр «не обновилась». Формулу сумма / число броней защищайте от нулевого знаменателя в пустой выборке.
=SUM(Bookings[total_amount])Даёт 1 884 807 100 ₽; не меняется при срезе сводной. В русской версии Excel — =СУММ(Bookings[total_amount]); имя таблицы и столбца не переводится.
График времени: не стройте его по 23 тысячам строк
Выберите сводную A и вставьте PivotChart. Для четырёх дней используйте столбцы или линию с датой по горизонтали и суммой по вертикали. Итоги по дням: 452 158 300 ₽, 467 666 600 ₽, 479 377 800 ₽ и 485 604 400 ₽. Подпишите ось в миллионах рублей, а точные значения оставьте в таблице или подсказке. Длинная метка с девятью цифрами над каждым столбцом сделает график хуже, чем таблица.
Четыре наблюдения последовательно растут, но график не доказывает, почему. Для вопроса об эффекте акции понадобится сравнение сопоставимых дней, информация о цене и маршрутах. Наш экран только указывает, где искать. Поэтому заголовок «За четыре дня сумма оформленных броней выросла» честнее, чем «Акция сработала».
Если на источник добавится незакрытый день, фильтруйте его до момента закрытия или явно помечайте как неполный. Иначе последний столбец упадёт просто потому, что до вечера ещё есть время. Обновление диаграммы и диапазона дат проверяйте вместе с контрольной сводной.
Учебный срез 03–06.08.2026 МСК. Столбцы начинаются с нуля.
Разрез: типы брони рядом с дневным итогом
Вторая диаграмма нужна не для украшения, а для ответа «какая группа формирует сумму». Из сводной B видно: 15 502 брони с одним билетом дали 886 603 000 ₽, 7 994 брони с несколькими — 998 204 100 ₽. Группа 2+ занимает около 34,02% броней, но около 52,96% суммы. Эти две доли отвечают на разные вопросы, поэтому не подписывайте обе просто «доля группы».
Для сравнения сумм двух категорий подойдут два горизонтальных столбца от нуля. Для состава каждого дня можно использовать таблицу из сводной C или сгруппированные столбцы. Круговая диаграмма здесь не помогает увидеть, что именно происходит по дням. Если нужны точные четыре пары чисел, таблица под графиком читается лучше ещё одной миниатюры.
При фильтре 2+ первая группа исчезнет. Проверьте, не остаётся ли старая подпись «один билет против нескольких» над графиком, в котором теперь один сегмент. Меняющийся экран должен показывать активное состояние фильтра и знаменатель долей.
| Тип брони | Броней | Сумма, ₽ | Доля суммы |
|---|---|---|---|
| 1 билет | 15 502 | 886 603 000 | 47,04% |
| 2+ билета | 7 994 | 998 204 100 | 52,96% |
| Итого | 23 496 | 1 884 807 100 | 100% |
Один срез должен управлять всеми зависимыми сводными
Выберите сводную, добавьте Slicer по ticket_bucket и разместите его над диаграммами. Щёлкните по срезу, откройте Slicer → Report Connections и отметьте сводные A, B и C. Microsoft предупреждает: общее подключение работает для сводных на одном источнике. Если вы случайно построили один отчёт из копии CSV, он может не появиться в списке подключений.
Тестируйте не только «срез есть», но и числа. На выбранном 2+ сумма должна стать 998 204 100 ₽, количество — 7 994. На 1 — 886 603 000 ₽ и 15 502. После очистки — вернуться к 1 884 807 100 ₽ и 23 496. Проверьте одновременно карточки, обе диаграммы и таблицу деталей. Если один объект остаётся старым, сначала найдите его источник, затем правьте оформление.
Не привязывайте контрольную формулу SUM(Bookings[total_amount]) к этому тесту: она намеренно показывает весь файл. На экране рядом с ним должно быть слово «контроль», иначе это выглядит как ошибка. Временную шкалу по book_date добавляйте только тогда, когда период действительно будут выбирать; для четырёх дней одного среза достаточно.
Условное форматирование должно вести к действию
Выделять красным любую сумму ниже вчерашней — плохое правило: воскресенье может отличаться от понедельника без аварии. Сначала определите, что считается отклонением, какой период служит базой и кто будет реагировать. В учебном файле плана нет, поэтому мы не рисуем «выполнение плана 92%» и не задаём произвольный красный порог. Можно подсветить пустой или ошибочный источник и подписать, что это проблема качества данных.
Для рабочего отчёта пример осмысленного правила: если после полного обновления дата последней записи старше ожидаемого дня, пометить состояние «данные устарели». Это контроль процесса, а не оценка бизнеса. Microsoft допускает условное форматирование диапазонов и таблиц; правила на сводной в Excel для Windows имеют отдельные особенности, поэтому проверьте их после Refresh.
Оставьте на странице обычный текстовый вывод рядом с цветом. При печати в чёрно-белом режиме или для человека, который не различает часть оттенков, красная заливка без подписи исчезнет как сигнал. График и карточка должны оставаться понятными без декоративного цвета.
Защита листа и права на редактирование
После проверки формул защитите лист «Дашборд» от случайного перетаскивания и удаления элементов. Команда Review → Protect Sheet позволяет выбрать разрешённые действия. Протестируйте, что читатель всё ещё может пользоваться нужными фильтрами; если нет, пересмотрите настройки защиты. Microsoft отдельно указывает, что защита листа не равна защите всего файла или настоящему разграничению доступа.
Исходный лист лучше держать доступным для ответственного аналитика, а для читателей раздавать копию с нужными правами. Не храните в этой книге секреты и персональные данные лишь потому, что лист скрыт или защищён. Наш файл не содержит контактов пассажиров, поэтому его можно использовать как учебный пример без такой маскировки.
Назовите владельца обновления и проверки. Если несколько человек правят копии книги, через месяц их определения «суммы продаж» разойдутся. Один сохранённый источник, понятный период и короткий чек-лист обновления дадут больше пользы, чем сложный пароль на каждой ячейке.
Обновление и контроль перед отправкой
При добавлении новых строк в Bookings нажмите Refresh All и дождитесь завершения всех источников. Затем проверьте максимальную дату, число уникальных ключей и общий итог. Для статического учебного файла эти величины равны 06.08.2026, 23 496 и 1 884 807 100 ₽. Если файл за ночь изменился без вашего участия, ищите новое поступление данных или неверный источник — не переписывайте контрольное число вручную.
Проверьте три пути исполнения: откройте книгу с чистым фильтром, выберите 2+, очистите фильтр. На каждом шаге просмотрите KPI, динамику и разрез. Отдельно проверьте пустую выборку: формула средней не должна делить на ноль. Дата в заголовке должна означать момент последнего успешного обновления данных, а не время, когда кто-то открыл книгу.
Если Power Query загружает CSV, Refresh All запускает цепочку подготовки и затем обновляет зависимые сводные. Наличие кнопки не гарантирует, что файл на диске новый, его формат тот же и все строки импортированы. Держите рядом проверку количества и пустых значений. Для регулярной загрузки есть отдельная инструкция по Power Query.
Когда Excel становится узким местом
Наш файл меньше мегабайта и им управляет один аналитик — это естественный сценарий Excel. Переход в BI нужен не потому, что «сводная выглядит скучно», а когда одновременно нужны централизованное обновление, много читателей с разными правами, несколько источников и общая модель метрик. Ещё сигнал — ручные копии книги с разными числами, которые уже невозможно согласовать простым контрольным итогом.
Перед переносом зафиксируйте определения: одна строка — бронь, сумма — стоимость оформления, период — полные дни по Москве, группа 2+ — число билетов в брони. Сохраните SQL-контроль на 23 496 и 1 884 807 100 ₽. В новом инструменте сначала добейтесь той же суммы на том же срезе, затем добавляйте новые фильтры и роли.
Если руководителю нужен лишь еженедельный файл от одного аналитика, не усложняйте архитектуру преждевременно. Если он хочет получать одинаковые метрики в нескольких командах ежедневно, Excel уже может быть прототипом требований, а не конечной точкой. Критерии выбора инструмента изложены отдельно.
Пять ошибок готового Excel-дашборда
Первая — карточка считает весь Bookings, а график фильтруется срезом: на странице оказываются разные совокупности. Вторая — сводные построены на разных копиях источника; Report Connections не может синхронизировать их. Третья — новый день внесён в исходник, но Refresh All не выполнен: дата в заголовке обещает свежие данные, а график старый.
Четвёртая — менеджеру показывают «выручку», хотя источник хранит только стоимость оформленных броней; вывод об оплатах становится необоснованным. Пятая — график по четырём дням объявляют доказательством эффекта акции. Шестая — защиту листа принимают за защиту конфиденциального файла. Во всех случаях исправление начинается с определения и пути данных, а не с цвета карточки.
Проверяйте не только общий итог. Контроль по двум группам должен сложиться: 886 603 000 + 998 204 100 = 1 884 807 100 ₽. Контроль по дням тоже должен дать эту сумму. Если обе разметки сходятся и ключи уникальны, вероятность случайного пропуска заметно ниже. Это не отменяет смысловой проверки показателя, но создаёт опору для чтения экрана.
Частые вопросы и следующий шаг
«Можно ли сделать дашборд без сводных?» Можно, если одна стабильная таблица и пара формул отвечают на вопрос. Но если нужны интерактивные разрезы, сводные сохраняют логику агрегации видимой. «Почему KPI не реагирует на срез?» Формула по Bookings считает весь источник; свяжите карточку с итогом подключённой сводной. «Почему срез работает только на одном графике?» Проверьте Report Connections и общий источник сводных.
«Когда нажимать Refresh?» После изменения данных и перед отправкой новой версии отчёта; затем проверяйте дату и контрольные итоги. «Можно ли использовать защиту листа вместо прав доступа?» Нет: это защита от случайного редактирования, не управление доступом к данным. Если файл содержит чувствительную информацию, права нужно задавать в месте хранения и в источнике.
Следующий шаг — подготовить ежедневную загрузку в Power Query, чтобы не вставлять CSV вручную. Если источник живёт в базе, потренируйтесь собирать слой агрегатов в SQL-тренажёре. Дашборд становится надёжнее тогда, когда один и тот же расчёт можно воспроизвести до и после смены инструмента.
Материалы по теме
Как сделать дашборд: от вопроса бизнеса до публикации
Пошагово собираем дашборд на проверенных данных: решение и читатель, SQL для KPI, динамика, разрез, проверка чисел, доступы и дата обновления.
Дашборд: что это такое, из чего состоит и какие бывают
Дашборд — это страница с несколькими регулярно обновляемыми показателями для одного решения. Чем он отличается от отчёта и BI-системы, какие бывают дашборды, из чего состоят и как посчитать KPI-карточки на SQL.
Собеседование BI-аналитика: дашборды, Excel, SQL и бизнес-кейсы
Практический гайд по собеседованию BI-аналитика: как проектировать дашборд, выбирать KPI, проверять данные, отвечать про Excel и защищать вывод перед бизнесом.