Kako napraviti makro u Excelu. Brzi vodič: Kreiranje makroa

Kao iu drugim aplikacijama microsoft office, postoji tako zanimljiva stvar kao što je makro. Ukratko, makro je programabilni niz akcija. Možete ga kreirati na dva načina:

1. Korišćenjem odgovarajuće stavke menija;

2. Ručno.

Prva metoda je vrlo jednostavna i ne zahtijeva posebne vještine programiranja. Ali prije nego počnete kreirati makro, morate se pozabaviti svojim sigurnosnim postavkama. Idite na File/Options/Trust Center/Trust Center Settings/Macro Settings. Ovdje trebate odabrati Omogući sve makroe.

Sada je sve spremno i prelazimo direktno na kreiranje makroa. Otvorite karticu Developer i pritisnite .

Pojavit će se prozor u kojem trebate odrediti naziv makroa, dodijeliti ključ za brzo pokretanje, a također odabrati knjigu u koju želite sačuvati makro.

Nakon pritiska na dugme uredu Makro snimanje počinje. Sve što radite biće snimljeno. Na primjer, možete kreirati tabelu i urediti je - postaviti potreban broj redova, stupaca i njihovu širinu. Zatim, da biste zaustavili snimanje, potrebno je da pritisnete odgovarajuće dugme.

Sada, da pokrenemo naš makro, moramo otići na karticu Developer pritisnite Makroi.

U prozoru koji se pojavi pronađite makro koji smo kreirali i kliknite Izvrši. Možete koristiti i kombinaciju tipki koju smo unijeli prilikom kreiranja makroa.

Nema mnogo prednosti ovog načina kreiranja makroa. U većini slučajeva, takav makro se može koristiti samo u trenutnoj radnoj knjizi i za određenu situaciju. Mnogo je efikasnije koristiti makro napisan programskim jezikom Visual basic. Međutim, stvaranje takvog makroa zahtijeva određene vještine. Razmatran je primjer kreiranja makronaredbe u VBA.

Dobar dan svima. Alexey Gulynin je u kontaktu. U ovom članku želim govoriti o tome kako snimiti makro. U svojim primjerima koristit ću Microsoft Excel 2007, ali možete koristiti bilo koji (poželjno je da verzija nije niža od moje). Idite u Excel, odaberite Pogled - Makroi - Snimi makro:

U verzijama 2010 i 2013, ploča se može razlikovati, ali samo neznatno. Zatim bi se trebao otvoriti ovaj dijaloški okvir, nazvan “Snimi makro”:

Navedite ime makroa ako je ovo konačno rješenje i ako ćete ga koristiti u budućnosti. Ako ćete snimiti makro da biste špijunirali nešto (što ste zaboravili), onda možete ostaviti ime kakvo jeste.

Zabranjeno je koristiti razmake, znakove interpunkcije ili brojeve na početku imena u nazivu makroa. Možete koristiti: engleska i ruska slova, brojeve (ne na početku), donju crtu. Također je zabranjeno imenovati makro čije će ime biti isto kao bilo koji postojeći objekt u Excelu.
Za makro možete postaviti kombinaciju tastera koja će ga, kada se pritisne, pokrenuti. Nema potrebe da se prečice dodeljuju svim makroima, samo onima koje ćemo često koristiti. Općenito, postoji mnogo načina za pokretanje makronaredbe, kao opciju, možete ga postaviti na traku (počevši od verzije 2010), u verziji 2007 postoji traka, ali se ne može uređivati, može se postaviti na listu. Također možete pokrenuti makro na osnovu nekog događaja. Prečice imaju Osetljivost i na registar i na izgled, pa pazite koji prečac dodijelite svom makrou. Možete staviti velika slova, da biste to uradili morate pritisnuti Ctrl + Shift + Vaše pismo.

Ako je makro sačuvan u ovoj radnoj svesci, on će se fizički nalaziti u ovoj datoteci. Stoga, kada se datoteka zatvori, ovaj makro neće biti dostupan. U kojim slučajevima je to opravdano učiniti: makro je potreban samo za proračune u ovoj knjizi, a ne u bilo kojoj drugoj. Ako ovaj makro treba da se koristi u drugim Excel datotekama (radnim knjigama), onda će ovaj fajl morati uvek da bude otvoren ili stalno otvoren i zatvoren, što je potpuno nezgodno. Ovdje morate odabrati drugu metodu štednje: ličnu knjigu o makroima.

To je skoro običan fajl, a zove se Personal.xlsb. Ova datoteka se nalazi u mapi XLStart i otvara se u skrivenom načinu. Iako nismo ništa napisali u ovaj fajl, on ne postoji. Čim nešto zapišemo, on će se pojaviti. Sve što se nalazi u mapi XLStart pokreće se automatski. Prednost je što će makro koji ste snimili u svoju ličnu radnu svesku uvijek biti dostupan (nećete morati stalno da ga otvarate ili zatvarate). Imajte na umu da će biti dostupan samo na ovom računaru.
Makrou možete dodati i opis.

Napišimo sljedeći makro, koji će kreirati novi list i napisati frazu Hello VBA u ćeliju C4. Štaviše, natpis bi trebao biti u crvenoj boji, a stil dizajna bi trebao biti podebljan. Kliknite OK.
Umetnite list ovako:

Sada u ćeliji C4 pišemo frazu Zdravo VBA. Zatim pritisnite Ctrl + Enter da ostanete u istoj ćeliji i primijenite oblikovanje na nju. Ako samo pritisnemo Enter, fokus će se premjestiti na drugu ćeliju (ovo nam ne treba). Zatim desnom tipkom miša kliknite ćeliju i odaberite Format Cells i idite na karticu Font, odaberite crvenu i boju i podebljajte font:

Kliknite OK. Sada možemo završiti naš makro. To možete učiniti na nekoliko načina: Prikaz - Makroi - Zaustavi snimanje, takođe se može onemogućiti u donjem levom uglu (u verziji 2007) u statusnoj traci (vidi se na 3. slici). "Sheet4" je kreiran. Izbrišemo ga i koristimo makro da kreiramo novi list. Da bismo to učinili, koristit ćemo prečicu koju smo postavili za makro Ctrl + Shift + C. Provjerite jeste li na ruskoj tastaturi. Ako ste sve uradili ispravno, trebali ste kreirati novi list sa unosom koji nam je potreban.

Excel ima moćnu, ali rijetko korištenu mogućnost kreiranja automatiziranih nizova radnji pomoću makroa. Makro je idealno rješenje ako se bavite istim tipom zadatka koji se ponavlja mnogo puta. Na primjer, obrada podataka ili formatiranje dokumenata pomoću standardiziranog predloška. U isto vrijeme, nije vam potrebno poznavanje programskih jezika.

Da li vas već zanima šta je makro i kako funkcioniše? Onda samo naprijed hrabro - tada ćemo s vama proći kroz cijeli proces kreiranja makroa korak po korak.

Šta je makro?

Makro u Microsoft Office-u (da, ova funkcija radi isto u mnogim aplikacijama u paketu Microsoft Office) je programski kod u programskom jeziku (VBA) koji se čuva unutar dokumenta. Da bi bilo jasnije, Microsoft Office dokument se može uporediti sa HTML stranicom, tada je makro analog Javascript-a. Ono što Javascript može učiniti s HTML podacima na web stranici je vrlo slično onome što makro može učiniti s podacima u Microsoft Office dokumentu.

Makroi mogu izvršiti gotovo svaku radnju koju biste mogli poželjeti u dokumentu. Evo nekih od njih (veoma mali dio):

• Primijenite stilove i formatiranje.
• Izvršite različite operacije s numeričkim i tekstualnim podacima.
• Koristite eksterne izvore podataka (datoteke baze podataka, tekstualni dokumenti, itd.)
• Kreirajte novi dokument.
• Uradite sve gore navedene korake u bilo kojoj kombinaciji.

Kreiranje makroa - praktičan primjer

