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

Задача остается прежней: вычислить коэффициенты линейной модели 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.

 

 

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

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

 

Для зависимостей со многими неизвестными подбор формул можно выполнить несколькими способами:

- с помощью функций из группы Статистические - ЛИНЕЙН и ЛГРФПРИБЛ.

- функции ТЕНДЕНЦИЯ и РОСТ – для вычисления значений аппроксимирующей функции в диапазоне наблюдения;

- инструмент для подбора формул со многими неизвестными Регрессия, входящий в Пакет анализа (Данные – Анализ данных…);

- матричными вычислениями по методу наименьших квадратов.

 

Функции ЛИНЕЙН и ТЕНДЕНЦИЯ применяют для аппроксимации экспериментальных данных линейные зависимости вида y = b + a₁x₁ + a₂x₂ + … + a_nx_n.

Функции ЛГРФПРИБЛ и РОСТ применяют для аппроксимации экспериментальных данных нелинейные (показательные) зависимости вида

Функции ЛИНЕЙН и ЛГРФПРИБЛ возвращают массив с т.н. регрессионной статистикой, который содержит вычисленные значения параметров (b, a₁, a₂, …, a_n), коэффициент детерминации R² и другие характеристики аппроксимирующей функции.

 

Рассмотрим пример оценивания значений функции y по трем переменным: х1, х2, х3, предполагая, что между каждой переменной х1, х2, х3 и зависимой переменной y существует линейная зависимость. Полученные в результате опыта (эксперимента) данные занесены в таблицу:

x1	x2	x3	y
35	8	2	30
35	8	10	28
38	8,5	2	32
38	8,5	10	31
38	9	2	33
38	9	10	32
40	10	2	36
40	10	10	34
45	12	2	40
45	12	10	39
50	12	2	50
50	12	10	49
50	15	2	52

Необходимо подобрать формулу для вычисления эмпирических (теоретических) значений y и вычислить прогнозное значение y с данными: х1 = 42, х2 = 11, х3 = 5.

Порядок решения задачи:

1. Заведите приведенную таблицу в ячейки A1:D14. Результаты ввода:

2. Выделите диапазон ячеек B17:E21 (рис.2) для сохранения результатов вычислений функции ЛИНЕЙН – массива регрессионной статистики.

Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов.

3. Вызовите статистическую функцию ЛИНЕЙН и установите параметры, как на рис.1. Параметр Изв_знач_ y содержит диапазон D2:D14, т.е. известные значения y. Параметр Изв_знач_х содержит диапазон A2:C14, т.е. известные значения х. Параметр Стат=1 – для получения дополнительной статистики.

 

Рис. 1

4. После нажатия ОК встаньте на строку формул (или нажмите F2) и нажмите Ctrl+Shift+Enter. В результате должен получиться массив значений, показанный на рис.2.

На рисунке искомые коэффициенты b, a₁, a₂, a₃ выделены (подробнее см. справку F1). Коэффициент детерминации R ²=0.9725 вполне удовлетворителен (близок к единице).

Таким образом, аппроксимирующая формула y=b+a₁x₁+a₂x₂+a₃x₃ имеет вид:

Y = 1,36*х1 + 0,1*х2 – 0,21*х3 – 19,27

 

Рис. 2

 

5. С использованием полученной формулы вычислите теоретические значения y_теор и прогнозное значение функции y_прогн при х1 = 42, х2 = 11, х3 = 5, записав самостоятельно в любую ячейку формулу для автоматического расчета. Результат расчета: y_прогн= 37,9.

 

Использование функции ТЕНДЕНЦИЯ рассмотрим на этом же примере для расчета значений y при других различных вариантах данных X.

Новые значения X
x1	x2	x3
33	8	1
35	8	20
36	8,5	1
38	8,5	20
38	9	1
39	9	20
42	12	1
40	10	20
45	12	1
45	12	20
50	12	1
50	12	20
55	15	1

Новые значения Х, для которых надо рассчитать y, введите в ячейки F2:H14.

Выделите диапазон I2:I14 для записи в него рассчитываемых значений y.

Вызовите функцию ТЕНДЕНЦИЯ. Параметры функции заполните как на рис.3. Параметр Нов_знач_х содержит диапазон F2:H14, т.е. новые значения x.

После нажатия ОК встаньте на строку формул (или нажмите клавишу F2) и нажмите Ctrl+Shift+Enter. В результате диапазон I2:I14 будет заполнен рассчитанными значениями y (рис.4).

 

Рис. 3

Рис. 4

Оценка эффективности рекламы

 

Подберите формулу для вычисления процента увеличения оборота при различных затратах на рекламу. Экспериментально известны проценты увеличения оборота при затратах в 5, 10, 15, 20 тыс.$ в 3-х масс-медиа – на телевидении, радио и в прессе:

	5 тыс. $	10 тыс. $	15 тыс. $	20 тыс. $
1. TV	28%	43%	61%	95%
2. Радио	15%	24%	34%	50%
3. Пресса	6%	9%	13%	20%

Кроме этого, надо вычислить процент увеличения оборота при затратах на рекламу в прессе 2 тыс.$ и на телевидении 22 тыс.$. Дополнительно вычислите проценты при затратах во всех масс-медиа 2, 17 и 25 тыс.$.

 

Для решения задачи в первую очередь следует правильно разместить данные (рис.1).

Рис. 1

 

Затем вычислите массив с регрессионной статистикой функцией ЛИНЕЙН: выделите диапазон ячеек F 2: H 6 и проделайте известные из предыдущего примера действия. В итоге должен получиться массив:

 

 

Видно, что коэффициент детерминации R²=0.8757 недостаточно удовлетворителен.

Поэтому выполните подбор формулы с помощью функции для нелинейных зависимостей ЛГРФПРИБЛ: выделите диапазон ячеек F 9: H 13 и проделайте известные из предыдущего примера действия. В итоге должен получиться массив:

 

 

В этом случае коэффициент детерминации R²=0.989 вполне удовлетворителен, и можно записать искомую аппроксимирующую формулу показательного типа (т.к. использована функция ЛГРФПРИБЛ):

Y = 0,44 * 0,46^х1 * 1,08^х2

 

Теперь вычислите проценты увеличения оборота по условию задачи: введите формулы и не забудьте установить процентный формат отображения значений в ячейках:

 

Пресса, 2 тыс. $	5,0%	=H9*G9^3*F9^2
TV, 22 тыс. $	11 5 , 9 %	=H9*G9^1*F9^22

 

Вычислите проценты для всех масс-медиа при затратах 2, 17 и 25 тыс.$. Подготовьте новые данные X в колонках K и L:

 

Для вычисления значений Y используем функцию РОСТ, поскольку известно, что зависимость нелинейная, показательная. Выделите диапазон ячеек M2:M10 и введите функцию РОСТ; установите параметры функции как на рисунке.

Рис.

Результаты расчетов:

Рис.

---

Дата добавления: 2021-01-21; просмотров: 204; Мы поможем в написании вашей работы!

Поделиться с друзьями:

---

---

Мы поможем в написании ваших работ!