Использование возможностей VBA при расчетах в Excel

1.2. изучение возможностей разработки баз данных в Excel.

2. Приборы и оборудование:

2.1. Методические указания.

2.2. ПЭВМ типа IBM PC/XT.

2.3. MS Excel.

2.4. Visual Basic for Application.

3. Порядок выполнения работы:

3.1. Изучить основные теоретические сведения (Приложение А). Запустите приложение Excel, сохраните документ. Перейдите в редактор VBA.

3.2. Задание 1. Дан табличный документ указанного ниже вида. Необходимо:

1) создать шаблонную часть этого документа с помощью табличного процессора Excel;

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

Рисунок 1 – Таблица заполнения данными.

Технология выполнения

Анализ таблицы показывает, что вид деятельности, прогноз прибыли и фактическая прибыль являются исходными данными, отклонение (в процентах и в сумме) – расчетными. Кроме того, рассчитываются итоги по некоторым графам таблицы.

Создание шаблона табличного документа

Шаблон создается на обычном рабочем листе в Excel. При этом необходимо только зарезервировать свободные ячейки для занесения следующих данных: месяц, год, потребительское общество, сумма издержек, товарооборот, уровень издержек. Поскольку заранее неизвестно количество потребительских обществ, то ячейки для итогов и ФИО экономиста заранее не резервируются. Рабочий лист переименован в Отчет. Реализация такого шаблона представлена на рисунке 2.

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

Рисунок 2 – Шаблон-заготовка табличного документа

Математическая модель решения задачи

Кроме организации ввода исходных данных и вывода их в некоторые ячейки электронной таблицы, программа должна производить расчет отклонений и итоговых значений по графам «Сумма издержек – план», «Сумма издержек – факт», «Товарооборот – план», «Товарооборот – факт», «Уровень издержек – план», «Уровень издержек – факт», «Отклонение по уровню». Для расчетных величин используем следующие переменные:

Nomer – номер текущей строки таблицы; SP – планируемая сумма издержек; SF – фактическая сумма издержек; TP – планируемый товарооборот; TF – фактический товарооборот; IP – планируемый уровень издержек; EF – фактический уровень издержек; ItogSP – накопление итога по столбцу «планируемая сумма издержек»; ItogSF – накопление итога по столбцу «фактическая сумма издержек»; ItogTP – накопление итога по столбцу «планируемый товарооборот»; ItogTF – накопление итога по столбцу «фактический товарооборот»; ItogIP – накопление итога по столбцу «планируемый уровень издержек»; ItogEF – накопление итога по столбцу «фактический уровень издержек».

С учетом введенных обозначений расчетные формулы будут иметь следующий вид:

1) для отклонений: [Отклонение в %] = (F – P) / P * 100, [Отклонение в сумме] = F – P

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

2) для итогов по прогнозу и факту: ItogP = ItogP + P, ItogF = ItogF + F.

Эти формулы реализуют алгоритм получения итоговой суммы методом накопления, когда величина прогноза (факта), соответствующая очередному виду деятельности, добавляется к сумме соответствующих величин по уже рассмотренным видам деятельности. Назовем эти суммы промежуточными. Когда будут обработаны все виды деятельности, промежуточные суммы превратятся в окончательные – итоговые. В начале этого процесса (до того, как будет рассчитываться первая промежуточная сумма) переменные ItogP и ItogF равны нулю;

3) для итогов по отклонениям: [итоговое отклонение в процентах] = (ItogF – ItogP) / ItogP * 100, [итоговое отклонение в сумме] = ItogF – ItogP.

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

Разработка интерфейса пользователя

Каждому текстовому полю поменяем стандартное имя (TextBox) на более понятное (см. рисунок 3):

TextBox1 – MesTextBox – ввод месяца; TextBox2 – YearTextBox – ввод года; TextBox3 – FIOTextBox – ввод фамилии, имени и отчества экономиста; TextBox4 – POTextBox – ввод названия потребительского общества; TextBox5 – SPTextBox – планируемая сумма издержек; TextBox6 – SFTextBox – фактическая сумма издержек; TextBox7 – TPTextBox – планируемый товарооборот; TextBox8 – TFTextBox – фактический товарооборот; TextBox9 – IPTextBox – планируемый уровень издержек; TextBox10 – EFTextBox – фактический уровень издержек.

Рисунок 3 – Разработанная форма в рабочем состоянии

Программа на языке VBA

'Объявление переменных и констант

Const StrNomer = 7 'количество строк для заголовка таблицы

Dim Nomer As Long 'номер очередной строки таблицы (потребительское общество)

Dim SP As Long

Dim SF As Long

Dim TP As Long

Dim TF As Long

Dim IP As Long

Dim EF As Long

Dim ItogSP As Long

Dim ItogSF As Long

Dim ItogTP As Long

Dim ItogTF As Long

Dim ItogIP As Long

Dim ItogEF As Long

Dim StrName1 As String

Dim StrName2 As String

'Процедура инициализации формы

Private Sub UserForm_Initialize()

Worksheets(«Отчет»).Activate

MesTextBox.SetFocus

End Sub

'Процедура считывания заголовочных данных и вывода их в ячейки электронной таблицы Private Sub CommandButton3_Click()

'Ввод данных для новой отчетной таблицы

With ActiveSheet

Range(«E3»).Value = MesTextBox.Text

Range(«G3»).Value = YearTextBox.Text

End With

ActiveWorkbook.SaveAs ("Отклонение фактического уровня издержек обращения от плана за " + MesTextBox.Text + «месяц. xls»)

Nomer = 1

ItogSP = 0

ItogSF = 0

ItogTP = 0

ItogTF = 0

ItogIP = 0

ItogEF = 0

End Sub

