Научете как да използвате макроси на Excel за автоматизиране на задачите

Една от по-мощните, но рядко използвани функции на Excel е способността да се създават много лесно автоматизирани задачи и персонализирана логика в макросите. Макросите осигуряват идеален начин за спестяване на време за предвидими, повтарящи се задачи, както и за стандартизиране на форматите на документи - много пъти, без да се налага да се пише един ред код.

Ако сте любопитни какви макроси са или как да ги създадете, няма проблем - ще ви преведем през целия процес.

Забележка: същият процес трябва да работи в повечето версии на Microsoft Office. Снимките на екрана може да изглеждат малко по-различно.

Какво е макро?

Microsoft Office Macro (тъй като тази функционалност се прилага за няколко от MS Office приложения) е просто код Visual Basic for Applications (VBA), съхраняван в документ. За сравнима аналогия, мислете за документ като HTML и макрос като Javascript. По същия начин, по който Javascript може да манипулира HTML на уеб страница, макросът може да манипулира документ.

Макросите са невероятно мощни и могат да направят почти всичко, което въображението ви може да предизвика. Като (много) кратък списък от функции, които можете да правите с макрос:

• Прилагане на стил и форматиране.
• Манипулирайте данните и текста.
• Общувайте с източници на данни (база данни, текстови файлове и т.н.).
• Създайте напълно нови документи.
• Всяка комбинация, в произволен ред, на някое от горепосочените.

Създаване на макрос: Обяснение по пример

Започваме с CSV файла на градината. Тук няма нищо специално, само 10 × 20 набор от числа между 0 и 100, както с ред, така и със заглавие на колона. Нашата цел е да създадем добре форматиран информационен лист, който включва обобщени суми за всеки ред.

Както казахме по-горе, макросът е VBA код, но едно от хубавите неща в Excel е, че можете да ги създадете / запишете с нулево кодиране - както ще направим тук.

За да създадете макрос, отидете в View> Macros> Macro.

Задайте на макрос име (без интервали) и щракнете върху OK.

След като това стане, всичко от вашите действия са записани - всяка промяна на клетка, действие на превъртане, промяна на размера на прозореца, името.

Има няколко места, които показват, че Excel е режим на запис. Единият е чрез преглед на менюто Macro и отбелязване, че Stop Recording е заменило опцията за Record Makro.

Другият е в долния десен ъгъл. Иконата "стоп" показва, че тя е в макро режим и натискането тук ще спре записа (подобно, когато не е в режим на запис, тази икона ще бъде бутонът за макроси за запис, който можете да използвате вместо менюто за макроси).

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

След това приложи подходящите формули (съответно):

• = SUM (В2: К2)
• = Средна (В2: К2)
• = MIN (В2: К2)
• = MAX (В2: К2)
• = MEDIAN (В2: К2)

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

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

Сега искаме да получим обобщените данни за целия лист, така че ще приложим още няколко изчисления:

съответно:

• = SUM (L2: L21)
• = СРЕДНО (B2: K21) *Това трябва да се изчисли за всички данни, тъй като средната стойност на средните стойности не е задължително равна на средната стойност на всички стойности.
• = MIN (N2: N21)
• = MAX (О2: O21)
• = MEDIAN (В2: K21) * Изчислена за всички данни по същата причина, както по-горе.

Сега, когато изчисленията са направени, ще приложим стила и форматирането. Първо приложите общото форматиране на номера във всички клетки, като направите Select All (или Ctrl + A, или щракнете върху клетката между заглавията на редовете и колоните) и изберете иконата "Стил на запетаята" в менюто Начало.

След това приложете визуално форматиране както към заглавията на редовете, така и към колоните:

• смел.
• центрирано.
• Цвят на запълване на фона.

И накрая, нанесете някакъв стил на сумите.

Когато всичко приключи, това е, което изглежда нашия информационен лист:

Тъй като сме доволни от резултатите, спрете записа на макроса.

Поздравления - току-що създадохте макрос на Excel.

За да използваме новия си записан макрос, трябва да запазим нашата работна книга на Excel в файлов формат с активиран макрос. Въпреки това, преди да направим това, първо трябва да изчистим всички съществуващи данни, така че да не бъдат вградени в нашия шаблон (идеята е всеки път, когато използваме този шаблон, ще импортираме най-актуалните данни).

За да направите това, изберете всички клетки и ги изтрийте.

С изчистването на данните (но макросите, които все още са включени в Excel файла), искаме да запазим файла като файл с шаблони с активиран макрос (XLTM). Важно е да се отбележи, че ако запазите това като стандартен шаблон (XLTX), то макросите ще не може да бъде избягван от него. Алтернативно, можете да запишете файла като наследствен шаблон (XLT), който ще позволи изпълнението на макросите.

След като запазите файла като шаблон, продължете напред и затворете Excel.

Използване на Excel макрос

Преди да обхванем начина, по който можем да приложим този новозаписан макрос, важно е да покрием няколко въпроса за макросите като цяло:

• Макросите могат да бъдат злонамерени.
• Виж горната точка.

VBA кодът всъщност е доста мощен и може да манипулира файлове извън обхвата на настоящия документ. Например макрос може да промени или изтрие случайни файлове в папката „Моите документи“. Поради това е важно да се уверите, че сте само изпълнява макроси от надеждни източници.

За да използвате макроса за формата на данните, отворете файла с шаблони на Excel, който е създаден по-горе. Когато направите това, при условие че имате активирани стандартни настройки за сигурност, в горната част на работната книга ще видите предупреждение, че макросите са деактивирани. Тъй като се доверяваме на макрос, създаден от нас, кликнете върху бутона „Активиране на съдържанието“.

След това ще импортираме най-новия набор от данни от CSV (това е източникът на работния лист, използван за създаване на нашия макрос).

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

След като нашите данни бъдат импортирани, просто отидете в менюто Макроси (в раздела Изглед) и изберете Преглед на макроси.

В появилия се диалогов прозорец виждаме макроса “FormatData”, който записахме по-горе. Изберете го и щракнете върху Изпълни.

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

Гледане под качулката: Какво прави макро работа

Както споменахме няколко пъти, макросът се управлява от Visual Basic за приложения (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