Оптимизация файла объемом 200 МБ через Power Query

Тяжелый Excel-отчет редко становится тяжелым за один день. Обычно он постепенно обрастает выгрузками из 1С, дополнительными листами, формулами, прогнозами и ручными исправлениями. В какой-то момент файл начинает открываться по несколько минут, реагировать на действия с задержкой и регулярно «ломаться» после очередной загрузки данных.

Именно так выглядел процесс закрытия промо-компенсаций в одном из рабочих отчетов. Книга весила больше 200 МБ. Данные из 1С копировали на листы вручную, затем протягивали формулы и проверяли, не сместились ли столбцы в новой выгрузке. Когда к исходным данным добавились прогнозы, поддерживать такую схему стало еще сложнее.

В результате архитектуру отчета пришлось менять целиком: данные вынесли за пределы Excel, предварительную подготовку сложных файлов отдали Python, а подключение, очистку и объединение данных оставили Power Query.

После этого размер книги сократился до 40 МБ, процесс ускорился в 3-4 раза, а часы, которые раньше уходили на поиск ошибок, удалось убрать из регулярной работы.

Точка А: 200 МБ ручной работы

Исходный процесс выглядел привычно для многих Excel-отчетов:

1. получить выгрузку из 1С;
2. открыть рабочую книгу;
3. скопировать данные на нужный лист;
4. проверить порядок столбцов;
5. протянуть или обновить формулы;
6. повторить то же для других источников;
7. найти места, где что-то «поехало».

Основная проблема была не только в объеме данных. Книга одновременно использовалась как хранилище, инструмент обработки и итоговый интерфейс для пользователя.

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

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

Почему Excel-файл разросся

Формат .xlsx представляет собой сжатый XML-архив. Это не означает, что Excel хранит только компактный набор значений. При работе с листами в книге накапливаются данные о форматировании, структуре ячеек и других элементах листа.

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

В такой архитектуре файл становится одновременно:

• хранилищем первичных данных;
• местом для преобразований;
• расчетной моделью;
• пользовательским интерфейсом.

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

Шаг 1. Вынести первичные данные за пределы книги

Первым изменением стало хранение выгрузок во внешних текстовых файлах - TXT и CSV. Вместо того чтобы копировать данные из 1С на листы рабочей книги, их стали сохранять отдельно.

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

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

В зависимости от задачи данные можно:

• загрузить в модель данных;
• оставить в виде подключения;
• вывести на отдельный лист уже после очистки и объединения.

Если данные загружать напрямую в модель Power Pivot или использовать только подключения, размер книги может быть в разы меньше, чем при хранении тех же таблиц на листах. Это связано не только с отсутствием копий исходных данных на листах, но и со способом сжатия данных в модели.

В Power Pivot используется движок VertiPaq. Он хранит данные в формате, который хорошо сжимает повторяющиеся значения и структуру таблиц. Подробно этот принцип описан в материале SQLBI о сжатии данных в Power Pivot и Tabular: Data Compression in Power Pivot and Tabular.

Общий принцип подключения внешних текстовых файлов и работы с ними через Power Query описан в документации Microsoft.

Шаг 2. Подготовить сложные прогнозы с помощью Python

С обычными выгрузками Power Query справлялся напрямую. Но прогнозы имели неудобную структуру: один лист соответствовал одной сети. Для дальнейшего объединения в Excel такой формат был непрактичен.

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

Для этой предварительной подготовки использовали Python-скрипт, собранный в .exe. Пользователю не требовалось устанавливать Python, запускать среду разработки или разбираться в коде. Он получал готовый инструмент: подать на вход исходный файл и получить подготовленную таблицу.

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

• принять прогноз в неудобном формате;
• привести его к единой структуре;
• подготовить файл для последующей загрузки в Excel.

Это не попытка заменить Excel отдельным приложением. Python стал промежуточным слоем между источником и Power Query - мостом для тех данных, которые было неудобно готовить формулами или вручную.

Почему не обошлись только Power Query или только Python

Технически часть операций можно было бы выполнить одним инструментом. Но в этом процессе у Power Query и Python были разные задачи.

Python оказался удобнее для сложной предварительной структуризации. Он позволил автоматизировать подготовку прогнозов и упаковать ее в .exe, чтобы пользователь работал с готовой программой, а не с кодом.

Power Query был удобнее для дальнейшей работы внутри Excel:

• подключения к внешним файлам;
• очистки данных;
• объединения таблиц;
• обновления результата через интерфейс книги;
• интеграции с существующим отчетом.

Если делать все только на Python, пользователю пришлось бы работать с отдельным процессом подготовки данных, а интеграция с Excel стала бы менее бесшовной. Если делать все только в Power Query, сложную структуру прогнозов пришлось бы разбирать внутри запросов или частично оставлять ручные операции.

Гибридная схема разделила обязанности между инструментами: Python подготовил сложный источник, Power Query встроил результат в отчет.

Как Power Query убрал зависимость от позиции столбца

Обычная ссылка вида =Лист1!A1 привязана к координате. Для нее важно, что нужное значение находится именно в ячейке A1. Если в исходной выгрузке добавится новая колонка, структура листа изменится, и формулы могут начать брать не те поля.

Power Query работает иначе: в запросах можно обращаться к именам столбцов. Поэтому для него важнее, как называется поле, а не в какой позиции оно находится.

Если в выгрузке 1С добавили новую колонку в середину, а названия уже используемых столбцов остались прежними, запрос может продолжить работу с нужными полями. Новая колонка не обязательно ломает всю цепочку преобразований: Power Query либо игнорирует ее, либо корректно учитывает в зависимости от настроек запроса.

Это не означает, что структура источника может меняться как угодно. Переименование нужного столбца или изменение его смысла все равно потребует проверки. Но исчезает один из самых неприятных классов ошибок - поломка отчета только из-за перемещения колонок.

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

Точка Б: что изменилось

После перехода на внешние данные, Power Query и подготовку прогнозов через Python результат был следующим:

• размер Excel-файла снизился с более чем 200 МБ до 40 МБ;
• процесс подготовки и обновления ускорился в 3-4 раза;
• исчезла необходимость постоянно искать ошибки, вызванные ручным копированием;
• изменение порядка столбцов в источнике перестало автоматически ломать расчетную часть;
• сложные прогнозы стали поступать в Excel уже в нормализованном виде.

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

Главное изменение произошло не в отдельных формулах и не в настройках форматирования. Изменилась сама роль Excel-файла. Он перестал быть складом всех исходных данных и стал рабочим интерфейсом над внешними источниками и моделью преобразований.

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