Автоматизація Excel - Автоматизація завдань за допомогою написання або запису макросів

Автоматизація Excel (Зміст)

Вступ до автоматизації в Excel
Приклади автоматизації Excel

Вступ до автоматизації в Excel

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

Деякі надбудови автоматизації, такі як XLTools, AutoMacro використовуються для автоматизації власних рутинних завдань Excel без макросів.

Приклади автоматизації Excel

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

Ви можете завантажити цей шаблон для автоматизації Excel тут - Шаблон автоматизації Excel

Приклад №1 - Автоматизація за допомогою макрозапису

Скажімо, у нас є набір даних про деякі цифри, і ми хочемо мати рядкові підсумкові статистичні дані (такі як сума, середній, мінімальний, максимальний) для них. Також ми хочемо застосувати деякий стиль форматування до набору даних. Таким чином, ми записуємо ці кроки в макрос, так що коли у нас є новий набір даних, який вимагає виконання тих же операцій над ним, тоді цей записаний макрос можна просто запустити для виконання цього завдання.

Подивимося, як виглядає набір даних:

Тепер, щоб зробити необхідні операції та записати це в макрос, ми будемо виконувати наведений нижче процес:

Перейдіть до "Developer" і натисніть "Record Macro":

При цьому відкривається спливаюче вікно наступним чином.

Дайте макросу відповідне ім’я та виберіть "Особиста робоча книга макросу" у спадному меню "Зберегти макрос в" та натисніть кнопку ОК.

Тепер ми можемо виконати необхідні операції над набором даних наступним чином.

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

Застосовуючи середню формулу в клітині Н2, результат показаний нижче.

Застосовуючи формулу MIN у комірці I2, результат показаний нижче.

Застосувавши формулу MAX у комірці J2, результат показаний нижче.

Тепер перетягніть їх, щоб отримати цю статистику для всіх рядків.

Тепер, коли всі ці операції виконуються, ми припиняємо запис макросу, як показано нижче.

Примітка. Доцільно використовувати клавіші клавіатури під час запису макросу. Мишу слід використовувати лише за потреби.

Приклад №2 - Використання записаних макросів

Скажімо, у нас є ще один подібний набір даних, як цей у "Приклад №2" файлу Excel, який вимагає тих же операцій та форматування. Тому для цього ми просто запустимо вищезаписаний макрос, і наше завдання буде виконано.

Перейдіть до розділу "Розробник" та натисніть "Макроси".

Тепер виберіть макрос: "Marks_Macro1" і натисніть кнопку "Виконати".

Тепер при натисканні Виконати, набір даних буде наступним.

Отже, на наведеному вище скріншоті ми бачимо, що при запуску записаного макросу, який називається "Marks_macro1", ми автоматизувались і, отже, повторили операції, виконані на Sheet1 у Example1, до Sheet2. Це автоматизація за допомогою запису макросу.

Цей вид автоматизації може використовуватися для простих повторюваних операцій, таких як щоденне форматування звіту, спілкування з базами даних, об'єднання файлів, створення нових документів, маніпулювання даними тощо.

Як макроси зберігаються як процедури коду VBA у VBE

Доступ до редактора Visual Basic потрібно отримати, натиснувши Alt + F11, щоб відкрити вікно редактора Visual Basic.

Після цього відкриється вікно. Тепер під панеллю «Project-VBAProject» ми будемо зберігати макрос в одному з модулів наступним чином.

За допомогою VBA Macro Recorder нам не потрібно кодувати макрос, а просто записувати його.
Макроси можна зберігати в особистій робочій книзі, що є прихованою робочою книжкою, яка відкривається у фоновому режимі щоразу, коли запускається Excel. Збереження макросів у Особистій робочій книжці робить макрос завжди доступним, оскільки Персональна книга не є системою або файлом.
Під час запису макросів Excel завжди виробляє під-процедуру (не функціональну процедуру). Отже, записані коди корисні лише для простих макросів. Більш складні макроси можна створити, записавши коди VBA, включивши меню «Developer» у Excel.
Запис макросів для автоматизації завдань може мати деякі обмеження. Не завжди можливо записувати макроси, які працюють саме так, як ми бажаємо. Макрокод часто вимагає деяких оновлень вручну, тому в цьому випадку можна використовувати AutoMacro, який є надбудовою, що безпосередньо встановлюється у VBE. Це вимагає дуже мало знань щодо кодування VBA.

Що слід пам’ятати про автоматизацію Excel

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