Корреляционный анализ в Excel

Корреляционный анализ в Excel

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

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

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

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

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

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

Метод 1. Определите корреляцию с помощью функции мастера.

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

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

  3. В списке, представленном в окне Мастера функций, найдите и выделите функцию CORREL. Нажмите на кнопку "в порядке" .
  4. Корреляционный анализ в Excel

  5. Открывает окно аргументов функции. В поле "Массив1" вводим координаты диапазона ячеек одного из значений, зависимость от которого необходимо определить. В нашем случае это значения в столбце "Объем продаж". Чтобы ввести адрес массива в поле, просто выберите все ячейки данных в верхнем столбце.

    В поле "Массив2" необходимо ввести координаты второго столбца. У нас есть стоимость рекламы. Так же, как и в предыдущем случае, вводим данные в поле.

    Нажмите на кнопку "в порядке" .

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

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

  1. Откройте вкладку "файл" .
  2. Корреляционный анализ в Excel

  3. В открывшемся окне перейдите в раздел "Настройки" .
  4. Корреляционный анализ в Excel

  5. Затем перейдите к пункту "Добавки" .
  6. Корреляционный анализ в Excel

  7. Внизу окна следующей вкладки "Управление" переключаем переключатель в положение "Дополнения в Excel" если он находится в другом положении. Нажмите на кнопку "в порядке" .
  8. Корреляционный анализ в Excel

  9. В окне надстройки установите флажок "Пакет анализа" . Нажмите на кнопку "в порядке" .
  10. Корреляционный анализ в Excel

  11. После этого активируется пакет анализа. Откройте вкладку "Данные" . Как видите, здесь на панели появляется новый блок инструментов — "Анализ" . Нажмите на кнопку "Анализ данных" который находится в нем.
  12. Корреляционный анализ в Excel

  13. Откроется список различных вариантов анализа данных. Выберите элемент "Корреляция" . Нажмите на кнопку "в порядке" .
  14. Корреляционный анализ в Excel

  15. Откроется окно с параметрами корреляционного анализа. В отличие от предыдущего метода, в поле "Интервал ввода" вводите интервал не отдельно для каждого столбца, а для всех столбцов, участвующих в анализе. В нашем случае это данные на графиках "Расходы на рекламу" а также "Объем продаж".

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

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

    Когда все настройки настроены, нажмите кнопку "в порядке" .

Корреляционный анализ в Excel

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

Корреляционный анализ в Excel

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

Понравилась статья? Поделиться с друзьями:
Что нужно знать пользователю?