Makroi u Excelu - Upute za upotrebu. Mironov

Dobar dan!

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

Kao što znate iz vlastitog iskustva, pri radu s makroom postoji mnogo "rutine", odnosno izvode se iste operacije i radnje koje su potrebne za postizanje rezultata, to može biti popunjavanje iste vrste tabela ili formulare, obrađujući podatke slične blizancima sedmične, mjesečne izvještaje, itd. Ali korištenje makronaredbi omogućit će vam da ove radnje izvodite u automatskom načinu, 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 potrebnih funkcija koje još nisu implementirane u standardnim Excel funkcijama (na primjer, izlaz, prikupljanje podataka na jednom listu, itd.).

Ako nikada niste čuli za makro, onda bi njegova najpreciznija definicija bila sljedeća: to su akcije koje su programirane u određenom slijedu i snimljene u programskom okruženju u jeziku Visual Basic za aplikacije (VBA)... Makro se može pokrenuti više puta i to će natjerati Excel da izvrši bilo koji slijed radnji koje su nam potrebne, što jednostavno ne volimo ili ne želimo raditi ručno. Unatoč velikoj raznolikosti programskih jezika za cijeli Microsoft Office kompleks, VBA je standard i radi u bilo kojoj aplikaciji uredskog paketa.

Dakle, kreiranje makronaredbe u Excelu moguće je na 2 načina:

Kreirajte makro u Excelukoristeći makro snimač

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

Makro snimač To je mali program ugrađen u Excel koji interpretira svaku radnju korisnika u kodovima VBA programskog jezika i upisuje naredbe koje su dobijene u procesu rada u programski modul. Odnosno, ako s uključenim makro snimačem kreiramo dnevni izvještaj koji nam je potreban, tada će makro snimač snimiti 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 ova lakoća 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 u tom duhu, opcije koje mu nisu dostupne;
• U slučaju da je u toku snimanja napravljena greška, ona će također biti snimljena. Ali možete koristiti dugme da poništite poslednju radnju, izbrišete poslednju komandu koju ste pogrešno napisali u VBA;
• Snimanje u makro snimaču vrši se samo unutar granica prozora MS Excel, a u slučaju kada zatvorite program ili uključite drugi, snimanje će se zaustaviti i prestati raditi.

Da biste omogućili snimanje makroa za snimanje, uradite sljedeće:

Sljedeći korak u radu sa snimačem makroa bit će postavljanje njegovih parametara za dalje snimanje makroa, to se može učiniti u prozoru "Snimanje makroa", 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 i razmake;
• okvir za prečice na tastaturi- će se koristiti u budućnosti za brzo pokretanje vašeg makroa. U slučaju da trebate registrovati novu, ova opcija će biti dostupna u meniju "Servis" - "Makro" - "Makroi" - "Pokreni" ili na kartici "Programer" pritiskom na dugme "makroi";
• polje "Sačuvaj u..."- možete odrediti mjesto gdje će se tekst makroa pohraniti (ali neće poslati), a to su 3 opcije:
  • "Ova knjiga"- makro će biti upisan u modul trenutne radne sveske i može se izvršiti samo ako je ova Excel radna sveska otvorena;
  • "nova knjiga"- makro će biti sačuvan u šablonu na osnovu kojeg se kreira prazna nova radna sveska u Excel-u, što znači da će makro biti dostupan u svim radnim sveskama koje će od tog trenutka biti kreirane na ovom računaru;
  • "Personal Macro Book"- je posebna Excel knjiga makronazvana "Personal.xls" i koristi se kao posebna biblioteka za skladištenje makroa. Prilikom pokretanja, makroi iz knjige "Personal.xls" se učitavaju u memoriju i mogu se pokrenuti u bilo kojoj knjizi u bilo koje vrijeme.
• Polje za opis- ovdje možete opisati šta i kako makro treba da radi, zašto je kreiran i koje funkcije ima, ovo je čisto informativno polje koje se zove memorija.

Nakon što pokrenete i snimite svoj makro, nakon što ste izvršili sve potrebne radnje, možete zaustaviti snimanje naredbom 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 Excela.

Uređivač VBA programa se pokreće drugačije, u zavisnosti od verzije vašeg Excel programa:

• u verzijama programa Excel 2003 i novijim, trebamo u meniju "Usluga", izaberite stavku "makro" i pritisnite "Visual Basic Editor";
• u verzijama programa Excel 2007 i novijim, trebamo u kartici "Programer" pritisnite dugme "Visual Basic Editor"... U slučaju da ne pronađete ovu karticu, potrebno je da je aktivirate odabirom stavke menija "Datoteka" - "Opcije" - "Prilagodi traku" i u dijaloškom okviru aktivirajte karticu "Programer".

U prozoru koji se otvori možete posmatrati interfejs VBA editora, ovde je sve napisano na engleskom i nema lokalizatora, ne morate da tražite, već samo prihvatite i radite, pogotovo jer je ovo samo na prvu nerazumljivo , i tada će sve biti poznato.

Dakle, kako da radim u VBA editoru, gde se šta čuva i kako da napravim makro u Excel-u. Pitanja ove prirode nastaju čim vidite urednika i sada ćemo ih sva razmotriti.

Svi naši makroi će biti pohranjeni u tzv softverski moduli... U bilo kojoj od knjiga bilo koje verzije Excel-a možemo kreirati bilo koje programske module u bilo kojoj količini i u njih smjestiti sve makroe koje smo kreirali. 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 levom uglu makro editora (možete ga pozvati i prečicom na tastaturi CTRL + R).

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

Zapravo, sam makro pri radu u standardnom modulu izgleda ovako:

Razmotrimo primjer radnog 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 nedostaju argumenti, zagrade treba ostaviti prazne;
• Bez greške, svi makroi završavaju sa operatorom End Sub;
• Podaci koji se nalaze između operatera Sub i End Sub, je tijelo makroa koji će se pokrenuti kada se makro pokrene. U primjeru, makro provjerava i, prilikom unosa podataka, pronalazi ga na listi baze podataka i prikazuje vrijednost specificiranu kriterijima.

Kao što vidite, drugi metod je teže koristiti i razumeti ako nemate iskustva u programiranju uopšte ili posebno u VBA. Vrlo je teško razumjeti i razumjeti koje naredbe i kako se unose, koje argumente koristi da bi makro počeo da obavlja svoj posao u automatskom režimu. Ali put će savladati onaj koji hoda, kako su rekli drevni mudraci, i zato ne treba odustati, već slijediti zapovijesti Lenjinovog djeda...

Kreiranje dugmeta za pokretanje makroa na traci sa alatkama

Kao što sam ranije rekao, makro proceduru možete pozvati kombinacijom prečaca, ali je vrlo zamorno zapamtiti koja je kombinacija kome dodijeljena, pa je najbolje kreirati dugme za pokretanje makroa. Kreirajte dugmad, moguće nekoliko tipova, i to:

Ova metoda je dostupna za bilo koju verziju MS Excel-a i sastoji se u tome da ćemo dugme dovesti direktno na naš radni list kao grafički objekat. Da biste to učinili, trebate:

• U MS Excelu 2003 i starijim idite na meni "Pogled", izaberite "Traka sa alatkama" i pritisnite dugme "obrasci".
• U MS Excelu 2007 i novijim trebate u 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 završetka procesa crtanja, automatski će se uključiti prozor u kojem ćete morati odabrati makro koji se mora izvršiti 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čnog makroa u standardnom programskom modulu. Razlika između ovih koncepata je u tome što će makro izvršavati radnje koje su mu inherentne s objektima knjige ili lista (to su formule, ćelije itd.), ali prilagođena funkcija radi samo s vrijednostima koje prima od nas i su argumenti početni podaci za proračune.

Na primjer, da kreiramo prilagođenu funkciju za porez na dodatu vrijednost, aka PDV, moramo otvoriti naš VBA editor i dodati novi modul, izabrati 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 popunjena lista argumenata, u našem primjeru jeste Summa... Nakon što unesemo naš kod, on će postati dostupan u standardnom prozoru čarobnjaka 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, u kojoj će se nalaziti iznos za koji obračunavamo PDV, sve se dešava kao i sa običnom funkcijom.
I to je sve za mene! Zaista se tome nadamčlanak o tome kako napraviti makro u Excelu Vama je bilo razumljivo i korisno. Bio bih veoma zahvalan na ostavljenim komentarima, jer je to pokazatelj čitljivosti i inspiracija za pisanje novih članaka! Podijelite ono što ste pročitali sa svojim prijateljima i lajkujte!

Malo ko zna da se prva verzija popularnog Microsoft Excel proizvoda pojavila 1985. godine. Od tada je prošao kroz nekoliko modifikacija i tražen je od strane miliona korisnika širom svijeta. Istovremeno, mnogi rade sa samo malim dijelom mogućnosti ovog procesora proračunskih tablica i ne znaju ni kako bi im život mogao olakšati programiranje u Excelu.

Šta je VBA

Programiranje u Excel-u se izvodi pomoću programskog jezika Visual Basic for Application, koji je originalno ugrađen u najpoznatiji Microsoftov procesor za tabelarne proračune.

Stručnjaci pripisuju relativnu lakoću razvoja njegovim prednostima. Kao što praksa pokazuje, čak i korisnici koji nemaju profesionalne vještine programiranja mogu savladati osnove VBA. Posebnosti VBA uključuju izvršavanje skripte u okruženju kancelarijskih aplikacija.

Nedostatak programa su problemi povezani s kompatibilnošću različitih verzija. Oni su uzrokovani činjenicom da se VBA programski kod odnosi na funkcionalnost koja je prisutna u novoj verziji proizvoda, ali ne i u staroj. Također, nedostaci uključuju pretjerano veliku otvorenost koda za promjenu od strane neovlaštene osobe. Međutim, Microsoft Office kao i IBM Lotus Symphony dozvoljavaju korisniku da šifrira seme i postavi lozinku za njegovo gledanje.

Objekti, kolekcije, svojstva i metode

Ovo su koncepti koje treba da razumeju oni koji će raditi u VBA okruženju. Prije svega, morate razumjeti šta je predmet. U Excelu, ovo je list, radna knjiga, ćelija i opseg. Ovi objekti imaju posebnu hijerarhiju, tj. poslušajte jedni druge.

Glavna je Aplikacija, koja odgovara samom programu Excel. Nakon toga slijede Radne sveske, Radni listovi i Opseg. Na primjer, da biste upućivali na ćeliju A1 na određenom listu, morate navesti stazu zasnovanu na hijerarhiji.

Što se tiče pojma "kolekcija", to je grupa objekata iste klase, koja u zapisu ima oblik ChartObjects. Njegovi pojedinačni elementi su također objekti.

Sljedeći koncept su svojstva. One su neophodna karakteristika svakog objekta. Na primjer, za raspon, to je vrijednost ili formula.

Metode su naredbe koje ukazuju na ono što treba učiniti. Kada pišete kod u VBA, oni moraju biti odvojeni od objekta tačkom. Na primjer, kao što će biti pokazano kasnije, naredba Cells (1,1) .Select se vrlo često koristi prilikom programiranja u Excelu. To znači da trebate odabrati ćeliju s koordinatama

Selection.ClearContents se često koristi s njim. Njegovo izvršenje znači brisanje sadržaja odabrane ćelije.

Kako početi

Zatim morate otići na VB aplikaciju, za koju je dovoljno koristiti kombinaciju tipki "Alt" i "F11". dalje:

• u traci menija koja se nalazi na vrhu prozora, kliknite na ikonu pored ikone programa Excel;
• izaberite Mudule tim;
• spremite klikom na ikonu sa slikom;
• napišite, recimo, skicu koda.

izgleda ovako:

Podprogram ()

„Naš kod

Napominjemo da će red "" Naš kod" biti označen drugom bojom (zelenom). Razlog je u apostrofu na početku reda, što znači da slijedi komentar.

Sada možete napisati bilo koji kod i kreirati novi alat za sebe u VBA Excel-u (pogledajte primjere programa ispod). Naravno, onima koji su upoznati sa osnovama Visual Basica to će biti mnogo lakše. Međutim, čak i oni koji ih nemaju, po želji, moći će se dovoljno brzo naviknuti.

Excel makroi

Ovo ime skriva programe napisane u jeziku Visual Basic za aplikacije. Dakle, programiranje u Excel-u je stvaranje makroa sa pravim kodom. Zahvaljujući ovoj funkciji, Microsoftov procesor tabela se samostalno razvija, prilagođavajući se zahtjevima određenog korisnika. Kada shvatite kako da kreirate module za pisanje makroa, spremni ste da počnete da gledate konkretne primere VBA Excel programa. Najbolje je početi s najosnovnijim kodovima.

Primjer 1

Zadatak: napisati program koji će kopirati vrijednost sadržaja jedne ćelije, a zatim pisati u drugu.

Za ovo:

• otvorite karticu "Prikaz";
• idite na ikonu "Makroi";
• kliknite na "Snimi makro";
• popunite otvoreni formular.

Radi jednostavnosti, ostavite “Macro1” u polju “Macro name” i umetnite, na primjer, hh u polje “Prečica na tastaturi” (to znači da možete pokrenuti program brzom komandom “Ctrl + h”). Pritisnite Enter.

Sada, kada je snimanje makroa već počelo, sadržaj jedne ćelije se kopira u drugu. Vraća se na originalnu ikonu. Kliknite na "Snimi makro". Ova akcija znači kraj programa.

• ponovo idite na red "Makroi";
• izaberite "Makro 1" na listi;
• kliknite na "Pokreni" (ista radnja se pokreće pokretanjem prečice na tastaturi "Ctrl + hh").

Kao rezultat toga, javlja se radnja koja je izvršena tokom snimanja makroa.

Ima smisla vidjeti kako izgleda kod. Da biste to učinili, vratite se na red "Makroi" i kliknite "Promijeni" ili "Enter". Kao rezultat toga, oni se nalaze u VBA okruženju. Zapravo, sam makro kod se nalazi između linija Sub Macro1 () i End Sub.

Ako je kopiranje obavljeno, na primjer, iz ćelije A1 u ćeliju C1, tada će jedan od redova koda izgledati kao Raspon (“C1”). Odaberite. Prevedeno, izgleda kao "Range (" C1 "). Select, drugim rečima, prebacuje se na VBA Excel, na ćeliju C1.

Naredba ActiveSheet.Paste završava aktivni dio koda. To znači upisivanje sadržaja odabrane ćelije (u ovom slučaju A1) u odabranu ćeliju C1.

Primjer 2

VBA petlje vam pomažu da kreirate različite makroe u Excelu.

VBA petlje vam pomažu da kreirate različite makroe. Pretpostavimo da imamo funkciju y = x + x 2 + 3x 3 - cos (x). Potrebno je kreirati makro da biste dobili njegov graf. Ovo se može uraditi samo pomoću VBA petlji.

Za početnu i konačnu vrijednost argumenta funkcije uzmite x1 = 0 i x2 = 10. Osim toga, morate unijeti konstantu - vrijednost za korak promjene argumenta i početnu vrijednost za brojač.

Svi uzorci VBA Excel makroa kreirani su koristeći istu proceduru kao što je gore prikazano. U ovom konkretnom slučaju, kod izgleda ovako:

Podprogram ()

shag = 0,1

Uradi Dok x1< x2 (цикл будет выполняться пока верно выражение x1 < x2)

y = x1 + x1 ^ 2 + 3 * x1 ^ 3 - Cos (x1)

Ćelije (i, 1) .Vrijednost = x1 (vrijednost x1 se upisuje u ćeliju sa koordinatama (i, 1))

Ćelije (i, 2) .Vrijednost = y (vrijednost y se upisuje u ćeliju sa koordinatama (i, 2))

i = i + 1 (brojac je aktivan);

x1 = x1 + shag (argument se mijenja veličinom koraka);

End Sub.

Kao rezultat pokretanja ovog makroa u Excelu, dobijamo dva stupca, od kojih prvi sadrži vrijednosti za x, a drugi za y.

Zatim se graf iscrtava koristeći ih na standardni način za Excel.

Primjer 3

Za implementaciju petlji u VBA Excel 2010, kao iu drugim verzijama, uz već datu Do While konstrukciju, koristi se For.

Razmislite o programu koji kreira kolonu. Svaka ćelija će sadržavati kvadrate broja odgovarajućeg reda. Korištenje konstrukcije For će vam omogućiti da je napišete vrlo kratko, bez korištenja brojača.

Prvo morate kreirati makro kao što je gore opisano. Zatim zapisujemo sam kod. Pretpostavljamo da nas zanimaju vrijednosti za 10 ćelija. Kod izgleda ovako.

Za i = 1 do 10 Dalje

Komanda je prevedena na "ljudski" jezik kao "Ponovi od 1 do 10 u koracima od jedan."

Ako je zadatak dobiti stupac s kvadratima, na primjer, sve neparne brojeve iz raspona od 1 do 11, tada pišemo:

Za i = 1 do 10 korak 1 Sljedeći.