'Процедура обработки данных по видам деятельности

Private Sub CommandButton2_Click()

StrName1 = Trim(Str(StrNomer + Nomer))

With ActiveSheet

Range("A" + StrName1).Value = Nomer

Range("B" + StrName1).Value = POTextBox.Text

SP = Val(SPTextBox.Text)

Range("C" + StrName1).Value = SP

ItogSP = ItogSP + SP

SF = Val(SFTextBox.Text)

Range("D" + StrName1).Value = SF

ItogSF = ItogSF + SF

TP = Val(TPTextBox.Text)

Range("E" + StrName1).Value = TP

ItogTP = ItogTP + TP

TF = Val(TFTextBox.Text)

Range("F" + StrName1).Value = TF

ItogTF = ItogTF + TF

IP = Val(IPTextBox.Text)

Range("G" + StrName1).Value = IP

ItogIP = ItogIP + IP

EF = Val(EFTextBox.Text)

Range("H" + StrName1).Value = EF

ItogEF = ItogEF + EF

Range("I" + StrName1).Value = EF – IP

'Выполнение автозаполнения с текущей строки таблицы на следующую строку

StrName2 = Trim(Str(StrNomer + Nomer + 1))

Set Range1 =.Range("A" + StrName1 +":I" + StrName1)

Set Range2 =.Range("A" + StrName1 +":I" + StrName2)

Range1.AutoFill Destination:=Range2

Range("A" + StrName2 +":I" + StrName2).ClearContents

End With

'Очистка полей формы для ввода очередных данных

POTextBox.Text = ""

SPTextBox.Text = ""

SFTextBox.Text = ""

TPTextBox.Text = ""

TFTextBox.Text = ""

IPTextBox.Text = ""

EFTextBox.Text = ""

POTextBox.SetFocus

Nomer = Nomer + 1

End Sub

'Закрытие формы, подведение итогов и вывод фамилии экономиста

Private Sub CommandButton1_Click()

UserForm1.Hide

StrName1 = Trim(Str(StrNomer + Nomer))

With ActiveSheet

Range("A" + StrName1).Value = «Итого:»

Range("C" + StrName1).Value = ItogSP

Range("D" + StrName1).Value = ItogSF

Range("E" + StrName1).Value = ItogTP

Range("F" + StrName1).Value = ItogTF

Range("G" + StrName1).Value = ItogIP

Range("H" + StrName1).Value = ItogEF

Range("I" + StrName1).Value = ItogEF – ItogIP

StrName2 = Trim(Str(StrNomer + Nomer + 2))

Range("A" + StrName2).Value = «Экономист:»

Range("G" + StrName2).Value = FIOTextBox.Text

End With

End Sub

Использование программы в режиме выполнения и получения результатов.

Перед запуском программы сохраним рабочую книгу под именем отчет1.xls. Кнопку «Создать отчетную таблицу» следует нажимать один раз после ввода заголовочной информации, кнопка «Добавить строку» нажимается каждый раз после ввода данных по очередному виду деятельности. После ввода всех данных необходимо нажать кнопку «Закончить», а затем переключиться в окно Microsoft Excel. На рабочем листе появится результат, аналогичный рисунку 4.

Рисунок 4 – Шаблон табличного документа, заполненный данными.

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

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

3.5. Задание 4. Создать программу, которая по введенным в соответствующие текстовые поля формы данным автоматизирует ввод данных на отпуск товара с некоторого склада и формирует счет-фактуру за определенный период времени. Кроме этого, программа должна вычислять данные по столбцам, отмеченным звездочками. Вывод данных предусмотреть на лист excel.

3.6. Создайте кнопку или панель в Вашем проекте для непосредственного вызова приложения. Добавьте комментарии. Текст и результат выполнения программы занести в отчёт.

3.7. Сформулируйте выводы по проделанной работе.

4. Содержание отчёта:

4.1. Тема работы.

4.2. Цель работы.

4.3. Приборы и оборудование.

4.4. Порядок выполнения работы.

4.5. Выводы.

4.6. Контрольные вопросы.

5. Контрольные вопросы:

5.1. Как создать шаблонную часть документа с помощью табличного процессора Excel?

5.2. Рекомендуемые сочетания первых трех символов имен?

5.3. Как задать количество строк для заголовка таблицы?

5.4. Как считать заголовочные данные и вывести их в ячейки электронной таблицы?

5.5. Как выполнить автозаполнение с текущей строки таблицы на следующую строку?

5.6. Как выполняется очистка полей формы для ввода очередных данных?

Приложение А

Теоретические сведения

Элементы управления

При разработке приложений, кроме рассмотренных ранее элементов управления, можно использовать элемент «полоса прокрутки» и «счетчик». Рассмотрим эти элементы.

Элемент управления ScrollBar (полоса прокрутки) и элемент управления SpinButton (счетчик) по своим функциональным возможностям аналогичны друг другу. Поэтому свойства их рассматриваем совместно.

Value – возвращает текущее значение полосы прокрутки (только целые неотрицательные числа);

Min – минимальное значение полосы прокрутки (только целые неотрицательные числа);

Max – максимальное значение полосы прокрутки (только целые неотрицательные числа);

SmallChange – устанавливает шаг изменения значения при щелчке по одной из стрелок полосы прокрутки;

Enabled – допустимые значения: True (пользователь может изменить значение полосы) и False (в противном случае);

Visible – допустимые значения: True (полоса прокрутки отображается во время выполнения программы) и False (в противном случае).

Создание VBA-программы

При разработке формы будет необходимо выполнять программирование примечаний ячеек листа. Данная процедура выполняется с помощью метода AddComment объекта Range. Этот метод имеет следующий синтаксис: AddComment (Text), где Text – текст комментария.

Лабораторная работа №8