Функция пользователя

Знания основ программирования на VBA может быть полезно при создании функций пользователя. С функциями пользователя можно работать с помощью мастера функций точно так же , как и со встроенными функциями рабочего листа.

Рассмотрим создание функции пользователя на примере.

Результаты работы менеджеров фирмы в квартале представлены
в таблице.

Рис.11. Данные о стаже работы и объеме реализованной продукции сотрудниками фирмы

Руководство решило выплатить премию по результатам квартала с учетом следующих условий.

Если продукции реализовано не меньше, чем на 1 000 000 руб., то премия составляет 1% от стоимости реализованной продукции.

Если продукции продано меньше, чем на 1 000 000 руб., то премия составляет 0,5% от стоимости реализованной продукции.

Если стаж работы в фирме не меньше 5 лет, то к премии выплачивается доплата в размере 0,2% от стоимости реализованной продукции.

Первый способ решения задачи. Используя логическую функцию ЕСЛИ составим и введем в ячейку формулу: ЕСЛИ(C2>1000000;1%*C2;0,5%*C2)+ЕСЛИ(B2>=5;0,2%*C2;0).

Размножим формулу до ячейки D6.

Однако при частом использовании заданных условий расчета премии целесообразно создать функцию пользователя.

Второй способ решения задачи.

В редакторе Visual Basic выполним команду Insert –Module. В открывшемся пустом окне наберем текст программы см. рис. 12.

Рис.12. Пример функции пользователя для расчета премии

Выйдите из редактора Visual Basic, вызовите мастер функций и выберите категорию Определенные пользователем. В окне Функция вы увидите функцию Премия. Вызовите эту функцию, и вы увидите диалоговое окно см. рис. 13. Задавая объем и стаж, вы можете пользоваться этой функцией, как и любой другой встроенной функцией.

Рис.13. Диалоговое окно функции пользователя Премия

Задачи

Диалог с компьютером. С помощью встроенных диалоговых окон (MsgBox и InputBox) создайте программу (макрос), реализующую следующий диалог с компьютером:

Компьютер: «Как вас зовут?»

Пользователь: ХХХ (вводит свое имя)

Компьютер: «Привет, ХХХ»(введенное имя)

«Сколько Вам лет?»

Пользователь: ГГ (вводит число лет)

Если ГГ(число лет) < 20, то компьютер выдает «Уже немало,ХХХ», в противном случае компьютер сообщает «Вы прекрасно выглядите для своих ГГ лет»

Создайте кнопку для запуска программы.

Угадай число. Создайте программу, реализующую следующий алгоритм. Компьютер вырабатывает случайное число в диапазоне от 0 до 100, не сообщая, запоминает его и предлагает «Угадай число». Пользователь вводит число. Если введенное число меньше того, которое придумал компьютер, то компьютер сообщает «Больше». Если введенное число больше того, которое придумал компьютер, то компьютер сообщает «Меньше». Если пользователь угадал число, то компьютер сообщает «Угадал!»

Подсказка. Для того, чтобы компьютер выработал случайное число Х в диапазоне от 0 до 100, начните программу с операторов

Randomize

X=Rnd*100

Для предыдущей задачи «Угадай число» выводите на лист Excel каждое из чисел, введенных пользователем, а после того, как число угадано, выведите на лист количество попыток при угадывании числа, общее время игры и вопрос «Продолжим д/н». При ответе «д» компьютер вырабатывает новое случайное число, и игра повторяется.

Экскурсии. Фирма реализует экскурсионные путевки по цене 500 руб. Для групп от 5 человек дается скидка 5% на каждого человека, для групп более 8 человек дается скидка на каждого – 7%. Создайте программу, реализующую следующий алгоритм. На лист Excel выводятся заголовки столбцов: Фамилия клиента, Количество путевок, Процент скидки, Получено с клиента. В диалоге запрашивается фамилия клиента и количество путевок. Процент скидки и Получено с клиента рассчитывается. Для каждого клиента записывайте данные в очередную строку. После записи каждой строки выводите диалоговое окно с вопросом «Закончить д/н?», при ответе «д» закончить работу программы. Создайте кнопку для запуска программы.

Для задачи «Экскурсии» (упр.4) создайте макрос для расчета общей суммы денег ( Итого), полученных с клиентов. (Первый вариант программы: количество клиентов известно, второй вариант – количество клиентов определите с помощью встроенной функции COUNTA). Создайте кнопку для вывода суммы в нужный столбец в конце таблицы.

По таблице «Экскурсии» создайте макрос для вывода на лист количества клиентов, получивших скидку 7%.

Создайте таблицу Сотрудники

Создайте таблицуСотрудники

Добавьте в нее несколько строк.

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

Откройте таблицу Учет(сотрудники), расположенную в папке Справка (диск D:). Сохраните ее в своей папке. Выведите количество сотрудников, имеющих разряд выше 13-го.

