Makro programmēšana programmā Excel: pilnīga soli pa solim sniegta rokasgrāmata

Informatec Digital » programmēšana » Makro programmēšana programmā Excel: pilnīga soli pa solim sniegta rokasgrāmata

Ja programmā Excel atkārtoti veicat vienus un tos pašus uzdevumus, makro ir jūsu labākie draugi laika taupīšanā. Ar to palīdzību varat automatizēt veselas darbību secības (uzziniet vairāk par VBA programmēšanu ), sākot no formatēšanas līdz datu importēšanai vai aprēķinu veikšanai, un aktivizēt tās jebkurā laikā ar klikšķi vai īsinājumtaustiņu.

Veidojot makro, programma Excel reģistrē jūsu klikšķus un taustiņsitienus un tos precīzi atkārtoti izpilda. Pēc tam varat pielāgot kodu, lai precizētu izpildi un konsultētos ar Excel resursiem . Parasti sāk ar makro ierakstītāju un pēc tam to pilnveido ar VBA, panākot lielāku kontroli, elastību un nulles kļūdas neuzmanības dēļ.

Kas īsti ir makro programmā Excel?

Makro ir instrukciju saraksts, ko programma Excel izpilda noteiktā secībā . Tas var būt tik vienkāršs kā procentuālā stila lietošana vai tik sarežģīts kā atskaites sagatavošana ar rakurstabulām , tās formatēšana, darblapu izveide un e-pasta nosūtīšana programmā Outlook. Makro tiek saglabāti jūsu darbgrāmatā un tos var palaist jebkurā laikā, kad tas nepieciešams.

Lai sāktu, nav jāzina, kā programmēt: ierakstītājs pārvērš jūsu darbības VBA kodā. Pat ja tā, dažas VBA zināšanas ļaus jums izveidot pielāgotas funkcijas, formas un pat mini lietojumprogrammas programmā Excel.

Kāpēc izmantot makro? Reālas priekšrocības

Makro galvenais mērķis ir izvairīties no atkārtotiem uzdevumiem un samazināt kļūdas . Ja katru mēnesi sagatavosiet vienu un to pašu pārskatu, makro ietaupīs jūs no mehāniskām darbībām un ļaus jums koncentrēties uz svarīgāko.

Tā priekšrocības ietver laika ietaupījumu, konsekventu izpildi (bez izlaistām darbībām) un iespēju izveidot saīsnes un pogas, lai paātrinātu procesus . Ja programmu Excel lietojat tikai reizēm, tas, iespējams, nav tā vērts; bet, ja to lietojat katru dienu, atšķirību pamanīsiet jau no pirmās dienas.

Iespējot izstrādātāja cilni (Windows un Mac)

Pēc noklusējuma cilne Izstrādātājs ir paslēpta. Operētājsistēmā Windows ar peles labo pogu noklikšķiniet uz lentes un atlasiet “Pielāgot lenti”. Atzīmējiet “Izstrādātājs” un noklikšķiniet uz Labi. Pēc tam blakus sadaļai Skats redzēsiet cilni Izstrādātājs.

Mac datorā atveriet Excel > Preferences > Rīkjoslas un lente. Sadaļā “Galvenās cilnes” atlasiet “Izstrādātājs” un saglabājiet. Tas dos jums piekļuvi ierakstītājam, VBA un vadīklām . Ja esat jauns Excel lietotājs, pārskatiet pamata Excel rokasgrāmatu , lai iepazītos ar lenti un tās opcijām.

Kā izveidot makro, izmantojot ierakstītāju

Diktofons ir lieliski piemērots darba sākšanai. Sadaļā Izstrādātājs > Ierakstīt makro (vai operētājsistēmā Windows nospiediet taustiņu kombināciju Alt+T+M+R) piešķiriet aprakstošu nosaukumu. Atcerieties: nosaukumam jāsākas ar burtu, bez atstarpēm vai simboliem.

Izvēlieties saglabāšanas vietu: "Šī darbgrāmata" darbosies vairumā gadījumu. Ja vēlaties, lai tā vienmēr būtu pieejama, atlasiet "Personīgā makro darbgrāmata", un Excel to izveidos/saglabās failā Personal.xlsb , kas programmā Excel tiek atvērts paslēpts.

Pēc izvēles varat pievienot saīsni. Lai izvairītos no konfliktiem (piemēram, Ctrl+Z pazaudēšanas), ieteicams izmantot saīsnes ar Ctrl+Shift. Varat arī uzrakstīt īsu, skaidru aprakstu (ļoti noderīgi, ja ir daudz makro).

Noklikšķiniet uz Labi un veiciet darbības, kas jāreģistrē. Piemēram: atlasiet A1 un lietojiet formātu "Procenti". Izvairieties no nevajadzīgiem klikšķiem vai atlases izmaiņām, kas nav paredzētajā procesā, jo ierakstītājs ieraksta gandrīz visu.

Kad esat pabeidzis, pārtrauciet ierakstīšanu. Lai to palaistu, dodieties uz Izstrādātājs > Makro (vai nospiediet taustiņu kombināciju Alt+F8), atlasiet makro un noklikšķiniet uz Izpildīt. Jūs redzēsiet, kā Excel atkārtos darbības tieši tādā pašā secībā.

Makro palaišana, rediģēšana un pārvaldība

Sadaļā Izstrādātājs > Makro (Alt+F8) varat palaist, rediģēt vai dzēst makro. Ja izvēlēsities Rediģēt, tiks atvērts Visual Basic redaktors ar ierakstīto kodu, kuru varat iztīrīt un optimizēt (tajā bieži ir iekļautas liekas darbības).

Makro piešķiršana pogai vai formai ir ļoti ērta: ievietojiet formu, ar peles labo pogu noklikšķiniet > Piešķirt makro, izvēlieties makro un viss ir izdarīts. Noklikšķinot uz formas, tā tiks palaista nekavējoties.

Lai tā vienmēr būtu viegli pieejama ātrās piekļuves rīkjoslā, atveriet rīkjoslas izvēlni (lejupvērstā bultiņa) > Vairāk komandu > sadaļā "Komandas pieejamas" izvēlieties "Makro", pievienojiet savas un, ja vēlaties, nomainiet ikonu. Tagad jums būs pastāvīga poga pat tad, ja neesat izstrādātāja režīmā.

Ja makro ir atkārtoti jāizmanto citā darbgrāmatā, moduli var kopēt no Visual Basic redaktora. Atveriet VBE (Alt+F11), velciet moduli uz citu atvērto darbgrāmatu vai izmantojiet .bas faila eksportēšanas/importēšanas funkciju.

Makro programmēšana programmā Excel, izmantojot Visual Basic (VBA) redaktoru

Lai pārietu uz ierakstītāja režīmiem, atveriet redaktoru, nospiežot taustiņu kombināciju Alt+F11. Ievietojiet moduli (Insert > Module) un izveidojiet savu pirmo apakšprogrammu. Pamata modelis ir Sub Name() … End Sub.

Piemēram, lai parādītu ziņojumu un ierakstītu vērtību šūnā A1, varat sākt ar kaut ko vienkāršu. Tādā veidā jūs sapratīsiet struktūru un to, kā izsaukt makro no Excel (Alt+F8) vai piešķirt to pogai, izmantojot ļoti maz koda rindiņu :

Sub Primera_Macro()
    Range("A1").Value = "Esto de las macros mola."
    MsgBox "Hola, querido amigo."
End Sub

Vēl viens ātrs piemērs, kā lietot procentuālo stilu pašreizējai atlasei. Šis makro atkārto to, ko darījāt ar ierakstītāju , bet ierakstījāt tieši:

Sub FormatoPorcentaje()
    Selection.Style = "Percent"
End Sub

Ideāla kombinācija ir šāda: ierakstīt, lai paātrinātu darbību, un pēc tam atvērt VBE, lai atbrīvotos no nevajadzīgām darbībām, pievienotu mainīgos, nosacījumus vai ciklus. Tādā veidā jūs pārvēršat "burtisku" ierakstu par stabilu un atkārtoti lietojamu makro.

Absolūtās un relatīvās atsauces

Pēc noklusējuma ierakstītājs izveido makro absolūtajā režīmā: tas vienmēr iedarbojas uz vienām un tām pašām šūnām (piemēram, B3) neatkarīgi no tā, kur atrodaties tā palaišanas laikā. Ja dati maina pozīciju jūsu procesā, iespējams, ka tas nav tas, kas jums nepieciešams.

Izmantojot opciju "Lietot relatīvās atsauces", makro darbojas attiecībā pret aktīvo šūnu, kad sākas izpilde. Piemēram, ja sākāt ierakstīšanu šūnā B3 un ierakstījāt "Vārds", "Uzvārds" utt., pārvietojoties pa labi, palaižot no šūnas C4, tā ierakstīs to pašu informāciju attiecībā pret šūnām C4, D4, E4 utt.

Tipisks piemērs: virsrakstu pievienošana tabulai, kas dažreiz sākas šūnā B3 un dažreiz šūnā C5. Absolūtajā režīmā tas vienmēr rakstītu šūnā B3. Relatīvajā režīmā tas pielāgosies atlasītajai sākuma šūnai.

Biežāk pieļautās kļūdas procesu reģistrēšanas laikā

Bieži sastopama kļūda ir formulas "vilkšana", piemēram, trīs rindas garumā, domājot, ka tā darbosies garākām tabulām. Ierakstītājs reģistrēs kaut ko līdzvērtīgu " aizpildīt trīs šūnas uz leju ", nevis "līdz tabulas beigām".

Risinājums ir mainīt pieeju: izmantot dinamiskos diapazonus (piemēram, noteikt pēdējo rindu), ciklus vai metodi CurrentRegion, vai arī pielāgot makro VBA pēc tā ierakstīšanas, lai tas mērogotos līdz jebkuram izmēram.

Laba prakse ierakstīšanas laikā

Pirms ierakstīšanas manuāli veiciet procesu pāris reizes, lai to pilnveidotu. Veidojiet īsus, specifiskus ierakstus viena liela ieraksta vietā; tas atvieglos to uzturēšanu un apvienošanu.

Izvairieties no nevajadzīgiem klikšķiem, ritināšanas lentē un atlasēm, kas neko nedod. Saglabājiet makro nosaukumu un aprakstu skaidru, lai jūs vai jebkurš kolēģis varētu uzreiz saprast tā mērķi.

Makro palaišana dažādos veidos

Papildus dialoglodziņam Makro (Alt+F8) makro var palaist, izmantojot formu, pogu ātrās piekļuves rīkjoslā vai atverot darbgrāmatu. Varat pat likt programmai Excel palaist makro no citām Office lietotnēm, kas ļauj automatizēt uzdevumus vairākās lietojumprogrammās (piemēram, tabulas atjaunināšanu un e-pasta sūtīšanu, izmantojot programmu Outlook).

Rūpīgi pārdomājiet īsinājumtaustiņus: ja makro piešķirat Ctrl+Z, jūs zaudēsiet iespēju atsaukt darbību, kamēr darbgrāmata ir atvērta. Tāpēc ieteicams izmantot taustiņu kombinācijas Ctrl+Shift , lai izvairītos no konfliktiem.

Darbgrāmatu saglabāšana ar makro

Ja failā ir VBA kods, saglabājiet to kā .xlsm failu, lai kods tiktu saglabāts un to varētu palaist. Saglabājot kā .xlsx failu, VBA projekts tiks iznīcināts . Ja vēlaties globālus makro, izmantojiet personīgo makro darbgrāmatu (Personal.xlsb).

Drošība un makro iespējošana

Makro var tikt bloķēti atkarībā no jūsu drošības iestatījumiem. Pārbaudiet drošības kontroles centru, lai tos iespējotu selektīvi, un nekad neiespējojiet makro no nezināmiem avotiem. Izpratne par savu drošības līmeni palīdz līdzsvarot aizsardzību un produktivitāti.

Makro piešķiršana pogām, formām un vadīklām

Lai lietotāji varētu automātiski palaist makro, piešķiriet makro pogai, formai vai vadīklai. Ar peles labo pogu noklikšķiniet uz objekta > Piešķirt makro > atlasīt. Varat arī ievietot ikonu lentē vai ātrās piekļuves rīkjoslā un saistīt to ar makro.

Ja izmantojat veidlapas vai ActiveX vadīklas, varat tām piešķirt makro vai notikumu procedūras (piemēram, klikšķi). Tas nodrošina lietotājam draudzīgākas saskarnes un vadītus procesus.

Noderīgi makro piemēri (VBA)

Šie piemēri ilustrē bieži veicamos uzdevumus. Varat tos ielīmēt modulī (Alt+F11 > Ievietot > Modulis) un pielāgot. Atcerieties parakstīt darbgrāmatas vai pielāgot drošības iestatījumus, ja jūsu organizācija to pieprasa, lai jūs varētu tās palaist bez brīdinājumiem.

Kopēt atlasīto saturu uz citu vietu tajā pašā lapā: Selection.Copy Destination

Sub CopiarSeleccion()
    If TypeName(Selection) = "Range" Then
        Selection.Copy Destination:=Selection.Offset(0, 2)
    Else
        MsgBox "Selecciona un rango de celdas primero."
    End If
End Sub

Izdrukāt aktīvo lapu: ActiveSheet.PrintOut

Sub ImprimirHojaActual()
    ActiveSheet.PrintOut
End Sub

Saglabājiet failu kā makro iespējotu darbgrāmatu (.xlsm): ActiveWorkbook.SaveAs

Sub GuardarComoXlsm()
    Dim ruta As String
    ruta = ThisWorkbook.Path & Application.PathSeparator & "LibroConMacros.xlsm"
    ActiveWorkbook.SaveAs Filename:=ruta, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

Lietojiet formatējumu diapazonam (pielāgojiet diapazonu savām vajadzībām): Ar diapazonu(…)

Sub FormatoRango()
    With Range("A1:D10")
        .Font.Bold = True
        .Interior.Color = RGB(230, 230, 250)
        .Borders.LineStyle = xlContinuous
    End With
