Отчеты и Аналитика · 12 мин
Как из 1С выгрузить в Excel с формулами
Способы выгрузить данные из 1С в Excel с сохранением формул: через макет, COM-соединение, ADO и обработки. Пошаговые инструкции, код, ошибки и FAQ.
Стандартная команда «Сохранить как» в 1С переносит в Excel только значения: числа, текст и даты. Формулы при этом теряются, потому что табличный документ 1С хранит результат вычисления, а не саму формулу. Чтобы в Excel попали именно формулы — например, =СУММ(B2:B15) или =C2*D2 — нужно использовать один из специальных приёмов: выгрузку через макет с формулами, запись напрямую в книгу Excel через COM-соединение или выгрузку в формате, где формулы задаются текстом.
Выбор способа зависит от задачи. Если нужно один раз отдать коллеге файл с расчётом, который он будет пересчитывать сам, — проще всего записать формулы через COM. Если выгрузка регулярная (ежедневный отчёт для руководства), имеет смысл один раз написать процедуру в расширении или внешней обработке. Если менять конфигурацию нельзя, подойдёт компромиссный вариант: выгрузить значения и добавить формулы макросом на стороне Excel.
Ниже разобраны четыре рабочих подхода с кодом, ограничениями каждого и проверкой результата. Примеры приведены для типовых конфигураций на управляемых формах (1С:Предприятие 8.3), но логика применима и к обычным формам.
Почему стандартное сохранение теряет формулы
Табличный документ 1С — это не электронная таблица в смысле Excel. В нём нет движка вычислений: каждая ячейка содержит либо текст, либо число, либо шаблон для заполнения. Когда отчёт СКД рассчитывает итог, в ячейку записывается уже готовое число. При сохранении в формат XLSX платформа переносит значения и оформление, но не логику расчёта.
Отсюда следует главное правило: формулу в Excel можно получить только если её кто-то туда запишет. Либо вы сами — программно при выгрузке, либо пользователь — вручную или макросом уже в Excel. Никакая настройка отчёта в 1С не превратит сумму, посчитанную СКД, в формулу СУММ() на листе.
Способ 1. Запись формул через COM-объект Excel
Самый надёжный способ получить «живые» формулы — создать книгу Excel программно и записать в ячейки свойство Formula. Код выполняется в общем модуле, внешней обработке или расширении.
Excel = Новый COMОбъект("Excel.Application");
Excel.Visible = Ложь;
Книга = Excel.Workbooks.Add();
Лист = Книга.Worksheets(1);
Строка = 2;
Пока Выборка.Следующий() Цикл
Лист.Cells(Строка, 1).Value = Выборка.Номенклатура;
Лист.Cells(Строка, 2).Value = Выборка.Количество;
Лист.Cells(Строка, 3).Value = Выборка.Цена;
// Формула записывается строкой на английском с запятой-разделителем
Лист.Cells(Строка, 4).Formula = "=B" + Строка + "*C" + Строка;
Строка = Строка + 1;
КонецЦикла;
Лист.Cells(Строка, 4).Formula = "=SUM(D2:D" + (Строка - 1) + ")";
Книга.SaveAs("D:\Отчеты\Реализация.xlsx");
Excel.Quit();
Обратите внимание на три момента. Во-первых, формулы в свойстве Formula пишутся на английском языке и с запятой как разделителем аргументов — независимо от региональных настроек. Если нужен русский синтаксис с точкой с запятой, используйте свойство FormulaLocal. Во-вторых, адреса ячеек формируются конкатенацией строк, поэтому переменные счётчики удобнее держать в числовом виде. В-третьих, после Excel.Quit() объектную переменную стоит обнулить, иначе процесс EXCEL.EXE может остаться висеть в памяти.
Способ 2. Макет табличного документа с последующей заменой
Если отчёт уже построен и выгружается как табличный документ, можно пойти от обратного: сначала сохранить значения в XLSX, затем открыть файл через COM и «дорисовать» формулы в нужные колонки. Это удобно, когда структура отчёта сложная (группировки, шапки), а формулы нужны только в нескольких итоговых колонках.
Порядок такой: сохраняете табличный документ методом Записать() с типом файла ТипФайлаТабличногоДокумента.XLSX, затем открываете файл через Excel.Workbooks.Open(), находите нужные ячейки по известным координатам или по поиску текста заголовка и записываете формулы. После сохранения файл содержит и данные, и пересчитываемые формулы.
- 1Сформируйте отчёт в табличный документ и запишите его в XLSX
- 2Откройте файл через COMОбъект Excel.Application
- 3Найдите колонки для формул по заголовкам в первой строке
- 4Запишите формулы через Cells(строка, колонка).Formula
- 5Сохраните книгу и закройте Excel с обнулением объекта
- 6Откройте файл вручную и проверьте пересчёт при изменении данных
Способ 3. Формирование XLSX без Excel на компьютере
Когда Excel не установлен (сервер Linux, регламентное задание на сервере Windows без Office), COM недоступен. В этом случае файл XLSX можно собрать как ZIP-архив с XML внутри: формат Office Open XML позволяет хранить формулу в элементе <f> внутри ячейки. Это трудоёмко вручную, но существуют готовые библиотеки и компоненты, формирующие XLSX из 1С без Excel.
Более простой компромисс — выгрузить данные в CSV или XLSX со значениями, а формулы хранить в отдельной колонке как текст-шаблон, который пользователь вставит в Excel. Подходит, когда получатель файла готов выполнить одно простое действие: скопировать столбец с текстом формул и заменить его значениями-формулами через «Текст по столбцам» или макрос.
Сравнение вариантов
Полноценные формулы, гибкая адресация. Требует установленный Excel и права на запуск COM; медленно на больших объёмах.
Быстрое формирование отчёта платформой, формулы добавляются точечно. Нужно знать координаты ячеек в готовом файле.
Работает на сервере без Office. Сложная реализация или зависимость от сторонних библиотек.
Ничего не меняется в 1С. Требует действий от пользователя и пересылки файла с макросом.
Производительность и большие объёмы
Построчная запись через COM — самое узкое место: каждый вызов Cells().Value — это отдельное межпроцессное обращение. На выгрузке 50 тысяч строк разница между «по ячейкам» и пакетной записью может составлять минуты против секунд. Для ускорения записывайте данные массивом в диапазон: Лист.Range("A2:C50001").Value = МассивДанных, где массив — двумерный SAFEARRAY, сформированный через Новый COMSafeArray.
Дополнительно отключайте на время выгрузки автопересчёт и обновление экрана: Excel.ScreenUpdating = False и Excel.Calculation = -4135 (ручной пересчёт). После записи всех формул верните автоматический пересчёт (-4105) и вызовите Книга.RefreshAll() или Excel.Calculate(), чтобы формулы посчитались до сохранения файла.
Если формулы ссылаются на ячейки того же листа, надёжнее сначала записать все значения массивом, а формулы — отдельным проходом. При смешанной записи построчно Excel пересчитывает лист после каждой ячейки, и на больших таблицах выгрузка замедляется в разы.
Типичные ошибки и их признаки
Самая частая ошибка — формула записана, но в ячейке виден текст =B2*C2 вместо результата. Причина: ячейка имела текстовый формат до записи формулы. Лечится установкой NumberFormat = "General" перед записью формулы. Вторая типичная ситуация — ошибка #ИМЯ? в Excel: формула записана русскими буквами (=СУММ(...)) в свойство Formula, которое понимает только английский синтаксис.
Третья группа проблем — зависшие процессы EXCEL.EXE в диспетчере задач после аварийного завершения кода. Они блокируют файлы и съедают память сервера. Профилактика — конструкция Попытка ... Исключение ... КонецПопытки с вызовом Excel.Quit() в обеих ветках и обнулением переменной.
Проверка результата перед передачей файла
Перед отправкой файла получателю откройте его в Excel и выполните три проверки. Первая: измените любое исходное значение в колонке данных — ячейка с формулой должна пересчитаться автоматически. Если значение не меняется, формула записана как текст или включён ручной пересчёт (проверьте Формулы → Параметры вычислений).
Вторая проверка: итоговые суммы, посчитанные формулами Excel, должны сойтись с итогами исходного отчёта 1С. Расхождение обычно означает, что в формулу попал не тот диапазон (например, захвачена строка заголовка или пропущена последняя строка данных). Третья: сохраните и переоткройте файл — формулы должны остаться формулами, а не превратиться в значения.
Как проверить результат
Формула пересчитывается автоматически
Значение не меняется — формула записана текстом или ручной пересчёт
Суммы совпадают до копейки
Расхождение — проверять диапазоны в формулах
Формулы на месте, видны в строке формул
В ячейках только значения — файл сохранён не в XLSX
EXCEL.EXE завершён
Процесс висит — доработать обработку исключений
Когда формулы не нужны: честная альтернатива
Перед тем как внедрять программную выгрузку, уточните у получателя файла, зачем ему формулы. Часто реальная потребность — не пересчёт, а проверка «откуда взялась цифра». Эту задачу решает расшифровка отчёта в самой 1С или выгрузка детальных данных отдельным листом: пользователь видит состав суммы без единой формулы.
Если файл нужен для загрузки в другую систему, формулы скорее вредны: многие загрузчики читают только значения и могут некорректно обработать ячейки с формулами. В таком случае выгружайте значения, а после COM-записи дополнительно замените формулы результатами через Range.Value = Range.Value.
Как выбрать действие
Частые вопросы
Можно ли выгрузить формулы из 1С без программирования?
Штатной кнопки «сохранить с формулами» в платформе нет. Без кода доступен только обходной путь: выгрузить значения, а формулы добавить в Excel вручную или макросом. Всё, что записывает формулы автоматически, — это программные механизмы: COM, внешние компоненты или библиотеки формирования XLSX.
Почему в ячейке виден текст формулы вместо результата?
Почти всегда причина в том, что у ячейки был текстовый формат до записи формулы. Установите формат «Общий» (NumberFormat = "General") и перезапишите формулу. Вторая возможная причина — в Excel включён режим «Показать формулы» (сочетание Ctrl+` переключает его).
Как записать формулу с русскими функциями, например СУММ?
Используйте свойство FormulaLocal вместо Formula — оно принимает локализованный синтаксис, включая русские имена функций и точку с запятой как разделитель. При чтении обратно учитывайте, что Formula вернёт английский вариант той же формулы.
Работает ли COM-способ на сервере 1С под Linux?
Нет. COM-объект Excel.Application требует Windows и установленного Microsoft Excel. На Linux-сервере формируйте XLSX программно как пакет Open XML (самостоятельно или через сторонние библиотеки) либо переносите генерацию файла на клиентское рабочее место с Excel.
Формулы посчитались в Excel, но итог не сходится с отчётом 1С. Где искать ошибку?
Проверьте границы диапазонов в формулах: типичны захват строки заголовка, пропуск последней строки данных и скрытые строки, которые СУММ игнорирует иначе, чем ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Также сверьте округление: 1С могла показывать округлённые значения, а в Excel считаются полные.
Можно ли выгружать формулы через регламентное задание?
Да, но с ограничением: на сервере Windows должен быть установлен Excel, а учётная запись службы агента 1С — иметь права на запуск COM. Это нестабильная схема (риск зависших процессов), поэтому для регламентных заданий предпочтительнее сборка XLSX без COM.
Что важно запомнить
1С хранит в табличном документе только значения, поэтому формулы в Excel появляются лишь при программной записи: через COM-свойство Formula (английский синтаксис) или FormulaLocal (русский). Для больших объёмов пишите данные массивом и отключайте пересчёт, для сервера без Excel — собирайте XLSX библиотекой. Всегда проверяйте пересчёт, сверку итогов и завершение процесса Excel.