Построим матрицу коэффициентов парной корреляции. Коэффициент парной корреляции в Excel

По территориям Южного федерального округа РФ приводятся данные за 2011 год

Территории федерального округа	Валовой региональный продукт, млрд. руб., Y	Инвестиции в основной капитал, млрд. руб., X1
1. Респ. Адыгея
2. Респ. Дагестан
3. Респ. Ингушетия
4. Кабардино-БалкарскаяРесп.
5. Респ. Калмыкия
6. Карачаево-ЧеркесскаяРесп.
7. Респ. Северная Осетия - Алания
8. Краснодарский кра)
9. Ставропольский край
10. Астраханская обл.
11. Волгоградская обл.
12. Ростовская обл.

• 1. Рассчитайте матрицу парных коэффициентов корреляции; оцените статистическую значимость коэффициентов корреляции.
• 2. Постройте поле корреляции результативного признака и наиболее тесно связанного с ним фактора.
• 3. Рассчитайте параметры линейной парной регрессии для каждого фактора Х..
• 4. Оцените качество каждой модели через коэффициент детерминации, среднюю ошибку аппроксимации и F-критерий Фишера. Выберите лучшую модель.

составит 80% от его максимального значения. Представьте графически: фактические и модельные значения, точки прогноза.

• 6. Используя пошаговую множественную регрессию (метод исключения или метод включения), постройте модель формирования цены квартиры за счёт значимых факторов. Дайте экономическую интерпретацию коэффициентов модели регрессии.
• 7. Оцените качество построенной модели. Улучшилось ли качество модели по сравнению с однофакторной моделью? Дайте оценку влияния значимых факторов на результат с помощью коэффициентов эластичности,в - и -? коэффициентов.

При решении данной задачи расчеты и построение графиков и диаграмм будем вести с использованием настройки Excel Анализ данных.

1. Рассчитаем матрицу парных коэффициентов корреляции и оценим статистическую значимость коэффициентов корреляции

В диалоговом окне Корреляция в поле Входной интервал вводим диапазон ячеек, содержащих исходные данные. Так как мы выделили и заголовки столбцов, то устанавливаем флажок Метки в первой строке.

Получили следующие результаты:

Таблица 1.1 Матрица парных коэффициентов корреляции

Анализ матрицы коэффициентов парной корреляции показывает, что зависимая переменная Y, т.е валового регионального продукта имеет более тесную связь с Х1 (инвестиции в основной капитал). Коэффициент корреляции равен 0,936. Это означает, что на 93,6% зависимая переменная Y (валовой региональный продукт) зависит от показателя Х1 (инвестиции в основной капитал).

Статистическая значимость коэффициентов корреляции определим с помощью t-критерия Стьюдента. Табличное значение сравниваем с расчетными значениями.

Вычислим табличное значение с помощью функции СТЬЮДРАСПОБР.

t табл.=0,129 при доверительной вероятности равной 0,9 и степенью свободы (n-2).

Статистическим значимым является фактор Х1.

2. Построим поле корреляции результативного признака (валового регионального продукта) и наиболее тесно связанного с ним фактора (инвестиции в основной капитал)

Для этого воспользуемся инструментом построения точечной диаграммы программы Excel.

В результате получаем поле корреляции цены валового регионального продукта, млрд. руб. и инвестиции в основной капитал, млрд. руб. (рисунок 1.1.).

Рисунок 1.1

3. Рассчитаем параметры линейной парной регрессии для каждого фактора Х

Для расчета параметров линейной парной регрессии воспользуемся инструментом Регрессия, входящим в настойку Анализ данных.

В диалоговом окне Регрессия в поле Входной интервал Y вводим адрес диапазона ячеек, которые представляет зависимую переменную. В поле

Входной интервал Х вводим адрес диапазона, который содержит значения независимых переменных. Выполним вычисления параметры парной регрессии для фактора Х.

Для Х1 получили следующие данные, представленные в таблице 1.2:

Таблица 1.2

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

4. Оценим качество каждой модели через коэффициент детерминации, среднюю ошибку аппроксимации и F-критерий Фишера. Установим, какая модель является лучшей.

Коэффициент детерминации, среднюю ошибку аппроксимации мы получили в результате расчетов, проведенных в пункте 3. Полученные данные представлены в следующих таблицах:

Данные по Х1:

Таблица 1.3а

Таблица 1.4б

А) Коэффициент детерминации определяет, какая доля вариации признака У учтена в модели и обусловлена влиянием на него фактора Х. Чем больше значение коэффициента детерминации, тем теснее связь между признаками в построенной математической модели.

