Как получить средние значения остатков на каждый день в 1С 7.7 для расчета оборачиваемости склада

← На главную

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

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

Выбор математической модели: Среднее арифметическое против средней хронологической

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

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

Средний остаток = (1/2 Остатка на начало + Остаток на день 2 + Остаток на день 3 + ... + 1/2 Остатка на конец) / (Количество дней - 1)

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

Метод 1: «Черный запрос» с группировкой по дням и заполнение пропущенных дней

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

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


// Создаем объект Запрос
Запрос = СоздатьОбъект("Запрос");
ТекстЗапроса = 
"//{{ЗАПРОС(ЕжедневныеОстатки)
|Период с ДатаНачала по ДатаКонца;
|Товар = Регистр.ОстаткиТоваров.Товар;
|Количество = Регистр.ОстаткиТоваров.Количество;
|Функция КонОст = КонОст(Количество);
|Группировка Товар без групп;
|Группировка День;
|Условие(Товар в СписокВыбранныхТоваров);
|//}}ЗАПРОС";

Если Запрос.Выполнить(ТекстЗапроса) = 0 Тогда
    Возврат;
КонецЕсли;

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

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


// Создаем результирующую Таблицу Значений
ТабОстатков = СоздатьОбъект("ТаблицаЗначений");
ТабОстатков.НоваяКолонка("Товар", "Справочник.Номенклатура");
ТабОстатков.НоваяКолонка("Дата", "Дата");
ТабОстатков.НоваяКолонка("Остаток", "Число", 15, 3);

Пока Запрос.Группировка(1) = 1 Цикл // Группировка по Товару
    ТекущийТовар = Запрос.Товар;
    
    // Инициализируем дату начала периода
    ВременнаяДата = ДатаНачала;
    ТекущийОстаток = 0;
    
    Пока ВременнаяДата <= ДатаКонца Цикл
        // Пытаемся позиционироваться на нужный день в группировке запроса
        Если Запрос.Получить(1, ВременнаяДата) = 1 Тогда
            ТекущийОстаток = Запрос.КонОст;
        КонецЕсли;
        
        // Записываем остаток в нашу таблицу (даже если движения не было, 
        // остаток переносится с предыдущего дня)
        ТабОстатков.НоваяСтрока();
        ТабОстатков.Товар = ТекущийТовар;
        ТабОстатков.Дата = ВременнаяДата;
        ТабОстатков.Остаток = ТекущийОстаток;
        
        ВременнаяДата = ВременнаяДата + 1; // Переходим к следующему дню
    КонецЦикла;
КонецЦикла;

Этот подход работает значительно быстрее, чем запуск отдельного запроса или метода СводныйОстаток() на каждый день в цикле. Мы переносим всю тяжелую работу по выборке данных на СУБД (или внутренний движок DBF), а «развертывание» по дням делаем в оперативной памяти.

Метод 2: Оптимизированный «Метод приращений» (без тяжелых запросов)

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

Вместо того чтобы просить базу данных рассчитывать остатки на каждый день, мы можем:

Получить остатки на самое начало анализируемого периода (одним быстрым методом ВыгрузитьИтоги() или простым запросом без группировки по времени).
Получить все движения (обороты) по товарам за весь период (приходы и расходы) с детализацией до дня.
Последовательно рассчитать остатки в памяти, проходя циклом от начала периода к концу. Остаток текущего дня будет равен: ОстатокВчера + ПриходСегодня - РасходСегодня.

Давайте посмотрим на пример реализации метода приращений:


// Шаг 1. Получаем начальные остатки на НачалоПериода
Рег = СоздатьОбъект("Регистр.ОстаткиТоваров");
ТЗ_НачальныеОстатки = СоздатьОбъект("ТаблицаЗначений");
Рег.ВыгрузитьИтоги(ТЗ_НачальныеОстатки, , ); // Выгружаем остатки на Точку Актуальности (ТА)
// Если ТА позже ДатыНачала, предварительно используем Рег.ВременныйРасчет(1) на НачалоПериода

// Шаг 2. Получаем обороты за период в Таблицу Значений
ЗапросОбороты = СоздатьОбъект("Запрос");
ТекстОборотов = "
|Период с ДатаНачала по ДатаКонца;
|Товар = Регистр.ОстаткиТоваров.Товар;
|ДатаДок = Регистр.ОстаткиТоваров.ТекущийДокумент.ДатаДок;
|Количество = Регистр.ОстаткиТоваров.Количество;
|Функция ПриходКол = Приход(Количество);
|Функция РасходКол = Расход(Количество);
|Группировка Товар без групп;
|Группировка ДатаДок;
|";
ЗапросОбороты.Выполнить(ТекстОборотов);

// Шаг 3. Расчет в памяти
// Проходя по таблице календаря, мы просто складываем приращения к начальному остатку.
// Этот алгоритм выполняется мгновенно, так как не производит никаких обращений к диску!

Решение проблемы фильтрации по реквизиту партии (Документа)

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

Попытка обратиться к реквизиту документа-партии через точку в черном запросе (например, |Условие(Партия.ЯвляетсяОбразцом = 0);) в 1С 7.7 приводит к катастрофическому падению производительности. На СУБД DBF это вызывает построчный перебор файлов, а на SQL порождает тяжелые вложенные подзапросы (subqueries). Рассмотрим два пути решения этой проблемы:

Вариант А: Предварительный отбор в Список Значений

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


// Сначала собираем "правильные" партии
СписокПравильныхПартий = СоздатьОбъект("СписокЗначений");
ВыборкаПартий = СоздатьОбъект("Документ.ПоступлениеТоваров"); // Предположим, это тип документа-партии
ВыборкаПартий.ВыбратьДокументы(ДатаНачала - 365, ДатаКонца); // Ищем партии за последний год
Пока ВыборкаПартий.ПолучитьДокумент() = 1 Цикл
    Если ВыборкаПартий.ЭтоОбразец = 0 Тогда
        СписокПравильныхПартий.ДобавитьЗначение(ВыборкаПартий.ТекущийДокумент());
    КонецЕсли;
КонецЦикла;

// Теперь передаем этот список в запрос по регистру остатков
// В тексте запроса пишем:
// |Партия = Регистр.ОстаткиТоваров.Партия;
// |Условие(Партия в СписокПравильныхПартий);

Вариант Б: Прямые SQL-запросы через 1С++

Если ваша база данных работает под управлением Microsoft SQL Server, лучшим решением для оптимизации будет использование внешней компоненты 1С++. С помощью прямых SQL-запросов можно связать таблицу остатков регистра с таблицей шапки или табличной части документа-партии через оператор INNER JOIN. На уровне SQL-сервера такая операция займет доли секунды, вернув готовые ежедневные остатки в обход ограничений штатного движка 1С 7.7.

Учет нулевых остатков и анализ «Out-of-stock»

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

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

Помимо расчета оборачиваемости, на основе полученной таблицы ежедневных остатков полезно вычислять показатель Out-of-stock (индекс дефицита) — количество дней за период, когда остаток товара на складе был равен нулю. Это позволит руководству компании увидеть упущенную выгоду: если оборачиваемость товара высокая, но при этом индекс Out-of-stock составляет 50%, это явный сигнал о том, что товар приносил бы в два раза больше прибыли, если бы отдел закупок своевременно пополнял складские запасы.