Создайте таблицу Поставщики

Добавьте в нее несколько строк

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

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

Добавьте в нее несколько строк

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

Откройте таблицу Учет(сотрудники), расположенную в папке Справка (диск D:). Сохраните ее в своей папке. Создайте кнопку, по нажатию на которую на текущий лист выводится общая сумма заработной платы сотрудников второго отдела.

По таблице Учет(сотрудники) см.упр.11 создайте кнопку, по нажатию на которую на текущий лист выводится средняя заработная плата конструкторов.

Для таблицы Учет(сотрудники) см.упр.11 создайте пользовательскую функцию для расчета средней заработной платы сотрудников разных должностей. (Должность – это параметр функции).

По таблице «Экскурсии» см.упр.4 создайте макрос для вывода на лист информации о клиентах (фамилия, количество путевок, получено с клиента), получивших скидку 5%. Создайте кнопку для запуска макроса.

Отредактируйте программу для упражнения 14. По нажатию на кнопку должно появляться диалоговое окно «Введите размер скидки»». После того, как пользователь ввел размер скидки (например, 5%), на листе должен появиться список клиентов, получивших заданную пользователем скидку.

По таблице «Технические средства» см. упр.10 создайте кнопку, по нажатию на которую на текущем листе создается новая таблица с заголовками «Тех.средство» и «Количество», в которую выводится список технических средств, установленных в бухгалтерии.

Для таблицы Учет(сотрудники) см.упр.11 выведите список сотрудников моложе 30 лет в таблицу с заголовками «Фамилия», «Дата рождения», «Должность», «Разряд», «Зарплата».

По таблице «Экскурсии» (упр.4) создайте макрос для вывода на лист (например, в 15-ю строку) информации о клиенте (фамилия, количество путевок, получено с клиента, номер строки), заказавшего максимальное количество путевок. Создайте кнопку для запуска макроса.

По таблице «Должности» (упр.7) создайте макрос для вывода на лист фамилии и должности сотрудника, получающего максимальную зарплату.

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

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

Товары 1.Заполните таблицу Товары. В отдельных ячейках создайте два поля со списками: в ячейке d10 – Товары (источник строк для списка – наименования товаров); в ячейке d11 – характеристика –список из трех позиций: цена, код производителя, кол-во на складе.

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

Товары 2. Заполните таблицу Товары. Создайте макрос для расчета Итого (кол-во и стоимость). Создайте кнопку для запуска макроса.

Итого:

Переименуйте Лист 1 в Меню, Лист2 в – БД. Создайте пользовательскую форму, содержащую три поля для ввода: наименование товара, цена и количество; надпись – стоимость, а также две кнопки Ок и Отмена. Стоимость вычисляется автоматически по формуле: цена* количество. При изменении данных в полях количество и цена стоимость должна пересчитываться. На листе Меню создайте кнопку для вызова созданной формы. Подготовьте программы для кнопок Ок и Отмена. По нажатию на кнопку Ок данные, введенные в форму, должны быть записаны на лист БД. При нажатии на кнопку Отмена форма должна исчезнуть с экрана.

Создайте пользовательскую форму для ввода фамилии клиента туристической фирмы, названия маршрута, цены путевки и размера скидки. В форме создайте надпись – стоимость,а такжекнопкиOкиОтмена.Стоимость вычисляется автоматически по формуле: цена путевки – размер скидки* цена путевки. При изменении данных в полях цена путевки и размер скидки стоимость должна пересчитываться. На листе Меню создайте кнопку для вызова созданной формы. Подготовьте программы для кнопок Ок и Отмена. По нажатию на кнопку Ок данные, введенные в форму, должны быть записаны на лист БД. При нажатии на кнопку Отмена форма должна исчезнуть с экрана.

Переименуйте Лист1 в Меню, Лист2 – в Клиенты. На листе Клиенты введите несколько фамилий клиентов автосервиса. Создайте пользовательскую форму, содержащую поля для ввода – дата, причина обращения и поле со списком – клиент. На листе Меню создайте кнопку для вызова созданной формы. В появляющейся форме поле со списком должно быть заполнено.

Переименуйте Лист1 в Меню, Лист2 – в Должности, Лист3 – в Отделы. На листе Должности введите несколько должностей. На листе Отделы введите несколько наименований отделов. Создайте пользовательскую форму для отдела кадров, содержащую поле для ввода – фамилия нового сотрудника, два поля со списками – должность и отдел и кнопки Ок и Отмена. На листе Меню создайте кнопку для вызова созданной формы. В появляющейся форме поля со списками должны быть заполнены. Подготовьте программы для кнопок Ок и Отмена. По нажатию на кнопку Ок данные, введенные в форму, должны быть записаны на лист БД. При нажатии на кнопку Отмена форма должна исчезнуть с экрана.