Objašnjavamo tehnologiju / Explainer / Saznajte kako koristiti Excel makronaredbe za automatizaciju dosadnih zadataka

Saznajte kako koristiti Excelove makronaredbe za automatizaciju dosadnih zadataka

Jedan od snažnijih, ali rijetko koji se koristifunkcije programa Excel je mogućnost vrlo jednostavnog stvaranja 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 standardiziranje formata dokumenata - mnogo puta bez pisanja jednog retka koda.

Ako vas zanima što su makronaredbe ili kako ih zapravo stvoriti, nema problema - provest ćemo vas kroz cijeli postupak.

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

Što je makro?

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

Makronaredbi su nevjerojatno moćni i mogu učiniti gotovo sve što vam mašta može pružiti. Kao (vrlo) kratki popis funkcija koje možete učiniti s makronaredbom:

Primjena stila i oblikovanja
Manipulirajte podacima i tekstom.
Komunicirajte s izvorima podataka (baza podataka, tekstualne datoteke itd.).
Stvorite potpuno nove dokumente.
Bilo koja kombinacija, u bilo kojem redoslijedu, bilo čega od navedenog.

Stvaranje makronaredbe: Objašnjenje po primjeru

Započinjemo s CSV datotekom vaše vrtne sorte. Ovdje nema ničeg posebnog, samo 10 × 20 skupa brojeva između 0 i 100 s obrascem retka i stupca. Naš cilj je izraditi dobro oblikovani, prezentacijski list s podacima koji sadrži sažetke za svaki red.

Kao što smo gore naveli, makronaredba je VBA kôd, ali jedna od lijepih stvari o programu Excel je da ih možete kreirati / snimiti bez nužnog kodiranja - kao što ćemo i ovdje.

Da biste stvorili makronaredbu, idite na Prikaz> Makroi> Snimanje makronaredbe.

Dodijelite makronaredbi ime (bez razmaka) i kliknite U redu.

Jednom kada to učinite, svi bilježe se vaše akcije - svaka promjena ćelije, pomicanje, promjena veličine prozora, to ime.

Postoji nekoliko mjesta koja pokazuju kako je Excel način snimanja. Jedna je pregledom izbornika Makronaredba i primjećivanjem da je Zaustavljanje snimanja zamijenila mogućnost za Snimanje makronaredbi.

Drugi je u donjem desnom kutu. Ikona 'stop' označava da je u makro načinu i pritiskom ovdje će se zaustaviti snimanje (isto tako, ako nije u načinu snimanja, ova će ikona biti tipka Snimanje makronaredbe, koju možete koristiti umjesto da otvorite izbornik Makronaredbe).

Sada kada bilježimo svoj makronaredbu, primijenimo naše sažetke izračuna. Prvo dodajte zaglavlja.

Zatim primijenite odgovarajuće formule (respektivno):

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

Sada označite sve proračunske ćelije i povucite duljinu svih redaka podataka da biste izračunali primijenili na svaki redak.

Nakon što je to učinjeno, svaki red trebao bi prikazivati svoje sažetke.

Sada želimo dobiti sažetke podataka za cijeli list, pa primijenimo još nekoliko izračuna:

Odnosno:

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

Sad kad su izračuni gotovi, primijenit ćemostilu i oblikovanju. Prvo primijenite opće oblikovanje broja na sve ćelije tako što ćete odabrati Select All (bilo Ctrl + A ili kliknite ćeliju između zaglavlja redaka i stupca) i odaberite ikonu "Style Style" u početnom izborniku.

Zatim primijenite vizualno oblikovanje na zaglavlje retka i stupaca:

Bold.
Centered.
Boja za ispunu pozadine.

I na kraju, primijenite neki stil prema zbrojevima.

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

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

Čestitamo - upravo ste stvorili Excel makronaredbu.

Kako bismo mogli koristiti novo snimljeni makronaredbu, imamoza spremanje naše Excelove radne knjige u formatu datoteke s makro omogućenim. No, prije nego što to učinimo, prvo moramo očistiti sve postojeće podatke kako se ne bi ugrađivali u naš predložak (ideja je da svaki put kada koristimo ovaj predložak, uvest ćemo najnovije podatke).

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

