Выберите меню Данные, Анализ данных, Регрессия.
Поместите курсор в окно Входной интервал Y и обведите мышью столбец с данными, результативного признака (Валовым региональным продуктом), включая и заголовок столбца.
Поместите курсор в окно Входной интервал X и обведите мышью все столбцы с данными факторных признаков, включая и заголовки столбцов.
Активизируйте опцию Метки в первой строке.
Опция Уровень надёжности по умолчанию настроена на 5% ошибку. Если потребуется другая точность вычисления, следует указать её.
ОК
На новом листе отображается таблица:
В таблице Регрессионная статистика сгенерированы результаты по регрессионной статистике. Эти результаты соответствуют следующим статистическим показателям:
Множественный R – коэффициенту корреляции R;
R-квадрат — коэффициенту детерминации R2;
Стандартная ошибка — остаточному стандартному отклонению
Наблюдения — числу наблюдений п.
В таблице Дисперсионный анализ сгенерированы результаты дисперсионного анализа, которые используются для проверки значимости коэффициента детерминации R-квадрат.
Столбцы имеют следующую интерпретацию:
Столбец df — число степеней свободы.
Для строки Регрессия число степеней свободы определяется количеством факторных признаков m в уравнении регрессии kф=m=5.
Для строки Остаток число степеней свободы определяется числом наблюдений n и количеством переменных в уравнении регресси: kо = п – (m+1)=79 – 6=73.
Для строки Итого число степеней свободы определяется суммой kY = kФ + kо=5+73=78.
Столбец SS — сумма квадратов отклонений.
Для строки Регрессия — это сумма квадратов отклонений теоретических данных от среднего:
Для строки Остаток – это сумма квадратов отклонений эмпирических данных от теоретических:
Для строки Итого — это сумма квадратов отклонений эмпирических данных от среднего: или
Столбец MS – дисперсии, рассчитываемые по формуле:
Для строки Регрессия – это факторная дисперсия.
Для строки Остаток – это остаточная дисперсия .
Столбец F – расчетное значение F-критерия Фишера вычисляемое по формуле
Столбец Значимость F– значение уровня значимости соответствующее вычисленному значению Fp. Определяется с помощью функции
FPACП (Fp; df(регрессия);df(остаток)).
Поскольку Значимость F = 1,23462E-25 меньше F= 65,7003778, уравнение регрессии значимо.
В следующей таблице сгенерированы значения коэффициентов регрессии аi,- и их статистические оценки.
Столбцы имеют следующую интерпретацию:
Коэффициенты — значения коэффициентов аi.
Стандартная ошибка — стандартные ошибки коэффициентов ai
t-статистика — расчетные значения t-критерия, вычисляемые по формуле
5. Р-значение – значения уровней значимости, соответствующие вычисленным значениям tp. Определяются с помощью функции
=СТЬЮДРАСП(tp;n-m-1).
6. Нижние 95 % и Верхние 95 % — соответственно нижние и верхние границы доверительных интервалов для коэффициентов регрессии. Для нахождения границ доверительных интервалов с помощью функции = СТЬДРАСПОБР (tp; n – т – 1) рассчитывается критическое значение t-критерия tкp, а затем по формулам
Нижние 95% = Коэффициент – Стандартная ошибка*tкр;
Верхние 95% = Коэффициент + Стандартная ошибка*tкр.
вычисляются соответственно нижние и верхние границы доверительных интервалов.
Анализ сгенерированных таблиц.
Рассчитанные ячейки коэффициенты регрессии аi,- позволяют построить уравнение, выражающее зависимость Валового регионального продукта на душу населения Y от величины Итогов миграции населения X1 , Количества правонарушений Х2, Средней начисленной заработной платы, руб. Х3, Денежных доходов на душу населения, млн.руб. Х4, Объема промышленного производства, млн. руб. Х5:
ŷ=-13774,24 – 0,51X1– 2,64Х2 + 8,01Х3+ 8,93Х4 + 4,65Х5
Значение множественного коэффициента детерминации R2 = 0,818182674 показывает, что 81,82 % общей вариации результативного признака объясняется вариацией факторных признаков Х1, Х2, Х3, Х4, Х5, а на 18,18 % другими неучтёнными факторами. Значит, выбранные факторы существенно влияют на прибыль предприятий, что подтверждает правильность их включения в построенную модель.
Рассчитанный уровень значимости = 1,23462E-25 < 0,05 (показатель Значимость F) подтверждает значимость R2.
Следующим этапом является проверка значимости коэффициентов регрессии: а0, а1…а5.
Сравнивая попарно элементы массивов Коэффициенты и Стандартная ошибка, видим, что коэффициент регрессии а1= -0,513940943 по абсолютной величине меньше, чем его стандартная ошибка = 0,625850921. Таким образом, фактор X1 следует исключить из уравнения регрессии.
Стандартные ошибки остальных коэффициентов аi меньше своих стандартных ошибок. Но не все они являются значимыми, о чем можно судить по значениям показателя Р-значение, которые должны быть меньше заданного уровня значимости α = 0,05. Таким незначимым является свободный член уравнения регрессии (коэффициент в строке Y-пересечение), его значимость 0,24906735 больше 0,05 и фактор X2 (Выявлено правонарушений) 0,1036179.
Подводя итог предварительному анализу уравнения регрессии, можно сделать вывод, что его целесообразно пересчитать без фактора X1, и X2, и свободного члена, которые не является статистически значимыми. В диалоговом окне Регрессия необходимо задать новые параметры, и следует активизировать флажок Константа-ноль (для исключения свободного члена).
Пересчитывая значения уравнения регрессии без свободного члена, а также фактора Х1 и Х2
получаем следующую таблицу:
Сравнивая попарно элементы массивов Коэффициенты и Стандартная ошибка, видим, что абсолютные значения факторов аi больше, чем их стандартные ошибки. К тому же эти коэффициенты являются значимыми, о чем можно судить по значениям показателя Р-значение, которые меньше заданного уровня значимости α = 0,05. Значения #Н/Д в строке Y-пересечение, означает отсутствие свободного члена в уравнении регрессии.
Таким образом, получаем новое уравнение регрессии:
ŷ=7,31Х4 + 7,28Х5+ 4,19Х6
Экономическая сущность коэффициентов аi в полученном уравнении регрессии состоит в том, что они показывают степень влияния каждого фактора на результативный признак (Внутренний региональный продукт на душу населения). Так, увеличение Средней начисленной заработной платы на 1 рубль ведет к ведёт в увеличению Внутреннего регионального продукта на душу населения на 7,31 руб., увеличение Денежных доходов населения на 1 млн. руб. ведет к росту Внутреннего регионального продукта на душу населения на 7,28 руб., увеличение Объёма промышленного производства на 1 млн. рублей ведет к росту Внутреннего регионального продукта на душу населения на 4,19 руб.