Na primjer, uzmimo najobičniji fajl CSV. Ovo je jednostavna tabela 10x20 ispunjena brojevima od 0 do 100 sa naslovima za kolone i redove. Naš zadatak je da ovaj skup podataka pretvorimo u prezentabilnu formatiranu tabelu i generišemo ukupne vrednosti u svakom redu.

Kao što je već pomenuto, makro je kod napisan u VBA programskom jeziku. Ali u Excelu možete kreirati program bez pisanja linije koda, što ćemo upravo sada učiniti.

Da kreirate makro, otvorite Pogled(Prikaz) > Makroi(Makroi) > Snimi makro(Snimite makro...)

Dajte svom makrou ime (bez razmaka) i kliknite uredu.

Od ovog trenutka se bilježe SVE vaše radnje s dokumentom: promjene ćelija, pomicanje kroz tablicu, čak i promjena veličine prozora.

Excel signalizira da je režim snimanja makroa omogućen na dva mjesta. Pre svega, na meniju Makroi(Makroi) – umjesto linije Snimi makro Pojavila se linija (Snimi makro...). Zaustavi snimanje(Zaustavi snimanje).

Drugo, u donjem lijevom kutu Excel prozora. Ikona Stani(mali kvadrat) označava da je režim snimanja makroa omogućen. Klikom na njega zaustavlja se snimanje. Suprotno tome, kada način snimanja nije omogućen, na ovom mjestu se nalazi ikona koja omogućava snimanje makroa. Klikom na nju dobit će isti rezultat kao i omogućavanje snimanja kroz meni.

Sada kada je režim snimanja makroa omogućen, pređimo na naš zadatak. Prije svega, dodajmo zaglavlja za zbirne podatke.

• =SUM(B2:K2) ili =SUM(B2:K2)
• =PROSJEČNO(B2:K2) ili =PROSJEČNO(B2:K2)
• =MIN(B2:K2) ili =MIN(B2:K2)
• =MAX(B2:K2) ili =MAX(B2:K2)
• =MEDIAN(B2:K2) ili =MEDIAN(B2:K2)

Sada odaberite ćelije s formulama i kopirajte ih u sve redove naše tablice povlačenjem ručke za automatsko popunjavanje.

Kada se ovaj korak završi, odgovarajući zbrojevi bi se trebali pojaviti u svakom redu.

odnosno:

• =SUM(L2:L21) ili =SUM(L2:L21)
• =PROSJEČAN(B2:K21) ili =PROSJEČAN(B2:K21)– za izračunavanje ove vrijednosti potrebno je uzeti tačno originalne podatke tabele. Ako uzmete prosjek prosjeka za pojedinačne redove, rezultat će biti drugačiji.
• =MIN(N2:N21) ili =MIN(N2:N21)
• =MAX(O2:O21) ili =MAX(O2:O21)
• =MEDIAN(B2:K21) ili =MEDIAN(B2:K21)– izračunavamo koristeći izvorne tabelarne podatke iz gore navedenog razloga.

Sada kada smo završili sa proračunima, pređimo na formatiranje. Prvo, postavimo isti format prikaza podataka za sve ćelije. Odaberite sve ćelije na listu koristeći prečicu na tipkovnici Ctrl+A, ili kliknite na ikonu Označi sve, koji se nalazi na raskrsnici naslova redova i kolona. Zatim kliknite Comma Style(Format Delimited) kartica Dom(Dom).

• Podebljani stil fonta.
• Centralno poravnanje.
• Ispunjavanje bojom.

I na kraju, postavimo format ukupnih vrijednosti.

Ovako bi to trebalo izgledati na kraju:

Ako ste zadovoljni sa svime, prestanite sa snimanjem makroa.

Čestitamo! Upravo ste sami snimili svoj prvi Excel makro.

Da bismo koristili kreirani makro, moramo spremiti Excel dokument u formatu koji podržava makroe. Prvo moramo izbrisati sve podatke iz tabele koju smo kreirali, tj. neka bude prazan šablon. Činjenica je da ćemo u budućnosti, radeći sa ovim šablonom, u njega uvoziti najnovije i najrelevantnije podatke.

