Как Сделать Чтобы Макрос в Excel Выполнялся Автоматически При Изменении Значений Ячеек

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

Прежде всего нужно изменить уровень безопасности (иначе работа с макросами будет невозможна): кнопка Office → Параметры Excel (внизу) → Центр управления безопасностью (слева) → Параметры центра управления безопасностью (справа):

Если вкладка Разработчик недоступна, то необходимо выполнить следующие действия: кнопка Office → Параметры Excel (внизу) → Основные (слева) → поставить “галочку” в пункте Показывать вкладку “Разработчик” на ленте (справа):

Создадим свой первый макрос. Для этого на вкладке Разработчик в группе Код нажимаем кнопку Запись макроса:

Нажимаем кнопку OK. При этом начинается запись всех ваши действий. Для прекращения записи служит кнопка Остановить запись, которая появится на месте кнопки Запись макроса:

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

Пускай, например, это будет изменение формата ячейки. Измените шрифт (гарнитуру, размер, цвет, наклон), потом выберите выравнивание с переносом по словам и т.п.

Затем не забудьте остановить запись. Теперь ваше творение можно увидеть нажав кнопку Макрос в группе Код на вкладке Разработчик ( + ):

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

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

Далее выполняем следующие действия

• Включаем запись макроса (Разработчик → Код → Запись макроса).
• Задаем макросу имя (в имени не должно быть пробелов). Можно оставить предложенные программой имена (Макрос1, Макрос2 и т.д.), но лучше дать более понятное.
• В поле Описание вводим Сортировка по полю Фамилия в алфавитном порядке (описания помогут найти нужный, когда список макросов разрастется).
• Можно назначить макросу комбинацию клавиш быстрого вызова (например + ).
• Сохранить в: Эта книга (оставляем):

Далее выполняем все действия, необходимые при сортировке текстового столбца в алфавитном порядке. Они подробно рассмотрены в разделе Сортировка списков.

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

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

Для того, чтобы поместить на рабочий лист Excel стандартную кнопку, необходимо на вкладке Разработчик в группе Элементы управления нажать кнопку Вставить:

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

Для редактирования созданной кнопки нужно навести курсор на область кнопки, нажать правую кнопку мыши и в контекстном меню выбрать пункт Формат объекта….

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

Например, мы хотим с помощью счетчика изменять значение курса доллара от 25 рублей до 35 рублей с шагом 0,01. Величина шага изменения счетчика может быть только целым положительным числом. Поэтому для возможности изменения копеек поделим значение счетчика на 100, а начальное значение возьмем равным 25. Сначала создадим простую табличку:

Далее (как и при создании стандартной кнопки) вызываем Элементы управления формы, затем выбираем значок Счетчик:

Вставляем счетчик на рабочий лист, изменяем его размеры и положение. Например, подгоняем размеры так, чтобы счётчик точно занимал область D15:D16:

В диалоговом окне Формат элемента управления на закладке Элемент управления устанавливаем требуемые параметры:

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

Далее (как и при создании других элементов управления) вызываем Элементы управления формы, затем выбираем значок Поле со списком:

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

Как и при создании других элементов управления вызываем Элементы управления формы, затем выбираем значок Флажок:

Теперь заменяем стандартный текст, для этого щелкаем по объекту правой кнопкой мыши и выбираем в контекстном меню пункт Изменить текст:

Если будет указана связь с ячейкой, то в нее будет записываться значение Истина при установленном флажке или Ложь при снятом флажке:

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

Переключатели создаются аналогично предыдущим элементам управления. Создадим несколько переключателей:

Теперь щелкаем по любому объекту правой кнопкой мыши и выбираем в контекстном меню пункт Формат объекта…, затем выбираем закладку Элемент управления:

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

Если не использовать группировку, то все переключатели на рабочем листе будут взаимозависимы. При включении одного из них, все остальные будут выключаться.

Для создания независимых групп переключателей используется элемент управления Группа , который вставляется на рабочий лист так же как и все перечисленные выше элементы управления и имеет вид рамки (с надписью или без):

Элемент управления Группа можно использовать также для чисто декоративных целей. Он не связывается ни с какими ячейками, хотя и этому элементу можно назначить макрос.

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

Эти элементы управления должны управлять отображением данных в рассмотренной ранее исходной таблице (выводить строки с конкретной фамилией или же все строки):

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

Далее выполняем действия, необходимые для вывода всех записей в Автофильтре. Работа с Автофильтром подробно рассмотрена в разделе Фильтрация данных.

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

В ячейках J42:J43 создадим Диапазон условий для Расширенного фильтра. В первую ячейку запишем название столбца Фамилия, во вторую — формулу, которая выбирает нужную фамилию из списка по номеру в списке:

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

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

Оставляем в пункте Обработка — фильтровать список на месте, Исходный диапазон – должен быть уже указан, Диапазон условий: J42:J43, кнопка OK.

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

Однако у такого способа управления есть один недостаток. При изменении фамилии в поле со списком режим отображения таблицы будет изменяться ТОЛЬКО ПРИ НАЖАТИИ на переключатель Выбрать.

Изменяем формулу в Диапазоне условий для Расширенного фильтра на эту =ЕСЛИ(L44=1; ИНДЕКС(H42:H47; L42); ””):

Последний параметр функции ЕСЛИ — пустая строка (две кавычки без промежутка между ними). Теперь при включенном переключателе Показать всех на месте формулы в режиме значений будет пустая ячейка и, следовательно, фильтрации не будет.

[expert_bq id=»1570″]Если вы работаете в MS Excel 2007, 2010 или 2013, вам необходимо будет сохранить вашу рабочую книгу как Книга Excel с поддержкой макросов. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Макросы значительно сокращают время, необходимое на выполнение однотипных заданий. Именно это объясняет такую высокую заинтересованность в их использовании со стороны большинства пользователей. Однако при первом запуске Excel, запустить макрос не удастся, поскольку по умолчанию функция воспроизведения макрокоманд отключена.

Как обновить макросы в excel

• автоматизацию скучной, повторяющейся работы, что может сэкономить время для более значимой работы
• применение последовательных корректировок к данным, чтобы изменения всегда были одинаковыми
• уменьшение ошибок, связанных с ручным вводом, так как шаги будут выполняться автоматически

Желая повысить эффективность своей работы, многие стараются изучить информацию, как писать макросы в Excel, однако специалисты настоятельно рекомендуют первоначально разобраться, как включить макросы в Excel.