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

← На главную

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

Проблема некорректного расчета среднего значения

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

Допустим, у нас есть данные:

20 лет — 3 сотрудника
25 лет — 5 сотрудников
30 лет — 1 сотрудник

Если считать "в лоб" через СРЕДНЕЕ(Возраст), СКД возьмет уникальные значения возраста (20, 25, 30) и разделит их сумму на количество строк: (20 + 25 + 30) / 3 = 25. Однако фактически у нас 9 сотрудников, и их суммарный возраст составляет (20 * 3 + 25 * 5 + 30 * 1) = 215. Правильное среднее значение должно быть: 215 / 9 ≈ 23,88.

Разница в результатах очевидна. Чтобы получить верное значение, нам необходимо использовать формулу взвешенного среднего.

Решение 1: Использование формулы в ресурсах

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

Разберем по шагам:

Убедимся, что в запросе или в вычисляемых полях у нас есть поле Возраст и поле КоличествоСотрудников (в запросе это обычно СУММА(1)).
Перейдем на закладку Ресурсы.
Добавим новое поле в ресурсы (например, СреднийВозраст, если это вычисляемое поле, или само поле Возраст).
В колонке "Выражение" введем следующую конструкцию:

Сумма(Возраст * КоличествоСотрудников) / Сумма(КоличествоСотрудников)

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

Решение 2: Использование вычисляемых полей

Часто возникает вопрос: где именно писать логику — в запросе или в СКД? Рассмотрим вариант с вычисляемым полем. Если мы создадим вычисляемое поле и сразу напишем там СРЕДНЕЕ(Возраст), система может вернуть 0 или выдать ошибку, так как вычисляемые поля на базовом уровне рассчитываются для каждой записи отдельно, а агрегатные функции должны работать на уровне итогов.

Правильный алгоритм работы с вычисляемым полем:

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

Решение 3: Обработка деления на ноль

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


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

Исключение нулевых значений из расчета

Иногда поле Возраст может быть не заполнено (равно 0). Если мы включим эти нули в расчет среднего, результат будет сильно занижен. Чтобы исключить их, можно использовать два подхода:

Подход А: Преобразование в NULL в запросе. Агрегатные функции СКД автоматически игнорируют значения NULL, но учитывают 0. Мы можем изменить запрос следующим образом:


ВЫБРАТЬ
    Сотрудники.Должность,
    ВЫБОР
        КОГДА Сотрудники.Возраст = 0 ТОГДА NULL
        ИНАЧЕ Сотрудники.Возраст
    КОНЕЦ КАК Возраст,
    СУММА(1) КАК Количество
ИЗ
    Справочник.Сотрудники КАК Сотрудники
ГРУППИРОВАТЬ ПО
    Должность,
    Возраст

Подход Б: Фильтрация в выражении ресурса. Мы можем воспользоваться функцией ВычислитьВыражение с условием отбора:

Сумма(Возраст) / ВычислитьВыражение("Количество(Возраст)", , , "Возраст <> 0")

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

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

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

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

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