Расчеты прогнозных данных в MS Excel

Использование линии тренда. Для того, чтобы построить линию тренда необходимо сначала построить точечную диаграмму того же самого процесса. Возвратимся к задаче 1 (рис.7) и построим точечную диаграмму (рис. 13).

Рис. 13

Для построения линии тренда необходимо в меню Диаграмма выполнить команду Добавить линию тренда, которая вызовет окно рис. 14.

а)

б)

Рис. 14

На вкладке Тип (рис. 14а) выбирается тип апроксимации данных (в нашем случае – линейная), а на вкладыше Параметры (рис. 14б) – необходимо установить флажки Показывать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации (R2) (коефициент детерминации R²). Вид построенной линии тренда показан на рис. 15. Из рисунка видно, что уравнение регрессии не отличается от полученного в разделе.

Рис. 15

Если в качестве типа апроксимации избрать полином второй степени (параболу), то построенная линия тренда будет иметь вид рис. 16.

Рис. 16

Из этого рисунка видно, что полученные в данном случае результаты будут более адекватны, чем полученные ранее.

На вкладке Параметры (рис. 14б) кроме того, можно указать количество единиц прогноза, тем самым спрогнозировать искомую величину (рис. 17).

Рис. 17

Использование функции ПРЕДСКАЗ(). Функция ПРЕДСКАЗ() предназначена для расчета значений зависимой случайной величины по известным стастистическим данным на основе линейного приближения методом наименьших квадратов и имеет следующий синтакис:

ПРЕДСКАЗ(X; изв_значY; изв_значX),

где X – элемент независимой переменной для которой вычисляется прогнозное значение; изв_значY – известные значения зависимой переменной; изв_значX - известные значения независимой переменной.

Для той же задачи спрогнозируем цену на сахар.

Пример заполнения параметров функции приведен на рис. 18, а пример расчета на рис. 19.

Рис. 18

а)

б)

Рис. 19

Использование функции ТЕНДЕНЦИЯ(). Назначение функции ТЕНДЕНЦИЯ() очень близко к функции ПРЕДСКАЗ(), но ее возможно употреблять для прогноза не только линейно зависимых величин (неявная линейная регрессия). Функция ТЕНДЕНЦИЯ() имеет следующий синтаксис: