Sisällys
Excelillä on tehokas, mutta samalla hyvin harvoin käytetty kyky luoda automaattisia toimintosarjoja makrojen avulla. Makro on ihanteellinen ratkaisu, jos käsittelet samantyyppistä tehtävää, joka toistetaan monta kertaa. Esimerkiksi tietojen käsittely tai dokumenttien muotoilu standardoidun mallin mukaan. Tässä tapauksessa sinun ei tarvitse tietää ohjelmointikieliä.
Oletko jo utelias mitä makro on ja miten se toimii? Siirry sitten rohkeasti eteenpäin – sitten teemme askel askeleelta koko makron luomisprosessin kanssasi.
Mikä on makro?
Microsoft Officen makro (kyllä, tämä toiminto toimii samoin monissa Microsoft Office -paketin sovelluksissa) on ohjelmakoodi ohjelmointikielessä Visual Basic sovelluksille (VBA) on tallennettu asiakirjan sisään. Selvyyden vuoksi Microsoft Office -asiakirjaa voidaan verrata HTML-sivuun, jolloin makro on Javascriptin analogi. Se, mitä Javascript voi tehdä verkkosivun HTML-tiedoilla, on hyvin samanlaista kuin mitä makro voi tehdä Microsoft Office -asiakirjan tiedoilla.
Makrot voivat tehdä asiakirjassa melkein mitä tahansa. Tässä muutamia niistä (hyvin pieni osa):
- Käytä tyylejä ja muotoiluja.
- Suorita erilaisia operaatioita numeerisilla ja tekstitiedoilla.
- Käytä ulkoisia tietolähteitä (tietokantatiedostoja, tekstidokumentteja jne.)
- Luo uusi asiakirja.
- Tee kaikki edellä mainitut missä tahansa yhdistelmässä.
Makron luominen – käytännön esimerkki
Otetaan esimerkiksi yleisin tiedosto CSV. Tämä on yksinkertainen 10 × 20 taulukko, joka on täynnä numeroita 0-100 ja otsikoita sarakkeille ja riveille. Tehtävämme on muuttaa tämä tietojoukko näyttävästi muotoilluksi taulukoksi ja tuottaa jokaiselle riville summat.
Kuten jo mainittiin, makro on VBA-ohjelmointikielellä kirjoitettu koodi. Mutta Excelissä voit luoda ohjelman kirjoittamatta koodiriviä, minkä teemme nyt.
Luo makro avaamalla Näytä (Tyyppi) > Makrot (Makro) > Nauhoita makro (Makrotallennus…)
Anna makrollesi nimi (ei välilyöntejä) ja napsauta OK.
Tästä hetkestä lähtien KAIKKI toiminnot asiakirjan kanssa tallennetaan: muutokset soluihin, taulukon selaaminen, jopa ikkunan koon muuttaminen.
Excel ilmoittaa, että makrotallennustila on käytössä kahdessa paikassa. Ensin valikkoon Makrot (Makrot) – merkkijonon sijaan Nauhoita makro (Makroa tallennetaan…) -rivi ilmestyi Pysäytä tallennus (Lopeta tallennus).
Toiseksi Excel-ikkunan vasemmassa alakulmassa. Kuvake stop (pieni neliö) osoittaa, että makrotallennustila on käytössä. Napsauttaminen lopettaa tallennuksen. Käänteisesti, kun tallennustila ei ole käytössä, tässä paikassa on kuvake makrotallennusta varten. Sen napsauttaminen antaa saman tuloksen kuin nauhoituksen käynnistäminen valikon kautta.
Nyt kun makrotallennustila on käytössä, siirrytään tehtäväämme. Ensinnäkin lisätään otsikot yhteenvetotietoihin.
Syötä seuraavaksi kaavat soluihin otsikoiden nimien mukaisesti (englanninkielisten kaavojen ja Excelin versioiden muunnelmat annetaan, solujen osoitteet ovat aina latinalaisia kirjaimia ja numeroita):
- =SUMMA(B2:K2) or =SUMMA(B2:K2)
- =KESKIARVO(B2:K2) or =СРЗНАЧ(B2:K2)
- =MIN(B2:K2) or =MIN(B2:K2)
- =MAX(B2:K2) or =MAX(B2:K2)
- =KEDIAANI(B2:K2) or =KEDIAANI(B2:K2)
Valitse nyt solut kaavoineen ja kopioi ne taulukon kaikille riveille vetämällä automaattisen täytön kahvaa.
Kun olet suorittanut tämän vaiheen, jokaisella rivillä pitäisi olla vastaavat summat.
Seuraavaksi teemme yhteenvedon koko taulukon tuloksista, tätä varten teemme vielä muutamia matemaattisia operaatioita:
Vastaavasti:
- =SUMMA(L2:L21) or =SUMMA(L2:L21)
- =KESKIARVO(B2:K21) or =СРЗНАЧ(B2:K21) – tämän arvon laskemiseksi on otettava tarkalleen taulukon alkutiedot. Jos otat yksittäisten rivien keskiarvojen keskiarvon, tulos on erilainen.
- =MIN(N2:N21) or =MIN(N2:N21)
- =MAX(O2:O21) or =MAX(O2:O21)
- =KEDIAANI(B2:K21) or =KEDIAANI(B2:K21) – harkitsemme taulukon lähtötietojen käyttämistä yllä mainitusta syystä.
Nyt kun olemme tehneet laskelmat, tehdään hieman muotoilua. Ensin asetetaan kaikille soluille sama tietojen näyttömuoto. Valitse kaikki arkin solut ja käytä tätä varten pikanäppäintä Ctrl +tai napsauta kuvaketta Valitse kaikki, joka sijaitsee rivi- ja sarakeotsikoiden leikkauskohdassa. Napsauta sitten Pilkkutyyli (Rajattu muoto) -välilehti Koti (Koti).
Muuta seuraavaksi sarakkeen ja rivien otsikoiden ulkoasua:
- Lihavoitu kirjasintyyli.
- Keskitasaus.
- Värillinen täyttö.
Ja lopuksi asetetaan loppusumman muoto.
Tältä sen pitäisi loppujen lopuksi näyttää:
Jos kaikki sopii sinulle, lopeta makron tallennus.
Onnittelut! Olet juuri itse tallentanut ensimmäisen makrosi Exceliin.
Luodun makron käyttämiseksi meidän on tallennettava Excel-dokumentti makroja tukevaan muotoon. Ensin meidän täytyy poistaa kaikki tiedot luomastamme taulukosta, eli tehdä siitä tyhjä malli. Tosiasia on, että tulevaisuudessa tuomme tähän malliin uusimmat ja asiaankuuluvat tiedot.
Poista kaikki solut tiedoista napsauttamalla kuvaketta hiiren kakkospainikkeella Valitse kaikki, joka sijaitsee rivi- ja sarakeotsikoiden leikkauskohdassa, ja valitse pikavalikosta Poista (Poistaa).
Nyt taulukko on täysin tyhjennetty kaikista tiedoista, kun taas makro pysyy tallennettuna. Meidän on tallennettava työkirja makrokäyttöisenä Excel-mallina, jolla on laajennus XLTM.
Tärkeä pointti! Jos tallennat tiedoston tunnisteella XLTX, silloin makro ei toimi siinä. Muuten, voit tallentaa työkirjan Excel 97-2003 -mallina, jonka muoto on XLT, se tukee myös makroja.
Kun malli on tallennettu, voit sulkea Excelin turvallisesti.
Makron suorittaminen Excelissä
Ennen kuin paljastan luomasi makron kaikki mahdollisuudet, mielestäni on oikein kiinnittää huomiota muutamaan tärkeään seikkaan, jotka koskevat makroja yleisesti:
- Makrot voivat olla haitallisia.
- Lue edellinen kappale uudelleen.
VBA-koodi on erittäin tehokas. Erityisesti se voi suorittaa toimintoja nykyisen asiakirjan ulkopuolisille tiedostoille. Makro voi esimerkiksi poistaa tai muokata mitä tahansa kansiossa olevia tiedostoja Omat asiakirjat. Tästä syystä aja ja salli makroja vain luotettavista lähteistä.
Suorita tietojen muotoilumakro avaamalla mallitiedosto, jonka loimme tämän opetusohjelman ensimmäisessä osassa. Jos sinulla on vakiosuojausasetukset, kun avaat tiedoston, taulukon yläpuolelle tulee varoitus, että makrot on poistettu käytöstä, ja painike niiden käyttöön ottamiseksi. Koska teimme mallin itse ja luotamme itseemme, painamme nappia Ota sisältö käyttöön (Sisällytä sisältö).
Seuraava vaihe on tuoda viimeisin päivitetty tietojoukko tiedostosta CSV (sellaisen tiedoston perusteella loimme makromme).
Kun tuot tietoja CSV-tiedostosta, Excel saattaa pyytää sinua määrittämään joitain asetuksia, jotta tiedot voidaan siirtää oikein taulukkoon.
Kun tuonti on valmis, siirry valikkoon Makrot (Makrot) -välilehti Näytä (Näytä) ja valitse komento Näytä makrot (Makro).
Avautuvassa valintaikkunassa näemme rivin, jossa on makromme nimi FormatData. Valitse se ja napsauta ajaa (Suorittaa).
Kun makro käynnistyy, näet taulukon kohdistimen hyppäävän solusta toiseen. Muutaman sekunnin kuluttua tiedoilla tehdään samat toiminnot kuin makroa tallennettaessa. Kun kaikki on valmis, taulukon tulee näyttää samalta kuin alkuperäinen, jonka muotoilimme käsin, vain soluissa on eri tiedot.
Katsotaanpa konepellin alle: Kuinka makro toimii?
Kuten useammin kuin kerran mainittiin, makro on ohjelmointikielen ohjelmakoodi. Visual Basic sovelluksille (VBA). Kun otat makrotallennustilan käyttöön, Excel itse asiassa tallentaa kaikki tekemäsi toiminnot VBA-ohjeiden muodossa. Yksinkertaisesti sanottuna Excel kirjoittaa koodin puolestasi.
Nähdäksesi tämän ohjelmakoodin sinun on oltava valikossa Makrot (Makrot) -välilehti Näytä (katso) napsauta Näytä makrot (Makrot) ja napsauta avautuvassa valintaikkunassa muokata (Muuttaa).
Ikkuna avautuu. Visual Basic sovelluksille, jossa näemme tallentamamme makron ohjelmakoodin. Kyllä, ymmärsit oikein, täällä voit muuttaa tätä koodia ja jopa luoda uuden makron. Toiminnot, jotka suoritimme taulukon kanssa tällä oppitunnilla, voidaan tallentaa käyttämällä automaattista makrotallennusta Excelissä. Mutta monimutkaisemmat makrot, joissa on hienosäädetyt sekvenssit ja toimintalogiikka, vaativat manuaalista ohjelmointia.
Lisätään vielä yksi askel tehtäväämme…
Kuvittele, että alkuperäinen tiedostomme data.csv luodaan automaattisesti jonkin prosessin toimesta ja tallennetaan aina levylle samaan paikkaan. Esimerkiksi, C:Datadata.csv – polku tiedostoon, jossa on päivitetyt tiedot. Tämän tiedoston avaamisprosessi ja tietojen tuonti siitä voidaan myös tallentaa makroon:
- Avaa mallitiedosto, johon tallensimme makron − FormatData.
- Luo uusi makro nimeltä LoadData.
- Tallennettaessa makroa LoadData tuoda tiedot tiedostosta data.csv – kuten teimme oppitunnin edellisessä osassa.
- Kun tuonti on valmis, lopeta makron tallennus.
- Poista kaikki tiedot soluista.
- Tallenna tiedosto makrokäyttöisenä Excel-mallina (XLTM-laajennus).
Näin ollen suorittamalla tämän mallin saat käyttöösi kaksi makroa – toinen lataa tiedot ja toinen muotoilee ne.
Jos haluat päästä ohjelmointiin, voit yhdistää näiden kahden makron toiminnot yhdeksi – yksinkertaisesti kopioimalla koodin LoadData koodin alkuun FormatData.