Создание собственных функций рабочего листа

РАБОТА №2

Создание макросов excel

Вы — заведующий больницей. Вам предлагается составить штатное расписание, то есть определить, сколько сотрудников, с каким окладом и на какие должности необходимо принять на работу. Общий месячный фонд зарплаты составляет $10000.

Предположим, что для нормальной работы больницы нужно 5-7 санитарок, 8-10 медсестер, 10-12 врачей, 1 заведующий аптекой, 3 заведующих отделениями, 1 главный врач, 1 заведующий хозяйством, 1 заведующий больницей.

Предлагается следующая модель решения задачи. За основу берется оклад санитарки. Размер оклада остальных сотрудников определяется по формуле

Оклад = А (Оклад санитарки) + В,

где А— коэффициент оклада;

В — величина надбавки, $.

Значения А и В назначаются, исходя из следующих соображений:

· медсестра должна получать в 1,5 раза больше санитарки;

· врач — в 3 раза больше санитарки;

· заведующий отделением — на $30 больше, чем врач;

· заведующий аптекой — в 2 раза больше санитарки;

· заведующий хозяйством — на $40 больше медсестры;

· главный врач — в 4 раза больше санитарки;

· заведующий больницей — на $20 больше главного врача.

Задания

Задание 1

1. Оформите таблицу, используя следующие столбцы: Должность, Количество сотрудников, Коэффициент оклада, Надбавка, Оклад, Итого.

При решении задачи используйте сервисную функцию Excel «Подбор параметра»: Сервис | Подбор параметра(рис. 1).

Создание собственных функций рабочего листа - student2.ru В поле Установить в ячейкеввести адрес ячейки, где вычисляется общая месячная зарплата всех сотрудников больницы. В поле Значениеввести предельное значение месячного фонда зарплаты. В поле Изменяя значение ячейкиввести адрес ячейки, где находится оклад санитарки. После нажатия ОКпроизойдет автоматический подбор значения оклада санитарки таким образом, чтобы общий месячный фонд зарплаты составил $10000.

Чтобы упростить эту работу, создайте простейший макрос — программу на языке VBA (VisualBasicforApplication), встроенном в офисные программы. Это можно сделать, не зная пока самого языка, с помощью транслятора MacroRecorder, который переводит на язык VBA действия пользователя с момента его запуска до окончания записи макроса. Для активизации MacroRecorder выбираем команду Сервис | Макрос | Начать запись. В появившемся диалоговом окне Запись макроса(рис. 2) задаем имя макроса (например, «Staff») и описание макроса (необязательно).

Создание собственных функций рабочего листа - student2.ru В поле Сохранить в:оставляем опцию по умолчанию Эта книга(тогда созданный макрос сохранится на новом листе модуля в активной рабочей книге). Будущий макрос можно запускать с помощью сочетания клавиш клавиатуры, например,Ctrl+z, если это указать в поле Сочетание клавиш.

После нажатия ОКвсе ваши действия над ячейками будут записываться. Для того чтобы остановить запись, необходимо нажать на кнопку Остановить записьна появившейся на экране плавающей панели или выполнить команду Сервис | Макрос | Остановить запись.

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

Чтобы посмотреть, какая же все-таки VBA-программа «создана» нами, выполним команду Сервис | Макрос | Макросы|. В появившемся диалоговом окне выберем макрос с именем «Staff» и нажмем кнопку Изменить. Откроется главное окно редактора VBA с текстом записанного макроса, например:

SubStaff()

‘

‘ StaffМакрос

' Штатное расписание больницы

‘

Range(“I14”).Select

Range(“I14”).GoalSeek Goal:=10000, ChangingCell:=Range(“H6”)

EndSub

Именно эта процедура и выполняется, если в диалоговом окне Макросынажать кнопку Выполнитьили на клавиатуре набрать указанное сочетание Ctrl+z. Для заданного нового количества штатных единиц будут рассчитаны новые оклады.

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

Кнопка является одним из элементов управления листа, создаваемых с помощью панели инструментов Формы. Обычно этой панели нет на экране, поэтому выполняем командуСервис | Настройка | Панели инструментов | Формы. На экран выводится панель инструментов Формы(рис. 3). Выбираем на ней щелчком мыши форму Кнопка. При этом указатель мыши превращается в тонкий крестик. Щелкаем им по листу. На нем появляется кнопка с именемКнопка1и одновременно открывается диалоговое окно Назначение макроса объекту. В поле Имя макросавыбираем имя нашего макроса «Stuff».

Создание собственных функций рабочего листа - student2.ru Теперь указанная выше процедура расчета окладов будет выполняться простым нажатием кнопки.

Можно изменить формат кнопки (шрифт надписи, размер и т.п.). Для этого следует вызвать контекстно-зависимое меню и выполнить необходимые операции.

Кнопку вызова макроса можно разместить и на любой из панелей инструментов. Для этого выполняем команду Сервис | Настройка | Команды | Макросы | Настраиваемая кнопка.Удерживая левую кнопку мыши, перетаскиваем кнопку Создание собственных функций рабочего листа - student2.ru на панель инструментов. Вызвав контекстно-зависимое меню, выбираем пункт Назначить макрос. В появившемся диалоговом окне выбираем имя нашего макроса Staff. Закрываем диалоговое окно Настройка. Кнопка готова к работе. Можно отредактировать всплывающее имя кнопки и рисунок на ней. Для этого необходимо сначала щелкнуть по ней правой кнопкой мыши и в появившемся меню выбрать Настройка. Затем еще раз щелкнуть по ней правой кнопкой мыши и в контекстно-зависимом меню выполнить необходимые операции.

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

ЗАДАНИЯ:

Вариант 1

1. Создайте макрос для Excel, изменяющий цвет и размер символов в ячейке.

Вариант 2

1. Создайте макрос для Excel, производящий автозаполнение строки ячеек месяцами.

Вариант 3

1. Создайте макрос для Excel, устанавливающий название рабочего листа.

Вариант 4

1. Создайте макрос для Excel, меняющий местами содержимое двух ячеек.

Вариант 5

1. Создайте макрос для Excel, меняющий местами заданные строки.

Вариант 6