Как рассчитать среднее значение в СКД 1С, если за некоторые периоды не было движений?

Рассмотрим одну из наиболее распространенных и коварных проблем, с которой сталкиваются разработчики при проектировании отчетов на Системе компоновки данных (СКД) в «1С:Предприятие». Речь идет о расчете среднепериодных показателей (например, среднемесячных продаж или возвратов), когда по определенным аналитикам (номенклатуре, контрагентам, складам) отсутствуют движения в некоторых интервалах времени.

Проанализируем ситуацию: отчет строится за три месяца (январь, февраль, март). Товар активно продавался в январе и марте, а в феврале отгрузок не было вообще. Если мы попытаемся рассчитать среднемесячные продажи стандартным делением общей суммы на число уникальных месяцев через функцию Количество(Различные МесяцРегистратора), СКД вернет для этого товара делитель, равный 2, а не 3. В результате итоговый средний показатель окажется существенно завышенным, так как нулевой месяц просто выпал из выборки информационной базы.

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

Способ 1. Расчет делителя через разность дат в параметрах СКД

Этот способ является самым простым, надежным и наименее требовательным к ресурсам системы. Если в настройках нашего отчета жестко заданы параметры начала и конца периода (например, &НачалоПериода и &КонецПериода), мы можем полностью отказаться от динамического подсчета количества месяцев по записям регистра. Вместо этого мы вычислим точное количество периодов математически прямо в выражении ресурса СКД.

Разберем по шагам, как это реализовать:

1. Перейдем на вкладку «Ресурсы» в схеме компоновки данных.
2. Найдем поле, для которого необходимо рассчитать среднемесячный показатель (или создадим для этого вычисляемое поле, например, СредниеПродажи).
3. В колонке «Выражение» напишем формулу, использующую встроенную функцию СКД РазностьДат.

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

Выбор
    Когда (РазностьДат(&НачалоПериода, &КонецПериода, "МЕСЯЦ") + 1) > 0
    Тогда Сумма(Количество) / (РазностьДат(&НачалоПериода, &КонецПериода, "МЕСЯЦ") + 1)
    Иначе 0
Конец

Проанализируем эту формулу. Функция РазностьДат с параметром "МЕСЯЦ" возвращает разницу между граничными датами. Мы прибавляем 1, чтобы учесть текущий месяц (например, разность между 01.01.2023 и 31.03.2023 составит 2 месяца, а физически это 3 календарных периода). Проверка через оператор Выбор защищает систему от критической ошибки деления на ноль, если пользователь сформирует отчет без указания дат.

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

Выбор
    Когда (РазностьДат(&НачалоПериода, &КонецПериода, "ДЕНЬ") + 1) > 0
    Тогда Сумма(Количество) / (РазностьДат(&НачалоПериода, &КонецПериода, "ДЕНЬ") + 1)
    Иначе 0
Конец

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

Способ 2. Настройка механизма «Дополнение периодов» в СКД

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

Этот механизм заставляет систему компоновки искусственно генерировать записи (строки или колонки) для недостающих дат в разрезе каждой группировки. Таким образом, в выборку попадают все периоды.

Рассмотрим подробнее процесс настройки дополнения периодов:

1. Перейдем на вкладку «Настройки» в СКД и выберем структуру нашего отчета.
2. В структуре найдем группировку по полю периода (например, это может быть поле МесяцРегистратора или ПериодМесяц).
3. Выделим эту группировку и в правой части экрана в свойствах найдем настройки дополнения.
4. Установим свойство «Тип дополнения» в значение Месяц (или День, в зависимости от шага вашего отчета).
5. Зададим границы дополнения. В качестве начальной даты укажем параметр &НачалоПериода, а в качестве конечной — &КонецПериода.

Теперь СКД принудительно добавит пустые записи для каждого товара по всем месяцам выбранного интервала. В ресурсах отчета мы можем смело использовать классическое выражение:

Сумма(Количество) / Количество(Различные МесяцРегистратора)

Благодаря автодополнению, функция Количество(Различные МесяцРегистратора) увидит все сгенерированные периоды (даже те, где продажи были нулевыми), и среднее значение рассчитается абсолютно корректно.

Способ 3. Использование функции «ВычислитьВыражение» в ресурсах

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

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

Укажем в выражении ресурса следующую конструкцию:

Сумма(Количество) / ВычислитьВыражение("Количество(Различные МесяцРегистратора)", , "ОбщийИтог")

