Задача №3. Расчёт параметров регрессии и корреляции с помощью Excel

По территориям региона приводятся данные за 200Х г.

Номер региона Среднедушевой прожиточный минимум в день одного трудоспособного, руб., х Среднедневная заработная плата, руб., у
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. Постройте поле корреляции и сформулируйте гипотезу о форме связи.

2. Рассчитайте параметры уравнения линейной регрессии

Линейная функция.

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

4. Дайте с помощью среднего (общего) коэффициента эластичности сравнительную оценку силы связи фактора с результатом.

5. Оцените с помощью средней ошибки аппроксимации качество уравнений.

6. Оцените с помощью F-критерия Фишера статистическую надёжность результатов регрессионного моделирования.

7. Рассчитайте прогнозное значение результата, если прогнозное значение фактора увеличится на 10% от его среднего уровня. Определите доверительный интервал прогноза для уровня значимости Уровень значимости 0,05.

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

 

Решение:

Решим данную задачу с помощью Excel.


1. Сопоставив имеющиеся данные х и у, например, ранжировав их в порядке возрастания фактора х, можно наблюдать наличие прямой зависимости между признаками, когда увеличение среднедушевого прожиточного минимума увеличивает среднедневную заработную плату. Исходя из этого, можно сделать предположение, что связь между признаками прямая и её можно описать уравнением прямой. Этот же вывод подтверждается и на основе графического анализа.

Чтобы построить поле корреляции можно воспользоваться ППП Excel. Введите исходные данные в последовательности: сначала х, затем у.

Выделите область ячеек, содержащую данные.

Затем выберете: Вставка / Точечная диаграмма / Точечная с маркерами как показано на рисунке 1.

Поле корреляции

Рисунок 1 Построение поля корреляции

Анализ поля корреляции показывает наличие близкой к прямолинейной зависимости, так как точки расположены практически по прямой линии.

2. Для расчёта параметров уравнения линейной регрессииЛинейная функция
воспользуемся встроенной статистической функцией ЛИНЕЙН.

Для этого:

1) Откройте существующий файл, содержащий анализируемые данные;
2) Выделите область пустых ячеек 5×2 (5 строк, 2 столбца) для вывода результатов регрессионной статистики.
3) Активизируйте Мастер функций: в главном меню выберете Формулы / Вставить функцию.
4) В окне Категория выберете Статистические, в окне функция – ЛИНЕЙН. Щёлкните по кнопке ОК как показано на Рисунке 2;

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

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

5) Заполните аргументы функции:

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

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

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

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

Щёлкните по кнопке ОК;

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

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

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

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

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

Факторная сумма квадратов

Остаточная сумма квадратов

Остаточная сумма квадратов

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

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

Получили уровнение регрессии:

Уравнение линейной регрессии

Делаем вывод: С увеличением среднедушевого прожиточного минимума на 1 руб. среднедневная заработная плата возрастает в среднем на 0,92 руб.

3. Коэффициент детерминации Коэффициент детерминации означает, что 52% вариации заработной платы (у) объясняется вариацией фактора х – среднедушевого прожиточного минимума, а 48% - действием других факторов, не включённых в модель.

По вычисленному коэффициенту детерминации Коэффициент детерминации можно рассчитать коэффициент корреляции: Коэффициент корреляции.

Связь оценивается как тесная.

4. С помощью среднего (общего) коэффициента эластичности определим силу влияния фактора на результат.

Для уравнения прямой Уравнение линейной регрессии средний (общий) коэффициент эластичности определим по формуле:

Средний показатель эластичности

Средние значения найдём, выделив область ячеек со значениями х, и выберем Формулы / Автосумма / Среднее, и то же самое произведём со значениями у.

Расчёт средних значений функции и аргумента

Рисунок 5 Расчёт средних значений функции и аргумент

Расчёт среднего показателя эластичности

Таким образом, при изменении среднедушевого прожиточного минимума на 1% от своего среднего значения среднедневная заработная плата изменится в среднем на 0,51%.

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

Порядок действий следующий:

1) проверьте доступ к Пакету анализа. В главном меню последовательно выберите: Файл/Параметры/Надстройки.

2) В раскрывающемся списке Управление выберите пункт Надстройки Excel и нажмите кнопку Перейти.

3) В окне Надстройки установите флажок Пакет анализа, а затем нажмите кнопку ОК.

• Если Пакет анализа отсутствует в списке поля Доступные надстройки, нажмите кнопку Обзор, чтобы выполнить поиск.

• Если выводится сообщение о том, что пакет анализа не установлен на компьютере, нажмите кнопку Да, чтобы установить его.

4) В главном меню последовательно выберите: Данные / Анализ данных / Инструменты анализа / Регрессия, а затем нажмите кнопку ОК.

5) Заполните диалоговое окно ввода данных и параметров вывода:

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

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

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

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

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

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

Затем нажмите кнопку ОК.

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

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

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

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

Рисунок 7 Результат применения инструмента регрессия

5. Оценим с помощью средней ошибки аппроксимации качество уравнений. Воспользуемся результатами регрессионного анализа представленного на Рисунке 8.

Результат применения инструмента регрессия «Вывод остатка»

Рисунок 8 Результат применения инструмента регрессия «Вывод остатка»

Составим новую таблицу как показано на рисунке 9. В графе С рассчитаем относительную ошибку аппроксимации по формуле:

Относительная ошибка аппроксимации

Расчёт средней ошибки аппроксимации

Рисунок 9 Расчёт средней ошибки аппроксимации

Средняя ошибка аппроксимации рассчитывается по формуле:

Формула и расчёт средней ошибки аппроксимации

Качество построенной модели оценивается как хорошее, так как  Средняя ошибка аппроксимации не превышает 8 – 10%.

6. Из таблицы с регрессионной статистикой (Рисунок 4) выпишем фактическое значение F-критерия Фишера: Фактическое значение F-критерия

Табличное значение F-критерия

Поскольку Фактическое значение F-критерия больше табличногопри 5%-ном уровне значимости, то можно сделать вывод о значимости уравнения регрессии (связь доказана).

8. Оценку статистической значимости параметров регрессии проведём с помощью t-статистики Стьюдента и путём расчёта доверительного интервала каждого из показателей.

Выдвигаем гипотезу Н0 о статистически незначимом отличии показателей от нуля:

параметры уравнения и коэффициент корреляции равны нулю.

Табличное значение t-критериядля числа степеней свободы Число степеней свободы

На рисунке 7 имеются фактические значения t-статистики:

Расчётные значения t-критерия для параметров регрессии

t-критерий для коэффициента корреляции можно рассчитать двумя способами:

I способ: Расчётное значение t-критерия для коэффициента корреляции 

где Случайная ошибка коэффициента корреляции– случайная ошибка коэффициента корреляции.

Данные для расчёта возьмём из таблицы на Рисунке 7.

Расчёт t-критерия для коэффициента корреляции

II способ: Расчёт t-статистики для коэффициента корреляции

Фактические значения t-статистики превосходят табличные значения:

Сравнение расчётных и табличных значений t-критерия

Сравнение фактического и табличного t-критерия для показателя корреляции

Поэтому гипотеза Н0 отклоняется, то есть параметры регрессии и коэффициент корреляции не случайно отличаются от нуля, а статистически значимы.

Доверительный интервал для параметра a определяется как

Формула расчёта доверительного интервала для параметра а

Для параметра a 95%-ные границы как показано на рисунке 7 составили:

Доверительный интервал для параметра а

Доверительный интервал для коэффициента регрессии определяется как

Формула расчёта доверительного интервала коэффициента регрессии

Для коэффициента регрессии b 95%-ные границы как показано на рисунке 7 составили:

Доверительный интервал для коэффициента регрессии

Анализ верхней и нижней границ доверительных интервалов приводит к выводу о том, что с вероятностью Значение вероятности параметры a и b, находясь в указанных границах, не принимают нулевых значений, т.е. не являются статистически незначимыми и существенно отличны от нуля.

7. Полученные оценки уравнения регрессии позволяют использовать его для прогноза. Если прогнозное значение прожиточного минимума составит:

Расчёт прогнозного значения фактора

Тогда прогнозное значение прожиточного минимума составит:

Расчёт прогнозного значения результата

Ошибку прогноза рассчитаем по формуле:

Формула средней ошибки прогнозируемого индивидуального значения у

где Сумма квадратов отклонений фактического значения от среднего

Дисперсию посчитаем также с помощью ППП Excel. Для этого:

1) Активизируйте Мастер функций: в главном меню выберете Формулы / Вставить функцию.

2) В окне Категория выберете Статистические, в окне функция – ДИСП.Г. Щёлкните по кнопке ОК.

3) Заполните диапазон, содержащий числовые данные факторного признака. Нажмите ОК.

Расчёт дисперсии

Рисунок 10 Расчёт дисперсии

Получили значение дисперсии Дисперсия фактора

Для подсчёта остаточной дисперсии на одну степень свободы воспользуемся результатами дисперсионного анализа как показано на Рисунке 7.

Формула и расчёт остаточной дисперсии на одну степень свободы

Расчёт средней ошибки прогнозируемого индивидуального значения у

Доверительные интервалы прогноза индивидуальных значений у при Прогнозное значение фактора с вероятностью 0,95 определяются выражением:

Формула доверительного интервала прогноза индивидуальных значений у

Расчёт доверительных интервалов прогноза индивидуальных значений у

Доверительный  интервал прогноза

Интервал достаточно широк, прежде всего, за счёт малого объёма наблюдений. В целом выполненный прогноз среднемесячной заработной платы оказался надёжным.

 

Условие задачи взято из: Практикум по эконометрике: Учеб. пособие / И.И. Елисеева, С.В. Курышева, Н.М. Гордеенко и др.; Под ред. И.И. Елисеевой. – М.: Финансы и статистика, 2003. – 192 с.: ил.