Вычисление корреляции в Excel: ваше практическое руководство

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

Не уверены в своих навыках работы с Excel? Без проблем. Вот как рассчитать — и понять — коэффициент корреляции в Excel.

Коэффициент корреляции +1 указывает на «идеальную положительную корреляцию», что означает, что по мере увеличения переменной X переменная Y увеличивается с той же скоростью. В то же время значение корреляции -1 является «совершенной отрицательной корреляцией», что означает, что по мере увеличения переменной X переменная Y уменьшается с той же скоростью. Корреляционный анализ также может возвращать результаты в диапазоне от -1 до +1, что указывает на то, что переменные изменяются с одинаковой, но не одинаковой скоростью.

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

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

Так как же рассчитать поправочный коэффициент в Excel? Простой! Следуй этим шагам:

1. Откройте Эксель.

Шаг первый. Откройте Excel и создайте новый рабочий лист для данных коррелированных переменных. Введите точки данных вашей первой переменной в столбце A и вашей второй переменной в столбце B. Вы также можете добавить дополнительные переменные в столбцы C, D, E и т. д. — Excel предоставит коэффициент корреляции для каждой из них.

В приведенном ниже примере мы ввели шесть строк данных в столбце A и шесть в столбце B.

2. Установите пакет инструментов анализа.

Следующий? Если у вас его нет, установите пакет инструментов анализа Excel.

Выберите «Файл», затем «Параметры», и вы увидите этот экран:

Выберите «Надстройки» и нажмите «Перейти».

Теперь установите флажок «Пакет инструментов анализа» и нажмите «ОК».

3. Выберите «Данные» в меню верхней панели.

После установки ToolPak выберите «Данные» в верхнем меню панели Excel. Это предоставляет вам подменю, содержащее различные параметры анализа ваших данных.

4. Выберите «Анализ данных» в правом верхнем углу.

Теперь найдите «Анализ данных» в правом верхнем углу и нажмите на него, чтобы открыть этот экран:

5. Выберите Корреляция.

Выберите «Корреляция» в меню и нажмите «ОК».

6. Определите диапазон данных и вывод.

Теперь определите диапазон данных и выходные данные. Вы можете просто щелкнуть левой кнопкой мыши и перетащить курсор на данные, которые вы хотите выбрать, и они будут автоматически заполнены в поле корреляции. Наконец, выберите выходной диапазон для ваших данных корреляции — мы выбрали A8. Затем нажмите «ОК».

7. Оцените свой коэффициент корреляции.

Ваши результаты корреляции теперь будут отображаться. В нашем примере значения в столбцах 1 и 2 имеют идеальную отрицательную корреляцию; по мере того, как один идет вверх, другой падает с той же скоростью.

Корреляционная матрица Excel

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

Вот наши данные:

А вот и матрица:

Ячейка C4 в матрице дает нам корреляцию между столбцом 3 и столбцом 2, которая составляет очень слабую 0,01025, в то время как столбец 1 и столбец 3 дают более сильную отрицательную корреляцию -0,17851. Однако на сегодняшний день самая сильная корреляция наблюдается между столбцом 1 и столбцом 2 на уровне -0,66891.

Так что же это означает на практике? Допустим, мы изучали влияние конкретных действий на эффективность кампании в социальных сетях, где столбец 1 представляет количество посетителей, которые нажимают на рекламу в социальных сетях, а столбцы 2 и 3 представляют два разных маркетинговых слогана. Матрица корреляции показывает сильную отрицательную корреляцию между столбцами 1 и 2, что говорит о том, что версия слогана для столбца 2 значительно снизила общую заинтересованность пользователей, в то время как столбец 3 привел лишь к небольшому снижению.

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

Формула корреляции Excel

Если вы предпочитаете вводить формулу корреляции самостоятельно, это тоже вариант. Вот как это выглядит:

X и Y — ваши измерения, ∑ — сумма, а X и Y с черточками над ними обозначают среднее значение измерений. Вы бы рассчитали это следующим образом:

• Вычислите сумму переменной X минус среднее значение X.
• Вычислите сумму переменной Y минус среднее значение Y.
• Умножьте эти два результата и отложите это число в сторону (это первый результат).
• Возведите в квадрат сумму X минус среднее значение X. Возведите в квадрат сумму Y минус среднее значение Y. Умножьте эти два числа.
• Извлеките квадратный корень (это второй результат).
• Разделите первый результат на второй результат.
• Вы получаете коэффициент корреляции.

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

Корреляция ≠ не причина

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

Рассмотрим несколько очень странных примеров.

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

Проблема? Хотя эти две переменные взаимосвязаны, между ними нет причинно-следственной связи; более высокие температуры не уменьшили популяцию пиратов, а уменьшение количества пиратов не привело к глобальному потеплению.

Хотя корреляция является мощным инструментом, она указывает только направление увеличения или уменьшения между двумя переменными, а не причину этого увеличения или уменьшения. Чтобы обнаружить причинно-следственные связи, компании должны увеличить или уменьшить одну переменную и наблюдать за результатом. Например, если корреляция показывает, что вовлеченность клиентов увеличивается с расходами в социальных сетях, стоит выбрать небольшое увеличение расходов с последующим измерением результатов. Если увеличение расходов ведет непосредственно к увеличению вовлеченности, связь является одновременно корреляционной и причинно-следственной. Если нет, то может быть один (или несколько) факторов, лежащих в основе увеличения обеих переменных.

Отслеживание корреляций

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