Функция ГПР в Excel

Цель данной статьи - показать, как работает функция Excel ГПР. Рассмотрим работу функций на примере расчета прогноза с логарифмическим трендом и сезонностью.

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

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

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

Скачайте файл с расчетными данными для примера здесь

Выделим из объема продаж по месяцам значения логарифмического тренда и продлим тренд в будущее.

Для этого воспользуемся функцией Excel =Предсказ:

Разберем формулу Excel =ПРЕДСКАЗ(

• LN(D2)  - X- ссылка но номер периода X, причем делаем логарифм Х;
• $D$4:$BE$4 - известные значения y - зафиксированная ссылка на ряд с объемом продаж за весь период;
• LN($D$2:$BE$2) - известные значения x -  зафиксированная ссылка на диапазон с номерами периодов - X под логарифмом;
• Обратите внимание, что ряд "известные значения y" = "известные значения x"

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

Способы расчета логарифмического тренда в Excel в статье "5 способов расчета логарифмического тренда"

Для чего мы пронумеровываем периоды и о временных рядах читайте статью "О временных рядах"

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

2. Выделим сезонность

1. Определим отношение Объема продаж к тренду - объем продаж разделим на логарифмический тренд:

2. Рассчитаем среднее отношение объема продаж к тренду для каждого месяца в году.

2.1. Определим номер месяца для каждого периода в ряду:

   2.2. Пронумеруем номера месяцев для сезонности:

   2.3. Воспользовавшись функцией Excel=СУММЕСЛИ и =СЧЕТЕСЛИ (подробнее о работе с этими функциями читайте в статье "Формулы Excel "СУММЕСЛИ" и "СЧЕТЕСЛИ""), рассчитаем сезонность для каждого месяца - среднее отклонение отношений объема продаж от логарифмического тренда:

   2.3. Рассчитаем нормирующий коэффициент - среднее значений полученной сезонности

В среднем сезонность должна быть равна 1. А у нас получилась 0,995027

   2.4. Рассчитаем сезонность.

Для того, чтобы сезонность стала равной 1, разделим полученную сезонность на нормирующий коэффициент, который у нас равен 0,995027:

Получили месячные коэффициенты сезонности приведенные в среднем к 1.

3. Прогнозный тренд скорректируем сезонностью, для этоговоспользуемся функцией Excel =ГПР - получим прогноз.

Мы рассчитали:

• Значения логарифмического тренда и продлили тренд в будущее на 12 месяцев
• Сезонность.

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

Прогноз = значения логарифмического тренда для будущих периодов мы умножаем на коэффициент сезонности.

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

Разберем ГПР(искомое значение; таблица;  номер строки; интервальный просмотр) по частям:

• Искомое значение - BF5 - номер прогнозного месяца, для которого нам необходимо найти коэффициент сезонности в таблице и вернуть его значение в ячейку:

• Таблица - $D$12:$O$13 - зафиксированная ссылка на таблицу с сезонностью, в которой в первой строке находятся номера месяцев, а во второй коэффициенты сезонности:

• интервальный просмотр ставим = 0

Нажимаем ввод и получаем прогноз - логарифмический тренд умноженный на коэффициент сезонности:

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

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

Коллеги, для автоматизации прогнозирования предлагаем воспользоваться нашей программой Forecast4AC PRO.

Forecast4AC PRO умеет автоматически подбирать подходящую модель прогноза и рассчитает прогноз в Excel для большого массива данных быстро и легко!

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