Makrot e gatshme. Mironov

Mirembrema!

Unë dua t'ia kushtoj këtë artikull një seksioni kaq të madh të MS Excel si makro, ose më saktë, le të fillojmë nga e para dhe të shohim se si të krijojmë një makro në Excel, për çfarë është dhe si ta përdorni atë në punën tuaj.

Siç e dini nga përvojën e vet, kur punoni me një makro, ka shumë "rutina", domethënë kryhen të njëjtat operacione dhe veprime që nevojiten për të marrë një rezultat, mund të jetë plotësimi i tabelave të të njëjtit lloj ose forma, përpunimi i të dhënave, raporte të ngjashme javore, mujore, etj. Por përdorimi i makrove do t'ju lejojë të kryeni këto veprime në modaliteti automatik, duke përdorur në maksimum aftësitë e Excel, duke hedhur poshtë këto operacione rutinë dhe monotone mbi supet e fuqishme të Excel. Gjithashtu, arsyeja e përdorimit të makrove mund të jetë shtimi mundësitë e duhura, të cilat ende nuk janë implementuar në standard funksionet e Excel(për shembull, prodhimi, mbledhja e të dhënave në një fletë, etj.).

Nëse nuk keni dëgjuar kurrë për një makro, atëherë përkufizimi më i saktë do të ishte: këto janë veprime që programohen për një sekuencë të caktuar dhe regjistrohen në mjedisin e programimit në Gjuha pamore Baza për Aplikime (VBA). Drejtimi i një makro mund të bëhet në mënyrë të përsëritur dhe kjo do të detyrojë Excel të kryejë çdo sekuencë veprimesh që na nevojiten, të cilat thjesht nuk na pëlqejnë ose nuk duam ta bëjmë me dorë. Pavarësisht nga shumëllojshmëria e madhe e gjuhëve të programimit për të gjithë kompleksin Microsoft Office VBA është standardi dhe funksionon në çdo aplikacion të paketës së zyrës.

Pra, ka 2 mënyra për të krijuar një makro në Excel:

Krijoni makro në Excelme makro regjistrues

Për të filluar, le të sqarojmë se çfarë është një regjistrues makro dhe çfarë lidhje ka një makro me të.

makro regjistruesështë një program i vogël i ngulitur në Excel që interpreton çdo veprim të përdoruesit në kodet gjuhësore Programimi VBA dhe shkruan në modulin e programit komandat që janë marrë gjatë punës. Kjo do të thotë, nëse ne, me makroregjistruesin të ndezur, krijojmë raportin ditor që na nevojitet, makroregjistruesi do të regjistrojë gjithçka në komandat e tij hap pas hapi dhe si rezultat do të krijojë një makro që do të krijojë automatikisht një raport ditor.

Kjo metodë është shumë e dobishme për ata që nuk kanë aftësi dhe njohuri për të punuar në mjedisin gjuhësor VBA. Por një lehtësi e tillë e ekzekutimit dhe regjistrimit të një makro ka të metat e saj, si dhe avantazhet:

• Regjistruesi makro mund të regjistrojë vetëm atë që mund të ndjejë, që do të thotë se mund të regjistrojë veprime vetëm kur përdoren butona, ikona, komanda të menysë dhe gjithçka të ngjashme, opsione të tilla që nuk janë të disponueshme për të;
• Në rast se është bërë një gabim gjatë periudhës së regjistrimit, ai gjithashtu do të regjistrohet. Por mund të përdorni butonin e anulimit veprimi i fundit, fshini komandën e fundit që keni shkruar gabimisht në VBA;
• Regjistrimi në makroregjistrues kryhet vetëm brenda kufijve të dritares MS Excel, dhe në rastin kur mbyllni programin ose ndizni një tjetër, regjistrimi do të ndalet dhe do të pushojë së kryeri.

Për të aktivizuar makroregjistruesin për regjistrim, duhet të kryeni veprimet e mëposhtme:

Hapi tjetër në punën me makroregjistruesin do të jetë vendosja e parametrave të tij për regjistrim të mëtejshëm makro, kjo mund të bëhet në dritare "Regjistrimi makro", ku:

• Fusha e emrit makro- mund të shkruani një emër që kuptoni në çdo gjuhë, por ai duhet të fillojë me një shkronjë dhe të mos përmbajë një shenjë pikësimi dhe hapësira;
• fusha e shkurtoreve të tastierës- do të përdoret nga ju, në të ardhmen, për fillim i shpejte makro juaj. Në rastin kur duhet të regjistroni një të re, ky opsion do të jetë i disponueshëm në meny "Shërbimi" - "Makro" - "Macros" - "Run" ose në skedën "Zhvilluesi" duke shtypur një buton "Makro";
• Fusha "Ruaj në...".- mund të specifikoni vendin ku do të ruhet (por nuk do të dërgohet) teksti makro, dhe këto janë 3 opsione:
  • "Ky libër"— makro do të shkruhet në modulin e librit aktual dhe mund të ekzekutohet vetëm nëse është dhënë Libër pune në Excel do të jetë i hapur;
  • "Nje liber i ri"- makro do të ruhet në shabllonin mbi bazën e të cilit krijohet një bosh në Excel nje liber i ri, që do të thotë se makroja do të jetë e disponueshme në të gjithë librat e punës që do të krijohen në këtë kompjuter tani e tutje;
  • "Libri personal makro"është një libër pune special për makro Excel i quajtur "Personal.xls" dhe përdoret si një depo-bibliotekë e veçantë e makrove. Në nisje, makrot nga libri Personal.xls ngarkohen në memorie dhe mund të ekzekutohen në çdo libër në çdo kohë.
• Fusha e përshkrimit- këtu mund të përshkruani se çfarë dhe si duhet të bëjë makro, pse u krijua dhe çfarë funksionesh mbart, kjo është një fushë thjesht informative, e cila quhet memorie.

