Проведение вычислений с использованием инструмента «Регрессия» Microsoft Excel

Оценку коэффициентов уравнения линейной регрессии, проверку значимости каждого коэффициента, значимости всего уравнения и построение интервальных оценок можно выполнить автоматически на компьютере, используя средство «Пакет анализа» Microsoft Excel (инструмент «Регрессия»).

Возьмем исходные данные примера 3.1 и введем их в таблицу Microsoft Excel (рис. 3.1).

Рис. 3.1. Исходные данные, введенные в таблицу Microsoft Excel.

Выбираем команду Данные —> Анализ данных —" Регрессия.

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

Входной интервал у — вводим ссылку на ячейки, содержащие данные по результирующему признаку (ячейки С2 — С11).

Входной интервал х — вводим ссылку на ячейки, содержащие данные по объясняющему признаку (ячейки В2 — В11).

Метки — установить «галочку», если в результат введем заголовки столбцов хи у (ячейки В1 и С1).

Константа-ноль — установить «галочку», если регрессионное уравнение оценивается без свободного члена (в нашем уравнении регрессии свободный член есть, это коэффициент Ь₀, следовательно, галочку ставить не нужно).

Выходной интервал — вводим ссылку на ячейку, с которой начнется вывод данных инструмента «Регрессия». Можем вывести результат на этом листе, на новом рабочем листе или в новой рабочей книге.

После введения всех задаваемых параметров нажимаем кнопку О К (рис. 3.2).

Рис. 3.2.

Введение

параметров регрессии

Результаты расчетов выводятся на экран в трех таблицах под названием «Вывод итогов» (рис. 3.3).

Рис. 3.3. Вывод итогов регрессионного анализа.

Первая таблица. Для парной линейной регрессии важно: вторая строчка — коэффициент детерминации R² — 0,925; четвертая строчка — стандартная ошибка — 2,662; пятая строчка — число наблюдений — 10.

Здесь «стандартная ошибка» — стандартное отклонение (5_е —.

стандартная ошибка — выборочное среднее квадратическое отклонение). Вторая таблица. Столбец «df» — число степеней свободы v: для строки «Регрессия» равно числу объясняющих переменных в уравнении — q_r = т

для строки «Остаток» — q_e— п — (т + 1); для строки «Итого» — q — q,.+ q_e.

Столбец «55» — сумма квадратов отклонений:

для строки «Регрессия» —

для строки «Остаток» —

для строки «Итого» —

Столбец «MS»:

для строки «Регрессия» — факторная дисперсия (дисперсия объясняющего фактора Q,. / т);

для строки «Остаток» — остаточная дисперсия (дисперсия остатков Ое/Яе) —

Столбец «Р» — 5-статистика.

Третья таблица. Столбец «Коэффициенты» — оценки коэффициентов эмпирического уравнения парной линейной регрессии у_> = b₍₎ + b_lx_j.

Столбец «Стандартная ошибка» — стандартные ошибки соответствующих коэффициентов.

Столбец «t-статистика» — статистическая значимость соответствующих коэффициентов.

Столбец «P-Значение» — это значения уровней значимости, соответствующие вычисленным t-статистикам.

Столбцы «Нижние 95%» и «Верхние 95%» задают допустимые границы значений коэффициентов уравнения регрессии при доверительной вероятности у = 0,95. Если требуется найти допустимые границы при другом значении доверительной вероятности, то при введении параметров регрессии (см. рис. 3.2) следует поставить галочку в окошко «Уровень значимости» и ввести значение доверительной вероятности в процентах. Теперь на рис. 3.3 вторые «Нижние» и «Верхние» покажут границы доверительного интервала при другом значении доверительной вероятности (первые «Нижние» и «Верхние» останутся без изменений для доверительной вероятности у = 0,95).

С остальными командами диалогового окна «Регрессия» познакомимся далее. Подробное разъяснение всех результатов работы инструмента «Регрессия» приведено в приложении 1.

В результате компьютерных расчетов с помощью диалогового окна «Регрессия» получены следующие результаты (все данные округлены до трех знаков после запятой):

уравнение парной линейной регрессии у_г = 3,295 + 2,283дг/ коэффициент детерминации R² = 0,925; статистическая значимость уравнения F= 99,690; коэффициент Ь₀: стандартная ошибка =3,266,^{-статистика} 1,008, доверительный интервал при уровне значимости, а = 0,05 (-4,239; 10,824);

коэффициент Ь_{: стандартная ошибка =0,228,^{-статистика} -9,984, доверительный интервал при уровне значимости, а = 0,05 (-1,756; 2,810).

Очевидно, что есть небольшое расхождение с результатами, полученными ранее аналитическим путем. Это легко объясняется тем, что аналитически при делении и последующем умножении производилось округление до трех знаков после занятой, компьютер вычисляет с гораздо большей точностью.

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