Продажи выросли, рекламный бюджет тоже, а денег на счёте стало меньше. Нужно ли сокращать расходы на рекламу? Хватит ли средств на ближайшие платежи? Чтобы ответить, важно разобраться, как связаны продажи, расходы и поступления средств, за какой период они посчитаны и что входит в каждый показатель
Привет, Хабр! Мы — Karpov Courses, онлайн-школа, где учат аналитике, программированию и работе с данными. В Excel можно пройти весь путь от исходной выгрузки до отчёта для бизнеса: подготовить данные, рассчитать показатели и представить результаты. Поэтому работа с таблицами — один из базовых навыков, которые мы помогаем освоить начинающим аналитикам.
В статье разберём, как собирать отчёты и дашборды в Excel: от подготовки данных до автоматизации и проверки. Приведём отдельные примеры для продаж, маркетинга и финансов. Основные действия описаны для настольного Excel под Windows. Доступность функций зависит от редакции и лицензии, ведь возможности версий для Mac и браузера отличаются. Числа в примерах учебные.
Какие задачи решают отчёты и дашборды в Excel
Полезный отчёт отвечает на конкретный вопрос. Например, какие товары обеспечили рост или почему расходы превысили план. От вопроса зависят данные, расчёты и представление результата.
Продажи, маркетинг и финансы: какие решения поддерживает отчётность
Управленческая отчётность в Excel помогает сопоставлять результат с планом и выбирать направления проверки. Руководитель продаж анализирует ассортимент и подразделения, маркетолог — расходы и результаты каналов, финансовый специалист — отклонения бюджета и движение денег.
Чем подробный отчёт отличается от дашборда
Первый позволяет проверить операции: найти заказ или открыть расходы за день. Дашборд показывает состояние основных показателей и помогает выбрать направление анализа.
Когда достаточно Excel, а когда стоит рассмотреть BI
Прежде чем выбирать, уточним, что пользователь будет делать с результатом. Смотреть продажи по регионам и переключать периоды? Для этого подойдёт дашборд в Superset. Вносить план, менять цены и проверять, как пересчитывается бюджет? Здесь удобнее рабочая книга с формулами.
Например, финансовый специалист готовит бюджет подразделения. Фактические расходы можно получить из общей отчётности, но планы на найм, закупки и командировки ещё предстоит собрать у руководителей. В Excel эти значения можно разместить по статьям и месяцам, связать с расчётами и проверить, укладывается ли подразделение в выделенную сумму.
Когда бюджет согласован, его можно использовать для регулярного сравнения плана и факта в BI. Так инструменты решают разные части одной задачи.
Как сделать отчёт в Excel: начинаем с задачи и данных
Допустим, коллега прислал несколько файлов и попросил собрать из них отчёт. Начнём с уточнений: что он хочет проверить, какие числа ожидает увидеть и как часто будет возвращаться к результату? Ответы помогут выбрать нужные данные и понять, какие преобразования должны повторяться при каждом обновлении.
Определяем получателя отчёта и вопросы, на которые он должен ответить
Выясним, для кого коллега собирает отчёт. Руководителю отдела может понадобиться выполнение плана по командам, а менеджеру — список заказов, которые нужно обработать сегодня. Часть исходных данных будет общей, но расчёты и детализация потребуются разные.
Допустим, отчёт нужен руководителю для еженедельного совещания. Он хочет видеть продажи с начала месяца, сравнивать их с месячным планом и находить отстающие команды. Зафиксируем эти вопросы: они помогут определить состав отчёта и проверить, хватает ли присланных файлов.
Фиксируем показатели, правила расчёта и периодичность обновления
Теперь уточним, что коллега называет продажами. В выгрузке могут быть оформленные, оплаченные, завершённые и отменённые заказы. Если сложить суммы по всем строкам, результат вряд ли будет соответствовать ожиданиям руководителя.
Договоримся, какие статусы учитываем, по какой дате относим продажу к периоду и как обрабатываем возвраты. Проверим, включены ли в суммы скидки и налоги. Затем выясним, по тем же ли правилам составлен план: сравнение имеет смысл, только если показатели сопоставимы.
Сохраним определения и формулы на отдельном листе. Там же укажем, кто передаёт новые файлы и кто обновляет отчёт. Например, если совещание проходит утром в понедельник, нужно договориться, к какому времени будут готовы данные за предыдущую неделю.
Готовим исходную таблицу: одна строка — одно наблюдение
Откроем файлы и выясним, что записано в каждой строке. Допустим, выгрузка продаж содержит отдельные товарные позиции. Тогда заказ из трёх товаров занимает три строки: его номер повторяется, но это ещё не дубли. Если оставить только одну запись с таким номером, часть продаж исчезнет.
Проверим и остальные файлы. План может быть составлен по командам и месяцам, а справочник сотрудников — содержать по одной строке на менеджера. Такую разницу важно сохранить: если приписать месячный план каждой продаже, при суммировании он будет учтён многократно.
В каждой исходной таблице выделим отдельные столбцы для даты, идентификаторов, суммы и других признаков. Заголовки разместим в одной строке, объединённые ячейки и промежуточные итоги уберём. Если указаны разные валюты, оставим для суммы и кода валюты отдельные столбцы; складывать такие суммы без согласованного пересчёта нельзя.
Преобразуем диапазон в «умную» таблицу и проверяем качество данных
Начнём с выгрузки продаж: выделим диапазон, нажмём Ctrl + T и подтвердим, что первая строка содержит заголовки. Зададим таблице понятное имя, например Sales. Аналогично оформим план и справочник сотрудников. Это упростит ссылки на данные и добавление новых строк.
Затем проверим содержимое.
Распознаются ли даты как даты, а суммы — как числа?
Есть ли строки без номера заказа или менеджера?
Не встречаются ли статусы, о которых коллега не упомянул?
Например, сумма может выглядеть как число, но храниться текстом — смена формата ячейки не всегда исправляет это.
Обнаруженные несоответствия разберём до расчётов. Если у продажи не указана команда, выясним, можно ли определить её по справочнику сотрудников. Само преобразование диапазона в «умную» таблицу такие проблемы не устраняет.
Распределяем задачи между формулами, Power Query, сводными и Power Pivot
Когда устройство файлов понятно, можно выбрать инструменты. Если коллега будет каждую неделю присылать однотипные выгрузки, их удобно обрабатывать через Power Query. Продажи по командам покажем в сводной, а для совместного анализа продаж и плана рассмотрим модель данных.
Инструмент | Как используем в отчёте |
Формулы листа | Рассчитаем отклонение от плана и добавим контрольные проверки |
Power Query | Загрузим файлы, приведём типы данных и объединим однотипные выгрузки |
Сводная таблица | Сгруппируем продажи по периодам и командам |
Power Pivot | Свяжем продажи и план через общие справочники и создадим меры для сравнения |
Использовать все инструменты сразу необязательно. Выберем тот набор, который позволит собрать отчёт и обновлять его без повторения одних и тех же ручных действий.
Power Query: загружаем и подготавливаем данные для отчёта
С первыми файлами разобрались, но через неделю коллега пришлёт новые. Если каждый раз копировать строки, исправлять названия столбцов и приводить даты к одному формату вручную, подготовка отчёта превратится в повторяющуюся работу. Настроим эти действия в Power Query, чтобы при обновлении применять к новым данным ту же последовательность преобразований.
Подключаем файлы и объединяем однотипные выгрузки
Для CSV выберите «Данные → Получить данные → Из файла → Из текста/CSV». Для однотипных выгрузок используйте подключение к папке и объединение. В редакторе проверьте список файлов и структуру результата.
Ежемесячные выгрузки можно добавить друг под другом, сохранив имя файла для проверки. Но если каждая содержит всю историю, старые продажи будут учтены повторно. Выясните, перед вами новые операции или полный снимок данных.
Настраиваем типы данных, обрабатываем пропуски и проверяем повторы
Идентификаторы с ведущими нулями оставьте текстом. Для дат и чисел проверьте региональные настройки: 01/02/2026 допускает разные трактовки. Ошибки преобразования исследуйте, прежде чем удалять строки.
Пропуск не всегда означает ноль. Повторный заказ может содержать разные товарные позиции. Определите ключ записи и только затем решайте, какие повторы удалять.
Соединяем таблицы и проверяем, не размножились ли строки
Команда добавления запросов складывает строки, а слияние запросов сопоставляет таблицы по ключу. Например, слияние помогает добавить категорию товара из справочника. Для сохранения всех продаж обычно подходит левое внешнее соединение.
Повторный артикул в справочнике размножит продажу после раскрытия результата. Проверьте уникальность ключа справа, затем число строк и сумму после соединения. Записи без категории выведите отдельно.
Сохраняем последовательность преобразований для повторного обновления
Результат запроса можно загрузить на лист или в модель данных. Ручные исправления выходной таблицы могут исчезнуть при обновлении. Замену ошибочного названия канала сохраните шагом запроса или в таблице соответствий.
Сводные таблицы в Excel: от исходных данных к показателям
Сводная таблица, или pivot table, позволяет группировать записи и получать суммы, количества и другие агрегаты. Вместо отдельных формул для каждой категории вы задаёте поля анализа.
Создаём сводную таблицу: пример отчёта по продажам
Пусть одна строка содержит один завершённый заказ, суммы указаны после скидок, возвратов в примере нет.
Дата | Заказ | Категория | Менеджер | Сумма, ₽ |
01.09.2026 | A01 | Кухня | Анна | 4 000 |
02.09.2026 | A02 | Текстиль | Борис | 6 000 |
03.09.2026 | A03 | Кухня | Анна | 3 000 |
04.09.2026 | A04 | Текстиль | Анна | 2 000 |
Чтобы сделать сводную таблицу:
Преобразуйте данные в таблицу через Ctrl + T.
Выберите любую ячейку внутри неё.
Нажмите «Вставка → Сводная таблица».
Проверьте источник и выберите размещение на новом листе.
Перенесите категорию в строки, а сумму — в значения.
Получится отчёт по продажам в Excel: «Кухня» — 7 000 ₽, «Текстиль» — 8 000 ₽, общий итог — 15 000 ₽. Эти числа удобно использовать для первой проверки.
Настраиваем строки, столбцы, значения и фильтры
Область строк задаёт основные группы, столбцы — дополнительное сравнение, значения — расчёт, фильтры — ограничение выборки. Добавьте менеджера в столбцы: увидите продажи каждой категории по сотрудникам. Для суммы проверьте способ агрегирования: нужна «Сумма», а не «Количество».
Добавляем доли, отклонения и сравнение периодов
Добавьте сумму в область значений второй раз. Для копии выберите «Дополнительные вычисления → Доля от общего итога». В примере доля кухни составит 46,7%, текстиля — 53,3%. Рядом останутся абсолютные значения. Для сравнения месяцев используйте разность или процентное отличие от базового элемента. Даты группируйте по годам и месяцам, чтобы не смешивать разные годы.
Вычисляемые поля и относительные показатели: где возникают ошибки
В обычной сводной вычисляемое поле работает с суммами исходных полей в текущей группе. Например, выражение «Выручка / Расходы» делит их суммы. Но «Цена × Количество» при разных ценах не заменяет построчный расчёт стоимости. Стоимость лучше вычислить в источнике, затем просуммировать.
Ещё ошибка — усреднять готовые проценты. Если у одного канала 10 заказов на 100 визитов, а у другого 20 на 1 000, общая конверсия равна 30 / 1 100 ≈ 2,73%. Среднее двух конверсий, 10% и 2%, даст 6% и исказит результат.
Настраиваем срезы и обновляем сводную
Добавьте срез на вкладке анализа сводной, например по менеджеру. Через подключения отчётов его можно связать с несколькими сводными, использующими общий кэш или модель данных.
После изменения источника обновите сводную и проверьте новые записи. Расширение исходной таблицы не гарантирует обновления сводной: проверьте настройки вашей версии.
Power Pivot и модель данных: отчёт на основе нескольких таблиц
Допустим, в присланных файлах продажи записаны по заказам, а план — по месяцам и командам. Чтобы сравнить показатели, нужно учесть эту разницу. Если добавить месячный план к каждой продаже и затем сложить значения, он будет учтён многократно.
Надстройка Power Pivot позволяет работать с моделью данных: хранить таблицы раздельно, создавать между ними связи и рассчитывать показатели на языке DAX. Это удобно, когда отчёт использует несколько источников, которые нельзя просто сложить в одну таблицу.
Связываем таблицы через справочники
Загрузим продажи и план в модель данных и создадим общие справочники месяцев и команд. В справочнике каждая команда встречается один раз, а в продажах её идентификатор может повторяться во множестве заказов. Такая связь называется «один ко многим».
Для строк и фильтров сводной возьмём месяцы и команды из справочников. Тогда выбранная команда будет ограничивать и продажи, и соответствующий план. При этом сравнивать показатели можно только в доступной детализации: месячный план без распределения по дням не подходит для оценки выполнения дневного плана.
Добавляем показатели и проверяем результат
В Power Pivot расчёт показателя оформляют как меру. Например, выполнение плана — это сумма продаж, делённая на сумму плана. Мера пересчитывается с учётом выбранных фильтров: месяца, команды или их сочетания.
После настройки выберем одну команду и месяц, отдельно проверим суммы в исходных файлах и сравним их со сводной. Если продажи меняются при переключении команды, а план остаётся общим, проверим связи и убедимся, что поле фильтра взято из справочника.
Как собрать читаемый дашборд в Excel
Посмотрим на дашборд глазами пользователя: понятно ли, что происходит с показателями, с чем сравниваются значения и где искать подробности? От этого зависит, какие числа вынести на первый экран, какие графики добавить и как расположить фильтры.
Выбираем ключевые показатели и убираем лишнее
На первом экране оставьте показатели, отвечающие на вопросы пользователя. В отчёте продаж это могут быть выручка, число заказов, средний чек и выполнение плана. Рядом с итогом нужен ориентир: прошлый сопоставимый период или согласованный план.
Подбираем диаграммы для динамики, сравнения и структуры
Линия подходит для динамики по времени, горизонтальные столбцы — для сравнения товаров или каналов. Для состава расходов можно использовать столбцы с накоплением. Когда важно узнать точное значение, небольшая таблица часто удобнее графика.
Размещаем показатели, графики и фильтры на одном экране
Создайте отдельный лист дашборда. Вверху расположите название, период и основные числа, ниже — динамику и сравнение групп. Фильтры соберите рядом. Диаграммы стройте по сводным, карточки показателей связывайте с расчётными ячейками.
Обозначаем период, единицы измерения и дату обновления
Подпись «Продажи — 1,8» ничего не говорит без единицы и периода. Лучше указать: «Выручка за сентябрь, млн ₽». Для относительных показателей поясните базу сравнения. Отметьте и фильтры, влияющие на экран.
Разделяйте время обновления книги и период полноты данных. Файл могли обновить сегодня, загрузив операции только по прошлую пятницу. Функция текущего времени не подтверждает успешную загрузку; отметку об обновлении нужно связывать с завершением процесса и проверками.
Примеры дашбордов в Excel для продаж, маркетинга и финансов
Если решите адаптировать пример под свою задачу, сначала уточните состав показателей и правила их расчёта.
Продажи: динамика, выполнение плана, товары и менеджеры
Руководитель хочет понять, где команда отстаёт от плана. На первом экране покажем фактические продажи, план и отклонение, ниже — динамику по месяцам и сравнение менеджеров. Для анализа ассортимента добавим таблицу товаров с продажами и долей в общей сумме.
Маркетинг: расходы, конверсии и эффективность каналов
Для сравнения каналов нужны расходы, визиты или лиды, целевые действия и атрибутированная выручка. Фиксируем период, модель атрибуции и окно учёта конверсий.
ROAS показывает отношение атрибутированной выручки к рекламным расходам.
Для ROMI потребуется определить учитываемую прибыль и состав затрат.
Для CAC — новых клиентов и затраты на их привлечение.
Данные разных рекламных кабинетов нельзя без проверки суммировать: они могут приписать себе один заказ.
Финансы: план-факт, структура расходов и движение денег
Финансовый дашборд может содержать сравнение бюджета и факта по статьям, структуру расходов и отдельный блок движения денег. Для каждого блока нужен свой источник и метод учёта. Платёжная выписка сама по себе не даёт отчёт о прибыли.
Например, начальный остаток равен 100 тыс. ₽, поступления — 500 тыс. ₽, выплаты — 420 тыс. ₽. Чистый денежный поток составляет 80 тыс. ₽, конечный остаток — 180 тыс. ₽. Внутренние переводы между включёнными в отчёт счетами исключаем из внешних поступлений и выплат. Полученный остаток сверяем с данными по счетам.
Типичные ошибки
У разных отчётов есть общие точки риска.
Ошибка | Последствие | Как проверить |
Усреднили конверсии без учёта объёма | Маленький канал влияет на итог как большой | Разделить общие целевые действия на общую базу |
Присоединили бюджет к каждому заказу | Расходы учтены многократно | Сверить суммы до и после соединения |
Приняли оплату за выручку периода | Авансы и задолженность искажают результат | Сопоставить даты платежей и признания выручки |
Автоматизация отчётности в Excel: что можно поручить инструментам
Автоматизируйте проверенный процесс: загрузку, преобразования и оформление. Если ручной отчёт использует спорную формулу, кнопка обновления лишь быстрее повторит ошибку.
Обновление через Power Query
Power Query при обновлении загружает данные и повторяет сохранённые преобразования. Но полноту выгрузок и готовность данных за период проверяем сами.
После команды «Обновить всё» дождёмся завершения загрузки и проверим, что сводные пересчитались. Иначе в экспорт могут попасть старые итоги. Для обновления по расписанию при закрытом Excel потребуется отдельное решение.
Макросы и VBA
VBA пригодится для повторяемых действий с книгой: подготовить лист к печати, применить оформление, сформировать отдельные файлы по подразделениям. Сначала опишите последовательность и условия завершения, затем автоматизируйте её.
Макрос экспорта должен дождаться загрузки и пересчёта, а при ошибке — остановиться с понятным сообщением. Книгу с VBA сохраняют в формате .xlsm; выполнение зависит от настроек безопасности. VBA не запускается в Excel для браузера, поэтому среду пользователя нужно учитывать заранее.
Copilot и AI-инструменты в Excel
По состоянию на октябрь 2026 года Microsoft описывает для Copilot в Excel создание и объяснение формул, работу с листами, сортировку, фильтрацию, построение диаграмм и сводных таблиц. В режиме редактирования помощник может вносить изменения непосредственно в книгу. Доступность зависит от лицензии, платформы, версии приложения и настроек организации: наличие обычного Excel не гарантирует весь набор ИИ-функций.
Как проверять предложенные ИИ формулы, преобразования и выводы
Возьмите небольшой набор, который можно посчитать вручную. Добавьте обычную продажу, отменённый заказ, нулевой знаменатель и пустое значение. Проверьте не только итог, но и то, какие строки вошли в расчёт.
Шаблоны отчётов и дашбордов: как адаптировать под свои данные
Шаблон экономит время, если его структура соответствует задаче. Прежде чем менять источник, разберитесь, на каких данных он работает и что считает.
Что проверить перед использованием шаблона
Посмотрите, что означает одна строка источника, какие поля обязательны и как учитываются статусы, возвраты и периоды. Проверьте версию Excel, внешние подключения, наличие макросов и скрытые листы с расчётами.
Как заменить источники и сохранить формулы, связи и диаграммы
Работайте с копией шаблона. Сопоставьте поля новой выгрузки с ожидаемой схемой и приведите названия и типы в Power Query. Если данные вставляются вручную, заменяйте входные строки, сохраняя расчётные столбцы.
Какие инструкции нужны для дальнейшего обновления
Добавьте лист «Инструкция»: откуда брать файлы, куда их сохранять, какие столбцы обязательны, как запустить обновление и проверить его завершение. Укажите определения показателей и известные ограничения.
Как проверить отчёт перед передачей пользователям
Сверяем итоговые суммы и выборочные расчёты
Сравните общие суммы с источником за тот же период и по тем же правилам. Затем выберите одну категорию или день и проверьте несколько операций вручную. Если просто скопировать исходную формулу, вместе с ней можно повторить ошибку.
Проверяем фильтры, пустые периоды и деление на ноль
Переключите каждый срез и убедитесь, что меняются все нужные показатели. Проверьте комбинацию фильтров без операций. Подтверждённый ноль, отсутствующая выгрузка и неприменимый показатель должны различаться.
При нулевых рекламных расходах ROAS не равен нулю: отношение не определено. Можно показать прочерк с пояснением. Массовая замена ошибок через ЕСЛИОШИБКА на нули скрывает причины и иногда создаёт ложное впечатление корректных данных.
Проверяем обновление на новой выгрузке
Добавьте тестовый файл следующего периода и пройдите инструкцию целиком. Проверьте даты, категории, суммы и графики. Повторное обновление с тем же набором файлов не должно увеличивать результат.
Как освоить Excel и Google Таблицы для работы с отчётностью
Работа с отчётами требует умения организовать данные, выбрать расчёт, показать результат и проверить его. Эти навыки полезно закреплять вместе на самостоятельных задачах.
Базовые навыки: структура данных, формулы, сводные и визуализация
Сначала разберитесь, как работать с датами, числами и текстом, закреплять ссылки в формулах, считать по условиям и находить значения. Затем переходите к сводным таблицам, диаграммам и обработке регулярных выгрузок. Новые формулы проверяйте на небольших примерах, где результат можно посчитать вручную. Эти навыки пригодятся и в Google Таблицах, но для задач, которые в Excel решают через Power Query, Power Pivot и VBA, понадобятся другие инструменты.
Практика
Путь от отдельных операций до самостоятельно собранного отчёта можно пройти на курсе «Excel и Google Таблицы». Программа начинается с основ и обработки данных, затем охватывает формулы, сводные таблицы, объединение источников и визуализацию. Предусмотрены базовые приёмы Power Query и отдельный урок об использовании ИИ и проверке его ошибок.
Обучение включает короткие видеоуроки, практические задания и поддержку эксперта и куратора. В финале студенты создают аналитический дашборд на данных Spotify. Проект помогает связать отдельные инструменты в законченную работу: подготовить данные, рассчитать показатели и представить результат.
Заключение: чек-лист готовности отчёта и дашборда
Перед передачей файла проверьте семь пунктов:
Понятно, кто использует отчёт и на какие вопросы он отвечает.
Для показателей зафиксированы источники, формулы и ограничения.
Определена детализация данных, проверены ключи и пропуски.
Преобразования воспроизводимы и не создают лишние записи.
Расчёты сверены независимо, фильтры работают ожидаемо.
На дашборде обозначены период, единицы и полнота данных.
Новая выгрузка проходит обновление по понятной инструкции.
Начните с одной задачи и нескольких показателей. Доведите их до результата, который можете проверить и объяснить. Затем добавляйте источники, графики и автоматизацию.
