Использование макросов

Практическая работа №12

Цели работы: 1) освоение приемов работы с макросами; 2) использование средств проверки вводимых данных в ячейки; 3) добавление и использование кнопок в работе Excel;

1. Создайте новую рабочую книгу MS Excel и назовите ее «Макросы». В данной книге у нас будут храниться платежные ведомости сотрудников предприятия за 3 месяца (январь, февраль, март), каждая из которых будет сопровождаться расчетными статистическими показателями (сумма, среднее, максимум, минимум, стандартное отклонение). Каждая ведомость будет находиться на отдельном рабочем листе, т.е. необходимо заполнить 3 рабочих листа, озаглавив их Январь, Февраль, Март. Примерный вид платежной ведомости за каждый месяц представлен на рис. 1.

Рис. 1

2. Номера сотрудников в столбце А введите с помощью автозаполнения. Для этого введите в ячейку А2 число 1, в ячейку А3 число 2, выделите обе ячейки(А2:А3), наведите курсор на нижний правый угол ячейки A3 и, нажав левую кнопку мыши, протяните мышь вниз до ячейки А11. У вас должен получиться ряд чисел с 1 до 10.

3. В столбец В введите фамилии сотрудников.

4. Выделите пустые ячейки С2:С11, предназначенные для ввода зарплаты сотрудников. Зайдите на вкладку Данные, в группу Работа с данными и нажмите на кнопку Проверка данных. В строке Тип данных выберите Целое число, в параметре Значение выберите Больше, в строке Минимум введите 7000 (рублей; величина прожиточного минимума в России), после чего нажмите Ок. Введите в ячейки C2:C11 величину заработной платы каждого сотрудника и убедитесь, что ввод числа меньше 7000 невозможен.

5. Расчет премии сотрудника производится по формуле: = Зарплата *0,25. Введите эту формулу в ячейку D2, заменив слово Зарплата ячейкой С2, и растиражируйте ее на весь столбец (D2:D11).

6. В столбце Всего рассчитайте номинальную величину дохода каждого сотрудника по формуле: = Зарплата + Премия. Введите эту формулу в ячейку E2, заменив слова Зарплата и Премия нужными ячейками, и растиражируйте ее на весь столбец (E2:E11).

7. Величина отчислений в Пенсионный фонд (столбец F) рассчитывается по формуле: = Всего *0,01. Введите эту формулу в ячейку F2, заменив слово Всего нужной ячейкой, и растиражируйте ее на весь столбец (F2:F11).

8. Величина налогооблагаемой базы (столбец G) рассчитывается по формуле: = Всего-Удержано в ПФ. Введите эту формулу в ячейку G2, заменив слова Всего и Удержано в ПФ нужными ячейками, и растиражируйте ее на весь столбец (G2:G11).

9. Величина налога (столбец H) рассчитывается по формуле: = НОБ *0,13. Введите эту формулу в ячейку Н2, заменив слово НОБ ячейкой G2, и растиражируйте ее на весь столбец (G2:G11).

10. Столбец Выдача рассчитывается по формуле: = Всего-Удержано в ПФ-Налог. Введите эту формулу в ячейку I2, заменив слова Всего, Удержано в ПФ, Налог нужными ячейками, и растиражируйте ее на весь столбец (I2:11).

11. Отсортируйте январскую платежную ведомость по фамилии сотрудников (по алфавиту). Выделите весь диапазон А1:I11 и на вкладке Данные выберите Сортировка. Далее выберите Сортировать по фамилии, от А до Я.

12. В ячейки В13:В17 введите названия статистических показателей, рассчитываемых по каждому столбцу ведомости: Сумма, Среднее, Максимум, Минимум, Стандартное отклонение как на рис. 1.

13. Назовите заполненный рабочий лист Январь.

14. Скопируйте диапазон А1:I17 на соседние листы. Назовите их Февраль и Март.

15. Вернитесь на рабочий лист Январь. Выделите ячейку С13, в которой будет рассчитана суммарная зарплата сотрудников. Перейдите на вкладку Вид. В группе Макросы щелкните на стрелке под кнопкой Макросы. В появившемся меню нажмите кнопку Относительные ссылки. Снова щелкните на стрелке под кнопкой Макросы и нажмите кнопку Запись макроса. Задайте имя макросу Стат, сочетание клавиш Ctrl+S и и выберите Сохранить –> Эта книга, нажмите Ок. Вы включили режим записи макроса.

