Автоматизация Excel (Оглавление)

  • Введение в автоматизацию в Excel
  • Примеры автоматизации Excel

Введение в автоматизацию в Excel

Автоматизация в Excel обычно включает в себя кодирование в VBA (Visual Basic для приложений), который является вариацией языка Visual Basic для интеграции с приложениями Microsoft Office. Это язык программирования на основе Excel, который помогает пользователю автоматизировать задачи путем записи или записи макросов. Макрос - это код, который выполняется в среде Excel и используется для автоматизации повторяющихся задач, т. Е. Если набор действий должен повторяться несколько раз, то Excel может записать эти действия и сгенерировать макрос, содержащий код для повторения этих шагов. После записи макроса набор действий можно повторить любое количество раз, запустив записанный макрос. Код VBA, созданный макросом, помещается в модуль, который можно просматривать через VBE.

Некоторые надстройки автоматизации, такие как XLTools, AutoMacro, используются для автоматизации собственных рутинных задач Excel без макросов.

Примеры автоматизации Excel

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

Вы можете скачать этот шаблон Excel для автоматизации здесь - Шаблон Excel для автоматизации

Пример № 1 - Автоматизация с помощью записи макросов

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

Давайте посмотрим, как выглядит набор данных:

Теперь, чтобы выполнить необходимые операции и записать его в макрос, мы будем следовать следующему процессу:

  • Перейдите в «Разработчик» и нажмите «Записать макрос»:

  • После этого открывается следующее всплывающее окно.

  • Дайте макросу подходящее имя и выберите «Персональная книга макросов» в раскрывающемся меню «Сохранить макрос в», а затем нажмите «ОК».

  • Теперь мы можем выполнить необходимые операции над набором данных следующим образом.

  • После использования формулы результат показан ниже.

  • Применяя среднюю формулу в ячейке H2, результат показан ниже.

  • Применение формулы MIN в ячейке I2, результат показан ниже.

  • Применяя формулу MAX в ячейке J2, результат показан ниже.

  • Теперь перетащите их, чтобы получить статистику для всех строк.

  • Теперь, когда все эти операции выполнены, мы прекращаем запись макроса, как показано ниже.

Примечание. Рекомендуется использовать клавиши клавиатуры во время записи макроса. Мышь следует использовать только при необходимости.

Пример № 2 - Использование записанных макросов

Теперь предположим, что у нас есть другой подобный набор данных, подобный этому, в «Примере № 2» файла Excel, который требует тех же операций и форматирования. Поэтому для этого мы просто запустим записанный выше макрос, и наша задача будет выполнена.

  • Перейдите в «Разработчик» и нажмите «Макросы».

  • Теперь выберите макрос: «Marks_Macro1» и нажмите «Выполнить».

  • Теперь при нажатии Run набор данных будет выглядеть следующим образом.

Таким образом, на приведенном выше снимке экрана мы можем видеть, что при запуске записанного макроса с именем «Marks_macro1» мы автоматизировали и, следовательно, реплицировали операции, выполненные на Листе 1 в Примере1, на Лист2. Это автоматизация посредством записи макроса.

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

Как макросы сохраняются как процедуры кода VBA в VBE

  • Чтобы открыть редактор Visual Basic, нажмите Alt + F11, чтобы открыть окно редактора Visual Basic.

  • При этом открывается окно. Теперь под панелью «Project-VBAProject» мы будем хранить макрос в одном из модулей следующим образом.

  • С VBA Macro Recorder нам не нужно кодировать макрос, а просто записывать его.
  • Макросы могут храниться в личной рабочей книге, которая является скрытой книгой, которая открывается в фоновом режиме при каждом запуске Excel. Сохранение макросов в Персональной книге делает макрос всегда доступным, поскольку Персональная книга не зависит от системы или файла.
  • При записи макросов Excel всегда создает подпроцедуру (не функциональную процедуру). Таким образом, записанные коды полезны только для простых макросов. Более сложные макросы можно создавать, написав коды VBA, включив меню «Разработчик» в Excel.
  • Запись макросов для автоматизации задач может иметь некоторые ограничения. Не всегда возможно записать макросы, которые работают именно так, как мы хотим. Код макроса часто требует некоторых обновлений вручную, поэтому в этом случае можно использовать AutoMacro, который является надстройкой, устанавливаемой непосредственно в VBE. Это требует очень мало знаний о кодировании VBA.

Что нужно помнить об автоматизации Excel

  • Некоторыми инструментами, которые используются для автоматизации Excel без каких-либо знаний в области программирования, являются Power Query, VBA Macro Recorder и некоторые надстройки Automation, такие как Auto Macro, XLTools. XLTools - это надстройка для автоматизации, которая может даже интегрировать Excel с другими приложениями и инструментами, такими как SQL, Python. Его можно использовать для написания команд в простых таблицах Excel.
  • Макросы можно даже добавить в меню функций Excel с помощью кнопки, чтобы они были доступны пользователю так же, как встроенные функции Excel.
  • Запись макроса полезна для выполнения простых повторяющихся задач, а также когда мы пишем сложные макрокоды, редактируя записанные коды.

Рекомендуемые статьи

Это руководство по автоматизации Excel. Здесь мы обсуждаем, как автоматизировать задачи путем написания или записи макросов, а также с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -

  1. Поле имени в Excel
  2. Расчеты в Excel
  3. Шаблон дорожной карты в Excel
  4. Соответствующие столбцы в Excel
  5. Примеры макросов Excel

Категория:

Советы по Excel