Kako napraviti makro u excelu. Brzi vodič: Stvaranje makronaredbe

Isto kao i druge aplikacije Microsoft Office, postoji tako zanimljiva stvar kao što je makro. Ukratko, makronaredba je programabilni slijed radnji. Možete ga stvoriti na dva načina:

1. Korištenje odgovarajuće stavke izbornika;

2. Ručno.

Prva metoda je vrlo jednostavna i ne zahtijeva posebne vještine programiranja. Ali prije nego počnete stvarati makronaredbu, morate podesiti sigurnosne postavke. Idite na File / Options / Trust Center / Trust Center Settings / Macro Settings. Ovdje morate odabrati Omogući sve makronaredbe.

Sada je sve spremno i idemo izravno na kreiranje makronaredbe. Otvaranje kartice Programer i pritisnite .

Pojavit će se prozor u kojem trebate odrediti naziv za makronaredbu, dodijeliti tipku prečaca i odabrati knjigu u koju želite spremiti makronaredbu.

Nakon pritiska na tipku u redu počinje snimanje makroa. Sve što radite bit će zabilježeno. Na primjer, možete stvoriti tablicu i urediti je - postaviti željeni broj redaka, stupaca, njihovu širinu. Zatim, da biste zaustavili snimanje, trebate pritisnuti odgovarajući gumb.

Sada, da bismo pokrenuli naš makro, trebamo karticu Programer pritisnite Makronaredbe.

U prozoru koji se pojavi pronađite makronaredbu koju smo kreirali i kliknite Trčanje. Također možete koristiti kombinaciju tipki koju smo unijeli prilikom izrade makronaredbe.

Nema mnogo prednosti ove metode stvaranja makronaredbi. U većini slučajeva takva se makronaredba može koristiti samo u trenutnoj radnoj knjizi i za određenu situaciju. Mnogo je učinkovitije koristiti makronaredbu napisanu pomoću programskog jezika Visual Basic. Međutim, stvaranje takvog makroa zahtijeva određene vještine. Razmatran je primjer kreiranja makronaredbe u VBA.

Dobar dan. Alexey Gulynin je u kontaktu. U ovom članku želio bih govoriti o 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 - Makronaredbe - Snimanje makronaredbi:

U verzijama 2010. i 2013. ploča se može razlikovati, ali ne značajno. Zatim bi se trebao otvoriti ovaj dijaloški okvir, koji se zove "Makro snimanje":

Navedite naziv makronaredbe ako će to biti konačno rješenje, a vi ćete ga koristiti u budućnosti. Ako ćete snimiti makro kako biste nešto zavirili (što ste zaboravili), onda možete ostaviti naziv kakav jest.

Zabranjeno je koristiti u nazivu makronaredbe: razmake, interpunkcijske znakove, brojeve na početku naziva. Možete koristiti: engleska i ruska slova, brojeve (ne na početku), donju crtu. Također je zabranjeno imenovati makronaredbu čije ime odgovara bilo kojem postojećem objektu u Excelu.
Za makronaredbu možete postaviti tipkovnički prečac, kada se pritisne, pokrenut će se. Ne morate dodijeliti prečice svim makronaredbama, samo ih trebate dodijeliti onima koje ćemo često koristiti. Općenito, postoji mnogo načina za pokretanje makronaredbe, kao opciju, možete je postaviti na vrpcu (počevši od verzije 2010.), u verziji iz 2007. postoji vrpca, ali se ne može uređivati, možete je postaviti na plahti. Također, makro se može pokrenuti na bilo kojem događaju. Prečice imaju osjetljivost i na slučaj i na izgled, stoga pazite koju prečacu dodijelite makronaredbi. Možete staviti velika slova, za to morate pritisnuti Ctrl + Shift + Vaše pismo.

Ako je makronaredba spremljena u ovoj knjizi, tada će se fizički nalaziti u ovoj datoteci. Stoga, kada se datoteka zatvori, ova makronaredba neće biti dostupna. U kojim slučajevima je to opravdano učiniti: makro je potreban samo za izračune u ovoj knjizi i ne više u bilo kojoj drugoj. Ako ovu makronaredbu treba koristiti u drugim Excel datotekama (knjigama), tada će se ova datoteka uvijek morati držati otvorenom ili stalno otvarati i zatvarati, što je prilično nezgodno. Ovdje morate odabrati drugi način spremanja: osobna knjiga o makroima.

To je gotovo normalna datoteka i zove se Personal.xlsb . Ova se datoteka nalazi u mapi XLStart i otvara se u skrivenom načinu rada. Iako nismo ništa napisali u ovu datoteku, ona ne postoji. Čim nešto napišemo, pojavit će se. Sve u mapi XLStart pokreće se automatski. Ovdje je prednost što će makro koji ste zapisali u svoju osobnu knjigu uvijek biti dostupan (neće je treba stalno otvarati i zatvarati). Skrećem vam pozornost na činjenicu da će biti dostupan samo na ovom računalu.
Također možete dodati opis makronaredbi.

Napišimo sljedeću makronaredbu, koja će stvoriti novi list i u ćeliju C4 napisati frazu Hello VBA. Štoviše, natpis bi trebao biti crven, a stil dizajna: podebljan. Pritisnemo OK.
Umetnite list ovako:

Sada u ćeliju 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 dovršiti naš makro. To možete učiniti na nekoliko načina: Pogled - Makronaredbe - Zaustavi snimanje, također se može onemogućiti u donjem lijevom kutu (u verziji iz 2007.) u statusnoj traci (možete ga vidjeti na 3. slici). "Sheet4" je kreiran. Izbrišite ga i upotrijebimo makro za stvaranje novog lista. Da bismo to učinili, koristit ćemo vruću tipku koju smo postavili za makronaredbu Ctrl + Shift + C. Provjerite jeste li na ruskoj tipkovnici. Ako ste sve učinili kako treba, trebali ste stvoriti novi list s zapisom koji nam je potreban.

Excel ima moćnu, ali u isto vrijeme vrlo rijetko korištenu mogućnost stvaranja automatskih nizova radnji pomoću makronaredbi. Makro je idealan izlaz ako se bavite istom vrstom zadatka koji se ponavlja mnogo puta. Na primjer, obrada podataka ili oblikovanje dokumenta prema standardiziranom predlošku. U ovom slučaju vam nije potrebno poznavanje programskih jezika.

Jeste li već znatiželjni što je makro i kako radi? Onda hrabro samo naprijed - tada ćemo korak po korak napraviti cijeli proces stvaranja makronaredbe s vama.

Što je makro?

Makronaredba u Microsoft Officeu (da, ova funkcija funkcionira isto u mnogim aplikacijama Microsoft Officea) je kod programskog jezika (VBA) pohranjen unutar dokumenta. Da bi bilo jasnije, Microsoft Office dokument se može usporediti s HTML stranicom, tada je makronaredba analogna Javascriptu. Ono što Javascript može učiniti s HTML podacima na web stranici vrlo je slično onome što makronaredba može učiniti s podacima u dokumentu Microsoft Officea.

Makronaredbe mogu učiniti gotovo sve što želite u dokumentu. Evo nekih od njih (vrlo mali dio):

• Primijenite stilove i oblikovanje.
• Izvođenje različitih operacija s brojčanim i tekstualnim podacima.
• Koristite vanjske izvore podataka (datoteke baze podataka, tekstualni dokumenti, itd.)
• Napravite novi dokument.
• Učinite sve gore navedeno u bilo kojoj kombinaciji.

Stvaranje makronaredbe - praktičan primjer

Na primjer, uzmimo najčešću datoteku csv. Ovo je jednostavna tablica 10x20 ispunjena brojevima od 0 do 100 s naslovima za stupce i retke. Naš je zadatak pretvoriti ovaj skup podataka u prezentovano formatiranu tablicu i generirati ukupne vrijednosti u svakom retku.

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

Da biste stvorili makronaredbu, otvorite pogled(Prikaz) > Makronaredbe(Makroi) > Snimi makro(Makro snimanje…)

Dajte svojoj makronaredbi ime (bez razmaka) i kliknite u redu.

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

Excel signalizira da je način snimanja makronaredbe omogućen na dva mjesta. Prvo, na jelovniku Makronaredbe(Makroi) - umjesto niza Snimi makro(Snimanje makronaredbe...) pojavila se linija Zaustavi snimanje(Zaustavi snimanje).

Drugo, u donjem lijevom kutu prozora programa Excel. Ikona Stop(mali kvadrat) označava da je snimanje makronaredbe omogućeno. Klikom na nju zaustavlja se snimanje. Suprotno tome, kada način snimanja nije omogućen, na ovom mjestu postoji ikona za omogućavanje snimanja makronaredbi. Klikom na nju dobit će isti rezultat kao i uključivanje snimanja kroz izbornik.

Sada kada je omogućen način snimanja makronaredbi, prijeđimo na naš zadatak. Prije svega, dodajmo zaglavlja za sažete podatke.

• =SUM(B2:K2) ili =SUM(B2:K2)
• =PROSJEČAN(B2:K2) ili =PROSJEČAN(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 retke naše tablice povlačenjem ručke za automatsko popunjavanje.

Nakon što dovršite ovaj korak, svaki redak trebao bi imati odgovarajuće ukupne vrijednosti.

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 točno početne podatke tablice. Ako uzmete prosjek prosjeka za pojedine retke, 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)- razmatramo korištenje početnih podataka iz tablice, iz gore navedenog razloga.

Sada kada smo završili s izračunima, napravimo malo formatiranja. Prvo, postavimo isti format prikaza podataka za sve ćelije. Odaberite sve ćelije na listu, da biste to učinili, upotrijebite tipkovnički prečac Ctrl+A ili kliknite na ikonu Odaberi sve, koji se nalazi na sjecištu naslova redaka i stupca. Zatim kliknite Zarez stil(Delimited Format) kartica Dom(Dom).

• Podebljani stil fonta.
• Poravnanje po sredini.
• Ispuna u boji.

I na kraju, postavimo format zbroja.

Ovako bi to trebalo izgledati na kraju:

Ako vam sve odgovara, prestanite snimati makro.

Čestitamo! Upravo ste sami snimili svoju prvu makronaredbu u Excelu.

Da bismo koristili generiranu makronaredbu, moramo spremiti Excel dokument u formatu koji podržava makronaredbe. Prvo moramo izbrisati sve podatke iz tablice koju smo kreirali, t.j. neka bude prazan predložak. Činjenica je da ćemo u budućnosti, radeći s ovim predloškom, u njega uvoziti najnovije i relevantne podatke.

Da biste izbrisali sve ćelije iz podataka, desnom tipkom miša kliknite ikonu Odaberi sve, koji se nalazi na sjecištu naslova redaka i stupaca, a iz kontekstnog izbornika odaberite Izbrisati(Izbrisati).

Sada je naš list potpuno očišćen od svih podataka, dok makronaredba ostaje snimljena. Moramo spremiti radnu knjigu kao Excel predložak s omogućenim makronaredbama koji ima ekstenziju XLTM.

Važna točka! Ako spremite datoteku s ekstenzijom XLTX, tada makro neće raditi u njemu. Usput, radnu knjigu možete spremiti kao predložak Excel 97-2003 koji ima format XLT, također podržava makronaredbe.

Kada je predložak spremljen, možete sigurno zatvoriti Excel.

Pokretanje makronaredbe u Excelu

Prije nego što otkrijemo sve mogućnosti makronaredbe koju ste kreirali, mislim da je ispravno obratiti pozornost na nekoliko važnih točaka u vezi s makronaredbama općenito:

• Makroi mogu biti štetni.
• Pročitajte još jednom prethodni odlomak.

VBA kod je vrlo moćan. Konkretno, može izvoditi operacije nad datotekama izvan trenutnog dokumenta. Na primjer, makronaredba može izbrisati ili izmijeniti sve datoteke u mapi Moji dokumenti. Iz tog razloga pokrenite i dopustite makronaredbe samo iz izvora kojima vjerujete.

Da biste pokrenuli našu makronaredbu za formatiranje podataka, otvorite datoteku predloška koju smo stvorili u prvom dijelu ovog vodiča. Ako imate standardne sigurnosne postavke, onda kada otvorite datoteku, iznad tablice će se pojaviti upozorenje da su makronaredbe onemogućene i gumb za njihovo omogućavanje. Budući da smo šablonu sami izradili i vjerujemo si, pritisnemo gumb Omogući sadržaj(Uključiti sadržaj).

Sljedeći korak je uvoz najnovijeg ažuriranog skupa podataka iz datoteke csv(na temelju takve datoteke kreirali smo naš makro).

Kada uvozite podatke iz CSV datoteke, Excel može zatražiti od vas da postavite neke postavke kako biste ispravno prenijeli podatke u tablicu.

Kada je uvoz dovršen, idite na izbornik Makronaredbe kartica (Makroi). pogled(Prikaz) i odaberite naredbu Prikaži makronaredbe(Makroi).

U dijaloškom okviru koji se otvori vidjet ćemo redak s nazivom naše makronaredbe FormatData. Odaberite ga i kliknite Trčanje(Izvršiti).

Kada se makronaredba pokrene, vidjet ćete kako pokazivač tablice skače sa ćelije na ćeliju. Nakon nekoliko sekundi, s podacima će se izvršiti iste operacije kao i kod snimanja makronaredbe. Kada je sve spremno, tablica bi trebala izgledati kao original koji smo ručno formatirali, samo s različitim podacima u ćelijama.

Pogledajmo ispod haube: Kako funkcionira makronaredba?

Kao što je više puta spomenuto, makro je programski kod u programskom jeziku. Visual Basic za aplikacije(VBA). Kada uključite način snimanja makronaredbi, Excel zapravo bilježi svaku vašu radnju u obliku VBA uputa. Jednostavno rečeno, Excel piše kod umjesto vas.

Da biste vidjeli ovaj programski kod, trebate u izborniku Makronaredbe kartica (Makroi). pogled(prikaz) kliknite Prikaži makronaredbe(Makronaredbe) i u dijaloškom okviru koji se otvori kliknite Uredi(Promijeniti).

Otvorit će se prozor Visual Basic za aplikacije, u kojem ćemo vidjeti programski kod makronaredbe koju smo snimili. Da, dobro ste razumjeli, ovdje možete promijeniti ovaj kod, pa čak i stvoriti novu makronaredbu. Radnje koje smo izveli s tablicom u ovoj lekciji mogu se zabilježiti pomoću automatskog snimanja makronaredbi u Excelu. Ali složenije makronaredbe, s fino podešenim slijedom i logikom akcije, zahtijevaju ručno programiranje.

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

Zamislite da je naša izvorna datoteka s podacima 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 s ažuriranim podacima. Proces otvaranja ove datoteke i uvoza podataka iz nje također se može snimiti u makronaredbi:

1. Otvorite datoteku predloška u koju smo spremili makro − FormatData.
2. Napravite novu makronaredbu pod nazivom učitavanje podataka.
3. Tijekom snimanja makronaredbe učitavanje podataka uvoz podataka iz datoteke data.csv- kao što smo radili u prethodnom dijelu sata.
4. Kada se uvoz završi, zaustavite snimanje makronaredbe.
5. Izbrišite sve podatke iz ćelija.
6. Spremite datoteku kao Excel predložak s omogućenim makronaredbama (XLTM ekstenzija).

Dakle, pokretanjem ovog predloška dobivate pristup dvjema makronaredbama - jedna učitava podatke, druga ih formatira.

Ako se želite upustiti u programiranje, možete kombinirati radnje ove dvije makronaredbe u jednu - jednostavnim kopiranjem koda iz učitavanje podataka na početak koda FormatData.

Ako imate zadatke u Microsoft Excelu koji se ponavljaju, možete snimiti makronaredbu za automatizaciju tih zadataka. Makro je radnja ili skup radnji koje se mogu pokrenuti koliko god puta želite. Kada kreirate makronaredbu, bilježe se klikovi mišem i tipke. Nakon što je makronaredba stvorena, može se modificirati kako bi se napravile male promjene u načinu rada.

Recimo da svaki mjesec izradite izvješće za svog upravitelja računovodstva. Želite formatirati korisnička imena koja su istekla crvenom bojom i primijeniti podebljano oblikovanje. Možete stvoriti makronaredbu koja brzo primjenjuje ove promjene oblikovanja na odabrane ćelije i pokrenuti je.

Postupak

Makro snimanje

Daljnje radnje

Postupak

dodatne informacije

Uvijek možete postaviti pitanje Excel Tech zajednici, zatražiti pomoć u zajednici Answers ili predložiti novu značajku ili poboljšanje na web stranici

Makroi su neizostavni. Makro se može definirati kao slijed naredbi (u nizu), što mogu biti klikovi, pritisci na tipke ili čak male linije koda s naprednijim značajkama. Ove sekvence se zapisuju u VBA modul i izvršavaju prema potrebi.

Glavna prednost makronaredbe je ušteda vremena koju pruža, jer automatizira ono što se mora učiniti ručno u više koraka. Vrlo korisno kada trebate izvršiti isti skup zadataka više puta.

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

• Korištenje Macro Recordera: U ovom postupku Excel sprema svaku radnju koja će biti dio makronaredbe, a zatim snimač makronaredbi te radnje pretvara u naredbe Visual Basica za aplikacije (VBA).
• Korištenje VBA: Ako ste pomalo programer ili čak znate VBA jezik, možete kreirati vlastite makronaredbe. Da biste to učinili, jednostavno upotrijebite VBA uređivač koji je prisutan u najnovijim verzijama Microsoft Excela.

Kao što je ranije spomenuto, postoji mnogo stvari koje možete učiniti s makronaredbama, ali neke od njih se razlikuju od drugih po tome što se češće koriste u svakodnevnom radu. Pogledajte 10 makronaredbi koje će vam pomoći s proračunskim tablicama.

1) Prikaži poruku

Samo zalijepite naredbu MsgBox u svoju makronaredbu. Kao u sljedećem primjeru:

MsgBox "tekst poruke"

Ova linija koda može se umetnuti u bilo koju makronaredbu, što je vrlo korisno u slučajevima kada je potrebno izdati upozorenja korisniku proračunske tablice u kojoj se makro izvršava.

2) Pokrenite makronaredbu kada se list otvori

U nekim slučajevima, makronaredbu je potrebno otvoriti zajedno s radnim listom. U ovom slučaju, samo montirajte makronaredbu s opcijom Auto_Open(). Provjerite primjer

Sub Auto_Open()
MsgBox "Da biste saznali sve o Excelu, idite na www.https://luz.vc/"
kraj pod

U gornjem primjeru, poruka će se prikazati nakon otvaranja proračunske tablice. Ne zaboravite omogućiti makronaredbe u svom Excelu.

3) Radni list s trenutnim datumom i vremenom

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

Sub writeDataEora()
Raspon ("A1") = Sada
kraj pod

4) Izvedite istu radnju za svaku odabranu ćeliju.

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

Prijeđite na doSpeedCell()
Za svaku ćeliju u Selection.Cells
Cell MsgBox
Sljedeći
kraj pod

5) Isto na svim odabranim ćelijama

Istu makronaredbu možete napraviti i prije, 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 = "(!LANG:Bok"!}
kraj pod

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

6) Identifikacija formula u svakoj ćeliji

SubCheckFormula()
Ako raspon("A1") ima formulu = True onda
MsgBox "Postoji formula"
Još
MsgBox "Nije formula"
Završi ako
kraj pod

7) Promijenite boju ćelije pri lebdenju mišem

Također možete pružiti više interaktivnosti s korisnicima proračunskih tablica. U idealnom slučaju, interaktivnost je namijenjena olakšavanju održavanja i vizualizacije informacija. Makronaredba u nastavku primjer je kako vaše tablice mogu prikazati ćelije s istim tonom boje kada miš prođe podatke koji povezuju te ćelije.

Privatni pod radni list_Izmjena izbora (prema vrijednosti cilja kao raspona)

Dima domaća linija kao raspon
Domet
Dim Line2 koliko dugo

Cells.Interior.ColorIndex = xlNone

Redak 2 = Cilj.Red

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

Boji odabranu ćeliju u stupcu 5
Postavi linija = Raspon (ćelije(Cilj.Red, 1), ćelije(Cilj.Red, 5))

s ravnalom
.Interijer.Indeks boja = 12
Završite s

8) Promijenite boje iznutra i fontove

Ova makronaredba mijenja boje unutar i na izvoru ćelija prema slovu ćelija.

SubColorir_interior_letra()
Za N=1 do raspona ("O65536"). Kraj (xlUp).Red

Odaberite raspon ljestvice ("O" i N)
Slučaj "A"
Raspon("O" i N).Interior.Indeks boja = 3
Raspon ("O" i N).Font.Indeks boje = 1

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

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

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

Slučaj Drugi
Raspon("O" i N).Interior.ColorIndex = 6
Raspon ("O" i N).Font.Indeks boje = 4
Kraj Odaberite

Sljedeći N

9) Govoreći makro

Već sam mislio da bi bilo zanimljivo imati proračunsku tablicu koja će sadržavati podatke koje sadrži, upravo to radi ova makronaredba. U primjeru u nastavku, tablica sadrži riječi koje idu od ćelije A1 do A5, ali možete odabrati razmak koji vam najviše odgovara, stoga samo promijenite A1:A5 u kodu u nastavku na koji god razmak želite.

Pod Excel pada ()

Raspon ("A1:A5").