2 способа корреляционного анализа в Microsoft Excel - TurboComputer.ru
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд (пока оценок нет)
Загрузка...

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

Корреляционно-регрессионный анализ в Excel: инструкция выполнения

Регрессионный и корреляционный анализ – статистические методы исследования. Это наиболее распространенные способы показать зависимость какого-либо параметра от одной или нескольких независимых переменных.

Ниже на конкретных практических примерах рассмотрим эти два очень популярные в среде экономистов анализа. А также приведем пример получения результатов при их объединении.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В первую очередь обращаем внимание на R-квадрат и коэффициенты.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На практике эти две методики часто применяются вместе.

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

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

Проведение корреляционного анализа средствами MS Excel .

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

Выбираем команду меню Сервис/Анализ данных/Корреляция. Откроется следующее диалоговое окно:

Далее следует нажать кнопку OK. После этого будет создана матрица коэффициентов парной корреляции:

Х1Х3Х6Х8Х9У
Х1
Х30,594
Х60,7080,51
Х80,5420,3020,633
Х9-0,63-0,63-0,75-0,68
У-0,55-0,35-0,87-0,620,719
Х1Х3Х6Х8Х9У
Х1
Х30,594
Х60,7080,51
Х80,5420,3020,633
Х9-0,63-0,63-0,75-0,68
У-0,55-0,35-0,87-0,620,719

Анализ матрицы коэффициентов парной корреляции показывает, что наиболее существенное влияние на зависимую переменную оказывают все факторы Х6,Х8,Х9 (см. строку Y).

X9 сильно связана с х6 (из них надо оставить одну, например, х6),

Для исключения явления мультиколлинеарности все факторы кроме X6 и X8 следует исключить из модели.

· Построить уравнение множественной линейной регрессии, используя надстройку MS Excel Пакет анализа (команда Сервис Анализ данных Регрессия)

Ищем линейное уравнение множественной регрессии в виде:

Параметры данного уравнения найдем с помощью инструмента «Регрессия» надстройки «Анализ данных» приложения MS Excel (результаты вычисления – в Приложении 7):

Для построения линейной регрессионной модели вMS Excel необходимо:

1) подготовить список из n строк и m столбцов, содержащий экспериментальные данные (столбец, содержащий выходную величину y должен быть либо первым, либо последним в списке);

2) обратиться к меню Сервис/Анализ данных/Регрессия

Если пункт “Анализ данных” в меню “Сервис” отсутствует, то следует обратиться к пункту “Надстройки” того же меню и установить флажок “Пакет анализа”.

3) в диалоговом окне “Регрессия” задать:

  • входной интервал Y;
  • входной интервал X;
  • выходной интервал – верхняя левая ячейка интервала, в который будут помещаться результаты вычислений (разместим на том же рабочем листе);

Настройки для решения поставленной задачи показаны на рисунке окна “Регрессия”.

· нажать “Ok” и проанализировать результаты.

Результаты расчетов размещены в 4-x таблицах

Коэффициенты регрессии находятся в таблице «Дисперсионный анализ» в столбце «Коэффициенты»

72,90356
-0,48853
-0,046

Получаем уравнение линейной множественной регрессии:

В регрессионном анализе наиболее важными результатами являются:

  • коэффициенты при переменных и Y-пересечение, являющиеся искомыми параметрами модели;
  • множественный R, характеризующий точность модели для имеющихся исходных данных;
  • F-критерий Фишера (в рассмотренном примере он значительно превосходит критическое значение, равное 4,06);

t-статистика– величины, характеризующие степень значимости отдельных коэффициентов модели

3) Определить значения коэффициента множественной корреляции и коэффициента детерминации и сделать выводы об адекватности построенной модели.

Величина множественного коэффициента детерминации R 2 =0,763361 рассчитана в таблице “Регрессионная статистика

Регрессионная статистика
Множественный R0,873706
R-квадрат0,763361
Нормированный R-квадрат0,743642
Стандартная ошибка8,931274
Наблюдения
значения коэффициента множественной корреляции0,873706Теснота совместного влияния факторов на результат довольна высокая (больше, чем 0,7)
значения коэффициента детерминации0,763361Больше чем 0,5. Модель объясняет более 76% дисперсии зависимой переменной.

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

Задачей построения регрессионной зависимости является нахождение вектора коэффициентов M модели (1) при котором коэффициент R принимает максимальное значение.

Значимость R определяется не только его величиной, но и соотношением между количеством экспериментов и количеством коэффициентов (параметров) модели. Действительно, корреляционное отношение для n=2 для простой линейной модели равно 1 (через 2 точки на плоскости можно всегда провести единственную прямую). Однако, если экспериментальные данные являются случайными величинами, доверять такому значению R следует с большой осторожностью. Обычно для получения значимого R и достоверной регрессии стремятся к тому, чтобы количество экспериментов существенно превышало количество коэффициентов модели (n>>k).

Читайте также:  Выгрузка данных из программы 1С в книгу Excel

4) Оценить значимость уравнения регрессии в целом (при заданном уровне значимости) (с помощью F-критерия Фишера) (статистическую надежность моделирования)

Корреляционный анализ в системе STATISTICA 6.1

Порядок входа Анализ —> Основные статистики и таблицы —> Парные и частные корреляции —>ОК [3,4, 14].

Перед нами рабочие окна всех действий (рис. 7.1, 7.2).

Рис. 7.1. Стартовая панель модуля Основные статистики и таблицы

Рис. 7.2. Рабочие окна ввода параметров для расчета Парных и частных корреляций

