Корреляционный анализ в Excel: 2 рабочих варианта

Регрессионный анализ в Excel

Показывает влияние некоторых значений (независимых, независимых) на зависимую переменную. Например, как количество экономически активного населения зависит от количества предприятий, заработной платы и других параметров. Или: как иностранные инвестиции, цены на энергоносители и т д влияют на уровень ВВП.

Результат анализа позволяет расставить приоритеты. И на основе основных факторов прогнозировать, планировать развитие приоритетных направлений, принимать управленческие решения.

Регрессия возникает:

• линейный (y = a + bx);
• параболический (y = a + bx + cx2);
• экспоненциальный (y = a * exp(bx));
• мощность (y = a*x^b);
• гиперболический (y = b/x + a);
• логарифмический (y = b * 1n(x) + a);
• экспоненциальный (y = a * b^x).

Рассмотрим пример построения регрессионной модели в Excel и интерпретации результатов. Возьмем линейный тип регрессии.

Задача. На 6 предприятиях были проанализированы среднемесячная заработная плата и количество уволившихся сотрудников. Необходимо определить зависимость количества вышедших на пенсию работников от средней заработной платы.

Модель линейной регрессии имеет следующий вид:

Y = а0 + а1х1 +… + ахк.

Где а — коэффициенты регрессии, х — влияющие переменные, а к — количество факторов.

В нашем примере Y — показатель конечных работников. Влияющим фактором является заработная плата (х).

В Excel есть встроенные функции, которые можно использовать для расчета параметров модели линейной регрессии. Но надстройка Analysis ToolPak сделает это быстрее».

Активируйте мощный инструмент аналитики:

1. Нажмите кнопку «Офис» и перейдите на вкладку «Параметры Excel». «Дополнение».
2. Внизу, под выпадающим списком, в поле «Управление» будет надпись «Надстройка Excel» (если ее нет, нажмите на галочку справа и выберите). И кнопка «Перейти». Нажмите.
3. Откроется список доступных надстроек. Выберите «Пакет анализа» и нажмите «ОК.

После включения надстройка будет доступна на вкладке «Данные».

Теперь займемся непосредственно регрессионным анализом.

1. откройте меню инструментов анализа данных. Выберите «Регрессия».
2. Откроется меню для выбора входных значений и параметров вывода (где должен отображаться результат). В полях для первых данных указываем диапазон описываемого параметра (Y) и влияющий на него фактор (X). Остальные могут быть или не быть завершены.
3. После нажатия кнопки «ОК» программа отобразит расчеты на новом листе (вы можете выбрать интервал отображения на текущем листе или назначить вывод новой рабочей книге).

Прежде всего, мы рассматриваем R-квадрат и коэффициенты.

R-квадрат – коэффициент детерминации. В нашем примере это 0,755, или 75,5%. Это означает, что рассчитанные параметры модели объясняют корреляцию между изучаемыми параметрами на 75,5%. Чем выше коэффициент детерминации, тем лучше модель. Хорошо — выше 0,8. Плохо — менее 0,5 (такой анализ вряд ли можно считать обоснованным). В нашем примере — «неплохо».

Коэффициент 64,1428 показывает, каким будет Y, если все переменные в рассматриваемой модели равны 0. Это означает, что на значение анализируемого параметра влияют и другие факторы, не описанные в модели.

Коэффициент -0,16285 показывает вес переменной X на Y. Это означает, что среднемесячная заработная плата в рамках данной модели влияет на количество уволившихся с весом -0,16285 (это малая степень влияния). Знак «-» указывает на негативное влияние: чем выше зарплата, тем меньше увольнений. Что справедливо.

Читайте также: Функция логарифм в Экселе

Корреляционный анализ в Excel

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

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

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

Рассмотрим, как с помощью Excel найти коэффициент корреляции.

Функция КОРРЕЛ используется для нахождения парных коэффициентов.

Задача: Выяснить, есть ли зависимость между временем работы токарного станка и затратами на его обслуживание.

Поместите курсор в любую ячейку и нажмите кнопку fx.

1. Во вкладке «Статистические» выберите функцию КОРРЕЛ.
2. Аргумент «Массив 1» — первое значение диапазона — время работы машины: А2:А14.
3. Аргумент «Массив 2» — второй диапазон значений — стоимость ремонта: B2:B14. Нажмите «ОК.

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

Для корреляционного анализа нескольких параметров (более 2-х) удобнее использовать «Анализ данных» (дополнение «Пакет анализа»). В списке выберите корреляцию и введите матрицу. Каждый.

Полученные коэффициенты появятся в корреляционной матрице. Как этот:

Корреляционно-регрессионный анализ

На практике эти два метода часто используются вместе.

Пример:

1. Строим корреляционное поле: «Вставка» — «Диаграмма» — «График рассеяния» (позволяет сравнивать пары). Диапазон значений — это все числовые данные в таблице.
2. Щелкните левой кнопкой мыши в любой точке графика. Так хорошо. В открывшемся меню выберите «Добавить линию тренда».
3. Назначьте параметры для линии. Тип — «Линейный». Внизу — «Покажи уравнение на схеме».
4. Нажмите «Закрыть».

Теперь данные регрессионного анализа видны.

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

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

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

Назначение корреляционного анализа

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

Важно! При коэффициенте 0 связи между величинами нет.

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

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

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

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

1. Выберите ячейку, в которой должен отображаться результат вычисления. Нажмите кнопку «Вставить функцию», расположенную слева от строки формул.
2. В списке, представленном в окне мастера функций, найдите и выберите функцию КОРРЕЛ. Нажмите на кнопку «ОК».
3. Откроется окно Аргументы функции. В поле «Массив1» введите координаты диапазона ячеек одного из значений, зависимость от которого необходимо определить. В нашем случае это будут значения в столбце «Сумма продаж». Чтобы ввести адрес матрицы в поле, просто выделите все ячейки с данными в столбце выше.В поле «Массив2» необходимо ввести координаты второго столбца. У нас есть расходы на рекламу. Точно так же, как и в предыдущем случае, вводим данные в поле.

   Нажмите на кнопку «ОК».

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

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

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

1. Перейдите на вкладку «Файл».
2. В открывшемся окне перейдите в раздел «Настройки».
3. Затем перейдите в пункт «Дополнения».
4. Внизу следующего окна в разделе «Администрирование» переведите переключатель в положение «Надстройки Excel», если он находится в другом положении. Нажмите на кнопку «ОК».
5. В дополнительном окне поставьте галочку напротив пункта «Пакет анализа». Нажмите на кнопку «ОК».
6. После этого активируется пакет анализа. Перейдите на вкладку «Данные». Как видите, здесь на ленте появился новый блок инструментов — «Анализ». Нажмите на кнопку «Анализ данных», которая находится в нем.
7. Откроется список с различными вариантами анализа данных. Выберите «Корреляция». Нажмите на кнопку «ОК».
8. Откроется окно с параметрами корреляционного анализа. В отличие от предыдущего способа, в поле «Входной интервал» мы вводим интервал не для каждого столбца отдельно, а для всех столбцов, участвующих в анализе. В нашем случае это данные столбцов «Расходы на рекламу» и «Продажи».Параметр «Группировка» оставляем без изменений — «по столбцам», так как наши группы данных разбиты на два столбца. Если они были разбиты построчно, переключатель следует перевести в положение «По строкам».

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

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

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

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

Коэффициент парной корреляции в Excel

Давайте узнаем, как правильно построить коэффициент парной корреляции в электронной таблице Excel.

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

Например, у вас есть значения x и y.

X — зависимая переменная, y — независимая переменная. Необходимо найти направление и силу связи между этими показателями. Пошаговые инструкции:

1. Найдем средние значения с помощью функции СРЗНАЧ.

1. Будем вычислять каждое x и x-среднее, y и среднее с помощью «-» оператора «-».

1. Умножаем рассчитанные разности.

1. Рассчитываем сумму показателей в этом столбце. Числитель – это найденный результат.

1. Вычислим знаменатели разности x и x-mean, y и y-mean. Для этого выполним возведение в квадрат.

1. С помощью функции АВТОСУММ находим показатели в полученных столбцах. Мы умножаем. С помощью функции КОРЕНЬ возводим результат в квадрат.

1. Вычисляем частное, используя значения знаменателя и числителя.

1. CORREL — это встроенная функция, позволяющая избежать сложных вычислений. Заходим в «Мастер функций», выбираем CORREL и указываем матрицы с индикаторами x и y. Строим график, который показывает полученные значения.

Матрица парных коэффициентов корреляции в Excel

Разберем, как рассчитать коэффициенты парных матриц. Например, это массив из четырех переменных.

Пошаговые инструкции:

1. Заходим в «Анализ данных», расположенный в блоке «Анализ» вкладки «Данные». Выберите «Корреляция» из появившегося списка».
2. Выставляем все необходимые настройки. «Входной интервал» — интервал всех четырех столбцов. «Интервал вывода» — место, где мы хотим вывести итоги. Нажимаем на кнопку «ОК».
3. В выбранном месте была построена корреляционная матрица. Каждое пересечение строки и столбца представляет собой коэффициент корреляции. Число 1 отображается, когда координаты совпадают.

Функция КОРРЕЛ для определения взаимосвязи и корреляции в Excel

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