В программе Excel обозначается R-квадрат.

Исходя из данного критерия наиболее адекватной является модель уравнения регрессии зависимости цены валового регионального продукта от инвестиции в основной капитал (Х1).

Б) Среднюю ошибку аппроксимации рассчитаем по формуле:

где числитель - сумма квадратов отклонения расчетных значений от фактических. В таблицах она находится в столбце SS, строке Остатки.

Среднее значение цены квартиры рассчитаем в Excel с помощью функции СРЗНАЧ. = 24,18182 млрд. руб.

При проведении экономических расчетов модель считается достаточно точной, если средняя ошибка аппроксимации меньше 5%, модель считается приемлемой, если средняя ошибка аппроксимации меньше 15%.

По данному критерию, наиболее адекватной является математическая модель для уравнения регрессии зависимости цены валового регионального продукта от инвестиции в основной капитал (Х1).

В) Для проверки значимости модели регрессии используется F-тест. Для этого выполняется сравнение и критического (табличного)значений F-критерия Фишера.

Расчетные значения приведены в таблицах 1.4б (обозначены буквой F).

Табличное значение F-критерий Фишера рассчитаем в Excel с помощью функции FРАСПОБР. Вероятность возьмем равной 0,05. Получили: = 4,75

Расчетные значения F-критерий Фишера для каждого фактора сравним с табличным значением:

71,02 > = 4,75 модель по данному критерию адекватна.

Проанализировав данные по всем трем критериям, можно сделать вывод, что наиболее лучшей является математическая модель, построена для фактора валового регионального продукта, которая описана линейным уравнением

5. Для выбранной модели зависимости цены валового регионального продукта

осуществим прогнозирование среднего значения показателя при уровне значимости, если прогнозное значения фактора составит 80% от его максимального значения. Представим графически: фактические и модельные значения, точки прогноза.

Рассчитаем прогнозное значение Х, по условию оно составит 80% от максимального значения.

Рассчитаем Х max в Excel с помощью функции МАКС.

0,8 *52,8 = 42,24

Для получения прогнозных оценок зависимой переменной подставим полученное значение независимой переменной в линейное уравнение:

5,07+2,14*42,24 = 304,55 млрд. руб.

Определим доверительный интервал прогноза, который будет иметь следующие границы:

Для вычисления доверительного интервала для прогнозного значения рассчитываем величину отклонения от линии регрессии.

Для модели парной регрессии величина отклонения рассчитывается:

т.е. значение стандартной ошибки из таблицы 1.5а.

(Так как число степеней свободы равно единицы, то знаменатель будет равен n-2). корреляция парная регрессия прогноз

Для расчета коэффициента воспользуемся функцией Excel СТЬЮДРАСПОБР, вероятность возьмем равную 0,1, число степеней свободы 38.

Значение рассчитаем с помощью Excel, получим 12294.

Определим верхнюю и нижнюю границы интервала.

• 304,55+27,472= 332,022
• 304,55-27,472= 277,078

Таким образом, прогнозное значение = 304,55 тыс.долл., будет находиться между нижней границей, равной 277,078 тыс.долл. и верхней границей, равной 332,022 млдр. Руб.

Фактические и модельные значения, точки прогноза представлены графически на рисунке 1.2.

Рисунок 1.2

6. Используя пошаговую множественную регрессию (метод исключения), построим модель формирования цены валового регионального продукта за счёт значимых факторов

Для построения множественной регрессии воспользуемся функцией Регрессия программы Excel, включив в нее все факторы. В результате получаем результативные таблицы, из которых нам необходим t-критерий Стьюдента.

Таблица 1.8а

Таблица 1.8б

Таблица 1.8в.

Получаем модель вида:

Поскольку < (4,75 < 71,024), уравнение регрессии следует признать адекватным.

Выберем наименьшее по модулю значение t-критерия Стьюдента, оно равно 8,427, сравниваем его с табличным значением, которые рассчитываем в Excel, уровень значимости берем равным 0,10, число степеней свободы n-m-1=12-4=8: =1,8595

Поскольку 8,427>1,8595 модель следует признать адекватной.

7. Для оценки значимого фактора полученной математической модели, рассчитаем коэффициенты эластичности, и - коэффициенты

Коэффициент эластичности показывает, насколько процентов изменится результативный признак при изменении факторного признака на 1%:

Э X4 = 2,137 *(10,69/24,182) = 0,94%

