Cum de a automatiza sarcinile de rutină în Excel folosind macro-uri

Excel are un puternic, dar în același timp este foarte rar folosit, abilitatea de a crea în mod automat face o secvență de acțiuni cu macro-uri. Macro - soluție ideală dacă aveți de-a face cu același tip de sarcină care se repetă de mai multe ori. De exemplu, prelucrarea datelor sau de formatare a documentelor pe un șablon standardizat. În acest caz, nu aveți nevoie de cunoștințe de limbaje de programare.

Ați întrebat ce macro, și cum funcționează? Apoi, cu curaj înainte - apoi pas cu pas vom face întregul proces de creare a unui macro cu tine.

Ce este un macro?

Macrocomenzile sunt capabili de a efectua aproape orice acțiune în documentul pe care le poate dori numai. Iată câteva dintre ele (o foarte mică parte):

• Aplică stiluri și formatare.
• Efectua diverse operații cu date numerice și textuale.
• Utilizați surse de date externe (fișiere de baze de date, documente text, etc.)
• Crearea unui nou document.
• Face toți pașii de mai sus, în orice combinație a acestora.

Crearea unui macro - un exemplu practic

De exemplu, luați fișierul CSV cele mai comune. Acest simplu 10x20 tabel umplut cu numere de la 0 la 100 cu titlurile de coloane și rânduri. Sarcina noastră este de a transforma acest set de date într-un tabel și forma prezentabil formatate rezultatele în fiecare rând.

După cum sa menționat deja, macro - un cod scris în limbaj de programare VBA. Dar, în Excel, puteți crea un program fără a scrie o singură linie de cod, pe care o vom face acum.

Pentru a crea un macro, deschide View (Vizualizare)> Macros (macro-uri)> Înregistrare Macro (Înregistrare Macro ...)

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

Din acest moment, toate acțiunile sunt înregistrate cu documentul: poziția de schimbare, defilare a mesei, chiar și redimensionarea ferestrei.

Excel indică faptul că modul de înregistrare macro este activat în două locuri. În primul rând, în meniul Macrocomenzi (Macro) - în locul liniei de înregistrare Macro (Record Macro ...) a existat o linie de Oprire înregistrare (Oprire înregistrare).

În al doilea rând, în colțul din stânga jos al ferestrei Excel. pictograma Stop (un pătrat mic) indică faptul că modul de înregistrare macro este activat. Făcând clic pe ea se va opri înregistrarea. Pe de altă parte, atunci când modul de înregistrare nu este activat, acest loc este o pictogramă pentru a activa înregistratorul macro. Făcând clic pe ea va da același rezultat ca și includerea intrărilor în meniu.

Acum, că modul de înregistrare macro este activată, să zaymomsya sarcina noastră. Primul pas, adăugați anteturi pentru totaluri.

Acum selectați celulele cu formule și să le copiați pe toate liniile de masa noastră trăgând de mânerul de completare automată a.

După efectuarea acestei acțiuni pe fiecare linie ar trebui să apară totalurile corespunzătoare.

În continuare, vom rezuma rezultatele pentru întregul tabel pentru a face acest lucru câteva operații matematice:

• = SUM (L2: L21) sau = SUM (L2: L21)
• = AVERAGE (B2: K21) sau = AVERAGE (B2: K21) - pentru a calcula această valoare este necesar să se ia masa de date brute. Dacă luăm media mijlocul unora dintre liniile, rezultatul va fi diferit.
• = MIN (N2: N21) sau = MIN (N2: N21)
• = MAX (O2: O21) sau = MAX (O2: O21)
• = MEDIAN (B2: K21) sau = MEDIAN (B2: K21) - vedere din tabelul de date brute, din motivele menționate mai sus.

Acum, că ați terminat cu calculele, zaymomsya formatare. Pentru a începe să pună toate celulele din același format de date. Selectați toate celulele din foaia de lucru, trebuie doar să utilizați tasta combinația Ctrl + A. sau faceți clic pe pictograma Selectați toate. care se află la intersecția dintre pozițiile rândului și coloanei. Apoi faceți clic pe Style Virgulă (format delimitat) pe fila Pornire (Acasă).

Apoi, schimba aspectul și titlurile coloanelor rând:

• font bold.
• Centrat.
• umple de culoare.

Și, în sfârșit, configurați formatul totaluri.

Așa ar trebui să arate ca în cele din urmă:

Dacă sunteți mulțumit, opriți înregistrarea macro.

Felicitări! Trebuie doar de auto-a lansat primul lor macro în Excel.

Pentru a utiliza pentru a crea un macro, avem nevoie pentru a salva un document Excel într-un format care acceptă macro-uri. În primul rând avem nevoie pentru a elimina toate datele de la noi a crea un tabel care este face un șablon gol. Faptul este că, în viitor, de lucru cu acest șablon, vom importa datele cele mai recente și mai relevante în ea.

