1c формула в отчете

Обновлено: 03.07.2024

Отчетов таких была написана куча и на доработку каждого требовалось куча времени. Я захотел попробовать найти какой-нибудь новый подход - все формулы засунуть в макет. Но меня поджидал облом. С простыми формулами типа "=RC[-2]-RC[-1]" все было замечательно - внести как текст и все ок, но, когда потребовалось реализовать формулу чуть посложнее, типа "=СУММ(R[1]C:R[КоличествоСтрок]C)", то тут ждал облом. Тут сразу напрашивается шаблон, но как оказалось, шаблон никак не хотел выводить символ "[", т.к. для 1С это служебный символ, определяющий параметры для шаблона. Поиск в инете типа "как вывести квадратные скобки в шаблоне макета" ничего не дал, вообще ничего не дал! Путем проб и ошибок выяснилось, чтоб вывести "КакойТоТекст[1]" нужно задать "КакойТоТекст [ [1]". А вот чтоб задать "КакойТоТекст[1]ещё[ КоличествоСтрок ]", где КоличествоСтрок - это параметр, который нужно менять, то тут нужно извратиться по жёсткому - "КакойТоТекст [] [[1]ещё [] [[[ КоличествоСтрок ]]". Конструкция [] - нужна, чтоб не удалялся текст - "КакойТоТекст".

Итого: копируем формулу Excel, модифицируем её:

1. заменяем " ; " на " , "

