Macrocomenzi utile Excel. Macro-uri în Excel - Instrucțiuni de utilizare. Exemple de macrocomandă Excel

Bună ziua

Vreau să dedic acest articol unei secțiuni atât de uriașe a MS Excel precum macrocomenzile sau, mai degrabă, să începem de la început și să vedem 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ă se efectuează aceleași operațiuni și acțiuni care sunt necesare pentru a obține rezultatul, aceasta poate fi completarea aceluiași tip de tabele sau formulare, prelucrarea datelor, similare rapoarte săptămânale, lunare etc. Dar utilizarea macrocomenzilor vă va permite să efectuați aceste acțiuni în mod automat, folosind capabilităț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 necesare, care nu sunt încă implementate în standard Funcții Excel(de exemplu, ieșire, colectarea datelor pe o singură foaie etc.).

Dacă nu ați auzit niciodată de o macrocomandă, cea mai precisă definiție a acesteia ar fi: acestea sunt acțiuni pentru care sunt programate o anumită secvențăși scris într-un mediu de programare în limbaj Visual Basic pentru aplicații (VBA). Rularea unei macrocomenzi se poate face de mai multe ori și acest lucru va forța Excel să efectueze orice secvență de acțiuni de care avem nevoie și care 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 VBA este standardul și funcționează în orice aplicație de suită de birou.

Deci, există 2 moduri de a crea o macrocomandă în Excel:

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

Î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 în modulul software comenzile care au fost obținute în timpul procesului de lucru. Adică, dacă creăm raportul zilnic de care avem nevoie cu macro recorder pornit, macro recorder va înregistra totul în comenzile sale pas cu pas și, ca urmare, va crea o macro care va crea un raport zilnic automat.

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

• Un macro recorder poate înregistra doar ceea ce poate atinge, ceea ce înseamnă că poate înregistra acțiuni numai atunci când butoanele, pictogramele, comenzile de meniu și orice asemenea opțiuni nu sunt disponibile;
• Dacă a fost făcută o eroare în timpul perioadei de înregistrare, aceasta va fi de asemenea î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 dacă închideți programul sau porniți altul, înregistrarea va fi oprită și nu va mai fi efectuată.

Pentru a activa macro recorder-ul pentru înregistrare, trebuie să efectuați următorii pași:

Următorul pas în lucrul cu înregistrarea macro va fi configurarea parametrilor acestuia pentru înregistrarea ulterioară a macro-ului, acest lucru se poate face în fereastră „Înregistrați o macrocomandă”, 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 sau spații;
• Câmp de comandă rapidă 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 „Instrumente” - „Macro” - „Macro” - „Run” sau pe filă "Dezvoltator" apăsând un buton "Macro-uri";
• Câmpul „Salvează în...”.— puteți specifica locația în care va fi salvat textul macro (dar nu trimis) și există 3 opțiuni:
  • "Această carte"— macro-ul va fi scris în modulul registrului de lucru curent și poate fi executat numai dacă această carte Excel se va deschide;
  • "O carte noua"— macro-ul va fi salvat în șablonul pe baza căruia se creează un șablon gol în Excel O carte noua, ceea ce înseamnă că macro-ul va deveni disponibil în toate registrele de lucru care vor fi create pe acest computer de acum înainte;
  • „Cartea macro personală”- este o carte specială macrocomenzi Excel, Care e numit „Personal.xls”și este folosit ca o bibliotecă specială de stocare a macrocomenzilor. La pornire, macrocomenzile din registrul de lucru „Personal.xls” sunt încărcate în memorie și pot fi lansate în orice registru de lucru în orice moment.
• Câmpul „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 se numește memorie.

Odată ce ați rulat și înregistrat macrocomanda, completâ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.

Lansarea editorului de programe VBA are loc 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 ulterioare, avem nevoie de pe filă "Dezvoltator" apasa butonul „Editor Visual Basic”. Dacă nu găsiți această filă, trebuie să o activați selectând elementul de meniu „Fișier” - „Opțiuni” - „Personalizare panglică” iar în caseta de dialog, utilizați caseta de selectare pentru a activa fila "Dezvoltator".

