ЛАБОРАТОРНАЯ РАБОТА4

ЛАБОРАТОРНАЯ РАБОТА №4 Методы вычисления параметров парной линейной регресии

Линейная парная регрессия

Парной линейной регрессией называется зависимость

y;^ = b₀ + b₁x

выборочного условного математического ожидания от переменной х. Термин «парная» означает зависимость двух переменных Y, X. Термин «линейная» означает их линейную зависимость.

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

Модель наблюдения

y_i = b₀ + b₁x + _i,

b₀ – оценка свободного члена ₀,

b₁ – оценка углового коэффициента ₁.

MS Excel обеспечивает эффективную поддержку для проведения регрессионного анализа.

1. Постановка задачи

В некоторой фирме имеются статистические данные (x_i, y_i).

x_i - независимая(объясняющая) переменная - расходы на рекламу продукции фирмы;

y_i - зависимая(объясняемая) переменная - объём продаж, соответствующий расходам x_i.

Следует построить линейную регрессионную модель, объясняющую, как повышение бюджета на рекламу влияет на объём продаж.

Учебные цели: приобрести навыки построения и анализа уравнения регрессии в ТП EXCEL.

Цель работы - освоение инструмента парной линейной регрессии в MS Excel.:

1. Исходные данные. Исходные наблюдения приведены в файле Варианты4.xls.

2. Задание. Необходимо построить линейную регрессионную модель y=a+bx.

3. Выполнение.

1-ый способ.

4.1. Диалоговое окно «Поиск решения»

Пример: На рис. 1 представлен образец выполнения работы.

Для шести наблюдений известны значения двух наблюдаемых величин х и у; х – отчетный месяц, у – объем продаж оборудования.

Отведем под переменные b и a ячейки D3 и E3 соответственно, а в ячейку F3 введем минимизируемую функцию: =суммквразн(B2:B7; E3+D3*A2:A7), которая вычисляет сумму квадратов разностей для элементов указанных массивов.

Выберем команду Сервис, Поиск решения и заполним диалоговое окно Поиск решения. Отметим, что на переменные b и a ограничения не налагаются. В результате вычислений получены значения переменных:

b=2 и a=7,3333. Можно рассчитать теоретические значения у для каждого наблюдения.

Рис. 1. Пример построения линейной модели

2-ой способ.

4.2. Диалоговое окно «Линия тренда»

Установка флажка на опции «показывать уравнение на диаграмме» приводит к появлению уравнения регрессии на диаграмме.

Этот метод плох тем, что для дальнейших вычислений, в которых необходимы параметры регрессии, приходится вводить их значения «вручную».

Последовательность выполнения данного способа состоит в следующем.

4.2.1. Ввести исходные данные, как в указанном примере.

4.2.2. Выделить диапазон этих данных A3:B21.

4.2.3. Выбрать команду Вставка/Диаграмма.

На первом шаге работы мастера выбрать тип диаграммы Точечная, Далее.

На втором шаге проверить, что в текстовом поле Диапазон введен диапазон A3:B21, Далее.

На третьем шаге установить названия осей, Далее.

На четвёртом шаге выбрать место размещения диаграммы и щёлкнуть по кнопке Готово.

4.2.4. Выделить диаграмму. Выбрать команду Диаграмма/ Добавить линию тренда.

4.2.5. Выбрать тип аппроксимации Линейная.

4.2.6. Щёлкнуть на вкладке Параметры и установить флажки как на следующем рисунке.

3-ий способ.

Расчёт по формулам нормальных уравнений

Формулы для расчёта параметров парной линейной регрессии таковы

b₀= – свободный член регрессии;

b₁ = – угловой коэффициент регрессии

где суммирование ведётся по всем выборочным данным.

Расчёт коэффициентов в Excel производится с помощью следующей таблицы

№	Х	Y	Х^2	XY
1	1,1	1,6	1,21	1,76
2	1,2	1,4	1,44	1,68
3	1,3	1,3	1,69	1,69
4	1,4	1,2	1,96	1,68
5	1,5	1,1	2,25	1,65
Итого	6,5	6,6	8,55	8,46

В столбцы ”X”, “Y” данные вводятся непосредственно. В столбцы “X2”, “XY” вводятся формулы с соответствующими ссылками на ячейки, содержащие значения X, Y. Затем эти формулы «растягиваются» по столбцу. В последней строке производится суммирование. Далее в некоторые две ячейки вводятся приведённые формулы со ссылками на соответствующие ячейки последней строки таблицы.

4-ый способ.

Использование функции «линейн»

Формат функции линейн:

линейн(изв_знач_у;изв_знач_х;константа;стат).

Смысл аргументов функции

изв_знач_у – диапазон значений у;

изв_знач_х – диапазон значений х;

константа – устанавливается на 0, если заранее известно, что свободный член равен 0 и на 1 в противном случае;

стат – устанавливается на 0, если не нужен вывод дополнительных сведений регрессионного анализа и на 1 в противном случае.

Использование функции линейн:

1. Выделить область пустых ячеек 5´2 (5 строк, 2 столбца) для вывода результатов регрессионной статистики и 1´2 для вывода только коэффициентов b₀, b₁.

2. Ввести функцию линейн вручную или через мастера.

3. После корректного ввода функции в левой верхней ячейке выделенной таблицы появится первый итоговый элемент таблицы. Чтобы раскрыть всю таблицу, следует а) нажать клавишу F2, б) затем комбинацию клавиш <CTRL>+<SHIFT>+<ENTER>. Далее появляется регрессионная статистика в следующем порядке

Значение коэффициента b1	Значение коэффициента b0
Среднеквадратическое отклонение b1	Среднеквадратическое отклонение b0
Коэффициент детерминации R2	Среднеквадратическое отклонение у
F-статистика	Число степеней свободы
Регрессионная сумма квадратов	Остаточная сумма квадратов

Дописать вычисление параметров с помощью функций Excel

Выполнение работы

По каждому варианту даны три массива данных х-у. Обработку каждого массива выполнять на отдельном листе.

Обработка массива заключается в следующем.

1. Определить параметры линейной регрессии 4 способами.

2. Сформировать массив невязок _i = b₁x_i + b₀ – y_i, сделав абсолютную ссылку на ячейки, содержащие b₁, b₀.

3. Построить диаграмму невязок. Сделать выводы.

Контрольные вопросы

1. Что такое невязки? По какой формуле их можно подсчитать?

2. Что показывает коэффициент детерминации.

3. Что такое число степеней свободы?

4. Что такое условное математическое ожидание Mx(Y)?

5. Что такое корреляционная и регрессионная зависимости Y от X?

6. Что такое модельное уравнение регрессии?

7. Что такое спецификация модели регрессии, объясняемая и объясняющая переменные, параметры модели?

8. Почему невозможно получить модельное уравнение регрессии?

9. Что такое выборочное уравнение регрессии?

10. Что такое выборочное условное среднее?

11. Каковы задачи регрессионного анализа?

12. Какие модели наблюдения соответствуют модельному и выборочному уравнению регрессии?

13. Что такое парная линейная регрессия, для чего она используется?

14. Имеется классическое линейное однофакторное уравнение регрессии, параметры которого оценены обычным МНК . Требуется доказать, что сумма остатков равна нулю: ;