Ведение личной переписки. 9 страница

Рис. 6

выполнить двойной щелчок на объекте "Эта книга", Рис. 7.

Рис 7

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

Окно кода может быть вызвано и для каждого рабочего листа. Технология его создания аналогична изложенной выше, только после вызова окна проекта двойной щелчок следует выполнить на объекте "Листn", где n. – номер рабочего листа. Далее можно вставить процедуру и ввести код программы.

2. Постановка задачи

Создать электронную таблицу "Расчет коммунальных услуг" следующей структуры:

Задача решается с использованием двух листов Excel.

На первом листе размещена основная таблица, содержащая столбцы: №, Фамилия И.О., Адрес, Жил. площадь, К-во проживающих, Участник ВОВ, Телефон, Квартплата

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

Требуется рассчитать стоимость месячной платы за коммунальные услуги (колонка 8 таблицы 1).

Таблица1 - Расчет коммунальных услуг (Лист 1)

Таблица 2 - Тарифы за услуги (Лист 2)

Для расчета квартплаты составить функцию на языке VBA. В этой функции построить оператор IF для правильного учета платы за телефон. Кроме этого учесть, что для участников ВОВ квартплата снижена на 50%.

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

3.1.Изучть настоящие методические указания.

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

3.2.На листе 2 рабочей книги создать таблицу тарифов за коммунальные услуги.

3.3. Построить формулу для расчета коммунальных услуг с учетом данных, приведенных в таблицах 8.1 и 8.2.

3.4. На языке Visual Basic for Application (VBA) разработать функцию KVPL для расчета коммунальных услуг с учетом оговоренных выше условий.

Для вызова среды VBA используется команда Сервис/Макрос/Редактор Visual Basic. Далее в меню "Вставка" выбрать команду "Модуль". Затем – в меню "Insert" выбрать команду "Procedure" и в открывшемся окне создать функцию пользователя kvpl.

Функция пользователя KVPL имеет следующий вид:

Function kvpl(pl, k, uv, t) As Single

x3 = Worksheets("Лист2").Range("c3").Value

x4 = Worksheets("Лист2").Range("c4").Value

x5 = Worksheets("Лист2").Range("c5").Value

x6 = Worksheets("Лист2").Range("c6").Value

x7 = Worksheets("Лист2").Range("c7").Value

x8 = 0

If t = 1 Then

x8 = Worksheets("Лист2").Range("c8").Value

End If

If t = 2 Then

x8 = Worksheets("Лист2").Range("c9").Value

End If

kvp = pl * (x3 + x4) + k * (x5 + x6) + x7 + x8

If uv = 1 Then

kvp = kvp / 2

End If

End Function

3.5. В столбец Н (колонка 8 таблицы 8.1) вставить разработанную функцию KVPL с помощью мастера функций. При вводе параметров этой функции использовать данные листа 1 рабочей книги.

3.6. Получить итоговую сумму в таблице 8.1.

3.6.Проанализировать полученные результаты.

3.7.Оформить отчет.

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

Отчет по работе должен содержать:

4.1.Цель работы;

4.2.Постановку задачи и исходные данные;

4.3.Структуру таблицы;

4.5.Выводы по работе.

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

5.1.Поясните структуру электронной таблицы.

5.2.Поясните технологию создания макроса с помощью VBA.

5.3.Как вызвать функцию пользователя?

5.4.Как отредактировать функцию, составленную на VBA?

5.5.Как ввести параметры функции пользователя?

5.6.Как из функции VBA прочитать значение ячейки рабочего листа?

Лабораторная работа № 22. Решение на VBA задачи "Расчет периодических выплат по вкладам в банке"

Цель работы: 1) Изучение технологии создания и оформления таблицы для расчета периодических выплат по вкладам в банке в системе MS Excel.

2) Изучение принципов разработки на языке VBA процедур и вызова их с рабочего листа

3) .Разработка процедуры на VBA для расчета периодических выплат по вкладам в банке.

Объем работы 2 часа

Постановка задачи. Банк принимает вклады от населения на следующих условиях:

a) на срок 1 месяц под 12% годовых;

b) на срок 3 месяца под 13 % годовых;

c) на срок 6 месяца под 14% годовых;

d) на срок 12 месяца под 14,5% годовых.

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

Перед вкладчиком стоит задача: какой из вариантов выбрать для размещения в банке вклада суммой в 1'500'000 руб. сроком на один год с целью получения наибольшей прибыли.

Требуется:

1) на рабочем листе "Лист1" в Excel разработать структуру электронной таблицы, построить необходимые формулы и провести соответствующие расчеты;

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

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

1.1.Создание и оформление таблицы для расчета периодических выплат по вкладам в банке в системе Microsoft Excel.

Структура электронной таблицы может быть следующей:

Таблица 2.1

Структура электронной таблицы для расчета накоплений денежных средств в банке

Для выполнения требуемых расчетов в таблицу необходимо ввести соответствующие формулы. Так для варианта а) сумма, накапливаемая по процентам банка, для ячейки D3 может быть вычислена по формуле: =C3*E3/12. Тогда в ячейке С3 "Сумма" должна увеличится в начале второго месяца на эту сумму (накопленную по процентам банка).

Для варианта b) продолжительность срока составляет 3 месяца. Поэтому основная сумма должна увеличиваться на сумму процентов только в конце каждого "срока", в начале же каждого второго и третьего месяца сумма, накапливаемая на дополнительном счете должна увеличиваться на 5%. Построенные с учетом этого формулы приведены в табл.2.1.

Таблица 2.1- Электронная таблица с формулами

Результаты расчета, полученные на основе разработанной таблицы, приведены ниже в таблице 2.3.

Таблица 2.3 - Электронная таблица с результатами расчета накоплений денежных средств в банке

2. Построение процедуры на VBA.

Для составления процедуры на VBA необходимо правильно вызвать редактор Visual Basic. Вызов редактора можно выполнить следующим образом:

1.Выполним команду "Макрос" из меню "Сервис", далее – "Редактор Visual Basic".

2.В открывшемся окне "Microsoft Visual Basic VBA..." в меню "Вставка" выполним команду "Модуль".

3.В том же окне "Microsoft Visual Basic VBA..." в меню "Вставка" выполним команду "Процедура".

4.В открывшемся окне "Вставка процедуры" введем имя создаваемой функции "Vk", в качестве "Типа" процедуры выберем "Проседура (Sub)"; а в качестве "Область определения" − "Общая (Public) ".Щелкнем по кнопке "Ok".

5.В окне редактора "VBA..." появятся два следующих оператора:

Public Sub Vk()

End Function

обозначающие начало и конец процедуры.

6.В эту процедуру введем необходимые исправления и операторы языка.

Перейдем на "Лист2", вызовем окно редактора VBA и введем операторы языка.

В окончательном виде процедура получит следующий вид:

Public Sub Vk()

With Worksheets(2

Cells(2, 2).Value = Date

'Cells(2, 2) = DateValue("09, 12, 2007")

For i = 2 To 13

x = Cells(i, 3)

proc = Cells(i, 5)

Cells(i + 1, 4) = x * proc / 12

Cells(i + 1, 3) = Cells(i, 3) + x * proc / 12

Next i

sumproc = 0

k = 2

For i = 3 To 14

x = Cells(k, 6)

proc = Cells(i - 1, 8)

sumproc = sumproc + (sumproc * 0.05 + x * proc) / 12

Cells(i, 7) = sumproc

If (i - 2) Mod 3 = 0 Then

k = k + 3

Cells(k, 6) = Cells(k - 3, 6) + sumproc

sumproc = 0

End If

Next i

End With

End Sub

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

1) вызвать панель "Формы" (Вид, Панели инструментов, Формы);

2) с помощью мыши выбрать "Кнопку" в панели "Формы" на рабочем листе;

3) обвести курсором мыши небольшой участок на рабочем листе (в пределах одной ячейки), появиться кнопка и окно "Назначить макрос объекту", в котором будет выведено имя разработанной процедуры Vk;

4) выбрать процедуру Vk и нажать кнопку "Ok";

5) ввести имя кнопки "Расчет".

В таблице появятся результаты расчета, табл. 2.4.

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

3.1.Ознакомится с методическими указаниями, изложенными в п.п. 1, 2;

3.2.Загрузить программу MS Excel;

3.3.Построить таблицу 2.1;

3.4.Осмыслить и ввести в таблицу формулы;

3.5.Проанализировать полученные результаты;

3.6.На листе 2 ввести аналогичную таблицу, но без формул;

3.7.Вызвать редактор VBA;

3.8.Ввести текст процедуры;

3.9.Отладить процедуру;

3.10.Построить кнопку вызова процедуры;

3.11.Выполнить расчет таблицы с помощью кнопки;

3.12.Проанилизировать результаты и сравнить их с результатами, полученными в табл. 2.3.

