Cum se creează o macrocomandă în Excel. Pornire rapidă: crearea unei macrocomenzi

La fel ca în alte aplicații Microsoft Office, există un lucru atât de interesant ca o macro. Pe scurt, o macrocomandă este o secvență programabilă de acțiuni. Există două moduri de a-l crea:

1. Folosind elementul de meniu corespunzător;

2. Manual.

Prima metodă este foarte simplă și nu necesită abilități speciale de programare. Dar înainte de a începe să creați o macrocomandă, trebuie să cercetați setările de securitate. Accesați Fișier / Opțiuni / Centru de încredere / Setări Centru de încredere / Setări macro. Aici trebuie să alegi Includeți toate macrocomenzile.

Acum totul este gata și trecem direct la crearea unei macrocomenzi. Deschide fila Dezvoltatorși faceți clic.

Va apărea o fereastră în care trebuie să specificați numele macrocomenzii, să atribuiți o cheie pentru lansarea rapidă și, de asemenea, să selectați registrul de lucru în care doriți să salvați macrocomanda.

După apăsarea butonului O.Kîncepe înregistrarea macro. Tot ceea ce faci va fi înregistrat. De exemplu, puteți crea un tabel și îl puteți edita - setați numărul necesar de rânduri, coloane și lățimea acestora. Apoi, pentru a opri înregistrarea, trebuie să faceți clic pe butonul corespunzător.

Acum, pentru a rula macrocomandă, aveți nevoie de filă Dezvoltator a împinge Macro-uri.

În fereastra care apare, găsiți macrocomandă creată de noi și faceți clic A executa... De asemenea, puteți utiliza comanda rapidă de la tastatură pe care am introdus-o la crearea macrocomenzii.

Nu există foarte multe avantaje pentru această metodă de a crea macrocomenzi. În cele mai multe cazuri, o astfel de macrocomandă poate fi aplicată numai în registrul de lucru curent și pentru o situație specifică. Este mult mai eficient să folosești o macrocomandă scrisă folosind un limbaj de programare Visual Basic... Cu toate acestea, crearea unei astfel de macrocomenzi necesită anumite abilități. Este luat în considerare un exemplu de creare a unei macrocomenzi în VBA.

Ziua bună tuturor. Alexey Gulynin este în legătură. În acest articol, aș vrea să vorbesc despre cum să înregistrați o macrocomandă... În exemplele mele, voi folosi Microsoft Excel 2007, dar puteți folosi oricare (este de dorit ca versiunea să nu fie mai mică decât a mea). Accesați Excel, selectați Vizualizare - Macrocomenzi - Înregistrare macrocomenzi:

În versiunile 2010 și 2013, panoul poate diferi, dar nu în mod semnificativ. Apoi, ar trebui să se deschidă această casetă de dialog, care se numește „Înregistrare macrocomandă”:

Specificați numele macrocomenzii dacă aceasta este soluția finală și o veți folosi în viitor. Dacă aveți de gând să înregistrați o macrocomandă pentru a spiona ceva (pe care ați uitat), atunci puteți lăsa numele așa cum este.

Este interzisă utilizarea numelui macro: spații, semne de punctuație, numere la începutul numelui. Puteți folosi: litere în engleză și rusă, cifre (nu la început), subliniere. De asemenea, este interzisă denumirea unei macrocomenzi al cărei nume va fi același cu orice obiect existent în Excel.
Pentru o macrocomandă, puteți seta o comandă rapidă de la tastatură, când este apăsată, aceasta va rula. Toate macrocomenzile nu trebuie să blocheze tastele rapide, trebuie doar să închideți cele pe care le vom folosi adesea. În general, există multe modalități de a rula o macrocomandă, opțional, aceasta poate fi plasată pe panglică (începând cu versiunea 2010), în versiunea 2007 există o panglică, dar nu poate fi editată, poate fi plasată pe o foaie. De asemenea, o macrocomandă poate fi rulată pe orice eveniment. Tastele rapide au sensibilitate atât la caz cât și la aspect, așa că aveți grijă ce tastă rapidă să atribuiți unei macrocomenzi. Puteți folosi litere mari, pentru aceasta trebuie să apăsați Ctrl + Shift + scrisoarea ta.

Dacă macrocomanda este salvată în această carte, atunci va fi fizic în acest fișier. Prin urmare, atunci când fișierul este închis, această macrocomandă nu va fi disponibilă. Când este justificat să o faci: o macrocomandă este necesară numai pentru calculele din această carte și nu în oricare altele. Dacă această macrocomandă trebuie utilizată în alte fișiere Excel (registre de lucru), atunci acest fișier va trebui să fie întotdeauna deschis sau deschis și închis în mod constant, ceea ce este complet incomod. Aici trebuie să alegeți o altă modalitate de salvare: macrocarte personală.

Este aproape un fișier obișnuit și se numește Personal.xlsb. Acest fișier se află în folderul XLStart și se deschide în modul ascuns. Până acum nu am scris nimic în acest fișier - nu există. De îndată ce scriem ceva, va apărea. Totul din folderul XLStart pornește automat. Plusul aici este că macrocomanda pe care ați înregistrat-o în registrul dvs. personal va fi întotdeauna disponibilă (nu va trebui să o deschideți constant - să o închideți). Vă atrag atenția asupra faptului că va fi disponibil doar pe acest computer.
De asemenea, puteți adăuga o descriere macrocomenzii.

Să scriem următoarea macrocomandă, care va crea o foaie nouă și va scrie expresia Hello VBA în celula C4. Mai mult, inscripția ar trebui să fie în roșu, iar stilul trebuie să fie îndrăzneț. Faceți clic pe OK.
Introducem foaia astfel:

Acum, în celula C4 scriem expresia Bună ziua VBA. Apoi apăsați Ctrl + Enter pentru a rămâne în aceeași celulă și a aplica formatarea acesteia. Dacă apăsăm doar Enter, atunci focalizarea va merge către o altă celulă (nu avem nevoie de asta). Apoi faceți clic dreapta pe celulă și selectați Formatare celule și accesați fila Font, selectați roșu și culoare și faceți fontul bold:

Faceți clic pe OK. Acum ne putem finaliza macro-ul. Acest lucru se poate face în mai multe moduri: Vizualizare - Macrocomenzi - Oprire înregistrare, poate fi dezactivat și în colțul din stânga jos (în versiunea 2007) în bara de stare (o puteți vedea în poza 3). „Sheet4” a fost creat. Să o ștergem și să folosim o macrocomandă pentru a crea o foaie nouă. Pentru a face acest lucru, vom folosi tasta rapidă pe care am setat-o ​​pentru macrocomandă Ctrl + Shift + D. Asigurați-vă că sunteți pe aspectul rusesc. Dacă ați făcut totul corect, atunci ar fi trebuit să creați o nouă foaie cu înregistrarea de care avem nevoie.

Excel are cea mai puternică, dar în același timp foarte rar folosită, capacitatea de a crea secvențe automate de acțiuni folosind macrocomenzi. O macro este o soluție ideală dacă aveți de-a face cu o sarcină de același tip care se repetă de mai multe ori. De exemplu, prelucrarea datelor sau formatarea documentelor conform unui șablon standardizat. În același timp, 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, mergeți înainte cu îndrăzneală - apoi, pas cu pas, vom parcurge împreună cu dvs. întregul proces de creare a unei macrocomenzi.

Ce este Macro?

O macrocomandă în Microsoft Office (da, această funcționalitate funcționează la fel în multe aplicații ale suitei Microsoft Office) este un cod de program într-un limbaj de programare (VBA) stocat într-un document. Pentru a fi mai clar, un document Microsoft Office poate fi comparat cu o pagină HTML, apoi o macrocomandă este 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 sunt capabile să efectueze aproape orice acțiune pe care o doriți asupra unui 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.
• Efectuați toate acțiunile de mai sus în orice combinație.

Crearea unei macrocomenzi - un exemplu practic

De exemplu, să luăm cel mai comun fișier CSV... Este o foaie de calcul simplă de 10x20 plină cu numere de la 0 la 100 cu titluri de coloană și rând. Sarcina noastră este să transformăm acest set de date într-un tabel formatat prezentabil și să generăm totaluri pentru 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 singură linie de cod, ceea ce vom face chiar acum.

Pentru a crea o macrocomandă, deschideți Vedere(Vizualizare)> Macro-uri(Macro-uri)> Înregistrare macro(Înregistrarea unei macrocomenzi...)

Dați macrocomenzii un nume (fără spații) și apăsați O.K.

Din acest moment, TOATE acțiunile tale cu documentul sunt înregistrate: modificări de celule, defilare în tabel, chiar și redimensionarea ferestrei.

