Saznajte kako koristiti makronaredbe programa Excel za automatizaciju zadataka

Jedna od moćnijih, ali rijetko korištenih funkcija Excela je mogućnost vrlo jednostavnog kreiranja automatiziranih zadataka i prilagođene logike unutar makronaredbi. Makronaredbe pružaju idealan način za uštedu vremena na predvidljivim, ponavljajućim zadacima, kao i na standardiziranju formata dokumenata - mnogo puta bez potrebe za pisanjem jednog retka koda.

Ako ste znatiželjni što su makronaredbe ili kako ih zapravo stvoriti, nema problema - provest ćemo vas kroz cijeli proces.

Bilješka: isti proces trebao bi raditi u većini verzija sustava Microsoft Office. Snimke zaslona mogu izgledati malo drugačije.

Što je makro?

Microsoft Office Macro (jer se ova funkcija primjenjuje na nekoliko MS Office aplikacija) jednostavno je Visual Basic for Applications (VBA) kod spremljen unutar dokumenta. Za usporedivu analogiju, smatrajte dokument kao HTML i makro kao Javascript. Na isti način na koji Javascript može manipulirati HTML-om na web-stranici, makronaredba može manipulirati dokumentom.

Makronaredbe su nevjerojatno moćne i mogu učiniti gotovo sve što vaša mašta može izazvati. Kao (vrlo) kratki popis funkcija koje možete raditi s makrom:

Primijenite stil i oblikovanje.
Manipulirajte podacima i tekstom.
Komunicirajte s izvorima podataka (baza podataka, tekstualne datoteke itd.).
Stvorite potpuno nove dokumente.
Bilo koja kombinacija bilo kojeg gore navedenog u bilo kojem redoslijedu.

Izrada makronaredbe: Objašnjenje primjerom

Počinjemo s vašom CSV datotekom u vrtu. Ništa posebno ovdje, samo 10 × 20 skup brojeva između 0 i 100 s oba retka i zaglavlja stupca. Naš je cilj izraditi dobro oblikovan i prezentabilan list podataka koji sadrži ukupne ukupne iznose za svaki redak.

Kao što smo gore naveli, makronaredba je VBA kod, ali jedna od lijepih stvari u Excelu je da ih možete kreirati / snimiti s nultim kodiranjem - kao što ćemo ovdje učiniti..

Da biste stvorili makronaredbu, idite na View> Macros> Macros.

Dodijelite makro naziv (bez razmaka) i kliknite U redu.

Nakon što se to učini, svi Vaše radnje su zabilježene - svaka promjena ćelije, akcija pomicanja, promjena veličine prozora, ime.

Postoji nekoliko mjesta koja ukazuju da je Excel način snimanja. Jedan je pregledom Macro izbornika i zabilježiti da je Stop Recording zamijenio opciju Record Makro.

Drugi je u donjem desnom kutu. Ikona 'stop' označava da je u makro načinu i pritiskom na ovdje zaustavit će se snimanje (isto tako, kada nije u načinu snimanja, ova ikona će biti gumb za snimanje makronaredbe, koji možete koristiti umjesto izbornika Makronaredbe).

Sada kada snimamo naš makro, primijenimo naše zbirne izračune. Prvo dodajte zaglavlja.

Zatim primijenite odgovarajuće formule ():

= SUM (B2: K2)
= Srednja (B2: K2)
= MIN (B2: K2)
= MAX (B2: K2)
= ENT (B2: K2)

Sada označite sve ćelije za izračun i povucite duljinu svih redova podataka da biste primijenili izračune na svaki redak.

Kada se to učini, svaki redak treba prikazati njihove odgovarajuće sažetke.

Sada želimo dobiti sažetak podataka za cijeli list pa ćemo primijeniti još nekoliko izračuna:

Odnosno:

= SUM (L2: L21)
= PROSJEK (B2: K21) *To se mora izračunati za sve podatke, jer prosjek prosjeka redaka ne mora nužno biti jednak prosjeku svih vrijednosti.
= MIN (N2: n21)
= MAX (O2: O21)
= ENT (B2: K21) * Izračunava se za sve podatke iz istog razloga kao i gore.

Sada kada su proračuni gotovi, primijenit ćemo stil i oblikovanje. Prvo primijenite opće oblikovanje brojeva na sve ćelije odabirom Svi (ili Ctrl + A ili kliknite ćeliju između zaglavlja retka i stupaca) i odaberite ikonu "Stil zareza" unutar izbornika Početak.

Zatim primijenite vizualno oblikovanje na zaglavlja retka i stupca:

odvažan.
Centrirano.
Boja pozadine ispune.

I na kraju, primijenite neki stil na ukupne iznose.

Kada je sve dovršeno, ovako izgleda naš podatkovni list:

Budući da smo zadovoljni rezultatima, zaustavite snimanje makronaredbe.

Čestitamo - upravo ste stvorili Excelovu makronaredbu.

Kako bi koristili naš novi snimljeni makro, moramo spremiti Excelovu radnu knjigu u formatu datoteke s omogućenim makroima. Međutim, prije nego što to učinimo, prvo moramo izbrisati sve postojeće podatke tako da ne budu ugrađeni u naš predložak (ideja je svaki put kada koristimo ovaj predložak, uvezemo najnovije podatke).

Da biste to učinili, odaberite sve ćelije i izbrišite ih.

S podacima koji su sada izbrisani (ali makroi koji su još uključeni u Excel datoteku), želimo spremiti datoteku kao datoteku s predloškom s omogućenom makro (XLTM). Važno je napomenuti da ako spremite ovu datoteku kao standardni predložak (XLTX), tada će makronaredbe ne biti u mogućnosti pobjeći od njega. Alternativno, možete spremiti datoteku kao staru datoteku predloška (XLT), koja će omogućiti pokretanje makronaredbi.

Nakon što spremite datoteku kao predložak, idite naprijed i zatvorite Excel.

Korištenje Excel makroa

Prije nego što pokrijemo kako možemo primijeniti ovaj novo snimljeni makro, važno je pokriti nekoliko točaka o makronaredbama općenito:

Makronaredbe mogu biti zlonamjerne.
Vidi gore navedenu točku.

VBA kod je zapravo vrlo moćan i može manipulirati datotekama izvan opsega trenutnog dokumenta. Na primjer, makronaredba može izmijeniti ili izbrisati slučajne datoteke u mapi Moji dokumenti. Kao takvo, važno je osigurati vas samo pokretanje makronaredbi iz pouzdanih izvora.

Da biste koristili makronaredbu formata podataka, otvorite datoteku predloška programa Excel koja je gore izrađena. Kada to učinite, pod pretpostavkom da imate omogućene standardne sigurnosne postavke, vidjet ćete upozorenje na vrhu radne knjige u kojem se navodi da su makronaredbe onemogućene. Budući da vjerujemo makrou koji smo sami stvorili, kliknite gumb "Omogući sadržaj".

Sljedeće ćemo uvesti najnovije podatke iz CSV-a (to je izvorni radni list koji se koristi za izradu makronaredbe).

Da biste dovršili uvoz CSV datoteke, možda ćete morati postaviti nekoliko opcija kako bi je Excel ispravno protumačio (npr. Graničnik, prisutna zaglavlja itd.).

Kada se naši podaci uvezu, jednostavno idite na izbornik Makronaredbe (na kartici Prikaz) i odaberite Pregledaj makronaredbe.

U rezultirajućem dijaloškom okviru vidimo makronaredbu "FormatData" koju smo snimili gore. Odaberite i kliknite Pokreni.

Kada se pokrene, možda ćete vidjeti kako se pokazivač skakutao nekoliko trenutaka, ali ćete vidjeti da se podaci manipuliraju točno kao što smo je snimili. Kada je sve rečeno i učinjeno, trebalo bi izgledati kao naš izvornik - osim s različitim podacima.

Traženje ispod haube: što čini makro rad

Kao što smo spomenuli nekoliko puta, makronaredbu pokreće Visual Basic for Applications (VBA) kod. Kada “snimite” makro, Excel zapravo prevodi sve što radite u svoje VBA upute. Jednostavno rečeno - ne morate pisati nikakav kod jer Excel piše kod za vas.

Da biste vidjeli kôd koji čini makro pokretanje, iz dijaloga Makronaredbe kliknite gumb Uredi.

Prozor koji se otvara prikazuje izvorni kod koji je zabilježen u našim postupcima prilikom stvaranja makronaredbe. Naravno, možete urediti ovaj kod ili čak stvoriti nove makronaredbe u cijelom prozoru koda. Iako će radnja snimanja korištena u ovom članku vjerojatno odgovarati većini potreba, više prilagođene radnje ili uvjetne radnje zahtijevat će da uredite izvorni kôd.

Uzimanje našeg primjera korak dalje ...

Pretpostavimo da je naša izvorna datoteka data.csv proizvedena automatskim procesom koji uvijek sprema datoteku na isto mjesto (npr. C: Data.csv je uvijek najnoviji podatak). Proces otvaranja te uvoz i uvoz datoteke također se može lako napraviti u makronaredbu:

Otvorite datoteku predloška programa Excel koja sadrži makronaredbu "FormatData".
Snimite novi makro pod nazivom "LoadData".
Uz makro snimanje, uvezite podatkovnu datoteku kao i obično.
Nakon uvoza podataka zaustavite snimanje makronaredbe.
Izbrišite sve podatke ćelije (odaberite sve, a zatim izbrišite).
Spremite ažurirani predložak (ne zaboravite koristiti predložak za makronaredbu).

Kada se to učini, svaki put kada se predloška otvori bit će dva makronaredbe - jedan koji učitava naše podatke i drugi koji ga formatira.

Ako ste doista htjeli uprljati ruke s malo uređivanja koda, te akcije možete jednostavno kombinirati u jednu makronaredbu kopiranjem koda proizvedenog iz "LoadData" i umetanjem na početku koda iz "FormatData".

Preuzmite ovaj predložak

Radi Vaše udobnosti, uključili smo i Excel predložak proizveden u ovom članku, kao i primjerak datoteke s podacima s kojom se možete igrati.

Preuzmite Excel makro predloška iz Kako-To Geek