Матрица парной корреляции. Построить матрицу парных коэффициентов корреляции
Коэффициент корреляции отражает степень взаимосвязи между двумя показателями. Всегда принимает значение от -1 до 1. Если коэффициент расположился около 0, то говорят об отсутствии связи между переменными.
Если значение близко к единице (от 0,9, например), то между наблюдаемыми объектами существует сильная прямая взаимосвязь. Если коэффициент близок к другой крайней точке диапазона (-1), то между переменными имеется сильная обратная взаимосвязь. Когда значение находится где-то посередине от 0 до 1 или от 0 до -1, то речь идет о слабой связи (прямой или обратной). Такую взаимосвязь обычно не учитывают: считается, что ее нет.
Расчет коэффициента корреляции в Excel
Рассмотрим на примере способы расчета коэффициента корреляции, особенности прямой и обратной взаимосвязи между переменными.
Значения показателей x и y:
Y – независимая переменная, x – зависимая. Необходимо найти силу (сильная / слабая) и направление (прямая / обратная) связи между ними. Формула коэффициента корреляции выглядит так:
Чтобы упростить ее понимание, разобьем на несколько несложных элементов.
Между переменными определяется сильная прямая связь.
Встроенная функция КОРРЕЛ позволяет избежать сложных расчетов. Рассчитаем коэффициент парной корреляции в Excel с ее помощью. Вызываем мастер функций. Находим нужную. Аргументы функции – массив значений y и массив значений х:
Покажем значения переменных на графике:
Видна сильная связь между y и х, т.к. линии идут практически параллельно друг другу. Взаимосвязь прямая: растет y – растет х, уменьшается y – уменьшается х.
Матрица парных коэффициентов корреляции в Excel
Корреляционная матрица представляет собой таблицу, на пересечении строк и столбцов которой находятся коэффициенты корреляции между соответствующими значениями. Имеет смысл ее строить для нескольких переменных.
Матрица коэффициентов корреляции в Excel строится с помощью инструмента «Корреляция» из пакета «Анализ данных».
Между значениями y и х1 обнаружена сильная прямая взаимосвязь. Между х1 и х2 имеется сильная обратная связь. Связь со значениями в столбце х3 практически отсутствует.
Коллинеарными являются факторы …
И коллинеарны.
4. В модели множественной регрессии определитель матрицы парных коэффициентов корреляции между факторами , и близок к нулю. Это означает, что факторы , и … мультиколлинеарность факторов.
5. Для эконометрической модели линейного уравнения множественной регрессии вида построена матрица парных коэффициентов линейной корреляции (y – зависимая переменная; х (1) , х (2) , х (3) , x (4) – независимые переменные):
Коллинеарными (тесно связанными) независимыми (объясняющими) переменными не являются
…x (2)
и x (3)
1. Дана таблица исходных данных для построения эконометрической регрессионной модели:
Фиктивными переменными не являются
…
стаж работы
производительность труда
2. При исследовании зависимости потребления мяса от уровня дохода и пола потребителя можно рекомендовать …
использовать фиктивную переменную – пол потребителя
разделить совокупность на две: для потребителей женского пола и для потребителей мужского пола
3. Изучается зависимость цены квартиры (у
) от ее жилой площади (х
) и типа дома. В модель включены фиктивные переменные, отражающие рассматриваемые типы домов: монолитный, панельный, кирпичный. Получено уравнение регрессии: ,
где 
, 
Частными уравнениями регрессии для кирпичного и монолитного являются …
для типа дома кирпичный 
для типа дома монолитный 
4. При анализе промышленных предприятий в трех регионах (Республика Марий Эл, Республика Чувашия, Республика Татарстан) были построены три частных уравнения регрессии:
для Республики Марий Эл;
для Республики Чувашия;
для Республики Татарстан.
Укажите вид фиктивных переменных и уравнение с фиктивными переменными, обобщающее три частных уравнения регрессии.
5. В эконометрике фиктивной переменной принято считать …
переменную, принимающую значения 0 и 1
описывающую количественным образом качественный признак
1. Для регрессионной модели зависимости среднедушевого денежного дохода населения (руб., у ) от объема валового регионального продукта (тыс. р., х 1 ) и уровня безработицы в субъекте (%, х 2 ) получено уравнение . Величина коэффициента регрессии при переменной х 2 свидетельствует о том, что при изменении уровня безработицы на 1% среднедушевой денежный доход ______ рубля при неизменной величине валового регионального продукта.
изменится на (-1,67)
2. В уравнении линейной множественной регрессии:
, где – стоимость основных фондов (тыс. руб.); – численность занятых (тыс. чел.); y
– объем промышленного производства (тыс. руб.) параметр при переменной х 1
, равный 10,8, означает, что при увеличении объема основных фондов на _____ объем промышленного производства _____ при постоянной численности занятых.
на 1 тыс. руб. … увеличится на 10,8 тыс. руб.
3. Известно, что доля остаточной дисперсии зависимой переменной в ее общей дисперсии равна 0,2. Тогда значение коэффициента детерминации составляет … 0,8
4. Построена эконометрическая модель для зависимости прибыли от реализации единицы продукции (руб., у ) от величины оборотных средств предприятия (тыс. р., х 1 ): . Следовательно, средний размер прибыли от реализации, не зависящий от объема оборотных средств предприятия, составляет _____ рубля. 10,75
5. F-статистика рассчитывается как отношение ______ дисперсии к ________ дисперсии, рассчитанных на одну степень свободы. факторной … остаточной
1. Для эконометрической модели уравнения регрессии ошибка модели определяется как ______ между фактическим значением зависимой переменной и ее расчетным значением. Разность
2. Величина называется … случайной составляющей
3. В эконометрической модели уравнения регрессии величина отклонения фактического значения зависимой переменной от ее расчетного значения характеризует … ошибку модели
4. Известно, что доля объясненной дисперсии в общей дисперсии равна 0,2. Тогда значение коэффициента детерминации составляет … 0,2
5. При методе наименьших квадратов параметры уравнения парной линейной регрессии 
определяются из условия ______ остатков .
минимизации суммы квадратов
1. Для обнаружения автокорреляции в остатках используется …
статистика Дарбина – Уотсона
2. Известно, что коэффициент автокорреляции остатков первого порядка равен –0,3. Также даны критические значения статистики Дарбина – Уотсона для заданного количества параметров при неизвестном и количестве наблюдений , . По данным характеристикам можно сделать вывод о том, что …автокорреляция остатков отсутствует
| y | x (1) | x (2) | x (3) | x (4) | x (5) | |
| y | 1.00 | 0.43 | 0.37 | 0.40 | 0.58 | 0.33 |
| x (1) | 0.43 | 1.00 | 0.85 | 0.98 | 0.11 | 0.34 |
| x (2) | 0.37 | 0.85 | 1.00 | 0.88 | 0.03 | 0.46 |
| x (3) | 0.40 | 0.98 | 0.88 | 1.00 | 0.03 | 0.28 |
| x (4) | 0.58 | 0.11 | 0.03 | 0.03 | 1.00 | 0.57 |
| x (5) | 0.33 | 0.34 | 0.46 | 0.28 | 0.57 | 1.00 |
Анализ матрицы парных коэффициентов корреляции показывает, что результативный показатель наиболее тесно связан с показателем x (4) - количество удобрений, расходуемых на 1 га ().
В то же время связь между признаками-аргументами достаточно тесная. Так, существует практически функциональная связь между числом колесных тракторов (x
(1)) и числом орудий поверхностной обработки почвы 
.
О наличии мультиколлинеарности свидетельствуют также коэффициенты корреляции и . Учитывая тесную взаимосвязь показателей x (1) , x (2) и x (3) , в регрессионную модель урожайности может войти лишь один из них.
Чтобы продемонстрировать отрицательное влияние мультиколлинеарности, рассмотрим регрессионную модель урожайности, включив в нее все исходные показатели:
F набл = 121.
В скобках указаны значения исправленных оценок среднеквадратических отклонений оценок коэффициентов уравнения 
.
Под уравнением регрессии представлены следующие его параметры адекватности: множественный коэффициент детерминации ; исправленная оценка остаточной дисперсии , средняя относительная ошибка аппроксимации и расчетное значение -критерия F набл = 121.
Уравнение регрессии значимо, т.к. F набл = 121 > F kp = 2,85 найденного по таблице F -распределения при a=0,05; n 1 =6 и n 2 =14.
Из этого следует, что Q¹0, т.е. и хотя бы один из коэффициентов уравнения q j (j = 0, 1, 2, ..., 5) не равен нулю.
Для проверки гипотезы о значимости отдельных коэффициентов регрессии H0: q j =0, где j =1,2,3,4,5, сравнивают критическое значение t kp = 2,14, найденное по таблице t -распределения при уровне значимости a=2Q =0,05 и числе степеней свободы n=14, с расчетным значением . Из уравнения следует, что статистически значимым является коэффициент регрессии только при x (4) , так как ½t 4 ½=2,90 > t kp =2,14.
Не поддаются экономической интерпретации отрицательные знаки коэффициентов регрессии при x (1) и x (5) . Из отрицательных значений коэффициентов следует, что повышение насыщенности сельского хозяйства колесными тракторами (x (1)) и средствами оздоровления растений (x (5)) отрицательно сказывается на урожайности. Таким образом, полученное уравнение регрессии неприемлемо.
Для получения уравнения регрессии со значимыми коэффициентами используем пошаговый алгоритм регрессионного анализа. Первоначально используем пошаговый алгоритм с исключением переменных.
Исключим из модели переменную x (1) , которой соответствует минимальное по абсолютной величине значение ½t 1 ½=0,01. Для оставшихся переменных вновь построим уравнение регрессии:
Полученное уравнение значимо, т.к. F набл = 155 > F kp = 2,90, найденного при уровне значимости a=0,05 и числах степеней свободы n 1 =5 и n 2 =15 по таблице F -распределения, т.е. вектор q¹0. Однако в уравнении значим только коэффициент регрессии при x (4) . Расчетные значения ½t j ½ для остальных коэффициентов меньше t кр = 2,131, найденного по таблице t -распределения при a=2Q =0,05 и n=15.
Исключив из модели переменную x (3) , которой соответствует минимальное значение t 3 =0,35 и получим уравнение регрессии:
(2.9)
В полученном уравнении статистически не значим и экономически не интерпретируем коэффициент при x (5) . Исключив x (5) получим уравнение регрессии:
(2.10)
Мы получили значимое уравнение регрессии со значимыми и интерпретируемыми коэффициентами.
Однако полученное уравнение является не единственно “хорошей” и не “самой лучшей” моделью урожайности в нашем примере.
Покажем, что в условии мультиколлинеарности пошаговый алгоритм с включением переменных является более эффективным. На первом шаге в модель урожайности y входит переменная x (4) , имеющая самый высокий коэффициент корреляции с y , объясняемой переменной -r (y , x (4))=0,58. На втором шаге, включая уравнение наряду с x (4) переменные x (1) или x (3) , мы получим модели, которые по экономическим соображениям и статистическим характеристикам превосходят (2.10):
(2.11)
(2.12)
Включение в уравнение любой из трех оставшихся переменных ухудшает его свойства. Смотри, например, уравнение (2.9).
Таким образом, мы имеем три “хороших” модели урожайности, из которых нужно выбрать по экономическим и статистическим соображениям одну.
По статистическим критериям наиболее адекватна модель (2.11). Ей соответствуют минимальные значения остаточной дисперсии =2,26 и средней относительной ошибки аппроксимации и наибольшие значения и F набл = 273.
Несколько худшие показатели адекватности имеет модель (2.12), а затем - модель (2.10).
Будем теперь выбирать наилучшую из моделей (2.11) и (2.12). Эти модели отличаются друг от друга переменными x (1) и x (3) . Однако в моделях урожайностей переменная x (1) (число колесных тракторов на 100 га) более предпочтительна, чем переменная x (3) (число орудий поверхностной обработки почвы на 100 га), которая является в некоторой степени вторичной (или производной от x (1)).
В этой связи из экономических соображений предпочтение следует отдать модели (2.12). Таким образом, после реализации алгоритма пошагового регрессионного анализа с включением переменных и учета того, что в уравнение должна войти только одна из трех связанных переменных (x (1) , x (2) или x (3)) выбираем окончательное уравнение регрессии:
Уравнение значимо при a=0,05, т.к. F набл = 266 > F kp = 3,20, найденного по таблице F -распределения при a=Q =0,05; n 1 =3 и n 2 =17. Значимы и все коэффициенты регрессии и в уравнении ½t j ½>t kp (a=2Q =0,05; n=17)=2,11. Коэффициент регрессии q 1 следует признать значимым (q 1 ¹0) из экономических соображений, при этом t 1 =2,09 лишь незначительно меньше t kp = 2,11.
Из уравнения регрессии следует, что увеличение на единицу числа тракторов на 100 га пашни (при фиксированном значении x (4)) приводит к росту урожайности зерновых в среднем на 0,345 ц/га.
Приближенный расчет коэффициентов эластичности э 1 »0,068 и э 2 »0,161 показывает, что при увеличении показателей x (1) и x (4) на 1% урожайность зерновых повышается в среднем соответственно на 0,068% и 0,161%.
Множественный коэффициент детерминации свидетельствует о том, что только 46,9% вариации урожайности объясняется вошедшими в модель показателями (x
(1) и x
(4)), то есть насыщенностью растениеводства тракторами и удобрениями. Остальная часть вариации обусловлена действием неучтенных факторов (x
(2) , x
(3) , x
(5) , погодные условия и др.). Средняя относительная ошибка аппроксимации характеризует адекватность модели, так же как и величина остаточной дисперсии . При интерпретации уравнения регрессии интерес представляют значения относительных ошибок аппроксимации 
. Напомним, что - модельное значение результативного показателя, характеризует среднее для совокупности рассматриваемых районов значение урожайности при условии, что значения объясняющих переменных x
(1) и x
(4) зафиксированы на одном и том же уровне, а именно x
(1) = x i
(1) и x
(4) = x i
(4) . Тогда по значениям d i
можно сопоставлять районы по урожайности. Районы, которым соответствуют значения d i
>0, имеют урожайность выше среднего, а d i
<0 - ниже среднего.
В нашем примере, по урожайности наиболее эффективно растениеводство ведется в районе, которому соответствует d 7 =28%, где урожайность на 28% выше средней по региону, и наименее эффективно - в районе с d 20 =-27,3%.
Задачи и упражнения
2.1. Из генеральной совокупности (y , x (1) , ..., x (p)), где y имеет нормальный закон распределения с условным математическим ожиданием и дисперсией s 2 , взята случайная выборка объемом n , и пусть (y i , x i (1) , ..., x i (p)) - результат i -го наблюдения (i =1, 2, ..., n ). Определить: а) математическое ожидание МНК-оценки вектора q ; б) ковариационную матрицу МНК-оценки вектора q ; в) математическое ожидание оценки .
2.2. По условию задачи 2.1 найти математическое ожидание суммы квадратов отклонений, обусловленных регрессией, т.е. EQ R , где
.
2.3. По условию задачи 2.1 определить математическое ожидание суммы квадратов отклонений, обусловленных остаточной вариацией относительно линий регрессии, т.е. EQ ост, где
2.4. Доказать, что при выполнении гипотезы Н 0: q=0 статистика
имеет F-распределение с числами степеней свободы n 1 =p+1 и n 2 =n-p-1.
2.5. Доказать, что при выполнении гипотезы Н 0: q j =0 статистика имеет t-распределение с числом степеней свободы n=n-p-1.
2.6. На основании данных (табл.2.3) о зависимости усушки кормового хлеба (y ) от продолжительности хранения (x ) найти точечную оценку условного математического ожидания в предположении, что генеральное уравнение регрессии - линейное.
Таблица 2.3.
Требуется: а) найти оценки и остаточной дисперсии s 2 в предположении, что генеральное уравнение регрессии имеет вид ; б) проверить при a=0,05 значимость уравнения регрессии, т.е. гипотезу Н 0: q=0; в) с надежностью g=0,9 определить интервальные оценки параметров q 0 , q 1 ; г) с надежностью g=0,95 определить интервальную оценку условного математического ожидания при х 0 =6; д) определить при g=0,95 доверительный интервал предсказания в точке х =12.
2.7. На основании данных о динамике темпов прироста курса акций за 5 месяцев, приведенных в табл. 2.4.
Таблица 2.4.
| месяцы (x ) | |||||
| y (%) |
и предположения, что генеральное уравнение регрессии имеет вид , требуется: а) определить оценки и параметров уравнения регрессии и остаточной дисперсии s 2 ; б) проверить при a=0,01 значимость коэффициента регрессии, т.е. гипотезы H 0: q 1 =0;
в) с надежностью g=0,95 найти интервальные оценки параметров q 0 и q 1 ; г) с надежностью g=0,9 установить интервальную оценку условного математического ожидания при x 0 =4; д) определить при g=0,9 доверительный интервал предсказания в точке x =5.
2.8. Результаты исследования динамики привеса молодняка приведены в табл.2.5.
Таблица 2.5.
Предполагая, что генеральное уравнение регрессии - линейное, требуется: а) определить оценки и параметров уравнения регрессии и остаточной дисперсии s 2 ; б) проверить при a=0,05 значимость уравнения регрессии, т.е. гипотезы H 0: q=0;
в) с надежностью g=0,8 найти интервальные оценки параметров q 0 и q 1 ; г) с надежностью g=0,98 определить и сравнить интервальные оценки условного математического ожидания при x 0 =3 и x 1 =6;
д) определить при g=0,98 доверительный интервал предсказания в точке x =8.
2.9. Себестоимость (y ) одного экземпляра книги в зависимости от тиража (x ) (тыс.экз.) характеризуется данными, собранными издательством (табл.2.6). Определить МНК-оценки и параметров уравнения регрессии гиперболического вида , с надежностью g=0,9 построить доверительные интервалы для параметров q 0 и q 1 , а также условного математического ожидания при x =10.
Таблица 2.6.
Определить оценки и параметров уравнения регрессии вида , проверить при a=0,05 гипотезу Н 0: q 1 =0 и построить с надежностью g=0,9 доверительные интервалы для параметров q 0 и q 1 и условного математического ожидания при x =20.
2.11. В табл. 2.8 представленные данные о темпах прироста (%) следующих макроэкономических показателей n =10 развитых стран мира за 1992г.: ВНП - x (1) , промышленного производства - x (2) , индекса цен - x (3) .
Таблица 2.8.
| Страны | x
и параметров уравнения регрессии, оценку остаточной дисперсии; б) проверить при a=0,05 значимость коэффициента регрессии, т.е. Н 0: q 1 =0; в) с надежностью g=0,9 найти интервальные оценки q 0 и q 1 ; г) найти при g=0,95 доверительный интервал для в точке х
0 =х i
, где i
=5; д) сравнить статистические характеристики уравнений регрессий: 1, 2 и 3.
2.12. Задачу 2.11 решить, приняв за объясняемую величину (у ) показатель x (1) , а за объясняющую (х ) переменную x (3) . 1. Айвазян С.А., Мхитарян В.С. Прикладная статистика и основы эконометрики: Учебник. М., ЮНИТИ, 1998 (2-е издание 2001); 2. Айвазян С.А., Мхитарян В.С. Прикладная статистика в задачах и упражнениях: Учебник. М. ЮНИТИ – ДАНА, 2001; 3. Айвазян С.А., Енюков И.С., Мешалкин Л.Д. Прикладная статистика. Исследование зависимостей. М., Финансы и статистика, 1985, 487с.; 4. Айвазян С.А., Бухштабер В. М., Енюков И.С., Мешалкин Л.Д. Прикладная статистика. Классификация и снижение размерностей. М., Финансы и статисика, 1989, 607с.; 5. Джонстон Дж. Эконометрические методы, М.: Статистика, 1980, 446с.; 6. Дубров А.В., Мхитарян В.С., Трошин Л.И. Многомерные статистические методы. М., Финансы и статистика, 2000; 7. Мхитарян В.С., Трошин Л.И. Исследование зависимостей методами корреляции и регрессии. М., МЭСИ, 1995, 120с.; 8. Мхитарян В.С., Дубров А.М., Трошин Л.И. Многомерные статистические методы в экономике. М., МЭСИ, 1995, 149с.; 9. Дубров А.М., Мхитарян В.С., Трошин Л.И. Математическая статистика для бизнесменов и менеджеров. М., МЭСИ, 2000, 140с.; 10. Лукашин Ю.И. Регрессионные и адаптивные методы прогнозирования: Учебное пособие, М., МЭСИ, 1997. 11. Лукашин Ю.И. Адаптивные методы краткосрочного прогнозирования. ‑ М., Статистика, 1979. ПРИЛОЖЕНИЯ Приложение 1 . Варианты заданий для самостоятельных компьютерных исследований. |
Контрольная работа №2
Вариант№5
Задание1. Используя компьютерные технологии, провести корреляционно-регрессионный анализ исследуемых экономических показателей и построить регрессионную модель………………………..…..3
1.1 Построение корреляционного поля ………………………………………4
1.2 Построение матрицы коэффициентов парной корреляции……………6
1.3 Построение и анализ однофакторных регрессионных моделей линейного и экспонентного вида средствами встроенных функций ТП MS Excel…………………………………………………………………………...6
1.4 Построение линейной однофакторной регрессионной модели……….10
1.5 Выводы………………………………………………………………………15
Задание 2. Используя компьютерные технологии, решить задачи линейного программирования……………………………………………….18
а) Задача оптимального планирования производства……………….19
1. Математическую постановку задачи……………………………………..19
2. Размещение на рабочем листе ТП MS Excel исходных данных, расчёт значений ограничений, расчёт значений целевой функции……………...19
3. Формулировка математической модели задачи в терминах ячеек рабочего листа ТП MS Excel…………………………………………………..20
4. Поиск оптимального решения поставленной задачи средствами надстройки «Поиск решения»………………………………………………..20
5. Анализ результатов………………………………………………………….21
б) Задача оптимизации плана перевозок (транспортная задача)…23
1. Математическую постановку задачи……………………………………..23
2. Размещение данных на рабочем листе ТП MS Excel …………………...24
3. Постановка задачи в терминах рабочего листа Excel для использования утилиты «Поиск решения»….…………………………25
4. Анализ результатов………………………………………………………….26
Список использованной литературы………………………………………..28
Задание 1. Используя компьютерные технологии, провести корреляционно-регрессионный анализ исследуемых экономических показателей и построить регрессионную модель.
В качестве инструментария исследования использовать:
Инструменты надстройки Пакет Анализа ТП MS Excel;
Встроенные функции библиотеки Stats (Statistics) CKM Maple.
Условия задания 1:
По выборочным данным исследовать влияние факторов X1, X2 и Х3 на результативный признак Y.
Построить корреляционное поле и сделать предположение о наличии и типе связи между исследуемыми факторами;
Оценив тесноту связи между исследуемыми факторами, построить многофакторную (однофакторную) линейную регрессионную модель вида Y=f(X1,X2 Х3)или вида Y=f(X).
Оценить:
Адекватность уравнения регрессии по значению коэффициента детерминированности R 2 ;
Значимость коэффициентов уравнения регрессии по t- критерию Стьюдента при заданном уровне доверительной вероятности р=0,05;
Степень случайности связи между каждым факторам Х и признаком Y (критерий Фишера);
Зависимость между показателями Х 1 , Х 2 , Х 3 основных фондов и объемом валовой продукции У предприятия одной из отраслей промышленности характеризуется следующими данными:
Вариант 5
| X 1 | 1.5 | 2.6 | 3.5 | 4.8 | 5.9 | 6.3 | 7.2 | 8.9 | 9.5 | 11.1 | 15.0 |
| X 2 | 10.2 | 15.3 | 18.4 | 20.5 | 24.7 | 25.6 | 27.3 | 28.3 | 29.6 | 30.1 | 31.0 |
| X 3 | 1.1 | 2.3 | 3.5 | 4.1 | 5.7 | 6.6 | 7.3 | 8.5 | 9.8 | 10.1 | 12.0 |
| Y |
Решение задания 1.
Решение задания 1 предполагает.
1. Построение корреляционного поля.
2. Построение матрицы коэффициентов парной корреляции.
3. Построение и анализ однофакторных регрессионных моделей линейного и экспонентного вида средствами встроенных функций ТП MS Excel.
4. Построение линейных однофакторных регрессионных моделей средствами надстройки «Пакет анализа».
5. Выводы.
Построение корреляционного поля.
Разместим таблицу с исходными данными в ячейках A3:D15 рабочего листа Excel.
| Приложение1.1 | |||
| Y | X1 | X2 | X3 |
| 1,5 | 10,2 | 1,1 | |
| 2,6 | 15,3 | 2,3 | |
| 3,5 | 18,4 | 3,5 | |
| 4,8 | 20,5 | 4,1 | |
| 5,9 | 24,7 | 5,7 | |
| 6,3 | 25,6 | 6,6 | |
| 7,2 | 27,3 | 7,3 | |
| 8,9 | 28,3 | 8,5 | |
| 9,5 | 29,6 | 9,8 | |
| 11,1 | 30,1 | 10,1 | |
| ? |
Используя возможности мастера диаграмм ТП MS Excel, построим корреляционное поле, то есть представим графически связь между результирующим признаком Y и каждым из факторов X. Из графиков видно, что между результирующим признаком Y и каждым из факторов X существует прямо пропорциональная зависимость, приближающаяся к линейной.
.
.
Исследуем тесноту и характер связи между факторами.
Построение матрицы коэффициентов парной корреляции.
Используя надстройку «Пакет анализа» ТП MS Excel (Сервис – Анализ данных – Корреляция), построим матрицу коэффициентов парной корреляции. Окно инструмента «Корреляция» представлено на рисунке 1. Матрица коэффициентов парной корреляции представлена на рисунке 2.
Рис.1. –Окно «Корреляция»
Рис.2. – Матрица коэффициентов парной корреляции.
Из этой матрицы видно, что все рассматриваемые факторы X1 – X3 имеют тесную связь с результативным признаком Y. Кроме того, все факторы Х между собой мультиколлинеарны. Поэтому построение многофакторной модели вида Y=f(Х1,Х2,Х3) невозможно.
Задание 2
1. Построить матрицу парных коэффициентов корреляции. Проверить наличие мультиколлинеарности. Обосновать отбор факторов в модель.
2. Построить уравнение множественной регрессии в линейной форме с выбранными факторами.
3. Оценить статистическую значимость уравнения регрессии и его параметров с помощью критериев Фишера и Стьюдента.
4. Построить уравнение регрессии со статистически значимыми факторами. Оценить качество уравнения регрессии с помощью коэффициента детерминации R 2 . Оценить точность построенной модели.
5. Оценить прогноз объема выпуска продукции, если прогнозные значения факторов составляют 75% от их максимальных значений.
Условия задачи (Вариант 21)
По данным, представленным в таблице 1 (n =17), изучается зависимость объема выпуска продукции Y (млн. руб.) от следующих факторов (переменных):
X 1 – численность промышленно-производственного персонала, чел.
X 2 – среднегодовая стоимость основных фондов, млн. руб.
X 3 – износ основных фондов, %
X 4 – электровооруженность, кВт×ч.
X 5 – техническая вооруженность одного рабочего, млн. руб.
X 6 – выработка товарной продукции на одного работающего, руб.
Таблица 1. Данные выпуска продукции
| № | Y | X 1 | X 2 | X 3 | X 4 | X 5 | X 6 |
| 39,5 | 4,9 | 3,2 | |||||
| 46,4 | 60,5 | 20,4 | |||||
| 43,7 | 24,9 | 9,5 | |||||
| 35,7 | 50,4 | 34,7 | |||||
| 41,8 | 5,1 | 17,9 | |||||
| 49,8 | 35,9 | 12,1 | |||||
| 44,1 | 48,1 | 18,9 | |||||
| 48,1 | 69,5 | 12,2 | |||||
| 47,6 | 31,9 | 8,1 | |||||
| 58,6 | 139,4 | 29,7 | |||||
| 70,4 | 16,9 | 5,3 | |||||
| 37,5 | 17,8 | 5,6 | |||||
| 62,0 | 27,6 | 12,3 | |||||
| 34,4 | 13,9 | 3,2 | |||||
| 35,4 | 37,3 | 19,0 | |||||
| 40,8 | 55,3 | 19,3 | |||||
| 48,1 | 35,1 | 12,4 |
Построить матрицу парных коэффициентов корреляции. Проверить наличие мультиколлинеарности. Обосновать отбор факторов в модель
В таблице 2 представлена матрица коэффициентов парной корреляции для всех переменных, участвующих в рассмотрении. Матрица получена с помощью инструмента Корреляция из пакета Анализ данных в Excel.
Таблица 2. Матрица коэффициентов парной корреляции
| Y | X1 | X2 | X3 | X4 | X5 | X6 | |
| Y | |||||||
| X1 | 0,995634 | ||||||
| X2 | 0,996949 | 0,994947 | |||||
| X3 | -0,25446 | -0,27074 | -0,26264 | ||||
| X4 | 0,12291 | 0,07251 | 0,107572 | 0,248622 | |||
| X5 | 0,222946 | 0,166919 | 0,219914 | -0,07573 | 0,671386 | ||
| X6 | 0,067685 | -0,00273 | 0,041955 | -0,28755 | 0,366382 | 0,600899 |
Визуальный анализ матрицы позволяет установить:
1) У имеет довольно высокие парные корреляции с переменными Х1, Х2 (>0,5) и низкие с переменными Х3,Х4,Х5,Х6 (<0,5);
2) Переменные анализа Х1, Х2 демонстрируют довольно высокие парные корреляции, что обуславливает необходимость проверки факторов на наличие между ними мультиколлинеарности. Тем более, что одним из условий классической регрессионной модели является предположение о независимости объясняющих переменных.
Для выявления мультиколлинеарности факторов выполним тест Фаррара-Глоубера по факторам Х1,Х2,Х3,Х4,Х5,Х6 .
Проверка теста Фаррара-Глоубера на мультиколлинеарность факторов включает несколько этапов.
1) Проверка наличия мультиколлинеарности всего массива переменных .
Одним из условий классической регрессионной модели является предположение о независимости объясняющих переменных. Для выявления мультиколлинеарности между факторами вычисляется матрица межфакторных корреляций R с помощью Пакета анализа данных (таблица 3).
Таблица 3.Матрица межфакторных корреляций R
| X1 | X2 | X3 | X4 | X5 | X6 | |
| X1 | 0,994947 | -0,27074 | 0,07251 | 0,166919 | -0,00273 | |
| X2 | 0,994947 | -0,26264 | 0,107572 | 0,219914 | 0,041955 | |
| X3 | -0,27074 | -0,26264 | 0,248622 | -0,07573 | -0,28755 | |
| X4 | 0,07251 | 0,107572 | 0,248622 | 0,671386 | 0,366382 | |
| X5 | 0,166919 | 0,219914 | -0,07573 | 0,671386 | 0,600899 | |
| X6 | -0,00273 | 0,041955 | -0,28755 | 0,366382 | 0,600899 |
Между факторами Х1 и Х2, Х5 и Х4, Х6 и Х5 наблюдается сильная зависимость (>0,5).
Определитель det (R) = 0,001488 вычисляется с помощью функции МОПРЕД. Определитель матрицы R стремится к нулю, что позволяет сделать предположение об общей мультиколлинеарности факторов.
2) Проверка наличия мультиколлинеарности каждой переменной с другими переменными:
· Вычислим обратную матрицу R -1 с помощью функции Excel МОБР (таблица 4):
Таблица 4. Обратная матрица R -1
| X1 | X2 | X3 | X4 | X5 | X6 | |
| X1 | 150,1209 | -149,95 | 3,415228 | -1,70527 | 6,775768 | 4,236465 |
| X2 | -149,95 | 150,9583 | -3,00988 | 1,591549 | -7,10952 | -3,91954 |
| X3 | 3,415228 | -3,00988 | 1,541199 | -0,76909 | 0,325241 | 0,665121 |
| X4 | -1,70527 | 1,591549 | -0,76909 | 2,218969 | -1,4854 | -0,213 |
| X5 | 6,775768 | -7,10952 | 0,325241 | -1,4854 | 2,943718 | -0,81434 |
| X6 | 4,236465 | -3,91954 | 0,665121 | -0,213 | -0,81434 | 1,934647 |
· Вычисление F-критериев , где – диагональные элементы матрицы , n=17, k = 6 (таблица 5).
Таблица 5. Значения F-критериев
| F1 (Х1) | F2 (Х2) | F3 (Х3) | F4 (Х4) | F5 (Х5) | F6 (Х6) |
| 89,29396 | 89,79536 | 0,324071 | 0,729921 | 1,163903 | 0,559669 |
· Фактические значения F-критериев сравниваются с табличным значением F табл = 3,21 (FРАСПОБР(0,05;6;10)) при n1= 6 и n2 = n - k – 1=17-6-1=10 степенях свободы и уровне значимости α=0,05, где k – количество факторов.
· Значения F-критериев для факторов Х1 и Х2 больше табличного, что свидетельствует о наличии мультиколлинеарности между данными факторами. Меньше всего влияет на общую мультиколлинеарность факторов фактор Х3.
3) Проверка наличия мультиколлинеарности каждой пары переменных
· Вычислим частные коэффициенты корреляции по формуле , где – элементы матрицы (таблица 6)
Таблица 6. Матрица коэффициентов частных корреляций
| X1 | X2 | X3 | X4 | X5 | X6 | |
| X1 | ||||||
| X2 | 0,996086 | |||||
| X3 | -0,22453 | 0,197329 | ||||
| X4 | 0,093432 | -0,08696 | 0,415882 | |||
| X5 | -0,32232 | 0,337259 | -0,1527 | 0,581191 | ||
| X6 | -0,24859 | 0,229354 | -0,38519 | 0,102801 | 0,341239 |
· Вычисление t
-критериев по формуле 
(таблица 7)
n - число данных = 17
K - число факторов = 6
Таблица 7.t-критерии для коэффициентов частной корреляции
| X1 | X2 | X3 | X4 | X5 | X6 | |
| X1 | ||||||
| X2 | 35,6355 | |||||
| X3 | -0,72862 | 0,636526 | ||||
| X4 | 0,296756 | -0,27604 | 1,446126 | |||
| X5 | -1,07674 | 1,13288 | -0,4886 | 2,258495 | ||
| X6 | -0,81158 | 0,745143 | -1,31991 | 0,326817 | 1,147999 |
t табл = СТЬЮДРАСПОБР(0,05;10) = 2,23
Фактические значения t-критериев сравниваются с табличным значением при степенях свободы n-k-1 = 17-6-1=10 и уровне значимости α=0,05;
t21 > tтабл
t54 > tтабл
Из таблиц 6 и 7 видно, что две пары факторов X1 и Х2, Х4 и Х5 имеют высокую статистически значимую частную корреляцию, то есть являются мультиколлинеарными. Для того чтобы избавиться от мультиколлинеарности, можно исключить одну из переменных коллинеарной пары. В паре Х1 и Х2 оставляем Х2, в паре Х4 и Х5 оставляем Х5.
Таким образом, в результате проверки теста Фаррара-Глоубера остаются факторы: Х2, Х3, Х5, Х6.
Завершая процедуры корреляционного анализа, целесообразно посмотреть частные корреляции выбранных факторов с результатом Y.
Построим матрицу парных коэффициентов корреляции, исходя из данных таблицы 8.
Таблица 8. Данные выпуска продукции с отобранными факторами Х2, Х3, Х5, Х6.
| № наблю-дения | Y | X 2 | X 3 | X 5 | X 6 |
| 39,5 | 3,2 | ||||
| 46,4 | 20,4 | ||||
| 43,7 | 9,5 | ||||
| 35,7 | 34,7 | ||||
| 41,8 | 17,9 | ||||
| 49,8 | 12,1 | ||||
| 44,1 | 18,9 | ||||
| 48,1 | 12,2 | ||||
| 47,6 | 8,1 | ||||
| 58,6 | 29,7 | ||||
| 70,4 | 5,3 | ||||
| 37,5 | 5,6 | ||||
| 12,3 | |||||
| 34,4 | 3,2 | ||||
| 35,4 | |||||
| 40,8 | 19,3 | ||||
| 48,1 | 12,4 |
В последнем столбце таблицы 9 представлены значения t-критерия для столбца У.
Таблица 9.Матрица коэффициентов частной корреляции с результатом Y
| Y | X2 | X3 | X5 | X6 | t критерий (t табл (0,05;11)= 2,200985 | |
| Y | 0,996949 | -0,25446 | 0,222946 | 0,067685 | ||
| X2 | 0,996949 | -0,26264 | 0,219914 | 0,041955 | 44,31676 | |
| X3 | -0,25446 | -0,26264 | -0,07573 | -0,28755 | 0,916144 | |
| X5 | 0,222946 | 0,219914 | -0,07573 | 0,600899 | -0,88721 | |
| X6 | 0,067685 | 0,041955 | -0,28755 | 0,600899 | 1,645749 |
Из таблицы 9 видно, что переменная Y имеет высокую и одновременно статистически значимую частную корреляцию с фактором Х2.