Как создать макрос в Excel

Представьте, что вы каждый день выполняете в Excel-таблицах рутинные задачи: копирование и вставка данных, форматирование, создание диаграмм. Это утомительно, но на помощь приходит макрос. В статье рассказываем, как правильно сделать макрос в Excel, чтобы автоматизировать как простые, так и сложные операции.

Макрос — это набор команд в Excel, который выполняет определенные действия автоматически. Это словно мини-программа, создаваемая один раз, а затем используемая по мере необходимости. В бизнесе это полезно и удобно при работе с корпоративными файлами, где требуется много повторяющихся действий.

Опишем вкратце основные принципы работы макроса:

1. Вы записываете макрос, показывая Excel, как нужно выполнять действия.
2. Excel запоминает ваши действия и записывает их в виде кода.
3. Вы сохраняете макрос для дальнейшего использования.
4. В любой момент вы можете запустить макрос одним кликом, и он выполнит все действия автоматически.

Создание инструкций и команд макросов в Excel производится на языке VBA (Visual Basic for Applications).

Вот для чего нужны макросы в Excel:

• Экономия времени и усилий: оптимизация операционной рутины освобождает время для более важных задач.
• Повышение точности: макросы выполняются без ошибок.
• Упрощение сложных задач: макросы обеспечивают автоматизацию сложных действий.
• Повышение производительности: быстрое выполнение задач повышает общую производительность.

Есть бизнес-процессы, в которых аналитика и вычисления требуют не Excel-таблиц, а более узкоспециализированных программ. Если данные по продажам из CRM передавать в сервис сквозной аналитики, можно анализировать реальную эффективность рекламы в разрезе «от клика до продажи».

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

Оптимизация повторяющихся процедур

Если вам нужно повторять одни и те же действия c данными в таблицах Excel, макрос сделает это за вас. Ниже приводим примеры таких действий:

• Копирование и вставка данных: макрос может автоматически заполнять документ, копируя данные из одной ячейки в другую, из одного листа в другой или даже из одного файла Excel в другой.
• Форматирование ячеек: макрос может изменить шрифт, цвет, размер и другие параметры форматирования ячеек, что сэкономит вам много времени при работе с большими таблицами. Можно, например, отформатировать один документ под корпоративный дизайн, а затем автоматически применять такое оформление во всех других корпоративных документах.
• Сортировка и фильтрация: макрос может выполнять сортировку и фильтрацию данных по любому критерию, что поможет вам быстро найти нужную информацию.

Объединение работы нескольких программ Microsoft Office

Макрос может «общаться» с другими программами Microsoft Office, такими как Word, Power Point и Access, благодаря чему становятся возможны:

• Создание документов Word из данных Excel: макрос может формировать новый документ Word и заполнять его данными из Excel в нужном формате.
• Добавление данных из Excel в презентации Power Point: можно добавить диаграммы и таблицы из Excel в презентацию Power Point.
• Формирование отчетов Access из данных Excel: есть возможность формирования новой таблицы в Access и заполнения ее данными из таблиц Excel.

Поиск ячеек с данными и перенос в другие файлы

С помощью макросов можно:

• просканировать все ячейки в таблице и выбрать те, которые содержат определенный текст;
• скопировать найденные ячейки в другой лист или в другой файл Excel.

Формирование шаблонов

Макрос может формировать шаблоны для ввода данных, что сделает процесс ввода более организованным и удобным, например:

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

Создание новых функций Excel

Вот еще для чего нужны макросы в Excel — они могут создавать новые функции, которые будут выполнять определенные действия с данными, например:

• функцию, которая будет вычислять среднее значение выбранного диапазона ячеек;
• функцию, которая будет преобразовывать единицы измерения (например, километры в метры).

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

• Бухгалтеры и финансисты: роботизация операционной рутины — составления отчетов, проверки данных, обновления таблиц — позволит сэкономить время и снизить риск ошибок.
• Менеджеры и аналитики: применение макросов в целях обработки больших объемов данных, составления графиков и диаграмм позволит анализировать информацию быстрее и эффективнее.
• Маркетологи и специалисты по продажам: обработка заказов, составление отчетов позволит увеличить производительность.
• IT-специалисты: с помощью макросов можно разрабатывать сложные логические алгоритмы, функции.
• Студенты и преподаватели: макрос для электронных таблиц Excel позволяет автоматизировать вычисления, создавать тесты, обрабатывать результаты исследований, делая работу с данными более простой и наглядной.
• Все, кто хочет сделать работу в Эксель-таблицах более эффективной: независимо от вашей профессии, макросы позволяют автоматизировать повторяющиеся задачи, сэкономить время и повысить продуктивность работы.

Макрорекордер — инструмент, который позволяет создать работающий макрос в Excel. Он преобразует в макрос заданную последовательность. Этот инструмент дает новичкам легко создаваемые простые макросы, однако при более сложных задачах требуется изучение VBA и ручная настройка макросов.

Перечислим примеры задач, которые нельзя реализовать посредством одного макрорекордера:

