Лабортанорная информатика начало. -Лабораторная12. IV. Макросы в ms excel
Скачать 0.56 Mb.
|
IV. Макросы в MS Excel Макросы представляют собой встроенные в документ Excel программы на Visual Basic. Они применяются в тех случаях, когда встроенных средств Excel не достаточно. По назначению их можно весьма условно разделить на следующие группы: 1. Макросы, обеспечивающие автоматизацию операций по обеспечению работы в Excel; 2. Макросы вычислительного характера; 3. Макросы, обеспечивающие необходимый интерфейс для вычислений. 4.1. Макросы для автоматизации работ Макросы данного типа применяются в тех случаях, когда при работе в Excel приходится часто выполнять одну и ту же последовательность операций. 4.1.1. Пример Пусть для работы Вам необходимо произвести расчеты нескольких таблиц и затем экспортировать их в документ Word. По умолчанию в Excel установлено: тип шрифта – «Arial Cyr»; размером шрифта – 10; выравнивание для текстовой информации по левому краю, а для числовой – по правому краю. После экспорта в Word таблицу приходится вручную перенастраивать под следующие параметры: размер шрифта – 14; тип шрифта – «Times New Roman»; выравнивание содержимого таблицы – по центру. Указанные настройки приходится каждый раз делать вручную. Но можно эти команды записать в макрос и, запуская его одним нажатием, сэкономить время. Создание макроса в Excel состоит из следующих этапов: Запись макроса Выделим нужную часть таблицы и выполним команды: Сервис > Макрос > Начать запись > В появившемся окне запроса о параметрах макроса указать только осмысленное имя макроса (например, «Настройка») > 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 = xlAutomatic End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With End 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. Интерфейс программы расчета точки безубыточности
От пользователя требуется ввести в ячейки B2:B4 исходные данные и затем щелкнуть по кнопке «Расчет». В результате в ячейку B5 должно быть выведено значение точки безубыточности, а в ячейки B9:D29 - результаты более детальных расчетов. На основе данных ячеек B9:D29 должен автоматически строиться график – рис. 4.1. Рис. 4.1. Графическое представление результатов расчетов в задаче о точке безубыточности Для обеспечения расчетов необходимо выполнить следующие шаги. В соответствии с табл. 4.1 ввести на лист Excel необходимые сопроводительные надписи. Создать командную кнопку. Для этого вызывается панель инструментов VisualBasic (Вид > Панели инструментов > VisualBasic) и на ней активизируется кнопка «Элементы управления». На появившейся панели выбирается элемент «Кнопка» и рисуется в нужном месте экрана. Для смены надписи на кнопке: – щелкнуть по ней правой кнопкой мыши и в появившемся меню выбрать пункт «Свойства»; – в окне свойств (Properties) выбрать свойство Caption (надпись) и исправить ее на слово «Расчет». Написать текст макроса для кнопки. Для ввода связанного с кнопкой расчетного макроса необходимо: – щелкнуть правой кнопкой мыши по нарисованной кнопке и в появившемся меню выбрать пункт «Исходный текст»; – система перейдет в редактор 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 ‘ и выводится в ячейку B5 Vmax = 2 * V ‘ Диапазон расчета h = Vmax / 20 ‘ Шаг расчета k = 8 ‘ Номер строки For V = 0 To Vmax Step h k = k + 1 Cells(k, 2) = V Cells(k, 3) = N + V * C Cells(k, 4) = V * S Next End Sub Внимание!! Очень важно!! Приведенный макрос настроен на показанное выше размещение данных. Если Вы разместили данные по-другому, то необходимо изменить макрос. Это можно сделать, только имея навыки программирования и потому нежелательно. Активизировать кнопку «Расчет». Для этого необходимо: – вернуться в Excel; – а панели VisualBasic нажать кнопку «Выход из режима конструктора». Обвести область ячеек C8:D28 и для этой области добавить диаграмму. Если расчеты еще не были выполнены, то диаграмма поначалу будет пустая. Если все было сделано правильно, то после нажатия по кнопке «Расчет» в ячейке 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 Размещение исходных данных в задаче моделирования налогообложения
Для расчетной кнопки ввести макрос следующего вида: 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. Лист с реализацией расчетов величины конечного вклада. Этапы создания интерфейса Создание кнопок Вызывается панель инструментов с заготовками интерфейса – Вид > Панели инструментов > Формы. На появившейся панели выбрать элемент «Кнопка» и нарисовать ее в нужном месте экрана. На запрос о назначении макроса щелкнуть «Отмена». Аналогично нарисовать все остальные кнопки меню и расчетных листах. Исправить надписи на кнопках. Оформление главного меню Оформлять или не оформлять главное меню дело вкуса. Но если в этом есть необходимость, то: Вызвать панель рисования (Вид > Панели инструментов > Рисование), выбрать на ней объект «Прямоугольник» и накрыть им кнопки главного меню. Выделить нарисованный прямоугольник и на панели рисования выбрать Рисование > Порядок > На задний план. При этом скрытые прямоугольником кнопки выйдут на передний план. Не снимая выделения с прямоугольника залить его выбранным цветом и стилем. Убрать сетку таблицы – Сервис > Параметры > Снять отметку с параметра «Сетка». Выбрать подложку для фона – Формат > Лист > Подложка > Выбрать рисунок > Вставить. Рисунок можно выбрать из коллекции ClipArt. Создание макросов для кнопок Согласно плану проекта интерфейса созданные кнопки должны обеспечить выполнение следующих команд – табл.4.3. Таблица 4.3 Предназначение кнопок
Все макросы, выполняющие указанные команды, создаются практически одинаково. Например, для кнопки «Конечный вклад»: 1. Перейти на Лист1. 2. Выполнить команды – Сервис > Макрос > Начать запись. 3. На запрос о параметрах макроса необходимо только указать осмысленное имя макроса. Например, Кон_вклад и затем «Ok». При вводе имени макроса нельзя использовать пробелы. 4. Система перейдет в режим записи макроса. Но в Excel, в отличие от Word, нет внешних признаков того, что система находится в режиме записи. Поэтому здесь необходимо очень аккуратно выполнить только необходимые команды и тут же остановить запись. 5. Применительно к рассматриваемой задаче – щелкнуть по ярлычку «Лист1» и затем Сервис > Макрос > Остановить запись. Точно также можно создать макросы для остальных кнопок перехода – «Начальный вклад», «Время», «Ставка» и «Главное меню» на расчетных листах. Но удобнее (и быстрее) остальные макросы создать следующим образом: После создания первого макроса (для кнопки «Конечный вклад») перейти в редактор Visual Basic – Сервис > Макрос > Макросы > Выбрать только что созданный > Изменить. Система перейдет в редактор Visual Basic, в котором мы увидим, как выглядит наш макрос в виде команд Бейсика. Если все было сделано правильно, то там должно быть примерно следующее: Sub Кон_вклад() Sheets("Лист2").Select End Sub Для создания остальных макросов имеющийся текст макроса как в Word выделяется, копируется в буфер и путем вставки делается пять его копий. Каждую копию следует исправить, создавая новые макросы. Например, первую копию исправить, так, чтобы она приняла следующий вид: Sub Нач_вклад() Sheets("Лист3").Select End Sub Вторую копию: Sub Время() Sheets("Лист4").Select End Sub Третью копию: Sub Ставка() Sheets("Лист5").Select End Sub Четвертую копию: Sub Меню() Sheets("Лист1").Select End Sub Для кнопки «Выход» макрос должен содержать следующую команду: Sub Выход() Workbooks.Close End Sub Все необходимые макросы созданы. Привязка макросов к кнопкам Для привязки имеющихся макросов к соответствующим кнопкам необходимо: Вернуться в Excel. Установить указатель мыши на нужную кнопку и щелкнуть правой кнопкой мыши. В появившемся меню выбрать пункт «Назначить макрос» Из списка макросов выбрать нужный и «Ok». После выполнения указанных команд выбранная кнопка становится активной – при установке на нее указателя мыши он принимает форму ладони. Реализация вычислений В соответствии с проектом вычисления по формулам (4.7) – (4.10) должны быть разнесены по разным листам. Организация вычисления на всех листах в каком-то смысле типична. На Лист2 вычисляется величина конечного вклада.
При этом в ячейку С5 введена формула (4.7): = C2*(1+C3)^C4. На Лист3 вычисляется величина начального вклада.
При этом в ячейку С5 введена формула (4.8): = C2/(1+C3)^C4. На Лист4 вычисляется время вклада.
При этом в ячейку С5 введена формула (4.9): = Ln(C2/C3)/Ln(1+C4). На лист5 вычисляется величина процентной ставки.
При этом в ячейку С5 введена формула (4.10): = (C2/C3)^(1/C4)-1. Задание.Разработать макросы всех трех групп для решения первой задачи из лабораторной 10. |