Pentru a șterge toate celulele din datele, faceți clic dreapta pe pictograma Selectați toate. care este situat la intersecția de la pozițiile rândului și coloanei, precum și din meniul contextual, selectați Ștergere (Eliminare).

Acum, fișa noastră complet eliminate toate datele, macro a fost înregistrată. Avem nevoie de a păstra cartea ca un șablon Excel cu suport macro-uri, care are o extensie XLTM.

Atunci când șablonul este salvat, puteți închide în condiții de siguranță Excel.

Executarea macro în Excel

Înainte de a dezvălui toate caracteristicile pe care le creați un macro, cred că este corect să acorde o atenție la câteva puncte importante despre macro în ansamblu:

• Macro-urile pot fi dăunătoare.
• Încă o dată, citiți paragraful precedent.

VBA-cod are un potențial foarte mare. În special, se poate efectua operațiuni pe fișiere în afara documentului curent. De exemplu, macrocomanda poate șterge sau modifica fișierele din folderul My Documents. Din acest motiv, executați și permit macro-uri din surse de încredere.

Pentru a rula macro noastre de formatare a datelor, deschideți fișierul șablon pe care l-am creat în prima parte a acestei lecții. Dacă setările de securitate standard atunci când deschideți fișierul pe partea de sus a unui avertisment este afișat deasupra tabelului de ceea ce macro-uri pentru a rula off, și un buton pentru a permite punerea în aplicare a acestora. Din moment ce ne-am făcut pe cont propriu șablon și în prezent avem încredere, apoi faceți clic pe butonul Activare Conținut (Activare conținut).

Următorul pas, noi de import set de date actualizate ultima dintr-un fișier CSV (pe baza dosarului am creat macrocomanda).

Când importați date dintr-un fișier CSV poate, Excel vă solicită să configurați anumite setări pentru transferul de date corespunzător în tabel.

În cazul în care importul este completă, accesați meniul Macros (Macro) de pe fila View (Vizualizare), apoi faceți clic pe View Macrocomenzi (macro-uri).

În caseta de dialog, vom vedea un șir de caractere cu numele macro noastre FormatData. Selectați-l și faceți clic pe Run (Executare).

Când macro începe să funcționeze, veți vedea cum cursorul tabelul de sare de la celula la celula. După câteva secunde de date se va face cu aceleași operații ca și în înregistrarea macro. Când totul este gata, masa ar trebui să arate la fel ca originalul, pe care am formatat manual, numai cu alte date din celule.

Să ne uităm sub capotă: Cum macro?

După cum a fost menționat de multe ori, macrocomanda - un cod software în Visual Basic pentru programare aplicații (VBA). Când activați modul de înregistrare macro, Excel înregistrează de fapt, fiecare acțiune făcută de tine în formă de instrucțiuni în limbajul VBA. Intr-un simplu, Excel scrie codul pentru tine.

Pentru a vedea acest cod, este necesar în meniul Macrocomenzi (Macro) de pe fila View (Vizualizare) faceți clic pe View Macrocomenzi (macro-uri) și în caseta de dialog, faceți clic pe Edit (Editare).

Visual Basic pentru fereastra Aplicații se deschide. în cazul în care vom vedea codul înregistrat macro-ne. Da, ați înțeles corect, acest lucru este codul pe care îl puteți modifica și chiar a crea un nou macro. Acele acțiuni pe care le-am făcut la masă în această lecție, se poate scrie cu ajutorul înregistrării automate în Excel macro. Dar macro-uri mai complexe, cu o secvență de reglaj fin de acțiune și logica necesită programare manuală.

Să adăugăm încă un pas la problema noastră ...

Imaginați-vă că datele noastre data.csv fișier sursă este creat în mod automat printr-un proces, și a păstrat întotdeauna în același loc de pe disc. De exemplu, C: \ Data \ data.csv - calea către datele actualizate. Procesul de deschidere a fișierului și importați datele din aceasta poate fi, de asemenea, scrise în macro:

1. Deschideți fișierul șablon în care ne-am menținut macro - FormatData.
2. Crearea unui nou macro numit LoadData.
3. În LoadData proces de înregistrare macro face importuri de date din data.csv fișier - așa cum am făcut-o în partea anterioară a tutorialului.
4. În cazul în care importul este completă, opriți înregistrarea macro.
5. Eliminați toate datele din celule.
6. Salvați fișierul ca un șablon Excel cu suport macro (XLTM de expansiune).

Astfel, prin rularea acestui șablon, aveți acces la cele două macro - unul descarcă datele, un alt format lor.

Dacă doriți să faceți programare, puteți combina acțiunile acestor două macro-uri într-o singură - pur și simplu copierea codului de la începutul codului LoadData FormatData.