Решение с помощью ППП Excel

 

Задача 1.3 По территориям региона приводятся данные (таблица 1.3.1).

Таблица 1.3.1.

Номер региона	Среднедушевой прожиточный минимум в день одного трудоспособного, у.е.	Среднедневная заработная плата, у.е.
1	78	133
2	82	148
3	87	134
4	79	154
5	89	162
6	106	195
7	67	139
8	88	158
9	73	152
10	87	162
11	76	159
12	115	173

 

1) Встроенная статистическая функция ЛИНЕЙН определяет параметры линейной регрессии  Порядок вычисления следующий:

1. Введите исходные данные или откройте существующий файл, содержащий анализируемые данные;

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

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

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

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

4. В окне категория (рис. 1.1) выберите Статистические, в окне функция – ЛИНЕЙН. Щелкните по кнопке ОК;

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

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

Известные_значения_y – диапазон, содержащий данные результативного признака;

Известные_значения_x – диапазон, содержащий данные факторов независимого признака;

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

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

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

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

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

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

 

Для данных из задачи 1.4 результат вычисления функции ЛИНЕЙН представлен на рис.1.3.

Рис. 1.3 Результат вычисления функции ЛИНЕЙН

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

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

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

 

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

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

Входной интервал Y – диапазон, содержащий данные результативного признака;

Входной интервал X – диапазон, содержащий данные факторов независимого признака;

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

Константа – ноль - флажок, указывающий на наличие или отсутствие свободного члена в уравнении;

Выходной интервал – достаточно указать левую верхнюю ячейку будущего диапазона;

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

Если необходимо получить информацию и графики остатков, установите соответствующие флажки в диалоговом окне. Щелкните по кнопке ОК.

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

 

Результаты регрессионного анализа для данных из задачи 1.3 представлены на рис. 1.6.

Рис. 1.6 Результат применения инструмента Регрессия.

 

 

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

 

1) Что такое коэффициент регрессии? Каковы способы его оценивания?

2) В чем смысл коэффициента детерминации?

3) Для чего применяется критерий Фишера? В чем его суть?

4) Для чего применяется анализ дисперсии в критерии Фишера?

5) Для чего необходим расчет дисперсии на одну степень свободы?

6) Перечислите виды нелинейных моделей.

7) Как определяются коэффициенты эластичности по различным видам регрессионных моделей?

8) Как определяется средняя ошибка аппроксимации? В чем ее смысл?

9) Как определяется коэффициент корреляции? В чем его смысл?

10) Какие виды регрессий существуют? В каких случаях они применяются?

Пример варианта промежуточного тестирования

 

1. Вариация уровня заработной платы на 56% объясняет вариацию расходов на продукты питания в общих расходах. Это означает, что:

а) линейный коэффициент парной регрессии равен 56%;

б) коэффициент детерминации равен 44%;

в) линейный коэффициент парной регрессии равен 44%;

г) коэффициент детерминации равен 56%;

д) средняя ошибка аппроксимации равна 56%.

 

 

2. После получения уравнения парной линейной регрессии выяснилось, что в среднем расчетные значения отклоняются от фактических на 1,5%. Это означает, что:

а) коэффициент детерминации равен 1,5%;

б) средняя ошибка аппроксимации равна 0,015;

в) линейный коэффициент парной регрессии равен 0,015;

г) значение F_расч равно 1,5

д) с вероятностью 1,5% уравнение регрессии статистически значимо.

 

 

3. F_расч=4,5 F_табл=4,3 Какой вывод можно сделать из анализа предложенных значений:

а) уравнение регрессии статистически незначимо;

б) необходим переход в расчетах от линейной регрессии к нелинейной;

в) уравнение регрессии статистически значимо;

г) в расчетах необходимо использовать множественную, а не парную регрессию.

д) получено неверное значение F_расч.

4. Каково соотношение между числом степеней свободы общей, факторной и остаточной сумм квадратов:

а) их сумма больше единицы;

б) сумма числа степеней свободы факторной и остаточной равна числу степеней свободы общей суммы квадратов;

в) их сумма равна нулю;

г) сумма числа степеней свободы факторной и остаточной больше числа степеней свободы общей суммы квадратов;

д) сумма числа степеней свободы факторной и остаточной меньше числа степеней свободы общей суммы квадратов.

5. Если при анализе дисперсии сумма квадратов отклонений, обусловленная регрессией, будет больше остаточной суммы квадратов, то уравнение регрессии:

а) статистически незначимо;

б) не рекомендуется использовать в дальнейших расчетах;

в) неверно вследствие неточного расчета коэффициентов;

г) статистически значимо;

д) отклоняется с доверительной вероятностью 95%.

 

6. Величина F‑критерия для проверки гипотезы  это:

а) отношение факторной дисперсии к остаточной;

б) произведение факторной и остаточной дисперсий;

в) отношение общей дисперсии к остаточной;

г) произведение общей и остаточной дисперсий;

д) отношение общей дисперсии к произведению факторной и остаточной.

7. Что следует из линейного уравнения y=500+300x, если y - это издержки, а x - объем продукции?

а) с уменьшением объема продукции на 1 ед. издержки производства возрастают в среднем на 500 ед;

б) с увеличением объема продукции на 1 ед. издержки производства возрастают на 800 ед;

в) с увеличением объема продукции в среднем на 1 ед. издержки производства возрастают в среднем на 300 ед;

г) с уменьшением объема продукции в среднем на 300 ед. издержки производства возрастают на 1 ед;

д) с увеличением объема продукции в среднем на 500 ед. издержки производства возрастают в среднем на 300 ед.