Функции категории «Статистические» для расчета линейных моделей

ЛАБОРАТОРНАЯ РАБОТА № 2

ТАБЛИЧНЫЙ ПРОЦЕССОР MS EXCEL.

СТАТИСТИЧЕСКИЙ АНАЛИЗ ДАННЫХ:

ПОСТРОЕНИЕ И АНАЛИЗ

ОДНОФАКТОРНЫХ РЕГРЕССИОННЫХ МОДЕЛЕЙ

Цель работы: приобрести практические навыки расчета и анализа однофакторных регрессионных моделей линейной и экспоненциальной структуры с помощью встроенных функций ТП MS Excel категории «статистические».

ТЕОРЕТИЧЕСКАЯ ЧАСТЬ

Встроенные функции категории «Статистические» ТП Excel обеспечивают расчет и оценку экономико-математических моделей линейной и экспоненциальной структуры. Некоторые другие виды однофакторных экономико-математических моделей (полиномиальная, логарифмическая, степенная) могут быть получены графическим способом.

В практической работе наибольшее распространение получили модели линейной зависимости.

Линейная однофакторная модель – это уравнение прямой на плоскости, которая может быть описана уравнением

Y = m∙x+b, (2.1)

где m – коэффициент уравнения регрессии, представляющий собой тангенс угла наклона прямой Y = f(x) к оси ОХ;

b – свободный член, равный значению точки пересечения прямой Y=f(x) с осью ОY.

Экспоненциальная однофакторная модель имеет вид

Y = b*m^x. (2.2)

Функции категории «Статистические» для расчета линейных моделей

Параметры линейной однофакторной модели вида

Y = m∙x+b

проще всего определить с помощью функций НАКЛОН и ОТРЕЗОК.

§ НАКЛОН(известные_значения_Y; известные_значения_X) – определяет коэффициент наклона линии линейной регрессии m к оси ОХ.

§ ОТРЕЗОК(известные_значения_Y; известные_значения_X) – вычисляет точку пересечения b линии линейной регрессии с осью ОY.

известные_начения_Y – это массив зависимого множества наблюдаемой величины.

известные_значения_X – это массив независимого множества наблюдаемой величины. Если аргумент известные_значения_Xопущен, то предполагают, что это массив значений 1, 2, 3, …, n той же длины, что и аргумент известные значения Y.

Рассчитать коэффициенты линейного уравнения регрессии с выводом дополнительной статистики по регрессии возможно с помощью функции ЛИНЕЙН.

§ ЛИНЕЙН(известные_значения_Y,известные_значения_X, константа; статистика) – рассчитывает статистику ряда с применением метода наименьших квадратов для вычисления уравнения прямой линии, которое наилучшим образом описывает исходные данные и может использоваться как для расчета однофакторных, так и многофакторных моделей, что определяется размером массива независимых переменных х_i.

известные_значения_Y – это известные значения Y, для которых параметры X по уравнению определены.

известные_значения_X – это известные значения независимой переменной X. Массив известные_значения_Xможет быть многомерным в отличие от массива известные­_значения_Y, который является одномерным. Массив X может быть опущен, тогда значения X устанавливаются автоматически как предварительный ряд чисел, начиная с 1. Но обязательно должно быть соответствие между размерностями массива X и Y, если массив X задан.

Константа – это логическое значение, которое указывает функции, каким образом должен быть определен коэффициент b. Если логическое значение ИСТИНА или оно опущено, то b определяется в обычном порядке. Если константа равна ЛОЖЬ, то коэффициенты подбираются таким образом, чтобы выполнялось равенство y =m∙x (b=0).

Статистика – логическое значение, которое может принимать значение ИСТИНА или ЛОЖЬ. Если статистика имеет значение ИСТИНА, то будет представлена дополнительная регрессионная статистика по регрессии, если ЛОЖЬ или опущено, то выходным массивом будет основная статистика, т.е. коэффициенты m₁, m₂, …, m_n и b.

В качестве результата функция ЛИНЕЙН возвращает массив коэффициентов уравнения регрессии и дополнительную статистику по регрессии, как показано в таблице 2.1.

Таблица 2.1. Результаты, возвращаемые функцией ЛИНЕЙН

Здесь m₁, m₂ … m_n, b – коэффициенты уравнения регрессии.

Все остальное – дополнительная статистика по регрессии:

Se₁, Se₂, Se_n – стандартные ошибки для коэффициентов m₁,m₂ … m_n;

Se_b– стандартная ошибка для свободного члена b;

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

Se_y– стандартная ошибка для Y;

F – критерий Фишера используется для определения того, является ли наблюдаемая взаимосвязь между зависимой и независимой переменными случайной или нет;

Df – степень свободы системы (уравнение надежности). Степени свободы полезны для нахождения F-критических значений в статистической таблице. Для определения уровня надежности модели нужно сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН;

SSreg- регрессионная сумма квадратов;

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

Осуществить прогноз фактора-признака Y на основании линейной зависимости по наблюдаемым X - и Y -значениям можно с помощью функций ПРЕДСКАЗ и ТЕНДЕНЦИЯ.

§ ПРЕДСКАЗ(X; известные_значения_Y; известные_значения_X) – эта функцияна основании линейного тренда вычисляет или предсказывает будущее значение зависимой переменной Y, соответствующее заданному X -значению, по существующим X - и Y -значениям. То есть определяет теоретическое (прогнозируемое) значение Y для фиксированного значения X, не рассчитывая уравнения регрессии;

X – это точка данных, для которой предсказывается значение;

известные_значения_Y – это зависимый массив или интервал данных;

известные_значения_X – это независимый массив или интервал данных.