Контрольная работа №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) невозможно.

Анализ матрицы парных коэффициентов корреляции показывает, что результативный показатель наиболее тесно связан с показателем x (4) - количество удобрений, расходуемых на 1 га ().

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

О наличии мультиколлинеарности свидетельствуют также коэффициенты корреляции
и
. Учитывая тесную взаимосвязь показателейx (1) , x (2) и x (3) , в регрессионную модель урожайности может войти лишь один из них.

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

F набл = 121.

В скобках указаны значения исправленных оценок среднеквадратических отклонений оценок коэффициентов уравнения
.

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

Уравнение регрессии значимо, т.к. F набл = 121 > F kp = 2,85 найденного по таблицеF -распределения при=0,05; 1 =6 и 2 =14.

Из этого следует, что 0, т.е. и хотя бы один из коэффициентов уравнения j (j = 0, 1, 2, ..., 5) не равен нулю.

Для проверки гипотезы о значимости отдельных коэффициентов регрессии H0:  j =0, гдеj =1,2,3,4,5, сравнивают критическое значениеt kp = 2,14, найденное по таблицеt -распределения при уровне значимости=2Q =0,05 и числе степеней свободы=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, найденного при уровне значимости=0,05 и числах степеней свободы 1 =5 и 2 =15 по таблицеF -распределения, т.е. вектор0. Однако в уравнении значим только коэффициент регрессии приx (4) . Расчетные значенияt j для остальных коэффициентов меньшеt кр = 2,131, найденного по таблицеt -распределения при=2Q =0,05 и=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)) выбираем окончательное уравнение регрессии:

Уравнение значимо при =0,05, т.к. F набл = 266 > F kp = 3,20, найденного по таблицеF -распределения при=Q =0,05; 1 =3 и 2 =17. Значимы и все коэффициенты регрессииив уравненииt j >t kp (=2Q =0,05;=17)=2,11. Коэффициент регрессии 1 следует признать значимым ( 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) . Тогда по значениям i можно сопоставлять районы по урожайности. Районы, которым соответствуют значения i >0, имеют урожайность выше среднего, а i <0 - ниже среднего.

В нашем примере, по урожайности наиболее эффективно растениеводство ведется в районе, которому соответствует  7 =28%, где урожайность на 28% выше средней по региону, и наименее эффективно - в районе с 20 =27,3%.

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

Рассматривая зависимости между признаками, необходимо выделить прежде всего два типа связей:

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

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

Изучая взаимосвязи между признаками, их классифицируют по направлению, форме, числу факторов:

• по направлению связи делятся на прямые и обратные. При прямой связи направление изменения результативного признака совпадает с направлением изменения признака-фактора. При обратной связи направление изменения результативного признака противоположно направлению изменения признака- фактора. Например, чем выше квалификация рабочего, тем выше уровень производительности его труда (прямая связь). Чем выше производительность труда, тем ниже себестоимость единицы продукции (обратная связь);
• по форме (виду функции) связи делят на линейные (прямолинейные) и нелинейные (криволинейные). Линейная связь отображается прямой линией, нелинейная - кривой (парабол ой, гиперболой и т.п.). При линейной связи с возрастанием значения факторного признака происходит равномерное возрастание (убывание) значения результативного признака;
• по количеству факторов, действующих на результативный признак, связи подразделяют на однофакторные (парные) и многофакторные.

Изучение зависимости вариации признака от окружающих условий и составляет содержание теории корреляции .

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

При изучении взаимосвязи между двумя факторами их, как правило, обозначают Х= (х р х 2 , ...,х п) и Y= (у { , у 2 , ...,у и).

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

Ковариация между двумя переменными X и Y рассчитывается следующим образом:

где- фактические значения переменных

X и г;

Если случайные величины Хи Y независимы, теоретическая ковариация равна нулю.

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

Для двух переменных X и Y коэффициент парной корреляции

определяется следующим образом:

где SSy - оценки дисперсий величин Хи Y. Эти оценки характеризуют степень разброса значений х { ,х 2 , ...,х п (у 1 ,у 2 ,у п) вокруг своего среднего х (у соответственно), или вариабельность (изменчивость) этих переменных на множестве наблюдений.

Дисперсия (оценка дисперсии) определяется по формуле

В общем случае для получения несмещенной оценки дисперсии сумму квадратов следует делить на число степеней свободы оценки (п-р), где п - объем выборки, р - число наложенных на выборку связей. Так как выборка уже использовалась один раз для определения среднего X, то число наложенных связей в данном случае равно единице (р = 1), а число степеней свободы оценки (т.е. число независимых элементов выборки) равно (п - 1).

Более естественно измерять степень разброса значений переменных в тех же единицах, в которых измеряется и сама переменная. Эту задачу решает показатель, называемый среднеквадратическим отклонением (стандартным отклонением ) или стандартной ошибкой переменной X (переменной Y) и определяемый соотношением

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

Знаменатель выражения для коэффициента парной корреляции [см. формулу (3.2.2)] просто нормирует числитель таким образом, что коэффициент корреляции оказывается легко интерпретируемым числом, не имеющим размерности, и принимает значения от -1 до +1.

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

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

• 0,1-0,3 - слабая;
• 0,3-0,5 - заметная;
• 0,5-0,7 - умеренная;
• 0,7-0,9 - высокая;
• 0,9-1,0 - весьма высокая.

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

Оценка значимости коэффициента корреляции при малых объемах выборки выполняется с использованием 7-критерия Стьюдента. При этом фактическое (наблюдаемое) значение этого критерия определяется по формуле

Вычисленное по этой формуле значение / набл сравнивается с критическим значением 7-критерия, которое берется из таблицы значений /-критерия Стьюдента (см. Приложение 2) с учетом заданного уровня значимости ос и числа степеней свободы (п - 2).

Если 7 набл > 7 табл, то полученное значение коэффициента корреляции признается значимым (т.е. нулевая гипотеза, утверждающая равенство нулю коэффициента корреляции, отвергается). И таким образом делается вывод, что между исследуемыми переменными есть тесная статистическая взаимосвязь.

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

• положительная, то при возрастании одной случайной величины другая имеет тенденцию в среднем возрастать;
• отрицательная, то при возрастании одной случайной величины другая имеет тенденцию в среднем убывать. Удобным графическим средством анализа парных данных является диаграмма рассеяния , которая представляет каждое наблюдение в пространстве двух измерений, соответствующих двум факторам. Диаграмму рассеяния, на которой изображается совокупность значений двух признаков, называют еще корреляционным полем. Каждая точка этой диаграммы имеет координаты х (. и у г По мере того как возрастает сила линейной связи, точки на графике будут лежать более близко к прямой линии, а величина г будет ближе к единице.

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

Пусть вся совокупность данных состоит из переменной Y = = (у р у 2 , ..., у п) и т переменных (факторов) X, каждая из которых содержит п наблюдений. Значения переменных Y и X, содержащиеся в наблюдаемой совокупности, записываются в таблицу (табл. 3.2.1).

Таблица 3.2.1

Переменная Номер наблюдения
					Х тЗ
					Х тп

На основании данных, содержащихся в этой таблице, вычисляют матрицу коэффициентов парной корреляции R, она симметрична относительно главной диагонали:

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

Одной корреляционной матрицей нельзя полностью описать зависимости между величинами. В связи с этим в многомерном корреляционном анализе рассматривается две задачи:

• 1. Определение тесноты связи одной случайной величины с совокупностью остальных величин, включенных в анализ.
• 2. Определение тесноты связи между двумя величинами при фиксировании или исключении влияния остальных величин.

Эти задачи решаются соответственно с помощью коэффициентов множественной и частной корреляции.

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

где R - R [см. формулу (3.2.6)]; Rjj - алгебраическое дополнение элемента той же матрицы R.

Квадрат коэффициента множественной корреляции Щ j 2 j _j J+l m принято называть выборочным множественным коэффициентом детерминации ; он показывает, какую долю вариации (случайного разброса) исследуемой величины Xj объясняет вариация остальных случайных величин Х { , Х 2 ,..., Х т.

Коэффициенты множественной корреляции и детерминации являются величинами положительными, принимающими значения в интервале от 0 до 1. При приближении коэффициента R 2 к единице можно сделать вывод о тесноте взаимосвязи случайных величин, но не о ее направлении. Коэффициент множественной корреляции может только увеличиваться, если в модель включать дополнительные переменные, и не увеличится, если исключать какие-либо из имеющихся признаков.

Проверка значимости коэффициента детерминации осуществляется путем сравнения расчетного значения /’-критерия Фишера

с табличным F raбл. Табличное значение критерия (см. Приложение 1) определяется заданным уровнем значимости а и степенями свободы v l = mnv 2 = n-m-l. Коэффициент R 2 значимо отличается от нуля, если выполняется неравенство

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

Выборочный частный коэффициент корреляции определяется по формуле

где R Jk , Rjj, R kk - алгебраические дополнения к соответствующим элементам матрицы R [см. формулу (3.2.6)].

Частный коэффициент корреляции, также как и парный коэффициент корреляции, изменяется от -1 до +1.

Выражение (3.2.9) при условии т = 3 будет иметь вид

Коэффициент г 12(3) называется коэффициентом корреляции между х { и х 2 при фиксированном х у Он симметричен относительно первичных индексов 1, 2. Его вторичный индекс 3 относится к фиксированной переменной.

Пример 3.2.1. Вычисление коэффициентов парной,

множественной и частной корреляции.

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

• 1. Построить диаграмму рассеяния (корреляционное поле) для переменных «объем продаж» и «индекс потребительских расходов».
• 2. Определить степень влияния индекса потребительских расходов на объем продаж (вычислить коэффициент парной корреляции).
• 3. Оценить значимость вычисленного коэффициента парной корреляции.
• 4. Построить матрицу коэффициентов парной корреляции по трем переменным.
• 5. Найти оценку множественного коэффициента корреляции.
• 6. Найти оценки коэффициентов частной корреляции.

1. В нашем примере диаграмма рассеяния имеет вид, приведенный на рис. 3.2.1. Вытянутость облака точек на диаграмме рассеяния вдоль наклонной прямой позволяет сделать предположение, что существует некоторая объективная тенденция прямой линейной связи между значениями переменных Х 2 Y (объем продаж).

Рис. 3.2.1.

2. Промежуточные расчеты при вычислении коэффициента корреляции между переменными Х 2 (индекс потребительских расходов) и Y (объем продаж) приведены в табл. 3.2.3.

Средние значения случайных величин Х 2 и Y, которые являются наиболее простыми показателями, характеризующими последовательности jCj, х 2 , ..., х 16 и y v y 2 , ..., у 16 , рассчитаем по следующим формулам:

Объем продаж Y, тыс. руб.		Индекс потреби тельских расходов	Объем продаж Y, тыс. руб.		Индекс потреби тельских расходов

Таблица 3.2.3

				л:, - х	(И - У)(х, - х)	(х, - х) 2	(у,- - у) 2

Дисперсия характеризует степень разброса значений x v x 2 ,х :

Рассмотрим теперь решение примера 3.2.1 в Excel.

Чтобы вычислить корреляцию средствами Excel, можно воспользоваться функцией =коррел (), указав адреса двух столбцов чисел, как показано на рис. 3.2.2. Ответ помещен в D8 и равен 0,816.

Рис. 3.2.2.

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

Если массив! и массив2 имеют различное количество точек данных, то функция коррел возвращает значение ошибки #н/д.

Если массив1 либо массив2 пуст или если о (стандартное отклонение) их значений равно нулю, то функция коррел возвращает значение ошибки #дел/0 !.)

Критическое значение /-статистики Стьюдента может быть также получено с помощью функции стьюдраспробр 1 пакета Excel. В качестве аргументов функции необходимо задать число степеней свободы, равное п - 2 (в нашем примере 16 - 2= 14) и уровень значимости а (в нашем примере а = 0,1) (рис. 3.2.3). Если фактическое значение /-статистики, взятое по модулю, больше критического, то с вероятностью (1 - а) коэффициент корреляции значимо отличается от нуля.

Рис. 3.2.3. Критическое значение /-статистики равно 1,7613

В Excel входит набор средств анализа данных (так называемый пакет анализа), предназначенный для решения различных статистических задач. Для вычисления матрицы коэффициентов парной корреляции R следует воспользоваться инструментом Корреляция (рис. 3.2.4) и установить параметры анализа в соответствующем диалоговом окне. Ответ будет помещен на новый рабочий лист (рис. 3.2.5).

1 В Excel 2010 название функции стьюдраспробр изменено на стью-

ДЕНТ.ОБР.2Х.

Рис. 3.2.4.

Рис. 3.2.5.

• Основоположниками теории корреляции считаются английские статистики Ф. Гальтон (1822-1911) и К. Пирсон (1857-1936). Термин «корреляция» был заимствован из естествознания и обозначает «соотношение, соответствие». Представление о корреляции как взаимозависимости между случайными переменными величинами лежит воснове математико-статистической теории корреляции.

	Y	X 1	X 2	X 3	X 4	X 5	X 6
Y
X 1	0,519
X 2	-0,273	0,030
X 3	0,610	0,813	-0,116
X 4	-0,572	-0,013	-0,022	-0,091
X 5	0,297	0,043	-0,461	0,120	-0,359
X 6	0,118	-0,366	-0,061	-0,329	-0,100	-0,290

Анализ межфакторных (между «иксами»!) коэффициентов корреляции показывает, что значение 0,8 превышает по абсолютной величине только коэффициент корреляции между парой факторов Х 1 –Х 3 (выделен жирным шрифтом). Факторы Х 1 –Х 3 , таким образом, признаются коллинеарными.

2. Как было показано в пункте 1, факторы Х 1 –Х 3 являются коллинеарными, а это означает, что они фактически дублируют друг друга, и их одновременное включение в модель приведет к неправильной интерпретации соответствующих коэффициентов регрессии. Видно, что фактор Х 3 имеет больший по модулю коэффициент корреляции с результатом Y , чем фактор Х 1: r y , x 1 =0,519; r y , x 3 =0,610; (см. табл. 1 ). Это свидетельствует о более сильном влиянии фактора Х 3 на изменение Y . Фактор Х 1 , таким образом, исключается из рассмотрения.

Для построения уравнения регрессии значения используемых переменных (Y , X 2 , X 3 , X 4 , X 5 , X 6) скопируем на чистый рабочий лист (прил. 3) . Уравнение регрессии строим с помощью надстройки «Анализ данных… Регрессия » (меню «Сервис» ® «Анализ данных… » ® «Регрессия »). Панель регрессионного анализа с заполненными полями изображена на рис. 2 .

Результаты регрессионного анализа приведены в прил. 4 и перенесены в табл. 2 . Уравнение регрессии имеет вид (см. «Коэффициенты» втабл. 2 ):

Уравнение регрессии признается статистически значимым, так как вероятность его случайного формирования в том виде, в котором оно получено, составляет 8,80×10 -6 (см. «Значимость F» втабл. 2 ), что существенно ниже принятого уровня значимости a=0,05.

Х 3 , Х 4 , Х 6 ниже принятого уровня значимости a=0,05 (см. «P-Значение» втабл. 2 ), что свидетельствует о статистической значимости коэффициентов и существенном влиянии этих факторов на изменение годовой прибыли Y .

Вероятность случайного формирования коэффициентов при факторах Х 2 и Х 5 превышает принятый уровень значимости a=0,05 (см. «P-Значение» втабл. 2 ), и эти коэффициенты не признаются статистически значимыми.

рис. 2. Панель регрессионного анализа модели Y (X 2 , X 3 , X 4 , X 5 , X 6)

Таблица 2

Y (X 2 , X 3 , X 4 , X 5 , X 6)

Регрессионная статистика
Множественный R	0,868
R-квадрат	0,753
Нормированный R-квадрат	0,694
Стандартная ошибка	242,3
Наблюдения
Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия		3749838,2	749967,6	12,78	8,80E-06
Остаток		1232466,8	58688,9
Итого		4982305,0
Уравнение регрессии
	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение
Y-пересечение	487,5	641,4	0,760	0,456
X2	-0,0456	0,0373	-1,224	0,235
X3	0,1043	0,0194	5,375	0,00002
X4	-0,0965	0,0263	-3,674	0,001
X5	2,528	6,323	0,400	0,693
X6	248,2	113,0	2,197	0,039

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

· факторы, коэффициенты при которых статистически значимы;

· факторы, у коэффициентов которых t ‑статистика превышает по модулю единицу (другими словами, абсолютная величина коэффициента больше его стандартной ошибки).

К первой группе относятся факторы Х 3 , Х 4 , Х 6 , ко второй - фактор X 2 . Фактор X 5 исключается из рассмотрения как неинформативный, и окончательно регрессионная модель будет содержать факторы X 2 , X 3 , X 4 , X 6 .

Для построения уравнения регрессии скопируем на чистый рабочий лист значения используемых переменных (прил. 5) и проведем регрессионный анализ (рис. 3 ). Его результаты приведены в прил. 6 и перенесены в табл. 3 . Уравнение регрессии имеет вид:

(см. «Коэффициенты» втабл. 3 ).

рис. 3. Панель регрессионного анализа модели Y (X 2 , X 3 , X 4 , X 6)

Таблица 3

Результаты регрессионного анализа модели Y (X 2 , X 3 , X 4 , X 6)

Регрессионная статистика
Множественный R	0,866
R-квадрат	0,751
Нормированный R-квадрат	0,705
Стандартная ошибка	237,6
Наблюдения
Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия		3740456,2	935114,1	16,57	2,14E-06
Остаток		1241848,7	56447,7
Итого		4982305,0
Уравнение регрессии
	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение
Y-пересечение	712,2	303,0	2,351	0,028
X2	-0,0541	0,0300	-1,806	0,085
X3	0,1032	0,0188	5,476	0,00002
X4	-0,1017	0,0223	-4,560	0,00015
X6	227,5	98,5	2,310	0,031

Уравнение регрессии статистически значимо: вероятность его случайного формирования ниже допустимого уровня значимости a=0,05 (см. «Значимость F» втабл. 3 ).

Статистически значимыми признаются и коэффициенты при факторах Х 3 , Х 4 , Х 6: вероятность их случайного формирования ниже допустимого уровня значимости a=0,05 (см. «P-Значение» втабл. 3 ). Это свидетельствует о существенном влиянии годового размера страховых сборов X 3 , годового размера страховых выплат X 4 и формы собственности X 6 на изменение годовой прибыли Y .

Коэффициент при факторе Х 2 (годовой размер страховых резервов) не является статистически значимым. Однако этот фактор все же можно считать информативным, так как t ‑статистика его коэффициента превышает по модулю единицу, хотя к дальнейшим выводам относительно фактора Х 2 следует относиться с некоторой долей осторожности.

4. Оценим качество и точность последнего уравнения регрессии, используя некоторые статистические характеристики, полученные в ходе регрессионного анализа (см. «Регрессионную статистику » в табл. 3 ):

· множественный коэффициент детерминации

показывает, что регрессионная модель объясняет 75,1 % вариации годовой прибыли Y , причем эта вариация обусловлена изменением включенных в модель регрессии факторов X 2 , X 3 , X 4 и X 6 ;

· стандартная ошибка регрессии

тыс. руб.

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

Средняя относительная ошибка аппроксимации определяется по приближенной формуле:

где тыс. руб. - среднее значение годовой прибыли (определено с помощью встроенной функции «СРЗНАЧ »; прил. 1 ).

Е отн показывает, что предсказанные уравнением регрессии значения годовой прибыли Y отличаются от фактических значений в среднем на 26,7 %. Модель имеет неудовлетворительную точность (при - точность модели высокая, при - хорошая, при - удовлетворительная, при - неудовлетворительная).

5. Для экономической интерпретации коэффициентов уравнения регрессии сведем в таблицу средние значения и стандартные отклонения переменных в исходных данных (табл. 4 ) . Средние значения были определены с помощью встроенной функции «СРЗНАЧ », стандартные отклонения - с помощью встроенной функции «СТАНДОТКЛОН » (см. прил. 1 ).

	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.

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