Saznajte kako koristiti makronaredbe programa Excel za automatiziranje dosadnih zadataka

2024 Autor: Geoffrey Carr | [email protected]. Zadnja promjena: 2023-12-17 10:57

Jedna od moćnijih, ali rijetko korištenih funkcija programa Excel je sposobnost da vrlo lako stvoriti automatizirane zadatke i prilagođenu logiku unutar makronaredbi. Makronaredbe pružaju idealan način da se uštede na predvidljivim, ponavljajućim zadacima, kao i standardiziraju formate dokumenata - mnogo puta bez pisanja jedne linije koda.

Ako ste znatiželjni koje su makronaredbe ili kako ih zapravo stvoriti, nema problema - proći ćemo vas kroz cijeli proces.

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

Što je makro?

Microsoft Office Macro (jer se ova funkcija odnosi na nekoliko MS Office programa) je jednostavno Visual Basic for Applications (VBA) kod spremljen u dokumentu. Za usporedivu analogiju, razmislite o dokumentu kao HTML i makronaredbi kao Javascript. Na mnogo načina 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 zvučati. Kao (vrlo) kratak popis funkcija možete učiniti s makronaredbom:

Primijeni stil i oblikovanje.
Manipulirajte podatke i tekst.
Komunicirajte s izvorima podataka (baze podataka, tekstualne datoteke itd.).
Izradite posve nove dokumente.
Bilo koja kombinacija, bilo kojim redoslijedom, bilo kojeg od gore navedenih.

Stvaranje makronaredbe: objašnjenje prema primjeru

Počinjemo s vašom CSV datotekom vrste vrtova. Ništa posebno ovdje, samo 10 × 20 skup brojeva od 0 do 100 sa zaglavljem redaka i stupca. Naš cilj je proizvesti dobro oblikovanu, prezentiranu listu s podacima koji uključuje sažete iznose za svaki redak.

Kao što smo već napomenuli, makronaredba je VBA kod, ali jedna od zgodnih stvari o Excelu je da ih možete stvoriti / zapisati s nužnim kodiranjem - kao što ćemo ovdje.

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

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

Kada to učinite, svi zabilježene su vaše akcije - svaka promjena stanica, pomicanje akcije, promjena veličine prozora, imenujte ga.

Postoji nekoliko mjesta koja ukazuju da je Excel način snimanja. Jedno je gledanjem izbornika Makro i primijetivši da je zaustavljanje snimanja zamijenilo opciju za snimanje makronaredbe.

Drugi je u donjem desnom kutu. Ikona za zaustavljanje označava da je u makro načinu rada, a pritiskom na ovdje zaustavit će se snimanje (isto tako, ako nije u načinu snimanja, ova ikona bit će gumb za snimanje makronaredbe, koji možete koristiti umjesto da idete na izbornik Makronaredbe).

Sada kada snimamo našu makronaredbu, primijenimo naše sažete izračune. Prvo dodajte zaglavlja.

Zatim primijenite odgovarajuće formule (odnosno):

= 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 redaka podataka da biste primijenili izračune za svaki redak.

Kada to učinite, svaki redak treba prikazati svoje odgovarajuće sažetke.

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

= SUM (L2: L21)
= PROSJEČAN (B2: K21) *To se mora izračunati na svim podacima jer prosjek prosječnih redaka ne mora nužno biti jednak prosjeku svih vrijednosti.
= MIN (N2: n21)
= MAX (O2: O21)
= ENT (B2: K21) * Izračunava se na svim podacima iz istog razloga kao što je gore navedeno.

Sada kada su izračuni učinjeni, primijenit ćemo stil i oblikovanje. Najprije primijenite opće formatiranje broja u svim ćelijama tako da odaberete Odaberi sve (bilo Ctrl + A ili kliknite ćeliju između zaglavlja redaka i stupaca) i odaberite ikonu "Stilne rubrike" u izborniku Početak.

Zatim primijenite vizualno oblikovanje na zaglavlja redaka i stupaca:

Bold.
Centered.
Boja pozadinskog ispuna.

I konačno, primijenite neki stil do iznosa.

Kada sve završi, to izgleda naš obrazac:

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

Čestitamo - upravo ste stvorili Excel makronaredbu.

