Servisneva.ru

Сервис Нева
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Что такое коэффициент корреляции и как его использовать в Excel

Что такое коэффициент корреляции и как его использовать в Excel

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

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

Для чего нужен коэффициент корреляции?

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

p, blockquote 11,0,0,0,0 —>

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

p, blockquote 12,0,0,0,0 —>

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

Корреляционный анализ это

Линейная корреляция Пирсона

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

Оценка

Для оценки достоверности и эффективности модели связей необходимо построить матрицу коэффициентов. Коэффициент в случае парной корреляции вычисляется по формуле:

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

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

Читать еще:  KeePass - как пользоваться менеджером паролей

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

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

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

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

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

таблица находим корреляцию

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

график

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

выбрать данные

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

график поля корреляции

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

Линейная регрессия в Excel через Анализ данных

4.9 (49) | количество просмотров119708 | количество коментариев2

Что такое линейная регрессионная модель и зачем это нужно

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

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

Общий вид модели линейной регрессии:

где a — параметры (коэффициенты) регрессии, x — влияющие факторы, k — количество факторов модели.

Исходные данные

Среди исходных данных нам необходим некий набор данных, который бы представлял из себя несколько последовательных или связанных между собой величин итогового параметра Y (например, ВВП) и такое же количество величин показателей, влияние которых мы изучаем (например, иностранные инвестиции).

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

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

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

Читать еще:  Как удалить с компьютера DirectX?

Пакет анализа

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

В Excel есть надстройка Пакет анализа, который является довольно мощным инструментом в помощь аналитику. Этот инструментарий, помимо всего прочего, умеет рассчитывать параметры регрессии, по тому же МНК, всего в несколько кликов, собственно, о том как этим инструментом пользоваться дальше и пойдет речь.

Активируем Пакет анализа

По умолчанию эта надстройка отключена и в меню вкладок вы ее не найдете, поэтому пошагово рассмотрим как ее активировать.

В эксель, слева вверху, активируем вкладку Файл, в открывшемся меню ищем пункт Параметры и кликаем на него.

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

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

Инструкция по поиску параметров линейной регрессии с помощью Пакета анализа

После активации надстройки Пакета анализа она будет всегда доступна во вкладке главного меню Данные под ссылкой Анализ данных

В активном окошке инструмента Анализа данных из списка возможностей ищем и выбираем Регрессия

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

После того как выбрали исходные данные и нажали кнопочку ОК, Excel выдает расчеты на новом листе активной книги (если в настройках не было выставлено иначе), эти расчеты имеют следующий вид:

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

Итак, 0,865 — это R 2 — коэффициент детерминации, показывающий что на 86,5% расчетные параметры модели, то есть сама модель, объясняют зависимость и изменения изучаемого параметра — Y от исследуемых факторов — иксов. Если утрировано, то это показатель качества модели и чем он выше тем лучше. Понятное дело, что он не может быть больше 1 и считается неплохо, когда R 2 выше 0,8, а если меньше 0,5, то резонность такой модели можно смело ставить под большой вопрос.

Читать еще:  Программы для сжатия видео без потери качества

Теперь перейдем к коэффициентам модели:
2079,85 — это a — коэффициент который показывает какой будет Y в случае, если все используемые в модели факторы будут равны 0, подразумевается что это зависимость от других неописанных в модели факторов;
-0,0056a1 — коэффициент, который показывает весомость влияния фактора x1 на Y, то есть количество предприятий в пределах данной модели влияет на показатель экономически активного населения с весом всего -0,0056 (довольно маленькая степень влияния). Знак минус показывает что это влияние отрицательно, то есть чем больше предприятий, тем меньше экономически активного населения, как бы это ни было парадоксальным по смыслу;
-0,0026a2 — коэффициент влияния объема инвестиций в капитал на величину экономически активного населения, согласно модели, это влияние также отрицательно;
0,0028a3— коэффициент влияния доходов населения на величину экономически активного населения, здесь влияние позитивное, то есть согласно модели увеличение доходов будет способствовать увеличению величины экономически активного населения.

Соберем рассчитанные коэффициенты в модель:

Собственно, это и есть линейная регрессионная модель, которая для исходных данных, используемых в примере, выглядит именно так.

Расчетные значения модели и прогноз

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

Фактические значения (те что имели место в реальности) и расчетные значения по модели на этом же рисунке отображены в виде графиков, чтобы показать разность, а значит погрешность модели.

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

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector