Vvmebel.com

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

Корреляционная матрица в excel

Множественный корреляционный анализ в excel. Как рассчитать коэффициент корреляции в Excel

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

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

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

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

Значения показателей x и y:

Y – независимая переменная, x – зависимая. Необходимо найти силу (сильная / слабая) и направление (прямая / обратная) связи между ними. Формула коэффициента корреляции выглядит так:

Чтобы упростить ее понимание, разобьем на несколько несложных элементов.

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

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

Покажем значения переменных на графике:

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

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

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

Матрица коэффициентов корреляции в Excel строится с помощью инструмента «Корреляция» из пакета «Анализ данных».


Между значениями y и х1 обнаружена сильная прямая взаимосвязь. Между х1 и х2 имеется сильная обратная связь. Связь со значениями в столбце х3 практически отсутствует.

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

Корреляция измеряет мощность и направление связи между 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

1.1 Корреляционный анализ в MS Excel

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

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

Общая классификация корреляционных связей (по Ивантер Э.В., Коросову А.В., 1992):

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

В MS Excel для вычисления парных коэффициентов линейной корреляции используется специальная функция КОРРЕЛ (массив1; массив2),

Презентация — Расчет корреляционных зависимостей в MS Excel

Текст этой презентации

Расчет корреляционных зависимостей в MS Excel
Подготовила учитель информатики Яценко Е.В.

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

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

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

Для реализации процедуры необходимо:
выполнить команду Данные — Анализ данных; 2. в появившемся списке Инструменты анализа выбрать строку Корреляция и нажать кнопку ОК; 3. в появившемся диалоговом окне указать Входной интервал, то есть ввести ссыл­ку на ячейки, содержащие анализируемые данные. Входной интервал должен содержать не менее двух столбцов. 4. в разделе Группировка переключатель установить в соответствии с введенными данными (по столбцам или по строкам); 5. указать выходной интервал, то есть ввести ссылку на ячейку, начиная с которой будут показаны результаты анализа. Размер выходного диапазона будет определен автоматически, и на экран будет выведено сообщение в случае возможного наложения выходного диапазона на исходные данные. Нажать кнопку ОК.

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

Имеются ежемесячные данные наблюдений за состоянием погоды и посещаемостью музеев и парков . Необходимо определить, существует ли взаимосвязь между состоянием погоды и посещаемостью музеев и парков.
Число ясных дней Количество посетителей музея Количество посетителей парка
8 495 132
14 503 348
20 380 643
25 305 865
20 348 743
15 465 541

Решение. Для выполнения корреляционного анализа введите в диапазон A1:G3 исходные данные . Затем в меню Сервис выберите пункт Анализ данных и далее укажите строку Корреляция. В появившемся диалоговом окне укажите Входной интервал (А2:С7). Укажите, что данные рассматриваются по столбцам. Укажите выходной диапазон (Е1) и нажмите кнопку ОК.

Вывод: видно, что корреляция между состоянием погоды и посещаемостью музея равна -0,92, а между состоянием погоды и посещаемостью парка — 0,97, между посещаемостью парка и музея — 0,92. В результате анализа выявлены зависимости: сильная степень обратной линейной взаимосвязи между посещаемостью музея и количеством солнечных дней ; очень сильная прямая связь между посещаемостью парка и состоянием погоды; сильная обратная взаимосвязь между посещаемостью музея и парка .

Множественный коэффициент корреляции в Excel (Эксель)

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

Коэффициент корреляции определяется некоторыми данными. Если уровень показателя составляет от 0 до 0.3, то в таком случае связи нет. Если показатель составляет от 0.3 до 0.5 — это слабая связь. Если показатель доходит до 0.7, то связь средняя. Высокой можно назвать когда показатель достигает отметки 0.7-0.9. Если показатель составляет 1 — это наиболее сильная связь.

Читать еще:  Поиск решения в excel скачать

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

Далее откроется новое окно. В нём нужно выбрать «Надстройки» и в поле управления параметрами выбрать среди элементов списка «Надстройки Excel»
После запуска окна параметров посредством его левого вертикального меню переходим в раздел «Надстройки». После этого нажимаем «Перейти».

Далее откроется новое окно надстроек. Находим в списке «Пакет анализа» и ставим галочку. После этого подтверждаем действие. И пакет анализа данных будет подключён для документа Excel.

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

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

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

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

Корреляционная матрица в excel

п.13 . Решение прикладных задач средствами EXCEL .

Коэффициент линейной корреляции Браве-Пирсона ( ) — параметр, характеризующий степень линейной взаимосвязи между двумя выборками. Он изменяется от (-1) (полная обратная линейная зависимость) до 1 (полная прямая пропорциональная зависимость). Коэффициент корреляции является безразмерной величиной и его значение не зависит от единиц измерения случайных величин X и У.

