Uzziniet, kā izmantot Excel makro, lai automatizētu garlaicīgos uzdevumus

Viena no spēcīgākajām, bet reti izmantotajām Excel funkcijām ir spēja ļoti viegli izveidot automatizētus uzdevumus un pielāgotu loģiku makros. Makro ir ideāls veids, kā ietaupīt laiku uz paredzamiem, atkārtotiem uzdevumiem, kā arī standartizēt dokumentu formātus - daudzas reizes bez rakstīt vienu rindas kodu.

Ja jūs esat ziņkārīgs par to, kas ir makro vai kā tos faktiski izveidot, nekādu problēmu - mēs jūs aizvedīsim cauri visam procesam.

Piezīme: šim procesam vajadzētu darboties lielākajā daļā Microsoft Office versiju. Ekrānuzņēmumi var izskatīties nedaudz atšķirīgi.

Kas ir makro?

Microsoft Office makro (jo šī funkcija attiecas uz vairākām MS Office lietojumprogrammām) ir vienkārši Visual Basic for Applications (VBA) kods, kas saglabāts dokumenta iekšpusē. Par līdzīgu analoģiju, domājiet par dokumentu kā HTML un makro kā Javascript. Tādā pašā veidā, kā JavaScript var manipulēt ar HTML tīmekļa lapā, makro var manipulēt ar dokumentu.

Makro ir neticami spēcīgi un var darīt diezgan daudz, ko var iztēloties jūsu iztēle. Kā (ļoti) īsu funkciju sarakstu, ko var veikt ar makro:

Lietot stilu un formatējumu.
Manipulēt datus un tekstu.
Sazināties ar datu avotiem (datu bāze, teksta faili utt.).
Izveidojiet pilnīgi jaunus dokumentus.
Jebkura kombinācija jebkurā secībā no jebkura iepriekš minētā.

Makro izveide: skaidrojums ar piemēru

Mēs sākam ar jūsu dārza šķirnes CSV failu. Nekas īpašs šeit, tikai 10 × 20 skaitļu kopums no 0 līdz 100 gan ar rindu, gan kolonnu. Mūsu mērķis ir izveidot labi formatētu, prezentējamu datu lapu, kurā ir apkopotas kopsavilkumi par katru rindu.

Kā minēts iepriekš, makro ir VBA kods, bet viena no jaukajām lietām programmā Excel ir tas, ka jūs varat izveidot / ierakstīt tos ar nulles kodēšanu, kā to darīsim šeit.

Lai izveidotu makro, dodieties uz View> Macros> Record Macro.

Piešķirt makro nosaukumu (bez atstarpēm) un noklikšķiniet uz Labi.

Kad tas ir izdarīts, viss jūsu darbības tiek ierakstītas - katru šūnu maiņu, ritināšanas darbību, loga izmēru, jūs to nosauksiet.

Ir vairākas vietas, kas norāda, ka Excel ir ierakstīšanas režīms. Viens no tiem ir Macro izvēlnes apskate un atzīmējot, ka Stop Recording ir nomainījis iespēju Record Macro.

Otrs ir labajā apakšējā stūrī. Ikona “stop” norāda, ka tā ir makro režīmā, un nospiežot šeit tiks pārtraukta ierakstīšana (tāpat, ja tā nav ierakstīšanas režīmā, šī ikona būs poga Record Macro, kuru var izmantot, nevis dodoties uz makro izvēlni).

Tagad, kad mēs ierakstām mūsu makro, piemērojam mūsu kopsavilkuma aprēķinus. Vispirms pievienojiet galvenes.

Tālāk piemērojiet atbilstošās formulas (attiecīgi):

= SUM (B2: K2)
= VIDĒJAIS (B2: K2)
= MIN (B2: K2)
= MAX (B2: K2)
= MEDIAN (B2: K2)

Tagad iezīmējiet visas aprēķinu šūnas un velciet visu mūsu datu rindu garumu, lai piemērotu aprēķinus katrai rindai.

Kad tas ir paveikts, katrai rindai jāparāda attiecīgie kopsavilkumi.

Tagad mēs vēlamies iegūt kopsavilkuma datus par visu lapu, tāpēc mēs izmantojam vēl dažus aprēķinus:

Attiecīgi:

= SUM (L2: L21)
= VIDĒJAIS (B2: K21) *Tas jāaprēķina visiem datiem, jo rindu vidējo rādītāju vidējais lielums ne vienmēr ir vienāds ar visu vērtību vidējo vērtību.
= MIN (N2: N21)
= MAX (O2: O21)
= MEDIAN (B2: K21) * Aprēķināts pēc visiem datiem tādā pašā nolūkā kā iepriekš.

Tagad, kad ir veikti aprēķini, mēs piemērosim stilu un formatējumu. Vispirms izmantojiet vispārējo numuru formatējumu visās šūnās, veicot Atlasīt visu (vai nu Ctrl + A, vai arī noklikšķiniet uz šūnas starp rindu un kolonnu virsrakstiem) un izvēlnē Sākums atlasiet ikonu “Komatu stils”.

Tālāk piemērojiet vizuālu formatējumu gan rindu, gan kolonnu virsrakstos:

Treknrakstā.
Centrēts.
Fona aizpildījuma krāsa.

Un visbeidzot, piemērojiet kopumu kopumu.

Kad viss ir pabeigts, mūsu datu lapa izskatās šādi:

Tā kā mēs esam apmierināti ar rezultātiem, pārtrauciet makro ierakstīšanu.

Apsveicam - tikko esat izveidojis Excel makro.

Lai izmantotu mūsu nesen ierakstīto makro, mums ir jāsaglabā Excel darbgrāmata makro iespējotā faila formātā. Tomēr, pirms mēs to darām, mums vispirms ir jāizdzēš visi esošie dati, lai tas nebūtu iekļauts mūsu veidnē (ideja tiek izmantota katru reizi, kad izmantojam šo veidni, mēs importēsim jaunākos datus).

Lai to izdarītu, atlasiet visas šūnas un izdzēsiet tās.

Kad dati tagad ir izdzēsti (bet makro joprojām ir iekļauti Excel failā), mēs vēlamies saglabāt failu kā makro iespējotu veidni (XLTM). Ir svarīgi atzīmēt, ka, saglabājot to kā standarta veidnes (XLTX) failu, tad makro būs ne jāspēj palaist no tā. Varat arī saglabāt failu kā mantojuma veidni (XLT), kas ļaus palaist makro.

Kad esat saglabājis failu kā veidni, dodieties uz priekšu un aizveriet programmu Excel.

Izmantojot Excel makro

Pirms aptverat, kā mēs varam piemērot šo jauno ierakstīto makro, ir svarīgi aptvert dažus punktus par makro vispār:

Makro var būt ļaunprātīga.
Skatīt iepriekš minēto.

VBA kods faktiski ir diezgan spēcīgs un var manipulēt ar failiem ārpus pašreizējā dokumenta darbības jomas. Piemēram, makro var mainīt vai dzēst izlases failus mapē Mani dokumenti. Tāpēc ir svarīgi, lai jūs būtu pārliecināts tikai palaist makro no uzticamiem avotiem.

Lai izmantotu mūsu datu formāta makro, atveriet iepriekš izveidoto Excel veidnes failu. To darot, pieņemot, ka ir iespējoti standarta drošības iestatījumi, darbgrāmatas augšpusē redzēsiet brīdinājumu, ka makro ir atspējoti. Tā kā mēs uzticamies mūsu izveidotam makrokomandam, noklikšķiniet uz pogas “Iespējot saturu”.

Tālāk mēs importēsim jaunāko datu kopu no CSV (tas ir avots, kuru izmanto, lai izveidotu makro).

Lai pabeigtu CSV faila importēšanu, iespējams, būs jāiestata dažas iespējas, lai programma Excel varētu to pareizi interpretēt (piem., Norobežotājs, klātbūtne, utt.).

Kad mūsu dati ir importēti, vienkārši dodieties uz izvēlni Makro (zem cilnes Skats) un atlasiet Skatīt makro.

Rezultātā redzamajā dialoglodziņā mēs redzam iepriekš ierakstīto makro “FormatData”. Atlasiet to un noklikšķiniet uz Palaist.

Kad darbojas, jūs varat redzēt, ka kursors pāris mirkļus pārlēkt, bet tā kā jūs redzēsiet manipulācijas datus precīzi kā mēs to ierakstījām. Kad viss ir teikts un darīts, tam vajadzētu izskatīties tāpat kā mūsu oriģināls - izņemot ar dažādiem datiem.

Aplūkojot kapuci: kas padara makro darbu

Kā jau minējām pāris reizes, makro vada Visual Basic for Applications (VBA) kods. Kad jūs ierakstāt makro, Excel faktiski pārvērš visu, ko jūs darāt, savā VBA instrukcijā. Vienkārši sakot - jums nav jāraksta nekāds kods, jo programma Excel raksta kodu.

Lai skatītu kodu, kas padara mūsu makro darbināšanu, dialoglodziņā Makro noklikšķiniet uz pogas Rediģēt.

Atvērtais logs parāda avota kodu, kas tika ierakstīts no mūsu darbībām, veidojot makro. Protams, jūs varat rediģēt šo kodu vai pat izveidot jaunus makro veidus tikai koda loga iekšpusē. Lai gan šajā rakstā izmantotā ieraksta darbība, visticamāk, atbilst lielākai daļai vajadzību, vairāk pielāgotām darbībām vai nosacītām darbībām būtu jāmaina avota kods.

Veicot mūsu piemēru vienu soli tālāk ...

Hipotētiski pieņemsim, ka mūsu avota datu fails, data.csv, tiek ražots ar automatizētu procesu, kas vienmēr saglabā failu tajā pašā vietā (piemēram, C: Data.csv vienmēr ir jaunākie dati). Atverot šo failu un importējot to, var viegli veikt arī makro:

Atveriet Excel veidnes failu, kas satur makro “FormatData”.
Ierakstiet jaunu makro ar nosaukumu “LoadData”.
Ar makro ierakstu importējiet tādus datu failus kā parasti.
Kad dati ir importēti, pārtrauciet makro ierakstīšanu.
Dzēst visus šūnu datus (atlasiet visus, tad izdzēsiet).
Saglabājiet atjaunināto veidni (neaizmirstiet izmantot makro iespējotu veidnes formātu).

Kad tas ir paveikts, tad, kad veidne tiek atvērta, būs divi makro - viens, kas ielādē mūsu datus un otrs, kas to formatē.

Ja jūs patiešām vēlējāties, lai jūsu rokas būtu netīras ar nelielu koda rediģēšanu, jūs varētu viegli apvienot šīs darbības vienā makro, kopējot kodu, kas iegūts no “LoadData”, un ievietojot to koda sākumā no “FormatData”.

Lejupielādēt šo veidni

Jūsu ērtībai mēs esam iekļāvuši gan šajā rakstā izveidoto Excel veidni, gan izlases datu failu, lai jūs varētu spēlēt apkārt.

Lejupielādēt Excel makro veidni no How-To Geek