3.13.Оформить отчет

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

Отчет по работе должен содержать:

4.1.Цель работы;

4.2.Постановку задачи и исходные данные;

4.3.Расчетную таблицу с результатами;

4.4.Текст программы на VBA;

4.5.Выводы по работе.

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

5.1.Поясните структуру электронной таблицы.

5.2.Поясните технологию построения формул в Excel;

5.3.Как вызвать окно редактора кода VBA;

5.4.Как работает оператор For?

5.5.Для чего используется оператор With?

5.6.Как построить кнопку вызова и для чего она используется?

5.7.Какие еще операторы VBA потребовались для разработки программы?

Лабораторная работа № 23 Изучение технологии работы с формами и элементами управления в среде VBA MS Excel

Цель работы: 1)изучение технологии работы с формами и элементами управления в среде VBA MS Excel;

2)изучение технологии разработки пользовательского интерфейса на основе элементов управления VBA MS Excel;

3)решение задачи "Учет товаров".

Объем работы 4 часа

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

Объекты VBA. К объектам VBA относятся рабочие книги, рабочие листы, блоки ячеек, ячейки, а также формы, элементы управления: Label, TextBox, ComboBox, CommandButton и другие.

Каждый объект характеризуется свойствам и может содержать методы.

Рассмотрим объекты форм. Форму можно вызвать из окна проекта VBA с помощью команды User Form из меню Insert, или комбинацией клавиш Ctpl+F11. Появится окно вида рис. 1.

Рис.1

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

В окне проекта могут содержаться окно проекта (Project) и окно свойств (Properties). В окне проекта отображены основные объекты проекта: листы, формы, модули. В окне свойств отображаются свойства активного объекта, параметры которых можно изменить (настроить) в этом окне.

Окно кода вызывается двойным щелчком на форме или элементе управления, содержащемся на ней. Вид окна кода приведен на рис. 2.

Рис. 2 – Вид окна кода

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

1.1.Редактолр кода VBA MS Excel позволяет пользователю (программисту) разработать удобный интерфейс для ввода данных и просмотра результатов расчета на основе форм и соответствующих элементов управления.

Для вызова формы в среде редактора кода VBA MS Excel необходимо в меню Insert (Вставка) выполнить команду User Form (Форма), после чего форма и окно элементов управления появятся на рабочем столе. Для разработки пользовательского интерфейса используются такие элементы управления, как "Надпись" (Label) "Текст" (Text), "Поле со списком" (Combo Box), "Командная кнопка" (Command Button) и другие. В начале элементы управления перетаскиваются на форму, затем устанавливаются их соответствующие свойства и наконец записывается программный код для обработки соответствующих событий на языке VBA.

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

На рабочем листе "Лист1" в первых 8 строках столбца А записаны наименования 8 товаров (Компьютеры, Принтеры, Сканеры, Модемы, Плоттеры, Клавиатуры, Мыши, Мониторы), в 10строке сформирована шапка таблицы следующего вида:

Требуется построить интерфейс на VBA для формирования базы данных операций, производимых с товарами. Окно интерфейса должно содержать следующие элементы:

- надпись (Label1) и поле со списком (ComboBox1) для ввода "Операции";

- надпись (Label2) и поле со списком (ComboBox2) для ввода "Наименования изделия";

- надпись (Label3) и текстовое поле (TextBox1) для ввода "Суммы";

- надпись (Label4) и текстовое поле (TextBox2) для ввода "Даты";

- кнопку (CommandButton1) "Ок";

- кнопку (CommandButton2) "Выход".

Элементы "Надпись и поле со списком для ввода "Операции" и "Надпись и поле со списком для ввода "Наименования изделия" заключить в рамку (Frame).

Вызовем форму как описано выше. Разместим на ней указанные выше элементы управления. Приведем форму к виду, приведенному ниже на рис. 6.1.

Вызовем окно кода и создадим следующий код:

Private Sub UserForm_Initialize()

Dim m(8) As Variant

ComboBox1.List = Array("Доставка", "Отпрака", "Оформление", _

"Сортировка", "Разгрузка")

With Worksheets("Лист1")

For i = 1 To 8

m(i) = Cells(i, 1)

Next i

End With

ComboBox2.List = m

End Sub

Private Sub ComboBox1_Change()

TextBox4.Text = Date

End Sub

Private Sub CommandButton1_Click()

'Создание новой строки для ввода данных

Поиск по сайту: