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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ВЫВОД ИТОГОВ
Регрессионная статистика
Множественный R	0,858524053
R-квадрат	0,73706355
Нормированный R-квадрат	0,710769905
Стандартная ошибка	4,755781896
Наблюдения
Дисперсионный анализ
	df	SS	MS	F	Значимость F
Регрессия		634,0127599	634,0127599	28,032004	0,00035
Остаток		226,1746144	22,61746144
Итого		860,1873743
	Коэффициенты	Стандартная ошибка	t-статистика	P-Значение	Нижние 95%	Верхние 95%
Y-пересечение	-38,80187863	15,69510578	-2,472227915	0,0329788	-73,7728	-3,83100381
Переменная X 1	2,643182454	0,499229307	5,294525812	0,0003503	1,53083	3,75553467

 

 

ВЫВОД ОСТАТКА
Наблюдение	Предсказанное Y	Остатки	Стандартные остатки
	41,28654973	1,683450269	0,371257046
	46,90331245	-3,495312446	-0,770833209
	43,61783666	-7,875836656	-1,736885196
	47,48745577	-3,681455769	-0,811884033
	52,14738644	-0,673386436	-0,148504214
	38,10944442	0,934555579	0,206100738
	38,61429227	0,68570773	0,151221471
	53,33153218	5,321467825	1,173561501
	53,99497097	7,199029029	1,587626495
	38,65658319	-3,266583189	-0,720390764
	27,94905107	5,937948933	1,309516188
	45,63458487	-2,769584868	-0,610786024