Будь умным!


У вас вопросы?
У нас ответы:) SamZan.ru

Лабораторна робота 3 Проведення кореляційнорегресивного аналізу з допомогою Excel Методичні рекомендаці

Работа добавлена на сайт samzan.ru: 2016-03-30

Поможем написать учебную работу

Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.

Предоплата всего

от 25%

Подписываем

договор

Выберите тип работы:

Скидка 25% при заказе до 24.1.2022

Лабораторна робота №3

Проведення кореляційно-регресивного аналізу з допомогою Excel

Методичні рекомендації

Регресійний аналіз дозволяє отримати функціональну залежність між деякою випадковою величиною Y та деякими впливають на Y величинами X. Така залежність отримала назву рівняння регресії. Розрізняють просту (парну) і множинну регресію лінійного і нелінійного типу.

Приклад простої лінійної регресії:

y = m1x + b.

Приклад множинної лінійної регресії:

y = m1x1 + m2x2 + ... + Mkxk + b.     (1)

Для оцінки ступеня зв'язку між величинами використовується коефіцієнт множинної кореляції R Пірсона (кореляційне відношення), який може приймати значення від 0 до 1. R = 0 якщо між величинами немає ніякого зв'язку і R = 1, якщо між величинами є функціональна (детермінована) зв'язок. У більшості випадків R приймає проміжні значення від 0 до 1. Величина R2 називається коефіцієнтом детермінації.

Завданням побудови регресійної залежності є знаходження вектора коефіцієнтів M моделі (1) при якому коефіцієнт R приймає максимальне значення.

Для оцінки значущості R застосовується F-критерій Фішера, який вираховується за формулою:

де n - розмір вибірки (кількість експериментів); k - число коефіцієнтів моделі. Якщо F перевищує деяке критичне значення для даних n і k і прийнятої довірчої ймовірності, то величина R вважається істотною. Таблиці критичних значень F наводяться в довідниках з математичної статистики.

Таким чином, значимість R визначається не тільки його величиною, а й співвідношенням між кількістю експериментів і кількістю коефіцієнтів (параметрів) моделі. Дійсно, кореляційне відношення для n = 2 для простої лінійної моделі дорівнює 1 (через 2 точки на площині можна завжди провести єдину пряму). Однак, якщо експериментальні дані є випадковими величинами, довіряти такому значенню R слід з великою обережністю. Зазвичай для отримання значимого R і достовірної регресії прагнуть до того, щоб кількість експериментів суттєво перевищувала кількість коефіцієнтів моделі (n >> k).

Для побудови лінійної регресійної моделі необхідно:

1) підготувати список з n рядків і m стовпців, що містить експериментальні дані (стовпець, що містить вихідну величину y повинен бути або першим, або останнім у списку);

2) звернутися до меню Сервіс / Аналіз даних / Регресія

Якщо пункт "Аналіз даних" в меню "Сервіс" відсутня, то слід звернутися до пункту "Надбудови" того ж меню і встановити прапорець "Пакет аналізу".

3) в діалоговому вікні "Регресія" задати:

• вхідний інтервал Y;

• вхідний інтервал X;

• вихідний інтервал - верхня ліва клітинка інтервалу, в який будуть міститися результати обчислень (рекомендується розмістити на новому робочому аркуші);

 
4) натиснути "Ok" і проаналізувати результати.

Приклад використання множинної лінійної регресії

Припустимо, що забудовник оцінює вартість групи невеликих офісних будівель в традиційному діловому районі. (Див Рис)

Забудовник може використовувати множинний регресійний аналіз для оцінки ціни офісної будівлі в заданому районі на основі наступних змінних.

y - оцінна ціна будівлі під офіс;

x1 - загальна площа у квадратних метрах;

x2 - кількість офісів;

x3 - кількість входів (0,5 входу означає вхід тільки для доставки кореспонденції);

x4 - час експлуатації будівлі в роках.

У цьому прикладі передбачається, що існує лінійна залежність між кожною незалежною змінною (x1, x2, x3 та x4) і залежною змінною (y), тобто ціною будівлі під офіс в даному районі. Вихідні дані показані на малюнку.

Настройки для вирішення поставленого завдання показані на рисунку вікна "Регресія". Результати розрахунків розміщені на окремому аркуші в трьох таблицях

В результаті ми отримали наступну математичну модель:

y = 52318 + 27,64 * x1 + 12530 * x2 + 2553 * x3 - 234,24 * x4.

