Регрессионный анализ. Вставьте в книгу Лист14ипереименуйте егонаРегрессионный анализ

Вставьте в книгу Лист14 ипереименуйте егона Регрессионный анализ. Заполните с клавиатуры нижеприведенную таблицу.

	A	B	C
	N	X	Y

Регрессионный анализ

· Введите данные в ячейки А1: С11. В ячейке F2 посчитайте коэффициент корреляции, который вычисляется с помощью функции КОРРЕЛ из категории Статистические (для данного примера коэффициент корреляции равен 0, 750677).

· Рассчитайте коэффициенты a и b уравнения линейной регрессии y=bx+a

с помощью функции ЛИНЕЙН. Для этого выделите ячейки G4: H8 и введите функцию =ЛИНЕЙН(C2: C11; B2: B11; 1; 1), а затем нажмите функциональную клавишу F2, а потом Ctrl+Shift+Enter.

· Для удобства дальнейшего исследования подпишите рядом с полученными численными значениями величины, которым они соответствуют (нам необходимы только те, которые отмечены в нижеприведенной таблице):

a, b – коэффициенты линейной регрессии.

ma, mb – стандартные значения ошибок для a, b.

F - F - статистика.

df – число степеней свободы.

 

Численные значения величин

 

b	0, 519494	62, 62366	a
mb	0, 161646	13, 03286	ma
	0, 563516	9, 827388
F	10, 32827		df
	997, 4795	772, 6205

К таблице исходных данных в ячейку D1 добавляем столбец y*, в который необходимо занести значения y, согласно уравнению линейной регрессии.

В ячейку D2 вносим расчетную формулу =G4*B2+H4.

Важно зафиксировать значения коэффициентов регрессии b и a знаком $. Для нашего примера измените формулу в ячейке D2 на =$G$4*B2+$H$4 и протяните до ячейки D11. Запись $G$4 означает, что при заполнении ячеек, когда мы протягиваем формулу, значение этого коэффициента всегда будет браться из ячейки G4.

· Постройте диаграмму корреляционного поля с нанесенной линией регрессии, используя для ряда значений Y точеную диаграмму с маркерами, а для ряда значений Y* - точечную диаграмму с прямыми отрезками.

 

 

 

· Проверьте регрессию на значимость. Для этого сравните значение получившейся F - статистики со значением, полученным с помощью статистической функции FРАСПОБР, которая возвращает значение обратное F - распределению вероятностей (распределению Фишера).

В ячейке М2 введите функцию =FРАСПОБР(0, 05; 1; H7).

 

F-статистика

Fтабл	5, 317655
F> Fтабл	регрессия значима

· С помощью логической функции ЕСЛИ покажите, значима регрессия или нет (если F> Fтабл, то регрессия значима).

· Проверьте на значимость полученные коэффициенты регрессии. Для этого необходимо посчитать t_a = a / m_a, t_b = b / m_b и сравнить их с табличным значением распределения Стьюдента.

· t_табл найдите с помощью функции = СТЬЮДРАСПОБР, где вероятность – 0, 05, степени свободы – ячейка, которая соответствует df в результатах, полученных с помощью функции ЛИНЕЙН.

 

Распределение Стьюдента

 

ta	4, 80506	tb	3, 213763
tтабл	2, 306004
ta> tтабл	коэффициент значим
tb> tтабл	коэффициент значим

 

· Укажите доверительные интервалы параметров регрессии.

 

 

Доверительные интервалы

Доверительные интервалы
32, 56983	alpha	92, 67748
0, 146736	beta	0, 892251

 

Контрольные Задания:

Стоимость аптечного товара с торговой наценкой/скидкой

Код	Товар	Цена	Наценка/скидка
ST-340	Коляска	15456, 70 р.	30%
В1-567	Нагрудники 3 шт.	350, 60 р.	40%
DI-328	Пеленки	820, 50 р.	35%
WI-989	Салфетки 4 шт.	151, 20 р.	20%
AS-469	Молокоотсос ручной	2500, 60 р.	45%