Как рассчитать коэффициент корреляции между двумя переменными в Excel?
Мы обычно используем коэффициент корреляции (значение, находящееся в диапазоне от -1 до 1), чтобы показать силу и направление линейной связи между двумя переменными. Коэффициент корреляции — это широко используемая статистическая величина, которая помогает вам понимать взаимосвязи, такие как связь между продажами и расходами на рекламу, температурой и продажами мороженого или другими парами данных. В Excel существует несколько простых методов для расчета коэффициента корреляции, включая встроенные функции и инструменты анализа.
Метод A: Прямое использование функции КОРРЕЛ
Метод B: Применение анализа данных и вывод результатов анализа
Метод C: Использование функции ПИРСОН в качестве альтернативы
Метод D: Использование кода VBA для расчета коэффициентов корреляции для нескольких пар
Метод A: Прямое использование функции КОРРЕЛ
Рассмотрим два списка данных, каждый из которых представляет собой переменную. Если вы хотите рассчитать коэффициент корреляции между этими двумя переменными в Excel, этот метод является быстрым и эффективным.
Для практического использования убедитесь, что оба диапазона данных являются числовыми и содержат одинаковое количество наблюдений. Например, если у вас есть следующие парные данные:
Выберите пустую ячейку, где вы хотите отобразить результат вычисления. Введите следующую формулу, затем нажмите клавишу «Enter», чтобы рассчитать коэффициент корреляции:
=CORREL(A2:A7,B2:B7)
В этой формуле A2:A7 и B2:B7 представляют собой два списка переменных, которые вы хотите проанализировать. Диапазоны должны быть одинаковой длины, и каждая пара должна соответствовать одному и тому же наблюдению.
Практический совет: КОРРЕЛ автоматически игнорирует пустые ячейки и текст, но если в двух столбцах нет допустимых числовых пар, он вернет ошибку #ДЕЛ/0!. Убедитесь, что ваши данные правильно выровнены и содержат числовые пары для точного расчета корреляции.
После того, как вы рассчитали коэффициент корреляции, вы можете вставить линейчатую диаграмму, чтобы визуально наблюдать взаимосвязь и дополнительно интерпретировать корреляцию, как показано ниже:
Этот метод лучше всего подходит для быстрой проверки между двумя небольшими наборами данных или при работе в интерактивном режиме внутри вашей электронной таблицы. Он хорошо подходит для пользователей, которые ищут немедленный результат без необходимости получения расширенного статистического вывода.
Раскройте магию Excel с Kutools AI
- Умное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это посредством простых команд.
- Пользовательские формулы: Создавайте индивидуальные формулы для оптимизации ваших рабочих процессов.
- Кодирование VBA: Пишите и внедряйте код VBA без особых усилий.
- Интерпретация формул: Легко разбирайтесь в сложных формулах.
- Перевод текста: Преодолейте языковые барьеры в ваших таблицах.
Метод B: Применение анализа данных и вывод результатов анализа
Если вам нужно проанализировать корреляцию между несколькими переменными сразу или требуется более полная таблица вывода, надстройка Excel «Анализ данных» предоставляет полезное решение. Эта надстройка генерирует матрицу корреляции и позволяет сравнить несколько переменных за один шаг, что ценно для больших наборов данных или составления статистических отчетов.
1. Если вы уже добавили надстройку «Анализ данных» на вкладку Данные, можно перейти к шагу 3. В противном случае кликните Файл > Параметры. В диалоговом окне «Параметры Excel» выберите Надстройки в левой панели, а затем нажмите кнопку Перейти рядом с полем «Надстройки Excel».
2. В диалоговом окне «Надстройки» установите флажок напротив пункта Пакет анализа, затем нажмите ОК. Это добавит группу «Анализ данных» на вкладку Данные .
3. Далее, нажмите Данные > Анализ данных. В появившемся диалоговом окне «Анализ данных» выберите Корреляция из списка, затем нажмите ОК.
4. В диалоговом окне Корреляция настройте следующее:
1) Выберите диапазон, содержащий ваши данные.
2) Выберите параметр «Столбцы» или «Строки», в зависимости от того, как организованы ваши данные.
3) Если ваши данные включают заголовки, отметьте опцию «Метки в первой строке».
4) Укажите место вывода в разделе «Параметры вывода», чтобы отобразить результаты.
5. Нажмите ОК для создания таблицы анализа корреляции. Коэффициенты корреляции будут представлены в указанном диапазоне.
Этот метод подходит, когда вам нужно оценить взаимосвязи между более чем двумя переменными или требуется сводная таблица для целей отчетности. Вывод Анализа данных краток, но не предоставляет дополнительных значимых статистик. Если вы получили неожиданные результаты, повторно проверьте данные на предмет согласованности, пустых ячеек и правильного выбора диапазона.
Метод C: Использование функции ПИРСОН в качестве альтернативы
Помимо КОРРЕЛ, Excel предоставляет функцию ПИРСОН, которая также вычисляет коэффициент корреляции Пирсона между двумя переменными. Функционально ПИРСОН и КОРРЕЛ дают одинаковый результат. Однако ПИРСОН строго следует оригинальной математической формуле, тогда как КОРРЕЛ оптимизирован для среды Excel. Если вы привыкли к статистической теории или работаете со статистическими инструментами вне Excel, ПИРСОН может показаться более знакомым.
Например, с двумя числовыми списками в A2:A7 и B2:B7, вы можете рассчитать корреляцию следующим образом:
1. Выберите ячейку, где вы хотите отобразить результат, и введите эту формулу:
=PEARSON(A2:A7,B2:B7)2. Нажмите Enter для завершения расчета. Если вы хотите проанализировать дополнительные пары данных, соответственно измените диапазоны ячеек или перетащите формулу в другие ячейки.
Советы: ПИРСОН игнорирует текст или логические значения, поэтому убедитесь, что оба диапазона содержат только числовые значения и имеют одинаковую длину. Если в одном столбце есть недостающие данные, соответственно скорректируйте свои диапазоны, чтобы избежать ошибок.
Использование ПИРСОН особенно практично для пользователей, переходящих с других статистических программ, или в академических условиях, где требуется строгое соблюдение терминологии. Оба КОРРЕЛ и ПИРСОН дают одинаковый результат для типичных случаев использования в Excel.
Если вы столкнулись с ошибкой #ДЕЛ/0!, проверьте, что оба диапазона равны по длине и не содержат несоответствующих пустых или нечисловых ячеек.
Плюсы: Легко использовать, совместим с статистическим программным обеспечением; Минусы: Не предлагает существенной разницы от КОРРЕЛ для большинства пользователей.
Метод D: Использование кода VBA для расчета коэффициентов корреляции для нескольких пар
Если вам нужно автоматизировать расчет коэффициентов корреляции для нескольких пар данных (например, при работе с множеством комбинаций переменных), написание простого макроса VBA является эффективным выбором. Этот метод хорошо подходит для продвинутых пользователей, которые хотят обрабатывать большие наборы данных или автоматизировать повторяющиеся задачи анализа.
1. Чтобы использовать этот метод, сначала откройте редактор VBA, нажав Разработчик > Visual Basic. В окне Visual Basic for Applications перейдите в меню Вставка > Модуль, а затем вставьте следующий код в модуль:
Sub BatchCalculateCorrelations()
Dim ws As Worksheet
Dim rng1 As Range, rng2 As Range
Dim lastRow As Long
Dim i As Long
Dim resultCol As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
Set rng1 = Application.InputBox("Select first variable range (single column)", xTitleId, Type:=8)
Set rng2 = Application.InputBox("Select second variable range (multiple columns)", xTitleId, Type:=8)
Set resultCol = Application.InputBox("Select starting cell for output", xTitleId, Type:=8)
If rng1.Rows.Count <> rng2.Rows.Count Then
MsgBox "The two data ranges must have the same number of rows.", vbCritical, xTitleId
Exit Sub
End If
For i = 1 To rng2.Columns.Count
resultCol.Cells(1, i).Value = "Correlation with " & rng2.Cells(1, i).EntireColumn.Column
resultCol.Cells(2, i).Value = WorksheetFunction.Correl(rng1, rng2.Columns(i))
Next i
End Sub2. После вставки кода закройте редактор VBA. В Excel нажмите Alt + F8, выберите BatchCalculateCorrelations и нажмите Запуск. Вам будет предложено выбрать:
- Первый диапазон переменной (один столбец, например, A2:A7)
- Второй диапазон переменной (один или несколько столбцов, например, B2:D7)
- Ячейку, с которой вы хотите начать отображение результатов (например, F2)
Макрос затем рассчитывает коэффициент корреляции между первой переменной и каждым столбцом во втором диапазоне, отображая результаты горизонтально, начиная с выбранной ячейки.
Преимущества: Автоматизация повторяющихся вычислений, экономия значительного времени при работе с большими наборами данных и обеспечение последовательности.
Если вы столкнетесь с проблемами, такими как «Два диапазона данных должны иметь одинаковое количество строк», убедитесь, что все выбранные столбцы имеют одинаковое количество строк и не содержат пустых строк. Для устранения ошибок проверьте, включены ли макросы, и правильно ли выбраны диапазоны.
При работе с коэффициентами корреляции в Excel выбор правильного метода зависит от структуры ваших данных и потребностей анализа. Для одноразовых, быстрых расчетов между двумя рядами формул типа КОРРЕЛ или ПИРСОН являются эффективными и простыми в использовании. Для нескольких переменных или необходимости сводных таблиц, Пакет анализа данных очень практичен. Если вам требуется повторный анализ на больших наборах данных или нужны индивидуальные рабочие процессы, рассмотрите возможность автоматизации с помощью VBA, чтобы сэкономить время и минимизировать человеческие ошибки.
Всегда убедитесь, что ваши диапазоны данных выровнены, чисты и не содержат пустых или нечисловых ячеек, чтобы избежать ошибок формул. Если вы получили неожиданные результаты, повторно проверьте выбор и типы данных.
Связанные статьи
- Расчет процентного изменения или разницы между двумя числами в Excel
В этой статье рассматривается расчет процентного изменения или разницы между двумя числами в Excel.
- Расчет или назначение буквенной оценки в Excel
Назначение буквенных оценок каждому студенту на основе их баллов может быть обычной задачей для учителя. Например, у меня есть шкала оценок, определенная таким образом, что баллы 0-59 = F, 60-69 = D, 70-79 = C, 80-89 = B и 90-100 = A, подробнее см. здесь.
- Расчет размера скидки или цены в Excel
Когда приближается Рождество, в торговых центрах обязательно будут многочисленные скидочные акции. Но если различные товары имеют разные скидки, как можно рассчитать размеры скидок или цены на разные товары?
- Подсчет количества дней / рабочих дней / выходных между двумя датами в Excel
Может быть, иногда вы просто хотите подсчитать рабочие дни между двумя датами, а иногда вам нужно подсчитать только выходные дни между двумя датами.
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек