Выполнение задания 1 в ППП MS Excel

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

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

Корреляционная связь - частный случай статистической связи, при которой с изменением значения признака-фактора Х закономерно изменяется среднее значение признака-результата Y.

Эмпирический анализ связи заключается в построении группировок (аналитической или комбинационной) и графиков: корреляционного поля и эмпирической линии регрессии. Результаты эмпирического анализа дополняются дисперсионным и корреляционным анализом.

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

Регрессия – зависимость среднего значения какой-либо случайной величины от одной или нескольких независимых величин.

Наиболее часто для описания статистической связи признаков используется линейное уравнение регрессии. Наиболее распространенным методом оценки параметров уравнения регрессии является метод наименьших квадратов (МНК).

1. Корреляционное поле

Корреляционное поле – точечный график, построенный в прямоугольной системе координат. Число точек равно числу единиц в совокупности. Каждая точка соответствует единице совокупности (рис. 3).

Для построения корреляционного поля выбираем ВСТАВКА и из разновидностей диаграмм MS Excel – ТОЧЕЧНАЯ. Каждая точка имеет координаты по оси абсцисс – значение признака-фактора Х, а по оси ординат – значение признака-результата Y у данной единицы совокупности.

Рис. 3. Корреляционное поле

2. Аналитическая группировка

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

Факторный признак желательно группировать равноинтервально или равнонаполненно.

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

Таблица 6. Аналитическая группировка

Численность персонала, чел X

Середина интервала

Частота n_j

Выручка, млн. у.е./год y_i

Среднее значение выручки, млн. у.е./год

Границы интервала

нижняя

верхняя

70, 50, 79, 61, 53, 58, 67, 80

64,75

46,5

123, 94, 146, 100, 159, 158, 180, 163

140,38

165, 175, 203, 198, 220, 248, 273, 268

218,75

81,5

274, 278, 274, 330, 293, 347, 352, 386

316,75

413, 500, 400, 482, 456, 480, 421, 650

475,25

Итого

Аналитическая группировка для признаков численность персонала (X) и выручка (Y) представлена в табл. 6.

3. Эмпирическая линия регрессии

Для построения эмпирической линии регрессии (рис. 4) требуются данные аналитической группировки (табл. 6). Число точек эмпирической линии регрессии равно числу групп (5).

Рис. 4. Корреляционное поле и эмпирическая линия регрессии

Для построения эмпирической линии регрессии выбираем ВСТАВКА и из разновидностей диаграмм MS Excel – ТОЧЕЧНАЯ (с прямыми отрезками). Координаты точек: по оси абсцисс – середина интервала в группе фактора X, а по оси ординат – среднее значение результата Y в группе.

4. Теорема о разложении дисперсии, эмпирический коэффициент детерминации, эмпирическое корреляционное отношение.

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

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

где

- общее среднее значение признака-результата;

- среднее значение признака-результата внутри j -ой группы;

- множество единиц наблюдения в j -ой группе.

Таблица 7. Разложение дисперсии

Интервал X

Частота n_j

Выручка, млн. у.е./год y_i

Среднее значение выручки, млн. у.е./год

20-40

70, 50, 79, 61, 53, 58, 67, 80

64,75

254683,85

27,56; 217,56; 203,06; 14,06; 138,06; 45,56; 5,06; 232,56

40-53

123, 94, 146, 100, 159, 158, 180, 163

140,38

84542,72

301,89; 2150,64; 31,64; 1630,14; 346,89; 310,64; 1570,14; 511,89

53-73

165, 175, 203, 198, 220, 248, 273, 268

218,75

4772,65

2889,06; 1914,06; 248,06; 430,56; 1,56; 855,56; 2943,06; 2425,56

73-90

274, 278, 274, 330, 293, 347, 352, 386

316,75

43306,25

1827,56; 1501,56; 1827,56; 175,56; 564,06; 915,06; 1242,56; 4795,56

90-100

413, 500, 400, 482, 456, 480, 421, 650

475,25

430870,45

3875,06; 612,56; 5662,56; 45,56; 370,56; 22,56; 2943,06; 30537,56

Итого

818175,90

76363,88

Расчет характеристик (см. табл. 7):

Общая дисперсия (найдена для исходного ряда значений признака «Выручка» с помощью функции ДИСПР):

Межгрупповая дисперсия:

Средняя из внутригрупповых дисперсий:

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

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

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

Расчет характеристик:

Эмпирический коэффициент детерминации:

Эмпирическое корреляционное отношение:

5. Комбинационная группировка

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

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

Результаты комбинационной группировки для признаков численность персонала (x) и выручка (y) приведены в табл. 8.

Таблица 8. Комбинационная группировка

Интервал y Интервал x	50-80	80-165	165-273	273-390	390-650	ИТОГО
20-40
40-53
43-73
73-90
90-100
ИТОГО

6. Парная линейная регрессия

