Macro-uri gata. Mironov

O zi buna!

Vreau să dedic acest articol unei secțiuni atât de uriașe a MS Excel precum macrocomenzile sau, mai degrabă, să o luăm de la capăt și să ne uităm la cum să creați o macrocomandă în Excel, pentru ce este și cum să o utilizați în munca dvs.

După cum știți de la propria experiență, atunci când lucrați cu o macrocomandă, există o mulțime de „rutină”, adică aceleași operațiuni și acțiuni sunt efectuate care sunt necesare pentru a obține un rezultat, poate fi completarea aceluiași tip de tabele sau formulare, prelucrarea datelor, similar cu gemeni, rapoarte săptămânale, lunare etc. Dar utilizarea macrocomenzilor vă va permite să efectuați aceste acțiuni în mod automat, folosind capacitățile Excel la maximum, aruncând aceste operațiuni de rutină și monotone pe umerii puternici ai Excel. De asemenea, motivul utilizării macrocomenzilor poate fi adăugarea oportunitățile potrivite care nu au fost încă implementate în standard Funcții Excel(de exemplu, rezultate, colectare de date pe o singură foaie etc.).

Dacă nu ați auzit niciodată de o macrocomandă, atunci definiția sa cea mai exactă ar fi următoarea: acestea sunt acțiuni care sunt programate într-o anumită secvență și înregistrate în mediul de programare pe Limbajul vizual De bază pentru aplicații (VBA)... O macrocomandă poate fi rulată de mai multe ori și acest lucru va forța Excel să efectueze orice secvență de acțiuni de care avem nevoie, ceea ce pur și simplu nu ne place sau nu dorim să le facem manual. În ciuda varietății mari de limbaje de programare pentru întregul complex Microsoft Office standardul este exact VBA și funcționează în orice aplicație a suitei office.

Deci, crearea unei macrocomenzi în Excel este posibilă în 2 moduri:

Creați o macrocomandă în Excelfolosind un reportofon macro

În primul rând, să clarificăm ce este un macro recorder și ce legătură are o macro cu el.

Macro recorder Este un mic program încorporat în Excel care interpretează orice acțiune a utilizatorului în coduri de limbă Programare VBAși scrie comenzile care au fost primite în timpul lucrului în modulul de program. Adică dacă, cu macro recorder-ul pornit, creăm raportul zilnic de care avem nevoie, atunci macro recorder-ul va înregistra totul în comenzile sale pas cu pas și, ca urmare, va crea un macro care va crea automat un raport zilnic.

Această metodă este foarte utilă pentru cei care nu au abilitățile și cunoștințele de lucru în mediul de limbaj VBA. Dar această ușurință de execuție și înregistrare a unei macrocomenzi are dezavantajele sale, precum și avantajele sale:

• Macro recorderul poate înregistra doar ceea ce poate atinge, ceea ce înseamnă că poate înregistra acțiuni numai atunci când sunt folosite butoane, pictograme, comenzi de meniu și tot ce este în acest spirit, astfel de opțiuni care nu sunt disponibile pentru acesta;
• În cazul în care a fost făcută o eroare în timpul perioadei de înregistrare, aceasta va fi și ea înregistrată. Dar puteți folosi butonul de anulare ultima actiune, ștergeți ultima comandă pe care ați scris-o incorect în VBA;
• Înregistrarea în macro recorder se efectuează numai în limitele ferestrei MS Excel, iar în cazul în care închideți programul sau porniți altul, înregistrarea va fi oprită și va înceta să ruleze.

Pentru a activa macro recorder-ul pentru înregistrare, procedați în felul următor:

Următorul pas în lucrul cu reportofonul macro va fi configurarea parametrilor acestuia pentru înregistrarea ulterioară a macro-ului, acest lucru se poate face în fereastră „Înregistrarea unei macrocomenzi”, Unde:

• Câmp pentru numele macrocomenzii- puteți scrie un nume pe care îl înțelegeți în orice limbă, dar trebuie să înceapă cu o literă și să nu conțină semne de punctuație și spații;
• caseta de comenzi rapide de la tastatură- va fi folosit de dvs., în viitor, pentru pornire rapidă macro-ul dvs. În cazul în care trebuie să înregistrați unul nou, această opțiune va fi disponibilă în meniu „Serviciu” - „Macro” - „Macrocomenzi” - „Run” sau pe filă "Dezvoltator" apăsând butonul "Macro-uri";
• câmpul „Salvare în...”- puteți specifica locul unde va fi salvat textul macrocomenzii (dar nu trimis), iar acestea sunt 3 opțiuni:
  • "Această carte"- macro-ul va fi scris în modulul cărții curente și poate fi executat numai dacă acest lucru Caiet de lucru Excel va fi deschis;
  • "O carte noua"- macro-ul va fi salvat în șablonul pe baza căruia se creează unul gol în Excel O carte noua, ceea ce înseamnă că macro-ul va deveni disponibil în toate cărțile care vor fi create pe acest computer din acel moment;
  • „Cartea macro personală”- este o carte specială de macrocomandă Excel numită „Personal.xls”și este folosit ca o bibliotecă-stocare specială de macro-uri. La pornire, macrocomenzile din cartea „Personal.xls” sunt încărcate în memorie și pot fi rulate în orice carte în orice moment.
• Câmp de descriere- aici puteți descrie ce și cum ar trebui să facă macro-ul, de ce a fost creată și ce funcții are, acesta este un câmp pur informativ, care este chemat pentru memorie.

După ce ați rulat și ați înregistrat macrocomandă făcând totul acțiunile necesare, înregistrarea poate fi oprită cu comanda Opriți înregistrarea iar macro-ul dvs. va fi creat folosind macro recorder-ul.

Creați o macrocomandă în Excelîn Editorul Visual Basic.

În această metodă, ne vom uita la cum să creați o macrocomandă în Excel prin editorul de programe VBA, care, așa cum am spus mai sus, este încorporat în orice versiune de Excel.

Editorul de programe VBA este lansat diferit, în funcție de versiunea programului dvs. Excel:

• în versiunile de Excel 2003 și ulterioare, avem nevoie în meniu "Serviciu", selectați elementul "Macro"și apăsați "Editor Visual Basic» ;
• în versiunile de Excel 2007 și mai noi, avem nevoie în filă "Dezvoltator" apasa butonul „Editor Visual Basic”... În cazul în care nu găsiți această filă, trebuie să o activați selectând elementul de meniu „Fișier” - „Opțiuni” - „Personalizați panglica” iar în caseta de dialog, activați fila "Dezvoltator".

În fereastra care se deschide, poți contempla interfața editorului VBA, totul aici este scris în engleză și nu există localizatori, nu trebuie să cauți, ci doar să accepți și să lucrezi, mai ales că acest lucru este doar de neînțeles la început , și atunci totul va fi familiar.

Deci, cum pot lucra în editorul VBA, unde este ce este stocat și cum să creez o macrocomandă în Excel. Întrebări de această natură apar imediat ce îl vedeți pe editor și acum le vom lua în considerare pe toate.

Toate macrocomenzile noastre vor fi stocate în așa-numitul module software... În oricare dintre cărți, oricare versiuni Excel putem crea orice module softwareîn orice cantitate și plasați în ele toate macrocomenzile pe care le-am creat. Un modul poate conține orice număr de macrocomenzi de care aveți nevoie sau create. Modulele sunt disponibile în fereastră „Explorator de proiect”și sunt situate în colțul din stânga sus al editorului de macro (puteți apela și cu comanda rapidă de la tastatură CTRL + R).

Modulele de program din editorul VBA există în mai multe forme și sunt obișnuite diferite opțiuni si situatii:

De fapt, macro-ul în sine atunci când lucrează într-un modul standard arată astfel:

Să luăm în considerare un exemplu de macrocomandă de lucru:

• Toate macrocomenzile vor începe în mod necesar cu un operator Sub urmat de numele macrocomenzii și de o listă de argumente între paranteze. În cazurile în care argumentele lipsesc, parantezele trebuie lăsate necompletate;
• Fără greșeală, toate macrocomenzile se termină cu operatorul End Sub;
• Date care sunt între operatori Subși End Sub, este corpul macrocomenzii care va rula atunci când macrocomandă este rulată. În exemplu, macro-ul verifică și, la introducerea datelor, le găsește în lista bazei de date și afișează valoarea specificată de criterii.

După cum puteți vedea, a doua metodă este mai dificil de utilizat și de înțeles dacă nu aveți experiență în programare în general sau în special în VBA. Este foarte greu de înțeles și de înțeles ce comenzi și cum sunt introduse, ce argumente folosește pentru ca macro-ul să înceapă să-și desfășoare activitatea în modul automat. Dar drumul va fi stăpânit de cel care merge, așa cum spuneau vechii înțelepți și, prin urmare, nu ar trebui să renunți, ci să urmezi poruncile bunicului lui Lenin ...

Crearea unui buton pentru a rula macrocomenzi în bara de instrumente

După cum am spus mai devreme, puteți apela o procedură macro combinație fierbinte taste, dar este foarte obositor să vă amintiți ce combinație este atribuită cui, așa că cel mai bun lucru este să creați un buton pentru a rula macro-ul. Creați butoane, eventual de mai multe tipuri și anume:

Această metodă este disponibilă pentru orice versiune de MS Excel și constă în faptul că vom aduce butonul direct în foaia noastră de lucru ca obiect grafic. Pentru a face acest lucru, aveți nevoie de:

• În MS Excel 2003 și mai vechi mergi la meniu "Vedere", alege „Bara de instrumente”și apăsați butonul „Formulare”.
• În MS Excel 2007 și mai nou ai nevoie în filă "Dezvoltator" deschide meniul drop-down "Introduce"și selectați un obiect "Buton".

După toate acestea, trebuie să desenați un buton pe foaia dvs. în timp ce țineți apăsat butonul stâng al mouse-ului. După încheierea procesului de desenare, se va aprinde automat o fereastră, unde va trebui să selectați macrocomanda care trebuie executată când faceți clic pe butonul dvs.

Cum se creează funcții personalizate în VBA

În principiu, crearea așa-numitelor funcții definite de utilizator nu este foarte diferită de crearea unei macrocomenzi obișnuite într-un modul de program standard. Diferența dintre aceste concepte este că macro-ul va efectua acțiunile inerente acesteia cu obiectele cărții sau foii (acestea sunt formule, celule etc.), dar funcția personalizată funcționează numai cu valorile pe care le primește de la noi si sunt argumentele.date initiale pentru calcule.

De exemplu, pentru a crea o funcție personalizată pentru taxa pe valoarea adăugată, numită TVA, trebuie să deschidem editorul nostru VBA și să adăugăm modul nou, selectați din meniu "Introduce" paragraf "Modul"și introduceți acolo textul pentru funcția noastră: Trebuie remarcat faptul că principala diferență dintre o funcție și o macrocomandă este antetul Funcţieînlocuit Subși există o listă completă de argumente, în exemplul nostru este Suma... După introducerea codului nostru, acesta va fi disponibil în fereastra standard Vrăjitorii de funcții găsiți în meniu "Formulele", paragraful „Funcția de inserare”.
Și alegeți o categorie „Definit de utilizator”în care va fi afișată funcția noastră scrisă „NDS”.
După selectarea funcției noastre, puteți plasa cursorul pe o celulă cu un argument, care va conține suma pentru care calculăm TVA, totul se întâmplă, ca și în cazul functia normala.
Și asta e tot pentru mine! Chiar sper că un articol despre cum să creați o macrocomandă în Excel a fost de înțeles și util pentru tine. Aș fi foarte recunoscător pentru comentariile lăsate, deoarece acesta este un indicator de lizibilitate și inspiră pentru a scrie articole noi! Distribuie ce ai citit prietenilor tăi și dă like!

