Функция корелл в excel — Мир ПК

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Как рассчитать коэффициент корреляции в Excel

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

Корреляция измеряет мощность и направление связи между x и y. На рисунке представлены различные типы корреляции в виде графиков рассеяния упорядоченных пар (x, y). По традиции переменная х размещается на горизонтальной оси, а y — на вертикальной.

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

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

Оставшаяся часть статьи посвящена линейным взаимосвязям между зависимой и независимой переменными.

Коэффициент корреляции

Коэффициент корреляции, r, предоставляет нам как силу, так и направление связи между независимой и зависимой переменными. Значения r находятся в диапазоне между — 1.0 и + 1.0. Когда r имеет положительное значение, связь между х и у является положительной (график A на рисунке), а когда значение r отрицательно, связь также отрицательна (график В). Коэффициент корреляции, близкий к нулевому значению, свидетельствует о том, что между х и у связи не существует график С).

Сила связи между х и у определяется близостью коэффициента корреляции к — 1.0 или +- 1.0. Изучите следующий рисунок.

График A показывает идеальную положительную корреляцию между х и у при r = + 1.0. График В — идеальная отрицательная корреляция между х и у при r = — 1.0. Графики С и D — примеры более слабых связей между зависимой и независимой переменными.

Коэффициент корреляции, r, определяет, как силу, так и направление связи между зависимой и независимой переменными. Значения r находятся в диапазоне от — 1.0 (сильная отрицательная связь) до + 1.0 (сильная положительная связь). При r= 0 между переменными х и у нет никакой связи.

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

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

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

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

Использование Excel для вычисления коэффициентов корреляции

Я уверен, что, взглянув на эти ужасные вычисления коэффициентов корреляции, вы испытаете истинную радость, узнав, что программа Excel может выполнить за вас всю эту работу с помощью функции КОРРЕЛ со следующими характеристиками:

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

массив 1 = диапазон данных для первой переменной,

массив 2 = диапазон данных для второй переменной.

Например, на рисунке показана функция КОРРЕЛ, используемая при вычислении коэффициента корреляции для примера с экзаменационной оценкой.

Вам также могут быть интересны следующие статьи

31 комментарий

Большое спасибо за простой и внятный а также общедоступный способ разжевывания информации. Теперь наконец-то обработаю в диссертации на вашем примере свою корреляцию. Побольше бы таких статей. Автору Большой Респект и Махталитет!

Согласен, всё описал доступно и по теме. То, что искал. Мне нужно было показать зависимость продаж магазина от погоды. Всё получилось и вполне логично.
Надо посмотреть и др. статьи, надеюсь найду ещё что-то полезное.
Спасибо.

А если массивов не 2, целых 7, тогда как посчитать?

Увы, в таких сложных расчетах я не силен. Возможно, нам поможет кто-нибудь из читателей

Тогда воспользуйтесь формулой Данные->Анализ Данных->выбираете корреляция

Да, интересный вопрос! Что будет если переменных хотя бы 3! ))

Есть все-таки вопрос: в приведенном примере можно ли сказать, что один дополнительный час улучшает оценку на 0,86 пункта?

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

Огромное спасибо за понятное изложение!!

По формуле я посчитала, все понятно. Но через Excel не получается. Поясните подробнее

Резеда, опишите, подробнее, что вы делаете и что у вас не получается

Подскажите,пожалуйста,а как по значению корреляции построить такой график,и можно ли его получить,если переменная непараметрическая(да-1,0-нет)?

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

Высчитывать ранговую или порядкову корреляцию типа 121211112211/111221122121111 и по всем факторам выходят понятные значения, и лишь при сравнении 2х определенных массивов постоянно выдает результат 2.26…..Е-17 что это значит?

результат 2.26…..Е-17 что это значит?
я думаю, что это равно 2.26 умножить на 10 в минус 17ой степени, т.е. ну очень маленькое число корреляции и эти два массива не связаны..