До расчета параметров регрессии необходимо определиться с формы связи. Линейная связь отображается прямой линией; криволинейная отображается кривой (параболой, гиперболой и т.п.). Методы выявления формы связи:

- графический (вид корреляционного поля и эмпирической линии регрессии);

- опыт предыдущих аналогичных исследований;

- перебор всевозможных видов функций и выбор наилучшей по показателю качества.

Линейная однофакторная регрессия имеет вид:

M (y │ x = x_i)= b ₀+ b ₁· x _i,

где M (y │ x = x_i) – условное мат. ожидание зависимой переменной y при значении независимой переменной х равном х_i;

b ₀, b ₁– параметры (коэффициенты) уравнения регрессии.

Наиболее распространенный метод оценки параметров регрессии - метод наименьших квадратов (МНК). Согласно МНК параметры регрессии подбираются так, чтобы сумма квадратов отклонений фактических значений результативного признака – y_i от расчетных (теоретических) значений – f (x_i) (рассчитанных по уравнению регрессии) была минимальна. МНК не предполагает какого-либо группирования. Все расчеты производятся по исходным столбцам анализируемых признаков.

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

Решение данной системы:

Оценка параметра b ₁ может быть определена через коэффициент корреляции (см. пункт «Корреляционный анализ»):

Знак коэффициента регрессии b ₁ указывает направление связи: если b ₁>0 - связь прямая, если b ₁<0 - связь обратная. Величина b ₁ показывает, на сколько единиц изменится в среднем признак-результат y при изменении признака-фактора х на 1 единицу своего измерения.

Формально значение параметра b ₀ – среднее значение признака-результата y при нулевом значении признака-фактора х. Если признак-фактор не может быть равен нулю, то параметр b ₀ не интерпретируется.

Для построения уравнения регрессии можно воспользоваться Пакетом анализа: вкладка Данные – Анализ данных – Регрессия. Если данного пакета нет, надо предварительно выполнить следующие действия: вкладка Файл – Параметры – Надстройки – Пакет анализа (отметить флажком). В окне Регрессия:

Входной интервал Х – это столбец значений признака-фактора;

Входной интервал Y – это столбец значений признака-результата;

Выходной интервал – левая верхняя ячейка для будущих результатов.

Результаты расчета MS Excel:

Таблица 9. «Вывод итогов»

Регрессионная статистика
Множественный R	0,94891
R-квадрат	0,90044
Нормированный R-квадрат	0,89782
Стандартная ошибка	48,41183
Наблюдения

	Коэффициенты	Стандартная ошибка
Y-пересечение	-129,10481	21,49081247
Переменная x₁	5,798751	0,312794302

Расчет параметров линейной регрессии (см. табл. 10):

Рассчитав значения коэффициентов регрессии, получаем линейное уравнение: f (x_i) = -129.105 + 5.799· х_i.

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

Таблица 10. Расчет параметров линейной регрессии

№	x	y	xy	f(x_i)	y- f(x_i)	[y- f(x_i)]²
				-13,13	83,13	6910,56
				4,27	45,73	2091,56
				15,86	63,14	3986,16
…	…	…	…	…	…	…
				439,17	40,83	1666,86
				450,77	-29,77	886,27
				450,77	199,23	39692,48
ИТОГО					0,00	89060,79
СРЗНАЧ	64,20	243,18	19084,48	243,18	0,00	2226,52
ДИСПР	598,86	22363,49		20136,97	2226,52

Для графического отображения линейной регрессии выбираем ВСТАВКА и из разновидностей диаграмм MS Excel – ТОЧЕЧНАЯ (с прямыми отрезками без маркеров). Координаты точек: по оси абсцисс – значения признака-фактора, по оси ординат – соответствующие фактору регрессионные значения признака-результата (т.к. регрессия линейна, достаточно двух точек).

Рис. 5. Корреляционное поле и линейная парная регрессия

Для графического построения линейной регрессии (рис. 5) можно воспользоваться на диаграмме функцией добавления линии тренда. Строим корреляционное поле (см. пункт 1, рис. 3). На любой точке корреляционного поля – щелчок правой кнопки (или, активировав окно диаграммы, через вкладку Работа с диаграммами – Макет) – Добавить линию тренда – Линейная (отметить) – Показывать уравнение на диаграмме (отметить) – Поместить на диаграмме величину достоверности аппроксимации (отметить).

7. Корреляционный анализ

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

Коэффициент Фехнера, Кф - показатель тесноты линейной связи:

где С/Н – число совпадений/несовпадений знаков отклонений Х от своего среднего значения и Y от своего среднего значения.

Значения данного показателя изменяются в пределах от -1 до +1. Если |Кф|→1, связь близка к линейной функциональной. Если |Кф|→0, признаки X и Y взаимно независимы. Если Кф<0, связь между признаками обратная. Если Кф>0, связь прямая. Равенство нулю означает отсутствие только линейной связи.