Pasi të keni ekzekutuar dhe regjistruar makro duke bërë të gjitha veprimet e nevojshme, regjistrimi mund të ndërpritet me komandën "Ndalo regjistrimin" dhe makroja juaj me makroregjistrues do të krijohet.

Krijoni makro në Excelnë redaktorin e Visual Basic.

Në këtë metodë, ne do të shikojmë se si të krijojmë një makro në Excel përmes redaktuesit të programit VBA, i cili, siç thashë më lart, është i integruar në çdo version të Excel.

Fillimi i redaktuesit të programit VBA është i ndryshëm, në varësi të versionit të programit tuaj Excel:

• në versionet e Excel 2003 dhe më vonë, na duhet në meny "Shërbimi", zgjidhni artikullin "Makro" dhe shtypni "Redaktor Bazë vizuale» ;
• në versionet e Excel 2007 dhe më vonë, duhet të tab "Zhvilluesi" Shtyp butonin "Redaktori Visual Basic". Në rastin kur nuk e gjeni këtë skedë, duhet ta aktivizoni duke zgjedhur artikullin e menysë "Skedari" - "Opsionet" - "Personalizoni shiritin" dhe në kutinë e dialogut, kontrolloni kutinë për të aktivizuar skedën "Zhvilluesi".

Në dritaren që hapet, ju mund të shihni ndërfaqen e redaktorit VBA, gjithçka është e shkruar në anglisht këtu dhe nuk ka lokalizime, nuk duhet të kërkoni, por thjesht pranoni dhe punoni, veçanërisht pasi në fillim është vetëm e pakuptueshme. dhe atëherë gjithçka do të jetë e njohur.

Pra, si të punoni në redaktorin VBA, ku ruhet dhe si të krijoni një makro në Excel. Pyetje të kësaj natyre lindin sapo të shihni redaktorin, dhe tani do t'i shqyrtojmë të gjitha.

Të gjitha makrot tona do të ruhen në të ashtuquajturat modulet e softuerit. Në ndonjë nga librat Versionet e Excel ne mund të krijojmë ndonjë modulet e softuerit në çdo sasi dhe vendosim në to të gjitha makrot që kemi krijuar. Një modul mund të përmbajë çdo numër makrosh që ju nevojiten ose krijoni. Modulet e disponueshme në dritare Project Explorer dhe janë të vendosura në këndin e sipërm të majtë të redaktorit makro (mund të thirret edhe me kombinimin e tastit CTRL+R).

Modulet e programit në redaktorin VBA ekzistojnë në disa forma dhe përdoren për të opsione të ndryshme dhe situatat:

Në fakt, vetë makroja, kur punon në një modul standard, duket kështu:

Konsideroni shembullin e një makro pune:

• Të gjitha makrot do të fillojnë domosdoshmërisht me operatorin Nën, e ndjekur nga emri i makros tuaj dhe një listë e argumenteve me kllapa. Në rastet kur nuk ka argumente, kllapat duhet të lihen bosh;
• Pa dështuar, të gjitha makrot përfundojnë me operatorin fundi nën;
• Të dhënat që janë ndërmjet operatorëve Nën Dhe fundi nën, është trupi i makros që do të ekzekutohet kur të ekzekutohet makroja. Në shembull, makro kontrollon dhe, pas hyrjes, e gjen atë në listën e bazës së të dhënave dhe , duke nxjerrë vlerën e specifikuar nga kriteret.

Siç mund ta shihni, mënyra e dytë është më e vështirë për t'u përdorur dhe kuptuar nëse nuk keni përvojë programimi në përgjithësi ose VBA në veçanti. Është shumë e vështirë të kuptosh dhe kuptosh se çfarë komandash dhe si futen ato, çfarë argumentesh përdor në mënyrë që makroja të fillojë të bëjë punën e saj automatikisht. Por rruga do të zotërohet nga ai që ecën, siç thoshin të urtët e lashtë, dhe për këtë arsye nuk duhet të dorëzoheni, por të ndiqni urdhërimet e gjyshit Lenin ...

Krijimi i një butoni për të ekzekutuar makro në shiritin e veglave

Siç thashë më herët, mund të telefononi një procedurë makro kombinim i nxehtëçelësat, por është shumë e lodhshme të kujtosh se cili kombinim kujt i është caktuar, kështu që është më mirë të krijosh një buton për të ekzekutuar makro. Butonat mund të krijohen të disa llojeve, përkatësisht:

Kjo metodë është e disponueshme për çdo version të MS Excel dhe konsiston në faktin se ne do ta sjellim butonin direkt në fletën tonë të punës si një objekt grafik. Për këtë ju duhet:

• Në MS Excel 2003 dhe më lart shkoni në menu "Pamje", zgjidhni "Toolbar" dhe shtypni butonin "Format".
• Në MS Excel 2007 dhe më të reja ju duhet në skedën "Zhvilluesi" hapni menunë rënëse "Fut" dhe zgjidhni objektin "Butoni".

Pas gjithë kësaj, duhet të vizatoni një buton në fletën tuaj duke mbajtur të shtypur butonin e majtë të miut. Pas përfundimit të procesit të vizatimit, automatikisht do të ndizet një dritare, ku do t'ju duhet të zgjidhni makro që duhet të ekzekutohet kur klikoni në butonin tuaj.

Si të krijoni funksione të personalizuara në VBA

Në parim, krijimi i të ashtuquajturave funksione të përcaktuara nga përdoruesi nuk është shumë i ndryshëm nga krijimi i një makro normale në një modul programi standard. Dallimi midis këtyre koncepteve është se makro do të kryejë veprimet e natyrshme në të me objektet e librit ose fletës (këto janë formula, qeliza, etj.), Por funksioni i përcaktuar nga përdoruesi funksionon vetëm me vlerat \u200që merr nga ne dhe janë të njëjtat argumente të dhëna fillestare për llogaritjet.

Për shembull, për të krijuar një funksion të personalizuar për tatimin mbi vlerën e shtuar, i njohur si TVSH, ne duhet të hapim redaktorin tonë VBA dhe të shtojmë modul i ri, zgjidhni nga menyja "Fut" paragraf modul dhe shkruani tekstin për funksionin tonë atje: Duhet të theksohet se ndryshimi kryesor midis një funksioni dhe një makro është kreu funksionin zëvendësohet Nën dhe ekziston një listë e plotë e argumenteve, në shembullin tonë është Përmbledhje. Pasi të futet kodi ynë, ai do të bëhet i disponueshëm në dritare standarde Funksioni Wizard, i cili ndodhet në meny "Formulat", paragraf "Fut funksionin".
Dhe zgjidhni një kategori "Perdorues i percaktuar" në të cilin do të shfaqet funksioni ynë i shkruar NDS.
Pasi të zgjidhni funksionin tonë, mund të vendosni kursorin në qelizë me argumentin, i cili do të përmbajë shumën për të cilën ne llogarisim TVSH-në, gjithçka ndodh, si me funksion normal.
Dhe kjo është gjithçka që kam! Unë vërtet shpresoj se artikull se si të krijoni një makro në Excel ishte e qartë dhe e dobishme për ju. Do të isha shumë mirënjohës për komentet e lëna, pasi ky është një tregues i lexueshmërisë dhe më frymëzon të shkruaj artikuj të rinj! Ndani me miqtë tuaj lexoni dhe pëlqeni!

Fillova ta ndërtoj këtë faqe si fletore në mundësi të përshtatshme Excel. Në çdo kohë, ju mund të rifreskoni njohuritë tuaja për një veçori të veçantë të programit duke shkuar në sit, plus mund t'u dërgoni një lidhje te artikulli miqve ose kolegëve që kërkojnë ndihmë me Excel. Audienca e faqes është rritur mjaft shpejt, dhe siç e shoh unë, informacioni i regjistruar është i dobishëm jo vetëm për mua dhe disa njerëz të tjerë, por edhe për shumë njerëz që nuk i njoh dhe vlerësohet shumë nga motorët e kërkimit. Nëse nga Karakteristikat e Excel, jam i sigurt për veten time, pastaj shumë specialist nivel të lartë Nuk mund ta përmend veten në VBA. Prandaj, unë do të doja të krijoj artikull i madh ndihmës, ku do të regjistroj makro të dobishme që i përdor shpesh. Jam i sigurt se një koleksion i tillë i veçorive VBA do të jetë i dobishëm jo vetëm për mua.

Faqja tashmë ka disa artikuj mbi VBA, duke përfshirë , por mendoj se do të ishte e dobishme të ruheshin në një vend locione kodesh që përdoren shpesh.

Fikni shfaqjen e veprimeve makro në ekran me një funksion të thjeshtë. Kjo jo vetëm që do të përmirësojë ekzekutimin vizualisht, por gjithashtu do ta bëjë makro të funksionojë shumë më shpejt.

Nën Makros1() Application.ScreenUpdating = 0 "Application Code.ScreenUpdating = 1 Fund Sub

Mos harroni të përfshini funksionin në fund të makros

Si të hiqni zgjedhjen e kopjeve pas ekzekutimit makro?

Nëse ndani një gamë për kopjimin dhe ngjitjen në makro. Mos harroni të çaktivizoni përzgjedhjen në fund të makros.

Nën Makros1() "Application Code.CutCopyMode = 0 Fund Sub

Makro të dobishme. Si të gjeni rreshtin ose kolonën e fundit të një diapazoni

Ky ndërtim do t'ju ndihmojë të gjeni lehtësisht numrin e rreshtit ose kolonës së fundit të diapazonit të mbushur. Është veçanërisht i përshtatshëm për t'u përdorur në cikle, nuk keni nevojë të vendosni 1000 rreshta të ciklit me një diferencë, vetë Excel do të gjejë se ku është fundi i diapazonit duke përdorur këtë ndërtim:

Sub makros1() Dim mLastRow Sa Long Dim nLastCol Sa Long mLastRow = Cells(Rows.Count, 1).End(xlUp).Row "Gjen numrin e rreshtit të fundit të një diapazoni të mbushur nLastCol= Cells(1, Columns.Count ).End(xlToLeft ).Column "Gjen numrin e kolones te mbushur perfundimisht te vargut End Sub

Për më tepër, unë e deklaroj menjëherë variablin si Long (gjatësia 2 147 483 647) në mënyrë që të mos futem në situatën kur numri i plotë popullor mund të mos jetë i mjaftueshëm (32 767) për tabela të mëdha.

Ju gjithashtu duhet t'i kushtoni vëmendje që kërkimi të bëhet në kolonën e parë ose rreshtin e parë, zgjidhni numrin e dëshiruar, për përcaktimin e saktë.

Për ciklin dhe gjendjen e kontrollit në lak

Nëse keni gjetur numrat e fundit varg rreshtave dhe kolonave, ju mund t'i përdorni ato në sythe. Për shembull, duhet të kaloni nëpër kolonën e parë dhe të numëroni numrin e qelizave boshe.

Nën makros1() Application.ScreenUpdating = 0 Dim mRastia e Fundit për aq kohë sa Dim Kol Sa Long "Numëroni variablin Dim i për aq kohë "Loop Variable mLastRow = Cells(Rows.Count, 1).End(xlUp).Rreshti Kol = 0 Për i = 1 Tek mRreshti i Fundit Nëse Cells(i, 1).Vlera = "" Pastaj Kol = Kol + 1 Fundi Nëse Next i MsgBox Kol Application.ScreenUpdating = 1 Fund Sub

