2 способа корреляционного анализа в Microsoft Excel

Автор: | 31.12.2021

Корреляция в Microsoft Excel

Корреляционный анализ — популярный метод статистического исследования, используемый для определения степени зависимости одного показателя от другого. В Microsoft Excel есть специальный инструмент для выполнения этого типа анализа. Давайте узнаем, как использовать эту функцию.

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

Целью корреляционного анализа является выявление наличия взаимосвязи между различными факторами. То есть определяется, влияет ли уменьшение или увеличение одного показателя на изменение другого.

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

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

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

Способ 1: определение корреляции через Мастер функций

Одним из способов выполнения корреляционного анализа является использование функции CORRELATION. Сама функция имеет общий вид CORREL (массив1; массив2).

Перейдите к мастеру корреляции в Microsoft Excel

Функция КОРРЕЛЯЦИЯ в функции мастера в Microsoft Excel

  1. Выберите ячейку, в которой должен отображаться результат расчета. Нажмите кнопку «Вставить функцию», расположенную слева от строки формул.
  2. В списке, представленном в окне мастера функций, найдите и выберите функцию КОРРЕЛЯЦИЯ. Щелкните кнопку «ОК».
  3. Откроется окно аргументов функции. В поле «Массив1» введите координаты диапазона ячеек одного из значений, зависимость от которого необходимо определить. В нашем случае это будут значения в столбце «Сумма продаж». Чтобы вставить адрес массива в поле, просто выберите все ячейки с данными в столбце выше.

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

    Щелкните кнопку «ОК».

Аргументы корреляционной функции в Microsoft Excel

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

Результат функции КОРРЕЛЬ в Microsoft Excel

Способ 2: вычисление корреляции с помощью пакета анализа

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

Перейдите на вкладку Файл в Microsoft Excel

Перейти к параметрам в Microsoft Excel

Перейти к надстройкам в Microsoft Excel

Перейти к надстройкам Excel в Microsoft Excel

Включение пакета анализа в Microsoft Excel

Перейти к анализу данных в Microsoft Excel

Перейти к корреляции в Microsoft Excel

  1. Перейдите на вкладку «Файл».
  2. В открывшемся окне перейдите в раздел «Параметры».
  3. Затем перейдите в «Дополнения».
  4. В нижней части следующего окна в разделе «Управление» переместите переключатель в положение «Надстройки Excel», если он находится в другом положении. Щелкните кнопку «ОК».
  5. В окне надстройки установите флажок «Пакет анализа». Щелкните кнопку «ОК».
  6. После этого активируется пакет анализа. Переходим во вкладку «Данные». Как видите, на ленте появляется новый блок инструментов: «Анализ». Нажмите на кнопку «Анализ данных», которая находится внутри него.
  7. Откроется список с различными вариантами анализа данных. Выбираем пункт «Корреляция». Щелкните кнопку «ОК».
  8. Откроется окно с параметрами корреляционного анализа. В отличие от предыдущего метода, в поле «Диапазон ввода» мы вводим диапазон не каждого столбца в отдельности, а всех столбцов, участвующих в анализе. В нашем случае это данные в столбцах «Расходы на рекламу» и «Сумма продаж».

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

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

    Когда все настройки установлены, нажмите кнопку «ОК».

Параметры для расчета корреляции в Microsoft Excel

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

Расчет корреляции в Microsoft Excel

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