Учебно-методическое пособие для студентов экономического и физико-математического факультетов (стр. 4 из 16)

Для парного регрессионного анализа выполняется условие: коэффициент детерминации R² равен квадрату коэффициента корреляции, то есть

Электронная таблица Excel

Ранее изученных нами статистических функций вполне достаточно для непосредственного вычисления коэффициентов регрессии. Для нахождения значения параметра b достаточно уметь вычислять значение ковариации и дисперсии, а для значения a необходимы также средние значения. Эти параметры можно легко найти самостоятельно, однако в электронной таблице Excel имеется много достаточно разнородных инструментов для определения параметров регрессии. Среди них, что совершенно очевидно, имеются статистические функции, а также дополнительные средства — это надстройка и средства точечных диаграмм. Начнем рассмотрение со статистических функций.

Функция НАКЛОН возвращает наклон (коэффициент b в уравнении линейной регрессии). При этом аргументами являются два массива, в первом из которых задаются значения зависимой переменной y, а во втором значения регрессора x. Значение коэффициента a может быть найдено либо по соответствующей формуле, либо при помощи функции ОТРЕЗОК, которая имеет подобные аргументы. Функция ПРЕДСКАЗ вычисляет или предсказывает будущее значение по произвольному значению x. Данная функция имеет три аргумента. Первый — это значение x, а остальные имеют тот же смысл, что и в функциях НАКЛОН и ОТРЕЗОК.

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

Предположим, что исходные данные также располагаются в таблице 1, тогда в документ Excel параметры регрессии можно вычислить на основании следующих формул:

b=	= КОВАР(C2:C16;B2:B16)/ДИСПР(B2:B16)
b=	=НАКЛОН(C2:C16;B2:B16)
a=	= СРЗНАЧ(C2:C16)- НАКЛОН(C2:C16;B2:B16)* СРЗНАЧ(B2:B16)
a=	=ОТРЕЗОК(C2:C16;B2:B16)
R2=	=КОРРЕЛ(C2:C16;B2:B16)* КОРРЕЛ(C2:C16;B2:B16)
Прогноз при x=17	=ПРЕДСКАЗ(17;C2:C16;B2:B16)

В данном случае предлагаются два способа вычисления параметров: на основании формул НАКЛОН и ОТРЕЗОК и через исходные формулы для параметров регрессии.

Вычисленные на основании этих формул значения будут равны:

b = –7,703

a = 239,96

R² = 0,7868.

При цене, равной 17, прогнозируемый спрос будет равен 109,014.

Анализируя полученные данные, можно прийти к следующим выводам:

1. Поскольку b = –7,703, то можно предполагать, что увеличение цены на единицу в среднем уменьшает спрос на –7,703 тысячи штук, аналогично уменьшение цены на единицу увеличит спрос на –7,703 тысячи штук.

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

3. Регрессионная модель имеет вид: y = 239,96 – 7,703x.

4. Прогнозируемый спрос при цене равной 17 будет составлять 109,014 тысячи единиц.

5. Коэффициент детерминации равен 0,7868. Данное значение может быть интерпретировано следующим образом: изменение зависимой переменной, в данном случае y на 78 %, описывается изменением независимой переменной (регрессора) x, что говорит о достаточной обоснованности использования данной модели.

Замечание. Описанные выше функции возвращают один параметр линейной регрессии. Однако имеется функция, которая одновременно возвращает оба параметра. Это функция ЛИНЕЙН(). Более подробно с данной функцией можно ознакомится по справочной системе.

Кроме указанных функций в Excel имеется возможность построить на диаграмме линию регрессии, которая называется линией линейного тренда. Для этого необходимо задать точечную диаграмму (диаграмма обязательно должна быть точечной), и выбрав произвольную точку в контекстном меню, можно выбрать пункт Добавить линию тренда. Хотя термин «тренд» имеет несколько другой смысл, применительно к временным рядам, в данном случае термины «тренд» и «линия регрессии» будем отождествлять друг с другом. Выбор пункта Добавить линию тренда приведет к появлению диалогового окна, у которого имеются две закладки — Тип и Параметры (рис. 6).

Рис. 6

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

На закладке Параметры можно задать дополнительную информацию, которая будет присутствовать на диаграмме. Во-первых, это возможность прогнозирования, что позволит построить линии тренда вперед или назад на соответствующее число единиц. Опция Показывать уравнение на диаграмме позволяет выдавать вид уравнения, а опция Поместить на диаграмму величину достоверности аппроксимации (R^2) выводит значение коэффициента детерминации. Построив точечную диаграммы для данных, заданных в таблице 1, и линию тренда, можно получить диаграмму, которая изображена на рисунке 7.

Рис. 7

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

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

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

Рис. 8

Если Входной интервал Y определить как C1:C16, а Входной интервал X — B1:B16, задать некоторым образом параметры вывода, а также установить опцию Метки, то автоматически на новом листе будет сгенерированна таблица 4.

Таблица 4

ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R	0,887036
R-квадрат	0,786833
Нормированный R-квадрат	0,770435
Стандартная ошибка	2,264609
Наблюдения	15

Продолжение табл. 4

Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия	1	246,0889	246,0889	47,985	1,04E–05
Остаток	13	66,66991	5,128455
Итого	14	312,7588
	Коэффициенты	Стандартная ошибка	t- статистика	P- значение	Нижние 95 %	Верхние 95 %
Y-пересечение	240,142	17,70861	13,56075	4,76E–09	201,8849	278,3991
Цена x (р.)	–7,71453	1,113671	–6,92712	1,04E–05	–10,1205	–5,30859

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

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