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

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

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

значение коэффициента 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 – теоретические значения результирующего показателя, соответствующие опытным величинам;

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