Тема 9. Автоматизация работы в Excel.

Теоретические сведения

На оглавление

Запись и выполнение макросов

Для автоматизации трудоемких или часто повторяющихся задач в Excel используются макросы. Макрос — это последовательность команд и действий, сохраненная под одним именем. Макрос можно создать двумя способами: 1) записать действия автоматически; 2) разработать процедуру в редакторе VBA.

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

Для работы с макросами и элементами управления используется закладка Разработчик (рис. 9.1). Если такой закладки на ленте инструментов нет, ее нужно включить следующим образом:

- нажать кнопку Office , щелкнуть по кнопке ;

- открыть категорию Основные;

- установить флажок Показывать вкладку “Разработчик” на ленте.

Запись макроса начинается после нажатия кнопки Запись макроса. Способ записи определяется состоянием кнопки Относительные ссылки. Если кнопку включить, то будет записываться макрос с относительными ссылками, если выключить — с абсолютными.

Рис. 9.1. Закладка Разработчик ленты инструментов

Выполнение макроса можно задать комбинации клавиш, кнопке на панели инструментов, элементу управления формы или графическому объекту на рабочем листе.

Удаление макроса производится в диалоге Макросы, который открывается кнопкой Макросы на закладке Разработчик. Нужно выбрать в списке требуемый макрос и нажать кнопку Удалить. В этом же диалоге можно запустить выбранный макрос на выполнение, если не задан другой способ выполнения макроса.

Для автоматизации заполнения шаблонов в Excel используются элементы управления формы и элементы ActiveX. Элемент управления — это графический объект, позволяющий пользователю управлять приложением. Чтобы создать элемент управления, нужно раскрыть кнопку Вставить (рис. 9.1), выбрать требуемый элемент и растянуть мышью до желаемого размера в нужном месте рабочего листа. Когда элемент управления выделен, с помощью кнопки Свойства на закладке Разработчик можно изменять его параметры (рис. 9.6), например, связать с какой-либо ячейкой листа.

Рис. 9.2. Элементы управления формы

1. Группа — рамка, которая используется для объединения переключателей.

2. Кнопка — используется для выполнения назначенного ей макроса.

3. Флажок — если установлен, то в связанной с ним ячейке выводится значение ИСТИНА, если снят — ЛОЖЬ.

4. Переключатель — используется всегда в группе. Когда переключатели объединены в группу, только один из них может установлен. Тогда в ячейке, связанной с этой группой переключателей, выводится порядковый номер выбранного переключателя.

5. Список (а) и Поле со списком (б) — отображают список значений, который нужно предварительно ввести в ячейки, а затем указать диапазон этих ячеек в параметрах элемента управления. Номер выбранного в списке значения помещается в ячейку, связанную со списком.

6. Полоса прокрутки (а) и Счетчик (б) — изменяют значение связанной с ними ячейки. В параметрах элемента управления можно задать диапазон и шаг изменения этого значения.

Пример 9.1. Записать макрос под именем «Первый_день_месяца», который начиная с текущей ячейки выводит даты первых дней шести месяцев, следующих за текущим, и форматирует их так, чтобы название месяца выводилось словом, выравнивание по левому краю, цвет текста — синий, шрифт — Courier New полужирный.Выполнение макроса назначить автофигуре.

Выполнение:

1. Нажать кнопку Запись макроса на закладке Разработчик.

2. В открывшемся диалоге задать имя макроса — Первый_день_месяца, можно задать комбинацию клавиш и затем нажать ОК. При этом включится запись и кнопка Запись макроса будет преобразована в кнопку Остановить запись (рис. 9.3).

3. Включить кнопку Относительные ссылки (рис. 9.3).

Рис. 9.3. Вид фрагмента закладки Разработчик во время записи макроса

4. Проделать действия, которые должен выполнять макрос:

- в текущую ячейку ввести формулу, которая будет возвращать дату первого числа месяца, следующего за текущим (текущей является дата 15.05.06): =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;1)

- выделить 6 ячеек, начиная с введенной формулы, раскрыть кнопку Заполнить на закладке Главная и выбрать команду Прогрессия…;

- в открывшемся диалоге указать Тип à Даты, Единицы à Месяц.

- открыть диалог Формат ячеек…;

- на закладке Число задать формат ДД ММММ ГГГГ, на закладке Выравнивание à по горизонтали по левому краю, на закладке Шрифт à шрифт — Courier New полужирный, цвет — синий.

