Задание 2. Линейная многофакторная корреляция

Постановка задачи: Выполните аппроксимацию откликаYот совместного влияния трех входов X1, X2, X3. Математическая модель множественной линейной трехфакторной корреляции имеет вид:

Решение задачи множественной корреляции заключается в поиске коэффициентов регрессии . Для этого создается систему нормальных уравнений.

Порядок работы: Введите исходные данные для корреляционного анализа по варианту задания копированием. Выполните форматирование ЭТ.

Создайте систему нормальных уравнений для нахождения коэффициентов регрессии.

Для вычисления сумм входов, отклика и произведений факторов влияния на отклик необходимо справа от основной таблицы добавить столбцы X1^2, X2^2, X3^2, X1*X2, X1*X3, X2*X3,X1*Y, X2*Y, X3*Yи вычислить по ним сумму.

Таблица 2 Промежуточные расчеты сумм

i	X1^2	X2^2	X3^2	X1*X2	X1*X3	X2*X3	Y X1	Y X2	Y X3
1
2
…
n-1
n
									

Для решения системы нормальных уравнений используют матричные операции A=X^-1 Y. Создайте матрицу коэффициентов системы уравнений и векторYправой части системы уравнений. Заполнение табличной формы системы уравнений нужно выполнять ссылками на суммы входных факторов, отклика, произведения факторов влияния и выхода, которые вычисленные во вспомогательной таблице.N- количество опытов можно ввести константой 20 или ссылкой на ячейку.

Матрица коэффициентов				Вектор Y
N	X1	X2	X3	Y
X1	X1^2	X1X2	X1X3	Yx1
X2	X1X2	X2^2	X2X3	Yx2
X3	X1X3	X2X3	X3^2	Yx3

Найдите ее обратную матрицу функцией =МОБР(массив матрицы коэффициентов). При использовании математических функций обработки массивов вMSExcelнеобходимо предварительно выделить диапазон пустых ячеек, в который будут помещены значения нового массива, затем вызвать функции и выделить диапазоны аргументов, а для выполнения расчета массивов нажать комбинацию клавишCTRL+SHIFT+ENTER.

Обратная матрица
{=МОБР(диапазон матрицы коэффициентов)}

Вычислите коэффициенты регрессии умножив обратную матрицу на вектор Yправой части уравнения с помощью функции =МУМНОЖ(массив1 – диапазон обратной матрицы; массив2 –диапазон вектора отклика). При использовании математических функций обработки массивов вMSExcelнеобходимо предварительно выделить диапазон пустых ячеек, в который будут помещены значения нового массива, затем вызвать функции и выделить диапазоны аргументов, а для выполнения расчета массивов нажать комбинацию клавишCTRL+SHIFT+ENTER.

Коэфициенты
а0	{=МУМНОЖ(массив1 – диапазон обратной матрицы; массив2 –диапазон вектора отклика)}
а1
а2
а3

Вычислите значения функции аппроксимации Yfпо формуле линейной множественной корреляции. В выраженииMSExcelна коэффициенты необходимо сделать абсолютные ссылки с помощью функциональной клавишиF4, потому что они являются константами. На входные факторы регрессии нужно сделать относительные ссылки, потому что они являются переменными математической модели.

Вычислите квадрат разности Yот значений функции регрессии Yf. Найдите по нему сумму Автосуммированием, полученное значение будем остаточной суммой квадратов отклонений.

Регрессионную сумму квадратов отклонения вычислите статистической функцией =КВАДРОТКЛ(диапазон вычисленной функции аппроксимации).

Вычислите коэффициент множественной корреляции R² =1-Sост/Sрег.

Таблица3. результаты корреляционного анализа

i	X1	X2	X3	Y	Yf=a0+a1*x1+a2*x2+a3*x3	(Y- Yf)^2
1
2
…
n-1
n
						=СУММ(диапазон)	Sостаточная
						=КВАДРОТКЛ(диапазон)	Sрегресионная
					R^2=1-So/Sr	=1-ссылка1/ссылка2

Постройте графики отклика от входов X1, X2, X3 с помощью диаграммы гладкого графика и совместный гладкий графикY=f(X1, X2, X3) от нумерации опытов.