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

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

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

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

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

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

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

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

SubStaff()

‘

‘ StaffМакрос

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

‘

Range(“I14”).Select

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

EndSub

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

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

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

Теперь указанная выше процедура расчета окладов будет выполняться простым нажатием кнопки.

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

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

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

ЗАДАНИЯ: