1.2 Реализация типовых задач на компьютере.
Решение с помощью ППП Excel
1. Встроенная статистическая функция ЛИНЕЙН определяет параметры линейной регрессии y=a+bx. Порядок вычисления следующий:
введите исходные данные или откройте существующий файл, содержащий анализируемые данные;
выделите область пустых ячеек 5х2 (5 строк, 2 столбца) для вывода результатов регрессионной статистики или область 1х2 - для получения только оценок коэффициентов регрессии;
активизируйте Мастер функций любым из способов:
а) в главном меню выберите Вставка/Функция;
б) на панели инструментов Стандартная щелкните по кнопке
Вставка/Функция;
окне Категория (рис. 1.1) выберите Статистические, в окне- ЛИНЕЙН. Щелкните по кнопке ОК;
заполните аргументы функции (рис. 1.2):
Рис. 1.1. Диалоговое окно «Мастер функций»
И
звестные_значения_у
- диапазон, содержащий данные
результативного признака;
Рис. 1.2. Диалоговое окно ввода аргументов функции ЛИНЕЙН
Известные_значения_х - диапазон, содержащий данные факторов независимого признака;
Константа - логическое значение, которое указывает на наличие или на отсутствие свободного члена в уравнении; если Константа = 1, то свободный член рассчитывается обычным образом, если Константа = 0, то свободный член равен 0;
Статистика - логическое значение, которое указывает, выводить дополнительную информацию по регрессионному анализу или нет. Если Статистика = 1, то дополнительная информация выводится, если Статистика = 0, то выводятся только оценки параметров уравнения.
Щелкните по кнопке ОК;
в левой верхней ячейке выделенной области появится первый элемент итоговой таблицы. Чтобы раскрыть всю таблицу, нажмите на клавишу <F2>, а затем - на комбинацию клавиш <CTRL>+<SHIFT>+<ENTER>.
Дополнительная регрессионная статистика будет выводиться в порядке, указанном в следующей схеме:
Значение коэффициента b |
Значение коэффициента а |
Среднеквадратическое отклонение b |
Среднеквадратическое отклонение а |
Коэффициент детерминации R2 |
Среднеквадратическое отклонение у |
F-статистика |
Число степеней свободы |
Регрессионная сумма квадратов |
Остаточная сумма квадратов |
Для вычисления параметров экспоненциальной кривой y=αβx в MS Excel применяется встроенная статистическая функция ЛГРФПРИБЛ. Порядок вычисления аналогичен применению функции ЛИНЕЙН.
Для данных из примера 2 результат вычисления функции ЛИНЕЙН представлен на рис. 1.3, функции ЛГРФПРИБЛ - на рис.1.4.
Рис. 1.3. Результат вычисления функции ЛИНЕЙН
Рис.
1.4. Результат
вычисления функции
ЛГФПРИБЛ 
2.С помощью инструмента анализа данных Регрессия, помимо результатов регрессионной статистики, дисперсионного анализа и доверительных интервалов, можно получить остатки и графики подбора линии регрессии, остатков и нормальной вероятности. Порядок действий следующий:
п
роверьте
доступ к пакету анализа. В главном меню
последовательно выберите
Сервис /Надстройки.
Установите флажок
Пакет анализа
(рис. 1.5);
Рис. 1.5. Подключение надстройки Пакет анализа.
2) в главном меню выберите Сервис/Анализ данных/Регрессия. Щелкните по кнопке ОК;
заполните диалоговое окно ввода данных и параметров вывода (рис. 1.6):
Входной интервал Y - диапазон, содержащий данные результативного признака;
Входной интервал Х - диапазон, содержащий данные факторов независимого признака;
Метки - флажок, который указывает, содержит ли первая строка названия столбцов или нет;
Константа - ноль - флажок, указывающий на наличие или отсутствие свободного члена в уравнении;
Выходной интервал - достаточно указать левую верхнюю ячейку будущего диапазона;
Новый рабочий лист - можно задать произвольное имя нового листа.
Если необходимо получить информацию и графики остатков, установите соответствующие флажки в диалоговом окне. Щелкните по кнопке OK.
Рис. 1.6. Диалоговое окно ввода параметров инструмента Регрессия.
Рис. 1.7. Результат применения инструмента Регрессия.
В задачах 1-8 выполните:
Задание
Постройте поле корреляции и сформулируйте гипотезу о форме связи
Рассчитайте параметры уравнений линейной, степенной, экспоненциальной, полулогарифмической, обратной, гиперболической регрессии.
Оцените тесноту связи с помощью показателей корреляции и детерминации
"Дайте с помощью среднего (общего) коэффициента эластичности сравнительную оценку силы связи фактора с результатом.
Оцените с помощью средней ошибки аппроксимации качество уравнений.
Оцените с помощью F-критерия Фишера статистическую надежность результатов регрессионного моделирования. По значениям характеристик, рассчитанных в пп. 4, 5 и данном пункте, выберите лучшее уравнение регрессии и дайте его обоснование.
Рассчитайте прогнозное значение результата, если прогнозное значение фактора увеличится на 10% от его среднего уровня. Определите доверительный интервал прогноза для уровня значимости =0,05.
8. Оцените полученные результаты, выводы оформите в аналитической записке.
Задача 1. По территориям Центрального района известны данные за 1995 г. (табл. 1.1).
Таблица 1.1
Район |
Доля денежных доходов, направленных на прирост сбережений во вкладах. займах. сертификатах и на покупку валюты, в общей сумме среднедушевого денежного дохода, %, у |
Среднемесячная начисленная заработная плата, тыс. руб., х |
Брянская обл. |
6,9 |
289 |
Владимирская обл. |
8,7 |
334 |
Ивановская обл. |
6,4 |
300 |
Калужская обл. |
8,4 |
343 |
Костромская обл. |
6,1 |
356 |
Орловская обл. |
9,4 |
289 |
Рязанская обл. |
11,0 |
341 |
Смоленская обл. |
6,4 |
327 |
Тверская обл. |
9,3 |
357 |
Тульская обл. |
8,2 |
352 |
Ярославская обл. |
8,6 |
381 |
Задача 2.
По территориям Центрального района известны данные за 1995 г. (табл. 1.2).
Таблица 1.2
Район |
Средний размер назначенных ежемесячных пенсий, тыс. руб., у |
Прожиточный минимум в среднем на одного пенсионера в месяц, тыс. руб., х |
Брянская обл. |
240 |
178 |
Владимирская обл. |
226 |
202 |
Ивановская обл. |
221 |
197 |
Калужская обл. |
226 |
201 |
Костромская обл. |
220 |
189 |
г. Москва |
250 |
302 |
Московская обл. |
237 |
215 |
Орловская обл. |
232 |
166 |
Рязанская обл. |
215 |
199 |
Смоленская обл. |
220 |
180 |
Тверская обл. |
222 |
181 |
Тульская обл. |
231 |
186 |
Ярославская обл. |
229 |
250 |
Задача 3.
По территориям Центрального и Волго-Вятского районов известны данные за ноябрь 1997 г. (табл. 1.3).
Таблица 1.3
Район |
Средняя заработная плата и выплаты социального характера, тыс. руб., у |
Прожиточный минимум в среднем на душу населения, тыс. руб., х |
Центральный |
|
|
Брянская обл. |
615 |
289 |
Владимирская обл. |
727 |
338 |
Ивановская обл. |
584 |
287 |
Калужская обл. |
753 |
324 |
Костромская обл. |
707 |
307 |
Орловская обл. |
653 |
304 |
Рязанская обл. |
654 |
307 |
Смоленская обл. |
693 |
290 |
Тверская обл. |
704 |
314 |
Тульская обл. |
780 |
304 |
Ярославская обл. |
830 |
341 |
Волго-Вятский район |
|
|
Респ. Марий Эл |
554 |
364 |
Респ. Мордовия |
560 |
342 |
Чувашская Респ. |
545 |
310 |
Кировская обл. |
672 |
411 |
Нижегородская обл. |
796 |
304 |
Задача 4.
По территориям Волго-Вятского, Центрально-Черноземного и Поволжского районов известны данные за ноябрь 1997 г. (табл. 1.19).
Таблица 1.4
Район |
Потребительские расходы в расчете на душу населения, тыс. руб., у |
Средняя заработная плата и выплаты социального характера, тыс. руб., х |
Волго-Вятский |
|
|
Респ. Марий Эл |
302 |
554 |
Респ. Мордовия |
360 |
560 |
Чувашская Респ. |
310 |
545 |
Кировская обл. |
415 |
672 |
Нижегородская обл. - |
452 |
796 |
Центрально-Черноземный |
|
|
Белгородская обл. |
502 |
777 |
Воронежская обл. |
355 |
632 |
Курская обл. |
416 |
688 |
Липецкая обл. |
501 |
833 |
Тамбовская обл. |
403 |
577 |
Поволжский |
|
|
Респ. Калмыкия |
208 |
584 |
Респ. Татарстан |
462 |
949 |
Астраханская обл. |
368 |
888 |
Волгоградская обл. |
399 |
831 |
Пензенская обл. |
342 |
562 |
Саратовская обл. |
354 |
665 |
Ульяновская обл. |
558 |
705 |
Задача 5.
По территориям Северного, Северо-западного и Центрального районов известны данные за ноябрь 1997 г. (табл. 1.5).
Таблица 1.5
Район |
Потребительские расходы на душу населения, тыс. руб., у |
Денежные доходы на душу населения, тыс. руб., х |
Северный |
|
|
Респ. Карелия |
596 |
913 |
Респ. Коми |
417 |
1095 |
Архангельская обл. |
354 |
606 |
Вологодски обл. |
526 |
876 |
Мурманская обл. |
934 |
1314 |
Сеаеро-Западный |
|
|
Ленинградская обл. |
412 |
593 |
Новгородская обл. |
525 |
754 |
Псковская обл. |
367 |
528 |
Центральный |
|
|
Брянская обл. |
364 |
520 |
Владимирская обл. |
336 |
539 |
Ивановская обл. |
409 |
540 |
Калужская обл. |
452 |
682 |
Костромская обл. |
367 |
537 |
Московская обл. |
328 |
589 |
Орловская обл. |
460 |
626 |
Рязанская обл. |
380 |
521 |
Смоленская обл. |
439 |
626 |
Тверская обл. |
344 |
521 |
Тульская обл. |
401 |
658 |
Ярославская обл. |
514 |
746 |
Задача 6.
По территориям Восточно-Сибирского и Дальневосточного районов известны данные за ноябрь 1997 г. (табл. 1.6).
Таблица 1.6
Район |
Потребительские расходы на душу населения, тыс. руб., у |
Денежные доходы на душу населения, тыс. руб., х |
Восточно-Сибирский |
|
|
"Респ. Бурятия |
408 |
524 |
Рссп. Тыва |
249 |
371 |
Респ. Хакасия |
253 |
453 |
Красноярский край |
580 |
1006 |
Иркутская обл. |
651 |
997 |
Усть-Ордынский Бурятский авт. округ. |
139 |
217 |
Читинская обл. |
322 |
486 |
Респ. Саха (Якутия) |
899 |
1989 |
Еврейская авт. обл. |
330 |
595 |
Чукотский авт. округ |
446 |
1550 |
Приморский край |
642 |
937 |
Хабаровский край |
542 |
761 |
Амурская обл. |
504 |
767 |
Камчатская обл. |
861 |
1720 |
Магаданская обл. |
707 |
1735 |
Сахалинская обл. |
557 |
1052 |
Задача 7
По территориям Уральского и Западно-Сибирского районов известны данные за ноябрь 1997 г. (табл. 1.7).
Таблица 1.7
Район |
Потребительские расходы на душу населения, тыс. руб., у |
Денежные доходы на душу населения, тыс. руб., х |
Уральский |
|
|
Респ. Башкортостан |
461 |
632 |
Удмуртская Респ. |
524 |
738 |
Курганская обл. |
298 |
515 |
Оренбургская обл. |
351 |
640 |
Пермская обл. |
624 |
942 |
Свердловская обл. |
584 |
888 |
Челябинская обл. |
425 |
704 |
Западносибирский район |
|
|
Респ. Алтай |
277 |
603 |
Алтайский край |
321 |
439 |
Кемеровская обл. |
573 |
985 |
Новосибирская обл. |
576 |
737 |
Омская обл. |
588 |
760 |
Томская обл. |
497 |
830 |
Тюменская обл. |
863 |
2093 |
Задача 8.
По территориям Уральского и Западно-Сибирского районов известны данные за ноябрь 1997 г. (табл. 1.8).
Таблица 1.8
Район
|
Потребительские расходы на душу населения, тыс. руб., у |
Средняя заработная плата и выплаты социального характера, тыс. руб., х |
Уральский |
|
|
Респ. Башкортостан |
461 |
912 |
Удмуртская Респ. |
524 |
809 |
Курганская обл. |
298 |
748 |
Оренбургская обл. |
351 |
847 |
Пермская обл. |
624 |
1087 |
Свердловская обл. |
584 |
1074 |
Челябинская обл. |
425 |
1008 |
Западно-Сибирский |
|
|
Респ. Алтай |
277 |
682 |
Алтайский край |
321 |
697 |
Кемеровская обл. |
573 |
1251 |
Новосибирская обл. |
576 |
967 |
Омская обл. |
588 |
898 |
Томская обл. |
497 |
1263 |
Тюменская обл. |
863 |
3027 |
Задача 9.
По 20 регионам страны изучается зависимость уровня безработицы у (%) от индекса потребительских цен x (% к предыдущему году). Информация о логарифмах исходных показателей представлена в табл. 1.9.
Таблица 1.9
Показатель |
In x; |
In у |
Среднее значение |
0,6 |
1,0 |
Среднее квадратическое отклонение |
0,4 |
0,2 |
Известно также, что коэффициент корреляции между логарифмами исходных показателей составил rlnx lny = 0,8.
Задание
1. Постройте уравнение регрессии зависимости уровня безработицы от индекса потребительских цен в степенной форме.
2. Дайте интерпретацию коэффициента эластичности данной модели регрессии.
3. Определите значение коэффициента детерминации и поясните его смысл.
Задача 10.
Изучается зависимость материалоемкости продукции от размера предприятия по 10 однородным заводам (табл. 1.10).
Таблица 1.10
Показатель |
Материалоемкость продукции по заводам |
|||||||||
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
Потреблено материалов на единицу продукции, кг |
9 |
6 |
5 |
4 |
3,7 |
3,6 |
3,5 |
6 |
7 |
3,5 |
Выпуск продукции, тыс. ед. |
100 |
200 |
300 |
400 |
500 |
600 |
700 |
150 |
120 |
250 |
Задание
Найдите параметры уравнения
Оцените тесноту связи с помощью индекса корреляции.
Охарактеризуйте эластичность изменения материалоемкости продукции.
Сделайте вывод о значимости уравнения регрессии.
Оцените полученные результаты, выводы оформите в аналитической записке.