Добрый день!
Обрабатываю экспериментальные данные, в Excel, выполнена аппроксимация графика. Получены уравнения. Не могу проверить на адекватность полученные уравнения (логарифмические, полиномиальные) с линейными получилось. А вот у остальных не знаю как ввести данные.
Кто нибудь подскажет, как это выполнить?
Где можно посмотреть алгоритм ввода?
Буду очень рада вашей помощи.

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

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

Подскажите, как использовать корреляцию для 3 и более переменных ?

В Эксел нет формулы расчета кратной корреляции.
Для 3 или более переменных нужно рассчитывать их попарно.
Или использовать кратную (многомерную) корреляцию: произведение усредненных массивов (данные минус среднее значение), деленное на кратную степень произведения дисперсий массивов. То есть при трех массивах вычисляете дисперсии каждого массива, перемножаете их и вычисляете КУБИЧЕСКИй корень (в знаменателе). При 5 массивах — произведение 5 массивов (центрированных — с вычитанием среднего) деленное корень пятой степени из произведения 5 дисперсий массивов

Проще вычислить сумму центрированных произведений переменных и разделить на произведение среднеквадратических отклонений переменных

Комментариев, подобных моему тут, конечно, уже много, но всё-же!
Спасибо за столь доступное и простое описание! Теперь действительно понятно стало!

Спасибо, очень понятно.
Вы приводите в качестве примера расчет корреляции по Персонал, т.е для количестве них переменных (напр. потраченные часы и оценка). Подскажите, а где в Excel функция ANOVA или MANOVA — расчет корреляция ной взаимосвязи между качественным и количестве ними переменными?

Добрый день! Как рассчитать корреляцию в еxcele я поняла. Несколько уточняющих вопросов. Во-первых, это рассчитывается ведь кор. Пирсона? И второе. В калькуляторах, рассчитывающих кор. Пирсона, к значению корреляции указывается еще и «p» (обычно p0,05 или 0,01), а в еxcele он какой? И третье. Если формула везде расчета Пирсона одна, то почему в разных калькуляторах, в том числе, и в сравнении с расчетами в еxcele, получаются разные результаты? По поводу «р» — еще просьба: я слабо дружу с матимаиткой и не дружу со статистикой вообще. Можете ли мне объяснить доступным языком про это р?

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

Подскажите, пожалуйста, если нужно рассчитать коэфыициент корреляции для выборки Х( -1,95; -4,13, -8; -10; -41,5) и У (-0,22; 1,54; -8,8; -10,8; 8,04; 0,47) . В эксель через КОРРЕЛ не считает.. Вообще при таком разбросе чисел (от отрицательных до положительных) это возможно установить силу связи между Х и У? И как тогда рассчитывать. То что связь мужду Х и У есть это исходные данные, нужно оценить силу связи этой.. Может есть другие идеи?

А у формулы определения коэффициента корреляции есть автор?

2 способа корреляционного анализа в Microsoft Excel

Корреляционный анализ – популярный метод статистического исследования, который используется для выявления степени зависимости одного показателя от другого. В Microsoft Excel имеется специальный инструмент, предназначенный для выполнения этого типа анализа. Давайте выясним, как пользоваться данной функцией.

Суть корреляционного анализа

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

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

Расчет коэффициента корреляции

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

Способ 1: определение корреляции через Мастер функций

Одним из способов, с помощью которого можно провести корреляционный анализ, является использование функции КОРРЕЛ. Сама функция имеет общий вид КОРРЕЛ(массив1;массив2).

Выделяем ячейку, в которой должен выводиться результат расчета. Кликаем по кнопке «Вставить функцию», которая размещается слева от строки формул.

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

В поле «Массив2» нужно внести координаты второго столбца. У нас это затраты на рекламу. Точно так же, как и в предыдущем случае, заносим данные в поле.

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

Способ 2: вычисление корреляции с помощью пакета анализа

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

Переходим во вкладку «Файл».

В открывшемся окне перемещаемся в раздел «Параметры».

Далее переходим в пункт «Надстройки».

В нижней части следующего окна в разделе «Управление» переставляем переключатель в позицию «Надстройки Excel», если он находится в другом положении. Жмем на кнопку «OK».

