Предположения регрессионного анализа. Регрессия в Excel: уравнение, примеры. Линейная регрессия. Разбор результатов анализа

ВЫВОД ИТОГОВ

Таблица 8.3а. Регрессионная статистика

Регрессионная статистика
Множественный R	0,998364
R-квадрат	0,99673
Нормированный R-квадрат	0,996321
Стандартная ошибка	0,42405
Наблюдения	10

Сначала рассмотрим верхнюю часть расчетов, представленную в таблице 8.3а , - регрессионную статистику.

Величина R-квадрат , называемая также мерой определенности, характеризует качество полученной регрессионной прямой. Это качество выражается степенью соответствия между исходными данными и регрессионной моделью (расчетными данными). Мера определенности всегда находится в пределах интервала .

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

Если значение R-квадрата близко к единице, это означает, что построенная модель объясняет почти всю изменчивость соответствующих переменных. И наоборот, значение R-квадрата , близкое к нулю, означает плохое качество построенной модели.

В нашем примере мера определенности равна 0,99673, что говорит об очень хорошей подгонке регрессионной прямой к исходным данным.

Множественный R - коэффициент множественной корреляции R - выражает степень зависимости независимых переменных (X) и зависимой переменной (Y).

Множественный R равен квадратному корню из коэффициента детерминации, эта величина принимает значения в интервале от нуля до единицы.

В простом линейном регрессионном анализе множественный R равен коэффициенту корреляции Пирсона. Действительно, множественный R в нашем случае равен коэффициенту корреляции Пирсона из предыдущего примера (0,998364).

Таблица 8.3б. Коэффициенты регрессии

	Коэффициенты	Стандартная ошибка	t-статистика
Y-пересечение	2,694545455	0,33176878	8,121757129
Переменная X 1	2,305454545	0,04668634	49,38177965
* Приведен усеченный вариант расчетов

Теперь рассмотрим среднюю часть расчетов, представленную в таблице 8.3б . Здесь даны коэффициент регрессии b (2,305454545) и смещение по оси ординат, т.е. константа a (2,694545455).

Исходя из расчетов, можем записать уравнение регрессии таким образом:

Y= x*2,305454545+2,694545455

Направление связи между переменными определяется на основании знаков (отрицательный или положительный) коэффициентов регрессии (коэффициента b).

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

Если знак при коэффициенте регрессии - отрицательный, связь зависимой переменной с независимой является отрицательной (обратной).

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

ВЫВОД ОСТАТКА

Таблица 8.3в. Остатки

Наблюдение	Предсказанное Y	Остатки	Стандартные остатки
1	9,610909091	-0,610909091	-1,528044662
2	7,305454545	-0,305454545	-0,764022331
3	11,91636364	0,083636364	0,209196591
4	14,22181818	0,778181818	1,946437843
5	16,52727273	0,472727273	1,182415512
6	18,83272727	0,167272727	0,418393181
7	21,13818182	-0,138181818	-0,34562915
8	23,44363636	-0,043636364	-0,109146047
9	25,74909091	-0,149090909	-0,372915662
10	28,05454545	-0,254545455	-0,636685276

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

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

Определение регрессионного анализа

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

Линейная регрессия

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

В случае, когда функция линейная регрессия имеет вид

здесь — компоненты вектора .

Значения параметров в случае линейной регрессии находят с помощью метода наименьших квадратов . Использование этого метода обосновано предположением о гауссовском распределении случайной переменной.

Разности между фактическими значениями зависимой переменной и восстановленными называются регрессионными остатками (residuals). В литературе используются также синонимы: невязки и ошибки . Одной из важных оценок критерия качества полученной зависимости является сумма квадратов остатков:

Здесь — Sum of Squared Errors.

Дисперсия остатков вычисляется по формуле

Здесь — Mean Square Error, среднеквадратичная ошибка.

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

Нелинейная регрессия

Нелинейные регрессионные модели - модели вида

которые не могут быть представлены в виде скалярного произведения

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

Значения параметров в случае нелинейной регрессии находят с помощью одного из методов градиентного спуска, например алгоритма Левенберга-Марквардта .

О терминах

Термин "регрессия" был введён Фрэнсисом Гальтоном в конце 19-го века. Гальтон обнаружил, что дети родителей с высоким или низким ростом обычно не наследуют выдающийся рост и назвал этот феномен "регрессия к посредственности". Сначала этот термин использовался исключительно в биологическом смысле. После работ Карла Пирсона этот термин стали использовать и в статистике.

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

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

Есть различие между терминами: "приближение функций", "аппроксимация", "интерполяция", и "регрессия". Оно заключается в следующем.

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

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

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

Целью регрессионного анализа является измерение связи меж­ду зависимой переменной и одной (парный регрессионный анализ) или не­сколькими (множественный) независимыми переменными. Независимые переменные называют также факторными, объясняющими, опреде­ляющими, регрессорами и предикторами.

Зависимую переменную иногда называют определяемой, объясняемой, «откликом». Чрезвы­чайно широкое распространение регрессионного анализа в эмпири­ческих исследованиях связано не только с тем, что это удобный ин­струмент тестирования гипотез. Регрессия, особенно множественная, является эффективным методом моделирования и прогнозирования.

Объяснение принципов работы с регрессионным анализом начнем с более простого - парного метода.

Парный регрессионный анализ

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

Чтобы более наглядно проиллюстрировать различия между двумя методами анализа данных, обратимся к уже рассмотренному приме­ру с переменными «поддержка СПС» и «доля сельского населения». Исходные данные идентичны. Отличие в диаграммах рассеяния бу­дет заключаться в том, что в регрессионном анализе корректно от­кладывать зависимую переменную - в нашем случае «поддержка СПС» по оси Y, тогда как в корреляционном анализе это не имеет значения. После чистки выбросов диаграмма рассеяния имеет вид:

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

Представим обычную математическую линейную функцию. Лю­бую прямую в евклидовом пространстве можно описать формулой:

где а - константа, задающая смещение по оси ординат; b - коэффи­циент, определяющий угол наклона линии.

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

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

Константой является точка пересечения линии регрессии и оси ординат (F-пересечение, в статистических пакетах, как правило, обозначаемое «interceptor»). В нашем примере с голосованием за СПС ее округленное значение составит 10,55. Угловой коэффициент Ъ бу­дет равен примерно -0,1 (как и в корреляционном анализе, знак по­казывает тип связи - прямая или обратная). Таким образом, получен­ная модель будет иметь вид СП С = -0,1 х Сел. нас. + 10,55.

Так, для случая «Республика Адыгея» с долей сель­ского населения 47% предсказанное значение составит 5,63:

СПС = -0,10 х 47 + 10,55 = 5,63.

Разность между исходным и предсказанным значениями называет­ся остатком (с этим термином - принципиальным для статистики - мы уже сталкивались при анализе таблиц сопряженности). Так, для случая «Республика Адыгея» остаток будет равен 3,92 - 5,63 = -1,71. Чем больше модульное значение остатка, тем менее удачно предсказа­но значение.

Рассчитываем предсказанные значения и остатки для всех случаев: Случай Сел. нас. СПС (исходное) СПС (предсказанное) Остатки Республика Адыгея 47 3,92 5,63 -1,71 - Республика Алтай 76 5,4 2,59 2,81 Республика Башкортостан 36 6,04 6,78 -0,74 Республика Бурятия 41 8,36 6,25 2,11 Республика Дагестан 59 1,22 4,37 -3,15 Республика Ингушетия 59 0,38 4,37 3,99 И т.д.

Анализ соотношения исходных и предсказанных значений служит для оценки качества полученной модели, ее прогностической способности. Одним из главных показателей регрессионной статистики является множественный коэффициент корреляции R - коэффициент корреляции между исходными и предсказанными значениями зави­симой переменной. В парном регрессионном анализе он равен обыч­ному коэффициенту корреляции Пирсона между зависимой и неза­висимой переменной, в нашем случае - 0,63. Чтобы содержательно интерпретировать множественный R, его необходимо преобразовать в коэффициент детерминации. Это делается так же, как и в корреля­ционном анализе - возведением в квадрат. Коэффициент детерминации R -квадрат (R 2) показывает долю вариации зависимой пере­менной, объясняемую независимой (независимыми) переменными.

В нашем случае R 2 = 0,39 (0,63 2); это означает, что переменная «доля сельского населения» объясняет примерно 40% вариации переменной «поддержка СПС». Чем больше величина коэффициента детер­минации, тем выше качество модели.

Другим показателем качества модели является стандартная ошиб­ка оценки (standard error of estimate). Это показатель того, насколько сильно точки «разбросаны» вокруг линии регрессии. Мерой разброса для интервальных переменных является стандартное отклонение. Со­ответственно, стандартная ошибка оценки - это стандартное откло­нение распределения остатков. Чем выше ее значение, тем сильнее разброс и тем хуже модель. В нашем случае стандартная ошибка со­ставляет 2,18. Именно на эту величину наша модель будет «ошибаться в среднем» при прогнозировании значения переменной «поддерж­ка СПС».

Регрессионная статистика включает в себя также дисперсионный анализ. С его помощью мы выясняем: 1) какая доля вариации (дисперсии) зависимой переменной объясняется независимой перемен­ной; 2) какая доля дисперсии зависимой переменной приходится на остатки (необъясненная часть); 3) каково отношение этих двух вели­чин (/"-отношение). Дисперсионная статистика особенно важна для выборочных исследований - она показывает, насколько вероятно наличие связи между независимой и зависимой переменными в генеральной совокупности. Однако и для сплошных исследований (как в нашем примере) изучение результатов дисперсионного анализа небесполезно. В этом случае проверяют, не вызвана ли выявленная ста­тистическая закономерность стечением случайных обстоятельств, насколько она характерна для того комплекса условий, в которых на­ходится обследуемая совокупность, т.е. устанавливается не истинность полученного результата для какой-то более обширной гене­ральной совокупности, а степень его закономерности, свободы от случайных воздействий.

В нашем случае статистика дисперсионного анализа такова:

	SS	df	MS	F	значение
Регрес.	258,77	1,00	258,77	54,29	0.000000001
Остат.	395,59	83,00	Л,11
Всего	654,36

F-отношение 54,29 значимо на уровне 0,0000000001. Соответ­ственно, мы можем с уверенностью отвергнуть нулевую гипотезу (что обнаруженная нами связь носит случайный характер).

Аналогичную функцию выполняет критерий t, но уже в отношении регрессионных коэффициентов (углового и F-пересечения). С помо­щью критерия / проверяем гипотезу о том, что в генеральной совокуп­ности регрессионные коэффициенты равны нулю. В нашем случае мы вновь можем уверенно отбросить нулевую гипотезу.

Множественный регрессионный анализ

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

Y = b1X1 + b2X2 + …+ bpXp + а.

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

При работе с множественной регрессией, в отличие от парной, не­обходимо определять алгоритм анализа. Стандартный алгоритм включает в итоговую регрессионную модель все имеющиеся предикторы. Пошаговый алгоритм предполагает последовательное включе­ние (исключение) независимых переменных, исходя из их объяснительного «веса». Пошаговый метод хорош, когда имеется много независимых переменных; он «очищает» модель от откровенно слабых предикторов, делая ее более компактной и лаконичной.

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

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

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

Национальный фактор (переменная «русское население»; операционализирована как доля русского населения в субъектах РФ). Предполагается, что увеличение доли русского населения ведет к сни­жению активности избирателей;

Фактор урбанизации (переменная «городское население»; операционализирована как доля городского населения в субъектах РФ, с этим фактором мы уже работали в рамках корреляционного анализа). Предполагается, что увеличение доли городского населения также ве­дет к снижению активности избирателей.

Зависимая переменная - «интенсивность избирательной активно­сти» («актив») операционализирована через усредненные данные яв­ки по регионам на федеральных выборах с 1995 по 2003 г. Исходная таблица данных для двух независимых и одной зависимой перемен­ной будет иметь следующий вид:

Случай	Переменные
	Актив.	Гор. нас.	Рус. нас.
Республика Адыгея	64,92	53	68
Республика Алтай	68,60	24	60
Республика Бурятия	60,75	59	70
Республика Дагестан	79,92	41	9
Республика Ингушетия	75,05	41	23
Республика Калмыкия	68,52	39	37
Карачаево-Черкесская Республика	66,68	44	42
Республика Карелия	61,70	73	73
Республика Коми	59,60	74	57
Республика Марий Эл	65,19	62	47

И т.д. (после чистки выбросов остается 83 случая из 88)

Статистика, описывающая качество модели:

1. Множественный R = 0,62; Л-квадрат = 0,38. Следовательно, национальный фактор и фактор урбанизации вместе объясняют около 38% вариации переменной «электоральная активность».

2. Средняя ошибка составляет 3,38. Именно настолько «в среднем ошибается» построенная модель при прогнозировании уровня явки.

3. /л-отношение объясненной и необъясненной вариации состав­ляет 25,2 на уровне 0,000000003. Нулевая гипотеза о случайности выявленных связей отвергается.

4. Критерий /для константы и регрессионных коэффициентов пе­ременных «городское население» и «русское население» значим на уровне 0,0000001; 0,00005 и 0,007 соответственно. Нулевая гипотеза о случайности коэффициентов отвергается.

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

В нашем примере к уникальным и влиятельным случаям, в частно­сти, относится Дагестан.

Случай	Исходные значения	Предска­ значения	Остатки	Расстояние Махаланобиса	Расстояние
Адыгея	64,92	66,33	-1,40	0,69	0,00
Республика Алтай	68,60	69.91	-1,31	6,80	0,01
Республика Бурятия	60,75	65,56	-4,81	0,23	0,01
Республика Дагестан	79,92	71,01	8,91	10,57	0,44
Республика Ингушетия	75,05	70,21	4,84	6,73	0,08
Республика Калмыкия	68,52	69,59	-1,07	4,20	0,00

Собственно регрессионная модель обладает следующими парамет­рами: У-пересечение (константа) = 75,99; Ь (Гор. нас.) = -0,1; Ъ (Рус. нас.) = -0,06. Итоговая формула:

Аактив, = -0,1 х Гор. нас.n+- 0,06 х Рус. нас.n + 75,99.

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

В нашем примере бета (Гор. нас.) = -0,43, бета (Рус. нас.) = -0,28. Та­ким образом, оба фактора отрицательно влияют на уровень электо­ральной активности, при этом значимость фактора урбанизации су­щественно выше значимости национального фактора. Совокупное влияние обоих факторов определяет около 38% вариации переменной «электоральная активность» (см. значение Л-квадрат).

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

Виды регрессии

Само это понятие было введено в математику в 1886 году. Регрессия бывает:

• линейной;
• параболической;
• степенной;
• экспоненциальной;
• гиперболической;
• показательной;
• логарифмической.

Пример 1

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

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

		Количество уволившихся	Зарплата
			30000 рублей
			35000 рублей
			40000 рублей
			45000 рублей
			50000 рублей
			55000 рублей
			60000 рублей

Для задачи определения зависимости количества уволившихся работников от средней зарплаты на 6 предприятиях модель регрессии имеет вид уравнения Y = а 0 + а 1 x 1 +…+а k x k , где х i — влияющие переменные, a i — коэффициенты регрессии, a k — число факторов.

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

Использование возможностей табличного процессора «Эксель»

Анализу регрессии в Excel должно предшествовать применение к имеющимся табличным данным встроенных функций. Однако для этих целей лучше воспользоваться очень полезной надстройкой «Пакет анализа». Для его активации нужно:

• с вкладки «Файл» перейти в раздел «Параметры»;
• в открывшемся окне выбрать строку «Надстройки»;
• щелкнуть по кнопке «Перейти», расположенной внизу, справа от строки «Управление»;
• поставить галочку рядом с названием «Пакет анализа» и подтвердить свои действия, нажав «Ок».

Если все сделано правильно, в правой части вкладки «Данные», расположенном над рабочим листом «Эксель», появится нужная кнопка.

в Excel

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

• щелкаем по кнопке «Анализ данных»;
• в открывшемся окне нажимаем на кнопку «Регрессия»;
• в появившуюся вкладку вводим диапазон значений для Y (количество уволившихся работников) и для X (их зарплаты);
• подтверждаем свои действия нажатием кнопки «Ok».

В результате программа автоматически заполнит новый лист табличного процессора данными анализа регрессии. Обратите внимание! В Excel есть возможность самостоятельно задать место, которое вы предпочитаете для этой цели. Например, это может быть тот же лист, где находятся значения Y и X, или даже новая книга, специально предназначенная для хранения подобных данных.

Анализ результатов регрессии для R-квадрата

В Excel данные полученные в ходе обработки данных рассматриваемого примера имеют вид:

Прежде всего, следует обратить внимание на значение R-квадрата. Он представляет собой коэффициент детерминации. В данном примере R-квадрат = 0,755 (75,5%), т. е. расчетные параметры модели объясняют зависимость между рассматриваемыми параметрами на 75,5 %. Чем выше значение коэффициента детерминации, тем выбранная модель считается более применимой для конкретной задачи. Считается, что она корректно описывает реальную ситуацию при значении R-квадрата выше 0,8. Если R-квадрата<0,5, то такой анализа регрессии в Excel нельзя считать резонным.

Анализ коэффициентов

Число 64,1428 показывает, каким будет значение Y, если все переменные xi в рассматриваемой нами модели обнулятся. Иными словами можно утверждать, что на значение анализируемого параметра оказывают влияние и другие факторы, не описанные в конкретной модели.

Следующий коэффициент -0,16285, расположенный в ячейке B18, показывает весомость влияния переменной Х на Y. Это значит, что среднемесячная зарплата сотрудников в пределах рассматриваемой модели влияет на число уволившихся с весом -0,16285, т. е. степень ее влияния совсем небольшая. Знак «-» указывает на то, что коэффициент имеет отрицательное значение. Это очевидно, так как всем известно, что чем больше зарплата на предприятии, тем меньше людей выражают желание расторгнуть трудовой договор или увольняется.

Множественная регрессия

Под таким термином понимается уравнение связи с несколькими независимыми переменными вида:

y=f(x 1 +x 2 +…x m) + ε, где y — это результативный признак (зависимая переменная), а x 1 , x 2 , …x m — это признаки-факторы (независимые переменные).

Оценка параметров

Для множественной регрессии (МР) ее осуществляют, используя метод наименьших квадратов (МНК). Для линейных уравнений вида Y = a + b 1 x 1 +…+b m x m + ε строим систему нормальных уравнений (см. ниже)

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

Отсюда получаем:

где σ — это дисперсия соответствующего признака, отраженного в индексе.

МНК применим к уравнению МР в стандартизируемом масштабе. В таком случае получаем уравнение:

в котором t y , t x 1, … t xm — стандартизируемые переменные, для которых средние значения равны 0; β i — стандартизированные коэффициенты регрессии, а среднеквадратическое отклонение — 1.

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

Задача с использованием уравнения линейной регрессии

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

	номер месяца	название месяца	цена товара N
			1750 рублей за тонну
			1755 рублей за тонну
			1767 рублей за тонну
			1760 рублей за тонну
			1770 рублей за тонну
			1790 рублей за тонну
			1810 рублей за тонну
			1840 рублей за тонну

Для решения этой задачи в табличном процессоре «Эксель» требуется задействовать уже известный по представленному выше примеру инструмент «Анализ данных». Далее выбирают раздел «Регрессия» и задают параметры. Нужно помнить, что в поле «Входной интервал Y» должен вводиться диапазон значений для зависимой переменной (в данном случае цены на товар в конкретные месяцы года), а в «Входной интервал X» — для независимой (номер месяца). Подтверждаем действия нажатием «Ok». На новом листе (если так было указано) получаем данные для регрессии.

Строим по ним линейное уравнение вида y=ax+b, где в качестве параметров a и b выступают коэффициенты строки с наименованием номера месяца и коэффициенты и строки «Y-пересечение» из листа с результатами регрессионного анализа. Таким образом, линейное уравнение регрессии (УР) для задачи 3 записывается в виде:

Цена на товар N = 11,714* номер месяца + 1727,54.

или в алгебраических обозначениях

y = 11,714 x + 1727,54

Анализ результатов

Чтобы решить, адекватно ли полученное уравнения линейной регрессии, используются коэффициенты множественной корреляции (КМК) и детерминации, а также критерий Фишера и критерий Стьюдента. В таблице «Эксель» с результатами регрессии они выступают под названиями множественный R, R-квадрат, F-статистика и t-статистика соответственно.

КМК R дает возможность оценить тесноту вероятностной связи между независимой и зависимой переменными. Ее высокое значение свидетельствует о достаточно сильной связи между переменными «Номер месяца» и «Цена товара N в рублях за 1 тонну». Однако, характер этой связи остается неизвестным.

Квадрат коэффициента детерминации R 2 (RI) представляет собой числовую характеристику доли общего разброса и показывает, разброс какой части экспериментальных данных, т.е. значений зависимой переменной соответствует уравнению линейной регрессии. В рассматриваемой задаче эта величина равна 84,8%, т. е. статистические данные с высокой степенью точности описываются полученным УР.

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

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

В рассматриваемой задаче для свободного члена посредством инструментов «Эксель» было получено, что t=169,20903, а p=2,89Е-12, т. е. имеем нулевую вероятность того, что будет отвергнута верная гипотеза о незначимости свободного члена. Для коэффициента при неизвестной t=5,79405, а p=0,001158. Иными словами вероятность того, что будет отвергнута верная гипотеза о незначимости коэффициента при неизвестной, равна 0,12%.

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

Задача о целесообразности покупки пакета акций

Множественная регрессия в Excel выполняется с использованием все того же инструмента «Анализ данных». Рассмотрим конкретную прикладную задачу.

Руководство компания «NNN» должно принять решение о целесообразности покупки 20 % пакета акций АО «MMM». Стоимость пакета (СП) составляет 70 млн американских долларов. Специалистами «NNN» собраны данные об аналогичных сделках. Было принято решение оценивать стоимость пакета акций по таким параметрам, выраженным в миллионах американских долларов, как:

• кредиторская задолженность (VK);
• объем годового оборота (VO);
• дебиторская задолженность (VD);
• стоимость основных фондов (СОФ).

Кроме того, используется параметр задолженность предприятия по зарплате (V3 П) в тысячах американских долларов.

Решение средствами табличного процессора Excel

Прежде всего, необходимо составить таблицу исходных данных. Она имеет следующий вид:

• вызывают окно «Анализ данных»;
• выбирают раздел «Регрессия»;
• в окошко «Входной интервал Y» вводят диапазон значений зависимых переменных из столбца G;
• щелкают по иконке с красной стрелкой справа от окна «Входной интервал X» и выделяют на листе диапазон всех значений из столбцов B,C, D, F.

Отмечают пункт «Новый рабочий лист» и нажимают «Ok».

Получают анализ регрессии для данной задачи.

Изучение результатов и выводы

«Собираем» из округленных данных, представленных выше на листе табличного процессора Excel, уравнение регрессии:

СП = 0,103*СОФ + 0,541*VO - 0,031*VK +0,405*VD +0,691*VZP - 265,844.

В более привычном математическом виде его можно записать, как:

y = 0,103*x1 + 0,541*x2 - 0,031*x3 +0,405*x4 +0,691*x5 - 265,844

Данные для АО «MMM» представлены в таблице:

Подставив их в уравнение регрессии, получают цифру в 64,72 млн американских долларов. Это значит, что акции АО «MMM» не стоит приобретать, так как их стоимость в 70 млн американских долларов достаточно завышена.

Как видим, использование табличного процессора «Эксель» и уравнения регрессии позволило принять обоснованное решение относительно целесообразности вполне конкретной сделки.

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

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

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

Теперь, когда мы перейдем во вкладку «Данные» , на ленте в блоке инструментов «Анализ» мы увидим новую кнопку – «Анализ данных» .

Виды регрессионного анализа

Существует несколько видов регрессий:

• параболическая;
• степенная;
• логарифмическая;
• экспоненциальная;
• показательная;
• гиперболическая;
• линейная регрессия.

О выполнении последнего вида регрессионного анализа в Экселе мы подробнее поговорим далее.

Линейная регрессия в программе 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 довольно просто составить таблицу регрессионного анализа. Но, работать с полученными на выходе данными, и понимать их суть, сможет только подготовленный человек.