Напомню читателям, что в первой части данного обзора мы с вами кратко познакомились с понятием прогнозирования и получили первые простейшие результаты, основанные на построении по заданным данным арифметической или же геометрической прогрессии. И всё бы было замечательно да, к сожалению, зачастую добиться желаемого лишь с помощью обычной экстраполяции достаточно сложно или же вовсе не представляется возможным. Поэтому в дополнение к уже написанному материалу, хотелось бы рассказать ещё несколько полезных методов, связанных с построением прогнозов в Microsoft 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, связанных с прогнозированием, но, тем не менее, даже эти методы вполне могут справиться с рядом достаточно важных задач.