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

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

Немного теории

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

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

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

Коэффициентом корреляции является число, которое выражает, насколько сильна взаимосвязь между переменными. Оно колеблется в диапазоне от –1 до 1. Чем ближе значение к нулю, тем ниже взаимозависимость между переменными. Соответственно, если коэффициент достигает 1 — речь идет о положительной корреляции, а если –1 — об отрицательной.

Корреляционный анализ с помощью формулы КОРРЕЛ

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

Исходная таблица

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

1. Выберите ячейку для записи результата и введите в нее функцию КОРРЕЛ. Альтернативный вариант — используйте Мастер функций. Для этого нажмите кнопку «Fx» слева от строки формул. В поиске найдите «КОРРЕЛ».
   

   Вставляем функцию «КОРРЕЛ»
2. Далее функция КОРРЕЛ попросит ввести аргументы для определения коэффициента. В нашем случае это показатели затрат на продажи из столбца В и размер прибыли из колонки D. Вводим ссылки на эти диапазоны в качестве аргумента и нажимаем OK.
   

   Вводим аргументы
3. В результате этого функция возвращает коэффициент и остается только интерпретировать полученный результат. В нашем случае значением коэффициента корреляции округленно составляет 0.9. Это указывает на то, что что величины из столбцов B и D имеют положительную зависимость. Причем показатель такой значимости довольно высокий.
   

   Результат выполнения

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

1. Выделяем столбцы, в которых записаны исходные значения. В нашем случае это колонки B и D.
   

   Выделяем столбцы
2. Затем переходим во вкладку «Вставка», где выбираем «Точечная диаграмма».
   

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

   Добавляем линию тренда

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

В целом, функция КОРРЕЛ — это простейший способ, позволяющий определить степень зависимости между двумя наборами данных. Отметим, что посчитать корреляцию можно только в том случае, если ячейки содержат числа. Если в них добавлен текст или данные какого-либо иного формата — они автоматически будут посчитаны, как величины со значением «0». Также нужно отметить, что вычислить коэффициент можно тогда, когда два набора данных имеют равное количество значений.

Надстройка «Анализ данных»

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

1. Открываем вкладку «Файл», а затем переходим в раздел «Параметры».
   

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

   Переходим в надстройки
3. Ставим отметку напротив строчки «Пакет анализа» и нажимаем OK.
   

   Включаем пакет анализа

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

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

   Запускаем анализ данных
2. В открывшемся окне выполняем поиск функции «Корреляция».
   

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

   Вводим аргументы

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

Результат выполнения

Подводим итоги

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

Видео по теме