Для автоматизации трудоемких или часто повторяющихся задач в Excel используются макросы. Макрос — это последовательность команд и действий, сохраненная под одним именем. Макрос можно создать двумя способами: 1) записать действия автоматически; 2) разработать процедуру в редакторе VBA (см. тему 8).
Запись макроса начинается после выполнения команды Сервис—Макрос—Начать запись… Можно записывать макрос с абсолютными ссылками и с относительными ссылками. Способ записи определяется состоянием кнопки Относительная ссылка на панели инструментов Остановка записи (рис. 7.1), которая появляется в режиме записи макроса. Если кнопку включить, то будет записываться макрос с относительными ссылками, если выключить — с абсолютными. Макрос с относительными ссылками выполняется, начиная с ячейки, которая была текущей перед началом выполнения макроса. Макрос с абсолютными ссылками выполняется в тех же ячейках, в которых он записывался.
Рис. 7.1. Панель инструментов Остановка записи
|
|
Выполнение макроса можно задать комбинации клавиш, кнопке на панели инструментов, элементу управления формы или графическому объекту на рабочем листе.
Удаление макроса производится в диалоге Сервис—Макрос—Макросы… Нужно выбрать в списке требуемый макрос и нажать кнопку Удалить.
Элементы управления
Для автоматизации заполнения шаблонов в Excel используются элементы управления, находящиеся на панелях инструментов Формы (рис. 7.2) и Элементы управления. Элемент управления — это графический объект, позволяющий пользователю управлять приложением. Чтобы создать элемент управления, нужно выбрать его на панели Формы и нарисовать в нужном месте рабочего листа. Щелкнув правой кнопкой мыши по созданному элементу и выбрав в контекстном меню команду Формат объекта…, можно изменить его параметры (рис. 7.5), например связать с какой-либо ячейкой листа.
|
|
|
|
Рис. 7.2. Панель инструментов Формы
1. Группа — рамка, которая используется для объединения переключателей.
2. Кнопка — используется для выполнения назначенного ей макроса.
3. Флажок — если установлен, то в связанной с ним ячейке выводится значение ИСТИНА, если снят — ЛОЖЬ.
4. Переключатель — используется всегда в группе. Когда переключатели объединены в группу, только один из них может установлен. Тогда в ячейке, связанной с этой группой переключателей, выводится порядковый номер выбранного переключателя.
5. Список (а) и Поле со списком (б) — отображают список значений, который нужно предварительно ввести в ячейки, а затем указать диапазон этих ячеек в параметрах элемента управления. Номер выбранного в списке значения помещается в ячейку, связанную со списком.
|
|
6. Полоса прокрутки (а) и Счетчик (б) — изменяют значение связанной с ними ячейки. В параметрах элемента управления можно задать диапазон и шаг изменения этого значения.
При использовании панели Элементы управления изменение параметров элемента осуществляется с помощью команды контекстного меню Свойства, которая открывает окно Properties.
Примеры
Пример 7.1. Записать макрос под именем «Первый день месяца», который начиная с текущей ячейки выводит даты первых дней шести месяцев, следующих за текущим, и форматирует их так, чтобы название месяца выводилось словом, выравнивание по левому краю, цвет текста — синий, шрифт — Courier New полужирный.Выполнение макроса назначить автофигуре.
Выполнение:
1. Выполнить команду Сервис—Макрос—Начать запись…
2. В открывшемся диалоге задать имя макроса «Первый день месяца» и после нажатия ОК включить кнопку Относительная ссылка (рис. 7.1).
3. Проделать действия, которые должен выполнять макрос:
- в текущую ячейку ввести формулу, которая будет возвращать дату первого числа месяца, следующего за текущим (текущей является дата 15.05.06): =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;1)
- выделить 6 ячеек, начиная с введенной формулы, выполнить команду Правка—Заполнить—Прогрессия… и в открывшемся диалоге указать Тип à Даты, Единицы à Месяц.
- открыть диалог Формат—Ячейки…; на закладке Число задать формат à
ДД ММММ ГГГГ; на закладке Выравнивание à по горизонтали по левому краю; на закладке Шрифт à шрифт — Courier New полужирный, цвет — синий.
4. Нажать кнопку Остановить запись (рис. 7.1).
5. На рабочем листе нарисовать какую-либо автофигуру, выбрав ее на панели инструментов Рисование. В контекстном меню автофигуры выбрать команду Назначить макрос. В открывшемся диалоге указать макрос «Первый день месяца» и нажать ОК.
Рис. 7.3. Результат выполнения макроса «Первый день месяца»
6. Сделать текущей любую ячейку и проверить работу макроса щелкнув по автофигуре (рис. 7.3).
Пример 7.2. Создать бланк заказ-наряда (рис. 7.4), в котором наименование работы выводится в ячейке В2 с помощью списка, а количество часов в ячейке В3 заполняется с помощью полосы прокрутки.
Рис. 7.4. Создание бланка с элементами управления формы.
Выполнение:
- Заполнить ячейки данными как на рис. 7.4, кроме ячеек В2, В3, Е1.
- Нарисовать список (рис. 7.2, 5-а) и полосу прокрутки (рис. 7.2, 6-а).
- Изменить параметры созданных элементов управления (рис. 7.5). Так как полоса прокрутки связана с ячейкой В3, то щелчок мышью по стрелке полосы прокрутки будет менять значение ячейки на один шаг — 1. Список связан с ячейкой Е1, поэтому при выборе значения «Побелка потолка» в ячейке будет выведен порядковый номер этого значения в списке — 3.
Рис. 7.5. Параметры полосы прокрутки (слева) и списка (справа).
- Чтобы вывести в В2 наименование работы, а не ее номер, нужно использовать функцию ВПР, которая в зависимости от номера выбранной из списка работы в ячейке Е1, будет возвращать соответствующее значение из второго столбца таблицы Е2:F5. Таким образом, формула в В2 будет следующей: =ВПР(E1;E2:F5;2).
Замечание. Полосу прокрутки и список можно создать с помощью панели инструментов Элементы управления и затем изменить их свойства, как показано на рисунке:
Практические задания
На оглавление