Практическая часть
Постановка задачи
Для заданного набора пар значений независимой переменной и функции (табл. 6) определить наилучшее линейное приближение в виде прямой с уравнением Y = а + bХ, приближение в виде параболы Y = а + bХ + сХ2 и в виде гиперболы Y = а + bХ + сХ2 + dX3.
Таблица 6
Исходные данные
|
X |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
|
Y |
1 |
5 |
6 |
5 |
4 |
3 |
4 |
6 |
9 |
10 |
-
Получить параметры линейного приближения с помощью трех способов, приведенных в теоретическом введении. Оценить функциональную близость значений X и Y по коэффициенту корреляции.
-
Получить параметры линейного приближения, параметры приближения в виде параболы и в виде гиперболы с помощью средства Поиск решения (Меню-Параметры Excel-Надстройки-Прейти-Поиск решения-Ok) из меню Данные.
Порядок выполнения работы
Линейная регрессия
Занесем исходные данные в таблицу. Линейная регрессия решается тремя способами.
1-й способ. Разместим исходные данные в диапазоне А2:В11, как показано ниже. В ячейках А14 и В14 поместим начальные значения коэффициентов а и b и дадим им имена. В С2:С11 вычислим yi= axi+ b.
В D2:D11 определим остатки (например, в D2 формула В2-С2). Наконец, в D14 вычислим сумму квадратов остатков (для этого воспользуемся функцией СУММКВ(диапазон)). Должен получиться следующий результат (рис. 31).
Проще воспользоваться формулой СУММКВРАЗН (В2:В11, С2:С11), тогда не надо использовать блок D2:D11. Однако анализ остатков очень полезен, и их всегда надо вычислять.
Рис. 31. Результат расчетов
Рис. 32. Результаты оптимизации
Теперь решим задачу оптимизации. Выделим ячейку D14, вызовем из меню Данные Поиск решения (включить надстройки в меню) и поставим задачу минимизации D14 путем изменения А14:В14. Ограничений нет. Результат представлен на рис. 32.
2-й способ. Оформим таблицу по образцу (рис. 33); исходные данные занесем в ячейки АЗ:С13; значения в ячейках F4, D2, Е2, G2 получим по формулам
F4=E$2+D$2*B4,
D2=(CЧЁT(B4:B13)*D14-B14*C14)/(CЧЁT(B4:B13)*E14-B14*B14),
E2=(C14-D2*B14)/CЧЁT(B4:B13),
G2=(CЧЁT(B4:B13)*$D$14-SB$14*C14)/(KOPEHЬ(CЧЁT(B4:B13)*
*$E$14-B14*B14)*KOPEHЬ(CЧЁT(B4:B13)*$G$14-$C$14*$C$14)).
Рис. 33. Линейная регрессия
Рис. 34. Техника аппроксимации сложными функциями
Содержимое клетки Е2 представлено в пользовательском формате вида +#,00" х";-0,00" х" с тем, чтобы отображался и знак плюс, и буква X. В строке 14 все формулы являются суммами вышележащих ячеек в диапазоне с 4-й по 13-ю строки. На основе сделанных расчетов оцениваем полученное уравнение регрессии Y = 0,64 + 1,8Х и коэффициент корреляции R = 0,722.
3-й способ. Данный способ используется, если исходные данные расположены так, как показано на рис. 32, и в В4:С4 введена функция =ЛИНЕЙН(В2:К2;В1:К1). Исходные данные расположены (рис. 34) в ячейках G4:G5, результаты – в Н4 и Н5,
Н4=ТЕНДЕНЦИЯ($В$2:$К$2;$В$1 :$К$1 ;G4),
Н5=ТЕНДЕНЦИЯ($В$2:$К$2;$В$1 :$К$1 ;G5).
Таким образом, при X = 12 ожидается Y = 9,44, а при X = 4,5 Y = 4,66. Используя значения X и Y, с помощью Excel построим график, совмещенный с линией регрессии (линией тренда), как показано на рис. 34.
Полиномиальная регрессия
Оформим таблицу по предложенному образцу (рис. 35). В колонках Прямая, Парабола и Гипербола отобразим квадраты погрешности между фактическим значением Y и полученным по уравнению регрессии первой, второй и третьей степени соответственно.
Для уравнения первого порядка (прямой) погрешность определяется по формуле ($В8-($В$3+$С$3*$А8))^2. Аналогичные формулы заносим (копируем) во все нижележащие ячейки (область С9:С17). В ячейке С18 вычисляем сумму погрешностей для всех точек: С18=СУММ(С8:С17). Нашей целью является приведение этой погрешности к минимуму путем изменения значений коэффициентов уравнения прямой (ячейки ВЗ и СЗ). В исходном состоянии они пустые. Для поиска оптимальных значений в окне Поиск решения в качестве целевой ячейки следует установить ячейку С18, а в качестве изменяемых параметров – область ВЗ:СЗ. Результаты, полученные в изменяемых ячейках, соответствуют уравнению вида у = 1,8 + 0,6364х. Общая погрешность приближения (ячейка С18) составила 30,69.
Аналогичным образом заполним столбец D8:D18 погрешностей для полинома второй степени (параболы). Здесь
($B8-($B$4+$C$4*$A8+$D$4*$A8^2))^2.
В окне Поиск решения целевая ячейка – D18, изменяемые параметры – область B4:D4. Полученный результат соответствует уравнению у = 4,05-0,4886х-0,1023х2.
Для уравнения третьей степени (гиперболы)
($B8-($B$5+$C$5*$A8+$D$5*$A8^2+$E$5*A8^3))^2.
В окне Поиск решения целевая ячейка – Е18, изменяемые параметры -область В5:Е5. Результат описывается уравнением у = -2,0333 + 4,907х --1,0676х2+0,0709х3.
С повышением порядка уравнения регрессии погрешность приближения все время уменьшается: 30,6909 -> 25,1682 -> 9,6408.
Рис. 35. Полиномиальная регрессия
Графическое отображение результатов вычислений приведено на рис. 35. Оно также подтверждает этот вывод – линии уравнений более высокой степени находятся ближе к исследуемым точкам.
Задания для самостоятельной работы
Исходные данные приведены в таблице:
-
Х
Варианты заданий
1
2
3
4
5
6
7
8
9
10
Y1
Y2
Y3
Y4
Y5
Y6
Y7
Y8
Y9
Y10
1
3
0
7
0
2
3
2
4
0
1
2
3
3
5
1
4
4
2
2
3
4
3
4
2
3
3
4
4
4
3
3
3
4
4
3
3
5
5
6
3
6
5
5
5
6
4
4
7
6
6
7
6
7
5
6
5
3
6
9
4
5
8
8
9
8
7
5
5
6
9
8
9
10
7
8
9
8
9
5
5
8
8
9
9
9
10
11
Получить параметры линейного приближения с помощью трех способов, приведенных в теоретическом введении. Найти уравнение регрессии первой, второй и третьей степени, используя технологию, рассмотренную выше. Построить аппроксимирующие зависимости, используя средства деловой графики Excel