Ovdje je korak korak. U ovom slučaju, jednako je dva. Po defaultu, odsustvo ove riječi u petlji znači da je korak jedan.

Dobijeni rezultati moraju biti sačuvani u ćelijama sa brojem (i, 1). Zatim, na svakom početku ciklusa, sa povećanjem i za veličinu koraka, broj reda će se takođe automatski povećati. Tako će kod biti optimizovan.

Generalno, kod će izgledati ovako:

Podprogram ()

Za i = 1 do 10 korak 1 (možete jednostavno napisati Za i = 1 do 10)

Ćelije (i, 1) .Vrijednost = i ^ 2 (tj. vrijednost kvadrata i je upisana u ćeliju (i, 1))

Dalje (u određenom smislu, igra ulogu brojača i znači još jedan početak petlje)

End Sub.

Ako je sve urađeno ispravno, uključujući snimanje i pokretanje makronaredbe (pogledajte gornje upute), tada će se svaki put kada se pozove dobiti stupac određene veličine (u ovom slučaju koji se sastoji od 10 ćelija).

Primjer 4

U svakodnevnom životu često postoji potreba za donošenjem ove ili one odluke, ovisno o nekom stanju. Ne možete bez njih u VBA Excel-u. Primjeri programa u kojima je odabran dalji tok izvođenja algoritma, a nije unaprijed određen inicijalno, najčešće koriste konstrukciju If… Then (za složene slučajeve) If… Then… END If.

Razmotrimo konkretan slučaj. Pretpostavimo da trebate kreirati makro za Excel tako da je ćelija s koordinatama (1,1) napisana:

1 ako je argument pozitivan;

0 ako je argument null;

-1 ako je argument negativan.

Kreiranje takvog makroa za Excel počinje na standardni način, koristeći prečice Alt i F11. Zatim se piše sljedeći kod:

Podprogram ()

x = Ćelije (1, 1) .Vrijednost (ova komanda dodjeljuje x vrijednost sadržaja ćelije na koordinatama (1, 1))

Ako je x> 0 Tada su ćelije (1, 1) .Vrijednost = 1

Ako je x = 0 Tada su ćelije (1, 1) .Vrijednost = 0

Ako je x<0 Then Cells(1, 1).Value = -1

End Sub.

Ostaje pokrenuti makro i dobiti željenu vrijednost za argument u Excelu.

VBA funkcije

Kao što ste možda primijetili, programiranje u Microsoftovom najpoznatijem procesoru proračunskih tablica nije tako teško. Pogotovo ako naučite kako koristiti VBA funkcije. Ukupno, ovaj programski jezik, kreiran posebno za pisanje aplikacija u Excelu i Wordu, ima oko 160 funkcija. Mogu se podijeliti u nekoliko velikih grupa. To:

• Matematičke funkcije. Primjenjujući ih na argument, dobijate vrijednost kosinusa, prirodnog logaritma, cijelog broja, itd.
• Finansijske funkcije. Zahvaljujući njihovom prisustvu i korišćenju programiranja u Excel-u, možete dobiti efikasne alate za računovodstvene i finansijske obračune.
• Funkcije obrade niza. To uključuje Array, IsArray; LBound; UBound.
• VBA Excel funkcije za string. Ovo je prilično velika grupa. Uključuje, na primjer, funkcije Space za kreiranje stringa s brojem razmaka jednakim cjelobrojnom argumentu ili Asc za prevođenje znakova u ANSI kod. Svi oni imaju široku upotrebu i omogućavaju vam rad s redovima u Excelu, kreirajući aplikacije koje uvelike olakšavaju rad s ovim tabelama.
• Funkcije konverzije tipa podataka. Na primjer, CVar vraća vrijednost argumenta Expression pretvarajući ga u tip podataka Variant.
• Funkcije za rad sa datumima. One značajno proširuju standardne.Na primjer, WeekdayName funkcija vraća naziv (pun ili djelomičan) dana u sedmici po njegovom broju. Tajmer je još korisniji. Daje vam broj sekundi koje su protekle od ponoći do određene tačke u danu.
• Funkcije za pretvaranje numeričkog argumenta u različite sisteme brojeva. Na primjer, Okt ispisuje broj u oktalnom prikazu.
• Funkcije formatiranja. Najvažniji od njih je Format. Vraća Variant s izrazom formatiranim prema uputama datim u opisu formata.
• itd.

Proučavanje svojstava ovih funkcija i njihove primjene značajno će proširiti opseg Excela.

Primjer 5

Pokušajmo prijeći na rješavanje složenijih problema. Na primjer:

Dat je papirni dokument izvještaja o stvarnom nivou troškova preduzeća. Obavezno:

• da razvije svoj predložak pomoću procesora Excel tabela;
• kreirajte VBA program koji će tražiti početne podatke da ih popuni, izvršiti potrebne proračune i popuniti ih u odgovarajuće ćelije šablona.

Razmotrimo jedno od rješenja.

Kreiranje šablona

Sve radnje se izvode na standardnom listu u Excel-u. Slobodne ćelije su rezervisane za unos podataka o nazivu preduzeća potrošača, visini troškova, njihovom nivou i prometu. Budući da broj kompanija (kompanija) za koje se sastavlja izvještaj nije fiksan, ćelije za unos vrijednosti na osnovu rezultata i punog imena stručnjaka nisu unaprijed rezervirane. Radnom listu je dat novi naziv. Na primjer, "Օ izvještaji".

Varijable

Da biste napisali program za automatsko popunjavanje šablona, ​​morate odabrati oznake. Oni će se koristiti za varijable:

• NN - broj tekućeg reda tabele;
• TP i TF - planirani i stvarni promet;
• SF i SP - stvarni i planirani troškovi;
• IP i IF - planirani i stvarni nivo troškova.