Тепер забудовник може визначити оціночну вартість будівлі під офіс в тому ж районі. Якщо цей будинок має площу 2500 квадратних метрів, три офіси, два входи і час експлуатації - 25 років, можна оцінити його вартість, використовуючи наступну формулу:

y = 27,64 * 2500 + 12 530 * 3 + 2 553 * 2 - 234,24 * 25 + 52 318 = 158 261 у.о.

У регресійному аналізі найбільш важливими результатами є:

• коефіцієнти при змінних і Y-перетин, які є шуканими параметрами моделі;

• множинний R, що характеризує точність моделі для наявних вихідних даних;

• F-критерій Фішера (в розглянутому прикладі він значно перевершує критичне значення, рівне 4,06);

• t-статистика - величини, що характеризують ступінь значущості окремих коефіцієнтів моделі.

На t-статистики слід зупинитися особливо. Дуже часто при побудові регресійної моделі невідомо, впливає той чи інший фактор x на y. Включення в модель факторів, які не впливають на вихідну величину, погіршує якість моделі. Обчислення t-статистики допомагає виявити такі чинники. Наближену оцінку можна зробити так: якщо при n >> k величина t-статистики за абсолютним значенням істотно більше трьох, відповідний коефіцієнт слід вважати значимим, а фактор включити в модель, інакше виключити з моделі. Таким чином, можна запропонувати технологію побудови регресійної моделі, що складається з двох етапів:

1) опрацювати пакетом "Регресія" всі наявні дані, проаналізувати значення t-статистики;

2) видалити з таблиці вихідних даних стовпці з тими факторами, для яких коефіцієнти незначущі і обробити пакетом "Регресія" нову таблицю.

Для прикладу розглянемо змінну x4. У довіднику з математичної статистики t-критичне с (nk-1) = 6 ступенями свободи і довірчою ймовірністю 0,95 одно 1,94. Оскільки абсолютна величина t, яка дорівнює 17,7 більше, ніж 1,94, термін експлуатації - це важлива змінна для оцінки вартості будівлі під офіс. Аналогічним чином можна протестувати всі інші змінні на статистичну значущість. Нижче наводяться спостережувані t-значення для кожної з незалежних змінних:

Загальна площа 5,1

Кількість офісів 31,3

Кількість входів 4,8

Термін експлуатації 17,7

Всі ці значення мають абсолютну величину більшу, ніж 1,94, отже, всі змінні, використані в рівнянні регресії, корисні для передбачення оціночної вартості будівлі під офіс в даному районі.

Завдання до самостійної роботи

Провести регресійний аналіз даних, проаналізувати вплив початкових даних на оціночні коефіцієнти регресійної моделі, і при необхідності перерахувати її.

Порядок виконання роботи

Проведення регресійного аналізу:

Створити лист з ім'ям «Регресійний аналіз»;

- Вибрати собі з пункту «Варіанти завдання до роботи» завдання для проведення регресійного аналізу. Номер свого варіанту відповідно до номеру академічного журналу;

- Провести математичне формулювання задачі у вигляді таблиці вихідних даних. Вибір вихідних даних відбувається по всіх рядках таблиці "Дані для проведення регресійного аналізу», а стовпці вибираються за таблицею «Регресійний аналіз» згідно з номером свого варіанта;

- Запустити пакет регресійного аналізу через меню «Сервис - Пакет аналізу - Регресія».

- У вікні «Регресія» задати інтервали, що містять Y і X. Результати обробки розташувати на аркуші «Регресійний аналіз»;

- Проаналізувати значення t-статистики для кожного коефіцієнта моделі. Якщо якесь значення не перевищує за абсолютним значенням граничного значення 3, то відповідний фактор X слід вважати не впливає суттєво на Y, виключити його з подальшої обробки і повторити «Регресійний аналіз».

Варіанти завдань до лабораторної роботи

вар.

Регресія

вар.

Регресія

y

x

y

x

1

Y1

X1-X4

16

Y6

X5-X8

2

Y2

X1-X4

17

Y7

X5-X8

3

Y3

X1-X4

18

Y8

X5-X8

4

Y4

X1-X4

19

Y9

X5-X8

5

Y5

X1-X4

20

Y10

X5-X8

6

Y6

X5-X8

21

Y1

X1-X4

7

Y7

X5-X8

22

Y2

X1-X4

8

Y8

X5-X8

23

Y3

X1-X4

9

Y9

X5-X8

24

Y4

X1-X4

10

Y10

X5-X8

25

Y5

X1-X4

11

Y1

X1-X4

26

Y6

X5-X8

12

Y2

X1-X4

27

Y7

X5-X8

13

