Пример выполнения лабораторной работы.

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

1. Создать таблицу данных.

2. Найти средние значения , выборочные дисперсии  исправленные средние квадратические отклонения .

3. Найти коэффициент корреляции и проверить его значимость.

4. Найти коэффициенты линейного уравнения регрессии.

5. Построить график прямой регрессии.

 

Пример выполнения лабораторной работы.

В табл. 1.1 приведены данные об объеме производства у (тыс.ед.) в зависимости от численности занятых х (тыс.чел.) некоторой фирмы.

Таблица 1.1.

Исходные данные

x	11	13	15	18	20	22	24	25	27
y	15	17	21	20	28	33	34	32	29

 

1. В диапазоне В3:C11 подготовим исходные данные.

 

 

2. Вводим следующие формулы:

Ячейка	Формула	Примечание
D3	=B3*C3	Копируем диапазон D3:D11
E3	=B3*B3	Копируем диапазон E3:E11
F3	=C3*C3	Копируем диапазон F3:F11
B12	=СРЗНАЧ(B3:B11)	Копируем диапазон B12:F12
A17	=E12 – B12*B12	Выборочная средняя фактора
B17	=F12 – C12*C12	Выборочная средняя результата
A20	=СТАНДОТКЛОН(B3:B11)	Исправленное среднее квадратическое отклонение фактора
B20	=СТАНДОТКЛОН(С3:С11)	Исправленное среднее квадратическое отклонение результата

 

Получим следующие результаты (см. рис. 1.1).

Рис. 1.1. Результаты простейшей обработки данных

 

3. Для определения коэффициента корреляции воспользуемся формулой .  Для этого в ячейку Е16 вводим формулу

=( D 12- B 12* C 12)/КОРЕНЬ( A 17* B 17)

Из расчетов следует, что коэффициент корреляции r=0,97. Это свидетельствует о том, что связь между объемом выпуска продукции и численностью занятых весьма высокая и положительная.

4. Для проверки значимости коэффициента корреляции введем вспомогательные данные:

Ячейки

К16         9        число предприятий;

К17      0,05      уровень значимости.

 

 

5. Далее вводим следующие формулы:

H19	=КОРЕНЬ((1-E16*E16)/(K16-2))	Стандартная ошибка
H20	=E16/H19	t - статистика
H21	=СТЬЮДРАСПОБР(K17;K16 – 2)	Критическое значение                   t - статистики
H22	=ЕСЛИ(ABS(H20)>H21; «Значим»; «Незначим»)	Вывод

 

 

Таким образом, получим данные, представленные на                               рис  1.2.

Рис. 1.2. Анализ значимости коэффициента корреляции

 

6. Для определения коэффициентов уравнения линейной регрессии на

основе формул

следует в ячейки I3, I4 ввести соответственно следующие формулы:

=(D12-B12*C12)/A17;

=C12-I3*B12.

Уравнение регрессии у=7,9+1,47х.

Значение коэффициента b=1,47 говорит о том, что при увеличении численности занятых на 1 тыс.чел. объем продукции увеличится на       1,74 тыс.ед.

Результаты расчетов приведены на рис.1.3.

Рис. 1.3. Результаты расчетов

 

7. Для построения графика выделим диапазон В3:С11. Вызовем Мастер диаграмм. Чтобы ось отражала фактические данные, выберем тип диаграммы Точечная. После чего нажмем кнопку Готово. На построенной диаграмме выделим график функции, щелкнув по нему левой кнопкой мыши. Выделение обозначается светлыми маркерами на функции. Нажав правую кнопку мыши, выведем контекстно-зависимое меню, в котором выберем опцию Добавить линию тренда. В окне Линия тренда по вкладке Тип выберем тип функции Линейная, а во вкладке Параметры – установим флажок показывать уравнение на диаграмме. В результате на диаграмме появиться вид теоретической кривой – тренда и ее уравнение (рис.1.4).

 

 

Рис. 1.4. Графики фактических данных и построенной регрессии

 

 

8. Вычисление параметров регрессии с помощью статистических функций Excel:

КОРРЕЛ(массив1;массив2) вычисляет коэффициент корреляции между двумя переменными; значения первой из них приведены в диапазоне массив1, значения второй – в диапазоне массив2;

НАКЛОН(известные_значения_y;известные_значения_x) служит для определения коэффициента b;

ОТРЕЗОК(известные_значения_y;известные_значения_x) служит для определения коэффициента a.

Вводим формулы:

