Материал: Введение в эконометрику10

2. Решение типовой задачи в среде Excel

2.1. Постановка задачи*

Продолжаем исследовать зависимость добычи угля на 1 рабочего (Y) от толщины угольного пласта (теперь этот фактор будем обозначать Х₁) и от нового фактора – уровня механизации работ (Х₂) по дан­ным таблицы 6 (которая отличается от таблицы 1 только до­бавленными значениями Х₂).

Таблица 6. Зависимость добычи угля от двух факторов

Требуется:

1. Определить параметры линейной регрессии. Пояснить их смысл. Проанализировать значения остаточной, общей и регрессионной сумм квадратов. Оценить на уровне =0,05 значимость уравнения регрессии, используя F-статистику.

2. Определить стандартизированные коэффициенты регрессии и коэффициенты эластичности. Пояснить их смысл.

3. Сравнить значения коэффициента детерминации с ситуацией р=1 (см. работу №1). Повысилось ли качество модели при добавлении второй объясняющей переменной? Существенно ли изменило качество модели добавление Х₂?

4. Вычислить значения нормированного коэффициента детерминации для случаев р=1 и р=2; анализируя полученные значения, ответить на вопросы п.3;

5. Проверить гипотезу о незначимости коэффициентов уравнения регрессии для уровня значимости 0,05. Ответить с новой точки зрения на вопросы п.3.

6. Найти с надежностью 0,95 интервальные оценки коэффициентов регрессии  и дисперсии возмущений.

7. Повторить расчеты, используя пакет анализа Excel.

2.2. Выполнение задания в среде Excel с помощью функции линейн

2.2.1. Применение функции линейн для множественной регрессии

Д ля результатов функции ЛИНЕЙН необходимо выделить область пус­тых ячеек из 5 строк и (p+1) столбцов (p – число факторов). Вызов окна функ­ции ЛИНЕЙН и его заполнение производится так же, как для парной регрес­сии; отличие состоит в том, что значения x представляют собой диапазон из p строк (или столбцов). Если параметр Статистика установлен в значение 1, то результаты будут выданы, как показано в таблице 7. Результаты функции ЛИНЕЙН для рассматриваемых данных приведены в таблице 8.

Получили следующие оценки коэффициентов множественной регрессии: b₀=-3,54, b₁=0,854, b₂=0,367. Смысл b₁: при увеличении только мощности пласта x₁ на 1 м (при неизменном x₂) добыча угля на 1 рабочего Y возрастет в среднем на 0,854 усл. ед.

Смысл b₂: при увеличении только уровня механизации работ x₂ на 1% (при неизменном x₁) Y возрастет в среднем на 0, 367 усл. ед.

Коэффициент b₀ не имеет практического смысла, так как при x₁= x₂=0 (см. формулу (27)), а нулевые значения факторов нереальны (далеко отстоят от интервала наблюдений).

Сравнение регрессионной (Q_R=27,27) и остаточной (Q_e=6,33) сумм говорит о значимости уравнения регрессии. Более обоснованное суждение о значимости уравнения можно вынести, используя критерий Фишера. Из таблицы 8 возьмем значение статистики Фишера: F=15,08. Пороговое значение (квантиль распределения Фишера уровня 1- с числом степеней свободы k1=p=2 и k2=n-p-1=7) определим с помощью функции FРАСПОБР: f(0,05;2;7)=4,74. Так как статистика F больше порога (неравенство (35) выполнено), то уравнение значимо.

2.2.2. Анализ стандартизированных коэффициентов регрессии и коэффициентов эластичности

Вычисления коэффициентов проводятся по формулам (29), (30). Предварительно надо рассчитать выборочные средние (для чего удобно использовать функцию СРЗНАЧ) и средние квадратичные отклонения (для вычисления суммы квадратов отклонений наблюдений от средних значений можно применить функцию КВАДРОТКЛ). В результате получим:

Анализируя полученные значения, можно сделать следующие выводы. Увеличение только мощности пласта на одно значение приводит к возрастанию добычи угля на 1 рабочего в среднем на 0,728s_y; увеличение только уровня механизации работ на одно значение приводит к возрастанию добычи угля на 1 рабочего в среднем на 0,285s_y.

Увеличение первого фактора на 1% (от среднего значения) приводит в среднем к росту добычи угля на 1,18%, а увеличение второго фактора на 1% приводит к росту добычи на 0,34%.

Таким образом, величина добычи угля сильнее зависит от мощности пласта, чем от уровня механизации работ.

2.2.3. Анализ значимости уравнения регрессии с помощью коэффициента детерминации

Из таблицы 8 имеем значение коэффициента детерминации: R²=0,812. Это достаточно большое значение говорит о значимости уравнения регрессии: изменение отклика на 81,2% объясняется изменениями двух включенных в модель факторов. Для модели с одним фактором – мощностью угольного пла­ста – коэффициент детерминации был равен 0,750 (см. таблицу 3). По-види­мому, добавление второго фактора – уровня механизации работ – не привело к существенному улучшению модели.

Известно, что добавление даже незначимого фактора приводит к увели­чению коэффициента детерминации; поэтому для сравнения моделей с различ­ным числом объясняющих переменных обычно используется нормированный коэффициент детерминации .

Проведя расчеты по формуле (33), получим: при p=1 =0,719, при p=2 =0,758. Такое увеличение , скорее всего, слиш­ком мало для того, чтобы говорить об улучшении качества модели при включении в нее второго фактора.

2.2.4. Проверка значимости коэффициентов регрессии

Из таблицы 8 имеем: b₁=0,854; b₂=0,367; =0,221; =0,243. Подста­вив эти значения в формулу (36а), получим T₁=3,87; T₂=1,51. Квантиль распре­деления Стьюдента рассчитаем с помощью функции СТЬЮДРАСПОБР: t(0,05;7)=2,36. Проверяя неравенство (37), получаем: первый фактор (мощность пласта) значим, а второй (уровень механизации) незначим. Этот результат под­тверждает выводы предыдущих параграфов.

2.2.5. Доверительные интервалы коэффициентов регрессии и дисперсии возмущений

Доверительный интервал будем строить только для значимого коэффициента ₁. Подставляя значения параметров из §2.2.4 в неравенство (38), получим, что с вероятностью 0,95 выполняется неравенство: 0,333≤₁≤1,375.

Расчет доверительного интервала дисперсии возмущений проведем по формуле (39). Из таблицы 8 Q_e=6,33. С помощью функции ХИ2ОБР для =0,95 вычислим квантили: ²(0,025;7)=16,01; ²(0,975;7)=1,69. Получим доверительный интервал надежности 0,95: 0,395≤²≤3,746.

2.3. Выполнение задания с помощью пакета анализа Excel

В среде Excel оценить уравнение регрессии и исследовать его значимость можно не только с помощью функции ЛИНЕЙН, но и используя пакет анализа. Этот пакет вызывается командой горизонтального меню Сервис/Анализ данных. Если пакет не доступен, то следует его установить с помощью ко­манды Сервис/Надстройки/Пакет анализа. В окне пакета надо выбрать возможность Регрессия. Правила заполнения окна Регрессия достаточно очевидны, при возникновении трудностей можно воспользоваться справкой этого окна. Здесь будут рассмотрены лишь некоторые особенности применения окна Регрессия.

С помощью окна Регрессия можно обрабатывать только вертикально ориентированные таблицы данных (в которых значения переменных расположены в столбцах). Если таблица ориентирована горизонтально (как в нашем примере), то ее необходимо транспонировать с помощью функции ТРАНСП. Порядок работы с функцией ТРАНСП следующий: выделяем область таблицы-результата (в нашем случае 11 строк, 4 столбца); вызываем функцию ТРАНСП (например, вызываем мастер функций командой Вставка/Функция, а в окне мастера выбираем ТРАНСП); в окно Массив функции мышью вводим диапазон исходной таблицы; завершаем работу с функцией ТРАНСП нажатием клавиш Ctrl+Shift+Enter.

В окне Регрессия указываются диапазоны значений отклика и факторов; флажок Метки ставится в том случае, если эти диапазоны содержат заголовки столбцов. Флажок Константа-ноль ставится, если предполагается ₀=0, флажок Уровень надежности – если расчет доверительных интервалов следует проводить не только для стандартного значения надежности γ=95%, но и для другого значения γ. В простейшем случае другие флажки можно не ставить. В качестве выходного интервала достаточно задать самую левую верхнюю ячейку диапазона результатов. Заполнив таким образом окно Регрессия и нажав кнопку OK, получим результаты, представленные в таблицах 9-11. Прямым шрифтом в таблицах показана информация, выведенная Excel, курсивом приведены термины и обозначения, принятые в этом данном пособии.

3. Задание на самостоятельную работу*

Предприниматель намерен использовать множественный регрессионный анализ для оценки стоимости офисного здания в заданном районе, используя данные таблицы 12. Для этого предлагается выполнить следующую работу:

1. Определить уравнение регрессии и его характеристики.

2. Проанализировать значения коэффициентов детерминации (стандартного и адаптированного). Можно ли говорить о сильной зависимости между объясняющими переменными и стоимостью здания?

3. Проверить значимость уравнения регрессии по критерию Фишера при уровне значимости 0,05.

4. Построить доверительные интервалы для коэффициентов уравнения регрессии.

5. Проверить значимость коэффициентов уравнения регрессии при уровне значимости 0,05. Все ли факторы полезны при оценке стоимости здания?

Таблица 12. Данные по стоимости офисных зданий