Excel semnalează că înregistrarea macro este activată în două locuri. În primul rând, în meniu Macro-uri(Macro) - în loc de șir Înregistrare macro A apărut linia (Înregistrare macro...). 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ă în acest loc pentru a activa înregistrarea macro. Făcând clic pe acesta, se va da același rezultat ca și activarea înregistrării prin meniu.

Acum că modul de înregistrare macro este activat, să trecem la sarcina noastră. Mai întâi, să adăugăm anteturi pentru datele rezumate.

• = SUMA (B2: K2) sau = SUMA (B2: K2)
• = MEDIE (B2: K2) sau = MEDIE (B2: K2)
• = MIN (B2: K2) sau = MIN (B2: K2)
• = MAX (B2: K2) sau = MAX (B2: K2)
• = MEDIANA (B2: K2) sau = MEDIANA (B2: K2)

Acum selectați celulele cu formulele și copiați-le în toate rândurile tabelului nostru trăgând de mânerul de completare automată.

După finalizarea acestui pas, totalurile corespunzătoare ar trebui să apară pe fiecare rând.

Respectiv:

• = SUMA (L2: L21) sau = SUMA (L2: L21)
• = MEDIE (B2: K21) sau = MEDIE (B2: K21)- pentru a calcula această valoare, este necesar să luăm exact datele inițiale ale tabelului. Dacă luați media mediilor individuale ale rândurilor, 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)- calculăm folosind datele inițiale ale tabelului, din motivul indicat mai sus.

Acum că am terminat cu calculele, să începem formatarea. Pentru început, setați același format de afișare a datelor pentru toate celulele. Selectați toate celulele de pe foaie, pentru aceasta utilizați comanda rapidă de la tastatură Ctrl + A, sau faceți clic pe pictogramă Selectează tot care se află la intersecția titlurilor rândurilor și coloanelor. Apoi apăsați Stilul virgulă fila (Format delimitat). Acasă(Acasă).

• Font îndrăzneț.
• Alinierea la centru.
• Umpleți cu culoare.

În cele din urmă, vom personaliza formatul totalurilor.

Asa ar trebui sa arate pana la urma:

Dacă sunteți mulțumit de toate, opriți înregistrarea macro-ului.

Felicitări! Tocmai ați înregistrat singur prima macrocomandă Excel.

Pentru a utiliza macrocomanda creată, trebuie să salvăm documentul Excel într-un format care acceptă macrocomenzi. În primul rând, trebuie să ștergeți toate datele din tabelul pe care l-am creat, de exemplu. faceți un șablon gol din el. Faptul este că în viitor, lucrând cu acest șablon, vom importa în el cele mai recente și mai relevante date.

Pentru a șterge toate datele din toate celulele, faceți clic dreapta pe pictogramă Selectează tot, care se află la intersecția titlurilor rândurilor și coloanelor, iar din meniul contextual selectați elementul Ș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 un șablon Excel 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ță.

Executarea 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 punctul 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șiere din folder Documentele mele... Din acest motiv, rulați și permiteți numai macrocomenzi din surse în care aveți încredere.

Pentru a rula datele noastre de formatare macro, deschideți fișierul șablon pe care l-am creat în prima parte a acestui tutorial. Dacă aveți setări de securitate standard, atunci când deschideți fișierul, va apărea un avertisment deasupra tabelului de deasupra tabelului că lansarea macrocomenzilor este dezactivată și un buton pentru a activa execuția lor. Deoarece am făcut șablonul singuri și avem încredere în noi, apăsăm butonul Activați conținut(Includeți conținut).

Următorul pas este să importați cel mai recent set de date actualizat din fișier CSV(am creat macrocomandă pe baza unui astfel de fișier).

Când importați date dintr-un fișier CSV, Excel vă poate cere să configurați niște parametri pentru transferul corect al datelor în tabel.

Când importul este terminat, accesați meniul Macro-uri fila (Macrocomenzi). Vedere(Vizualizare) și selectați comanda Vizualizați macrocomenzi(Macro-uri).

În caseta de dialog care se deschide, vom vedea o linie cu numele macrocomenzii noastre Formatare date... Selectați-l și faceți clic Alerga(Alerga).

Când macro-ul începe să funcționeze, veți vedea cum cursorul tabelului sare de la o celulă la alta. După câteva secunde, se vor efectua aceleași operațiuni cu datele ca la înregistrarea macro-ului. 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.

