Создание пользовательских функций

Создание макросов в MS Eхcel c использованием макрорекордера

Цель работы:

· изучить возможности создания макросов в MS Eхcel с помощью макрорекордера;

· получить практические навыки написания пользовательских функций в модуле редактора VBA(Visual Basic for Application);

· создание команд меню и кнопок на панели инструментов для запуска макроса.

Для автоматизации рабочего места пользователя записывают макросы с помощью макрорекордера. Макрос-этопрограмма,состоящая из списка команд, которые должны бытьвыполнены приложением.VBA - это среда программирования, разработанная для создания макросов в приложениях. Если пользователь не умеет программировать, то с помощью VBA может «записать» макрос и назначить его какой-либо кнопке. Макрорекордер записывает каждый шаг, вызов команды, перемещение мыши, ввод формул, ввод с клавиатуры. После того как запись окончена, нужно остановить рекордер.

Задание 1

Создать таблицу и сохранить ее в своей папке под своим именем, таблицу не форматировать.

Таблица №1

Задание 2

Создать следующие пользовательские функции для расчета таблицы:

1. функцию для расчета отчислений в пенсионный фонд;

-отчисления в пенсионный фонд составляют 1% от оклада, поэтому получим следующее выражение:

отчисления в пенсионный фонд=оклад*1%;

1. функцию, которая вычисляет подоходный налог;

-подоходный налог составляет 13% от оклада за вычетом пенсионного фонда:

подоходный налог = (оклад - пенсионный фонд)*0.13

1. функцию, которая вычисляет все вычеты из заработной платы работника;

удержания = подоходный налог +отчисления в пенсионный фонд ;

1. функция, которая вычисляет алименты:

Если один ребенок, то

• алименты =25%*(оклад - удержания),

Если два и более, то

• алименты =33%*( оклад - удержания);
1. функцию, которая вычисляет сумму, выдаваемую работнику на руки.

Итого к выдаче = оклад - удержания - алименты;

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

· войти в редактор VBA СЕРВИС/МАКРОС/РЕДАКТОР VBA.( ALT+F11);

· Вставить в окно проекта МОДУЛЬ команда ВСТАВКА(INSERT)/МОДУЛЬ(MODULE);

• в него вставить процедуру FUNCTION- ВСТАВКА/ПРОЦЕДУРА(PROCEDURE) ,в ДО выбрать процедуру функцию и дать имя пользовательской функции (без пробелов ) и ОК.

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

Пример пользовательской функции для вычисления алиментов:

Public Function Алименты (оклад, удержания, кол_детей)

If кол_детей = 1 Then Алименты = 0.25 * (оклад - удержания)

If кол_детей >= 2 Then Алименты = 0.33 * (оклад - удержания)

If кол_детей = 0 Then Алименты = 0

End Function

В этой пользовательской функции оклад, удержания, кол_детей –аргументы, а Алименты –имя функции.

Задание 3

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

Порядок записи макроса:

Команда Сервис/Макрос/Начать записьв диалоговом окне (Рис.1) Запись макросадать имя макросу, сохранить в Личной книге макросов(Personal.xls),описать макрос, назначить макросу горячие клавиши и ОК.

Рис.1

На листе MS Eхcel появится ПИ с двумя кнопками .

Начать запись:

• открыть файл в котором была создана таблица с исходными данными команда Файл/Открыть;
• провести в таблице расчет зарплаты, используя созданные пользовательские функции. Курсор поставить в ячейку под заголовок Пенсионный фонд и запустить мастер функций, найти среди категории пользовательские функцию, для расчета отчислений в пенсионный фонд ;
• расчет провести для первого в таблице сотрудника, а далее используя механизм копирования формул заполнить столбец;
• по аналогии провести расчеты по всей ведомости;
• рассчитать строчку «Итого по ведомости», используя функцию Автосумма()и механизм копирования формул;
• отформатировать таблицу по своему усмотрению Формат/Ячейки;
• остановить запись макроса ,используя кнопку останова на панели инструментов или меню Сервис/Макрос/Остановить запись.
• сохранить таблицу с начисленной заработной платой в файле под другим именем.

Задание 4

Выполнить созданный макрос либо из меню Сервис/Макрос/Макросывыбрать название из списка и запустить или используя горячие клавиши, которые задали для этого макроса.

Файл сохранить под новым именем!

Задание 5

Назначение макросу команду меню. В меню Сервис включить новый пункт Зарплата и связать его с созданным макросом.

Выбрать в меню Сервис команду Настройка,появится ДО Настройка (Рис.2),вкладка Командывыделить категориюмакросы.

Рис.2

