Лабораторная работа № 5
Цель: По опытным данным построить уравнение регрессии вида у = ах3 + bх2 + сх + d.
ПРИМЕР. Рассмотрим решение задачи на примере опытных данных
Х | ||||||||||||
Y |
Введем эти данные в электронную таблицу вместе с подписями в ячейки А1-М2.
Построим график. Для этого обведем данные Y (ячейки В2-М2), и выберем График с маркерами. Видно, что график имеет 2 экстремума и один перегиб, поэтому его можно приблизить полиномом 3 степени у = ах3 + bх2 + сх + d.
Для нахождения коэффициентов a, b, c, d нужно решить систему уравнений:
Рассчитаем суммы. Для этого в ячейку А3 вводим подпись «Х^2», а в В3 вводим формулу «= В1*В1» и Автозаполнением переносим ее на всю строку В3-М3.
В ячейку А4 вводим подпись «Х^3», а в В4 формулу «=В1*В3» и Автозаполнением переносим ее на всю строку В4-М4.
В ячейку А5 вводим «Х^4», а в В5 формулу «=В4*В1», автозаполняем строку.
В ячейку А6 вводим «Х^5», а в В6 формулу «=В5*В1», автозаполняем строку.
|
|
В ячейку А7 вводим «Х^6», а в В7 формулу «=В6*В1», автозаполняем строку.
В ячейку А8 вводим «Х*Y», а в В8 формулу «=В2*В1», автозаполняем строку.
В ячейку А9 вводим «Х^2*Y», а в В9 формулу «=В3*В2», автозаполняем строку.
В ячейку А10 вводим «Х^3*Y», а в В10 формулу «=В4*В2», автозаполняем строку.
Теперь считаем суммы. Выделяем другим цветом столбец N. В ячейку N1 помещаем курсор и щелкнув по кнопке автосуммы со значком ∑, вычисляем сумму первой строки. Автозаполнением переносим формулу на ячейки N1-N10.
Решаем теперь систему уравнений. Для этого вводим основную матрицу системы. В ячейку А13 вводим подпись «А=», а в ячейки матрицы В13-Е16 вводим ссылки, отраженные в таблице
B | C | D | E | |
=N7 | =N6 | =N5 | =N4 | |
=N6 | =N5 | =N4 | =N3 | |
=N5 | =N4 | =N3 | =N1 | |
=N4 | =N3 | =N1 |
Вводим также правые части системы уравнений. В G13 вводим подпись «В=», а в Н13-Н16 вводим, соответственно ссылки на ячейки «=N10», «=N 9», «=N 8», «=N 2».
Решаем систему матричным методом. Из высшей математики известно, что решение равно А-1В. Находим обратную матрицу. Для этого в ячейку I13 вводим подпись «А обр.» и, поставив курсор в J13 задаем формулу МОБР (категория «Математические»). В качестве аргумента «Массив» даем ссылку на ячейки В13:Е16. Результатом также должна быть матрица размером 4×4. Для ее получения обводим ячейки J13-М16 мышью, выделяя их и нажимаем F2 и Ctrl+Shift+Enter. Результат – матрица А -1.
Найдем теперь произведение этой матрицы на столбец В (ячейки Н13-Н16). Вводим в ячейку А18 подпись «Коэффициенты» и в В18 задаем функцию МУМНОЖ (категория «Математические»). Аргументами функции «Массив 1» служит ссылка на матрицу А -1 (ячейки J13-М16), а в поле «Массив 2» даем ссылку на столбец В (ячейки Н13-Н16). Далее выделяем В18-В21 и нажимаем F2 и Ctrl+Shift+Enter. Получившийся массив – коэффициенты уравнения регрессии a, b, c, d. В результате получаем уравнение регрессии вида: у = 0,0122 х 3 – 0,4379 х 2 + 4,0458 х + 0,6071.
|
|
Построим графики исходных данных и полученных на основе уравнения регрессии. Для этого в ячейку А11 вводим подпись «Регрессия» и в В11 вводим формулу «=$В$18*В4+$В$19*В3+$В$20*В1+$В$21». Автозаполнением переносим формулу в ячейки В11-М11. Строим График с маркерами по значениям строк Регрессия и Y. Видно, что кривые почти совпадают.