Лабораторная работа № 5 вычисление периодических выплат. Построение диаграммы

Цель работы:

1. Научиться выводить результаты табулирования функции в элемент управления ListBox (список).

2. Строить диаграмму, тип которой выбирается в группе переключателей.

3. Выполнять расчёты с помощью финансовой функции.

4. Выполнять проверку введённых значений в поля TextBox (текстовые поля)

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

UserForm_Initialize	Активизирует диалогово окно
CommandButton1_Click (кнопка Вычислить)	1. Проверяет вводимые данные. 2. Проверяет согласованность вводимых данных. В случае ошибок отображаются соответствующие сообщения. 3. Выводит заголовки. 4. Определяет число точек, в которых будут табулироваться процентные ставки. 5. Последовательно находит размеры выплат и выводит на рабочий лист и в двухмерный массив. 6. Заполняет список. 7. Строит диаграмму.

Методы объекта Chart

Метод ChartWizard применяется для построения и изменения существующей диаграммы. С помощью метода ChartWizard нельзя задать все возможные свойства диаграммы.

Синтаксис:

ChartWizard(Source, Gallery, Format, PlotBy, _

                 CategoryLabels, SeriesLabels, HasLegend, Title, _

                 CategoryTitle, ValueTitle, ExtraTitle)

Аргументы:

Source	Диапазон, по которому строится диаграмма
Gallery	Тип диаграммы. Допустимые значения: xlBar, xlColumn, xlLine, xlPie, xlRadar, xlXYScatter, xlCombination, xl3DArea, xl3DBar, xl3DColumn, xl3DLine, xl3DPie, xl3DSurface, xlDoughnut или xlDefaultAutoFormat
Format	Формат для данного типа диаграммы. Каждый тип имеет до 10 форматов
PlotBy	Расположение данных. Допустимые значения: xlRows (строки), xlColumns (столбцы)
CategoryLabels	Указывает число строк (столбцов) с метками категорий (т.е. данных, отводимых под ось абсцисс)
SeriesLabels	Указывает число строк (столбцов) с метками рядов (т.е. данных, отводимых под заголовки)
HasLegend	Отображение легенды: True – отображать легенду, False – не отображать легенду
Title	Название диаграммы
CategoryTitle	Название оси абсцисс
ValueTitle	Название оси ординат
ExtraTitle	Название z-оси для трёхмерных диаграмм

 

Наиболее часто используемые методы объекта Chart, изменяющие детали диаграммы:

Axes	Возвращает семейство Axes, предназначенное для установки различных свойств осей. Для возвращения уникальной оси необходимо использовать следующий синтаксис метода: Axes(Type, Group) Аргументы: Type – определяет тип оси. Допустимые значения: xlCategory, xlSeries, xlValue   Group – допустимые значения xlPrimary, xlSecondary
SeriesCollection	Возвращает семейство объектов Series или объект этого семейства. Синтаксис (для одного объекта): SeriesCollection(Index) Index – имя или номер элемента   Синтаксис (для семейства объектов): SeriesCollection
SetSourceData	Устанавливает источник данных диаграммы. Ситаксис: SetSourceData(Source, PlotBy)   Source – диапазон, на основе которого строится диаграмма   PlotBy – расположение данных. Допустимые значения: xlRows (расположение по строкам), xlColumns (расположение по столбцам)

 

Для создания диаграммы используется следующий метод семейства ChartObjects:

Add(Left, Top, Width, Height)

Left, Top – координаты левого верхнего угла диаграммы

Width, Height – ширина и высота диаграммы

 

Форма с элементами управления

Вид рабочего листа после выполнения расчётов

Private Sub CommandButton1_Click()

Dim p As Double

Dim i_нрс As Double

Dim i_кпс As Double

Dim i_шаг As Double

Dim k As Integer

Dim i As Integer

Dim n As Integer

Dim m As Integer

Dim A() As Double

Dim Проценты() As Double

Dim ПроцентыФормат() As Variant

Dim ЭлементыСписка() As Variant

Dim Area As Object

'Проверка: вводятся ли в поля диалогового окна числа

If IsNumeric(TextBox1.Text) = False Then

MsgBox "Ошибка в ссуде", vbInformation, "Выплаты"

TextBox1.SetFocus

Exit Sub

End If

If IsNumeric(TextBox2.Text) = False Then

MsgBox "Ошибка в числе выплат", vbInformation, "Выплаты"

TextBox2.SetFocus

Exit Sub

End If

If IsNumeric(TextBox3.Text) = False Then

MsgBox "Ошибка в начальной процентной ставке", _

vbInformation, "Выплаты"

TextBox3.SetFocus

Exit Sub

End If

If IsNumeric(TextBox4.Text) = False Then

MsgBox "Ошибка в конечной процентной ставке", _

vbInformation, "Выплаты"

TextBox4.SetFocus

Exit Sub

End If

If IsNumeric(TextBox5.Text) = False Then

MsgBox "Ошибка в шаге процентной ставки", _

vbInformation, "Выплаты"

TextBox5.SetFocus

Exit Sub

End If

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

p = CDbl(TextBox1.Text)

k = CInt(TextBox2.Text)

i_нпс = CDbl(TextBox3.Text) / 100

i_кпс = CDbl(TextBox4.Text) / 100

i_шаг = CDbl(TextBox5.Text) / 100

'Проверка согласованности ввода значений

If i_кпс < i_нпс Then

MsgBox "Конечная процентная ставка меньше начальной", _

vbExclamation, "График"

TextBox3.SetFocus

Exit Sub

 End If

If i_кпс < i_нпс + i_шаг Then

MsgBox "Шаг слишком большой!" & Chr(13) & _

"Табулируется только одно значение.", _

vbExclamation, "График"

TextBox5.SetFocus

Exit Sub

 End If

If i_шаг <= 0 Then

MsgBox "Шаг должен быть положительным!", _

vbExclamation, "График"

TextBox5.SetFocus

Exit Sub

 End If

'Очистка рабочего листа от результатов предыдущих вычислений

ActiveSheet.Cells.Clear

'

m = (i_кпс - i_нпс) / i_шаг + 1

'Установка границ динамических массивов

ReDim A(1 To m)

ReDim Проценты(1 To m)

ReDim ПроцентыФормат(1 To m)

'

With ActiveSheet

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

.Range("A1").Value = "Процентная ставка"

.Range("A2").Value = "Размер выплаты"

.Range("A3").Value = "Ссуда"

.Range("A4").Value = "Число выплат"

End With

'

'Форматирование заголовков записей

'

ActiveSheet.Range("A1:A4").Select

With Selection

.HorizontalAlignment = xlGeneral

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 30

.ShrinkToFit = False

.MergeCells = False

End With

With Selection.Interior

.ColorIndex = 36

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

End With

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

With ActiveSheet

For i = 1 To m

Проценты(i) = i_нпс + i_шаг * (i - 1)

A(i) = Pmt(Проценты(i), k, -p)

A(i) = Format(A(i), "##")

ПроцентыФормат(i) = Format(Проценты(i), "#.0%")

.Cells(1, i + 1).Value = ПроцентыФормат(i)

.Cells(2, i + 1).Value = A(i)

  

Next i

.Range("B3").Value = p

.Range("B4").Value = k

End With

ReDim ЭлементыСписка(1 To m, 0 To 1)

For i = 1 To m

ЭлементыСписка(i, 0) = ПроцентыФормат(i)

ЭлементыСписка(i, 1) = A(i)

Next i

With ListBox1

.Clear

.ColumnCount = 2

.List = ЭлементыСписка()

.ListIndex = 0

End With

If OptionButton1.Value = True Then График xlColumn, 6

If OptionButton2.Value = True Then График xlLine, 10

End Sub

 

Sub График(ТипГрафика As Integer, Формат As Integer)

Dim Area As Object

Dim n As Integer

Set Area = ActiveSheet.Cells(1, 2).CurrentRegion

n = Area.Columns.Count

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

ActiveChart.ChartWizard Source:= _

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

Gallery:=ТипГрафика, Format:=Формат, _

PlotBy:=xlRows, CategoryLabels:=1, _

SeriesLabels:=0, HasLegend:=False, _

Title:="Диаграмма", CategoryTitle:="Справка", _

ValueTitle:="Выплаты", ExtraTitle:=""

End Sub

 

Private Sub CommandButton2_Click()

UserForm1.Hide

End Sub

 

Private Sub CommandButton3_Click()

ActiveSheet.Cells(1, 1).CurrentRegion.Clear

End Sub

 

Private Sub UserForm_Initialize()

OptionButton1.Value = True

With CommandButton1

.Default = True

.ControlTipText = "Вычисления и составление отчёта на рабочем листе"

End With

With CommandButton2

.Cancel = True

.ControlTipText = "Кнопка отмены"

End With

CommandButton3.ControlTipText = "Очистка рабочего листа"

End Sub