Макроси на Excel - практически урок за начинаещи с примери

excel macros hands tutorial

Този практически урок за макроси на Excel обяснява какво е макрос, как да създавате и използвате VBA макроси с много примери:

Повечето от нас в бранша със сигурност ще имат определени задачи, които трябва да се изпълняват многократно почти всеки ден. Сега си представете дали тези задачи се изпълняват само с едно щракване. Звучи вълнуващо? Excel Macros е отговорът на това.

В този урок ще научим какво е макрос? Как да запишем макрос, използвайки абсолютна и относителна препратка, заедно с някои практически примери.

=> Погледнете тук ръководството за начинаещи VBA

Какво ще научите:

• Какво представляват макросите на Excel
  • Как да активирам макроси в Excel
  • Активиране на раздела за програмисти
  • Опции на раздела за програмисти
  • Как да запиша макрос
  • Запазване на работна книга на Excel с макрос
  • Изпълнение на макрос
  • Препратка към клетки
  • Запишете макрос на Excel, като използвате относителна препратка
  • често задавани въпроси
• Заключение
  • Препоръчително четене

Какво представляват макросите на Excel

Макросът е набор от действия, които можете да изпълните, за да изпълните желаната задача.

Да предположим, че всеки месец създавате отчет, който трябва да маркира потребителските акаунти с просрочената сума в получер и червен цвят. След това можете да създадете и стартирате макрос, който прилага тези промени във форматирането всеки път, когато пожелаете.

Как да активирам макроси в Excel

Разделът за програмисти ни дава възможност да работим с функции като макроси, добавки и също така ни позволява да напишем собствен VBA код, който ще ни помогне да автоматизираме всичко, което пожелаем. Този раздел е скрит по подразбиране.

Следвайте стъпките по-долу, за да покажете раздела за програмисти. Това работи във всички версии на Excel за Windows (Excel 2007,2010, 2013, 2016, 2019).

Забележка: Това е еднократен процес. След като активирате раздела за програмисти, той винаги ще се показва в персонализирана лента за всеки екземпляр на Excel, който отворите, освен ако не продължите и не го деактивирате изрично.

Активиране на раздела за програмисти

# 1) Щракнете върху Файл раздел

# две) Щракнете Настроики

# 3) Кликнете върху Персонализирайте лентата.

# 4) Под Персонализиране на лентата активирайте Разработчик.

След като активирате раздела за програмисти, той ще се покаже в списъка с ленти.

Опции на раздела за програмисти

По-долу са изброени опциите, които присъстват в раздела за програмисти.

• Visual Basic: Дава редактор за писане или редактиране на VBA код. Може да се отвори и чрез Alt + F11.
• Макроси: Дава списък на всички вече записани макроси и също се използва за записване на нов. Alt + F8 директно ще отвори списъка с макроси.
• Добавки: Позволява вмъкване на добавка и може също да ги управлява.
• Контроли: Помага ни да използваме контролите за формуляри и ActiveX контролите. Преглед и редактиране на контролните свойства. Режимът за проектиране ON / OFF се контролира тук.
• XML: Помага ни да импортираме / експортираме XML файл с данни, да управляваме XML пакети за разширение и също така да отваряме прозореца на задачите XML Source.

Как да запиша макрос

Помислете за пример , че вашата компания има определен инструмент, който генерира разписания за различни отдели в Excel. Вие като мениджър носите отговорността да преглеждате и изпращате листа на финансовия екип всяка седмица.

Но преди изпращане от вас се иска да направите някакво форматиране като:

1. Поставете заглавието за всеки лист, който включва име на екип и номер на седмицата, маркирайте го получер и фона жълт.
2. Начертайте граница
3. Удебелете заглавията на колоните.
4. Преименувайте името на листа като име на екипа.

Вместо да правите това ръчно всяка седмица, можете просто да създадете макрос и да извършите всички тези действия само с едно щракване.

Записването на макрос е доста лесно. Придвижете се до раздела за програмисти и натиснете Запис на макрос.

Това ще отвори прозорец, където трябва да влезете.

# 1) Име на макрос: Името не трябва да има интервали между думите. Това трябва да започне с азбука или долна черта.

# 2) Клавиш за бърз достъп: Това е полезно, когато използвате макрос. Ако натиснете клавиш за бърз достъп, той ще бъде изпълнен. Уверете се, че сте дали ключ, който още не е взет, иначе макросът ще замени това.

Например, ако споменете Ctrl + S като пряк път, тогава всеки път, когато натиснете Ctrl + S, вашият макрос ще бъде изпълнен и по този начин игнорира опцията за запис на файл. Следователно се препоръчва да добавите Shift, като Ctrl + Shift + D

# 3) Съхраняване на макрос в: Това има 3 опции, както е дадено по-долу.

• Тази работна книга: Всички създадени макроси ще бъдат достъпни само за текущата работна книга. Ако отворите нов Excel, тогава създаденият по-рано макрос няма да бъде достъпен и следователно не може да се използва.
• Лична работна книга за макроси: Ако изберете това, създаденият макрос ще бъде съхранен и ще се покаже, когато отворите нов лист на Excel.
• Нова работна книга: Тази опция ще отвори нова работна книга и действията, извършени в нея, ще бъдат записани.

# 4) Описание: Това ще опише целта на макроса. Препоръчително е да се даде подробно описание, така че всеки, който използва това, да знае за какво точно се използва.

След като попълните подробностите за полетата, споменати по-горе, можете да продължите и да извършите необходимите действия в работната книга на Excel и всичко ще бъде записано. След като приключите, върнете се в раздела за програмисти и натиснете Спиране на записа.

