Мастер функций f(х), Статистические, линейн

Лабораторная работа №2 по дисциплине «Эконометрика»

Тема: Решение задачи построения уравнения парной регрессии с помощью МS Excel

Задание 1

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

Имеются данные по 50 предприятиям о стоимости основных производственных фондов (ОПФ) (х, млн р.) и среднесуточной производительности (у, т) в таблице 1.

Таблица 1

№
х	35,03	36,79	37,40	36,68	37,62	38,48	39,09	39,23	39,26	39,31	39,72	40,19
у	12,98	15,35	15,81	17,13	18,64	18,6	24,29	23,41	22,21	19,80	21,84	17,53

№
х	40,10	40,19	40,41	40,76	41,54	41,68	45,12	48,42	48,29	48,65	51,93	53,23
у	16,29	22,13	19,81	21,30	20,18	24,17	24,97	26,44	24,19	22,05	22,16	27,72

№
х	54,35	54,69	56,61	56,76	56,80	57,30	59,26	59,84	61,45	61,72	62,91	65,21
у	25,86	21,15	25,37	24,83	30,80	28,00	30,55	31,66	29,77	31,12	35,13	33,73

№
х	67,92	67,63	68,45	68,24	68,46	68,58	68,53	69,17	69,45	70,98	71,4	74,23
у	29,08	32,66	34,12	33,88	26,33	34,54	36,34	29,19	28,50	35,60	32,19	30,76

№
х	74,97	75,78
у	31,87	38,00

Парный регрессионный анализ начинается с построения уравнения регрессии . Получить его можно двумя способами:

Первый способ. Построение линейной регрессии с помощью:

Мастер функций f(х), Статистические, ЛИНЕЙН

Порядок действий следующий.

1. Введите исходные данные в ячейки А1:В51 (таблица 1).

2. Выделите область пустых ячеек 5x2 (5 строк, 2 столбца) D2:E6 для вывода результатов регрессионной статистики или область 1x2 - для получения только оценок коэффициентов регрессии.

3. Активируйте Мастер функций любым из способов:

а) в главном меню выберите Вставка/Функция;

б) на панели инструментов Стандартная щелкните по кнопке
Вставка функции.

4. В окне «Категория» (рис. 1) выберите Статистические, в окне «Функция» - ЛИНЕЙН. Щелкните по кнопке ОК.

Рис.1 Диалоговое окно «Мастер функций»

5. Заполните аргументы функции (рис. 2)

Рис.2 Диалоговое окно ввода аргументов функции ЛИНЕЙН

Известные_значения_ y - диапазон, содержащий данные ре-зультативного признака (среднесуточной производительности).

Известные_значения _ x - диапазон, содержащий данные факторов независимого признака (стоимости ОПФ).

Константа — логическое значение, которое указывает на наличие или отсутствие свободного члена в уравнении. Если Константа = 1, то свободный член рассчитывается обычным образом, если Константа = 0, то свободный член равен 0.

Статистика — логическое значение, которое указывает, выводить дополнительную информацию по регрессионному анализу или нет. Если Статистика = 1, то дополнительная информация выводится, если Статистика = 0, то выводятся только оценки параметров уравнения.

Щелкните по кнопке ОК.

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

Таблица 2

Значение коэффициента	Значение коэффициента
Среднеквадратическое отклонение коэффициента	Среднеквадратическое отклонение коэффициента
Коэффициент детерминации	Стандартная ошибка регрессии
статистика	Число степеней свободы
Объясненная регрессией сумма квадратов отклонений RSS	Остаточная сумма квадратов ESS

Получить результат для данных из рассматриваемого примера (таблица 1)

Функция ЛИНЕЙН рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные. Функция возвращает массив, описывающий полученную прямую, уравнение которой можно записать в виде:

________________________________

Свободный член экономического значения не имеет, в отличие от коэффициента регрессии, который показывает, что ____________________________________________________________________________________________________________________________________

Второй способ. Построение линейной регрессии

с помощью инструментов Сервис, Анализ данных, Регрессия

С помощью инструмента параметров Регрессия можно получить результаты регрессионной статистики, дисперсионного анализа и доверительных интервалов, а также остатки и графики подбора линии регрессии.

Порядок действий следующий.

1. Активируйте доступ к Пакету анализа (если он не был активирован до этого). В главном меню последовательно выберите Сервис, Надстройки. Установите флажок Пакет анализа (рис. 3).

2. В главном меню выберите Сервис, Анализ данных, Регрессия. Щелкните по кнопке ОК.

3. Заполните диалоговое окно ввода данных и параметров вывода (рис. 4):

Входной интервал y, x - аналогичны известным значениям y ($B$2:$B$51), x ($A$2:$A$51).

Метки - флажок, который указывает, содержит ли первая строка названия столбцов или нет