То есть с ростом инвестиции в основной капитал 1% стоимость в среднем возрастает на 0,94%.

Коэффициент показывает на какую часть величины среднего квадратического отклонения меняется среднее значение зависимой переменной с изменением независимой переменной на одно среднеквадратическое отклонение.

2,137* (14.736/33,632) = 0,936.

Данные средних квадратических отклонений взяты из таблиц, полученных с помощью инструменты Описательная статистика.

Таблица 1.11 Описательная статистика (Y)

Таблица 1.12 Описательная статистика (Х4)

Коэффициент определяет долю влияния фактора в суммарном влиянии всех факторов:

Для расчета коэффициентов парной корреляции вычисляем матрицу парных коэффициентов корреляции в программе Excel с помощью инструмента Корреляция настройки Анализа данных.

Таблица 1.14

(0,93633*0,93626) / 0,87 = 1,00.

Вывод: Из полученных расчетов можно сделать вывод, что результативный признак Y (валовой региональный продукт) имеет большую зависимость от фактора X1 (инвестиции в основной капитал) (на 100%).

Список литературы

• 1. Магнус Я.Р., Катышев П.К., Пересецкий А.А. Эконометрика. Начальный курс. Учебное пособие. 2-е изд. - М.: Дело, 1998. - с. 69 - 74.
• 2. Практикум по эконометрике: Учебное пособие / И.И. Елисеева, С.В. Курышева, Н.М. Гордеенко и др. 2002. - с. 49 - 105.
• 3. Доугерти К. Введение в эконометрику: Пер. с англ. - М.: ИНФРА-М, 1999. - XIV, с. 262 - 285.
• 4. Айвызян С.А., Михтирян В.С. Прикладная математика и основы эконометрики. -1998., с 115-147 .
• 5. Кремер Н.Ш., Путко Б.А. Эконометрика. -2007. с 175-251.

Коэффициент корреляции отражает степень взаимосвязи между двумя показателями. Всегда принимает значение от -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 практически отсутствует.

	Z 1 (t)	Z 2 (t)	t	y(t)
Z 1 (t)
Z 2 (t)
t
y(t)

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

Это можно сделать с помощью так называемого двух стадийного отбора. В соответствии с ним в модель включаются все предварительно отобранные факторы. Затем среди них на основе специальной количественной оценки и дополнительно качественного анализа выявляются несущественно влияющие факторы, которые постепенно отбрасываются пока не останутся те, относительно которых можно утверждать, что имеющийся статистический материал согласуется с гипотезой об их совместном существенном влиянии на зависимую переменную при выбранной форме связи.

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

Рассчитаем t ф по найденным коэффициентам парной корреляции и сравним их с t критическим для 5% уровня значимости (двустороннего) и 18 степенями свободы (ν = n-2).

где r – значение коэффициента парной корреляции;

n – число наблюдений (n=20)

При сравнении t ф для каждого коэффициента с t кр = 2,101 получаем, что найденные коэффициенты признаются значимыми, т.к. t ф > t кр.

t ф для r yx 1 = 2, 5599 ;

t ф для r yx 2 = 7,064206 ;

t ф для r yx 3 = 2,40218 ;

t ф для r х1 x 2 = 4,338906 ;

t ф для r х1 x 3 = 15,35065;

t ф для r х2 x 3 = 4,749981

При отборе факторов включаемых в анализ к ним предъявляются специфические требования. Прежде всего, показатели, выражающие эти факторы должны быть количественно измеримы.

Факторы, включаемые в модель, не должны находиться между собой в функциональной или близкой к ней связи. Наличие таких связей характеризуется мультиколлинеарностью.

Мультиколлинеарность свидетельствует о том, что некоторые факторы характеризуют одну и ту же сторону изучаемого явления. Поэтому их одновременное включение в модель нецелесообразно, так как они в определённой степени дублируют друг друга. Если нет особых предположений говорящих в пользу одного из этих факторов, следует отдавать предпочтение тому из них, который характеризуется большим коэффициентом парной (или частной) корреляции.

Считается, что предельным является значение коэффициента корреляции между двумя факторами, равное 0,8.

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

Матрица парных коэффициентов корреляции

	Y	X1	X2	X3	X4	X5
Y
X1	0,732705
X2	0,785156	0,706287
X3	0,179211	-0,29849	0,208514
X4	0,667343	0,924333	0,70069	0,299583
X5	0,709204	0,940488	0,691809	0,326602	0,992945

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

