Использование инструмента Регрессия

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

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

Рассмотрим модель линейной парной регрессии:

, (24)

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

Оценка параметров линейной модели осуществляется методом наименьших квадратов (МНК). Согласно принципу МНК оценки параметров уравнения и находятся путем минимизации суммы квадратов:

. (25)

Отсюда имеем систему уравнений:

. (26)

Решая систему, получим:

, . (27)

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

Качество уравнения регрессииоценивается с помощью коэффициента детерминации:

. (28)

Чем ближе к единице, тем лучше.

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

-критерия Фишера представляет собой отношение факторной и остаточной дисперсий в расчете на одну степень свободы:

. (29)

Если , уравнение регрессии считается статистически значимым, в противном случае – статистически незначимым.

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

Различают два класса нелинейных регрессий:

1 Регрессии нелинейные относительно включенных в анализ объясняющих переменных, но линейные по оцениваемым параметрам:

а) полиномы, ;

б) равносторонняя гипербола, ;

2 Регрессии, нелинейные по оцениваемым параметрам:

а) степенная, ;

б) показательная, ;

в) экспоненциальная, .

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

.

Ряд уравнений нелинейной регрессии могут быть сведены к линейным, путем замен переменных (таблица 27).

Таблица 27 - Линеаризация ( ) некоторых наиболее употребительных регрессионных зависимостей

Вид зависимости
Гипербола,
Показательная,
Степенная,
Полулогарифмическая,
Обратная,

Среди нелинейных функций, сводящихся к линейным, в экономических исследованиях очень широко используется степенная функция. Это связано с тем, что параметр в ней имеет четкое экономическое истолкование. Он является коэффициентом эластичности. Т.е. величина коэффициента показывает, на сколько % изменится результат в среднем, если фактор изменится на 1 %.

Коэффициент эластичности рассчитывается по следующей формуле:

. (30)

Уравнение нелинейной регрессии, так же как и в линейной зависимости, дополняется показателем корреляции – индексом корреляции ( ):

. (31)

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

При использовании инструмента Регрессия выбираются отдельно интервалы для независимых переменных (х) и зависимой переменной (y). Предельное число независимых переменных – 18. Выделенные интервалы могут быть помечены метками. MS Excel автоматически упорядочивает независимые переменные по возрастанию слева направо и присваивает им в выходной таблице имена х1, х2, х3 и т.д.

Для того, чтобы линия регрессии проходила через начало координат, в соответствующем диалоговом окне можно выделить флажок Константа – Ноль.

Особое внимание следует уделять выходному интервалу. Для этого вводится ссылка для верхней левой ячейки интервала, в которой будут представлены выходные таблицы. Необходимо отвести по крайней мере семь столбцов для выходной таблицы дисперсионного анализа (ANOVA – analysis of variance); не менее 4 столбцов выходной таблицы остатков, содержащей остатки, стандартизированные остатки и предсказанные значения; не менее двух столбцов для выходной таблицы распределения данных. Кроме того, данный инструмент по желанию пользователя может генерировать три графика остатков (диаграмм для независимых переменных в сравнении с разностью), три графика подбора (диаграмм для предсказанных значений по сравнению с наблюдениями), один график нормального распределения (диаграмму для нормальных вероятностей).

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

Группа итоговых выходных таблиц (ВЫВОД ИТОГОВ) будет включать следующие таблицы: Регрессионная статистика: Множественный R, R-квадрат, Нормированный R-квадрат, Стандартная ошибка (оценка стандартного отклонения выборки), Наблюдения (счет); Дисперсионный анализ (Регрессия, Остаток, Итого (столбцы df, SS, MS, F значение критерия Фишера для выборки), Значимость F (вероятность ошибочного отклонения нулевой гипотезы для F)), Коэффициенты (коэффициенты регрессии для определения наличия взаимосвязи между массивами зависимой и независимыми переменными), Стандартная ошибка (см. формулу 1), t-статистика (критерий Стьюдента), Р-Значение, Нижние 95%, Верхние 95%, Нижние 95.000%, Верхние 95.000% (границы интервальных оценок для математического ожидания и дисперсии).

Пример. Определить регрессию между независимыми переменными x₁, x₂, х₃ и зависимой переменной у. Входная информация представлена в таблице 28.

Таблица 28 – Входная информация при использовании инструмента Регрессия

После обращения к команде Анализ данных и открытия соответствующего диалогового окна, в нем выбирается инструмент Регрессия. Вид диалогового окна Регрессия представлен на рисунке 20.

Рисунок 20 - Вид диалогового окна «Регрессия»

В диалоговом окне Регрессия указывается входной интервал Y: Е3:Е6 и входной интервал X:B2:D6. Включив переключатель Метки, можно указать выходной интервал G2 (ОК).

Как видно из ВЫВОДА ИТОГОВ уравнение регрессии выглядит следующим образом: у = 1,57 + 1,91 x₁ – 0,15*х₂ – 0,04х₃ (таблица 29).

Таблица 29 - Фрагмент ВЫВОДА ИТОГОВ при использовании инструмента Регрессия

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

- линейная;

- полиномиальная;

- логарифмическая;

- экспоненциальная;

- степенная.

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

1 Активизировать диаграмму.

2 Выделить ряд данных, для которого строится линия тренда;

3 Выполнить команду Добавить линию тренда из контекстного меню. На экране появится диалоговое окно Линия тренда.

4 Выбрать в диалоговом окне вид аппроксимации;

5 Нажать кнопку ОК.