Решение с помощью ППП Excel
1. Встроенная статистическая функцияЛИНЕЙН определяет параметры линейной регрессии y=a+b×x. Порядок вычисления следующий:
1. введите исходные данные или откройте существующий файл, содержащий анализируемые данные;
2. выделите область пустых ячеек 5х2 (5 строк, 2 столбца) для вывода результатов регрессионной статистики или область 1х2 - для получения только оценок коэффициентов регрессии;
3. активизируйте Мастер функций любым из способов:
а) в главном меню выберитеВставка/Функция;
б) на панели инструментовСтандартная щелкните по кнопке
Вставка/Функция;
4. окне Категория (рис. 1.1) выберитеСтатистические, в окне-ЛИНЕЙН. Щелкните по кнопкеОК;
5. заполните аргументы функции (рис. 1.2):
 |
Рис. 1.1. Диалоговое окно «Мастер функций»
 |
Известные_значения_у - диапазон, содержащий данные результативного признака;
Рис. 1.2. Диалоговое окно ввода аргументов функцииЛИНЕЙН
Известные_значения_х - диапазон, содержащий данные факторов независимого признака;
Константа - логическое значение, которое указывает на наличие или на отсутствие свободного члена в уравнении; если Константа = 1, то свободный член рассчитывается обычным образом, если Константа = 0, то свободный член равен 0;
Статистика - логическое значение, которое указывает, выводить дополнительную информацию по регрессионному анализу или нет. Если Статистика = 1, то дополнительная информация выводится, если Статистика = 0, то выводятся только оценки параметров уравнения.
Щелкните по кнопкеОК;
6. в левой верхней ячейке выделенной области появится первый элемент итоговой таблицы. Чтобы раскрыть всю таблицу, нажмите на клавишу <F2>, а затем - на комбинацию клавиш <CTRL>+<SHIFT>+<ENTER>.
Дополнительная регрессионная статистика будет выводиться в порядке, указанном в следующей схеме:
| Значение коэффициента b | Значение коэффициента а |
| Среднеквадратическое отклонение b | Среднеквадратическое отклонение а |
| Коэффициент детерминации R2 | Среднеквадратическое отклонение у |
| F-статистика | Число степеней свободы |
| Регрессионная сумма квадратов | Остаточная сумма квадратов |
Для вычисления параметров экспоненциальной кривой y=α×βx в MS Excel применяется встроенная статистическая функцияЛГРФПРИБЛ. Порядок вычисления аналогичен применению функцииЛИНЕЙН.
Для данных из примера 2 результат вычисления функции ЛИНЕЙН представлен на рис. 1.3, функцииЛГРФПРИБЛ - на рис.1.4.
 |
Рис. 1.3. Результат вычисления функции ЛИНЕЙН
Рис. 1.4. Результат вычисления функции ЛГФПРИБЛ 
2.С помощью инструмента анализа данныхРегрессия, помимо результатов регрессионной статистики, дисперсионного анализа и доверительных интервалов, можно получить остатки и графики подбора линии регрессии, остатков и нормальной вероятности. Порядок действий следующий:
1)
 |
проверьте доступ к пакету анализа. В главном меню последовательно выберитеСервис /Надстройки. Установите флажокПакет анализа (рис. 1.5);
Рис. 1.5. Подключение надстройки Пакет анализа.
2) в главном меню выберитеСервис/Анализ данных/Регрессия.Щелкните по кнопкеОК;
3) заполните диалоговое окно ввода данных и параметров вывода (рис. 1.6):
Входной интервал Y - диапазон, содержащий данные результативного признака;
Входной интервал Х - диапазон, содержащий данные факторов независимого признака;
Метки - флажок, который указывает, содержит ли первая строка названия столбцов или нет;
Константа - ноль - флажок, указывающий на наличие или отсутствие свободного члена в уравнении;
Выходной интервал - достаточно указать левую верхнюю ячейку будущего диапазона;
Новый рабочий лист - можно задать произвольное имя нового листа.
Если необходимо получить информацию и графики остатков, установите соответствующие флажки в диалоговом окне. Щелкните по кнопке OK.
 |
