2 способа корреляционного анализа в microsoft excel

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

Корреляция измеряет мощность и направление связи между x и y. На рисунке представлены различные типы корреляции в виде графиков рассеяния упорядоченных пар (x, y). По традиции переменная х размещается на горизонтальной оси, а y — на вертикальной.

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

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

Оставшаяся часть статьи посвящена линейным взаимосвязям между зависимой и независимой переменными.

Коэффициент корреляции

Коэффициент корреляции, r, предоставляет нам как силу, так и направление связи между независимой и зависимой переменными. Значения r находятся в диапазоне между — 1.0 и + 1.0. Когда r имеет положительное значение, связь между х и у является положительной (график A на рисунке), а когда значение r отрицательно, связь также отрицательна (график В). Коэффициент корреляции, близкий к нулевому значению, свидетельствует о том, что между х и у связи не существует график С).

Сила связи между х и у определяется близостью коэффициента корреляции к — 1.0 или +- 1.0. Изучите следующий рисунок.

График A показывает идеальную положительную корреляцию между х и у при r = + 1.0. График В — идеальная отрицательная корреляция между х и у при r = — 1.0. Графики С и D — примеры более слабых связей между зависимой и независимой переменными.

Коэффициент корреляции, r, определяет, как силу, так и направление связи между зависимой и независимой переменными. Значения r находятся в диапазоне от — 1.0 (сильная отрицательная связь) до + 1.0 (сильная положительная связь). При r= 0 между переменными х и у нет никакой связи.

Мы можем вычислить фактический коэффициент корреляции с помощью следующего уравнения:

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

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

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

Использование Excel для вычисления коэффициентов корреляции

Я уверен, что, взглянув на эти ужасные вычисления коэффициентов корреляции, вы испытаете истинную радость, узнав, что программа Excel может выполнить за вас всю эту работу с помощью функции КОРРЕЛ со следующими характеристиками:

КОРРЕЛ (массив 1; массив 2),

массив 1 = диапазон данных для первой переменной,

массив 2 = диапазон данных для второй переменной.

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

Что представляет собой корреляционный анализ

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

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

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

1. Количество человек, которые стали утопленниками в бассейнах, четко коррелирует с количеством фильмов, в которых Николас Кейдж был актером.
2. Количество съеденной моцареллы и количество человек, которые получили докторскую степень, также коррелирует на протяжении 2000-2009 годов. Наверно, действительно, моцарелла как-то влияет на мозг и стимулирует желание совершать научные открытия.
3. Почти во всех случаях средний возраст женщин, которые получили статус «Мисс Америка» коррелирует с количеством людей, которые погибли от нахождения в горячем паре.
4. Число людей, которое погибло в результате дорожно-транспортного происшествия, четко коррелирует с количеством сметаны, которое съедают люди.
5. Мало кто знает, что чем больше курятины человек ест, тем больше сырой нефти импортируется в мире. Правда, это тоже пример ложной корреляции. Кстати, импорт сырой нефти родом из Норвегии тесно связано с количеством людей, которые погибли в результате столкновения автомобиля с поездом. Причем в этом случае корреляция почти 100 процентов.
6. А еще маргарин негативно влияет на статистику разводов. Чем больше людей, которые проживали в штате Мэн, потребляли маргарина, тем выше была частота разводов. Правда, здесь еще может быть рациональное зерно. Ведь частота потребления маргарина имеет обратную корреляцию с экономическим положением в семье. В свою очередь, плохое экономическое положение в семье имеет непосредственную связь с количеством разводов. И это уже доказано научно. Так что кто знает, может, эта корреляция и не является такой ложной. Правда, никто этого не перепроверял.
7. Количество денег, которое правительство США тратит на развитие науки, космоса и технологий, имеет тесную связь с количеством самоубийств, проведенных в форме повешения или удушения.

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

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

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

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

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

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

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

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

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

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

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

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

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

Распространенные заблуждения

Корреляция и причинно-следственная связь

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

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

Простые линейные корреляции

Четыре набора данных с одинаковой корреляцией 0,816

