Печать
PDF

Функция ГПР в 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 для большого массива данных быстро и легко!

 

Зарегистрируйтесь и протестируйте Forecast4AC PROскачать программу для прогнозирования Forecast4AC PRO

график модель прогноза + трендграфик сезенностьграфик прогноз + границы прогноза + трендграфик прогноз к предыдущим периодам

Forcast4AC Pro 4ddabd6bbee26 250x250Подписка "Прогноз с точностью 90% и выше!"

Присоединяясь к нам Вы получаете:

  • Более 10 рекомендаций по прогнозированию продаж!
  • Практические примеры!
  • Ссылку на Forecast4AC PRO - 40 дней бесплатно!
  • + советы по работе с программой!

Forecast4AC PRO - прогноз одним нажатием клавиши!

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

Ваши комментарии и вопросы:

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


Защитный код
Обновить