17 авг. 2022 г.
читать 3 мин
Одним из способов количественной оценки связи между двумя переменными является использованиекоэффициента корреляции Пирсона , который является мерой линейной связи между двумя переменными .
Он имеет значение от -1 до 1, где:
- -1 указывает на совершенно отрицательную линейную корреляцию между двумя переменными
- 0 указывает на отсутствие линейной корреляции между двумя переменными
- 1 указывает на совершенно положительную линейную корреляцию между двумя переменными.
Чем дальше коэффициент корреляции от нуля, тем сильнее связь между двумя переменными.
Но в некоторых случаях мы хотим понять корреляцию между более чем одной парой переменных.
В этих случаях мы можем создать матрицу корреляции , представляющую собой квадратную таблицу, которая показывает коэффициенты корреляции между несколькими попарными комбинациями переменных.
В этом руководстве объясняется, как создать и интерпретировать корреляционную матрицу в Excel.
Как создать корреляционную матрицу в Excel
Предположим, у нас есть следующий набор данных, который показывает среднее количество очков, подборов и передач для 10 баскетболистов:
Чтобы создать матрицу корреляции для этого набора данных, перейдите на вкладку « Данные » на верхней ленте Excel и нажмите « Анализ данных» .
Если вы не видите эту опцию, вам нужно сначала загрузить бесплатный пакет инструментов анализа данных в Excel .
В новом появившемся окне выберите « Корреляция » и нажмите « ОК ».
Для Input Range выберите ячейки, в которых находятся данные (включая первую строку с метками). Установите флажок рядом с Метки в первой строке.Для выходного диапазона выберите ячейку, в которой вы хотите разместить корреляционную матрицу. Затем нажмите ОК .
Это автоматически создаст следующую корреляционную матрицу:
Как интерпретировать матрицу корреляции в Excel
Значения в отдельных ячейках корреляционной матрицы сообщают нам коэффициент корреляции Пирсона между каждой парной комбинацией переменных. Например:
Корреляция между очками и подборами: -0,04639. Очки и подборы имеют небольшую отрицательную корреляцию, но это значение настолько близко к нулю, что нет убедительных доказательств значимой связи между этими двумя переменными.
Соотношение очков и передач: 0,121871. Очки и передачи имеют небольшую положительную корреляцию, но это значение также довольно близко к нулю, поэтому нет убедительных доказательств значимой связи между этими двумя переменными.
Корреляция между подборами и передачами: 0,713713. Подборы и передачи имеют сильную положительную корреляцию. То есть игроки, у которых больше подборов, как правило, и чаще делают передачи.
Обратите внимание, что диагональные значения в матрице корреляции равны 1, потому что корреляция между переменной и самой собой всегда равна 1. На практике интерпретировать это число бесполезно.
Бонус: визуализация коэффициентов корреляции
Один из простых способов визуализировать значение коэффициентов корреляции в таблице — применить условное форматирование к таблице.
На верхней ленте в Excel перейдите на вкладку « Главная », затем в группу « Стили ».
Нажмите « Таблица условного форматирования » , затем нажмите « Цветовые шкалы » , затем нажмите « Цветовая шкала зелено-желто-красный» .
Это автоматически применяет следующую цветовую шкалу к корреляционной матрице:
Это помогает нам легко визуализировать силу корреляции между переменными.
Это особенно полезный прием, если мы работаем с корреляционной матрицей с большим количеством переменных, потому что она помогает нам быстро определить переменные, которые имеют самые сильные корреляции.
По теме: Что считается «сильной» корреляцией?
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в R:
Как создать матрицу диаграммы рассеяния в Excel
Как выполнить корреляционный тест в Excel
Содержание
- Вычисление множественного коэффициента корреляции
- Этап 1: активация пакета анализа
- Этап 2: расчет коэффициента
- Этап 3: анализ полученного результата
- Вопросы и ответы
Для определения степени зависимости между несколькими показателями применяется множественные коэффициенты корреляции. Их затем сводят в отдельную таблицу, которая имеет название корреляционной матрицы. Наименованиями строк и столбцов такой матрицы являются названия параметров, зависимость которых друг от друга устанавливается. На пересечении строк и столбцов располагаются соответствующие коэффициенты корреляции. Давайте выясним, как можно провести подобный расчет с помощью инструментов Excel.
Читайте также: Корреляционный анализ в Экселе
Вычисление множественного коэффициента корреляции
Принято следующим образом определять уровень взаимосвязи между различными показателями, в зависимости от коэффициента корреляции:
- 0 – 0,3 – связь отсутствует;
- 0,3 – 0,5 – связь слабая;
- 0,5 – 0,7 – средняя связь;
- 0,7 – 0,9 – высокая;
- 0,9 – 1 – очень сильная.
Если корреляционный коэффициент отрицательный, то это значит, что связь параметров обратная.
Для того, чтобы составить корреляционную матрицу в Экселе, используется один инструмент, входящий в пакет «Анализ данных». Он так и называется – «Корреляция». Давайте узнаем, как с помощью него можно вычислить показатели множественной корреляции.
Этап 1: активация пакета анализа
Сразу нужно сказать, что по умолчанию пакет «Анализ данных» отключен. Поэтому, прежде чем приступить к процедуре непосредственного вычисления коэффициентов корреляции, нужно его активировать. К сожалению, далеко не каждый пользователь знает, как это делать. Поэтому мы остановимся на данном вопросе.
- Переходим во вкладку «Файл». В левом вертикальном меню окна, которое откроется после этого, щелкаем по пункту «Параметры».
- После запуска окна параметров посредством его левого вертикального меню переходим в раздел «Надстройки». Там в самом низу правой части окна располагается поле «Управление». Переставляем переключатель в нём в позицию «Надстройки Excel», если отображен другой параметр. После этого клацаем по кнопке «Перейти…», находящейся справа от указанного поля.
- Происходит запуск небольшого окошка «Надстройки». Устанавливаем флажок около параметра «Пакет анализа». Затем в правой части окна кликаем по кнопке «OK».
После указанного действия пакет инструментов «Анализ данных» будет активирован.
Этап 2: расчет коэффициента
Теперь можно переходить непосредственно к расчету множественного коэффициента корреляции. Давайте на примере представленной ниже таблицы показателей производительности труда, фондовооруженности и энерговооруженности на различных предприятиях рассчитаем множественный коэффициент корреляции указанных факторов.
- Перемещаемся во вкладку «Данные». Как видим, на ленте появился новый блок инструментов «Анализ». Клацаем по кнопке «Анализ данных», которая располагается в нём.
- Открывается окошко, которое носит наименование «Анализ данных». Выделяем в списке инструментов, расположенных в нём, наименование «Корреляция». После этого щелкаем по кнопке «OK» в правой части интерфейса окна.
- Открывается окно инструмента «Корреляция». В поле «Входной интервал» следует внести адрес диапазона таблицы, в котором расположены данные по трем изучаемым факторам: энерговооруженность, фондовооруженность и производительность. Можно произвести ручное внесение координат, но легче просто установить курсор в поле и, зажав левую кнопку мыши, выделить соответствующую область таблицы. После этого адрес диапазона будет отображен в поле окна «Корреляция».
Так как у нас факторы разбиты по столбцам, а не по строкам, то в параметре «Группирование» выставляем переключатель в позицию «По столбцам». Впрочем, он там уже и так установлен по умолчанию. Поэтому остается только проверить правильность его расположения.
Около пункта «Метки в первой строке» галочку ставить не обязательно. Поэтому мы пропустим данный параметр, так как он не повлияет на общий характер расчета.
В блоке настроек «Параметр вывода» следует указать, где именно будет располагаться наша корреляционная матрица, в которую выводится результат расчета. Доступны три варианта:
- Новая книга (другой файл);
- Новый лист (при желании в специальном поле можно дать ему наименование);
- Диапазон на текущем листе.
Давайте выберем последний вариант. Переставляем переключатель в положение «Выходной интервал». В этом случае в соответствующем поле нужно указать адрес диапазона матрицы или хотя бы её верхнюю левую ячейку. Устанавливаем курсор в поле и клацаем по ячейке на листе, которую планируем сделать верхним левым элементом диапазона вывода данных.
После выполнения всех указанных манипуляций остается только щелкнуть по кнопке «OK» в правой части окошка «Корреляция».
- После выполнения последнего действия Excel строит матрицу корреляции, заполняя её данными, в указанном пользователем диапазоне.
Этап 3: анализ полученного результата
Теперь давайте разберемся, как понимать тот результат, который мы получили в процессе обработки данных инструментом «Корреляция» в программе Excel.
Как видим из таблицы, коэффициент корреляции фондовооруженности (Столбец 2) и энерговооруженности (Столбец 1) составляет 0,92, что соответствует очень сильной взаимосвязи. Между производительностью труда (Столбец 3) и энерговооруженностью (Столбец 1) данный показатель равен 0,72, что является высокой степенью зависимости. Коэффициент корреляции между производительностью труда (Столбец 3) и фондовооруженностью (Столбец 2) равен 0,88, что тоже соответствует высокой степени зависимости. Таким образом, можно сказать, что зависимость между всеми изучаемыми факторами прослеживается довольно сильная.
Как видим, пакет «Анализ данных» в Экселе представляет собой очень удобный и довольно легкий в обращении инструмент для определения множественного коэффициента корреляции. С его же помощью можно производить расчет и обычной корреляции между двумя факторами.
Еще статьи по данной теме:
Помогла ли Вам статья?
Коэффициент корреляции отражает степень взаимосвязи между двумя показателями. Всегда принимает значение от -1 до 1. Если коэффициент расположился около 0, то говорят об отсутствии связи между переменными.
Если значение близко к единице (от 0,9, например), то между наблюдаемыми объектами существует сильная прямая взаимосвязь. Если коэффициент близок к другой крайней точке диапазона (-1), то между переменными имеется сильная обратная взаимосвязь. Когда значение находится где-то посередине от 0 до 1 или от 0 до -1, то речь идет о слабой связи (прямой или обратной). Такую взаимосвязь обычно не учитывают: считается, что ее нет.
Расчет коэффициента корреляции в Excel
Рассмотрим на примере способы расчета коэффициента корреляции, особенности прямой и обратной взаимосвязи между переменными.
Значения показателей x и y:
Y – независимая переменная, x – зависимая. Необходимо найти силу (сильная / слабая) и направление (прямая / обратная) связи между ними. Формула коэффициента корреляции выглядит так:
Чтобы упростить ее понимание, разобьем на несколько несложных элементов.
- Найдем средние значения переменных, используя функцию СРЗНАЧ:
- Посчитаем разницу каждого y и yсредн., каждого х и хсредн. Используем математический оператор «-».
- Теперь перемножим найденные разности:
- Найдем сумму значений в данной колонке. Это и будет числитель.
- Для расчета знаменателя разницы y и y-средн., х и х-средн. Нужно возвести в квадрат.
- Находим суммы значений в полученных колонках (с помощью функции АВТОСУММА). Перемножаем их. Результат возводим в квадрат (функция КОРЕНЬ).
- Осталось посчитать частное (числитель и знаменатель уже известны).
Между переменными определяется сильная прямая связь.
Встроенная функция КОРРЕЛ позволяет избежать сложных расчетов. Рассчитаем коэффициент парной корреляции в Excel с ее помощью. Вызываем мастер функций. Находим нужную. Аргументы функции – массив значений y и массив значений х:
Покажем значения переменных на графике:
Видна сильная связь между y и х, т.к. линии идут практически параллельно друг другу. Взаимосвязь прямая: растет y – растет х, уменьшается y – уменьшается х.
Матрица парных коэффициентов корреляции в Excel
Корреляционная матрица представляет собой таблицу, на пересечении строк и столбцов которой находятся коэффициенты корреляции между соответствующими значениями. Имеет смысл ее строить для нескольких переменных.
Матрица коэффициентов корреляции в Excel строится с помощью инструмента «Корреляция» из пакета «Анализ данных».
- На вкладке «Данные» в группе «Анализ» открываем пакет «Анализ данных» (для версии 2007). Если кнопка недоступна, нужно ее добавить («Параметры Excel» — «Надстройки»). В списке инструментов анализа выбираем «Корреляция».
- Нажимаем ОК. Задаем параметры для анализа данных. Входной интервал – диапазон ячеек со значениями. Группирование – по столбцам (анализируемые данные сгруппированы в столбцы). Выходной интервал – ссылка на ячейку, с которой начнется построение матрицы. Размер диапазона определится автоматически.
- После нажатия ОК в выходном диапазоне появляется корреляционная матрица. На пересечении строк и столбцов – коэффициенты корреляции. Если координаты совпадают, то выводится значение 1.
Между значениями y и х1 обнаружена сильная прямая взаимосвязь. Между х1 и х2 имеется сильная обратная связь. Связь со значениями в столбце х3 практически отсутствует.
Изобразим наглядно корреляционные отношения с помощью графиков.
- Сильная прямая связь между y и х1.
- Сильная обратная связь между y и х2. Изменения значений происходят параллельно друг другу. Но если y растет, х падает. Значения y увеличиваются – значения х уменьшаются.
- Отсутствие взаимосвязи между значениями y и х3. Изменения х3 происходят хаотично и никак не соотносятся с изменениями y.
Скачать вычисление коэффициента парной корреляции в Excel
Для чего нужен такой коэффициент? Для определения взаимосвязи между наблюдаемыми явлениями и составления прогнозов.
One way to quantify the relationship between two variables is to use the Pearson correlation coefficient, which is a measure of the linear association between two variables.
It has a value between -1 and 1 where:
- -1 indicates a perfectly negative linear correlation between two variables
- 0 indicates no linear correlation between two variables
- 1 indicates a perfectly positive linear correlation between two variables
The further away the correlation coefficient is from zero, the stronger the relationship between the two variables.
But in some cases we want to understand the correlation between more than just one pair of variables.
In these cases, we can create a correlation matrix, which is a square table that shows the the correlation coefficients between several pairwise combination of variables.
This tutorial explains how to create and interpret a correlation matrix in Excel.
How to Create a Correlation Matrix in Excel
Suppose we have the following dataset that shows the average numbers of points, rebounds, and assists for 10 basketball players:
To create a correlation matrix for this dataset, go to the Data tab along the top ribbon of Excel and click Data Analysis.
If you don’t see this option, then you need to first load the free Data Analysis Toolpak in Excel.
In the new window that pops up, select Correlation and click OK.
For Input Range, select the cells where the data is located (including the first row with the labels). Check the box next to Labels in first row. For Output Range, select a cell where you’d like the correlation matrix to appear. Then click OK.
This will automatically produce the following correlation matrix:
How to Interpret a Correlation Matrix in Excel
The values in the individual cells of the correlation matrix tell us the Pearson Correlation Coefficient between each pairwise combination of variables. For example:
Correlation between Points and Rebounds: -0.04639. Points and rebounds are slightly negatively correlated, but this value is so close to zero that there isn’t strong evidence for a significant association between these two variables.
Correlation between Points and Assists: 0.121871. Points and assists are slightly positively correlated, but this value also is fairly close to zero so there isn’t strong evidence for a significant association between these two variables.
Correlation between Rebounds and Assists: 0.713713. Rebounds and assists are strongly positively correlated. That is, players who have more rebounds also tend to have more assists.
Notice that the diagonal values in the correlation matrix are all equal to 1 because the correlation between a variable and itself is always 1. In practice, this number isn’t useful to interpret.
Bonus: Visualizing Correlation Coefficients
One easy way to visualize the value of the correlation coefficients in the table is to apply Conditional Formatting to the table.
Along the top ribbon in Excel, go to the Home tab, then the Styles group.
Click Conditional Formatting Chart, then click Color Scales, then click the Green-Yellow-Red Color Scale.
This automatically applies the following color scale to the correlation matrix:
This helps us easily visualize the strength of the correlations between the variables.
This is a particularly helpful trick if we’re working with a correlation matrix that has a lot of variables because it helps us quickly identify the variables that have the strongest correlations.
Related: What is Considered to Be a “Strong” Correlation?
Additional Resources
The following tutorials explain how to perform other common tasks in R:
How to Create a Scatterplot Matrix in Excel
How to Perform a Correlation Test in Excel
Друзья, приветствую вас на WiFiGid! Буквально на днях разрабатывал модель для одной рыночно-нейтральной торговой стратегии, где как раз и нужно было рассчитывать коэффициенты корреляции (на самом деле корреляционную матрицу). Лично я сделал это на «питоне» – все равно потом ее как-то же надо обрабатывать, ведь миллионы ячеек глазом не просмотришь. Но на этапе предварительного анализа кому-то может быть проще использовать именно Excel. Поэтому в этой статье я и покажу вам основные варианты расчета коэффициента корреляции в Excel.
А если вдруг останутся какие-то вопросы или дополнения – добро пожаловать в комментарии. По мере возможности буду отвечать.
И как дополнение, конечно, можно ввести стандартную формулу любой корреляции в Excel, и на ее основе рассчитать коэффициент. Но в этой статье предлагаю все-таки остановиться на уже встроенных способах в Excel.
Содержание
- Коротко про коэффициент корреляции
- Способ расчета 1 – Формула
- Способ 2 (корреляционная матрица) – Пакет анализа
- Видео по теме
- Задать вопрос автору статьи
Коротко про коэффициент корреляции
Коэффициент корреляции отражает зависимость одного ряда от другого. Принимает значение от -1 до +1 (включая все промежуточные дробные значения):
- +1 – обе величины абсолютно зависимы, куда идет первая, туда идет и вторая. Например, на рынке криптовалюты все очень зависимо от биткоина. Куда он, туда и все. И коэффициент корреляции к «битку» для среднего альткоина обычно стремится к единице (но не так сильно).
- 0 – нет никакой зависимости. Каждый ходит, куда хочет.
- -1 – обратная зависимость. Т.е. если один вверх, то второй строго вниз.
Т.е. для двух рядов значений можно строго определять какое-то одно число – коэффициент корреляции. А если вдруг нам нужно проверить очень много рядов между собой, то удобнее построить таблицу, где на пересечении значений нужных строк и столбцов и будут находиться значения коэффициента корреляции для выбранной пары. Такую таблицу называют корреляционной матрицей.
Напоследок, существует несколько математических способов расчета коэффициента корреляции. Самый популярный (и как раз он используется в Excel) – метод Пирсона. Кому нужны другие варианты (тот же Спирмен), как по мне, гораздо проще перейти на любой более-менее развитый язык программирования. Ну или уже вручную вбить формулу здесь.
Что-то я увлекся теорией, предлагаю уже переходить к практике.
Способ расчета 1 – Формула
Очень надеюсь, что вас не нужно учить писать формулы. Иначе просто закройте Excel
![:)]()
А теперь непосредственно попробуем посчитать коэффициент корреляции. Если вы пришли сюда за формулой, то вот она (можно найти и через мастер функций, но руками быстрее):
=КОРРЕЛ(массив1;массив2)
Под массивами подразумеваем ряды данных величин, между которыми и хотим найти корреляцию. Показываю на примере:
В данном примере у меня на очень малом промежутке времени получился коэффициент корреляции 0,29. Но главное, что способ работает. Так что если нужно получить быстро значение корреляции – круче формулы ничего и не придумаешь.
Способ 2 (корреляционная матрица) – Пакет анализа
В Excel есть еще глубоко запрятанный «Пакет анализа», который тоже содержит надстройку для расчета коэффициента корреляции. Но лично я им не пользуюсь – это очень долго. Поэтому этот раздел больше для интересующихся, чем для практикующих.
- Сначала нужно активировать этот пакет. Переходим в «Файл – Параметры – Надстройки».
- В списке находим «Пакет анализа» (обычно он в разделе неактивных надстроек), выделяем его, убеждаемся что внизу установлено «Надстройки Excel» и нажимаем по кнопке «Перейти».
- Ставим галочку напротив «Пакет анализа» и нажимаем кнопку «Ок». Это должно активировать пакет.
- Теперь переходим на вкладку «Данные». Именно тут справа должна добавить кнопка «Анализ данных». Щелкаем по ней.
- В списке выбираем «Корреляцию».
- Задаем настройки. Во входной интервал нужно передать сразу все столбцы, для которых будем рассчитывать корреляцию. Выходные данные можно переместить на новый лист или задать им место здесь же.
Если вы задали всего два столбца, то и получите то же самое число, что и через формулу. Но если передали сразу несколько столбцов, то и на выходе должна получиться целая корреляционная матрица:
Пользоваться можно, но лично мне не нравится здесь, что нельзя сразу передать названия столбцов и пустые ячейки в верхнем «треугольнике». Работать с этим можно только при небольшом количестве данных. Для остального нужно закрывать Excel.
Видео по теме
Прикладываю видео по теме, где кроме основ есть те же понятия корреляции Спирмена и поля корреляции.
Корреляционный анализ – это распространённый метод исследования, применяемый для определения уровня зависимости 1-й величины от 2-й. В табличном процессоре есть особый инструмент, который позволяет реализовать данный тип исследования.
Содержание
- Суть корреляционного анализа
- Назначение корреляционного анализа
- Расчет коэффициента корреляции
- Способ 1: определение корреляции через Мастер функций
- Способ 2: вычисление корреляции с помощью Пакета анализа
- Определение и вычисление множественного коэффициента корреляции в MS Excel
- Коэффициент парной корреляции в Excel
- Расчет коэффициента парной корреляции в Excel
- Матрица парных коэффициентов корреляции в Excel
- Функция КОРРЕЛ для определения взаимосвязи и корреляции в Excel
- Примеры использования функции КОРРЕЛ в Excel
- Определение коэффициента корреляции влияния действий на результат
- Анализ популярности контента по корреляции просмотров и репостов видео
- Особенности использования функции КОРРЕЛ в Excel
- Оценка статистической значимости коэффициента корреляции
- Заключение
Суть корреляционного анализа
Он необходим для определения зависимости между двумя разными величинами. Иными словами, происходит выявление того, в какую сторону (меньшую/большую) меняется величина в зависимости от изменений второй.
Назначение корреляционного анализа
Зависимость устанавливается тогда, когда начинается выявление коэффициента корреляции. Этот метод отличается от анализа регрессии, так как здесь только один показатель, рассчитываемый при помощи корреляции. Интервал изменяется от +1 до -1. Если она плюсовая, то повышение первой величины способствует повышению 2-й. Если минусовая, то повышение 1-й величины способствует понижению 2-й. Чем выше коэффициент, тем сильнее одна величина влияет на 2-ю.
Важно! При 0-м коэффициенте зависимости между величинами нет.
Расчет коэффициента корреляции
Разберем расчёт на нескольких образцах. К примеру, есть табличные данные, где по месяцам описаны в отдельных столбцах траты на рекламное продвижение и объём продаж. Исходя из таблицы, будем выяснять уровень зависимости объема продаж от денег, затраченных на рекламное продвижение.
Способ 1: определение корреляции через Мастер функций
КОРРЕЛ – функция, позволяющая реализовать корреляционный анализ. Общий вид — КОРРЕЛ(массив1;массив2). Подробная инструкция:
- Необходимо произвести выделение ячейки, в которой планируется выводить итог расчета. Нажать «Вставить функцию», находящуюся слева от текстового поля для ввода формулы.
- Открывается «Мастер функций». Здесь необходимо найти КОРРЕЛ, кликнуть на нее, затем на «ОК».
- Открылось окошко аргументов. В строку «Массив1» необходимо ввести координаты интервалы 1-го из значений. В рассматриваемом примере — это столбец «Величина продаж». Нужно просто произвести выделение всех ячеек, которые находятся в этой колонке. В строку «Массив2» аналогично необходимо добавить координаты второй колонки. В рассматриваемом примере — это столбец «Затраты на рекламу».
- После введения всех диапазонов кликаем на кнопку «ОК».
Коэффициент отобразился в той ячейке, которая была указана в начале наших действий. Полученный результат 0,97. Этот показатель отображает высокую зависимость первой величины от второй.
Способ 2: вычисление корреляции с помощью Пакета анализа
Существует еще один метод определения корреляции. Здесь используется одна из функций, находящаяся в пакете анализа. Перед ее использованием нужно провести активацию инструмента. Подробная инструкция:
- Переходим в раздел «Файл».
- Открылось новое окошко, в котором нужно кликнуть на раздел «Параметры».
- Жмём на «Надстройки».
- Находим в нижней части элемент «Управление». Здесь необходимо выбрать из контекстного меню «Надстройки Excel» и кликнуть «ОК».
- Открылось специальное окно надстроек. Ставим галочку рядом с элементом «Пакет анализа». Кликаем «ОК».
- Активация прошла успешно. Теперь переходим в «Данные». Появился блок «Анализ», в котором необходимо кликнуть «Анализ данных».
- В новом появившемся окошке выбираем элемент «Корреляция» и жмем на «ОК».
- На экране появилось окошко настроек анализа. В строчку «Входной интервал» необходимо ввести диапазон абсолютно всех колонок, принимающих участие в анализе. В рассматриваемом примере — это столбики «Величина продаж» и «Затраты на рекламу». В настройках отображения вывода изначально выставлен параметр «Новый рабочий лист», что означает показ результатов на другом листе. По желанию можно поменять локацию вывода результата. После проведения всех настроек нажимаем на «ОК».
Вывелись итоговые показатели. Результат такой же, как и в первом методе – 0,97.
Определение и вычисление множественного коэффициента корреляции в MS Excel
Для выявления уровня зависимости нескольких величин применяются множественные коэффициенты. В дальнейшем итоги сводятся в отдельную табличку, именуемую корреляционной матрицей.
Подробное руководство:
- В разделе «Данные» находим уже известный блок «Анализ» и жмем «Анализ данных».
- В отобразившемся окошке жмем на элемент «Корреляция» и кликаем на «ОК».
- В строку «Входной интервал» вбиваем интервал по трём или более столбцам исходной таблицы. Диапазон можно ввести вручную или же просто выделить его ЛКМ, и он автоматически отобразится в нужной строчке. В «Группирование» выбираем подходящий способ группировки. В «Параметр вывода» указывает место, в которое будут выведены результаты корреляции. Кликаем «ОК».
- Готово! Построилась матрица корреляции.
Коэффициент парной корреляции в Excel
Разберем, как правильно проводить коэффициент парной корреляции в табличном процессоре Excel.
Расчет коэффициента парной корреляции в Excel
К примеру, у вас есть значения величин х и у.
Х – это зависимая переменна, а у – независимая. Необходимо найти направление и силу связи между этими показателями. Пошаговая инструкция:
- Выявим средние показатели величин при помощи функции СРЗНАЧ.
- Произведем расчет каждого х и хсредн, у и усредн при помощи оператора «-».
- Производим перемножение вычисленных разностей.
- Вычисляем сумму показателей в этом столбце. Числитель – найденный результат.
- Посчитаем знаменатели разницы х и х-средн, у и у-средн. Для этого произведем возведение в квадрат.
- Используя функцию АВТОСУММА, найдем показатели в полученных столбиках. Производим перемножение. При помощи функции КОРЕНЬ возводим результат в квадрат.
- Производим подсчет частного, используя значения знаменателя и числителя.
- КОРРЕЛ – интегрированная функция, которая позволяет предотвратить проведение сложнейших расчетов. Заходим в «Мастер функций», выбираем КОРРЕЛ и указываем массивы показателей х и у. Строим график, отображающий полученные значения.
Матрица парных коэффициентов корреляции в Excel
Разберем, как проводить подсчет коэффициентов парных матриц. К примеру, есть матрица из четырех переменных.
Пошаговая инструкция:
- Заходим в «Анализ данных», находящийся в блоке «Анализ» вкладки «Данные». В отобразившемся списке выбираем «Корелляция».
- Выставляем все необходимые настройки. «Входной интервал» – интервал всех четырех колонок. «Выходной интервал» – место, в котором желаем отобразить итоги. Кликаем на кнопку «ОК».
- В выбранном месте построилась матрица корреляции. Каждое пересечение строки и столбца – коэффициенты корреляции. Цифра 1 отображается при совпадающих координатах.
Функция КОРРЕЛ для определения взаимосвязи и корреляции в Excel
КОРРЕЛ – функция, применяемая для подсчета коэффициента корреляции между 2-мя массивами. Разберем на четырех примерах все способности этой функции.
Примеры использования функции КОРРЕЛ в Excel
Первый пример. Есть табличка, в которой расписана информация об усредненных показателях заработной платы работников компании на протяжении одиннадцати лет и курсе $. Необходимо выявить связь между этими 2-умя величинами. Табличка выглядит следующим образом:
Алгоритм расчёта выглядит следующим образом:
Отображенный показатель близок к 1. Результат:
Определение коэффициента корреляции влияния действий на результат
Второй пример. Два претендента обратились за помощью к двум разным агентствам для реализации рекламного продвижения длительностью в пятнадцать суток. Каждые сутки проводился социальный опрос, определяющий степень поддержки каждого претендента. Любой опрошенный мог выбрать одного из двух претендентов или же выступить против всех. Необходимо определить, как сильно повлияло каждое рекламное продвижение на степень поддержки претендентов, какая компания эффективней.
Используя нижеприведенные формулы, рассчитаем коэффициент корреляции:
- =КОРРЕЛ(А3:А17;В3:В17).
- =КОРРЕЛ(А3:А17;С3:С17).
Результаты:
Из полученных результатов становится понятно, что степень поддержки 1-го претендента повышалась с каждыми сутками проведения рекламного продвижения, следовательно, коэффициент корреляции приближается к 1. При запуске рекламы другой претендент обладал большим числом доверия, и на протяжении 5 дней была положительная динамика. Потом степень доверия понизилась и к пятнадцатым суткам опустилась ниже изначальных показателей. Низкие показатели говорят о том, что рекламное продвижение отрицательно повлияло на поддержку. Не стоит забывать, что на показатели могли повлиять и остальные сопутствующие факторы, не рассматриваемые в табличной форме.
Анализ популярности контента по корреляции просмотров и репостов видео
Третий пример. Человек для продвижения собственных роликов на видеохостинге Ютуб применяет соцсети для рекламирования канала. Он замечает, что существует некая взаимосвязь между числом репостов в соцсетях и количеством просмотров на канале. Можно ли про помощи инструментов табличного процессора произвести прогноз будущих показателей? Необходимо выявить резонность применения уравнения линейной регрессии для прогнозирования числа просмотров видеозаписей в зависимости от количества репостов. Табличка со значениями:
Теперь необходимо провести определение наличия связи между 2-мя показателями по нижеприведенной формуле:
0,7;ЕСЛИ(КОРРЕЛ(A3:A8;B3:B8)>0,7;»Сильная прямая зависимость»;»Сильная обратная зависимость»);»Слабая зависимость или ее отсутствие»)’ class=’formula’>
Если полученный коэффициент выше 0,7, то целесообразней применять функцию линейной регрессии. В рассматриваемом примере делаем:
Теперь производим построение графика:
Применяем это уравнение, чтобы определить число просматриваний при 200, 500 и 1000 репостов: =9,2937*D4-206,12. Получаем следующие результаты:
Функция ПРЕДСКАЗ позволяет определить число просмотров в моменте, если было проведено, к примеру, двести пятьдесят репостов. Применяем: 0,7;ПРЕДСКАЗ(D7;B3:B8;A3:A8);»Величины не взаимосвязаны»)’ class=’formula’>. Получаем следующие результаты:
Особенности использования функции КОРРЕЛ в Excel
Данная функция имеет нижеприведенные особенности:
- Не учитываются ячейки пустого типа.
- Не учитываются ячейки, в которых находится информация типа Boolean и Text.
- Двойное отрицание «—» применяется для учёта логических величин в виде чисел.
- Количество ячеек в исследуемых массивах обязаны совпадать, иначе будет выведено сообщение #Н/Д.
Оценка статистической значимости коэффициента корреляции
При проверке значимости корреляционного коэффициента нулевая гипотеза состоит в том, что показатель имеет значение 0, а альтернативная не имеет. Для проверки применяется нижеприведенная формула:
Заключение
Корреляционный анализ в табличном процессоре – это простой и автоматизированный процесс. Для его выполнения необходимо знать всего лишь, где находятся нужные инструменты и как их активировать через настройки программы.
Оцените качество статьи. Нам важно ваше мнение:
4
ЛАБОРАТОРНАЯ РАБОТА
КОРРЕЛЯЦИОННЫЙ АНАЛИЗ В EXCEL
1.1 Корреляционный анализ в MS Excel
Корреляционный анализ состоит в
определении степени связи между двумя
случайными величинами X и Y. В качестве
меры такой связи используется коэффициент
корреляции. Коэффициент корреляции
оценивается по выборке объема п связанных
пар наблюдений (xi, yi) из
совместной генеральной совокупности
X и Y. Для оценки степени взаимосвязи
величин X и Y, измеренных в количественных
шкалах, используется коэффициент
линейной корреляции (коэффициент
Пирсона), предполагающий, что выборки
X и Y распределены по нормальному закону.
Коэффициент корреляции изменяется от
-1 (строгая обратная линейная зависимость)
до 1 (строгая прямая пропорциональная
зависимость). При значении 0 линейной
зависимости между двумя выборками
нет.
Общая классификация корреляционных
связей (по Ивантер Э.В., Коросову А.В.,
1992):
-
сильная,
или тесная
при коэффициенте корреляции r
0,70; -
средняя при
0,50r0,69; -
умеренная при
0,30r0,49; -
слабая при
0,20r0,29; -
очень слабая при r
0,19.
0,70;
r
0,69;
r
0,49;
r
0,29;
0,19.
Существует несколько типов
коэффициентов корреляции, что зависит
от переменных Х и Y,
которые могут быть измерены в разных
шкалах. Именно этот факт и определяет
выбор соответствующего коэффициента
корреляции (см. табл. 13):
В MS Excel для вычисления парных коэффициентов
линейной корреляции используется
специальная функция КОРРЕЛ (массив1;
массив2),
|
№ испытуемых |
X |
Y |
|
1 |
19 |
17 |
|
2 |
32 |
7 |
|
3 |
33 |
17 |
|
4 |
44 |
28 |
|
5 |
28 |
27 |
|
6 |
35 |
31 |
|
7 |
39 |
20 |
|
8 |
39 |
17 |
|
9 |
44 |
35 |
|
10 |
44 |
43 |
где массив1 – ссылка на диапазон
ячеек первой выборки (X);
массив2 – ссылка на диапазон ячеек
второй выборки (Y).
Пример 1: 10 школьникам были даны
тесты на наглядно-образное и вербальное
мышление. Измерялось среднее время
решения заданий теста в секундах.
Исследователя интересует вопрос:
существует ли взаимосвязь между
временем решения этих задач? Переменная
X — обозначает среднее время решения
наглядно-образных, а переменная Y—
среднее время решения вербальных
заданий тестов.
Р
ешение:
Для выявления степени взаимосвязи,
прежде всего, необходимо ввести данные
в таблицу MS Excel (см. табл., рис. 1). Затем
вычисляется значение коэффициента
корреляции. Для этого курсор установите
в ячейку C1. На панели инструментов
нажмите кнопку Вставка функции (fx).
В появившемся диалоговом окне Мастер
функций выберите категорию
Статистические и функцию КОРРЕЛ,
после чего нажмите кнопку ОК. Указателем
мыши введите диапазон данных выборки
Х в поле массив1 (А1:А10). В поле массив2
введите диапазон данных выборки У
(В1:В10). Нажмите кнопку ОК. В ячейке С1
появится значение коэффициента
корреляции — 0,54119. Далее необходимо
посмотреть на абсолютное число
коэффициента корреляции и определить
тип связи (тесная, слабая, средняя и
т.д.)
Рис. 1. Результаты вычисления коэффициента
корреляции
Таким образом, связь между
временем решения наглядно-образных и
вербальных заданий теста не доказана.
Задание 1. Имеются данные по 20
сельскохозяйственным хозяйствам. Найти
коэффициент корреляции между
величинами урожайности зерновых культур
и качеством земли и оценить его значимость.
Данные приведены в таблице.
Таблица 2. Зависимость урожайности
зерновых культур от качества земли
|
Номер |
Качество |
Урожайность, |
|
1 |
32 |
19,5 |
|
2 |
33 |
19 |
|
3 |
35 |
20,5 |
|
4 |
37 |
21 |
|
5 |
38 |
20,8 |
|
6 |
39 |
21,4 |
|
7 |
40 |
23 |
|
8 |
41 |
23,3 |
|
9 |
42 |
24 |
|
10 |
44 |
24,5 |
|
11 |
45 |
24,2 |
|
12 |
46 |
25 |
|
13 |
47 |
27 |
|
14 |
49 |
26,8 |
|
15 |
50 |
27,2 |
|
16 |
52 |
28 |
|
17 |
54 |
30 |
|
18 |
55 |
30,2 |
|
19 |
58 |
32 |
|
20 |
60 |
33 |
Задание 2. Определите,
имеется ли связь между временем работы
спортивного тренажера для фитнеса (тыс.
часов) и стоимость его ремонта (тыс.
руб.):
-
Время
работа тренажера (тыс. часов)Стоимость
ремонта (тыс. руб.)0,50
7,50
0,60
7,75
0,70
7,25
0,80
7,40
0,90
7,90
1,00
8,00
1,10
8,50
1,20
8,40
1,30
8,35
1,40
8,55
1,50
8,70
1,60
9,05
1,70
8,80
1,80
9,10
1,90
9,30
2,00
9,25
2,10
9,45
1.2
Множественная корреляция в MS Excel
При большом числе наблюдений,
когда коэффициенты корреляции необходимо
последовательно вычислять для нескольких
выборок, для удобства получаемые
коэффициенты сводят в таблицы, называемые
корреляционными
матрицами.
Корреляционная матрица — это
квадратная таблица, в которой на
пересечении соответствующих строк и
столбцов находятся коэффициент корреляции
между соответствующими параметрами.
В MS Excel для вычисления
корреляционных матриц используется
процедура Корреляция
из пакета Анализ
данных. Процедура
позволяет получить корреляционную
матрицу, содержащую коэффициенты
корреляции между различными параметрами.
Для реализации процедуры необходимо:
1. выполнить команду Сервис
— Анализ
данных;
2. в появившемся списке
Инструменты анализа
выбрать строку Корреляция
и нажать кнопку ОК;
3. в появившемся диалоговом
окне указать Входной
интервал, то есть
ввести ссылку на ячейки, содержащие
анализируемые данные. Входной интервал
должен содержать не менее двух столбцов.
4. в разделе Группировка
переключатель установить в соответствии
с введенными данными (по столбцам или
по строкам);
5. указать выходной
интервал,
то есть ввести ссылку на ячейку, начиная
с которой будут показаны результаты
анализа. Размер выходного диапазона
будет определен автоматически, и на
экран будет выведено сообщение в случае
возможного наложения выходного диапазона
на исходные данные. Нажать кнопку ОК.
В
выходной диапазон будет выведена
корреляционная матрица, в которой
на пересечении каждых строки и столбца
находится коэффициент корреляции
между соответствующими параметрами.
Ячейки выходного диапазона, имеющие
совпадающие координаты строк и столбцов,
содержат значение 1, так как каждый
столбец во входном диапазоне полностью
коррелирует сам с собой
Пример 2.
Имеются ежемесячные данные наблюдений
за состоянием погоды и посещаемостью
музеев и парков (см. табл. 3). Необходимо
определить, существует ли взаимосвязь
между состоянием погоды и посещаемостью
музеев и парков.
Таблица 3. Результаты наблюдений
-
Число
ясных днейКоличество
посетителей музеяКоличество
посетителей парка8
495
132
14
503
348
20
380
643
25
305
865
20
348
743
15
465
541
Решение.
Для выполнения корреляционного анализа
введите в диапазон A1:G3 исходные данные
(рис. 2). Затем в меню Сервис
выберите пункт Анализ
данных
и далее укажите строку Корреляция.
В появившемся диалоговом окне укажите
Входной интервал
(А2:С7). Укажите, что данные рассматриваются
по столбцам. Укажите выходной диапазон
(Е1) и нажмите кнопку ОК.
На рис. 33 видно, что корреляция
между состоянием погоды и посещаемостью
музея равна -0,92, а между состоянием
погоды и посещаемостью парка — 0,97,
между посещаемостью парка и музея —
0,92.
Таким образом, в результате
анализа выявлены зависимости: сильная
степень обратной линейной взаимосвязи
между посещаемостью музея и количеством
солнечных дней и практически линейная
(очень сильная прямая) связь между
посещаемостью парка и состоянием погоды.
Между посещаемостью музея и парка
имеется сильная обратная взаимосвязь.
Рис. 2. Результаты вычисления
корреляционной матрицы из примера 2
Задание 3. 10 менеджеров
оценивались по методике экспертных
оценок психологических характеристик
личности руководителя. 15 экспертов
производили оценку каждой психологической
характеристики по пятибальной системе
(см. табл. 4). Психолога интересует вопрос,
в какой взаимосвязи находятся эти
характеристики руководителя между
собой.
Таблица 4. Результаты исследования
|
Испытуемые п/п |
тактичность |
требовательность |
критичность |
|
1 |
70 |
18 |
36 |
|
2 |
60 |
17 |
29 |
|
3 |
70 |
22 |
40 |
|
4 |
46 |
10 |
12 |
|
5 |
58 |
16 |
31 |
|
6 |
69 |
18 |
32 |
|
7 |
32 |
9 |
13 |
|
8 |
62 |
18 |
35 |
|
9 |
46 |
15 |
30 |
|
10 |
62 |
22 |
36 |
Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]
- #
- #
- #
- #
- #
- #
- #
- #
- #
- #
- #