Запазване на работна книга на Excel с макрос

Избирането на макрос Store в „Тази работна книга“: Помислете, че сте избрали макроса Store в „Тази работна книга“, докато записвате. След като приключите, запазете файла. Докато запазвате, трябва да изберете Работна книга с активиран макрос на Excel. Не е нужно да запазвате изрично макроса. Запазва се автоматично.

Избиране на Store macro в „Personal Macro workbook“: Сега помислете дали да не изберете макроса Store като „Personal Macro workbook“, докато записвате. Трябва да запазите изрично макроса. Ако просто запазите файла на Excel и след това се опитате да го затворите. След това ще получите изскачащ диалогов прозорец, както е показано по-долу.

Забележка: Ако не запазите това, тогава макросът ще бъде изтрит.

Изпълнение на макрос

След като приключихме със записа и запазването на файла, нека се опитаме да го стартираме и да постигнем желаните резултати. Продължихме и записахме макрос с всички стъпки, необходими за постигане в примера за работно време и го запазихме като Тази работна книга с клавиш за бърз достъп като Ctrl + Shift + B.

Така че всяка седмица, когато получите нов Excel от софтуерния инструмент, трябва просто да отворите този файл на Excel и да натиснете клавишната комбинация (Ctrl + Shift + B) и всички промени ще бъдат включени, както се очаква. Полученият Excel е даден по-долу.

Прикачен Excel-Macro-Workbook

Забележка:

1. Ако сте забравили клавиша за бърз достъп, можете да отидете на Developer -> Macros, изберете макроса и щракнете върху опциите.
2. Ако макросът, съхраняван като личен магазин, не се вижда под раздела Макроси. Отидете на View -> Unhide и това ще покаже списъка с всички макроси.

Препратка към клетки

Има 2 начина за запис на макрос, както е показано по-долу.

1. Абсолютни препратки към клетки
2. Относителни препратки към клетки

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

Помислете за нашия пример за работен график за посещаемост. Винаги искаме заглавието да е на първия ред на всеки лист. Не искаме препратката към клетка да се променя, когато се копира на други листове или работни книги. В този случай Absolute Cell Referencing е полезен.

Относителни препратки към клетки: Да предположим, че трябва да повторите стъпките на различни места в работния лист. Относителните препратки са удобни винаги, когато трябва да повторите едно и също изчисление или стъпки в множество редове или колони.

Пример: Да предположим, че имате лист на Excel с пълните имена, телефонни номера и DOB на 1000 служители. (Форматът е както е показано по-долу)

Вашият мениджър очаква да:

как изглежда клавишът wep

1. Отделно име и фамилия.
2. Добавете Пример за код на държава (+91) към телефонния номер.
3. Покажете DOB под формата на dd-mon-yy, Пример: 10 януари 87.

Тъй като има 1000 записа, извършването му ръчно ще отнеме време. Така че решавате да създадете макрос. Но използването на абсолютна референция няма да реши проблема ви, тъй като искате той да работи в множество редове и колони. В този случай относителната препратка е полезна.

Запишете макрос на Excel, като използвате относителна препратка

За да записвате с помощта на относителна препратка, първо изберете клетката, която искате да започнете да записвате.

Отидете на Developer -> кликнете върху Use Relative Reference -> Record Macro . Запишете всичко, което желаете, и натиснете спиране на записа.

За горния пример следвайте тези стъпки.

1. Първо, трябва да вмъкнем колона до Emp FullName и да променим заглавието на колоната като FirstName и LastName.
2. Изберете B2 клетка-> Отидете на Developer -> Използвайте относителна препратка -> Record Macro .
3. С помощта на разделител на текст отделно име и фамилия. След като приключите, спрете записването.
4. По същия начин създайте още 2 макроса за телефонен номер и DOB.
5. Запазете файла.
6. За да изпълните, изберете всички Emp Пълно име, т.е. B3 до последното излизане, което е B1001, и изпълнете 1^улМакро.
7. Следвайте подобни стъпки за Телефонен номер и DOB. Полученият Excel е показан по-долу.

Прикачен файл за справка

често задавани въпроси

В # 1) Какво е пример за макроси в Excel?

Отговор: Макросът е набор от действия, които можете да изпълните, за да изпълните желаната задача.

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

В # 2) Къде са макросите в Excel?

Отговор: Всички записани макроси ще бъдат достъпни под Раздел за програмисти -> Макроси

Ако не можете да намерите личен макрос, отидете на Преглед -> Разкриване .

В # 3) Какви са видовете препратки към клетки в Excel?

Отговор:

• Абсолютно: Абсолютните препратки винаги ще сочат към конкретната клетка, където е била записана. Например, ако записвате текст в клетка D10, тогава всеки път, когато се използва макросът, той винаги сочи към D10.
• Относително: Те са удобни винаги, когато трябва да повторите едно и също изчисление или стъпки в множество редове или колони.

В # 4) Как да запазя макрос в Excel във всички работни книги?

Отговор: Докато записвате макрос, изберете Персонален макрос работна книга под съхранение макрос в, това ще направи вашия макрос наличен за всички работни книги. Ако все още не виждате опцията, отидете на Преглед -> Разкриване .

Заключение

В този урок научихме макросите на Excel, които ни помагат при автоматизирането на рутинните задачи в Excel.

Видяхме какво е макрос? Как да активирам макроса да се показва в Excel. Също така проучихме как да запишем макрос, използвайки абсолютни и относителни препратки към клетки с примери.

=> Прочетете серията Easy VBA Training

Препоръчително четене

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