В MS Excel для вычисления парных коэффициентов линейной корреляции используется специальная функция КОРРЕЛ. Параметрами функции являются КОРРЕЛ (массив 1, массив 2), где:

массив 1 — это диапазон ячеек первой случайной величины;

массив 2 — это второй интервал ячеек со значениями второй случайной величины.

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

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

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

Для реализации процедуры необходимо:

•выполнить команду Сервис ►Анализ данных;

•в появившемся списке Инструменты анализа выбрать строку Корреляция и нажать кнопку 0К;

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

•в разделе Группировка переключатель установить в соответствии с введенными данными;

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

•нажать кнопку ОК.

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

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

Ниже показаны две возможности вычисления коэффициента линейной корреляции Браве-Пирсона: на основе привлечения возможностей Мастера функций и на основе использования Пакета анализа.

Приведен пример исходных данных измерения двух показателей интеллекта (вербального (Х i ) и невербального (У i )) у 20 учащихся 8 класса. Рассчитать коэффициент корреляции.

Вербальный (Х i ): 13, 9, 8, 9, 7, 9, 8, 13, 11, 12, 8, 9, 10, 10, 12, 10, 8, 9, 10, 11.

Невербальный ( Yi ): 12, 11, 8, 12, 9, 11, 9, 13, 9, 10, 9, 8, 10, 12, 10, 10, 11, 10, 11, 13.

Для расчета коэффициента корреляции, прежде всего, необходимо ввести данные в рабочую таблицу. Откройте новую рабочую таблицу. Введите в ячейку А1 – Вербальный (Х i ) . Затем в ячейки А2-А21 — соответствующие значения. В ячейки B 1- B 21 введите название Невербальный ( Yi ) и значения. Затем вычисляется значение коэффициента корреляции между выборками. Для этого табличный курсор установите в свободную ячейку (А22). На панели инструментов нажмите кнопку Вставка функции ( fx ). В появившемся диалоговом окне Мастер функций выберите категорию Статистические и функцию КОРРЕЛ, после чего нажмите кнопку ОК. Появившееся диалоговое окно КОРРЕЛ за серое поле мышью отодвиньте вправо на 1-2 см от данных (при нажатой левой клавише). Указателем мыши введите диапазон данных Х1 в поле Массив 1 (А2-А21). В поле Массив 2 введите диапазон данных У1 (В2-В21). Нажмите кнопку ОК. В ячейке А22 появится значение коэффициента корреляции — 0,517392.

Читать еще:  Как разделить в excel на 2

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

Что такое коэффициент корреляции и как его использовать в Excel

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

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

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

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

  1. Кликнуть по ячейке, в которой должен появиться результат.
  2. Нажать кнопку «Вставить формулу».
  3. В появившемся окне выбрать категорию «Полный алфавитный перечень».
  4. Найти и активировать функцию «КОРРЕЛ».
  5. Кликнуть «ОК».
  6. В открывшемся окне аргументов поставить курсор в поле «Массив 1», выделить первый столбец с данными.
  7. Поставить курсор в поле «Массив 2», выделить второй столбец из таблицы.
  8. Кликнуть «ОК».

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

Расчёт с помощью пакета анализа

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

  1. Выполнить действия «Файл» — «Сведения» — «Параметры».
  2. В появившемся окне перейти в раздел «Надстройки». В нижней части окна в выпадающем списке выбрать «НадстройкиExcel». Нажать кнопку «Перейти».
  3. В открывшемся окне «Надстройки» следует отметить пункт «Пакет анализа» и нажать «ОК»

Чтобы воспользоваться пакетом, следует:

  1. На панели задач активировать вкладку «Данные».
  2. Нажать кнопку «Анализ данных».
  3. В новом окне выделить строку «Корреляция» и нажать «ОК». Появится окно с параметрами.
  4. Для выбора входного интервала необходимо установить курсор в соответствующее поле и выделить сразу оба столбца.
  5. Параметр группировки следует отметить «по столбцам». Вывод результатов возможен в указанное место, на новый лист или в новую книгу.
  6. Следует отметить соответствующее поле.

После указание всех параметров следует нажать «ОК».

Значение получилось тем же, что и в первом случае.

Поле корреляции (диаграмма рассеяния)

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

В редакторе Excel построение выполняется с помощью инструмента «Диаграмма»:

  1. Выделить столбцы с данными.
  2. Кликнуть «Вставка» — «Точечная» — «Точечная с маркерами».

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

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

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

  1. Кликнуть правой кнопкой мыши на любой точке диаграммы.
  2. В контекстном меню выбрать «добавить линию тренда».
  3. Настроить параметры линии тренда (можно оставить по умолчанию).
  4. Нажать кнопку «закрыть».

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

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

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

Другие примеры использования коэффициента корреляции:

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

Редактор электронных таблиц Microsoft Excel является удобным инструментом для вычисления и наглядного представления результатов вычисления коэффициента корреляции.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector