Лабораторная работа_10
Задача по построению парной регрессии.
Известны размеры годовых доходов и потребительских расходов на душу населения.
Потребительские расходы но душу населения, т. т. | 364 | 336 | 409 | 452 | 367 | 328 | 460 | 380 | 439 | 344 | 401 | 514 |
Денежные доходы на душу населения | 520 | 539 | 540 | 682 | 337 | 589 | 626 | 521 | 626 | 521 | 638 | 746 |
Требуется:
1. Построить поле корреляции и выдвинуть гипотезу о форме связи.
2. Рассчитать параметры уравнений линейной, степенной, показательной, гиперболической парной регрессии.
3. Определить основные характеристики полученных моделей – коэффициент и индекс корреляции, коэффициент детерминации, ошибку аппроксимации, коэффициент эластичности.
4. Дать экономическую интерпретацию найденных параметров.
5. Оценить статистическую надежность полученных моделей и выбрать лучшие уравнения регрессии.
6. Сделать выводы по результатам регрессионного анализа.
Решение задачи с помощью ППП EXCEL.
1. Выполним первый пункт.
С этой целью осуществим ввод исходных данных, приведенных в таблице. Откроем в EXCEL какой-либо файл, пусть это будет стандартный КНИГА 1, и наберем числовые данные вместе с заголовком. После формирования таблицы построим график парных значений (x, y) – после корреляции. Для этого надо выполнить следующие действия:
1. активизировать исходные данные x иy, т. е. выделить полностью столбцы x иy с числовыми данными и заголовком они будут окрашены в черный цвет;
2. Построим диаграмму с помощью мастер диаграмм любым из двух способов:
- в главном меню выбрать Вставка – Диаграмма;
- на панели инструментов Стандартная щелкнуть кнопу Мастер диаграмм;
3. в появившемся окне Мастер диаграмм следует выбрать Тип – Точечная, а в соседнем окне Вид – выбрать соответствующий рисунок в виде точек. Щелкнуть по кнопке готово.
4. появится график. Это и есть поле корреляции. Средствами EXCEL можно провести график к удобному виду и положению. Необходимо сделать визуальный анализ графика и выдвинуть гипотезу о форме зависимости переменных. После того, как график получен, следует отметить выделение (столбцы x и y обведены рамкой) исходных данных.
2. Определим параметры линейной регрессии. Для этого используется встроенная статистическая функция ЛИНЕЙН. Порядок вычисления следующий:
1. открыть лист, на котором размещены исходные данные и поле корреляции (Лист 1);
2. в сводной зоне, выделить область пустых ячеек размера 5х2 (5 строк, 2 столбца) для вывода результатов регрессионной статистики или область 1х2 для вывода результатов регрессионной статистики или область 1х2 – для получения только оценок коэффициентов регрессия; выделенная область будет окрашена в черный цвет;
3. активизировать Мастер функций любым из способов:
а) в главном меню будет Вставка/Функция;
б) на панели инструментов выбрать значок функции;
4. в окне Категория выбрать Статистические, в окне Функция – ЛИНЕЙН. Щелкнуть по кнопке ОК.
5. появится окно ввода аргументов функции ЛИНЕЙН, в котором необходимо указать адреса числовых данных (без заголовков) этого можно достичь как непосредственным вводом с клавиатуры, так и простым выделением числовых данных в столбцах xy/
Известные_значения_y – диапазон, содержащий данные результативного признака;
Известные_значения_x – диапазон, содержащий данные факторов независимого признака;
Логическое значение указывает на наличие или отсутствие свободного члена.
Статистика – логическое значение, которой указывает, выводить дополнительную информацию по регрессионному анализу или нет. Если Статистика =1 , то дополнительная информация выводится, если Статистика =0, то выводится оценки параметров уравнения.
После заполнения окна щелкнуть по кнопке ОК.
6. в левой верхней ячейке выделенной области появится первый элемент итоговой таблицы. Чтобы раскрыть всю таблицу, следует нажать на клавишу <F2>, а затем одновременно на комбинацию клавиш <CTRL>+<SHIFT>+<ENTER>. В результате будет получена таблица значений. Полученные числовые значения имеют следующий смысл:
Значение коэффициента b | Значение коэффициента а |
Среднеквадратическое отклонение b | Среднеквадратическое отклонение а |
Коэффициент детерминирования R2 | Среднеквадратическое отклонение y |
3. Дальнейший анализ модели можно осуществить с помощью инструмента анализа данных РЕГРЕССИЯ, который позволяет получить результаты дополнительной регрессионной статистики, дисперсионного анализа, а также доверительные интервалы, остатки и графики подбора линии регрессии, остатки нормальной вероятности. С этой целью необходимо выполнить следующую последовательность действий:
1. в главном меню выбрать Сервис/Надстройки. Установить флажок Пакет анализа, ОК.
2. в главном меню выбрать Сервис/Анализ данных /Регрессия, ОК;
3. появится окно ввода аргументов функции РЕГРЕССИЯ, в котором необходимо указать адреса числовых данных этого можно достичь как непосредственным вводом с клавиатуры, так и простым выделением данных в столбцах x и y.
Входной интервал Y – диапазон, содержащий данные результативного признака;
Входной диапазон X - диапазон, содержащий данные факторов независимого признака;
Метки –флажок, указывающий на наличие или отсутствие свободного члена в управлении;
Константа – ноль – флажок, указывающий на наличие или отсутствие свободного члена в управлении;
Выходной интервал – достаточно указать левую верхнюю ячейку будущего диапазона;
Новый рабочий лист – можно задать произвольное имя нового листа.
Если необходимо получить информацию и графики остатков, установите соответствующие флажки в диалоговом окне. Щелкните по кнопке ОК.
Следует отметить, что построение степенной, показательной, логарифмической осуществляется по описанной выше схеме. В частности, для построения экспоненциальной модели применяется встроенная статистическая функция ЛГРФПРИБЛ. Порядок вычисления аналогичен применению функции ЛИНЕЙН.