16. Вернитесь на вкладку Главная. Щелкните на кнопке ∑ (Сумма) и рассчитайте суммарную зарплату сотрудников. После этого выделите ячейку С13, нажав левую кнопку мыши, растиражируйте ее на всю строку С13:I13.

17. Выделите ячейку С14 и рассчитайте в ней среднюю зарплату сотрудников. Для этого щелкните на стрелке, справа от кнопки ∑ и выберите строку Среднее. Выделите нужный диапазон (С2:С11) и нажмите Enter. Растиражируйте полученную в ячейке C14 формулу на всю строку С14:I14.

18. В ячейках С15 и С16 рассчитайте максимальную и минимальную зарплату соответственно. Для этого щелкните на стрелке, справа от кнопки ∑ и выберите строку Максимум или Минимум. Затем выделите нужный диапазон (С2:С11) и нажмите Enter. Растиражируйте полученные в ячейках C15 и С16 формулы на строки С15:I15 и С16:I16 соответственно.

19. Выделите ячейку С17 и рассчитайте в ней стандартное отклонение по столбцу Зарплата. Для этого нажмите на кнопку f_x (вставить функцию) и найдите функцию СТАНДОТКЛОН. В строке Число 1 укажите диапазон С2:С11 и нажмите Ок. После этого растиражируйте формулу из ячейки C17 на всю строку С17:I17. Примерный вид платежной ведомости за январь с рассчитанными статистическими показателями приведен на рис. 2.

20. Перейдите на вкладку Вид, нажмите на стрелку под кнопкой Макросы и щелкните на строке остановить запись. Макрос записан и готов к работе.

21. Перейдите на лист Февраль. Выделите пустую ячейку С13 и нажмите сочетание клавиш Ctrl+s (не забудьте, что для этого должна стоять английская раскладка). Расчет статистических показателей в диапазоне С13:I17 должен произойти автоматически.

22. То же самое сделайте на листе Март.

Рис. 2

23. Давайте модернизируем рабочий лист Январь и добавим на него 2 управляющие кнопки. Одна кнопка при нажатии на нее будет рассчитывать статистические показатели (вместо сочетания клавиш Ctrl+s), нажатие на другую кнопку приведет к удалению всех рассчитанных статистических показателей. Для этого нам потребуется вкладка Разработчик. Если у вас этой вкладки нет, ее нужно добавить следующим образом: Файл->Параметры->Настройка ленты и в списке Основные вкладки ставим галочку в строке Разработчик, нажимаем Ок.

24. Переходим на вкладку Разработчик. В группе Элементы управления нажимаем на кнопку Вставить и щелкаем на элементе Кнопка, после чего курсор мыши должен изменить вид на крестик . Выбираем место для кнопки, нажимаем левую кнопку мыши и, не отпуская, тянем ее до нужных размеров кнопки, после чего отпускаем. В появившемся окне назначаем кнопке макрос Стат и нажимаем Ок. Затем щелкаем на созданной кнопке правой кнопкой мыши и выбираем пункт Изменить текст, присваиваем кнопке имя Рассчитать. Проверьте работоспособность кнопки. Для этого удалите рассчитанные стат. показатели в диапазоне С13:I17 и, оставляя выделенным диапазон С13:I17, нажмите кнопку Рассчитать. Выделенный диапазон должен вновь заполнится рассчитанными значениями. Можно было выделять не весь диапазон С13:I17, а выделить только ячейку C13, после чего нажать кнопку Рассчитать. Результат должен быть тот же. Проверьте.

25. Теперь создадим новый макрос, автоматически очищающий диапазон С13:I17. Выделите ячейку С13. Перейдите на вкладку Вид и начните запись макроса. Присвойте ему имя Удалить. Сочетание клавиш можно не указывать (он будет запускаться при нажатии соответствующей кнопки), сохранение произведите в эту книгу. Выделите диапазон С13:I17 и нажмите на клавиатуре клавишу Del. Диапазон должен очиститься. Теперь остановите запись. Макрос создан.

26. Создайте вторую кнопку. Можете сразу не назначать ей макрос, поэтому нажмите Ок. Присвойте кнопке имя Удалить. Теперь щелкните по ней правой кнопкой и в появившемся меню нажмите Назначить макрос, затем выберите макрос Удалить.

27. Проверьте работоспособность кнопок. Очистите диапазон С13:I17. Выделите ячейку С13 и нажмите кнопку Рассчитать. Снова выделите ячейку С13 и нажмите кнопку Удалить.

28. Повторите пройденный в практикуме материал.