1. Если в первом столбце матрицы есть коэффициенты корреляции, для которых /r / < 0,5, то соответствующие признаки из модели исключаются. В данном случае в первом столбце матрицы коэффициентов корреляции исключается фактор или коэффициент роста уровня инфляции. Данный фактор оказывает меньшее влияние на результативный признак, нежели оставшиеся четыре признака.

2. Анализируя парные коэффициенты корреляции факторных признаков друг с другом, (r XiXj), характеризующие тесноту их взаимосвязи, необходимо оценить их независимость друг от друга, поскольку это необходимое условие для дальнейшего проведения регрессионного анализа. В виду того, что в экономике абсолютно независимых признаков нет, необходимо выделить, по возможности, максимально независимые. Факторные признаки, находящиеся в тесной корреляционной зависимости друг с другом, называются мультиколлинеарными. Включение в модель мультиколлинеарных признаков делает невозможным экономическую интерпретацию регрессионной модели, так как изменение одного фактора влечет за собой изменение факторов с ним связанных, что может привести к «поломке» модели в целом.

Критерий мультиколлениарности факторов выглядит следующим образом:

/r XiXj / > 0,8

В полученной матрице парных коэффициентов корреляции этому критерию отвечают два показателя, находящиеся на пересечении строк и . Из каждой пары этих признаков в модели необходимо оставить один, он должен оказывать большее влияние на результативный признак. В итоге из модели исключаются факторы и , т.е. коэффициент роста себестоимости реализованной продукции и коэффициент роста объёма её реализации.

Итак, в регрессионную модель вводим факторы Х1 и Х2.

Далее осуществляется регрессионный анализ (сервис, анализ данных, регрессия). Вновь составляет таблица исходных данных с факторами Х1 и Х2. Регрессия в целом используется для анализа воздействия на отдельную зависимую переменную значений независимых переменных (факторов) и позволяет корреляционную связь между признаками представить в виде некоторой функциональной зависимости называемой уравнением регрессии или корреляционно-регрессионной моделью.

В результате регрессионного анализа получаем результаты расчета многомерной регрессии. Проанализируем полученные результаты.

Все коэффициенты регрессии значимы по критерию Стьюдента. Коэффициент множественной корреляции R составил 0,925, квадрат этой величины (коэффициент детерминации) означает, что вариация результативного признака в среднем на 85,5% объясняется за счет вариации факторных признаков, включенных в модель. Коэффициент детерминированности характеризует тесноту взаимосвязи между совокупностью факторных признаков и результативным показателем. Чем ближе значение R-квадрат к 1, тем теснее взаимосвязь. В нашем случае показатель, равный 0,855, указывает на правильный подбор факторов и на наличие взаимосвязи факторов с результативным показателем.

Рассматриваемая модель адекватна, поскольку расчетное значение F-критерия Фишера существенно превышает его табличное значение (F набл =52,401; F табл =1,53).

В качестве общего результата проведенного корреляционно-регрессионного анализа выступает множественное уравнение регрессии, которое имеет вид:

Полученное уравнение регрессии отвечает цели корреляционно-регрессионного анализа и является линейной моделью зависимости балансовой прибыли предприятия от двух факторов: коэффициента роста производительности труда и коэффициента имущества производственного назначения.

На основании полученной модели можно сделать вывод о том, что при увеличении уровня производительности труда на 1% к уровню предыдущего периода величина балансовой прибыли возрастет на 0,95 п.п.; увеличение же коэффициента имущества производственного назначения на 1% приведет к росту результативного показателя на 27,9 п.п. Слелдовательно, доминирующее влияние на рост балансовой прибыли оказывает увеличение стоимости имущества производственного назначения (обновление и рост основных средств предприятия).

По множественной регрессионной модели выполняется многофакторный прогноз результативного признака. Пусть известно, что Х1 = 3,0, а Х3 = 0,7. Подставим значения факторных признаков в модель, получим Упр = 0,95*3,0 + 27,9*0,7 – 19,4 = 2,98. Таким образом, при увеличении производительности труда и модернизации основных средств на предприятии балансовая прибыль в 1 квартале 2005 г. по отношению к предыдущему периоду (IV квартал 2004 г.) возрастет на 2,98%.

Коллинеарными являются факторы …

И коллинеарны.

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. Также даны критические значения статистики Дарбина – Уотсона для заданного количества параметров при неизвестном и количестве наблюдений , . По данным характеристикам можно сделать вывод о том, что …автокорреляция остатков отсутствует