Рекомендации по выполнению
MS EXCEL
Вариант 1
№1 Построить диаграмму рассеяния. Построить линейное уравнение парной регрессии по .
Для построения диаграммы рассеяния необходимо построить таблицу с данными и построить точечную диаграмму (см. рис.3).
|
|
Рисунок 3 – Таблица данных и диаграмма рассеяния
Произведём визуальный анализ диаграммы рассеяния: форма – линейная, направление – обратное (наблюдается убывание).
Построение линейного уравнения парной регрессии по .
Модели временного ряда включают несколько составляющих:
Тенденция (тренд), Сезонность, Циклы, Нерегулярные изменения, Случайная составляющая.
Рисунок 4 - Составляющие временного ряда
Временной ряд можно считать состоящим из двух частей: детерминированная и случайная.
Таблица 2 - Временной ряд
Детерминированная составляющая |
Случайная
составляющая e(t)
( |
|||||
Тренд f(t) |
Циклическая компонента u(t) |
Сезонная компонента s(t) |
«Белый шум» |
Авторегрессия |
Скользящее среднее |
Смешанная |
)
Необходимо
найти значения параметров линейной
регрессии:
и
.
Уравнение парной (простой) линейной регрессии выглядит следующим образом:
|
(1) |
где ε - отклонение от теоретически предполагаемого значения;
и - неизвестные параметры (коэффициенты регрессии).
Переменную называют фактором (входной переменной, независимой переменной), переменную y называют откликом (реакцией, зависимой переменной).
При помощи этого уравнения переменная y выражается через константу и угол наклона прямой (или угловой коэффициент) , умноженный на значение переменной . Константу также называют свободным членом, а угловой коэффициент - коэффициентом регрессии. Параметры уравнения могут быть определены с помощью метода наименьших квадратов (МНК).
Расчетные формулы для нахождения коэффициентов уравнения регрессии:
|
(2) |
|
(3) |
где n – количество пар исходных данных. |
|
,1Способ (с помощью добавления тренда):
|
|
|
|
Рисунок 5 - Добавление линии тренда
|
Рис. 6 – Формат линии тренда
Результат:
Значение
коэффициента детерминации
Рисунок 7 – Построенная линия тренда
На диаграмме рассеяния показаны не только точки-объекты, но и теоретическая линия регрессии (прямая).
Замечание. Результат-график у каждого варианта свой, отличный от других.
2Способ (с помощью функций):
с помощью функции ЛИНЕЙН(массив У; массив Х; 1; 1),
с помощью функций НАКЛОН и ОТРЕЗОК.
Функция ЛИНЕЙН(массив У; массив Х; 1; 1)
Выделить диапазон ячеек (5 строчек и 2 столбца, например А23:В27).
С помощью мастера функций
вставить функцию ЛИНЕЙН() из категории
Статистические.В окне Аргументы функции заполнить поля: Известные_значения_у, Известные_значения_х, Конст, Статистика (см. рисунок 9).
Нажать одновременно <Ctrl>+<Shift>+<Enter>.
Появится таблица в диапазоне ячеек А23:В27.
Таблица 3 – Результат вычисления ЛИНЕЙН()
Значение коэффициента a |
Значение коэффициента b |
Среднеквадратическое отклонение a |
Среднеквадратическое отклонение b |
Коэффициент детерминации R2 |
Среднеквадратическое отклонение у |
F-статистика |
Число степеней свободы |
Регрессионная сумма квадратов |
Остаточная сумма квадратов |
Рисунок 8 – Поиск функции ЛИНЕЙН()
Рисунок 9 – Аргументы функции ЛИНЕЙН()
Рисунок 10 – Результат вычисления ЛИНЕЙН()
Получили
уравнение вида
.
функции НАКЛОН (для нахождения а) и ОТРЕЗОК (для b)
|
|
Получено
уравнение регрессии:
.