Sisu
Excelil on võimas, kuid samas väga harva kasutatav võimalus makrode abil automaatsete toimingute jadade loomiseks. Makro on ideaalne väljapääs, kui tegelete sama tüüpi ülesandega, mida korratakse mitu korda. Näiteks andmetöötlus või dokumendi vormindamine standardiseeritud malli järgi. Sel juhul pole teil vaja programmeerimiskeelte tundmist.
Kas olete juba uudishimulik, mis on makro ja kuidas see töötab? Siis julgelt edasi – siis teeme koos sinuga samm-sammult kogu makro loomise protsessi läbi.
Mis on makro?
Microsoft Office'i makro (jah, see funktsioon töötab samamoodi paljudes Microsoft Office'i paketi rakendustes) on programmikood programmeerimiskeeles Visual Basic rakendustele (VBA) salvestatakse dokumendi sees. Selguse huvides võib Microsoft Office'i dokumenti võrrelda HTML-lehega, siis makro on Javascripti analoog. See, mida Javascript saab teha veebilehel olevate HTML-andmetega, on väga sarnane sellele, mida makro saab teha Microsoft Office'i dokumendi andmetega.
Makrodega saab dokumendis teha peaaegu kõike, mida soovite. Siin on mõned neist (väga väike osa):
- Rakendage stiile ja vormingut.
- Tehke erinevaid toiminguid numbri- ja tekstiandmetega.
- Kasutage väliseid andmeallikaid (andmebaasifailid, tekstidokumendid jne)
- Looge uus dokument.
- Tehke kõike ülaltoodut mis tahes kombinatsioonis.
Makro loomine – praktiline näide
Näiteks võtame kõige tavalisema faili CSV. See on lihtne 10 × 20 tabel, mis on täidetud numbritega 0–100 koos veergude ja ridade pealkirjadega. Meie ülesanne on muuta see andmekogum esindusliku vorminguga tabeliks ja genereerida igas reas kogusummad.
Nagu juba mainitud, on makro VBA programmeerimiskeeles kirjutatud kood. Kuid Excelis saate luua programmi ilma koodirida kirjutamata, mida me ka praegu teeme.
Makro loomiseks avage vaade (Tüüp) > Makrod (Makro) > Salvestage makro (Makrosalvestus...)
Andke oma makrole nimi (ilma tühikuteta) ja klõpsake OK.
Sellest hetkest alates salvestatakse KÕIK teie toimingud dokumendiga: lahtrite muudatused, tabeli kerimine, isegi akna suuruse muutmine.
Excel annab märku, et makrosalvestusrežiim on kahes kohas lubatud. Esiteks menüüs Makrod (Makrod) – stringi asemel Salvestage makro (Makro salvestamine…) ilmus rida Lõpeta salvestamine (Lõpeta salvestamine).
Teiseks Exceli akna vasakus alanurgas. Ikoon Peatus (väike ruut) näitab, et makrosalvestusrežiim on lubatud. Sellel klõpsamine peatab salvestamise. Ja vastupidi, kui salvestusrežiim pole lubatud, on selles kohas makrosalvestuse lubamiseks ikoon. Sellel klõpsamine annab sama tulemuse kui menüü kaudu salvestamise sisselülitamine.
Nüüd, kui makrosalvestusrežiim on lubatud, asume oma ülesande juurde. Kõigepealt lisame kokkuvõtlike andmete päised.
Järgmisena sisestage valemid lahtritesse vastavalt pealkirjade nimedele (antud on ingliskeelsete valemite variandid ja Exceli versioonid, lahtrite aadressid on alati ladina tähed ja numbrid):
- =SUM(B2:K2) or =SUM(B2:K2)
- =KESKMINE(B2:K2) or =СРЗНАЧ(B2:K2)
- =MIN(B2:K2) or =MIN(B2:K2)
- =MAX(B2:K2) or =MAX(B2:K2)
- =MEDIAAN(B2:K2) or =MEDIAAN(B2:K2)
Nüüd valige valemitega lahtrid ja kopeerige need meie tabeli kõikidele ridadele, lohistades automaatse täitmise käepidet.
Pärast selle sammu lõpetamist peaks igal real olema vastav kogusumma.
Järgmisena võtame kogu tabeli tulemused kokku, selleks teeme veel mõned matemaatilised toimingud:
Vastavalt:
- =SUM(L2:L21) or =SUM(L2:L21)
- =KESKMINE(B2:K21) or =СРЗНАЧ(B2:K21) – selle väärtuse arvutamiseks on vaja võtta täpselt tabeli algandmed. Kui võtta üksikute ridade keskmiste keskmine, on tulemus erinev.
- =MIN(N2:N21) or =MIN(N2:N21)
- =MAX(O2:O21) or =MAX(O2:O21)
- =MEDIAAN(B2:K21) or =MEDIAAN(B2:K21) – kaalume ülaltoodud põhjusel tabeli algandmete kasutamist.
Nüüd, kui oleme arvutustega lõpetanud, teeme vormingu. Esmalt määrame kõigile lahtritele ühesuguse andmekuvamise vormingu. Valige lehel kõik lahtrid ja kasutage selleks kiirklahvi Ctrl +või klõpsake ikooni Vali kõik, mis asub rea- ja veerupealkirjade ristumiskohas. Seejärel klõpsake Koma stiil (piiratud vorming) vahekaart Avaleht (Kodu).
Järgmisena muutke veeru ja reapäiste välimust.
- Paks kirjastiil.
- Keskjoondus.
- Värviline täitmine.
Ja lõpuks paneme paika kogusummade vormingu.
Nii peaks see lõpuks välja nägema:
Kui kõik teile sobib, lõpetage makro salvestamine.
Palju õnne! Salvestasite just ise oma esimese makro Excelis.
Loodud makro kasutamiseks peame salvestama Exceli dokumendi makrosid toetavas vormingus. Kõigepealt peame enda loodud tabelist kõik andmed kustutama ehk tegema selle tühjaks malliks. Fakt on see, et tulevikus impordime selle malliga töötades sellesse kõige värskemad ja asjakohased andmed.
Kõigi lahtrite andmetest kustutamiseks paremklõpsake ikoonil Vali kõik, mis asub rea- ja veerupealkirjade ristumiskohas ning valige kontekstimenüüst kustutama (Kustuta).
Nüüd on meie leht kõigist andmetest täielikult puhastatud, samas kui makro jääb salvestatud. Peame töövihiku salvestama makrotoega Exceli mallina, millel on laiend XLTM.
Oluline punkt! Kui salvestate faili laiendiga XLTX, siis makro selles ei tööta. Muide, saate töövihiku salvestada Excel 97-2003 mallina, millel on vorming XLT, see toetab ka makrosid.
Kui mall on salvestatud, saate Exceli turvaliselt sulgeda.
Makro käivitamine Excelis
Enne loodud makro kõigi võimaluste avaldamist arvan, et on õige pöörata tähelepanu paarile olulisele punktile seoses makrodega üldiselt:
- Makrod võivad olla kahjulikud.
- Loe eelmine lõik uuesti läbi.
VBA kood on väga võimas. Eelkõige saab see teha toiminguid failidega väljaspool praegust dokumenti. Näiteks võib makro kustutada või muuta kaustas olevaid faile Minu dokumendid. Sel põhjusel käivitage ja lubage makrosid ainult usaldusväärsetest allikatest.
Andmete vormindamise makro käivitamiseks avage selle õpetuse esimeses osas loodud mallifail. Kui teil on standardsed turvaseaded, kuvatakse faili avamisel tabeli kohale hoiatus, et makrod on keelatud, ja nupp nende lubamiseks. Kuna malli tegime ise ja usaldame ennast, siis vajutame nuppu Luba sisu (Kaasake sisu).
Järgmine samm on importida failist uusim värskendatud andmestik CSV (sellise faili põhjal lõime oma makro).
Kui impordite andmeid CSV-failist, võib Excel paluda teil seadistada mõned sätted, et andmed õigesti tabelisse üle kanda.
Kui importimine on lõppenud, minge menüüsse Makrod (Makrod) vahekaart vaade (View) ja valige käsk Kuva makrod (Makro).
Avanevas dialoogiboksis näeme rida meie makro nimega FormatData. Valige see ja klõpsake jooks (Teosta).
Kui makro hakkab töötama, näete tabelikursorit hüppamas lahtrist lahtrisse. Mõne sekundi pärast tehakse andmetega samad toimingud, mis makro salvestamisel. Kui kõik on valmis, peaks tabel välja nägema sama, mis käsitsi vormindatud originaal, ainult et lahtrites on erinevad andmed.
Vaatame kapoti alla: kuidas makro töötab?
Nagu korduvalt mainitud, on makro programmeerimiskeele programmikood. Visual Basic rakendustele (VBA). Kui lülitate makrosalvestusrežiimi sisse, salvestab Excel tegelikult kõik teie tehtud toimingud VBA juhiste kujul. Lihtsamalt öeldes kirjutab Excel teie eest koodi.
Selle programmikoodi nägemiseks peate menüüs olema Makrod (Makrod) vahekaart vaade (vaata) klõps Kuva makrod (Makrod) ja klõpsake avanevas dialoogiboksis Edit (Muuda).
Aken avaneb. Visual Basic rakendustele, milles näeme salvestatud makro programmikoodi. Jah, saite õigesti aru, siin saate seda koodi muuta ja isegi luua uue makro. Selles õppetükis tabeliga tehtud toiminguid saab salvestada Exceli automaatse makrosalvestuse abil. Kuid keerukamad makrod koos peenhäälestatud järjestuse ja tegevusloogikaga nõuavad käsitsi programmeerimist.
Lisame oma ülesandele veel ühe sammu…
Kujutage ette, et meie algne andmefail data.csv luuakse mingi protsessi poolt automaatselt ja salvestatakse alati kettale samas kohas. Näiteks, C:Datadata.csv – uuendatud andmetega faili tee. Selle faili avamise ja sellest andmete importimise protsessi saab salvestada ka makrosse:
- Avage mallifail, kuhu makro salvestasime FormatData.
- Looge uus makro nimega LoadData.
- Makro salvestamise ajal LoadData importida andmed failist data.csv – nagu tegime tunni eelmises osas.
- Kui importimine on lõppenud, lõpetage makro salvestamine.
- Kustutage lahtritest kõik andmed.
- Salvestage fail makrotoega Exceli mallina (XLTM-laiend).
Seega saate selle malli käivitamisega juurdepääsu kahele makrole – üks laadib andmed, teine vormindab need.
Kui soovite programmeerimisega tegelema hakata, saate nende kahe makro toimingud üheks ühendada – lihtsalt kopeerides koodi LoadData koodi algusesse FormatData.