Da obrišete sve ćelije podataka, kliknite desnim tasterom miša na ikonu Označi sve, koji se nalazi na preseku naslova redova i kolona, ​​a iz kontekstnog menija izaberite stavku Izbriši(Izbriši).

Sada je naš list potpuno očišćen od svih podataka, dok makro ostaje snimljen. Moramo da sačuvamo radnu svesku kao Excel predložak sa omogućenim makroima koji ima ekstenziju XLTM.

Važna tačka! Ako sačuvate datoteku sa ekstenzijom XLTX, tada makro neće raditi u njemu. Usput, radnu svesku možete spremiti kao Excel 97-2003 predložak, koji ima format XLT, takođe podržava makroe.

Kada se predložak sačuva, možete bezbedno zatvoriti Excel.

Pokretanje makroa u Excelu

Prije nego što otkrijemo sve mogućnosti makroa koji ste kreirali, mislim da je ispravno obratiti pažnju na nekoliko važnih tačaka u vezi s makroima općenito:

• Makroi mogu biti štetni.
• Pročitajte prethodni pasus ponovo.

VBA kod je veoma moćan. Konkretno, može obavljati operacije nad datotekama izvan trenutnog dokumenta. Na primjer, makro može izbrisati ili promijeniti bilo koje datoteke u folderu Moji dokumenti. Iz tog razloga, pokrenite i dozvolite samo makroe iz izvora kojima vjerujete.

Da biste pokrenuli naš makro za formatiranje podataka, otvorite datoteku šablona koju smo kreirali u prvom dijelu ovog vodiča. Ako imate standardne sigurnosne postavke, onda kada otvorite datoteku, iznad tabele će se pojaviti upozorenje koje ukazuje da je pokretanje makroa onemogućeno i dugme za omogućavanje njihovog izvršavanja. Pošto smo sami napravili šablon i verujemo sebi, pritisnemo dugme Omogući sadržaj(Uključiti sadržaj).

Sljedeći korak je uvoz najnovijeg ažuriranog skupa podataka iz datoteke CSV(kreirali smo naš makro na osnovu takvog fajla).

Kada uvezete podatke iz CSV datoteke, Excel može tražiti od vas da konfigurirate neke postavke za ispravan prijenos podataka u tabelu.

Kada se uvoz završi, idite na meni Makroi(Makroi) kartica Pogled(Prikaz) i odaberite naredbu View Macros(Makroi).

U dijaloškom okviru koji se otvori vidjet ćemo liniju s imenom našeg makroa FormatData. Odaberite ga i kliknite Trči(Trči).

Kada makro počne da se pokreće, videćete da kursor tabele skače sa ćelije na ćeliju. Nakon nekoliko sekundi, sa podacima će se izvršiti iste operacije kao i kod snimanja makroa. Kada je sve spremno, tabela bi trebala izgledati isto kao i original koji smo ručno formatirali, samo s drugačijim podacima u ćelijama.

Pogledajmo ispod haube: Kako funkcioniše makro?

Kao što je više puta pomenuto, makro je programski kod u programskom jeziku Visual Basic za aplikacije(VBA). Kada omogućite režim snimanja makroa, Excel zapravo beleži svaku radnju koju poduzmete kao uputstva u VBA. Jednostavno rečeno, Excel piše kod umjesto vas.

Da biste vidjeli ovaj programski kod, morate otići u meni Makroi(Makroi) kartica Pogled(Prikaz) kliknite View Macros(Makroi) i u dijaloškom okviru koji se otvori kliknite Uredi(Promjena).

Otvoriće se prozor Visual Basic za aplikacije, u kojem ćemo vidjeti programski kod makroa koji smo snimili. Da, dobro ste shvatili, ovdje možete promijeniti ovaj kod, pa čak i kreirati novi makro. Radnje koje smo izveli sa tabelom u ovoj lekciji mogu se snimiti pomoću automatskog snimanja makroa u Excel-u. Ali složeniji makroi, sa fino podešenim redosledom i logikom akcija, zahtevaju ručno programiranje.

Dodajmo još jedan korak našem zadatku...

Zamislite našu originalnu datoteku podataka data.csv kreira se automatski nekim procesom i uvijek se pohranjuje na disk na istom mjestu. Na primjer, C:\Data\data.csv– put do datoteke sa ažuriranim podacima. Proces otvaranja ove datoteke i uvoza podataka iz nje također se može snimiti u makronaredbi:

1. Otvorite fajl šablona u koji smo sačuvali makro - FormatData.
2. Kreirajte novi makro pod nazivom LoadData.
3. Tokom snimanja makroa LoadData uvoz podataka iz datoteke data.csv- kao što smo radili u prethodnom dijelu lekcije.
4. Kada se uvoz završi, zaustavite snimanje makroa.
5. Uklonite sve podatke iz ćelija.
6. Sačuvajte datoteku kao Excel predložak s podrškom za makroe (XLTM ekstenzija).

Dakle, pokretanjem ovog predloška dobijate pristup dvama makroima - jedan učitava podatke, drugi ih formatira.

Ako želite da se bavite programiranjem, možete kombinovati radnje ova dva makroa u jedan - jednostavnim kopiranjem koda sa LoadData na početak koda FormatData.

Ako imate zadatke u programu Microsoft Excel koji se ponavljaju, možete snimiti makro za automatizaciju tih zadataka. Makro je radnja ili skup radnji koje možete pokrenuti koliko god puta želite. Kada kreirate makro, bilježe se klikovi mišem i tipke. Nakon što kreirate makro, možete ga modificirati kako biste unijeli male promjene u njegovo funkcioniranje.

Recimo da svakog mjeseca kreirate izvještaj za svog računovodstvenog menadžera. Želite da formatirate korisnička imena naloga koji su istekli crvenom bojom i takođe primenite podebljano oblikovanje. Možete kreirati makro koji brzo primjenjuje ove promjene oblikovanja na odabrane ćelije i pokrenuti ga.

Procedura

Snimite makro

Dalje radnje

Procedura

Dodatne informacije

Uvijek možete postaviti pitanje iz Excel Tech zajednice, zatražiti pomoć u zajednici Answers ili predložiti novu funkciju ili poboljšanje web stranice

Makroi su neophodni. Makro se može definirati kao niz naredbi (uzastopno), koje mogu biti klikovi, pritisci na tipke ili čak male linije koda s naprednijom funkcionalnošću. Ove sekvence se zapisuju u VBA modul i izvršavaju po potrebi.

Glavna prednost makroa je ušteda vremena koje pruža, jer automatizuje ono što se mora uraditi ručno u nekoliko koraka. Vrlo korisno kada trebate izvršiti isti skup zadataka više puta.

Makro se može kreirati na dva različita načina:

• Koristeći Macro Recorder: U ovoj proceduri, Excel sprema svaku radnju koja će biti dio makronaredbe, a zatim program za pisanje makroa pretvara te radnje u naredbe Visual Basica za aplikacije (VBA).
• Koristeći VBA: Ako znate nešto o programiranju ili čak znate VBA, možete sami kreirati svoje makroe. Da biste to učinili, jednostavno koristite VBA editor prisutan u najnovijim verzijama Microsoft Excel-a.

Kao što je ranije spomenuto, postoji mnogo stvari koje možete učiniti s makroima, ali neke se razlikuju od drugih jer se češće koriste u svakodnevnom radu. Pogledajte 10 makroa koji će vam pomoći s vašim proračunskim tablicama.

1) Prikaži poruku

Samo zalijepite naredbu MsgBox u svoj makro. Kao u sljedećem primjeru:

MsgBox "tekst poruke"

Ova linija koda može se umetnuti u bilo koji makro, što je vrlo korisno u slučajevima kada trebate izdati upozorenja korisniku proračunske tablice u kojoj je makro pokrenut.

2) Pokrenite makro kada otvarate list

U nekim slučajevima, makro treba otvoriti zajedno sa radnim listom. U ovom slučaju, jednostavno montirajte makro pomoću parametra Auto_Open(). Provjerite primjer