Рис. 1.6. Диалоговое окно ввода параметров инструмента Регрессия.
Рис. 1.7. Результат применения инструмента Регрессия.
В задачах 1-8 выполните:
Задание
1. Постройте поле корреляции и сформулируйте гипотезу о форме связи
2. Рассчитайте параметры уравнений линейной, степенной, экспоненциальной, полулогарифмической, обратной, гиперболической регрессии.
3. Оцените тесноту связи с помощью показателей корреляции и детерминации
4. "Дайте с помощью среднего (общего) коэффициента эластичности сравнительную оценку силы связи фактора с результатом.
5. Оцените с помощью средней ошибки аппроксимации качество уравнений.
6. Оцените с помощью F-критерия Фишера статистическую надежность результатов регрессионного моделирования. По значениям характеристик, рассчитанных в пп. 4, 5 и данном пункте, выберите лучшее уравнение регрессии и дайте его обоснование.
7. Рассчитайте прогнозное значение результата, если прогнозное значение фактора увеличится на 10% от его среднего уровня. Определите доверительный интервал прогноза для уровня значимости =0,05.
8. Оцените полученные результаты, выводы оформите в аналитической записке.
Задача 1.По территориям Центрального района известны данные за 1995 г. (табл. 1.1).
Таблица 1.1
| Район | Доля денежных доходов, направленных на прирост сбережений во вкладах. займах. сертификатах и на покупку валюты, в общей сумме среднедушевого денежного дохода, %, у | Среднемесячная начисленная заработная плата, тыс. руб., х |
| Брянская обл. | 6,9 | |
| Владимирская обл. | 8,7 | |
| Ивановская обл. | 6,4 | |
| Калужская обл. | 8,4 | |
| Костромская обл. | 6,1 | |
| Орловская обл. | 9,4 | |
| Рязанская обл. | 11,0 | |
| Смоленская обл. | 6,4 | |
| Тверская обл. | 9,3 | |
| Тульская обл. | 8,2 | |
| Ярославская обл. | 8,6 |
Задача 2.
По территориям Центрального района известны данные за 1995 г. (табл. 1.2).
Таблица 1.2
| Район | Средний размер назначенных ежемесячных пенсий, тыс. руб., у | Прожиточный минимум в среднем на одного пенсионера в месяц, тыс. руб., х |
| Брянская обл. | ||
| Владимирская обл. | ||
| Ивановская обл. | ||
| Калужская обл. | ||
| Костромская обл. | ||
| г. Москва | ||
| Московская обл. | ||
| Орловская обл. | ||
| Рязанская обл. | ||
| Смоленская обл. | ||
| Тверская обл. | ||
| Тульская обл. | ||
| Ярославская обл. |
Задача 3.
По территориям Центрального и Волго-Вятского районов известны данные за ноябрь 1997 г. (табл. 1.3).
Таблица 1.3
| Район | Средняя заработная плата и выплаты социального характера, тыс. руб., у | Прожиточный минимум в среднем на душу населения, тыс. руб., х |
| Центральный | ||
| Брянская обл. | ||
| Владимирская обл. | ||
| Ивановская обл. | ||
| Калужская обл. | ||
| Костромская обл. | ||
| Орловская обл. | ||
| Рязанская обл. | ||
| Смоленская обл. | ||
| Тверская обл. | ||
| Тульская обл. | ||
| Ярославская обл. | ||
| Волго-Вятский район | ||
| Респ. Марий Эл | ||
| Респ. Мордовия | ||
| Чувашская Респ. | ||
| Кировская обл. | ||
| Нижегородская обл. |
return false">ссылка скрыта
Задача 4.
По территориям Волго-Вятского, Центрально-Черноземного и Поволжского районов известны данные за ноябрь 1997 г. (табл. 1.19).
Таблица 1.4
| Район | Потребительские расходы в расчете на душу населения, тыс. руб., у | Средняя заработная плата и выплаты социального характера, тыс. руб., х |
| Волго-Вятский | ||
| Респ. Марий Эл | ||
| Респ. Мордовия | ||
| Чувашская Респ. | ||
| Кировская обл. | ||
| Нижегородская обл. - | ||
| Центрально-Черноземный | ||
| Белгородская обл. | ||
| Воронежская обл. | ||
| Курская обл. | ||
| Липецкая обл. | ||
| Тамбовская обл. | ||
| Поволжский | ||
| Респ. Калмыкия | ||
| Респ. Татарстан | ||
| Астраханская обл. | ||
| Волгоградская обл. | ||
| Пензенская обл. | ||
| Саратовская обл. | ||
| Ульяновская обл. |
Задача 5.
По территориям Северного, Северо-западного и Центрального районов известны данные за ноябрь 1997 г. (табл. 1.5).
Таблица 1.5
| Район | Потребительские расходы на душу населения, тыс. руб., у | Денежные доходы на душу населения, тыс. руб., х |
| Северный | ||
| Респ. Карелия | ||
| Респ. Коми | ||
| Архангельская обл. | ||
| Вологодски обл. | ||
| Мурманская обл. | ||
| Сеаеро-Западный | ||
| Ленинградская обл. | ||
| Новгородская обл. | ||
| Псковская обл. | ||
| Центральный | ||
| Брянская обл. | ||
| Владимирская обл. | ||
| Ивановская обл. | ||
| Калужская обл. | ||
| Костромская обл. | ||
| Московская обл. | ||
| Орловская обл. | ||
| Рязанская обл. | ||
| Смоленская обл. | ||
| Тверская обл. | ||
| Тульская обл. | ||
| Ярославская обл. |
Задача 6.
По территориям Восточно-Сибирского и Дальневосточного районов известны данные за ноябрь 1997 г. (табл. 1.6).
Таблица 1.6
| Район | Потребительские расходы на душу населения, тыс. руб., у | Денежные доходы на душу населения, тыс. руб., х |
| Восточно-Сибирский | ||
| "Респ. Бурятия | ||
| Рссп. Тыва | ||
| Респ. Хакасия | ||
| Красноярский край | ||
| Иркутская обл. | ||
| Усть-Ордынский Бурятский авт. округ. | ||
| Читинская обл. | ||
| Респ. Саха (Якутия) | ||
| Еврейская авт. обл. | ||
| Чукотский авт. округ | ||
| Приморский край | ||
| Хабаровский край | ||
| Амурская обл. | ||
| Камчатская обл. | ||
| Магаданская обл. | ||
| Сахалинская обл. |
Задача 7
По территориям Уральского и Западно-Сибирского районов известны данные за ноябрь 1997 г. (табл. 1.7).
Таблица 1.7
| Район | Потребительские расходы на душу населения, тыс. руб., у | Денежные доходы на душу населения, тыс. руб., х |
| Уральский | ||
| Респ. Башкортостан | ||
| Удмуртская Респ. | ||
| Курганская обл. | ||
| Оренбургская обл. | ||
| Пермская обл. | ||
| Свердловская обл. | ||
| Челябинская обл. | ||
| Западносибирский район | ||
| Респ. Алтай | ||
| Алтайский край | ||
| Кемеровская обл. | ||
| Новосибирская обл. | ||
| Омская обл. | ||
| Томская обл. | ||
| Тюменская обл. |
Задача 8.
По территориям Уральского и Западно-Сибирского районов известны данные за ноябрь 1997 г. (табл. 1.8).
Таблица 1.8
| Район | Потребительские расходы на душу населения, тыс. руб., у | Средняя заработная плата и выплаты социального характера, тыс. руб., х |
| Уральский | ||
| Респ. Башкортостан | ||
| Удмуртская Респ. | ||
| Курганская обл. | ||
| Оренбургская обл. | ||
| Пермская обл. | ||
| Свердловская обл. | ||
| Челябинская обл. | ||
| Западно-Сибирский | ||
| Респ. Алтай | ||
| Алтайский край | ||
| Кемеровская обл. | ||
| Новосибирская обл. | ||
| Омская обл. | ||
| Томская обл. | ||
| Тюменская обл. |
Задача 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
| Показатель | Материалоемкость продукции по заводам | |||||||||
| Потреблено материалов на единицу продукции, кг | 3,7 | 3,6 | 3,5 | 3,5 | ||||||
| Выпуск продукции, тыс. ед. |
Задание
1. Найдите параметры уравнения 
2. Оцените тесноту связи с помощью индекса корреляции.
3. Охарактеризуйте эластичность изменения материалоемкости продукции.
4. Сделайте вывод о значимости уравнения регрессии.
Оцените полученные результаты, выводы оформите в аналитической записке.