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

Задание 1

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

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

Таблица 1

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

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

Мастер функций 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).

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

Рис. 3 Подключение надстройки Пакет анализа

Рис. 4. Диалоговое окно ввода параметров инструмента Регрессия