5. Нажать кнопку Остановить запись (рис. 9.3).

6. На рабочем листе нарисовать какую-либо автофигуру, выбрав ее на закладке Вставка. В контекстном меню автофигуры выбрать команду Назначить макрос. В открывшемся диалоге указать макрос «Первый­_день_месяца» и нажать ОК.

Рис. 9.4. Результат выполнения макроса «Первый_день_месяца»

7. Сделать текущей любую ячейку и проверить работу макроса щелкнув по автофигуре (рис. 9.4).

Пример 9.2. Создать бланк заказ-наряда (рис. 9.5), в котором наименование работы выводится в ячейке В2 с помощью списка, а количество часов в ячейке В3 заполняется с помощью полосы прокрутки.

Рис. 9.5. Создание бланка с элементами управления формы.

Выполнение:

1. Заполнить ячейки данными как на рис. 9.5, кроме ячеек В2, В3, Е1.
2. Нарисовать список (рис. 9.2, 5-а) и полосу прокрутки (рис. 9.2, 6-а).
3. Изменить параметры созданных элементов управления (рис. 9.6). Так как полоса прокрутки связана с ячейкой В3, то щелчок мышью по стрелке полосы прокрутки будет менять значение ячейки на один шаг — 1. Список связан с ячейкой Е1, поэтому при выборе значения «Побелка потолка» в ячейке будет выведен порядковый номер этого значения в списке — 3.

Рис. 9.6. Параметры полосы прокрутки (слева) и списка (справа).

1. Чтобы вывести в В2 наименование работы, а не ее номер, нужно использовать функцию ВПР, которая в зависимости от номера выбранной из списка работы в ячейке Е1, будет возвращать соответствующее значение из второго столбца таблицы Е2:F5. Таким образом, формула в В2 будет следующей: =ВПР(E1;E2:F5;2).

Замечание. Полосу прокрутки и список можно создать с помощью элементов ActiveX и затем изменить их свойства, как показано на рисунке:

 

Практические задания

На оглавление

Лабораторная работа № 9

Цель работы: научиться создавать и использовать макросы и элементы управления формы на рабочем листе для автоматизации работы в Excel.

Задания:

I. Записать макросы:

1. С относительными ссылками. Макрос должен выводить названия месяцев в столбце, начиная с текущей ячейки, со следующими элементами форматирования:

- цвет символов — красный,

- обрамление ячеек — тонкая линия,

- текст выровнен по центру,

- внешнее обрамление столбца — жирная линия.

На панели Элементы управления формы выбрать элемент Кнопка (см. рисунок 9.1), нарисовать его на рабочем листе, назвать МЕСЯЦЫ и назначить созданный макрос.

1. С абсолютными ссылками. Макрос должен очищать весь рабочий лист. На рабочем листе нарисовать любую автофигуру и назначить созданный макрос.

II. Создать шаблон для заполнения бланка заказа авиабилетов с элементами управления формы:

1. Заполнить ячейки данными, кроме ячеек С2:С4, С6 и С13, и вставить элементы управления формы:

2. Изменить свойства элементов управления:

2.1. Для поля со списком вывод на печать отключен, объемное затенение включено, список формируется по диапазону I3:I7, результат помещается в ячейку G3, т. е. в эту ячейку помещается номер элемента, который был выбран в списке.

2.2. Для счетчика вывод на печать отключен, объемное затенение включено, диапазон изменения от 1 до 10 с шагом 1, результат помещается в ячейку С6.

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

2.4. Для кнопки вывод на печать отключен.

3. В ячейках С2:С4 написать формулы (используя функцию ВПР), которые выводят город, время вылета и цену билета в зависимости от выбранного значения списка.

4. В ячейке С13 написать формулу для расчета суммы к оплате в зависимости от количества билетов и типа билета (для льготного — скидка 30%).

5. Снять защиту с ячеек, с которыми связаны элементы управления.

6. Формулы скрыть.

7. Скрыть столбцы, содержащие вспомогательные данные.

8. Создать и назначить кнопке «Печать» макрос, который задает альбомную раскладку и размер бумаги А5 и отображает шаблон в режиме предварительного просмотра.

9. Защитить лист и сохранить файл как шаблон.

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

1. Какими способами можно автоматизировать заполнение шаблона?

2. Зачем используются относительные ссылки при записи макроса?

3. Назовите элементы управления и их назначение.

4. Как создать элемент управления на рабочем листе и изменить его параметры?

5. Назовите способы выполнения макроса.