Оглавление

Часть первая
Часть вторая

Макросы и элементы управления

Макрос - это программа (процедура без параметров), написанная на языке VBA (Visual Basic for Applications), которая автоматизирует какую-либо задачу. Это может быть выполнение каких-либо повторяющихся действий пользователя по форматированию внешнего вида ячеек или одинаковых действий над записями таблицы, например сортировка и т.п. По форме макрос – это записанный по определенным правилам текст программы, который хранится в тех же файлах, что и листы рабочей книги, в особых областях, называемых модулями.

Простейший макрос для форматирования

Прежде всего нужно изменить уровень безопасности (иначе работа с макросами будет невозможна): кнопка Office Кнопка OfficeПараметры Excel (внизу) → Центр управления безопасностью (слева) → Параметры центра управления безопасностью (справа):
Настройки
Далее Параметры макросов (слева) → Включить все макросы (справа):
Настройки
Если вкладка Разработчик недоступна, то необходимо выполнить следующие действия: кнопка Office Кнопка OfficeПараметры Excel (внизу) → Основные (слева) → поставить “галочку” в пункте Показывать вкладку “Разработчик” на ленте (справа):
Настройки
Создадим свой первый макрос. Для этого на вкладке Разработчик в группе Код нажимаем кнопку Запись макроса:
Начать
Появится диалоговое окно:
Запись макроса
Нажимаем кнопку OK. При этом начинается запись всех ваши действий. Для прекращения записи служит кнопка Остановить запись, которая появится на месте кнопки Запись макроса:
Остановить
Записываются все перемещения по листу, смена рабочих листов, выделение ячеек, нажатия на кнопки и т.п. Так что, если вы хотите, чтобы в дальнейшем макрос выполнял действия не над конкретной ячейкой, а над любой выбранной в этот момент ячейкой, то после включения записи макроса не перемещайтесь по листу - сразу начинайте операцию, которую нужно записать.
Пускай, например, это будет изменение формата ячейки. Измените шрифт (гарнитуру, размер, цвет, наклон), потом выберите выравнивание с переносом по словам и т.п.
Затем не забудьте остановить запись. Теперь ваше творение можно увидеть нажав кнопку Макрос в группе Код на вкладке Разработчик (<Alt> + <F8>):
Макросы
В диалоговом окне появится список всех созданных макросов:
Макросы
Макросы можно удалять, изменять и выполнять.
Если нажать кнопку Изменить, то появится окно редактора Visual Basic:

Создание макроса для сортировки

Создадим макрос для сортировки по алфавиту списка сотрудников по текстовому полю - Фамилия. Исходная таблица должна выглядеть как список (база данных), т.е. иметь одну строку с заголовками и отделена пустыми строками и столбцами от других данных. Перед началом работы создадим копию листа расчета зарплаты и переименуем новый лист в Создание макросов.
Исходная таблица
Далее выполняем следующие действия

Запуск макроса

  1. Комбинация клавиш быстрого вызова, если вы ее указывали (например <Ctrl> + <a>).
  2. Через меню: РазработчикКодМакросы (<Alt> + <F8>). Выбираем в списке требуемый макрос и нажимаем кнопку Выполнить:
    Запуск макроса
  3. Через какой-либо элемент управления, к которому привязан макрос.

Создание кнопки для запуска макроса

Для того, чтобы поместить на рабочий лист Excel стандартную кнопку, необходимо на вкладке Разработчик в группе Элементы управления нажать кнопку Вставить:
Кнопка Вставить
В появившемся окне Элементы управления формы выбираем значок Кнопка:
Кнопка
Далее нужно нажать левую кнопку мыши и (не отпуская) обвести свободный прямоугольный участок на текущем листе, а затем выбрать нужный макрос из списка.
Для редактирования созданной кнопки нужно навести курсор на область кнопки, нажать правую кнопку мыши и в контекстном меню выбрать пункт Формат объекта….

Создание счетчика

Например, мы хотим с помощью счетчика изменять значение курса доллара от 25 рублей до 35 рублей с шагом 0,01. Величина шага изменения счетчика может быть только целым положительным числом. Поэтому для возможности изменения копеек поделим значение счетчика на 100, а начальное значение возьмем равным 25. Сначала создадим простую табличку:
Курс доллара
Далее (как и при создании стандартной кнопки) вызываем Элементы управления формы, затем выбираем значок Счетчик:
Счетчик
Вставляем счетчик на рабочий лист, изменяем его размеры и положение. Например, подгоняем размеры так, чтобы счётчик точно занимал область D15:D16:
Счетчик
Щелкаем по счетчику правой кнопкой мыши и выбираем в контекстном меню пункт Формат объекта…:
Счетчик
В диалоговом окне Формат элемента управления на закладке Элемент управления устанавливаем требуемые параметры:
Счетчик

Создание раскрывающегося списка

