Макросы в Excel

Макросы в Excel

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

Приложение Excel умеет записывать все действия пользователя с помощью макрорекодера, создавая специальную программу на языке программирования Visual Basic for Application (VBA), которую в последующем можно запустить для исполнения. При этом Вам не нужно быть программистом или иметь специальное образование.

Как записать макрос в Excel?

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

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

RPA и VBA: 3 примера использования

Давайте представим 3 бизнес-процесса, которые иллюстрируют идеальный сценарий использования RPA, VBA и обеих технологий вместе.

1. VBA: сортировка, фильтрация и упорядочивание данных в Excel

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

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

2. RPA: обработка данных из различных источников

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

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

3. RPA + VBA

В приведённом выше примере, где все действия выполняют программные роботы RPA, мы можем использовать макросы на тех участках цепочки, где задействован Excel. Тогда процесс будет выглядеть так:

Программный робот собирает данные из ERP, помещает их в электронную таблицу Excel, затем данные обрабатываются с помощью макросов. Заключительные этапы (отправка и уведомление) снова будут выполнены программным роботом. Эти два сценария отличаются не сильно, но следует отметить, что функции макросов будут запущены с помощью роботов, а не вручную. Это означает, что процесс всё ещё является программной автоматизацией, хотя в нём частично используется VBA.

Проблема:

Боюсь, вы путаете vba с google-apps-script ( GAS ). Google Таблицы и Excel — две совершенно разные платформы, которые могут выглядеть одинаково, но сильно отличаются.

К сожалению, вы не можете использовать свои макросы Excel в GAS . Если вы хотите использовать макросы в файле электронной таблицы, вам необходимо создать сценарий GAS . Создайте новый файл электронной таблицы, скопируйте данные из файла Excel в этот файл электронной таблицы, а затем перейдите к Tools = > Редактор сценариев для создания сценария GAS , который выполняет то же действие, что и ваш VBA .

Фильтры данных (информации из баз данных Excel)

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

• суммирование данных по 3-5 конкретным городам – резонно использовать положительный фильтр;
• суммирование данных по всем городам, кроме Москвы и Санкт-Петербурга – здесь удобнее отрицательный фильтр;

Функция формирования фильтров для сводной таблицы (по массиву эталонных строковых значений) написана и есть в коде…

Зачастую VBA макросы Excel разрастаются до сотен строк кода, очень неудобных в работе. XLTools «Автоматизация» позволяет писать команды в простых и компактных таблицах Excel. Табличное представление более информативно, наглядно и проще для редактирования. Вы также можете добавить собственные кнопки на панель инструментов Excel, привязав их к своим командам автоматизации.

«Автоматизация» – это универсальный инструмент для автоматизации практически любых команд и их последовательностей:

Команды автоматизации в таблице Excel создаются по следующему принципу:

XLTools.SQLSelect – в точности напечатайте название команды; поместите в объединённую ячейку.

Мы подготовили пособие с примерами, синтаксисом и построчными комментариями.

Просто напишите команду, используя пособие Нажмите Выполнить команды Готово!

10) Защита электронных таблиц с логином и паролем

Если вы прочитали это далеко, нет ничего более справедливого, чем удивление еще одним пунктом в этом списке. И это макрос, к которому вы можете получить доступ прямо сейчас. Многим людям трудно ограничить данные в электронной таблице, когда они делятся ими с партнерами, сотрудниками и т. Д.

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

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

А вы уже используете какой-нибудь макрос в повседневной жизни? Поделитесь с нами своим опытом, оставив комментарий или связавшись!