Файл: Контрольная работа по теме Базы данных в Excel 72 IV. Макросы в ms excel 78 Макросы для автоматизации работ 78.doc

– вернуться в Excel;

– а панели VisualBasic нажать кнопку «Выход из режима конструктора».

5. 
   Обвести область ячеек C8:D28 и для этой области добавить диаграмму. Если расчеты еще не были выполнены, то диаграмма поначалу будет пустая.
   
6. 
   Если все было сделано правильно, то после нажатия по кнопке «Расчет» в ячейке B5 появится значение точки безубыточности, в ячейках B9:D28 результаты расчета и будет построена диаграмма, аналогичная рис. 4.1.
   

4.2.2. Пример 2. Моделирование процесса налогообложения [8]

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

Работа модели выглядит следующим образом:

– у предприятия с рентабельностью R имеется стартовый капитал – K;

– в конце года предприятие получает прибыль, равную P = K * R;

– с прибыли берется налог, пропорциональный налоговой ставке:

Nalog = Stavka * P; (4.5)

– оставшаяся после уплаты налога сумма добавляется к стартовому капиталу:

K = K + (P – Nalog); (4.6)

– годовой цикл повторяется вновь.

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

Для организации вычислений исходные данные можно разместить следующим образом – табл. 4.2.

Таблица 4.2

Размещение исходных данных в задаче моделирования налогообложения

	B	C	D	E	F	G	H	I	J	K	L	M
7
8			Ставка налога на прибыль
9		Рентабельность	10%	20%	30%	40%	50%	60%	70%	80%	90%
10		10%
11		20%
12		30%
13		40%
14		50%
15		60%
16		70%
17		80%
18		90%
19		100%
20

---

Для расчетной кнопки ввести макрос следующего вида:
Private Sub CommandButton1_Click()

For i = 10 To 19

Rent = Cells(i, 3)

For j = 4 To 12

k = 100

b = 0

Stavka = Cells(9, j)

For t = 1 To 10

Prib = k * Rent

b = b + Prib * Stavka

OstPrib = Prib * (1 - Stavka)

k = k + OstPrib

Next

Cells(i, j) = b

Next

Next

EndSub
Примечание

Так же, как и в примере 1 приведенный макрос настроен на показанное выше размещение данных.

Е
сли все было сделано правильно, то после нажатия по кнопке «Расчет» таблица заполнится результатами расчетов. По полученным данным можно построить либо одномерную – рис.4.2, либо двумерную диаграмму.

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

Если присмотреться к рассчитанным данным, то можно сделать ряд интересных выводов.

Например:

– величина поступлений в бюджет в зависимости от ставки налога проходит через максимум.

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

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

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

Рассмотрим пример создания интерфейса для обеспечения расчетов, связанных с работой по вкладам.

Величина вклада рассчитывается по формуле сложных процентов:

, (4.7)

где P – начальный вклад;

c – ставка сложных процентов;

t – время вклада;

S – величина вклада через время t.

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

---

– по известному конечному вкладу, величине ставки и времени вычислить начальный вклад:

, (4.8)

– по известным начальному и конечным вкладам и процентной ставке вычислить время вклада:

, (4.9)

– по известным начальному и конечному вкладам и времени вычислить величину ставки:

. (4.10)
Проектирование интерфейса

На основе уравнений (4.7) – (4.10) можно создать вычислительный комплекс со следующим интерфейсом.

При запуске программы появляется лист, содержащий главное меню – рис.4.3.



Рис.4.3. Внешний вид главного меню программы
При щелчке по кнопке «Конечный вклад» система переходит на Лист2, в котором реализованы расчеты по формуле (4.7) – рис. 4.4. После завершения расчетов вернуться в главное меню можно с помощью соответствующей кнопки.

По аналогичной схеме работают кнопки «Начальный вклад», «Время» и «Ставка». При этом система должна переходить на соответствующие листы – Лист3, Лист4 и Лист5. Кнопка «Выход» обеспечивает закрытие программы и выход из Excel.



Рис.4.4. Лист с реализацией расчетов величины конечного вклада.

Этапы создания интерфейса

Создание кнопок

1. 
   Вызывается панель инструментов с заготовками интерфейса –
   

Вид > Панели инструментов > Формы.

2. 
   На появившейся панели выбрать элемент «Кнопка» и нарисовать ее в нужном месте экрана. На запрос о назначении макроса щелкнуть «Отмена».
   
3. 
   Аналогично нарисовать все остальные кнопки меню и расчетных листах.
   
4. 
   Исправить надписи на кнопках.
   

Оформление главного меню

Оформлять или не оформлять главное меню дело вкуса. Но если в этом есть необходимость, то:

1. 
   Вызвать панель рисования (Вид > Панели инструментов > Рисование), выбрать на ней объект «Прямоугольник» и накрыть им кнопки главного меню.
   
2. 
   Выделить нарисованный прямоугольник и на панели рисования выбрать Рисование > Порядок > На задний план. При этом скрытые прямоугольником кнопки выйдут на передний план.
   
3. 
   Не снимая выделения с прямоугольника залить его выбранным цветом и стилем.
   
4. 
   Убрать сетку таблицы – Сервис > Параметры > Снять отметку с параметра «Сетка».
   
5. 
   Выбрать подложку для фона – Формат > Лист > Подложка > Выбрать рисунок > Вставить. Рисунок можно выбрать из коллекции ClipArt.
   

---

Создание макросов для кнопок

Согласно плану проекта интерфейса созданные кнопки должны обеспечить выполнение следующих команд – табл.4.3.

Таблица 4.3

Предназначение кнопок

N	Кнопка	Назначение
1	«Конечный вклад»	Переход на Лист2
2	«Начальный вклад»	Переход на Лист3
3	«Время»	Переход на Лист4
4	«Ставка»	Переход на Лист5
5	«Выход»	Выход из Excel
6	«Главное меню» на расчетных листах	Переход на Лист1

Все макросы, выполняющие указанные команды, создаются практически одинаково.

Например, для кнопки «Конечный вклад»:

1. Перейти на Лист1.

2. Выполнить команды – Сервис > Макрос > Начать запись.

3. На запрос о параметрах макроса необходимо только указать осмысленное имя макроса. Например, Кон_вклад и затем «Ok». При вводе имени макроса нельзя использовать пробелы.

4. Система перейдет в режим записи макроса. Но в Excel, в отличие от Word, нет внешних признаков того, что система находится в режиме записи. Поэтому здесь необходимо очень аккуратно выполнить только необходимые команды и тут же остановить запись.

5. Применительно к рассматриваемой задаче – щелкнуть по ярлычку «Лист1» и затем Сервис > Макрос > Остановить запись.

Точно также можно создать макросы для остальных кнопок перехода – «Начальный вклад», «Время», «Ставка» и «Главное меню» на расчетных листах.

Но удобнее (и быстрее) остальные макросы создать следующим образом:

1. 
   После создания первого макроса (для кнопки «Конечный вклад») перейти в редактор Visual Basic – Сервис > Макрос > Макросы > Выбрать только что созданный > Изменить.
   
2. 
   Система перейдет в редактор Visual Basic, в котором мы увидим, как выглядит наш макрос в виде команд Бейсика. Если все было сделано правильно, то там должно быть примерно следующее:
   

Sub Кон_вклад()

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

End Sub

3. 
   Для создания остальных макросов имеющийся текст макроса как в Word выделяется, копируется в буфер и путем вставки делается пять его копий.
   
4. 
   Каждую копию следует исправить, создавая новые макросы. Например, первую копию исправить, так, чтобы она приняла следующий вид:
   

---

Sub Нач_вклад()

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

End Sub

Вторую копию:

Sub Время()

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

End Sub

Третью копию:

Sub Ставка()

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

End Sub

Четвертую копию:

Sub Меню()

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

End Sub

5. 
   Для кнопки «Выход» макрос должен содержать следующую команду:
   

Sub Выход()

Workbooks.Close

End Sub

6. 
   Все необходимые макросы созданы.
   

Привязка макросов к кнопкам

Для привязки имеющихся макросов к соответствующим кнопкам необходимо:

1. 
   Вернуться в Excel.
   
2. 
   Установить указатель мыши на нужную кнопку и щелкнуть правой кнопкой мыши.
   
3. 
   В появившемся меню выбрать пункт «Назначить макрос»
   
4. 
   Из списка макросов выбрать нужный и «Ok».
   

После выполнения указанных команд выбранная кнопка становится активной – при установке на нее указателя мыши он принимает форму ладони.
Реализация вычислений

В соответствии с проектом вычисления по формулам (4.7) – (4.10) должны быть разнесены по разным листам. Организация вычисления на всех листах в каком-то смысле типична.

На Лист2 вычисляется величина конечного вклада.

	А	B	C	D
1
2		Начальный вклад	10000
3		Ставка	0,06
4		Время	5
5		Конечный вклад	13382,26
6

При этом в ячейку С5 введена формула (4.7): = C2*(1+C3)^C4.

---