ПОСЛЕДОВАТЕЛЬНОСТЬ ВЫПОЛНЕНИЯ ПРАКТИЧЕСКОГО ЗАДАНИЯ

ЦЕЛЬ РАБОТЫ

Цель работы – изучить возможности применения финансовых функций MS Excel; рассмотреть особенности программного ввода формулы в ячейку рабочего листа; рассмотреть особенности работы с диаграммами MS Excel.

ЗАДАНИЕ

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

Рис. 1. Главная форма

При нажатии кнопки «Добавить» на рабочий лист добавляется информация о дате платежа, сумме платежа и ставке дисконтирования.

При нажатии кнопки «Расчет NPV» рассчитывается чистая приведенная стоимость.

При нажатии кнопки «Диаграмма» формируется диаграмма платежей.

При нажатии кнопки «Выход» главная форма скрывается.

ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ

Одно из основных применений Excel — это анализ данных. А для анализа данных часто удобно использовать диаграммы с их специальными возможностями. На практике задачи по автоматизации создания множества похожих друг на друга диаграмм (обычно на основе информации, полученной из базы данных) возникают очень часто.

С диаграммами в Excel существует некоторая терминологическая путаница. То, что на графическом интерфейсе русского Excel называется диаграммой (меню Вставка | Диаграмма), по-английски называется графиком (Chart) и ему соответствует объект Chart. В объектной модели Excel предусмотрен также и объект Diagram, но он представляет скорее схему отношений (то, что при помощи графического интерфейса русского Excel можно добавить при помощи меню Вставка | Схематическая диаграмма).

Под диаграммой будет понимать то же, что и у создателей русского Excel — обычный график. Диаграммы в Excel создаются при помощи объекта Chart. Вначале лучше этот объект объявить: Dim oChart As Chart. Дальше можно создавать диаграмму. Создание диаграммы производится при помощи много раз использованного нами приема — вызова метода Add() коллекции Charts: Set oChart = ActiveWorkbook.Charts.Add(, ActiveSheet).

В принципе, диаграмма уже создана, но поскольку никакие ее свойства не определены, она выглядит как пустой лист. Чтобы она обрела содержание, необходимо выполнить еще несколько действий. Первое (и единственное обязательное действие) — определить источник данных для диаграммы, для чего предназначен метод SetSourceData(). В качестве источника может выступать только объект Range (он передается в качестве первого и единственного обязательного параметра этого метода). Второй параметр (необязательный) определяет, в каком порядке считывать данные — сначала по столбцам, потом по строкам или наоборот. Для всех остальных параметров будут приняты значения по умолчанию.

Однако на практике нужно определить еще хотя бы тип диаграммы (по умолчанию она будет выглядеть как "обычная гистограмма", т. е. ряд из столбиков разной длины). Для этой цели используется свойство ChartType, для которого разработчиками предусмотрено 73 значения. Например, чтобы преобразовать диаграмму в обычный график, можно использовать код вида: oChart.ChartType = xlLineMarkers.

Еще одна очень распространенная задача — добавить дополнительные ряды на диаграмму. Для этой цели необходимо создать и получить ссылку на объект Series — ряд, а потом для ряда определить свойство Values (ему передается в качестве значения объект Range).

Пользователи часто говорят, что им необходимо создавать диаграммы не на отдельном листе, а на том же листе, на котором расположены данные. По умолчанию диаграмма создается в оперативной памяти и помещается на отдельный лист. Если нам необходимо поместить ее на уже существующий лист, то в этом случае ее надо создать вначале на отдельном листе, а затем переместить при помощи метода Location. Отдельный лист, созданный для диаграммы, при этом автоматически исчезнет: oChart.Location xlLocationAsObject, "Лист1".

Остальные многочисленные параметры диаграммы настраиваются при помощи свойств и методов объектов Chart.

ПОСЛЕДОВАТЕЛЬНОСТЬ ВЫПОЛНЕНИЯ ПРАКТИЧЕСКОГО ЗАДАНИЯ

Наше приложение будет состоять из рабочей книги MS Excel и формы «Форма управления проектом», на которой будут размещены следующие объекты: объекты Label, объекты TextBox, в которые будем вводить дату платежа, сумму платежа и ставку дисконтирования; кнопка «Добавить», кнопка «Расчет NPV», кнопка «Диаграмма», кнопка «Выход».

