vsebina
Excel ima močno, a hkrati zelo redko uporabljeno možnost ustvarjanja samodejnih zaporedij dejanj z uporabo makrov. Makro je idealen izhod, če imate opravka z isto vrsto naloge, ki se večkrat ponavlja. Na primer obdelava podatkov ali oblikovanje dokumenta po standardizirani predlogi. V tem primeru ne potrebujete znanja programskih jezikov.
Vas že zanima, kaj je makro in kako deluje? Potem pogumno naprej – potem bomo korak za korakom skupaj z vami opravili celoten postopek ustvarjanja makra.
Kaj je makro?
Makro v Microsoft Officeu (da, ta funkcija deluje enako v mnogih aplikacijah paketa Microsoft Office) je programska koda v programskem jeziku Visual Basic za aplikacije (VBA), ki je shranjen v dokumentu. Da bi bilo bolj jasno, lahko dokument Microsoft Office primerjamo s stranjo HTML, nato pa je makro analog Javascripta. Kaj lahko Javascript naredi s podatki HTML na spletni strani, je zelo podobno temu, kar lahko naredi makro s podatki v dokumentu Microsoft Office.
Makri lahko naredijo skoraj vse, kar želite v dokumentu. Tukaj je nekaj izmed njih (zelo majhen del):
- Uporabite sloge in oblikovanje.
- Izvedite različne operacije s številskimi in besedilnimi podatki.
- Uporabite zunanje vire podatkov (datoteke baze podatkov, besedilni dokumenti itd.)
- Ustvarite nov dokument.
- Naredite vse našteto v poljubni kombinaciji.
Ustvarjanje makra – praktični primer
Za primer vzemimo najpogostejšo datoteko CSV. To je preprosta tabela 10 × 20, napolnjena s številkami od 0 do 100 z naslovi za stolpce in vrstice. Naša naloga je spremeniti ta nabor podatkov v pregledno oblikovano tabelo in ustvariti vsote v vsaki vrstici.
Kot že rečeno, je makro koda, napisana v programskem jeziku VBA. Toda v Excelu lahko ustvarite program, ne da bi napisali vrstico kode, kar bomo storili prav zdaj.
Če želite ustvariti makro, odprite Poglej (Vrsta) > Makri (Makro) > Snemaj makro (makro posnetek...)
Poimenujte svoj makro (brez presledkov) in kliknite OK.
Od tega trenutka se zabeležijo VSA vaša dejanja z dokumentom: spremembe celic, pomikanje po tabeli, celo spreminjanje velikosti okna.
Excel signalizira, da je način snemanja makrov omogočen na dveh mestih. Najprej na jedilniku Makri (Makri) – namesto niza Snemaj makro Prikaže se vrstica (Snemanje makra ...). Ustavi snemanje (Ustavi snemanje).
Drugič, v spodnjem levem kotu okna Excel. Ikona stop (majhen kvadrat) pomeni, da je omogočen način snemanja makrov. Če kliknete nanjo, se snemanje ustavi. Nasprotno, ko način snemanja ni omogočen, je na tem mestu ikona za omogočanje snemanja makrov. Klik nanj bo dal enak rezultat kot vklop snemanja prek menija.
Zdaj, ko je način snemanja makrov omogočen, pojdimo k naši nalogi. Najprej dodajmo glave za povzetek podatkov.
Next, enter the formulas in the cells in accordance with the names of the headings (variants of the formulas for the English and versions of Excel are given, cell addresses are always Latin letters and numbers):
- =SUM(B2:K2) or =SUM(B2:K2)
- =POVPREČJE(B2:K2) or =СРЗНАЧ(B2:K2)
- =MIN(B2:K2) or =MIN(B2:K2)
- =MAX(B2:K2) or =MAX(B2:K2)
- =MEDIAN(B2:K2) or =MEDIAN(B2:K2)
Zdaj izberite celice s formulami in jih kopirajte v vse vrstice naše tabele tako, da povlečete ročico za samodejno izpolnjevanje.
Ko končate ta korak, mora imeti vsaka vrstica ustrezne vsote.
Nato bomo povzeli rezultate za celotno tabelo, za to naredimo še nekaj matematičnih operacij:
oz.:
- =SUM(L2:L21) or =SUM(L2:L21)
- =POVPREČJE(B2:K21) or =СРЗНАЧ(B2:K21) – za izračun te vrednosti je potrebno natančno vzeti začetne podatke tabele. Če vzamete povprečje povprečij za posamezne vrstice, bo rezultat drugačen.
- =MIN(N2:N21) or =MIN(N2:N21)
- =MAX(O2:O21) or =MAX(O2:O21)
- =MEDIAN(B2:K21) or =MEDIAN(B2:K21) – upoštevamo uporabo začetnih podatkov tabele iz zgoraj navedenega razloga.
Zdaj, ko smo končali z izračuni, naredimo nekaj oblikovanja. Najprej nastavimo enako obliko prikaza podatkov za vse celice. Izberite vse celice na listu, za to uporabite bližnjico na tipkovnici Ctrl +ali kliknite na ikono Izberi vse, ki se nahaja na presečišču naslovov vrstic in stolpcev. Nato kliknite Slog vejice (Razmejena oblika). Domov (domov).
Nato spremenite videz naslovov stolpcev in vrstic:
- Krepki slog pisave.
- Sredinska poravnava.
- Barvno polnjenje.
In končno, nastavimo obliko vsot.
Takole naj bi izgledalo na koncu:
Če vam vse ustreza, prenehajte snemati makro.
čestitke! Pravkar ste sami posneli svoj prvi makro v Excelu.
Za uporabo ustvarjenega makra moramo Excelov dokument shraniti v formatu, ki podpira makre. Najprej moramo iz tabele, ki smo jo ustvarili, izbrisati vse podatke, torej narediti prazno predlogo. Dejstvo je, da bomo v prihodnosti pri delu s to predlogo vanjo uvozili najnovejše in ustrezne podatke.
Če želite počistiti vse celice iz podatkov, z desno miškino tipko kliknite ikono Izberi vse, ki se nahaja na presečišču naslovov vrstic in stolpcev, in v kontekstnem meniju izberite Brisanje (Izbriši).
Zdaj je naš list popolnoma očiščen vseh podatkov, medtem ko makro ostane posnet. Delovni zvezek moramo shraniti kot Excelovo predlogo, ki podpira makre in ima pripono XLTM.
Pomembna točka! Če shranite datoteko s pripono XLTX, potem makro v njem ne bo deloval. Mimogrede, delovni zvezek lahko shranite kot predlogo Excel 97-2003, ki ima obliko XLT, podpira tudi makre.
Ko je predloga shranjena, lahko varno zaprete Excel.
Izvajanje makra v Excelu
Preden razkrijete vse možnosti makra, ki ste ga ustvarili, se mi zdi prav, da posvetite pozornost nekaj pomembnim točkam glede makrov na splošno:
- Makri so lahko škodljivi.
- Še enkrat preberi prejšnji odstavek.
Koda VBA je zelo zmogljiva. Zlasti lahko izvaja operacije na datotekah zunaj trenutnega dokumenta. Na primer, makro lahko izbriše ali spremeni katero koli datoteko v mapi Moji dokumenti. Iz tega razloga izvajajte in dovolite samo makre iz virov, ki jim zaupate.
Če želite zagnati naš makro za oblikovanje podatkov, odprite datoteko predloge, ki smo jo ustvarili v prvem delu te vadnice. Če imate standardne varnostne nastavitve, se bo ob odpiranju datoteke nad tabelo pojavilo opozorilo, da so makri onemogočeni, in gumb, da jih omogočite. Ker smo šablono naredili sami in si zaupamo, pritisnemo na gumb Omogoči vsebino (Vključite vsebino).
Naslednji korak je uvoz najnovejšega posodobljenega nabora podatkov iz datoteke CSV (na podlagi take datoteke smo ustvarili naš makro).
Ko uvozite podatke iz datoteke CSV, vas bo Excel morda prosil, da nastavite nekaj nastavitev za pravilen prenos podatkov v tabelo.
Ko je uvoz končan, pojdite v meni Makri (Makri). Poglej (Ogled) in izberite ukaz Ogled makrov (makro).
V pogovornem oknu, ki se odpre, bomo videli vrstico z imenom našega makra FormatData. Izberite in kliknite Run (Izvedi).
Ko se makro začne izvajati, boste videli kazalec tabele, ki skače iz celice v celico. Po nekaj sekundah bodo s podatki opravljene enake operacije kot pri snemanju makra. Ko je vse pripravljeno, mora tabela izgledati enako kot original, ki smo ga ročno oblikovali, le z drugačnimi podatki v celicah.
Poglejmo pod pokrov: Kako deluje makro?
Kot že večkrat omenjeno, je makro programska koda v programskem jeziku. Visual Basic za aplikacije (VBA). Ko vklopite način snemanja makrov, Excel dejansko zabeleži vsako vaše dejanje v obliki navodil VBA. Preprosto povedano, Excel napiše kodo namesto vas.
Če želite videti to programsko kodo, morate v meniju Makri (Makri). Poglej (ogled) klik Ogled makrov (Makri) in v pogovornem oknu, ki se odpre, kliknite Uredi (Spremeni).
Odpre se okno. Visual Basic za aplikacije, v katerem bomo videli programsko kodo makra, ki smo ga posneli. Da, prav ste razumeli, tukaj lahko spremenite to kodo in celo ustvarite nov makro. Dejanja, ki smo jih izvedli s tabelo v tej lekciji, lahko posnamete s samodejnim snemanjem makrov v Excelu. Toda bolj zapleteni makri s fino nastavljenim zaporedjem in logiko dejanj zahtevajo ročno programiranje.
Dodajmo naši nalogi še en korak …
Predstavljajte si, da je naša izvirna podatkovna datoteka podatki.csv ustvari samodejno nek proces in je vedno shranjen na disku na istem mestu. na primer C:Datadata.csv – pot do datoteke s posodobljenimi podatki. Postopek odpiranja te datoteke in uvoza podatkov iz nje je mogoče zabeležiti tudi v makro:
- Odprite datoteko predloge, kamor smo shranili makro − FormatData.
- Ustvarite nov makro z imenom LoadData.
- Med snemanjem makra LoadData uvozi podatke iz datoteke podatki.csv – kot smo storili v prejšnjem delu lekcije.
- Ko je uvoz končan, ustavite snemanje makra.
- Izbrišite vse podatke iz celic.
- Shranite datoteko kot Excelovo predlogo z omogočenimi makri (razširitev XLTM).
Tako z zagonom te predloge dobite dostop do dveh makrov – eden naloži podatke, drugi jih formatira.
Če se želite lotiti programiranja, lahko združite dejanja teh dveh makrov v enega – preprosto tako, da kopirate kodo iz LoadData na začetek kode FormatData.