Aflați cum să utilizați macrocomenzile Excel pentru a automatiza sarcini plictisitoare

Una dintre cele mai puternice, dar rareori utilizate funcții ale Excel este abilitatea de a crea foarte ușor sarcini automate și logică personalizată în cadrul macro-urilor. Macroanele oferă o modalitate ideală de a economisi timp pe sarcini previzibile, repetitive, precum și de a standardiza formatele de documente - de multe ori fără a fi nevoie să scrieți o singură linie de cod.

Dacă sunteți curios ce macro-uri sunt sau cum să le creați de fapt, nici o problemă - vă vom trece prin întregul proces.

Notă: același proces ar trebui să funcționeze în majoritatea versiunilor de Microsoft Office. Capturile de ecran ar putea părea puțin diferite.

Ce este un macro?

O macrocomandă Microsoft Office (deoarece această funcționalitate se aplică mai multor aplicații MS Office) este doar codul Visual Basic for Applications (VBA) salvat într-un document. Pentru o analogie comparabilă, gândiți-vă la un document ca HTML și la o macrocomandă ca Javascript. În mare parte în același mod în care Javascript poate manipula HTML pe o pagină web, o macrocomandă poate manipula un document.

Macroanele sunt incredibil de puternice și pot face destul de mult imaginația voastră. Ca o (foarte) scurtă listă de funcții ce se poate face cu o macrocomandă:

Aplicați stilul și formatarea.
Manipulați datele și textul.
Comunicați cu sursele de date (bază de date, fișiere text etc.).
Creați documente complet noi.
Orice combinație, în orice ordine, a oricăreia dintre cele de mai sus.

Crearea unui macro: o explicație prin exemplu

Începem cu fișierul CSV pentru grădină. Nimic special aici, doar un set de 10 × 20 de numere între 0 și 100, atât cu antetul rândului cât și cu coloana. Scopul nostru este de a produce o fișă de date bine formatată, prezentabilă, care să includă rezumate sumare pentru fiecare rând.

Așa cum am afirmat mai sus, o macrocomandă este cod VBA, dar unul dintre lucrurile frumoase despre Excel este că le puteți crea / înregistra cu zero codare necesară - așa cum o vom face aici.

Pentru a crea o macrocomandă, accesați Vizualizare> Macrouri> Înregistrare macro.

Atribuiți macrocomenzii un nume (fără spații) și faceți clic pe OK.

Odată ce acest lucru este făcut, toate din acțiunile dvs. sunt înregistrate - fiecare schimbare de celule, acțiunea de defilare, redimensionarea ferestrei, o numiți.

Există câteva locuri care indică faptul că Excel este modul de înregistrare. Unul este vizualizarea meniului Macro și notarea că opțiunea Înregistrare oprită a înlocuit opțiunea pentru Înregistrare macro.

Celălalt este în colțul din dreapta jos. Pictograma "stop" indică faptul că este în modul macro și apăsând aici se va opri înregistrarea (de asemenea, atunci când nu este în modul înregistrare, această pictogramă va fi butonul Înregistrare macro, pe care îl puteți utiliza în loc să accesați meniul Macrocomenzi).

Acum, când înregistrăm macro-ul nostru, să aplicăm calculele sumare. Mai întâi adăugați anteturile.

Apoi, aplicați formulele corespunzătoare (respectiv):

= SUM (B2: K2)
= AVERAGE (B2: K2)
= MIN (B2: K2)
= MAX (B2: K2)
= MEDIAN (B2: K2)

Acum, evidențiați toate celulele de calcul și trageți lungimea tuturor rândurilor de date pentru a aplica calculele pentru fiecare rând.

Odată ce acest lucru este făcut, fiecare rând ar trebui să afișeze rezumatele lor respective.

Acum, dorim să obținem datele de sinteză pentru întreaga foaie, așadar vom aplica și alte calcule:

Respectiv:

= SUM (L2: L21)
= MEDIU (B2: K21) *Aceasta trebuie să fie calculată pentru toate datele, deoarece media mediilor rând nu este în mod necesar egală cu media tuturor valorilor.
= MIN (N2: N21)
= MAX (O2: O21)
= MEDIAN (B2: K21) * Calculat pentru toate datele pentru același motiv ca mai sus.

Acum că se fac calculele, vom aplica stilul și formatarea. Mai întâi aplicați formatarea generală a numerelor pe toate celulele făcând o selectare a tuturor (fie Ctrl + A, fie faceți clic pe celulă între anteturile rândului și coloanei) și selectați pictograma "Stilul comenzii" din meniul Acasă.

Apoi, aplicați unele formatări vizuale atât la anteturile de rând, cât și la coloane:

Îndrăzneţ.
Centrat.
Culoarea fundalului de fundal.

Și, în sfârșit, aplicați un anumit stil totalurilor.

Când totul este terminat, aceasta este foaia noastră de date:

Deoarece suntem mulțumiți de rezultate, opriți înregistrarea macrocomenzii.

Felicitări - tocmai ați creat o macrocomandă Excel.

Pentru a folosi macroul nou înregistrat, trebuie să salvăm cartea noastră de lucru Excel într-un format de fișiere cu funcții de tip macro. Cu toate acestea, înainte de a face acest lucru, trebuie mai întâi să ștergem toate datele existente, astfel încât să nu fie încorporate în șablonul nostru (ideea fiind de fiecare dată când folosim acest șablon, vom importa cele mai actualizate date).

