Если вы регулярно работаете с электронными таблицами Excel, вы, вероятно, повторяете одни и те же шаги снова и снова. Было бы неплохо щелкнуть кнопку, и эти задачи выполнялись бы автоматически?
Здесь на помощь приходят макросы Excel. Вы можете использовать макросы для автоматизации повторяющихся задач, что может сэкономить вам много времени и усилий.
ОГЛАВЛЕНИЕ
- Что такое макрос Excel?
- Как записать макрос
- Важные примечания о работе с макросами
- Как отредактировать или создать макрос с кодом
Что такое макрос Excel?
Макрос Excel — это записанная последовательность команд и действий Excel, которую вы можете воспроизводить столько раз, сколько захотите. Макросы можно использовать для автоматизации практически любой последовательности задач в Excel, от чего-то столь же простого, как ввод названия и адреса вашей компании в электронную таблицу, до чего-то столь сложного, как создание пользовательского отчета. Если вы можете сделать это в Excel, вы, вероятно, сможете автоматизировать это с помощью макроса.
Чтобы использовать макрос, его сначала нужно записать . Вы выполняете последовательность шагов, которые хотите автоматизировать, а Excel отслеживает их все и сохраняет в макросе. После того, как вы записали макрос, вы можете запустить его снова в любое время. Вы даже можете назначить сочетание клавиш для макроса, чтобы запускать его всего несколькими нажатиями клавиш.
Макросы Excel основаны на языке программирования Microsoft Visual Basic для приложений (VBA). Когда вы записываете макрос, Excel незаметно переводит ваши действия в код VBA. Таким образом, помимо создания макросов путем их записи, вы также можете писать их вручную в коде VBA. В этой статье я сосредоточусь в первую очередь на создании макросов путем их записи — самый простой и быстрый способ. После этого я расскажу, как можно редактировать или написать макросы с нуля с помощью VBA, и предоставлю некоторые ресурсы для самообучения.
Как записать макрос
Чтобы проиллюстрировать этот процесс, я буду использовать небольшой пример набора данных. Предположим, что мы отвечаем за получение имен клиентов и балансов и выполнение двух задач: сначала разделение имени клиента на отдельные имена и фамилии, а затем выделение всех, у кого есть задолженность по балансу больше нуля. В этом примере нам дали семь клиентов для работы.
Чтобы создать макрос, мы будем использовать вкладку «Разработчик» на панели инструментов «Лента» в верхней части окна Excel. Его нет по умолчанию, поэтому нам нужно будет его добавить. Щелкните вкладку « Файл » в крайнем левом углу ленты (выделено на снимке экрана выше), а затем на появившемся экране нажмите « Параметры » в нижней части левого столбца.
Появится экран параметров Excel. Выберите « Настроить ленту» на левой панели навигации. Затем в области «Настройка ленты» справа просмотрите список «Основные вкладки» и установите флажок « Разработчик » . Нажмите ОК .
(В macOS щелкните меню Excel в верхней части экрана и выберите « Настройки» > «Лента и панель инструментов ». В области «Настройка ленты» справа просмотрите список «Основные вкладки» и установите флажок « Разработчик » . Нажмите « Сохранить ». .)
Если у вас есть вкладка «Разработчик», нажмите на нее, и вы увидите параметры, аналогичные показанным на экране ниже.
Чтобы начать запись вашего первого макроса, нажмите кнопку « Запись макроса », и вам будут представлены следующие параметры. Во-первых, придумайте имя для своего макроса, помня, что вы не можете использовать пробелы. Для удобства чтения вы можете разделить слова чем-то вроде _ или — . Затем добавьте сочетание клавиш или описание, если хотите, но это не обязательно.
После того, как вы нажмете OK , значок должен измениться, показывая, что макрос записывает ваши действия. Важно, чтобы вы выполняли только те действия, которые вы хотите, чтобы макрос выполнял, и ничего больше с этого момента, пока вы не нажмете «Остановить запись» .
Теперь, когда макрос записывается, приступим к нашим задачам. Сначала выделите столбец «Сальдо к оплате», затем щелкните правой кнопкой мыши и выберите « Вставить столбец » . Это добавит новый столбец между столбцами «Имя клиента» и «Остаток к оплате».
Далее мы переименуем столбцы, заменив «Имя клиента» на «Имя» и добавив заголовок «Фамилия» в только что созданный столбец. Выберите имена клиентов в первом столбце (ячейки от A: 2 до A: 8), выберите вкладку « Данные » на ленте и выберите команду « Текст в столбцы » .
Появится мастер со следующими параметрами. Выберите «С разделителями» и нажмите « Далее » .
Затем установите флажок « Пробел» , чтобы указать, что слова, разделенные пробелом, должны располагаться в отдельных столбцах. Нажмите «Далее» .
Для последнего варианта оставьте все как есть и нажмите « Готово » .
У вас должен получиться следующий результат с именами и фамилиями в отдельных столбцах.
Наконец, нам нужно выделить каждого клиента с балансом больше нуля. Выделите все данные в третьем столбце (ячейки от C:2 до C:8) и нажмите Главная > Условное форматирование .
Выберите «Правила выделения ячеек», а затем «Больше чем » . Введите 0 и нажмите OK , чтобы выделить каждого клиента, у которого баланс больше нуля.
Это должен быть окончательный результат:
Теперь, когда вы выполнили последовательность задач, вернитесь на вкладку « Разработчик » и нажмите «Остановить запись» . Ваш первый макрос Excel готов.
Важные примечания о работе с макросами
Если вы хотите снова запустить свой макрос, просто нажмите кнопку « Макросы » , и он будет доступен для запуска. Или, если вы назначили макросу ярлык, просто нажмите комбинацию клавиш, чтобы запустить его.
Обратите внимание, что вы не можете сохранить электронную таблицу с макросами как обычную книгу .xlsx. Вы должны сохранить его как книгу Excel с поддержкой макросов (.xlsm), чтобы не потерять макросы.
После внесения этого изменения в любое время, когда вы захотите работать с новым набором данных, вы можете просто снова открыть книгу и импортировать данные, с которыми хотите работать, перейдя на вкладку « Данные » и выбрав «Получить данные » . Вы сможете импортировать данные из файлов, баз данных и других онлайн-сервисов.
Имейте в виду, что во многих случаях макросы отключены по умолчанию. Это связано с тем, что, как отмечает Microsoft, «макросы VBA — это распространенный способ для злоумышленников получить доступ для развертывания вредоносных программ и программ-вымогателей». Чтобы защитить организации от таких угроз, Microsoft теперь блокирует макросы в файлах из Интернета, а иногда и в файлах, хранящихся на общих дисках компании. Ваша организация могла наложить дополнительные ограничения на макросы.
Поэтому всякий раз, когда вы открываете книгу Excel с макросами (включая свои собственные), есть вероятность, что вы увидите предупреждающее сообщение, подобное приведенному ниже. Если ваша рабочая книга создана вами или получена из надежного источника, активируйте ее. Однако, если макрос получен из ненадежного источника, не включайте его, так как это может быть вредоносное ПО.
Другой важной особенностью, о которой следует помнить при записи макросов, является использование относительных ссылок. Эта функция делает так, что независимо от того, где в электронной таблице начинаются данные, макрос сможет найти их и начать обработку там.
Например, созданный нами макрос всегда будет начинать обработку со столбца A, поскольку относительные ссылки не были включены. Однако, если мы проделаем те же операции, но сначала нажмем « Использовать относительные ссылки », тогда макрос сможет определить, где начинается информация (например, столбец C), и начать ее обработку с этой точки. Эта функция полезна, если данные, с которыми вы работаете, не всегда начинаются с одной и той же точки.
Как отредактировать или создать макрос с кодом
Если вы хотите увидеть код VBA, стоящий за макросом, перейдите на вкладку « Разработчик », нажмите « Макрос », выберите макрос и нажмите « Изменить » .
Вы попадете на панель, где сможете увидеть исходный код созданного вами макроса. На снимке экрана ниже подчеркнутые элементы показывают действия, которые мы выполняли, такие как изменение названий заголовков и выделение строк. Вы можете изменить их для различных вариантов использования. Например, если данные, с которыми вы работаете, находятся в диапазоне A1:A20, вы можете расширить диапазон, чтобы включить все возможные ячейки.
Если вы хотите попробовать свои силы в написании макросов с нуля, в Интернете есть несколько ресурсов для обучения написанию сценариев VBA, включая Codewars , Udemy и Codecademy . Вернувшись в Excel, нажмите «Разработчик» > «Макросы» > «Создать» . Вы попадете на пустую панель, где вы можете написать код VBA.