Макросы и элементы управления
Макрос - это программа (процедура без параметров), написанная на языке VBA (Visual Basic for Applications), которая автоматизирует какую-либо задачу. Это может быть выполнение каких-либо повторяющихся действий пользователя по форматированию внешнего вида ячеек или одинаковых действий над записями таблицы, например сортировка и т.п. По форме макрос – это записанный по определенным правилам текст программы, который хранится в тех же файлах, что и листы рабочей книги, в особых областях, называемых модулями.
Простейший макрос для форматирования
Прежде всего нужно изменить уровень безопасности (иначе работа с макросами будет невозможна): кнопка Office → Параметры Excel (внизу) → Центр управления безопасностью (слева) → Параметры центра управления безопасностью (справа):
Далее Параметры макросов (слева) → Включить все макросы (справа):
Если вкладка Разработчик недоступна, то необходимо выполнить следующие действия: кнопка Office → Параметры Excel (внизу) → Основные (слева) → поставить “галочку” в пункте Показывать вкладку “Разработчик” на ленте (справа):
Создадим свой первый макрос. Для этого на вкладке Разработчик в группе Код нажимаем кнопку Запись макроса:
Появится диалоговое окно:
Нажимаем кнопку OK. При этом начинается запись всех ваши действий.
Для прекращения записи служит кнопка Остановить запись, которая появится на месте кнопки Запись макроса:
Записываются все перемещения по листу, смена рабочих листов, выделение ячеек, нажатия на кнопки и т.п. Так что, если вы хотите, чтобы в дальнейшем макрос выполнял действия не над конкретной ячейкой, а над любой выбранной в этот момент ячейкой, то после включения записи макроса не перемещайтесь по листу - сразу начинайте операцию, которую нужно записать.
Пускай, например, это будет изменение формата ячейки. Измените шрифт (гарнитуру, размер, цвет, наклон), потом выберите выравнивание с переносом по словам и т.п.
Затем не забудьте остановить запись. Теперь ваше творение можно увидеть нажав кнопку Макрос в группе Код на вкладке Разработчик (<Alt> + <F8>):
В диалоговом окне появится список всех созданных макросов:
Макросы можно удалять, изменять и выполнять.
Если нажать кнопку Изменить, то появится окно редактора Visual Basic:
Создание макроса для сортировки
Создадим макрос для сортировки по алфавиту списка сотрудников по текстовому полю - Фамилия. Исходная таблица должна выглядеть как список (база данных), т.е. иметь одну строку с заголовками и отделена пустыми строками и столбцами от других данных. Перед началом работы создадим копию листа расчета зарплаты и переименуем новый лист в Создание макросов.
Далее выполняем следующие действия
- Включаем запись макроса (Разработчик → Код → Запись макроса).
- Задаем макросу имя (в имени не должно быть пробелов). Можно оставить предложенные программой имена (Макрос1, Макрос2 и т.д.), но лучше дать более понятное.
- В поле Описание вводим Сортировка по полю Фамилия в алфавитном порядке (описания помогут найти нужный, когда список макросов разрастется).
- Можно назначить макросу комбинацию клавиш быстрого вызова (например <Ctrl> + <a>).
- Сохранить в: Эта книга (оставляем):
- Нажимаем OK. При этом кнопка заменится на кнопку , также в строке состояния Excel появится кнопка Стоп :
Далее выполняем все действия, необходимые при сортировке текстового столбца в алфавитном порядке. Они подробно рассмотрены в разделе Сортировка списков.
- Выделяем любую ячейку внутри таблицы.
- На вкладке Данные в группе Сортировка и фильтр щелкаем
- Останавливаем запись (Разработчик → Код → или кнопка Стоп в строке состояния).
Запуск макроса
- Комбинация клавиш быстрого вызова, если вы ее указывали (например <Ctrl> + <a>).
- Через меню: Разработчик → Код → Макросы (<Alt> + <F8>). Выбираем в списке требуемый макрос и нажимаем кнопку Выполнить:
- Через какой-либо элемент управления, к которому привязан макрос.
Создание кнопки для запуска макроса
Для того, чтобы поместить на рабочий лист Excel стандартную кнопку, необходимо на вкладке Разработчик в группе Элементы управления нажать кнопку Вставить:
В появившемся окне Элементы управления формы выбираем значок Кнопка:
Далее нужно нажать левую кнопку мыши и (не отпуская) обвести свободный прямоугольный участок на текущем листе, а затем выбрать нужный макрос из списка.
Для редактирования созданной кнопки нужно навести курсор на область кнопки, нажать правую кнопку мыши и в контекстном меню выбрать пункт Формат объекта….
Создание счетчика
Например, мы хотим с помощью счетчика изменять значение курса доллара от 25 рублей до 35 рублей с шагом 0,01. Величина шага изменения счетчика может быть только целым положительным числом. Поэтому для возможности изменения копеек поделим значение счетчика на 100, а начальное значение возьмем равным 25. Сначала создадим простую табличку:
Далее (как и при создании стандартной кнопки) вызываем Элементы управления формы, затем выбираем значок Счетчик:
Вставляем счетчик на рабочий лист, изменяем его размеры и положение. Например, подгоняем размеры так, чтобы счётчик точно занимал область D15:D16:
Щелкаем по счетчику правой кнопкой мыши и выбираем в контекстном меню пункт Формат объекта…:
В диалоговом окне Формат элемента управления на закладке Элемент управления устанавливаем требуемые параметры:
Создание раскрывающегося списка
Где-нибудь на рабочем листе формируем список. Например, записываем список фамилий:
Далее (как и при создании других элементов управления) вызываем Элементы управления формы, затем выбираем значок Поле со списком:
Вставляем объект на рабочий лист, изменяем его размеры и положение.
Щелкаем по объекту правой кнопкой мыши и выбираем в контекстном меню пункт Формат объекта….
В диалоговом окне на закладке Элемент управления устанавливаем требуемые параметры:
Результат выбранного элемента списка - это порядковый номер в списке записанный в ячейку K7:
Мы можем использовать это значение для каких-то других действий.
Создание флажка (галочки)
Как и при создании других элементов управления вызываем Элементы управления формы, затем выбираем значок Флажок:
Вставляем объект на рабочий лист, изменяем его размеры и положение.
Теперь заменяем стандартный текст, для этого щелкаем по объекту правой кнопкой мыши и выбираем в контекстном меню пункт Изменить текст:
Затем щелкаем по объекту правой кнопкой мыши и выбираем в контекстном меню пункт Формат объекта….
В диалоговом окне на закладке Элемент управления устанавливаем требуемые параметры:
Если будет указана связь с ячейкой, то в нее будет записываться значение Истина при установленном флажке или Ложь при снятом флажке:
Это значение можно использовать для каких-то других действий.
Создание переключателя
Переключатели создаются аналогично предыдущим элементам управления. Создадим несколько переключателей:
Теперь щелкаем по любому объекту правой кнопкой мыши и выбираем в контекстном меню пункт Формат объекта…, затем выбираем закладку Элемент управления:
Если будет указана связь с ячейкой, то в нее будет записываться значение, соответствующее выбранному переключателю. На каждый переключатель можно назначить отдельный макрос:
Если не использовать группировку, то все переключатели на рабочем листе будут взаимозависимы. При включении одного из них, все остальные будут выключаться.
Для создания независимых групп переключателей используется элемент управления Группа , который вставляется на рабочий лист так же как и все перечисленные выше элементы управления и имеет вид рамки (с надписью или без):
Элемент управления Группа можно использовать также для чисто декоративных целей. Он не связывается ни с какими ячейками, хотя и этому элементу можно назначить макрос.
Фильтрация по точному значению с помощью макросов и элементов управления
Создадим следующую группу элементов управления:
Эти элементы управления должны управлять отображением данных в рассмотренной ранее исходной таблице (выводить строки с конкретной фамилией или же все строки):
1. Сначала создадим макрос для отображения всех строк и привяжем его к переключателю.
- Включаем запись макроса (Разработчик → Код → Запись макроса).
- Задаем макросу имя - Показать_всех.
- В поле Описание вводим Автофильтр - Выделить все по полю Фамилия.
- Сохранить в: Эта книга (оставляем).
- Нажимаем OK. При этом кнопка заменится на кнопку , также в строке состояния Excel появится кнопка Стоп .
Далее выполняем действия, необходимые для вывода всех записей в Автофильтре. Работа с Автофильтром подробно рассмотрена в разделе Фильтрация данных.
- Выделяем любую ячейку внутри таблицы.
- На вкладке Данные в группе Сортировка и фильтр нажимаем большую кнопку Фильтр (появятся кнопочки Автофильтра в заголовках столбцов).
- Щелкаем по кнопке Автофильтра в заголовке Фамилия. В открывшемся меню два раза щелкаем по пункту (Выделить все), затем OK::
- Еще раз щелкаем по большой кнопке Фильтр (чтобы убрать кнопочки Автофильтра в заголовках столбцов).
- Останавливаем запись (Разработчик → Код → или кнопка Стоп в строке состояния).
- Привязываем наш переключатель Показать всех к созданному макросу (нажимаем правую кнопку на переключателе и выбираем пункт Назначить макрос…. В открывшемся списке выбираем Показать_всех).
2. Теперь сделаем так, чтобы поле со списком формировало диапазон условий для Расширенного фильтра.
Создаем в отдельной области список всех фамилий из первого столбца исходной таблицы:
Устанавливаем связь Поля со списком с какой-нибудь ячейкой на рабочем листе (например, L42). Для этого щелкаем по Полю со списком правой кнопкой мыши и выбираем в контекстном меню пункт Формат объекта…, потом выбираем закладку Элемент управления:
В ячейках J42:J43 создадим Диапазон условий для Расширенного фильтра. В первую ячейку запишем название столбца Фамилия, во вторую - формулу, которая выбирает нужную фамилию из списка по номеру в списке:
В режиме значений:
3. Создаем макрос для Расширенного фильтра и привязываем его к переключателю.
- Включаем запись макроса (Разработчик → Код → Запись макроса).
- Задаем макросу имя - Фильтр_по_точному_значению.
- В поле Описание вводим Расширенный фильтр по точному значению в поле Фамилия.
- Сохранить в: Эта книга (оставляем).
- Нажимаем OK. При этом кнопка заменится на кнопку , также в строке состояния Excel появится кнопка Стоп .
Далее выполняем действия, необходимые для фильтрации данных в столбце по точному значению, заданному в диапазоне условий. Такая фильтрация подробно рассмотрена в разделе Расширенный фильтр.
- Выделяем любую ячейку внутри исходной таблицы и вызываем диалоговое окно Расширенного фильтра (на вкладке Данные в группе Сортировка и фильтр нажимаем кнопку Дополнительно:
Оставляем в пункте Обработка - фильтровать список на месте, Исходный диапазон – должен быть уже указан, Диапазон условий: J42:J43, кнопка OK.
- Останавливаем запись (Разработчик → Код → или кнопка Стоп в строке состояния).
- Привязываем наш переключатель Выбрать к созданному макросу (нажимаем правую кнопку на переключателе и выбираем пункт Назначить макрос…. В открывшемся списке выбираем Фильтр_по_точному_значению).
Теперь у нас при выборе того или другого переключателя будут отображаться все строки или только строки с заданной фамилией.
Однако у такого способа управления есть один недостаток. При изменении фамилии в поле со списком режим отображения таблицы будет изменяться ТОЛЬКО ПРИ НАЖАТИИ на переключатель Выбрать.
Было бы неплохо, чтобы при изменении фамилии в поле со списком вид таблицы сразу же менялся. Это можно сделать привязав макрос Фильтр_по_точному_значению еще и к полю со списком (мы можем привязывать один и тот же макрос к разным элементам управления), но тогда вид таблицы при изменении фамилии будет изменяться всегда, даже при выбранном переключателе Показать всех.
Усложнить задачу можно, например, введя проверку переключателей.
Сначала привязываем поле со списком к макросу Фильтр_по_точному_значению.
Затем устанавливаем связь переключателя Выбрать с вспомогательной ячейкой (правая кнопка мыши, Формат объекта…, на закладке Элемент управления, Связь с ячейкой: L44). Теперь, при включенном переключателе Выбрать в ячейке L44) будет единица, при включенном переключателе Показать всех - будет двойка.
Изменяем формулу в Диапазоне условий для Расширенного фильтра на эту =ЕСЛИ(L44=1; ИНДЕКС(H42:H47; L42); ””):
Последний параметр функции ЕСЛИ - пустая строка (две кавычки без промежутка между ними). Теперь при включенном переключателе Показать всех на месте формулы в режиме значений будет пустая ячейка и, следовательно, фильтрации не будет.