Msgbox përdoret gjithashtu këtu; me këtë veçori, ju mund të shfaqni të dhënat në një dritare të veçantë. Për shembullin tim do të duket kështu:

Numërimi i kohës së ekzekutimit të makrove

Nën makros1() TimeStart = Tani TimeFinish = Tani MsgBox "Time: " & Format(TimeFinish - TimeStart, "h:mm:ss") Fund Sub

MsgBox jep daljen e mëposhtme:

Është shumë i përshtatshëm për të gjurmuar kohën e ekzekutimit të makrove për optimizimin e mëvonshëm.

Këto janë makro të dobishme që i përdor shumë shpesh. Unë gradualisht do ta zgjeroj këtë listë. Gjithë automatizimi efektiv!

Ndani artikullin tonë në rrjetet tuaja sociale:

Shembujt e mëposhtëm të thjeshtë makro të Excel ilustrojnë disa nga veçoritë dhe teknikat e përshkruara në tutorialin Excel VBA.

Makro Excel Shembull 1

Fillimisht, kjo procedurë Nënështë dhënë si shembull i përdorimit të komenteve në kodin VBA. Sidoqoftë, këtu mund të shihni gjithashtu se si deklarohen variablat, si referohen qelizat Excel, duke përdorur një lak Për, operator i kushtëzuar Nëse dhe shfaqja e një kutie mesazhesh.

"Procedura Sub kërkon një qelizë që përmban vargun e dhënë "në rangun e qelizave A1:A100 të fletës aktive të punës Sub Find_String(sFindText As String) Dim i As Integer "Një numër i plotë i llojit Integer, i përdorur në një cikli For Dim iRowNumber Si Integer "Një numër i plotë i llojit Integer për ruajtjen e rezultatit iRowNumber = 0 "Shikon nëpër qelizat A1:A100 një nga një derisa të gjendet vargu sFindText For i = 1 To 100 If Cells(i, 1).Value = sFindText Then " Nëse gjendet një përputhje me vargun e dhënë "ruani numrin linjë aktuale dhe dalje Për lak iRowNumber = i Dil Për Fund Nëse Next i "Ne i tregojmë përdoruesit në një dritare që shfaqet nëse vargu i kërkuar është gjetur "Nëse gjendet vargu i dhënë, tregoni në cilën qelizë gjendet përputhja Nëse iRowNumber = 0 Pastaj MsgBox "String " & sFindText & " nuk u gjet" Tjetër MsgBox " String " & sFindText & " gjenden në qelizën A" & iRowNumber Fund If End Sub

Shembulli i makro Excel 2

Procedura e radhës Nën- një shembull i përdorimit të një lak Bej Nderkohe. Këtu mund të shihni gjithashtu se si deklarohen variablat, duke punuar me referencat e qelizave Excel dhe duke aplikuar një operator të kushtëzuar. Nëse.

"Nënprocedura nxjerr numra Fibonacci që nuk i kalojnë 1000 Sub Fibonacci() Dim i Si numër i plotë "Numërues për të treguar pozicionin e elementit në sekuencë Dim iFib si numër i plotë "Ruan vlerën aktuale të sekuencës Dim iFib_Next si numër i plotë "Ruan vlerën tjetër të sekuencës Dim iStep si numër i plotë " Ruan madhësinë e rritjes së ardhshme "Inicializimi i variablave i dhe iFib_Next i = 1 iFib_Next = 0 "Cakulli Do while do të ekzekutohet derisa vlera e "numrit aktual Fibonacci" të kalojë 1000 Do Ndërsa iFib_Next< 1000 If i = 1 Then "Një rast i veçantë për elementin e parë të sekuencës iStep = 1 iFib = 0 Përndryshe "Ruaj madhësinë e rritjes tjetër përpara se të mbishkruash "vlerën aktuale të sekuencës iStep = iFib iFib = iFib_Next Fundi Nëse "Shfaq numrin aktual të Fibonacci në kolonën A të fletë pune aktive "në rresht me indeksin i Cells(i, 1).Vlera = iFib "Llogaritni numrin tjetër të Fibonaccit dhe rritni indeksin e pozicionit të elementit me 1 iFib_Next = iFib + iStep i = i + 1 Loop Fund Sub

Shembulli i makro Excel 3

Kjo procedurë Nën skanon qelizat e një kolone A fletë aktive derisa të ndeshet me një qelizë boshe. Vlerat shkruhen në një grup. Kjo makro e thjeshtë Excel tregon se si të punohet me vargje dinamike dhe si të përdoret një lak. Bëni Deri. NË ky shembull ne nuk do të kryejmë asnjë veprim me grupin, megjithëse në praktikën reale të programimit, pasi të dhënat janë shkruar në grup, veprime të tilla zakonisht kryhen mbi to.

"Procedura Sub ruan vlerat e qelizave të kolonës A të fletës aktive në grupin Sub GetCellValues() Dim iRow As Integer "Ruaj numrin e rreshtit aktual Dim dCellValues() Si Double "Një grup për ruajtjen e vlerave të qelizave ​iRow = 1 ReDim dCellValues(1 deri në 10) "Bëj loop derisa të kalojë nëpër qelizat e kolonës A të fletës aktive" dhe nxjerr vlerat e tyre në një grup derisa të takohet qelizë boshe Vepro deri në IsEmpty(Cells(iRow, 1)) "Kontrollo që grupi dCellValues ​​është mjaft i madh" Nëse jo, rrite madhësinë e grupit me 10 duke përdorur 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

Shembulli i makro Excel 4

Në këtë shembull, procedura Nën lexon vlerat nga një kolonë A fletë pune Fleta 2 dhe performon me ta veprimet aritmetike. Rezultatet futen në qelizat e kolonës A në fletën aktive të punës. Kjo makro demonstron përdorimin e objekteve të Excel. Në veçanti, përdoret procedura Nën tek objekti kolonat, dhe tregon se si arrihet ky objekt përmes objektit Fletë pune. Tregohet gjithashtu se kur i referohemi një qelize ose një gamë qelizash në fletën aktive, nuk është e nevojshme të specifikoni emrin e kësaj flete kur shkruani një lidhje.

"Procedura Sub kalon nëpër vlerat në kolonën A të fletës së punës Sheet2", kryen operacione aritmetike për secilën vlerë dhe e shkruan rezultatin në kolonën A të fletës aktive të punës (Sheet1) Sub Transfer_ColA() Dim i As Numri i plotë Dim Col As Range Dim dVal As Double "Cakto variablit Col kolona A e fletës së punës Fleta 2 Set Col = Sheets("Sheet2").Columns("A") i = 1 "Duke përdorur një lak, ne lexojmë vlerat e qelizave Kolona kolone derisa të haset një qelizë e zbrazët Do Deri IsEmpty(Col.Cells(i)) "Kryerni veprime aritmetike mbi vlerën e qelizës aktuale dVal = Col.Cells(i).Vlera * 3 - 1 "Komanda e mëposhtme shkruan rezultatin në kolonën A të fletës aktive të punës "Nuk ka nevojë të përfshihet emri i fletës në referencë, pasi është fleta aktive e punës. Cells(i, 1) = dVal i = i + 1 Fundi i ciklit Nën

Shembulli i makro Excel 5

Kjo makro tregon një shembull të kodit VBA që dëgjon për një ngjarje Excel. Ngjarja me të cilën lidhet makro ndodh sa herë që zgjidhet një qelizë ose një varg qelizash në fletën e punës. Në rastin tonë, kur zgjedhim një qelizë B1, do të shfaqet një kuti mesazhi.

"Kodi i dhënë tregon një kuti mesazhi nëse qeliza B1 është zgjedhur në fletën aktuale të punës. Nën-Private Worksheet_SelectionChange(ByVal Target As Range) "Kontrollo nëse qeliza B1 është zgjedhur nëse Target.Count = 1 And Target.Row = 1 And Target.Column = 2 Pastaj "Nëse zgjidhet qeliza B1, kryeni veprimin e nevojshëm MsgBox "Ju keni zgjedhur qelizën B1" Fund If End Sub

Shembulli i makro Excel 6

Një shembull i kësaj procedure tregon përdorimin e operatorëve Në Gabim Dhe Përmbledhje për trajtimin e gabimeve. Ky kod tregon gjithashtu një shembull të hapjes dhe leximit të të dhënave nga një skedar.

"Procedura Sub vendos vlerat e qelizave A1 dhe B1 në argumentet Val1 dhe Val2 "nga libri i punës Data.xlsx i vendosur në dosjen C:\Documents and Settings Sub Set_Values(Val1 As Double, Val2 As Double) Dim DataWorkbook Si fletore pune mbi gabimin GoTo Error Handling" Ne hapim fletore pune me të dhëna Set DataWorkbook = Workbooks.Open("C:\Documents and Settings\Data") "Cakto variablave Val1 dhe Val2 vlerat nga libri i caktuar i punës Val1 = Sheets("Sheet1"). Cells(1, 1) Val2 = Sheets("Sheet1").Cells(1, 2) DataWorkbook.Close Exit Sub Error Handling: "Nëse skedari nuk gjendet, përdoruesit do t'i kërkohet të vendosë skedarin e kërkuar në dosjen e dëshiruar dhe pas kësaj vazhdoni të ekzekutoni makro MsgBox "Skedari Data.xlsx nuk u gjet!" & _ "Ju lutemi shtoni librin e punës në dosjen C:\Documents and Settings dhe klikoni OK" Resume Fund Sub

Excel ka një aftësi të fuqishme, por në të njëjtën kohë shumë rrallë të përdorur, për të krijuar sekuenca automatike të veprimeve duke përdorur makro. Një makro është një rrugëdalje ideale nëse keni të bëni me të njëjtin lloj detyre që përsëritet shumë herë. Për shembull, përpunimi i të dhënave ose formatimi i dokumentit sipas një modeli të standardizuar. Në këtë rast, nuk keni nevojë për njohuri të gjuhëve të programimit.

A jeni tashmë kurioz se çfarë është një makro dhe si funksionon? Pastaj me guxim ecni përpara - atëherë ne do të bëjmë hap pas hapi të gjithë procesin e krijimit të një makro me ju.

Çfarë është Macro?

Macro në Microsoft Office (po, ky funksion funksionon njësoj në shumë aplikacione Paketa Microsoft zyra) është kod programimi në një gjuhë programimi (VBA) të ruajtur brenda një dokumenti. Për ta bërë më të qartë, një dokument i Microsoft Office mund të krahasohet me Faqe HTML, atëherë makro është analoge me Javascript. Çfarë mund të bëjë Javascript me të dhënat në Formati HTML në një faqe interneti është shumë e ngjashme me atë që një makro mund të bëjë me të dhënat në Dokumenti i Microsoft zyrë.

Makrot mund të bëjnë pothuajse gjithçka që dëshironi në një dokument. Këtu janë disa prej tyre (një pjesë shumë e vogël):

• Aplikoni stilet dhe formatimin.
• Kryen veprime të ndryshme me të dhëna numerike dhe tekstuale.
• Përdorni burime të jashtme të të dhënave (skedarët e bazës së të dhënave, dokumente tekstuale etj.)
• Krijo një dokument të ri.
• Bëni të gjitha sa më sipër në çdo kombinim.

Krijimi i një makro - një shembull praktik

Për shembull, le të marrim skedarin më të zakonshëm csv. Kjo tabelë e thjeshtë 10x20 e mbushur me numra nga 0 deri në 100 me titujt për kolonat dhe rreshtat. Detyra jonë është ta kthejmë këtë grup të dhënash në një tabelë të formatuar aktualisht dhe të gjenerojmë totale në çdo rresht.

Siç është përmendur tashmë, një makro është kodi i shkruar në gjuhën e programimit VBA. Por në Excel, ju mund të krijoni një program pa shkruar një rresht kodi, të cilin do ta bëjmë tani.

Për të krijuar një makro, hapeni pamje(Shiko) > Makrot(Makro) > Regjistro makro(Regjistrimi makro...)

Jepni makro një emër (pa hapësira) dhe klikoni Ne rregull.

Duke filluar nga ky moment, TË GJITHA veprimet tuaja me dokumentin regjistrohen: ndryshimet në qeliza, lëvizja nëpër tabelë, madje edhe ndryshimi i madhësisë së dritares.

Excel sinjalizon se modaliteti i regjistrimit makro është aktivizuar në dy vende. Së pari, në menu Makrot(Macros) - në vend të një vargu Regjistro makro(Duke regjistruar një makro...) u shfaq rreshti Ndalo regjistrimin(Ndalo regjistrimin).

Së dyti, në këndin e poshtëm të majtë Windows Excel. Ikona Ndalo(katror i vogël) tregon që regjistrimi makro është aktivizuar. Klikimi mbi të do të ndalojë regjistrimin. Në të kundërt, kur modaliteti i regjistrimit nuk është i aktivizuar, ekziston një ikonë për të aktivizuar regjistrimin makro në këtë vendndodhje. Klikimi mbi të do të japë të njëjtin rezultat si aktivizimi i regjistrimit përmes menysë.

Tani që modaliteti i regjistrimit makro është aktivizuar, le të kalojmë te detyra jonë. Para së gjithash, le të shtojmë titujt për të dhënat përmbledhëse.

• =SUM(B2:K2) ose =SUM(B2:K2)
• =MESATARE (B2:K2) ose =MESATARE (B2:K2)
• =MIN(B2:K2) ose =MIN(B2:K2)
• =MAX(B2:K2) ose =MAX(B2:K2)
• =MEDIAN (B2:K2) ose =MEDIAN (B2:K2)

Tani zgjidhni qelizat me formula dhe kopjoni ato në të gjitha rreshtat e tabelës sonë duke zvarritur dorezën e plotësimit automatik.

Pasi të keni përfunduar këtë hap, çdo rresht duhet të ketë totalet përkatëse.

Përkatësisht:

• =SUM(L2:L21) ose =SUM(L2:L21)
• =MESATARE (B2:K21) ose =MESATARE (B2:K21)- për të llogaritur këtë vlerë, është e nevojshme të merren saktësisht të dhënat fillestare të tabelës. Nëse merrni mesataren e mesatareve për rreshtat individualë, rezultati do të jetë i ndryshëm.
• =MIN(N2:N21) ose =MIN(N2:N21)
• =MAX(O2:O21) ose =MAX(O2:O21)
• =MEDIAN (B2:K21) ose =MEDIAN (B2:K21)- ne konsiderojmë përdorimin e të dhënave fillestare të tabelës, për arsyen e treguar më sipër.

Tani që kemi mbaruar me llogaritjet, le të bëjmë disa formatim. Së pari, le të vendosim të njëjtin format të shfaqjes së të dhënave për të gjitha qelizat. Zgjidhni të gjitha qelizat në fletë, për ta bërë këtë, përdorni shkurtoren e tastierës Ctrl+A ose klikoni në ikonën Selektoj të gjitha, i cili ndodhet në kryqëzimin e titujve të rreshtave dhe kolonave. Pastaj klikoni Stili i presjes Skeda (Format i kufizuar). Shtëpi(Shtëpi).

• Stili i shkronjave të theksuara.
• Shtrirja në qendër.
• Mbushje me ngjyra.

Dhe së fundi, le të vendosim formatin e totaleve.

Kështu duhet të duket në fund:

Nëse gjithçka ju përshtatet, ndaloni regjistrimin e makro.

urime! Sapo keni regjistruar makronë tuaj të parë në Excel vetë.

Për të përdorur makronë e krijuar, duhet të kursejmë dokument Excel në një format që mbështet makro. Së pari, duhet të fshijmë të gjitha të dhënat nga tabela që kemi krijuar, d.m.th. bëje atë shabllon bosh. Fakti është se në të ardhmen, duke punuar me këtë shabllon, ne do të importojmë të dhënat më të fundit dhe më të rëndësishme në të.

Për të pastruar të gjitha qelizat e të dhënave, klikoni klikoni me të djathtën miu për ikonë Selektoj të gjitha, i cili ndodhet në kryqëzimin e titujve të rreshtave dhe kolonave, dhe nga menyja e kontekstit zgjidhni artikullin Fshije(Fshi).

Tani fleta jonë është pastruar plotësisht nga të gjitha të dhënat, ndërsa makroja mbetet e regjistruar. Duhet ta ruajmë librin model excel makro-aktivizuar, e cila ka shtrirjen XLTM.

Pika e rëndësishme! Nëse e ruani skedarin me shtesën XLTX, atëherë makro nuk do të funksionojë në të. Nga rruga, ju mund ta ruani librin e punës si një shabllon Excel 97-2003, i cili ka formatin XLT, ai gjithashtu mbështet makro.

Kur shablloni ruhet, mund ta mbyllni me siguri Excel.

Drejtimi i një makro në Excel

Para se të zbuloj të gjitha mundësitë e makros që keni krijuar, e konsideroj të drejtë t'i kushtoj vëmendje disa pika të rëndësishme në lidhje me makrot në përgjithësi:

• Makrot mund të jenë të dëmshme.
• Lexoni përsëri paragrafin e mëparshëm.

Kodi VBA është shumë mundësi të mëdha. Në veçanti, ai mund të kryejë operacione në skedarë jashtë dokument aktual. Për shembull, një makro mund të fshijë ose modifikojë çdo skedar në një dosje Dokumentet e mia. Për këtë arsye, ekzekutoni dhe lejoni vetëm makro nga burimet që ju besoni.

Për të ekzekutuar makronë tonë të formatimit të të dhënave, hapni skedarin e shabllonit që krijuam në pjesën e parë të këtij tutoriali. Nëse keni cilësimet standarde sigurisë, atëherë kur hapni një skedar, mbi tabelë do të shfaqet një paralajmërim që makro-të janë çaktivizuar dhe një buton për t'i aktivizuar ato. Meqenëse shabllonin e bëmë vetë dhe i besojmë vetes, shtypim butonin Aktivizo përmbajtjen(Përfshi përmbajtjen).

Hapi tjetër është të importoni të dhënat më të fundit të përditësuara nga skedari csv(bazuar në një skedar të tillë, ne krijuam makro tonë).

Kur importoni të dhëna nga një skedar CSV, Excel mund t'ju kërkojë të konfiguroni disa opsione në mënyrë që të transferoni saktë të dhënat në tabelë.

Kur të përfundojë importimi, shkoni te menyja Makrot Skeda (Makro). pamje(Shiko) dhe zgjidhni një komandë Shikoni makro(Makro).

Në kutinë e dialogut që hapet, do të shohim një rresht me emrin e makros tonë Të Dhënat e Formatit. Zgjidhni atë dhe klikoni Vraponi(Ekzekutoni).

Kur makroja fillon të funksionojë, do të shihni kursorin e tabelës duke kërcyer nga qeliza në qelizë. Pas disa sekondash, me të dhënat do të kryhen të njëjtat veprime si gjatë regjistrimit të një makro. Kur gjithçka të jetë gati, tabela duhet të duket e njëjtë me origjinalin që kemi formatuar me dorë, vetëm me të dhëna të ndryshme në qeliza.

Le të shohim nën kapuç: Si funksionon një makro?

Siç u përmend më shumë se një herë, një makro është kod programi në një gjuhë programimi. Visual Basic për aplikacione(VBA). Kur aktivizoni modalitetin e regjistrimit makro, Excel në fakt regjistron çdo veprim që bëni në formën e udhëzimeve VBA. E thënë thjesht, Excel shkruan kodin për ju.

Për të parë këtë kod programi, ju duhet në meny Makrot Skeda (Makro). pamje(shikoni) klikoni Shikoni makro(Macros) dhe në kutinë e dialogut që hapet, klikoni Redakto(Ndrysho).

Do të hapet një dritare Visual Basic për aplikacione, në të cilën do të shohim kodin e programit të makros që kemi regjistruar. Po, e keni kuptuar saktë, këtu mund ta ndryshoni këtë kod dhe madje të krijoni një makro të re. Veprimet që kemi kryer me tabelën në këtë mësim mund të shkruhen duke përdorur regjistrim automatik makro në Excel. Por makrot më komplekse, me sekuencë të akorduar mirë dhe logjikë veprimi, kërkojnë programim manual.

Le t'i shtojmë edhe një hap detyrës sonë...

Imagjinoni që tonën skedar origjinal me të dhëna të dhëna.csv krijohet automatikisht nga ndonjë proces dhe ruhet gjithmonë në disk në të njëjtin vend. Për shembull, C:\Data\data.csv– rruga drejt skedarit me të dhëna të përditësuara. Procesi i hapjes së këtij skedari dhe i importimit të të dhënave prej tij mund të regjistrohet gjithashtu në një makro:

1. Hapni skedarin e shabllonit ku kemi ruajtur makro − Të Dhënat e Formatit.
2. Krijo një makro të re me emrin ngarkoni të dhënat.
3. Gjatë regjistrimit të një makro ngarkoni të dhënat importoni të dhëna nga skedari të dhëna.csv- siç bëmë në pjesën e mëparshme të mësimit.
4. Kur të përfundojë importimi, ndaloni regjistrimin e makros.
5. Fshini të gjitha të dhënat nga qelizat.
6. Ruani skedarin si një shabllon Excel i aktivizuar me makro (extension XLTM).

Kështu, duke ekzekutuar këtë shabllon, ju keni akses në dy makro - njëra ngarkon të dhënat, tjetra i formaton ato.

Nëse dëshironi të futeni në programim, mund të kombinoni veprimet e këtyre dy makrove në një - thjesht duke kopjuar kodin nga ngarkoni të dhënat deri në fillim të kodit Të Dhënat e Formatit.

Për të automatizuar detyrat e përsëritura, mund të regjistroni një makro duke përdorur makroregjistruesin në Microsoft Excel. Imagjinoni që keni data në formate të rastësishme dhe dëshironi të aplikoni të njëjtin format për të gjithë. Kjo mund të bëhet me një makro. Mund të regjistroni një makro duke aplikuar formatin e dëshiruar, dhe opsionalisht luani makro.

Kur regjistroni një makro, të gjitha veprimet e nevojshme janë shkruar si kod Visual Basic për Aplikacione (VBA). Këto veprime mund të përfshijnë futjen e tekstit ose numrave, zgjedhjen e qelizave ose komandave në shirit ose meny, formatimin e qelizave, rreshtave ose kolonave, madje edhe importimin e të dhënave nga një burim i jashtëm si p.sh. Microsoft Access. Visual Basic for Applications (VBA) është pjesë e gjuhës Programim vizual bazë. Është i disponueshëm në shumicën e aplikacioneve të Office. Ndërsa VBA ju lejon të automatizoni proceset brenda dhe ndërmjet aplikacioneve të Office, nuk keni nevojë të dini se si të programoni ose dini gjuhën VBA, pasi makroregjistruesi do të kujdeset për gjithçka.