В окне надстроек устанавливаем галочку около пункта «Пакет анализа». Жмем на кнопку «OK».

После этого пакет анализа активирован. Переходим во вкладку «Данные». Как видим, тут на ленте появляется новый блок инструментов – «Анализ». Жмем на кнопку «Анализ данных», которая расположена в нем.

Открывается список с различными вариантами анализа данных. Выбираем пункт «Корреляция». Кликаем по кнопке «OK».

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

Параметр «Группирование» оставляем без изменений – «По столбцам», так как у нас группы данных разбиты именно на два столбца. Если бы они были разбиты построчно, то тогда следовало бы переставить переключатель в позицию «По строкам».

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

Когда все настройки установлены, жмем на кнопку «OK».

Так как место вывода результатов анализа было оставлено по умолчанию, мы перемещаемся на новый лист. Как видим, тут указан коэффициент корреляции. Естественно, он тот же, что и при использовании первого способа – 0,97. Это объясняется тем, что оба варианта выполняют одни и те же вычисления, просто произвести их можно разными способами.

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Функция коррел в excel

КОРРЕЛ (функция КОРРЕЛ)

​Смотрите также​ значениями в столбце​ «Корреляция».​​ другу. Взаимосвязь прямая:​​ квадрат.​

Описание

​ y:​Если значение близко к​Так как ячейка A4​ отобразить результаты формул,​Обычно функцию Ч не​ число.​ отобразить результаты формул,​ или ссылка на​ а затем —​ A и B.​

Синтаксис

​ листа Excel. Чтобы​

​ содержит текст, логические​В этой статье описаны​

​ х3 практически отсутствует.​​Нажимаем ОК. Задаем параметры​ растет y –​

​Находим суммы значений в​​Y – независимая переменная,​ единице (от 0,9,​ содержит логическое значение​

Замечания

​ выделите их и​ требуется использовать в​Ч(значение)​ выделите их и​ ячейку, содержащую текст,​ клавишу ВВОД. При​0,997054486​ отобразить результаты формул,​

​ значения или пустые​ синтаксис формулы и​Изобразим наглядно корреляционные отношения​ для анализа данных.​ растет х, уменьшается​

​ полученных колонках (с​ x – зависимая.​ например), то между​ ИСТИНА, возвращается значение​ нажмите клавишу F2,​ формулах, поскольку необходимые​Аргументы функции Ч описаны​

​ нажмите клавишу F2,​ который нужно преобразовать.​

​ необходимости измените ширину​

​В этой статье описаны​ выделите их и​

Пример

​ ячейки, то такие​ использование функции​ с помощью графиков.​ Входной интервал –​ y – уменьшается​ помощью функции АВТОСУММА).​ Необходимо найти силу​ наблюдаемыми объектами существует​ 1.​ а затем —​ преобразования значений выполняются​ ниже.​ а затем —​

​Текст может быть в​

​ столбцов, чтобы видеть​

​ синтаксис формулы и​

​ нажмите клавишу F2,​

​ значения пропускаются; однако​

​Сильная прямая связь между​

​ диапазон ячеек со​

​ Перемножаем их. Результат​

​ сильная прямая взаимосвязь.​

​ клавишу ВВОД. При​

​ в Microsoft Excel​

​ клавишу ВВОД. При​ любом формате, допускаемом​ все данные.​

КОРЕНЬПИ (функция КОРЕНЬПИ)

​ а затем — клавишу​ ячейки, которые содержат​в Microsoft Excel.​​ y и х1.​​ значениями. Группирование –​

Описание

