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

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

1. 
   Запись макроса
   

Выделим нужную часть таблицы и выполним команды:Сервис > Макрос > Начать запись > В появившемся окне запроса о параметрах макроса указать только осмысленное имя макроса (например, «Настройка») > Ok.Система перейдет в режим записи макроса. Здесь необходимо очень аккуратно выполнить все необходимые команды.В данном случае:

• 
  установить размер шрифта, равный 14;
  
• 
  установить тип шрифта «Times New Roman»;
  
• 
  установить выравнивание по центру.
  

После этого тут же остановить запись: Сервис > Макрос > Остановить запись.2. Обеспечение запуска макроса.Для малоопытных пользователей самым удобным способом является запуск макроса с помощью командной кнопки. Для ее создания:Сервис > Настройка > В окне «Настройка» выбрать закладку «Команды» > В списке категорий выбрать категорию «Макросы» > В списке команд выбрать команду «Настраиваемая кнопка» и перетащить ее на панель инструментов > Не закрывая окна «Настройка» установить указатель мыши на только что перетащенную кнопку > Щелкнуть правой кнопкой мыши > В открывшемся меню выбрать пункт «Назначить макрос» > Из списка макросов выбрать макрос «Настройка».ПримечаниеС помощью того же контекстного меню можно изменить надпись на кнопке, выбрать рисунок для нее, нарисовать свой рисунок и т.д. После оформления кнопки окно «Настройка» закрыть.3. Проверка действия макросаЕсли при щелчке по созданной кнопке макрос делает что-то не то, то его необходимо исправить. Если макрос очень простой, то для малоопытных пользователей проще всего перезаписать макрос заново, используя команды пункта 1.Сам текст макроса можно просмотреть, если выполнить команды:Сервис > Макрос > Макросы > Выбрать нужный > Изменить > Система перейдет в редактор VisualBasic, в котором будет представлен текст выбранного макроса.Для рассматриваемого примера должно появиться примерно следующее:Sub Настройка()With Selection.Font.Name = "Times New Roman"

.Size = 14.Strikethrough = False.Superscript = False.Subscript = False.OutlineFont = False.Shadow = False.Underline = xlUnderlineStyleNone.ColorIndex = xlAutomaticEnd WithWith Selection.HorizontalAlignment = xlCenter.VerticalAlignment = xlBottom.WrapText = False.Orientation = 0.AddIndent = False.IndentLevel = 0.ShrinkToFit = False.ReadingOrder = xlContext.MergeCells = FalseEnd WithEnd SubЗдесь все команды настройки записаны в виде команд Visual Basic.Для понимания команд макроса достаточно номинальных познаний английского языка. Сами методы работы в редакторе аналогичны работе в любом текстовом редакторе. Поэтому, если Вы в тексте макроса обнаружите что-то лишнее, то это лишнее можно просто удалить.ПримечаниеТочно такой же макрос и с точно таким же вариантом запуска можно создать и в Word.4.2. Вычислительные макросыСоздание подобных макросов требует от пользователей наличия у них определенных навыков программирования в Visual Basic for Application. Данное требование обычно не предъявляется к студентам экономических специальностей. Поэтому приводимые далее примеры являются относительно несложными.4.2.1. Пример 1. Расчет точки безубыточностиОписание задачи выглядит следующим образом:– пусть для организации производства необходимы начальные вложения (закупка оборудования, аренда помещений и т.д.), равные N руб.;– себестоимость выпуска одного изделия равна С руб.;– цена реализации изделий равна S руб.Тогда:– затраты на производство V изделий будут равны:Z = N + C * V (4.1)– выручка от продаж будет составлять:P = S * V (4.2)Производство станет безубыточным в том случае, когда выручка от продаж превзойдет затраты на производство. Необходимый для этого объем производства можно определить из условия равенства уравнений 4.1 и 4.2.N + C * V = S * V (4.3)Из уравнения 4.3 находим минимально необходимый объем выпуска:V = N / (S – C) (4.4)Возможный интерфейс расчетов приведен в табл. 4.1.Таблица 4.1.Интерфейс программы расчета точки безубыточности

	A	B	C	D	E
1
2	Начальные затраты	70000
3	Себестоимость	50
4	Цена реализации	150
5	Точка безубыточности	700
6
7
8		Объем выпуска	Затраты	Выручка
9		0	70000	0
10		70	73500	10500
11		140	77000	21000
12		210	80500	31500
13		280	84000	42000

От пользователя требуется ввести в ячейки B2:B4 исходные данные и затем щелкнуть по кнопке «Расчет».В результате в ячейку B5 должно быть выведено значение точки безубыточности, а в ячейки B9:D29 - результаты более детальных расчетов. На основе данных ячеек B9:D29 должен автоматически строиться график – рис. 4.1.Рис. 4.1. Графическое представление результатов расчетов в задаче о точке безубыточностиДля обеспечения расчетов необходимо выполнить следующие шаги.

1. 
   В соответствии с табл. 4.1 ввести на лист Excel необходимые сопроводительные надписи.
   
2. 
   Создать командную кнопку.
   

Для этого вызывается панель инструментов VisualBasic (Вид > Панели инструментов > VisualBasic) и на ней активизируется кнопка «Элементы управления». На появившейся панели выбирается элемент «Кнопка» и рисуется в нужном месте экрана.Для смены надписи на кнопке:– щелкнуть по ней правой кнопкой мыши и в появившемся меню выбрать пункт «Свойства»;– в окне свойств (Properties) выбрать свойство Caption (надпись) и исправить ее на слово «Расчет».

3. 
   Написать текст макроса для кнопки.
   

Для ввода связанного с кнопкой расчетного макроса необходимо:– щелкнуть правой кнопкой мыши по нарисованной кнопке и в появившемся меню выбрать пункт «Исходный текст»;– система перейдет в редактор VisualBasic, в котором будет пустая заготовка макроса:Private Sub CommandButton1_Click()End Sub– ввести в нее следующий текст:Private Sub CommandButton1_Click()N = Range("B2") ‘ Из ячеек считываются C = Range("B3") ‘ исходные данныеS = Range("B4") ‘V = N / (S - C) ‘ Рассчитывается точка безубыточностиRange("B5") = V ‘ и выводится в ячейку B5Vmax = 2 * V ‘ Диапазон расчетаh = Vmax / 20 ‘ Шаг расчетаk = 8 ‘ Номер строки For V = 0 To Vmax Step hk = k + 1Cells(k, 2) = VCells(k, 3) = N + V * CCells(k, 4) = V * SNextEnd SubВнимание!! Очень важно!!Приведенный макрос настроен на показанное выше размещение данных. Если Вы разместили данные по-другому, то необходимо изменить макрос. Это можно сделать, только имея навыки программирования и потому нежелательно.

4. 
   Активизировать кнопку «Расчет».
   

Для этого необходимо:

– вернуться в 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 = 100b = 0 Stavka = Cells(9, j)For t = 1 To 10 Prib = k * Rent b = b + Prib * StavkaOstPrib = Prib * (1 - Stavka) k = k + OstPribNextCells(i, j) = bNextNextEndSubПримечаниеТак же, как и в примере 1 приведенный макрос настроен на показанное выше размещение данных.Е сли все было сделано правильно, то после нажатия по кнопке «Расчет» таблица заполнится результатами расчетов. По полученным данным можно построить либо одномерную – рис.4.2, либо двумерную диаграмму.При желании в шапки таблицы с исходными данными можно ввести любые другие значения рентабельности и налоговых ставок. При этом данные будут пересчитаны только после нажатия кнопки «Расчет».Если присмотреться к рассчитанным данным, то можно сделать ряд интересных выводов. Например:– величина поступлений в бюджет в зависимости от ставки налога проходит через максимум.– чем больше рентабельность предприятия, тем меньше должна быть ставка налога (с точки зрения максимума отчислений в бюджет).Полученные выводы вполне можно рекомендовать для использования в государственной налоговой политике, т.е. чем предприятие рентабельнее, тем меньше должно быть налоговое бремя на него. В результате такой политики из экономики страны быстрее выбраковываются предприятия и производства с низкой рентабельностью. 4.3. Использование макросов для создания интерфейсаПроцесс создания интерфейса рассмотрим на следующем примере.Постановка задачиРассмотрим пример создания интерфейса для обеспечения расчетов, связанных с работой по вкладам.Величина вклада рассчитывается по формуле сложных процентов: , (4.7)где P – начальный вклад;c – ставка сложных процентов;t – время вклада;S – величина вклада через время t.Уравнение (4.7) представляет собой решение прямой задачи. Но, поскольку все, входящие в него параметра являются взаимосвязанными, то возможны следующие обратные задачи.