Oc-windows.ru

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

Матрица межфакторных корреляций в excel

Определение множественного коэффициента корреляции в MS Excel

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

Вычисление множественного коэффициента корреляции

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

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

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

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

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

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

  1. Переходим во вкладку «Файл». В левом вертикальном меню окна, которое откроется после этого, щелкаем по пункту «Параметры».

После запуска окна параметров посредством его левого вертикального меню переходим в раздел «Надстройки». Там в самом низу правой части окна располагается поле «Управление». Переставляем переключатель в нём в позицию «Надстройки Excel», если отображен другой параметр. После этого клацаем по кнопке «Перейти…», находящейся справа от указанного поля.

  • Происходит запуск небольшого окошка «Надстройки». Устанавливаем флажок около параметра «Пакет анализа». Затем в правой части окна кликаем по кнопке «OK».
  • После указанного действия пакет инструментов «Анализ данных» будет активирован.

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

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

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

    Открывается окошко, которое носит наименование «Анализ данных». Выделяем в списке инструментов, расположенных в нём, наименование «Корреляция». После этого щелкаем по кнопке «OK» в правой части интерфейса окна.

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

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

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

    В блоке настроек «Параметр вывода» следует указать, где именно будет располагаться наша корреляционная матрица, в которую выводится результат расчета. Доступны три варианта:

    • Новая книга (другой файл);
    • Новый лист (при желании в специальном поле можно дать ему наименование);
    • Диапазон на текущем листе.
    Читать еще:  Как соединить ячейки в excel

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

    После выполнения всех указанных манипуляций остается только щелкнуть по кнопке «OK» в правой части окошка «Корреляция».

  • После выполнения последнего действия Excel строит матрицу корреляции, заполняя её данными, в указанном пользователем диапазоне.
  • Этап 3: анализ полученного результата

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

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

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

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

    Матрица межфакторных корреляций в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Читать еще:  Табличный процессор ms excel

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

    Результаты анализа. В выходной диапазон будет выведена корреляционная матрица, в которой на пересечении каждых строки и столбца находится коэффициент корреляции между соответствующими параметрами. Ячейки выходного диапазона, имеющие совпадающие координаты строк и столбцов, содержат значение 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

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

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

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

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

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

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

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

    1. Найдем средние значения переменных, используя функцию СРЗНАЧ:
    2. Посчитаем разницу каждого y и yсредн., каждого х и хсредн. Используем математический оператор «-».
    3. Теперь перемножим найденные разности:
    4. Найдем сумму значений в данной колонке. Это и будет числитель.
    5. Для расчета знаменателя разницы y и y-средн., х и х-средн. Нужно возвести в квадрат.
    6. Находим суммы значений в полученных колонках (с помощью функции АВТОСУММА). Перемножаем их. Результат возводим в квадрат (функция КОРЕНЬ).
    7. Осталось посчитать частное (числитель и знаменатель уже известны).
    Читать еще:  Критерий фишера в excel примеры

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

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

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

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

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

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

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

    1. На вкладке «Данные» в группе «Анализ» открываем пакет «Анализ данных» (для версии 2007). Если кнопка недоступна, нужно ее добавить («Параметры Excel» — «Надстройки»). В списке инструментов анализа выбираем «Корреляция».
    2. Нажимаем ОК. Задаем параметры для анализа данных. Входной интервал – диапазон ячеек со значениями. Группирование – по столбцам (анализируемые данные сгруппированы в столбцы). Выходной интервал – ссылка на ячейку, с которой начнется построение матрицы. Размер диапазона определится автоматически.
    3. После нажатия ОК в выходном диапазоне появляется корреляционная матрица. На пересечении строк и столбцов – коэффициенты корреляции. Если координаты совпадают, то выводится значение 1.

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

    Изобразим наглядно корреляционные отношения с помощью графиков.

    1. Сильная прямая связь между y и х1.
    2. Сильная обратная связь между y и х2. Изменения значений происходят параллельно друг другу. Но если y растет, х падает. Значения y увеличиваются – значения х уменьшаются.
    3. Отсутствие взаимосвязи между значениями y и х3. Изменения х3 происходят хаотично и никак не соотносятся с изменениями y.

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

    Записки магистра бизнес-информатики

    Вычисление ковариационной, корреляционной матриц, матрицы вторых моментов в MS Excel

    Основная цель — научиться вычислять матрицы ковариации и корреляции.

    Задание: для набора курсов валют вычислить ковариационную матрицу, корреляционную матрицу, матрицу вторых моментов, нормированную матрицу вторых моментов.

    1. Подготовка исходных данных в MS Excel

    2. Центрирование данных

    Создали новый лист, задали ему имя «Центр». Установили курсор на ячейку В3 и набрали команду =Лист1!B3-Лист1!B$12

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

    3. Вычисление ковариационной матрицы

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

    Для транспонирования матрицы используем команду ТРАНСП(). Команда перемножения матриц имеет вид

    Полученная ковариационная матрица показана на рисунке ниже.

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

    4. Вычисление корреляционной матрицы

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

    Здесь E — диагональная матрица, i-ый диагональный коэффициент которой равен обратной величине среднеквадратического отклонения i-го показателя.

    По формуле =1/КОРЕНЬ(C3) и так далее для диагонали предыдущей матрицы построили матрицу нормализации.
    Пустые ячейки (по обе стороны диагонали заполнили нулями).

    Создали лист и задали ему имя «Корреляция». Установите курсор на ячейке С3 и выполнили умножение трех матриц командой =МУМНОЖ(Ковариация!L3:Q8; МУМНОЖ(Ковариация!C3:H8; Ковариация!L3:Q8)).

    Корреляционная матрица представлена на рисунке ниже.

    5. Вычисление матрицы вторых моментов.

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

    Матрица вторых моментов показана на рисунке ниже

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