Коэффициент корреляции Пирсона указывает на силу линейной связи между двумя переменными, но его значение, как правило, не полностью характеризует их взаимосвязь. В частности, если условное среднее из дано , обозначается , не является линейным в , коэффициент корреляции будет не в полной мере определить форму .
Y{\ displaystyle Y}Икс{\ displaystyle X}E⁡(Y∣Икс){\ displaystyle \ operatorname {E} (Y \ mid X)}Икс{\ displaystyle X}E⁡(Y∣Икс){\ displaystyle \ operatorname {E} (Y \ mid X)}

Прилегающие изображение показывает разброс участков из квартет энскомбы , набор из четырех различных пар переменных , созданный Фрэнсис Анскомбами . Четыре переменные имеют одинаковое среднее значение (7,5), дисперсию (4,12), корреляцию (0,816) и линию регрессии ( y  = 3 + 0,5 x ). Однако, как видно на графиках, распределение переменных сильно отличается. Первый (вверху слева), кажется, распределен нормально и соответствует тому, что можно было бы ожидать, рассматривая две коррелированные переменные и следуя предположению о нормальности. Второй (вверху справа) не распространяется нормально; хотя можно наблюдать очевидную взаимосвязь между двумя переменными, она не является линейной. В этом случае коэффициент корреляции Пирсона не указывает на то, что существует точная функциональная связь: только степень, в которой эта связь может быть аппроксимирована линейной зависимостью. В третьем случае (внизу слева) линейная зависимость идеальна, за исключением одного выброса, который оказывает достаточное влияние, чтобы снизить коэффициент корреляции с 1 до 0,816. Наконец, четвертый пример (внизу справа) показывает другой пример, когда одного выброса достаточно для получения высокого коэффициента корреляции, даже если связь между двумя переменными не является линейной.
у{\ displaystyle y}

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

Как проводится корреляционный анализ в Excel

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

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

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

Вариант 1: Вызов через Мастер функций

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

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

Откроется окно мастера функций. В поле “Категория” нужно поставить значение “Полный алфавитный перечень”, чтобы отобразились все доступные для применения функции. Там отыщите пункт “КОРРЕЛ” нажмите по нему и затем на кнопку “Ок”.

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

Закончив с заполнением нажмите кнопку “Ок”. Подсчет будет произведен автоматически и выведен в указанной ранее ячейке.
Если полученный коэффициент оказался больше +/-0.5, то это значит, что одна величина сильно зависима от другой.

Вариант 2: Применение пакета анализа

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

1. Перейдите во вкладку “Файл”, что расположена в верхней части окна.

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

В открывшемся окне установите галочку у пункта “Пакет анализа” и нажмите “Ок”. После этого у вас должны появится дополнительные инструменты в верхней панели Excel.
Нужные нам инструменты расположена во вклакде “Данные”. Там должен будет появится дополнительный блок инструментов — “Анализ”. Воспользуйтесь в нем единственным инструментом — “Анализом данных”.

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

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

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

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

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

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

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

В списке, который представлен в окне Мастера функций, ищем и выделяем функцию КОРРЕЛ. Жмем на кнопку «OK».</li>

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

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

Жмем на кнопку «OK».</li></ol>

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

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

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

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

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

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

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

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

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

Помогла ли вам эта статья?

Microsoft Office Excel часто используется для анализа данных и применения различных функций, которые встроены в программу. Для выявления зависимости одной величины от другой ведутся корреляционные исследования, которые достаточно популярны в статистике. Сегодня разберемся, как осуществляется корреляция в excel.

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

Практическое занятие «Проверка адекватности модели».

Цель работы: Изучение t-критерия Стьюдента.

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

• Определение значимости модели

• Установление наличия или отсутствия систематической ошибки.

Проверка значимости отдельных коэффициентов регрессии проводится по t-критерию Стьюдента путем проверки гипотезы о равенстве нулю каждого коэффициента регрессии.

Расчетные значения t -критерия сравнивают с табличным значением критерия, которое определяется при (n-k-1) степенях свободы и соответствующем уровне значимости α.