Ковариация, cov (X,Y) – показатель совместной вариации признаков:

Это размерный показатель; его единицы измерения равны произведению единиц измерения Х на единицы измерения Y.

Коэффициент линейной парной корреляции, r_x_,_y – показатель степени тесноты линейной связи:

Это безразмерный показатель. Область допустимых значений - от -1 до +1. Если | r_x_,_y |→1, связь близка к линейной функциональной. Если признаки Х и Y взаимно независимы, то | r_x_,_y |→0. Если r_x_,_y >0, зависимость прямая, если r_x_,_y <0 - обратная.

Признаки могут быть связаны тесной нелинейной связью. Если r -| r |>0,1, то связь, скорее, нелинейная, если меньше – скорее, линейная.

Расчет данных показателей может быть осуществлен с использованием функций СЧЕТЕСЛИМН, КОВАРИАЦИЯ, КОРРЕЛ. Вызываем необходимую функцию (из категории «Статистические»):

= СЧЕТЕСЛИМН (Диапазон_условия1;Условие1; Диапазон_условия2;Условие2…)

где Диапазон_условия1; Диапазон_условия2 – столбцы значений отклонений признака-фактора и признака-результата от своих средних соответственно;

Условие1; Условие2 - условия в форме числа, выражения, которые определяют, какие ячейки требуется учитывать.

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

= КОВАРИАЦИЯ (массив1;массив2)

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

= КОРРЕЛ (массив1;массив2)

Результаты расчета MS Excel:

(млн. у.е./год)·чел

Расчет показателей (см. табл. 10):

Ковариация:

Коэффициент линейной корреляции (сравнить с Множественный R в табл. 9):

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

На основе этого разложения рассчитывается теоретический коэффициент детерминации, R²_yx как отношение объясненной уравнением дисперсии признака-результата - d², к общей дисперсии признака-результата s²_y:

где – объясненная уравнением регрессии дисперсия y;

s²_y - общая (полная) дисперсия y.

Или:

где - остаточная дисперсия y.

Этот показатель характеризует долю вариации результативного признака y, объясняемую уравнением связи, в общей вариации y. Коэффициент детерминации R ² _yx принимает значения от 0 до 1. Чем ближе R ² _yx к 0, тем слабее связь между признаками, чем ближе к 1, тем сильнее. Величина 1- R ² _yx характеризует долю дисперсии y, вызванную влиянием прочих неучтенных в уравнении факторов и ошибками измерений. При парной линейной регрессии R ² _yx = r ² _yx.

Средняя квадратическая ошибка уравнения регрессии, s_e - это среднее квадратическое отклонение наблюдаемых значений результативного признака от теоретических значений, рассчитанных по модели:

где h – число параметров в модели регрессии (в линейной парной регрессии. h = 2).

Если s_e окажется меньше s_y, то использование модели регрессии является целесообразным.

Средняя ошибка аппроксимации, А:

Чем меньше рассеяние эмпирических точек вокруг теоретической линии регрессии, тем меньше средняя ошибка аппроксимации. Ошибка аппроксимации меньше 7% говорит о хорошем качестве модели.

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

Расчет данных показателей может быть осуществлен с использованием функций ДИСПР и СТОШYX. Вызываем необходимую функцию (из категории «Статистические»):

= СТОШYX (известные_значения_y; известные_ значения_x)

где известные_значения_y; известные_значения_x – столбцы значений признака-результата и признака-фактора, для которых вычисляется средняя квадратическая ошибка уравнения регрессии.

Расчет характеристик (см. табл. 10):

8. Проверка значимости уравнения регрессии

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

Для проверки гипотезы о надежности уравнения регрессии используют F-статистику:

где n - число наблюдений;

h – число оцениваемых параметров (в случае парной линейной регрессии h =2);

R²_y(x1,...,xm) - коэффициент детерминации.

Для поиска критического значения - F_кр пользуются таблицами распределения Фишера-Снедоккора, задаваясь уровнем значимости a (обычно 0,05) и двумя числами степеней свободы k 1= h -1 и k 2= n-h.

Далее сравниваются рассчитанное значение, F _набл, и критическое, F _кр(_a_{; k 1; k 2)}:

если F _набл< F _кр(_a_{; k 1; k 2)}, то гипотезу о незначимости уравнения регрессии не отвергают;

если F _набл> F _кр(_a_{; k 1; k2),} то гипотезу отвергают и принимают альтернативную - о статистической значимости уравнения регрессии с вероятностью (1- a).

Значение F-статистики можно найти в Дисперсионном анализе таблицы «Вывод итогов», воспользовавшись построением регрессии через Пакет анализа (см. пункт 6).

Таблица 11. «Вывод итогов». Дисперсионный анализ.

Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия		805478,981	805478,981	343,68	0,0000
Остаток		89060,794	2343,705
Итого		894539,775

Результаты расчета MS Excel (см. табл. 11):