2. Прогнозирование с помощью линий тренда

Линии тренда графиков Excel используются для установления зависимости и исследования связи между двумя переменными.

Задача. Условия задачи остаются прежними (как в пункте 1).

Решение:

1. Введите значения X и Y по столбцам, скопировав лист с данными из п.1.

2. Постройте график зависимости Y(X), используя тип диаграммы «Точечная, с гладкими кривыми и маркерами».

3. Поставьте указатель мыши на линию графика функции, правая кнопка мыши – контекстное меню «Добавить линию тренда».

4. Установите параметры: тип тренда «линейная», «прогноз вперед на 1 период», «показывать уравнение на диаграмме», «поместить величину достоверности аппроксимации R^2».

5. Введите заголовок диаграммы «Прогноз объема производства на 11-й год».

6. Повторите пункты 2, 3, 4 для получения прогноза по следующим функциям: логарифмическая, полиномиальная второй степени, степенная, экспоненциальная.

7. Выберите лучшую модель по критерию R^2, которая лучше остальных описывает зависимость Y от X. Коэффициент детерминации R^2 равен доле исходных данных, которые подчиняются выбранной тенденции.

8. Произведите расчеты теоретических значений Y_i^теор. и прогнозного значения Y_прогн для ожидаемого X_ож = 11 по наилучшей модели.

9. Результаты расчетов изображены на рисунке.

3. Прогнозирование по методу наименьших квадратов с помощью матричных операций

Задача остается прежней: вычислить коэффициенты линейной модели b и a по методу наименьших квадратов, но только с помощью матричных операций.

Матричный способ решения построения модели имеет преимущества и недостатки.

Преимущества: компактность записи формул; исследование многофакторных моделей.

Недостатки: необходимость знания матричной алгебры; необходимость наличия программных средств выполнения матричных операций (Excel выполняет все матричные вычисления, кроме вычисления собственных значений и собственных векторов).

Перечень матричных операций в Excel:

- транспонирование – функция ТРАНПС категории «Ссылки и массивы»;

- вычисление обратной матрицы – функция МОБР категории «Математические»;

- умножение матриц – функция МУМНОЖ категории «Математические».

Особенности выполнения матричных операций в Excel:

1. после выбора функции установить нужные аргументы и выполнить расчеты для первой ячейки результирующего массива;

2. выделить первую ячейку с расчетами и все ячейки, на которые будет распространено действие функции;

3. нажать и отпустить клавишу F2;

4. последовательно нажать, не отпуская, клавиши Ctrl + Shift + Enter.

Решение задачи:

1. Введите данные X_i и Y_i по столбцам. Для этого достаточно скопировать лист, в котором решалась задача в п.1.

2. Выполните расчет коэффициентов модели a и b в матричном виде по формуле:

A = (X^Т X)^-1 X^Т Y,

где A – вектор-столбец коэффициентов модели;

X – матрица исходных данных, которая включает вектор-столбец переменной для свободного коэффициента b (его значения в нашем случае равны 1) и векторы-столбцы объясняемых факторов (в нашем случае – один столбец со значениями X_i);

X^Т – транспонированная матрица;

(X^Т X)^-1 – обратная матрица от произведения двух матриц;

Y – вектор-столбец зависимой переменной.

3. Вычислите коэффициенты модели a и b в матричном виде в последовательности, как показано на рисунке.

4. Вычислите расчетные (теоретические) значения Y_i^теор., прогнозное значение Y_прогн для X_ожид=11.

Разобранный пример – учебный. Поэтому мы ограничились очень небольшим числом экспериментальных точек. В реальных условиях для обеспечения достоверности результатов исследования нужно брать гораздо большее число экспериментальных точек.