Opi käyttämään Excel-makroja automatisoimaan tylsiä tehtäviä

Yksi voimakkaimmista, mutta harvoin käytetyistä Excel-toiminnoista on kyky luoda helposti automatisoituja tehtäviä ja mukautettua logiikkaa makroissa. Makrot tarjoavat ihanteellisen tavan säästää aikaa ennustettavissa olevissa, toistuvissa tehtävissä sekä standardoida dokumenttimuotoja - monta kertaa ilman, että sinun tarvitsee kirjoittaa yhtä koodiriviä.

Jos olet utelias, mitä makroja on tai miten niitä luodaan, ei ole ongelma - me kävelemme sinua koko prosessin läpi.

Huomautus: saman prosessin pitäisi toimia useimmissa Microsoft Office -versioissa. Kuvakaappaukset saattavat näyttää hieman erilaisilta.

Mikä on makro?

Microsoft Office Macro (koska tämä toiminto koskee useita MS Office -sovelluksia) on yksinkertaisesti Visual Basic for Applications (VBA) -koodi, joka on tallennettu asiakirjan sisälle. Vertailukelpoisen analogian vuoksi ajattele asiakirjaa HTML-muodossa ja makroina Javascript-muodossa. Makro voi manipuloida asiakirjaa paljon samalla tavalla kuin Javascript voi käsitellä HTML-sivua verkkosivulla.

Makrot ovat uskomattoman voimakkaita ja voivat tehdä melko paljon mitä mielikuvitus voi tuoda. (Erittäin) lyhyt luettelo toiminnoista, joita voit tehdä makron avulla:

• Käytä tyyliä ja muotoilua.
• Käsittele tietoja ja tekstiä.
• Kommunikoi tietolähteiden (tietokanta, tekstitiedostot jne.) Kanssa.
• Luo täysin uusia asiakirjoja.
• Mikä tahansa edellä mainitun yhdistelmä missä tahansa järjestyksessä.

Makron luominen: selitys esimerkin avulla

Aloitamme CSV-tiedostollasi. Tässä ei ole mitään erityistä, vain 10 × 20 joukkoa numeroita välillä 0 - 100 sekä rivin että sarakkeen otsikon kanssa. Tavoitteenamme on tuottaa hyvin muotoiltu, esiteltävä tietolomake, joka sisältää yhteenvedon kullekin riville.

Kuten edellä mainitsimme, makro on VBA-koodi, mutta yksi hienoista asioista Excelissä on, että voit luoda / tallentaa niitä nollakoodauksella - kuten teemme täällä.

Voit luoda makron siirtymällä kohtaan View> Macros> Record Macro.

Määritä makro nimi (ei välilyöntejä) ja napsauta OK.

Kun tämä on tehty, kaikki tallennetaan toimintojasi - jokainen solunvaihto, vieritystoiminto, ikkunan koon muuttaminen, nimeät sen.

On olemassa muutamia paikkoja, jotka osoittavat, että Excel on tallennustila. Yksi on Macro-valikon katseleminen ja huomaa, että Stop Recording on korvannut Record Macro -toiminnon.

Toinen on oikeassa alakulmassa. 'Stop' -kuvake osoittaa, että se on makrotilassa, ja painamalla tätä pysäyttää tallennus (samoin, kun ei tallennustilassa, tämä kuvake on Record Macro -näppäin, jota voit käyttää makro-valikon sijaan).

Nyt kun tallennamme makroamme, otamme käyttöön yhteenvetolaskelmat. Lisää ensin otsikot.

Käytä seuraavaksi sopivia kaavoja (vastaavasti):

• = SUM (B2: K2)
• = Keskiarvo (B2: K2)
• = MIN (B2: K2)
• = MAX (B2: K2)
• = MEDIAN (B2: K2)

Korosta kaikki laskentakennot ja vedä kaikkien tietorivien pituus soveltaaksesi laskelmia kullekin riville.

Kun tämä on tehty, jokaisen rivin tulee näyttää niiden yhteenvedot.

Nyt haluamme saada koko arkin yhteenvetotiedot, joten käytämme muutamia laskelmia:

Vastaavasti:

• = SUM (L2: L21)
• = AVERAGE (B2: K21) *Tämä on laskettava kaikista tiedoista, koska rivien keskiarvojen keskiarvo ei välttämättä vastaa kaikkien arvojen keskiarvoa.
• = MIN (N2: N21)
• = MAX (O2: O21)
• = MEDIAN (B2: K21) * Laskettu kaikista tiedoista samasta syystä kuin edellä.

Nyt kun laskelmat on tehty, käytämme tyyliä ja muotoilua. Käytä ensin yleistä numeromuotoa kaikissa soluissa valitsemalla Valitse kaikki (joko Ctrl + A tai napsauta solua rivi- ja sarakeotsikoiden välillä) ja valitse Kotivalikko-valikosta Comma Style..

Käytä seuraavaksi visuaalista muotoilua sekä rivi- että sarakeotsikoihin:

• rohkea.
• keskitetty.
• Taustan täyttöväri.

Ja lopuksi, käytä jotakin tyyliä kokonaismäärään.

Kun kaikki on päättynyt, tietosivu näyttää siltä:

Koska olemme tyytyväisiä tuloksiin, lopeta makron tallennus.

Onnittelut - olet juuri luonut Excel-makron.

Jotta voisimme käyttää äskettäin tallennettua makroamme, meidän on tallennettava Excel-työkirjasi makrokäyttöisessä tiedostomuodossa. Ennen kuin teemme sen, meidän on ensin tyhjennettävä kaikki olemassa olevat tiedot, jotta se ei ole upotettu malliin (ajatus joka kerta, kun käytämme tätä mallia, tuomme ajan tasalla olevat tiedot).

Voit tehdä tämän valitsemalla kaikki solut ja poistamalla ne.

Kun tiedot on nyt tyhjennetty (mutta makrot ovat edelleen Excel-tiedostossa), haluamme tallentaa tiedoston makroyhteensopivaksi malliksi (XLTM). On tärkeää huomata, että jos tallennat tämän tavallisena mallina (XLTX), niin makrot tulevat ei pystyä ajamaan siitä. Vaihtoehtoisesti voit tallentaa tiedoston vanhaksi malliksi (XLT), joka mahdollistaa makrojen suorittamisen.

Kun olet tallentanut tiedoston mallina, mene eteenpäin ja sulje Excel.

Excel-makron käyttäminen

Ennen kuin käsittelet tämän äskettäin tallennetun makron soveltamista, on tärkeää kattaa muutaman pisteen makroista yleensä:

• Makrot voivat olla haitallisia.
• Katso edellä oleva kohta.

VBA-koodi on itse asiassa varsin tehokas ja voi käsitellä tiedostoja nykyisen asiakirjan soveltamisalan ulkopuolella. Esimerkiksi makro voi muuttaa tai poistaa satunnaisia ​​tiedostoja Omat tiedostot -kansiossa. Siksi on tärkeää varmistaa, että vain suorittaa makroja luotetuista lähteistä.

Jos haluat käyttää tietomuotoista makroamme, avaa yllä luotu Excel-mallitiedosto. Kun teet tämän, olettaen, että käytössäsi on vakioasetukset, näet työkirjan yläosassa olevan varoituksen, jossa todetaan, että makrot on poistettu käytöstä. Koska luotamme itse luomaan makroon, napsauta Ota sisältö käyttöön -painiketta.

Seuraavaksi aiomme tuoda viimeisimmät tietokannat CSV: stä (tämä on lähde, jota käytetään laskentataulukon luomiseen makroomme).

CSV-tiedoston tuonnin loppuun saattamiseksi on ehkä asetettava muutama vaihtoehto, jotta Excel voi tulkita sen oikein (esim. Erotin, nykyiset otsikot jne.).

Kun tiedot on tuotu, siirry Makrot-valikkoon (Näytä-välilehden alla) ja valitse Näytä makrot.

Tuloksena olevassa valintaikkunassa näemme edellä kuvatun ”FormatData” -makron. Valitse se ja valitse Suorita.

Kun olet käynnissä, saatat nähdä, että kohdistin hyppää noin muutaman minuutin ajan, mutta kuten näet, manipuloidut tiedot näkyvät tarkalleen kun tallennimme sen. Kun kaikki on sanottu ja tehty, sen pitäisi näyttää aivan alkuperäiseltä - paitsi erilaisilla tiedoilla.

Hupun alla: mikä tekee Macro-työstä

Kuten mainitsimme pari kertaa, makroa ohjaa Visual Basic for Applications (VBA) -koodi. Kun tallennat makron, Excel todella kääntää kaiken tekemäsi VBA-ohjeisiinsa. Yksinkertaisesti sanottuna - sinun ei tarvitse kirjoittaa mitään koodia, koska Excel kirjoittaa sinulle koodin.

Voit tarkastella makroa käyttävän koodin Macros-valintaikkunassa Muokkaa-painiketta.

Avaa avautuva ikkuna näyttää lähdekoodin, joka on tallennettu toiminnoistamme makroa luodessasi. Voit tietenkin muokata tätä koodia tai jopa luoda uusia makroja täysin koodin ikkunan sisällä. Vaikka tässä artikkelissa käytetty tallennustoiminto sopii todennäköisesti useimpiin tarpeisiin, enemmän mukautetut toimet tai ehdolliset toimet edellyttävät, että lähdekoodia muokataan.

Esimerkkinä yksi askel eteenpäin ...

Oletetaan oletettavasti, että lähdetiedostomme data.csv tuotetaan automatisoidulla prosessilla, joka tallentaa tiedoston aina samaan paikkaan (esim. C: Data.csv on aina uusin tieto). Tämän tiedoston avaaminen ja sen tuonti voidaan tehdä helposti myös makroksi:

1. Avaa Excel-mallitiedosto, joka sisältää ”FormatData” -makron.
2. Tallenna uusi makro nimeltä "LoadData".
3. Makro tallennuksen avulla tuodaan tavalliseen tapaan tiedosto.
4. Kun tiedot on tuotu, lopeta makron tallennus.
5. Poista kaikki solutiedot (valitse kaikki ja poista sitten).
6. Tallenna päivitetty malli (muista käyttää makroominaisuuksia käyttävää mallimuotoa).

Kun tämä on tehty, aina kun mallipohja avataan, on kaksi makroa - joka lataa tietomme ja toinen muodostaa sen.

Jos todella halusit saada kädet likaisiksi hieman koodin muokkauksella, voit helposti yhdistää nämä toimet yhteen makroon kopioimalla "LoadData" -toiminnosta tuotetun koodin ja lisäämällä sen koodin alussa "FormatDatasta".

Lataa tämä malli

Mukavuuden vuoksi olemme sisällyttäneet sekä tässä artikkelissa tuotetun Excel-mallin että näytetiedostotiedoston, jolla voit pelata.

Lataa Excel-makro-malli How-To Geekista