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

функция ГПР 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 месяцев в будущее:

 функция ГПР в Excel пример

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

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

ГПР в Excel пример

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

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

ГПР Excel

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

функция ГПР в Excel пример

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

формула ГПР в Excel

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

 формула ГПР

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

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

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

ГПР Excel

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

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

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

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

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

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

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

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

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

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

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

Формула ГПР ищет номер месяца в первой строке таблицы и возвращает коэффициент сезонности из второй, но для этого еще надо указать строку из которой возвращаем сезонность:

  • 2 - номер строки - номер строки в таблице, из которого возвращаем коэффициент сезонности. В первой строке таблице с сезонностью номера месяцев, во второй коэффициенты сезонности

функция ГПР пример расчета

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

ГПР расчет

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

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

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

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

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

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

 

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast - прогноз в Excel - точно, легко и быстро!

  • Novo Forecast Lite - автоматический расчет прогноза в Excel.
  • 4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
  • Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

  • Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Зарегистрируйтесь и скачайте решения

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

 

Комментарии   

#2 Алексей Батурин 26.05.2016 10:53
Цитирую Дмитрий:
По проведенному прогнозу не могу подтвердить корректность вашего расчета коэфф. сезонности и в итоге самого прогноза.

Из исторических данных видно, что по всем годам, с января по декабрь, продажи падают, а в вашем прогнозе (Прогноз = тренд * сезонность) объем продаж не имеет такой отрицательной линейности, а наоборот

Дмитрий, здравствуйте.
Спасибо что обратили внимание.
Но статья о применении функции ГПР в Excel, а не о подборе оптимальной модели прогнозирования .
Цитировать
#1 Дмитрий 26.05.2016 10:20
По проведенному прогнозу не могу подтвердить корректность вашего расчета коэфф. сезонности и в итоге самого прогноза.

Из исторических данных видно, что по всем годам, с января по декабрь, продажи падают, а в вашем прогнозе (Прогноз = тренд * сезонность) объем продаж не имеет такой отрицательной линейности, а наоборот
Цитировать

Добавить комментарий