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

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

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

Если корреляционный коэффициент отрицательный, то это значит, что связь параметров обратная.

Для того, чтобы составить корреляционную матрицу в Экселе, используется один инструмент, входящий в пакет «Анализ данных» . Он так и называется – «Корреляция» . Давайте узнаем, как с помощью него можно вычислить показатели множественной корреляции.

Этап 1: активация пакета анализа

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

После указанного действия пакет инструментов «Анализ данных» будет активирован.

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

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

Этап 3: анализ полученного результата

Теперь давайте разберемся, как понимать тот результат, который мы получили в процессе обработки данных инструментом «Корреляция» в программе Excel.

Как видим из таблицы, коэффициент корреляции фондовооруженности (Столбец 2 ) и энерговооруженности (Столбец 1 ) составляет 0,92, что соответствует очень сильной взаимосвязи. Между производительностью труда (Столбец 3 ) и энерговооруженностью (Столбец 1 ) данный показатель равен 0,72, что является высокой степенью зависимости. Коэффициент корреляции между производительностью труда (Столбец 3 ) и фондовооруженностью (Столбец 2 ) равен 0,88, что тоже соответствует высокой степени зависимости. Таким образом, можно сказать, что зависимость между всеми изучаемыми факторами прослеживается довольно сильная.

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

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

Существует 2 метода вычисления коэффициента корреляции: метод квадратов(Пирсона), метод рангов (Спирмена).

Наиболее точным является метод квадратов (Пирсона), при котором коэффициент корреляции определяется по формуле: , где

r ху ― коэффициент корреляции между статистическим рядом X и Y.

d х ― отклонение каждого из чисел статистического ряда X от своей средней арифметической.

d у ― отклонение каждого из чисел статистического ряда Y от своей средней арифметической.

В зависимости от силы связи и ее направления коэффициент корреляции может находиться в пределах от 0 до 1 (-1). Коэффициент корреляции, равный 0, говорит о полном отсутствии связи. Чем ближе уровень коэффициента корреляции к 1 или (-1), тем соответственно больше, теснее измеряемая им прямая или обратная связь. При коэффициенте корреляции равном 1 или (-1) связь полная, функциональная.

Схема оценки силы корреляционной связи по коэффициенту корреляции

Сила связи	Величина коэффициента корреляции при наличии
	прямой связи (+)	обратной связи (-)
Связь отсутствует
Связь малая (слабая)	от 0 до +0,29	от 0 до –0,29
Связь средняя (умеренная)	от +0,3 до +0,69	от –0,3 до –0,69
Связь большая (сильная)	от +0,7 до +0,99	от –0,7 до –0,99
Связь полная (функциональная)

Для вычисления коэффициента корреляции по методу квадратов составляется таблица из 7 колонок. Разберем процесс вычисления на примере:

ОПРЕДЕЛИТЬ СИЛУ И ХАРАКТЕР СВЯЗИ МЕЖДУ

	Пора- ность зобом (V y )	d x = V x –M x	d y = V y –M y	d x d y	d x 2	d y 2
				Σ -1345 ,0	Σ 13996 ,0	Σ 313 , 47

1. Определяем среднее содержание йода в воде (в мг/л).

мг/л

2.Определяем среднюю пораженность зобом в %.

3. Определяем отклонение каждого V x от М x , т.е. d x .

201–138=63; 178–138=40 и т.д.

4. Аналогично определяем отклонение каждого V у от M у, т.е. d у.

0,2–3,8=-3,6; 0,6–38=-3,2 и т.д.

5. Определяем произведения отклонений. Полученное произведение суммируем и получаем.

6. d х возводим в квадрат и результаты суммируем, получаем.

7. Аналогично возводим в квадрат d у, результаты суммируем, получим

8. Наконец, все полученные суммы подставляем в формулу:

Для решения вопроса о достоверности коэффициента корреляции определяют его среднюю ошибку по формуле:

(Если число наблюдений менее 30, тогда в знаменателе n–1).

В нашем примере

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

В нашем примере

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

Коэффициент корреляции можно определить несколько менее точным, но намного более легким способом ― методом рангов (Спирмена).

Метод Спирмена: P=1-(6∑d 2 /n-(n 2 -1))

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

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

определить разность рангов между х и у (d): d = х - у

возвести полученную разность рангов в квадрат (d 2)

получить сумму квадратов разности (Σ d 2) и подставить полученные значения в формулу:

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

Обоснование выбора метода: для решения задачи может быть выбран только метод ранговой корреляции, т.к. первый ряд признака "стаж работы в годах" имеет открытые варианты (стаж работы до 1 года и 7 и более лет), что не позволяет использовать для установления связи между сопоставляемыми признаками более точный метод - метод квадратов.

Решение . Последовательность расчетов изложена в тексте, результаты представлены в табл. 2.

Таблица 2

Стаж работы в годах	Число травм	Порядковые номера (ранги)		Разность рангов	Квадрат разности рангов
				d(х-у)	d 2

Каждый из рядов парных признаков обозначить через "х" и через "у" (графы 1-2).

Величину каждого из признаков заменить ранговым (порядковым) номером. Порядок раздачи рангов в ряду "x" следующий: минимальному значению признака (стаж до 1 года) присвоен порядковый номер "1", последующим вариантам этого же ряда признака соответственно в порядке увеличения 2-й, 3-й, 4-й и 5-й порядковые номера - ранги (см. графу 3). Аналогичный порядок соблюдается при раздаче рангов второму признаку "у" (графа 4). В тех случаях, когда встречаются несколько одинаковых по величине вариант (например, в задаче-эталоне это 12 и 12 травм на 100 работающих при стаже 3-4 года и 5-6 лет, порядковый номер обозначить средним числом из суммы их порядковых номеров. Эти данные о числе травм (12 травм) при ранжировании должны занимать 2 и 3 места, таким образом среднее число из них равно (2 + 3)/2 = 2,5. Таким образом, числу травм "12" и "12" (признаку) следует раздать ранговые номера одинаковые - "2,5" (графа 4).

Определить разность рангов d = (х - у) - (графа 5)

Разность рангов возвести в квадрат (d 2) и получить сумму квадратов разности рангов Σ d 2 (графа 6).

Произвести расчет коэффициента ранговой корреляции по формуле:

где n - число сопоставляемых пар вариант в ряду "x" и в ряду "у"

Где x·y , x , y - средние значения выборок; σ(x), σ(y) - среднеквадратические отклонения.
Кроме того, коэффициент линейной парной корреляции может быть определен через коэффициент регрессии b: , где σ(x)=S(x), σ(y)=S(y) - среднеквадратические отклонения, b - коэффициент перед x в уравнении регрессии y=a+bx .

Другие варианты формул:
или

К xy - корреляционный момент (коэффициент ковариации)

Линейный коэффициент корреляции принимает значения от –1 до +1 (см. шкалу Чеддока). Например, при анализе тесноты линейной корреляционной связи между двумя переменными получен коэффициент парной линейной корреляции, равный –1 . Это означает, что между переменными существует точная обратная линейная зависимость.

Геометрический смысл коэффициента корреляции : r xy показывает, насколько различается наклон двух линий регрессии: y(x) и х(у) , насколько сильно различаются результаты минимизации отклонений по x и по y . Чем больше угол между линиями, то тем больше r xy .
Знак коэффициента корреляции совпадает со знаком коэффициента регрессии и определяет наклон линии регрессии, т.е. общую направленность зависимости (возрастание или убывание). Абсолютная величина коэффициента корреляции определяется степенью близости точек к линии регрессии.

Свойства коэффициента корреляции

1. |r xy | ≤ 1;
2. если X и Y независимы, то r xy =0, обратное не всегда верно;
3. если |r xy |=1, то Y=aX+b, |r xy (X,aX+b)|=1, где a и b постоянные, а ≠ 0;
4. |r xy (X,Y)|=|r xy (a 1 X+b 1 , a 2 X+b 2)|, где a 1 , a 2 , b 1 , b 2 – постоянные.

Инструкция . Укажите количество исходных данных. Полученное решение сохраняется в файле Word (см. Пример нахождения уравнения регрессии). Также автоматически создается шаблон решения в Excel . .

Количество строк (исходных данных)
Заданы итоговые значения величин (∑x, ∑x 2 , ∑xy, ∑y, ∑y 2)

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

Задача:
Имеется связанная выборка из 26 пар значений (х k ,y k ):

k	1	2	3	4	5	6	7	8	9	10
x k	25.20000	26.40000	26.00000	25.80000	24.90000	25.70000	25.70000	25.70000	26.10000	25.80000
y k	30.80000	29.40000	30.20000	30.50000	31.40000	30.30000	30.40000	30.50000	29.90000	30.40000

k	11	12	13	14	15	16	17	18	19	20
x k	25.90000	26.20000	25.60000	25.40000	26.60000	26.20000	26.00000	22.10000	25.90000	25.80000
y k	30.30000	30.50000	30.60000	31.00000	29.60000	30.40000	30.70000	31.60000	30.50000	30.60000

k	21	22	23	24	25	26
x k	25.90000	26.30000	26.10000	26.00000	26.40000	25.80000
y k	30.70000	30.10000	30.60000	30.50000	30.70000	30.80000

Требуется вычислить/построить:
- коэффициент корреляции;
- проверить гипотезу зависимости случайных величин X и Y, при уровне значимости α = 0.05 ;
- коэффициенты уравнения линейной регрессии;
- диаграмму рассеяния (корреляционное поле) и график линии регрессии;

РЕШЕНИЕ:

1. Вычисляем коэффициент корреляции.

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

Вычислить коэффициент корреляции можно по следующим формулам:

n

Σ

k = 1

(x k -M x) 2 , σ y 2 =

M x

=

1 n

n Σ k = 1

x k ,

M y

=

или по формуле R x,y = M xy - M x M y S x S y (1.4), где: M x = 1 n n Σ k = 1 x k , M y = 1 n n Σ k = 1 y k , M xy = 1 n n Σ k = 1 x k y k (1.5) S x 2 = 1 n n Σ k = 1 x k 2 - M x 2 , S y 2 = 1 n n Σ k = 1 y k 2 - M y 2 (1.6) На практике, для вычисления коэффициента корреляции чаще используется формула (1.4) т.к. она требует меньше вычислений. Однако если предварительно была вычислена ковариация cov(X,Y) , то выгоднее использовать формулу (1.1), т.к. кроме собственно значения ковариации можно воспользоваться и результатами промежуточных вычислений. 1.1 Вычислим коэффициент корреляции по формуле (1.4) , для этого вычислим значения x k 2 , y k 2 и x k y k и занесем их в таблицу 1. Таблица 1 k x k y k х k 2 y k 2 х k y k 1 2 3 4 5 6 1 25.2 30.8 635.04000 948.64000 776.16000 2 26.4 29.4 696.96000 864.36000 776.16000 3 26.0 30.2 676.00000 912.04000 785.20000 4 25.8 30.5 665.64000 930.25000 786.90000 5 24.9 31.4 620.01000 985.96000 781.86000 6 25.7 30.3 660.49000 918.09000 778.71000 7 25.7 30.4 660.49000 924.16000 781.28000 8 25.7 30.5 660.49000 930.25000 783.85000 9 26.1 29.9 681.21000 894.01000 780.39000 10 25.8 30.4 665.64000 924.16000 784.32000 11 25.9 30.3 670.81000 918.09000 784.77000 12 26.2 30.5 686.44000 930.25000 799.10000 13 25.6 30.6 655.36000 936.36000 783.36000 14 25.4 31 645.16000 961.00000 787.40000 15 26.6 29.6 707.56000 876.16000 787.36000 16 26.2 30.4 686.44000 924.16000 796.48000 17 26 30.7 676.00000 942.49000 798.20000 18 22.1 31.6 488.41000 998.56000 698.36000 19 25.9 30.5 670.81000 930.25000 789.95000 20 25.8 30.6 665.64000 936.36000 789.48000 21 25.9 30.7 670.81000 942.49000 795.13000 22 26.3 30.1 691.69000 906.01000 791.63000 23 26.1 30.6 681.21000 936.36000 798.66000 24 26 30.5 676.00000 930.25000 793.00000 25 26.4 30.7 696.96000 942.49000 810.48000 26 25.8 30.8 665.64000 948.64000 794.64000 1.2. Вычислим M x по формуле (1.5) . 1.2.1. x k x 1 + x 2 + … + x 26 = 25.20000 + 26.40000 + ... + 25.80000 = 669.500000 1.2.2. 669.50000 / 26 = 25.75000 M x = 25.750000 1.3. Аналогичным образом вычислим M y . 1.3.1. Сложим последовательно все элементы y k y 1 + y 2 + … + y 26 = 30.80000 + 29.40000 + ... + 30.80000 = 793.000000 1.3.2. Разделим полученную сумму на число элементов выборки 793.00000 / 26 = 30.50000 M y = 30.500000 1.4. Аналогичным образом вычислим M xy . 1.4.1. Сложим последовательно все элементы 6-го столбца таблицы 1 776.16000 + 776.16000 + ... + 794.64000 = 20412.830000 1.4.2. Разделим полученную сумму на число элементов 20412.83000 / 26 = 785.10885 M xy = 785.108846 1.5. Вычислим значение S x 2 по формуле (1.6.) . 1.5.1. Сложим последовательно все элементы 4-го столбца таблицы 1 635.04000 + 696.96000 + ... + 665.64000 = 17256.910000 1.5.2. Разделим полученную сумму на число элементов 17256.91000 / 26 = 663.72731 1.5.3. Вычтем из последнего числа квадрат величины M x получим значение для S x 2 S x 2 = 663.72731 - 25.75000 2 = 663.72731 - 663.06250 = 0.66481 1.6. Вычислим значение S y 2 по формуле (1.6.) . 1.6.1. Сложим последовательно все элементы 5-го столбца таблицы 1 948.64000 + 864.36000 + ... + 948.64000 = 24191.840000 1.6.2. Разделим полученную сумму на число элементов 24191.84000 / 26 = 930.45538 1.6.3. Вычтем из последнего числа квадрат величины M y получим значение для S y 2 S y 2 = 930.45538 - 30.50000 2 = 930.45538 - 930.25000 = 0.20538 1.7. Вычислим произведение величин S x 2 и S y 2 . S x 2 S y 2 = 0.66481 0.20538 = 0.136541 1.8. Извлечем и последнего числа квадратный корень, получим значение S x S y . S x S y = 0.36951 1.9. Вычислим значение коэффициента корреляции по формуле (1.4.) . R = (785.10885 - 25.75000 30.50000) / 0.36951 = (785.10885 - 785.37500) / 0.36951 = -0.72028 ОТВЕТ: R x,y = -0.720279 2. Проверяем значимость коэффициента корреляции (проверяем гипотезу зависимости). Поскольку оценка коэффициента корреляции вычислена на конечной выборке, и поэтому может отклоняться от своего генерального значения, необходимо проверить значимость коэффициента корреляции. Проверка производится с помощью t -критерия: t = R x,y √ n - 2 √ 1 - R 2 x,y (2.1) Случайная величина t следует t -распределению Стьюдента и по таблице t -распределения необходимо найти критическое значение критерия (t кр.α) при заданном уровне значимости α . Если вычисленное по формуле (2.1) t по модулю окажется меньше чем t кр.α , то зависимости между случайными величинами X и Y нет. В противном случае, экспериментальные данные не противоречат гипотезе о зависимости случайных величин. 2.1. Вычислим значение t -критерия по формуле (2.1) получим: t = -0.72028 √ 26 - 2 √ 1 - (-0.72028) 2 = -5.08680 2.2. Определим по таблице t -распределения критическое значение параметра t кр.α Искомое значение t кр.α располагается на пересечении строки соответствующей числу степеней свободы и столбца соответствующего заданному уровню значимости α . В нашем случае число степеней свободы есть n - 2 = 26 - 2 = 24 и α = 0.05 , что соответствует критическому значению критерия t кр.α = 2.064 (см. табл. 2) Таблица 2 t -распределение Число степеней свободы (n - 2) α = 0.1 α = 0.05 α = 0.02 α = 0.01 α = 0.002 α = 0.001 1 6.314 12.706 31.821 63.657 318.31 636.62 2 2.920 4.303 6.965 9.925 22.327 31.598 3 2.353 3.182 4.541 5.841 10.214 12.924 4 2.132 2.776 3.747 4.604 7.173 8.610 5 2.015 2.571 3.365 4.032 5.893 6.869 6 1.943 2.447 3.143 3.707 5.208 5.959 7 1.895 2.365 2.998 3.499 4.785 5.408 8 1.860 2.306 2.896 3.355 4.501 5.041 9 1.833 2.262 2.821 3.250 4.297 4.781 10 1.812 2.228 2.764 3.169 4.144 4.587 11 1.796 2.201 2.718 3.106 4.025 4.437 12 1.782 2.179 2.681 3.055 3.930 4.318 13 1.771 2.160 2.650 3.012 3.852 4.221 14 1.761 2.145 2.624 2.977 3.787 4.140 15 1.753 2.131 2.602 2.947 3.733 4.073 16 1.746 2.120 2.583 2.921 3.686 4.015 17 1.740 2.110 2.567 2.898 3.646 3.965 18 1.734 2.101 2.552 2.878 3.610 3.922 19 1.729 2.093 2.539 2.861 3.579 3.883 20 1.725 2.086 2.528 2.845 3.552 3.850 21 1.721 2.080 2.518 2.831 3.527 3.819 22 1.717 2.074 2.508 2.819 3.505 3.792 23 1.714 2.069 2.500 2.807 3.485 3.767 24 1.711 2.064 2.492 2.797 3.467 3.745 25 1.708 2.060 2.485 2.787 3.450 3.725 26 1.706 2.056 2.479 2.779 3.435 3.707 27 1.703 2.052 2.473 2.771 3.421 3.690 28 1.701 2.048 2.467 2.763 3.408 3.674 29 1.699 2.045 2.462 2.756 3.396 3.659 30 1.697 2.042 2.457 2.750 3.385 3.646 40 1.684 2.021 2.423 2.704 3.307 3.551 60 1.671 2.000 2.390 2.660 3.232 3.460 120 1.658 1.980 2.358 2.617 3.160 3.373 ∞ 1.645 1.960 2.326 2.576 3.090 3.291 2.2. Сравним абсолютное значение t -критерия и t кр.α Абсолютное значение t -критерия не меньше критического t = 5.08680, t кр.α = 2.064, следовательно экспериментальные данные, с вероятностью 0.95 (1 - α ), не противоречат гипотезе о зависимости случайных величин X и Y. 3. Вычисляем коэффициенты уравнения линейной регрессии. Уравнение линейной регрессии представляет собой уравнение прямой, аппроксимирующей (приблизительно описывающей) зависимость между случайными величинами X и Y. Если считать, что величина X свободная, а Y зависимая от Х, то уравнение регрессии запишется следующим образом Y = a + b X (3.1), где: b = R x,y σ y σ x = R x,y S y S x (3.2), a = M y - b M x (3.3) Рассчитанный по формуле (3.2) коэффициент b называют коэффициентом линейной регрессии. В некоторых источниках a называют постоянным коэффициентом регрессии и b соответственно переменным. Погрешности предсказания Y по заданному значению X вычисляются по формулам: Величину σ y/x (формула 3.4) еще называют остаточным средним квадратическим отклонением , оно характеризует уход величины Y от линии регрессии, описываемой уравнением (3.1), при фиксированном (заданном) значении X.

.

S y 2 / S x 2 = 0.20538 / 0.66481 = 0.30894. Извлечем из последнего числа квадратный корень - получим:
S y / S x = 0.55582

3.3 Вычислим коэффициент b по формуле (3.2)

b = -0.72028 0.55582 = -0.40035

3.4 Вычислим коэффициент a по формуле (3.3)

a = 30.50000 - (-0.40035 25.75000) = 40.80894

3.5 Оценим погрешности уравнения регрессии .

3.5.1 Извлечем из S y 2 квадратный корень получим:

= 0.31437
3.5.4 Вычислим относительную погрешность по формуле (3.5)

δ y/x = (0.31437 / 30.50000)100% = 1.03073%

4. Строим диаграмму рассеяния (корреляционное поле) и график линии регрессии.

Диаграмма рассеяния - это графическое изображение соответствующих пар (x k , y k ) в виде точек плоскости, в прямоугольных координатах с осями X и Y. Корреляционное поле является одним из графических представлений связанной (парной) выборки. В той же системе координат строится и график линии регрессии. Следует тщательно выбрать масштабы и начальные точки на осях, чтобы диаграмма была максимально наглядной.

4.1. Находим минимальный и максимальный элемент выборки X это 18-й и 15-й элементы соответственно, x min = 22.10000 и x max = 26.60000.

4.2. Находим минимальный и максимальный элемент выборки Y это 2-й и 18-й элементы соответственно, y min = 29.40000 и y max = 31.60000.

4.3. На оси абсцисс выбираем начальную точку чуть левее точки x 18 = 22.10000, и такой масштаб, чтобы на оси поместилась точка x 15 = 26.60000 и отчетливо различались остальные точки.

4.4. На оси ординат выбираем начальную точку чуть левее точки y 2 = 29.40000, и такой масштаб, чтобы на оси поместилась точка y 18 = 31.60000 и отчетливо различались остальные точки.

4.5. На оси абсцисс размещаем значения x k , а на оси ординат значения y k .

4.6. Наносим точки (x 1 , y 1 ), (x 2 , y 2 ),…,(x 26 , y 26 ) на координатную плоскость. Получаем диаграмму рассеяния (корреляционное поле), изображенное на рисунке ниже.

4.7. Начертим линию регрессии.

Для этого найдем две различные точки с координатами (x r1 , y r1) и (x r2 , y r2) удовлетворяющие уравнению (3.6), нанесем их на координатную плоскость и проведем через них прямую. В качестве абсциссы первой точки возьмем значение x min = 22.10000. Подставим значение x min в уравнение (3.6), получим ординату первой точки. Таким образом имеем точку с координатами (22.10000, 31.96127). Аналогичным образом получим координаты второй точки, положив в качестве абсциссы значение x max = 26.60000. Вторая точка будет: (26.60000, 30.15970).

Линия регрессии показана на рисунке ниже красным цветом

Обратите внимание, что линия регрессии всегда проходит через точку средних значений величин Х и Y, т.е. с координатами (M x , M y).

1.Открыть программу Excel

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

1 столбик — № испытуемого

2 столбик — агрессивность в баллах

3 столбик — неуверенность в себе в баллах

3.Затем необходимо выбрать пустую ячейку рядом с таблицей и нажать на значок f(x) в панели Excel

4.Откроется меню функций, среди категорий необходимо выбрать Статистические , а затем среди списка функций по алфавиту найти КОРРЕЛ и нажать ОК

5.Затем откроется меню аргументов функции, которое позволит выбрать нужные нам столбики с данными. Для выбора первого столбика Агрессивность нужно нажать на синюю кнопочку у строки Массив1

6.Выберем данные для Массива1 из столбика Агрессивность и нажмем на синюю кнопочку в диалоговом окне

7. Затем аналогично Массиву 1 нажмём на синюю кнопочку у строки Массив2

8.Выберем данные для Массива2 — столбик Неуверенность в себе и опять нажмем синюю кнопку, затем ОК

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

Таким образом, статистическим выводом эксперимента будет: r = 0,225, выявлена умеренная положительная взаимосвязь между переменными агрессивность и неуверенность в себе.

В некоторых исследованиях требуется указывать р-уровень значимости коэффициента корреляции, однако программа Excel, в отличие от SPSS, не предоставляет такой возможности. Ничего страшного, есть (А.Д. Наследов).

Также Вы можете и приложить её к результатам исследования.