Указания к выполнению лабораторной работы

1) Создайте и сохраните на своем диске Z новый файл MS Excel.

2) Запишите тему, цель работы и вид регрессионной модели (уравнение).

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

4) Выполните расчеты коэффициентов а и в уравнения регрессии, используя регрессионный анализ (методика приведена на стр. 2).

5) Выполните расчет по уравнению линейной регрессии , пристроив дополнительный столбец к таблице исходных данных (см. пример таблица 1.

6) Рассчитайте среднюю ошибку аппроксимации (см. пример таблицы 2).

7) Сделайте вывод по показателям:

· коэффициент корреляции – о тесноте и характере связи;

· коэффициент детерминации – о влиянии изменения результата «у» при изменении факторной переменной «х»;

· средняя ошибка аппроксимации – о величине ошибки регрессии;

· F- критерий Фишера – о значимости и надежности оцениваемых характеристик (о принятии или отвержении гипотезы Н₀).

Рассмотрим пример расчета и анализа следующих исходных данных.

Таблица 1

Продажа макаронных изделий (за год)

	Y	Х
Территории потребителей	Продано в год, т. р.	Заказано в год, т.р.
Свердловская область	475,5
Республика Бурятия	2100,3	2022,2
Красноярский край	3188,4	3012,2
Омская область		1053,3
Алтайский край	2199,8	2100,1
Кемеровская область		19034,5
Новосибирская область	46104,3	38321,1
Томская область	2503,8	2202,2

1) Определим, где в таблице исходных данных зависимая переменная «у», а где независимая (объясняющая) переменная «х» (т.е. что от чего зависит: объем продаж от объема заказов (изначально) или наоборот).

Обозначим: Y – продано товаров за год, т. р., Х – заказано в соответствии с договорами за год, т.р.

2) Введем обозначения переменных над шапкой таблицы исходных данных и выделите эти ячейки яркой заливкой (см. пример таблицы 2.1).

3) Находим параметры а и в. Для этого на вкладке «Данные» программы Excel щелкнуть в «Анализ данных → Регрессия» ( Настройка Анализа данных: нажать Файл Параметры слева в диалоговом окне щелкнуть в Надстройки справа в окне «Пакет анализа» далее внизу кнопку Перейти активировать(отметить галочкой) «Пакет анализа». Справа, в верхнем углу, на панели инструментов вкладки ДАННЫЕ появится функция Анализ данных).

В раскрывшемся диалоговом окне вводим:

® «Входной интервал Y» - щелкнуть курсор в окно напротив «входной интервал Y» и выделить весь столбец числовых значений результативного показателя (у);

® «Входной интервал Х» - щелкнуть курсор в окно напротив «входной интервал Х» и выделить весь столбец числовых значений факторного показателя (х);

® «Выходной интервал» - щелкнуть курсор в окно напротив «выходной интервал» и выделить одну пустую ячейку слева под таблицей исходных данных (см. пример рис. 1.)

® Нажать ОК.

Под таблицей исходных данных появится «ВЫВОД ИТОГОВ» - таблицы с показателями, рассчитанные в ходе выполнения регрессионного анализа.

«ВЫВОД ИТОГОВ» содержит 3 таблицы:

1. «Регрессионная статистика»:

· множественный R – коэффициент корреляции R;

· R–квадрат – коэффициент детерминации R2;

· нормированный R – нормированное значение коэффициента корреляции;

· стандартная ошибка – стандартное отклонение остатков;

· наблюдения – число исходных наблюдений (объем выборки).

2. Показатели таблицы «Дисперсионный анализ»:

· столбец «F» – расчетное значение F-критерия Фишера (это Fфакт);

· столбец «Значимость F» – значение уровня значимости, соответствующее вычисленному значению Fр.

3. Показатели таблицы 3:

· ячейка на пересечении столбца «Коэффициенты» и строки «Y - пересечение» – значение параметра уравнения линейной регрессии а - «отрезок».

· ячейка на пересечении столбца «Коэффициенты» и строки «Переменная Х₁» – значение параметра уравнения линейной регрессии b – наклон.

Для нашего примера «вывод итогов» будет выглядеть следующим образом:

	Y	X
Территории	Продано, т.р.	Заказано, т.р.	y^=a+b*x	(y-y^)/y	\|(y-y^)/y\|
Свердловская область	475,5		289,431216	0,391312	0,3913118
Республика Бурятия	2100,3	2022,2	2103,45947	-0,0015	0,0015043
Красноярский край	3188,4	3012,2	3284,80927	-0,03024	0,0302375
Омская область		1053,3	947,28793	0,087391	0,0873912
Алтайский край	2199,8	2100,1	2196,41619	0,001538	0,0015382
Кемеровская область		19034,5	22403,9416	-0,06782	0,0678205
Новосибирская область	46104,3	38321,1	45418,3064	0,014879	0,0148792
Томская область	2503,8	2202,2	2318,25034	0,074107	0,0741072
Якутия			1532,83172	0,138859	0,1388586
Читинская область			727,365941	0,145281	0,1452809
					0,9529295
				Аср.=	9,5292947
ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R (r_ху)	0,999313564	коэффициент корреляции
R-квадрат ,	0,998627599	коэффициент детерминации
Нормированный R-квадрат	0,998456049
Стандартная ошибка	576,4462962
Наблюдения		объем выборки
				F (табл)	5,25
Дисперсионный анализ				критерий Фишера
	df	SS	MS	F (факт)	Значимость F
Регрессия				5821,202	9,706E-13
Остаток		2658322,659	332290,332
Итого

	Коэффициенты	Станд. ошибка	t-статистика	P-Значение	Нижние 95%	Верхние 95%	Нижние 95,0%	Верхние 95,0%
