Macros Excel: tutorial pràctic per a principiants amb exemples

excel macros hands tutorial

Aquest tutorial pràctic sobre macros d'Excel explica què és una macro, com crear i utilitzar macros VBA amb molts exemples:

La majoria de nosaltres a la indústria segurament tindrem certes tasques que s’han de dur a terme gairebé diàriament. Imagineu-vos si aquestes tasques es fan amb un sol clic. Sona emocionant? Les macros Excel són la resposta.

En aquest tutorial, aprendrem què és una macro? Com gravar una macro fent servir referència absoluta i relativa juntament amb alguns exemples pràctics.

=> Feu una ullada a la guia per a principiants de VBA aquí

Què aprendreu:

• Què són les macros d'Excel
  • Com s'activen les macros a Excel
  • Activació de la pestanya Desenvolupador
  • Opcions de la pestanya per a desenvolupadors
  • Com gravar una macro
  • Desar un llibre de treball Excel amb macro
  • S'està executant una macro
  • Referència de cèl·lules
  • Enregistreu la macro d'Excel mitjançant una referència relativa
  • Preguntes freqüents
• Conclusió
  • Lectura recomanada

Què són les macros d'Excel

Una macro és un conjunt d'accions que podeu executar per realitzar la tasca desitjada.

Suposem que cada mes creeu un informe que ha de marcar els comptes d'usuari amb l'import vençut en negreta i vermell. A continuació, podeu crear i executar una macro que apliqui aquests canvis de format cada vegada que vulgueu.

Com s'activen les macros a Excel

La pestanya Desenvolupador ens permet treballar amb funcions com ara macros, complements i també ens permet escriure el nostre propi codi VBA que ens ajudarà a automatitzar tot el que desitgem. Aquesta pestanya està oculta per defecte.

Seguiu els passos següents per mostrar la pestanya Desenvolupador. Funciona en totes les versions d'Excel per a Windows (Excel 2007,2010, 2013, 2016, 2019).

Nota: Es tracta d’un procés puntual. Un cop hàgiu activat la pestanya Desenvolupador, sempre es mostrarà en una cinta personalitzada per a cada instància d'Excel que obriu, tret que continueu i la desactiveu explícitament.

Activació de la pestanya Desenvolupador

# 1) Feu clic a Dossier fitxa

# 2) Feu clic a Opcions

# 3) Fer clic a Personalitza la cinta.

# 4) A Personalitzar la cinta habilitar Desenvolupador.

Un cop hàgiu activat la pestanya Desenvolupador, es mostrarà a la llista de cintes.

Opcions de la pestanya per a desenvolupadors

A continuació, es mostren les opcions que hi ha a la pestanya Desenvolupador.

• Visual Basic: Proporciona a un editor que escrigui o editi codi VBA. També es pot obrir amb Alt + F11.
• macros: Ofereix la llista de totes les macros ja gravades i també s’utilitza per gravar-ne una de nova. Alt + F8 obrirà directament la llista de macros.
• Complements: Permet inserir un complement i també pot gestionar-los.
• Controls: Ens ajuda a utilitzar controls de formulari i controls ActiveX. Visualització i edició de les propietats del control. Aquí es controla el mode de disseny ON / OFF.
• XML: Ens ajuda a importar / exportar un fitxer de dades XML, a gestionar els paquets d’expansió XML i també a obrir el tauler de tasques Font XML.

Com gravar una macro

Penseu en un exemple , que la vostra empresa té una eina determinada que genera fulls de temps per a diversos departaments d'Excel. Com a gerent, teniu la responsabilitat de revisar i enviar el full a l’equip financer cada setmana.

Abans d'enviar-lo, se us demana que feu un format com ara:

1. Inseriu el títol de cada full que inclogui el nom de l’equip i el número de la setmana, marqueu-lo en negreta i el fons en groc.
2. Dibuixa una vora
3. Les capçaleres de columna en negreta.
4. Canvieu el nom del nom del full com el nom de l'equip.

En lloc de fer-ho manualment cada setmana, només podeu crear una macro i realitzar totes aquestes accions amb només un clic.

Gravar macro és bastant fàcil. Navegueu fins a la pestanya Desenvolupador i premeu Grava la macro.

Això obrirà una finestra on heu d’entrar.

# 1) Nom de la macro: El nom no ha de tenir espais entre paraules. Això ha de començar amb un alfabet o un subratllat.

# 2) Tecla de drecera: Això és útil quan executeu una macro. Si premeu la tecla de drecera, s’executarà. Assegureu-vos de donar una clau que encara no s'hagi pres, en cas contrari la macro anul·larà aquesta.

Per exemple, si esmenta Ctrl + S com a drecera, cada vegada que premeu Ctrl + S, s'executarà la macro i, per tant, ignorarà l'opció de desar fitxer. Per tant, es recomana afegir Maj, com Ctrl + Maj + D

# 3) Emmagatzema la macro a: Té 3 opcions tal com es mostra a continuació.

• Aquest llibre de treball: Totes les macros creades només estaran disponibles per al llibre de treball actual. Si obriu un nou excel, la macro creada anteriorment no estarà disponible i, per tant, no es podrà utilitzar.
• Llibre de treball de macro personal: Si seleccioneu això, la macro creada s’emmagatzemarà i es mostrarà quan obriu un nou full Excel.
• Nou llibre de treball: Aquesta opció obrirà un llibre nou i es gravaran les accions realitzades en aquest llibre.

# 4) Descripció: Això descriurà l'objectiu de la macro. Es recomana fer una descripció detallada perquè tothom que ho faci sàpiga per a què serveix exactament.

Un cop emplenats els detalls dels camps esmentats anteriorment, podeu continuar i realitzar les accions necessàries al llibre de treball d'Excel i tot quedarà registrat. Quan hàgiu acabat, torneu a la pestanya Desenvolupador i premeu Atura la gravació.

Desar un llibre de treball Excel amb macro

Selecció de la macro de la botiga com a 'Aquest llibre': Penseu que heu seleccionat la macro Store com a 'Aquest llibre' durant la gravació. Un cop fet, seguiu endavant i deseu el fitxer. Mentre deseu, heu de seleccionar Llibre de treball habilitat per a macros de l'Excel. No cal que deseu explícitament la macro. Es desa automàticament.

Seleccionar la botiga de macro com a 'Llibre de treball de macro personal': Ara penseu a seleccionar la macro Store com a 'Llibre de treball de Macros personals' durant la gravació. Heu de desar la macro explícitament. Si només deseu el fitxer Excel i després intenteu tancar-lo. A continuació, rebreu un quadre de diàleg emergent com es mostra a continuació.

Nota: Si no el deseu, la macro se suprimirà.

S'està executant una macro

Ara que hem acabat de gravar i desar el fitxer, intentem executar-lo i aconseguir els resultats desitjats. Hem seguit endavant i hem enregistrat una macro amb tots els passos necessaris per assolir a l'exemple de full de temps d'assistència i l'hem desat com a Aquest llibre amb la tecla de drecera com Ctrl + Maj + B.

Per tant, cada setmana, quan rebeu un nou Excel de l’eina de programari, només heu d’obrir aquest fitxer Excel i prémer la tecla de drecera (Ctrl + Maj + B) i tots els canvis s’incorporaran tal com s’esperava. A continuació es mostra l'excel·lent resultant.

S'ha adjuntat el llibre de treball Excel-Macro

Nota:

1. Si heu oblidat la tecla de drecera, podeu anar a Desenvolupador -> Macros, seleccioneu la macro i feu clic a opcions.
2. Si la macro emmagatzemada com a botiga personal no és visible a la pestanya Macros. Aneu a Visualitza -> Mostra i mostra la llista de totes les macros.

Referència de cèl·lules

Hi ha dues maneres d’enregistrar una macro com es mostra a continuació.

1. Referència de cèl·lules absolutes
2. Referència cel·lular relativa

Referència de cèl·lules absolutes: Les referències absolutes sempre apuntaran a la cel·la concreta on s’ha enregistrat. Per exemple: si enregistreu un text a la cel·la A10, la propera vegada que feu servir aquesta macro en un altre llibre, col·locarà aquest text a A10.

Penseu en el nostre exemple de full de temps d'assistència. Sempre volem que el títol estigui a la primera fila de cada full. No volem que la referència de la cel·la canviï quan es copia a altres fulls o llibres de treball. En aquest cas, la referència de cèl·lules absolutes és útil.

Referència de cel·la relativa: Suposem que heu de repetir els passos en diversos llocs del full de treball. Les referències relatives són útils sempre que hàgiu de repetir el mateix càlcul o passos en diverses files o columnes.

Exemple: Suposem que teniu un full d'Excel amb els noms complets, els números de telèfon i els DOB de 1000 empleats. (El format és el que es mostra a continuació)

El vostre gestor espera que:

com és una clau wep

1. Nom i cognoms separats.
2. Afegiu un codi de país Exemple (+91) al número de telèfon.
3. Mostra DOB en forma de dd-dl-aa, Exemple: 10 de gener de 87.

Com que hi ha 1.000 registres, fer-ho manualment trigaria a fer-ho. Per tant, decidiu crear una macro. Però l’ús de referència absoluta no solucionarà el problema, ja que voleu que funcioni en diverses files i columnes. En aquest cas, la referència relativa és útil.

Enregistreu la macro d'Excel mitjançant una referència relativa

Per gravar mitjançant una referència relativa, primer seleccioneu la cel·la que voleu iniciar la gravació.

Aneu a Desenvolupador -> feu clic a Utilitza la referència relativa -> Grava la macro . Enregistreu tot allò que desitgeu i feu clic a Atura la gravació.

Per a l'exemple anterior, seguiu aquests passos.

1. Primer, hem d’inserir una columna al costat d’Emp FullName i canviar l’encapçalament de la columna com a FirstName i LastName.
2. Seleccioneu Cèl·lula B2-> Aneu a Desenvolupador -> Utilitzeu una referència relativa -> Registre de macro .
3. Utilitzant Delimitador de text, primer nom i cognom. Un cop fet, deixeu de gravar.
4. De la mateixa manera, creeu 2 macros més per al número de telèfon i el DOB.
5. Deseu el fitxer.
6. Per executar, seleccioneu tot el nom complet d'Emp, és a dir, B3 fins a l'últim emp que és B1001 i executeu l'1^cMacro.
7. Seguiu passos similars per al número de telèfon i el DOB. L’Excel resultant es mostra a continuació.

Fitxer adjunt com a referència

Preguntes freqüents

P # 1) Què és un exemple de macros a Excel?

Resposta: Una macro és un conjunt d'accions que podeu executar per realitzar la tasca desitjada.

Suposem que creeu un informe cada mes que ha de marcar els comptes d'usuari amb l'import vençut en negreta i vermell. Podeu crear i executar una macro que apliqui aquests canvis de format cada vegada que vulgueu amb un sol clic.

P # 2) On hi ha les macros a Excel?

Resposta: Totes les macros gravades estaran disponibles a Pestanya Desenvolupador -> Macros

Si no podeu trobar una macro personal, aneu a Visualitza -> Mostra .

P # 3) Quins són els tipus de referències de cel·les a Excel?

Resposta:

• Absolut: Les referències absolutes sempre apuntaran a la cel·la concreta on s’ha enregistrat. Per exemple, si enregistreu un text a la cel·la D10, cada vegada que s'utilitza la macro sempre apunta a D10.
• Relatiu: Són útils sempre que necessiteu repetir el mateix càlcul o passos en diverses files o columnes.

P # 4) Com puc desar una macro a Excel a tots els llibres de treball?

Resposta: Mentre enregistreu una macro, seleccioneu el llibre de treball de Macro personal a la botiga de macro, això farà que la vostra macro estigui disponible per a tots els llibres de treball. Si encara no veieu l'opció, aneu a Visualitza -> Mostra .

Conclusió

En aquest tutorial, hem après les macros Excel que ens ajuden a automatitzar les tasques rutinàries a Excel.

Hem vist què és una macro? Com s'activa la visualització de la macro a Excel. També hem explorat com registrar una macro mitjançant referències de cel·les absolutes i relatives amb exemples.

=> Llegiu tota la sèrie de formació Easy VBA

Lectura recomanada

• Tutorial Excel VBA: introducció a VBA Excel
• Tipus de dades VBA: tipus de dades numèriques i no numèriques a VBA
• 35+ Preguntes i respostes més freqüents de l'entrevista de Microsoft Excel
• Com automatitzar tasques de control de qualitat repetitives mitjançant macros d'Excel (Exemples)
• Com obrir fitxers XML a Excel, Chrome i MS Word
• Com llegir o escriure dades des d'un full d'Excel al controlador web Selenium
• 5 millors coses que un provador ha de tenir per Excel (i les perspectives canviants del provador de programari)
• Treballar amb objectes VBScript Excel