Y3

X1-X4

28

Y8

X5-X8

14

Y4

X1-X4

29

Y9

X5-X8

15

Y5

X1-X4

30

Y10

X5-X8

Дані для проведення регресійного аналізу

x1

x2

x3

x4

x5

x6

x7

x8

y1

y2

y3

y4

y5

y6

y7

y8

y9

y10

1

4,4

8,5

2,4

3,7

2,2

7,4

3,7

9,9

28,0

32,4

23,5

11,5

15,2

22,2

30,0

28,6

27,2

3,5

2

8,3

6,4

2,5

5,8

5,7

8,7

2,6

6,1

41,7

21,5

14,7

25,1

18,4

32,4

18,8

40,4

29,6

19,2

3

9,5

4,4

7,3

1,9

4,1

5,3

4,6

1,2

32,1

31,8

21,4

33,5

20,1

21,4

18,3

23,0

20,8

16,3

4

1,6

9,6

3,2

9,7

4,1

9,2

8,7

4,4

28,7

31,0

29,5

4,3

26,9

28,3

30,3

33,6

26,9

16,5

5

7,4

3,7

0,7

9,9

2,4

2,5

5,8

5,1

49,5

6,3

7,3

27,3

23,3

11,6

30,2

10,3

10,8

7,5

6

1

0,9

0,6

4,1

5,4

3,9

4,0

7,0

19,5

4,4

7,5

11,1

13,1

19,6

27,2

24,4

13,9

15,9

7

2,6

4,2

0,8

2,7

8,7

4,1

3,1

8,1

18,5

14,5

11,5

12,0

9,4

27,6

25,5

33,0

18,4

24,0

8

6,7

7,8

5,0

4,9

7,7

0,4

3,9

3,4

32,9

32,5

24,1

21,3

23,1

18,5

24,8

18,9

6,4

27,5

9

8,2

6,8

7,0

6,9

6,2

6,2

9,6

3,4

38,4

34,4

25,6

24,6

30,6

27,2

37,3

26,5

16,3

22,7

10

5,5

7,6

8,2

8,9

1,6

2,8

5,0

7,9

33,3

36,4

30,3

17,2

39,1

11,5

32,9

11,8

12,5

5,5

11

7,3

4,9

4,6

0,6

7,5

5,9

4,2

4,5

24,9

29,6

18,5

23,4

12,2

30,1

20,9

33,6

21,3

26,9

12

2,5

3,4

3,4

8,1

5,9

8,6

8,5

4,0

29,6

13,9

16,5

10,9

26,8

32,8

32,4

35,3

24,4

21,5

13

1,9

0,2

9,9

0,5

3,2

8,5

2,6

4,8

6,6

26,5

24,3

11,9

22,2

27,6

16,1

35,0

29,5

12,5

14

5,8

4

7,1

9,5

0,0

3,6

8,0

2,0

37,8

23,0

22,9

23,0

37,3

11,1

32,2

8,3

10,9

3,1

15

5,1

1,7

7,6

4,8

7,5

2,9

3,5

0,7

27,1

21,2

20,6

20,3

27,5

24,8

15,7

25,6

13,8

27,4

16

3,6

1,4

0,8

8,9

0,1

4,8

2,3

0,3

37,7

3,6

7,1

18,6

22,6

11,8

8,0

15,9

21,7

5,1

17

4,6

3,5

1,5

4,4

9,8

6,9

8,6

4,8

28,6

13,1

10,1

17,1

15,5

34,6

35,5

37,1

19,6

29,9

18

5,5

5,5

9,5

5,9

3,2

2,0

7,6

4,0

26,7

33,1

32,3

17,9

35,4

13,0

35,2

10,8

8,1

12,3

19

6,4

9,6

9,4

2,5

0,3

5,8

7,7

1,7

22,1

51,7

38,9

16,1

26,5

14,4

26,7

16,3

17,7

5,0

20

3,8

2,4

9,3

3,7

7,1

5,8

7,2

5,1

15,7

26,5

27,1

15,5

29,5

28,0

32,0

27,4

17,7

22,0

21

3,4

6,3

6,9

8,6

1,7

6,5

9,2

4,0

28,8

27,4

27,2

10,9

34,9

19,9

35,9

19,6

18,9

6,4

22

5,3

6,6

4,3

0,3

4,0

2,8

7,0

7,5

20,9

31,4

20,8

19,0

13,2

16,7

39,2

15,3

8,1

10,0

23

3,7

7

6,4

0,5

9,9

4,9

4,8

7,9

15,4

36,9

