Реализация процедуры ЛИНЕЙН

Статистическая функция ЛИНЕЙН позволяет вычислить параметры линейной регрессии:

 

 

Вся регрессионная статистика будет выводиться по схеме:

значение коэффициента b значение коэффициента a

среднеквадратическое отклонение b среднеквадратическое отклонение a

коэффициент детерминации среднеквадратическое отклонение y

F -статистика число степеней свободы

регрессионная сумма квадратов остаточная сумма квадратов

 

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

1) Подготовку исходных данных;

2) Выделение области пустых ячеек для вывода результатов регрессионной статистики;

3) Активизацию Мастера функций одним из способов:

a) В главном меню выбрать ВСТАВКА/ФУНКЦИЯ;

b) На панели СТАНДАРТНАЯ щелкнуть по кнопке ВСТАВКА ФУНКЦИИ;

4) Выбор в окне КАТЕГОРИЯ – СТАТИСТИЧЕСКИЕ, в окне ФУНКЦИЯ – ЛИНЕЙН; затем щелкнуть по кнопке ОК;

5) Заполнение аргументов функции;

6) В левой верхней ячейке выделенной области появится первый элемент итоговой таблицы. Для раскрытия всей таблицы необходимо нажать на клавишу F2, затем нажать комбинацию клавишей CTRL+SHIFT+ENTER.

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

Значение коэффициента b Значение коэффициента a

0,13295 45,69645

Среднеквадратическое отклонение b Среднеквадратическое отклонение a

0,033832 3,756182

Коэффициент детерминации Среднеквадратическое отклонение y

0,606946 0,870117

F -статистика Число степеней свободы

15,44181 10

Регрессионная сумма квадратов Остаточная сумма квадратов

11,69105 7,571038

8.2 Реализация процедуры «Анализ данных»

Для активации надстройки «Пакет анализа» необходимо в меню «Office» открыть «Параметры Excel» и выбрать строку «Надстройки». В списке «Управление» выбрать «Надстройки Excel» и нажать «Перейти». В открывшемся меню следует отметить строку «Пакет анализа» и подтвердить выбор кнопкой «ОК».

Использование пакета анализа осуществляется выбором строки «Анализ данных» во вкладке «Данные» меню Excel. Построение парной линейной регрессии выполняется с помощью инструмента «Регрессия» пакета анализа.

Инструмент анализа «Регрессия» пакета анализа данных Excel позволяет по введенным статистическим данным получить значения выборочных коэффициентов корреляции и детерминации, стандартного отклонения; разложения общей суммы квадратов на объясненную и остаточную; расчетное значение F -статистики и уровень значимости, на котором расчетная F -статистика равняется соответствующей табличной величине; значения регрессионных параметров, их стандартные ошибки и t -статистики; таблицу теоретических значений и величины их отклонений от опытных данных; график статистических данных с линией регрессии, график остатков и другие статистические оценки.

Вызов опции «Регрессия» осуществляется через надстройку «Анализ данных» меню «Данные».

Вызов надстройки «Анализ данных» приведет к появлению списка инструментов анализа. В этом списке необходимо выбрать «Регрессия» и подтвердить выбор нажатием кнопки «ОК».

Интерфейс инструмента анализа «Регрессия» представляет собой диалоговое окно, в верхней части которого следует ввести статистические данные результирующей переменной в поле «Входной интервал Y» и данные фактора в поле «Входной интервал X». При необходимости построения уравнения регрессии вида нужно задать параметр «Константа-ноль». Параметр «Уровень надежности» в процентах определяет величину доверительной вероятности . В качестве выходного интервала удобно задать адрес ячейки непосредственно рядом с таблицей исходных данных. Рекомендуется активизировать параметры «Остатки» (таблица теоретических значений результирующего показателя и соответствующие значения остатков), «График остатков» (график отклонений теоретических значений результирующего показателя от его опытных значений) и «График подбора» (график статистических данных с соответствующими теоретическими величинами, вычисленными по уравнению регрессии).

После подтверждения настроек нажатием кнопки «ОК» итоги регрессионного анализа высветятся в заданной области.

Ниже приведены пояснения к итогам расчетов инструмента анализа «Регрессия».

1. Регрессионная статистика:

множественный R – выборочный коэффициент корреляции;

R-квадрат – выборочный коэффициент детерминации;

нормированный R-квадрат – выборочный скорректированный на объем выборки коэффициент детерминации;

Стандартная ошибка – стандартная ошибка результирующей переменной;

Наблюдения – объем выборки.

2. Дисперсионный анализ:

регрессия – строка таблицы, соответствующая объясненной сумме квадратов отклонений;

остаток – строка таблицы, соответствующая остаточной сумме квадратов отклонений;

итого – строка таблицы, соответствующая общей сумме квадратов отклонений;

df – столбец значений числа степеней свободы;

SS – столбец значений сумм квадратов отклонений;

MS – столбец значений сумм квадратов отклонений отнесенных к числу степеней свободы;

F – расчетное значение F -статистики;

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

Y-пересечение – строка таблицы соответствующая свободному регрессионному коэффициенту;

переменная XI – строка таблицы соответствующая регрессионному коэффициенту при переменной x;

коэффициенты – столбец значений регрессионных параметров;

стандартная ошибка – столбец значений выборочных среднеквадратичных отклонений регрессионных параметров;

t-статистика – столбец расчетных значений t-статистик регрессионных параметров;

3. Вывод остатков:

наблюдения – номера наблюдений по порядку;

предсказанное Y – теоретические значения результирующего показателя, соответствующие опытным величинам;

остатки – отклонения (разность) теоретических значений результирующего показателя и соответствующих опытных значений.

ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R	0,779068
R-квадрат	0,606946
Нормированный R-квадрат	0,567641
Стандартная ошибка	0,870117
Наблюдения
Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия		11,69105	11,69105	15,44181	0,002822027
Остаток		7,571038	0,757104
Итого		19,26209
	Коэффиц- иенты	Стандартная ошибка	t-статис- тика	P-Зна- чение	Нижние 95%	Верхние 95%	Нижние 95,0%	Верхние 95,0%
Y-пересечение	45,69645	3,756182	12,16566	2,56773E-07	37,32715	-54,0657	37,32715	54,0657
Переменная X 1	-0,13295	0,033832	-3,92961	0,002822	-0,208331	-0,05757	-0,20833	-0,05756
	ВЫВОД ОСТАТКА
	Наблюдение	Предсказанное Y	Остатки	Стандартные остатки
		31,09879066	-0,73879066	-0,890512529
		29,84908241	-0,89908241	-1,083722619
		29,3039969	0,0260031	0,031343236
		30,03520917	-0,67520917	-0,813873615
		31,17855927	1,27144073	1,532550365
		33,26583795	-0,44583795	-0,537397532
		32,04271923	0,14728077	0,177527106
		30,60688422	1,68311578	2,028769132
		30,80630575	0,11369425	0,137043088
		30,96584297	0,56415703	0,680015227
		31,25832788	-0,19832788	-0,239057524
		31,21844358	-0,84844358	-1,022684336