Sada su podaci očišćeni (ali makronaredbe i dalje)uključene u datoteku Excel), datoteku želimo spremiti kao datoteku predloška s makro omogućenim (XLTM). Važno je napomenuti da ako to spremite kao standardnu datoteku predloška (XLTX), tada će se makronaredbe pojaviti ne biti u stanju pokrenuti se s njega. Možete i spremiti datoteku kao staru datoteku predloška (XLT), što će omogućiti pokretanje makronaredbi.

Nakon što ste spremili datoteku kao predložak, nastavite i zatvorite Excel.

Korištenje Excelove makronaredbe

Prije nego što opišemo kako možemo primijeniti ovu novo snimljenu makronaredbu, važno je pokriti nekoliko točaka o makronaredbama općenito:

Makronaredbe mogu biti zlonamjerne.
Vidi gornju točku.

VBA kod je zapravo prilično moćan i možemanipulirati datotekama izvan okvira trenutnog dokumenta. Na primjer, makronaredba može mijenjati ili brisati slučajne datoteke u mapi Moja dokumenta. Kao takvo, važno je biti siguran samo pokretanje makronaredbi iz pouzdanih izvora.

Da biste koristili našu makronaredbu formata podataka, otvoriteDatoteka predloška Excela koja je stvorena gore. Kad to učinite, pod pretpostavkom da su omogućene standardne sigurnosne postavke, na vrhu radne knjige vidjet ćete upozorenje koje kaže da su makronaredbe onemogućene. Budući da vjerujemo makronaredbi koju smo sami kreirali, kliknite gumb "Omogući sadržaj".

Dalje ćemo uvesti najnoviji skup podataka iz CSV-a (ovo je izvor radnog lista za izradu našeg makronaredbe).

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

Nakon što se naši podaci uveze, jednostavno idite na izbornik Makronaredbe (na kartici View) i odaberite View Macros.

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

Jednom kada pokrenete, vidjet ćete kako se pokazivač skače uokolo nekoliko trenutaka, ali kako to čini, vidjet ćete kako se podacima manipulira točno kako smo to snimili. Kad se sve kaže i učini, trebalo bi izgledati baš kao i naš izvornik - osim s različitim podacima.

Gledanje pod haubu: Što čini makro djelo

Kao što smo već par puta spomenuli, makropokreće Visual Basic za aplikacije (VBA) kôd. Kada "snimite" makronaredbu, Excel zapravo prevodi sve što radite u VBA upute. Jednostavnije rečeno - ne morate pisati nijedan kôd jer Excel piše kôd za vas.

Da biste pogledali kôd zbog kojeg se naš makronaredba pokreće, u dijaloškom okviru Makronaredbe kliknite gumb Uredi.

Prozor koji se otvorio prikazuje izvorni kodto je zabilježeno iz naših akcija prilikom stvaranja makronaredbe. Naravno, ovaj kôd možete urediti ili čak stvoriti nove makronaredbe u potpunosti unutar prozora koda. Iako će radnja snimanja korištena u ovom članku vjerojatno odgovarati većini potreba, zahtijevat će više prilagođene radnje ili uvjetne radnje da uredite izvorni kôd.

Uzimajući naš primjer, korak dalje ...

Hipotetski pretpostavimo našu izvornu datoteku podataka, podatke.csv, proizvodi se automatiziranim postupkom koji datoteku uvijek sprema na isto mjesto (npr. C: Datadata.csv su uvijek najnoviji podaci). Proces otvaranja ove datoteke i njenog uvoza lako se može pretvoriti i u makronaredbu:

Otvorite datoteku predloška programa Excel koja sadrži našu makronaredbu "FormatData".
Snimite novu makronaredbu pod nazivom "LoadData".
Pomoću makro zapisa snimite datoteku podataka kao što je to uobičajeno.
Nakon uvoza podataka prestanite snimati makronaredbu.
Izbrišite sve podatke ćelije (odaberite sve, a zatim obrišite).
Spremite ažurirani predložak (ne zaboravite da koristite oblik predloška na makronaredbi).

Kad se to učini, kad god se otvori predložak, postojat će dvije makronaredbe - jedna koja učitava naše podatke, a druga koja oblikuje.

Ako ste se doista htjeli umazati rukamamalo uređivanja koda, te radnje možete lako kombinirati u jedan makronarednik kopiranjem koda proizvedenog iz "LoadData" i umetanjem na početku koda iz "FormatData".