Где-нибудь на рабочем листе формируем список. Например, записываем список фамилий:
Список
Далее (как и при создании других элементов управления) вызываем Элементы управления формы, затем выбираем значок Поле со списком:
Список
Вставляем объект на рабочий лист, изменяем его размеры и положение.
Щелкаем по объекту правой кнопкой мыши и выбираем в контекстном меню пункт Формат объекта….
В диалоговом окне на закладке Элемент управления устанавливаем требуемые параметры:
Список
Результат выбранного элемента списка - это порядковый номер в списке записанный в ячейку K7:
Список
Мы можем использовать это значение для каких-то других действий.

Создание флажка (галочки)

Как и при создании других элементов управления вызываем Элементы управления формы, затем выбираем значок Флажок:
Флажок
Вставляем объект на рабочий лист, изменяем его размеры и положение.
Теперь заменяем стандартный текст, для этого щелкаем по объекту правой кнопкой мыши и выбираем в контекстном меню пункт Изменить текст:
Флажок
Затем щелкаем по объекту правой кнопкой мыши и выбираем в контекстном меню пункт Формат объекта….
В диалоговом окне на закладке Элемент управления устанавливаем требуемые параметры:
Флажок
Если будет указана связь с ячейкой, то в нее будет записываться значение Истина при установленном флажке или Ложь при снятом флажке:
Флажок
Это значение можно использовать для каких-то других действий.

Создание переключателя

Переключатели Переключатель создаются аналогично предыдущим элементам управления. Создадим несколько переключателей:
Переключатели
Теперь щелкаем по любому объекту правой кнопкой мыши и выбираем в контекстном меню пункт Формат объекта…, затем выбираем закладку Элемент управления:
Переключатели
Если будет указана связь с ячейкой, то в нее будет записываться значение, соответствующее выбранному переключателю. На каждый переключатель можно назначить отдельный макрос:
Переключатели
Если не использовать группировку, то все переключатели на рабочем листе будут взаимозависимы. При включении одного из них, все остальные будут выключаться.
Для создания независимых групп переключателей используется элемент управления Группа Группа, который вставляется на рабочий лист так же как и все перечисленные выше элементы управления и имеет вид рамки (с надписью или без):
Переключатели
Элемент управления Группа можно использовать также для чисто декоративных целей. Он не связывается ни с какими ячейками, хотя и этому элементу можно назначить макрос.

Фильтрация по точному значению с помощью макросов и элементов управления

Создадим следующую группу элементов управления:
Фильтрация по точному значению
Эти элементы управления должны управлять отображением данных в рассмотренной ранее исходной таблице (выводить строки с конкретной фамилией или же все строки):
Исходная таблица

1. Сначала создадим макрос для отображения всех строк и привяжем его к переключателю.

2. Теперь сделаем так, чтобы поле со списком формировало диапазон условий для Расширенного фильтра.

Создаем в отдельной области список всех фамилий из первого столбца исходной таблицы:
Список фамилий
Устанавливаем связь Поля со списком с какой-нибудь ячейкой на рабочем листе (например, L42). Для этого щелкаем по Полю со списком правой кнопкой мыши и выбираем в контекстном меню пункт Формат объекта…, потом выбираем закладку Элемент управления:
Список
В ячейках J42:J43 создадим Диапазон условий для Расширенного фильтра. В первую ячейку запишем название столбца Фамилия, во вторую - формулу, которая выбирает нужную фамилию из списка по номеру в списке:
Список
В режиме значений:
Список

3. Создаем макрос для Расширенного фильтра и привязываем его к переключателю.

Теперь у нас при выборе того или другого переключателя будут отображаться все строки или только строки с заданной фамилией.
Однако у такого способа управления есть один недостаток. При изменении фамилии в поле со списком режим отображения таблицы будет изменяться ТОЛЬКО ПРИ НАЖАТИИ на переключатель Выбрать.
Было бы неплохо, чтобы при изменении фамилии в поле со списком вид таблицы сразу же менялся. Это можно сделать привязав макрос Фильтр_по_точному_значению еще и к полю со списком (мы можем привязывать один и тот же макрос к разным элементам управления), но тогда вид таблицы при изменении фамилии будет изменяться всегда, даже при выбранном переключателе Показать всех.
Усложнить задачу можно, например, введя проверку переключателей.
Сначала привязываем поле со списком к макросу Фильтр_по_точному_значению. Затем устанавливаем связь переключателя Выбрать с вспомогательной ячейкой (правая кнопка мыши, Формат объекта…, на закладке Элемент управления, Связь с ячейкой: L44). Теперь, при включенном переключателе Выбрать в ячейке L44) будет единица, при включенном переключателе Показать всех - будет двойка.
Изменяем формулу в Диапазоне условий для Расширенного фильтра на эту =ЕСЛИ(L44=1; ИНДЕКС(H42:H47; L42); ””):
Расширенный фильтр
Последний параметр функции ЕСЛИ - пустая строка (две кавычки без промежутка между ними). Теперь при включенном переключателе Показать всех на месте формулы в режиме значений будет пустая ячейка и, следовательно, фильтрации не будет.
Расширенный фильтр