Как в 1С программно задать формулу Excel через числовые номера строк и столбцов?

← На главную

При программном формировании отчетов в MS Excel через COM-соединение из 1С разработчики часто сталкиваются с задачей установки формул в ячейки (например, суммы по диапазону строк). Если в коде координаты ячеек представлены в виде чисел — номер строки и номер колонки — запись формулы в стандартном стиле A1 (например, =СУММ(B4:B64)) требует ручного перевода номера колонки в букву. Попытка передать конструкцию вида СУММ(АДРЕС(4;2):АДРЕС(64;2)) напрямую приводит к синтаксической ошибке Excel.

Разберем основные и наиболее эффективные способы решения этой задачи.

Способ 1. Запись формулы через свойство FormulaR1C1 (Рекомендуемый)

В объектной модели Excel для ячейки предусмотрено специальное свойство FormulaR1C1, которое принимает координаты в формате R[строка]C[колонка] (Row/Column). Главное преимущество этого способа — независимо от того, в каком стиле пользователь привык видеть файл (A1 или R1C1), Excel сам отобразит формулу в стандартном для пользователя виде.

Проанализируем два варианта адресации:

Абсолютная адресация (фиксированные координаты): координаты задаются числами без скобок. Например, R4C2:R64C2 соответствует диапазону $B$4:$B$64.
Относительная адресация (смещение относительно текущей ячейки): координаты указываются в квадратных скобках. Например, R[-1]C[-2] означает «на одну строку выше и на две колонки левее».

Посмотрим на пример установки формулы суммы через COM-объект в 1С:


// Создаем объект Excel
ExcelПриложение = Новый COMОбъект("Excel.Application");
Книга = ExcelПриложение.Workbooks.Add();
Лист = Книга.Worksheets(1);

// Номера строк и колонки
НомерСтрокиНачала = 4;
НомерСтрокиКонца = 64;
НомерКолонки = 2;

// Целевая ячейка для итога (строка 65, колонка 2)
ЯчейкаИтога = Лист.Cells(65, НомерКолонки);

// Задаем формулу в абсолютном формате R1C1
// Внимание: для свойства FormulaR1C1 используются английские имена функций и запятая в качестве разделителя!
ЯчейкаИтога.FormulaR1C1 = "=SUM(R" + НомерСтрокиНачала + "C" + НомерКолонки + ":R" + НомерСтрокиКонца + "C" + НомерКолонки + ")";

Важный нюанс локализации: если вы используете русскоязычные имена функций (например, =СУММ(...)) или точку с запятой в качестве разделителя аргументов, используйте свойство FormulaR1C1Local вместо FormulaR1C1:


ЯчейкаИтога.FormulaR1C1Local = "=СУММ(R" + НомерСтрокиНачала + "C" + НомерКолонки + ":R" + НомерСтрокиКонца + "C" + НомерКолонки + ")";

Способ 2. Получение буквенного диапазона через метод Address

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

Метод Address(RowAbsolute, ColumnAbsolute) позволяет получить строковый адрес любой ячейки или диапазона без знаков абсолютной адресации $, если передать параметры Ложь, Ложь (или 0, 0):


// Получаем диапазон по числовым координатам
Диапазон = Лист.Range(Лист.Cells(4, 2), Лист.Cells(64, 2));

// Метод вернет строку "B4:B64"
АдресДиапазона = Диапазон.Address(Ложь, Ложь);

// Устанавливаем стандартную формулу
Лист.Cells(65, 2).FormulaLocal = "=СУММ(" + АдресДиапазона + ")";

Способ 3. Вычисление адреса функциями Excel (ДВССЫЛ и ИНДЕКС)

Если по каким-то причинам требуется передать формулу, которая динамически строит ссылки внутри самого Excel, обычная склейка АДРЕС() не сработает без функции разыменования. Для этого используется ДВССЫЛ (в английской версии INDIRECT):


// Вариант через ДВССЫЛ и АДРЕС
ЯчейкаИтога.FormulaLocal = "=СУММ(ДВССЫЛ(АДРЕС(4;2)):ДВССЫЛ(АДРЕС(64;2)))";

Обратите внимание: функция ДВССЫЛ является «летучей» (volatile). Это означает, что Excel будет пересчитывать эту формулу при любом изменении на листе, что может вызвать существенное замедление работы при больших объемах данных. Если необходимо формировать динамический диапазон формулой Excel, гораздо эффективнее использовать нелетучую функцию ИНДЕКС (INDEX):


// Диапазон строится от 4-й до 64-й строки колонки B
ЯчейкаИтога.FormulaLocal = "=СУММ(ИНДЕКС(B:B; 4):ИНДЕКС(B:B; 64))";

Способ 4. Формирование файлов через ТабличныйДокумент (без COM-объекта)

Вызовы COM-объекта Excel работают медленно и требуют установленного пакета MS Office (что невозможно на серверах под управлением Linux). В 1С платформе есть встроенная возможность установить формулу ячейки через ТабличныйДокумент и затем сохранить результат в формате .xlsx.

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


ТабДок = Новый ТабличныйДокумент;

// Заполняем данные...
// Устанавливаем формулу в ячейку (строка 65, колонка 2)
ОбластьИтога = ТабДок.Область(65, 2, 65, 2);
ОбластьИтога.Формула = "СУММ(R4C2:R64C2)";

// Сохраняем в файл XLSX напрямую
ТабДок.Записать("C:\Reports\Report.xlsx", ТипФайлаТабличногоДокумента.XLSX);

При открытии полученного файла в Excel платформа 1С автоматически преобразует ссылки в нужный формат отображения пользователя.