Макроси Excel - практичний посібник для початківців із прикладами

excel macros hands tutorial

Цей практичний підручник з макросів Excel пояснює, що таке макрос, як створювати та використовувати макроси VBA з великою кількістю прикладів:

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

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

=> Погляньте на посібник для початківців VBA тут

Що ви дізнаєтесь:

• Що таке макроси Excel
  • Як увімкнути макроси в Excel
  • Увімкнення вкладки Розробник
  • Параметри вкладки розробника
  • Як записати макрос
  • Збереження книги Excel за допомогою макросу
  • Виконання макросу
  • Посилання на стільникові
  • Запис макросу Excel за допомогою відносного посилання
  • Часті запитання
• Висновок
  • Рекомендована література

Що таке макроси Excel

Макрос - це набір дій, які можна виконати для виконання бажаного завдання.

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

Як увімкнути макроси в Excel

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

Виконайте наведені нижче дії, щоб показати вкладку Розробник. Це працює у всіх версіях Excel для Windows (Excel 2007,2010, 2013, 2016, 2019).

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

Увімкнення вкладки Розробник

# 1) Клацніть на Файл вкладку

# два) Клацніть Варіанти

# 3) Натисніть на Налаштування стрічки.

# 4) У розділі Налаштування стрічки увімкнути Розробник.

як відкрити торрентований файл - -

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

Параметри вкладки розробника

Нижче перераховані параметри, доступні на вкладці Розробник.

• Visual Basic: Дає редактору можливість писати або редагувати код VBA. Також можна відкрити за допомогою Alt + F11.
• Макроси: Дає список усіх вже записаних макросів, а також використовується для запису нового. Alt + F8 безпосередньо відкриє список макросів.
• Надбудови: Дозволяє вставляти надбудову, а також може керувати ними.
• Елементи керування: Допомагає нам у використанні елементів керування Form та ActiveX. Перегляд і редагування властивостей управління. Тут регулюється режим проектування ON / OFF.
• XML: Допомагає нам імпортувати / експортувати файл даних XML, керувати пакетами розширення XML, а також відкривати область завдань джерела XML.

Як записати макрос

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

Але перед відправкою вас попросять виконати деяке форматування, наприклад:

що таке крос-браузерне тестування

1. Вставте заголовок для кожного аркуша, що містить назву команди та номер тижня, позначте його жирним шрифтом та фон жовтим.
2. Намалюйте межу
3. Жирним заголовком стовпців.
4. Перейменуйте назву аркуша як назву команди.

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

Запис макросу досить простий. Перейдіть на вкладку розробника та натисніть Запис макросу.

Це відкриє вікно, куди вам потрібно увійти.

# 1) Назва макросу: Ім'я не повинно мати пробілів між словами. Починати це слід з алфавіту або підкреслення.

# 2) Клавіша швидкого доступу: Це корисно, коли ви запускаєте макрос. Якщо натиснути клавішу швидкого доступу, вона буде виконана. Обов’язково надайте ключ, який ще не взяли, інакше макрос це замінить.

Наприклад, якщо ви згадуєте Ctrl + S як ярлик, то кожного разу, коли ви натискаєте Ctrl + S, ваш макрос буде виконаний, і тим самим ігноруючи опцію збереження файлу. Тому рекомендується додати Shift, як Ctrl + Shift + D

# 3) Зберігати макрос у: Це має 3 варіанти, як зазначено нижче.

• Ця робоча книга: Усі створені макроси будуть доступні лише для поточної книги. Якщо ви відкриєте новий Excel, макрос, створений раніше, буде недоступним, а отже, його не можна використовувати.
• Персональна книга з макросів: Якщо ви вибрали це, створений макрос буде збережений і відображатиметься, коли ви відкриєте новий аркуш Excel.
• Нова робоча книга: Цей параметр відкриє нову книгу, і дії, виконані в цій книзі, будуть записані.

# 4) Опис: Це опише призначення макросу. Рекомендується дати детальний опис, щоб кожен, хто користується цим, знав, для чого саме він використовується.

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

Збереження книги Excel за допомогою макросу

Вибір макросу Store у якості “This Workbook”: Вважайте, що під час запису ви вибрали макрос Store як “This Workbook”. Закінчивши, збережіть файл. Під час збереження потрібно вибрати книгу Excel з підтримкою макросів. Вам не потрібно явно зберігати макрос. Він зберігається автоматично.

Вибір пункту Зберегти макрос як “Персональна книга макросів”: Тепер подумайте про те, щоб вибрати макрос Store у якості “Personal Macro workbook” під час запису. Вам потрібно явно зберегти макрос. Якщо просто зберегти файл Excel, а потім спробувати закрити файл. Тоді ви отримаєте спливаюче діалогове вікно, як показано нижче.

Примітка: Якщо ви не збережете це, макрос буде видалено.

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

