Линейная парная регрессия на точечной диаграмме в MS Excel

Рассмотрим один из самых простых и быстрых способов получения статистической модели взаимосвязи между двумя случайными переменными в виде уравнения парной линейной регрессии. Для этого будем использовать точечную диаграмму и линию тренда в среде электронных таблиц MS Excel.

Уравнение линейной парной регрессии имеет вид:

$y=a+bx+\varepsilon,$

где $y$ — моделируемая переменная,
$x$ — переменная-фактор,
$\varepsilon$ — ошибка.

Стандартной задачей является нахождение параметров $a$ и $b$ для конкретных статистических данных. Рассмотрим пример решения этой задачи простыми инструментами программы MS Excel.

Пример. Изучается зависимость себестоимости единицы изделия ( $y$ , тыс. руб.) от величины выпуска продукции ( $x$ , тыс. шт.) по группам предприятий за отчетный период. Экономист обследовал $n=5$ предприятий и получил следующие результаты:

$x$	2	3	4	5	6
$y$	1,9	1,7	1,8	1,6	1,4

Найти уравнение линейной парной регрессии $y=a+bx+\varepsilon$ .

Источник: Просветов Г.И. Эконометрика: задачи и решения: учебно-методическое пособие. — М.: Издательство «Альфа-пресс», 2008. — 192 с. (Пример 18, с.32)

Решение. Введем исходные данные в таблице MS Excel:

Выделим диапазон исходных данных:

В меню Вставка выбираем инструмент Точечная диаграмма (именно эта диаграмма позволяет строить точки по двум координатам):

Появляется диаграмма (в статистике этот график называют корреляционным полем):

Выполняем правый щелчок мыши по любой точке на диаграмме, появляется контекстное меню, в котором выбираем команду Добавить линию тренда:

Появляется окно диалога:

В окне диалога Формат линии тренда выбираем вид Линейная (обычно выбрано по умолчанию) и ставим флажок Показывать уравнение на диаграмме, нажимаем кнопку Закрыть. На точечной диаграмме появляется сглаживающая линия и ее уравнение, это и есть искомое уравнение регрессии:

Таким образом, уравнение регрессии имеет вид:

$y=-0,11x+2,12+\varepsilon$

Сделаем вывод: коэффициент регрессии $b=-0,11$ показывает, что при увеличении выпуска продукции на одну тысячу штук, себестоимость единицы продукции уменьшается в среднем на 0,11 тысяч рублей.

Задание на СР: Найти уравнение линейной парной регрессии, если $y$ — недельные объемы продаж, тыс. руб., $x$ — расходы на рекламу, тыс. руб.

$x$	5	8	6	5	3	9	12	4	3	10
$y$	72	76	78	70	68	80	82	65	62	90

Источник: Просветов Г.И. Эконометрика: задачи и решения: учебно-методическое пособие. — М.: Издательство «Альфа-пресс», 2008. — 192 с. (Задача 18, с.32)

Линейная парная регрессия на точечной диаграмме в MS Excel

Добавить комментарий Отменить ответ