C27	=КОРРЕЛ(B3:B11;C3:C11)	Коэффициент корреляции
C28	=НАКЛОН(С3:С11;B3:B11)	Коэффициент b
C29	=ОТРЕЗОК(C3:C11;B3:B11)	Коэффициент a

 

Встроенная статистическая функция ЛИНЕЙН определяет параметры линейной регрессии. Порядок вычислений следующий:

1) выделите область пустых ячеек 5х2 (5 строк, 2 столбца) с целью вывода результатов регрессионной статистики (А27:В3);

2) в главном меню выберите Вставка/Функция;

3) в строке Категория (рис.1.5) выберите Статистические, в окне Функция – ЛИНЕЙН. Щелкните ОК.

Рис. 1.5. Диалоговое окно «Мастер функций»

 

4) Заполните аргументы функции (рис.1.6.):

 

Известные_значения_у – диапазон, содержащий данные результативного признака;

 

Известные_значения_х – диапазон, содержащий данные факторов независимого признака;

 

Константа – логическое значение, которое указывает на наличие или на отсутствие свободного члена в уравнении; если Константа = 1, то свободный член рассчитывается обычным образом, если Константа = 0, то свободный член равен 0.

 

Статистика – логическое значение, которое указывает выводить дополнительную информацию по регрессионному анализу или нет. Если Статистика = 1, то дополнительная информация выводится, если Статистика = 0, то выводится только оценки параметров уравнения. Далее ОК.

Рис.1.6. Диалоговое окно ввода аргументов функции ЛИНЕЙН

5) В левой верхней ячейке выделенной области появится первый элемент итоговой таблицы. Чтобы раскрыть всю таблицу, нажмите на клавишу F2, а затем – на комбинацию клавиш CTRL+SHIFT+ENTER. Дополнительная регрессионная статистика будет выводиться в порядке, указанном в следующей схеме:

Значение коэффициента	Значение коэффициента
Среднеквадратическое отклонение	Среднеквадратическое отклонение
Коэффициент детерминации	Среднеквадратическое отклонение y
F - статистика	Число степеней свободы
Регрессионная сумма квадратов	Остаточная сумма квадратов

 

Результаты регрессионного анализа представлены на рис.1.7.

Рис. 1.7.Результаты регрессионного анализа

 

Индивидуальное задание к лабораторной работе №1

 

По предприятиям легкой промышленности региона получена информация, характеризующая зависимость объема выпуска продукции      ( y ,млн. руб.) от объема капиталовложений ( x , млн. руб.)

№		1	2	3	4	5	6	7	8	9	10
1	x	66	58	73	82	81	84	55	67	81	59
	y	133	107	145	162	163	170	104	132	159	116
2	x	72	52	73	74	76	79	54	68	73	64
	y	121	84	119	117	129	128	102	111	112	98
3	x	38	28	27	37	46	27	41	39	28	44
	y	69	52	46	63	73	48	67	62	47	67
4	x	36	28	43	52	51	54	25	37	51	29
	y	104	77	117	137	143	144	82	101	132	77
5	x	31	23	38	47	46	49	20	32	46	24
	y	38	26	40	45	51	49	34	35	42	24
6	x	33	17	23	17	36	25	39	20	13	12
	y	43	27	32	29	45	35	7	32	22	24
7	x	36	28	43	52	51	54	25	37	51	29
	y	85	60	99	117	118	125	56	86	115	68
8	x	17	22	10	7	12	21	14	7	20	3
	y	26	27	22	19	21	26	20	15	30	13
9	x	12	4	18	27	26	29	1	13	26	5
	y	21	10	26	33	34	37	9	21	32	14
10	x	26	18	33	42	41	44	15	27	41	19
	y	43	28	51	62	63	67	26	43	61	33
1 1	x	66	58	73	82	81	84	55	67	81	59
	y	133	107	145	162	163	170	104	132	159	116
1 2	x	72	52	73	74	76	79	54	68	73	64
	y	121	84	119	117	129	128	102	111	112	98
1 3	x	38	28	27	37	46	27	41	39	28	44
	y	69	52	46	63	73	48	67	62	47	67
1 4	x	36	28	43	52	51	54	25	37	51	29
	y	104	77	117	137	143	144	82	101	132	77
1 5	x	31	23	38	47	46	49	20	32	46	24
	y	38	26	40	45	51	49	34	35	42	24

 

⇐ Предыдущая12345678Следующая ⇒

Поделиться: