Sužinokite, kaip naudoti Excel makrokomandas, kad automatizuotumėte nuobodus uždavinius

Viena iš galingesnių, bet retai naudojamų „Excel“ funkcijų yra gebėjimas labai lengvai sukurti automatines užduotis ir individualią logiką makrose. Makrokomandos yra idealus būdas sutaupyti laiko nuspėjamoms, pasikartojančioms užduotims ir standartizuoti dokumentų formatus - daug kartų nereikia rašyti vienos eilutės kodo.

Jei esate smalsu, kas yra makrokomandos, ar kaip jas sukurti, jokių problemų - mes nueisime jus per visą procesą.

Pastaba: tas pats procesas turėtų veikti daugelyje „Microsoft Office“ versijų. Ekrano nuotraukos gali atrodyti šiek tiek kitokios.

Kas yra makro?

„Microsoft Office“ makro (kadangi ši funkcija taikoma kelioms „MS Office“ programoms) yra „Visual Basic for Applications“ (VBA) kodas, išsaugotas dokumento viduje. Dėl panašios analogijos pagalvokite apie dokumentą kaip HTML ir makro kaip „Javascript“. Daugeliu atvejų, kai „JavaScript“ gali manipuliuoti HTML tinklalapyje, makro gali manipuliuoti dokumentu.

Makrokomandos yra neįtikėtinai galingos ir gali padaryti viską, ką gali įsivaizduoti jūsų vaizduotė. Kaip (labai) trumpą funkcijų sąrašą, kurį galite atlikti naudodami makrokomandą:

• Taikyti stilių ir formatavimą.
• Manipuliuoti duomenis ir tekstą.
• Bendraukite su duomenų šaltiniais (duomenų baze, tekstiniais failais ir tt).
• Sukurkite visiškai naujus dokumentus.
• Bet koks aukščiau paminėtas bet koks derinys.

Makro kūrimas: paaiškinimas pagal pavyzdį

Pradedame nuo jūsų sodo veislės CSV failo. Nieko ypatingo čia, tik 10 × 20 rinkinių nuo 0 iki 100 ir eilutės, ir stulpelio antraštės. Mūsų tikslas - parengti gerai suformatuotą, reprezentatyvų duomenų lapą, kuriame būtų pateiktos kiekvienos eilutės suvestinės.

Kaip jau minėjome, makrokomandas yra VBA kodas, bet vienas iš gražiausių „Excel“ dalykų yra tai, kad galite sukurti / įrašyti juos nuliniu kodavimu - kaip tai darysime čia.

Norėdami sukurti makrokomandą, eikite į „View“> „Makros“> „Record Macro“.

Priskirkite makrokomandą pavadinimui (be tarpų) ir spustelėkite Gerai.

Kai tai bus padaryta, visi įrašomi veiksmai - kiekvienas ląstelių keitimas, slinkties veiksmas, lango dydžio keitimas, pavadinimas.

Yra keletas vietų, kuriose nurodoma, kad „Excel“ yra įrašymo režimas. Vienas iš jų - peržiūrėti makro meniu ir pažymėti, kad „Stop Recording“ pakeitė „Record Macro“ parinktį.

Kitas yra apatiniame dešiniajame kampe. „Stop“ piktograma rodo, kad jis yra makro režimu, ir paspaudus čia bus sustabdytas įrašymas (taip pat, kai nėra įrašymo režimo, ši piktograma bus „Record Macro“ mygtukas, kurį galite naudoti vietoj „Macros“ meniu).

Dabar, kai įrašome makrokomandą, taikykime mūsų suvestinius skaičiavimus. Pirmiausia pridėkite antraštes.

Toliau naudokite atitinkamas formules (atitinkamai):

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

Dabar paryškinkite visus skaičiavimo langelius ir vilkite visų mūsų duomenų eilučių ilgį, kad pritaikytumėte skaičiavimus kiekvienai eilutei.

Kai tai bus padaryta, kiekviena eilutė turi rodyti atitinkamas jų santraukas.

Dabar norime gauti viso lapo suvestinius duomenis, todėl taikome dar keletą skaičiavimų:

Atitinkamai:

• = SUM (L2: L21)
• = VIDUTINIS (B2: K21) *Tai turi būti apskaičiuojama pagal visus duomenis, nes eilutės vidurkių vidurkis nebūtinai yra lygus visų verčių vidurkiui.
• = MIN (N2: N21)
• = MAX (O2: O21)
• = MEDIAN (B2: K21) * Apskaičiuota visais duomenimis dėl tos pačios priežasties kaip ir anksčiau.

Atlikus skaičiavimus, taikysime stilių ir formatavimą. Pirmiausia naudokite bendrą numerių formatavimą visose ląstelėse, atlikdami „Select All“ (Pasirinkti viską) (arba „Ctrl + A“ arba spustelėdami langelį tarp eilutės ir stulpelių antraščių) ir pasirinkite „Comma Style“ piktogramą pagrindiniame meniu.

Be to, naudokite kai kuriuos vizualius formatus tiek eilutės, tiek stulpelio antraštėse:

• Bold.
• Centruotas.
• Fono užpildymo spalva.

Ir, galiausiai, pritaikykite bendrą stilių.

Kai viskas baigta, atrodo, kaip atrodo mūsų duomenų lapas:

Kadangi esame patenkinti rezultatais, sustabdykite makro įrašymą.

Sveikiname - ką tik sukūrėte „Excel“ makrokomandą.

Kad galėtume naudoti mūsų naujai įrašytą makrokomandą, mes turime išsaugoti „Excel“ darbaknygę makro režimu. Tačiau prieš tai atlikdami pirmiausia turime išvalyti visus esamus duomenis, kad jie nebūtų įterpti į mūsų šabloną (idėja yra kaskart naudojant šį šabloną, importuosime naujausius duomenis).

Norėdami tai padaryti, pasirinkite visus langelius ir juos ištrinkite.

Kai duomenys dabar išvalomi (bet makrokomandos vis dar yra įtrauktos į „Excel“ failą), mes norime išsaugoti failą kaip makro įjungtą šabloną (XLTM). Svarbu pažymėti, kad jei išsaugosite jį kaip standartinį šabloną (XLTX), tada bus makrokomandos ne gali būti paleisti iš jo. Arba galite išsaugoti failą kaip senąjį šabloną (XLT), kuris leis paleisti makrokomandas.

Išsaugoję failą kaip šabloną, eikite į priekį ir uždarykite „Excel“.

Naudojant „Excel Macro“

Prieš padengiant, kaip galime taikyti šią naujai įrašytą makrokomandą, svarbu aptarti keletą taškų apie makrokomandas apskritai:

• Makrokomandos gali būti kenkėjiškos.
• Žr. Aukščiau esantį punktą.

VBA kodas iš tikrųjų yra gana galingas ir gali manipuliuoti failais už dabartinio dokumento ribų. Pavyzdžiui, makrokomanda gali pakeisti arba ištrinti atsitiktinius failus aplanke „Mano dokumentai“. Todėl svarbu įsitikinti, kad jūs tik paleisti makrokomandas iš patikimų šaltinių.

Norėdami naudoti mūsų duomenų formato makrokomandą, atidarykite aukščiau sukurtą „Excel“ šablono failą. Kai tai padarysite, darant prielaidą, kad įjungti standartiniai saugumo nustatymai, darbaknygės viršuje pamatysite įspėjimą, kuriame teigiama, kad makrokomandos yra išjungtos. Kadangi pasitikime savimi sukurta makro, spustelėkite mygtuką „Įjungti turinį“.

Toliau ketiname importuoti naujausius duomenų rinkinius iš CSV (tai yra šaltinis, naudojamas darbo maketui sukurti).

Kad užbaigtumėte CSV failo importą, gali tekti nustatyti kelias parinktis, kad „Excel“ galėtų ją teisingai interpretuoti (pvz., Ribotuvas, esamos antraštės ir tt).

Kai mūsų duomenys bus importuoti, tiesiog eikite į makrokomandų meniu (skirtuke „View“) ir pasirinkite „View Macros“ (peržiūrėti makrokomandas).

Atidarytame dialogo lange matome „FormatData“ makrokomandą, kurią įrašėme aukščiau. Pasirinkite jį ir spustelėkite Vykdyti.

Kai paleisite, galite matyti, kad žymeklis per kelias akimirkas šokinėja, bet kaip matysite manipuliuojamus duomenis tiksliai kaip mes jį įrašėme. Kai viskas yra pasakyta ir padaryta, ji turėtų atrodyti kaip mūsų originalas, išskyrus skirtingus duomenis.

Žiūrint po gaubtu: kas daro makro darbą

Kaip minėjome keletą kartų, makrokomandą valdo „Visual Basic for Applications“ (VBA) kodas. Kai „įrašote“ makrokomandą, „Excel“ iš tikrųjų verčia viską, ką darote, į atitinkamas VBA instrukcijas. Paprasčiausiai - nereikia rašyti jokio kodo, nes „Excel“ jums rašo kodą.

Norėdami peržiūrėti kodą, kuris daro mūsų makrokomandą, iš makrokomandos dialogo spustelėkite mygtuką Redaguoti.

Atsidariusiame lange rodomas šaltinis, kuris buvo įrašytas iš mūsų veiksmų kuriant makrokomandą. Žinoma, galite redaguoti šį kodą arba net sukurti visiškai makrokomandas tik kodo lango viduje. Nors šiame straipsnyje naudojamas įrašymo veiksmas greičiausiai atitiks daugumą poreikių, labiau pritaikyti veiksmai ar sąlyginiai veiksmai reikalauja redaguoti šaltinio kodą.

Imdamiesi vieno žingsnio pavyzdžio ...

Hipotetiškai manome, kad mūsų šaltinio duomenų failas data.csv gaminamas automatizuotu procesu, kuris visada išsaugo failą toje pačioje vietoje (pvz., C: Duomenys.csv visada yra naujausi duomenys). Šio failo atidarymo ir importavimo procesas taip pat gali būti lengvai įterpiamas į makrokomandą:

1. Atidarykite „Excel“ šablono failą, kuriame yra „FormatData“ makro.
2. Įrašykite naują makrokomandą „LoadData“.
3. Naudodami makrokomandą, importuokite duomenų failą, kaip paprastai.
4. Kai duomenys importuojami, sustabdykite makro įrašymą.
5. Ištrinti visus ląstelių duomenis (pasirinkite visus, tada ištrinkite).
6. Išsaugokite atnaujintą šabloną (nepamirškite naudoti makro įgalinto šablono formato).

Kai tai bus padaryta, kai bus atidarytas šablonas, bus du makrokomandos - viena, kuri įkels mūsų duomenis, o kita, kuri ją formatuoja.

Jei tikrai norėjote nešvarias rankas su šiek tiek kodų redagavimu, šiuos veiksmus galite lengvai sujungti į vieną makrokomandą, nukopijuodami iš „LoadData“ pagamintą kodą ir įterpdami jį kodo pradžioje iš „FormatData“.

Atsisiųskite šį šabloną

Jūsų patogumui mes įtraukėme ir šiame straipsnyje sukurtą „Excel“ šabloną, ir mėginio duomenų failą, kad galėtumėte žaisti.

Atsisiųskite „Excel“ makro šabloną iš „How-To Geek“