End Sub

Atrodiet vērtību diapazonā un parādiet pirmo atbilstību: Range(…).Find

Sub BuscarValor()
    Dim celda As Range
    Dim valor As String
    valor = InputBox("¿Qué valor quieres buscar?")
    If valor = "" Then Exit Sub
    Set celda = Range("A1:D1000").Find(What:=valor, LookIn:=xlValues, LookAt:=xlPart)
    If Not celda Is Nothing Then
        MsgBox "Encontrado en: " & celda.Address
        celda.Select
    Else
        MsgBox "No se encontró el valor especificado."
    End If
End Sub

Praktisks gadījums: formatēšana procentos

Ierakstīšana: Sāciet ierakstīšanu, piešķiriet tai nosaukumu, izvēlieties saglabāšanas vietu un pēc izvēles piešķiriet saīsni un aprakstu. Atlasiet šūnas un cilnē Sākums lietojiet funkciju "Procenti". Apturiet ierakstīšanu un pēc tam atkārtojiet to, nospiežot taustiņu kombināciju Alt+F8. Ja vēlaties to atskaņot ar minimālistisku kodu, VBA valodā izmantojiet Selection.Style = "Procenti" .

Padoms. Ierakstīšanas laikā izvairieties no nevajadzīgas šūnu pārslēgšanas. Šīs kustības uz priekšu un atpakaļ tiek ierakstītas un var palēnināt un sabojāt makro.

Automatizējiet garus uzdevumus: mazi makro ir labāki

Ja jums ir process ar daudziem soļiem, apsveriet iespēju to sadalīt vairākos mazākos makro un secīgi izsaukt tos. Tas atvieglo apkopi, ļauj atkārtoti izmantot blokus un vienkāršo atkļūdošanu.

Piemēram, viens makro datu sagatavošanai, cits formatēšanai, vēl viens izdrukas ģenerēšanai un vēl viens eksportēšanai uz PDF vai nosūtīšanai pa e-pastu . Varat tos izsaukt pa vienam vai visus uzreiz.

Rediģēt un iztīrīt ierakstīto kodu

Diktofons fiksē "visu": atlases, klikšķus un tabulēšanas taustiņu izmaiņas. Atverot VBE, tas noņem nevajadzīgās atlases (Select/Selection), aizstājot tās ar tiešām atsaucēm (piemēram, Range("A1").Value = ... ), un grupē formātus blokos With/End With.

Turklāt tas pievieno mainīgos un pamata kļūdu apstrādi. Pāris "if" priekšrakstu un vienkāršas pārbaudes novērš kļūdas, ja diapazons ir tukšs vai atlase neatbilst paredzētajam.

Bieži uzdotie jautājumi

Kā programmā Excel izveidot lietotāja veidlapu (UserForm)? Atveriet Visual Basic redaktoru (Alt+F11), dodieties uz Ievietot > Lietotāja veidlapu un pievienojiet vadīklas (teksta lodziņus, pogas utt.). To var parādīt, izmantojot makro.

Sub MostrarFormulario()
    UserForm1.Show
End Sub

Pēc tam piešķiriet procedūras vadības elementu notikumiem (piemēram, klikšķim uz pogas), lai izpildītu nepieciešamo loģiku.

Kā aizsargāt/noņemt aizsardzību lapai ar makro? Izmantojiet šīs rutīnas, mainot paroli pēc vēlēšanās. Tās ļauj aktivizēt vai deaktivizēt aizsardzību ar vienu klikšķi.

Sub ProtegerHoja()
    ActiveSheet.Protect Password:="segura", AllowFiltering:=True
End Sub

Sub DesprotegerHoja()
    ActiveSheet.Unprotect Password:="segura"
End Sub

Kā izdzēst makro? Dodieties uz Izstrādātājs > Makro, atlasiet makro un nospiediet Dzēst. Ja vēlaties dzēst visu kodu no moduļa, atveriet VBE (Alt+F11), atrodiet moduli projektā, ar peles labo pogu noklikšķiniet un atlasiet Noņemt moduli (varat to vispirms eksportēt, ja vēlaties to saglabāt).

Vēl viens praktisks padoms: ja formatējat ikmēneša pārskatu, kurā klienti ar neapmaksātiem atlikumiem ir jāizceļ sarkanā krāsā un treknrakstā, ierakstiet secību vienreiz, pielāgojiet VBA kodu, lai tas tiktu lietots pareizajā diapazonā (izmantojot pēdējās rindas noteikšanu vai filtrus), un saglabājiet makro savā personīgajā makro darbgrāmatā. Nākamreiz vienkārša saīsne vai poga būs viss, kas nepieciešams, lai formatējumu lietotu uzreiz, nevainojami, dažu sekunžu laikā.