 |
2.8 Реализация решенных задач на компьютере
|
|
|
|
2. 8 Реализация решенных задач на компьютере
Определение линейной функции регрессии выполним с помощью ППП Excel.
2. 8. 1 Реализация процедуры «ЛИНЕЙН»
Статистическая функция ЛИНЕЙН позволяет вычислить параметры линейной регрессии:
ŷ x = a + b · x.
Вся регрессионная статистика будет выводиться по схеме:
| Значение коэффициента b | значение коэффициента а | |||
| Среднеквадратическое отклонение b |
| среднеквадратическое | отклонение а | |
| коэффициент детерминации | среднеквадратическое | отклонение y | ||
| F-статистика |
| число степеней свободы | ||
| регрессионная сумма квадратов | остаточная сумма квадратов | |||
Алгоритм вычисления регрессионной статистики включает следующие этапы:
1) подготовку исходных данных;
2) выделение области пустых ячеек 5 ´ 2 для вывода результатов регрессионной статистики;
3) активизацию Мастера функций одним из способов:
а) в главном меню выбрать ВСТАВКА/ФУНКЦИЯ;
в) на панели СТАНДАРТНАЯ щелкнуть по кнопке ВСТАВКА ФУНКЦИИ;
4) в окне КАТЕГОРИЯ выбрать СТАТИСТИЧЕСКИЕ, в окне ФУНКЦИЯ – ЛИНЕЙН; затем щелкнуть по кнопке ОК;
5) заполнить аргументы функции;
6) в левой верхней ячейке выделенной области появится первый элемент итоговой таблицы. Для раскрытия всей таблицы необходимо нажать на клавишу F2, затем нажать комбинацию клавишей CTRL + SHIFT + ENTER.
Ниже приводятся результаты регрессионной линейной математической модели цен жилья на первичном рынке в зависимости от себестоимости строительства в 12 областях (республиках) по статистическим данным Рф.
| Значение коэффициента b 1, 339270 | Значение коэффициента a 4, 597762 |
| Среднеквадратическое отклонение b 0, 106086 | Среднеквадратическое отклонение а 2, 010093 |
| Коэффициент детерминации 0, 940960 | Среднеквадратическое отклонение y 1, 279915 |
| F-статистика 159, 376337 | Число степеней свободы |
| Регрессионная сумма квадратов 261, 087353 | Остаточная сумма квадратов 16, 381814 |
2. 8. 2 Реализация процедуры «Анализ данных»
|
|
|
Для активизации надстройки «Пакет анализа» необходимо открыть меню «Сервис» и щелкнуть по строке «Надстройки…». В открывшемся меню следует отметить строку «Пакет анализа» и подтвердить выбор кнопкой «ОК».
Использование пакета анализа осуществляется выбором строки «Анализ данных…» в строке «Сервис» после выделения любой ячейки рабочего листа. Построение парной линейной регрессии выполняется с помощью инструмента «Регрессия» пакета анализа.
Инструмент анализа «Регрессия» пакета анализа данных Excel позволяет по введенным статистическим данным получить значения выборочных коэффициентов корреляции и детерминации, стандартного отклонения; разложения общей суммы квадратов на объясненную и остаточную, расчетное значение 
-статистики и уровень значимости на котором расчетная -статистика равняется соответствующей табличной величине; значения регрессионных параметров, их стандартные ошибки, и 
-статистики; таблицу теоретических значений и величины их отклонений от опытных данных; график статистических данных с линией регрессии и график остатков; и другие статистические оценки.
Вызов опции «Регрессия» осуществляется через надстройку «Анализ данных…» меню «Сервис».
Вызов надстройки «Анализ данных…» приведет к появлению списка инструментов анализа. В этом списке необходимо выбрать «Регрессия» и подтвердить выбор нажатием кнопки «ОК».
Интерфейс инструмента анализа «Регрессия» представляет собой диалоговое окно, в верхней части которого следует ввести статистические данные результирующей переменной в поле «Входной интервал Y» и данные фактора в поле «Входной интервал X». При необходимости построения уравнения регрессии вида 
нужно задать параметр «Константа-ноль». Параметр «Уровень надежности» в процентах определяет величину доверительной вероятности 
. В качестве выходного интервала удобно задать адрес ячейки непосредственно рядом с таблицей исходных данных. Рекомендует активизировать параметры «Остатки» (таблица теоретических значений результирующего показателя и соответствующие значения остатков), «График остатков» (график отклонений теоретических значений результирующего показателя от его опытных значений) и «График подбора» (график статистических данных с соответствующими теоретическими величинами, вычисленными по уравнению регрессии).
|
|
|
После подтверждения настроек нажатием кнопки «ОК» итоги регрессионного анализа высветятся в заданной области.
Ниже приведены пояснения к итогам расчетов инструмента анализа «Регрессия».
1. Регрессионная статистика:
Множественный R – выборочный коэффициент корреляции;
R-квадрат – выборочный коэффициент детерминации;
Нормированный R-квадрат – выборочный скорректированный на объем выборки коэффициент детерминации;
Стандартная ошибка – стандартная ошибка результирующей переменной;
Наблюдения – объем выборки.
2. Дисперсионный анализ:
Регрессия – строка таблицы, соответствующая объясненной сумме квадратов отклонений;
Остаток – строка таблицы, соответствующая остаточной сумме квадратов отклонений;
Итого – строка таблицы, соответствующая общей сумме квадратов отклонений;
df – столбец значений числа степеней свободы;
SS – столбец значений сумм квадратов отклонений;
MS – столбец значений сумм квадратов отклонений отнесенных к числу степеней свободы;
F – расчетное значение -статистики;
Значимость F – значение уровня статистической значимости, при котором табличное значение -статистики с числом степеней свободы 1 и 
будет равно расчетной -статистике (если это значение меньше заданного уровня значимости, то есть основание отвергнуть гипотезу о статистической ненадежности уравнения регрессии).
|
|
|
Y-пересечение – строка таблицы соответствующая свободному регрессионному коэффициенту;
Переменная X1 – строка таблицы соответствующая регрессионному коэффициенту при переменной 
;
Коэффициенты – столбец значений регрессионных параметров;
Стандартная ошибка – столбец значений выборочных среднеквадратичных отклонений регрессионных параметров;
t-статистика – столбец расчетных значений -статистик регрессионных параметров;
3. Вывод остатков:
Наблюдения – номера наблюдений по порядку;
Предсказанное Y – теоретические значения результирующего
показателя, соответствующие опытным величинам;
Остатки – отклонения (разность) теоретических значений результирующего показателя и соответствующих опытных значений.
На рис. Представлен График остатков, а на рис. Представлен График подбора.
|
|
|