În fereastra care se deschide se vede interfața editorului VBA, totul aici este scris în engleză și nu există localizări, nu trebuie să o cauți, ci doar să o accepți și să lucrezi, mai ales că este doar de neînțeles la început, apoi totul va fi familiar.

Deci, cum să lucrați în editorul VBA, unde este ce este stocat și cum să creați o macrocomandă în Excel. Întrebări de această natură apar imediat de îndată ce îl vedeți pe editor, iar 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țile oricărei versiuni de Excel, putem crea oricare module softwareîn orice cantitate și plasați în ele toate macrocomenzile pe care le creăm. Un modul poate conține orice număr de macrocomenzi de care aveți nevoie sau le creați. Modulele sunt disponibile în fereastră „Explorator de proiect”și sunt situate în colțul din stânga sus al editorului de macro-uri (puteți apela și cu combinația de taste CTRL+R).

Modulele de program din editorul VBA există în mai multe tipuri și sunt utilizate pentru diferite opțiuni și situații:

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

Să ne uităm la un exemplu de macrocomandă funcțională:

• Toate macrocomenzile în obligatoriu va începe cu operatorul Sub, urmat de numele macrocomenzii și de o listă de argumente între paranteze. În cazurile în care nu există argumente, parantezele trebuie lăsate goale;
• Toate macrocomenzile trebuie să se termine 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 VBA în special. Este foarte dificil să înțelegeți și să vă dați seama ce comenzi sunt introduse și cum, ce argumente folosește pentru ca macro-ul să înceapă să-și facă treaba automat. Dar cel care umblă va stăpâni drumul, așa cum spuneau vechii înțelepți, și de aceea nu trebuie să renunți, ci să urmezi poruncile bunicului 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 pariu ar fi să creați un buton pentru a rula macro-ul. Butoanele pot fi create de mai multe tipuri, și anume:

Această metodă este disponibilă pentru orice versiune de MS Excel și constă în faptul că vom plasa butonul direct pe 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 versiuni ulterioare ai nevoie pe 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ă finalizarea procesului de desenare, se va deschide automat o fereastră în care va trebui să selectați macrocomanda care trebuie să fie executată atunci 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ă o macrocomandă va efectua acțiunile conținute în ea cu obiecte de carte sau de foaie (acestea sunt formule, celule etc.), dar o funcție definită de utilizator funcționează doar cu valorile pe care le primește de la noi iar acestea sunt argumente date inițiale pentru calcule.

De exemplu, pentru a crea funcție personalizată taxa pe valoarea adăugată, alias 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 macro este antetul Funcţieînlocuit Subși există o listă completă de argumente, în exemplul nostru aceasta este Suma. Odată ce codul nostru a fost introdus, acesta va deveni disponibil în fereastra standard Function Wizard, care se află în meniu "Formulele", paragraful „Inserare funcție”.
Și selectaț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 celula cu argumentul, care va conține suma pentru care calculăm TVA, totul se întâmplă ca la o funcție obișnuită.
Și asta e tot pentru mine! Chiar sper că articol despre cum să creați o macrocomandă în Excel ți-a fost clar și util. Aș fi foarte recunoscător pentru comentariile voastre, deoarece acesta este un indicator de lizibilitate și mă inspiră să scriu articole noi! Distribuie ce ai citit prietenilor tăi și da like!

Excel are o capacitate puternică, dar rar folosită, de a crea secvențe automate de acțiuni folosind macrocomenzi. O macrocomandă este o 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 formatarea documentelor folosind un ș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 mai departe cu îndrăzneală - apoi vom parcurge întregul proces de creare a unui macro pas cu pas împreună cu dvs.

Ce este o macro?

O macrocomandă în Microsoft Office (da, această funcționalitate funcționează la fel în multe aplicații din suita Microsoft Office) este codul programuluiîntr-un limbaj de programare (VBA), salvat în document. Pentru a fi mai clar, un document Microsoft Office poate fi comparat cu Pagina HTML, atunci 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 din document Microsoft Birou.

Macro-urile pot efectua aproape orice acțiune pe care o doriți într-un document. Iată câteva dintre ele (o parte foarte mică):

• Aplicați stiluri și formatare.
• Îndeplini diverse operatii cu date numerice și text.
• Utilizare surse externe date (fișiere baze de date, documente text etc.)
• Creați un document nou.
• Efectuați toți pașii de mai sus în orice combinație.

Crearea unei macrocomenzi - Exemplu practic

De exemplu, să luăm cel mai mult dosar obișnuit CSV. Acest 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 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 Vedere(Vizualizare) > Macro-uri(Macro-uri) > Înregistrare macro(Înregistrați o macrocomandă...)

Dați macrocomenzii un nume (fără spații) și faceți clic Bine.

Din acest moment, TOATE acțiunile tale cu documentul sunt înregistrate: modificări de celulă, defilare prin tabel, chiar schimbarea dimensiunii ferestrei.

Excel semnalează că modul de înregistrare macro este activat în două locuri. În primul rând, în meniu Macro-uri(Macro) – în loc de o linie Î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 de toate, 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)
• =MEDIANĂ(B2:K2) sau =MEDIANĂ(B2:K2)

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

Odată ce această acțiune este finalizată, totalurile corespunzătoare ar trebui să apară în 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 originale din tabel. Dacă luați media mediilor pentru rânduri individuale, 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 din tabel pentru motivul menționat mai sus.

Acum că am terminat cu calculele, să trecem la formatare. Mai întâi, să setăm același format de afișare a datelor pentru toate celulele. Selectați toate celulele de pe foaie folosind 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 apasa Stil virgulă(Format delimitat). Acasă(Acasă).

• Stilul de font aldine.
• Alinierea la centru.
• Umplere cu culoare.

Și, în sfârșit, să setăm formatul valorilor totale.

Iată cum ar trebui să arate în final:

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ă ștergem toate datele din tabelul pe care l-am creat, de exemplu. fă-l un șablon gol. Faptul este că în viitor, lucrând cu acest șablon, vom importa cele mai recente și mai relevante date în el.

Pentru a șterge toate celulele de date, faceți clic Click dreapta mouse-ul după 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 cartea ca Șablon Excel macro-activat, care are extensia XLTM.

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.

Odată ce șablonul este salvat, puteți închide Excel în siguranță.

Rularea 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 paragraful anterior.

Codul VBA este foarte puternic. În special, poate efectua operații 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. Daca ai setări standard securitate, apoi atunci când deschideți un fișier, va apărea un avertisment în partea de sus a tabelului care afirmă că rularea macrocomenzilor este dezactivată și un buton pentru a activa execuția lor. Din moment ce noi am făcut șablonul ș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 macro-ul nostru pe baza unui astfel de fișier).

Când importați date dintr-un fișier CSV, Excel vă poate solicita să configurați unele setări transmisie corectă date într-un tabel.

Când importul este complet, 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ă ruleze, veți vedea cursorul tabelului sărind de la o celulă la alta. După câteva secunde, se vor efectua aceleași operații asupra datelor 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.

Să ne uităm sub capotă: Cum funcționează o macro?

După cum sa 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 limbaj VBA. Mai simplu spus, Excel scrie codul pentru tine.

Pentru a vedea acest cod de program, trebuie să mergeți la 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ă macro în Excel. Dar macrocomenzile mai complexe, cu o secvență și o logică a acțiunilor bine reglate, 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:\Data\data.csv– calea către fișierul cu datele 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 macro-ul - 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. Eliminați toate datele din celule.
6. Salvați fișierul ca șablon Excel cu suport macro (extensie XLTM).

Astfel, prin rularea acestui șablon, aveți acces la două macrocomenzi - una încarcă date, 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.

Tabelele Excel sunt una dintre cele mai populare moduri de stocare și organizare a informațiilor. Dacă introduceți macrocomenzi în Excel, capacitățile programului pot fi aproape dublate. De exemplu, macrocomenzile ajută la automatizare procese de rutinăși accelerează munca monotonă. Ele sunt adesea folosite de specialiștii care traduc rapoarte din 1C în Excel. Cu ajutorul codului încorporat, acestea pot fi ușor reduse la un singur șablon standard.

Ce sunt macrocomenzile Excel?

Macro-urile sunt folosite nu numai în tabele, ci și în Microsoft Office. Sunt coduri create folosind Visual Basic for Applications. Dacă sunteți parțial familiarizat cu programarea web, atunci puteți face o analogie cu Javascript. Macrocomenzile Excel fac aproximativ același lucru pe care îl face Javascript cu datele HTML.

O mică parte din ceea ce poate face o macrocomandă în aplicațiile de birou:

Aliniați documentul în funcție de stil și formatați-l;
. efectuează operații cu date numerice și text;
. caută ajutor fișiere externe(text, baze de date și altele);
. creați noi documente de la zero;
. combinați mai multe acțiuni și efectuați-le automat.

Pentru a lucra cu un tabel, îl puteți crea singur sau îl puteți descărca opțiune gata făcută de pe internet. Macro-urile ar trebui să fie descărcate numai de pe site-uri de încredere, altfel puteți introduce un virus în Excel.

Cum pot fi periculoase macrocomenzile din Excel?

Deteriorează fișierele de pe computer;
. fura date confidențiale.

Virusul este introdus în timpul funcționării unuia dintre aplicații de birou- Word sau Excel. După ce încetați să lucrați, vor începe funcţionare autonomă virus și infectează întregul sistem.

Un alt mod în care funcționează o macrocomandă rău intenționată este ca intermediar pentru a introduce amenințări în sistem. În acest caz, este un fel de poartă de intrare pentru introducerea și generarea de software troian. Nu va mai fi controlat de o macrocomandă modificată, ci de sistem de operare, și prin urmare amenințarea devine mai mare. Nu este întotdeauna posibil să vindeci astfel de viruși uneori trebuie să schimbi complet sistemul de operare Windows sau HDD PC (în funcție de sarcinile pe care hackerul le-a stabilit la scrierea codului).

Puteți alege orice metodă de protecție împotriva unor astfel de macrocomenzi. Cea mai populară opțiune este creșterea nivelului de securitate al Excel, dar în acest caz este posibil ca programul să nu mai suporte macrocomenzile tale originale, utile. Nu ar trebui să aveți încredere în fișierele de la surse necunoscute, deoarece se pot infecta. Chiar și o macrocomandă primită de la computerul colegului tău poate fi periculoasă. Dacă un prieten v-a oferit un fișier cu care să lucrați care acceptă utilizarea macrocomenzilor, atunci înainte de a permite sistemului să-l folosească, clarificați cum a primit această macrocomandă.

Cea mai bună opțiune este să înveți în mod independent arta de a crea macrocomenzi pentru nevoile tale. În acest caz, puteți garanta pe deplin calitatea și siguranța acestora.

Cum să înregistrați macrocomenzi în Excel

Cel mai simplu mod de a crea o macrocomandă este să programați direct în Excel. Pentru a face acest lucru, veți avea nevoie de un tabel cu datele introduse și puține cunoștințe pentru a permite înregistrarea unui astfel de cod în program.

Crearea începe cu meniul Vizualizare. În el, trebuie să selectați poziția „Macro-uri” și comanda „Înregistrare macrocomandă”. Apoi, trebuie să specificați un nume fără spații, de exemplu, „Format” (dacă codul va fi responsabil pentru modificarea formatului textului sau al celulelor).

După care toate acțiunile tale vor fi înregistrate automat și convertite în cod macro. Un buton cu un pătrat mic („Oprire”) va apărea în partea de jos a documentului, făcând clic pe care puteți opri înregistrarea. Astfel, puteți înregistra automat macrocomenzi în Excel, exemple ale cărora le puteți analiza în continuare în procesul de învățare.

