Как в СКД вычислить среднее значение в колонке, если есть пустые или нулевые значения?

← На главную

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

В этой статье мы подробно разберем, как заставить СКД игнорировать ненужные строки при расчете среднего, рассмотрим разницу между 0 и NULL, а также выясним, почему «среднее от среднего» — это методологическая ловушка. При работе с пустыми наборами данных в отчетах также будет полезен инструмент для автоматического скрытия колонок СКД при отсутствии данных.

Метод 1. Преобразование нулевых значений в NULL на уровне запроса

Проанализируем механику работы СКД: стандартная функция Среднее() в ресурсах учитывает 0, но игнорирует NULL. Это фундаментальное различие, которое мы можем использовать. Если в вашем наборе данных (запросе) поле отклонения или количества может принимать значение 0, и вы не хотите учитывать его в знаменателе среднего, нужно заменить его на NULL.

Рассмотрим пример кода в тексте запроса:


ВЫБРАТЬ
    Продажи.Номенклатура,
    ВЫБОР
        КОГДА Продажи.Количество = 0 ТОГДА NULL
        ИНАЧЕ Продажи.Количество
    КОНЕЦ КАК КоличествоДляРасчета
ИЗ
    РегистрНакопления.Продажи КАК Продажи

Используя конструкцию ВЫБОР, мы явно указываем системе, что отсутствие значения не должно считаться числом ноль. Теперь, если мы выберем это поле в качестве ресурса и установим выражение Среднее(КоличествоДляРасчета), СКД автоматически разделит сумму только на количество записей, содержащих реальные числа.

Метод 2. Использование функции ВычислитьВыражениеСГруппировкойМассив с отбором

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

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


Среднее(ВычислитьВыражениеСГруппировкойМассив("Среднее(Отклонение)", "Продукция", "Отклонение <> 0"))

В данном примере мы выполняем следующие действия:

Первый параметр "Среднее(Отклонение)" — это выражение, которое вычисляется для каждой группировки.
Второй параметр "Продукция" — это поле, в разрезе которого мы собираем массив.
Третий параметр "Отклонение <> 0" — это критически важный фильтр (отбор записей). Он говорит системе: «Не включай в массив те строки, где отклонение равно нулю».

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

Метод 3. Ручной расчет среднего через сумму и количество

Иногда стандартные функции ведут себя непредсказуемо, особенно в сложных отчетах с множеством группировок. В таких случаях мы рекомендуем «ручной» способ расчета, где вы сами контролируете и числитель, и знаменатель. Этот метод основан на том, что функция Количество() в СКД также игнорирует NULL.

Для реализации этого метода выполним следующие шаги:

Создадим вычисляемое поле, например, ПолеДляСчетчика.
В выражении этого поля напишем: Выбор Когда Отклонение = 0 Тогда NULL Иначе Отклонение Конец.
В ресурсах настроим итоговую формулу следующим образом:

Сумма(Отклонение) / Количество(ПолеДляСчетчика)

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

Метод 4. Математически верный подход — Средневзвешенное значение

Выясним важную деталь: часто под «средним отклонением» пользователи понимают не просто среднее арифметическое по строкам, а общее отклонение по всей массе данных. В сообщениях форума справедливо отмечается, что расчет «среднего от среднего» (как пытался сделать автор темы) может привести к искажению данных, если в одной строке план — 1 единица, а в другой — 1000.

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


ВЫБОР
    КОГДА Сумма(КоличествоПлан) = 0 ТОГДА 0
    ИНАЧЕ (Сумма(КоличествоФакт) - Сумма(КоличествоПлан)) / Сумма(КоличествоПлан) * 100
КОНЕЦ

Почему этот метод лучше?

Объективность: Он учитывает вес каждой позиции. Большое отклонение по мелкой позиции не «перекосит» общий итог отчета.
Отсутствие проблем с нулями: Если по какой-то продукции план был нулевым, условие ВЫБОР предотвратит ошибку деления на ноль.
Простота: Вам не нужно возиться с массивами и сложными отборами внутри СКД. Эта формула будет одинаково верно работать и в детальных записях, и в общих итогах.

Метод 5. Использование агрегатной функции Сумма() с условием

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

Пример формулы в поле «Выражение» на закладке «Ресурсы»:

Сумма(Отклонение) / Сумма(Выбор Когда Отклонение = 0 Тогда 0 Иначе 1 Конец)

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

Резюме и рекомендации

Выбор метода зависит от ваших целей:

Если нужно просто исключить пустые строки из стандартного расчета — используйте замену 0 на NULL в запросе.
Если вы работаете в сложной иерархии группировок — используйте ВычислитьВыражениеСГруппировкойМассив с фильтром.
Если вы строите управленческий отчет по выполнению плана — используйте формулу средневзвешенного отклонения (отношение сумм). Это избавит вас от методологических ошибок и сделает отчет профессиональным.

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