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

Однією з найбільш потужних, але рідко використовуваних функцій Excel є можливість дуже легко створювати автоматизовані завдання і власну логіку в макросах. Макроси забезпечують ідеальний спосіб заощадити час на передбачуваних, повторюваних завданнях, а також стандартизувати формати документів - багато разів без необхідності писати один рядок коду.

Якщо вам цікаво, які макроси є або як насправді їх створити, без проблем - ми проведемо вас через весь процес.

Примітка: той самий процес повинен працювати в більшості версій Microsoft Office. Скріншоти можуть виглядати дещо іншим.

Що таке макрос?

Макрос Microsoft Office (оскільки ця функціональність застосовується до декількох додатків MS Office) - це просто код Visual Basic для додатків (VBA), збережений у документі. Для порівнянної аналогії розглянемо документ як HTML і макрос як Javascript. Так само, як Javascript може керувати HTML на веб-сторінці, макрос може маніпулювати документом.

Макроси неймовірно потужні і можуть зробити все, що може уявити ваша уява. Як (дуже) короткий список функцій, які можна робити з макросом:

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

Створення макросу: пояснення прикладом

Ми починаємо з вашого CSV-файлу саду. Нічого особливого тут, тільки 10 × 20 набір номерів між 0 і 100 з обох рядків і заголовків стовпців. Нашою метою є створення добре відформатованого, презентабельного паспорта, який містить підсумкові підсумки для кожного рядка.

Як ми вже говорили вище, макрос - це код VBA, але одна з приємних речей Excel - це створити / записати їх з нульовим кодуванням - як ми будемо робити тут.

Щоб створити макрос, перейдіть до меню Перегляд> Макроси> Макрос запису.

Призначте макросу ім'я (без пробілів) і натисніть кнопку OK.

Як тільки це буде зроблено, все Ваших дій записано - кожна зміна клітинки, дія прокрутки, зміна розмірів вікна, іменоване.

Є кілька місць, які вказують на режим запису Excel. Одним з них є перегляд меню "Макрос" і зауваження, що "Зупинка запису" замінила опцію "Макрос запису".

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

Тепер, коли ми записуємо наш макрос, давайте застосуємо наші підсумкові обчислення. Спочатку додайте заголовки.

Далі застосуйте відповідні формули (відповідно):

• = SUM (B2: K2)
• = СЕРЕДНЯ (B2: K2)
• = MIN (B2: K2)
• = MAX (B2: K2)
• = MEDIAN (B2: K2)

Тепер виділіть всі клітинки обчислення і перетягніть довжину всіх рядків даних, щоб застосувати обчислення до кожного рядка.

Як тільки це буде зроблено, кожен рядок повинен відображати відповідні резюме.

Тепер ми хочемо отримати підсумкові дані для всього аркуша, тому застосуємо ще кілька обчислень:

Відповідно:

• = SUM (L2: L21)
• = СЕРЕДНЯ (B2: K21) *Це повинно бути обчислено для всіх даних, оскільки середнє значення середніх величин рядків не обов'язково дорівнює середньому значенням всіх значень.
• = MIN (N2: N21)
• = MAX (O2: O21)
• = MEDIAN (B2: K21) * Розраховано для всіх даних з тієї ж причини, що й вище.

Тепер, коли розрахунки виконані, ми застосуємо стиль і форматування. Спочатку застосуйте загальне форматування чисел у всіх клітинках, виконавши команду Виділити все (або Ctrl + A, або клітинку між заголовками рядків і стовпчиків), і виберіть піктограму "Стиль коми" у головному меню.

Далі застосуйте візуальне форматування до заголовків рядків і стовпців:

• Жирний.
• По центру.
• Колір заливки фону.

І, нарешті, застосуйте стиль до підсумків.

Коли все закінчиться, це виглядає так:

Оскільки ми задоволені результатами, зупиніть запис макросу.

Вітаємо - ви щойно створили макрос Excel.

Щоб скористатися новим записаним макросом, ми повинні зберегти нашу книгу Excel у форматі файлів, що підтримують макрос. Однак перед тим, як ми це зробимо, потрібно спершу очистити всі існуючі дані, щоб вони не були вбудовані в наш шаблон (ідея щоразу, коли ми використовуємо цей шаблон, імпортуватимемо найновіші дані).

Для цього виділіть усі клітинки та видаліть їх.

З очищеними даними (але макросами, які все ще входять до файлу Excel), ми хочемо зберегти файл як файл з підтримкою макросів (XLTM). Важливо відзначити, що якщо ви збережете це як стандартний шаблон (XLTX), то макроси будуть ні бути в змозі запустити з нього. Крім того, ви можете зберегти файл у вигляді файлу успадкованого шаблону (XLT), який дозволить виконувати макроси.

Після збереження файлу як шаблону перейдіть вперед і закрийте Excel.

Використання макросу Excel

Перш ніж розкрити, як можна застосувати цей нещодавно записаний макрос, важливо охопити декілька моментів щодо макросів загалом:

• Макроси можуть бути шкідливими.
• Див. Вище.

Код VBA насправді досить потужний і може керувати файлами поза рамками поточного документа. Наприклад, макрос може змінити або видалити випадкові файли у папці "Мої документи". Таким чином, важливо переконатися, що ви тільки запускати макроси з надійних джерел.

Щоб використовувати макрос формату даних, відкрийте файл шаблону Excel, створений вище. Якщо ви зробите це, якщо у вас є стандартні налаштування безпеки, ви побачите попередження у верхній частині робочої книги, в якому буде сказано, що макроси вимкнено. Оскільки ми довіряємо макросу, створеному нами, натисніть кнопку "Увімкнути вміст".

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

Для завершення імпорту файлу CSV, можливо, доведеться встановити кілька варіантів, щоб Excel міг правильно інтерпретувати (наприклад, роздільник, заголовки, тощо).

Після того, як наші дані імпортуються, просто перейдіть до меню "Макроси" (на вкладці "Перегляд") і виберіть "Переглянути макроси".

У отриманому діалоговому вікні ми бачимо макрос "FormatData", який ми записали вище. Виберіть його та натисніть кнопку Виконати.

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

Дивлячись під капотом: Що робить макро роботу

Як ми вже згадували кілька разів, макрос керується кодом Visual Basic for Applications (VBA). Коли ви "записуєте" макрос, Excel фактично переводить все, що ви робите, у відповідні інструкції VBA. Простіше кажучи, вам не потрібно писати код, тому що Excel пише код для вас.

Для перегляду коду, який робить наш макрос виконанням, у діалоговому вікні Макроси натисніть кнопку Змінити.

У вікні, що відкриється, відображається вихідний код, який був записаний з наших дій при створенні макросу. Звичайно, ви можете редагувати цей код або навіть створювати нові макроси повністю всередині вікна коду. Хоча запис, який використовується в цій статті, ймовірно, відповідає більшості потреб, дії з підвищеною інтенсивністю або умовні дії потребують редагування вихідного коду.

Приймаючи наш приклад один крок далі ...

Гіпотетично припустимо, що наш файл вихідних даних, data.csv, створюється автоматизованим процесом, який завжди зберігає файл у тому самому місці (наприклад, C: Data.csv завжди є найновішими даними). Процес відкриття цього файлу та імпортування його також можна легко внести до макросу:

1. Відкрийте файл шаблонів Excel, що містить макрос "FormatData".
2. Запишіть новий макрос з назвою "LoadData".
3. За допомогою запису макросу імпортуйте файл даних, як ви зазвичай.
4. Після імпортування даних зупиніть запис макросу.
5. Видалити всі дані комірки (виберіть усі, потім видаліть).
6. Збережіть оновлений шаблон (не забудьте скористатися шаблоном із включеним макросом).

Як тільки це буде зроблено, кожного разу, коли шаблон буде відкрито, буде два макроси - один, який завантажує наші дані, а інший - формат.

Якщо ви дійсно хочете, щоб ваші руки брудні з трохи редагування коду, ви можете легко об'єднати ці дії в одному макросі, копіюючи код, створений з "LoadData" і вставивши його на початку коду з "FormatData".

Завантажте цей шаблон

Для вашої зручності ми включили як шаблон Excel, вироблений у цій статті, так і зразок файлу даних, з яким ви можете грати.

Завантажити шаблон макросу Excel з How-To Geek