Cum să activați macrocomenzi în Excel

Pentru a activa o macrocomandă înregistrată în alte documente, trebuie să o salvați după ștergerea tabelului. Formatul documentului trebuie setat ca XLTM (este cel care acceptă macrocomenzi).

Când accesați ulterior documentul pentru a activa macrocomenzi în Excel, mai întâi trebuie să le activați în fereastra pop-up linia de sus. Apoi, trebuie să importați datele care vor fi editate de macrocomandă dintr-un tabel CSV obișnuit. Odată ce importul este finalizat, trebuie să reveniți la meniul „Vizualizare”, selectați acolo „Macrocomenzi” și găsiți numele codului pe care l-ați creat în listă. În cele din urmă, faceți clic pe butonul „Run”. După care veți vedea cum programul, conform algoritmului specificat anterior, efectuează calcule și formatează documentul.

Cum pot vedea codul macro?

Toate macrocomenzile create în Excel sunt scrise automat, fără intervenția umană. Programul compune în mod independent codul programului, care poate fi deschis din tabel apelându-l folosind meniul.

Pentru a face acest lucru, trebuie să rulați secvențial comenzile:

Extindeți fila „Vizualizare”;
. deschideți „Macro-uri”;
. selectați numele macrocomenzii necesare;
. Apelați o fereastră cu codul său folosind butonul „Schimbare”.

În noua casetă de dialog puteți edita și modifica macrocomanda. Veți vedea acolo codul pe care programul l-a înregistrat automat și puteți adăuga manual orice acțiune. După salvarea informațiilor, macrocomanda va funcționa diferit atunci când se importă date noi.

Cum să porniți o macrocomandă făcând clic pe o celulă

Să presupunem că doriți ca o macrocomandă să ruleze atunci când este selectată celula A1. Pentru ca o celulă selectată în Excel să ruleze o macrocomandă, trebuie să adăugați cod de program la primul modul:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dacă Target.Address = "$A$1" Atunci: Apelați modulul1
End Sub

Cel mai bun mod de a învăța macrocomenzi este în tutorial Excel. Există mai mult de un exemplu similar, iar pentru cei care tocmai se familiarizează cu toate posibilitățile tabelului, acesta este Cel mai bun modînvață să-și folosească pe deplin funcționalitatea.

Unde poți învăța să lucrezi cu macrocomenzi?

Există mai multe moduri de a învăța cum să creați macrocomenzi mai complexe. Ele trebuie să fie distinse prin logică și o secvență clar definită de acțiuni. Astfel de macrocomenzi pot fi create doar prin scrierea manuală a codului VBA. Pentru a crea macrocomenzi complexe, va trebui să petreceți ceva timp învățând complexitățile limbii Programare vizuală Basik.

Acum să-i explorezi pe toată lumea Capabilitati Excel Au fost create multe cursuri speciale care durează câteva săptămâni, dar pentru a le urma va trebui să vă deplasați la un centru de formare sau să participați la webinarii (ceea ce nu este întotdeauna convenabil din cauza diferenței de timp). Dar o modalitate mai eficientă și mai simplă de a învăța cum să creați macrocomenzi în Excel este un tutorial cu exemple. Îl poți studia în orice moment convenabil pentru tine, iar manualele pas cu pas te vor ajuta să te dezvolți diferite variante: din cele mai multe coduri simple la cele mai complexe. Microsoft Excel este un editor universal de foi de calcul care poate fi folosit pentru a rezolva multe probleme complexe.

Exemple

Există coduri care vă permit să realizați automatizare diverse sarcini. Deci, de exemplu, să ne uităm la macrocomanda de inserare a rândurilor. Arata cam asa:

Sub Macro1()

VBA este considerat limbajul de scripting standard pentru aplicațiile Microsoft și este acum inclus în toate Aplicații de birouși chiar aplicații de la alte companii. Prin urmare, odată ce stăpâniți VBA pentru Excel, puteți trece imediat la crearea de macrocomenzi pentru alte software-uri. produse Microsoft. Mai mult, vei putea crea cu drepturi depline produse software, folosind simultan funcțiile unei varietăți de aplicații.

Cum să activați macrocomenzi în Excel

În mod implicit, fila responsabilă cu gestionarea și navigarea macrocomenzilor în Excel este ascunsă. A activa această opțiune accesați fila Fişier la grup Opțiuni.În caseta de dialog care apare Opțiuniexcela accesați fila Personalizare panglică,în caseta combinată din dreapta, plasați un marcator vizavi de filă Dezvoltator. Aceste acțiuni sunt relevante pentru versiuni Excel 2010 și mai vechi.

Apare pe bandă inserție nouă Dezvoltator cu controale de automatizare Excel.

Scrierea macrocomenzi în Excel

În fila Dezvoltator in grup Cod, faceți clic pe butonul Înregistrați o macrocomandă. Va apărea o casetă de dialog Înregistrați o macrocomandă, care solicită unele informații despre viitorul cod care se scrie. Dacă este prima dată când creați o macrocomandă, puteți pur și simplu să faceți clic pe butonul BINE. CU în acest moment Excel va înregistra fiecare acțiune a utilizatorului într-un modul VBA, fie că este vorba de introducerea datelor, formatarea sau crearea de diagrame. Pentru a opri înregistrarea unei macrocomenzi, faceți clic pe butonul Opriți înregistrarea care se află în același grup Cod.

De asemenea, puteți profita de o opțiune alternativă pentru înregistrarea macrocomenzi utilizând butonul Înregistrați o macrocomandă, care se află în colțul din stânga jos al registrului de lucru Excel (în dreapta stării Gata).

Acum puteți vizualiza o listă cu toate macrocomenzile create făcând clic pe butonul Macro, situat în grup Cod.În caseta de dialog care apare, puteți da nume mai descriptive codurilor sau puteți seta comenzi rapide de la tastatură care ar rula o anumită macrocomandă. Opțiune alternativă a lansa această fereastră înseamnă a apăsa Tastele Alt+ F8.

Editarea macrocomenzilor

Felicitări! Ai scris prima ta macrocomandă. Ar fi logic să verificăm acum ce cod a generat Excel pentru noi. Codul generat este scris în VBA (Visual Basic for Applications). Trebuie să-l deschizi ca să-l vezi. EditorVB(VBE), care se lansează apăsând Alt + F11 sau butonul VizualDe bază pe filă Dezvoltator.

Pentru a evita confuzia în editor, puteți lucra cu o singură filă într-un registru de lucru, foaie sau modul. Așa arată editorul în viața reală.

ofer mai departe în această etapă Aflați mai multe despre diferitele ferestre și meniuri ale editorului VBA. Acest lucru vă va ajuta să economisiți mult timp în viitor.

Pentru a vedea codul, dați clic pe fir Moduleîn fereastra proiectelor și faceți dublu clic pe ramura care apare Modul1 . Editorul va deschide o fereastră cu codul, așa cum se arată în imagine.

Aici puteți edita codul generat care a fost scris în timp ce lucrați în Excel. De exemplu, trebuie să completați o anumită coloană cu valori de la 1 la 10. Aveți deja primii trei pași, care introduc valorile 1, 2 și 3 în primele trei celule ale coloanei A. Trebuie să parcurgeți restul de șapte pași.

Dacă te uiți la codul de mai sus, vei vedea că macro-ul într-un anumit fel structurat. Aplicația mută mai întâi cursorul pe celulă folosind comanda Range(„A1”). Selectați, apoi editează conținutul utilizând ActiveCell.FormulaR1C1 = „1”. Deci pentru pașii rămași putem repeta acești pași, schimbând adresa celulei și valoarea pe care doriți să o scrieți în celula respectivă. De exemplu, pentru a seta celula A4 la 4, ați scrie:

Și repetați pași similari pentru valorile rămase.