Da bismo mogli upotrebljavati novu snimljenu makronu, moramo spremiti Excelovu radnu knjigu u makronarednom formatu. Međutim, prije nego što to učinimo, prvo moramo izbrisati sve postojeće podatke tako da nije ugrađen u naš predložak (ideja je da svaki put koristimo ovaj predložak, uvest ćemo najnovije podatke).

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

S podacima koji su sada izbrisani (ali makronaredbe koje se još nalaze u datoteci Excel) želimo spremiti datoteku kao datoteku predložaka makronaredbe (MAC enabled template). Važno je napomenuti da ako to spremite kao standardni predložak (XLTX), tada će makronaredbe ne biti u mogućnosti da se pokrenuti iz nje. Alternativno, datoteku možete spremiti kao datoteku s naslijeđenim predloškom (XLT), koja će omogućiti pokretanje makronaredbi.

Kada spremite datoteku kao predložak, prijeđite i zatvorite Excel.

Korištenje makro programa Excel

Prije nego što pokrijemo kako možemo primijeniti novu snimljenu makronu, važno je pokriti nekoliko točaka o makronaredbama općenito:

Makronaredbe mogu biti zlonamjerne.
Pogledajte 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 promijeniti ili izbrisati slučajne datoteke u mapi Moji dokumenti.Kao takav, važno je osigurati vas samo pokrenuti makronaredbe iz pouzdanih izvora.

Da biste koristili makronaredbu formata podataka, otvorite datoteku iz predloška Excela koja je stvorena gore. Kada to učinite, uz pretpostavku da imate standardne sigurnosne postavke, vidjet ćete upozorenje na vrhu radne knjige koja kaže da su makronaredbe onemogućene. Budući da vjerujemo makronaredbi koju sami izradite, kliknite gumb "Omogući sadržaj".

Gore, uvest ćemo najnoviji skup podataka iz CSV-a (izvor je list za izradu makronaredbe).

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

Nakon uvoza podataka, jednostavno idite na izbornik Makronaredbe (ispod kartice Prikaz) i odaberite Prikaz makronaredbi.

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

Kada se pokrenete, možda ćete pokazati da se pokazivač skreće za nekoliko trenutaka, ali kako vidite da se podaci manipuliraju točno kao što smo snimili. Kada je sve rečeno i učinjeno, trebalo bi izgledati poput našeg izvornika - osim s različitim podacima.

Gledajući pod kapu: Što čini makro posao

Kao što smo spomenuli nekoliko puta, makronaredbu vodi Visual Basic for Applications (VBA) kod. Kada "snimate" makronaredbu, Excel zapravo prevodi sve što radite u odgovarajućim VBA uputama. Jednostavno rečeno - ne morate napisati nikakav kod jer Excel napištava kod za vas.

Da biste pregledali kôd koji čini našu makronaredbu, od dijaloga Makronaredbe kliknite gumb Uredi.

Prozor koji se otvori prikazuje izvorni kôd koji je snimljen iz naših radnji prilikom izrade makronaredbe. Naravno, možete uređivati ovaj kôd ili čak stvoriti nove makronaredbe u cijelosti unutar prozora koda. Dok se radnja snimanja koja se koristi u ovom članku najvjerojatnije odgovara većini potreba, više prilagođenih radnji ili uvjetnih radnji zahtijevaju uređivanje izvornog koda.

Uzimanje našeg primjera jedan korak dalje …

Hipotetično, pretpostavimo da je izvorna podatkovna datoteka, data.csv, izrađena automatiziranim postupkom koji uvijek sprema datoteku na istu lokaciju (npr., C: Data data.csv je uvijek najnoviji podatak). Proces otvaranja ove datoteke i njegovog uvoza može se lako pretvoriti u makronaredbu:

Otvorite datoteku predloška programa Excel koja sadrži makronaredbu "FormatData".
Zabilježite novu makronaredbu pod nazivom "LoadData".
Pomoću makro snimanja uvezite podatkovnu datoteku kao i obično.
Nakon što se podaci uvoze, prestanite snimati makronaredbu.
Izbrišite sve podatke o ćeliji (odaberite sve zatim brisanje).
Spremite ažurirani predložak (ne zaboravite upotrebljavati format predložaka omogućen makronaredbi).

Kada to učinite, svaki put kada se otvori predložak bit će dva makronaredba - jedan koji učitava naše podatke, a drugi koji ga formatira.