Как рассчитать коэффициент корреляции в Excel (2 простых способа)

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

Поиск корреляции между двумя рядами данных — один из наиболее распространенных статистических расчетов при работе с большими наборами данных.

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

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

Итак, приступим!

Что такое коэффициент корреляции?

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

Коэффициент корреляции — это значение, которое показывает, насколько тесно связаны два ряда данных.

Часто используемый пример — это вес и рост 10 человек в группе. Если мы рассчитаем коэффициент корреляции для данных о росте и весе этих людей, мы получим значение от -1 до 1.

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

А значение больше нуля указывает на положительную корреляцию, что означает, что если рост увеличивается, то увеличивается вес, а если рост уменьшается, то вес уменьшается.

Чем ближе значение к 1, тем сильнее положительная корреляция. Таким образом, значение 0,8 будет означать, что данные о росте и весе сильно коррелированы.

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

Теперь давайте посмотрим, как рассчитать этот коэффициент корреляции в Excel.

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

Как я уже упоминал, есть несколько способов рассчитать коэффициент корреляции в Excel.

Использование формулы CORREL

CORREL — это статистическая функция, представленная в Excel 2007.

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

Ниже приведена формула, которая сделает это:
= КОРРЕЛЬ (B2: B12; C2: C12)

Вышеупомянутая функция CORREL принимает два аргумента — серию с точками данных роста и серию с точками данных веса.

И это все!

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

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

Этот метод лучше всего использовать, если у вас есть две серии и все, что вам нужно, — это коэффициент корреляции.

Но если у вас есть несколько рядов, и вы хотите узнать коэффициент корреляции всех этих рядов, вы также можете рассмотреть возможность использования пакета инструментов анализа данных в Excel (рассматривается далее).

Использование пакета инструментов анализа данных

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

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

Включение пакета инструментов анализа данных

Ниже приведены шаги по включению пакета инструментов анализа данных в Excel:

  1. Перейдите на вкладку Файл.
  2. Нажмите на Параметры
  3. В открывшемся диалоговом окне «Параметры Excel» щелкните параметр «Надстройки» на боковой панели.
  4. В раскрывающемся списке «Управление» выберите надстройки Excel.
  5. Щелкните Go. Откроется диалоговое окно надстроек.
  6. Отметьте опцию Analysis Toolpak
  7. Нажмите ОК

Вышеупомянутые шаги добавят новую группу на вкладке «Данные» на ленте Excel под названием «Анализ». В этой группе у вас будет опция анализа данных

Расчет коэффициента корреляции с помощью пакета Data Analysis Toolpak

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

Предположим, у вас есть набор данных, как показано ниже, и вы хотите выяснить корреляцию между тремя рядами (рост и вес, рост и доход, вес и доход).

Ниже приведены шаги для этого:

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

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

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

Итак, это два быстрых и простых метода расчета коэффициента корреляции в Excel.

Надеюсь, вы нашли этот урок полезным!

Оцените статью
Vip Excel: cоветы по работе с Эксель, таблицы и формулы
Adblock
detector