​​ возводим в квадрат​ и направление (прямая​

Синтаксис

​ Если коэффициент близок​

​=Ч(A5)​ необходимости измените ширину​

​ автоматически. Эта функция​​ Обязательный. Значение, которое требуется​ необходимости измените ширину​

Замечание

​ в Microsoft Excel​Формула​КОРЕНЬПИ​

Пример

​ ВВОД. При необходимости​ нулевые значения, учитываются.​Возвращает коэффициент корреляции между​Сильная обратная связь между​ по столбцам (анализируемые​Корреляционная матрица представляет собой​ (функция КОРЕНЬ).​ / обратная) связи​ к другой крайней​Так как ячейка A5​ столбцов, чтобы видеть​ предназначена для обеспечения​ преобразовать. Функция Ч​

​ столбцов, чтобы видеть​

​в Microsoft Excel.​

​ измените ширину столбцов,​

​Если «массив1» и «массив2″​

​ диапазонами ячеек «массив1″​

​ y и х2.​ данные сгруппированы в​

​ таблицу, на пересечении​

Функция ЗНАЧЕН

​Осталось посчитать частное (числитель​ между ними. Формула​ точке диапазона (-1),​​ содержит дату, возвращается​​ все данные.​

Описание

​ совместимости с другими​ преобразует значения, приведенные​

Синтаксис

​ или времени. Если​Результат​

​Возвращает квадратный корень из​​ чтобы видеть все​ имеют различное количество​ и «массив2». Коэффициент​ Изменения значений происходят​

Примечания

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

​ текст не соответствует​=КОРЕНЬПИ(1)​ значения выражения (число​ данные.​ точек данных, функция​ корреляции используется для​ параллельно друг другу.​ – ссылка на​ которой находятся коэффициенты​

Пример

​ известны).​ так:​ имеется сильная обратная​ (изменяется в зависимости​7​В приложении Excel даты​Аргумент «значение» равен или​Описание​ ни одному из​Квадратный корень числа «пи».​ * ПИ).​Данные1​ КОРРЕЛ возвращает значение​

​ определения взаимосвязи между​

​ ячейку, с которой​

​ корреляции между соответствующими​

​Между переменными определяется сильная​Чтобы упростить ее понимание,​

​ взаимосвязь. Когда значение​

​ от используемой системы​

​Четный​ хранятся в виде​ ссылается на значение​Результат​

​ этих форматов, то​

Ч (функция Ч)

​1,772454​КОРЕНЬПИ(число)​Данные2​​ ошибки #Н/Д.​​ двумя свойствами. Например,​

Описание

​ растет, х падает.​ начнется построение матрицы.​

Синтаксис

​ значениями. Имеет смысл​

​ прямая связь.​ разобьем на несколько​

​ находится где-то посередине​​ вычисления дат).​ИСТИНА​ последовательных чисел, что​Возвращаемое значение​

​=ЗНАЧЕН(«1 000 ₽»)​ функция ЗНАЧЕН возвращает​

​Аргументы функции КОРЕНЬПИ описаны​

​Если какой-либо из массивов​ можно установить зависимость​ Значения y увеличиваются​

​ Размер диапазона определится​ ее строить для​

​Встроенная функция КОРРЕЛ позволяет​

​ позволяет использовать их​

​Числовой эквивалент текстовой строки​

Замечания

​ значение ошибки #ЗНАЧ!.​Квадратный корень числа «пи»,​ ниже.​9​ пуст или если​ между средней температурой​ – значения х​ автоматически.​ нескольких переменных.​

​ избежать сложных расчетов.​Найдем средние значения переменных,​ 1 или от​=Ч(«7»)​Формула​ в вычислениях. По​То же число​ «1 000 ₽»​Обычно функцию ЗНАЧЕН не​ умноженного на 2.​Число​2​ «s» (стандартное отклонение)​

Пример

​ в помещении и​ уменьшаются.​После нажатия ОК в​Матрица коэффициентов корреляции в​ Рассчитаем коэффициент парной​ используя функцию СРЗНАЧ:​ 0 до -1,​Так как «7» является​Описание​ умолчанию дате 1​»Дата» в одном из​1000​ требуется использовать в​

​ Обязательный. Число, которое умножается​

​ их значений равно​

​Отсутствие взаимосвязи между значениями​

​ выходном диапазоне появляется​

​ Excel строится с​

​ корреляции в Excel​

​Посчитаем разницу каждого y​ то речь идет​ текстом, возвращается значение​

​ января 1900 г. соответствует​

​ встроенных форматов Microsoft​=ЗНАЧЕН(«16:48:00»)-ЗНАЧЕН(«12:00:00»)​ формулах, поскольку необходимые​

​В этой статье описаны​

​4​ нулю, функция КОРРЕЛ​КОРРЕЛ(массив1;массив2)​ y и х3.​

​ корреляционная матрица. На​

​ помощью инструмента «Корреляция»​

​ с ее помощью.​ и yсредн., каждого​ о слабой связи​ 0.​=Ч(A2)​ число 1, а​

​Числовой формат, эквивалентный 4​

​ преобразования значений выполняются​ синтаксис формулы и​Если число

Коэффициент парной корреляции в Excel

​ возвращает значение ошибки​Аргументы функции КОРРЕЛ описаны​ Изменения х3 происходят​ пересечении строк и​ из пакета «Анализ​ Вызываем мастер функций.​ х и хсредн.​ (прямой или обратной).​0​

​Так как ячейка A2​ 1 января 2008​Указанная дата в числовом​ часам 48 минутам​ в Microsoft Excel​ использование функции​ функция КОРЕНЬПИ возвращает​5​ #ДЕЛ/0!.​ ниже.​ хаотично и никак​ столбцов – коэффициенты​ данных».​ Находим нужную. Аргументы​ Используем математический оператор​ Такую взаимосвязь обычно​Коэффициент корреляции отражает степень​ содержит число, оно​ г. — число 39 448,​ формате​ — «16:48:00»-«12:00:00» (0,2​

Расчет коэффициента корреляции в Excel

​ автоматически. Эта функция​ЗНАЧЕН​ значение ошибки #ЧИСЛО!.​15​Уравнение для коэффициента корреляции​

​Массив1​ не соотносятся с​

​ корреляции. Если координаты​На вкладке «Данные» в​ функции – массив​ «-».​ не учитывают: считается,​ взаимосвязи между двумя​ же и возвращается.​ поскольку интервал между​ИСТИНА​

​ или 4:48)​ предназначена для обеспечения​в Microsoft Excel.​

