Как сочетать Power Query и языковые модели в финансовой отчётности. Выгрузки, сверки, очистка и контроль расчётов в Excel.
Ежемесячная отчётность редко начинается с расчёта. Обычно сначала нужно собрать десять выгрузок, понять, почему в одной таблице дата записана как текст, в другой у контрагента пропал ИНН, а в третьей поменяли название столбца. Потом свести всё в один формат, найти расхождения и только после этого переходить к цифрам.
Power Query хорошо закрывает именно этот слой работы. Он забирает данные из файлов и папок, очищает их по заданным правилам, соединяет справочники, создаёт повторяемый маршрут подготовки данных. Языковая модель помогает разобраться в постановке задачи, предложить формулу, написать шаг на языке M или макрос. Точные суммы, контрольные соотношения и итоговые показатели должны оставаться в Excel, Power Query, Power Pivot или проверяемом коде.
Общее правило работы с ИИ и Excel уже разобрано в статье «Нейросеть и Эксель — как совмещать и не терять деньги в расчётах». Здесь важнее финансовый контур, в котором одна ошибка типа данных способна незаметно испортить сверку, а красивый ответ модели создаёт ложное ощущение готового результата.
Где проходит граница ответственности
Языковая модель похожа на стажёра с красным дипломом. Она быстро читает инструкцию, понимает смысл задачи, помнит тысячи типовых решений и может написать вполне рабочий код. Но стажёр не должен один утверждать оборотно-сальдовую ведомость, рассчитывать резерв или объяснять расхождение в регуляторной форме.
Полезное распределение работы выглядит так.
Модель помогает сформулировать правило очистки, объясняет незнакомую формулу, готовит черновик M-запроса, VBA-макроса или SQL-запроса. Она же неплохо находит логические дыры в уже подготовленной методике, предлагает контрольные вопросы и превращает техническое описание в комментарий для руководителя.
Power Query загружает, преобразует и объединяет данные. Excel и Power Pivot считают показатели по утверждённой логике. Код выполняет алгоритм, если расчёт сложнее табличной модели. Аналитик определяет правила, проверяет результат и отвечает за экономический смысл.
Здесь важно не подменять одну задачу другой. Фраза «посчитай просрочку по файлу» для модели выглядит естественно, но внутри неё слишком много неопределённости. Какие даты брать за основу. Как учитывать частичное погашение. Что делать с выходными. Как округлять сумму. Какие договоры исключать. Языковая модель заполнит пробелы сама, причём уверенно. В финансовой работе это и есть риск.
Гораздо надёжнее описать логику, проверить её на тестовом наборе и передать вычисление Power Query, Excel, Power Pivot либо коду. Модель в таком процессе остаётся помощником аналитика, а не расчётным движком.
Сначала строится маршрут данных
Финансовый отчёт удобно представить как пять последовательных слоёв. Есть источник, слой подготовки, расчётная модель, контроль и пояснение результата.
Источник — выгрузки из учётной системы, CRM, банковского контура, файлового хранилища или справочников. Слой подготовки приводит их к единой структуре. Расчётная модель формирует показатели. Контроль сверяет итоги с исходными данными и предыдущим периодом. На последнем слое аналитик объясняет отклонения.
Самая дорогая привычка в этой цепочке — вручную копировать данные из новых файлов в старую рабочую книгу. Она кажется быстрой, пока объём не вырастает до нескольких десятков выгрузок. После этого никто уже точно не помнит, какой лист обновили, откуда взяли курс, почему одна строка не попала в сводную таблицу и где в формуле появился дополнительный ноль.
Power Query фиксирует маршрут в последовательности шагов. При следующем обновлении он повторяет те же действия. Это не отменяет проверки. Зато проверка становится предметной. Аналитик сверяет конкретный результат работы запроса, а не пытается восстановить вручную всю историю копирования.
Язык M, на котором Power Query хранит такие шаги, функциональный и чувствительный к регистру. Его официальная документация полезна как справочник, когда модель предложила функцию или синтаксис, который нужно проверить. Microsoft описывает язык M и его систему типов.
Сценарий с ежемесячными выгрузками из папки
Типовая задача финансовой службы — собрать одинаковые отчёты за несколько подразделений или периодов. Каждый файл содержит данные за месяц, но имена файлов отличаются, часть листов названа по-разному, один сотрудник добавил примечание над таблицей, другой поменял порядок столбцов.
Первое решение здесь должно быть организационным. Зафиксируйте шаблон выгрузки, имя папки, минимальный набор полей и правило хранения периода. Уже после этого подключайте папку в Power Query.
Запрос читает все файлы, выбирает нужный лист или таблицу, удаляет служебные строки, приводит названия полей к стандарту и добавляет строки в общую таблицу. В финансовом наборе почти всегда нужны как минимум дата отчёта, идентификатор записи, подразделение, статья, сумма, валюта и источник файла. Поле с именем файла лучше сохранить. Оно пригодится, когда потребуется найти конкретную строку в исходной выгрузке.
Операция добавления запросов работает по названиям столбцов, а не по их позиции в таблице. Если в одном файле столбец называется «Сумма», а в другом «Сумма операции», Power Query создаст два разных поля. В строках, где нужного поля нет, появится null. Это ожидаемое поведение, которое описано в документации Microsoft по Append queries.
Именно здесь модель может сэкономить время. Ей можно показать обезличенный образец структуры и попросить составить таблицу соответствия полей. Например, какие варианты заголовков считать одним показателем, какие листы исключить, как извлечь период из имени файла. Затем аналитик проверяет правило на трёх-четырёх реальных форматах и переносит его в Power Query.
Не стоит поручать модели ответ на вопрос, корректно ли собран итог за квартал. Она может пересказать, как выглядит таблица, и даже предложить правдоподобную сумму. Но фактический итог должен формироваться в обновляемом запросе или расчётной модели.
Сценарий со сверкой двух систем
Сверка остатков, платежей, заявок или договоров обычно выглядит как сравнение двух массивов. В одной таблице есть данные учётной системы, в другой — выгрузка из внешнего сервиса или операционного контура. Формально ключи совпадают. На практике один номер хранится как текст с ведущими нулями, другой как число, а в третьем месте к нему добавили пробел.
Power Query позволяет собрать такую сверку в повторяемый процесс.
Сначала каждая таблица приводится к своей нормальной структуре. Идентификаторы становятся текстом, даты получают явный тип даты, денежные поля — тип десятичного или фиксированного числа в зависимости от методики. Затем таблицы объединяются через Merge. Для финансовой сверки особенно полезны левое внешнее соединение и левое антисоединение. Первое добавляет реквизиты из второй системы к базовой выборке. Второе оставляет строки, для которых соответствие не найдено.
У объединения есть важная ловушка. Power Query требует сопоставимые типы для полей, по которым выполняется соединение. Совпадающие названия столбцов сами по себе ничего не гарантируют. Microsoft отдельно предупреждает, что разные типы могут дать неверный результат объединения. Это стоит держать в контрольном листе каждой сверки. Описание Merge queries также показывает, что порядок нескольких выбранных полей влияет на результат.
После объединения полезно сформировать четыре набора. Совпавшие строки, строки только из первой системы, строки только из второй системы и строки с отличающимися суммами. Последний набор лучше не пытаться интерпретировать автоматически. Его задача — показать аналитику очередь исключений.
Языковая модель здесь помогает в трёх местах. Она формулирует перечень проверок, предлагает M-код для нормализации номера договора или описывает причину расхождения понятным языком для владельца процесса. Само решение о допустимости расхождения остаётся за методикой и сотрудником, который знает экономический смысл операции.
Нечёткое сопоставление по похожему тексту в финансовой сверке требует отдельной осторожности. Оно удобно для предварительного поиска вариантов по названию контрагента. Оно плохо подходит для автоматического признания совпадения. ИНН, договор, лицевой счёт и уникальный внутренний идентификатор надёжнее любых похожих наименований.
Сценарий с грязными данными и форматами
Большая часть ошибок в отчётности возникает до первой формулы. Столбец с номерами договоров превратился в число и потерял нули. Дата 03.04.2026 интерпретировалась по другому региональному формату. В денежном поле оказались пробелы, неразрывные пробелы и текст «н/д». В справочник попали два варианта названия одной статьи.
Power Query умеет определить типы автоматически, но автоматике нельзя выдавать доверенность на весь файл. Для неструктурированных источников, включая Excel, CSV и текстовые файлы, Power Query определяет типы по значениям. В стандартном режиме он анализирует первые 200 строк и добавляет шаги повышения заголовков и изменения типов. Это полезное ускорение, но не подтверждение корректности всей колонки. Microsoft рекомендует явно задавать типы данных.
В финансовом шаблоне проверки я обычно выделяю четыре класса полей.
- Уникальные идентификаторы, номера счетов и коды хранятся как текст.
- Даты приводятся с явно заданной локалью и проверяются на невозможные значения.
- Денежные поля очищаются от лишних символов и получают единый числовой тип.
- Справочные значения нормализуются по утверждённому справочнику.
Особенно неприятна локаль. Один и тот же текст может означать разные даты и разные десятичные разделители в зависимости от региональных настроек. Power Query использует региональный формат операционной системы, если он не переопределён в параметрах текущего файла. Поэтому перенос книги между компьютерами без явной настройки способен изменить интерпретацию данных. Это не ошибка модели и не ошибка Excel. Это неописанное правило преобразования, которое осталось в голове у автора файла.
Языковая модель хорошо объясняет, какой шаг M нужен для очистки пробелов, извлечения кода из номера счёта или преобразования даты. Ей нужно давать небольшой обезличенный образец, ожидаемый результат и явно указывать локаль. После этого код проверяют на копии файла, где заранее известен правильный итог.
Сценарий с объединением справочников и фактов
Финансовая отчётность часто требует связать факт операций со справочником статей, центров финансовой ответственности, валют, продуктов или контрагентов. Это задача не про формулу ВПР. Это задача про качество ключа и модель данных.
Сначала подготовьте таблицу фактов. В ней одна строка должна описывать одну операцию, остаток на дату или иной минимальный объект анализа. Затем подготовьте справочник, где каждый ключ встречается один раз. Если в справочнике две строки с одним кодом, объединение размножит строки факта. Сумма в отчёте станет выше, хотя запрос формально отработал без ошибки.
Перед Merge полезно проверить три вещи. Уникальность ключа в справочнике, тип ключа в обеих таблицах и число совпадений после объединения. В отчётах с крупными суммами это должен быть отдельный контрольный показатель, а не наблюдение по глазам.
Модель может написать шаг проверки дубликатов или запрос, который покажет несопоставленные коды. Но модель не знает, считать ли новый код допустимым исключением, ошибкой выгрузки или изменением управленческого справочника. Это решение зависит от конкретной методики.
Когда подготовленные таблицы стабилизированы, их можно загрузить в модель данных и считать показатели в Power Pivot. Такое разделение полезно по простой причине. Power Query отвечает за получение и подготовку данных. Power Pivot и DAX отвечают за связи и расчёты. В одной рабочей книге смешивать оба слоя возможно, но для поддержки модели лучше понимать, на каком именно слое возникло расхождение.
Как ставить задачу языковой модели
Фраза «напиши Power Query для отчёта» почти бесполезна. Хороший запрос содержит структуру входных данных, правило преобразования, пример ожидаемого результата и ограничения.
Для обезличенного примера можно написать так.
Есть CSV-файл с полями «Дата операции», «Номер договора», «Сумма», «Подразделение». Номер договора должен остаться текстом с ведущими нулями. Даты записаны в формате дд.мм.гггг. Нужно удалить пустые строки, заменить неразрывные пробелы в сумме, задать типы и вернуть M-код с комментариями к каждому шагу. Перед ответом задай пять уточняющих вопросов о возможных исключениях.
Такой запрос не передаёт клиентские данные, но содержит достаточный контекст. Модель сначала поможет обнаружить неоговорённые условия. Например, что делать со сторнирующими операциями, как трактовать пустую дату, где хранится период отчёта, нужна ли загрузка ошибок отдельной таблицей.
После получения кода полезно проверить его в трёх режимах. На маленьком наборе с известным результатом, на реальной выгрузке за один период и на файле с намеренно испорченными значениями. Если запрос переживает все три проверки, его можно включать в повторяемый маршрут.
Код от модели нельзя вставлять в рабочую книгу вслепую. Отдельно смотрите на шаги удаления столбцов, фильтрацию строк, группировку и объединения. Именно они чаще всего меняют состав данных. M-код должен быть понятен человеку, который поддержит отчёт через полгода.
Ловушки языковой модели в финансовой работе
Галлюцинация выглядит как неразборчивая вывеска издали. Пока смотришь издалека, кажется, что всё написано правильно. Начинаешь читать буква за буквой — появляется несуществующая функция, неверная ссылка на столбец или правило, которого в методике никогда не было.
Первая ловушка — модель говорит, что обработала файл, хотя на самом деле пересказала его фрагмент. Это особенно опасно для больших таблиц, сложных книг с объединёнными ячейками и файлов со скрытыми листами. Надёжнее загружать в модель очищенный CSV или небольшой образец структуры. Форматированный Excel-файл лучше оставить Power Query и Excel.
Вторая ловушка — арифметика внутри диалога. Модель может верно объяснить формулу NPV, но ошибиться в сумме при длинном списке значений. Либо дать правильное число без возможности воспроизвести расчёт. В отчётность должны попадать только показатели, которые можно пересчитать из исходных данных.
Третья ловушка — выдуманные свойства функции. Модель способна предложить параметр M-функции, которого не существует, или составить синтаксис из похожих примеров. Официальный справочник Microsoft и тестовый запрос в Power Query быстро покажут ошибку. Сложнее ситуация, когда код исполняется, но возвращает неверный набор строк. Поэтому контроль состава данных важнее отсутствия красных ошибок.
Четвёртая ловушка — ложное понимание бизнес-правила. Фраза «исключи технические операции» кажется понятной только человеку из конкретного подразделения. Модели нужны явные признаки исключения, коды операций и приоритет правил. Если их нет, она дополнит правило статистическим предположением.
Что остаётся зоной аналитика
Даже идеально собранная таблица не отвечает сама по себе на главный финансовый вопрос. Рост остатка может быть следствием сезонности, изменения методики, крупной разовой операции, ошибки в источнике или настоящего изменения бизнеса. Модель способна перечислить эти версии и составить аккуратный комментарий. Выбрать верную версию она не может без знания процесса, договорённостей с владельцами данных и истории предыдущих периодов.
Поэтому в рабочей книге полезно отделять технический результат от управленческого вывода. На одном листе или в отдельном запросе лежат подготовленные факты, исключения и контрольные суммы. В другом месте находятся показатели и расшифровки. Текстовое пояснение сначала пишет аналитик либо согласует его с владельцем процесса. Языковая модель может отредактировать формулировку, сократить её для руководителя и проверить, что в комментарии нет противоречий. Ей не стоит передавать право объяснять причину отклонения на основании одного только файла.
Контроль должен быть встроен в процесс
Хорошая автоматизация отчётности не заканчивается кнопкой «Обновить всё». После обновления нужен короткий набор контрольных соотношений.
Первое соотношение — количество строк. Сколько строк пришло из каждого источника, сколько удалено как пустые или ошибочные, сколько записей попало в исключения. Резкое изменение количества строк часто замечают раньше, чем изменение суммы.
Второе — контроль сумм. Итог до преобразования, итог после преобразования, итог по исключениям и итог в отчётной модели должны объяснимо связываться между собой. Если часть строк отфильтрована, причина должна быть видна в отдельной таблице исключений.
Третье — контроль ключей. Сколько строк не нашло соответствия в справочнике. Сколько ключей оказалось неуникальными. Сколько записей совпало по двум системам при сверке.
Четвёртое — контроль периода и обновления. Какая дата отчёта загружена, когда обновлялся источник, какой файл использован. Особенно полезно сохранять имя исходного файла в данных Power Query.
Для чувствительных источников добавляется ещё один слой. Power Query использует уровни приватности источников и может ограничить их комбинирование, чтобы данные не передавались в нежелательный контур. Отключение этих ограничений ради скорости требует отдельной оценки. Microsoft прямо рекомендует не игнорировать уровни приватности для конфиденциальных данных и следить за запросами к источникам. Актуальные рекомендации по безопасности Power Query стоит включить в технический регламент команды.
Ограничения конкретного банка или финансовой службы определяет её ИБ и внутренний порядок работы с данными. Для выбора учебного контура и сценариев можно использовать программу «ИИ в финансовых организациях и банках», но отдельное согласование источников и доступа к ним остаётся задачей организации.
Языковая модель в этом процессе полезна как ревизор документации. Ей можно дать обезличенное описание запроса и попросить назвать возможные точки отказа, недостающие контрольные показатели и неявные допущения. Затем аналитик сверяет рекомендации с методикой. Это хороший способ увидеть слепую зону, но не замена внутреннему контролю.
Как внедрять связку в команде
Начинать лучше с одного повторяемого процесса, который уже отнимает время каждый месяц. Например, со сборки выгрузок по подразделениям, сверки двух систем или подготовки справочника для управленческой отчётности.
На первом этапе команда описывает входные файлы и результат. На втором собирает Power Query с прозрачными шагами. На третьем добавляет контрольные таблицы. Только после этого имеет смысл подключать модель для ускорения формул, M-кода, документации и пояснений.
Такой порядок даёт два результата. Процесс становится быстрее уже без магии ИИ. Затем языковая модель сокращает время на постановку задач и рутинное написание кода, не получая права самостоятельно определять итоговую цифру.
Корпоративное обучение Power Query
Посмотреть программу корпоративного курса Power Query. На занятии команда разбирает собственные типы выгрузок, правила очистки, объединение таблиц, сверки и подготовку данных для Power Pivot.