Cuprins
Excel are o capacitate puternică, dar în același timp foarte rar folosită, de a crea secvențe automate de acțiuni folosind macrocomenzi. O macrocomandă este o ieșire ideală dacă aveți de-a face cu același tip de sarcină care se repetă de multe ori. De exemplu, prelucrarea datelor sau formatarea documentelor conform unui șablon standardizat. În acest caz, nu aveți nevoie de cunoștințe de limbaje de programare.
Ești deja curios ce este o macro și cum funcționează? Apoi continuați cu îndrăzneală – apoi vom face pas cu pas întregul proces de a crea o macrocomandă împreună cu dvs.
Ce este Macro?
O macrocomandă în Microsoft Office (da, această funcționalitate funcționează la fel în multe aplicații ale pachetului Microsoft Office) este un cod de program într-un limbaj de programare Visual Basic pentru aplicații (VBA) stocate în document. Pentru a fi mai clar, un document Microsoft Office poate fi comparat cu o pagină HTML, apoi o macrocomandă este un analog cu Javascript. Ce poate face Javascript cu datele HTML dintr-o pagină web este foarte asemănător cu ceea ce poate face o macrocomandă cu datele dintr-un document Microsoft Office.
Macro-urile pot face aproape orice doriți într-un document. Iată câteva dintre ele (o parte foarte mică):
- Aplicați stiluri și formatare.
- Efectuați diverse operații cu date numerice și text.
- Utilizați surse de date externe (fișiere de baze de date, documente text etc.)
- Creați un document nou.
- Faceți toate cele de mai sus în orice combinație.
Crearea unui macro – un exemplu practic
De exemplu, să luăm cel mai comun fișier CSV. Acesta este un tabel simplu de 10×20 plin cu numere de la 0 la 100 cu titluri pentru coloane și rânduri. Sarcina noastră este să transformăm acest set de date într-un tabel formatat prezentabil și să generăm totaluri pe fiecare rând.
După cum sa menționat deja, o macrocomandă este un cod scris în limbajul de programare VBA. Dar în Excel, puteți crea un program fără a scrie o linie de cod, ceea ce vom face chiar acum.
Pentru a crea o macrocomandă, deschideți Vizualizare (Tip) > Macrocomenzi (Macro) > Înregistrați macro (Înregistrare macro...)
Dați macrocomenzii un nume (fără spații) și faceți clic OK.
Începând din acest moment, sunt înregistrate TOATE acțiunile tale cu documentul: modificări ale celulelor, derularea tabelului, chiar redimensionarea ferestrei.
Excel semnalează că modul de înregistrare macro este activat în două locuri. În primul rând, în meniu Macrocomenzi (Macro) – în loc de șir Înregistrați macro A apărut linia (Înregistrarea unei macrocomenzi...). Opriți înregistrarea (Opriți înregistrarea).
În al doilea rând, în colțul din stânga jos al ferestrei Excel. Pictogramă Stop (pătrat mic) indică faptul că modul de înregistrare macro este activat. Făcând clic pe el, înregistrarea va fi oprită. În schimb, când modul de înregistrare nu este activat, există o pictogramă pentru a activa înregistrarea macro în această locație. Făcând clic pe acesta, se va da același rezultat ca și pornirea înregistrării prin meniu.
Acum că modul de înregistrare macro este activat, să trecem la sarcina noastră. Mai întâi de toate, să adăugăm anteturi pentru datele rezumate.
Apoi, introduceți formulele în celule în conformitate cu numele titlurilor (se oferă variante ale formulelor pentru engleză și versiuni de Excel, adresele celulelor sunt întotdeauna litere și numere latine):
- =SUMA(B2:K2) or =SUMA(B2:K2)
- =MEDIE(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)
Acum selectați celulele cu formule și copiați-le în toate rândurile tabelului nostru trăgând mânerul de completare automată.
După ce finalizați acest pas, fiecare rând ar trebui să aibă totalurile corespunzătoare.
În continuare, vom rezuma rezultatele pentru întregul tabel, pentru aceasta mai facem câteva operații matematice:
Respectiv:
- =SUMA(L2:L21) or =SUMA(L2:L21)
- =MEDIE(B2:K21) or =СРЗНАЧ(B2:K21) – pentru a calcula această valoare, este necesar să luăm exact datele inițiale ale tabelului. Dacă luați media mediilor pentru rânduri individuale, rezultatul va fi diferit.
- =MIN(N2:N21) or =MIN(N2:N21)
- =MAX(O2:O21) or =MAX(O2:O21)
- =MEDIANĂ(B2:K21) or =MEDIANĂ(B2:K21) – avem în vedere utilizarea datelor inițiale ale tabelului, din motivul indicat mai sus.
Acum că am terminat cu calculele, hai să facem niște formatări. Mai întâi, să setăm același format de afișare a datelor pentru toate celulele. Selectați toate celulele de pe foaie, pentru a face acest lucru, utilizați comanda rapidă de la tastatură Ctrl + Asau faceți clic pe pictogramă selectați toate, care se află la intersecția titlurilor rândurilor și coloanelor. Apoi apasa Stil virgulă (Format delimitat). Acasă (Acasă).
Apoi, modificați aspectul antetelor de coloană și rând:
- Stilul de font aldine.
- Alinierea la centru.
- Umplere de culoare.
Și, în sfârșit, să stabilim formatul totalurilor.
Asa ar trebui sa arate in final:
Dacă totul vă convine, opriți înregistrarea macro-ului.
Felicitări! Tocmai ați înregistrat prima dvs. macrocomandă în Excel.
Pentru a utiliza macrocomandă generată, trebuie să salvăm documentul Excel într-un format care acceptă macrocomenzi. În primul rând, trebuie să ștergem toate datele din tabelul pe care l-am creat, adică să îl facem un șablon gol. Faptul este că în viitor, lucrând cu acest șablon, vom importa în el cele mai recente și relevante date.
Pentru a șterge toate celulele din date, faceți clic dreapta pe pictogramă selectați toate, care se află la intersecția titlurilor rândurilor și coloanelor, iar din meniul contextual, selectați Șterge (Șterge).
Acum foaia noastră este complet șters de toate datele, în timp ce macro-ul rămâne înregistrat. Trebuie să salvăm registrul de lucru ca șablon Excel activat cu macro care are extensia XLTM.
Un punct important! Dacă salvați fișierul cu extensia XLTX, atunci macro-ul nu va funcționa în ea. Apropo, puteți salva registrul de lucru ca șablon Excel 97-2003, care are formatul XLT, acceptă și macrocomenzi.
Când șablonul este salvat, puteți închide Excel în siguranță.
Rularea unei macrocomenzi în Excel
Înainte de a dezvălui toate posibilitățile macrocomenzii pe care le-ați creat, cred că este corect să acordați atenție câteva puncte importante referitoare la macrocomenzi în general:
- Macro-urile pot fi dăunătoare.
- Citiți din nou paragraful anterior.
Codul VBA este foarte puternic. În special, poate efectua operațiuni asupra fișierelor din afara documentului curent. De exemplu, o macrocomandă poate șterge sau modifica orice fișier dintr-un folder Documentele mele. Din acest motiv, rulați și permiteți numai macrocomenzi din surse în care aveți încredere.
Pentru a rula macrocomanda noastră de formatare a datelor, deschideți fișierul șablon pe care l-am creat în prima parte a acestui tutorial. Dacă aveți setări standard de securitate, atunci când deschideți un fișier, deasupra tabelului va apărea un avertisment că macrocomenzile sunt dezactivate și un buton pentru a le activa. Din moment ce noi am realizat șablonul și avem încredere în noi, apăsăm butonul Activați conținutul (Includeți conținut).
Următorul pas este să importați cel mai recent set de date actualizat din fișier CSV (pe baza unui astfel de fișier, am creat macro-ul nostru).
Când importați date dintr-un fișier CSV, Excel vă poate solicita să configurați unele setări pentru a transfera corect datele în tabel.
Când importul este terminat, accesați meniul Macrocomenzi fila (Macrocomenzi). Vizualizare (Vizualizare) și selectați o comandă Vizualizați macrocomenzile (Macro).
În caseta de dialog care se deschide, vom vedea o linie cu numele macrocomenzii noastre Formatare date. Selectați-l și faceți clic pe Alerga (A executa).
Când macro-ul începe să ruleze, veți vedea cursorul tabelului sărind de la o celulă la alta. După câteva secunde, se vor face aceleași operațiuni cu datele ca la înregistrarea unui macro. Când totul este gata, tabelul ar trebui să arate la fel ca originalul pe care l-am formatat manual, doar cu date diferite în celule.
Să ne uităm sub capotă: Cum funcționează o macro?
După cum am menționat de mai multe ori, o macrocomandă este un cod de program într-un limbaj de programare. Visual Basic pentru aplicații (VBA). Când activați modul de înregistrare macro, Excel înregistrează de fapt fiecare acțiune pe care o faceți sub formă de instrucțiuni VBA. Mai simplu spus, Excel scrie codul pentru tine.
Pentru a vedea acest cod de program, aveți nevoie în meniu Macrocomenzi fila (Macrocomenzi). Vizualizare (vizualizare) faceți clic Vizualizați macrocomenzile (Macro) și în caseta de dialog care se deschide, faceți clic Editati (Schimbare).
Fereastra se deschide. Visual Basic pentru aplicații, în care vom vedea codul de program al macrocomenzii pe care am înregistrat-o. Da, ați înțeles bine, aici puteți schimba acest cod și chiar crea o nouă macrocomandă. Acțiunile pe care le-am efectuat cu tabelul din această lecție pot fi înregistrate folosind înregistrarea automată macro în Excel. Dar macrocomenzile mai complexe, cu o secvență și o logică de acțiune fin reglată, necesită programare manuală.
Să mai adăugăm un pas sarcinii noastre...
Imaginează-ți că fișierul nostru de date original date.csv este creat automat de un proces și este întotdeauna stocat pe disc în același loc. De exemplu, C:Datadata.csv – calea către fișierul cu date actualizate. Procesul de deschidere a acestui fișier și import de date din acesta poate fi, de asemenea, înregistrat într-o macrocomandă:
- Deschideți fișierul șablon în care am salvat macro-ul − Formatare date.
- Creați o nouă macrocomandă numită Incarca date.
- În timpul înregistrării unei macrocomenzi Incarca date importați date din fișier date.csv – așa cum am făcut în partea anterioară a lecției.
- Când importul este complet, opriți înregistrarea macrocomenzii.
- Ștergeți toate datele din celule.
- Salvați fișierul ca șablon Excel activat pentru macro (extensie XLTM).
Astfel, prin rularea acestui șablon, aveți acces la două macrocomenzi – una încarcă datele, cealaltă le formatează.
Dacă doriți să intrați în programare, puteți combina acțiunile acestor două macrocomenzi într-una singură - pur și simplu prin copierea codului din Incarca date până la începutul codului Formatare date.