Označimo istim slovima, ali sa "prefiksom" Itog, akumulaciju ukupnog iznosa za ovu kolonu. Na primjer, ItogTP - odnosi se na kolonu tabele pod nazivom „Projektovani promet“.

Rješavanje problema korištenjem VBA programiranja

Koristeći uvedene oznake dobijamo formule za odstupanja. Ako želite izračunati u%, imamo (F - P) / P * 100, a ukupno - (F - P).

Rezultate ovih proračuna najbolje je unijeti direktno u odgovarajuće ćelije u Excel tabeli.

Za činjenične i prognozirane zbrojeve dobijaju se pomoću formula ItogP = ItogP + P i ItogF = ItogF + F.

Za odstupanja koristite = (ItogF - ItogP) / ItogP * 100, ako se obračun vrši u procentima, a u slučaju ukupne vrijednosti - (ItogF - ItogP).

Rezultati se, opet, odmah upisuju u odgovarajuće ćelije, tako da nema potrebe da ih dodjeljujete varijablama.

Prije pokretanja kreiranog programa potrebno je da sačuvate radnu svesku, na primjer, pod imenom "Report1.xls".

Dugme "Kreiraj tabelu za izvještavanje" potrebno je pritisnuti samo 1 put nakon unosa informacija zaglavlja. Trebalo bi da znate i druga pravila. Konkretno, dugme "Dodaj red" mora se pritisnuti svaki put nakon unosa vrednosti za svaku vrstu aktivnosti u tabelu. Nakon što unesete sve podatke, potrebno je da kliknete na dugme "Završi", a zatim se prebacite na prozor "Excel".

Sada znate kako riješiti Excel zadatke pomoću makronaredbi. Mogućnost korištenja vba excel-a (pogledajte primjere programa iznad) može biti potrebna i za rad u okruženju trenutno najpopularnijeg uređivača teksta "Word". Konkretno, možete kreirati dugmad menija pisanjem, kao što je prikazano na samom početku članka, ili pisanjem koda, zahvaljujući kojem se mnoge operacije nad tekstom mogu izvršiti pritiskom na dežurne tipke ili preko kartice Pogled i Makroa ikona.

Priručnik sadrži makronaredbe o sljedećim temama:
Pokretanje makronaredbe sa traženjem ćelije.
Pokrenite makro kada otvarate radnu svesku.
Pokrenite makro kada unesete u ćeliju "2".
Pokretanje makroa kada pritisnete "Enter".
Dodajte svoju karticu "Dodaci" (format ćelije) na ploču.
Rad sa fajlovima (tj. razmena podataka sa TXT, RTF, XLS, itd.).
Provjera postojanja datoteke na navedenoj putanji.
Potražite željeni fajl.
Automatizacija brisanja fajlova.
Slobodan tekst na statusnoj traci.
Vraćanje statusne trake.
Linija koja se kreće u statusnoj traci.

Brzo promijenite naslov prozora.
Promjena naslova prozora (sakrivanje naziva datoteke).
Vraća se na originalni naslov.
Šta je trenutno otvoreno.
Rad sa tekstualnim fajlovima.
Pisanje i čitanje tekstualne datoteke.
Obrada više tekstualnih datoteka.
Određivanje kraja reda tekstualne datoteke.
Kopiranje iz tekstualne datoteke u Excel.

Kopirajte sadržaj u tekstualnu datoteku.
Izvezite podatke u txt.
Izvoz podataka u html.
Uvoz podataka koji zahtijevaju više od 256 stupaca.
Napravite rezervne kopije vrijednih datoteka.
Brojanje broja otvaranja datoteka.
Izlaz putanje datoteke do aktivne ćelije.
Kopiranje sadržaja RTF datoteke u Excel.
Kopiranje podataka iz zatvorene radne knjige.
Ekstrahiranje podataka iz zatvorene datoteke.
Potražite riječ u datotekama.
Kreirajte tekstualnu datoteku i unesite tekst u datoteku.
Kreirajte tekstualnu datoteku i unesite tekst (detekcija kraja datoteke).
Kreirajte Word dokumente iz Excel tabele.
Naredbe za kreiranje i brisanje direktorija.
Preuzimanje trenutnog direktorija.
Promjena imenika.

Pogledajte sve datoteke u direktoriju.
Pogledajte sve datoteke u direktoriju.
Microsoft Excel radni prostor.
Radna sveska.
Broj naziva radnih knjiga.
Zaštita radne knjižice.
Zabrana štampanja knjige.
Otvaranje knjige (ili tekstualnih datoteka).
Otvaranje knjige i dodavanje teksta u ćeliju A1.
Koliko je knjiga otvoreno.
Zatvaranje svih knjiga.
Zatvaranje radne sveske samo kada je ispunjen uslov.
Čuva radnu svesku pod imenom koje predstavlja trenutni datum.
Da li je radna sveska sačuvana.
Napravite knjigu sa jednim listom.
Kreirajte knjigu.
Uklanjanje nepotrebnih imena.
Brza reprodukcija radne sveske.
Sortiraj listove.
Pronalaženje maksimalne vrijednosti na svim listovima knjige.
Radni list.
Provjera da li je radni list zaštićen.
Lista sortiranih listova.
Kreirajte novi list.
Kreirajte novi list.
Uklanjanje listova u zavisnosti od datuma.
Kopiranje lista u knjizi.
Kopiranje lista u novu radnu svesku (kreiranu).
Premjestite list u knjizi.
Premjestite više listova u novu radnu svesku.
Zamijenite postojeći fajl.
"Prelistavanje" knjige.
Umetnite zaglavlje i podnožje s nazivom knjige, lista i trenutnim datumom.
Da li list postoji.
Da li list postoji.
Prikazuje broj listova u aktivnoj radnoj svesci.
Prikazuje broj listova u aktivnoj radnoj svesci kao hiperveze.
Prikazuje nazive aktivnih listova jedan po jedan.
Prikazuje naziv i brojeve listova trenutne knjige.
Učinite list nevidljivim.
Koliko stranica ima na svim listovima?
Ćelija i opseg (kolone i redovi).
Kopirajte redove na drugi list.
Kopirajte kolone na drugi list.
Broji broj ćelija koje sadrže navedene vrijednosti.
Broji broj ćelija u rasponu koje sadrže navedene vrijednosti.
Broji broj vidljivih ćelija u rasponu.
Određuje broj ćelija u rasponu i zbir njihovih vrijednosti.
Brojanje broja ćelija.
Automatsko ponovno izračunavanje podataka tablice prilikom promjene vrijednosti.
Unošenje podataka u ćelije.
Unošenje podataka pomoću formula.
Serijski unos podataka.
Unos tekstualnih podataka u ćelije.
Izlaz u ćelije naslova knjige, lista i broja listova.
Uklanjanje praznih redova.
Uklanjanje praznih redova.
Uklanjanje praznih redova.
Brisanje reda uslovno.
Uklanjanje skrivenih linija.
Uklonite korištene skrivene linije ili linije s nultom visinom.
Uklanjanje duplikata pomoću maske.

Odabire raspon iznad trenutne ćelije.
Odaberite ćeliju i unesite broj.
Isticanje negativnih vrijednosti.
Dodjela raspona i korištenje apsolutnih adresa.

Odabir ćelija razmakom.
Odabir više raspona.
Kretanje kroz ćelije.
Pronalazi najbližu praznu ćeliju u koloni.
Pronalaženje maksimalne vrijednosti.
Pretražite i zamijenite po uzorku.
Potražite vrijednost i prikažite rezultat u zasebnom prozoru.

Pretraživanje s isticanjem pronađenih podataka.
Traži po stanju u rasponu.
Pronalazi posljednju nepraznu ćeliju u rasponu.
Pronađite zadnju nepraznu ćeliju u koloni.
Pronađite zadnju nepraznu ćeliju u nizu.
Pronalazi plavu ćeliju u rasponu.
Pronalazi negativnu vrijednost u rasponu i ističe plavom bojom.
Pronalazi postojanje vrijednosti u stupcu.
Tražite podudaranja u rasponu.
Potražite ćeliju u rasponu.
Potražite ćeliju u rasponu.
Pronalaženje približne vrijednosti u rasponu.
Pronalazi početak i kraj raspona koji sadrži podatke.
Potražite početak podataka.
Automatska zamjena vrijednosti.
Brzo popunjavanje opsega (niza).
Punjenje u intervalima (niz).
Popunjavanje navedenog raspona (niza).
Popunjavanje opsega (niz).
Izračunavanje zbira prvih vrijednosti raspona.
Postavljanje u ćeliju elektronskog sata.
"Alarm".
Dekoracija gornje i donje granice raspona.
Aktivna adresa ćelije.
Koordinate aktivne ćelije.
Formula aktivnih ćelija.
Dobijte formulu iz ćelije.
Tip podataka ćelije.
Prikazuje adresu kraja opsega.
Dobivanje informacija o odabranom rasponu.
Uzmite riječ sa 13 znakova u ćeliji.
Kreiranje promjenjive liste (tabele).
Provjera prazne vrijednosti.
Presjek ćelija.
Pomnožite odabrani raspon sa.
Simultano množenje svih podataka u opsegu.
Podjela raspona po.
Kvadrira svaku ćeliju u rasponu.
Sumirajte podatke samo vidljivih ćelija.
Zbir ćelija sa numeričkim vrijednostima.
Prilikom zbrajanja, kursor je unutar opsega.

Obračun kamate u zavisnosti od iznosa.
Obračun kamate u zavisnosti od iznosa.
Sažeti primjer obračuna provizije.
Pokret dometa.
Pomak od odabrane ćelije.
Prođite kroz ćelije niz kolonu.
Kreirajte ispunu za raspon.
Odabir parametra ćelije.
Podjela raspona.
Kombiniranje podataka o rasponu.
Kombiniranje podataka o rasponu.
Saznajte maksimalnu kolonu ili red.
Ograničavanje mogućih vrijednosti raspona.
Testiranje raspona brzine čitanja i pisanja.
Otvorite MsgBox kada odaberete ćeliju.
Sakrivanje reda.
Sakrivanje više linija.
Skrivanje kolone.
Sakrivanje više kolona.
Sakrij red po imenu ćelije.
Skrivanje više redova po adresama ćelija.
Sakrij kolonu po imenu ćelije.
Skrivanje više kolona po adresama ćelija.
Ćelija treperi.
Rad sa bilješkama.
Prikazuje sve bilješke na radnom listu.
Funkcija izdvajanja komentara.
Spisak napomena zaštićenih listova.

Spisak napomena u posebnoj listi.
Spisak napomena u posebnoj listi.

Brojanje broja nota.
Brojanje nota.
Odaberite ćelije s bilješkama.
Prikazuje sve bilješke.
Promijenite boju bilješki.
Dodavanje bilješki.
Dodavanje napomena u opseg uslovno.
Prijenos komentara u i iz ćelije.

Prijenos vrijednosti iz ćelije u komentar.
Prilagođeni jezičci na vrpci.
Dodatak alatnoj traci.
Dodavanje dugmeta na traku sa alatkama.
Panel sa jednim dugmetom.
Panel sa dva dugmeta.
Kreirajte panel sa desne strane.

Kreiranje prilagođenog menija (opcija 1).
Kreiranje prilagođenog menija (opcija 2).
Kreiranje prilagođenog menija (opcija 3).
Kreiranje prilagođenog menija (opcija 4).
Kreiranje prilagođenog menija (opcija 5).
Kreiranje prilagođenog menija (opcija 6).
Kreiranje liste stavki u glavnom meniju Excel.
Kreiranje liste stavki kontekstnog menija.
Prikazuje traku sa alatkama pod određenim uslovima.
Skrivanje i prikazivanje alatnih traka.
Kreirajte opis za moje dugmad.
Kreira meni na osnovu podataka sa radnog lista.
Kreiranje kontekstnog menija.
Zaključavanje kontekstnog menija.
Dodavanje komande u meni Alati.
Dodavanje komande u meni Pogled.
Kreiranje panela sa listom.
Crtani film sa asistentom u glavnoj ulozi.
Dopunjavanje pomoćnika tekstom, naslovom, dugmetom i ikonom.
Nove opcije pomoćnika.
Pomoću pomoćnika odaberite boju ispune.
DIALOGUE WINDOWS.
Funkcija INPUTBOX (putem unosa vrijednosti).
Pregled poziva.
Konfiguriranje unosa podataka u dijaloškom okviru.

Otvara dijaloški okvir (Otvori datoteku).
Otvara dijaloški okvir (Štampaj).
Ostali dijaloški okviri.
Pozivanje pretraživača iz Excel-a.
Dijalog za unos podataka.
Okvir za dijalog postavki fonta.
Zadane vrijednosti.
Formatiranje teksta. Stolovi. OBRUBE I PUNJENJE.
Prikazuje listu dostupnih fontova.
Odabir svih brojeva iz teksta.
Veliko slovo samo na početku teksta.
Brojanje broja ponavljanja teksta za pretragu.
Odaberite proizvoljan element iz teksta.
Prikaži tekst od pozadi prema naprijed.
Tekst na engleskom - velikim slovima.
Pokretanje tabele simbola iz Excel-a.
informacije o korisniku, računaru, štampaču itd.
Dobiti korisničko ime.
Rezolucija ekrana monitora.
Dobijte informacije o štampaču koji koristite.
Pogledajte informacije o diskovima na vašem računaru.
USERFORMS.
DIJAGRAMI.
Izrada dijagrama pomoću makroa.
Pohranjivanje dijagrama u posebnu datoteku.
Izgradnja i brisanje dijagrama pritiskom na jedno dugme.
Prikazuje listu dijagrama u posebnom prozoru.
Primjena nasumične palete boja.
Učinak transparentnosti grafikona.
Napravite grafikon na osnovu podataka iz više radnih listova.
Kreirajte oznake za podatke grafikona.
RAZLIČITI PROGRAMI.
Program za sastavljanje ukrštenih reči.
Kreirajte omot DVD-a.
Igra minskog polja.
Igra "Pogodi životinju".
Proračun na osnovu ćelija određene boje.
OSTALE FUNKCIJE I MAKROSI.
Pozivanje funkcijskih tipki.
Izračunavanje aritmetičke sredine.
Pretvaranje brojeva u novac.
Potražite najbliži ponedjeljak.
Računajući broj punih godina.
Izračun ponderisanog prosjeka.
Pretvorite broj mjeseca u njegov naziv.
Korištenje relativnih veza.
Pretvorite Excel tabelu u HTML format.
Generator slučajnih brojeva.
Slučajni brojevi su zasnovani na rasponu.
Primjena funkcije bez unošenja u ćeliju.
Brojanje imenovanih objekata.
Uključite AutoFilter pomoću makroa.
Izrada puzeće linije.
Kreiranje trkaće slike.
Rotirajući autooblici.
Pozivanje tabele boja.
Kreiranje kalkulatora.
Deklinacija prezimena, imena i patronima.
DATUM I VRIJEME.
Prikaz datuma i vremena.
Prikaz datuma i vremena.
Dobivanje sistemskog datuma.
Izvucite datum i sate.
Funkcija DateFull Nije navedena verzija MS Officea koja se koristi.

Makro za isticanje ćelije A1 na svakom listu u aktivnoj radnoj svesci. Ovo takođe pomera ekran.

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

Makro za kopiranje trenutnog lista određeni broj puta. Korisno za testiranje nekih makronaredbi - izvršili ste izmjene, provjerili kopiju podataka. Nema kopija - ponovo pokrenite makro

Sub SimpleCopy () Dim i Kao cijeli broj, j As Integer i = Application.InputBox Application.ScreenUpdating = False Za j = 1 do i ActiveSheet.Copy nakon: = 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 opseg za svaku ćeliju u listovima za selekciju.Dodajte nakon: = listovi (broj listova) ActiveSheet.Name = ćelija.Vrijednost Sljedeća ćelija Kraj pod

Odloženo slanje pošte markros. Modificiran makro iz knjige Johna Walkenbacha Profesionalno VBA programiranje

Sub Email () Zatamniti OutApp kao objekt Zatamniti OutMail kao objekt Set OutApp = CreateObject ("Outlook.Application") OutApp.Session.Logon On Error GoTo cleanup Postavi OutMail = OutApp.CreateItem (0) U slučaju greške Nastavi Sljedeće s OutMail .To = " [email protected]".Subject =" Izvještaj o prodaji ".Attachments.Add" C: \ Test.txt ".Body =" Tijelo e-pošte ".DeferredDeliveryTime = Zamijeni (Datum,". "," / ") &" 11:00:00 " .send" .Prikaži za generiranje poruke i otvaranje.

Malo izmijenjeni makro sadržaja od Nikolaja Pavlova.
Ako list "Sadržaj" već postoji u knjizi, makro predlaže da ga izbrišete. Ako ne, kreira se list "Sadržaj" i umeće veze s nazivima listova

Sub TableOfContent () Zatamnjeni list kao radni list Zatamnjena ćelija kao raspon 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 pod nazivom Sadržaj. Izbrisati ga? ", 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 )). Odaberite Sheets.Add Sheets (1) .Name = "Table of Contents" 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.Dodajte sidro: = ćelija, Adresa: = "", Podadresa: = "" "& list .Name & "" "&"! A1 "cell.Formula = sheet.Name End Ako se sljedeći list završava sa redovima (" 1: 1 "). Izbriši Application.ScreenUpdating = True End Sub

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

Sub SORT_ALL_LISTS () Application.ScreenUpdating = False: Application.EnableEvents = False Dim iSht As Worksheet, oDict As Object, i%, j% Set oDict = CreateObject ("Scripting.Dictionary") "zapamti stanje vidljivosti svakog lista i uradi sve vidljivo za svaki iSht u ActiveWorkbook.Sheets oDict.Item (iSht.Name) = iSht.Visible: iSht.Visible = True Next With ActiveWorkbook "sortirajte vidljive listove Za i = 1 Do .Sheets.Count - 1 Za j = i + 1 U .Sheets.Count Ako UCase (.Sheets (i) .Name)> UCase (.Sheets (j) .Name) Zatim .Sheets (j) .Move Before: =. Sheets (i) Next j Next i End With " vrati prvobitno stanje vidljivosti svakog od listova za svaki iSht u ActiveWorkbook.Sheets iSht.Visible = oDict.Item (iSht.Name) Sljedeća Application.EnableEvents = True: Application.ScreenUpdating = True End Sub

Uvoz kolona "Field1" i "Field2" iz lista "Sheet1" Excel datoteke "C: \ Manager.xls" putem ADODB veze i lijepljenje sadržaja počevši od ćelije A1 trenutnog lista

Da biste automatizirali zadatke koji se ponavljaju, možete snimiti makro koristeći Macro Recorder u programu Microsoft Excel. Zamislite da imate datume u nasumičnim formatima i želite primijeniti jedan format na sve. Ovo se može uraditi pomoću makroa. Možete snimiti makro koristeći format koji želite i reproducirati makro ako je potrebno.

Kada snimite makro, sve što radite se snima kao Visual Basic for Applications (VBA) kod. Ove radnje mogu uključivati ​​unos teksta ili brojeva, odabir ćelija ili komandi na traci ili menijima, formatiranje ćelija, redova ili kolona, ​​pa čak i uvoz podataka iz vanjskog izvora kao što je Microsoft Access. Visual Basic za aplikacije (VBA) je dio programskog jezika Visual Basic. Dostupan je u većini Office aplikacija. Iako vam VBA omogućava automatizaciju procesa u Office aplikacijama i između njih, ne morate biti vešti u programiranju ili poznavati VBA jezik jer će se makro snimač pobrinuti za sve što trebate da uradite.

Važno je znati da kada snimite makro, gotovo sve što radite se evidentira. Stoga, ako napravite grešku, kao što je klik na pogrešno dugme, snimač makroa će zabilježiti tu radnju. U tom slučaju možete prepisati cijeli niz ili promijeniti VBA kod. Stoga, prije snimanja procesa, trebali biste ga dobro razraditi. Što preciznije snimite sekvencu, to će makro efikasnije raditi.

Developer koji je podrazumevano skriven, pa ga prvo morate omogućiti. Za više informacija pogledajte članak Prikaz kartice za programere.

Makro snimanje

U kartici Developer kliknite Makroi da vidite makroe povezane sa radnom sveskom. Alternativno, možete pritisnuti tipke ALT + F8... Ovo će otvoriti dijaloški okvir Makro.

pažnja:

Rad sa snimljenim kodom u Visual Basic Editor-u (VBE)

Uz Visual Basic Editor (VBE), možete dodati vlastite varijable, kontrolne strukture i druge elemente snimljenom kodu koje snimač makroa ne podržava. Budući da snimač makroa bilježi gotovo svaki korak koji se dogodi tokom snimanja, možda ćete također morati ukloniti nepotreban kod. Pregledanje snimljenog koda je odličan način da naučite kako programirati u VBA ili usavršiti svoje vještine.

Za primjer izmjene snimljenog koda pogledajte Početak rada s VBA u Excel-u.

Makro snimanje

Korisno je znati sljedeće prije snimanja makronaredbi:

Makro napisan za rad s Excel rasponom će se izvoditi samo na ćelijama u tom rasponu. Stoga, ako dodate novu liniju u raspon, makro se neće primijeniti na njega.

Ako trebate snimiti dugi niz zadataka, preporučujemo korištenje nekoliko manjih makroa.

Makro također može sadržavati zadatke koji nisu povezani s Excelom. Makro proces može obuhvatiti druge Office aplikacije i druge programe koji podržavaju Visual Basic za aplikacije (VBA). Na primjer, možete snimiti makro koji prvo ažurira tabelu u Excelu, a zatim otvara Outlook da biste je poslali e-poštom.

VBA makroi i alati se nalaze na kartici Developer koji je podrazumevano skriven, pa ga prvo morate omogućiti.

Molimo odaberite Excel > Opcije> Traka i panel.

Da biste snimili makro, slijedite upute u nastavku.

Rad sa makroima snimljenim u Excel-u

U kartici Developer kliknite Makroi da vidite makroe povezane sa radnom sveskom. Ovo će otvoriti dijaloški okvir Makro.

Bilješka: Makroi se ne mogu otkazati. Prije pokretanja snimljenog makroa po prvi put, sačuvajte ili kreirajte kopiju radne knjige kako biste spriječili neželjene promjene. Ako niste zadovoljni rezultatima izvršavanja makroa, možete zatvoriti radnu svesku bez spremanja.

U nastavku pogledajte više informacija o radu sa makroima u programu Excel.

Dodatne informacije

Uvijek možete postaviti pitanje Excel tehničkoj zajednici, zatražiti pomoć u zajednici Answers ili predložiti novu funkciju ili poboljšanje na web stranici.