Тепер, коли ми закінчили запис та збереження файлу, спробуємо запустити його та досягти бажаних результатів. Ми продовжили і записали макрос з усіма кроками, необхідними для досягнення, у прикладі табеля обліку відвідуваності та зберегли його як цю книгу з комбінацією клавіш Ctrl + Shift + B.

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

Додано Excel-Macro-Workbook

Примітка:

1. Якщо ви забули клавішу швидкого доступу, перейдіть до Розробник -> Макроси, Виберіть макрос і натисніть на параметри.
2. Якщо макрос, що зберігається як особистий магазин, не відображається на вкладці Макроси. Перейдіть до Переглянути -> Показати, і тут відобразиться список усіх макросів.

Посилання на стільникові

Існує 2 способи записати макрос, як показано нижче.

1. Абсолютні посилання на стільники
2. Відносні посилання на клітинки

Абсолютні посилання на клітинки: Абсолютні посилання завжди вказуватимуть на конкретну комірку, де вона була записана. Наприклад: якщо ви записуєте текст у клітинку A10, тоді наступного разу, коли ви будете використовувати цей макрос в іншій книзі, він помістить цей текст у A10.

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

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

Приклад: Скажімо, припустімо, що у вас є аркуш Excel із повними іменами, номерами телефонів та DOB 1000 співробітників. (Формат такий, як показано нижче)

Ваш менеджер очікує від вас:

1. Відокремте ім’я та прізвище.
2. Додайте до номера телефону Приклад коду країни (+91).
3. Показати DOB у формі dd-mon-yy, Приклад: 10 січня 87.

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

Запис макросу Excel за допомогою відносного посилання

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

Запитання та відповіді на інтерв’ю для розробників

Перейдіть до Розробник -> натисніть Використовувати відносне посилання -> Запис макросу . Запишіть все, що побажаєте, і натисніть зупинити запис.

Для наведеного вище прикладу виконайте такі дії.

1. Спочатку нам потрібно вставити стовпець поруч із Emp FullName і змінити заголовок стовпця на FirstName та LastName.
2. Виберіть Клітинка B2-> Перейти до розробника -> Використовувати відносне посилання -> Запис макросу .
3. За допомогою роздільника тексту відокремте ім’я та прізвище. Після закінчення зупиніть запис.
4. Подібним чином створіть ще 2 макроси для номера телефону та DOB.
5. Збережіть файл.
6. Щоб виконати, виберіть все ім'я Emp FullName, тобто B3 до останнього часу, тобто B1001, і виконайте 1^вулМакрос
7. Виконайте подібні кроки для номера телефону та DOB. Отриманий Excel показано нижче.

Вкладений файл для довідки

Часті запитання

Q # 1) Який приклад макросів у Excel?

Відповідь: Макрос - це набір дій, які можна виконати для виконання бажаного завдання.

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

Q # 2) Де знаходяться макроси в Excel?

Відповідь: Усі записані макроси будуть доступні в Вкладка розробника -> Макроси

Якщо ви не можете знайти особистий макрос, перейдіть до Перегляд -> Показати .

Запитання №3) Які типи посилань на комірки в Excel?

Відповідь:

• Абсолютно: Абсолютні посилання завжди вказуватимуть на конкретну комірку, де вона була записана. Наприклад, якщо ви записуєте текст у комірку D10, то кожного разу, коли використовується макрос, він завжди вказує на D10.
• Відносна: Вони зручні, коли вам потрібно повторити одне і те ж обчислення або кроки в декількох рядках або стовпцях.

Q # 4) Як зберегти макрос у Excel у всі книги?

Відповідь: Під час запису макросу, вибраного книги «Персональний макрос», у розділі «Зберігати макрос» це зробить ваш макрос доступним для всіх книг. Якщо ви все ще не бачите опції, перейдіть до Перегляд -> Показати .

Висновок

У цьому підручнику ми вивчили макроси Excel, які допомагають нам в автоматизації рутинних завдань у Excel.

Ми бачили, що таке макрос? Як увімкнути показ макросу в Excel. Ми також дослідили, як записати макрос, використовуючи абсолютне та відносне посилання на комірки на прикладах.

=> Прочитайте навчальну серію Easy VBA

Рекомендована література

• Підручник з VBA - Вступ до VBA в
• Типи даних VBA - числові та нечислові типи даних у VBA
• 35+ Найпоширеніші запитання та відповіді на інтерв’ю Microsoft Excel
• Як автоматизувати повторювані завдання контролю якості за допомогою макросів Excel (Приклади)
• Як відкрити файл XML у Excel, Chrome та MS Word
• Як читати або записувати дані з аркуша Excel у веб-драйвері Selenium
• 5 найкращих речей, які тестер повинен мати у програмі Excel (і перспективи зміни тестера програмного забезпечення)
• Робота з об’єктами Excel VBScript