Aruncă o privire sub capotă: Cum funcționează o macro?

După cum am menționat deja 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 în limba VBA. Într-un mod simplu, Excel scrie codul programului pentru tine.

Pentru a vedea acest cod de program, aveți nevoie în meniu Macro-uri fila (Macrocomenzi). Vedere(Vizualizare) faceți clic Vizualizați macrocomenzi(Macro) și în caseta de dialog care se deschide, faceți clic Editați | ×(Schimbare).

Se va deschide o fereastră 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 puteți 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ță fin reglată și o logică a acțiunilor, necesită programare manuală.

Să mai adăugăm un pas sarcinii noastre...

Imaginați-vă că fișierul nostru de date original date.csv este creat automat de un proces și este întotdeauna salvat pe disc în același loc. De exemplu, C: \ Date \ data.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ă:

1. Deschideți fișierul șablon în care am salvat macrocomanda - Formatare date.
2. Creați o nouă macrocomandă numită Incarca date.
3. Î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.
4. Când importul este complet, opriți înregistrarea macrocomenzii.
5. Ștergeți toate datele din celule.
6. Salvați fișierul ca șablon Excel Macro-Enabled (extensie XLTM).

Astfel, prin rularea acestui șablon, aveți acces la două macrocomenzi - una încarcă date, cealaltă le formatează.

Dacă doriți să începeți programarea, 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.

Dacă Microsoft Excel are sarcini care sunt repetitive, puteți înregistra o macrocomandă pentru a automatiza acele sarcini. O macrocomandă este o acțiune sau un set de acțiuni pe care le puteți rula câte doriți. Când creați o macrocomandă, clicurile mouse-ului și apăsările de taste sunt înregistrate. După ce creați o macrocomandă, o puteți edita pentru a face mici modificări în munca dvs.

Să presupunem că în fiecare lună creați un raport pentru managerul dvs. de contabilitate. Doriți să formatați numele de utilizator cu conturile expirate în roșu și, de asemenea, să aplicați formatare aldine. Puteți crea o macrocomandă care să aplice rapid aceste modificări de formatare celulelor selectate și să o execute.

Procedură

Înregistrare macro

Pasii urmatori

Procedură

Informații suplimentare

Puteți pune oricând o întrebare comunității Excel Tech, puteți cere ajutor în comunitatea Răspunsuri sau puteți sugera o nouă funcție sau îmbunătățire pe site.

Macro-urile sunt de neînlocuit. O macrocomandă poate fi definită ca o secvență de comenzi (secvențial), care poate fi clicuri, apăsări de taste sau chiar linii mici de cod cu funcționalități mai avansate. Aceste secvențe sunt scrise în modulul VBA și executate după cum este necesar.

Principalul avantaj al unei macrocomenzi este timpul pe care îl oferă, deoarece automatizează ceea ce trebuie făcut manual în mai mulți pași. Este foarte util atunci când trebuie să efectuați același set de sarcini de mai multe ori.

O macrocomandă poate fi creată în două moduri diferite:

• Folosind Macro Recorder:În această procedură, Excel salvează fiecare acțiune care va face parte dintr-o macrocomandă, iar apoi înregistratorul de macrocomandă convertește acele acțiuni în comenzi Visual Basic pentru aplicații (VBA).
• Folosind VBA: Dacă știți puțin despre programare sau chiar cunoașteți VBA, vă puteți crea singuri propriile macrocomenzi. Pentru a face acest lucru, pur și simplu utilizați editorul VBA care este prezent în cele mai recente versiuni de Microsoft Excel.

După cum am menționat mai devreme, există multe lucruri pe care le puteți face cu macrocomenzi, dar unele dintre ele diferă de altele prin faptul că sunt utilizate mai frecvent în munca de zi cu zi. Consultați 10 macrocomenzi care vă vor ajuta cu foile de calcul.

1) Afișați mesajul

Doar lipiți comanda MsgBox în macrocomandă. Iată cum în următorul exemplu:

MsgBox „text mesaj”

Această linie de cod poate fi inserată în orice macrocomandă, ceea ce este foarte util atunci când trebuie să emiteți avertismente utilizatorului foii de calcul în care rulează macrocomanda.

2) Rulați macro-ul când foaia este deschisă

