Парная и множественная регрессия и корреляция
Рисунок 3 Диалоговое окно Аргументы функции
Щелкаю по кнопке ОК;
6) в левой верхней ячейке выделенной области появился первый элемент итоговой таблицы. Чтобы раскрыть всю таблицу, нажимаю на клавишу <F2>, а затем – на комбинацию клавиш <CTRL> + <SHIFT> + <ENTER>.
Дополнительная регрессионная статистика будет выводиться в порядке, указанном в следующей схеме:
| Значение коэффициента b | Значение коэффициента a |
| Среднеквадратическое отклонение b | Среднеквадратическое отклонение a |
| Коэффициент детерминации R2 | Среднеквадратическое отклонение y |
| F – статистика | Число степеней свободы |
| Регрессионная сумма квадратов | Остаточная сумма квадратов |
Результаты вычислений функции ЛИНЕЙН представлены на рисунке 4.

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

Рисунок 5 Диалоговое окно режима Регрессия
Результаты регрессионного анализа для исходных данных представлены на рисунке 6.
Вывод итогов
| Регрессионная статистика | |
| Множественный R | 0,19101862 |
| R-квадрат | 0,03648811 |
| Нормированный R-квадрат | -0,1240972 |
| Стандартная ошибка | 0,74755394 |
| Наблюдения | 8 |
Дисперсионный анализ
| df | SS | MS | F | Значимость F | |
| Регрессия | 1 | 0,12697864 | 0,126978637 | 0,2272195 | 0,6504571 |
| Остаток | 6 | 3,35302136 | 0,558836894 | ||
| Итого | 7 | 3,48 |
| Коэффициенты | Стандартная ошибка | t-статистика | P-Значение | Нижние 95% | Верхние 95% | |
| Y-пересечение | 8,271668362 | 3,844685341 | 2,151455224 | 0,07495556 | 1,135937744 | 17,67927447 |
| Переменная X 1 | 0,160732452 | 0,337194727 | 0,476675461 | 0,6504571 | 0,664353319 | 0,985818223 |
Вывод остатка
| Наблюдение | Предсказанное Y | Остатки | Стандартные остатки |
| 1 | 9,846846389 | 0,353153611 | 0,510263509 |
| 2 | 10,08794507 | 0,012054934 | 0,017417896 |
| 3 | 10,12009156 | -0,020091556 | -0,029029827 |
| 4 | 10,08794507 | -0,887945066 | -1,28297135 |
| 5 | 10,02365209 | 0,676347915 | 0,977239505 |
| 6 | 10,10401831 | -1,104018311 | -1,595170599 |
| 7 | 10,29689725 | 0,103102747 | 0,148970781 |
| 8 | 10,23260427 | 0,867395727 | 1,253280084 |
2. Нелинейные модели регрессии и их линеаризация
Цель работы: ознакомиться с методикой расчета показателей парной нелинейной регрессии и корреляции, овладеть приемами построения нелинейных регрессионных моделей с помощью MS Exсel.
РЕШЕНИЕ:
2.1.1 Регрессия в виде степенной функции имеет вид:
.
Для оценки параметров модели линеаризую (привожу к
линейному виду) модель путем логарифмирования:
.
Обозначаю lny =Y, lna =A, lnx =X.
Тогда получаю: Y=A+bX.
Для расчетов составляю с помощью MS Excel вспомогательную таблицу, в которой рассчитаю натуральные логарифмы с помощью математической функции LN (рисунок 7).

Рисунок 7 Расчет натуральных логарифмов
Далее с помощью инструмента Регрессия рассчитываю параметры уравнения (рисунки 8, 9).

Рисунок 8 Диалоговое окно Регрессия

Рисунок 9 Результаты расчета параметров степенной функции
Таким образом, уравнение регрессии имеет вид:
.
Выполнив потенцирование, получим:
.
Параметр b=0,151 означает коэффициент эластичности, который показывает, что с ростом величины среднедушевых доходов населения на 1% общий коэффициент рождаемости увеличится в среднем на 0,151%.
2.1.2 Регрессия в виде экспоненты имеет вид:
. (13)
Для оценки ее параметров необходимо привести уравнение к линейному виду:
.
Для расчета параметров экспоненциальной прямой можно воспользоваться статистической функцией ЛГРФПРИБЛ MS Excel. Результаты вычислений представлены на рисунке 10.


Рисунок 10 Результаты вычислений параметров экспоненциальной функции
Таким образом, уравнение регрессии в виде экспоненты имеет вид:
.
2.1.3 Регрессия в виде равносторонней гиперболы имеет вид:
,
чтобы оценить параметры a и b, привожу модель к линейному виду, заменив
.
Тогда
.
Результаты замены представлены на рисунке 11.

Рисунок 11 Вспомогательная таблица для расчета параметров гиперболы
Далее с помощью инструмента Регрессия рассчитываю параметры уравнения. Результаты расчета представлены на рисунке 12.

Рисунок 12 Результаты вычислений параметров гиперболической функции
Выберем наилучшую модель, для чего объединим результаты построения парных регрессий в одной таблице 3.
Все уравнения регрессии достаточно хорошо описывают исходные данные.
Таблица 3 Результаты корреляционно-регрессионного анализа
| Уравнение регрессии | Коэффициент корреляции | Коэффициент детерминации | F-критерий Фишера |
|
|
0,659 | 0,036 | 0,227 |
|
|
0,161 | 0,026 | 0,159 |