Примеры использования функции КОРРЕЛ в Excel

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

Алгоритм расчета выглядит так:

Отображаемый балл близок к 1. Результат:

Определение коэффициента корреляции влияния действий на результат

Второй пример. Два участника торгов обратились в два разных агентства за помощью в проведении пятнадцатидневной кампании. Ежедневно проводился опрос общественного мнения, который определял степень поддержки каждого отдельного претендента. Любой собеседник может выбрать одного из двух претендентов или выступить против всех. Необходимо определить, насколько каждая рекламная кампания повлияла на степень поддержки соискателей, какая компания более эффективна.

Используя приведенные ниже формулы, вычисляем коэффициент корреляции:

• = КОРРЕЛ (A3: A17; B3: B17).
• = КОРРЕЛ (A3: A17; C3: C17).

Полученные результаты:

Из полученных результатов становится понятно, что степень поддержки 1 заявителя увеличивалась на каждый день размещения рекламы, поэтому коэффициент корреляции приближается к 1. При запуске рекламы у второго заявителя было большое количество трастов, а за 5 дней была положительная тенденция. Затем степень уверенности упала и к пятнадцатому дню упала ниже первоначальных показателей. Низкие баллы указывают на то, что продвижение негативно повлияло на поддержку. Не забывайте, что на показатели могут влиять и другие одновременные факторы, не учтенные в табличной форме.

Анализ популярности контента по корреляции просмотров и репостов видео

Третий пример. Человек для продвижения собственных видео на YouTube видеохостинг использует социальные сети для рекламы канала. Он замечает, что существует определенная корреляция между количеством репостов в социальных сетях и количеством просмотров на канале. Можно ли предсказать будущую производительность с помощью инструментов для работы с электронными таблицами? Необходимо выявить целесообразность использования уравнения линейной регрессии для прогнозирования количества просмотров видео в зависимости от количества репостов. Таблица значений:

Теперь необходимо определить наличие связи между 2-мя показателями по приведенной ниже формуле:

0.7;IF(CORREL(A3:A8;B3:B8)>0.7;»Сильная прямая корреляция»;»Сильная обратная корреляция»);»Слабая корреляция или ее отсутствие»)’ class=’formula’>

Если полученный коэффициент выше 0,7, целесообразнее использовать функцию линейной регрессии. В этом примере мы делаем:

Теперь строим график:

Мы используем это уравнение для определения количества показов при 200, 500 и 1000 репостах: =9,2937*D4-206,12. Получаем следующие результаты:

Функция ПРОГНОЗ позволяет определить количество просмотров на данный момент, если, например, было сделано двести пятьдесят репостов. Используйте: 0.7;PREDICTION(D7;B3:B8;A3:A8);»Значения не связаны»)’ class=’формула’>. Получаем следующие результаты:

Особенности использования функции КОРРЕЛ в Excel

Эта функция имеет следующие особенности:

1. Пустые ячейки не учитываются.
2. Ячейки, содержащие логическую и текстовую информацию, не учитываются.
3. Двойное отрицание «-» используется для учета логических значений в виде чисел.
4. Количество ячеек в исследуемых массивах должно совпадать, иначе появится сообщение #Н/Д.

Оценка статистической значимости коэффициента корреляции

При проверке значимости коэффициента корреляции нулевая гипотеза состоит в том, что индикатор имеет значение 0, а альтернатива — нет. Для проверки используется следующая формула:

Как построить поле корреляции в Excel

Итак, теперь давайте разберемся, как построить корреляционное поле. Сначала нужно выяснить, что это на самом деле. Поле корреляции на самом деле означает график корреляции. Главное требование к такой диаграмме состоит в том, что каждая точка должна соответствовать единице совокупности. Корреляционное поле поможет установить более глубокие связи и более качественно проанализировать данные. Во-первых, нам нужно найти коэффициент корреляции между двумя диапазонами, используя функцию КОРРЕЛ. 

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

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

Этот график можно построить не только на основе корреляции, определяемой через функцию КОРРЕЛ.

Диаграмма рассеивания. Поле корреляции

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

После этого появится диалоговое окно, в котором мы должны выбрать тип точечной диаграммы. Мы видим, что логика действий в старых версиях офисного пакета в целом осталась прежней, только немного другой интерфейс. Чуть правее мы можем посмотреть, как будет выглядеть диаграмма рассеяния и выбрать нужный дисплей, а также прочитать описание этого типа диаграммы. Затем нажмите на кнопку «Далее».

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

Затем войдите в настройки. Нас интересует тип «Линейный», и в окне параметров нужно поставить галочку «Показывать уравнение на диаграмме».

После подтверждения действий у нас будет примерно такой график.

Как видите, возможных вариантов конструкции может быть большое количество.