выполнить двойной щелчок на объекте "Эта книга", Рис. 7.
Рис 7
Далее используя технологию, изложенную выше, можно вставить процедуру и ввести код программы.
Окно кода может быть вызвано и для каждого рабочего листа. Технология его создания аналогична изложенной выше, только после вызова окна проекта двойной щелчок следует выполнить на объекте "Листn", где n. – номер рабочего листа. Далее можно вставить процедуру и ввести код программы.
2. Постановка задачи
Создать электронную таблицу "Расчет коммунальных услуг" следующей структуры:
Задача решается с использованием двух листов Excel.
На первом листе размещена основная таблица, содержащая столбцы: №, Фамилия И.О., Адрес, Жил. площадь, К-во проживающих, Участник ВОВ, Телефон, Квартплата
На втором листе размещается таблица тарифов за услуги (таблица 2). В ней приводятся стоимости ежемесячной платы за жилплощадь, за отопление, за холодное и горячее водоснабжение, за радиоточку и телефон.
Требуется рассчитать стоимость месячной платы за коммунальные услуги (колонка 8 таблицы 1).
Таблица1 - Расчет коммунальных услуг (Лист 1)
№
Фамилия И.О.
Адрес
Жил.площ,м2
К-во прожив-х
участник ВОВ
тел:1-спар,
0-нет
Квартплата, руб
Иванов А.О.
ул.Вавилова,17,56
Сидоро Н.И.
ул.Романова,19,23
Петров С.П.
ул.Первомайская 27,77
Ганцевич А.И.
ул.Ленина 33,29
Ванцевич А.Н.
ул.Гагарина 5,21
Итого:
Таблица 2 - Тарифы за услуги (Лист 2)
№
Услуги
Стоимость
Примечание
Квартплата
за 1 м2
Отопление
за 1 м2
Холодное водоснабжение
за 1 проживающего
Горячее водоснабжение
за 1 проживающего
Радиоточка
за 1 проживающего
Телефон неспаренный
Телефон спаренный
Для расчета квартплаты составить функцию на языке 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
Структура электронной таблицы для расчета накоплений денежных средств в банке
A
B
C
D
E
F
G
H
ФИО
Дата вклада
Сумма
Сумма проц
Проц ставка 1 (срок 1 месяц)
Сумма
Сумма проц
Проц ставка 2 (срок 3 месяца)
Сидоров В.П.
05.03.2007
400 000,00
12%
400 000,00
13%
05.04.2007
12%
13%
05.05.2007
12%
13%
05.06.2007
12%
13%
05.07.2007
12%
13%
05.08.2007
12%
13%
05.09.2007
12%
13%
05.10.2007
12%
13%
05.11.2007
12%
13%
05.12.2007
12%
13%
05.01.2008
12%
13%
05.02.2008
12%
13%
05.03.2008
12%
13%
Для выполнения требуемых расчетов в таблицу необходимо ввести соответствующие формулы. Так для варианта а) сумма, накапливаемая по процентам банка, для ячейки D3 может быть вычислена по формуле: =C3*E3/12. Тогда в ячейке С3 "Сумма" должна увеличится в начале второго месяца на эту сумму (накопленную по процентам банка).
Для варианта b) продолжительность срока составляет 3 месяца. Поэтому основная сумма должна увеличиваться на сумму процентов только в конце каждого "срока", в начале же каждого второго и третьего месяца сумма, накапливаемая на дополнительном счете должна увеличиваться на 5%. Построенные с учетом этого формулы приведены в табл.2.1.
Таблица 2.1- Электронная таблица с формулами
A
B
C
D
E
F
G
H
ФИО
Дата вклада
Сумма
Сумма проц
Проц ставка 1 (срок 1 мес)
Сумма
Сумма проц
Проц ставка 2 (срок 3 мес)
Сидоров В.П.
05.03.2007
12%
13%
05.04.2007
=C2+D3
=C2*E2/12
12%
=F2*H2/12
13%
05.05.2007
=C3+D4
=C3*E3/12
12%
=G3+(F2*H3+G3*0,05)
13%
05.06.2007
=C4+D5
=C4*E4/12
12%
=F$2+G$5
=G4+(F2*H4+G4*0,05)
13%
05.07.2007
=C5+D6
=C5*E5/12
12%
=F5*H5
13%
05.08.2007
=C6+D7
=C6*E6/12
12%
=G6+(F5*H6+G6*0,05)
13%
05.09.2007
=C7+D8
=C7*E7/12
12%
=F$5+G$8
=G7+(F5*H7+G7*0,05)
13%
05.10.2007
=C8+D9
=C8*E8/12
12%
=F8*H8
13%
05.11.2007
=C9+D10
=C9*E9/12
12%
=G9+(F8*H9+G9*0,05)
13%
05.12.2007
=C10+D11
=C10*E10/12
12%
=F$8+G$11
=G10+(F8*H10+G10*0,05)
13%
05.01.2008
=C11+D12
=C11*E11/12
12%
=F11*H11
13%
05.03.2008
=C12+D13
=C12*E12/12
12%
=G12+(F11*H12+G12*0,05)
13%
05.03.2008
=C13+D14
=C13*E13/12
12%
=F$11+G$14
=G13+(F11*H13+G13*0,05)
13%
Результаты расчета, полученные на основе разработанной таблицы, приведены ниже в таблице 2.3.
Таблица 2.3 - Электронная таблица с результатами расчета накоплений денежных средств в банке
ФИО
Дата вклада
Сумма
Сумма проц
Проц ставка 1 (срок 1 мес)
Сумма
Сумма проц
Проц ставка 2 (срок 3 мес)
Сидоров В.П.
05.03.2007
12%
13%
05.04.2007
12%
4333,333
13%
05.05.2007
12%
13%
05.06.2007
412120,4
4080,4
12%
511377,5
111377,5
13%
05.07.2007
416241,6
4121,204
12%
66479,08
13%
05.08.2007
4162,416
12%
136282,1
13%
05.09.2007
424608,1
4204,04
12%
720952,8
209575,3
13%
05.10.2007
428854,1
4246,081
12%
93723,86
13%
05.11.2007
433142,7
4288,541
12%
192133,9
13%
05.12.2007
437474,1
4331,427
12%
295464,5
13%
05.01.2008
441848,9
4374,741
12%
132134,2
13%
05.03.2008
446267,3
4418,489
12%
270875,2
13%
05.03.2008
4462,673
12%
416553,2
13%
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 и введем операторы языка.
В окончательном виде процедура получит следующий вид:
После ввода строк кода (операторов процедуры), и их отладки на рабочем листе можно построить кнопку вызова на выполнение разработанной процедуры. Построение такой кнопки выполняется следующим образом.
2) с помощью мыши выбрать "Кнопку" в панели "Формы" на рабочем листе;
3) обвести курсором мыши небольшой участок на рабочем листе (в пределах одной ячейки), появиться кнопка и окно "Назначить макрос объекту", в котором будет выведено имя разработанной процедуры Vk;
4) выбрать процедуру Vk и нажать кнопку "Ok";
5) ввести имя кнопки "Расчет".
В таблице появятся результаты расчета, табл. 2.4.
ФИО
Дата вклада
Сумма
Сумма проц
Проц ставка 1 (срок 1 мес)
Сумма
Сумма проц
Проц ставка 2 (срок 3 мес)
Сидоров В.П.
05.03.2007
12%
13%
05.04.2007
12%
4333,333
13%
05.05.2007
12%
13%
05.06.2007
412120,4
4080,4
12%
511377,5
111377,5
13%
05.07.2007
416241,6
4121,204
12%
66479,08
13%
05.08.2007
4162,416
12%
136282,1
13%
05.09.2007
424608,1
4204,04
12%
720952,8
209575,3
13%
05.10.2007
428854,1
4246,081
12%
93723,86
13%
05.11.2007
433142,7
4288,541
12%
192133,9
13%
05.12.2007
437474,1
4331,427
12%
295464,5
13%
05.01.2008
441848,9
4374,741
12%
132134,2
13%
05.03.2008
446267,3
4418,489
12%
270875,2
13%
05.03.2008
4462,673
12%
416553,2
13%
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строке сформирована шапка таблицы следующего вида:
A
B
C
D
. . .
Операция
Изделие
Цена
Дата
Требуется построить интерфейс на VBA для формирования базы данных операций, производимых с товарами. Окно интерфейса должно содержать следующие элементы:
- надпись (Label1) и поле со списком (ComboBox1) для ввода "Операции";
- надпись (Label2) и поле со списком (ComboBox2) для ввода "Наименования изделия";
- надпись (Label3) и текстовое поле (TextBox1) для ввода "Суммы";
- надпись (Label4) и текстовое поле (TextBox2) для ввода "Даты";
- кнопку (CommandButton1) "Ок";
- кнопку (CommandButton2) "Выход".
Элементы "Надпись и поле со списком для ввода "Операции" и "Надпись и поле со списком для ввода "Наименования изделия" заключить в рамку (Frame).
Вызовем форму как описано выше. Разместим на ней указанные выше элементы управления. Приведем форму к виду, приведенному ниже на рис. 6.1.