Уравнение корреляции в excel

Корреляционный анализ – популярный метод статистического исследования, который используется для выявления степени зависимости одного показателя от другого. В Microsoft Excel имеется специальный инструмент, предназначенный для выполнения этого типа анализа. Давайте выясним, как пользоваться данной функцией.

Суть корреляционного анализа

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

Если зависимость установлена, то определяется коэффициент корреляции. В отличие от регрессионного анализа, это единственный показатель, который рассчитывает данный метод статистического исследования. Коэффициент корреляции варьируется в диапазоне от +1 до -1. При наличии положительной корреляции увеличение одного показателя способствует увеличению второго. При отрицательной корреляции увеличение одного показателя влечет за собой уменьшение другого. Чем больше модуль коэффициента корреляции, тем заметнее изменение одного показателя отражается на изменении второго. При коэффициенте равном 0 зависимость между ними отсутствует полностью.

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

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

Способ 1: определение корреляции через Мастер функций

Одним из способов, с помощью которого можно провести корреляционный анализ, является использование функции КОРРЕЛ. Сама функция имеет общий вид КОРРЕЛ(массив1;массив2).

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

Открывается окно аргументов функции. В поле «Массив1» вводим координаты диапазона ячеек одного из значений, зависимость которого следует определить. В нашем случае это будут значения в колонке «Величина продаж». Для того, чтобы внести адрес массива в поле, просто выделяем все ячейки с данными в вышеуказанном столбце.

Читайте также:  Sound blasteraxx axx 200

В поле «Массив2» нужно внести координаты второго столбца. У нас это затраты на рекламу. Точно так же, как и в предыдущем случае, заносим данные в поле.

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

Способ 2: вычисление корреляции с помощью пакета анализа

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

    Переходим во вкладку «Файл».

В открывшемся окне перемещаемся в раздел «Параметры».

Далее переходим в пункт «Надстройки».

В нижней части следующего окна в разделе «Управление» переставляем переключатель в позицию «Надстройки Excel», если он находится в другом положении. Жмем на кнопку «OK».

В окне надстроек устанавливаем галочку около пункта «Пакет анализа». Жмем на кнопку «OK».

После этого пакет анализа активирован. Переходим во вкладку «Данные». Как видим, тут на ленте появляется новый блок инструментов – «Анализ». Жмем на кнопку «Анализ данных», которая расположена в нем.

Открывается список с различными вариантами анализа данных. Выбираем пункт «Корреляция». Кликаем по кнопке «OK».

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

Параметр «Группирование» оставляем без изменений – «По столбцам», так как у нас группы данных разбиты именно на два столбца. Если бы они были разбиты построчно, то тогда следовало бы переставить переключатель в позицию «По строкам».

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

Читайте также:  Сколько времени выветривается ртуть

Когда все настройки установлены, жмем на кнопку «OK».

Так как место вывода результатов анализа было оставлено по умолчанию, мы перемещаемся на новый лист. Как видим, тут указан коэффициент корреляции. Естественно, он тот же, что и при использовании первого способа – 0,97. Это объясняется тем, что оба варианта выполняют одни и те же вычисления, просто произвести их можно разными способами.

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Корреляцию в Excel можно найти по формуле:

Результат показан ниже

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


затем переходим на область графика

и выбираем данные из диапазона B3:C11, затем Ок. В итоги получаем график поля корреляции по точкам


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

Выбираем корреляцию и жмём Ок.

Такой же выбираем диапазон данных, как и ранее делали

В результате получаем отчёт

Аналитически, корреляция определяется по формуле:

Функция КОРРЕЛ Возвращает коэффициент корреляции двух диапазонов ячеек. Коэффициент корреляции используется для определения взаимосвязи между двумя свойствами. Например, можно установить зависимость между средней температурой в помещении и использованием кондиционера.

Синтаксис

Аргументы функции КОРРЕЛ описаны ниже.

аргумент Обязательный. Диапазон значений ячеек.

Массив2 Обязательный. Второй диапазон значений ячеек.

Замечания

Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, то такие значения игнорируются; Тем не менее, ячейки, содержащие нулевые значения, учитываются.

Читайте также:  Dark messiah зана арт

Если аргумент "массив1" и "массив2" имеют различное количество точек данных, КОРРЕЛ возвращает ошибку #N/A.

Если аргумент массив1 или массив2 пуст или если ( стандартное отклонение) их значений равны нулю, КОРРЕЛ возвращает значение #DIV/0! Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку #ССЫЛКА!.

Так как коэффициент корреляции ближе к + 1 или-1, он указывает на положительную (+ 1) или отрицательную (-1) корреляцию между массивами. Положительная корреляция означает, что если значения в одном массиве увеличиваются, значения в другом массиве также увеличиваются. Коэффициент корреляции, который ближе к 0, указывает на то, что корреляция не является надежной.

Уравнение для коэффициента корреляции имеет следующий вид:

являются средними значениями выборок СРЗНАЧ(массив1) и СРЗНАЧ(массив2).

Пример

В следующем примере возвращается коэффициент корреляции двух наборов данных в столбцах A и B.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Get expert help now

Don’t have time to figure this out? Our expert partners at Excelchat can do it for you, 24/7.