В правом списке щелчок л. м. на элементе Настраиваемая команда менюи, не отпуская кнопку отбуксировать элемент на меню СЕРВИС.При перемещении мыши вдоль меню будет перемещаться горизонтальнаялиния, которая отмечает будущее положение вставляемой в меню команды. Отбуксировать мышь так, чтобы горизонтальная линия располагалась за командой Макроси отпустить кнопку. В меню появиться окруженная рамкой команда Настраиваемая команда меню. Щелчок пр.м. на команде, открыть контекстное меню. Очистить поле и ввести в него нужную команду (например, Зарплата) и ВВОД. Еще раз открыть контекстное меню команды. Выбрать команду Назначить макрос,откроется диалог, Назначить макрос,выбрать в списке макрос и ОК. Щелчок на кнопке Закрыть.

Задание 6

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

Выбрать в меню Сервис команду Настройка,появится диалог НАСТРОЙКА,вкладка КОМАНДЫвыделить категориюмакросы.В правом списке щелчок л.м.на элементе Настраиваемая кнопкаи не отпуская кнопку отбуксировать ее на панель инструментов , после того как отбуксировали кнопку ее можно изменить нажав кнопкуИзменить выделенный объект(кнопка становится активной)в ДОНастройказадается имя кнопке и изменяется значок из команды Выбрать значок для кнопки .Из контекстного меню выбрать команду Назначить макрос,и ОК .Щелчок на кнопке Закрыть закроется окно диалога НАСТРОЙКИ интерфейса MS Excel.

При создании кнопки задать свойству кнопки всплывающая подсказка - Зарплата.

Используя редактор кнопок отредактировать кнопку по своему усмотрению.

Задание 7

Создать графический объект, используя панель инструментов Рисование, привязать к объекту макрос используя контекстное меню. Отредактировать объект, используя команду Формат объектаиз контекстного меню.

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

Задание 8

Просмотреть записанный макрос:CЕРВИС/МАКРОС/МАКРОСЫкнопка Изменить ,откроется редактор VBA с записанным макросом.

Контрольные вопросы

1. Что такое макрос?
2. Как создаются макросы в MSEхcel?
3. Как просмотреть «написанный» макрос?
4. Как назначить макросу кнопку или команду меню?
5. Как создать пользовательскую функцию?
6. Как удалить команду меню или кнопку ПИ, запускающие макрос на выполнение?

Литература

1. Лавренев С.М. «EXCEL.Сборник примеров и задач» - М., Финансы и статистика,
   2000. – 336 с.
2. Макарова Н.В. «Статистика в EXCEL»- М.,Финансы и статистика, 2003. – 366 с.
3. Гарнаев А. «EXCEL,VBA,INTERNET в экономике и финансах» - СПб. :БХВ - Петербург.»,2001.-816с.
4. Евдокимов В.В. «Экономическая информатика». Учебник для вузов. СПб. : Питер.1997.-598с.
5. С.В.Симонович. Информатика Базовый курс. СЛБ 2007 г.
6. Ф .Новиков, А. Яценко. Microsoft Office в целом.М.,2005 г.
7. Г.А.Новиков, П.А.Новиков, М.В.Орлова, А.М.Пылькин. Работа с текстовым процессором MS Word.-M,:Горячая линия- Телеком,2005.-198 с.

8.Безручко В.Т. Практикум по курсу «Информатика» Работа в WINDOS,WORD,EXCEL. М., Финансы и статистика,2004,271 c.

9. Информатика и информационные технологии / Уч. пособие. Под ред. Ю.Д. Романовой. M., 2010.

1. Колосков П.В, Кузнецова Н.А.. Самоучитель Windows Vista. Наука и техника, Санкт - Петербург, 2007.
2. Новиков Ф., Яценко А. Microsoft Office 2000 в целом. – СПб.: Санкт - Петербург, 1999.
3. Пасечник А. Переход на Windows 2000: для профессионалов. – СПб.: Питер, 2000.
4. Гаевский А.В. Самоучитель Word , Excel,Электронная почта. – Технолоджи - 3000: Москва, 2005.
5. Безручко В.Т.Практическая работаWord 2000. – СПб.: Питер, 2004.
6. Рудикова Л.В.Microsoft Excel для студентов. – СПб.: Санкт - Петербург, 2006.
7. Маликова Л.В., Пылькин А.Н., Жулева С.Ю. Практический курс по электронным таблицам MS Excel . – Телеком: Москва, 2006.
8. Калабухова Г.В., Титов В.М. Компьютерный практикум по информатике. М., 2008.
9. Лапунов А.В., Ульянов О.В., Прокди Р.Г. Интернет: Самоучитель. – М.: Наука и Техника, 2009.

19. Хожайнова Г.И. Курс лекций в электронном варианте.

Хожайнова Галина Ивановна

[1] Журнал издается в г.Москва