Дашборд в Excel: как собрать отчёт по продажам и рекламе без BI-системы

Что считать дашбордом

Красивые диаграммы легко принять за готовую аналитику, хотя за ними могут стоять несопоставимые выгрузки. Дашборд в Excel — компактный экран ключевых показателей с фильтрами и понятными правилами расчёта, который помогает заметить отклонение и перейти к данным для объяснения.

Дальше разберём еженедельный отчёт в настольном Excel для Microsoft 365 на Windows: расходы небольшой компании услуг поступают из рекламных кабинетов, обращения и продажи — из CRM, а за книгу отвечает один сотрудник. Здесь важна повторяемость. Руководителю требуется видеть, на какой вопрос отвечает каждый показатель и какой период уже закрыт, иначе обновлённый график станет поводом спорить об исходных числах вместо решения о рекламе. Ежедневное наблюдение каждой сделки в этот отчёт не входит. Для него удобнее рабочий интерфейс CRM, в котором сотрудники ведут отдельные обращения и назначают следующие действия.

Начните с решения, которое принимают по экрану: например, какие направления требуют разбора стоимости привлечения. Такой выбор лучше универсального набора всех доступных графиков, потому что читателю не придётся искать главный вопрос среди декоративных сравнений. Формулы и источники описывают до оформления, чтобы цвет не маскировал неодинаковый смысл выручки и расходов.

Данные рекламы и продаж

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

Для первого отчёта удобнее хранить исходные файлы отдельно от расчётной части. Они пригодятся для разбора расхождений. Сотрудник загружает подготовленные выгрузки, а преобразования очищают названия каналов и приводят даты к одному виду. В Excel для Microsoft 365 на Windows инструмент Power Query доступен на вкладке «Данные», что подтверждает документация Microsoft о модели данных. Конкретный состав подключений всё равно стоит испытать на установленной версии программы и реальных файлах, прежде чем обещать полностью автоматическое обновление.

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

Единица строки и связи

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

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

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

Расчёт показателей

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

Для расчётного примера возьмём рекламные расходы в 60 000 рублей. Пусть выгрузка содержит 120 уникальных обращений. Тогда стоимость обращения равна 500 рублям. Эти числа иллюстрируют арифметику, а не результат компании, поэтому рядом с формулой нужен словарь: какое обращение считается уникальным, в какой период оно попало и как учитывается повторный контакт. Если знаменатель меняется от отчёта к отчёту, сравнение теряет смысл даже при безошибочном делении.

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

• Стоимость обращения = рекламный расход / число уникальных обращений.
• Конверсия обращения в оплату = обращения с оплатой / обращения одной выбранной группы.
• Средняя сумма оплаты = сумма оплат / число оплат с одинаковыми правилами возврата.
• При нулевом знаменателе — отдельная отметка об отсутствии базы расчёта, без подмены нулём результата.

Сводные и фильтры

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

Срез делает выбор заметным. По инструкции Microsoft его добавляют через «Вставка» → «Срез». Он фильтрует связанную таблицу или сводную таблицу кнопками и показывает выбранные значения. Для общей фильтрации нескольких сводных требуется один и тот же источник данных. Поэтому сначала следует решить, какие блоки относятся к одному набору данных, а потом обещать единый переключатель для всего экрана.

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

Как пережить обновление

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

В настольном Excel обновление данных книги вызывается сочетанием Ctrl+Alt+F5, указанным в справке Microsoft об обновлении подключений. Это команда запуска. До чтения итогов сотрудник ждёт завершения и разбирает ошибки источников. Обновление предварительного просмотра в редакторе Power Query оставляет без обновления таблицу на листе и модель данных. Поэтому видимые новые строки в редакторе ещё не означают, что руководитель уже видит новые суммы на экране отчёта.

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

Приёмка отчёта

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

Для испытания нужна копия выгрузки. В ней меняют одну сумму, добавляют обращение с неизвестным каналом и убирают обязательный столбец. Каждый такой опыт отвечает на отдельный вопрос. Руководитель увидит, меняется ли итог ожидаемым образом, сохраняется ли неизвестная группа и становится ли ошибка заметной, вместо молчаливой публикации неполного отчёта. После испытания возвращают исходные данные и повторяют обновление.

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

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

Когда книги достаточно

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

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

Рабочий результат такой книги — небольшой набор объяснимых показателей, сохранённые исходники и понятный выпуск отчёта. Если задача уже требует связать рекламные данные с продажами, обсудить её можно со ШТАБ ИТ, передав словарь показателей и пример обезличенных выгрузок. По ним проще оценить объём аналитики и понять, хватит ли книги для следующего этапа работы.