1. ​Скопируйте образец данных из​6​
2. ​ имеет следующий вид:​ — обязательный аргумент. Диапазон​ изменениями y.​ совпадают, то выводится​ группе «Анализ» открываем​
3. ​ значений y и​
4. ​Теперь перемножим найденные разности:​ что ее нет.​ показателями. Всегда принимает​
5. ​7​ датами составляет 39 448​1​0,2​ совместимости с другими​
6. ​Преобразует строку текста, отображающую​ следующей таблицы и​17​где​ ячеек со значениями.​просмотреть вычисление коэффициента парной​
7. ​ значение 1.​ пакет «Анализ данных»​ массив значений х:​

​Найдем сумму значений в​Рассмотрим на примере способы​

​ значение от -1​=Ч(A3)​ дней.​ЛОЖЬ​В этой статье описаны​ программами электронных таблиц.​ число, в число.​ вставьте их в​Формула​являются средними значениями выборок​

​Массив2​ корреляции в Excel​

​Между значениями y и​ (для версии 2007).​Покажем значения переменных на​ данной колонке. Это​ расчета коэффициента корреляции,​ до 1. Если​Так как ячейка A3​Скопируйте образец данных из​0​

​ синтаксис формулы и​

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

​Скопируйте образец данных из​ЗНАЧЕН(текст)​ ячейку A1 нового​Описание​ СРЗНАЧ(массив1) и СРЗНАЧ(массив2).​ — обязательный аргумент. Второй​Для чего нужен такой​ х1 обнаружена сильная​

​ Если кнопка недоступна,​ графике:​ и будет числитель.​ особенности прямой и​ коэффициент расположился около​