2. к неизменным значениям в скобках добавляем спереди [] [

3. изменяемые значения в скобках обворачиваем [] [[изменяемое значение в скобках]. И макет готов.

И тогда весь код при формировании Excel будет минимальным:

Эксель = Новый COMОбъект("Excel.Application");
Эксель.Application.DisplayAlerts = False;
Эксель.Application.ReferenceStyle = "xlR1C1";
Эксель.Application.ErrorCheckingOptions.InconsistentFormula = Ложь;

ВсегоКолонок = ТабличныйДокумент.ШиринаТаблицы;
ВсегоСтрок = ТабличныйДокумент.ВысотаТаблицы;

МассивКом = Лист.Range(Лист.Cells(1, 1), Лист.Cells(ВсегоСтрок, ВсегоКолонок)).FormulaR1C1;
Для Ном = 1 По МассивКом.GetLength(0) Цикл
Для Ном2 = 1 По МассивКом.GetLength(1) Цикл
Значение = МассивКом.GetValue(Ном,Ном2);

Если НЕ Найти(Значение, "") = 0 Тогда
МассивКом.SetValue(Ном, Ном2, СтрЗаменить(Значение, "", ""));
КонецЕсли;

КонецЦикла;
КонецЦикла;
Лист.Range(Лист.Cells(1, 1), Лист.Cells(ВсегоСтрок, ВсегоКолонок)).FormulaR1C1 = МассивКом; //Если тут ошибка, то скорее всего косяк в формуле

Как вы будете использовать макет - это ваше дело, хотите формируйте отчет через СКД используя Макет, хотите выводите отчет алгоритмически. В приложенном файле, я использую алгоритмическое формирование табличного документа (через СКД не получилось получить КоличествоСтрок), а затем формирую Excel файл из него. Приложенный файл это шаблон для ознакомления с подходом, он будет запускаться в Бухгалтерии, УТ, в общем там где есть регистр сведений ЦеныНоменклатуры.

Конечно формула в таком виде "=КакойТоТекст [] [[1]ещё [] [[[ КоличествоСтрок ]]" мне не очень нравится. Если кто-нибудь знает, как можно представить формулу в более наглядном виде, был бы рад узнать.

Работа с вычисляемыми полями

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

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

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

Пример

В схеме компоновки данных определим набор данных - запрос:

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

Определим вычисляемое поле. Для этого перейдем в конструкторе схемы компоновки данных на вкладку Вычисляемые поля и добавим поле.

Назовем поле ПеревыполнениеПлана . В качестве выражения поля введем:

Таким образом, значением данного поля в случае, если значение поля СтоимостьПлан равно 0 , будет строка Не запланировано , и процент от перевыполнения плана в противном случае.

Для того чтобы в результат данное поле выводилось с точностью два знака, установим для поля в оформлении формат ЧДЦ=2 .

Определим настройки отчета, для этого добавим группировку - детальные записи (группировку без полей группировки) и в выбранные поля добавим поля Номенклатура , Подразделение , Стоимость план , Стоимость факт , Перевыполнение плана .

Результат отчета будет выглядеть так:

Т.е. для каждой строчки было рассчитано значение вычисляемого поля.

Для того чтобы по вычисляемому полю можно было рассчитывать итоги, добавим данное поле в список ресурсов. В конструкторе схемы компоновки это делается на вкладке Ресурсы . Выберем поле и добавим его в список ресурсов. Аналогичную операцию выполним и для полей СтоимостьПлан и СтоимостьФакт .

Теперь в итог отчета будет выводиться сумма по полям, в том числе и по полю ПеревыполнениеПлана :

Однако, простым суммированием, мы получили не вполне адекватный итог по перевыполнению плана. Мы получили сумму процентов по каждой комбинации Номенклатура / Подразделение .

Для того чтобы получить правильный итог по выполнению плана изменим формулу расчета итога по полю Выполнение плана :

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

Вычисление строк отчёта по формулам. Отчёт по статьям ДДС.

Использование парсера для вычисления строк отчёта

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

Основными преимуществами данного подхода являются:

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

Рассмотрим данную концепцию подробнее. У нас есть начальные данные – это, в данном случае, движения регистра бухгалтерии по статьям ДДС. Такие данные легко получить с помощью обычного запроса к регистру бухгалтерии. Единственное условие к начальным данным – это то, что они должны содержать детализированные суммы, которые можно собрать и из которых можно полностью построить итоговый отчёт по ДДС. Далее есть алгоритм построения отчёта в виде специальной алгоритмической таблицы, где указаны все формулы для вычисления. Берутся начальные данные, алгоритмическая таблица и на выходе получается таблица с результатом (в виде соответствий параметр – сумма).

Представленная ниже обработка 1С выполняет следующие действия для построения отчёта:

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

Структура отчёта

Регламентированный отчёт по статьям ДДС имеет следующую структуру (на рисунке приведена часть отчёта):

Определённому коду строки в отчёте соответствует сумма за соответствующий период формирования. Каждая сумма имеет свою расшифровку и свой алгоритм построения.

Программная реализация

Функция по вычислению итоговой таблицы называется «РасчётАлгоритмическойТаблицы». В обработке она выполняется со следующими параметрами:

Разные колонки алгоритмической таблицы выполняют разные роли и разбиваются в соответствии с этим на группы, что и указывается в структуре «СтруктураКолонок»:

КолонкаПоказатель – В этой колонке указывается идентификатор итоговой строки отчёта. В отчёте по ДДС этот показатель представлен номером строки 4110, 4111, 4112 и т.д.
КолонкиОтбор – Список колонок общий для алгоритмической таблицы и таблицы движений, которые выступают в качестве отбора. Для отчёта по ДДС такая колонка – это «Счёт». Если колонки для отбора не заданы, то предварительного отбора не выполняется, что ускоряет вычисление итоговой таблицы.
КолонкаИдентификатор – Общая для алгоритмической таблицы и таблицы движений колонка, выполняющая роль фиксированного идентификатора для формулы. В отчёте по ДДС такая колонка содержит кода статей ДДС, в привязке к которым вычисляются значения формул и суммируются полученные данные.
КолонкиСумма – Список колонок алгоритмической таблицы, в которых содержаться формулы вычисления значений. В отчёте по ДДС такая колонка с формулами называется «СуммаФормула».
КолонкиАТСумма – Список колонок, в которые сохраняется результат вычисления соответствующих колонок с формулами.
ОтборНаУсловие – Определяет применять ли результат отбора к вычислению формул по колонке «Условие». Если колонки отбора не заданы, то значение этого параметра не важно.

Обработка 1С

Обработка демонстрирует функционирование построения отчёта по ДДС (Отчёт о движении денежных средств). В окне обработки есть следующие вкладки:

Статьи ДДС – Таблица движений по статьям ДДС. Здесь есть счёт движения регистра бухгалтерии, статья ДДС, сумма оборот, сумма дебет и сумма кредит. В реальном отчёте ДДС таблицу движений легко получить с помощью запроса.
Алгоритм – Последовательность вычисления сумм по строкам отчёта ДДС. В алгоритме есть следующие основные столбцы: Идентификатор параметра, счёт, статья ДДС, сумма, условие. Столбцы «Счёт» и «Статья ДДС» выступают в качестве отбора по данной строке. В столбце «Сумма» задаётся формула вычисления итогового значения параметра. В столбце «Условие» задаётся условие включения или исключения данной строки в алгоритм расчёта.
Результат – Получаемый в итоге расчётов результат. Здесь есть колонки: группа (тот же «Идентификатор параметра» в алгоритме) и сумма.

Чтобы протестировать выполнение обработки 1С, делаем следующее:

1. Открываем обработку, на вкладке «Статьи ДДС» нажимаем кнопку «Получить движения». Движения по статьям ДДС для проверки алгоритма появятся ниже в табличном поле. Для тестирования можно изменить или добавить свои движения.

2. На вкладке «Алгоритм» представлена последовательность вычисления результата. Для тестирования можно поменять строки алгоритма, изменить формулы и условия выполнения.

3. Нажимаем кнопку «Выполнить» и переходим на вкладку «Результат», смотрим что получилось в результате вычислений.

Итого: копируем формулу Excel, модифицируем её:

1. заменяем " ; " на " , "

2. к неизменным значениям в скобках добавляем спереди [] [

3. изменяемые значения в скобках обворачиваем [] [[изменяемое значение в скобках]. И макет готов.

И тогда весь код при формировании Excel будет минимальным:

ВсегоКолонок = ТабличныйДокумент.ШиринаТаблицы;
ВсегоСтрок = ТабличныйДокумент.ВысотаТаблицы;

Если НЕ Найти(Значение, "") = 0 Тогда
МассивКом.SetValue(Ном, Ном2, СтрЗаменить(Значение, "", ""));
КонецЕсли;

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

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

1. Запускаем 1С в режиме конфигуратора, нажимаем "Файл" - "Новый" - "Внешний отчет":

2. В новом окне задаем Имя для отчета, нажимаем "Открыть схему компоновки данных" (ОСКД):

Находясь на вкладке "Наборы данных" нажимаем кнопку:

3. Теперь в окне "Конструктор запроса" на вкладке "Таблицы и поля" нужно выбрать поля из таблицы (регистр), которые будем использовать в создаваемом отчете:

Из 1 колонки мне понадобился Регистр сведений "флСостоянияСертификацииСерий", который я нашел в дереве "РегистрыСведений", затем встал на него курсором и нажал на кнопку ">", после этого он отобразился во 2-ой колонке с данными, весь регистр я добавлять не стал, а раскрыл ветвь с "флСостоянияСертификацииСерий" и поочередно выделяя нужные регистры добавлял их через кнопку ">", после чего они отображались в 3-ей колонке: с этими данными мы уже будем работать в дальнейшей в нашем отчете.

После того как вывели нужные поля, нажимаем "ОК"

4. Переходим на вкладку "Настроки" ОСКД, указываем Имя в шапке отчета, Группировку, выбираем поля, которые будут выводиться в отчете:

Здесь же на вкладке "Отбор" можно настроить фильтр.

5. Теперь в отчете будут выводиться данные из выбранных регистров, но следующей задачей является вывести вычисляемые поля (колонки) с условием и представлением данных. В моем случае, серия одобрена "Да" / "Нет": подсчитать кол-во каждого критерия.

Переходим на вкладку "Вычисляемые поля", добавляем 2-а поля: колонка "Путь к данным": придумываем название поля, которое потом укажем на вкладке "Настройки" - "Выбранные поля", чтобы вывести вычисляемые поля в отчет (пункт №6). Колонка "Выражение" - это формула, в ней я указываю выборку, что если партия одобрена = Истина, если не одобрена, то = Ложь - в соответствии с условием, в колонке будет устанавливаться Булево "Да" или "Нет" (поле СерияНоменклатурыфлПартияОдобрена должно быть добавлено в Набор данных (пункт №3)):

Колонка "Заголовок" название заголовка в колонке отчета.

6. Чтобы подсчитать количество "Да" / "Нет" в группировке, нужно зайти на вкладку "Ресурсы" и указать поля, которые будем обрабатывать:

7. Теперь остается указать вычисляемые поля на вкладке "Настройки" - "Выбранные поля" чтобы вывести их в отчет:

8. Сохраняем отчет с расширением *.erf через "Файл" - "Сохранить". Теперь данный отчет можно открыть в 1С в пользовательском режиме "Предприятие", через "Файл" - "Открыть". Выше приведенные настройки сформировались в отчете в следующем виде:

Помимо выводимых данных из регистра сведений, в 2-ух последних колонках мы подсчитываем количество "Да" / "Нет"

Читайте также: