Turinys
Excel turi galingą, bet tuo pačiu labai retai naudojamą galimybę kurti automatines veiksmų sekas naudojant makrokomandas. Makrokomandas yra ideali išeitis, jei susiduriate su tos pačios rūšies užduotimis, kurios kartojasi daug kartų. Pavyzdžiui, duomenų apdorojimas arba dokumentų formatavimas pagal standartizuotą šabloną. Tokiu atveju jums nereikia programavimo kalbų žinių.
Ar jau smalsu, kas yra makrokomandos ir kaip ji veikia? Tada drąsiai pirmyn – tada žingsnis po žingsnio kartu su jumis atliksime visą makrokomandos kūrimo procesą.
Kas yra makro?
„Microsoft Office“ makrokomanda (taip, ši funkcija taip pat veikia daugelyje „Microsoft Office“ paketo programų) yra programos kodas programavimo kalba „Visual Basic for Applications“ (VBA), saugomas dokumento viduje. Kad būtų aiškiau, Microsoft Office dokumentą galima palyginti su HTML puslapiu, tada makrokomanda yra Javascript analogas. Tai, ką „Javascript“ gali padaryti su HTML duomenimis tinklalapyje, labai panašu į tai, ką makrokomandos gali padaryti su duomenimis „Microsoft Office“ dokumente.
Makrokomandos gali padaryti beveik viską, ko norite dokumente. Štai keletas iš jų (labai maža dalis):
- Taikykite stilius ir formatavimą.
- Atlikite įvairias operacijas su skaitiniais ir tekstiniais duomenimis.
- Naudokite išorinius duomenų šaltinius (duomenų bazės failus, tekstinius dokumentus ir kt.)
- Sukurkite naują dokumentą.
- Atlikite visus aukščiau nurodytus veiksmus bet kokiu deriniu.
Makrokomandos kūrimas – praktinis pavyzdys
Pavyzdžiui, paimkime dažniausiai naudojamą failą CSV. Tai paprasta 10 × 20 lentelė, užpildyta skaičiais nuo 0 iki 100 su stulpelių ir eilučių antraštėmis. Mūsų užduotis yra paversti šį duomenų rinkinį tinkamai suformatuota lentele ir kiekvienoje eilutėje generuoti sumas.
Kaip jau minėta, makrokomanda yra kodas, parašytas VBA programavimo kalba. Tačiau „Excel“ galite sukurti programą neįrašę kodo eilutės, o mes tai padarysime dabar.
Norėdami sukurti makrokomandą, atidarykite Vaizdas (Tipas) > Makrokomandos (Makro) > Įrašyti makrokomandą (Makro įrašymas...)
Suteikite makrokomandai pavadinimą (be tarpų) ir spustelėkite OK.
Nuo šio momento įrašomi VISI jūsų veiksmai su dokumentu: ląstelių pakeitimai, lentelės slinkimas, net lango dydžio keitimas.
„Excel“ signalizuoja, kad makrokomandos įrašymo režimas įjungtas dviejose vietose. Pirma, meniu Makrokomandos (Makrokomandos) – vietoj eilutės Įrašyti makrokomandą (Makrokomandos įrašymas...) pasirodė eilutė Sustabdyti įrašymą (Sustabdykite įrašymą).
Antra, apatiniame kairiajame „Excel“ lango kampe. Piktograma Stop (mažas kvadratas) rodo, kad makrokomandos įrašymo režimas įjungtas. Paspaudus ant jo įrašymas bus sustabdytas. Ir atvirkščiai, kai įrašymo režimas neįjungtas, šioje vietoje yra piktograma, leidžianti įjungti makrokomandų įrašymą. Paspaudus ant jo, rezultatas bus toks pat, kaip ir įjungus įrašymą per meniu.
Dabar, kai įjungtas makrokomandos įrašymo režimas, pereikime prie užduoties. Visų pirma, pridėkime suvestinių duomenų antraštes.
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)
- =VIDUTINIS(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)
Dabar pasirinkite langelius su formulėmis ir nukopijuokite juos į visas mūsų lentelės eilutes, vilkdami automatinio pildymo rankenėlę.
Kai atliksite šį veiksmą, kiekvienoje eilutėje turi būti atitinkamos sumos.
Toliau apibendrinsime visos lentelės rezultatus, tam atliksime dar keletą matematinių operacijų:
Atitinkamai:
- =SUMA(L2:L21) or =SUMA(L2:L21)
- =VIDUTINIS(B2:K21) or =СРЗНАЧ(B2:K21) – norint apskaičiuoti šią reikšmę, reikia tiksliai paimti pradinius lentelės duomenis. Jei imsite atskirų eilučių vidurkių vidurkį, rezultatas bus kitoks.
- =MIN(N2:N21) or =MIN(N2:N21)
- =MAX(O2:O21) or =MAX(O2:O21)
- =MEDIAN(B2:K21) or =MEDIAN(B2:K21) – svarstome naudoti pradinius lentelės duomenis dėl aukščiau nurodytos priežasties.
Dabar, kai baigėme skaičiavimus, atlikime formatavimą. Pirmiausia nustatykime tą patį duomenų rodymo formatą visoms ląstelėms. Pasirinkite visus lapo langelius, kad tai padarytumėte, naudokite sparčiuosius klavišus Ctrl + Aarba spustelėkite piktogramą Pasirinkti visus, kuris yra eilučių ir stulpelių antraščių sankirtoje. Tada spustelėkite Kablelių stilius (Atskirtas formatas) skirtuką Pagrindinis (Namai).
Tada pakeiskite stulpelio ir eilučių antraščių išvaizdą:
- Paryškintas šrifto stilius.
- Išlygiavimas centre.
- Spalvų užpildymas.
Ir galiausiai nustatykime sumų formatą.
Štai kaip tai turėtų atrodyti pabaigoje:
Jei viskas jums tinka, nustokite įrašyti makrokomandą.
Sveikiname! Jūs ką tik patys įrašėte savo pirmąją makrokomandą „Excel“.
Norėdami naudoti sugeneruotą makrokomandą, turime išsaugoti Excel dokumentą tokiu formatu, kuris palaiko makrokomandas. Pirmiausia turime ištrinti visus duomenis iš savo sukurtos lentelės, ty padaryti ją tuščiu šablonu. Faktas yra tas, kad ateityje dirbdami su šiuo šablonu į jį importuosime naujausius ir aktualiausius duomenis.
Norėdami išvalyti visus langelius iš duomenų, dešiniuoju pelės mygtuku spustelėkite piktogramą Pasirinkti visus, kuris yra eilutės ir stulpelio antraščių sankirtoje, ir kontekstiniame meniu pasirinkite panaikinti (Ištrinti).
Dabar mūsų lapas yra visiškai išvalytas nuo visų duomenų, o makrokomandas lieka įrašytas. Turime išsaugoti darbaknygę kaip makrokomandą įgalintą „Excel“ šabloną su plėtiniu XLTM.
Svarbus punktas! Jei išsaugosite failą su plėtiniu XLTX, tada makrokomanda jame neveiks. Beje, darbaknygę galite išsaugoti kaip Excel 97-2003 šabloną, kurio formatas XLT, ji taip pat palaiko makrokomandas.
Išsaugoję šabloną galite saugiai uždaryti „Excel“.
„Excel“ makrokomandos paleidimas
Prieš atskleidžiant visas jūsų sukurtos makrokomandos galimybes, manau, teisinga atkreipti dėmesį į keletą svarbių dalykų, susijusių su makrokomandomis apskritai:
- Makrokomandos gali būti kenksmingos.
- Dar kartą perskaitykite ankstesnę pastraipą.
VBA kodas yra labai galingas. Visų pirma, jis gali atlikti operacijas su failais, nepriklausančiais dabartiniam dokumentui. Pavyzdžiui, makrokomanda gali ištrinti arba modifikuoti bet kokius aplanke esančius failus Mano dokumentai. Dėl šios priežasties paleiskite ir leiskite makrokomandas tik iš šaltinių, kuriais pasitikite.
Norėdami paleisti duomenų formatavimo makrokomandą, atidarykite šablono failą, kurį sukūrėme pirmoje šios pamokos dalyje. Jei turite standartinius saugos nustatymus, atidarius failą virš lentelės atsiras įspėjimas, kad makrokomandos išjungtos, ir mygtukas jas įjungti. Kadangi šabloną pasidarėme patys ir pasitikime savimi, spaudžiame mygtuką Įgalinti turinį (Įtraukite turinį).
Kitas veiksmas – importuoti iš failo naujausią atnaujintą duomenų rinkinį CSV (remdamiesi tokiu failu sukūrėme savo makrokomandą).
Kai importuojate duomenis iš CSV failo, programa „Excel“ gali paprašyti nustatyti kai kuriuos nustatymus, kad duomenys būtų tinkamai perkelti į lentelę.
Kai importavimas baigtas, eikite į meniu Makrokomandos (Makrokomandos) skirtuką Vaizdas (View) ir pasirinkite komandą Peržiūrėti makrokomandas (Makro).
Atsidariusiame dialogo lange pamatysime eilutę su mūsų makrokomandos pavadinimu FormatData. Pasirinkite jį ir spustelėkite paleisti (Vykdyti).
Pradėjus veikti makrokomandai, lentelės žymeklis šokinėja iš vieno langelio į kitą. Po kelių sekundžių su duomenimis bus atliekamos tos pačios operacijos kaip ir įrašant makrokomandą. Kai viskas bus paruošta, lentelė turėtų atrodyti taip pat, kaip originalas, kurį suformatavome ranka, tik su skirtingais duomenimis langeliuose.
Pažiūrėkime po gaubtu: kaip veikia makrokomandos?
Kaip jau ne kartą minėta, makrokomanda yra programos kodas programavimo kalba. „Visual Basic for Applications“ (VBA). Kai įjungiate makrokomandos įrašymo režimą, „Excel“ iš tikrųjų įrašo kiekvieną jūsų atliktą veiksmą VBA instrukcijų forma. Paprasčiau tariant, „Excel“ parašo kodą už jus.
Norėdami pamatyti šį programos kodą, jums reikia meniu Makrokomandos (Makrokomandos) skirtuką Vaizdas (žiūrėti) spustelėkite Peržiūrėti makrokomandas (Makrokomandos) ir atsidariusiame dialogo lange spustelėkite Redaguoti (Keisti).
Atsidaro langas. „Visual Basic for Applications“, kuriame matysime mūsų įrašytos makrokomandos programos kodą. Taip, jūs teisingai supratote, čia galite pakeisti šį kodą ir net sukurti naują makrokomandą. Veiksmus, kuriuos atlikome su lentele šioje pamokoje, galima įrašyti naudojant automatinį makrokomandų įrašymą programoje „Excel“. Tačiau sudėtingesnės makrokomandos su tiksliai suderinta seka ir veiksmų logika reikalauja rankinio programavimo.
Pridėkime prie mūsų užduoties dar vienu žingsniu…
Įsivaizduokite, kad mūsų pradinis duomenų failas data.csv sukuriamas automatiškai tam tikro proceso metu ir visada saugomas diske toje pačioje vietoje. Pavyzdžiui, C:Datatata.csv – kelias į failą su atnaujintais duomenimis. Šio failo atidarymo ir duomenų importavimo iš jo procesas taip pat gali būti įrašytas į makrokomandą:
- Atidarykite šablono failą, kuriame išsaugojome makrokomandą FormatData.
- Sukurkite naują makrokomandą pavadinimu LoadData.
- Įrašant makrokomandą LoadData importuoti duomenis iš failo data.csv – kaip darėme ankstesnėje pamokos dalyje.
- Kai importavimas bus baigtas, sustabdykite makrokomandos įrašymą.
- Ištrinkite visus duomenis iš langelių.
- Išsaugokite failą kaip „Excel“ šabloną su makrokomandomis (XLTM plėtinys).
Taigi, paleidę šį šabloną, gaunate prieigą prie dviejų makrokomandų – viena įkelia duomenis, kita – formatuoja.
Jei norite pradėti programuoti, galite sujungti šių dviejų makrokomandų veiksmus į vieną – tiesiog nukopijuodami kodą iš LoadData iki kodo pradžios FormatData.