n – число уравнений,

k – число переменных,

α = 0,05 при доверительной вероятности 0,95 .

Формула для определения t-критерия Стьюдента:

,,

где Sa0 и Sa1 – стандартные отклонения свободного члена и коэффициента регрессии.

Определяются по формулам:

=
,
=
.

Задание: Рассчитать t-критерий Стьюдента по данным в табл.3 и сделать выводы о значимости отдельных коэффициентов уравнения регрессии.

0,636263125

a0=

-109

ε 2

( x_i – х_ср. ) 2

1

3357

2425

2027

2

3135

2050

1886

3

2842

1683

1700

4

3991

2375

2431

5

2293

1167

1350

6

3340

1925

2017

7

3089

1042

1857

8

4372

2925

2673

9

3563

2200

2158

10

3219

1892

1940

11

3308

2008

1996

12

3724

2225

2261

13

3416

1983

2065

14

3022

2342

1814

15

3383

2458

2044

16

4267

2125

2606

Сумма

Расчет t-критерий Стьюдента можно также произвести с помощью Excel, используя стандартную функцию, приведенную в таблице 4.

Сервис / Анализ данных Для вычисления параметров уравнения регрессии следует воспользоваться инструментом Регрессия

Оценка значимости коэффициента парной корреляции с использованием t – критерия Стьюдента. Вычисленное по этой формуле значение t_набл сравнивается с критическим значением t-критерия, которое берется из таблицы значений t Стьюдента с учетом заданного уровня значимости и числа степеней свободы (n-2).

СТЬЮДРАСПОБР (вероятность; степени_свободы) Вероятность — вероятность, соответствующая двустороннему распределению Стьюдента. Степени_свободы — число степеней свободы, характеризующее распределение.

Сделать выводы о значимости коэффициентов уравнения регрессии.

Практическое занятие «Определение значимости модели по F – критерию Фишера»

Цель работы: Изучение F- критерия Фишера.

Для проверки значимости уравнения регрессии в целом используется F – критерий Фишера.

В случае парной линейной регрессии критерий определяется:

= (n-k-1) (6).

Если при заданном уровне значимости расчетное значение F – критерий Фишера с γ 1= k , γ 2 = n – k -1 степенями свободы больше табличного, то модель считается значимой

Задание: Используя данные предыдущей работы, рассчитать F- критерий Фишера и сделать выводы.

Для расчета следует воспользоваться инструментом Регрессия из пакета Сервис / Анализ данных и выбрать значение.

Расчет F-критерий Фишера можно также произвести с помощью Excel, используя стандартную функцию (см. табл.5)

Оценка параметров модели парной и множественной линейной регрессии.

Для вычисления параметров уравнения регрессии следует воспользоваться инструментом Регрессия
Проверка значимости модели регрессии с использованием F-критерий Фишера

FРАСПОБР(вероятность; степени_свободы1; степени_свободы2) Вероятность — это вероятность, связанная с F-распределением. Степени_свободы 1 — это числитель степеней свободы-n1= k. Степени_свободы 2 — это знаменатель степеней свободы-.n2 = (n – k – 1), где k – количество факторов, включенных в модель,

Дата добавления: 2019-07-15 ; просмотров: 110 ;

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

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

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

Регрессия бывает:

• линейной (у = а + 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 предприятиях была проанализирована среднемесячная заработная плата и количество уволившихся сотрудников. Необходимо определить зависимость числа уволившихся сотрудников от средней зарплаты.

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

У = а0 + а1×1 +…+акхк.

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

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

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

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

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

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

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

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

В первую очередь обращаем внимание на R-квадрат и коэффициенты. R-квадрат – коэффициент детерминации. В нашем примере – 0,755, или 75,5%

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

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

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

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

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

Похожие записи:

Колонтитулы в word Как правильно увеличить или изменить файл подкачки windows 10, 8 и 7? Для чего нужно менять формат файлов в windows и как это сделать Как отобразить скрытые папки в windows 10 Сборка или оригинал windows 10 что лучше Как узнать, какой directx установлен, или все о «директе»