Построение линейной регрессии, оценивание ее параметров и их значимости можно выполнить значительнее быстрей при использовании пакета анализа 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 %-ном уровне значимости.
В случае парной линейной регрессии коэффициент корреляции можно определить как 
. Полученное значение коэффициента корреляции свидетельствует, что связь между расходами на питание и душевым доходом очень тесная.
Показывает влияние одних значений (самостоятельных, независимых) на зависимую переменную. К примеру, как зависит количество экономически активного населения от числа предприятий, величины заработной платы и др. параметров. Или: как влияют иностранные инвестиции, цены на энергоресурсы и др. на уровень ВВП.
Результат анализа позволяет выделять приоритеты. И основываясь на главных факторах, прогнозировать, планировать развитие приоритетных направлений, принимать управленческие решения.
Регрессия бывает:
· линейной (у = а + 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 имеется еще более быстрый и удобный способ построить график линейной регрессии (и даже основных видов нелинейных регрессий, о чем см. далее). Это можно сделать следующим образом:
1) выделить столбцы с данными X и Y (они должны располагаться именно в таком порядке!);
2) вызвать Мастер диаграмм и выбрать в группе Тип – Точечная и сразу нажать Готово ;
3) не сбрасывая выделения с диаграммы, выбрать появившейся пункт основного меню Диаграмма , в котором следует выбрать пункт Добавить линию тренда ;
4) в появившемся диалоговом окне Линия тренда во вкладке Тип выбрать Линейная ;
5) во вкладке Параметры можно активизировать переключатель Показывать уравнение на диаграмме , что позволит увидеть уравнение линейной регрессии (4.4), в котором будут вычислены коэффициенты (4.5).
6) В этой же вкладке можно активизировать переключатель Поместить на диаграмму величину достоверности аппроксимации (R^2) . Эта величина есть квадрат коэффициента корреляции (4.3) и она показывает, насколько хорошо рассчитанное уравнение описывает экспериментальную зависимость. Если R 2 близок к единице, то теоретическое уравнение регрессии хорошо описывает экспериментальную зависимость (теория хорошо согласуется с экспериментом), а если R 2 близок к нулю, то данное уравнение не пригодно для описания экспериментальной зависимости (теория не согласуется с экспериментом).
В результате выполнения описанных действий получится диаграмма с графиком регрессии и ее уравнением.
§4.3. Основные виды нелинейной регрессии
Параболическая и полиномиальная регрессии.
Параболической зависимостью величины Y от величины Х называется зависимость, выраженная квадратичной функцией (параболой 2-ого порядка):
Это уравнение называется уравнением параболической регрессии Y на Х . Параметры а , b , с называются коэффициентами параболической регрессии . Вычисление коэффициентов параболической регрессии всегда громоздко, поэтому для расчетов рекомендуется использовать компьютер.
Уравнение (4.8) параболической регрессии является частным случаем более общей регрессии, называемой полиномиальной. Полиномиальной зависимостью величины Y от величины Х называется зависимость, выраженная полиномом n -ого порядка:
где числа а i (i =0,1,…, n ) называются коэффициентами полиномиальной регрессии .
Степенная регрессия.
Степенной зависимостью величины Y от величины Х называется зависимость вида:
Это уравнение называется уравнением степенной регрессии Y на Х . Параметры а и b называются коэффициентами степенной регрессии .
ln =lna +b· lnx . (4.11)
Это уравнение описывает прямую на плоскости с логарифмическими координатными осями lnx и ln . Поэтому критерием применимости степенной регрессии служит требование того, чтобы точки логарифмов эмпирических данных lnx i и lnу i находились ближе всего к прямой (4.11).
Показательная регрессия.
Показательной (или экспоненциальной ) зависимостью величины Y от величины Х называется зависимость вида:
(или ). (4.12)
Это уравнение называется уравнением показательной (или экспоненциальной ) регрессии Y на Х . Параметры а (или k ) и b называются коэффициентами показательной (или экспоненциальной ) регрессии .
Если прологарифмировать обе части уравнения степенной регрессии, то получится уравнение
ln =x· lna +lnb (или ln =k·x +lnb ). (4.13)
Это уравнение описывает линейную зависимость логарифма одной величины ln от другой величины x . Поэтому критерием применимости степенной регрессии служит требование того, чтобы точки эмпирических данных одной величины x i и логарифмы другой величины lnу i находились ближе всего к прямой (4.13).
Логарифмическая регрессия.
Логарифмической зависимостью величины Y от величины Х называется зависимость вида:
=a +b· lnx . (4.14)
Это уравнение называется уравнением логарифмической регрессии Y на Х . Параметры а и b называются коэффициентами логарифмической регрессии .
Гиперболическая регрессия.
Гиперболической зависимостью величины Y от величины Х называется зависимость вида:
Это уравнение называется уравнением гиперболической регрессии Y на Х . Параметры а и b называются коэффициентами гиперболической регрессии и определяются методом наименьших квадратов. Применение этого метода приводит к формулам:
В формулах (4.16-4.17) суммирование проводится по индексу i от единицы до количества наблюдений n .
К сожалению, в Excel нет функции, вычисляющих коэффициенты гиперболической регрессии. В тех случаях, когда заведомо не известно, что измеряемые величины связаны обратной пропорциональностью, рекомендуется вместо уравнения гиперболической регрессии искать уравнение степенной регрессии, так в Excel имеется процедура ее нахождения. Если же между измеряемыми величинами предполагается гиперболическая зависимость, то коэффициенты ее регрессии придется вычислять с помощью вспомогательных расчетных таблиц и операций суммирования по формулам (4.16-4.17).
Регрессионный анализ является одним из самых востребованных методов статистического исследования. С его помощью можно установить степень влияния независимых величин на зависимую переменную. В функционале Microsoft Excel имеются инструменты, предназначенные для проведения подобного вида анализа. Давайте разберем, что они собой представляют и как ими пользоваться.
Но, для того, чтобы использовать функцию, позволяющую провести регрессионный анализ, прежде всего, нужно активировать Пакет анализа. Только тогда необходимые для этой процедуры инструменты появятся на ленте Эксель.
Теперь, когда мы перейдем во вкладку «Данные» , на ленте в блоке инструментов «Анализ» мы увидим новую кнопку – «Анализ данных» .
Существует несколько видов регрессий:
О выполнении последнего вида регрессионного анализа в Экселе мы подробнее поговорим далее.
Внизу, в качестве примера, представлена таблица, в которой указана среднесуточная температура воздуха на улице, и количество покупателей магазина за соответствующий рабочий день. Давайте выясним при помощи регрессионного анализа, как именно погодные условия в виде температуры воздуха могут повлиять на посещаемость торгового заведения.
Общее уравнение регрессии линейного вида выглядит следующим образом: У = а0 + а1х1 +…+акхк. В этой формуле Y означает переменную, влияние факторов на которую мы пытаемся изучить. В нашем случае, это количество покупателей. Значение x – это различные факторы, влияющие на переменную. Параметры a являются коэффициентами регрессии. То есть, именно они определяют значимость того или иного фактора. Индекс k обозначает общее количество этих самых факторов.
Результаты регрессионного анализа выводятся в виде таблицы в том месте, которое указано в настройках.
Одним из основных показателей является R-квадрат . В нем указывается качество модели. В нашем случае данный коэффициент равен 0,705 или около 70,5%. Это приемлемый уровень качества. Зависимость менее 0,5 является плохой.
Ещё один важный показатель расположен в ячейке на пересечении строки «Y-пересечение» и столбца «Коэффициенты» . Тут указывается какое значение будет у Y, а в нашем случае, это количество покупателей, при всех остальных факторах равных нулю. В этой таблице данное значение равно 58,04.
Значение на пересечении граф «Переменная X1» и «Коэффициенты» показывает уровень зависимости Y от X. В нашем случае — это уровень зависимости количества клиентов магазина от температуры. Коэффициент 1,31 считается довольно высоким показателем влияния.
Как видим, с помощью программы Microsoft Excel довольно просто составить таблицу регрессионного анализа. Но, работать с полученными на выходе данными, и понимать их суть, сможет только подготовленный человек.