27,9

13,6

18,0

34,6

29,6

36,3

17,2

27,7

24

9,3

1,3

8,6

0,4

3,3

5,4

5,6

2,8

28,7

26,0

17,5

36,2

20,4

20,9

23,8

23,3

19,6

12,6

25

5,2

4,6

2,0

0,4

6,4

8,3

9,4

9,8

21,7

20,4

13,7

18,0

9,1

32,1

43,6

31,3

22,7

16,3

26

4,3

5,1

7,0

9,7

7,6

6,2

7,9

2,6

33,5

25,8

27,3

16,7

35,1

30,0

28,8

31,8

20,1

27,7

27

5

2

2,5

2,2

2,5

0,6

5,2

9,5

24,8

12,4

9,9

22,0

11,8

8,2

39,0

6,0

3,1

3,5

28

8,6

0,7

1,3

1,2

1,1

0,5

2,0

2,5

34,9

7,1

2,6

31,7

8,2

7,9

14,9

6,6

8,9

8,3

29

1,3

4,4

7,5

2,7

7,0

7,1

4,6

9,2

9,2

32,3

26,6

8,7

25,1

30,7

32,7

36,2

26,1

18,2

30

9,3

9,7

1,3

6,9

3,6

6,0

5,7

1,9

47,3

31,0

19,5

26,0

21,4

23,3

19,3

25,8

20,3

14,7

31

2,3

8,6

7,3

8,7

2,0

3,4

5,6

8,7

25,7

36,1

34,3

8,1

34,5

13,6

37,3

15,6

12,3

3,6

32

8,5

5

1,9

1,2

3,2

3,4

1,5

9,5

35,9

23,0

10,8

27,4

8,2

18,0

24,3

18,4

16,0

6,6

33

5

1,9

7,2

1,7

1,1

4,4

4,1

7,0

20,4

23,1

18,1

19,8

21,9

15,1

28,6

16,3

16,0

1,9

34

2,2

3,2

5,5

2,0

0,7

6,1

1,6

6,0

13,5

25,6

21,7

12,0

18,7

17,3

16,6

24,7

23,8

1,9

35

5,4

4,1

0,9

9,9

8,7

1,4

2,7

4,0

41,3

8,7

11,2

20,0

23,6

22,9

22,4

25,6

11,1

30,6

36

7,2

3,7

0,4

3,0

6,0

5,8

0,9

8,9

35,6

14,5

8,8

27,5

9,2

24,6

19,2

35,1

23,0

16,3

37

2,9

8

6,3

9,7

0,8

7,6

4,6

2,2

29,6

32,6

30,0

8,4

34,6

19,0

18,3

25,9

25,0

7,2

38

1,3

5,6

2,7

8,8

6,8

3,1

4,2

1,5

26,4

17,5

23,0

8,2

25,7

23,2

18,6

25,1

15,0

24,4

39

3,6

5,3

4,2

2,1

4,7

7,7

8,7

2,3

17,0

27,3

22,6

14,1

14,8

29,2

29,5

29,6

21,7

18,9

40

1,9

0,8

6,7

0,4

3,4

9,9

4,6

5,6

6,3

20,0

19,0

12,4

15,5

30,0

22,3

38,4

33,0

12,3

41

2,7

0,5

6,9

3,5

8,7

7,3

3,9

6,7

16,7

17,6

18,0

15,4

24,9

36,4

24,6

41,8

24,2

27,8

42

6,3

1,1

5,7

6,7

2,3

3,6

9,7

1,7

34,0

13,9

13,8

24,3

28,1

13,3

33,4

12,6

8,0

12,1

43

3,5

6,9

6,8

0,7

1,4

3,7

6,6

3,0

11,6

36,8

28,7

11,0

19,2

14,4

27,8

11,0

11,5

9,8

44

9,1

3

4,7

2,1

0,6

3,0

5,9

4,6

35,6

20,6

14,0

30,3

16,4

8,6

29,4

10,5

12,9

3,0

45

7,2

0,9

0,4

6,4

6,8

9,7

4,2

7,4

40,8

2,2

0,0

28,7

14,6

37,8

24,8

42,1

31,1

18,2

46

3,1

0,2

8,2

0,6

9,6

9,7

9,2

5,4

10,0

22,4

21,4

18,2

19,2

40,0

33,7

42,2

27,1

30,9

47

0,5

9,3

8,4

4,4

8,4

3,2

9,8

6,8

9,2

45,5

42,1

2,0

30,1

25,5

44,4

23,6

7,9

25,1

48

8,7

0,7