Y-пересечение (коэф-нт a)	-309,596665	213,1717852	-1,4523342	0,184474	-801,1717	181,9784	-801,172	181,9784
Переменная X (коэф-нт b)	1,193282631	0,015640009	76,2967991	9,71E-13	1,1572167	1,229349	1,157217	1,229349

Рис. 1. Пример оформления лабораторной работы

Как показано в примере на рисунке 1, выделите и подпишите в выводах итогах регрессии все необходимые показатели: коэффициенты корреляции r_хуи детерминации , F-критерий Фишера, коэффициенты регрессии а и в.

После того как найдены коэффициенты а и в, рассчитываем функцию линейной регрессии ̂ . Для этого к исходной таблице 1 добавляем столбец ̂ , (таблица 2).

Таблица 2

	Y	X
Территории потребителей	Продано, т.р.	Заказано, т.р.	y^=a+b*x	(y-y^)/y	\|(y-y^)/y\|
Свердловская область	475,5		=а(F4)+b(F4)*гр.3	0,391312	0,3913118
Республика Бурятия	2100,3	2022,2	2103,45947	-0,0015	0,0015043
Красноярский край	3188,4	3012,2	3284,80927	-0,03024	0,0302375
Омская область		1053,3	947,28793	0,087391	0,0873912
Алтайский край	2199,8	2100,1	2196,41619	0,001538	0,0015382
Кемеровская область		19034,5	22403,9416	-0,06782	0,0678205
Новосибирская область	46104,3	38321,1	45418,3064	0,014879	0,0148792
Томская область	2503,8	2202,2	2318,25034	0,074107	0,0741072
Якутия			1532,83172	0,138859	0,1388586
Читинская область			727,365941	0,145281	0,1452809
					0,9529295
				Аср.=	9,5292947

Для расчета величины у^ необходимо выполнить следующее:

1. поставить курсор в ячейку, выделенную желтым цветом;

2. ввести формулу:

= абсолютный адрес коэффициента (а) (F4) + абсолютный адрес
коэффициента (в)(F4) * относительный адрес значения xi (Заказано, т.р.) по Свердловской области.

Для того чтобы адрес ячейки не смещался при протягивании формулы, нужно во время записи формулы ввести абсолютный адрес этой ячейки, т.е. при ссылке на адрес ячейки, где стоит коэффициент а и в вводится символ доллара (например, $D$11). Для этого достаточно нажать клавишу F4, после введения в строку формулы адреса соответствующей ячейки:

= адрес ячейки « коэффициент (а)» (нажать F4) + адрес ячейки « коэффициент (в)» (нажать F4) * адрес ячейки xi (Заказано) по Свердловской области.

3. после ввода формулы нажать клавишу Enter и захватив мышью правый нижний угол ячейки, протянуть ее на 9 строчек вниз.

Таким образом, функция линейной регрессии рассчитана.

Далее рассчитываем среднюю ошибку аппроксимации по формуле:

Расчет выполним также с помощью дополнительных вспомогательных расчетных столбцов (см. табл. 3 графа 5,6)

Таблица 3

	Y	X

Территории потребителей	Продано, т.р.	Заказано, т.р.	y^=a+b*x	(y-y^)/y	\|(y-y^)/y\|
Свердловская область	475,5		=а(F4)+b(F4)*гр.3	=(гр.2-гр.4)/гр.2	=АВS (гр.5)
Республика Бурятия	2100,3	2022,2	2103,45947	-0,0015	0,0015043
Красноярский край	3188,4	3012,2	3284,80927	-0,03024	0,0302375
Омская область		1053,3	947,28793	0,087391	0,0873912
Алтайский край	2199,8	2100,1	2196,41619	0,001538	0,0015382
Кемеровская область		19034,5	22403,9416	-0,06782	0,0678205
Новосибирская область	46104,3	38321,1	45418,3064	0,014879	0,0148792
Томская область	2503,8	2202,2	2318,25034	0,074107	0,0741072
Якутия			1532,83172	0,138859	0,1388586
Читинская область			727,365941	0,145281	0,1452809
					0,9529295	=S гр.6
					9,5292947	= Аср.

Для расчета графы 5 ((y-y^)/y) таблицы 3 необходимо:

1. поставить курсор в пустую ячейку, выделенную желтым цветом;

2.ввести формулу:

= (относительный адрес значения «у» по Свердловской области – относительный адрес значения « у^» по Свердловской области) / относительный адрес значения «у» по Свердловской области.

Для расчета графы 6 (|(y-y^)/y|) таблицы 3необходимо:

1. поставить курсор в ячейку, выделенную оранжевым цветом;

2. На панели инструментов щелкнуть в кнопку f(x). В появившемся диалоговом окне выбрать Категорию «Математические» ® функция «ABS» ®ОК® выделить значение графы 5 ((y-y^)/y) по Свердловской области ® ОК.

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

4. Находим сумму графы |(y-y^)/y|(т.е. автосумму графы 6) в ячейке сиреневого цвета (см.табл.3) под этим столбцом.

5. Рассчитываем Аср. по формуле стр.7. Ставим курсор в ячейку ниже автосуммы (гр.6) и записываем формулу:

= щелкаем в ячейку с автосуммой/ 10*100

6. Делаем выводы о качестве модели по показателям:

1. коэффициент корреляции;

2. коэффициент детерминации;

3. средняя ошибка аппроксимации;

4. F-критерий Фишера.

7. Сделаем общий вывод о качестве построенной линейной модели (см. стр.1)

Понравилась статья? Добавь ее в закладку (CTRL+D) и не забудь поделиться с друзьями: