Регрессионный анализ с помощью функции ЛИНЕЙН

Корреляционно-регрессионный анализ финансово-экономических показателей.

Задание

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

Простая Линейная регрессия.

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

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

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

1.1. Добавление линейного тренда.

Точки на рисунке расположены примерно на одной прямой, поэтому можно построить линейный тренд.

1. Щелкнуть ПКМ по ряду данных и выбрать Добавить линию тренда в контекстном меню
2. На вкладке Тип линии тренда щелкнуть по пиктограмме Линейная.
3. На вкладке Параметры линии тренда выберите Автоматическое название аппроксимирующей (сглаживающей) прямой. Включите опции Показывать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации (R²).

Чем ближе R² к 1 тем точнее аппроксимация.

Изучив уравнение аппроксимации, переписанное в виде

Предсказанная цена = 37,584 + 0,4523*Площадь

можно ответить на вопрос о среднем соотношении изучаемых переменных.

Регрессионный анализ с помощью функции ЛИНЕЙН

Применение функции ЛИНЕЙН (массив ; массив ; A; B), которая вычисляет параметры линейной регрессии Y по факторам X₁, X₂, …, X_k.

Первый аргумент – диапазон, содержащий значения результативного признака; второй аргумент – диапазон, содержащий значения факторных признаков; A – логическое значение, которое указывает на наличие (1) или отсутствие (0) свободного члена в уравнении; B – логическое значение, которое указывает, выводить ли дополнительную статистику по регрессионному анализу (1) или нет (0).

В случае изучения множественной регрессии выделяется диапазон размером 5 на 1+k, где k – число объясняющих переменных.

Вызовем функцию ЛИНЕЙН. Введём аргументы и нажмём на комбинацию клавиш <CTRL>+<SHIFT>+<ENTER>. Регрессионная статистика будет выводиться в следующем порядке:

		…			b*
		…			Sb
R2	S
F-статистика	n-1-k
Sr2	Se2

Величину называют стандартной ошибкой оценки Y. Стандартные отклонения оценок коэффициентов регрессии связаны с S следующими формулами:

.

При проверке гипотезы о значимости коэффициента регрессии рассматривается величина , называемая t- статистикой коэффициента . Если , то коэффициент регрессии значим. Здесь m – число параметров при переменных x (в линейной регрессии совпадает с числом самих объясняющих переменных), а n − число наблюдений. (В Excel t-критическое можно найти с помощью функции СТЬЮДРАСПОБР).

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

где – полная сумма квадратов,

– сумма квадратов, объясняемая регрессией Y по X,

– остаточная сумма квадратов.

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

F-статистика для проверки качества оценивания регрессии рассчитывается по формуле

, где - коэффициент детерминации для уравнения множественной регрессии, и при заданном уровне значимости гипотезы α сравнивается с критической точкой распределения Фишера
F_кр(ά, m, n-m-1). При F>F_кр качество оценивания признаётся достаточным. (В Excel F-критическое можно найти с помощью функции FРАСПОБР).