Лабораторная работа №4. Лабораторная работа 3 Нелинейная парная регрессия
 Скачать 0.65 Mb.
|
|
ЛАБОРАТОРНАЯ РАБОТА № 3 Нелинейная парная регрессия. Цель: 1. Научиться аппроксимировать уравнения нелинейной регрессии. 2. Научиться подбирать вид модели, которая будет иметь наименьшую ошибку аппрокси- мации. Пример выполнения Данные о росте безработицы X , % и росте преступности Y , % приведены в таблице (рис. 1). Таблицу можно считать функцией, заданной таблично. Рис. 1 1.Методом наименьших квадратов по табличным данным найти аппроксимирующие (приближаемые) функции, то есть регрессионные модели: линейную, квадратичную, по- казательную, гиперболическую. 2. Построить линии регрессии на одной плоскости вместе с исходными данными. 3.В каждом случае найти общую, среднюю и среднеквадратическую погрешности аппроксимации. Указать модель лучшей аппроксимации. Алгоритм решения задачи Определить систему нормальных уравнений для нахождения оценок параметров линейной регрессии: 1.1 В целях удобства расчетов представить таблицу исходных данных следующим образом (рис. 2), которую дополним еще двумя расчетными столбцами: X 2 и XY Рис. 2 1.2. В верхнюю ячейку столбца 4 ввести формулу и автоматически заполнить весь столбец (путем протягивания ячейки с формулой на область заполнения). =СТЕПЕНЬ(«верхняя ячейка столбца x»;2) Вызов функции: MS Excel – Вставка – Функция… – Математические 1.3. В верхнюю ячейку столбца 5 ввести формулу и автоматически заполнить весь столбец (путем протягивания ячейки с формулой на область заполнения). =«верхняя ячейка столбца Х»*«верхняя ячейка столбца У» 1.4. Просуммируем значения столбцов:Х, У, Х 2 , ХУ с помощью функции СУММ, а результат суммирования записать под столбцом с соответствующими данными (рис. 3). Получим систему нормальных уравнений для линейной регрессии: Рис. 3 Замечание . Данную систему нормальных уравнений можно решать методом Крамера, матричным методом. Однако мы будем использовать для ее решения надстройку MS Excel Поиск решения…. 2. Решить систему нормальных уравнений для линейной регрессии. 2.1. Составить исходную табличную модель для решения системы линейных алгебраиче- ских уравнений с помощью надстройки Поиск решения... (рис. 4). Рис.4 2.2. В блок «Переменные» в первую строку записать переменные системы алгебраических уравнений. 2.3. В блок «Переменные» во вторую строку записать произвольные числовые значения (удобнее в качестве числовых значений поставить номера переменных), затем, после вы- полнения команды Поиск решения..., в этих ячейках получим исходные решения системы. 2.4. В блок «Матрица коэффициентов исходной системы» записываем соответствующую матрицу коэффициентов при переменных а 0 , а 1. 2.5. В блок «Значения левых частей уравнений» в верхнюю ячейку вводим формулу: =СУММПРОИЗВ(«фиксированный диапазон строки значений переменных а 0 , а 1 »;«диапазон первой строки матрицы коэффициентов исходной системы») 2.6. Автоматически заполняем весь столбец «Значения левых частей уравнений». 2.7. В блок «Свободные члены исходной системы» в столбец записываем значения правой части исходной системы. 2.8. Вызываем Поиск решения и заполняем форму: Вызов Поиск решения...: MS Excel – Данные – Поиск решения… Установить целевую ячейку – ничего не ставить; Равной – максимальному значению; Изменяя ячейки – диапазон «Строки значений переменных»; Ограничения – диапазон «Значения левых частей уравнений» = диапазон «Свободные члены исходной системы»; 2.8.1. Заполнить форму Результаты поиска решений: поставить опцию Сохранить найденное решение; нажать ОК. Результат выполнения команды Поиск решения… будет следующий (рис. 5) Рис. 5 2.9. Изменить формат ячеек с полученным решением (строка значений переменных) так, чтобы было три знака после запятой. Записываем уравнение линейной регрессии. Модель линейной регрессии: У = 4,200 + 0,999Х Определить систему нормальных уравнений для нахождения оценок параметров квадратичной регрессии: Дальнейшие действия выполняются аналогично пунктам 1.1 – 2.9, в результате чего полу- чаем: Модель квадратичной регрессии: У = 4,199 + 0,100х – 0,000001х 2 Определить систему нормальных уравнений для нахождения оценок пара- метров показательной регрессии: ; ; , , Возвращаясь к показательной функции, найдем параметры , из условий, ис- пользовав функцию EXP: , Дальнейшие действия выполняются аналогично пунктам 1.1 – 2.9, в результате че- го получаем: Модель показательной регрессии: Y = 4,207e 0,022x Определить систему нормальных уравнений для нахождения оценок параметров гиперболической регрессии: , Дальнейшие действия выполняются аналогично пунктам 1.1 – 2.9, в результате чего полу- чаем: Модель гиперболической регрессии: Y = 4,663 – 0,247/ x Определить погрешности аппроксимации Для нахождения общей ошибки и средней ошибки аппроксимации построить вспомога- тельную таблицу (рис. 6). 1. В верхнюю ячейку столбца 4 введем формулу и автоматически заполним столбец (протягиванием ячейки с формулой на область заполнения): = 4,200 + 0,099*«верхняя ячейка столбца x» Замечание . Вместо значений оценок параметров вводим абсолютные ссылки на ячей- ки, содержащие значения этих оценок параметров. 2. В верхнюю ячейку столбца 5 введем формулу и автоматически заполним столбец (протягиванием ячейки с формулой на область заполнения): = 4,199 + 0,100*«верхняя ячейка столбца x» - 0,000001*(«верхняя ячейка столбца x»^2) 3. В верхнюю ячейку столбца 6 введем формулу и автоматически заполним столбец (протягиванием ячейки с формулой на область заполнения): = 4,207*EXP(0,022*«верхняя ячейка столбца x») 4. В верхнюю ячейку столбца 7 введем формулу и автоматически заполним столбец (протягиванием ячейки с формулой на область заполнения): = 4,663-0,247/«верхняя ячейка столбца x» 5. В верхнюю ячейку столбца 8 введем формулу и автоматически заполним столбец (протягиванием ячейки с формулой на область заполнения): =СТЕПЕНЬ((«верхняя ячейка столбца y»-«верхняя ячейка столбца 4»);2) 6. В верхнюю ячейку столбца 9 введем формулу и автоматически заполним столбец (протягиванием ячейки с формулой на область заполнения): =СТЕПЕНЬ((«верхняя ячейка столбца y»-«верхняя ячейка столбца 5»);2) Рис.6 7. В верхнюю ячейку столбца 10 введем формулу и автоматически заполним стол- бец (протягиванием ячейки с формулой на область заполнения): =СТЕПЕНЬ((«верхняя ячейка столбца y»-«верхняя ячейка столбца 6»);2) 8. В верхнюю ячейку столбца 11 введем формулу и автоматически заполним стол- бец (протягиванием ячейки с формулой на область заполнения): =СТЕПЕНЬ((«верхняя ячейка столбца y»-«верхняя ячейка столбца 7»);2) 9. В верхнюю ячейку столбца 12 введем формулу и автоматически заполним стол- бец (протягиванием ячейки с формулой на область заполнения): =ABS((«верхняя ячейка столбца y»-«верхняя ячейка столбца 4»)/«верхняя ячейка столбца y») 10. В верхнюю ячейку столбца 13 введем формулу и автоматически заполним стол- бец (протягиванием ячейки с формулой на область заполнения): =ABS((«верхняя ячейка столбца y»-«верхняя ячейка столбца 5»)/«верхняя ячейка столбца y») 11. В верхнюю ячейку столбца 14 введем формулу и автоматически заполним стол- бец (протягиванием ячейки с формулой на область заполнения): =ABS((«верхняя ячейка столбца y»-«верхняя ячейка столбца 6»)/«верхняя ячейка столбца y») 12. В верхнюю ячейку столбца 15 введем формулу и автоматически заполним стол- бец (протягиванием ячейки с формулой на область заполнения): =ABS((«верхняя ячейка столбца y» - «верхняя ячейка столбца 7»)/«верхняя ячейка столбца y») 13. Просуммируем значения столбцов 4 – 11 с помощью функции СУММ, а резуль- тат суммирования запишем под столбцом с соответствующими данными. В ячейках под столбцами 12 – 15 введем формулы и придадим этим ячейкам формат Про- центный: =1/7*СУММ(«диапазон значений ячеек соответствующего столбца 12 – 15»)*100% Замечание . Суммы значений столбцов 8 – 11 – это общие ошибки линейной, квадратич- ной, показательной, гиперболической регрессий соответственно. Суммы значений столб- цов 12 – 15 – это средние ошибки аппроксимации линейной, квадратичной, показатель- ной, гиперболической регрессий соответственно. 14. Укажем функцию наилучшей аппроксимации по общей ошибке и по средней ошибке аппроксимации, используя функции ЕСЛИ и МИН. Для определения регрессии с минимальной общей ошибкой введем формулу: =ЕСЛИ(«ячейка суммы столбца y-линейное»=МИН(«диапазон ячеек сумм регрес- сий»);"y-линейное"; ЕСЛИ(«ячейка суммы столбца y-квадратичное»=МИН(«диапазон ячеек сумм регрессий»);"y-квадратичное"; ЕСЛИ(«ячейка суммы столбца y- показательное»=МИН(«диапазон ячеек сумм регрессий»);"y-показательное"; ЕСЛИ(«ячейка суммы столбца y-гиперболическое»=МИН(«диапазон ячеек сумм регрес- сий»);"y-гиперболическое"))) Для определения регрессии с минимальной средней ошибкой аппроксимации вве- дем формулу: =ЕСЛИ(«ячейка со значением средней ошибки аппроксимации y-ли- нейное»=МИН(«диапазон ячеек со значениями всех средних ошибок аппроксимации»);"y- линейное";ЕСЛИ(«ячейка со значением средней ошибки аппроксимации y- квадратичное»=МИН(«диапазон ячеек со значениями всех средних ошибок аппроксима- ции»);"y-квадратичное"; ЕСЛИ(«ячейка со значением средней ошибки аппроксимации y- показательное»=МИН(«диапазон ячеек со значениями всех средних ошибок аппроксима- ции»);"y-по-казательное";"y-гиперболическое"))) Вывод. Линейная функция – функция наилучшей аппроксимации. Следовательно, по вы- бранному критерию наилучшей является модель линейной функции. Построить линии регрессии в одной плоскости вместе с исходными данными Замечание. Если в таблице (рис. 6) значения аргумента функции не упорядочены по воз- растанию, то мастер диаграмм не отразит правильно полученные регрессии. Для упорядо- чивания данных столбца 2 необходимо поставить автофильтры и отфильтровать по воз- растанию. MS Excel – Данные – Фильтр – Автофильтр Прежде чем поставить автофильтр, необходимо предварительно выделить диапазон ячеек, включающий название столбцов и их значения (рис. 7). Рис. 7 Построить линии регрессии в одной плоскости вместе с исходными данными (рис.8) Рис. 8 Контрольные вопросы 1. Что означает аппроксимация функции? 2. Для чего применяется метод наименьших квадратов? В чем его суть? 3. С помощью каких показателей можно судить о лучшей аппроксимации функции? 4. Перечислите этапы построения математической модели по разобранному алго- ритму 5. Как вычисляется средняя ошибка аппроксимации? Что она характеризует? 6. Приведите пять примеров возможных аппроксимирующих функции, кроме тех, что рассмотрены в задаче. 8. Что произойдет с уровнем преступности при увеличении уровня безработицы, если исходить из графического изображения линейной регрессии? |