Методы и формулы прогнозирования в Excel

Для начала давайте вспомним пример, где мы просчитывали величину банковского вклада через несколько лет при определённой процентной ставке. В этом случае решение легко находилось построением экспоненциальной экстраполяции по начальному вкладу и его величине через год. Однако давайте представим ситуацию, когда этот самый вклад может постоянно изменяться. Проделывать постоянно один и тот же блок операций, согласитесь, не самый удобный вариант. В этой ситуации на помощь проходят специальные статистические функции Excel. Для линейной экстраполяции используется ТЕНДЕЦИЯ (TREND), для экспоненциальной – РОСТ или GROWTH. Осталось всего лишь разобраться, как с ними работать.

Сперва создаём новую книгу, где вначале отводим колонку под номер года и уровень процентной ставки по истечении соответствующего числа лет. После этого определяем в некоторой ячейке величину первоначального вклада. У меня лично это получилось следующим образом:

 

Далее выбираем ячейку D2 и проделываем следующее:

1. В поле формулы вводим = C2*
2. Вставка → Функция…
3. Выбираем функцию РОСТ (раздел статистические)
4. Задаём Известные_значения_y := B2:B11, после чего нажимаем ОК.
5. Выделяем ячейки D2:D11.
6. Нажимаем , затем ++

После этого последний столбец, как и в случае, когда мы просто применяли экспоненциальную экстраполяцию, будет заполнен соответствующими значениями размера вклада. Однако в этом случае для перерасчёта нам просто-напросто необходимо изменить значение одной ячейки:

 

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

1. Заполним ячейку A13 необходимым нам годом
2. Выбираем ячейку D13
3. В поле формулы вводим = C2*
4. Вставка → Функция…
5. Выбираем функцию РОСТ (раздел статистические)
6. Задаём Известные_значения_y := B2:B11, Известные_значения_х := А2:А11, Новые_значения_y := А13, после чего нажимаем ОК.

Таким образом, в ячейке D13 мы без труда получаем, вклад в заданном году:

 

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

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