Урок 5

Тема: Решение уравнения, зависящего от параметра. Построение диаграммы

Цель урока

В уроке разрабатывается приложение, позволяющее находить корни уравнения в зависимости от параметра, а также строить диаграмму, демонстрирующую зависимость корня от параметра. Конструируя данное приложение вы научитесь:

1 Вводить формулы при помощи элемента управления RefEdit

2 Находить методом GoalSeek корни уравнения, зависящего от параметра

3 Устанавливать параметры метода GoalSeek

4 Создавать прогрессию на рабочем листе при помощи метода DataSeries

5 Программировать протаскивание маркера заполнения выделенного диапазона на рабочем листе при помощи метода AutoFill

6 Строить диаграммы

Теория

В этом разделе решается уравнение

х³ - х - b = 0

при различных величинах параметра b, изменяющихся от начального до конечного значения с указанным шагом. При каждом конкретном значении параметра корень уравнения находится методом GoalSeek. Для нахождения корней уравнения при различных значениях параметра, эти значения вводятся в диапазон ячеек рабочего листа, формулы левых частей уравнении - в другой диапазон, третий диапазон отводится под неизвестную. В диапазон с неизвестными вводятся начальные приближения корня, а потом последовательно перебирая соответствующие ячейки из этих трех диапазонов методом GoalSeek находятся соответствующие корни.

Практика

Для решения задачи нахождения корней уравнения, зависящего от параметра, и построения диаграммы зависимости корней от параметра с помощью редактора пользовательских форм создадим диалоговое окно нелинейное

уравнение с параметром (рис. У5.1).

Рис. У5.1. Диалоговое окно Нелинейное уравнение с параметром

О решаемом уравнении предполагается, что оно преобразовано к виду, когда только левая часть зависит от неизвестной и параметра. Правая же часть — постоянна. При вводе левой части уравнения в поле ввода элемента управления RefEdit вместо переменной х надо давать ссылку на ячейку в2, а вместо параметра — А2. Кроме того, формула должна быть составлена по тем же правилам, по которым пишутся формулы рабочего листа. Например, для упомянутого выше уравнения в поле надо ввести:

=B2^3-B2-A2

либо эквивалентную формулу с абсолютными ссылками на ячейки.

Обсудим, как приведенная ниже программа решает перечисленные задачи и что происходит в программе

UserForm Initialize	1.	Активизирует диалоговое окно.
	2.	Назначает клавише <Esc> функцию кноп-
		ки Отмена, а клавише <Enter> — вычис-
		лить.
Нажатие кнопки вычис	3.	При выполнении щелчка в соответст-
лить запускает на выпол-		вующей ячейке в поле элемента управле-
нение процедуру		ния RefEdit вводится абсолютная ссылка
CommandButton1 Click		на эту ячейку. При выделении ячейки
		формулой и протаскивании ее маркера
		заполнения вниз вдоль столбца для полу-
		чения корректного результата нужна
		абсолютная, а относительная ссылка.
		С этой целью из строки с формулой, присвоенной строковой переменной Формула,
		удаляются все знаки $, тем самым превращая все абсолютные ссылки в от- носительные.
	4.	Удаляются с рабочего листа ранее введенные
		данные и создаются заголовки полей отчета.
	5.	Устанавливаются предельное число итераций
		и относительная погрешность метода
		GoalSeek.
	6.	Методом DataSeries в диапазоне создается арифметическая прогрессия изменения
		значений параметра от начального до
		конечного значения с указанным шагом.
	7.	Вводится начальное приближение в диапазон.
	8.	Вводится в диапазон левая часть уравнения
		при различных значениях параметра.
	9.	Последовательно для каждой ячейки,
		имеющей формулу с левой частью урав-
		нения, методом GoalSeek, находится корень
		уравнения.
	10.	Вызывается процедура ПостроениеГрафика
		для построения графика (рис. У5.2).
Нажатие кнопки отмена	11.	Закрывает диалоговое окно.
запускает на выполнение
процедуру
ConimandButton2 Click
Процедура	12.	Строит график.
ПостроениеГрафика

Рис. У5.2. Отчет, выводимый на рабочем листе программой решения уравнения с параметром

Private Sub ConmiandButton1_Click ()

' Процедура нахождения корней уравнения с параметром

Dim ПараметрНач As Double

Dim ПараметрКон As Double

Dim ПараметрШаг As Double

Dim НачПрибл As Double

Dim ПраваяЧасть As Double

Dim Формула As String

' ПараметрНач - начальное значение параметра

' ПараметрКон - конечное значение параметра

' ПараметрШаг - шаг изменения параметра

' НачПрибл - начальное приближение корня, общее для всех

' значений параметра

' ПраваяЧасть - правая часть уравнения

' Формула - левая часть уравнения. Уравнение записывается так,

' что неизвестная входит только в левую часть,

а ' правая часть – постоянна

Dim i As Integer

Dim Длина As Integer

Dim n As Integer

' i, n. Длина - вспомогательные переменные

' Ввод исходных данных из диалогового окна

With UserForm1

ПараметрНач = CDb1(.TextBox1.Text)

ПараметрКон = CDb1(.TextBox2.Text)

ПараметрШаг = CDb1(.TextBox3.Text)

НачПрибл = CDb1(.TextBox4.Text)

Формула = Trim(CStr(.RefEdit1.Text))

ПраваяЧасть = CDb1(.TextBox5.Text)

End With

' Элемент управления RefEdit при вводе в него ссылок на ячейки

' щелчком в соответствующей ячейке возвращает абсолютные ссылки на

' эти ячейки.

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

' содержащей формулу левой части уравнения, вниз по столбцу

' для получения корректного результата необходима не абсолютная, а

' относительная ссылка. Для преобразования абсолютной ссылки в

' относительную ниже в операторе цикла Do-Loop из строки с формулой,

' присвоенной строковой переменной Формула, удаляются все знаки

' абсолютной ссылки $

i = 1

Do

If Mid(Фopмyлa, i, 1) = "$" Then

Длина = Len(Формула)

Формула = Left(Формула, i - 1) + Right(Формула, Длина - i)

Else

i = i + 1

End If

Loop While i <= Len(Формула)

' Очистка трех первых столбцов рабочего листа

Range("А:С").Clear

' Форматирование заголовка отчетной таблицы.

' Установка:

' ширины первых трех столбцов

' высоты первой строки

' выравнивание

Range("A:A").ColumnWidth = 12

Range("B:B").ColumnWidth = 14

Range("С:С").ColumnWidth = 17

Range("A1:C1").Select

With Selection

.RowHeight = 37

.HorizontalAlignment = x1General

.VerticalAlignment = x1Top

.WrapText = True

.Font.Bold = True

.Font.Size = 11

End With

' Ввод заголовков трех первых столбцов рабочего листа

Range("A1").Value = "Параметр"

Range("B1").Value = "Переменная"

Range("C1").Value = "Левая часть уравнения"

' Установка параметров метода Подбор параметра

With Application

.MaxIterations = 1000

.MaxChange = 0.0001

End With

' Ввод в столбец А значений параметра

Range("A2").Value = ПараметрНач

Range("A2").Select

Selection. DataSeries Rowco1:=x1Columns, _

Type:=x1Linear, Step:=пapaмeтpШar, Stop:=ПараметрКон

' Определение числа заполненных строк

n = Range("A2").CurrentRegion.Rows.Count

' Ввод в диапазон столбца В начального приближения

Range(Cells(2, 2), Cells(n, 2)).Value = НачПрибл

' Ввод в диапазон столбца С левой части уравнения

Range ("C2").Formula = Формула

Range ("С2").AutoFill

Destination:=Range(Cells(2, 3), Cells(n, 3)), _

Type:=x1FillDefault

' Последовательное решение уравнений с помощью команды Подбор параметра

For i = 2 То n

Cells(i, 3).GoalSeek Соа1:=ПраваяЧасть,

ChangingCell:=Cells(i, 2)

Next i

' Вызов процедуры для построения графика

ПостроениеГрафика

End Sub

Private Sub ConroandButton2_Click()

' Процедура закрытия диалогового окна

Usertorm1 .Hide

End Sub

Private Sub UserFom Initialize()

' Процедура активизации диалогового окна

' Клавише <Enter> назначена функция кнопки Вычислить.

' Клавише <Еsс> назначена функция кнопки Отмена.

CommandButton1.Default = True

CoininandButton2 . Cancel = True

UserForm1.Show

End Sub

Sub Построение Графика ()

' Процедура построения графика

Dim n As Integer

' n - число строк диапазона, по которому строится график

Dim Диапазон Оси Y As Object

Dim Диапазон Оси Х As Object

Dim Имя Диаграммы As String

Dim Диапазон Y As String

Dim Диапазон Х As String

Dim Имя Листа As String

n = ActiveSheet. Cells(1, 1).CurrentRegion. Rows.Count

Имя Листа = ActiveSheet. Name

' Удаление всех ранее построенных диаграмм с рабочего листа

ActiveSheet. ChartObjects. Delete

' Создание новой диаграммы и установка ее типа

Charts.Add

ActiveChart.ChartType = x1LineMarkers

' Определение диапазона, отводимого под значения функции

Диапазону = "В2:В" & LTrimCStr (n) )

Set Диапазон ОсиY = Sheets(Имя Листа).Range(Диапазон Y)

' Определение диапазона, отводимого под значения аргумента

Диапазон Х = "А2:А" & LTrim(CStr (n) )

Set Диапазон Оси Х = Sheets(Имя Листа).Range(Диапазон Х)

' Построение графика

ActiveChart.SetSourceData Source:=Диапаэон Оси Y, PlotBy:=x1Column

ActiveChart.SenesCoilection(1).XValues – Диапазон Оси Х

ActiveChart.Location Where:=x1LocationAsObject, Name:=Имя Листа

With ActiveChart

.HasTitle = True

.ChartTitle.Characters.Text = "Зависимость корня от параметра"

.Axes (x1Category, x1Primary) .HasTitle = True

.Axes(x1Category, x1Primary).AxisTitle.Characters.Text = "Параметр"

.Axes(x1Value, x1Primary).HasTitle = True

.Axes(x1Vaiue, x1Primary).AxisTitle.Characters.Text - "Корень"

End With

ActiveChart.HasLegend = False

Имя Диаграммы = ActiveSheet .ChartObjects (1).Name

' Перемещение диаграммы и изменение ее размеров

ActiveSheet.Shapes(Имя Диаграммы).ScaleHeight 1.17, msoFalse,

msoScalcEromBottomRight

ActiveSheet.Shapes(Имя Диаграммы).IncrementLeft 124.5

ActiveSheet.Shapes(Имя Диаграммы).IncrementTop -25.5

End Sub

Процедура построениеГрафика выглядит довольно громоздкой. При ее написании лучше всего воспользоваться MacroRecorder, который переведет производимые пользователем вручную действия по построению диаграммы на язык VBA. Итак, для активизации MacroRecorder выберите команду Сервис, Макрос, Начать запись (Tools, Macro, Record New Macro) и запустите MacroRecorder на запись. После задания всех параметров в появившемся диалоговом окне Запись макроса (Record Macro) и нажатия кнопки ОК появится плавающая панель инструментов с кнопкой Остановить запись (Stop Recording). Теперь все производимые действия будут записываться до тех пор, пока не будет нажата эта кнопка. Постройте диаграмму по следующему алгоритму:

1. Нажмите кнопку Мастер диаграмм (Chart Wizard) панели инструментов Стандартная (Standard).

2. В появившемся окне первого шага мастера диаграмм на вкладке Стандартные (Standard Types) в списке Тип (Chart type) выберите График (Line), а в группе Вид (Chart sub-type) — четвертый элемент. Нажмите кнопку Далее > (Next >).

3. В появившемся окне второго шага мастера диаграмм на вкладке Диапазон данных (Data Range) выберите переключатель Ряды в столбцах (Series in Columns) и в поле Диапазон (Data range) введите ссылку на диапазон =лист1! $в$2:$в$12, отводимый под значения функции, график которой строится. В поле Подписи оси Х (Category (X) axis labels) вкладки Ряд (Series) введите ссылку на диапазон =Лист1! $А$2:$А$12, отводимый под значения аргумента. Нажмите кнопку Далее > (Next >).

4. В появившемся окне третьего шага мастера диаграмм на вкладке Заголовки (Titles) в поле Название диаграммы (Chart title) введите Зависимость корня от параметра, в поле Ось Х (категорий) (Category (X) axis) — Параметр, в поле Ось Y (значений) (Category (Y) axis) — Корень. На вкладке Легенда (Legent) снимите флажок Добавить легенду (Show Legent). Нажмите кнопку Далее > (Next >).

5. В появившемся окне четвертого шага мастера диаграмм в группе Поместить диаграмму на листе (Place chart) выберите переключатель имеющемся (As object in). Нажмите кнопку Готово (Finish).

6. В построенной диаграмме ухватитесь за маркер изменения размера и увеличьте размер диаграммы. После этого, ухватившись за маркер переноса, переместите диаграмму в требуемое место на рабочем листе.

Перечисленные выше действия будут переведены MacroRecorder в следующий макрос.

Sub Макрос1()

' Макрос1 Макрос

' Макрос записан 27.04.99 (Андрей)

Charts.Add

ActiveChart.ChartType = x1LineMarkers

ActiveChart.SetSourceData

Source:=Sheets("Лист1").Range("B2:B12"), _

PlotBy: =x1Columns

ActiveChart.SeriesCollection(1).XValues = "=Лист1!R2C1:R12C1"

ActiveChart.Location Where:=x1LocationAsObject, Namе:="Лист1"

With ActiveChart

.HasTitle = False

.Axes(x1Category, x1Primary).HasTitle = False

.Axes(x1Value, x1Primary).HasTitle = False

End With

With ActiveChart

.HasTitle = True

.ChartTitle.Characters.Text = "Зависимость корня от параметра"

.Axes(x1Category, x1Primary).HasTitle = True

.Axes(x1Category, x1Primary).AxisTitle.Characters.Text --= "Параметр"

.Axes(xlValue, x1Primary).HasTitle = True

.Axes(xlValue, x1Primary).AxisTitle.Characters.Text = "Корень"

End With

ActiveSheet.ChartObjects("Диагр. 14").Activate

ActiveChart.ChartArea.Select

ActiveSheet.Shapes("Диагр. 14").ScaleHeight 1.17, msoFalse, _

msoScaleFromBottomRight

ActiveSheet.Shapes("Диагр. 14").IncrementLeft 124.5

ActiveSheet.Shapes("Диагр. 14").IncrementTop -25.5

End Sub

Не трудно заметить, что данный макрос построения диаграмм сильно привязан к конкретному диапазону данных, рабочему листу и имени диаграммы. В процедуре построение Графика этот макрос существенно видоизменен для того, чтобы избавиться от этих обременительных ограничений. Кроме того, в ней предусмотрено удаление всех ранее построенных на рабочем листе диаграмм с тем, чтобы они не наслаивались друг на друга при многократном запуске приложения на одном и том же рабочем листе.

Процесс создания процедур для построения графиков с использованием MacroRecorder, особенно для избавления процедуры от привязки к конкретному диапазону данных, рабочему листу и имени диаграммы, кому-то может показаться излишне искусственным. Им советуем для построения графиков использовать метод ChartWizard, дающий более быстрый путь к цели. Ниже приведены необходимые изменения в процедуре Построение-Графика для метода ChartWizard. Уже поверхностное сравнение старой и новой версий процедуры Построение Графика показывает, что благодаря методу СhartWizard процедура стала компактнее. Кроме того, теперь она использует меньше вспомогательных переменных, необходимых при построении диаграммы.

Sub Построение Графика() I

' Процедура построения графика

Dim n As Integer

' n - число строк диапазона, по которому строится график

n = ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count

' Удаление всех ранее построенных диаграмм с рабочего листа

ActiveSheet.ChartObjects.Delete

'(195, 30, 200, 190) - координаты области, где строится диаграмма

ActiveSheet.ChartObjects.Add(195, 30, 200, 190).Select

' Построение диаграммы

ActiveChart ChartWizard Source:=

Range(Cells(2, 1), Cells(n, 2)), _

Gallery.=x1Line, Format:=4, PlotBy:=x1Columns, _

CategoryLabels:=1, SeriesLabels ."0, HasLegend-=False,

Title:="Зависимость корня от параметра",

CategoryTitle:="Параметр",

ValueTitle:="Корень", ExtraTitle:=""

End Sub

Самостоятельное задание

Разработать приложение с диалоговым окном Уравнение с двумя параметрами (рис. У5.3), которое позволит:

1. Находить при всех возможных комбинациях значений параметров a и b корни уравнения с одной неизвестной, зависящего от этих двух параметров. Например,

2. х³ - ax² + b = 0.

3. Строить график, показывающий зависимость корня от значений параметров а и b.