НЕЛИНЕЙНАЯ РЕГРЕССИЯ
Лабораторная работа № 4
Цель: освоить методы построения основных видов нелинейных уравнений парной регрессии с помощью с помощью ЭВМ (внутренне линейные модели), научиться получать и анализировать показатели качества регрессионных уравнений.
Рассмотрим случай, когда нелинейные модели с помощью преобразования данных можно свести к линейным (внутренне линейные модели).
ПРИМЕР. Некоторая организация желает исследовать зависимость полученной прибыли Y (сотни тыс. руб.) от вложения средств в научные разработки выпускаемой продукции X (тыс. руб.). Для этого рассматриваются 4 регрессионных уравнения: линейное:
y = ax + b, гиперболическое y = a/x + b, экспоненциальное y = a·ebx и степенное y = a·xb. В результате наблюдений, получены данные:
| Прибыль Y | |||||||||||
| Прибыль X |
Введем данные в таблицу вместе с подписями (ячейки A1-L2). Оставим свободными три строчки ниже таблицы для ввода преобразованных данных, выделим первые пять строк, проведя по левой серой границе по числам от 1 до 5 и выбрать какой-либо цвет (светлый – желтый или розовый) раскрасить фон ячеек.
Далее, начиная с A6, выводим параметры линейной регрессии. Для этого в ячейку A6 делаем подпись «Линейная» и в соседнюю ячейку B6 вводим функцию ЛИНЕЙН (категория «Статистические», см. предыдущую лабораторную работу). В полях для значений у и х даем ссылку на B1-L1 и B2-L2, следующие два поля принимают значения по единице. Далее обводим область ниже в 5 строчек и левее в 2 строки и нажимаем F2 и Ctrl+Shift+Enter. Результат - таблица с параметрами регрессии, из которых наибольший интерес представляет коэффициент детерминации в первом столбце третий сверху. В нашем случае он равен R1 = 0,90627178. Значение F-критерия, позволяющего проверить адекватность модели F1 = 87,02230833 (четвертая строка, первый столбец). Уравнение регрессии равно y = 3,154x – 11,992 (коэффициенты a и b приведены в ячейках B6 и C6).
Определим аналогичные характеристики для других регрессий и в результате сравнения коэффициентов детерминации найдем лучшую регрессионную модель.
Рассмотрим гиперболическую регрессию. Для ее получения преобразуем данные. В третьей строке в ячейку A3 введем подпись «1/x» а в ячейку B3 введем формулу «=1/B2». Растянем автозаполнением данную ячейку на область B3-L3. Получим характеристики регрессионной модели.
В ячейку А12 введем подпись «Гипербола», а в соседнюю функцию ЛИНЕЙН. В полях для значений у и х даем ссылку на B1-L1 и преобразованные данные аргумента x – B3-L3, следующие два поля принимают значения по единице. Далее обводим область ниже 5 строчек и левее в 2 строки и нажимаем F2 и Ctrl+Shift+Enter. Получаем таблицу параметров регрессии. Коэффициент детерминации в данном случае равен R2 = 0,345994664, что намного хуже, чем в случае линейной регрессии. F-статистика равна F2 = 4,761355604. Уравнение регрессии равно y = -106,34/x + 42,76.
Рассмотрим экспоненциальную регрессию. Для ее линеаризации получаем уравнение 
, где ỹ = ln y, ã = b, 
= ln a. Видно, что надо сделать преобразование данных – y заменить на ln y. Ставим курсор в ячейку А4 и делаем заголовок «ln y». Ставим курсор в В4 и вводим формулу LN (категория «Математические»). В качестве аргумента делаем ссылку на В1. Автозаполнением распространяем формулу на четвертую строку на ячейки В4-L4.
Далее в ячейке F6 задаем подпись «Экспонента» и в соседней G6 вводим функцию ЛИНЕЙН, аргументами которой будут преобразованные данные В4-L4 в поле для значений у, а остальные поля такие же как и для случая линейной регрессии (B2-L2, 1, 1). Далее обводим ячейки G6-H10 и нажимаем F2 и Ctrl+Shift+Enter. Результат R3 = 0,979276, F3 = 425,2748, что говорит об очень хорошей регрессии.
Для нахождения коэффициентов уравнения регрессии b = ã; 
ставим курсор в J6 и делаем заголовок «а=», а в соседней К6 формулу «=ЕХР(Н6)», в J7 даем заголовок «b=», а в К7 формулу «=G6». Уравнение регрессии есть y = 3,956 · e0,125x.
Рассмотрим степенную регрессию. Для ее линеаризации получаем уравнение ỹ = ã 
, где ỹ = ln y, 
= ln x, ã = b, = ln a. Видно, что надо сделать преобразование данных – y заменить на ln y и x заменить на ln x. Строчка с ln y у нас уже есть. Преобразуем переменные х. В ячейку А5 даем подпись «ln x», а в В5 и вводим формулу LN (категория «Математические»). В качестве аргумента делаем ссылку на В2. Автозаполнением распространяем формулу на пятую строку на ячейки B5-L5. Далее в ячейке F12 задаем подпись «Степенная» и в соседней G12 вводим функцию ЛИНЕЙН, аргументами которой будут преобразованные данные B4-L4 (в поле для у), и B5-L5 (в поле для х), остальные поля – единицы. Далее освободим ячейки G12-H16 и нажимаем F2 и Ctrl+Shift+Enter. Результат R4 = 0,895786, F4 = 77?36103, что говорит об хорошей регрессии. Для нахождения коэффициентов уравнения регрессии b = ã; ставим курсор в J12 и делаем заголовок «а=», а в соседней К12 формулу «=ЕХР(Н12)», в J13 даем заголовок «b=», а в К13 формулу «=G12». Уравнение регрессии есть у = 1,133 · х1,157.
Проверим, все ли уравнения адекватно описывают данные. Для этого нужно сравнить F-статистики каждого критерия с критическим значением. Для его получения вводим в А21 подпись «F-критическое», а в В21 функцию FРАСПОБР, аргументами которой вводим соответственно «0,05» (уровень значимости), «1» (число факторов Х в строке «Уровень значимости 1») и «9» (степень свободы 2 = n – 2). Результат 5,117357. Видно, что F – критическое в большинстве своем меньше F-статистики (F-критерия), значит эти модели адекватны. А гиперболическая регрессия неадекватна, т.к. F2<F10.
Для того, чтобы определить, какая модель наилучшим образом описывает данные, сравним индексы детерминации для каждой модели R1, R2, R3, R4. Наибольшим является R3 = 0,979276. Значит опытные данные лучше описывать моделью у = 3,956 · е0,125х.