Është e rëndësishme të dini se kur regjistroni një makro, pothuajse gjithçka që bëni regjistrohet. Pra, nëse bëni një gabim, si për shembull shtypja e butonit të gabuar, makroregjistruesi do ta regjistrojë atë veprim. Në një rast të tillë, ju mund të rishkruani të gjithë sekuencën ose të modifikoni kodin VBA. Prandaj, përpara se të regjistroni procesin, duhet ta përpunoni mirë. Sa më saktë të shkruani sekuencën, aq më efektivisht do të funksionojë makroja.

Zhvilluesi, e cila është e fshehur si parazgjedhje, kështu që ju duhet ta aktivizoni atë së pari. Për më shumë informacion, shihni Shfaq skedën e Zhvilluesit.

Regjistrimi makro

Në skedën Zhvilluesi klikoni Makrot për të parë makrot e lidhura me librin e punës. Ju gjithashtu mund të shtypni tastet ALT + F8. Kjo do të hapë një kuti dialogu. Makro.

Kujdes:

		Mësoni rreth cilësimeve makro të sigurisë dhe kuptimit të tyre.
	Makrot mund të ekzekutohen menyra te ndryshme, për shembull duke përdorur një shkurtore të tastierës, objekt grafik, panele akses i shpejtë, butonin, apo edhe kur hapni një libër.
	Mund të përdorni redaktuesin Visual Basic për të modifikuar makrot që janë bashkangjitur në një libër pune.
	caktoni një makro. Në fushë Cakto makro
	Mësoni se si të aktivizoni dhe çaktivizoni makro në skedarët e Office.
	Shtypni tastet ALT + F11.

Puna me kodin e shkruar në Visual Basic Editor (VBE)

Me Redaktuesin Visual Basic (VBE), mund të shtoni variablat tuaja, strukturat e kontrollit dhe elementë të tjerë në kodin e regjistruar që makroregjistruesi nuk i mbështet. Meqenëse makroregjistruesi kap pothuajse çdo hap që ndodh gjatë regjistrimit, mund t'ju duhet gjithashtu të hiqni kodin e panevojshëm. Shiko kodin e regjistruar - mënyrë e shkëlqyer mësoni se si të programoni në VBA ose të përmirësoni aftësitë tuaja.

Një shembull i modifikimit të kodit të regjistruar mund të gjendet në artikullin Fillimi me VBA në Excel.

Regjistrimi makro

Para se të regjistroni makro, është e dobishme të dini sa vijon:

Macro e regjistruar për të punuar Gama e Excel, do të ekzekutohet vetëm për qelizat në këtë varg. Pra, nëse shtoni në varg linjë e re, makro nuk do të zbatohet për të.

Nëse keni nevojë të regjistroni një sekuencë të gjatë detyrash, ju rekomandojmë të përdorni disa makro më të vogla.

Një makro mund të përmbajë gjithashtu detyra jo-Excel. Procesi makro mund të mbulojë të tjera Aplikacionet e zyrës dhe programe të tjera që mbështesin Visual Basic për Aplikacione (VBA). Për shembull, mund të regjistroni një makro që së pari përditëson një spreadsheet në Excel dhe më pas hap Outlook për t'i dërguar email.

Mjetet makro dhe VBA janë në skedë Zhvilluesi, e cila është e fshehur si parazgjedhje, kështu që ju duhet ta aktivizoni atë së pari.

Zgjidhni shkëlqejnë > Parametrat> Shirit dhe panel.

Për të regjistruar një makro, ndiqni udhëzimet e mëposhtme.

Puna me makro të regjistruara në Excel

Në skedën Zhvilluesi klikoni Makrot për të parë makrot e lidhura me librin e punës. Kjo do të hapë një kuti dialogu. Makro.

Shënim: Makrot nuk mund të zhbëhen. Përpara se të ekzekutoni një makro të regjistruar për herë të parë, ruani ose bëni një kopje të librit të punës për ta parandaluar ndryshime të padëshiruara. Nëse nuk jeni të kënaqur me rezultatet e ekzekutimit të një makro, mund ta mbyllni librin e punës pa e ruajtur atë.

Më poshtë janë informacion shtese rreth punës me makro në Excel.

		Mësoni se si të aktivizoni dhe çaktivizoni makrot në Excel për Mac.
	Për të kursyer kohë në kryerjen e detyrave të përsëritura shpesh, mund të regjistroni sekuencën përkatëse të veprimeve në formën e një makro. Mësoni se si të krijoni dhe ekzekutoni makro.
	Nëse një libër pune përmban një makro VBA që duhet të përdoret diku tjetër, ky modul mund të kopjohet në një libër tjetër pune duke përdorur Redaktori i Microsoft Bazë vizuale.
Caktimi i një makro për një objekt, formë ose element grafik	Në fletë, klikoni me të djathtën mbi objektin, imazhin, formën ose elementin të cilit dëshironi t'i caktoni një makro ekzistuese dhe më pas klikoni caktoni një makro. Në fushë Cakto makro zgjidhni makro që dëshironi të caktoni.
	Mund të caktoni një makro në një ikonë dhe ta shtoni atë në shiritin e veglave ose shiritin e aksesit të shpejtë.
	Ju mund të caktoni makro në forma dhe Kontrollet ActiveX në fletë.
Hapja e redaktorit të Visual Basic	Në skedën Zhvilluesi klikoni Bazë vizuale ose zgjidhni Shërbimi > Makro > Redaktori i Visual Basic.
	Mësoni si të gjeni ndihmë për elementët e Visual Basic.

informacion shtese

Ju gjithmonë mund t'i bëni një pyetje komunitetit të Excel Tech, të kërkoni ndihmë në komunitetin e Përgjigjeve dhe gjithashtu të sugjeroni veçori e re ose përmirësim në faqen e internetit