Давайте детально проанализируем параметры этой функции:

1. Первый параметр — "Количество(Различные МесяцРегистратора)". Он обязательно должен быть передан в виде строки в двойных кавычках. Это выражение, которое будет вычисляться.
2. Второй параметр — группировка. Мы оставляем его пустым (просто ставим запятую), чтобы расчет производился в контексте всей результирующей таблицы.
3. Третий параметр — область вычисления. Мы передаем строковое значение "ОбщийИтог". Это указывает СКД, что функцию подсчета уникальных месяцев нужно выполнить глобально по всему отчету, проигнорировав отборы конкретной строки с товаром.

В результате, даже если конкретный товар продавался только в марте, делитель для него будет взят из общего итога всего отчета (где зафиксированы, например, январь, февраль и март). То есть сумма его продаж разделится на 3.

Важное примечание: если за какой-то месяц во всей компании вообще не было ни одной продажи ни по одному товару, этот месяц не попадет в выборку запроса, и ВычислитьВыражение его не учтет. Если такой сценарий возможен, лучше использовать Способ 1 или Способ 4.

Способ 4. Левое соединение с календарем на уровне запроса

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

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

Рассмотрим пример построения такого запроса в конструкторе:

ВЫБРАТЬ
    Календарь.Период КАК ПериодМесяц,
    Продажи.Номенклатура КАК Номенклатура,
    ЕСТЬNULL(Продажи.Количество, 0) КАК Количество
ИЗ
    (ВЫБРАТЬ РАЗЛИЧНЫЕ
        НАЧАЛОПЕРИОДА(РегламентированныйПроизводственныйКалендарь.Дата, МЕСЯЦ) КАК Период
    ИЗ
        РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК РегламентированныйПроизводственныйКалендарь
    ГДЕ
        РегламентированныйПроизводственныйКалендарь.Дата МЕЖДУ &НачалоПериода И &КонецПериода) КАК Календарь
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, Авто, ) КАК Продажи
        ПО НАЧАЛОПЕРИОДА(Продажи.Период, МЕСЯЦ) = Календарь.Период

Проанализируем этот запрос. Мы выбрали все уникальные начала месяцев из регистра РегламентированныйПроизводственныйКалендарь за указанный интервал. Затем мы связали их левым соединением с реальными оборотами продаж.

Если продаж по какому-то товару в конкретном месяце не было, поле Номенклатура примет значение NULL. Чтобы избежать этой проблемы при последующей группировке, часто сначала формируют декартово произведение (кросс-соединение) уникальных товаров и всех месяцев периода, а уже к этой сетке левым соединением привязывают продажи.

Посмотрим на пример такого более продвинутого запроса:

ВЫБРАТЬ
    Товары.Ссылка КАК Номенклатура,
    Календарь.Период КАК ПериодМесяц
ПОМЕСТИТЬ СеткаПериодов
ИЗ
    Справочник.Номенклатура КАК Товары
    КРОСС СОЕДИНЕНИЕ (
        ВЫБРАТЬ РАЗЛИЧНЫЕ
            НАЧАЛОПЕРИОДА(РегламентированныйПроизводственныйКалендарь.Дата, МЕСЯЦ) КАК Период
        ИЗ
            РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК РегламентированныйПроизводственныйКалендарь
        ГДЕ
            РегламентированныйПроизводственныйКалендарь.Дата МЕЖДУ &НачалоПериода И &КонецПериода
    ) КАК Календарь
;

ВЫБРАТЬ
    Сетка.Номенклатура КАК Номенклатура,
    Сетка.ПериодМесяц КАК ПериодМесяц,
    ЕСТЬNULL(Продажи.КоличествоОборот, 0) КАК Количество
ИЗ
    СеткаПериодов КАК Сетка
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, Месяц, ) КАК Продажи
        ПО Сетка.Номенклатура = Продажи.Номенклатура
        И Сетка.ПериодМесяц = Продажи.ПериодМесяц

Этот подход гарантирует, что в результирующей таблице для каждого товара будет ровно столько строк, сколько месяцев выбрано в периоде отчета. В СКД нам останется лишь настроить простейший ресурс:

Сумма(Количество) / Количество(Различные ПериодМесяц)

Этот способ является самым гибким и профессиональным, так как он готовит «чистые» данные еще на уровне СУБД, облегчая работу компоновщика и полностью исключая любые неоднозначности при расчете итогов.