Вводим исходные данные, нажимаем клавишу Квадратная матрица и в появившемся окне, в строке Первый список, вводим исходные данные (рис. 7.3).

Для отображения решения выбираем вкладку Опции и нажимаем окошечко Матрица парных корреляций. Получаем решение (рис. 7.4).

Рис. 7.3. Вкладка Парные и частные корреляции

Рис. 7.4. Решение матрицы парных корреляций

Можно вывести подробную информацию, если на вкладке Парные и частные корреляции (рис. 7.5) выбрать Опции и отметить Подробная таблица результатов —? ОК вместо таблицы (рис. 7.4) появится таблица (рис. 7.6).

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

Значимая корреляционная связь выделена красным цветом.

Результаты аналогичны табл. 7.3, выполненным вручную.

Все рассуждения аналогичны приведенным в п. 7.2, 7.3.

Система STATISTICA позволяет построить как дополнительные таблицы, так и графики, которые помогают более детально провести корреляционный анализ.

Рис. 7.5. Выбор опции «Подробная таблица результатов»

Рис. 7.6. Подробная таблица результатов корреляции

Например, в окне Парные и частные корреляции (рис. 7.3) вывести Матрицу (рис. 7.7), которая содержит, парные коэффициенты корреляциии и статистику по каждой из переменных, или построить матрицу диаграмм рассеивания для всех переменных (рис. 7.8). Диаграммы более наглядно, чем таблицы, демонстрируют взаимосвязи между переменными.

Есть возможность вывести диаграмму рассеивания для каждого случая в отдельности с нанесенными доверительными интервалами. На рис. 7.9 показана диаграмма рассеивания для переменных Ни А.

Если есть необходимость, можно вывести диаграмму ЗН-рассеивания (рис. 7.10,7.11).

Рис. 7.7. Таблица данных матрицы парных корреляции

Рис. 7.8. Матрица диаграмм рассеивания для всех переменных

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

Рис. 7.9. Диаграмма рассеивания для частного случая

Рис. 7.10. Рабочие окна для ввода данных при построении диаграммы ЗР-рассеивания (окончание см. на с. 186)

Рис. 7.10. Окончание (начало см. на с. 185)

Рис. 7.11. Диаграмма Зй-рассеивания

Выбираем частные корреляции и вводим переменные.

  • 1. Исключаем влажность W, получим рис. 7.12 (ввод исходных данных) и рис. 7.13 (частные корреляции).
  • 2. Исключаем высоту откоса Я, получим рис. 7.14.
  • 3. Исключаем А, получим рис. 7.15.

4. Исключаем угол внутреннего трения R, получаем рис. 7.16. Красным цветом отмечены значимые коэффициенты корреляции для принятого уровня значимости р = 0,05.

Рис. 7.12. Далоговое окно ввода исходных данных для расчета частной корреляции

Рис. 7.13. Таблица результатов расчета частных корреляций при исключенной влажности

Рис. 7.14. Таблица результатов расчета частных корреляций при исключении высоты откоса

Рис. 7.15. Таблица результатов расчета частных корреляций при исключении угла откоса

Рис. 7.16. Таблица результатов расчета частных корреляций при исключении угла внутреннего трения

Значения коэффициентов корреляции второго порядка, приведенные в табл. 7.13-7.16 (вычисляемые по формуле (7.8)) определяют частные коэффициенты корреляции первого порядка, аналогичные приведенным в табл. 7.4-7.7.

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

т. е. частные коэффициенты стали значительно больше, чем те же парные коэффициенты корреляции,

т. е. связь уже обозначалась как сильная, т. е. закрепление одного из элементов увеличило связь между Я и аиЯир.

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

Результаты для двух сочетаний представлены на рис. 7.17, 7.18.

Читайте также:  Конвертация файлов XML в форматы Excel

Рис. 7.17. Результаты сочетаний высоты и угла откоса

Рис. 7.18. Результаты сочетаний угла откоса и угла внутреннего трения

Пример из табл. 7.8 совпадает с расчетом, показанным на рис. 7.18. Анализ аналогичен приведенному в п. 7.3.

Корреляция и ковариация в Microsoft Excel

Microsoft Excel имеет значительно меньшие возможности, нежели система STATISTICA.

Вызыв функций Ковариация и Корреляция осуществляется как обычно: Данные —? Анализ данных —? Ковариация (Корреляция) —?ОК (рис. 7.19).

Работа функции Ковариация представлена на рис. 7.20, а функции Корреляция – на рис. 7.21.

Рис. 7.19. Стартовая панель для вызова функций Ковариация и Корреляция

Рис. 7.20. Работа функции ковариация

Рис. 7.21. Работа функции корреляция

Значения корреляционных коэффициентов аналогичны вычисленным вручную и в системе STATISTICA.

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

Формула для вычисленийФункция EXCEL или инструмент Анализа данных
Оценка параметров модели парной регрессии ЛИНЕЙН(изв_знач_у; зв_знач_х; константа; стат) Смысл аргументов функции изв_знач_у – диапазон значений у; изв_знач_х – диапазон значений х; константа – устанавливается на 0, если заранее известно, что свободный член равен 0 и на 1 в противном случае; стат– устанавливается на 0, если не нужен вывод дополнительных сведений регрессионного анализа и на 1 в противном случае.

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

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

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

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

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

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

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

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

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

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

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

, , (5)

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

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

= , = .

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

0,636263125

a0=

-109

ε 2

( xi – хср. ) 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 ;

Ссылка на основную публикацию
Adblock
detector