зміст
Excel має потужну, але в той же час дуже рідко використовувану можливість створювати автоматичні послідовності дій за допомогою макросів. Макрос — ідеальний вихід, якщо ви маєте справу з однотипним завданням, яке повторюється багато разів. Наприклад, обробка даних або форматування документа за стандартизованим шаблоном. У цьому випадку вам не знадобиться знання мов програмування.
Вам уже цікаво, що таке макрос і як він працює? Тоді сміливо вперед – далі ми крок за кроком зробимо з вами весь процес створення макросу.
Що таке макрос?
Макрос у Microsoft Office (так, ця функція однаково працює в багатьох програмах пакета Microsoft Office) — це програмний код мовою програмування Visual Basic для додатків (VBA), що зберігається всередині документа. Щоб було зрозуміліше, документ Microsoft Office можна порівняти зі сторінкою HTML, тоді макрос є аналогом Javascript. Те, що Javascript може робити з даними HTML на веб-сторінці, дуже схоже на те, що макрос може робити з даними в документі Microsoft Office.
Макроси можуть робити в документі майже все, що завгодно. Ось деякі з них (дуже мала частина):
- Застосуйте стилі та форматування.
- Виконувати різноманітні операції з числовими та текстовими даними.
- Використовуйте зовнішні джерела даних (файли бази даних, текстові документи тощо)
- Створіть новий документ.
- Виконайте все вищезазначене в будь-якій комбінації.
Створення макросу – практичний приклад
Для прикладу візьмемо самий звичайний файл CSV. Це проста таблиця 10×20, заповнена числами від 0 до 100 із заголовками для стовпців і рядків. Наше завдання — перетворити цей набір даних у презентабельно відформатовану таблицю та згенерувати підсумки в кожному рядку.
Як уже згадувалося, макрос — це код, написаний на мові програмування VBA. Але в Excel можна створити програму, не написавши жодного рядка коду, що ми зараз і зробимо.
Щоб створити макрос, відкрийте вид (Тип) > Макрос (Макро) > Запис макросу (Макрозапис…)
Дайте своєму макросу назву (без пробілів) і натисніть OK.
З цього моменту фіксуються ВСІ ваші дії з документом: зміни комірок, прокрутка таблиці, навіть зміна розміру вікна.
Excel сигналізує, що режим запису макросу включений у двох місцях. По-перше, в меню Макрос (Макроси) – замість рядка Запис макросу (Записування макросу…) з’явився рядок Зупинити запис (Зупинити запис).
По-друге, в нижньому лівому куті вікна Excel. значок Стоп (маленький квадрат) вказує на те, що ввімкнено режим запису макросу. Якщо натиснути на нього, запис буде зупинено. І навпаки, коли режим запису не ввімкнено, у цьому місці є значок для ввімкнення запису макросу. Натискання на нього дасть той же результат, що і включення запису через меню.
Тепер, коли режим запису макросу включений, приступаємо до нашого завдання. Перш за все, давайте додамо заголовки для підсумкових даних.
Далі введіть формули в комірки відповідно до назв заголовків (наведено варіанти формул для англійської та версії Excel, адреси комірок завжди латинськими літерами та цифрами):
- =СУМ(B2:K2) or =СУМ(B2:K2)
- =СЕРЕДНЄ(B2:K2) or =СРЗНАЧ(B2:K2)
- =MIN(B2:K2) or =MIN(B2:K2)
- =МАКС(B2:K2) or =МАКС(B2:K2)
- =МЕДІАН(B2:K2) or =МЕДІАН(B2:K2)
Тепер виділіть клітинки з формулами та скопіюйте їх у всі рядки нашої таблиці, перетягнувши ручку автозаповнення.
Після завершення цього кроку кожен рядок має мати відповідні підсумки.
Далі зведемо підсумки по всій таблиці, для цього зробимо ще кілька математичних дій:
Відповідно:
- =SUM(L2:L21) or =SUM(L2:L21)
- =СЕРЕДНЄ(B2:K21) or =СРЗНАЧ(B2:K21) – щоб обчислити цю величину, необхідно взяти саме вихідні дані табл. Якщо взяти середнє із середніх для окремих рядків, результат буде іншим.
- =MIN(N2:N21) or =MIN(N2:N21)
- =МАКС(O2:O21) or =МАКС(O2:O21)
- =МЕДІАН(B2:K21) or =МЕДІАН(B2:K21) – розглядаємо використання вихідних даних таблиці, з причини, зазначеної вище.
Тепер, коли ми закінчили з обчисленнями, давайте трохи відформатуємо. Спочатку встановимо однаковий формат відображення даних для всіх клітинок. Виділіть всі комірки на аркуші, для цього використовуйте комбінацію клавіш Ctrl + Aабо натисніть на іконку Вибрати всі, який розташований на перетині заголовків рядків і стовпців. Потім натисніть Стиль коми Вкладка (Формат із роздільниками). Головна (Додому).
Далі змініть вигляд заголовків стовпців і рядків:
- Жирний шрифт.
- Вирівнювання по центру.
- Кольорове заповнення.
І, нарешті, давайте налаштуємо формат підсумків.
Ось як це повинно виглядати в кінці:
Якщо вас все влаштовує, припиніть запис макросу.
Щиро вітаю! Ви щойно самі записали свій перший макрос у Excel.
Щоб використовувати згенерований макрос, нам потрібно зберегти документ Excel у форматі, який підтримує макроси. Спочатку нам потрібно видалити всі дані зі створеної таблиці, тобто зробити її порожнім шаблоном. Справа в тому, що надалі, працюючи з цим шаблоном, ми будемо імпортувати в нього найсвіжіші та актуальні дані.
Щоб очистити всі клітинки від даних, клацніть правою кнопкою миші на значку Вибрати всі, який знаходиться на перетині заголовків рядків і стовпців, і в контекстному меню виберіть видаляти (Видалити).
Тепер наш аркуш повністю очищений від усіх даних, а макрос залишається записаним. Нам потрібно зберегти книгу як шаблон Excel із підтримкою макросів і розширенням XLTM.
Важливий момент! Якщо зберегти файл із розширенням XLTX, то в ньому макрос не працюватиме. До речі, книгу можна зберегти як шаблон Excel 97-2003, який має формат Розширення XLT, він також підтримує макроси.
Коли шаблон буде збережено, ви можете безпечно закрити Excel.
Запуск макросу в Excel
Перш ніж розкривати всі можливості створеного вами макросу, вважаю доцільним звернути увагу на пару важливих моментів щодо макросів у цілому:
- Макроси можуть бути шкідливими.
- Прочитайте ще раз попередній абзац.
Код VBA дуже потужний. Зокрема, він може виконувати операції з файлами за межами поточного документа. Наприклад, макрос може видаляти або змінювати будь-які файли в папці Мої документи. З цієї причини запускайте та дозволяйте лише макроси з джерел, яким ви довіряєте.
Щоб запустити наш макрос форматування даних, відкрийте файл шаблону, який ми створили в першій частині цього посібника. Якщо у вас стандартні налаштування безпеки, то при відкритті файлу над таблицею з'явиться попередження про те, що макроси вимкнені, і кнопка для їх увімкнення. Оскільки ми зробили шаблон самі і довіряємо собі, то натискаємо кнопку Увімкнути вміст (Включіть вміст).
Наступним кроком є імпорт останнього оновленого набору даних із файлу CSV (на основі такого файлу ми створили наш макрос).
Коли ви імпортуєте дані з файлу CSV, Excel може попросити вас налаштувати деякі параметри, щоб правильно перенести дані в таблицю.
Після завершення імпорту перейдіть до меню Макрос (Макроси). вид (Перегляд) і виберіть команду Переглянути макроси (Макро).
У діалоговому вікні, що відкриється, ми побачимо рядок з назвою нашого макросу FormatData. Виберіть його та натисніть прогін (Виконати).
Коли макрос почне працювати, ви побачите, як курсор таблиці перескакує з клітинки на клітинку. Через кілька секунд з даними будуть виконані ті ж операції, що і при записі макросу. Коли все буде готово, таблиця повинна виглядати так само, як оригінал, який ми відформатували вручну, тільки з іншими даними в осередках.
Давайте заглянемо під капот: як працює макрос?
Як вже неодноразово говорилося, макрос - це програмний код на мові програмування. Visual Basic для додатків (VBA). Коли ви вмикаєте режим запису макросу, Excel фактично записує кожну вашу дію у вигляді інструкцій VBA. Простіше кажучи, Excel пише код за вас.
Щоб побачити цей програмний код, потрібно в меню Макрос (Макроси). вид (переглянути) клацнути Переглянути макроси (Макроси) і клацніть у діалоговому вікні, що відкриється Редагувати (Змінити).
Відчиняється вікно. Visual Basic для додатків, в якому ми побачимо програмний код записаного нами макросу. Так, ви правильно зрозуміли, тут ви можете змінити цей код і навіть створити новий макрос. Дії, які ми виконували з таблицею в цьому уроці, можна записати за допомогою автоматичного запису макросу в Excel. Але більш складні макроси з точно налаштованою послідовністю та логікою дій вимагають ручного програмування.
Додамо ще один крок до нашого завдання…
Уявіть, що наш оригінальний файл даних data.csv створюється автоматично певним процесом і завжди зберігається на диску в тому самому місці. Наприклад, C:Datadata.csv – шлях до файлу з оновленими даними. Процес відкриття цього файлу та імпортування даних з нього також можна записати в макросі:
- Відкрийте файл шаблону, де ми зберегли макрос − FormatData.
- Створіть новий макрос під назвою LoadData.
- Під час запису макросу LoadData імпортувати дані з файлу data.csv – як і в попередній частині уроку.
- Після завершення імпорту припиніть запис макросу.
- Видалити всі дані з комірок.
- Збережіть файл як шаблон Excel із підтримкою макросів (розширення XLTM).
Таким чином, запустивши цей шаблон, ви отримуєте доступ до двох макросів – один завантажує дані, інший їх форматує.
Якщо ви хочете заглибитися в програмування, ви можете об’єднати дії цих двох макросів в один – просто скопіювавши код з LoadData до початку коду FormatData.