Am început să construiesc acest site ca caiet pe oportunități convenabile Excela. În orice moment, vă puteți perfecționa cunoștințele despre o anumită caracteristică a programului accesând site-ul, plus puteți trimite un link către articol prietenilor sau colegilor care vă cer ajutor folosind Excel. Audiența site-ului a crescut destul de repede și, după cum văd eu, informațiile înregistrate sunt utile nu numai mie și altor câteva persoane, ci și multor persoane pe care nu le cunosc și sunt foarte apreciate de motoarele de căutare. Dacă prin Capabilitati Excel, sunt încrezător în mine, atunci un specialist este foarte nivel inalt Nu pot să mă numesc în VBA. Prin urmare, aș dori să creez grozav articol helper unde voi scrie macro-uri utile pe care le folosesc frecvent. Sunt sigur că o astfel de colecție de caracteristici VBA va fi utilă nu numai pentru mine.

Site-ul are deja câteva articole despre VBA, inclusiv, dar cred că va fi util să păstrați gadget-urile de cod care sunt adesea folosite într-un singur loc.

Opriți afișarea acțiunilor macro pe ecran cu o funcție simplă. Acest lucru nu numai că va îmbunătăți performanța vizuală, ci și va accelera semnificativ execuția macro-ului.

Sub Makros1 () Application.ScreenUpdating = 0 "Codul dvs. Application.ScreenUpdating = 1 End Sub

Nu uitați să includeți funcția la sfârșitul macro-ului

Cum se elimină selecția de copiere după execuția macro?

Dacă sunteți așa în macrocomandă, alocați un interval pentru copiere și lipire. Nu uitați să dezactivați selecția la sfârșitul macrocomenzii

Sub Makros1 () „Codul dvs. Application.CutCopyMode = 0 End Sub

Macro-uri utile. Cum să găsiți ultimul rând sau coloană dintr-un interval

Această construcție vă va ajuta să găsiți cu ușurință numărul ultimului rând sau coloană din intervalul completat. Este deosebit de convenabil de utilizat în bucle, nu trebuie să specificați 1000 de linii de buclă cu o marjă, Excel se va găsi acolo unde sfârșitul intervalului folosește următoarea construcție:

Sub makros1 () Dim mLastRow As Long Dim nLastCol As Long mLastRow = Cells (Rows.Count, 1) .End (xlUp) .Row "Găsește ultimul număr de rând din intervalul completat nLastCol = Cells (1, Columns.Count) . End (xlToLeft ) .Column "Găsește numărul ultimei coloane populate din intervalul End Sub

Mai mult, declar imediat variabila ca Long (lungime 2 147 483 647), pentru a nu ajunge într-o situație în care popularul Integer ar putea să nu fie suficient (32 767) pentru mese mari.

De asemenea, ar trebui să acordați atenție că căutarea are loc pe prima coloană sau pe primul rând, selectați numărul dorit, pentru definirea corectă.

Pentru buclă și verificarea unei condiții într-o buclă

Daca gasesti ultimele probleme intervale de rânduri și coloane, le puteți utiliza în bucle. De exemplu, trebuie să parcurgeți prima coloană și să numărați numărul de celule goale.

Sub makros1 () Application.ScreenUpdating = 0 Dim mLastRow As Long Dim Kol As Long "Variable amount Dim i As Long" Variabila de ciclu mLastRow = Cells (Rows.Count, 1) .End (xlUp) .Row Kol = 0 For i = 1 To mLastRow If Cells (i, 1) .Value = "" Then Kol = Kol + 1 End If Next i MsgBox Kol Application.ScreenUpdating = 1 End Sub

Aici Msgbox este folosit și cu această caracteristică, puteți afișa datele într-o fereastră separată. Pentru exemplul meu, va arăta astfel:

Numărarea timpului de execuție macro

Sub makros1 () TimeStart = Now TimeFinish = Now MsgBox „Time:” & Format (TimeFinish - TimeStart, „h: mm: ss”) End Sub

MsgBox produce această ieșire:

Este foarte convenabil să urmăriți timpul de execuție al macrocomenzilor pentru optimizarea ulterioară.

Acestea sunt câteva macrocomenzi utile pe care le folosesc foarte des. Voi extinde treptat această listă. Toată automatizarea eficientă!

Distribuie articolul nostru pe rețelele tale de socializare:

Următoarele exemple simple de macrocomandă Excel ilustrează unele dintre caracteristicile și tehnicile descrise în tutorialul Excel VBA.

Macrocomandă Excel: Exemplul 1

Inițial această procedură Sub a fost dat ca exemplu de utilizare a comentariilor în codul VBA. Totuși, aici puteți vedea și cum sunt declarate variabilele, cum se referă la celule Excel, folosind bucla Pentru, operator condițional Dacăși afișarea unei casete de mesaj.

„Subprocedura caută o celulă care conține șirul specificat” în intervalul de celule A1: A100 din foaia activă Sub Find_String (sFindText As String) Dim i As Integer „Un număr întreg de tip Integer, utilizat într-o buclă For Dim iRowNumber As Integer" Un întreg de tip Integer pentru stocarea rezultatului iRowNumber = 0 "Se uită prin celulele A1: A100 una după alta până când se găsește un șir sFindText For i = 1 To 100 If Cells (i, 1) .Value = sFindText Then" Dacă se găsește o potrivire cu șirul dat „ salvați numărul linia curentă si iesi din Pentru buclă iRowNumber = i Exit For End If Next i "Informăm utilizatorul într-o fereastră pop-up dacă a fost găsit șirul de căutare" Dacă șirul specificat este găsit, indicați în ce celulă a fost găsită potrivirea Dacă iRowNumber = 0 Apoi MsgBox "The șir" & sFindText & "nu a fost găsit" Else MsgBox " String "& sFindText &" găsit în celula A "& iRowNumber End If End Sub

Macrocomandă Excel: Exemplul 2

Următoarea procedură Sub- un exemplu de utilizare a unei bucle Face în timp ce... Aici puteți vedea, de asemenea, cum sunt declarate variabilele, cum să lucrați cu referințele de celule Excel și cum să aplicați operatorul condiționat. Dacă.

„Sub procedură scoate numere Fibonacci care nu depășesc 1000 Sub Fibonacci () Dim i As Integer” Contor pentru indicarea poziției unui element în secvența Dim iFib As Integer „Stochează valoarea curentă a secvenței Dim iFib_Next As Integer” Stochează următoarea valoare a secvenței Dim iStep As Integer „Stochează dimensiunea următorului increment „Inițializați variabilele i și iFib_Next i = 1 iFib_Next = 0” Bucla Do While va rula până când valoarea „numărului actual Fibonacci depășește 1000 Do While iFib_Next< 1000 If i = 1 Then "Un caz special pentru primul element al secvenței iStep = 1 iFib = 0 Altfel „Salvați dimensiunea următorului increment înainte de a suprascrie” valoarea curentă a secvenței iStep = iFib iFib = iFib_Next End If „Imprimați numărul actual Fibonacci în coloana A din foaie de lucru activă" în rândul cu indice i Celulele (i, 1) .Valoare = iFib "Calculați următorul număr Fibonacci și creșteți indicele de poziție a elementului cu 1 iFib_Next = iFib + iStep i = i + 1 Loop End Sub

Macrocomandă Excel: Exemplul 3

Această procedură Sub circulă prin celulele unei coloane A foaie activă până când întâlnește o celulă goală. Valorile sunt scrise într-o matrice. Această macrocomandă simplă Excel demonstrează lucrul cu matrice dinamice, precum și utilizarea unei bucle Faceți până când... V acest exemplu nu vom efectua nicio acțiune cu matricea, deși în practica reală de programare, după ce datele sunt scrise în matrice, astfel de acțiuni sunt de obicei efectuate asupra lor.

„Procedura Sub stochează valorile celulelor din coloana A a foii active în tabloul Sub GetCellValues ​​​​() Dim iRow As Integer” Stochează numărul rândului curent Dim dCellValues ​​() As Double „Array pentru stocarea valorilor celulelor iRow = 1 ReDim dCellValues ​​(1 To 10)" Loop Do Until trece succesiv prin celulele coloanei A a foii active "și extrage valorile acestora într-o matrice până când întâlnește celulă goală A face Until IsEmpty (Celele (iRow, 1)) „Verificați dacă matricea dCellValues ​​este suficient de mare” Dacă nu, măriți dimensiunea matricei cu 10 utilizând ReDim If UBound (dCellValues)< iRow Then ReDim Preserve dCellValues(1 To iRow + 9) End If "Сохраняем значение текущей ячейки в массиве dCellValues dCellValues(iRow) = Cells(iRow, 1).Value iRow = iRow + 1 Loop End Sub

Macrocomandă Excel: Exemplul 4

În acest exemplu, procedura Sub citește valori dintr-o coloană A fisa de lucru Foaia 2și se desfășoară cu ei operatii aritmetice... Rezultatele sunt înregistrate în celulele coloanei A pe foaia de lucru activă. Această macrocomandă demonstrează utilizarea obiectelor Excel. În special, se face contestație prin procedură Sub la obiect Coloane, și arată cum este accesat acest obiect prin intermediul obiectului Fisa de lucru... De asemenea, se arată că atunci când se face referire la o celulă sau la un interval de celule de pe foaia activă, nu este necesar să se specifice numele acestei foi atunci când se scrie linkul.

„Sub-procedura folosind o buclă citește valorile din coloana A a foii de lucru Sheet2”, efectuează operații aritmetice cu fiecare valoare și scrie rezultatul în „coloana A a foii de lucru active (Sheet1) Sub Transfer_ColA () Dim i As Integer Dim Col As Range Dim dVal As Double "Atribuiți coloana A a foii de lucru variabilei Col. Sheet 2 Set Col = Sheets (" Sheet2 "). Coloane (" A ") i = 1" Folosind o buclă, citiți valorile lui celulele Coloana Col până când „până când se întâlnește o celulă goală Do Until IsEmpty (Col.Cells (i))” Efectuați operații aritmetice asupra valorii celulei curente dVal = Col.Cells (i) .Value * 3 - 1 „Următoarea comandă scrie rezultat în coloana A a foii de lucru active „Nu trebuie să specificați numele foii în link, deoarece aceasta este foaia activă. Celulele (i, 1) = dVal i = i + 1 Loop End Sub

Macrocomandă Excel: Exemplul 5

Această macrocomandă arată exemplu de cod VBA care monitorizează un eveniment Excel. Evenimentul la care este legată macrocomanda are loc de fiecare dată când o celulă sau un interval de celule este selectată în foaia de lucru. În cazul nostru, atunci când selectați o celulă B1, pe ecran este afișată o fereastră de mesaj.

"Acest cod afișează o casetă de mesaj dacă celula B1 este selectată în foaia de lucru curentă „Private Sub Worksheet_SelectionChange (ByVal Target As Range)” Verificați dacă celula B1 este selectată Dacă Target.Count = 1 And Target.Row = 1 And Target.Column = 2 Then " Dacă este selectată celula B1, efectuați acțiunea necesară MsgBox „Ați selectat celula B1” End If End Sub

Macrocomandă Excel: Exemplul 6

Această procedură ilustrează utilizarea operatorilor La Eroareși Relua pentru a gestiona erorile. Acest cod arată, de asemenea, un exemplu de deschidere și citire a datelor dintr-un fișier.

„Procedura Sub setează argumentele Val1 și Val2 la valorile celulelor A1 și B1” din registrul de lucru Data.xlsx situat în folderul C: \ Documents and Settings Sub Set_Values ​​​​(Val1 As Double, Val2 As Double) Dim DataWorkbook As Workbook On Error GoTo ErrorHandling " Deschidem registru de lucru cu date Set DataWorkbook = Workbooks.Open ("C: \ Documents and Settings \ Data") "Atribuim valori din registrul de lucru specificat variabilelor Val1 și Val2 Val1 = Sheets (" Sheet1 "). Celule (1, 1). ) Val2 = Foi („Sheet1”). Celule (1, 2) DataWorkbook.Close Exit Sub ErrorHandling: „Dacă fișierul nu este găsit, utilizatorului i se va cere să introducă fișierul căutat” în folderul doritși apoi continuați executarea macrocomenzii MsgBox „Fișierul Data.xlsx nu a fost găsit!” & _ „Vă rugăm să adăugați registrul de lucru în folderul C: \ Documents and Settings și faceți clic pe OK” Reluare End Sub

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?

Macro în Microsoft Office (da, această funcționalitate funcționează la fel în multe aplicații Pachetul Microsoft Birou) este codul programuluiîn limbajul de programare (VBA), stocat în document. Pentru a fi mai clar, un document Microsoft Office poate fi comparat cu Pagina HTML, atunci macro-ul este analog cu Javascript. Ce poate face Javascript cu datele în format HTML situat pe o pagină web este foarte asemănător cu ceea ce poate face o macrocomandă cu datele din document Microsoft Birou.

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... aceasta masă simplă 10x20, umplut 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 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 Bine.

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 ferestre 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 document 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 din ea șablon gol... 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 celulele de date, faceți clic Click dreapta mouse-ul pe pictogramă Selectează tot care se află la intersecția titlurilor rândurilor și coloanelor și de la 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 cartea ca Șablon Excel cu suport pentru macro-uri, 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 macro-ului pe care l-ați creat, cred că este corect să acordați atenție unui cuplu Puncte importante referitor la macro-uri în general:

• Macro-urile pot fi dăunătoare.
• Citiți din nou punctul anterior.

Codul VBA este foarte mari oportunități... În special, poate efectua operațiuni pe fișiere din afara document 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. Daca ai setări standard securitate, atunci când deschideți fișierul deasupra tabelului, va apărea un avertisment că macrocomenzile sunt dezactivate ș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 notate folosind înregistrare automată o macrocomandă î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...

Imaginează-ți că al nostru dosarul original cu date 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.

Pentru a automatiza sarcinile repetitive, puteți înregistra o macrocomandă folosind Macro Recorder în Microsoft Excel... Imaginați-vă că aveți date în formate aleatorii și doriți să aplicați un singur format tuturor. Acest lucru se poate face folosind o macrocomandă. Puteți înregistra o macrocomandă prin aplicare formatul doritși redați macro-ul dacă este necesar.

Când înregistrați o macrocomandă, toate acțiunile necesare sunt scrise ca cod Visual Basic pentru aplicații (VBA). Astfel de acțiuni pot include introducerea de text sau numere, selectarea celulelor sau comenzilor din panglică sau meniuri, formatarea celulelor, rândurilor sau coloanelor și chiar importarea datelor dintr-o sursă externă, cum ar fi Microsoft Access... Visual Basic pentru aplicații (VBA) face parte din limbaj Programare vizuală De bază. Este disponibil în majoritatea aplicațiilor Office. Deși VBA vă permite să automatizați procesele în și între aplicațiile Office, nu trebuie să fiți competent în programare sau să cunoașteți limbajul VBA, deoarece macro recorderul se va ocupa de tot ce trebuie să faceți.

Este important să știți că atunci când înregistrați o macrocomandă, aproape tot ceea ce faceți este înregistrat. Prin urmare, dacă faceți o greșeală, cum ar fi să faceți clic pe butonul greșit, macro recorder-ul va înregistra acțiunea respectivă. Într-un astfel de caz, puteți rescrie întreaga secvență sau puteți modifica codul VBA. Prin urmare, înainte de a înregistra procesul, ar trebui să îl rezolvați bine. Cu cât înregistrați mai precis secvența, cu atât mai eficient va funcționa macrocomanda.

Dezvoltator care este ascuns în mod implicit, așa că trebuie să îl activați mai întâi. Pentru mai multe informații, consultați articolul Afișarea filei Dezvoltator.

Înregistrare macro

În fila Dezvoltator clic Macro-uri pentru a vizualiza macrocomenzile asociate cu registrul de lucru. Alternativ, puteți apăsa tastele ALT + F8... Aceasta va deschide o casetă de dialog Macro.

Atenţie:

		Aflați despre parametrii de securitate ai macrocomenzilor și semnificația acestora.
	Macro-urile pot fi rulate căi diferite, de exemplu folosind o comandă rapidă de la tastatură, obiect grafic, panouri acces rapid, butoane sau chiar atunci când deschideți o carte.
	Folosind Visual Basic Editor, puteți edita macrocomenzile atașate registrului de lucru.
	atribuiți o macrocomandă. În câmp Atribuiți o macrocomandă
	Aflați cum să activați și să dezactivați macrocomenzi în fișierele Office.
	Apăsați tastele ALT + F11.

Lucrul cu codul înregistrat în Visual Basic Editor (VBE)

Cu Visual Basic Editor (VBE), puteți adăuga propriile variabile, structuri de control și alte elemente la codul înregistrat pe care macro recorderul nu le acceptă. Deoarece reportofonul macro captează aproape fiecare pas care are loc în timpul înregistrării, este posibil să fie necesar să eliminați codul inutil. Vizualizați codul înregistrat - metodă grozavăînvață să programezi în VBA sau perfecționează-ți abilitățile.

Pentru un exemplu de modificare a codului înregistrat, consultați Începeți cu VBA în Excel.

Înregistrare macro

Este util să știți următoarele înainte de a înregistra macrocomenzi:

O macrocomandă înregistrată pentru a lucra Gama Excel, va fi executat numai pentru celulele din acest interval. Deci, dacă adăugați la gamă linie nouă, macrocomanda nu i se va aplica.

Dacă trebuie să înregistrați o secvență lungă de sarcini, vă recomandăm să utilizați mai multe macrocomenzi mai mici.

Macro-ul poate conține și sarcini care nu sunt legate de Excel. Procesul macro poate include și altele Aplicații de birouși alte programe care acceptă Visual Basic pentru aplicații (VBA). De exemplu, puteți înregistra o macrocomandă care actualizează mai întâi un tabel în Excel și apoi deschide Outlook pentru a o trimite prin e-mail.

Macrocomenzile și instrumentele VBA sunt situate în filă Dezvoltator care este ascuns în mod implicit, așa că trebuie să îl activați mai întâi.

Te rog selecteaza excela > Opțiuni> Panglică și panou.

Pentru a înregistra o macrocomandă, urmați instrucțiunile de mai jos.

Lucrul cu macrocomenzi înregistrate în Excel

În fila Dezvoltator clic Macro-uri pentru a vizualiza macrocomenzile asociate cu registrul de lucru. Aceasta va deschide o casetă de dialog Macro.

Notă: Macrocomenzile nu pot fi anulate. Înainte de a rula macrocomanda înregistrată pentru prima dată, salvați sau creați o copie a registrului de lucru pentru a preveni modificări nedorite... Dacă nu sunteți mulțumit de rezultatele execuției macro, puteți închide registrul de lucru fără a-l salva.

Mai jos sunt Informații suplimentare despre lucrul cu macrocomenzi în Excel.

		Aflați cum să activați și să dezactivați macrocomenzi în Excel pentru Mac.
	Pentru a economisi timp în sarcinile frecvent repetitive, puteți înregistra secvența corespunzătoare de acțiuni sub forma unei macrocomenzi. Aflați cum să creați și să rulați macrocomenzi.
	Dacă registrul de lucru conține o macrocomandă VBA pe care doriți să o utilizați în altă parte, puteți copia modulul într-un alt registru de lucru folosind Editor Microsoft Visual Basic.
Atribuirea unei macrocomenzi unui obiect, unei forme sau element grafic	Pe foaie, faceți clic dreapta pe obiectul, imaginea, forma sau elementul căruia doriți să îi atribuiți o macrocomandă existentă, apoi faceți clic pe atribuiți o macrocomandă. În câmp Atribuiți o macrocomandă selectați macrocomanda pe care doriți să o atribuiți.
	Puteți să atribuiți o macrocomandă unei pictograme și să o adăugați la Bara de instrumente Acces rapid sau în Panglică.
	Puteți atribui macrocomenzi formularelor și controale ActiveX pe foaie.
Deschiderea editorului Visual Basic	În fila Dezvoltator clic Visual Basic sau selectați Serviciu > Macro > Editor Visual Basic.
	Aflați cum să găsiți ajutor pentru articolele Visual Basic.

Informații suplimentare

Puteți pune oricând o întrebare comunității Excel Tech, puteți cere ajutor în comunitatea Răspunsuri și, de asemenea, puteți sugera noua functie sau îmbunătățirea site-ului