Najkorisniji makroi u excelu. Kako automatizirati rutinske zadatke u Excelu pomoću makroa

Dobar dan

Ovaj članak želim posvetiti tako velikom dijelu MS Excel-a kao što su makroi, odnosno, počnimo od početka i pogledajmo kako napraviti makro u Excelu, čemu služi i kako ga koristiti u svom radu.

Kao što znate iz sopstveno iskustvo, pri radu sa makroom postoji puno „rutine“, odnosno izvode se iste operacije i radnje koje su neophodne za dobijanje rezultata, to može biti popunjavanje iste vrste tabela ili obrazaca, obrada podataka, slično kao blizanci sedmični, mjesečni izvještaji itd. Ali korištenje makronaredbi će vam omogućiti da izvršite ove radnje u automatski način rada, koristeći mogućnosti Excela do maksimuma, bacivši ove rutinske i monotone operacije na moćna ramena Excela. Također, razlog za korištenje makronaredbi može biti dodavanje potrebne mogućnosti, koji još nisu implementirani u standardne karakteristike Excel (na primjer, izlaz, prikupljanje podataka na jednom listu, itd.).

Ako nikada niste čuli za makro, najpreciznija definicija bi bila: to su akcije koje su programirane za određeni niz i napisane u programskom okruženju na jeziku Visual basic za aplikacije (VBA). Pokretanje makronaredbe može se izvršiti više puta i to će prisiliti Excel da izvrši bilo koji slijed radnji koje su nam potrebne, a koje jednostavno ne volimo ili ne želimo raditi ručno. Unatoč velikoj raznolikosti programskih jezika za cijeli kompleks microsoft office VBA je standard i radi u bilo kojoj aplikaciji uredskog paketa.

Dakle, postoje 2 načina da kreirate makro u Excelu:

Kreirajte makro u Excelukoristeći makro snimač

Prvo, hajde da razjasnimo šta je makro snimač i kakve veze ima makro sa njim.

Makro snimač je mali program ugrađen u Excel koji tumači svaku radnju korisnika u jezičkim kodovima VBA programiranje i upisuje u softverski modul komande koje su dobijene tokom procesa rada. Odnosno, ako kreiramo dnevni izvještaj koji nam je potreban s uključenim snimačem makroa, makro snimač će snimati sve u svojim naredbama korak po korak i, kao rezultat, kreirati makro koji će automatski kreirati dnevni izvještaj.

Ova metoda je veoma korisna za one koji nemaju veštine i znanja za rad u VBA jezičkom okruženju. Ali takva jednostavnost izvođenja i snimanja makroa ima svoje nedostatke, kao i svoje prednosti:

• Makro snimač može snimiti samo ono što može dodirnuti, što znači da može snimati radnje samo kada se koriste dugmad, ikone, komande menija i sve slično; takve opcije mu nisu dostupne;
• Ako je greška napravljena tokom perioda snimanja, ona će također biti snimljena. Ali možete koristiti dugme za otkazivanje poslednja akcija, obrišite poslednju komandu koju ste pogrešno napisali u VBA;
• Snimanje u makro snimaču se vrši samo u granicama prozora MS Excel, a ako zatvorite program ili uključite drugi, snimanje će se zaustaviti i više se neće izvoditi.

Da biste omogućili snimanje makroa za snimanje, morate izvršiti sljedeće korake:

Sljedeći korak u radu sa snimačem makroa bit će konfiguriranje njegovih parametara za dalje snimanje makroa; to se može učiniti u prozoru "Snimi makro", Gdje:

• Polje imena makroa- možete napisati ime koje razumete na bilo kom jeziku, ali mora da počinje slovom i ne sadrži znakove interpunkcije ili razmake;
• Polje za prečice na tastaturi- će se koristiti u budućnosti za Brzi početak vaš makro. U slučaju da trebate registrovati novu, ova opcija će biti dostupna u meniju "Alati" - "Makro" - "Makroi" - "Pokreni" ili na kartici "Programer" pritiskom na dugme "Makroi";
• Polje "Sačuvaj u...".— možete odrediti lokaciju na kojoj će makro tekst biti sačuvan (ali neće biti poslan), a postoje 3 opcije:
  • "Ova knjiga"— makro će biti upisan u modul trenutne radne knjige i može se izvršiti samo ako je dat Excel radna sveskaće biti otvoren;
  • "nova knjiga"— makro će biti sačuvan u šablonu na osnovu kojeg se kreira prazan šablon u Excel-u Nova knjiga, što znači da će makro postati dostupan u svim radnim sveskama koje će od sada biti kreirane na ovom računaru;
  • "Personal Macro Book"- je posebna Excel radna knjiga makroa pod nazivom "Personal.xls" i koristi se kao posebna biblioteka za skladištenje makroa. Prilikom pokretanja, makroi iz radne knjige “Personal.xls” se učitavaju u memoriju i mogu se pokrenuti u bilo kojoj radnoj svesci u bilo koje vrijeme.
• Polje "Opis".- ovdje možete opisati šta bi makro trebao raditi i kako, zašto je kreiran i koje funkcije ima, ovo je čisto informativno polje, koje se zove memorija.

Nakon što pokrenete i snimite svoj makro, dovršite sve neophodne radnje, snimanje se može zaustaviti komandom "Zaustavi snimanje" i vaš makro će biti kreiran pomoću snimača makroa.

Kreirajte makro u Exceluu Visual Basic editoru.

U ovoj metodi ćemo pogledati kako napraviti makro u Excelu kroz VBA program editor koji je, kao što sam rekao gore, ugrađen u bilo koju verziju Excel-a.

Pokretanje VBA uređivača programa odvija se drugačije, ovisno o verziji vašeg Excel programa:

• u verzijama programa Excel 2003 i novijim, trebamo u meniju "Usluga", odaberite stavku "makro" i pritisnite "Visual Basic Editor";
• u verzijama programa Excel 2007 i novijim, trebamo na kartici "Programer" pritisnite dugme "Visual Basic Editor". Ako ne pronađete ovu karticu, morate je aktivirati odabirom stavke menija “Datoteka” - “Opcije” - “Prilagodi traku” a u dijaloškom okviru koristite potvrdni okvir da aktivirate karticu "Programer".

U prozoru koji se otvori vidi se interfejs VBA editora, ovde je sve napisano na engleskom i nema lokalizacije, ne morate da ga tražite, već samo prihvatite i radite, pogotovo što je samo nerazumljivo prvo, a onda će sve biti poznato.

Dakle, kako raditi u VBA editoru, gdje se šta čuva i kako napraviti makro u Excelu. Pitanja ove prirode nastaju odmah čim vidite urednika, a sada ćemo ih razmotriti sva.

Svi naši makroi će biti pohranjeni u tzv softverski moduli. U bilo kojoj od knjiga bilo koje verzije Excela, možemo kreirati bilo koju softverski moduli u bilo kojoj količini i stavite sve makroe koje kreiramo u njih. Jedan modul može sadržavati bilo koji broj makroa koje trebate ili kreirate. Moduli su dostupni u prozoru "Istraživač projekta" i nalaze se u gornjem lijevom uglu uređivača makroa (možete ga pozvati i kombinacijom tipki CTRL+R).

Programski moduli u VBA editoru postoje u nekoliko tipova i koriste se za različite opcije i situacije:

Zapravo, sam makro, kada radi u standardnom modulu, izgleda ovako:

Pogledajmo primjer funkcionalnog makroa:

• Svi makroi će nužno početi sa operatorom Sub, nakon čega slijedi ime vašeg makroa i lista argumenata u zagradama. U slučajevima kada nema argumenata, zagrade treba ostaviti prazne;
• Svi makroi moraju završavati sa operatorom End Sub;
• Podaci koji se nalaze između operatera Sub I End Sub, je tijelo makroa koje će se pokrenuti kada se makro pokrene. U primjeru, makro provjerava i, prilikom unosa podataka, pronalazi ga u listi baze podataka i prikazuje vrijednost specificiranu kriterijima.

Kao što vidite, drugu metodu je teže koristiti i razumeti ako nemate iskustva u programiranju uopšte ili posebno u VBA. Vrlo je teško razumjeti i shvatiti koje se komande unose i kako, koje argumente koristi da bi makro automatski počeo da radi svoj posao. Ali onaj ko hoda savladaće put, kako su govorili drevni mudraci, i zato ne treba odustajati, već slediti zapovesti dede Lenjina...

Kreiranje dugmeta za pokretanje makroa na traci sa alatkama

Kao što sam rekao ranije, možete pozvati makro proceduru vruća kombinacija tastere, ali je veoma zamorno pamtiti koja kombinacija je kome dodeljena, tako da bi najbolje bilo da kreirate dugme za pokretanje makroa. Dugmad se mogu kreirati u nekoliko tipova, i to:

Ova metoda je dostupan za bilo koju verziju MS Excel-a i sastoji se u tome da ćemo dugme postaviti direktno na naš radni list kao grafički objekat. Da biste to uradili potrebno vam je:

• U MS Excelu 2003 i starijim idite na meni "Pogled", izaberite "Traka sa alatkama" i pritisnite dugme "obrasci".
• U MS Excel 2007 i novijim verzijama trebate na kartici "Programer" otvori padajući meni "ubaci" i izaberite objekat "dugme".

Nakon svega ovoga, morate nacrtati dugme na svom listu dok držite pritisnutu lijevu tipku miša. Nakon što je proces crtanja završen, automatski će se otvoriti prozor u kojem ćete trebati odabrati makro koji je potreban da se izvrši kada kliknete na vaše dugme.

Kako kreirati prilagođene funkcije u VBA

U principu, kreiranje takozvanih korisnički definiranih funkcija ne razlikuje se mnogo od kreiranja običan makro u standardnom softverskom modulu. Razlika između ovih koncepata je u tome što će makro izvršavati radnje sadržane u njemu s objektima knjige ili lista (to su formule, ćelije itd.), ali korisnička funkcija radi samo s vrijednostima koje prima od nas a ovo su argumenti početni podaci za proračune.

Na primjer, za stvaranje prilagođena funkcija porez na dodatu vrijednost, aka PDV, trebamo otvoriti naš VBA editor i dodati novi modul, izaberite iz menija "ubaci" stav "Modul" i tamo unesite tekst za našu funkciju: Treba napomenuti da je glavna razlika između funkcije i makroa zaglavlje Funkcija zamijenjen Sub i postoji kompletna lista argumenata, u našem primjeru je ovo Summa. Nakon što unesemo naš kod, on će postati dostupan u standardni prozorČarobnjak za funkcije, koji se nalazi u meniju "Formule", stav "Insert Function".
I odaberite kategoriju "Definisano od strane korisnika" u kojoj će biti prikazana naša napisana funkcija "NDS".
Nakon odabira naše funkcije, možete postaviti kursor na ćeliju sa argumentom, koja će sadržavati iznos za koji obračunavamo PDV, sve se dešava isto kao i sa normalna funkcija.
I to je sve za mene! Zaista se tome nadamčlanak o tome kako napraviti makro u Excelu Vama je bilo jasno i korisno. Bio bih veoma zahvalan na vašim komentarima, jer je to pokazatelj čitljivosti i inspiriše me da pišem nove članke! Podijelite ono što ste pročitali sa svojim prijateljima i lajkujte!

Programski makroi Microsoft Excel omogućavaju vam da značajno ubrzate rad sa dokumentima u ovom uređivaču tabela. To se postiže automatizacijom ponavljajućih radnji snimljenih u poseban kod. Pogledajmo kako kreirati makroe Excel program, i kako se mogu uređivati.

Makro se može snimiti na dva načina:

• automatski;
• ručno.

Koristeći prvu opciju, jednostavno snimate određene radnje Microsoft program Excel u kojem radite ovog trenutka vrijeme. Zatim možete pustiti ovaj snimak. Ova metoda je vrlo jednostavna i ne zahtijeva poznavanje koda, ali je njena upotreba u praksi prilično ograničena.

Ručno snimanje makroa, naprotiv, zahtijeva znanje programiranja, budući da se kod kuca ručno s tastature. Ali kod napisan na ovaj način može značajno ubrzati izvršavanje procesa.

Automatsko snimanje makroa

Prije nego počnete automatski snimati makroe, trebate.

Otvara se prozor postavki makro snimanja. Ovdje možete odrediti bilo koje ime makroa ako vam ne odgovara zadani. Glavna stvar je da ime počinje slovom, a ne brojem. Također, naslov ne bi trebao sadržavati razmake. Ostavili smo podrazumevano ime – “Macro1”.

Ovdje, ako želite, možete postaviti kombinaciju tipki koja će, kada se pritisne, pokrenuti makro. Prvi ključ mora biti Ctrl taster, a korisnik samostalno postavlja drugi ključ. Na primjer, postavili smo tipku M kao primjer.

Zatim morate odrediti gdje će makro biti pohranjen. Podrazumevano će biti uskladišten u istoj radnoj svesci (datoteci), ali ako želite, možete podesiti skladištenje u novoj radnoj svesci ili u zasebnoj radnoj svesci makroa. Ostavićemo podrazumevanu vrednost.

U najnižem polju postavki makroa možete ostaviti bilo koji opis makroa koji je prikladan za kontekst. Ali, to nije neophodno.

Kada su sva podešavanja završena, kliknite na dugme “OK”.

Nakon toga, sve vaše radnje u ovoj Excel radnoj svesci (datoteci) biće snimljene u makronaredbi dok ne zaustavite snimanje.

Na primjer, zapišimo najjednostavniju aritmetičku operaciju: dodavanje sadržaja tri ćelije (=C4+C5+C6).

Nakon toga kliknite na dugme "Zaustavi snimanje". Ovo dugme je konvertovano iz dugmeta "Snimi makro" nakon što je snimanje omogućeno.

Pokretanje makroa

Da biste provjerili kako snimljeni makro funkcionira, kliknite u istom bloku alata “Kod” na tipku “Makroi” ili pritisnite kombinaciju tipki Alt+F8.

Nakon toga otvara se prozor sa listom snimljenih makroa. Tražimo makro koji smo snimili, odabiremo ga i kliknemo na dugme „Pokreni“.

Možete to učiniti još jednostavnije i čak ne pozivati ​​prozor za odabir makroa. Sjećamo se da smo zapisali kombinaciju “vrućih tipki”. brzi poziv makro. U našem slučaju, to je Ctrl+M. Ovu kombinaciju kucamo na tastaturi, nakon čega se pokreće makro.

Kao što vidite, makro je izvršio tačno sve radnje koje su ranije snimljene.

Uređivanje makroa

Da biste uredili makro, ponovo kliknite na dugme „Makroi”. U prozoru koji se otvori odaberite potreban makro, i kliknite na dugme „Promeni“.

Otvara se Microsoft Visual Basic (VBE) je okruženje u kojem se vrši uređivanje makroa.

Svaki makro snimak počinje naredbom Sub, a završava naredbom End Sub. Odmah iza naredbe Sub nalazi se naziv makroa. Operator “Range(“…”).Select specificira izbor ćelije. Na primjer, naredba "Raspon("C4").Select odabire ćeliju C4. Operator “ActiveCell.FormulaR1C1” koristi se za snimanje radnji u formulama i za druge proračune.

Pokušajmo malo promijeniti makro. Da biste to učinili, dodajte sljedeći izraz makrou:

Opseg("C3").Odaberi
ActiveCell.FormulaR1C1 = "11"

Izraz “ActiveCell.FormulaR1C1 = “=R[-3]C+R[-2]C+R[-1]C”” bit će zamijenjen sa “ActiveCell.FormulaR1C1 = “= R[-4]C+R [-3 ]C+R[-2]C+R[-1]C"".

Zatvorite uređivač i pokrenite makro kao i prije. Kao što vidite, zbog izmjena koje smo uveli, dodana je još jedna ćelija sa podacima. Takođe je uključen u obračun ukupnog iznosa.

Ako je makro prevelik, može potrajati dosta vremena da se završi. Ali, uvođenjem ručna promjena u kod, možemo ubrzati proces. Dodajte naredbu “Application.ScreenUpdating = False”. To će uštedjeti računarsku snagu i samim tim ubrzati rad. Ovo se postiže tako što se ekran ne osvježava dok su računske radnje u toku. Za nastavak ažuriranja nakon izvršavanja makronaredbe, na kraju pišemo naredbu “Application.ScreenUpdating = True”

Dodajmo i naredbu “Application.Calculation = xlCalculationManual” na početak koda i dodajmo “Application.Calculation = xlCalculationAutomatic” na kraju koda. Na taj način na početku makroa deaktiviramo automatsko ponovno izračunavanje rezultata nakon svake promjene ćelije, a na kraju makroa ga omogućavamo. Na ovaj način, Excel će rezultat izračunati samo jednom, umjesto da ga stalno preračunava, što će uštedjeti vrijeme.

Pisanje makro koda od nule

Napredni korisnici mogu ne samo uređivati ​​i optimizirati snimljene makroe, već i pisati makro kod od nule. Da biste započeli, potrebno je da kliknete na dugme „Visual Basic“, koje se nalazi na samom početku trake programera.

Nakon toga, otvara se poznati prozor VBE editora.

Programer ručno upisuje makro kod.

Kao što vidite, makroi u Microsoft Excel-u mogu značajno ubrzati izvršavanje rutinskih i monotonih procesa. Ali, u većini slučajeva, ručno kodirani makroi su pogodniji za to od automatski snimljenih radnji. Osim toga, makro kod se može optimizirati kroz VBE editor kako bi se ubrzao proces izvršavanja zadatka.

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 Officeu (da, ova funkcija radi isto u mnogim aplikacijama Microsoft paket Ured) je programski kod u programskom jeziku (VBA), sačuvanom unutar dokumenta. Da bi bilo jasnije, Microsoft Office dokument se može uporediti sa HTML stranica, tada je makro analog Javascript-a. Šta Javascript može učiniti s podacima u HTML format, koji se nalazi na web stranici, vrlo je sličan onome što makro može učiniti s podacima u njima Microsoftov dokument Ured.

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 jednostavna tabela 10x20, ispunjen 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 sačuvati Excel dokument u formatu koji podržava makroe. Prvo moramo izbrisati sve podatke iz tabele koju smo kreirali, tj. napravi to od nje 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 desni klik miš po ikoni Označi sve, koji se nalazi na raskrsnici naslova redova i kolona, ​​i od kontekstni meni odaberite stavku Izbriši(Izbriši).

Sada je naš list potpuno očišćen od svih podataka, dok makro ostaje snimljen. Moramo da sačuvamo knjigu kao Excel šablon makro-omogućen, 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 par važne tačkešto se tiče makroa općenito:

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

VBA kod je vrlo velike prilike. Konkretno, može obavljati operacije nad datotekama izvan aktuelni dokument. 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 postavke sigurnost, onda kada otvorite datoteku, na vrhu tabele će se pojaviti upozorenje da je pokretanje makronaredbi 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 tablicom u ovoj lekciji mogu se zapisati pomoću automatsko snimanje makro u Excelu. Ali složeniji makroi, sa fino podešenim redosledom i logikom akcija, zahtevaju ručno programiranje.

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

Zamislite da je naš originalni fajl sa 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 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.

Excel tabele su jedna od najpopularnijih popularne načinečuvanje i sistematizacija informacija. Ako se implementira u Excel makroi, tada se mogućnosti programa mogu proširiti skoro dva puta. Na primjer, makroi pomažu u automatizaciji rutinskih procesa i ubrzavaju monoton rad. Često ih koriste stručnjaci koji prevode izvještaje iz 1C u Excel. Uz pomoć ugrađenog koda, oni se lako mogu svesti na jedan standardni predložak.

Šta su Excel makroi?

Makroi se koriste ne samo u tabelama, već iu cijelom Microsoft Officeu. Oni su kod kreiran pomoću Visual Basica za aplikacije. Ako ste djelimično upoznati sa web programiranjem, onda možete povući analogiju sa Javascriptom. Excel makroi rade otprilike istu stvar koju Javascript radi sa HTML podacima.

Mali dio onoga što makro može učiniti u uredskim aplikacijama:

Poravnajte dokument prema stilu i formatirajte ga;
. obavljati operacije sa numeričkim i tekstualnim podacima;
. potražite pomoć eksterne datoteke(tekst, baze podataka i ostalo);
. kreirajte nove dokumente od nule;
. kombinovati nekoliko radnji i izvršiti ih automatski.

Da biste radili s tablicom, možete je sami kreirati ili preuzeti gotovu verziju s Interneta. Makroe treba preuzimati samo sa pouzdanih lokacija, inače možete uneti virus u Excel.

Kako makroi u Excelu mogu biti opasni?

Oštećivanje datoteka na vašem računaru;
. ukrasti povjerljive podatke.

Virus se unosi tokom rada jednog od kancelarijske aplikacije- Word ili Excel. Nakon što prestanete raditi, oni će početi autonomni rad virusa i zaraze čitav sistem.

Drugi način na koji zlonamjerni makro funkcionira je kao posrednik za uvođenje prijetnji u sistem. U ovom slučaju, to je svojevrsna kapija za uvođenje i generiranje trojanskog softvera. Više ga neće kontrolirati modificirani makro, već operativni sistem, pa stoga prijetnja postaje veća. Takve viruse nije uvijek moguće izliječiti, ponekad morate potpuno promijeniti Windows OS ili HDD PC (u zavisnosti od zadataka koje je haker postavio prilikom pisanja koda).

Možete odabrati bilo koju metodu zaštite od takvih makroa. Najpopularnija opcija je povećanje sigurnosnog nivoa programa Excel, ali u ovom slučaju program možda više neće podržavati vaše originalne korisne makroe. Ne biste trebali vjerovati fajlovima iz Nepoznati izvori, jer se mogu zaraziti. Čak i makro primljen sa računara vašeg kolege može biti opasan. Ako vam je prijatelj dao datoteku za rad koja podržava upotrebu makroa, onda prije nego što dozvolite sistemu da je koristi, pojasnite kako je primio ovaj makro.

Najbolja opcija je samoobrazovanje umjetnost kreiranja makroa za vaše potrebe. U tom slučaju možete u potpunosti jamčiti za njihov kvalitet i sigurnost.

Kako snimiti makroe u Excelu

Najlakši način za kreiranje makroa je programiranje direktno u Excel-u. Za to će vam biti potrebna tabela sa unesenim podacima i malo znanja da biste omogućili snimanje takvog koda u programu.

Kreiranje počinje sa menijem Pogled. U njemu trebate odabrati poziciju “Makroi” i naredbu “Snimi makro”. Zatim morate navesti ime bez razmaka, na primjer, "Format" (ako će kod biti odgovoran za promjenu formata teksta ili ćelija).

Nakon toga će sve vaše radnje biti automatski snimljene i konvertovane u makro kod. Na dnu dokumenta pojavit će se dugme sa malim kvadratom (“Stop”), klikom na koji možete zaustaviti snimanje. Na ovaj način možete automatski snimiti makroe u Excel, čije primjere možete dalje analizirati u procesu učenja.

Kako omogućiti makronaredbe u Excelu

Da biste omogućili snimljeni makro u drugim dokumentima, morate ga sačuvati nakon brisanja tabele. Format dokumenta mora biti postavljen kao XLTM (to je onaj koji podržava makroe).

Kada kasnije pristupite dokumentu da biste omogućili makroe u Excelu, prvo ih morate omogućiti u iskačućem prozoru gornja linija. Zatim morate uvesti podatke koje će uređivati ​​makro iz obične CSV tabele. Kada se uvoz završi, potrebno je da se vratite na meni „Prikaz“, tamo izaberete „Makroi“ i na listi pronađete naziv koda koji ste napravili. Na kraju kliknite na dugme „Pokreni“. Nakon toga ćete vidjeti kako program, prema algoritmu koji ste prethodno naveli, izvodi proračune i formatira dokument.

Kako mogu vidjeti kod makroa?

Svi makroi kreirani u Excelu se pišu automatski, bez ljudske intervencije. Program samostalno sastavlja programski kod, koji se može otvoriti iz tabele pozivanjem pomoću menija.

Da biste to uradili, morate izvoditi naredbe uzastopno:

Proširite karticu "Prikaz";
. otvorite "Makroi";
. izaberite ime potrebnog makroa;
. Pozovite prozor sa njegovim kodom pomoću dugmeta „Promeni“.

U novom dijaloškom okviru možete uređivati ​​i mijenjati makro. Tamo ćete vidjeti kod koji je program snimio automatski, a možete ručno dodati bilo koju radnju. Nakon spremanja informacija, makro će raditi drugačije prilikom uvoza novih podataka.

Kako pokrenuti makro klikom na ćeliju

Recimo da želite da se makro pokrene kada je odabrana ćelija A1. Za odabir u Excel ćelija kada pokrenete makro, morate dodati programski kod prvom modulu:

Privatni pod radni list_Izmjena izbora (prema vrijednosti cilja kao raspona)
Ako je Target.Address = "$A$1" Tada: Pozovite modul1
End Sub

Najbolji vodič koji će vam pomoći da naučite makroe u Excelu je tutorijal. Postoji više sličnih primjera, a za one koji se tek upoznaju sa svim mogućnostima tabele, ovo je Najbolji način naučite u potpunosti koristiti njegovu funkcionalnost.

Gdje možete naučiti raditi sa makroima?

Postoji nekoliko načina da naučite kako kreirati složenije makroe. Moraju se razlikovati po logici i jasno definiranom slijedu radnji. Takvi makroi se mogu kreirati samo ručnim pisanjem VBA koda. Da biste kreirali složene makroe, morat ćete provesti neko vrijeme učeći zamršenost jezika Vizuelno programiranje Basik.

Sada da istražim sve Excel mogućnosti Postoji mnogo specijalnih kurseva koji traju nekoliko sedmica, ali na njih ćete morati putovati Obrazovni centar ili pohađati webinare (što nije uvijek zgodno zbog vremenske razlike). Ali učinkovitiji i jednostavniji način da naučite kako kreirati makroe u Excelu je vodič s primjerima. Možete ga učiti u bilo koje vrijeme koje vam odgovara, a udžbenici korak po korak pomoći će vam da se razvijete različite varijante: od najviše jednostavni kodovi do najsloženijeg. Microsoft Excel je univerzalni uređivač proračunskih tablica koji se može koristiti za rješavanje mnogih složenih problema.

Primjeri

Postoje kodovi koji vam omogućavaju automatizaciju raznih zadataka. Dakle, kao primjer, pogledajmo makro za umetanje reda. izgleda ovako:

Podmakro1()

Makro za isticanje ćelije A1 na svakom listu u aktivnoj radnoj svesci. Ovo takođe uzrokuje pomeranje ekrana.

Sub A1SelectionEachSheet() Dim i As Integer Application.ScreenUpdating = False Za i = 1 na listove.Broj listova(i).Select ActiveWindow.ScrollColumn = 1 ActiveWindow.ScrollRow = 1 Range("a1").Odaberi sljedeće listove(1) .Select Application.ScreenUpdating = True End Sub

Makro za kopiranje trenutnog lista određeni broj puta. Korisno za testiranje nekih makronaredbi - napravio izmjene, provjerio ih na kopiji podataka. Ponestalo nam je kopija — ponovo pokrenite makro

Sub SimpleCopy() Dim i As Integer, j As Integer i = Application.InputBox("Unesite broj kopija trenutnog lista") Application.ScreenUpdating = False Za j = 1 Za i ActiveSheet.Copy after:=Sheets(Sheets .Count) ActiveSheet .Name = "Copy" & j Next j Application.ScreenUpdating = True End Sub

Kreirajte listove s naslovima iz određenog raspona na listu

Sub CreateFromList() Zatamnjena ćelija kao raspon za svaku ćeliju u listovima za odabir.Dodajte nakon:=Listovi(Liste.Broj) ActiveSheet.Name = cell.Value Sljedeća ćelija Kraj pod

Markros za slanje pisma sa zakašnjenjem. Modifikovani makro iz knjige Džona Vokenbaha Profesionalno VBA programiranje

Sub SendLetter() Zatamni OutApp kao objekt Zatamni OutMail kao objekt Postavi OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon On Error GoTo cleanup Postavi OutMail = OutApp.CreateItem(0) U slučaju greške Nastavi Sljedeći sa OutMail .To = " [email protected]" .Subject = "Izvještaj o prodaji" .Prilozi.Dodaj "C:\Test.txt" .Body = "Tekst e-pošte" .DeferredDeliveryTime = Zamijeni(Datum, ".", "/") & " 11:00:00 " .send ".Display da generišete pismo i otvorite ga End With On Greška Idi na 0 Postavi OutMail = Ništa čišćenje: Postavi OutApp = Ništa Kraj Sub

Malo izmijenjeni makro sadržaja od Nikolaja Pavlova.
Ako list “Sadržaj” već postoji u knjizi, makro će od vas tražiti da ga izbrišete. Ako nije, kreira list „Sadržaj“ i umeće veze s nazivima listova

Sub TableOfContent() Zatamnjeni list kao radni list Zatamnjena ćelija kao opseg Dim Odgovor kao cijeli broj Application.ScreenUpdating = False Sa ActiveWorkbook za svaki radni list u ActiveWorkbook.Worksheets If Worksheet.Name = "Sadržaj" Tada Answer = MsgBox("Radna sveska ima list sa imenom Sadržaj. Izbrisati?", vbYesNo) Ako je odgovor = vbNo, onda izađi iz sub Ako je odgovor = vbYes Onda Application.DisplayAlerts = False Worksheet.Delete Application.DisplayAlerts = True End Ako Kraj ako je sljedeći kraj sa listovima (niz (1)).Select Sheets.Add Sheets(1).Name = "Sadržaj" sa ActiveWorkbook za svaki list u ActiveWorkbook.Worksheets If sheet.Name<>"Sadržaj" Zatim postavite ćeliju = Radni listovi(1).Ćelije(list.Indeks, 1) .Radni listovi(1).Hiperveze.Dodaj sidro:=ćelija, Adresa:="", Podadresa:=""" & list .Name & """ & "!A1" cell.Formula = sheet.Name End If Next sheet End With Rows("1:1").Delete Application.ScreenUpdating = True End Sub

Sortiranje listova iz VBA čarobnjaka. Makro takođe sortira skriveni listovi. Neće raditi ako knjiga ima zaštićenu strukturu

Sub SORT_ALL_SHEETS() Application.ScreenUpdating = False: Application.EnableEvents = False Dim iSht As Worksheet, oDict As Object, i%, j% Set oDict = CreateObject("Scripting.Dictionary") " zapamtite stanje vidljivosti svakog lista i uradite sve vidljivo za svaki iSht u ActiveWorkbook.Sheets oDict.Item(iSht.Name) = iSht.Visible: iSht.Visible = True Next With ActiveWorkbook " sortiranje vidljivih listova Za i = 1 Do .Sheets.Count - 1 Za j = i + 1 Na .Sheets.Count Ako je UCase(.Sheets(i).Name) > UCase(.Sheets(j).Name) Onda .Sheets(j).Move Before:=.Sheets(i) Next j Next i End With "vratiti početno stanje vidljivost svakog lista Za svaki iSht u ActiveWorkbook.Sheets iSht.Visible = oDict.Item(iSht.Name) Next Application.EnableEvents = True: Application.ScreenUpdating = True End Sub

Uvezite kolone "Field1" i "Field2" iz lista "Sheet1" Excel fajl“C:\Manager.xls” preko ADODB veze i umetanje sadržaja počevši od ćelije A1 trenutnog lista