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

← На главную

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

Проанализируем классическую ошибку начинающих разработчиков. Если мы попытаемся использовать стандартную виртуальную таблицу ОстаткиИОбороты с периодичностью День, мы получим записи только за те дни, в которых были реальные движения (обороты) по регистру. Если по какому-то товару движения были только 1 и 10 числа, то в выборку попадут только эти две даты. Простое деление суммы остатков на количество записей в выборке или на разность дат в периоде даст в корне неверный результат, так как промежуточные дни с неизменным остатком просто не будут учтены.

Рассмотрим подробнее четыре эффективных метода решения этой проблемы: от встроенных возможностей Системы компоновки данных (СКД) до оптимизированных математических расчетов в чистом SQL-запросе 1С.

Метод 1. Нативный метод СКД: Настройка «Дополнения периодов»

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

Шаг 1. Подготовка запроса

В тексте запроса используем стандартную виртуальную таблицу остатков и оборотов с периодичностью День. Обязательно выбираем поле периода и конечного остатка:


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

Шаг 2. Настройка роли поля периода в СКД

Переходим на вкладку «Набор данных» в конструкторе схемы компоновки данных:

Для поля ПериодДень в колонке «Роль» открываем настройки.
Указываем тип роли — Период.
В поле «Имя» вводим уникальное имя периода (например, ПериодДень).
Обязательно устанавливаем флажки Обязательное и Игнорировать NULL.

Шаг 3. Настройка ресурса

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

Среднее(КонечныйОстаток)

Шаг 4. Включение дополнения в настройках отчета

Переходим на вкладку «Настройки» и выполняем следующие действия:

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

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

Метод 2. Запросный метод: Генерация дат через Производственный календарь

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

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

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

Посмотрим на пример практической реализации этого алгоритма в запросе:


ВЫБРАТЬ
	Календарь.Дата КАК ПериодДень
ПОМЕСТИТЬ ВТ_Календарь
ИЗ
	РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК Календарь
ГДЕ
	Календарь.Дата МЕЖДУ &НачалоПериода И &КонецПериода
;

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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	Календарь.ПериодДень КАК ПериодДень,
	ОстаткиДвижений.Номенклатура КАК Номенклатура,
	МАКСИМУМ(ОстаткиПрошлые.Период) КАК ПериодАктуальногоОстатка
ПОМЕСТИТЬ ВТ_СвязьПериодов
ИЗ
	ВТ_Календарь КАК Календарь
		ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ОстаткиДвижений КАК ОстаткиДвижений
		ПО ИСТИНА
		ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ОстаткиДвижений КАК ОстаткиПрошлые
		ПО ОстаткиДвижений.Номенклатура = ОстаткиПрошлые.Номенклатура
			И ОстаткиПрошлые.Период <= Календарь.ПериодДень
СГРУППИРОВАТЬ ПО
	Календарь.ПериодДень,
	ОстаткиДвижений.Номенклатура
;

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

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

Метод 3. Оптимизированный математический метод (без генерации пустых строк)

Предыдущий запрос имеет один существенный недостаток. Если в базе ведется учет по десяткам тысяч позиций номенклатуры, то перемножение справочника товаров на каждый день периода в виртуальной таблице ВТ_СвязьПериодов приведет к колоссальному росту объема временных таблиц в СУБД. Это может вызвать переполнение памяти или сильное снижение быстродействия.

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

Идея метода заключается в следующем:

Каждое значение остатка «живет» определенное количество дней — с момента его изменения до следующего изменения (или до конца периода отчета).
Мы можем рассчитать эту длительность в днях с помощью функции РАЗНОСТЬДАТ.
Формула среднедневного остатка преобразуется к виду: СреднийОстаток = Сумма(ЗначениеОстатка * КоличествоДнейЕгоАктивности) / ОбщееКоличествоДнейВПериоде.

Рассмотрим структуру запроса для реализации этого метода:


ВЫБРАТЬ
	ТоварыОстатки.Номенклатура КАК Номенклатура,
	ТоварыОстатки.Период КАК ПериодНачала,
	ТоварыОстатки.КонечныйОстаток КАК Остаток,
	МИНИМУМ(ЕСТЬNULL(ТоварыСледующие.Период, ДОБАВИТЬКДАТЕ(&КонецПериода, ДЕНЬ, 1))) КАК ПериодОкончания
ПОМЕСТИТЬ ВТ_Интервалы
ИЗ
	РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , ) КАК ТоварыОстатки
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , ) КАК ТоварыСледующие
		ПО ТоварыОстатки.Номенклатура = ТоварыСледующие.Номенклатура
			И ТоварыОстатки.Период < ТоварыСледующие.Период
СГРУППИРОВАТЬ ПО
	ТоварыОстатки.Номенклатура,
	ТоварыОстатки.Период,
	ТоварыОстатки.КонечныйОстаток
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	Интервалы.Номенклатура КАК Номенклатура,
	СУММА(Интервалы.Остаток * РАЗНОСТЬДАТ(Интервалы.ПериодНачала, Интервалы.ПериодОкончания, ДЕНЬ)) / (РАЗНОСТЬДАТ(&НачалоПериода, &КонецПериода, ДЕНЬ) + 1) КАК СреднедневнойОстаток
ИЗ
	ВТ_Интервалы КАК Интервалы
СГРУППИРОВАТЬ ПО
	Интервалы.Номенклатура

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

Метод 4. Финансовый подход: Средняя хронологическая

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

Формула выглядит следующим образом:

СреднийОстаток = ((ОстатокНач / 2) + Остаток1 + Остаток2 + ... + (ОстатокКон / 2)) / (N - 1)

где N — общее количество дат в периоде расчета.

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

Резюме

При выборе метода расчета руководствуйтесь архитектурой вашего решения:

Для простых и быстрых интерактивных отчетов используйте Метод 1 (Дополнение периодов в СКД).
Для небольших объемов данных вне СКД отлично подойдет классический Метод 2 (Производственный календарь).
Для высоконагруженных систем с миллионными остатками выбирайте математически оптимизированный Метод 3 (Интервальный расчет).