close

Вход

Забыли?

вход по аккаунту

?

Nikiforov Prikl progr

код для вставкиСкачать
С. Н. НИКИФОРОВ
ИНФОРМАТИКА
ЧАСТЬ III.
ПРИКЛАДНОЕ ПРОГРАММИРОВАНИЕ
Министерство образования и науки
Российской Федерации
Санкт-Петербургский государственный
архитектурно-строительный университет
C. Н. НИКИФОРОВ
ИНФОРМАТИКА
Часть III
Прикладное программирование
Учебное пособие
Санкт-Петербург
2016
УДК 004.4
Рецензент д-р физ.-мат. наук, профессор Б. Г. Вагер (СПбГАСУ)
Никифоров, С. Н.
Информатика : учеб. пособие. Ч. III. Прикладное программирование / С. Н. Никифоров; СПбГАСУ. – СПб., 2016. – 128 с.
ISBN 978-5-9227-0743-5
Подготовлено в помощь студентам при выполнении лабораторных
работ в средах Excel и VBA. Комментируются некоторые особенности
программирования, приводятся и поясняются варианты программ.
Предназначено для студентов всех специальностей.
Ил. 125. Библиогр.: 4 назв.
Рекомендовано Учебно-методическим советом СПбГАСУ в качестве учебного пособия.
ISBN 978-5-9227-0743-5
© С. Н. Никифоров, 2016
© Санкт-Петербургский государственный
архитектурно-строительный университет, 2016
ВВЕДЕНИЕ
Настоящее издание предназначено для студентов, выполняющих лабораторные работы по курсу «Информатика». Предполагается, что читатели знакомы с первой и второй частями учебного пособия [1, 2].
Описываются приемы, используемые при вычислении определенных интегралов, решении нелинейных уравнений, построении интерполяционных полиномов и уравнений регрессии.
Основная задача пособия – оказать помощь студентам при выполнении лабораторных работ в средах Excel и VBA.
3
1. ВЫЧИСЛЕНИЕ ОПРЕДЕЛЕННОГО
ИНТЕГРАЛА
Лабораторная работа «Вычисление определенного интеграла» выполняется в среде Excel, в среде VBA и с использованием
пользовательской формы Userform.
Значение определенного интеграла вычисляется методом
левых прямоугольников, методом трапеций и методом Симпсона.
1.1. Метод прямоугольников
Вычисление определенного интеграла
b
∫ f ( x)dx
a
(1.1.1)
в геометрической интерпретации адекватно вычислению площади фигуры, ограниченной осью абсцисс, ординатами, восстановленными из соответствующих пределам интегрирования точек
оси абсцисс, и отрезком подынтегральной функции (рис. 1.1.1).
Рис. 1.1.1
4
1.1. Метод прямоугольников
Вычисление определенного интеграла методом левых прямоугольников заключается в разбиении промежутка [a;b] на n равных частей, длина каждой части H определяется по формуле
b−a
(1.1.2)
;
n
построении прямоугольников (см. рис. 1.1.1) и замене площади
искомой фигуры суммой площадей прямоугольников.
При этом реализуется формула
S прям = H ( y0 + y1 + ... + yn−1 ),
H=
где y0 = f (a ),
y1 = f (a + H ),
(1.1.3)
y2 = f (a + 2 H ),
..............................
yn−1 = f [a + (n −1) H ].
Результат решения задачи представлен на рис. 1.1.2.
Рис. 1.1.2
Начинать решение следует с заполнения верхней строки таблицы (в данной адресации это четвертая строка).
5
1. Вычисление определенного интеграла
В соответствии с вариантом задания в ячейку B4 записывается константа 0, в ячейку D4 – константа 1, в ячейку F4 – константа 0,1.
В ячейку H4 записывается арифметическое выражение, соответствующее формуле (1.1.2): =(D4-B4)/F4.
Затем в ячейку B6 копируется значение ячейки B4, то есть
B6 = B4.
В ячейку B7 записывается арифметическое выражение
=B6 +$F$4. Этот закон преобразования информации копируется
до ячейки B16, формируя спектр значений аргумента X от 0 до 1
включительно.
Далее с помощью мастера функций fx создается модуль
VBA, соответствующий подынтегральной функции заданного
интеграла:
Function integral_1(x)
integral_1 = x ^ 2 + 1
End Function
С помощью модуля VBA (в данном случае с именем integral_1)
в ячейках C6:C16 формируется спектр значений подынтегральной функции от аргумента x и строится график этой функции.
В ячейках A6:A16, начиная с 0, указываются номера пар значений подынтегральной функции и аргумента x. В рассматриваемом примере, в соответствии с результатом вычисления арифметического выражения ячейки H4, нумерация оканчивается
числом 10.
В ячейку D6 записывается арифметическое выражение
=C6*$F$4, соответствующее формуле вычисления площади первого прямоугольника y0H в площади искомой фигуры.
Содержимое ячейки D6 копируется до ячейки D15 включительно, формируя вычисление площадей прямоугольников
(в данном случае десяти), образующих площадь искомой фигуры. Прямоугольник с последней yN ординатой не образуется, так
как это будет одиннадцатый прямоугольник.
В ячейку D18 записывается арифметическое выражение, соответствующее формуле (1.1.3): =СУММ(D6:D15).
Результатом его вычисления является число 1.28500000. Это
и есть значение определенного интеграла, соответствующего
6
1.1. Метод прямоугольников
варианту задания и найденного по методу левых прямоугольников в среде Excel.
Для нахождения значения определенного интеграла по методу
левых прямоугольников в среде VBA необходимо предварительно создать модуль VBA, реализующий алгоритм, соответствующий формуле (1.1.3):
Function Prym(a As Double, b As Double, n As Integer)
h = (b - a) / n
Prym = 0
For i = 0 To n - 1
Prym = Prym + integral_1(a + i * h)
Next i
Prym = Prym * h
End Function
При вызове модуля Prym(a, b, n) из ячейки D19 появляется
окно Аргументы функции (рис. 1.1.3).
Рис. 1.1.3
После подстановки в качестве аргументов значений ячеек
B4, D4 и H4 и нажатия кнопки ОК в ячейке D19 отображается
число 1.28500000. Это и есть значение определенного интеграла,
7
1. Вычисление определенного интеграла
соответствующего варианту задания и найденного по методу
левых прямоугольников в среде VBA.
Совпадение результатов в ячейках D18 и D19 подтверждает
правильность вычислений.
1.2. Метод трапеций
Вычисление определенного интеграла
b
∫ f ( x)dx
(1.2.1)
a
в геометрической интерпретации адекватно вычислению площади фигуры, ограниченной осью абсцисс, ординатами, восстановленными из соответствующих пределам интегрирования точек
оси абсцисс, и отрезком подынтегральной функции (рис. 1.2.1).
Рис. 1.2.1
Вычисление определенного интеграла методом трапеций заключается в разбиении промежутка [a;b] на n равных частей,
длина каждой части H определяется по формуле
H=
b−a
;
n
(1.2.2)
построении трапеций (см. рис. 1.2.1) и замене площади искомой
фигуры суммой площадей трапеций.
8
1.2. Метод трапеций
При этом реализуется формула
H
S прям = [ y0 + y N + 2 ( y1 + ... + yn−1 )],
2
где y0 = f (a ),
y1 = f (a + H ),
(1.2.3)
y2 = f (a + 2 H ),
..............................
yn−1 = f [a + (n −1) H ].
Результат решения задачи представлен на рис. 1.1.2.
В дополнение к операциям, описанным в разделе 1.1, в ячейку
E6 записывается арифметическое выражение =(C6+C7)/2*$F$4,
соответствующее формуле вычисления площади первой трапеции (y0+y1)/2*H в площади искомой фигуры.
Содержимое ячейки E6 копируется до ячейки E15 включительно, формируя вычисление площадей трапеций (в данном
случае десяти), образующих площадь искомой фигуры. Трапеция
с последней yN ординатой и ординатой yN+1 не образуется, так как
это будет одиннадцатая трапеция.
В ячейку E18 записывается арифметическое выражение, соответствующее формуле (1.2.3): =СУММ(E6:E15).
Результатом вычисления этого выражения является число
1.33500000. Это и есть значение определенного интеграла, соответствующего варианту задания и найденного по методу трапеций в среде Excel.
Для нахождения значения определенного интеграла по методу трапеций в среде VBA необходимо предварительно создать
модуль VBA, реализующий алгоритм, соответствующий формуле (1.2.3):
Function Trap(a As Double, b As Double, n As Integer)
h = (b - a) / n
Trap = integral_1(a) + integral_1(b)
For i = 1 To n - 1
Trap = Trap + 2 * integral_1(a + i * h)
Next i
9
1. Вычисление определенного интеграла
Trap = Trap * h / 2
End Function
При вызове модуля Trap (a, b, n) из ячейки E19 появляется
окно Аргументы функции (рис. 1.2.2).
Рис. 1.2.2
После подстановки в качестве аргументов значений ячеек B4,
D4 и H4 и нажатия кнопки ОК в ячейке E19 отображается число
1.33500000. Это и есть значение определенного интеграла, соответствующего варианту задания и найденного по методу трапеций в среде VBA.
Совпадение результатов в ячейках E18 и E19 подтверждает
правильность вычислений.
1.3. Метод Симпсона
Вычисление определенного интеграла
b
∫ f ( x)dx
(1.3.1)
a
в геометрической интерпретации адекватно вычислению площади фигуры, ограниченной осью абсцисс, ординатами, восстановленными из соответствующих пределам интегрирования
точек оси абсцисс, и отрезком подынтегральной функции
(рис. 1.3.1).
10
1.3. Метод Симпсона
Рис. 1.3.1
Вычисление определенного интеграла методом Симпсона (методом криволинейных трапеций*) заключается в разбиении промежутка [a;b] на n равных частей, длина каждой части H определяется по формуле
b−a
(1.3.2)
H=
;
n
построении криволинейных трапеций (см. рис. 3.1) и замене
площади искомой фигуры суммой площадей криволинейных
трапеций.
При этом реализуется формула
H
[ y0 + y N + 4 ( y1 + y3 ... + yn−1 ) +2 ( y2 + y4 ... + yn−2 )],
3
где y0 = f (a ),
S Симп =
y1 = f (a + H ),
(1.3.3)
y2 = f (a + 2 H ),
..............................
yn−1 = f [a + (n −1) H ],
y N = f (b).
* Криволинейная трапеция – трапеция, у которой одна сторона является отрезком параболы.
11
1. Вычисление определенного интеграла
Результат решения задачи представлен на рис. 1.1.2.
В дополнение к операциям, описанным в разделах 1.1 и 1.2,
в ячейку F6 копируется значение ячейки С6, то есть F6=C6, что соответствует нахождению значения ординаты y0 в формуле (1.3.3).
В ячейку F16 копируется значение ячейки С16, то есть
F16=C16, что соответствует нахождению значения ординаты yN
в формуле (1.3.3).
В ячейку F7 записывается арифметическое выражение =4*F7,
соответствующее ординате y1, взятой с коэффициентом 4, в формуле (1.3.3).
В ячейку F8 записывается арифметическое выражение =2*F7,
соответствующее ординате y2, взятой с коэффициентом 2, в формуле (1.3.3).
Выделенная пара ячеек F7:F8 копируется до ячейки F15. При
этом формируются значения ординат 4(y1 + y3 + … + yN–1) + 2(y2 +
+ y4 + … + yN-2) в формуле (1.3.3).
В ячейку F18 записывается арифметическое выражение
=СУММ(F6:F16)*F4/3), соответствующее формуле (1.3.3).
Результатом вычисления этого выражения является число
1.33500000. Это и есть значение определенного интеграла, соответствующего варианту задания и найденного по методу Симпсона в среде Excel.
Для нахождения значения определенного интеграла по методу
Симпсона в среде VBA, необходимо предварительно создать модуль
VBA, реализующий алгоритм, соответствующий формуле (1.3.3):
Function Simp(a As Double, b As Double, n As Integer)
h = (b - a) / n
Simp = integral_1(a) + integral_1(b)
For i = 1 To n - 1 Step 2
Simp = Simp + 4 * integral_1(a + i * h)
Next i
For i = 2 To n - 2 Step 2
Simp = Simp + 2 * integral_1(a + i * h)
Next i
Simp = Simp * h / 3
End Function
При вызове модуля Simp (a, b, n) из ячейки F19 появляется
окно Аргументы функции (рис. 1.3.2).
12
1.4. Использование пользовательской формы UserForm
Рис. 1.3.2
После подстановки в качестве аргументов значений ячеек B4,
D4 и H4 и нажатия кнопки ОК в ячейке F19 отображается число
1.33333333. Это и есть значение определенного интеграла, найденного по формуле Симпсона в среде VBA.
Совпадение результатов в ячейках F18 и F19 подтверждает
правильность вычислений.
1.4. Использование пользовательской формы UserForm
Для вычисления определенного интеграла с помощью пользовательской формы необходимо в среде VBA создать платформу
UserForm, применяя объекты панели Toolbox (рис. 1.4.1).
Рис. 1.4.1
13
1. Вычисление определенного интеграла
Организация взаимодействия объектов, находящихся на платформе UserForm, определяется модулем, программно поддерживающим объект CommandButton1 (Вычислить).
В данном случае этот модуль имеет следующий вид:
Private Sub CommandButton1_Click()
Dim a As Double
Dim b As Double
Dim n As Integer
If Not IsNumeric(TextBox1) Or Not IsNumeric(TextBox2) Or
Not IsNumeric(TextBox3) Then
MsgBox « Не цифровые данные»
Exit Sub
End If
a = CDbl(TextBox1)
b = CDbl(TextBox2)
n = CDbl(TextBox3)
If OptionButton1 Then
TextBox4 = Prym(a, b, n)
TextBox5 = Trap(a, b, n)
TextBox6 = Simp(a, b, n)
End If
End Sub
Некоторые комментарии к программе
Фрагмент программы
If Not IsNumeric(TextBox1) Or Not IsNumeric(TextBox2) Or
Not IsNumeric(TextBox3) Then
MsgBox « Не цифровые данные»
Exit Sub
End If
проверяет принадлежность данных, вводимых через объекты
TextBox1, TextBox2 и TextBox3, к цифровому типу.
Если включен объект OptionButton1, то в объекты TextBox4, TextBox5 и TextBox6 соответственно выводятся результаты функционирования модулей Prym(a, b, n), Trap(a, b, n)
и Simp(a, b, n) – то есть значения определенного интеграла, соответствующего варианту задания и вычисленного методом левых
прямоугольников, методом трапеций и методом Симпсона.
14
1.5. Калькулятор для вычисления определенных интегралов
Достоинство пользовательской формы заключается в том,
что в ней можно изменять пределы интегрирования и точность.
На рис. 1.4.2 показан пример увеличения точности вычисления
определенного интеграла при разбиении на 10 000 прямоугольников, трапеций, криволинейных трапеций. Видно, что результаты вычислений разными методами сближаются.
Недостаток пользовательской формы состоит в том, что вычисления возможны только для одного интеграла, точнее для
одной подынтегральной функции.
Рис. 1.4.2
1.5. Калькулятор для вычисления
определенных интегралов
Для вычисления нескольких видов определенных интегралов
с использованием пользовательской формы можно предложить
калькулятор, показанный на рис. 1.5.1.
Выбор интеграла осуществляется с помощью объектов OptionButton; при этом в объектах TextBox, обеспечивающих ввод пределов интегрирования и числа разбиений, устанавливается определенный вариант, который при необходимости можно изменить.
Так, при выборе интеграла вида
1
∫ x (1− x) dx
2
3
0
устанавливается вариант расчета при a = 0, b = 1 и N = 20.
15
1. Вычисление определенного интеграла
Рис. 1.5.1
При выборе других интегралов устанавливаются варианты
пределов интегрирования и разбиений в соответствии с модулями, программно поддерживающими объекты OptionButton2
и OptionButton3.
Private Sub OptionButton1_Click()
TextBox1 = “0”
TextBox2 = “1”
TextBox3 = “20”
End Sub
Private Sub OptionButton2_Click()
TextBox1 = “0”
TextBox2 = “2”
TextBox3 = “10”
End Sub
16
1.5. Калькулятор для вычисления определенных интегралов
Private Sub OptionButton3_Click()
TextBox1 = «0»
TextBox2 = «1,2»
TextBox3 = «12»
End Sub
Результаты вычислений для метода левых прямоугольников,
метода трапеций и метода Симпсона отображаются в соответствующих объектах TextBox с точностью до десятого десятичного знака.
На рис. 1.5.2 показан пример увеличения точности вычисления определенного интеграла при разбиении на 20 000 прямоугольников, трапеций, криволинейных трапеций. Видно, что результаты вычислений разными методами сближаются.
Рис. 1.5.2
Модуль, программно поддерживающий кнопку Вычислить
пользовательской формы Вычисление определенного интеграла, в среде VBA имеет вид
17
1. Вычисление определенного интеграла
Private Sub CommandButton1_Click()
Dim a As Double
Dim b As Double
Dim n As Integer
If Not IsNumeric(TextBox1) Or Not IsNumeric(TextBox2) Or
Not IsNumeric(TextBox3) Then
MsgBox «Не цифровые данные»
Exit Sub
End If
a = CDbl(TextBox1)
b = CDbl(TextBox2)
n = CDbl(TextBox3)
If OptionButton1 Then
TextBox4 = Format(Prym(a, b, n), “0.0000000000”)
TextBox5 = Format(trap(a, b, n), “0.0000000000”)
TextBox6 = Format(simpson(a, b, n), “0.0000000000”)
End If
If OptionButton2 Then
TextBox4 = Format(Prym1(a, b, n), “0.0000000000”)
TextBox5 = Format(trap1(a, b, n), “0.0000000000”)
TextBox6 = Format(simpson1(a, b, n), “0.0000000000”)
End If
If OptionButton3 Then
TextBox4 = Prym2(a, b, n)
TextBox5 = trap2(a, b, n)
TextBox6 = simpson2(a, b, n)
End If
End Sub
Модуль, программно поддерживающий кнопку Выход пользовательской формы Вычисление определенного интеграла, имеет вид
Private Sub CommandButton2_Click()
Unload Me
End Sub
Модули подынтегральных функций имеют вид
Function integral_1(x)
18
1.5. Калькулятор для вычисления определенных интегралов
integral_1 = (x * (1 - x) ^ 2) ^ (1 / 3)
End Function
Function integral_2(x)
integral_2 = 3 * x / (1 + x ^ 3) ^ (1 / 2)
End Function
Function integral_3(x)
integral_3 = (Exp(x / 2)) / ((x + 1) ^ (1 / 2))
End Function
Используемые при вычислениях модули имеют вид
Prym(a As Double, b As Double, n As Integer)
h = (b - a) / n
Prym = 0
For i = 0 To n - 1
Prym = Prym + integral_1(a + i * h)
Next i
Prym = Prym * h
End Function
Function Prym1(a As Double, b As Double, n As Integer)
h = (b - a) / n
Prym1 = 0
For i = 0 To n - 1
Prym1 = Prym1 + integral_2(a + i * h)
Next i
Prym1 = Prym1 * h
End Function
Function Prym2(a As Double, b As Double, n As Integer)
h = (b - a) / n
Prym2 = 0
For i = 0 To n - 1
Prym2 = Prym2 + integral_3(a + i * h)
Next i
Prym2 = Prym2 * h
End Function
19
1. Вычисление определенного интеграла
Function trap(a As Double, b As Double, n As Integer)
h = (b - a) / n
trap = integral_1(a) + integral_1(b)
For i = 1 To n - 1
trap = trap + integral_1(a + i * h)
Next i
trap = trap * h
End Function
Function trap1(a As Double, b As Double, n As Integer)
h = (b - a) / n
trap1 = integral_2(a) + integral_2(b)
For i = 1 To n - 1
trap1 = trap1 + integral_2(a + i * h)
Next i
trap1 = trap1 * h
End Function
Function trap2(a As Double, b As Double, n As Integer)
h = (b - a) / n
trap2 = integral_3(a) + integral_3(b)
For i = 1 To n - 1
trap2 = trap2 + integral_3(a + i * h)
Next i
trap2 = trap2 * h
End Function
Function simpson(a As Double, b As Double, n As Integer)
h = (b - a) / n
simpson = integral_1(a) + integral_1(b)
For i = 1 To n - 1 Step 2
simpson = simpson + 4 * integral_1(a + i * h)
Next i
For i = 2 To n - 2 Step 2
simpson = simpson + 2 * integral_1(a + i * h)
Next i
simpson = simpson * (h / 3)
End Function
20
1.5. Калькулятор для вычисления определенных интегралов
Function simpson1(a As Double, b As Double, n As Integer)
h = (b - a) / n
simpson1 = integral_2(a) + integral_2(b)
For i = 1 To n - 1 Step 2
simpson1 = simpson1 + 4 * integral_2(a + i * h)
Next i
For i = 2 To n - 2 Step 2
simpson1 = simpson1 + 2 * integral_2(a + i * h)
Next i
simpson1 = simpson1 * (h / 3)
End Function
Function simpson2(a As Double, b As Double, n As Integer)
h = (b - a) / n
simpson2 = integral_3(a) + integral_3(b)
For i = 1 To n - 1 Step 2
simpson2 = simpson2 + 4 * integral_3(a + i * h)
Next i
For i = 2 To n - 2 Step 2
simpson2 = simpson2 + 2 * integral_3(a + i * h)
Next i
simpson2 = simpson2 * (h / 3)
End Function
Для обеспечения возможности вычислять в калькуляторе определенный интеграл с новой подынтегральной функцией необходимо:
• создать модуль новой подынтегральной функции;
• создать для добавляемой подынтегральной функции модули вычисления методом левых прямоугольников, методом трапеций и методом Симпсона;
• добавить на пользовательскую форму Вычисление определенного интеграла в VBA объект OptionButton (в данном
случае OptionButton4) и задать в программно поддерживающем
его модуле с помощью объектов TextBox вариант пределов интегрирования и числа разбиений;
• внести изменения в модуль, программно поддерживающий кнопку Вычислить пользовательской формы Вычисление
21
1. Вычисление определенного интеграла
определенного интеграла в VBA, в части обращения через объект OptionButton4 к вновь созданным модулям вычисления определенного интеграла с новой подынтегральной функцией.
Это достаточно легко выполнить копированием уже существующих модулей.
1.6. Создание надписи (изображения)
на объекте OptionButton
Для создания новой надписи на объекте OptionButton необходимо выполнить следующие действия:
• создать саму надпись, для чего последовательно пройти по
цепочке Вставка, Объект, Microsoft Equation 3.0 и, используя
шаблоны, написать, например,
1, 2
∫ ( e +1) dx;
2
1
• создать файл Точечный рисунок (например, Рис. 200);
• скопировать в этот файл созданную в Microsoft Equation 3.0 надпись и растянуть на весь экран (файл);
• вызвать Userform (в данном случае Вычисление интеграла в VBA) с созданным объектом OptionButton и открыть таблицу свойств этого объекта;
• в свойстве Picture открыть файл с созданной надписью
(в данном случае Точечный рисунок Рис. 200).
В результате получится пользовательская форма с новым
объек­том OptionButton4, на котором размещена связанная
с объек­том надпись (изображение) (рис. 1.6.1).
1.7. Выводы
Если в силу каких-либо причин вычислить определенный интеграл путем преобразования подынтегральной функции невозможно, его можно вычислить приближенно методом левых прямоугольников, методом трапеций или методом Симпсона с любой
наперед заданной точностью, определяемой числом разбиений
интервала интегрирования или шагом разбиения.
22
1.7. Выводы
Рис. 1.6.1
23
2. РЕШЕНИЕ НЕЛИНЕЙНЫХ УРАВНЕНИЙ
Лабораторная работа «Решение нелинейных уравнений» выполняется в среде Excel, в среде VBA и с использованием пользовательской формы Userform.
Значения корней нелинейных уравнений вычисляются методом половинного деления, методом Ньютона и методом хорд.
2.1. Метод половинного деления (дихотомия)
Определение 2.1.1. Корень уравнения – это число или числа, при
подстановке которых уравнение обращается в ноль или тождество.
Любому методу решения нелинейного уравнения предшествует этап отделения корней.
Определение 2.1.2. Отделение корней – это установление достаточно тесного промежутка или промежутков значений на
оси Х, в которых находится корень.
Алгоритм нахождения корней нелинейного уравнения методом половинного деления с заданной точностью e представлен
на рис. 2.1.1.
Рис. 2.1.1
24
2.1. Метод половинного деления (дихотомия)
Суть алгоритма в следующем:
• в программу (в данном случае с именем Pol_del и тремя
формальными параметрами a, b, e) передаются значения промежутка [a;b], в котором обнаружен корень, и значение точности;
• вычисляется значение средней точки промежутка: c =
= (a + b)/2;
• определяется, в какой половине промежутка функция уравнения f(x) поменяла знак, для чего вычисляется логическое выражение f(a)*f(c)<0;
• если результат вычисления равен 1, значит функция уравнения f(x) поменяла знак в первой половине промежутка [a;b]
и поэтому b = c, в противном случае a = c;
• процесс повторяется до тех пор, пока не выполнится условие |a-b|< = e;
• значение средней точки последнего промежутка [a;b]
и есть корень уравнения, вычисленный с заданной точностью e.
На рис. 2.1.2 приведен пример решения нелинейного уравнения 2x3 + x2 – 3x + 2 = 0.
Рис. 2.1.2
Начинать выполнение задания следует с создания в среде
VBA модуля, соответствующего функции уравнения задания:
25
2. Решение нелинейных уравнений
Function nel_ur_1(x)
nel_ur_1 = 2 * x ^ 3 + x ^ 2 - 3 * x + 2
End Function
Затем в диапазоне A5:B25 (в данной адресации) необходимо
получить таблицу значений функции nel_ur_1 от аргумента X
и построить график этой функции. При этом начальное значение аргумента X (ячейка A5) и начальное значение шага изменения аргумента X (ячейка B2) подбираются таким образом, чтобы
на графике четко увидеть пересечение кривой графика функции
с осью абсцисс. Это и есть графическое решение заданного нелинейного уравнения.
В таблице, в столбце значений аргументов X (диапазон
A5:A25) следует определить пару значений аргумента X, при которых функция (диапазон B5:B25) меняет знак. В данном случае
это пара ячеек A17:A18. Таких пар может быть несколько.
Этап отделения корней завершен.
Далее необходимо в строке 7 оформить шапку таблицы
(см. рис. 2.1.2).
Заполнить первую строку таблицы:
• в ячейку E8 записать =A17;
• в ячейку F8 записать =A18;
• в ячейку G8 записать арифметическое выражение для вычисления значения средней точки: =(F8 + E8)/2;
• в ячейке H8 с помощью мастера функций fx вызвать модуль, соответствующий решаемому уравнению: =nel_ur_1(E8);
• в ячейке I8 с помощью мастера функций fx вызвать модуль,
соответствующий решаемому уравнению: =nel_ur_1(F8);
• в ячейке J8 с помощью мастера функций fx вызвать модуль,
соответствующий решаемому уравнению: =nel_ur_1(G8);
• убедиться, что значения ячеек B17 и H8 совпадают;
• убедиться, что значения ячеек B18 и I8 совпадают;
• в ячейку F8 записать условный оператор =ЕСЛИ(ABS(E8F8)<=$G$5;»корень =»&ТЕКСТ(G8;»0.0000000000»)&» за
«&D8&» шагов»;ABS(E8-F8)).
Заполнить вторую строку таблицы:
• в
ячейку
E9
записать
условный
оператор
=ЕСЛИ(H8*J8<0;E8;G8);
26
2.1. Метод половинного деления (дихотомия)
• в
ячейку
F9
записать
условный
оператор
=ЕСЛИ(H8*J8<0;G8;F8);
• выделить диапазон G8:K8 первой строки таблицы и скопировать на вторую строку таблицы;
• выделить диапазон E9:K9 второй строки таблицы и копировать вниз до тех пор, пока в столбце K не появится сообщение корень
= -1.7148040771 за 15 шагов (для решаемого варианта задания).
Таким образом, нелинейное уравнение 2x3 + x2 – 3x + 2 = 0
решено методом дихотомии в среде Excel. Корень уравнения:
-1.7148040771.
При этом заданная точность решения e = 0.00001 достигнута
за 15 итераций.
Для решения заданного уравнения в среде VBA необходимо
создать модуль VBA:
Function Pol_del(a As Double, b As Double, e As Double)
Dim c As Double
10 c = (a + b) / 2
If nel_ur_1(a) * nel_ur_1(c) < 0 Then b = c Else a = c
If Abs(a - b) <= e Then Pol_del = Format(c, “0.0000000000”)
Else GoTo 10
End Function
Затем следует вызвать этот модуль с помощью мастера функций fx, например, в ячейке K26.
В появившемся окне Аргументы функции подставляем значения ячеек E8, F8 и G5 (рис. 2.1.3) и, нажав кнопку OK, получаем результат -1.7148010254.
Рис. 2.1.3
27
2. Решение нелинейных уравнений
Недостаток использования функции VBA в данном случае состоит в том, что она возвращает только один параметр и определить количество итераций невозможно.
Достоинство использования функции VBA — возможность
изменять значение точности вычислений без построения достаточно объемной таблицы.
2.2. Метод Ньютона (метод касательных)
Решение нелинейного уравнения методом Ньютона графически проиллюстрировано на рис. 2.2.1.
Рис. 2.2.1
К точке f(a) = f(x0) проводится касательная до пересечения
с осью абсцисс в точке x1. Это первое приближение к корню.
Затем вычисляется величина |x0-x1|. Если |x0-x1| < = e, то значение x1 принимается за корень уравнения, если нет, то процесс повторяется, при этом x0 = x1, а x1 определяется снова.
Так как tgα = f ‘(x0), то
x1 = x0 −
f ( x0 )
.
f ′ ( x0 )
(2.2.1)
Алгоритм нахождения корней нелинейного уравнения методом Ньютона с заданной точностью e представлен на рис. 2.2.2.
28
2.2. Метод Ньютона (метод касательных)
Рис. 2.2.2
Суть алгоритма в следующем:
• в программу (в данном случае с именем Newton и двумя
формальными параметрами a, e) передаются значение начала
(или окончания) промежутка [a;b], в котором обнаружен корень,
и значение точности;
• присваивается x0 = a;
• по формуле (2.2.1) вычисляется x1;
• вычисляется величина |x0-x1|,
• если |x0-x1| < e, то значение x1 принимается за корень уравнения, если нет, то процесс повторяется, при этом присваивается
x0 = x1, а x1 определяется снова;
• значение последней точки x1 и есть корень уравнения, найденный с заданной точностью e.
Так как этап отделения корней для заданного варианта уже выполнен, то решение нелинейного уравнения 2x3 + x2 – 3x + 2 =
= 0 методом Ньютона в среде Excel можно начинать с создания
шапки таблицы (см. рис. 2.1.2, строка 30).
Заполнить первую строку таблицы:
• в ячейку E31 записать =A17;
• в ячейке F31 с помощью мастера функций fx вызвать модуль, соответствующий решаемому уравнению: =nel_ur_1(E31);
• убедиться, что значения ячеек F31 и B17 совпадают;
29
2. Решение нелинейных уравнений
• в ячейке G31 с помощью мастера функций fx вызвать модуль, соответствующий первой производной от функции решаемого уравнения: =nel_ur_1D(E31);
• модуль nel_ur_1D оформить в VBA предварительно:
Function nel_ur_1D(x)
nel_ur_1D = 2 * 3 * x ^ 2 + 2 * x - 3
End Function
Заполнить вторую строку таблицы:
• в ячейку E32 записать арифметическое выражение, соответствующее формуле (2.2.1): =E31-F31/G31;
• выделить диапазон F31:G31 первой строки таблицы и скопировать на вторую строку таблицы;
• в ячейку H32 записать условный оператор =ЕСЛИ(ABS(E31E32)<=$I$30;»Корень =»&ТЕКСТ(E32;»0,0000000000»)&» за
«&D32&» шагов»;ABS(E31-E32));
• выделить диапазон E32:H32 второй строки таблицы и копировать вниз до тех пор, пока в столбце H не появится сообщение корень = -1.7148059167 за 4 шагов (для решаемого
варианта).
Таким образом, нелинейное уравнение 2x3 + x2 – 3x + 2 = 0
решено методом Ньютона в среде Excel. Корень уравнения:
-1.7148059167.
При этом заданная точность решения e = 0.00001 достигнута
за 4 итерации.
Для решения заданного уравнения в среде VBA необходимо
создать модуль VBA:
Function Newton(a As Double, e As Double)
x=a
x1 = x - nel_ur_1(x) / nel_ur_1D(x)
While Abs(x - x1) > e
x = x1
x1 = x - nel_ur_1(x) / nel_ur_1D(x)
Wend
Newton = Format(x1, “0.0000000000”)
End Function
Затем следует вызвать этот модуль с помощью мастера функций fx, например, в ячейке J35.
30
2.3. Метод хорд
В появившемся окне Аргументы функции подставляем значения ячеек E31 и I30 (рис. 2.2.3) и, нажав кнопку OK, получаем
результат -1.7148010254.
Рис. 2.2.3
Недостаток использования функции VBA в данном случае состоит в том, что она возвращает только один параметр и определить количество итераций невозможно.
Достоинство использования функции VBA — возможность
изменять значение точности вычислений без построения достаточно объемной таблицы (хотя и несколько меньшей, чем при методе дихотомии).
2.3. Метод хорд
С особенностями решения нелинейных уравнений методом
хорд можно ознакомиться в работе [3]. В настоящем пособии рассматривается решение нелинейного уравнения в среде VBA.
Для решения заданного уравнения необходимо создать модуль
VBA:
Public Function Horda(a As Double, b As Double, delta As
Double)
Dim c1 As Double, c2 As Double, Fa As Double, Fb As Double,
Fc As Double
31
2. Решение нелинейных уравнений
Fa = nel_ur_1(a)
Fb = nel_ur_1(b)
If Fa * Fb > 0 Then
MsgBox («Интервал [a, b] выбран неправильно»)
Exit Function
End If
Do
c2 = c1
c1 = a - (b - a) / (Fb - Fa) * Fa
Fc = nel_ur_1(c)
If Fc * Fa > 0 Then b = c1: Fb = Fc Else a = c1: Fa = Fc
Loop Until Abs(c1 - c2) > delta
Horda = Format(c1, «0.0000000000»)
End Function
Затем следует вызвать этот модуль с помощью мастера функций fx, например, в ячейке K35.
В появившемся окне Аргументы функции подставляем значения ячеек E8, F8 и G5 (рис. 2.3.1) и, нажав кнопку OK, получаем результат -1.7138047138.
Рис. 2.3.1
Недостаток использования функции VBA в данном случае состоит в том, что она возвращает только один параметр, и определить количество итераций невозможно.
32
2.4. Решение второго нелинейного уравнения
Достоинство использования функции VBA — возможность
изменять значение точности вычислений без построения достаточно объемной таблицы.
2.4. Решение второго нелинейного уравнения
Для решения второго заданного нелинейного уравнения, например вида 3sin(x/2) = 2x2 – 4, необходимо выполнить следующие действия:
• выделить на листе Excel диапазон ячеек, в которых решалось первое уравнение (в данной адресации A1:T38);
• скопировать выделенный фрагмент, например со строки 42
(рис. 2.4.1);
Рис. 2.4.1
• создать модули VBA для нового уравнения (в данном случае 3sin(x/2) = 2x2 – 4):
Function nel_ur_2(x)
nel_ur_2 = 3 * Sin(x / 2) - 2 * x ^ 2 + 4
End Function
Function nel_ur_2D(x)
nel_ur_2D = 3 * Cos(x / 2) + 1 / 2 - 2 * x
End Function
33
2. Решение нелинейных уравнений
Function Pol_del2(a As Double, b As Double, e As Double)
Dim c As Double
10 c = (a + b) / 2
If nel_ur_2(a) * nel_ur_2(c) < 0 Then b = c Else a = c
If Abs(a - b) <= e Then Pol_del2 = Format(c, “0.0000000000”)
Else GoTo 10
End Function
Function Newton2(a As Double, e As Double)
x=a
x1 = x - nel_ur_2(x) / nel_ur_2D(x)
While Abs(x - x1) > e
x = x1
x1 = x - nel_ur_2(x) / nel_ur_2D(x)
Wend
Newton2 = Format(x1, “0.0000000000”)
End Function
Public Function Horda2(a As Double, b As Double, delta As
Double)
Dim c1 As Double, c2 As Double, Fa As Double, Fb As Double,
Fc As Double
Fa = nel_ur_2(a)
Fb = nel_ur_2(b)
If Fa * Fb > 0 Then
MsgBox («Интервал [a, b] выбран неправильно»)
Exit Function
End If
Do
c2 = c1
c1 = a - (b - a) / (Fb - Fa) * Fa
Fc = nel_ur_2(c)
If Fc * Fa > 0 Then b = c1: Fb = Fc Else a = c1: Fa = Fc
Loop Until Abs(c1 - c2) > delta
Horda2 = Format(c1, “0.0000000000”)
End Function
34
2.4. Решение второго нелинейного уравнения
• заменить в диапазоне ячеек B46:B66 функцию =nel_ur_1
на функцию =nel_ur_2 (см. рис. 2.4.1);
• подобрав значения ячеек A46 и D43, выполнить этап отделения корней для второго уравнения, то есть для функции
nel_ur_2 (в данном случае ячейками, в которых функция nel_ur_2
меняет знак, являются A54 и A55);
• скопировать значение ячейки A54 в ячейку E49, то есть записать E49=A54;
• скопировать значение ячейки A55 в ячейку F49, то есть записать F49=A55;
• заменить в диапазоне ячеек H49:J64 функцию =nel_ur_1
на функцию =nel_ur_2 (рис. 2.4.1);
• в ячейке K63 (в данной адресации) появится сообщение корень = -1.1000030518 за 15 шагов (для решаемого варианта задания).
Таким образом, нелинейное уравнение 3sin(x/2) = 2x2 – 4
в среде Excel решено методом дихотомии. Корень уравнения:
-1.1000030518.
При этом заданная точность решения e = 0.00001 достигнута
за 15 итераций.
Для решения заданного уравнения в среде VBA необходимо
вызвать модуль Pol_del2 с помощью мастера функций fx, например, в ячейке K67.
В появившемся окне Аргументы функции подставляем значения ячеек E49, F49 и G46 (рис. 2.4.2) и, нажав кнопку OK, получаем результат -1.1021057129.
Рис. 2.4.2
35
2. Решение нелинейных уравнений
Для решения второго заданного нелинейного уравнения вида
3sin(x/2) = 2x2 – 4 методом Ньютона необходимо выполнить следующие действия:
• скопировать значение ячейки A54 в ячейку E72, то есть записать E72=A54;
• заменить в диапазоне ячеек F72:F79 функцию =nel_ur_1
на функцию =nel_ur_2 (см. рис. 2.4.1);
• заменить в диапазоне ячеек G72:G79 функцию =nel_ur_1D
на функцию =nel_ur_2D (см. рис. 2.4.1);
• в ячейке H77 (в данной адресации) появится сообщение
корень = -1.1021004844 за 5 шагов (для решаемого варианта задания).
Таким образом, нелинейное уравнение 3sin(x/2) = 2x2 – 4
в среде Excel решено методом Ньютона. Корень уравнения:
-1.1021004844.
При этом заданная точность решения e = 0.00001 достигнута
за 5 итераций.
Для решения заданного уравнения в среде VBA необходимо
с помощью мастера функций fx вызвать модуль Newton2, например, в ячейке J76.
В появившемся окне Аргументы функции подставляем значения ячеек E72 и I71 (рис. 2.4.3) и, нажав кнопку OK, получаем
результат -1.1021004844.
Рис. 2.4.3
36
2.5. Калькулятор для решения нелинейных уравнений
Для решения заданного уравнения в среде VBA методом
хорд необходимо с помощью мастера функций fx вызвать модуль
Horda2, например, в ячейке K76.
В появившемся окне Аргументы функции подставляем значения ячеек E49, F49 и G46 (рис. 2.4.4) и, нажав кнопку OK, получаем результат -1.1020377217.
Рис. 2.4.4
Недостаток использования функции VBA в данном случае состоит в том, что она возвращает только один параметр, и определить количество итераций невозможно.
Достоинство использования функции VBA — возможность
изменять значение точности вычислений без построения достаточно объемной таблицы.
2.5. Калькулятор для решения нелинейных уравнений
Для решения нескольких видов нелинейных уравнений с помощью пользовательской формы можно предложить калькулятор
(рис. 2.5.1).
Выбор уравнения осуществляется с помощью объектов OptionButton; при этом в объектах TextBox, обеспечивающих ввод
пределов промежутка [a;b], в котором обнаружен корень, и точности, устанавливается определенный вариант, который при необходимости можно изменить.
37
2. Решение нелинейных уравнений
Рис. 2.5.1
Так, при выборе нелинейного уравнения вида 2x3 + x2 – 3x +
+ 2 = 0 устанавливается вариант расчета при a = -1,8, b = -1,7
и Е = 0,00001.
При выборе других уравнений варианты пределов промежутков и точности устанавливаются в соответствии с модулями, программно поддерживающими объекты OptionButton2 и OptionButton3.
Private Sub OptionButton1_Click()
TextBox1 = “-1,8”
TextBox2 = “-1,7”
TextBox3 = “0,00001”
End Sub
Private Sub OptionButton2_Click()
TextBox1 = «-1,2»
38
2.5. Калькулятор для решения нелинейных уравнений
TextBox2 = «-1,1»
TextBox3 = «0,00001»
End Sub
Так как в модулях VBA, использовавшихся для вычисления
значений корней, применялись грамматические конструкции
Function, не позволявшие определить количество итераций, необходимо применить грамматические конструкции Sub для его
определения:
Sub PolDel(a As Double, b As Double, e As Double, c As Double,
ii As Integer)
ii = 0
10 c = (a + b) / 2
ii = ii + 1
If nel_ur_1(a) * nel_ur_1(c) < 0 Then b = c Else a = c
If Abs(a - b) >= e Then GoTo 10
End Sub
Sub Newton(a As Double, e As Double, x1 As Double, j As
Integer)
Dim x As Double
x=a
x1 = x - nel_ur_1(x) / nel_ur_1D(x)
j=1
While Abs(x - x1) > e
x = x1
x1 = x - nel_ur_1(x) / nel_ur_1D(x)
j=j+1
Wend
End Sub
Sub Horda(a As Double, b As Double, delta As Double, c1 As
Double, jj As Integer)
Dim c2 As Double
Fa = nel_ur_1(a)
Fb = nel_ur_1(b)
If Fa * Fb > 0 Then
MsgBox («Интервал [a, b] выбран неправильно»)
Exit Sub
End If
39
2. Решение нелинейных уравнений
c1 = a - (b - a) / (Fb - Fa) * Fa
jj = 0
While Abs(c1 - c2) >= delta
Fa = nel_ur_1(a)
Fb = nel_ur_1(b)
Fc = nel_ur_1(c1)
If Fc * Fa > 0 Then b = c1: Fb = Fc Else a = c1: Fa = Fc
c2 = c1
c1 = a - (b - a) / (Fb - Fa) * Fa
jj = jj + 1
Wend
End Sub
Sub PolDel2(a As Double, b As Double, e As Double, c As Double, ii As Integer)
ii = 0
10 c = (a + b) / 2
ii = ii + 1
If nel_ur_2(a) * nel_ur_2(c) < 0 Then b = c Else a = c
If Abs(a - b) >= e Then GoTo 10
End Sub
Sub Newton2(a As Double, e As Double, x1 As Double, j As Integer)
Dim x As Double
x=a
x1 = x - nel_ur_2(x) / nel_ur_2D(x)
j=1
While Abs(x - x1) > e
x = x1
x1 = x - nel_ur_2(x) / nel_ur_2D(x)
j=j+1
Wend
End Sub
Sub Horda2(a As Double, b As Double, delta As Double, c1 As
Double, jj As Integer)
Dim c2 As Double
Fa = nel_ur_2(a)
Fb = nel_ur_2(b)
If Fa * Fb > 0 Then
40
2.5. Калькулятор для решения нелинейных уравнений
MsgBox («Интервал [a, b] выбран неправильно»)
Exit Sub
End If
c1 = a - (b - a) / (Fb - Fa) * Fa
jj = 0
While Abs(c1 - c2) >= delta
Fa = nel_ur_2(a)
Fb = nel_ur_2(b)
Fc = nel_ur_2(c1)
If Fc * Fa > 0 Then b = c1: Fb = Fc Else a = c1: Fa = Fc
c2 = c1
c1 = a - (b - a) / (Fb - Fa) * Fa
jj = jj + 1
Wend
End Sub
Грамматические конструкции Sub, в отличие от грамматических конструкций Function, позволяют в списке формальных параметров задать несколько выходных параметров. Вызов грамматических конструкций Sub осуществляется с помощью специального
оператора вызова Call. Поэтому в объекте CommandButton1 пользовательской формы UserForm2 Решение нелинейных уравнений нужно написать следующий программный модуль:
Private Sub CommandButton1_Click()
Dim a As Double, Ad As Double, Bd As Double
Dim An As Double, Ah As Double, bh As Double
Dim b As Double
Dim e As Double
Dim c As Double
Dim x1 As Double
Dim cc As Double
Dim i As Integer
Dim it As Integer
Dim jt As Integer
a = CDbl(TextBox1)
b = CDbl(TextBox2)
e = CDbl(TextBox3)
41
2. Решение нелинейных уравнений
Ad = a: Bd = b
An = a:
Ah = a: bh = b
If OptionButton1 Then
Call PolDel(Ad, Bd, e, c, i)
TextBox4 = Format(c, “0.0000000000”)
TextBox5 = i
Call Newton(An, e, x1, it)
TextBox8 = Format(x1, “0.0000000000”)
TextBox9 = it
Call Horda(Ah, bh, e, cc, jt)
TextBox10 = Format(cc, “0.0000000000”)
TextBox11 = jt
End If
If OptionButton2 Then
Call PolDel2(Ad, Bd, e, c, i)
TextBox4 = Format(c, “0.0000000000”)
TextBox5 = i
Call Newton2(An, e, x1, it)
TextBox8 = Format(x1, “0.0000000000”)
TextBox9 = it
Call Horda2(Ah, bh, e, cc, jt)
TextBox10 = Format(cc, “0.0000000000”)
TextBox11 = jt
End If
End Sub
Некоторые комментарии к программе
Фрагмент программы
Ad = a: Bd = b
An = a:
Ah = a: bh = b
42
2.5. Калькулятор для решения нелинейных уравнений
обеспечивает запоминание начальных значений промежутка
[a;b], в котором обнаружен корень (при вычислениях в первой
вызванной процедуре PolDel эти параметры принимают измененные значения, и при вызове следующей по порядку процедуры
Newton количество итераций в ней определяется неверно).
Применение пользовательской формы позволяет изменять
значения промежутка [a;b], в котором обнаружен корень, и точность вычислений.
Так, при точности e = 0,000000001 можно убедиться в полном
совпадении значений корней, вычисленных методом дихотомии,
методом Ньютона и методом хорд и представленных в формате
с точностью до десятого десятичного знака: -1.7148059167. При
этом выбранная точность достигается при использовании метода
дихотомии за 27 итераций, метода Ньютона – за 4 итерации, метода хорд – за 5 итераций (рис. 2.5.2).
Рис. 2.5.2
Аналогичное совпадение значений корней можно получить
и для второго уравнения 3sin(x/2) = 2x2 – 4 (рис. 2.5.3). В этом случае
43
2. Решение нелинейных уравнений
совпадение значений корней до десятого разряда после десятичной
точки происходит при точности E = 0,0000000001, а число итераций, обеспечивших это совпадение, при методе дихотомии составляет 30, при методе Ньютона – 10, при методе хорд – 3.
Рис. 2.5.3
2.6. Создание надписи (изображения)
на объекте OptionButton
Для создания надписи на объекте OptionButton необходимо
выполнить следующие действия:
• создать саму надпись, для чего последовательно пройти по
цепочке Вставка, Объект, Microsoft Equation 3.0 и, используя
шаблоны, написать, например:
2 x 3 + x 2 − 3 x + 2 = 0;
x
3 sin( ) = 2 x 2 − 4;
2
44
2.7. Выводы
• создать файл Точечный рисунок, например Рис. 300 и 400;
• скопировать в этот файл созданную в Microsoft Equation 3.0 надпись и растянуть на весь экран (файл);
• вызвать Userform (в данном случае Решение нелинейных
уравнений) с объектами OptionButton и по очереди открыть таблицы свойств каждого объекта;
• в свойстве Picture открыть файл с созданной надписью
(в данном случае файлы Точечный рисунок Рис. 300 и 400).
В результате получится пользовательская форма с объектами
OptionButton1 и OptionButton2, на которых размещены связанные с объектами надписи (изображения) (рис. 2.6.1).
Рис. 2.6.1
2.7. Выводы
Если в силу каких-либо причин решение нелинейного уравнения путем аналитических преобразований невозможно, то значения корней (если они существуют) могут быть найдены приближенно методом дихотомии, методом Ньютона или методом хорд
с любой наперед заданной точностью.
45
3. ИНТЕРПОЛЯЦИЯ
Пусть таблично задана некоторая функция y = f(x) и требуется
получить значение функции для такого значения аргумента x, которое входит в промежуток [x0;xn], но не совпадает ни с одним из
значений xi (i = 0,1, … , n).
В этом случае необходимо найти некоторую приближающую
функцию, значения которой в узлах интерполяции точно совпадают с данными таблицы, и с ее помощью вычислить искомое значение функции.
Обычно интерполирующую функцию ищут в виде многочлена n-й степени, где n + 1 – количество строк в исходной таблице:
Pn(x) = a0xn + a1xn–1 + … + an–1x + an.
Искомое значение функции определяется: решением системы линейных алгебраических уравнений (СЛАУ), составленной
на основании данных исходной таблицы; с помощью интерполяционного многочлена Лагранжа; с помощью интерполяционного
многочлена Ньютона для равноотстоящих узлов. Работа выполняется в средах Excel и VBA.
3.1. Нахождение коэффициентов полинома
решением СЛАУ
Пусть исходная функция задана в виде значений в диапазоне
ячеек B7:C10 (рис. 3.1.1).
Составим систему из n + 1 уравнений с n + 1 неизвестными:
• в ячейку F7 запишем условие =ЕСЛИ(B7<0;1;B7^0)
(в данном случае этого можно и не делать, так как в предложенном варианте задания ситуации 00 не возникает);
• в ячейку G7 запишем арифметическое выражение =B7^1;
• в ячейку H7 запишем арифметическое выражение =B7^2;
46
3.1. Нахождение коэффициентов полинома решением СЛАУ
• в ячейку I7 запишем арифметическое выражение =B7^3;
• выделим диапазон ячеек F7:I7 и скопируем законы преобразования информации ячеек диапазона до 10 строки вклю­
чительно.
Рис. 3.1.1
В результате получим матрицу значений аргументов x в степенях от 0 до n, то есть до 3 (см. рис. 3.1.1).
Значения функции y = f(x) просто скопируем из исходной таблицы: J7=C7, J8=C8, J9=C9, J10=C10.
Коэффициенты СЛАУ определены. Чтобы найти корни этой
системы, необходимо:
• с помощью мастера функций fx в диапазоне ячеек F13:I16
сформировать матрицу, обратную матрице аргументов x:
=МОБР(F7:I10);
• с помощью мастера функций fx в диапазоне ячеек
B13:B16 вычислить корни СЛАУ, используя функцию
=МУМНОЖ(F13:I16;J7:J10).
47
3. Интерполяция
Таким образом, корни СЛАУ, являющиеся коэффициентами
интерполяционного полинома P3(x), определены; их значения
a0 = -12.2857, a1 = 20.4454, a2 = -5.5714 и a3 = 0.4107 показаны
в диапазоне ячеек B13:B16.
Чтобы увидеть аналитическое выражение полученного интерполяционного полинома P3(x), объединим, например, ячейки диапазона D22:O28 и в объединенную ячейку запишем сцепленные
символьные константы:
=»P₃(x) = «&ТЕКСТ($B$13;»0,00»)& ЕСЛИ($B$14<0;ТЕК
СТ($B$14;»0,00»); «+»&ТЕКСТ($B$14;»0,00»))&»x»&ЕСЛИ
($B$15<0;ТЕКСТ($B$15;»0,00»);»+»&ТЕКСТ($B$15;»0,00»))
&»x²»&ЕСЛИ($B$16<0;ТЕКСТ($B$16;»0,00»);»+»&ТЕКСТ(
$B$16;»0,00»))&»x³»
В результате в объединенной ячейке получим:
P3(x) = -12.29 + 20.45 x – 5.57 x2 + 0.41 x3
Для проверки правильности решения вычислим спектр значений полученного интерполяционного полинома P3(x) на спектре
значений аргумента x∈[0;8,5]. Начальное значение диапазона 0
и конечное 8,5 выбираются такими, чтобы «накрыть» диапазон
исходной таблицы x∈[x0;x3]. Шаг табулирования (в данном случае 0,5) выбирается таким, чтобы попасть в узлы интерполяции.
Тогда, оформив в ячейках A19 и B19 шапку таблицы, как показано на рис. 3.1.1, в ячейках A20 и A37 получим спектр значений аргумента x.
В ячейку B20 запишем арифметическое выражение интерполяционного полинома P3(x):
=$B$13+$B$14*A20^1+$B$15*A20^2+$B$16*A20^3
с коэффициентами, взятыми в абсолютной адресации из ячеек
B13:B16, и аргументом x, взятым в относительной адресации из
ячейки A20.
Скопируем закон преобразования информации ячейки B20 до
ячейки B37 включительно.
Заметим, что в узлах интерполяции x = 1, 3, 5, 8 значения интерполяционного полинома P3(x) точно соответствуют значениям
исходной таблицы Y = 3, 10, 2, 5.
Равенство значений исходной таблицы и значений интерполяционного полинома P3(x) в узлах интерполяции является доказательством правильности полученного решения.
48
3.1. Нахождение коэффициентов полинома решением СЛАУ
Построим график интерполяционного полинома P3(x) (рис. 3.1.2).
Рис. 3.1.2
Скопируем значения Y интерполяционной таблицы в столбец C таблицы табулирования интерполяционного полинома
P3(x), то есть: C22=C7, C26=C8, C30=C9, C36=C10.
Добавим значения диапазона ячеек C20:C37 на график интерполяционного полинома P3(x) (рис. 3.1.3).
Убедимся, что значения Y исходной таблицы в узлах интерполяции расположены точно по линии построенного графика интерполяционного полинома P3(x).
Чтобы решить задание в полном объеме, необходимо вычислить значение интерполяционного полинома P3(x) при x = 2,372.
Для этого скопируем закон преобразования информации ячейки B37, соответствующий аналитическому выражению интерполяционного полинома P3(x) = –12,29 + 20,45x – 5,57x2 + 0,41x3,
например, в ячейку B39. То есть в ячейке B39 запишется арифметическое выражение
=$B$13+$B$14*A39^1+$B$15*A39^2+$B$16*A39^3
49
3. Интерполяция
Рис. 3.1.3
В ячейку A39 внесем константу 2.372, тогда в ячейке B39 отобразится результат вычисления значения интерполяционного полинома: P3(x) = 10.35 (рис. 3.1.4).
Рис. 3.1.4
50
3.2. Интерполяционный многочлен Лагранжа
Таким образом, задание выполнено:
• определено аналитическое выражение интерполяционного
полинома: P3(x) = –12,29 + 20,45x – 5,57x2 + 0,41x3;
• вычислено значение интерполяционного полинома P3(x)
при x = 2,372: P3(x) = 10,35.
Полученное значение достоверно, так как значения интерполяционного полинома P3(x) и исходной таблицы полностью совпадают в узлах интерполяции.
3.2. Интерполяционный многочлен Лагранжа
Интерполяционный многочлен Лагранжа в общем виде выглядит следующим образом:
n
Ln ( x) = ∑ yi
i =0
( x − x0 ) ... ( x − xi −1 ) ( x − xi +1 ) ... ( x − xn )
. (3.2.1)
( xi − x0 ) ... ( xi − xi −1 ) ( xi − xi +1 ) ... ( xi − xn )
Пусть исходная функция задана в виде значений в диапазоне
ячеек B7:C10 (рис. 3.2.1). Тогда выражение (3.2.1) примет вид
L3 ( x) = y0
( x − x1 ) ( x − x2 ) ( x − x3 )
( x − x0 ) ( x − x2 ) ( x − x3 )
+ y1
+
( x1 − x0 ) ( x1 − x2 ) ( x1 − x3 )
( x0 − x1 ) ( x0 − x2 ) ( x0 − x3 )
( x − x0 ) ( x − x1 ) ( x − x3 )
( x − x0 ) ( x − x1 ) ( x − x2 )
+ y2
+ y3
.
( x2 − x0 ) ( x2 − x1 ) ( x2 − x3 )
( x3 − x0 ) ( x3 − x1 ) ( x3 − x2 )
(3.2.2)
В ячейках D19:H19 оформим шапку таблицы вычисления слагаемых формулы (3.2.2), как показано на рис. 3.2.1.
Вычислим слагаемые формулы (3.2.2):
• в ячейку E20 запишем арифметическое выражение, соответствующее первому слагаемому формулы (3.2.2) (значения аргументов x0, x1, x2, x3, y0 взяты из ячеек диапазона B7:B10 и ячейки C7 в абсолютной адресации, а значение аргумента x взято из
ячейки A20 в относительной адресации):
=$C$7*(A20-$B$8)*(A20-$B$9)*(A20-$B$10)/($B$7-$B$8)/
($B$7-$B$9)/($B$7-$B$10)
• в ячейку F20 запишем арифметическое выражение, соответствующее второму слагаемому формулы (3.2.2) (значения аргументов x0, x1, x2, x3, y1 взяты из ячеек диапазона B7:B10
51
3. Интерполяция
и ячейки C8 в абсолютной адресации, а значение аргумента x
взято из ячейки A20 в относительной адресации):
=$C$8*(A20-$B$7)*(A20-$B$9)*(A20-$B$10)/($B$8-$B$7)/
($B$8-$B$9)/($B$8-$B$10)
Рис. 3.2.1
• в ячейку G20 запишем арифметическое выражение, соответствующее третьему слагаемому формулы (3.2.2) (значения аргументов x0, x1, x2, x3, y2 взяты из ячеек диапазона B7:B10 и ячейки C9 в абсолютной адресации, а значение аргумента x взято из
ячейки A20 в относительной адресации):
=$C$9*(A20-$B$7)*(A20-$B$8)*(A20-$B$10)/($B$9-$B$7)/
($B$9-$B$8)/($B$9-$B$10)
• в ячейку H20 запишем арифметическое выражение, соответствующее четвертому слагаемому формулы (3.2.2) (значения аргументов x0, x1, x2, x3, y3 взяты из ячеек диапазона B7:B10
и ячейки C10 в абсолютной адресации, а значение аргумента x
взято из ячейки A20 в относительной адресации):
52
3.2. Интерполяционный многочлен Лагранжа
=$C$10*(A20-$B$7)*(A20-$B$8)*(A20-$B$9)/($B$10-$B$7)/
($B$10-$B$8)/($B$10-$B$9)
В ячейку D20 запишем сумму четырех слагаемых в соответствии с формулой (3.2.2):
=E20 + F20 + G20 + H20
Полученное в ячейке D20 значение -12.2857 и есть результат
вычисления интерполяционного полинома Лагранжа L3(x) для
аргумента x = 0.
Выделим диапазон ячеек D20:H20 и скопируем законы преобразования информации в них до D37:H37 включительно.
Появившиеся в диапазоне ячеек D20:D37 значения и есть значения интерполяционного полинома Лагранжа L3(x), вычисленные на спектре значений аргумента x диапазона ячеек A20:A37
(x∈[0;8,5]) (рис. 3.2.2).
Рис. 3.2.2
Полное совпадение значений диапазонов ячеек B20:B37 (канонический полином P3(x)) и D20:D37 (интерполяционный
53
3. Интерполяция
полином Лагранжа L3(x)) является доказательством правильности полученного решения.
Выделим диапазон ячеек D37:H37 и скопируем законы преобразования информации в них в ячейки D39:H39.
Тогда в ячейке D39 отобразится результат вычисления значения интерполяционного полинома Лагранжа L3(x) = 10.35 при
x = 2,372 (см. рис. 3.2.2).
Совпадение значений ячеек B39 и D39 подтверждает правильность вычислений.
Для вычисления значений интерполяционного полинома Лагранжа
L3(x) в среде VBA необходимо предварительно создать модуль VBA:
Function lagr(x As Double, xe As Variant, ye As Variant)
n = Application.Count(xe)
lagr = 0
For i = 1 To n
p=1
For j = 1 To n
If j <> i Then p = p * (x - xe(j)) / (xe(i) - xe(j))
Next j
lagr = lagr + ye(i) * p
Next i
End Function
Затем, установив курсор в ячейке I20, с помощью мастера функций fx нужно вызвать модуль lagr и в появившемся окне
Аргументы функции установить значения, как показано на
рис. 3.2.3 (значения исходной таблицы $B$7:$B$10 и $C$7:$C$10
необходимо брать в абсолютной адресации).
Рис. 3.2.3
54
3.2. Интерполяционный многочлен Лагранжа
После нажатия кнопки ОК следует скопировать закон преобразования информации ячейки I20 до ячейки I37 включительно
и в ячейку I39.
Появившиеся в диапазоне ячеек I20:I37 значения и есть значения интерполяционного полинома Лагранжа L3(x), вычисленные
в среде VBA на спектре значений аргумента x диапазона ячеек
A20:A37 (x∈[0;8,5]) (рис. 3.2.4).
Рис. 3.2.4
Полное совпадение значений диапазонов ячеек B20:B37 (канонический полином P3(x)), D20:D37 (интерполяционный полином Лагранжа L3(x) в среде Excel) и I20:I37 (интерполяционный
полином Лагранжа L3(x) в средеVBA) доказывает правильность
полученного решения.
Совпадение значений ячеек B39, D39 и I39 подтверждает правильность вычислений значений полиномов P3(x) и L3(x) при
x = 2,372 (значение равно 10,35) (см. рис. 3.2.4).
55
3. Интерполяция
3.3. Интерполяционный многочлен Ньютона
Интерполяционный многочлен Ньютона для равноотстоящих
узлов в общем виде выглядит следующим образом:
∆10
∆2
( x − x0 ) + 0 2 ( x − x0 ) ( x − x1 ) + ...
h
2!h
∆n0
... +
( x − x0 ) ( x − x1 )...( x − xn−1 ).
n!h n
N n ( x ) = y0 +
(3.3.1)
Пусть исходная функция задана в виде значений в диапазоне
ячеек B7:C10 (рис. 3.3.1). Примечание: так как рассматривается
формула Ньютона для равноотстоящих узлов, значения исходной
таблицы соответствующим образом изменены.
Тогда выражение (3.3.1) примет вид
∆10
∆2
( x − x0 ) + 0 2 ( x − x0 ) ( x − x1 ) +
h
2!h
∆30
+
( x − x0 ) ( x − x1 )( x − x2 ).
n!h n
N 3 ( x ) = y0 +
(3.3.2)
В ячейках M19, O19 и Q19 вычислим конечные разности ∆10,
3
∆ и ∆ 0 для интерполяционного многочлена Ньютона N3(x) для
равноотстоящих узлов по формуле (3.2.2), как показано на
рис. 3.3.1.
В ячейку J20 запишем арифметическое выражение интерполяционного многочлена Ньютона N3(x) для равноотстоящих
узлов по формуле (3.2.2) (значения аргументов x0, x1, x2 и y0 взяты
из ячеек диапазона B7:B9 и ячейки C7 в абсолютной адресации,
а значение аргумента x взято из ячейки A20 в относительной
адресации):
=$C$7+$M$19/$B$11*(A20-$B$7)+$O$19/(2*$B$11^2)*(A20$B$7)*(A20-$B$8)+$Q$19/(6*$B$11^3)*(A20-$B$7)*(A20$B$8)*(A20-$B$9)
Значение величины h, которая является шагом в исходной
таблице равноотстоящих значений x0, x1, x2 и x3, вычисляется, например, в ячейке B11 как =B8-B7 и берется в абсолютной
адресации.
2
0
56
3.3. Интерполяционный многочлен Ньютона
Рис. 3.3.1
Полученное в ячейке J20 значение -14.25 (исходная таблица изменена) и есть результат вычисления интерполяционного полинома Ньютона N3(x) для равноотстоящих узлов для аргумента x = 0.
Скопируем закон преобразования информации ячейки J20 до
J37 включительно.
Появившиеся в диапазоне ячеек J20:J37 значения и есть значения интерполяционного полинома Ньютона N3(x) для равноотстоящих узлов, вычисленные на спектре значений аргумента x
диапазона ячеек A20:A37 (x∈[0;8,5]) (см. рис. 3.3.1).
Полное совпадение значений диапазонов ячеек B20:B37 (канонический полином P3(x)), D20:D37 (интерполяционный полином
Лагранжа L3(x) в среде Excel), I20:I37 (интерполяционный полином Лагранжа L3(x) в средеVBA) и J20:J37 (интерполяционный
полином Ньютона N3(x) для равноотстоящих узлов в среде Excel)
является доказательством правильности полученного решения.
Совпадение значений ячеек B39, D39, I39 и J39 подтверждает правильность вычислений значений полиномов P3(x), L3(x)
и N3(x) при x = 2,372 (значение равно 10,64) (см. рис. 3.3.1).
57
3. Интерполяция
Для вычисления значений интерполяционного полинома Ньютона N3(x) для равноотстоящих узлов в среде VBA необходимо
предварительно создать модуль VBA:
Function Newtonn(x As Double, xe As Variant, ye As Variant)
n = Application.Count(xe)
Dim D() As Variant
ReDim D(n, n) As Variant
For i = 1 To n - 1
D(i, 1) = ye(i + 1) - ye(i)
Next i
‘
For j = 2 To n - 1
For i = 1 To j
D(i, j) = D(i + 1, j - 1) - D(i, j - 1)
Next i
h = xe(2) - xe(1)
Next j
ne = ye(1)
For i = 1 To n - 1
p=1
For j = 1 To i
p = p * (x - xe(j)) / (j * h)
Next j
ne = ne + p * D(1, i)
Next i
Newtonn = ne
End Function
Затем, установив курсор в ячейке K20, с помощью мастера функций fx нужно вызвать модуль Newtonn и в появившемся
окне Аргументы функции установить значения, как показано на
рис. 3.3.2 (значения исходной таблицы $B$7:$B$10 и $C$7:$C$10
необходимо брать в абсолютной адресации).
После нажатия кнопки ОК следует скопировать закон преобразования информации ячейки K20 до ячейки K37 включительно и в ячейку K39.
Появившиеся в диапазоне ячеек K20:K37 значения и есть
значения интерполяционного полинома Ньютона N3(x) для
58
3.4. Калькулятор для вычисления значений интерполяционных полиномов
равноотстоящих узлов, вычисленные в среде VBA на спектре значений аргумента x диапазона ячеек A20:A37 (x∈[0;8,5])
(см. рис. 3.3.2).
Рис. 3.3.2
Полное совпадение значений диапазонов ячеек B20:B37 (канонический полином P3(x)), D20:D37 (интерполяционный полином Лагранжа L3(x) в среде Excel), I20:I37 (интерполяционный
полином Лагранжа L3(x) в средеVBA), J20:J37 (интерполяционный полином Ньютона N3(x) для равноотстоящих узлов в среде
Excel) и K20:K37 (интерполяционный полином Ньютона N3(x)
для равноотстоящих узлов в среде VBA) является доказательством правильности полученного решения.
Совпадение значений ячеек B39, D39, I39, J39 и K39 подтверждает правильность вычислений значений полиномов P3(x),
L3(x) и N3(x) при x = 2,372 (значение равно 10,64) (см. рис. 3.3.1).
3.4. Калькулятор для вычисления значений
интерполяционных полиномов
В данном разделе рассмотрены два варианта построения калькуляторов для вычисления значений интерполяционных полиномов.
Чтобы вызвать первый калькулятор, необходимо нажать кнопку Калькулятор для вычисления значений интерполяционных полиномов (рис. 3.4.1).
59
3. Интерполяция
Рис. 3.4.1
На появившейся панели калькулятора нужно включить флажки у полиномов, значения которых необходимо найти, а затем нажать кнопку Вычислить (рис. 3.4.2).
Рис. 3.4.2
60
3.4. Калькулятор для вычисления значений интерполяционных полиномов
В появившемся окне Ввод значений интерполяционной
табли­цы нужно сформировать исходную интерполяционную
таб­лицу, последовательно вводя значения исходной заданной
табли­цы (а потом и любые другие данные) и нажимая кнопку ОК
(на рис. 3.4.3 для удобства указаны данные, соответствующие выполняемому варианту задания).
Рис. 3.4.3
После последнего нажатия кнопки ОК появится панель калькулятора с результатами вычислений (рис. 3.4.4).
Рис. 3.4.4
Таким образом, для выполняемого варианта задания при
х = 2,372 вычисленные значения канонического интерполяционного полинома, полинома Лагранжа и полинома Ньютона
61
3. Интерполяция
совпадают и равны 10,644. Эти результаты совпадают и с результатами вычислений в среде Excel.
Не закрывая панель калькулятора, можно проверить совпадение значений построенного интерполяционного полинома
и данных исходной интерполяционной таблицы в узлах интерполяции.
Работа калькулятора для вычисления значений интерполяционного полинома поддерживается следующими программными
модулями:
Private Sub CommandButton1_Click()
Dim x As Double
Dim n As Integer
Dim xe() As Double
Dim ye() As Double
n = CDbl(TextBox1)
x = CDbl(TextBox3)
ReDim xe(1 To n) As Double
ReDim ye(1 To n) As Double
For i = 1 To n
xe(i) = InputBox(“Введите x(“ & CStr(i - 1) & “) =” & vbCr
& “x(0)=1” & vbCr & “x(1)=3” & vbCr & “x(2)=5” & vbCr &
“x(3)=7”, “Ввод значений интерполяционной таблицы”, 1,
5000, 8000)
Next i
For i = 1 To n
ye(i) = InputBox(“Введите y(“ & CStr(i - 1) & “) =” & vbCr
& “y(0)=3” & vbCr & “y(1)=10” & vbCr & “y(2)=2” & vbCr &
“y(3)=5”, “Ввод значений интерполяционной таблицы”, 1,
8000, 5000)
Next i
Label1 = “X” & “ Y” & vbCr & CStr(xe(1)) & “ “ & CStr(ye(1))
& vbCr & CStr(xe(2)) & “ “ & CStr(ye(2)) & vbCr & CStr(xe(3))
& “ “ & CStr(ye(3)) & vbCr & CStr(xe(4)) & “ “ & CStr(ye(4))
If CheckBox2 Then TextBox5 = Format(lagr(x, xe, ye), “0.000”)
Else TextBox5 = “”
If CheckBox3 Then TextBox6 = Format(Newtonn(x, xe, ye),
“0.000”) Else TextBox6 = “”
62
3.4. Калькулятор для вычисления значений интерполяционных полиномов
If CheckBox1 Then TextBox4 = Format(Kanon(x, xe, ye),
“0.000”) Else TextBox4 = “”
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Function lagr(x As Double, xe As Variant, ye As Variant)
n = Application.Count(xe)
lagr = 0
For i = 1 To n
p=1
For j = 1 To n
If j <> i Then p = p * (x - xe(j)) / (xe(i) - xe(j))
Next j
lagr = lagr + ye(i) * p
Next i
End Function
Function Newtonn(x As Double, xe As Variant, ye As Variant)
n = Application.Count(xe)
Dim D() As Variant
ReDim D(n, n) As Variant
‘конечные разности 1-ого порядка в 1-0м столбце массива d
For i = 1 To n - 1
D(i, 1) = ye(i + 1) - ye(i)
Next i
For j = 2 To n - 1
For i = 1 To j
D(i, j) = D(i + 1, j - 1) - D(i, j - 1)
Next i
h = xe(2) - xe(1)
Next j
ne = ye(1)
For i = 1 To n - 1
63
3. Интерполяция
p=1
For j = 1 To i
p = p * (x - xe(j)) / (j * h)
Next j
ne = ne + p * D(1, i)
Next i
Newtonn = ne
End Function
Function Kanon(x As Double, xe As Variant, ye As Variant)
Dim xx() As Double
Dim yye() As Double
n = Application.Count(xe)
ReDim xx(1 To n, 1 To n) As Double
ReDim yye(1 To n, 1 To 1) As Double
For i = 1 To n
For j = 1 To 1
yye(i, j) = ye(i)
Next j
Next i
For i = 1 To n
For j = 1 To n
If j = 1 Then xx(i, j) = xe(i) ^ 0
If j = 2 Then xx(i, j) = xe(i) ^ 1
If j = 3 Then xx(i, j) = xe(i) ^ 2
If j = 4 Then xx(i, j) = xe(i) ^ 3
If j = 5 Then xx(i, j) = xe(i) ^ 4
If j = 6 Then xx(i, j) = xe(i) ^ 5
Next j
Next i
Kanon = 0
For i = 1 To n
Kanon = Kanon + Application.Product(Application.Index(Application.MMult(Application.MInverse(xx), yye), i), x ^ (i - 1))
Next i
End Function
64
3.4. Калькулятор для вычисления значений интерполяционных полиномов
Некоторые комментарии к программам
Модули
Function lagr(x As Double, xe As Variant, ye As Variant)
Function Newtonn(x As Double, xe As Variant, ye As Variant)
Function Kanon(x As Double, xe As Variant, ye As Variant)
обеспечивают вычисления значений интерполяционных полиномов Лагранжа, Ньютона и канонического интерполяционного
полинома.
Подпрограмма Private Sub CommandButton1_Click() обеспечивает ввод исходных данных и вызов модулей lagr, Newtonn
и Kanon.
Калькулятор поддерживает вычисление значений интерполяционных полиномов степенью не выше пятой, хотя изменения
программы для увеличения степени вычисляемых интерполяционных полиномов достаточно просты.
Недостаток рассмотренного калькулятора состоит в том, что
при любом изменении данных необходимо снова вводить всю интерполяционную таблицу.
Для вызова второго калькулятора необходимо нажать кнопку
Второй калькулятор (рис. 3.4.5).
На появившейся панели калькулятора Интерполяция следует
нажать кнопку Ввод таблицы (рис. 3.4.6).
В появившемся окне Ввод значений интерполяционной
табли­цы нужно сформировать исходную интерполяционную
табли­цу, последовательно вводя значения заданной исходной таблицы (а потом и любые другие данные) и нажимая кнопку ОК
(на рис. 3.4.7 для удобства указаны данные, соответствующие выполняемому варианту задания).
После последнего нажатия кнопки ОК появится панель калькулятора Интерполяция. На панели нужно включить флажки
у полиномов, значения которых необходимо найти, а затем нажать кнопку Вычисление (рис. 3.4.8).
Появится панель калькулятора Интерполяция с результатами
вычислений (рис. 3.4.9).
65
3. Интерполяция
Рис. 3.4.5
Рис. 3.4.6
66
3.4. Калькулятор для вычисления значений интерполяционных полиномов
Рис. 3.4.7
Рис. 3.4.8
Рис. 3.4.9
67
3. Интерполяция
Таким образом, для выполняемого варианта задания при
х = 2,372 вычисленные значения канонического интерполяционного полинома, полинома Лагранжа и полинома Ньютона совпадают и равны 10,644. Эти результаты совпадают и с результатами
вычислений в среде Excel.
Кроме того, в объекте Label6 появится аналитическое выражение полинома P3(x) = –14,25 + 23,46x – 6,75x2 + 0,54x3, записанное в виде P3(x) = -14.25 + 23.45x^1 – 6.75 x^2 + 0.54 x^3, также
совпадающее с аналитическим выражением интерполяционного
полинома, полученного в среде Excel.
Не закрывая панель калькулятора Интерполяция, можно проверить совпадение значений построенного интерполяционного полинома и данных исходной интерполяционной таблицы в узлах
интерполяции. При этом достаточно изменять только значение величины x, не перезагружая исходную интерполяционную таблицу.
Работа калькулятора Интерполяция для вычисления значений интерполяционных полиномов поддерживается следующими программными модулями:
Dim xe() As Double
Dim ye() As Double
Dim Aa() As Variant
Dim t As Variant
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub CommandButton3_Click()
Dim x As Double
n = CDbl(TextBox7)
ReDim Preserve xe(1 To n)
ReDim Preserve ye(1 To n)
For i = 1 To n
xe(i) = InputBox(“Введите x(“ & CStr(i - 1) & “) =” & vbCr
& “x(0)=1” & vbCr & “x(1)=3” & vbCr & “x(2)=5” & vbCr &
“x(3)=7”, “ Ввод значений интерполяционной таблицы”, 1,
5000, 8000)
68
3.4. Калькулятор для вычисления значений интерполяционных полиномов
Next i
For i = 1 To n
ye(i) = InputBox(“Введите y(“ & CStr(i - 1) & “) =” & vbCr
& “y(0)=3” & vbCr & “y(1)=10” & vbCr & “y(2)=2” & vbCr &
“y(3)=5”, “ Ввод значений интерполяционной таблицы”, 1,
8000, 5000)
Next i
Label5 = “X” & “ Y” & vbCr & CStr(xe(1)) & “ “ & CStr(ye(1))
& vbCr & CStr(xe(2)) & “ “ & CStr(ye(2)) & vbCr & CStr(xe(3))
& “ “ & CStr(ye(3)) & vbCr & CStr(xe(4)) & “ “ & CStr(ye(4))
End Sub
Private Sub CommandButton4_Click()
Dim x As Double
Dim nad As String
Dim ziclo As Double
n = CDbl(TextBox7)
ReDim Preserve xe(1 To n) As Double
ReDim Preserve ye(1 To n) As Double
x = CDbl(TextBox3)
‘If CheckBox1 Then
‘TextBox4 = Format(Kanon(x, xe, ye), “0.000”)
‘Else
‘extBox4 = “”
‘End If
If CheckBox1 Then
Call Kanon3(x, xe, ye, nad, ziclo)
TextBox4 = Format(ziclo, “0.000”)
Label6 = nad
Else
TextBox4 = “”
End If
If CheckBox2 Then TextBox5 = Format(lagr(x, xe, ye), “0.000”)
Else TextBox5 = “”
If CheckBox3 Then TextBox6 = Format(Newtonn(x, xe, ye),
“0.000”) Else TextBox6 = “”
End Sub
69
3. Интерполяция
Sub Kanon3(x As Double, xe As Variant, ye As Variant, Polinom
As String, Kanon_znac As Double)
Dim xx() As Double
Dim yye() As Double
n = Application.Count(xe)
ReDim xx(1 To n, 1 To n) As Double
ReDim yye(1 To n, 1 To 1) As Double
For i = 1 To n
For j = 1 To 1
yye(i, j) = ye(i)
Next j
Next i
For i = 1 To n
For j = 1 To n
If j = 1 Then xx(i, j) = xe(i) ^ 0
If j = 2 Then xx(i, j) = xe(i) ^ 1
If j = 3 Then xx(i, j) = xe(i) ^ 2
If j = 4 Then xx(i, j) = xe(i) ^ 3
If j = 5 Then xx(i, j) = xe(i) ^ 4
If j = 6 Then xx(i, j) = xe(i) ^ 5
Next j
Next i
Kanon_znac = 0
For i = 1 To n
Kanon_znac = Kanon_znac + Application.Product(Application.
Index(Application.MMult(Application.MInverse(xx), yye), i), x ^
(i - 1))
Next i
Polinom = “P3(x)=”
For i = 1 To n
If
Application.Product(Application.Index(Application.
MMult(Application.MInverse(xx), yye), i), 1) < 0 Then
Polinom = Polinom & Format(Application.Product(Application.
Index(Application.MMult(Application.MInverse(xx), yye), i), 1),
“0.00”)
If i = 1 Then Polinom = Polinom & “”
If i = 2 Then Polinom = Polinom & “x^1”
70
3.4. Калькулятор для вычисления значений интерполяционных полиномов
If i = 3 Then Polinom = Polinom & “x^2”
If i = 4 Then Polinom = Polinom & “x^3”
If i = 5 Then Polinom = Polinom & “x^4”
If i = 6 Then Polinom = Polinom & “x^5”
Else
Polinom = Polinom & “+”
Polinom = Polinom & Format(Application.Product(Application.
Index(Application.MMult(Application.MInverse(xx), yye), i), 1),
“0.00”)
If i = 1 Then Polinom = Polinom & “”
If i = 2 Then Polinom = Polinom & “x^1”
If i = 3 Then Polinom = Polinom & “x^2”
If i = 4 Then Polinom = Polinom & “x^3”
If i = 5 Then Polinom = Polinom & “x^4”
If i = 6 Then Polinom = Polinom & “x^5”
End If
Next i
End Sub
Function lagr(x As Double, xe As Variant, ye As Variant)
n = Application.Count(xe)
lagr = 0
For i = 1 To n
p=1
For j = 1 To n
If j <> i Then p = p * (x - xe(j)) / (xe(i) - xe(j))
Next j
lagr = lagr + ye(i) * p
Next i
End Function
Function Newtonn(x As Double, xe As Variant, ye As Variant)
n = Application.Count(xe)
Dim D() As Variant
ReDim D(n, n) As Variant
‘конечные разности 1-го порядка в 1-м столбце массива d
For i = 1 To n - 1
71
3. Интерполяция
D(i, 1) = ye(i + 1) - ye(i)
Next i
For j = 2 To n - 1
For i = 1 To j
D(i, j) = D(i + 1, j - 1) - D(i, j - 1)
Next i
h = xe(2) - xe(1)
Next j
ne = ye(1)
For i = 1 To n - 1
p=1
For j = 1 To i
p = p * (x - xe(j)) / (j * h)
Next j
ne = ne + p * D(1, i)
Next i
Newtonn = ne
End Function
Некоторые комментарии к программам
Модули
Function lagr(x As Double, xe As Variant, ye As Variant)
Function Newtonn(x As Double, xe As Variant, ye As Variant)
Sub Kanon3(x As Double, xe As Variant, ye As Variant, Polinom
As String, Kanon_znac As Double)
обеспечивают вычисления значений интерполяционных полиномов Лагранжа, Ньютона и канонического интерполяционного
полинома.
Подпрограмма Private Sub CommandButton3_Click() обеспечивает ввод исходных данных и отображение их в объекте
Label5.
Подпрограмма Private Sub CommandButton4_Click() обеспечивает вызов модулей lagr, Newtonn и Kanon3. При этом модуль
Kanon3 выполнен не в виде функции Function, а в виде подпрограммы Sub. Такая организация позволяет получать из подпрограммы Kanon3 несколько выходных параметров, в частности
параметры Polinom As String и Kanon_znac As Double.
72
3.4. Калькулятор для вычисления значений интерполяционных полиномов
Калькулятор поддерживает вычисление значений интерполяционных полиномов степенью не выше пятой, хотя изменения
программы для увеличения степени вычисляемых интерполяционных полиномов достаточно просты.
Если в качестве исходной интерполяционной таблицы ввести
значения x = 1, 4, 5, 7 и y = 3, 10, 2, 5 и вычислить значение интерполяционного полинома для x = 2,372, то мы получим результаты, показанные на рис. 3.4.10.
Рис. 3.4.10
Значения канонического интерполяционного полинома и полинома Лагранжа для x = 2,372 совпадают и равны 17,597, в то
время как значение интерполяционного полинома Ньютона для
x = 2,372 равно 9,005. Дело в том, что в калькуляторе для вычисления значений интерполяционного полинома Ньютона используется формула для равноотстоящих узлов интерполяционной
таблицы, а это условие в рассматриваемом примере не соблюдается. Так что для введенной интерполяционной таблицы аналитическое выражение интерполяционного полинома имеет вид
P3(x) = –28,83 + 43,04x – 12,17x2 + 0,96x3, а его значение при
x = 2,372 равно 17,597 (см. рис. 3.4.10).
73
4. МЕТОД НАИМЕНЬШИХ КВАДРАТОВ
Цель работы – найти уравнение регрессии, которое наилучшим образом подходит для исходного варианта таблично заданной функции. Процесс состоит из двух этапов:
1) выбор вида уравнения регрессии;
2) определение коэффициентов уравнения.
Вид уравнения регрессии выбирается произвольно (перебором), а коэффициенты вычисляются с помощью метода наименьших квадратов.
В отличие от методов интерполяции метод наименьших квадратов применяется в тех случаях, когда не требуется точного
совпадения в узлах интерполяции значений исходной таблицы
и значений уравнения регрессии, либо когда данные исходной таблицы не вызывают доверия.
4.1. Линейное уравнение регрессии
В общем случае линейное уравнение имеет вид y = ax + b.
Пусть исходная заданная функция y = f(x) имеет вид, показанный
в таблице (рис. 4.1.1) в диапазоне ячеек A7:B18.
Построим точечный график функции y = f(x).
В строке 21 создадим шапку таблицы (см. рис. 4.1.1).
В ячейку B22 запишем произвольную константу 1, а в ячейку
C22 – произвольную константу 2.
В ячейку A22 запишем оператор присваивания для сцепленных символьных констант:
=»y=»&ТЕКСТ(B22;»0.00»)&»x»&ЕСЛИ(C22<0;ТЕКСТ(C
22;»0.00»);»+»&ТЕКСТ(C22;»0.00»))
Ячейке С6 присвоим такое же значение, какое приобретает
ячейка A22, то есть C6=A22.
Тогда, в соответствии с выбранными коэффициентами в ячейках B22=1 и C22=2, получим в ячейках A22 и C6 результат
y = 1.00x + 2.00.
74
4.1. Линейное уравнение регрессии
Рис. 4.1.1
Запишем в ячейку C7 уравнение прямой с коэффициентами, взятыми в абсолютной адресации из ячеек B22 и C22:
=$B$22*A7+$C$22 (в качестве аргумента x берется значение
ячейки A7 исходной таблицы).
Скопируем закон преобразования информации ячейки C7 до
ячейки С18 включительно.
В результате получим спектр значений функции y = 1,00x + 2,00
на спектре аргументов x в диапазоне значений ячеек A7:A18
(см. рис. 4.1.1).
В ячейку C19, используя мастер функций fx, запишем результат вычисления функции: =СУММКВРАЗН(B7:B18;C7:C18)
(рис. 4.1.2). Примечание: обозначения Массив_x и Массив_y на
рис. 4.1.2 – математические и не совпадают с обозначениями выполняемого задания.
Добавим на точечный рисунок исходной таблицы уравнение
прямой y = 1,00x + 2,00 (рис. 4.1.3).
Заметим, что это уравнение линейной регрессии с произвольными значениями коэффициентов: a = 1, b = 2. Его соответствие
исходному распределению можно оценить путем вычисления
функции суммы квадратов разностей, значение которой составляет 247.
75
4. Метод наименьших квадратов
Рис. 4.1.2
Рис. 4.1.3
Для определения оптимальных значений коэффициентов a и b
нужно воспользоваться функцией Поиск решения:
• установить курсор в ячейку C19;
• последовательно выбирая Разработчик*, Данные, Поиск
решения**, вызвать окно Параметры поиска решения, в котором установить параметры, как показано на рис. 4.1.4;
* Если раздел Разработчик отсутствует, то добавить его, пройдя по цепочке
Файл, Параметры, Настройка ленты, Разработчик.
** Если раздел Поиск решения отсутствует, то добавить его, пройдя по цепочке
Разработчик, Надстройки, Поиск решения.
76
4.1. Линейное уравнение регрессии
• нажать кнопку Найти решение.
Рис. 4.1.4
После этого в ячейке B22 установится значение 0.16, в ячейке
C22 – 4.85, в ячейке C19 – 67.0547786 (рис. 4.1.5).
Рис. 4.1.5
77
4. Метод наименьших квадратов
Это означает, что прямая с коэффициентами a = 0,16 и b = 4,85
отобразится на графике Линейная регрессия, как показано на
рис. 4.1.5, при этом значение суммы квадратов разностей будет
минимально и равно 67.0547786.
Таким образом, коэффициенты для линейного уравнения регрессии определены, и для выполняемого задания уравнение
имеет вид y = 0,16x + 4,85.
Чтобы убедиться в правильности решения, щелкнем правой
клавишей мышки по любой точке исходного задания на графике
Линейная регрессия (см. рис. 4.1.5).
В появившемся окне выберем раздел Добавить линию тренда (рис. 4.1.6).
Рис. 4.1.6
В появившемся окне Формат линии тренда выберем параметры Линейная, показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации
(R^2) и нажмем кнопку Закрыть (рис. 4.1.7).
Появившаяся на графике Линейная регрессия линия тренда полностью совпадает с графиком построенного уравнения регрессии y = 0,16x + 4,85, совпадает и уравнение линии тренда
y = 0,1643x + 4,8485, что является доказательством правильности
решения (рис. 4.1.8).
Следует отметить, что значение коэффициента детерминации
R2 = 0,0545 свидетельствует о том, что выбранный вид уравнения
регрессии (линейная функция) не вполне отвечает исходному заданию, так как максимальное значение коэффициента детерминации R2 = 1.
78
4.1. Линейное уравнение регрессии
Рис. 4.1.7
Рис. 4.1.8
На рис. 4.1.9 показан результирующий график использования в качестве уравнения регрессии линейной функции
79
4. Метод наименьших квадратов
y = 0,16x + 4,85 и построения линии тренда y = 0,1643x + 4,8485
для исходного варианта задания y = f(x).
Рис. 4.1.9
4.2. Параболическое уравнение регрессии
В общем случае параболическое уравнение имеет вид y = ax2 +
+ bx + c. Пусть исходная заданная функция y = f(x) имеет вид, показанный в таблице (рис. 4.2.1) в диапазоне ячеек A7:B18.
Построим точечный график функции Параболическая регрессия y = f(x).
Рис. 4.2.1
80
4.2. Параболическое уравнение регрессии
В строке 21 создадим шапку таблицы, как показано на рис. 4.2.1.
В ячейку B23 запишем произвольную константу 1, в ячейку
C23 – произвольную константу 2, в ячейку D23 – произвольную
константу 3.
В ячейку A23 запишем оператор присваивания для сцепленных символьных констант:
=»y=»&ТЕКСТ(B23;»0.00»)&»x^2»&ЕСЛИ(C23<0;ТЕКСТ
(C23;»0.00»);»+»&ТЕКСТ(C23;»0.00»))&»x»&ЕСЛИ(D23<0;Т
ЕКСТ(D23;»0.00»);»+»&ТЕКСТ(D23;»0.00»))
Ячейке D6 присвоим такое же значение, какое приобретает
ячейка A23, то есть D6=A23.
Тогда, в соответствии с выбранными коэффициентами в ячейках B23=1, С23=2 и D23=3, получим в ячейках A23 и D6 результат y = 1.00x^2 + 2.00x + 3.00.
Запишем в ячейку D7 уравнение параболы с коэффициентами, взятыми в абсолютной адресации из ячеек B23, D23 и C23:
=$B$23*A7^2+$C$23*A7+$D$23 (в качестве аргумента x берется значение ячейки A7 исходной таблицы).
Скопируем закон преобразования информации ячейки D7 до
ячейки D18 включительно.
В результате получим спектр значений функции y = 1,00x2 +
+ 2,00x + 3,00 на спектре аргументов x в диапазоне значений
ячеек A7:A18 (см. рис. 4.2.1).
В ячейку D19, используя мастер функций fx, запишем результат вычисления функции: =СУММКВРАЗН(B7:B18;D7:D18)
(рис. 4.2.2). Примечание: обозначения Массив_x и Массив_y на
рис. 4.2.2 – математические и не совпадают с обозначениями выполняемого задания.
Рис. 4.2.2
81
4. Метод наименьших квадратов
Добавим на точечный рисунок исходной таблицы уравнение
параболы y = 1,00x2 + 2,00x + 3,00 (рис. 4.2.3).
Рис. 4.2.3
Заметим, что это уравнение параболической регрессии с произвольными значениями коэффициентов: a = 1, b = 2, c = 3. Его
соответствие исходному распределению можно оценить путем
вычисления функции суммы квадратов разностей, значение которой составляет 82 989.
Для определения оптимальных значений коэффициентов a, b
и c следует воспользоваться функцией Поиск решения:
• установить курсор в ячейку C19;
• последовательно выбирая Разработчик, Данные, Поиск
решения, вызвать окно Параметры поиска решения, в котором
установить параметры, как показано на рис. 4.2.4;
• нажать кнопку Найти решение.
После этого в ячейке B23 установится значение -0.22, в ячейке C23 – 2.97, в ячейке D23 – -1.70, в ячейке D19 – 4.764985223
(рис. 4.2.5).
82
4.2. Параболическое уравнение регрессии
Рис. 4.2.4
Рис. 4.2.5
83
4. Метод наименьших квадратов
Это означает, что парабола с коэффициентами a = –0,2, b = 2,97
и c = –1,70 отобразится на графике Параболическая регрессия,
как показано на рис. 4.2.5, при этом значение суммы квадратов
разностей будет минимально и равно 4,764985223.
Таким образом, коэффициенты для параболического уравнения регрессии определены, и для исходного задания уравнение
имеет вид y = –0,2x2 + 2,97x – 1,70.
Чтобы убедиться в правильности решения, щелкнем правой
клавишей мышки по любой точке исходного задания на графике
Параболическая регрессия (см. рис. 4.2.5).
В появившемся окне выберем раздел Добавить линию тренда (рис. 4.2.6).
Рис. 4.2.6
В появившемся окне Формат линии тренда выберем параметры Полиномиальная, Степень 2, показывать уравнение на диаграмме и поместить на диаграмму величину достоверности
аппроксимации (R^2) и нажмем кнопку Закрыть (рис. 4.2.7).
Появившаяся на графике Параболическая регрессия линия
тренда полностью совпадает с графиком построенного уравнения
регрессии y = –0,2x2 + 2,97x – 1,70, совпадает и уравнение линии
тренда y = –0,216x2 + 2,9728x – 1,7045, что является доказательством правильности решения (рис. 4.2.8).
Следует отметить, что значение коэффициента детерминации
R2 = 0,9328 свидетельствует о том, что выбранный вид уравнения регрессии (параболическая функция) больше соответствует
исходному заданию, чем линейная регрессия, так как максимальное значение коэффициента детерминации R2 = 1.
84
4.2. Параболическое уравнение регрессии
Рис. 4.2.7
Рис. 4.2.8
На рис. 4.2.9 показан результирующий график использования в качестве уравнения регрессии параболической функции
y = –0,2x2 + 2,97x – 1,70 и построения линии тренда y = –0,216x2 +
+ 2,9728x – 1,7045 для исходного варианта задания y = f(x).
85
4. Метод наименьших квадратов
Рис. 4.2.9
4.3. Уравнение регрессии в виде полинома
третьей степени
В общем случае уравнение полинома третьей степени имеет
вид y = ax3 + bx2 + cx + d. Пусть исходная заданная функция
y = f(x) имеет вид, показанный в таблице (рис. 4.3.1) в диапазоне
ячеек A7:B18.
Построим точечный график функции Кубическая регрессия
y = f(x).
Рис. 4.3.1
86
4.3. Уравнение регрессии в виде полинома третьей степени
В строке 21 создадим шапку таблицы, как показано на рис. 4.3.1.
В ячейку B24 запишем произвольную константу 1, в ячейку
C24 – произвольную константу 2, в ячейку D24 – произвольную
константу 3, в ячейку E24 – произвольную константу 4.
В ячейку A24 запишем оператор присваивания для сцепленных символьных констант:
=»y=»&ТЕКСТ(B24;»0.00»)&»x^3»&ЕСЛИ(C24<0;ТЕКСТ
(C24;»0.00»);»+»&ТЕКСТ(C24;»0.00»))&»x^2»&ЕСЛИ(D24<0
;ТЕКСТ(D24;»0.00»);»+»&ТЕКСТ(D24;»0.00»))&»x»&ЕСЛИ
(E24<0;ТЕКСТ(E24;»0.00»);»+»&ТЕКСТ(E24;»0.00»))
Ячейке E6 присвоим такое же значение, какое приобретает
ячейка A24, то есть E6=A24.
Тогда, в соответствии с выбранными коэффициентами в ячейках B24 = 1, С24 = 2, D24 = 3 и E24 = 4, получим в ячейках A24
и E6 результат y = 1.00x^3 + 2.00x^2 + 3.00x + 4.00.
Запишем в ячейку E7 уравнение полинома третьей степени с коэффициентами, взятыми в абсолютной адресации из ячеек B24, D24,
C24 и E24: =$B$24*A7^3+$C$24*A7^2+$D$24*A7+$E$24 (в качестве аргумента x берется значение ячейки A7 исходной таблицы).
Скопируем закон преобразования информации ячейки E7 до
ячейки E18 включительно.
В результате получим спектр значений функции y = 1,00x3 +
+ 2,00x2 + 3,00x + 4,00 на спектре аргументов x в диапазоне значений ячеек A7:A18 (см. рис. 4.3.1).
В ячейку E19, используя мастер функций fx, запишем результат вычисления функции: =СУММКВРАЗН(B7:B18;E7:E18)
(рис. 4.3.2). Примечание: обозначения Массив_x и Массив_y на
рис. 4.3.2 – математические и не совпадают с обозначениями выполняемого задания.
Добавим на точечный рисунок исходной таблицы уравнение полинома третьей степени y = 1,00x3 + 2,00x2 + 3,00x + 4,00
(рис. 4.3.3).
Заметим, что это уравнение полинома третьей степени с произвольными значениями коэффициентов: a = 1, b = 2, c = 3, d = 4.
Его соответствие исходному распределению можно оценить с помощью вычисления функции суммы квадратов разностей, значение которой составляет 9 920 119.
87
4. Метод наименьших квадратов
Рис. 4.3.2
Рис. 4.3.3
Для определения оптимальных значений коэффициентов a, b,
c и d следует воспользоваться функцией Поиск решения:
• установить курсор в ячейку E19;
• последовательно выбирая Разработчик, Данные, Поиск
решения, вызвать окно Параметры поиска решения, в котором
установить параметры, как показано на рис. 4.3.4;
• нажать кнопку Найти решение.
88
4.3. Уравнение регрессии в виде полинома третьей степени
Рис. 4.3.4
После этого в ячейке B24 установится значение -0.02, в ячейке
C24 – 0.11, в ячейке D24 – 1.21, в ячейке E24 – 0.58, в ячейке E19
– 1.532467553 (рис. 4.3.5).
Рис. 4.3.5
89
4. Метод наименьших квадратов
Это означает, что полином третьей степени с коэффициентами a = –0,02, b = 0,11, c = 1,21 и d = 0,58 отобразится на графике Кубическая регрессия, как показано на рис. 4.3.5, при этом
значение суммы квадратов разностей будет минимально и равно
1,532467553.
Таким образом, коэффициенты для уравнения регрессии
в виде полинома третьей степени определены, и для исходного
задания уравнение имеет вид y = –0,02x3 + 0,11x2 + 1,21x + 0,58.
Чтобы убедиться в правильности решения, щелкнем правой
клавишей мышки по любой точке исходного задания на графике
Кубическая регрессия (см. рис. 4.3.5).
В появившемся окне выберем раздел Добавить линию тренда (рис. 4.3.6).
Рис. 4.3.6
В появившемся окне Формат линии тренда выберем параметры Полиномиальная, Степень 3, показывать уравнение
на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^2) и нажмем кнопку Закрыть
(рис. 4.3.7).
Появившаяся на графике Кубическая регрессия линия
тренда полностью совпадает с графиком построенного уравнения регрессии y = –0,02x3 + 0,11x2 + 1,21x + 0,58, совпадает
и уравнение линии тренда y = –0,0167x3 + 0,1097x2 + 1,2103x +
+ 0,5758, что является доказательством правильности решения
(рис. 4.3.8).
90
4.3. Уравнение регрессии в виде полинома третьей степени
Рис. 4.3.7
Рис. 4.3.8
Следует отметить, что значение коэффициента детерминации R2 = 0,9784 свидетельствует о том, что уравнение регрессии
91
4. Метод наименьших квадратов
в виде полинома третьей степени соответствует исходному заданию еще лучше, чем параболическая и линейная регрессии,
так как максимальное значение коэффициента детерминации
R2 = 1.
На рис. 4.3.9 показан результирующий график использования в качестве уравнения регрессии полинома третьей степени
y = –0,02x3 + 0,11x2 + 1,21x + 0,58 и построения линии тренда
y = –0,0167x3 + 0,1097x2 + 1,2103x + 0,5758 для исходной заданной функции y = f(x).
Рис. 4.3.9
4.4. Уравнение регрессии в виде
экспоненциальной функции
В общем случае уравнение экспоненциальной функции имеет
вид y = aebx. Пусть заданная исходная функция y = f(x) имеет вид,
показанный в таблице (рис. 4.4.1) в диапазоне ячеек A7:B18.
Построим точечный график функции Экспоненциальная регрессия y = f(x).
В строке 21 создадим шапку таблицы, как показано на
рис. 4.4.1.
В ячейку B25 запишем произвольную константу 1, а в ячейку
C25 – произвольную константу 0.1.
92
4.4. Уравнение регрессии в виде экспоненциальной функции
Рис. 4.4.1
В ячейку A25 запишем оператор присваивания для сцепленных символьных констант:
=»y=»&ТЕКСТ(B25;»0.00»)&»e^(«&ЕСЛИ(C25<0;ТЕКСТ
(C25;»0.00»);»+»&ТЕКСТ(C25;»0.00»))&»)x»
Ячейке F6 присвоим такое же значение, какое приобретает
ячейка A25, то есть F6=A25.
Тогда, в соответствии с выбранными коэффициентами в ячейках B25=1 и C2=0.1, получим в ячейках A25 и F6 результат
y = 1.00e^(+2.00)x.
Запишем в ячейку F7 уравнение экспоненты с коэффициентами, взятыми в абсолютной адресации из ячеек B25
и C25: =$B$25*EXP($C$25*A7) (в качестве аргумента x берется
значение ячейки A7 исходной таблицы).
Скопируем закон преобразования информации ячейки F7 до
ячейки F18 включительно.
В результате получим спектр значений функции y = 1,00e2x
на спектре аргументов x в диапазоне значений ячеек A7:A18
(см. рис. 4.4.1).
В ячейку F19, используя мастер функций fx, запишем результат вычисления функции: =СУММКВРАЗН(B7:B18;F7:F18)
(рис. 4.4.2). Примечание: обозначения Массив_x и Массив_y
93
4. Метод наименьших квадратов
на рис. 4.4.2 – математические и не совпадают с обозначениями
выполняемого задания.
Рис. 4.4.2
Добавим на точечный рисунок исходной таблицы уравнение
экспоненты y = 1,00e2x (рис. 4.4.3).
Рис. 4.4.3
Заметим, что это уравнение экспоненциальной регрессии
с произвольными значениями коэффициентов: a = 1, b = 0,1. Его
соответствие исходному распределению можно оценить путем
вычисления функции суммы квадратов разностей, значение которой составляет 254,2530876.
94
4.4. Уравнение регрессии в виде экспоненциальной функции
Для определения оптимальных значений коэффициентов a и b
следует воспользоваться функцией Поиск решения:
• установить курсор в ячейку F19;
• последовательно выбирая Разработчик, Данные, Поиск
решения, вызвать окно Параметры поиска решения, в котором
установить параметры, как показано на рис. 4.4.4;
• нажать кнопку Найти решение.
Рис. 4.4.4
После этого в ячейке B25 установится значение 5.16, в ячейке
C25 – 0.22, в ячейке F19 – 68.04196941 (рис. 4.4.5).
Это означает, что экспонента с коэффициентами a = 5,16
и b = 0,22 отобразится на графике Экспоненциальная регрессия, как показано на рис. 4.4.5, при этом значение суммы квадратов разностей будет минимально и равно 68,04196941.
Таким образом, коэффициенты для экспоненциального уравнения регрессии определены, и для исходного задания уравнение
имеет вид y = 5,16e0,22x.
95
4. Метод наименьших квадратов
Рис. 4.4.5
Чтобы убедиться в правильности решения, щелкнем правой
клавишей мышки по любой точке исходного задания на графике
Экспоненциальная регрессия (см. рис. 4.4.5).
В появившемся окне выберем раздел Добавить линию тренда (рис. 4.4.6).
Рис. 4.4.6
В появившемся окне Формат линии тренда выберем параметры Экспоненциальная, показывать уравнение на диаграмме
и поместить на диаграмму величину достоверности аппроксимации (R^2) и нажмем кнопку Закрыть (рис. 4.4.7).
Появившаяся на графике Экспоненциальная регрессия
линия тренда не совпадает с графиком построенного уравнения регрессии y = 5,16e0,22x, как и само уравнение линии тренда
y = 4,2912e0,0316x (рис. 4.4.8).
96
4.4. Уравнение регрессии в виде экспоненциальной функции
Рис. 4.4.7
Рис. 4.4.8
Если полученные значения коэффициентов a = 4,2912
и b = 0,0316 предложенного уравнения линии тренда подставить
в ячейки B25 и C25 соответственно, то в ячейке F19 согласно запрограммированной формуле получим значение суммы квадратов разностей 73,14441113.
97
4. Метод наименьших квадратов
Так как значение суммы квадратов разностей 73,14441113 при
коэффициентах a = 4,2912 и b = 0,0316 больше значения суммы
квадратов разностей 68,04196941 при коэффициентах a = 5,16
и b = 0,22, отдадим предпочтение построенному уравнению регрессии y = 5,16e0,22x.
Следует отметить, что значение коэффициента детерминации R2 = 0,0432 свидетельствует о том, что выбранная в качестве
уравнения регрессии экспоненциальная функция не вполне отвечает исходному заданию, так как максимальное значение коэффициента детерминации R2 = 1.
На рис. 4.4.9 показан результирующий график использования в качестве уравнения регрессии экспоненциальной функции
y = 5,16e0,22x и построения линии тренда y = 4,2912e0,0316x для исходной заданной функции y = f(x).
Рис. 4.4.9
4.5. Логарифмическое уравнение регрессии
В общем случае уравнение логарифмической функции имеет
вид y = aln(x) + b. Применение логарифмической функции в качестве уравнения регрессии возможно только в том случае, если
в спектре значений аргумента x (диапазон ячеек A7:А18) отсутствуют нулевые и отрицательные значения.
98
4.5. Логарифмическое уравнение регрессии
Пусть исходная заданная функция y = f(x) имеет вид, показанный в таблице (рис. 4.5.1) в диапазоне ячеек A7:B18.
Построим точечный график функции Логарифмическая регрессия y = f(x).
В строке 21 создадим шапку таблицы, как показано на рис. 4.5.1.
В ячейку B26 запишем произвольную константу 1, а в ячейку
C26 – произвольную константу 2.
Рис. 4.5.1
В ячейку A26 запишем оператор присваивания для сцепленных символьных констант:
=»y=»&ТЕКСТ(B26;»0.00»)&»Ln(x)»&ЕСЛИ(C26<0;ТЕКСТ
(C26;»0.00»);»+»&ТЕКСТ(C26;»0.00»))
Ячейке G6 присвоим такое же значение, какое приобретает
ячейка A26, то есть G6=A26.
Тогда, в соответствии с выбранными коэффициентами в ячейках B26=1 и C26=2, получим в ячейках A26 и G6 результат
y = 1.00Ln(x) + 2.00.
Запишем в ячейку G7 уравнение логарифмической функции
с коэффициентами, взятыми в абсолютной адресации из ячеек
B26 и C26: =$B$26*LN(A7) + $C$26 (в качестве аргумента x берется значение ячейки A7 исходной таблицы).
Скопируем закон преобразования информации ячейки G7 до
ячейки G18 включительно.
В результате получим спектр значений функции y = 1,00ln(x) +
+ 2,00 на спектре аргументов x в диапазоне значений ячеек
A7:A18 (см. рис. 4.5.1).
99
4. Метод наименьших квадратов
В ячейку G19, используя мастер функций fx, запишем результат вычисления функции: =СУММКВРАЗН(B7:B18;G7:G18)
(рис. 4.5.2). Примечание: обозначения Массив_x и Массив_y на
рис. 4.5.2 – математические и не совпадают с обозначениями выполняемого задания.
Рис. 4.5.2
Добавим на точечный рисунок исходной таблицы уравнение
логарифмической функции y = 1,00ln(x) + 2,00 (рис. 4.5.3).
Рис. 4.5.3
Заметим, что это уравнение логарифмической регрессии
с произвольными значениями коэффициентов: a = 1, b = 2. Его соответствие исходному распределению можно оценить путем вычисления функции суммы квадратов разностей, значение которой
составляет 118,0845511.
100
4.5. Логарифмическое уравнение регрессии
Для определения оптимальных значений коэффициентов a и b
следует воспользоваться функцией Поиск решения:
• установить курсор в ячейку G19;
• последовательно выбирая Разработчик, Данные, Поиск
решения, вызвать окно Параметры поиска решения, в котором
установить параметры, как показано на рис. 4.5.4;
• нажать кнопку Найти решение.
Рис. 4.5.4
После этого в ячейке B26 установится значение 1.59, в ячейке
C26 – 3.28, в ячейке G19 – 55.12464582 (рис. 4.5.5).
Это означает, что логарифмическая функция с коэффициентами a = 1,59 и b = 3,28 отобразится на графике Логарифмическая
регрессия, как показано на рис. 4.5.5, при этом значение суммы
квадратов разностей будет минимально и равно 55,12464582.
Таким образом, коэффициенты для логарифмического уравнения регрессии определены, и для исходного задания уравнение
имеет вид y = 1,59ln(x) + 3,28.
Чтобы убедиться в правильности решения, щелкнем правой
клавишей мышки по любой точке исходного задания на графике
Логарифмическая регрессия (см. рис. 4.5.5).
101
4. Метод наименьших квадратов
Рис. 4.5.5
В появившемся окне выберем раздел Добавить линию тренда (рис. 4.5.6).
Рис. 4.5.6
В появившемся окне Формат линии тренда выберем параметры Логарифмическая, показывать уравнение на диаграмме
и поместить на диаграмму величину достоверности аппроксимации (R^2) и нажмем кнопку Закрыть (рис. 4.5.7).
Появившаяся на графике Логарифмическая регрессия линия
тренда полностью совпадает с графиком построенного уравнения
регрессии y = 1,59ln(x) + 3,28, совпадает и уравнение линии тренда y = 1,5852ln(x) + 3,2763, что является доказательством правильности решения (рис. 4.5.8).
102
4.5. Логарифмическое уравнение регрессии
Рис. 4.5.7
Рис. 4.5.8
Следует отметить, что значение коэффициента детерминации R2 = 0,2227 свидетельствует о том, что выбранная в качестве
уравнения регрессии логарифмическая функция не вполне отвечает исходному заданию, так как максимальное значение коэффициента детерминации R2 = 1.
103
4. Метод наименьших квадратов
На рис. 4.5.9 показан результирующий график использования в качестве уравнения регрессии логарифмической функции
y = 1,59ln(x) + 3,28 и построения линии тренда y = 1,5852ln(x) +
+ 3,2763 для исходной заданной функции y = f(x).
Рис. 4.5.9
4.6. Степенное уравнение регрессии
В общем случае уравнение степеннóй функции имеет вид y = axb.
Пусть исходная заданная функция y = f(x) имеет вид, показанный в таблице (рис. 4.6.1) в диапазоне ячеек A7:B18. Построим
точечный график функции Степенная регрессия y = f(x).
В строке 21 создадим шапку таблицы, как показано на рис. 4.6.1.
В ячейку B27 запишем произвольную константу 1, а в ячейку
C27 – произвольную константу 2.
Рис. 4.6.1
104
4.6. Степенное уравнение регрессии
В ячейку A27 запишем оператор присваивания для сцепленных символьных констант:
=»y=»&ТЕКСТ(B27;»0.00»)&»x^(«&ЕСЛИ(C27<0;ТЕКСТ
(C27;»0.00»);»+»&ТЕКСТ(C27;»0.00»))&»)»
Ячейке Н6 присвоим такое же значение, какое приобретает
ячейка A27, то есть H6=A27.
Тогда, в соответствии с выбранными коэффициентами в ячейках B27=1 и C27=2, получим в ячейках A27 и H6 результат
y = 1.00 x^(+2.00).
Запишем в ячейку H7 уравнение степеннóй функции с коэффициентами, взятыми в абсолютной адресации из ячеек B27
и C27: =$B$27*A7^$C$27 (в качестве аргумента x берется значение ячейки A7 исходной таблицы).
Скопируем закон преобразования информации ячейки H7 до
ячейки H18 включительно.
В результате получим спектр значений функции y = 1,00x2
на спектре аргументов x в диапазоне значений ячеек A7:A18
(см. рис. 4.6.1).
В ячейку H19, используя мастер функций fx, запишем результат вычисления функции: =СУММКВРАЗН(B7:B18;H7:H18)
(рис. 4.6.2). Примечание: обозначения Массив_x и Массив_y на
рис. 4.6.2 – математические и не совпадают с обозначениями выполняемого задания.
Рис. 4.6.2
Добавим на точечный рисунок исходной таблицы уравнение
логарифмической функции y = 1,00x2 (рис. 4.6.3).
105
4. Метод наименьших квадратов
Рис. 4.6.3
Заметим, что это уравнение степеннóй регрессии с произвольными значениями коэффициентов: a = 1, b = 2. Его соответствие
исходному распределению можно оценить путем вычисления
функции суммы квадратов разностей, значение которой составляет 53 475.
Для определения оптимальных значений коэффициентов a и b
следует воспользоваться функцией Поиск решения:
• установить курсор в ячейку H19;
• последовательно выбирая Разработчик, Данные, Поиск
решения, вызвать окно Параметры поиска решения, в котором
установить параметры, как показано на рис. 4.6.4;
• нажать кнопку Найти решение.
После этого в ячейке B27 установится значение 4.03, в ячейке
C27 – 0.22, в ячейке H19 – 58.40875563 (рис. 4.6.5).
Это означает, что степеннáя функция с коэффициентами
a = 4,03 и b = 0,22 отобразится на графике Степенная регрессия,
как показано на рис. 4.6.5, при этом значение суммы квадратов
разностей будет минимально и равно 58,4086725.
Таким образом, коэффициенты для степеннóго уравнения регрессии определены, и для исходного задания уравнение имеет
вид y = 4,03x0,22.
Чтобы убедиться в правильности решения, щелкнем правой
клавишей мышки по любой точке исходного задания на графике
Степенная регрессия (см. рис. 4.6.5).
В появившемся окне выберем раздел Добавить линию тренда (рис. 4.6.6).
106
4.6. Степенное уравнение регрессии
Рис. 4.6.4
Рис. 4.6.5
Рис. 4.6.6
107
4. Метод наименьших квадратов
В появившемся окне Формат линии тренда выберем параметры Степенная, показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации
(R^2) и нажмем кнопку Закрыть (рис. 4.6.7).
Рис. 4.6.7
Появившаяся на графике Степенная регрессия линия тренда не совпадает с графиком построенного уравнения регрессии y = 4,03x0,22, как и уравнение линии тренда y = 3,007x0,3367
(рис. 4.6.8).
Рис. 4.6.8
108
4.7. Дробно-линейное уравнение регрессии
Если полученные значения коэффициентов a = 3,007 и b = 0,3367
предложенного уравнения линии тренда подставить в ячейки B27
и C27 соответственно, то в ячейке H19 согласно запрограммированной формуле получим значение суммы квадратов разностей
63,25460832.
Так как значение суммы квадратов разностей 63,25460832 при
коэффициентах a = 3,007 и b = 0,3367 больше значения суммы
квадратов разностей 58,4086725 при коэффициентах a = 4,03
и b = 0,22, то отдадим предпочтение построенному уравнению
регрессии y = 4,04x0,22.
Следует отметить, что значение коэффициента детерминации R2 = 0,2159 свидетельствует о том, что выбранная в качестве
уравнения регрессии степеннáя функция не вполне отвечает исходному заданию, так как максимальное значение коэффициента
детерминации R2 = 1.
На рис. 4.6.9 показан результирующий график использования
в качестве уравнения регрессии степеннóй функции y = 4,03x0,22
и построения линии тренда y = 3,007x0,3367 для исходной заданной
функции y = f(x).
Рис. 4.6.9
4.7. Дробно-линейное уравнение регрессии
В общем случае уравнение дробно-линейной функции имеет
вид y = 1/(ax + b).
109
4. Метод наименьших квадратов
Пусть исходная заданная функция y = f(x) имеет вид, показанный в таблице (рис. 4.7.1) в диапазоне ячеек A7:B18. Построим
точечный график функции Дробно-линейная регрессия y = f(x).
В строке 21 создадим шапку таблицы, как показано на рис. 4.7.1.
В ячейку B28 запишем произвольную константу 1, а в ячейку
C28 – произвольную константу 2.
Рис. 4.7.1
В ячейку A28 запишем оператор присваивания для сцепленных символьных констант:
=»y=»&»1/(«&ТЕКСТ(B28;»0.00»)&»x»&ЕСЛИ(C28<0;
ТЕКСТ(C28;»0.00»);»+»&ТЕКСТ(C28;»0.00»))&»)»
Ячейке I6 присвоим такое же значение, какое приобретает
ячейка A28, то есть I6=A28.
Тогда, в соответствии с выбранными коэффициентами в ячейках B28=1 и C28=2, получим в ячейках A28 и I6 результат
y = 1/(1.00x + 2.00).
Запишем в ячейку I7 уравнение дробно-линейной функции
с коэффициентами, взятыми в абсолютной адресации из ячеек
B28 и C28: =1/($B$28*A7+$C$28) (в качестве аргумента x берется значение ячейки A7 исходной таблицы).
Скопируем закон преобразования информации ячейки I7 до
ячейки I18 включительно.
В результате получим спектр значений функции y = 1/(x + 2)
на спектре аргументов x в диапазоне значений ячеек A7:A18
(см. рис. 4.7.1).
110
4.7. Дробно-линейное уравнение регрессии
В ячейку I19, используя мастер функций fx, запишем результат вычисления функции: =СУММКВРАЗН(B7:B18;I7:I18)
(рис. 4.7.2). Примечание: обозначения Массив_x и Массив_y на
рис. 4.7.2 – математические и не совпадают с обозначениями выполняемого задания.
Рис. 4.7.2
Добавим на точечный рисунок исходной таблицы уравнение
дробно-линейной функции y = 1/(x + 2) (рис. 4.7.3).
Рис. 4.7.3
Заметим, что это уравнение дробно-линейной регрессии
с произвольными значениями коэффициентов: a = 1, b = 2. Его
соответствие исходному распределению можно оценить путем
111
4. Метод наименьших квадратов
вычисления функции суммы квадратов разностей, значение которой составляет 472,9887276.
Для определения оптимальных значений коэффициентов a и b
следует воспользоваться функцией Поиск решения:
• установить курсор в ячейку I19;
• последовательно выбирая Разработчик, Данные, Поиск
решения, вызвать окно Параметры поиска решения, в котором
установить параметры, как показано на рис. 4.7.4;
• нажать кнопку Найти решение.
Рис. 4.7.4
После этого в ячейке B28 установится значение –0.0028,
в ячейке C28 – 0.19, в ячейке I19 – 68.63295607 (рис. 4.7.5).
Это означает, что дробно-линейная функция с коэффициентами a = –0,0028 и b = 0,19 отобразится на графике Дробнолинейная регрессия, как показано на рис. 4.7.5, при этом значение суммы квадратов разностей будет минимально и равно
68,63295607.
112
4.8. Дробно-рациональное уравнение регрессии
Рис. 4.7.5
Таким образом, коэффициенты для дробно-линейного уравнения регрессии определены, и для исходного задания уравнение
имеет вид y = 1/(–0,0028x + 0,19).
Так как в библиотеке линий тренда отсутствует дробно-линейная функция, проверить правильность решения добавлением
линии тренда невозможно.
На рис. 4.7.6 показан результирующий график использования в качестве уравнения регрессии дробно-линейной функции
y = 1/(–0,0028x + 0,19) для исходной заданной функции y = f(x).
Рис. 4.7.6
4.8. Дробно-рациональное уравнение регрессии
В общем случае уравнение дробно-рациональной функции
имеет вид y = x/(ax + b).
113
4. Метод наименьших квадратов
Пусть исходная заданная функция y = f(x) имеет вид, показанный в таблице (рис. 4.8.1) в диапазоне ячеек A7:B18. Построим точечный график функции Дробно-рациональная регрессия
y = f(x).
В строке 21 создадим шапку таблицы, как показано на
рис. 4.8.1.
В ячейку B29 запишем произвольную константу 1, а в ячейку
C29 – произвольную константу 2.
Рис. 4.8.1
В ячейку A29 запишем оператор присваивания для сцепленных символьных констант:
=»y=»&»x/(«&ТЕКСТ(B29;»0.00»)&»x»&ЕСЛИ(C29<0;
ТЕКСТ(C29;»0.00»);»+»&ТЕКСТ(C29;»0.00»))&»)»
Ячейке J6 присвоим такое же значение, какое приобретает
ячейка A29, то есть J6=A29.
Тогда, в соответствии с выбранными коэффициентами в ячейках B29=1 и C29=2, получим в ячейках A29 и J6 результат
y = x/(1.00 x + 2.00).
Запишем в ячейку J7 уравнение дробно-рациональной функции с коэффициентами, взятыми в абсолютной адресации из
ячеек B29 и C29: =A7/($B$29*A7+$C$29) (в качестве аргумента x берется значение ячейки A7 исходной таблицы).
Скопируем закон преобразования информации ячейки J7 до
ячейки J18 включительно.
114
4.8. Дробно-рациональное уравнение регрессии
В результате получим спектр значений функции y = x/(x + 2)
на спектре аргументов x в диапазоне значений ячеек A7:A18
(см. рис. 4.8.1).
В ячейку J19, используя мастер функций fx, запишем результат вычисления функции: =СУММКВРАЗН(B7:B18;J7:J18)
(рис. 4.8.2). Примечание: обозначения Массив_x и Массив_y на
рис. 4.8.2 – математические и не совпадают с обозначениями выполняемого задания.
Рис. 4.8.2
Добавим на точечный рисунок исходной таблицы уравнение
дробно-рациональной функции y = x/(x + 2) (рис. 4.8.3).
Рис. 4.8.3
115
4. Метод наименьших квадратов
Заметим, что это уравнение дробно-рациональной регрессии
с произвольными значениями коэффициентов: a = 1, b = 2. Его соответствие исходному распределению можно оценить путем вычисления функции суммы квадратов разностей, значение которой
составляет 391,9722706.
Для определения оптимальных значений коэффициентов a и b
следует воспользоваться функцией Поиск решения:
• установить курсор в ячейку J19;
• последовательно выбирая Разработчик, Данные, Поиск
решения, вызвать окно Параметры поиска решения, в котором
установить параметры, как показано на рис. 4.8.4;
• нажать кнопку Найти решение.
Рис. 4.8.4
После этого в ячейке B29 установится значение 0.13, в ячейке
C29 – 0.19, в ячейке J19 – 50.09723368 (рис. 4.8.5).
Это означает, что дробно-рациональная функция с коэффициентами a = 0,13 и b = 0,19 отобразится на графике Дробно-рациональная регрессия, как показано на рис. 4.8.5, при этом значение суммы
квадратов разностей будет минимально и равно 50,09723368.
116
4.8. Дробно-рациональное уравнение регрессии
Рис. 4.8.5
Таким образом, коэффициенты для дробно-рационального
уравнения регрессии определены, и для исходного задания уравнение имеет вид y = x/(0,13x + 0,19).
Так как в библиотеке линий тренда отсутствует дробно-рациональная функция, проверить правильность решения добавлением
линии тренда невозможно.
На рис. 4.8.6 показан результирующий график использования в качестве уравнения регрессии дробно-рациональной
функции y = x/(0,13x + 0,19) для исходной заданной функции
y = f(x).
Рис. 4.8.6
117
4. Метод наименьших квадратов
4.9. Уравнение регрессии в виде гиперболы
В общем случае уравнение гиперболы имеет вид y = a/x + b.
Пусть исходная заданная функция y = f(x) имеет вид, показанный в таблице (рис. 4.9.1) в диапазоне ячеек A7:B18. Построим
точечный график функции Гиперболическая регрессия y = f(x).
В строке 21 создадим шапку таблицы, как показано на
рис. 4.9.1.
В ячейку B30 запишем произвольную константу 1, а в ячейку
C30 – произвольную константу 2.
Рис. 4.9.1
В ячейку A30 запишем оператор присваивания для сцепленных символьных констант:
=»y=»&ТЕКСТ(B30;»0.00»)&»/x»&ЕСЛИ(C30<0;ТЕКСТ
(C30;»0.00»);»+»&ТЕКСТ(C30;»0.00»))
Ячейке K6 присвоим такое же значение, какое приобретает
ячейка A30, то есть K6=A30.
Тогда, в соответствии с выбранными коэффициентами в ячейках B30=1 и C30=2, получим в ячейках A30 и K6 результат
y = 1.00/x + 2.00.
Запишем в ячейку K7 уравнение гиперболы с коэффициентами, взятыми в абсолютной адресации из ячеек B30 и C30: =$B$30/
A7+$C$30 (в качестве аргумента x берется значение ячейки A7
исходной таблицы).
118
4.9. Уравнение регрессии в виде гиперболы
Скопируем закон преобразования информации ячейки K7 до
ячейки K18 включительно.
В результате получим спектр значений функции y = 1/x + 2 на спектре аргументов x в диапазоне значений ячеек A7:A18 (см. рис. 4.9.1).
В ячейку K19, используя мастер функций fx, запишем результат вычисления функции: =СУММКВРАЗН(B7:B18;K7:K18)
(рис. 4.9.2). Примечание: обозначения Массив_x и Массив_y на
рис. 4.9.2 – математические и не совпадают с обозначениями выполняемого задания.
Рис. 4.9.2
Добавим на точечный рисунок исходной таблицы уравнение
гиперболы y = 1/x + 2 (рис. 4.9.3).
Рис. 4.9.3
119
4. Метод наименьших квадратов
Заметим, что это уравнение гиперболической регрессии с произвольными значениями коэффициентов: a = 1, b = 2. Его соответствие исходному распределению можно оценить путем вычисления функции суммы квадратов разностей, значение которой
составляет 240,8885697.
Для определения оптимальных значений коэффициентов a и b
следует воспользоваться функцией Поиск решения:
• установить курсор в ячейку K19;
• последовательно выбирая Разработчик, Данные, Поиск
решения, вызвать окно Параметры поиска решения, в котором
установить параметры, как показано на рис. 4.9.4;
• нажать кнопку Найти решение.
Рис. 4.9.4
После этого в ячейке B30 установится значение -5.66, в ячейке
C30 – 7.38, в ячейке K19 – 46.48573172 (рис. 4.9.5).
Это означает, что гипербола с коэффициентами a = –5,66
и b = 7,38 отобразится на графике Гиперболическая регрессия,
как показано на рис. 4.9.5, при этом значение суммы квадратов
разностей будет минимально и равно 46,48573172.
120
4.10. Оценка результатов
Рис. 4.9.5
Таким образом, коэффициенты для гиперболического уравнения регрессии определены, и для исходного задания уравнение
имеет вид y = –5,66/x + 7,38.
Так как в библиотеке линий тренда отсутствует гиперболическая функция, проверить правильность решения добавлением
линии тренда невозможно.
На рис. 4.9.6 показан результирующий график использования в качестве уравнения регрессии гиперболической функции
y = –5,66/x + 7,38 для исходной заданной функции y = f(x).
Рис. 4.9.6
4.10. Оценка результатов
Для оценки полученных результатов, то есть для определения
уравнения регрессии, наилучшим образом соответствующего
121
4. Метод наименьших квадратов
исходной таблично заданной функции y = f(x), необходимо сравнить рассмотренные уравнения регрессии по вычисленным значениям сумм квадратов разностей (ячейки C19:K19) или по значениям коэффициентов детерминации.
При выполнении сравнения целесообразно использовать имеющуюся в среде Excel функцию РАНГ:
• установить курсор в ячейку B20 и записать в нее символьную константу Ранг (рис. 4.10.1, столбцы D, E и F сжаты);
Рис. 4.10.1
• установить курсор в ячейку C20 и вызвать с помощью мастера функций fx функцию РАНГ (рис. 4.10.2);
• в появившемся окне Аргументы функции в окне Число
ввести адрес ячейки C19*, в окне Ссылка – диапазон ячеек
$C$19:$K$19, а в окне Порядок – константу** (например, 2) и нажать кнопку ОК (рис. 4.10.3);
• в ячейке C20 появится число 7, означающее, что число
67,05477855 занимает седьмое место (имеет ранг 7) по возрастанию среди всех чисел диапазона ячеек $C$19:$K$19 (рис. 4.10.4);
* В ячейке C19 находится число (в данном случае 67.05477855), место (ранг) которого среди всех чисел в ячейках диапазона $C$19:$K$19 (обязательно указывать в абсолютной адресации) мы хотим определить.
** Константа 2 определяет порядок ранжирования (по убыванию или по возрастанию). В данном случае на первое место (Ранг 1) помещается наименьшее число.
122
4.10. Оценка результатов
Рис. 4.10.2
Рис. 4.10.3
• теперь скопируем функцию Ранг ячейки C20 на весь диапазон ячеек C20:K20 – в результате получим значения рангов для
всех чисел диапазона ячеек $C$19:$K$19 (см. рис. 4.10.4).
Для удобства результаты ранжирования можно свести в таблицу (рис. 4.10.5).
На основе данных таблицы (см. рис. 4.10.5) можно сделать
вывод, что из всех девяти исследованных уравнений регрессии
исходной таблично заданной функции y = f(x) наилучшим образом соответствует уравнение кубического полинома y = –0,02x3 +
123
4. Метод наименьших квадратов
+ 0,11x2 + 1,21x + 0,58, так как вычисленная для него сумма квадратов разностей минимальна и равна 1,532467553.
Рис. 4.10.4
Рис. 4.10.5
Этот результат подтверждается и значением коэффициента детерминации, величина которого максимальна: R = 0,9784.
Результат выполнения задания показан на рис. 4.10.6.
Рис. 4.10.6
124
Рекомендуемая литература
1. Никифоров С. Н. Информатика для I курса: учеб. пособие. Ч. I /
С. Н. Никифоров; СПбГАСУ. – СПб., 2011. – 100 c.
2. Никифоров С. Н. Информатика: учеб. пособие. Ч. II / С. Н. Никифоров; СПбГАСУ. – СПб., 2016. – 84 c.
3. Гарнаев А. Ю. Использование MS Excel и VBA в экономике и финансах / А. Ю. Гарнаев. – СПб. : БХВ-Петербург, 1999. – 336 с.
4. Гарбер Г. З. Основы программирования на Visual Basic и VBA
в Excel 2007 / Г. З. Гарбер. – М. : СОЛОН-ПРЕСС, 2008. – 192 с.
125
Оглавление
ВВЕДЕНИЕ.............................................................................................. 3
1. ВЫЧИСЛЕНИЕ ОПРЕДЕЛЕННОГО ИНТЕГРАЛА........................ 4
1.1. Метод прямоугольников................................................................ 4
1.2. Метод трапеций............................................................................. 8
1.3. Метод Симпсона.......................................................................... 10
1.4. Использование пользовательской формы UserForm................ 13
1.5. Калькулятор для вычисления определенных интегралов........ 15
1.6. Создание надписи (изображения) на объекте OptionButton.... 22
1.7. Выводы......................................................................................... 22
2. РЕШЕНИЕ НЕЛИНЕЙНЫХ УРАВНЕНИЙ.................................... 24
2.1. Метод половинного деления (дихотомия)................................. 24
2.2. Метод Ньютона (метод касательных)........................................ 28
2.3. Метод хорд................................................................................... 31
2.4. Решение второго нелинейного уравнения................................. 33
2.5. Калькулятор для решения нелинейных уравнений.................. 37
2.6. Создание надписи (изображения) на объекте OptionButton.... 44
2.7. Выводы......................................................................................... 45
3. ИНТЕРПОЛЯЦИЯ............................................................................. 46
3.1. Нахождение коэффициентов полинома решением СЛАУ....... 46
3.2. Интерполяционный многочлен Лагранжа................................. 51
3.3. Интерполяционный многочлен Ньютона.................................. 56
3.4. Калькулятор для вычисления значений
интерполяционных полиномов......................................................... 59
4. МЕТОД НАИМЕНЬШИХ КВАДРАТОВ......................................... 74
4.1. Линейное уравнение регрессии................................................. 74
4.2. Параболическое уравнение регрессии....................................... 80
4.3. Уравнение регрессии в виде полинома третьей степени......... 86
126
Оглавление
4.4. Уравнение регрессии в виде экспоненциальной функции...... 92
4.5. Логарифмическое уравнение регрессии.................................... 98
4.6. Степенное уравнение регрессии.............................................. 104
4.7. Дробно-линейное уравнение регрессии.................................. 109
4.8. Дробно-рациональное уравнение регрессии...........................113
4.9. Уравнение регрессии в виде гиперболы...................................118
4.10. Оценка результатов.................................................................. 121
Рекомендуемая литература.................................................................. 125
127
Учебное издание
Никифоров Сергей Николаевич
ИНФОРМАТИКА
Часть III.
ПРИКЛАДНОЕ ПРОГРАММИРОВАНИЕ
Учебное пособие
Редактор Т. В. Ананченко
Корректор М. А. Молчанова
Компьютерная верстка Е. В. Королевой
Подписано к печати 29.12.2016. Формат 60×84 1/16. Бум. офсетная.
Усл. печ. л. 7,5. Тираж 150 экз. Заказ 233. «С» 127.
Санкт-Петербургский государственный архитектурно-строительный университет.
190005, Санкт-Петербург, 2-я Красноармейская ул., д. 4.
Отпечатано на ризографе. 190005, Санкт-Петербург, ул. Егорова, д. 5/8, лит. А.
128
Документ
Категория
Без категории
Просмотров
2
Размер файла
5 035 Кб
Теги
prikl, prog, nikiforov
1/--страниц
Пожаловаться на содержимое документа