Vba_расчеты

2. Решение в Excel нелинейного уравнения

2.1.Встроенные и дополнительные средства решения

ВExcel имеется средство «Подбор параметра», которым часто пользуются для решения нелинейного уравнения. Рассмотрим следующий пример.

Пример 2.1. Найти с относительной погрешностью 0,001 корень урав-

(см. рис. 2.1). Далее можно выбрать: Данные, Анализ «что-если», Подбор параметра и искать решение в ручном режиме. Но мы воспользуемся следующим макросом и запустим его:

Sub Primer_2_1()

Application.MaxChange = 0.0001 ' «относительная погрешность»

Range("x") = 0. ' назначаем начальное приближение res = Range("f").GoalSeek(Goal:=0, ChangingCell:=Range("x")) Debug.Print res, Range("x"), Range("f")

End Sub

В результате в окне отладки получим:

True 1,08491453462296 -5,22345966353088E-05,

т.е. True – корень уравнения найден, он равен x 1,0849145, а «погрешность» относится не к корню, а к значению функции при найденном значении x.

Но если мы зададим лучшее начальное приближение (ближе к искомому корню) Range("x") = 0.65 , то результат окажется таков:

False -14355105132924,3 -2,95814278232263E+39,

31

т.е. корень не найден, подбор параметра не сработал. Не стоит особо удивляться этому, ведь это средство не предназначено специально для решения уравнений. Заметим, что если (см. задачу 1 в разделе 2.2) менять x на интервале (0; 1) с шагом dx=0.1, то при x=0.7, …,1.0 подбор параметра сработает, и корень будет найден.

Существуют надѐжные методы численного решения нелинейного уравнения f (x) 0 , один простой метод из их числа - метод деления отрезка пополам. Его идея состоит в том, что отрезок, на концах которого непрерывная функция имеет противоположные знаки, делится пополам, а затем выбирается для дальнейшего поиска та его половина, на концах которой знаки функции различные. Процесс деления продолжается до тех пор, пока отрезок [ , ], содержащий корень, не станет достаточно малым. Представим алгоритм данного метода на VBA в виде функции:

Function DOP(Fch As String, Xch As String, a As Double, _

b As Double, Optional Eps As Double = 0.0001, Optional Iter As _ Integer) As Boolean

' Fch, Xch - имена ячеек с функцией и аргументом,

'a, b - левая и правая границы отрезка, содержащего корень,

'Eps - относительная погрешность корня |X* - X|/max(|a|,|b|)<Eps,

'Iter - число выполненных приближений.

Dim Alpha As Double, Beta As Double, Sfa As Integer Dim X As Double, D As Double, Mabs As Double

'++++++++++++++++++++++++++++++++++++++++++++++++ Range(Xch) = a

Sfa = Sgn(Range(Fch)) Range(Xch) = b

Bisec = False

If Sfa * Sgn(Range(Fch)) > 0 Then Exit Function Mabs = IIf(Abs(a) > Abs(b), Abs(a), Abs(b))

If Eps < 0.000000000000005 Then Eps = 0.000000000000005 Alpha = a: Beta = b

Iter = 0

32

Do: Iter = Iter + 1

D = 0.5 * (Beta - Alpha)

X = Alpha + D: Range(Xch) = X

If D / Mabs <= Eps Then Exit Do

Select Case Sgn(Range(Fch)) * Sfa

End Select

Loop

DOP = True

End Function

Sub Primer_2_2( ) ' Пример 2.2. Запускающая процедура:

Dim N As Integer

res = DOP(Fch:="f", Xch:="x", a:=0, b:=3, Eps:=0.0001, Iter:=N) Debug.Print res, Range("x"), Range("f"), N

End Sub

Результат, выведенный в окно отладки:

2.2. Задачи

результаты.

2.Цилиндрическая автоцистерна для перевозки жидкости имеет в сечении форму эллипса с полуосями: a=1м, b=0.75м. Длина L цистерны составляет 4м. Цистерна установлена горизонтально. Для тарировки мерного щупа, опускаемого вдоль вертикальной оси эллипса, рассчи-

тать высоту h уровня налитой жидкости для объѐма V через каждые 0.5 м3 объѐма V. Построить график зависимости высоты h уровня от объѐма V. Указание. Зависимость объѐма V жидкости, налитой в

33

Eg 1.11эв, XTI 3.

Указание. Напряжение на источнике тока равно: V E RS I(V,T) .

6.Асинхронный мотор вращает колесо воздуходувки. Рассчитать зависимость числа оборотов n и величину скольжения s ротора мотора

7.Рассчитать зависимость максимальной скорости v движения автомобиля на прямой передаче с двигателем внутреннего сгорания от

нение скорости ограничить диапазоном v 50/ 3.6 м/ сек;115/ 3.6 м/ сек .

8. Решить задачу 5 для автомобиля с двигателем, удельная тяга которого (на прямой передаче) изменяется по закону

T v 3 v10 0.2 v10 3 н / кг .

35

3. Представление данных в виде графиков и диаграмм 3.1. Построение графиков

Построение в Excel графика функции Y Y(X) по значениям Yi в

равноотстоящих точках Xi [a, b] отрезка [a, b] изменения аргумента выполняется следующим образом: выделяют столбец значений Yi , а затем используют цепочку вызовов Вставка, Диаграммы, График. При этом график будет построен не на шкале аргумента X , а на шкале индексов (номеров точек). Чтобы придать этой шкале вид оси аргумента X , меняют подпись горизонтальной оси, подставляя в еѐ диапазон числа из столбца X . Полученная картинка будет соответствовать графику Y Y(X) для однозначной функции при равноотстоящих значениях Xi .

В общем случае, когда график строится по массивам точек Xi и Yi ,

следует использовать точеные диаграммы. Рассмотрим их применение при решении следующей задачи.

Пример 3.1. Построить график функции Y F(X), заданной формулой «=X/КОРЕНЬ(1 + X ^ 2)» в ячейке B2. Для запоминания значений аргумента X, изменяющегося от -2 до +2 с шагом 0,1 , использовать ячейку A2.

Решение примера 3.1 начнѐм с оформления ячеек на рабочем листе: введѐм в ячейки A1 и B1 соответственно символы X и F, а ячейкам A2 и B2 присвоим имена X и F (выбрав предварительно Формулы на ленте). Введѐм формулу в ячейку B2. Оформим вычисление значений аргументов и функции в виде следующего макроса:

End Sub

После исполнения макроса выделим заполненный числами диапазон

A3:B43 и выполним цепочку Вставка, Диаграммы, Точечная (с гладки-

ми кривыми) – на диаграмме должен появиться график (см. рис. 3.1).

Рис. 3.1.

3.2. Изображение плоских параметрических кривых

Точечные диаграммы (ТД) идеально подходят для изображения плоских параметрических кривых – кривых, X(t),Y(t)-координаты которых зависят от значения параметра t . Кривые r r( ) , заданные в полярных координатах, также могут рассматриваться как параметрические (с параметром ), если принять, что X r( ) cos( ), Y r( ) sin( ) . Рассмотрим построение таких кривых на следующем примере.

Пример 3.2. Изобразить на точечной диаграмме Excel полярные кривые r 20 a cos2 sin2 , [0, 2 ], /36, при каждом из значений параметра a 60,40, 20,0 .

Решение примера 3.2. Вычисление Xi , Yi -координат кривых выполним в макросе, и разместим координаты в столбцах A и B таблицы Excel, а значение параметра a будем выводить в столбец C в строку со значениями координат первой точки каждой кривой. Учтѐм особенность построения гладкий кривых (см. рис. 3.2) - координаты каждой последующей кривой (новой кривой, строящейся независимо от предыдущей) отделяются пустой строкой от координат предыдущей кривой.

37

3.3. Задачи

Варианты 1–10. Рассчитать x,y-координаты точек кривых (заданных в параметрической форме) для параметра t, изменяющегося на отрезке [tнач; t кон] с шагом t, при каждом из указанных значений a.

Варианты 11–20. Рассчитать координаты точек кривых (заданных в полярных координатах) для полярного угла , изменяющегося на отрезке [ нач; кон] с шагом , при каждом из указанных значений a.

Контрольные значения, вычисленные при 2-ом значении аргумента и 1-ом значении параметра a.