Формулы Excel "СУММЕСЛИ" и "СЧЕТЕСЛИ" при расчете сезонности

Если мы хотим быстро, не задумываясь, без ошибок рассчитать сезонность для длинных временных рядов, то рекомендую использовать 2 формулы Excel — "СУММЕСЛИ" и "СЧЕТЕСЛИ". 

Часто временной ряд оказывается длинным - продажи по месяцам за 4 - 5 лет и больше, и еще задача может усложнится тем, что первый и последний год могут оказаться не полными, т.е. данные начинаются не с января, а заканчиваются не декабрем, а мы хотим быстро и правильно усреднить сезонные отклонения и получить сезонность. Для этого воспользуемся 2 формулами Excel - суммесли и счетесли.

Рассмотрим использование формул "СУММЕСЛИ" и "СЧЕТЕСЛИ"
на примере расчета прогноза с помощью модели "Скользящей средней к 3-м месяцам с аддитивной сезонностью".

Рассчитаем:

1. Скользящую среднюю к 3-м месяцам;
2. Разность между значениями ряда и средними значениями к 3-м месяцам (пункт 1);
3. Усредним разность ряда и средней для каждого месяца получим сезонность в абсолютной величание - для этого воспользуемся функциями СУММЕСЛИ и СЧЕТЕСЛИ;
4. Продлим значения ряда с помощью скользящей средней к 3-м месяцам и скорректируем её аддитивной сезонностью.

1. Скользящую среднюю к 3-м месяцам для каждого момента времени во временном ряду начиная с 4-го периода:

2.Разность между значениями ряда и средними значениями к 3-м месяцам для каждого момента времени t (пункт 1):

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

3. Усредним разность ряда и средней (пункт 2) для 12 месяцев - получим сезонность в абсолютной величание - аддитивную сезонность.

Для этого вначале выделим номера месяцев с помощью функции Excel =месяц(дата).

 

Чтобы воспользоваться функцией Excel =месяц(дата), проверяем, являются ли наши даты "январь 2010 г." датами, если нет, функция =месяц() выдаст ошибку.

Получаем ряд с пронумерованными месяцами от 1 до 12 для каждого момента времени t:

Далее с помощью функции Excel =СУММЕСЛИ суммируем для каждого месяца от 1 до 12 отклонения, которые мы считали в пункте 2.

Для этого в формулу СУММЕСЛИ передаем следующие значения:

=СУММЕСЛИ(

• диапазон - $G$8:$AY$8 - диапазон с номера месяцев за весь период;
• критерий - D9 - номер месяца для которого суммируем отклонения и рассчитываем сезоность;
• диапазон для суммирования - $G$7:$AY$7 - диапазон с отклонениями рассчитанными в пункте 2).

 

Фиксируем ссылки на диапазон с номерами месяцев и диапазон с отклонениями (как зафиксировать ссылки описано в статье "Как зафиксировать ссылку в Excel?"), и протягиваем формулу для 12 месяцев от 1 до 12.

Получаем сумму отклонений для каждого месяца.

Далее с помощью формулы Excel =СЧЁТЕСЛИ() считаем количество месяцев во временном ряду, на которое мы разделим сумму отклонений и получим среднее абсолютное отклонение для каждого месяца - аддитивную сезонность.

И так передаем в формулу =СЧЁТЕСЛИ(

• диапазон - $G$8:$AY$8 - фиксируем ссылку - номера месяцев во временном ряду;
• критерий - D9 - номера месяцев для сезонности, кол-во которых нам надо рассчитать во временном ряду для того чтобы рассчитать среднее отклонение.

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

Протягиваем формулу Excel =СЧЁТЕСЛИ от 1 до 12 и получаем ряд с количеством месяцев для каждого месяца сезонности:

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

Данный расчет мы проделали в 3 шага

1. Просуммировали отклонения для каждого месяца сезонности с помощью СУММЕСЛИ,
2. Рассчитали количество периодов для каждого месяца сезонности с помощью СЧЁТЕСЛИ,
3. И разделили пункт 1 на пункт 2, т.е. сумму отклонений на количество периодов, т.е. нашли среднее отклонение для каждого месяца.

Но эти 3 шага можно совместить в один и сразу получить 12 значений аддитивной сезонности.
Для этого СУММЕСЛИ разделим на СЧЁТЕСЛИ, как на картинке:

 

Формула получается следующего вида:

=СУММЕСЛИ($G$8:$AY$8;D9;$G$7:$AY$7)/СЧЁТЕСЛИ($G$8:$AY$8;D9)

Вводим формулу, протягиваем на 12 месяцев, получаем сезонность:

Еще раз её разберем =СУММЕСЛИ($G$8:$AY$8;D9;$G$7:$AY$7)/СЧЁТЕСЛИ($G$8:$AY$8;D9)

СУММЕСЛИ($G$8:$AY$8 (зафиксированный диапазон с номерами месяцев);D9 (номер месяца, для которого мы рассчитываем сезонность); $G$7:$AY$7 (зафиксированный диапазон со значениями разности между рядом и средней - пункт 2))

СЧЁТЕСЛИ($G$8:$AY$8 (зафиксированный диапазон с номерами месяцев);D9 (номер месяца, для которого мы рассчитываем сезонность))

Теперь давайте рассчитаем прогноз.

Для этого продлим значения ряда с помощью скользящей средней к 3-м месяцам и скорректируем её аддитивной сезонностью.

Продлим скользящую среднею к 3-м месяцам в будущее на 12 месяцев:

 

Скорректируем скользящую сезонностью.

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

Подробнее об этом читайте в статье "ГПР в Excel"

Прогноз = средние продажи за последние 3 месяца + сезонность:

=СРЗНАЧ(AW4:AY4 (средние продажи за 3 последних месяца))+ГПР(AZ3 (искомый номер месяца$D$9:$O$13 (зафиксированная ссылка на таблицу с сезонностью);5 (номер строки с сезонностью);0)

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

Очень часто сталкиваюсь с ошибками посетителей наших онлайн курсов по прогнозированию, когда усредняют отклонения воспользовавшись стандартной формулой СРЗНАЧ и одну из ссылок ставят на не соответствующий месяц, т.е. считаем средние отклонения января, а одна из ссылок оказывается на декабрь или февраль, в результате чего коэффициент сезонности получается не верным.

Для того чтобы этой ошибки сознательно избежать и заставить думать машину используйте формулы Excel =СУММЕСЛИ и СЧЁТЕСЛИ. Комбинация данных формул поможет вам рассчитать среднее значение по заданному критерию.

А если хотите на 100% избежать ошибок в расчетах прогнозов, да еще и автоматически подобрать нужную модель для каждого временного ряда работайте с Forecast4AC PRO.

Forecast4AC PRO сэкономит вам кучу времени позволит делать более точные прогнозы.

Если есть вопросы или комментарии, пожалуйста, пишите в форме ниже!

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

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