Sub Auto_Open()
MsgBox "Da saznate sve o Excelu, idite na www.https://luz.vc/"
End Sub

U gornjem primjeru, poruka će se prikazati nakon otvaranja tabele. Ne zaboravite da omogućite makroe u svom Excelu.

3) Radni list sa trenutnim datumom i vremenom

Možete napisati trenutni datum i vrijeme na listu. Sljedeći primjer umeće ove podatke u ćeliju A1, vidi

Sub writeDataEora()
Raspon("A1") = Sada
End Sub

4) Izvršite istu radnju za svaku odabranu ćeliju.

U nekom trenutku morate izvršiti radnju na ćelijama koje je odabrao korisnik. Da biste to učinili, kreirajte makro kao u primjeru ispod.

Sub to doSpeedCell()
Za svaku ćeliju u Selection.Cells
Cell MsgBox
Sljedeći
End Sub

5) Isto na svim odabranim ćelijama

Isti makro možete uraditi i ranije, ali na malo potpuniji način, odnosno radnja se neće izvoditi korak po korak. U primjeru ispod, "Hello" je napisano u sve ćelije.

Prijeđite na doSomethingAllAsCells()
Selection.Cells.Value = "Zdravo"!}
End Sub

Možete napraviti mnogo promjena u kodu, kao što je promjena boje teksta, veličine fonta, vrste fonta i još mnogo toga.

6) Identifikacija formula u svakoj ćeliji

Sub CheckFormula()
Ako opseg("A1") ima formulu = Tačno onda
MsgBox “Postoji formula”
Više
MsgBox "Nije formula"
End If
End Sub

7) Promijenite boju ćelije pri lebdenju mišem

Također možete pružiti veću interaktivnost sa korisnicima proračunskih tablica. U idealnom slučaju, interaktivnost ima za cilj da olakša održavanje i vizualizaciju informacija. Makro u nastavku je primjer kako vaše tabele mogu prikazati ćelije sa istim tonom boje kada miš prosledi podatke koji koreliraju te ćelije.

Privatni pod radni list_SelectionChange (ByVal Target As Range)

Dima domaća linija kao asortiman
Domet
Dim Line2 kao dugačak

Cells.Interior.ColorIndex = xlNone

Linija 2 = Cilj.Red

Postavite početak linije = domet ("A" i linija 2, cilj)

Boji odabranu ćeliju u koloni 5
Postavite linija = Opseg(ćelije(Cilj.Red, 1), ćelije(Cilj.Red, 5))

Sa lenjirom
.Interior.ColorIndex = 12
Završi sa

8) Promijenite boje iznutra i fontove

Ovaj makro mijenja boje unutar i na izvoru ćelija prema slovu ćelija.

Sub Colorir_interior_letra()
Za N=1 do raspona (“O65536”). Kraj (xlUp).Row

Odaberite raspon skale (“O” i N)
Slučaj "A"
Raspon("O" i N).Interior.Indeks boja = 3
Raspon("O" i N).Font.ColorIndex = 1

Slučaj "B"
Raspon("O" i N).Interior.ColorIndex = 4
Raspon("O" i N).Font.ColorIndex = 2

Slučaj "C"
Raspon("O" i N).Interior.ColorIndex = 5
Raspon("O" i N).Font.ColorIndex = 3

Slučaj "D"
Raspon("O" i N).Interior.ColorIndex = 7
Raspon("O" i N).Font.ColorIndex = 12

Case Else
Raspon("O" i N).Interior.ColorIndex = 6
Raspon("O" i N).Font.ColorIndex = 4
Kraj Odaberite

Sljedeći N

9) Govoreći makro

Već sam mislio da bi bilo zanimljivo imati tabelu koja bi sadržavala podatke sadržane u njoj, upravo to radi ovaj makro. U donjem primjeru, tabela ima riječi koje idu od ćelije A1 do A5, ali možete odabrati razmak koji vam najviše odgovara, pa jednostavno promijenite A1:A5 u kodu ispod na razmak koji želite.

Sub ExcelFalling()

Raspon ("A1:A5").