Лабораторная работа 1. Статистический анализ данных в MS EXCEL.
Построение и анализ однофакторных регрессионных моделей.
Цель работы: приобрести практические навыки расчета и анализа однофакторных регрессионных моделей линейной и экспоненциальной структуры с помощью функций MS Excel категории «Статистические».
ТЕОРЕТИЧЕСКАЯ ЧАСТЬ
Условия применимости регрессионного анализа:
- Линейность связи переменных: перед тем как приступить к расчетам, надо нанести данные на график;
- Нормальность распределения;
- Изучаемые признаки должны быть количественными.
Линейная однофакторная модель – это уравнение прямой на плоскости, которая может быть описана уравнением
Y=m*X + b
Экспоненциальная однофакторная модель имеет вид
Y=b * mX
Рассчитать коэффициенты линейного уравнения регрессии с выводом дополнительной статистики по регрессии можно с помощью функции ЛИНЕЙН
- ЛИНЕЙН(известные_значения_Y,известные_значения_X,константа,статист.) – рассчитывает статистику ряда с применением метода наименьших квадратов для вычисления уравнения прямой линии, которое наилучшим образом описывает исходные данные и может использоваться как для расчета однофакторных, так и многофакторных моделей, что определяется размером массива независимых переменных Х.
- известные_значения_Y - это известные значения Y, для которых известны значения Х.
-известные_значения_X - это известные значения независимой переменной Х. Этот массив может быть многомерным в отличие от массива известные_значения_Y, который является одномерным.
-константа - это логическое значение, которое указывает функции, каким образом должен быть определен коэффициент b. Если логическое значение ИСТИНА или оно опущено, то b определяется обычным образом. Если константа равно ЛОЖЬ, то коэффициенты подбираются таким образом, чтобы выполнялось равенство Y=m*X (b=0).
- статистика – логическое значение, ИСТИНА или ЛОЖЬ. Если статистика имеет значение ИСТИНА, то будет представлена дополнительная регрессионная статистика по регрессии, если ЛОЖЬ или опущено, то выходным массивом будет основная статистика, т.е. коэффициенты m1, m2, …,mn и b.
В качестве результата функция ЛИНЕЙН возвращает массив коэффициентов уравнения регрессии и дополнительную статистику по регрессии в порядке, как показано в табл.1.
Табл.1. Результаты, возвращаемые функцией ЛИНЕЙН
| mn | mn-1 | ... | m2 | m1 | b |
| sen | sen-1 | se2 | se1 | seb | |
| R2 | Sey | ||||
| F | df | ||||
| SSreg | SSresid | ||||
В Табл.2 приведено подробное описание величин из Табл.1
| Величина | Наименование | Описание |
| m1 m2,…, mn, b | коэффициенты уравнения регрессии | y=m1x+b y=m1x1+m2x2+…+b |
| se1, se2, sen | стандартные ошибки для коэффициентов m1, m2,…, mn; | |
| seb | стандартная ошибка для свободного члена b; | Показатели, которые используются для получения доверительного интервала прогноза с определенной доверительной вероятностью |
| Sey | стандартная ошибка для Y | |
| R2 | коэффициент детерминированности | показывает, как близко теоретическое уравнение описывает исходные данные. Если коэффициент равен 1, имеет место полная корреляция с моделью, т.е. нет различия между фактическим и рассчитанным значением у. В противном случае уравнение регрессии неудачно. |
| F | Расчетное значение F-критерия Фишера | используется для определения того, является ли наблюдаемая взаимосвязь между зависимой и независимой переменными случайной или нет. Полученное значение сравнивается с табличным при соответствующем числе степеней свободы и некотором уровне значимости (обычно 0,05). Связь считается статистически значимой, если расчетное значение критерия больше чем табличное. |
| df | степени свободы системы (уровень надежности). | Степени свободы полезны для нахождения F-критических значений в статистической таблице. Для определения уровня надежности модели нужно сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН. |
| SSreg | регрессионная сумма квадратов | Сумма квадратов отклонений теоретических уровней результативного признака от среднего значения результативного признака |
| SSresid | остаточная сумма квадратов | Сумма квадратов отклонений теоретических уровней результативного признака от его фактических значений. |
Осуществить прогноз фактора-признака Y на основании линейной зависимости по наблюдаемым X- и Y-значениям можно с помощью функций ПРЕДСКАЗ и ТЕНДЕНЦИЯ.
- ПРЕДСКАЗ(Х; известные_значения_Y,известные_значения_X) – функция на основании линейного тренда вычисляет или предсказывает будущее значение зависимой переменной Y, соответствующее заданному X-значению, по заданным значениям X, Y. Т.е. определяет теоретическое (прогнозируемое) значение Y для фиксированного значения X;
- Х – это точка данных, для которой предсказывается значение;
- известные_значения_Y – это зависимый массив или интервал данных;
- известные_значения_X - это независимый массив или интервал данных.
- ТЕНДЕНЦИЯ(известные_значения_Y;известные_значения_X;новые_значения_X; константа) – функция на основании линейного тренда вычисляет или предсказывает будущее значение зависимой переменной Y, соответствующее заданному массиву X-значений по имеющимся X,Y-значениям. В отличие от функции ПРЕДСКАЗ, функция ТЕНДЕНЦИЯ позволяет рассчитать теоретические значения Y для массива новых значений X.
- новые_значения_X – массив (интервал данных), который должен содержать столбец (или строку) для каждой независимой переменной, как и известные_значения_X;
- константа–логическое значение, которое указывает вид константы b (0 или другое значение).
Функции для расчета экспоненциальных моделей
Рассчитать коэффициенты уравнения регрессии экспоненциальной структуры с выводом дополнительной статистики по регрессии возможно с помощью функции ЛГРФПРИБЛ:
- ЛГРФПРИБЛ(известные_значен_Y,известные_значен_X,константа,статист.)- в регрессионном анализе вычисляет экспоненциальную кривую, аппроксимирующую данные, и возвращает массив значений, описывающий эту кривую.
- константа - это логическое значение, которое указывает, требуется ли, чтобы коэффициент b был равен 1. Если логическое значение ИСТИНА или оно опущено, то b определяется обычным образом. Если константа имеет значение ЛОЖЬ, то коэффициент b полагается равным 1 и значения m подбираются таким образом, чтобы выполнялось равенство Y=mX (b=1).
В случае парной регрессии
Y=b*mX или Y=mX
В случае множественной регрессии
Y=b*m1X1* m2X2 … *mnXn или Y=m1X1*m2X2*…*mnXn
Осуществить прогноз фактора-признака Y на основании экспоненциальной зависимости по наблюдаемым X- и Y-значениям можно с помощью функции РОСТ.
- РОСТ(известные_значения_Y,известные_значения_X; новые_значения_X;константа) – функция используется для расчета прогнозируемого экспоненциального роста на основании имеющихся данных.