Lumpics lumpics.ru

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

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

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

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

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

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

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

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

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

  1. Выделяем ячейку, в которой должен выводиться результат расчета. Кликаем по кнопке «Вставить функцию», которая размещается слева от строки формул.
  2. Переход в мастер функций для корреляции в Microsoft Excel
  3. В списке, который представлен в окне Мастера функций, ищем и выделяем функцию КОРРЕЛ. Жмем на кнопку «OK».
  4. Функция КОРРЕЛ в Мастере функций в Microsoft Excel
  5. Открывается окно аргументов функции. В поле «Массив1» вводим координаты диапазона ячеек одного из значений, зависимость которого следует определить. В нашем случае это будут значения в колонке «Величина продаж». Для того, чтобы внести адрес массива в поле, просто выделяем все ячейки с данными в вышеуказанном столбце.

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

    Жмем на кнопку «OK».

Аргументы функции КОРРЕЛ в Microsoft Excel

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

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

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

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

  1. Переходим во вкладку «Файл».
  2. Переход во вкладку Файл в Microsoft Excel
  3. В открывшемся окне перемещаемся в раздел «Параметры».
  4. Переход в раздел Параметры в Microsoft Excel
  5. Далее переходим в пункт «Надстройки».
  6. Переход в надстройки в Microsoft Excel
  7. В нижней части следующего окна в разделе «Управление» переставляем переключатель в позицию «Надстройки Excel», если он находится в другом положении. Жмем на кнопку «OK».
  8. Переход в надстройки Excel в Microsoft Excel
  9. В окне надстроек устанавливаем галочку около пункта «Пакет анализа». Жмем на кнопку «OK».
  10. Включение пакета анализа в Microsoft Excel
  11. После этого пакет анализа активирован. Переходим во вкладку «Данные». Как видим, тут на ленте появляется новый блок инструментов – «Анализ». Жмем на кнопку «Анализ данных», которая расположена в нем.
  12. Переход в анализ данных в Microsoft Excel
  13. Открывается список с различными вариантами анализа данных. Выбираем пункт «Корреляция». Кликаем по кнопке «OK».
  14. Переход в Корреляцию в Microsoft Excel
  15. Открывается окно с параметрами корреляционного анализа. В отличие от предыдущего способа, в поле «Входной интервал» мы вводим интервал не каждого столбца отдельно, а всех столбцов, которые участвуют в анализе. В нашем случае это данные в столбцах «Затраты на рекламу» и «Величина продаж».

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

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

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

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

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

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

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

Обсудить в TelegramНаш Telegram каналТолько полезная информация
Автор статьи Вы на сайте: Статья обновлена: . Автор: Максим Тютюшев

Вам помогли мои советы?

Получить ответ на Email
Уведомить о

10 ответов
По рейтингу
Новые Старые
Межтекстовые Отзывы
Посмотреть все комментарии
василиса
21 апреля 2021 17:12

у Вас в примере два массива, а если массива три? например процент высоких, процент средних и процент низких, тогда как?

кирилл
27 мая 2019 20:30

Статью написали, а на вопросы в комментариях не отвечаете ( А, действительно, если параметров более 2 как быть?

Аноним
29 мая 2019 10:49
Ответить на  кирилл

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

Аноним
25 апреля 2020 15:56

Не работает пишет входной интервал содержит не числовые значения

Аноним
20 июня 2023 13:18
Ответить на  Аноним

Поставь числовой формат ячейки

александр
22 марта 2018 21:36

спасибо, своевременная помощь новичку по корреляции! А если нелинейные функции, то можно воспользоваться этим расчетом?

Аноним
13 апреля 2023 05:55
Ответить на  александр

а где хоть слово что это для линейных?

Аноним
15 мая 2018 15:56

Доброго времени суток!
Подскажите, какой метод (Пирсона или Спирмена) используется в Excel? Необходимо ли ранжировать список?

Галина
8 июня 2018 14:56

А как провести анализ , если столбцов 10 штук. У вас в примере их 2.

Аноним
20 июня 2023 13:19
Ответить на  Галина

Выделяешь все 10

Задать вопрос