contingut
Excel té una capacitat potent, però al mateix temps molt poc utilitzada, de crear seqüències automàtiques d'accions mitjançant macros. Una macro és una sortida ideal si es tracta del mateix tipus de tasca que es repeteix moltes vegades. Per exemple, tractament de dades o format de documents segons una plantilla estandarditzada. En aquest cas, no necessiteu coneixements de llenguatges de programació.
Ja tens curiositat per saber què és una macro i com funciona? Aleshores, endavant amb valentia; aleshores farem tot el procés de creació d'una macro amb vosaltres pas a pas.
Què és la macro?
Una macro a Microsoft Office (sí, aquesta funcionalitat funciona igual en moltes aplicacions del paquet Microsoft Office) és un codi de programa en un llenguatge de programació Visual Basic per a aplicacions (VBA) emmagatzemat dins del document. Per fer-ho més clar, un document de Microsoft Office es pot comparar amb una pàgina HTML, llavors una macro és un anàleg de Javascript. El que Javascript pot fer amb les dades HTML d'una pàgina web és molt semblant al que pot fer una macro amb les dades d'un document de Microsoft Office.
Les macros poden fer gairebé qualsevol cosa que vulgueu en un document. Aquestes són algunes d'elles (una part molt petita):
- Aplicar estils i format.
- Realitzar diverses operacions amb dades numèriques i de text.
- Utilitzeu fonts de dades externes (fitxers de bases de dades, documents de text, etc.)
- Creeu un document nou.
- Feu tot l'anterior en qualsevol combinació.
Creació d'una macro: un exemple pràctic
Per exemple, prenem el fitxer més comú CSV. Aquesta és una taula senzilla de 10 × 20 plena de números del 0 al 100 amb encapçalaments per a columnes i files. La nostra tasca és convertir aquest conjunt de dades en una taula amb un format presentable i generar totals a cada fila.
Com ja s'ha esmentat, una macro és un codi escrit en el llenguatge de programació VBA. Però a Excel, podeu crear un programa sense escriure una línia de codi, cosa que farem ara mateix.
Per crear una macro, obriu Veure comanda (Tipus) > Macros (Macro) > Grava la macro (Enregistrament macro...)
Doneu un nom a la vostra macro (sense espais) i feu clic OK.
A partir d'aquest moment, es registren TOTES les vostres accions amb el document: canvis a cel·les, desplaçament per la taula, fins i tot canvi de mida de la finestra.
Excel indica que el mode d'enregistrament de macro està habilitat en dos llocs. Primer, al menú Macros (Macros) - en lloc d'una cadena Grava la macro Va aparèixer la línia (Enregistrament d'una macro...). Atura la gravació (Atura la gravació).
En segon lloc, a la cantonada inferior esquerra de la finestra d'Excel. Icona Stop (quadrat petit) indica que el mode d'enregistrament de macro està habilitat. Si feu-hi clic, s'aturarà la gravació. Per contra, quan el mode d'enregistrament no està habilitat, hi ha una icona per habilitar l'enregistrament de macros en aquesta ubicació. Si feu-hi clic, obtindreu el mateix resultat que activar la gravació a través del menú.
Ara que el mode d'enregistrament de macro està habilitat, anem a la nostra tasca. Primer de tot, afegim capçaleres per a les dades de resum.
Next, enter the formulas in the cells in accordance with the names of the headings (variants of the formulas for the English and versions of Excel are given, cell addresses are always Latin letters and numbers):
- =SUMA(B2:K2) or =SUMA(B2:K2)
- =MITJANA (B2:K2) or =СРЗНАЧ(B2:K2)
- =MIN(B2:K2) or =MIN(B2:K2)
- =MAX(B2:K2) or =MAX(B2:K2)
- =MEDIANA(B2:K2) or =MEDIANA(B2:K2)
Ara seleccioneu les cel·les amb fórmules i copieu-les a totes les files de la nostra taula arrossegant el mànec d'emplenament automàtic.
Després de completar aquest pas, cada fila hauria de tenir els totals corresponents.
A continuació, resumirem els resultats per a tota la taula, per a això fem unes quantes operacions matemàtiques més:
Respectivament:
- =SUMA(L2:L21) or =SUMA(L2:L21)
- =MITJANA (B2:K21) or =СРЗНАЧ(B2:K21) – per calcular aquest valor, cal prendre exactament les dades inicials de la taula. Si agafeu la mitjana de les mitjanes per a files individuals, el resultat serà diferent.
- =MIN(N2:N21) or =MIN(N2:N21)
- =MAX(O2:O21) or =MAX(O2:O21)
- =MEDIANA(B2:K21) or =MEDIANA(B2:K21) – considerem utilitzar les dades inicials de la taula, pel motiu indicat anteriorment.
Ara que hem acabat amb els càlculs, fem una mica de format. Primer, establim el mateix format de visualització de dades per a totes les cel·les. Seleccioneu totes les cel·les del full, per fer-ho, utilitzeu la drecera del teclat Ctrl + Ao feu clic a la icona Selecciona-ho tot, que es troba a la intersecció dels encapçalaments de fila i columna. A continuació, feu clic Estil de coma pestanya (Format delimitat). Home (Casa).
A continuació, canvieu l'aparença de les capçaleres de columna i fila:
- Estil de lletra en negreta.
- Alineació central.
- Farciment de color.
I finalment, configurem el format dels totals.
Així és com hauria de quedar al final:
Si tot et convé, deixa de gravar la macro.
Felicitats! Acabeu d'enregistrar la vostra primera macro a Excel.
Per utilitzar la macro generada, hem de desar el document Excel en un format que admeti macros. En primer lloc, hem d'esborrar totes les dades de la taula que hem creat, és a dir, fer-la una plantilla buida. El cas és que en el futur, treballant amb aquesta plantilla, hi importarem les dades més recents i rellevants.
Per esborrar totes les cel·les de les dades, feu clic amb el botó dret a la icona Selecciona-ho tot, que es troba a la intersecció dels encapçalaments de fila i columna, i des del menú contextual, seleccioneu Esborrar (Suprimeix).
Ara el nostre full està completament esborrat de totes les dades, mentre que la macro roman registrada. Hem de desar el quadern de treball com una plantilla d'Excel activada per macro que tingui l'extensió XLTM.
Un punt important! Si deseu el fitxer amb l'extensió XLTX, aleshores la macro no funcionarà. Per cert, podeu desar el llibre de treball com a plantilla Excel 97-2003, que té el format XLT, també admet macros.
Quan es deseu la plantilla, podeu tancar Excel amb seguretat.
Execució d'una macro en Excel
Abans de revelar totes les possibilitats de la macro que heu creat, crec que és correcte parar atenció a un parell de punts importants pel que fa a les macros en general:
- Les macros poden ser perjudicials.
- Torna a llegir el paràgraf anterior.
El codi VBA és molt potent. En particular, pot realitzar operacions sobre fitxers fora del document actual. Per exemple, una macro pot suprimir o modificar qualsevol fitxer d'una carpeta La meva documents. Per aquest motiu, només executeu i permeteu macros de fonts de confiança.
Per executar la nostra macro de format de dades, obriu el fitxer de plantilla que hem creat a la primera part d'aquest tutorial. Si teniu una configuració de seguretat estàndard, quan obriu un fitxer, apareixerà un avís a sobre de la taula que indica que les macros estan desactivades i un botó per activar-les. Com que hem fet la plantilla nosaltres mateixos i confiem en nosaltres mateixos, premem el botó Activa el contingut (Inclou contingut).
El següent pas és importar el darrer conjunt de dades actualitzat del fitxer CSV (en base a aquest fitxer, vam crear la nostra macro).
Quan importeu dades d'un fitxer CSV, és possible que Excel us demani que configureu alguns paràmetres per transferir correctament les dades a la taula.
Quan finalitzi la importació, aneu al menú Macros pestanya (Macros). Veure comanda (Veure) i seleccioneu una ordre Veure macros (Macro).
Al quadre de diàleg que s'obre, veurem una línia amb el nom de la nostra macro FormatData. Seleccioneu-lo i feu clic Correr (Executar).
Quan la macro comenci a executar-se, veureu el cursor de la taula saltant de cel·la a cel·la. Al cap d'uns segons, es faran les mateixes operacions amb les dades que quan s'enregistra una macro. Quan tot estigui llest, la taula ha de tenir el mateix aspecte que l'original que hem format a mà, només amb dades diferents a les cel·les.
Mirem sota el capó: com funciona una macro?
Com s'ha esmentat més d'una vegada, una macro és codi de programa en un llenguatge de programació. Visual Basic per a aplicacions (VBA). Quan activeu el mode d'enregistrament de macros, Excel enregistra cada acció que feu en forma d'instruccions VBA. En poques paraules, Excel escriu el codi per a tu.
Per veure aquest codi de programa, necessiteu al menú Macros pestanya (Macros). Veure comanda (veure) feu clic Veure macros (Macros) i al quadre de diàleg que s'obre, feu clic Editar (Canvi).
S'obre la finestra. Visual Basic per a aplicacions, en el qual veurem el codi del programa de la macro que hem gravat. Sí, heu entès bé, aquí podeu canviar aquest codi i fins i tot crear una nova macro. Les accions que hem realitzat amb la taula d'aquesta lliçó es poden enregistrar mitjançant l'enregistrament automàtic de macros a Excel. Però les macros més complexes, amb una seqüència i una lògica d'acció ben ajustades, requereixen programació manual.
Afegim un pas més a la nostra tasca...
Imagineu que el nostre fitxer de dades original dades.csv es crea automàticament per algun procés i sempre s'emmagatzema al disc al mateix lloc. Per exemple, C:Datadata.csv – camí al fitxer amb dades actualitzades. El procés d'obertura d'aquest fitxer i d'importació de dades també es pot registrar en una macro:
- Obriu el fitxer de plantilla on hem desat la macro − FormatData.
- Creeu una macro nova anomenada LoadData.
- Durant la gravació d'una macro LoadData importar dades del fitxer dades.csv – com hem fet a la part anterior de la lliçó.
- Quan s'hagi completat la importació, deixeu d'enregistrar la macro.
- Suprimeix totes les dades de les cel·les.
- Deseu el fitxer com a plantilla d'Excel amb macro (extensió XLTM).
Així, en executar aquesta plantilla, obteniu accés a dues macros: una carrega les dades i l'altra les formatea.
Si voleu introduir-vos en la programació, podeu combinar les accions d'aquestes dues macros en una, simplement copiant el codi de LoadData fins al començament del codi FormatData.