Съдържание
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, адресите на клетките винаги са латински букви и цифри):
- =SUM(B2:K2) or =SUM(B2:K2)
- =СРЕДНО(B2:K2) or =СРЗНАЧ(B2:K2)
- =MIN(B2:K2) or =MIN(B2:K2)
- =MAX(B2:K2) or =MAX(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)
- =MAX(O2:O21) or =MAX(O2:O21)
- =МЕДИАН(B2:K21) or =МЕДИАН(B2:K21) – смятаме да използваме изходните данни от таблицата, поради посочената по-горе причина.
След като приключихме с изчисленията, нека направим малко форматиране. Първо, нека зададем един и същ формат за показване на данни за всички клетки. Изберете всички клетки на листа, за да направите това, използвайте клавишната комбинация Ctrl + Aили щракнете върху иконата Избери всички, който се намира в пресечната точка на заглавията на реда и колоната. След това щракнете Стил със запетая Раздел (Формат с разделители). Начало (У дома).
След това променете външния вид на заглавките на колоните и редовете:
- Удебелен шрифт.
- Централно подравняване.
- Цветно запълване.
И накрая, нека настроим формата на общите суми.
Ето как трябва да изглежда накрая:
Ако всичко ви устройва, спрете да записвате макроса.
Честито! Вие току-що сами записахте първия си макрос в Excel.
За да използваме генерирания макрос, трябва да запазим документа на Excel във формат, който поддържа макроси. Първо, трябва да изтрием всички данни от създадената от нас таблица, т.е. да я направим празен шаблон. Факт е, че в бъдеще, работейки с този шаблон, ние ще импортираме най-актуалните и подходящи данни в него.
За да изчистите всички клетки от данни, щракнете с десния бутон върху иконата Избери всички, който се намира в пресечната точка на заглавията на редовете и колоните и от контекстното меню изберете Изтрий (Изтрий).
Сега нашият лист е напълно изчистен от всички данни, докато макросът остава записан. Трябва да запазим работната книга като шаблон на Excel с активирани макроси, който има разширението XL ™.
Важен момент! Ако запишете файла с разширението XLTX, тогава макросът няма да работи в него. Между другото, можете да запазите работната книга като шаблон на Excel 97-2003, който има формата XLT, поддържа и макроси.
Когато шаблонът е записан, можете спокойно да затворите Excel.
Изпълнение на макрос в Excel
Преди да разкриете всички възможности на създадения от вас макрос, смятам, че е редно да обърнете внимание на няколко важни момента относно макросите като цяло:
- Макросите могат да бъдат вредни.
- Прочетете предишния параграф отново.
VBA кодът е много мощен. По-специално, той може да извършва операции с файлове извън текущия документ. Например, макрос може да изтрие или модифицира всякакви файлове в папка Моите документи. Поради тази причина стартирайте и разрешавайте само макроси от източници, на които имате доверие.
За да изпълните нашия макрос за форматиране на данни, отворете шаблонния файл, който създадохме в първата част на този урок. Ако имате стандартни настройки за сигурност, тогава когато отворите файл, над таблицата ще се появи предупреждение, че макросите са деактивирани, и бутон за активирането им. Тъй като сами направихме шаблона и си имаме доверие, натискаме бутона Активиране на съдържанието (Включете съдържание).
Следващата стъпка е да импортирате последния актуализиран набор от данни от файла CSV (въз основа на такъв файл създадохме нашия макрос).
Когато импортирате данни от CSV файл, Excel може да ви помоли да зададете някои настройки, за да прехвърлите правилно данните в таблицата.
Когато импортирането приключи, отидете в менюто Макроси (Макроси). Гледка (Преглед) и изберете команда Преглед на макроси (Макро).
В диалоговия прозорец, който се отваря, ще видим ред с името на нашия макрос FormatData. Изберете го и щракнете бягане (Изпълни).
Когато макросът започне да се изпълнява, ще видите курсора на таблицата да скача от клетка в клетка. След няколко секунди с данните ще бъдат извършени същите операции, както при запис на макрос. Когато всичко е готово, таблицата трябва да изглежда по същия начин като оригинала, който форматирахме на ръка, само с различни данни в клетките.
Нека да погледнем под капака: Как работи макросът?
Както беше споменато повече от веднъж, макросът е програмен код на език за програмиране. Visual Basic за приложения (VBA). Когато включите режима за запис на макроси, Excel всъщност записва всяко действие, което правите под формата на инструкции на VBA. Просто казано, Excel пише кода вместо вас.
За да видите този програмен код, трябва в менюто Макроси (Макроси). Гледка (преглед) щракнете Преглед на макроси (Макроси) и щракнете върху диалоговия прозорец, който се отваря редактирам (Промяна).
Прозорецът се отваря. Visual Basic за приложения, в който ще видим програмния код на макроса, който сме записали. Да, разбрахте правилно, тук можете да промените този код и дори да създадете нов макрос. Действията, които извършихме с таблицата в този урок, могат да бъдат записани с помощта на автоматично записване на макроси в Excel. Но по-сложните макроси, с фино настроена последователност и логика на действие, изискват ръчно програмиране.
Нека добавим още една стъпка към нашата задача...
Представете си, че нашият оригинален файл с данни данни.csv се създава автоматично от някакъв процес и винаги се съхранява на диска на едно и също място. Например, C:Datadata.csv – път до файла с актуализирани данни. Процесът на отваряне на този файл и импортиране на данни от него също може да бъде записан в макрос:
- Отворете файла с шаблона, където сме записали макроса − FormatData.
- Създайте нов макрос с име LoadData.
- Докато записвате макрос LoadData импортиране на данни от файл данни.csv – както направихме в предишната част на урока.
- Когато импортирането приключи, спрете записа на макроса.
- Изтрийте всички данни от клетките.
- Запазете файла като шаблон на Excel с активирани макроси (разширение XLTM).
Така, стартирайки този шаблон, получавате достъп до два макроса – единият зарежда данните, другият ги форматира.
Ако искате да се занимавате с програмиране, можете да комбинирате действията на тези два макроса в едно – просто като копирате кода от LoadData към началото на кода FormatData.