Semenalidery.com

IT Новости из мира ПК
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

Что такое коэффициент корреляции?

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

Выделяют 2 вида связи между ними:

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

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

Для большинства медико-биологических процессов статистически доказано присутствие этого типа связи.

Статистические методы позволяют установить факт существования взаимозависимости признаков. Использование для этого специальных расчетов приводит к установлению коэффициентов корреляции (меры связанности).

Такие расчеты получили название корреляционного анализа. Он проводится для подтверждения зависимости друг от друга 2-х переменных (случайных величин), которая выражается коэффициентом корреляции.

Использование корреляционного метода позволяет решить несколько задач:

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

Для переменных величин:

  • относящихся к порядковой шкале, рассчитывается коэффициент Спирмена;
  • относящихся к интервальной шкале – коэффициент Пирсона.

Это наиболее часто используемые параметры, кроме них есть и другие.

В первом случае при увеличении значения одной переменной наблюдается увеличение второй. При отрицательном коэффициенте – закономерность обратная.

Для чего нужен коэффициент корреляции?

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

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

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

Этой статистической характеристике присущи следующие свойства:

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

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

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

В случае положительной корреляции при значении:

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

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

Можно воспользоваться упрощенной шкалой Челдока, в которой выделяется всего 3 градации силы корреляционной связи:

  • очень сильная – показатели ±0,7 — ±1;
  • средняя – показатели ±0,3 — ±0,699;
  • очень слабая – показатели 0 — ±0,299.

Виды коэффициента корреляции

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

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

  • Пирсона;
  • Спирмена;
  • Кендала;
  • знаков Фехнера;
  • конкорддации или множественной ранговой корреляции.

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

Метод Пирсона рекомендуется использовать для ситуаций, требующих:

  • точного установления корреляционной силы;
  • сравнения количественных признаков.

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

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

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

Метод Спирмена рекомендуется применять в ситуациях:

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

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

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

Пошаговый расчет коэффициента корреляции в Excel

Расчёт корреляционного коэффициента предполагает последовательное выполнение ряда математических операций.

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

Достаточно соблюсти несложный алгоритм действий:

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

(1 оценок, среднее: 5,00 из 5)

Коэффициент корреляция в excel — примеры как применять

Microsoft Office Excel часто используется для анализа данных и применения различных функций, которые встроены в программу. Для выявления зависимости одной величины от другой ведутся корреляционные исследования, которые достаточно популярны в статистике. Сегодня разберемся, как осуществляется корреляция в excel.

Введение

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

  1. Если значение близко к 1 или -1, то существует сильная прямая или обратная связь между величинами.
  2. Коэффициент около 0,5 или -0,5 говорит о том, что между массивами слабая взаимосвязь.
  3. Если получается число близкое к нулю, то величины не связаны между собой.
Читать еще:  If в excel примеры

При этом есть ряд особенностей использования функции КОРРЕЛ:

  1. Программа не учитывает в расчете пустые ячейки, элементы массива с текстовым форматом и ячейки с логическими операторами. При этом числа в виде текста будут учтены.
  2. Размеры двух массивов должны быть одинаковыми, в противном случае редактор выдаст ошибку типа Н/Д.
  3. При корреляционном анализе нельзя использовать пустые столбцы или диапазон с нулевыми значениями.

Примеры использования

Рассмотрим несколько задач, чтобы понять принцип работы статистической функции.

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

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

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

Пример 2.

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

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

Пример 3.

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

Полученный результат говорит о слабой связи этих категорий.

Прочие возможности

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

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

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

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

Жми «Нравится» и получай только лучшие посты в Facebook ↓

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 = диапазон данных для второй переменной.

Читать еще:  Как задать формат ячейки в excel

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

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

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 зависимость между ними отсутствует полностью.

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

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

Читать еще:  Как поставить фильтр в таблице excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как найти корреляцию в Excel

Microsoft Excel — утилита, которая широко используется во многих компаниях и на предприятиях. Реалии таковы, что практически любой работник должен в той или иной мере владеть Экселем, так как эта программа применяется для решения очень широкого спектра задач. Работая с таблицами, нередко приходится определять, связаны ли между собой определённые переменные. Для этого используется так называемая корреляция. В этой статье мы подробно рассмотрим, как рассчитать коэффициент корреляции в Excel. Давайте разбираться. Поехали!

Рассчитываем корреляцию в Excel

Начнём с того, что такое коэффициент корреляции вообще. Он показывает степень взаимосвязи между двумя элементами и всегда находится в диапазоне от -1 (сильная обратная взаимосвязь) до 1 (сильная прямая взаимосвязь). Если коэффициент равен 0, это говорит о том, что взаимосвязь между значениями отсутствует.

Теперь, разобравшись с теорией, перейдём к практике. Чтобы найти взаимосвязь между переменными и у, воспользуйтесь встроенной функцией Microsoft Excel «КОРРЕЛ». Для этого нажмите на кнопку мастера функций (она расположена рядом с полем для формул). В открывшемся окне выберите из списка функций «КОРРЕЛ». После этого задайте диапазон в полях «Массив1» и «Массив2». Например, для «Массив1» выделите значения у, а для «Массив2» выделите значения х. В итоге вы получите рассчитанный программой коэффициент корреляции.

Коэффициент корреляции рассчитывается программой

Следующий способ будет актуален для студентов, от которых требуют найти зависимость по заданной формуле. Прежде всего, нужно знать средние значения переменных x и y. Для этого выделите значения переменной и воспользуйтесь функцией «СРЗНАЧ». Далее необходимо вычислить разницу между каждым x и xср, и yср. В выбранных ячейках напишите формулы x-x, y-. Не забудьте закрепить ячейки со средними значениями. Затем растяните формулу вниз, чтобы она применилась и к остальным числам.

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

Теперь перейдём к знаменателю. Посчитанные разности нужно возвести в квадрат. Для этого в отдельной колонке введите формулы: (x-xср) 2 и (y-yср) 2 . Затем растяните формулы на весь диапазон. После, при помощи кнопки «Автосумма», найдите сумму по всем колонкам (для x и для y). Осталось перемножить найденные суммы и извлечь из них квадратный корень. Последний шаг — поделите числитель на знаменатель. Полученный результат и будет искомым коэффициентом корреляции.

Как видите, умея правильно работать с функциями Microsoft Excel, можно существенно упростить себе задачу расчёта непростых математических выражений. Благодаря средствам, реализованным в программе, вы без труда сделаете корреляционный анализ в Excel всего за пару минут, сэкономив время и силы. Пишите в комментариях, помогла ли вам статья разобраться в вопросе, спрашивайте обо всём, что заинтересовало вас по рассмотренной теме.

Ссылка на основную публикацию
Adblock
detector