1. ​ содержит текст, возвращается​ следующей таблицы и​Значение ошибки, например #ДЕЛ/0!​ использование функции​ следующей таблицы и​Аргументы функции ЗНАЧЕН описаны​ листа Excel. Чтобы​Результат​Скопируйте образец данных из​ диапазон ячеек со​
2. ​ коэффициент? Для определения​ прямая взаимосвязь. Между​ нужно ее добавить​Видна сильная связь между​Для расчета знаменателя разницы​ обратной взаимосвязи между​ 0, то говорят​ значение 0.​ вставьте их в​Значение ошибки​Ч​ вставьте их в​ ниже.​
3. ​ отобразить результаты формул,​=КОРРЕЛ(A2:A6;B2:B6)​ следующей таблицы и​ значениями.​ взаимосвязи между наблюдаемыми​ х1 и х2​ («Параметры Excel» -​ y и х,​

​ y и y-средн.,​ переменными.​ об отсутствии связи​0​ ячейку A1 нового​Любое другое​в Microsoft Excel.​ ячейку A1 нового​

​Текст​ выделите их и​

1. ​Коэффициент корреляции двух наборов​ вставьте их в​
2. ​Если аргумент, который является​ явлениями и составления​ имеется сильная обратная​ «Надстройки»). В списке​ т.к. линии идут​ х и х-средн.​Значения показателей x и​ между переменными.​=Ч(A4)​
3. ​ листа Excel. Чтобы​0​Возвращает значение, преобразованное в​ листа Excel. Чтобы​ Обязательный. Текст в кавычках​ нажмите клавишу F2,​

​ данных в столбцах​ ячейку A1 нового​

​ массивом или ссылкой,​ прогнозов.​ связь. Связь со​ инструментов анализа выбираем​ практически параллельно друг​

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Как рассчитать коэффициент корреляции в Excel

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

Корреляция измеряет мощность и направление связи между x и y. На рисунке представлены различные типы корреляции в виде графиков рассеяния упорядоченных пар (x, y). По традиции переменная х размещается на горизонтальной оси, а y — на вертикальной.

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

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

Оставшаяся часть статьи посвящена линейным взаимосвязям между зависимой и независимой переменными.

Коэффициент корреляции

Коэффициент корреляции, r, предоставляет нам как силу, так и направление связи между независимой и зависимой переменными. Значения r находятся в диапазоне между — 1.0 и + 1.0. Когда r имеет положительное значение, связь между х и у является положительной (график A на рисунке), а когда значение r отрицательно, связь также отрицательна (график В). Коэффициент корреляции, близкий к нулевому значению, свидетельствует о том, что между х и у связи не существует график С).

Сила связи между х и у определяется близостью коэффициента корреляции к — 1.0 или +- 1.0. Изучите следующий рисунок.

График A показывает идеальную положительную корреляцию между х и у при r = + 1.0. График В — идеальная отрицательная корреляция между х и у при r = — 1.0. Графики С и D — примеры более слабых связей между зависимой и независимой переменными.

Коэффициент корреляции, r, определяет, как силу, так и направление связи между зависимой и независимой переменными. Значения r находятся в диапазоне от — 1.0 (сильная отрицательная связь) до + 1.0 (сильная положительная связь). При r= 0 между переменными х и у нет никакой связи.

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

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

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

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

Использование Excel для вычисления коэффициентов корреляции

Я уверен, что, взглянув на эти ужасные вычисления коэффициентов корреляции, вы испытаете истинную радость, узнав, что программа Excel может выполнить за вас всю эту работу с помощью функции КОРРЕЛ со следующими характеристиками:

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

массив 1 = диапазон данных для первой переменной,

массив 2 = диапазон данных для второй переменной.

Например, на рисунке показана функция КОРРЕЛ, используемая при вычислении коэффициента корреляции для примера с экзаменационной оценкой.

Вам также могут быть интересны следующие статьи

31 комментарий

Большое спасибо за простой и внятный а также общедоступный способ разжевывания информации. Теперь наконец-то обработаю в диссертации на вашем примере свою корреляцию. Побольше бы таких статей. Автору Большой Респект и Махталитет!

Согласен, всё описал доступно и по теме. То, что искал. Мне нужно было показать зависимость продаж магазина от погоды. Всё получилось и вполне логично.
Надо посмотреть и др. статьи, надеюсь найду ещё что-то полезное.
Спасибо.

А если массивов не 2, целых 7, тогда как посчитать?

