Корреляционный анализ в Excel. Пример выполнения корреляционного анализа

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

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

Необходимо определить связь между двумя разными величинами. Другими словами, он показывает, в какую сторону (меньше/больше) меняется значение в зависимости от изменения второго.

Цель корреляционного анализа

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

Важно! При 0-м коэффициенте связи между величинами нет.

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

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

Способ 1: определение корреляции с помощью мастера функций

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

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

2. Откроется Мастер функций. Здесь вам нужно найти CORREL, нажмите на него, затем на «ОК».

3. Откроется окно аргументов. В строке «Массив1» необходимо ввести координаты интервалов 1-го из значений. В данном примере это столбец «Стоимость продаж». Вам просто нужно выделить все ячейки, которые есть в этом столбце. Аналогично нужно добавить координаты второго столбца в строку «Массив2». В нашем примере это столбец «Расходы на рекламу».

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

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

Метод 2: расчет корреляции с помощью пакета инструментов анализа

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

1. Перейдите в раздел «Файл».

2. Откроется новое окно, в котором нужно нажать на раздел «Настройки».
3. Нажмите «Дополнения».
4. Внизу находим элемент «Управление». Здесь вам нужно выбрать «Надстройки Excel» из контекстного меню и нажать «ОК».

5. Открылось специальное окно дополнений. Поставьте галочку напротив элемента «Пакет анализа». Нажимаем «ОК».
6. Активация прошла успешно. Теперь перейдем к данным. Появился блок «Анализ», в котором нужно нажать «Анализ данных».
7. В появившемся новом окне выберите элемент «Корреляция» и нажмите «ОК».

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

Объявлены окончательные результаты. Результат тот же, что и в первом методе – 0,97.

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

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

Подробное руководство:

1. В разделе «Данные» находим уже известный блок «Анализ» и нажимаем «Анализ данных».

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

4. Готовый! Построена корреляционная матрица.

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

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

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

Например, у вас есть значения x и y.

X — зависимая переменная, а y — независимая. Необходимо найти направление и силу связи между этими показателями. Пошаговая инструкция:

1. Найдем средние значения с помощью функции СЕРДЦЕ.

2. Давайте посчитаем каждый х и xavg, у и средний с помощью оператора «-».

3. Умножаем рассчитанные разницы.

4. Рассчитаем сумму показателей в этом столбце. Числитель — это найденный результат.

5. Вычислите знаменатели разности х и x-среднее, y и y-средний. Для этого выполним возведение в квадрат.

6. Используя функцию АВТОСУММА, найдите показатели в полученных столбцах. Делаем умножение. Использование функции ROOT возведите результат в квадрат.

7. Вычисляем частное, используя значения знаменателя и числителя.

8. КОРРЕЛ — интегрированная функция, позволяющая избежать сложных вычислений. Заходим в «Мастер функций», выбираем КОРРЕЛ и указываем массивы индикаторов. х и у. Строим график, отображающий полученные значения.

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

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

Пошаговая инструкция:

1. Заходим в «Анализ данных», расположенный в блоке «Анализ» вкладки «Данные». Выберите «Корреляция» из появившегося списка.
2. Выставляем все необходимые настройки. «Входной интервал» — интервал всех четырех столбцов. «Интервал вывода» — место, где мы хотим вывести итоги. Нажимаем на кнопку «ОК».
3. В выбранном месте строилась корреляционная матрица. Каждое пересечение строки и столбца представляет собой коэффициент корреляции. Число 1 отображается, когда координаты совпадают.

Функция КОРРЕЛ для определения взаимосвязи и корреляции в Excel

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

Примеры использования функции КОРРЕЛ в Excel

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

Алгоритм расчета выглядит следующим образом:

Отображаемый балл близок к 1. Результат:

Определение коэффициента корреляции влияния действий на результат

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

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

• =КОРРЕЛ(A3:A17;B3:B17).
• =КОРРЕЛ(A3:A17;C3:C17).

Результаты:

Из полученных результатов становится ясно, что степень поддержки 1-го заявителя увеличивалась с каждым днем ​​рекламного продвижения, поэтому коэффициент корреляции приближается к 1. При запуске рекламы другой заявитель имел большое количество доверия, и для 5 дней наблюдалась положительная динамика. Затем степень доверия снизилась и к пятнадцатому дню опустилась ниже первоначальных показателей. Низкие оценки предполагают, что продвижение по службе негативно повлияло на поддержку. Не стоит забывать, что на показатели могут повлиять и другие сопутствующие факторы, не учтенные в табличной форме.

Анализ популярности контента по соотношению просмотров видео и репостов

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

Теперь необходимо определить наличие связи между 2-мя показателями по формуле ниже:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Сильная прямая связь”;”Сильная обратная связь”);”Слабая или отсутствие связи”)' class='formula'>

Если результирующий коэффициент выше 0,7, то целесообразнее использовать функцию линейной регрессии. В этом примере мы делаем:

Теперь строим график:

Мы применяем это уравнение для определения количества просмотров при 200, 500 и 1000 репостах: =9,2937*Д4-206,12. Получаем следующие результаты:

Функция ПРОГНОЗ позволяет определить количество просмотров на данный момент, если было, например, двести пятьдесят репостов. Мы применяем: 0,7;PREDICTION(D7;B3:B8;A3:A8);”Значения не связаны”)' class='formula'>. Получаем следующие результаты:

Особенности использования функции КОРРЕЛ в Excel

Эта функция имеет следующие особенности:

1. Пустые ячейки не учитываются.
2. Ячейки, содержащие информацию типов Boolean и Text, не учитываются.
3. Двойное отрицание «-» используется для учета логических значений в виде чисел.
4. Количество ячеек в изучаемых массивах должно совпадать, иначе будет выведено сообщение #Н/Д.

Оценка статистической значимости коэффициента корреляции

При проверке значимости коэффициента корреляции нулевая гипотеза заключается в том, что показатель имеет значение 0, а альтернатива — нет. Для проверки используется следующая формула:

Заключение

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