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:
- Перейдите на вкладку Файл.
- Нажмите на Параметры
- В открывшемся диалоговом окне «Параметры Excel» щелкните параметр «Надстройки» на боковой панели.
- В раскрывающемся списке «Управление» выберите надстройки Excel.
- Щелкните Go. Откроется диалоговое окно надстроек.
- Отметьте опцию Analysis Toolpak
- Нажмите ОК
Вышеупомянутые шаги добавят новую группу на вкладке «Данные» на ленте Excel под названием «Анализ». В этой группе у вас будет опция анализа данных
Расчет коэффициента корреляции с помощью пакета Data Analysis Toolpak
Теперь, когда инструмент анализа снова доступен на ленте, давайте посмотрим, как с его помощью рассчитать коэффициент корреляции.
Предположим, у вас есть набор данных, как показано ниже, и вы хотите выяснить корреляцию между тремя рядами (рост и вес, рост и доход, вес и доход).
Ниже приведены шаги для этого:
- Перейдите на вкладку «Данные».
- В группе «Анализ» выберите параметр «Анализ данных».
- В открывшемся диалоговом окне «Анализ данных» нажмите «Корреляция».
- Щелкните ОК. Откроется диалоговое окно «Корреляция».
- Для диапазона ввода выберите три серии, включая заголовки.
- Убедитесь, что для параметра «Сгруппировано по» выбрано «Столбцы».
- Выберите вариант — «Ярлык в первой строке». Это гарантирует, что в результирующих данных будут одинаковые заголовки, и будет намного легче понять результаты.
- В параметрах вывода выберите, где вы хотите получить результирующую таблицу. Я собираюсь использовать ячейку G1 на том же листе. Вы также можете получить результаты на новом листе или в новой книге.
- Нажмите ОК.
Как только вы это сделаете, Excel вычислит коэффициент корреляции для всех серий и выдаст вам таблицу, как показано ниже:
Обратите внимание, что результирующая таблица является статической и не будет обновляться в случае изменения какой-либо точки данных в вашей таблице. В случае каких-либо изменений вам придется повторить вышеуказанные шаги еще раз, чтобы сгенерировать новую таблицу коэффициентов корреляции.
Итак, это два быстрых и простых метода расчета коэффициента корреляции в Excel.
Надеюсь, вы нашли этот урок полезным!