În unele cazuri, macrocomanda trebuie deschisă împreună cu foaia de lucru. În acest caz, doar montați macro-ul cu parametrul Auto_Open (). Verificați exemplul

Sub Auto_Open ()
MsgBox „Pentru totul despre Excel, accesați www.https: //luz.vc/”
End Sub

În exemplul de mai sus, după deschiderea foii de calcul, va fi afișat un mesaj. Nu uitați să activați macrocomenzi în Excel.

3) Foaie de lucru cu data și ora curente

Puteți scrie data și ora curentă pe o foaie de lucru. Următorul exemplu inserează aceste date în celula A1, vezi

Sub writeDataEora ()
Interval ("A1") = Acum
End Sub

4) Efectuați aceeași acțiune pentru fiecare celulă selectată.

La un moment dat, trebuie să efectuați o acțiune asupra celulelor selectate de utilizator. Pentru a face acest lucru, creați o macrocomandă ca în exemplul de mai jos.

Sub la doSpeedCell ()
Pentru fiecare celulă din Selection.Cells
Celula MsgBox
Următorul
End Sub

5) Același lucru pentru toate celulele selectate

Puteți face aceeași macrocomandă mai devreme, dar într-un mod puțin mai complet, adică acțiunea nu va fi efectuată pas cu pas. În exemplul de mai jos, „Bună ziua” este scris în toate celulele.

Sub la doSomethingAllAsCells ()
Selection.Cells.Value = „(! LANG: Bună"!}
End Sub

Puteți face o mulțime de inserări de cod, cum ar fi schimbarea culorii textului, dimensiunea fontului, tipul fontului și multe altele.

6) Identificarea formulelor în fiecare celulă

Sub CheckFormula ()
Dacă Range ("A1") HasFormula = True, atunci
MsgBox „Există o formulă”
Mai mult
MsgBox „Nu este o formulă”
Încheiați dacă
End Sub

7) Schimbați culoarea celulei pe mouse peste

De asemenea, puteți oferi mai multă interactivitate cu utilizatorii foilor de calcul. În mod ideal, interactivitatea este menită să faciliteze întreținerea și vizualizarea informațiilor. Macrocomanda de mai jos este un exemplu despre modul în care tabelele dvs. pot afișa celule cu același ton de culoare atunci când mouse-ul transmite date care corelează celulele respective.

Private Sub Worksheet_SelectionChange (ByVal Target As Range)

Reduceți linia de acasă ca gamă
Gamă
Dim Line2 cât timp

Cells.Interior.ColorIndex = xlNone

Line2 = Target.Row

Set Line Start = Range ("A" și Line2, Target)

Pictează celula selectată în coloana 5
Set Line = Range (celule (Target.Row, 1), celule (Target.Row, 5))

Cu riglă
.Interior.ColorIndex = 12
Se termina cu

8) Schimbați culoarea în interior și fonturile

Această macrocomandă modifică culorile din interiorul și de la sursa celulelor pentru a se potrivi cu litera celulelor.

Sub Colorir_interior_letra ()
Pentru N = 1 până la intervalul ("O65536"). End (xlUp) .Rând

Selectați intervalul de scară ("O" și N)
Cazul „A”
Interval ("O" și N) .Interior.ColorIndex = 3
Interval ("O" și N) .Font.ColorIndex = 1

Cazul „B”
Interval ("O" și N) .Interior.ColorIndex = 4
Interval ("O" și N) .Font.ColorIndex = 2

Cazul „C”
Interval ("O" și N) .Interior.ColorIndex = 5
Interval ("O" și N) .Font.ColorIndex = 3

Cazul „D”
Interval ("O" și N) .Interior.ColorIndex = 7
Interval ("O" și N) .Font.ColorIndex = 12

Alt caz
Interval ("O" și N) .Interior.ColorIndex = 6
Interval ("O" și N) .Font.ColorIndex = 4
Încheierea selectării

Următorul N

9) Macro vorbitor

M-am gândit deja că ar fi interesant să avem o foaie de calcul care să conțină datele pe care le conține, exact asta face această macrocomandă. În exemplul de mai jos, tabelul conține cuvinte care merg de la celula A1 la A5, dar puteți alege spațierea care funcționează cel mai bine pentru dvs., așa că doar schimbați A1: A5 prezentat în codul de mai jos la spațierea pe care o preferați.

Sub ExcelFalling ()

Interval ("A1: A5").