După ce ați terminat de editat, salvați cartea. Puteți rula macrocomandă apăsând butonul F5 sau revenind la lucru Caietul de lucru Excel, accesați fila Dezvoltator la grup Cod -> Macro-uriși selectați din listă macrocomanda care vă interesează.

Luați câteva minute pentru a studia cu atenție codul generat de Excel. Dacă sunteți începător, investind câteva minute în învățarea codului va aduce rezultate uimitoare în cunoașterea obiectelor VBA mai târziu. Vă rugăm să rețineți că exemplul pe care l-am discutat este doar o ilustrație. Există mai rapide și moduri eficiente obținând rezultate similare, despre care vom discuta mai târziu.

Creșteți viteza de execuție a macrocomenzii Excel

Până acum, bine. Să ne uităm la câteva trucuri care vor ajuta la accelerarea execuției macro. Să luăm ca exemplu fragmentul de cod de mai sus. Calculatoare moderne va lucra la codul în cauză atât de repede încât nici măcar nu îl veți observa. Dar dacă trebuie să efectuați operația de 50.000 de ori. Acest lucru va dura ceva timp. Dacă macrocomanda pe care o scrieți are o lungime de sute de linii, puteți accelera execuția codului prin tăierea părții din procese care nu este utilizată în timpul execuției macrocomenzii.

Folosind comanda Application.ScreenUpdating

Primul truc este să evitați actualizarea ecranului în timp ce macro-ul rulează. Acest lucru va permite Excel să salveze putere de calcul computer și actualizați ecranul cu cele mai recente valori numai după ce tot codul a fost executat. Pentru a face acest lucru, trebuie să adăugați o comandă pentru a dezactiva reîmprospătarea ecranului la începutul macrocomenzii și o comandă pentru a activa reîmprospătarea ecranului la sfârșitul macrocomenzii.

Comanda Application.ScreenUpdating îi spune Excel să nu mai afișeze datele recalculate pe ecran și să returneze valorile terminate la sfârșitul execuției codului.

Folosind comanda Aplicație. Calcul

Al doilea truc este dezactivarea calculelor automate. Lasă-mă să explic. De fiecare dată când un utilizator sau un proces actualizează o celulă, Excel încearcă să recalculeze toate celulele care depind de aceasta. Deci, să presupunem că dacă celula pe care macro-ul încearcă să o actualizeze afectează alte 10.000 de celule, Excel va încerca să le recalculeze pe toate înainte ca codul să se termine de executat. În consecință, dacă există întreaga linie influențând celulele, recalcularea poate încetini semnificativ execuția codului. Pentru a preveni acest lucru, puteți instala comanda Aplicație. Calcul la începutul codului, care va comuta recalcularea formulelor la mod manualși apoi returnați calculul automat la sfârșitul macro-ului.

Fiți atenți, nu uitați să comutați din nou această opțiune la mod auto la sfârșitul macro-ului. În caz contrar, va trebui să faceți acest lucru în Excel, făcând clic pe filă Formule la grup Calculși alegeți Opțiuni de calcul –> Automat.

Evitarea selectării celulelor și intervalelor

În modul de înregistrare automată a macrocomenzilor, este posibil să observați că Excel utilizează foarte des comanda de selecție a celulei, de exemplu, Interval(„A1”).Selectați. În exemplul nostru, am folosit această comandăîn mod repetat pentru a selecta o celulă și a modifica valoarea acesteia. Puteți evita acest lucru prin simpla specificare a adresei celulei și oferindu-i valoarea necesară (Macro-ul a înregistrat mișcarea cursorului de la o celulă la alta, prin urmare inserând acești pași. Cu toate acestea, nu sunt necesari). Deci, un cod mai eficient ar arăta așa.

ÎN în acest caz, pur și simplu am făcut referire la celulă și i-am dat valoarea necesară fără a o selecta deloc. Această metodă este mai rapidă decât cea anterioară.

Exemple de macrocomandă Excel

Mai jos sunt câteva exemple de coduri VBA care vă vor ajuta să automatizați cele mai comune sarcini.