11 | 12 | 2017

Задача Многофакторная корреляция в EXCEL

Задача Многофакторная корреляция в EXCEL

1) Введите колонки данных – результативного признака и факторного, например:

2) Модель множественной корреляции может быть линейная, параболическая, экспоненциальная и т. д. В экономических расчетах (спрос, предложение, потребление) часто используют экспоненциальную зависимость.

Уравнение линейной множественной корреляции имеет вид:

У = а0 + а1х1 + а2х2 +…+аNXn

Уравнение экспоненты:

У = еах1 × еах2 ×× еаXn

3) Мышкой на пустом (незаполненном) пространстве Книги EXCEL выделите прямоугольник

4) На панели инструментов «кликните» иконку

(или на верхней панели инструментов)

5) Выберите нужную функцию:

6) После выбора функций высветится панель из 4-х строчек:

У

Выделите мышкой (курсором) фрагмент книги EXCEL, где содержатся значения результативного признака «У» В данном примере высветится А1 : А10

Х

Выделите мышкой (курсором) фрагмент книги EXCEL, где содержатся значения факторных признаков «Х» В примере высветится В1 : Е10

Константа

Введите слово «Истина»

Статистика

Введите слово «Истина»

7) Одновременно нажимаете клавиши:

8) В прямоугольнике, указанном в пункте 3 появятся числа – их интерпретация приводится ниже*:

Пример 18:

В качестве факторных признаков, влияющих на риск неурожая сельскохозяйственных культур были выбраны:

Сумма осадков в мм за период вегетации;

Сумма активных температур более 100С;

Внесение минеральных удобрений, кг/га

Внесение органических удобрений, т/га

Определите степень влияния указанных факторов на неурожай сельскохозяйственной культуры

Результативный признакСнижение урожайности культуры (в сопоставлении со средней урожайностью за предшествующие 5 лет), ц/га

Факторные признаки

Наблюдения

Сумма осадков в мм за период вегетации

Сумма активных температур более 100С

Внесение минеральных удобрений,

Ц. д. в-ва/га

Внесение органических удобрений, т/га

1.

2,5

410

2480

67

20

2.

3,0

400

2430

65

17

3.

5,0

380

2400

50

15

4.

10,0

320

2100

0

0

5.

7,5

360

2250

30

12

6.

11,0

300

2000

0

0

7.

4,5

385

2410

60

15

8.

1,5

420

2500

70

22

9.

6,0

370

2300

40

13

10.

8,0

350

2200

20

10

Ответ:

Решение данной достаточно сложной задачи можно провести пользуясь редактором электронных таблиц EXCEL. Методика, многофакторного корреляционного анализа, представленная выше, позволяет интерпретировать полученные в EXCEL табличные данные.

0,154864515

-0,061546

0,00616

-0,096911

28,0510678

0,091618027

0,025002

0,005945

0,029855

8,756971027

0,993513577

0,345283

#Н/Д

#Н/Д

#Н/Д

191,4602084

5

#Н/Д

#Н/Д

#Н/Д

91,30389769

0,596102

#Н/Д

#Н/Д

#Н/Д

Анализ подтверждает наличие тесной связи между потерями урожайности, неблагоприятными погодными условиями и внесением минеральных и органических удобрений. Коэффициент детерминации – 99,35%.

Линейное уравнение 4-х факторной корреляционной модели имеет следующий вид:

У = 28,05 – 0,097х1 + 0,006х2 – 0,062х3 + 0,155х4