Как провести регрессионный анализ в excel. Регрессионный анализ в Microsoft 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 предприятиях была проанализирована среднемесячная заработная плата и количество уволившихся сотрудников. Необходимо определить зависимость числа уволившихся сотрудников от средней зарплаты.

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

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

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

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

В 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

ЛАБОРАТОРНАЯ РАБОТА №1

1. ОПРЕДЕЛЕНИЕ КОЭФФИЦИЕНТА ПАРНОЙ КОРРЕЛЯЦИИ В ПРОГРАММЕ EXCEL

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

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

Множественная корреляция возникает от взаимодействия нескольких факторов с результативным показателем.

Необходимые условия применения корреляционного анализа:

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

2. Исследуемые факторы должны иметь количественное измерение и отражение в тех или иных источниках информации.

Применение корреляционного анализа позволяет решить следующие задачи:

1.Определить изменение результативного показателя под воздействием одного или нескольких факторов.

2. Установить относительную степень зависимости результативного показателя от каждого фактора.

Задание 1.

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

Таблица. Зависимость урожайности зерновых культур от качества земли

Номер хозяйства	Качество земли, балл х	Урожайность, ц/га у

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

Значимость коэффициента корреляции проверяется по критерию Стьюдента .

Для рассматриваемого примера r=0,99, n=18.

Для нахождения квантиля распределения Стьюдента используется функция СТЬЮДРАСПОБР со следующими аргументам: Вероятность –0,05, Степени свободы –18.

Сравнив значение t-статистики с квантилем распределения Стьюдента сделать выводы о значимости коэффициента парной корреляции. Если расчетное значение t-статистики больше квантиля распределения Стьюдента, то величина коэффициента корреляции является значимой.

ПОСТРОЕНИЕ РЕГРЕССИОННОЙ МОДЕЛИ СВЯЗИ МЕЖДУ ДВУМЯ ВЕЛИЧИНАМИ

Задание 2 .

По данным задания 1:

1) построить уравнение регрессии (линейную модель), которое характеризует прямолинейную зависимость между качеством земли и урожайностью;

2). выполнить проверку адекватности полученной модели.

1 - ый способ.

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

2. Вызвать функцию ЛИНЕЙН .

3.Указать для функции следующие аргументы: Изв_знач_ y Урожайность, ц/га; Изв_знач_ x - столбец значений показателя Качество земли, балл ; Константа –1, Стат– 1 (позволяет вычислить показатели, используемые для проверки адекватности модели. Если Стат– 0, то такие показатели вычисляться не будут.

4. Нажать комбинацию клавиш Ctrl - Shift - Enter .

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

Таблица 2
a 1	a 0
S e1	S e0
R 2	S e

Q R	Q e

a 1 , a 0 – коэффициенты модели;

S e 1 S e 0 – стандартные ошибки коэффициентов. Чем точнее модель, тем меньше эти величины.

R 2 – коэффициент детерминации. Чем он больше, тем точнее модель.

F – статистика для проверки значимости модели.

n - k -1 – число степеней свободы (n-объем выборки, k- количество входных переменных; в данном примере n=20, к=1)

Q R – сумма квадратов, обусловленная регрессией;

Q e – сумма квадратов ошибок.

5. Для проверки адекватности модели найти квантиль распределения Фишера F f . с помощью функции F РАСПОБР . Для этого в любой свободной ячейке ввести функцию F РАСПОБР со следующими аргументами: Вероятность – 0,05, Степени_свободы _1–1, Степени_свободы _2–18. Если F> F f , то модель адекватна исходным данным

6. Проверить адекватность построенной модели, используя расчетный уровень значимости (P). Ввести функцию F РАСП со следующими аргументами: X – значение статистики F , Степени_свободы_1 –1, Степени_свободы_2 – 18. Если расчетный уровень значимости P<α =0,05, то модель адекватна исходным данным.

2 –й способ.

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

Выбрать команду Сервис/Анализ данных/Регрессия . В диалоговом окне установить: Входной интервал Y – значения показателя Урожайность, ц/га, Входной интервал X – значения показателя Качество земли, балл .

Установить флажок Метки . В области Параметры вывода выбрать переключатель Выходной интервал и указать ячейку, с которой будет начинаться вывод результатов. Для получения результатов нажать кнопку ОК.

Интерпретация результатов.

Искомые коэффициенты модели находятся в столбце Коэффициенты :

Для данного примера уравнение модели имеет вид:

Y=2,53+0,5X

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

Проверка адекватности модели выполняется по расчетному уровню значимости P, указанному в столбце Значимость F . Если расчетный уровень значимости меньше заданного уровня значимости α =0,05, то модель адекватна.

Проверка статистической значимости коэффициентов модели выполняется по расчетным уровням значимости P, указанным в столбце P -значение . Если расчетный уровень значимости меньше заданного уровня значимости α =0,05, то соответствующий коэффициент модели статистически значим.

Множественный R – коэффициент корреляции . Чем ближе его величина к 1, тем более тесная связь между изучаемыми показателями. Для данного примера R= 0,99. Это позволяет сделать вывод, что качество земли – один из основных факторов, от которого зависит урожайность зерновых культур.

R -квадрат – коэффициент детерминации . Он получается возведением в квадрат коэффициента корреляции – R 2 =0,98. Он показывает, что урожайность зерновых культур на 98% зависит от качества почвы, а на долю других факторов приходится 0,02%.

3-ий способ . ГРАФИЧЕСКИЙ СПОСОБ ПОСТРОЕНИЯ МОДЕЛИ.

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

Получить линейную модель зависимости урожайности зерновых культур от качества земли.

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

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

Регрессионный анализ в 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).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример:

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

Построение линейной регрессии, оценивание ее параметров и их значимости можно выполнить значительнее быстрей при использовании пакета анализа Excel (Регрессия). Рассмотрим интерпретацию полученных результатов в общем случае (k объясняющих переменных) по данным примера 3.6.

В таблице регрессионной статистики приводятся значения:

Множественный R – коэффициент множественной корреляции ;

R - квадрат – коэффициент детерминации R 2 ;

Нормированный R - квадрат – скорректированный R 2 с поправкой на число степеней свободы;

Стандартная ошибка – стандартная ошибка регрессии S ;

Наблюдения – число наблюдений n .

В таблице Дисперсионный анализ приведены:

1. Столбец df - число степеней свободы, равное

для строки Регрессия df = k ;

для строкиОстаток df = n – k – 1;

для строкиИтого df = n – 1.

2. Столбец SS – сумма квадратов отклонений, равная

для строки Регрессия ;

для строкиОстаток ;

для строкиИтого .

3. Столбец MS дисперсии, определяемые по формуле MS = SS /df :

для строки Регрессия – факторная дисперсия;

для строкиОстаток – остаточная дисперсия.

4. Столбец F – расчетное значение F -критерия, вычисляемое по формуле

F = MS (регрессия)/MS (остаток).

5. Столбец Значимость F –значение уровня значимости, соответствующее вычисленной F -статистике.

Значимость F = FРАСП(F- статистика, df (регрессия), df (остаток)).

Если значимость F < стандартного уровня значимости, то R 2 статистически значим.

	Коэффи-циенты	Стандартная ошибка	t-cта-тистика	P-значение	Нижние 95%	Верхние 95%
Y	65,92	11,74	5,61	0,00080	38,16	93,68
X	0,107	0,014	7,32	0,00016	0,0728	0,142

В этой таблице указаны:

1. Коэффициенты – значения коэффициентов a , b .

2. Стандартная ошибка –стандартные ошибки коэффициентов регрессии S a , S b .

3. t- статистика – расчетные значения t -критерия, вычисляемые по формуле:

t-статистика = Коэффициенты / Стандартная ошибка.

4.Р -значение (значимость t ) – это значение уровня значимости, соответствующее вычисленной t- статистике.

Р -значение = СТЬЮДРАСП (t -статистика, df (остаток)).

Если Р -значение < стандартного уровня значимости, то соответствующий коэффициент статистически значим.

5. Нижние 95% и Верхние 95% – нижние и верхние границы 95 %-ных доверительных интервалов для коэффициентов теоретического уравнения линейной регрессии.

ВЫВОД ОСТАТКА
Наблюдение	Предсказанное y	Остатки e
	72,70	-29,70
	82,91	-20,91
	94,53	-4,53
	105,72	5,27
	117,56	12,44
	129,70	19,29
	144,22	20,77
	166,49	24,50
	268,13	-27,13

В таблице ВЫВОД ОСТАТКА указаны:

в столбце Наблюдение – номер наблюдения;

в столбце Предсказанное y – расчетные значения зависимой переменной;

в столбце Остатки e – разница между наблюдаемыми и расчетными значениями зависимой переменной.

Пример 3.6. Имеются данные (усл. ед.) о расходах на питание y и душевого дохода x для девяти групп семей:

x
y

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

Результаты регрессионного анализа принято записывать в виде:

где в скобках указаны стандартные ошибки коэффициентов регрессии.

Коэффициенты регрессии а = 65,92 и b = 0,107. Направление связи между y и x определяет знак коэффициентарегрессии b = 0,107, т.е. связь является прямой и положительной. Коэффициент b = 0,107 показывает, что при увеличении душевого дохода на 1 усл. ед. расходы на питание увеличиваются на 0,107 усл. ед.

Оценим значимость коэффициентов полученной модели. Значимость коэффициентов (a, b ) проверяется по t -тесту:

Р-значение (a ) = 0,00080 < 0,01 < 0,05

Р-значение (b ) = 0,00016 < 0,01 < 0,05,

следовательно, коэффициенты (a, b ) значимы при 1 %-ном уровне, а тем более при 5 %-ном уровне значимости. Таким образом, коэффициенты регрессии значимы и модель адекватна исходным данным.

Результаты оценивания регрессии совместимы не только с полученными значениями коэффициентов регрессии, но и с некоторым их множеством (доверительным интервалом). С вероятностью 95 % доверительные интервалы для коэффициентов есть (38,16 – 93,68) для a и (0,0728 – 0,142) для b.

Качество модели оценивается коэффициентом детерминации R 2 .

Величина R 2 = 0,884 означает, что фактором душевого дохода можно объяснить 88,4 % вариации (разброса) расходов на питание.

Значимость R 2 проверяется по F- тесту: значимость F = 0,00016 < 0,01 < 0,05, следовательно, R 2 значим при 1 %-ном уровне, а тем более при 5 %-ном уровне значимости.

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

Линия регрессии является графическим отражением взаимосвязи между явлениями. Очень наглядно можно построить линию регрессии в программе Excel.

Для этого необходимо:

1.Открыть программу Excel

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

1 столбик — № испытуемого

2 столбик — агрессивность в баллах

3 столбик — неуверенность в себе в баллах

3.Затем необходимо выделить оба столбика (без названия столбика), нажать вкладку вставка , выбрать точечная , а из предложенных макетов выбрать самый первый точечная с маркерами .

4.Итак у нас получилась заготовка для линии регрессии — так называемая — диаграмма рассеяния . Для перехода к линии регрессии нужно щёлкнуть на получившийся рисунок, нажать вкладку конструктор, найти на панели макеты диаграмм и выбрать Ма кет9 , на нем ещё написано f(x)

5.Итак, у нас получилась линия регрессии. На графике также указано её уравнение и квадрат коэффициента корреляции

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

Линия регрессии построена в MS Excel. Теперь её можно добавить в текст работы.