• Сформировать макрос с логикой и условиями. Макрорекордер записывает только конкретные действия, но не может принимать решения или выполнять логические действия в зависимости от условий.
• Обработать данные из внешних источников. Макрорекордер работает только с данными, которые уже есть в Эксель. Он не может импортировать данные из файлов, баз данных или других приложений.
• Сконструировать собственные функции. Макрорекордер может записать использование существующих функций Эксель, но не может создавать новые.
• Разработать сложные интерфейсы. Макрорекордер не позволяет создавать формы, диалоговые окна или другие элементы управления.
• Взаимодействовать с другими приложениями. Макрорекордер не может управлять другими программами, например, Word или PowerPoint.
• Сделать новые макросы в процессе записи. Когда записывается макрос, макрорекордер не может одновременно формировать новые макросы или вставлять уже существующие.
• Фиксировать и обрабатывать ошибки. Макрорекордером не фиксируются ошибки, а также исключительные ситуации. Если в ходе реализации макроса произойдет ошибка, макрос остановится, при этом пользователь не получит сведений об ошибке.
• Рисовать и выполнять действия с объектами. Посредством макрорекордера нельзя писать операции, которые затрагивают создание и изменение форм, начертание фигур или добавление картинок.
• Работать с защищенными листами и рабочими книгами. Если лист или книга закрыты паролем, они становятся недоступны для операций макрорекордера.

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

По умолчанию функциональность макросов в электронной таблице отключена — этого требует политика безопасности. Так что сначала потребуется включить их в настройках:

• Перейти в «Файл», далее в «Параметры».
• Выбрать раздел «Центр управления безопасностью», далее «Настройки центра управления безопасностью», в разделе «Параметры макросов» выбрать нужный уровень безопасности.

Есть следующие варианты разрешения работы с макросами:

• «Отключить все макросы без уведомления»: макросы не будут выполняться.
• «Включить все макросы»: все макросы будут выполняться без предупреждения.
• «Включить все макросы с уведомлением»: Эксель будет спрашивать, хотите ли вы выполнить макрос перед тем, как вы его запустите.
• «Отключить все макросы за исключением подписанных макросов»: будут выполняться только макросы, подписанные доверенным издателем.

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

Создать макрос в программе Excel можно одним из двух способов — записью кода с нуля и автоматической записью. Ниже по очереди описываем их.

Запись кода макроса с нуля

В данном случае пользователь выполняет запись кода на VBA и пользуется редактором VBA. Чтобы сформировать макрос таким путем, нужно:

1. Открыть в Excel редактор VBA (сочетание клавиш Alt + F11) и выбрать вкладку «Разработчик», после этого необходимо нажать на «Редактор Visual Basic».
2. Образовать новый модуль: для этого нужно в меню «Вставка» выбрать «Модуль».
3. Написать код: в открывшемся окне начните запись кода VBA. В код могут входить инструкции, означающие разные операции в программе — работа с данными, форматирование, подсчеты и т.д.
4. Сохранить макрос: модуль с получившимся макросом будет храниться в книге электронной таблицы.

Приведем для примера основные элементы кода:

• Sub (подпрограмма): каждая подпрограмма — это отдельный блок кода, который выполняет конкретную задачу.
• End Sub: означает конец подпрограммы.
• Переменные: используются для хранения данных (например, значения ячеек).
• Операторы: выполняют конкретные действия (например, копирование, вставку, форматирование).

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

Автоматическая запись макроса в программе Excel

Данный способ сделать макросы в Экселе позволяет быстро вносить операции и превращать их в макрос.

Какие действия нужно выполнить:

1. Включить запись макроса: сначала потребуется открыть вкладку «Разработчик» в электронной таблице (если она не показывается по умолчанию, нужно ее найти в «Параметрах Excel»). Далее вам будет нужно в этой вкладке нажать на «Записать макрос» — после этого в Excel откроется диалоговое окно, в котором можно указать наименование макроса и зафиксировать место его сохранения.
2. Начать выполнять желаемые действия в Excel: например, скопируйте данные, отформатируйте ячейки, создайте диаграмму.
3. Остановить запись макроса: нажмите «Остановить запись» во вкладке «Разработчик».

Электронная таблица Эксель автоматически создаст макрос на основе ваших действий, записав код VBA, и сохранит его.

У автоматической записи есть следующие преимущества:

• Упрощает формирование простых макросов: с таким способом записи вам не нужно знать VBA, Эксель делает все за вас.
• Экономит время: вы не тратите время на то, чтобы вручную записать код.
• Позволяет быстро сформировать прототип макроса: позже его можно доработать вручную в редакторе VBA.

При этом важно иметь в виду, что автоматическая запись:

• Не позволяет создавать макросы с логикой и условиями.
• Может записать только конкретные действия, без гибкости и настройки.
• Не подходит для сложных задач.

После того, как вы записали макрос, его можно запускать. Для этого нужно выполнить следующие шаги:

• Перейти в «Разработчик», далее нажать на «Макросы» (или «Просмотреть макросы»).
• В открывшемся окне выбрать тот макрос, который нужно запустить.
• Кликнуть на «Выполнить» или «Запуск».

Иногда для достижения нужного эффекта от программы необходимо доработать и отредактировать код, который вы записали — для этого нужно:

1. Открыть редактор VBA.
2. Найти макрос, для которого требуется редактура. В левой части редактора VBA есть обозреватель Project Explorer.
3. Раскрыть ветку «Эта книга» или иной объект, где находится макрос, который вы записали. Вам будут показаны все модули, в том числе тот, где находится макрос.
4. Кликнуть два раза на модуль с макросом, и он отобразится в редакторе.
5. Произвести нужные корректировки в коде вашего макроса. Есть возможность менять, удалять и вносить новые операции.
6. Сохранить изменения — это делается либо с помощью сочетания клавиш Ctrl + S, либо нажатием на «Сохранить» в разделе «Файл» редактора VBA.
7. Закрыть VBA — для этого нужно либо нажать Ctrl + Q либо кликнуть на «Файл», а затем на «Закрыть и вернуться в Microsoft Excel» (в некоторых версиях программы).

Редактировать макросы нужно предельно внимательно, чтобы не допустить ошибочных корректировок, которые сделают код нерабочим. На всякий случай проверяйте, как срабатывает код, после каждого внесения корректировок.