microbik.ru
1 2 3

Лабораторная работа № 2

Автоматизированный корреляционно-регрессионный анализ взаимосвязи статистических данных в среде MS Excel

Задание 1. Построение аналитической группировки для выявления

корреляционной зависимости результативного признака от факторного и оценка тесноты взаимосвязи признаков

Задание 2. Построение однофакторной линейной регрессионной модели связи изучаемых признаков с помощью инструмента Регрессия надстройки Пакет анализа

Задание 3. Построение однофакторных нелинейных регрессионных моделей связи признаков с помощью инструмента Мастер диаграмм и выбор наиболее адекватного уравнения регрессии

Порядок выполнения лабораторной работы


Для выполнения Лабораторной работы №2 выделяется Лист 2 рабочего файла, сформированного в персональной папке студента при выполнении Лабораторной работы №1, и используется следующая информация из Лабораторной работы №1:

  • исходные данные – Таблица 1, полученная после удаления аномальных значений (А4:С33);

  • интервальный ряд распределения единиц совокупности по факторному признаку ХСреднегодовая стоимость основных производственных фондов из табл. 7 (А102:В106);

  • диаграмма рассеяния, расположенная начиная с ячейки F4.

I. Подготовительный этап


На данном этапе студент должен скопировать необходимую информацию из Лабораторной работы №1 на Лист 2 рабочего файла персональной папки ФИО.

На Листе 2 рабочего файла персональной папки студента заготовлены макеты таблиц, используемые при выполнении Лабораторной работы №2.

Для записи необходимой информации в рабочий и отчетный файлы персональной папки необходимо скопировать данные из Листа 1 в Лист 2 рабочего файла в соответствии с нижеследующей таблицей:

Лист 1 Лист 2

Номер

таблицы

Содержимое

таблицы

Адресация

содержимого




Номер

таблицы

Содержимое

таблицы

Адресация

содержимого

Табл. 1

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

A4:С33




Табл. 2.1

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

A4:С33



Диаграмма рассеяния копируется дважды

Начиная с ячейки F4

Копировать в




Диаграмма рассеяния

Начиная с ячейки Е4 и Е20

Табл. 7

Интервальный ряд распределения факторного признака Х

А102: В106



Табл. 2.2

и

табл. 2.3

Интервальный ряд распределения факторного признака Х

B41:C45

и

B52:C56

Расположение макетов результативных таблиц в рабочем файле на Листе 2 персональной папки студента




A

B

C

D

E

1







Таблица 2.1







2

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







3

Номер предприятия

Среднегодовая стоимость основных производственных фондов, млн. руб.

Выпуск продукции, млн. руб.







4
















5
















6
















7
















8
















9
















10
















11
















12
















13
















14
















15
















16
















17
















18
















19
















20
















21
















22
















23
















24
















25
















26
















27
















28
















29
















30
















31
















32
















33
















34
















35
















36




















A

B

C

D

E

37













Таблица 2.2

39

Зависимость выпуска продукции от среднегодовой стоимости основных фондов

39

Номер группы

Группы предприятий по среднегодовой стоимости основных производственных фондов, млн. руб.

Число предприятий

Выпуск продукции, млн. руб.

40

Всего

В среднем
на одно
предприятие

41

1

 

 

СУММ() 

D41/C41

42

2

 

 

СУММ() 

D42/C42

43

3

 

 

СУММ() 

D43/C43

44

4

 

 

СУММ() 

D44/C44

45

5

 

 

СУММ() 

D45/C45

46

Итого

 

СУММ(C41:C45)

СУММ(D41:D45)

D46/C46

47
















48
















49










Таблица 2.3




50

Показатели внутригрупповой вариации




51

Номер группы

Группы предприятий по среднегодовой стоимости основных производственных фондов, млн. руб.

Число предприятий

Внутригрупповая дисперсия




52

1

 

 

ДИСПР()




53

2

 

 

ДИСПР()




54

3

 

 

ДИСПР()




55

4

 

 

ДИСПР()




56

5

 

 

ДИСПР()




57

Итого

 

СУММ(C52:C56)

СУММ(D52:D56)




58
















59
















60










Таблица 2.4




61

Показатели дисперсии и эмпирического корреляционного отношения




62

Общая дисперсия

Средняя из внутригрупповых дисперсий

Межгрупповая дисперсия

Эмпирическое корреляционное отношение




63

ДИСПР(C4:C33)

СУММПРОИЗВ (D52:D56,C52:C56)/C46

A63-B63

C63/A63




64
















65
















66







Таблица 2.5







67

Линейный коэффициент корреляции признаков







68

 

Столбец1

Столбец2







69

Столбец1

 

 







70

Столбец2

 

 







71
















72




















A

B

C

D

E

F

73

Выходные таблицы










74



















75

ВЫВОД ИТОГОВ
















76



















77

Регрессионная статистика













78

Множественный R

 













79

R-квадрат

 













80

Нормированный R-квадрат

 













81

Стандартная ошибка

 













82

Наблюдения

 













83



















84

Дисперсионный анализ
















85

 

df

SS

MS

F

Значимость F

86

Регрессия

 

 

 

 

 

87

Остаток

 

 

 

 

 

88

Итого

 

 

 

 

 

89

























A

B

C

D

E

F

G

H

I

90

 

Коэффициенты

Стандарт-ная ошибка

t-ста-тис-тика

P-Зна-чение

Нижние 95%

Верхние 95%

Нижние 68,3%

Верхние 68,3%

91

Y-пересечение

 

 

 

 

 

 

 

 

92

Переменная X 1

 

 

 

 

 

 

 

 

93




























94




























95




























96

ВЫВОД ОСТАТКА






















97




























98

Наблюдение

Предсказанное Y

Остатки



















99
























































































































следующая страница >>