Решение с помощью ППП 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>.

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

Для вычисления параметров экспоненциальной кривой 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

Задача 2.

По территориям Центрального района известны данные за 1995 г. (табл. 1.2).

Таблица 1.2

Задача 3.

По территориям Центрального и Волго-Вятского районов из­вестны данные за ноябрь 1997 г. (табл. 1.3).

Таблица 1.3

Задача 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

Известно также, что коэффициент корреляции между логариф­мами исходных показателей составил r_{lnx lny} = 0,8.

Задание

1. Постройте уравнение регрессии зависимости уровня безработицы от индекса потребительских цен в степенной форме.

2. Дайте интерпретацию коэффициента эластичности данной модели регрессии.

3. Определите значение коэффициента детерминации и поясните его смысл.

Задача 10.

Изучается зависимость материалоемкости продукции от размера предприятия по 10 однородным заводам (табл. 1.10).

Таблица 1.10

Задание

1. Найдите параметры уравнения

2. Оцените тесноту связи с помощью индекса корреляции.

3. Охарактеризуйте эластичность изменения материалоемкости продукции.

4. Сделайте вывод о значимости уравнения регрессии.

Оцените полученные результаты, выводы оформите в аналитиче­ской записке.

Поиск по сайту: