Создайте на VBA приложение для анализа доходов от издательской деятельности по разделу упражнений «Решение задач. Подбор параметра».
MS VBA
Создайте на VBA функцию пользователя «Стоимость» (см. стр. 80 уч. пособия «Информатика. Microsoft Office Professional »).
Создайте на VBA приложение «Калькулятор», используя приведенные ниже вид UserForm и пример текста процедуры.
Рис. 1
x = CDbl(TextBox2.Text)
Range("B3").Value = x
Range("B4").Formula = "=ACOS(B3)"
y = Range("B4").Value
TextBox1.Text = CStr(Format(y, "######0.0#############"))
Создайте на VBA приложение «Стоимость товара с учетом скидки», используя созданную в п.1 функцию «Стоимость».
Указания по выполнению.
При разработке приложения создайте UserForm по следующему образцу (рис. 2):
Рис.2
Для вычисления цены товара с учетом скидок используйте функцию «Стоимость», созданную при выполнении упражнения 1.
Для ввода специальной скидки используйте элемент управления Check Box. Надпись «Внимание! Скидка только по дисконтной карте» должна появляться только при установке флажка CheckBox.
4. Создайте на VBA приложение "Расчет маргинальной процентной ставки".
Условие задачи. Клиент просит дать ссуду размером P с условием возвращения в течение N лет, при размере одной выплаты А (конечно, A*N>P).Есть альтернатива - не давать ссуду клиенту, а положить деньги в банк под i процентов годовых.
Требуется рассчитать:
а) какую сумму нужно положить в банк, чтобы получить (ежегодно!) такой же доход, как и в случае предоставления ссуды;
б) под какой минимальный процент (это и есть маргинальная процентная ставка) можно положить в банк сумму денег, равную ссуде, чтобы получить тот же самый доход, что и при предоставлении ссуды.
|
|
|
Указания по выполнению.
1. При выполнении п.а) используйте стандартную функцию PV
2. При составлении текста процедуры расчета используйте следующие типовые фрагменты:
а) Описание типов переменных Dim i As Double, Dim n As Integer;
б) функции чтения из окон ввода числовых значений с преобразованием текстового представления числа в число в формате Integer или Double:
i=CInt(TextBox1.Text)
p=CDbl(TextBox2.Text)
в) функции переформатирование вычисленных значений из числовой формы в текстовую и вывод их в диалоговые окна:
TextBox3.Text = CStr(Format(p,"Fixed"))
3. Для вычисления маргинальной процентной ставки создайте программным путем таблицу:
Табл. 1
| A | B | |
| 1 | ||
| 2 | Число выплат | n |
| 3 | Размер ссуды | p |
| 4 | Размер одной выплаты | a |
| 5 | Процентная ставка | i |
| 6 | Текущий объем ссуды | =PV(B5,B2,-B4) |
| 7 | Маргинальная процентная ставка | i |
| 8 | Маргинальный чистый объем ссуды | =PV(B7,B2,-B4) |
Собственно вычисление маргинальной процентной ставки произведите при использовании метода GoalSeek объекта Range.
При разработке приложения создайте UserForm по следующему образцу (рис. 3):
|
|
|
Рис. 3
Создайте на VBA приложение для анализа доходов от издательской деятельности по разделу упражнений «Решение задач. Подбор параметра».
Примечание. Используйте при создании приложения элемент управления ComboBox, МЕТОД AddItem при заполнении, СВОЙСТВО ListRow для задания количества элементов списка.
Пример. ComboBox1.AddItem "КолЭкз"
ComboBox1.ListRow=4
Указания по выполнению.
При разработке приложения создайте UserForm по следующему образцу (рис. 13):
Здесь «Расчет доходов/расходов», «Подбор параметра» и «Сброс» - кнопки управления (Button), «Фактор» - поле со списком (ComboBox), остальные элементы - окна ввода/вывода (TextBox).
 |
