Лабораторная работа № 4. Часть 2
1. Найти коэффициенты квадратичной аппроксимирующей функции y=F(x) для функции y=f(x), которая задана таблицей
· решением системы линейных алгебраических уравнений;
Аппроксимирующая квадратичная функция имеет вид y=Cx2+Bx+A. Для нахождения неизвестных коэффициентов A и B необходимо решить следующую систему уравнений:
Составим таблицу для вычисления коэффициентов СЛАУ:
Получим матрицу коэффициентов системы уравнений A1 и столбец правых частей уравнений B1. Полученную систему уравнений
решим методом обратной матрицы. Решение системы – вектор X={x1,x2, x3}, где x1=C, x2=B, x3=A
Следовательно, искомая формула имеет вид
y=-0,18148x2+2,051258x-1,31163
· найти регрессионную сумму квадратов
· найти остаточную сумму квадратов
· найти коэффициент детерминированности
· построить в одной системе координат точечный график заданной функции y=f(x) и квадратичной аппроксимирующей функции y=F(x)
2. Найти коэффициенты квадратичной аппроксимирующей функции y=F(x) для функции y=f(x), которая задана таблицей (Табл.11.1)
· вычислением степенной регрессии с помощью функций Excel;
· найти регрессионную сумму квадратов
· найти остаточную сумму квадратов
· найти коэффициент детерминированности
В ячейки А3:А11 введите заданные значения xi, в ячейки В3:В11 введите заданные значения xi2, в ячейки C3:C11 введите заданные значения yi.
Присвойте ячейкам G6,H6 и I6 имена C_, В и А соответственно.
Обратите внимание, что для коэффициента С используется обозначение С_.
В ячейку D3 введите =A+B*A3+C_*B3, скопируйте формулу в ячейки D3:D11.
Определите место для коэффициентов регрессии и статистики.
Перейдем к вычислению коэффициентов.
Выделите ячейки G6:I10 и введите формулу
=ЛИНЕЙН(C3:C11;A3:B11;ИСТИНА;ИСТИНА)
Нажмите комбинацию клавиш <Ctrl> + <Shift> + <Enter> для вставки формулы во все ячейки в виде массива.
· построить в одной системе координат точечный график заданной функции y=f(x) и квадратичной аппроксимирующей функции y=F(x)
3. Найти коэффициенты квадратичной аппроксимирующей функции y=F(x) для функции y=f(x), которая задана таблицей (Табл.11.1)
· с использованием надстройки Поиск решения;
· найти регрессионную сумму квадратов
· найти остаточную сумму квадратов
· найти коэффициент детерминированности
В ячейки А3:А11 введите заданные значения xi, в ячейки В3:В11 введите заданные значения xi2, в ячейки C3:C11 введите заданные значения yi.
В ячейку D3 введите =$H$5*B3+$H$4*A3+$H$3, скопируйте формулу в ячейки D3:D11.
В ячейку H8 введите формулу для r2
=1-СУММ((C3:C11-D3:D11)^2)/СУММ((C3:C11-СРЗНАЧ(C3:C11))^2)
Нажмите комбинацию клавиш <Ctrl> + <Shift> + <Enter> для вставки формулы в виде массива.
В ячейку H9 введите формулу для регрессионной суммы квадратов
=СУММ((C3:C11-СРЗНАЧ(C3:C11))^2)
Нажмите комбинацию клавиш <Ctrl> + <Shift> + <Enter> для вставки формулы в виде массива.
В ячейку H10 введите формулу для остаточной суммы квадратов
=СУММ((C3:C11-D3:D11)^2)
Нажмите комбинацию клавиш <Ctrl> + <Shift> + <Enter> для вставки формулы в виде массива.
Перейдем к использованию надстройки Поиск решения
Установите произвольные значения коэффициентов А,B и C в ячейках H3, H4 и H5.
Выберете команду меню Сервис -> Поиск решения.
Заполните диалоговое окно как показано на рисунке
Для поиска решения нажмите на кнопку «Выполнить».
· построить в одной системе координат точечный график заданной функции y=f(x) и квадратичной аппроксимирующей функции y=F(x)
4. Построить точечный график заданной функции y=f(x) и, воспользовавшись свойством диаграмм создавать линии тренда, добавить линейную линию тренда и степенную (степень = 2).
В ячейки А2:А10 введите заданные значения xi, в ячейки В2:В10 введите заданные значения yi.
Постройте точечный график заданной функции y=f(x).
Выберите команду меню Диаграмма –> Добавить линию тренда.
В диалоговом окне на вкладке Тип выберите Линейный.
На вкладке Параметры установите флажки показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации.
Выберите команду меню Диаграмма –> Добавить линию тренда.
В диалоговом окне на вкладке Тип выберите Полиномиальный и установите в поле Степень значение 3 (квадратичная функция).
На вкладке Параметры установите флажки показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации.
5. Постройте сравнительные таблицы для коэффициентов, полученных разными методами.
| Квадратичная функция | С | В | А | регрессионная сумма квадратов | остаточная сумма квадратов | коэффициент детерминированности |
| система линейных алгебраических уравнений | -0,1814758 | 2,0512584 | -1,311628 | 14,27888889 | 1,08604857 | 0,92394026 |
| степенная регрессия с помощью функций Excel | -0,1814758 | 2,0512584 | -1,311628 | 13,19284032 | 1,08604857 | 0,92394026 |
| использование надстройки Поиск решения | -0,1814717 | 2,0512094 | -1,311507 | 14,27888889 | 1,08604858 | 0,92394026 |
| линия тренда | -0,1815 | 2,0513 | -1,3116 | 0,9239 |