ВПР в Excel на примере скользящей средней

Цель данной статьи - показать, как работает функция ВПР в Excel. Использование ВПР мы рассмотрим на примере модификации модели прогноза по методу скользящей средней к 3-м месяцам.

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

Сезонность по товарной группе мы подтянем к средним продажам по позиции с помощью стандартной функции Excel ВПР.

Рассмотрим ВПР в подробностях.

По ходу статьи мы:

1. Рассчитаем коэффициенты сезонности к 3-м месяцам по товарной группе;
2. Рассчитаем скользящую среднюю к 3-м месяцам по позициям;
3. Скорректируем скользящую среднюю сезонностью по группе. Коэффициенты сезонности подтянем с помощью ВПР и разберем функция по частям.

1. Рассчитаем коэффициенты сезонности к 3-м месяцам по товарной группе;

Рассчитаем коэффициенты сезонности к 3-м месяцам по товарной группе 1 и 2 с помощью Forecast4AC PRO (Как самостоятельно рассчитать коэффициенты сезонности к 3-м месяцам можете прочитать в статье "Расчет прогноза по методу скользящей средней!")

Скачайте файл с примером

Для этого установим курсор в начало продаж по товарным группам:

Выберите в настройках "Сезонность" "к 3-м месяцам":

Нажимаем кнопку "Рассчитать". Получаем в продолжении ряда коэффициенты сезонности к 3-м месяцам:

Копируем сезонность на отдельный лист "к 3-м" получаем табличку, в которой в первом столбце названия товарных групп, а в столбцах со 2-го по 13-й - коэффициенты сезонности для 1 - 12 месяцев:

2. Рассчитаем скользящую среднюю к 3-м месяцам по позициям.

Используем стандартную функцию =срзнач(продажи за 3 последних месяца):

Протянем среднюю на все позиции на 24 месяца вперед:

Скачайте файл с примером

3. Скорректируем скользящую среднюю сезонностью по группе и разберем ВПР.

Теперь средние продажи умножим на коэффициент сезонности по товарной группе, который подтянем с помощью функции ВПР.

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

1. В искомое значение передаем название товарной группы и фиксируем столбец:

=СРЗНАЧ(BD3:BF3)*впр($C3 (передаем название товарной группы и фиксируем столбец с помощью значка "$");'к 3-м'!$A$3:$M$4;данные!BG$2+1;0)

Подробнее о фиксировании ссылок читайте в статье "Как зафиксировать ссылку в Excel".

2. В таблицу передаем таблицу с коэффициентами сезонности для товарных групп и фиксируем таблицу:

=СРЗНАЧ(BD3:BF3)*впр($C3;'к 3-м'!$A$3:$M$4(передаем таблицу с товарными группами и фиксируем таблицу с помощью значка "$");данные!BG$2+1;0)

В первом столбце таблицы содержатся искомые значения - названия товарных групп. Фиксируем таблицу, чтобы формула имела такой вид 'к 3-м'!$A$3:$M$4 и ссылки не поехали, когда мы будем протягивать формулу.

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

=СРЗНАЧ(BD3:BF3)*впр($C3;'к 3-м'!$A$3:$M$4;данные!BG$2+1(передаем номер столбца в котором содержится искомый коэффициент сезонности для соответствующего месяца и фиксируем строку с номерами столбцов месяца  с помощью значка "$");0)

 

Т.к. номер столбца в таблице с сезонностью для первого месяца будет вторым, то прибавляем "1"

=СРЗНАЧ(BD3:BF3)*впр($C3;'к 3-м'!$A$3:$M$4;данные!BG$2+1(прибавляем 1, т.к. номер столбца в таблице с сезонностью для первого месяца 2, в первом столбце название товарных групп);0)

4. =СРЗНАЧ(BD3:BF3)*впр($C3;'к 3-м'!$A$3:$M$4;данные!BG$2+1;0 (ищем точное соответствие названий товарных групп))

Протягиваем полученную формулу, получаем средние продажи за 3 предыдущие месяца по товарной позиции скорректированные сезонностью по товарной группе к 3-м месяцам:

=СРЗНАЧ(BD3:BF3)*ВПР($C3;'к 3-м'!$A$3:$M$4;данные!BG$2+1;0)

Получаем расчет прогноза по методу скользящей средней к 3-м месяцам по товарным позициям, используя сезонность по товарной группе.

Скачайте файл с примером 

Данный подход может значительно увеличить точность расчета прогноза по товарным позициям внутри группы. Попробуйте рассчитать прогноз по методу скользящей средней к 2-м и 4-м месяцам, используя функцию ВПР и Forecast4AC PRO на текущий год, и сравните прогнозы с фактическими продажами. Выберите модель, которая была максимально близка к факту.

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

Точных вам прогнозов!

Статья полезная? Поделитесь с друзьями