Как преобразовать выгрузку из 1С в плоскую таблицу

Выгрузки из 1С редко выглядят как готовая таблица: в них есть строки групп, промежуточные итоги, объединённые ячейки, пустые колонки и повторяющиеся шапки. Такой формат удобен для печати, но бесполезен для анализа — сводная таблица, фильтр или загрузка в BI-систему требуют «плоской» структуры, где каждая строка — одна запись, а каждый столбец — одно поле.

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

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

Что мешает в типовой выгрузке 1С

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

Вторая частая проблема — строки итогов: «Итого по группе», «Всего», промежуточные суммы. При импорте в аналитический инструмент они считаются обычными записями и удваивают суммы. Третья проблема — объединённые ячейки и многострочные шапки, которые ломают структуру столбцов.

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

Подготовка: в каком виде выгружать данные из 1С

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

Выгружайте в формат «Лист Excel» (xlsx), а не в «Табличный документ» (mxl) и не через копирование на печатную форму. При сохранении в xlsx 1С часто сохраняет структуру группировок, которую можно свернуть или разобрать. Если выгрузка идёт через «Файл → Сохранить копию», выбирайте именно лист Excel без оформления, когда такой вариант предлагается.

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

Ручная чистка выгрузки в Excel

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

Удаление групп, итогов и объединений

Сначала снимите объединение ячеек: выделите всю таблицу и отключите «Объединить ячейки». Затем удалите строки итогов — удобно отфильтровать колонку с наименованием по словам «Итого», «Всего» и удалить видимые строки. Если в файле есть группировка строк (плюсики слева), снимите её через «Данные → Разгруппировать».

Заполнение пустых ячеек значениями групп

Ключевой приём — заполнение вниз: выделите колонку с аналитикой, нажмите F5 → «Выделить» → «Пустые ячейки», затем введите формулу вида =A2 (ссылка на ячейку выше) и нажмите Ctrl+Enter. Все пустые ячейки заполнятся значениями из строк выше. После этого замените формулы значениями через «Копировать → Вставить как значения», иначе при удалении строк-заголовков групп данные сломаются.

1. 1
   Сохраните копию исходного файла и работайте в ней
2. 2
   Снимите объединение ячеек и группировку строк
3. 3
   Удалите строки итогов и повторяющиеся шапки через фильтр
4. 4
   Заполните пустые ячейки аналитики значением строки выше (F5 → пустые → =ссылка выше → Ctrl+Enter)
5. 5
   Замените формулы значениями через специальную вставку
6. 6
   Удалите строки-заголовки групп, которые больше не нужны
7. 7
   Проверьте: первая строка — заголовки, дальше только данные, без пустых строк

Автоматическая чистка через Power Query

Если выгрузка формируется регулярно, ручная чистка быстро надоедает и становится источником ошибок. Power Query (в Excel: «Данные → Получить данные → Из файла → Из книги Excel») позволяет один раз записать последовательность преобразований и потом обновлять результат одной кнопкой.

Типовой сценарий в редакторе Power Query: удалить верхние строки до шапки, повысить первую строку до заголовков, отфильтровать строки с итогами по условию «не содержит "Итого"», применить «Заполнить вниз» к колонкам с аналитикой, удалить лишние столбцы. Все шаги записываются в панели «Применённые шаги» и повторяются при обновлении.

Главное условие стабильности — выгрузка из 1С должна каждый раз иметь одинаковую структуру: те же столбцы в том же порядке и та же шапка. Если состав колонок плавает, запрос будет падать с ошибкой, и его придётся корректировать.

Кладите новые выгрузки в одну папку под одним и тем же именем файла (например, vigruska.xlsx), а Power Query настройте на этот путь. Тогда обновление сводится к замене файла и нажатию «Обновить» — путь менять не придётся.

Получение плоских данных напрямую из 1С

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

Во-первых, универсальный отчёт по регистру или справочнику с отключёнными группировками выводит данные списком. Во-вторых, консоль запросов (внешняя обработка) позволяет составить запрос к нужным таблицам и выгрузить результат в Excel или CSV — это даёт полностью плоскую структуру без единой лишней строки. В-третьих, в типовых конфигурациях многие списки поддерживают «Вывести список» с настраиваемыми колонками.

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

Сравнение способов

Ручная чистка в Excel

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

Power Query

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

Запрос или обработка в 1С

Даёт идеально плоский результат без постобработки; нужны права и навыки либо помощь разработчика

Проверка результата и типичные ошибки

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

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

Контрольные точки

Как проверить результат

Сумма по колонке сумм

Норма

Совпадает с итогом исходного отчёта 1С

Тревожный признак

Больше исходного — остались строки «Итого»; меньше — удалены данные

Первая строка

Норма

Только заголовки столбцов, без объединений

Тревожный признак

Многострочная шапка или пустые ячейки в заголовках

Колонки аналитики

Норма

Заполнены в каждой строке данных

Тревожный признак

Пустые ячейки внутри данных — не выполнено заполнение вниз

Сводная таблица по результату

Норма

Строится сразу, суммы сходятся

Тревожный признак

Поля дублируются, появляются строки «(пусто)»

Ограничения и когда нужна помощь разработчика

Самостоятельная чистка имеет пределы. Очень большие выгрузки (сотни тысяч строк) Excel обрабатывает медленно, а ручные операции на таких объёмах рискованны. Данные, собираемые из нескольких отчётов с разной структурой, тоже плохо поддаются ручной склейке.

Если выгрузка нужна по расписанию, содержит сложные соединения данных из разных регистров или должна уходить в другую систему в строгом формате — это задача для доработки 1С: обработки с регламентным заданием, выгрузки через запрос в CSV или обмена через HTTP. Такие решения делает разработчик, а попытка собрать их руками обходится дороже.

Частые вопросы

Можно ли получить плоскую таблицу без Excel?

Да. Выгрузка результата запроса в CSV через консоль запросов или обработку даёт плоский файл, который открывается в любой системе. Также многие BI-инструменты подключаются к 1С напрямую через COM, OData или выгрузки в базу данных — тогда промежуточный файл вообще не нужен.

Что делать, если в выгрузке несколько уровней группировки?

Обрабатывайте уровни по очереди: сначала заполните вниз колонку самой верхней группы, затем следующей и так далее до детальных строк. В Power Query для этого есть отдельная операция «Заполнить вниз» для каждой колонки. Строки-заголовки групп удаляйте только после того, как все уровни заполнены.

Почему после чистки даты и числа стали текстом?

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

Как быть, если структура выгрузки каждый раз разная?

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

Можно ли автоматизировать чистку макросом вместо Power Query?

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