При открытии рабочей книги появляется рабочий лист, на котором поместим кнопку «Вызов формы управления» (рис. 3), при нажатии на которую будет открываться главная форма приложения. Также на рабочем листе создадим шаблон для ввода данных о дате платежа, сумме платежа и ставки дисконтирования.

1. Создадим рабочую книгу MS Excel. Поместим на рабочий лист 1 кнопку «Вызов формы управления». Создадим шаблон для ввода данных.

2. Создадим главную форму «Форма управления проектом». Выберем команду Insert – UserForm. Разместим на ней все необходимые объекты и определим для них все необходимые параметры. Форма примет следующий вид (рис. 2):

Рис. 2. Главная форма "Форма управления проектом"

При нажатии кнопки «Добавить» на рабочий лист добавляется информация о дате платежа, сумме платежа и ставке дисконтирования.

При нажатии кнопки «Расчет NPV» рассчитывается чистая приведенная стоимость.

При нажатии кнопки «Диаграмма» формируется диаграмма платежей на новом листе «Диаграмма».

При нажатии кнопки «Выход» главная форма скрывается.

3. Напишем код программы:

 

‘При открытии рабочей книги появляется главная форма приложения

Private Sub Workbook_Open()

UserForm1.Show

End Sub

‘Обработчик события нажатия кнопки «Вызов формы управления»

Private Sub CommandButton1_Click()

UserForm1.Show

End Sub

‘Объявление глобальной переменной Row – номер текущей строки

Public Row As Integer

 

‘Обработчик события нажатия кнопки «Добавить»

Private Sub CommandButton1_Click()

Row = Application.CountA(ActiveSheet.Columns(1)) + 1

Лист1.Cells(Row, 1).Value = TextBox1.Value

Лист1.Cells(Row, 2).Value = TextBox2.Value

Лист1.Cells(2, 3).Value = CDbl(TextBox3.Value)

End Sub

 

‘Обработчик события нажатия кнопки «Расчет NPV»

Private Sub CommandButton2_Click()

Row = Application.CountA(ActiveSheet.Columns(1))

Лист1.Cells(2, 4).FormulaLocal = "=ЧПС(C2;B2:B" & CStr(Row) & ")"

‘Ячейка будет желтого цвета

If Лист1.Cells(2, 4).Value < 0 Then Cells(2, 4).Interior.ColorIndex = 6

‘Ячейка будет красного цвета

If Лист1.Cells(2, 4).Value > 0 Then Cells(2, 4).Interior.ColorIndex = 3

End Sub

 

‘Обработчик события нажатия кнопки «Диаграмма»

Private Sub CommandButton3_Click()

Dim chrt As Chart

Sheets("Лист1").Select

Row = Application.CountA(ActiveSheet.Columns(1))

Sheets("Диаграмма").Select

ActiveWindow.SelectedSheets.Delete

Sheets("Лист1").Select

Set chrt = Application.Charts.Add

chrt.SetSourceData Лист1.Range("B2:B" & CStr(Row))

chrt.ChartType = xlLineMarkers

ActiveChart.SeriesCollection(1).XValues = Лист1.Range("A2:A" & CStr(Row))

 

'Линия тренда

ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlLinear, Forward:=3, Backward:=0, DisplayEquation:=True, DisplayRSquared:=False).Select

 

'Создать новую диаграмму на листе «Диаграмма»

chrt.Location xlLocationAsNewSheet, "Диаграмма"

End Sub

 

‘Обработчик события нажатия кнопки «Выход»

Private Sub CommandButton4_Click()

UserForm1.Hide

End Sub

 

4. Проверим работу приложения. Откроем книгу Excel. Нажмем кнопку «Вызов формы управления». В результате появится главная форма приложения «Форма управления проектом» (рис. 3).

Рис. 3. Главная форма приложения

5. Введем дата платежа, сумму платежа и ставку дисконтирования. Нажмем кнопку «Добавить». Новые данные добавятся в конец списка (рис. 4).

Рис. 4. Нажатие кнопки "Добавить"

6. Нажмем на кнопку «Расчет NPV». В результате будет выполнен расчет чистой приведенной стоимости по данным таблицы (рис. 5).

Рис. 5. Нажатие кнопки "Расчет NPV"

7. Нажмем на кнопку «Диаграмма». В результате будет создана диаграмма на рабочем листе «Диаграмма» (рис. 6).

Рис. 6. Нажатие кнопки "Диаграмма"