Главная страница

Лабортанорная информатика начало. -Лабораторная12. IV. Макросы в ms excel


Скачать 0.56 Mb.
НазваниеIV. Макросы в ms excel
АнкорЛабортанорная информатика начало
Дата07.06.2020
Размер0.56 Mb.
Формат файлаdoc
Имя файла-Лабораторная12.doc
ТипДокументы
#128614


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 состоит из следующих этапов:

  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 = 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.

Интерфейс программы расчета точки безубыточности




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 (надпись) и исправить ее на слово «Расчет».

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

Для ввода связанного с кнопкой расчетного макроса необходимо:

– щелкнуть правой кнопкой мыши по нарисованной кнопке и в появившемся меню выбрать пункт «Исходный текст»;

– система перейдет в редактор 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
Внимание!! Очень важно!!

Приведенный макрос настроен на показанное выше размещение данных. Если Вы разместили данные по-другому, то необходимо изменить макрос. Это можно сделать, только имея навыки программирования и потому нежелательно.

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

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

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

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

  1. Обвести область ячеек C8:D28 и для этой области добавить диаграмму. Если расчеты еще не были выполнены, то диаграмма поначалу будет пустая.

  2. Если все было сделано правильно, то после нажатия по кнопке «Расчет» в ячейке 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. Вызывается панель инструментов с заготовками интерфейса –

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

  1. На появившейся панели выбрать элемент «Кнопка» и нарисовать ее в нужном месте экрана. На запрос о назначении макроса щелкнуть «Отмена».

  2. Аналогично нарисовать все остальные кнопки меню и расчетных листах.

  3. Исправить надписи на кнопках.

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

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

  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

  1. Для создания остальных макросов имеющийся текст макроса как в Word выделяется, копируется в буфер и путем вставки делается пять его копий.

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

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

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

End Sub

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

Sub Время()

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

End Sub

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

Sub Ставка()

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

End Sub

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

Sub Меню()

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

End Sub

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

Sub Выход()

Workbooks.Close

End Sub

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


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

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

  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.

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





A

B

C

D

1













2




Конечный вклад

15000




3




Ставка

0,06




4




Время

5




5




Начальный вклад

11208,87




6













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

На Лист4 вычисляется время вклада.




A

B

C

D

1













2




Конечный вклад

15000




3




Начальный вклад

10000




4




Ставка

0,06




5




Время

6,958516




6













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

На лист5 вычисляется величина процентной ставки.





A

B

C

D

1













2




Конечный вклад

15000




3




Начальный вклад

10000




4




Время

5




5




Ставка

0,084472




6














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

Задание.


Разработать макросы всех трех групп для решения первой задачи из лабораторной 10.





написать администратору сайта