Pentru aceasta, selectați toate celulele și ștergeți-le.

Odată cu ștergerea datelor (dar macrocomenzile incluse în fișierul Excel), dorim să salvăm fișierul ca fișier șablon macroactiv (XLTM). Este important să rețineți că, dacă salvați acest fișier ca fișier standard (XLTX), atunci macrocomenzile o vor face nu să poată fi condus de la el. Alternativ, puteți salva fișierul ca fișier șablon legacy (XLT), care va permite rularea macrocomenzilor.

Odată ce ați salvat fișierul ca șablon, continuați și închideți Excel.

Utilizarea unui Macro Excel

Înainte de a descrie modul în care putem aplica această macrocomandă recent înregistrată, este important să acoperim câteva puncte despre macrocomenzi în general:

Macrourile pot fi rău intenționate.
Vedeți punctul de mai sus.

Codul VBA este de fapt destul de puternic și poate manipula fișiere în afara domeniului de aplicare al documentului curent. De exemplu, o macrocomandă ar putea modifica sau șterge fișiere aleatorii în dosarul Documentele mele. Ca atare, este important să vă asigurați că sunteți numai rulați macro-uri din surse de încredere.

Pentru a folosi macroul format al datelor, deschideți fișierul Excel Template creat mai sus. Când faceți acest lucru, presupunând că aveți setările standard de securitate activate, veți vedea un avertisment în partea de sus a listei de lucru, care spune că macrocomenzile sunt dezactivate. Pentru că avem încredere în o macrocomandă creată de noi înșine, faceți clic pe butonul "Activați conținutul".

În continuare, vom importa ultimul set de date dintr-un CSV (aceasta este sursa foaia de lucru utilizată pentru a crea macrocomanda noastră).

Pentru a finaliza importul fișierului CSV, poate fi necesar să setați câteva opțiuni pentru ca Excel să îl interpreteze corect (de exemplu delimiter, anteturi prezente etc.).

Odată ce datele noastre sunt importate, mergeți pur și simplu la meniul Macrocomenzi (în fila Vizualizare) și selectați Vizualizare macrocomenzi.

În caseta de dialog rezultată, vedem macrocomanda "FormatData" pe care am înregistrat-o mai sus. Selectați-l și faceți clic pe Executare.

Odată ce rulează, este posibil să vedeți saltul cursorului în jur pentru câteva momente, dar pe măsură ce veți vedea datele pe care le manipulați exact așa cum am înregistrat-o. Când totul este spus și făcut, ar trebui să arate exact ca originalul nostru - cu excepția datelor diferite.

Privind sub cap: Ce face un Macro de lucru

Așa cum am menționat de câteva ori, o macrocomandă este condusă de codul Visual Basic for Applications (VBA). Când "înregistrați" o macrocomandă, Excel traduce tot ceea ce faceți în instrucțiunile VBA respective. Pentru a spune pur și simplu - nu trebuie să scrieți niciun cod deoarece Excel scrie codul pentru dvs..

Pentru a vedea codul care face ca macrocomanda să fie difuzată, faceți clic pe butonul Editare din dialogul Macrocomenzi.

Fereastra care se deschide afișează codul sursă care a fost înregistrat din acțiunile noastre la crearea macrocomenzii. Desigur, puteți să editați acest cod sau chiar să creați macrocomenzi noi în întregime în interiorul ferestrei de cod. În timp ce acțiunea de înregistrare utilizată în acest articol va corespunde probabil celor mai multe nevoi, acțiuni mai personalizate sau acțiuni condiționate ar necesita modificarea codului sursă.

Luând exemplul nostru un pas mai departe ...

Din punct de vedere ipotetic, presupunem că fișierul nostru de date sursă, data.csv, este produs printr-un proces automatizat care salvează întotdeauna fișierul în aceeași locație (de exemplu, C: \ Data \ data.csv este întotdeauna cele mai recente date). Procesul de deschidere a acestui fișier și de import al acestuia se poate face cu ușurință și într-o macrocomandă:

Deschideți fișierul Șablon Excel care conține macrocomanda "FormatData".
Înregistrați o nouă macro numită "LoadData".
Cu înregistrarea macro, importați fișierul de date ca în mod normal.
Odată ce datele sunt importate, opriți înregistrarea macrocomenzii.
Ștergeți toate datele despre celule (selectați toate apoi ștergeți).
Salvați șablonul actualizat (nu uitați să utilizați un format de șablon macro activat).

Odată ce acest lucru este făcut, ori de câte ori șablonul este deschis, vor exista două macrocomenzi - una care încarcă datele noastre și cealaltă care o formatează.

Dacă doriți cu adevărat să vă murdăriți mâinile cu un pic de editare de cod, puteți combina cu ușurință aceste acțiuni într-o singură macrocomandă prin copierea codului produs din "LoadData" și inserarea acestuia la începutul codului din "FormatData".

Descărcați acest șablon

Pentru comoditatea dvs., am inclus atât șablonul Excel produs în acest articol, cât și un fișier de date de probă pentru a vă putea juca.

Descărcați Excel Macro Template de la How-To Geek