Как сделать сводный отчёт в Excel из разных источников

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

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

Я предлагаю начинать с устройства отчёта: что считается одной записью, какие источники нужны и какие проверки позволяют доверять результату. Ниже — порядок сборки свода в Excel, место Power Query и готовые промпты для задач, где приходится разбирать отличающиеся формы.

Как сделать сводный отчёт в Excel: порядок действий

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

Рабочая последовательность выглядит так:

1. Выбрать период и перечень обязательных источников.
2. Определить, что означает одна строка общего массива.
3. Описать поля, единицы измерения и правила расчёта.
4. Привести каждый источник к этой структуре.
5. Объединить строки или соединить связанные таблицы.
6. Проверить полноту, суммы, дубли и пропуски.
7. Построить сводную таблицу и заполнить итоговый шаблон.

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

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

Если исходники ещё лежат в разных файлах, сначала нужен общий массив.

Какой шаблон нужен до загрузки файлов?

Шаблон должен определять смысл полей и правила заполнения, а не только внешний вид отчёта. Для каждого показателя укажите период, единицу измерения, источник и способ расчёта — иначе одинаково названные столбцы могут содержать разные данные.

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

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

Также заранее определите, какие показатели можно складывать. Количество событий обычно суммируют при отсутствии дублей. Проценты требуют исходных числителей и знаменателей: среднее арифметическое процентов по точкам может дать неверный общий результат.

Для первого разбора файлов можно использовать такой промпт:

Помоги подготовить структуру регулярного отчёта.

Вход: приложенные выгрузки и пустой итоговый шаблон.
Пока не объединяй данные и не рассчитывай итоги.

Для каждого файла покажи:
— листы и названия столбцов;
— что означает одна строка;
— период и единицы измерения;
— возможный ключ записи;
— соответствие полям итогового шаблона.

Отдельно перечисли неоднозначности:
разные определения показателя, отсутствующий период,
непонятные единицы, смешение итогов и отдельных записей.

Не выбирай трактовку самостоятельно.
Верни таблицу соответствий и вопросы, которые нужно решить.

Результат этого шага — согласованные правила. Например, «Код точки» из одного файла и «Магазин» из другого можно объединять только после проверки справочника. Похожее название ещё не доказывает, что поля обозначают одно и то же.

Когда использовать Power Query, а когда подключать ИИ?

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

Разная структура файлов сама по себе не делает Power Query бесполезным. Для каждого устойчивого формата можно настроить отдельную подготовку: выбрать нужный лист, убрать служебные строки, переименовать поля и задать типы. Затем результаты объединяют.

В Power Query есть две разные операции. Добавление запросов помещает строки таблиц друг под другом; столбцы сопоставляются по названиям. Это описано в официальной документации Microsoft. Такой вариант подходит, например, для однотипных выгрузок обращений из разных отделов.

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

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

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

Как поручить ИИ сборку из нескольких Excel-файлов?

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

В интерфейсе с анализом файлов сначала проверьте чтение одного источника: попросите показать листы, заголовки и несколько записей. Затем загрузите остальные файлы. Такой пробный шаг помогает заметить неверно выбранный лист или потерянную строку заголовков до общей сборки.

Готовое задание для обработки:

Собери недельный отчёт из приложенных Excel-файлов.
Используй приложенный шаблон и согласованную таблицу
соответствий полей.

Правила:
1. Обрабатывай только указанный отчётный период.
2. Сохраняй код точки как текст, включая ведущие нули.
3. Не заменяй пустые значения нулём.
4. Не включай строки промежуточных итогов как отдельные записи.
5. Не удаляй предполагаемые дубли без заданного правила.
6. Сохраняй имя исходного файла, лист и номер строки.
7. Считай Δ как факт минус план.
8. Не объединяй разные единицы измерения.

Если правило отсутствует или данные неоднозначны,
помести запись в список исключений.
Не придумывай недостающие значения.

Подготовь Excel-файл с листами:
— Подготовленные данные;
— Сводный отчёт;
— Исключения;
— Проверка.

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

Для PDF добавьте требование сохранять номер страницы и исходную формулировку, предварительно проверив на выбранном инструменте, что он делает это надёжно. Например, статус работ «выполнено частично» нельзя превращать в «выполнено» ради удобства таблицы. Нечитаемые суммы и даты должны остаться исключениями до проверки по документу.

Коды, даты и суммы требуют отдельных правил типов. В Power Query тип данных задаётся для столбца; варианты типов и особенности преобразования описаны в справке Microsoft. Для кода с ведущими нулями задавайте текст, а для суммы — согласованный числовой тип и формат источника.

Как проверить, что сводный отчёт собран правильно?

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

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

Затем проверьте движение строк. Каждая прочитанная запись должна попасть в подготовленные данные, исключения или явно описанную категорию служебных строк. Если система показывает только число строк на выходе, непонятно, что случилось с остальными.

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

Для ручного spot-check выберите 3–5 записей: обычную строку, пустое значение, предполагаемый дубль, нестандартную дату и запись с заметным отклонением. Проследите каждую от исходного файла до итогового отчёта.

Проверь собранный отчёт по исходным файлам.
Не исправляй данные в ходе проверки.

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

Для каждого расхождения укажи файл, лист, строку,
ожидаемое значение, полученное значение
и правило, по которому выполнено сравнение.

Отдельно покажи выбранные контрольные записи:
исходные поля → преобразования → итоговая строка.

Этот промпт помогает составить протокол проверки, но окончательную сверку нельзя замыкать только на ответе той же модели. Контрольные значения посчитайте в Excel или проверьте по исходным документам.

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

Сколько времени занимает сборка и что автоматизировать дальше?

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

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

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

У регулярного отчёта должен быть владелец: он следит за комплектностью источников и принимает результат. Также нужен порядок действий при сбое — кому вернуть неверную выгрузку, как отметить задержку и можно ли отправить отчёт с неполными данными. Например, отсутствие отчёта отдела стоит показывать в статусе сборки, а не скрывать пустой строкой.

Когда свод проверен, можно переходить к объяснению результатов. Для структуры и формулировок есть статья «Отчёт руководителя с помощью ИИ». Для работы с причинами отклонений — порядок план-факт анализа.