Задача Многофакторная корреляция в 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