Рис. 4
Программа создаваемого приложения должна обеспечить:
а) создание таблицы : Табл. 17
| A | B | ||||
| 1 | Расходы/доходы от издания книги | ||||
| 2 |
| ||||
| 3 |
| ||||
| 4 | Количество экземпляров | 20 000 | |||
| 5 | Доход | =B17*B4 | |||
| 6 | Себестоимость | =B18*B4 | |||
| 7 | Валовая прибыль | =B5-B6 | |||
| 8 | % накладных расходов | 30 | |||
| 9 | Затраты на зарплату | =250*B4 | |||
| 10 | Затраты на рекламу | =50*B4 | |||
| 11 | Накладные расходы | =B5*B8/100 | |||
| 12 | Валовые издержки
| =B11+B9+B10 | |||
| 13 |
| ||||
| 14 | Прибыль от продукции | =B7-B12 | |||
| 15 |
| ||||
| 16 |
| ||||
| 17 | Цена продукции | 6000 | |||
| 18 | Себестоимость продукции | 2000 | |||
б) подбор параметра в ячейке В14 (выяснить условия получения определенной заданной прибыли за счет всех возможных изменений факторов: тиража, уровня накладных расходов, цены и себестоимости).
Заполнение списка ComboBox производите программно, с помощью отдельной процедуры в модуле по следующему образцу:
Private Sub UserForm_Initialize()
With ComboBox1
.AddItem "КолЭкз"
.AddItem "НаклРасх"
.AddItem "ЦенаКниги"
.AddItem "СебестКниги"
.ListRows = 4
End With
End Sub
При составлении программы рекомендуется воспользоваться примерами, приведенными в разделе «Основные средства VBA» и приведенными ниже:
а) Чтение текста из окна ввода и преобразование его в числовой формат (Integer):
k = CInt(TextBox2.Text)
б) Запрет ввода в окно, предназначенное для вывода:
TextBox6.Enabled = False
в) Назначение функции кнопки CommandButton1 клавише Enter:
With CommandButton1
.Default = True
End With
г) Установка текста всплывающей подсказки для кнопки CommandButton1:
With CommandButton1
.ControlTipText = "Поиск значения"
End With
Дополните разработанные приложения по п. 1-5, предусмотрев в них следующие дополнительные средства:
|
|
|
§ запрет ввода текста в окна, предназначенные для вывода (свойство Enabled=False);
§ всплывающие таблички с комментариями к элементам управления на панели UserForm (свойство ControlTipText);
§ кнопки для закрытия панелей UserForm (используйте метод Hide);
§ встроенные диалоговые окна InputBox и MsgBox;
- присвоение клавишам Enter и Cancel функций кнопок на панели UserForm (свойства Default и Cancel соответственно);
- дополнительную процедуру, выполняющую предыдущую функцию при инициализации UserForm (событие Initialize).
- Дополните программу «Расчет маргинальной процентной ставки» средствами обработки ошибок:
а) выход из процедуры с установкой фокуса на окно ввода (TextBox1) при ошибке в формате данных;
б) выход из процедуры с установкой фокуса на окно ввода при ошибке в данных (если сумма выплат оказывается меньше ссуды).
6. Создайте приложение пользователя для ведения базы данных
Указания по выполнению задания
1. UserForm рекомендуется создать по приведенной ниже форме рис. 5 и с приведенным набором окон ввода/вывода и кнопок управления.
2. При программном создании в Excel приведенной ниже табл. 2 используйте следующие объекты, методы, свойства и события VBA:
а) Очистка ячеек активного листа рабочей книги Excel:
ActiveSheet.Cells.Clear
б) Заполнение строки заголовков таблицы базы данных:
Range("A1:L1").Value = Array("Дата", "НаимОрганиз", "Кол1", "Цена1", "Сумма1", "Кол2", "Цена2", "Сумма2", "Всего", "Дата_опл.", "Оплата", "Остаток")
в) Определение текущей пустой строки БД:
НомерСтроки = Application.CountA(ActiveSheet.Columns(1)) + 1
б) Внесение очередной записи в БД:
With ActiveSheet
.Cells(НомерСтроки, 1).Value = Дата
.Cells(НомерСтроки, 2).Value = НаимОрг
………………………………………..
End With
3. Значения Цена1, Цена2 задайте в программе, значения Сумма1, Сумма2, Всего, Остаток – вычислите в программе.
Табл. 2
| Дата | Наим. организ. | Кол1 | Цена1 | Сумма1 | Кол2 | Цена2 | Сумма2 | Всего | Дата | Оплата | Оста-ток |
| 01.янв | ВИЛАРД | 50 | 30 | 1500 | 40 | 0 | 1500 | 15. янв | 1400 | 100 | |
| 01.янв | КАРАТ | 80 | 30 | 2400 | 40 | 0 | 2400 | 0 | 2400 | ||
| 01.янв | КОНДАС | 50 | 30 | 1500 | 2 | 40 | 80 | 1580 | 05. янв | 1000 | 580 |
| 01.янв | ЛИГА | 20 | 30 | 600 | 40 | 0 | 600 | 0 | 600 | ||
| 02.янв | ЛОРА | 100 | 30 | 3000 | 1 | 40 | 40 | 3040 | 07. янв | 3000 | 40 |
| 02.янв | СОЛО | 50 | 30 | 1500 | 40 | 0 | 1500 | 0 | 1500 | ||
| 02.янв | ТИГР | 50 | 30 | 1500 | 40 | 0 | 1500 | 0 | 1500 | ||
| 03.янв | АЛЬЯНС | 50 | 30 | 1500 | 40 | 0 | 1500 | 0 | 1500 | ||
| 03.янв | АННА-МАРИЯ | 200 | 30 | 6000 | 40 | 0 | 6000 | 0 | 6000 |
Примечание: В именах переменных в программе не должны содержаться символы точки и запятой.
3. Для создания модулей формирования отчетов после создания базы данных создайте макросы в Excel, используя MacroRecorder, затем скопируйте тексты макросов через буфер обмена.
 |
Рис. 5
7. Создайте многостраничное приложение
Указания по выполнению
а) Многостраничное приложение должно содержать титульный лист и листы, на каждом из которых размещены созданные ранее приложения пользователя по п.п. 1-6.
б) На одном из листов должен быть выведен график, созданный средствами VBA, (см. example11.xls). Пример текста программы приведен ниже
Приложение 1. Примеры методов и свойств объектов VBA в Excel.
Объект Worksheet и семейство Worksheets
| Метод | Действие | Пример |
| Activate | Активизация указанного рабочего листа | Worksheets(1).Activate |
| Add | Создание нового рабочего листа | ActiveWorkbook.Worksheets.Add |
| Delete | Удаление рабочего листа | Worksheets(1).Delete |
| Protect | Установка защиты активного листа от внесения в него изменений | Activesheet.Protect Password:=”Мария” |
| Unprotect | Снятие защиты с активного листа | Activesheet.Unprotect Password:=”Мария” |
| Copy | Копирование активного листа в другое место рабочей книги | Worksheets(“Лист1”).Copy after:= Worksheets(“Лист3”) |
Объект Range
| Свойство | Действие | Пример |
| Value | Возвращает значение из ячейки Заносит в ячейку значение Заносит в ячейку текст | x=Range(“B1”).Value Range(“B1”).Value=y Range(“B1”).Value=”Объем ссуды” |
| NumberFormat | Заносит в ячейку параметры формата | Range(“B3”).NumberFormat=”0.00%” |
| Formula | Заносит в ячейку формулу | Range(“B5”).Formula=”=A5*B17” |
| WrapText | Устанавливает режим ввода текста в ячейку с переносом по словам | With Range(“B2”) .Value=”Компьютер Пентиум 4” .WrapText = True End With |
Объект Range
| Метод | Действие | Пример |
| Select | Выделяет диапазон ячеек | Range(“A1:G17”).Select |
| AutoFit | Автоматически настраивает ширину помеченных строк /столбцов | Selection.Rows.AutoFit Selection.Columns.AutoFit |
| Clear | Очищает диапазон ячеек | Range(“A1:G17”).Clear |
| Copy | Копирует диапазон ячеек в другой диапазон | Range(“A1:A4”).Copy _ Destination:=Range(“D1:D4”)[1] |
| GoalSeek | Производит подбор параметра | Range(“B8”).GoalSeek Goal:=p,_ ChangingCell:=Range(“B7”)[2] |
Приложение 2. Примеры методов, свойств и событий элементов управления VBA в Excel.
Объект UserForm
| Метод | Действие | Пример |
| Show | Отображает UserFofm на экране | UserFofm1.Show |
| Hide | Скрывает UserFofm | UserFofm1.Hide |
| Событие | Действие | Пример заголовка процедуры |
| Initialize | Происходит при отображении формы на экране | Private Sub UserForm_ Initialize () |
| Terminate | Происходит при закрытии формы | Private Sub UserForm_ Terminate () |
Объект TextBox
| Свойство | Действие | Пример |
| Text | Возвращает текст, содержащийся в поле | Цена=TextBox1.Text |
| Enabled | Разрешает/запрещает вносить изменения в поле (окно TextBox) | TextBox1.Enabled=True TextBox1.Enabled=False |
| WordWrap | Разрешает/запрещает перенос в поле по словам | TextBox1. WordWrap =True TextBox1. WordWrap =False |
| Visible | Разрешает/запрещает отображение TextBox на экране | TextBox1. Visible =True TextBox1. Visible =False |
Объект Label
| Свойство | Действие | Пример |
| Caption | Возвращает текст, отображаемый в надписи | Label2.Caption=”Функция” |
| WordWrap | Разрешает/запрещает перенос в надписи по словам | Label2. WordWrap =True Label2. WordWrap =False |
| Visible | Разрешает/запрещает отображение надписи на экране | Label2. Visible =True Label2. Visible =False |
Объект CommandButton
| Свойство | Действие | Пример |
| Caption | Возвращает текст, отображаемый на кнопке | CommandButton1.Caption=”Выполнить” |
| Enabled | Разрешает/запрещает нажатие кнопки пользователем | CommandButton1. Enabled =True CommandButton1. Enabled =False |
| Visible | Разрешает/запрещает отображение кнопки на экране во время выполнении программы | CommandButton1. Visible =True CommandButton1. Visible =False |
| Default | Присваивает/отменяет присвоение клавише <Enter> функции кнопки | CommandButton1. Default =True CommandButton1. Default =False |
| Cancel | Присваивает/отменяет присвоение клавише <Esc> функции кнопки | CommandButton1. Cancel =True CommandButton1. Cancel =False |
| ControlTipText | Создает всплывающую табличку с надписью | CommandButton1. ControlTipText=”После нажатия на кнопку ваш ответ не может быть изменен” |
Объект OptionButton
| Свойство | Действие | Пример |
| Caption | Создает надпись, отображаемую рядом с переключателем OptionButton | OptionButton1.Caption=”Выполнить” |
| Enabled | Разрешает/запрещает пользование переключателем | OptionButton 1. Enabled =True OptionButton 1. Enabled =False |
| Visible | Разрешает/запрещает отображение переключателя на экране во время выполнения программы | OptionButton 1. Visible =True OptionButton 1. Visible =False |
Visual Basic for Application. Требования к проекту
Проект по разделу Visual Basic for Application должен представлять собой многостраничное приложение пользователя, отражающее:
Учет товаров (изделий), производимых (закупаемых) предприятием с указанием даты (месяца), наименований, количества и себестоимости изделий по каждой позиции;
Учет товаров (изделий), продаваемых (поставляемых) потребителям с указанием даты (месяца), наименований, количества и продажной стоимости изделий по каждой позиции и каждому потребителю, сумму оплаты и остатка (недоплаты);
Анализ данных по продажам, выполняемую по датам и потребителям.
Дата добавления: 2019-02-12; просмотров: 853; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!