Один из самых простых и распространенных статистических расчетов в Excel вы можете сделать это корреляция. Это простая статистика, но она может быть очень информативной, если вы хотите увидеть, связаны ли две переменные. Если вы знаете правильные команды, найти коэффициент корреляции в Excel очень легко.
Мы рассмотрим, что такое корреляция, чтобы дать вам представление об информации, которую она вам дает. Затем мы перейдем к поиску коэффициента корреляции в Excel, используя два метода и хороший график для просмотра корреляций. Наконец, я дам вам очень краткое введение в линейную регрессию, еще одну статистическую функцию, которая может оказаться полезной, когда вы смотрите на корреляции.
Что такое корреляция?
Прежде чем мы начнем, давайте обсудим определение корреляции. Это простая мера того, как все связано. Давайте посмотрим на две переменные, которые не имеют никакой корреляции.
Эти две переменные (одна на оси X, другая на Y) абсолютно случайны и не имеют тесной связи.
Тем не менее, две переменные ниже взаимосвязаны:
В общем случае, когда растет одна переменная, растет и другая. Это корреляция. (Обратите внимание, что это может быть и наоборот; если один повышается, а другой понижается, это отрицательная корреляция.)
Понимание коэффициента корреляции
Коэффициент корреляции показывает, насколько связаны две переменные. Коэффициент находится между -1 и 1. Коэффициент корреляции 0 означает, что нет абсолютно никакой корреляции между двумя переменными. Это то, что вы должны получить, когда у вас есть два набора случайных чисел.
Коэффициент -1 означает, что у вас есть идеальная отрицательная корреляция: при увеличении одной переменной пропорционально уменьшается другая. Коэффициент 1 является идеальной положительной корреляцией: при увеличении одной переменной пропорционально увеличивается и другая.
Любое число между ними представляет шкалу. Например, корреляция 0,5 является умеренной положительной корреляцией.
Как вы можете видеть на графике ниже, корреляция ищет только линейные отношения. Две переменные могут быть сильно связаны по-другому и все еще иметь коэффициент корреляции ноль:
Как найти коэффициент корреляции в Excel, используя CORREL
В Excel есть встроенная функция для корреляции. Функция CORREL имеет очень простой синтаксис:
=CORREL(array1, array2) array1 — ваша первая группа чисел, а array2 — вторая группа. Excel выложит число, и это ваш коэффициент корреляции. Давайте посмотрим на пример.
В этой таблице у нас есть список автомобилей с указанием модели и года выпуска, а также их стоимости. Я использовал функцию CORREL, чтобы увидеть, были ли связаны год и стоимость модели:
Там очень слабая положительная корреляция; так же, как год растет, так и стоимость автомобиля. Но не очень.
Графические корреляции
Когда вы запускаете корреляции, рекомендуется использовать точечный график, чтобы получить визуальное представление о том, как связаны ваши наборы данных. Перейдите в Диаграммы> Scatter, чтобы увидеть, как выглядят ваши данные:
Вы можете видеть, что в этих данных год автомобиля не сильно влияет на стоимость. Есть небольшая положительная тенденция, но она слабая. Это то, что мы нашли с нашей функцией CORREL.
Другим полезным элементом в диаграмме рассеяния является линия тренда, которая выглядит следующим образом:
Линия тренда может быть полезна, когда вы хотите четко определить корреляцию на графике рассеяния. В Windows нажмите Инструменты диаграммы> Дизайн> Добавить элемент диаграммы и выберите линию тренда . На Mac вам нужно перейти к Chart Layout или Chart Design , в зависимости от выпуска Excel.
И не забудьте ознакомиться с нашим руководством по созданию великолепных диаграмм в Excel. прежде чем представлять какие-либо выводы!
Корреляция нескольких переменных с пакетом инструментов анализа данных
Если у вас много разных наборов чисел, и вы хотите найти корреляции между ними, вам нужно запустить функцию CORREL для каждой комбинации. Однако с помощью пакета анализа данных вы можете выбрать несколько наборов данных и посмотреть, где находятся корреляции.
Не уверены, что у вас есть набор инструментов для анализа данных? Ознакомьтесь с нашим пошаговым руководством по основам чтобы загрузить и освоить его.
Чтобы запустить Toolpak, выберите « Данные»> «Анализ данных» . Вы увидите список вариантов:
Выберите Корреляцию и нажмите ОК .
В появившемся окне выберите все свои наборы данных в поле « Диапазон ввода» и скажите Excel, где вы хотите разместить результаты:
Вот что вы получите, когда нажмете ОК :
На изображении выше мы провели корреляцию по четырем различным наборам данных: год, население мира и два набора случайных чисел.
Корреляция каждого набора данных с самим собой равна 1. Год и население мира имеют чрезвычайно сильную корреляцию, в то время как в других местах очень слабые корреляции, как и следовало ожидать со случайными числами.
Корреляция против линейной регрессии в Excel
Корреляция — это простая мера: насколько тесно связаны две переменные? Эта мера, однако, не имеет никакого прогнозирующего или причинного значения. То, что две переменные коррелируют, не означает, что одна вызывает изменения в другой. Это очень важная вещь для понимания корреляции.
Если вы заинтересованы в утверждении о причинности, вам нужно использовать линейную регрессию. Вы также можете получить доступ к этому через пакет анализа данных. (В этой статье не рассматриваются подробности работы линейной регрессии, но есть множество бесплатных ресурсов по статистике. чтобы познакомить вас с основами.)
Откройте набор инструментов анализа данных, выберите « Регрессия» и нажмите « ОК» .
Заполните диапазоны X и Y (значение X — это объясняющая переменная, а значение Y — это значение, которое вы пытаетесь предсказать). Затем выберите, куда вы хотите направить вывод, и снова нажмите OK .
Число, на котором вы хотите сосредоточиться, является p-значением для вашей объясняющей переменной:
Если оно меньше 0,05, у вас есть веский аргумент, что изменения в вашей переменной X вызывают изменения в вашей переменной Y. На изображении выше мы показали, что год является значимым предиктором мирового населения.
Линейная регрессия также полезна тем, что может рассматривать несколько значений. Здесь мы использовали регрессию, чтобы увидеть, являются ли год и население значимыми предикторами цены сырой нефти:
Оба значения p меньше 0,05, поэтому мы можем сделать вывод, что как год, так и население мира являются значимыми предикторами цены сырой нефти. (Хотя сильные корреляции между переменными X могут вызвать их собственные проблемы.)
Опять же, это очень упрощенное объяснение линейной регрессии, и если вы заинтересованы в причинности, вам следует прочитать некоторые учебные пособия по статистике.
Но теперь у вас есть представление о том, как выйти за рамки простой корреляции, если вы ищете больше статистической информации!
Получите лучшие данные из Excel
Понимание основных статистических функций в Excel может помочь вам получить гораздо больше полезной информации из ваших данных. Корреляция — это простая мера, но она может оказать большую помощь, когда вы пытаетесь заявить о цифрах в вашей электронной таблице.
Конечно, вы можете запустить множество других более сложных мер. Но если вы не знакомы со статистикой, начните с основ
Регулярно ли вы используете функции корреляции Excel? Какие еще статистические функции вы хотели бы узнать?