1,7

5,2

9,3

5,8

6,7

7,2

44,7

6,4

3,1

31,4

18,7

32,7

36,3

32,3

18,1

26,5

49

7,6

2,6

1,5

4,1

8,4

7,3

0,8

0,8

36,6

11,0

8,1

27,7

13,7

34,7

3,8

44,4

29,3

31,3

50

8,4

6,6

4,5

2,8

1,7

6,2

5,8

9,7

35,5

32,7

20,8

25,5

17,2

19,0

36,3

20,1

21,7

3,5

ЛІТЕРАТУРА

1.

Ткач Є.І., Сторожук В.П. Загальна теорія статистики: Підручник (для студ. вищ. навч. закл.). – К.: Центр учбової літератури, 2009. – 442 с.

2.

Уманець Т.В. Загальна теорія статистики:  Навч.  посіб.  –  К.:  Знання, 2006.    – 239 с.

3.

Захожай В.Б., Попов І.І. Статистика: Підруч. для студ. вищ. навч. закл. – К.: МАУП, 2006. – 536 с.

4.

Фещур Р.В., Барвінський А.Ф., Кічор В.П. Статистика: Навчальний посібник     / За наук. ред. Р.В.Фещура. – 3-є вид. оновлене і доповнене. – Львів: Інтелект-Захід, 2006. – 256 с.

5.

Ткач Є.І., Сторожук В.П., Кустовська О.В., Шост І.М. та ін. Курс лекцій з дисципліни «Статистика». Частина 1. Теорія статистики. – Тернопіль: Економічна думка, 2008. – 220 с.

6.

Кустовська О.В., Матійчук Л.П., Солтис В.В., Ціщик Р.В.,Чорний В.С. Практикум із дисципліни «Статистика» з використанням Excel – Тернопіль: ТНЕУ, 2009. – 216 с.

7.

Притула М.М., Онишко О.Є. Практикум із теорії статистки: Навчальний посібник. – Львів: Компакт-ЛВ, 2006. – 224 с.

8.

Артеменко В.Б. Моделювання і прогнозування економічних рядів динаміки: Навч. посібник. – Львів: Вид-во Львівської комерційної академії, 2003. – 228 с.

9.

Опря А.Т. Статистика (з програмованою формою контролю знань).   Математична статистика. Теорія статистики. Навчальний посібник. – Київ: Центр навчальної літератури, 2005. – 472 с.

10.

Єрина А.М., Пальян З.О. Теорія статистика: Практикум. – 7-е вид., стер. – К.: Знання, 2009. – 255 с. – (Вища освіта ХХІ століття).

11.

Теория статистики: Учебник. / Р.А. Шмойлова, В.Г. Минишкин, Н.А. Садовникова, Е.Б. Шувалова. – М.: Финансы и статистика, 2003. – 656 с.

12.

Моторин Р.М., Чекотовський Е.В. Статистика. Збірник індивідуальних завдань з використанням Excel: Навч. метод. посібн. для самост. вивч. дисц. – К.: КНЕУ, 2005. – 268 с.

13.

Деловая графика (Учебный курс). – Харьков: Фолио, 2002. – 389 с.

14.

Хабрейкен, Джо. Изучи Microsoft Excel 2002 за 10 минут: Пер. с англ. – М.: Издательский дом «Вильямс», 2002. – 192 с.: ил.




1. Методические рекомендации для студентов 1 курса заочной формы обучения юридического факультета Специально
2. Створення найпростііших прогграм засобами С-С++
3. Ликин. Судя по книжке в руках и быстрой походке похоже на то что ты спешишь к учителю Гермотим
4. Лекция 16 ФИЛОСОФИЯ БЫТИЯ часть 2
5. ПРМЗ на собственном заготовительном оборудовании оказывает услуги по изготовлению заготовок из металла
6. Контрольная работа- Формирование целей организации - кондитерский цех
7. История философии 1
8. Тематика контрольных работ по педагогике Педагогика как наука ее объект
9. Реферат- Операционный бенчмаркетинг
10. Мобильный банк [9] 2
11. Республика Хакасия
12. Економіка Політична економія Предмет політичної економії
13. ТЕМАХ З ПІСЛЯДІЄЮ
14. Утверждаю учителей начальных классов Директор МО
15. . супруг который не знал об обстоятельствах препятствующих заключению брака; 2
16. Расчет себестоимости продукции
17. Культура Древнего Египта
18. Неокорпоративизм как система функционального представительства интересов в современной России
19. Европейский парламент.html
20. Эпидемия Терраграмма