Увы, в таких сложных расчетах я не силен. Возможно, нам поможет кто-нибудь из читателей

Тогда воспользуйтесь формулой Данные->Анализ Данных->выбираете корреляция

Да, интересный вопрос! Что будет если переменных хотя бы 3! ))

Есть все-таки вопрос: в приведенном примере можно ли сказать, что один дополнительный час улучшает оценку на 0,86 пункта?

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

Огромное спасибо за понятное изложение!!

По формуле я посчитала, все понятно. Но через Excel не получается. Поясните подробнее

Резеда, опишите, подробнее, что вы делаете и что у вас не получается

Подскажите,пожалуйста,а как по значению корреляции построить такой график,и можно ли его получить,если переменная непараметрическая(да-1,0-нет)?

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

Высчитывать ранговую или порядкову корреляцию типа 121211112211/111221122121111 и по всем факторам выходят понятные значения, и лишь при сравнении 2х определенных массивов постоянно выдает результат 2.26…..Е-17 что это значит?

результат 2.26…..Е-17 что это значит?
я думаю, что это равно 2.26 умножить на 10 в минус 17ой степени, т.е. ну очень маленькое число корреляции и эти два массива не связаны..

Добрый день!
Обрабатываю экспериментальные данные, в Excel, выполнена аппроксимация графика. Получены уравнения. Не могу проверить на адекватность полученные уравнения (логарифмические, полиномиальные) с линейными получилось. А вот у остальных не знаю как ввести данные.
Кто нибудь подскажет, как это выполнить?
Где можно посмотреть алгоритм ввода?
Буду очень рада вашей помощи.

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

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

Подскажите, как использовать корреляцию для 3 и более переменных ?

В Эксел нет формулы расчета кратной корреляции.
Для 3 или более переменных нужно рассчитывать их попарно.
Или использовать кратную (многомерную) корреляцию: произведение усредненных массивов (данные минус среднее значение), деленное на кратную степень произведения дисперсий массивов. То есть при трех массивах вычисляете дисперсии каждого массива, перемножаете их и вычисляете КУБИЧЕСКИй корень (в знаменателе). При 5 массивах — произведение 5 массивов (центрированных — с вычитанием среднего) деленное корень пятой степени из произведения 5 дисперсий массивов

Проще вычислить сумму центрированных произведений переменных и разделить на произведение среднеквадратических отклонений переменных

Комментариев, подобных моему тут, конечно, уже много, но всё-же!
Спасибо за столь доступное и простое описание! Теперь действительно понятно стало!

Спасибо, очень понятно.
Вы приводите в качестве примера расчет корреляции по Персонал, т.е для количестве них переменных (напр. потраченные часы и оценка). Подскажите, а где в Excel функция ANOVA или MANOVA — расчет корреляция ной взаимосвязи между качественным и количестве ними переменными?

Добрый день! Как рассчитать корреляцию в еxcele я поняла. Несколько уточняющих вопросов. Во-первых, это рассчитывается ведь кор. Пирсона? И второе. В калькуляторах, рассчитывающих кор. Пирсона, к значению корреляции указывается еще и «p» (обычно p0,05 или 0,01), а в еxcele он какой? И третье. Если формула везде расчета Пирсона одна, то почему в разных калькуляторах, в том числе, и в сравнении с расчетами в еxcele, получаются разные результаты? По поводу «р» — еще просьба: я слабо дружу с матимаиткой и не дружу со статистикой вообще. Можете ли мне объяснить доступным языком про это р?

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

Подскажите, пожалуйста, если нужно рассчитать коэфыициент корреляции для выборки Х( -1,95; -4,13, -8; -10; -41,5) и У (-0,22; 1,54; -8,8; -10,8; 8,04; 0,47) . В эксель через КОРРЕЛ не считает.. Вообще при таком разбросе чисел (от отрицательных до положительных) это возможно установить силу связи между Х и У? И как тогда рассчитывать. То что связь мужду Х и У есть это исходные данные, нужно оценить силу связи этой.. Может есть другие идеи?

А у формулы определения коэффициента корреляции есть автор?