agwebrunner.com

Una de les funcions d'Excel més potents, però poques vegades utilitzades, és la possibilitat de crear tasques automatitzades i lògica personalitzada molt fàcilment a les macros. Les macros ofereixen una manera ideal d’estalviar temps en tasques predicibles i repetitives, així com estandarditzar els formats de document, moltes vegades sense haver d’escriure una sola línia de codi.

Si teniu curiositat per saber què són les macros o com crear-les realment, no hi ha cap problema: us guiarem durant tot el procés.

Nota:el mateix procés hauria de funcionar a la majoria de versions de Microsoft Office. Les captures de pantalla poden tenir un aspecte lleugerament diferent.

Què és una macro?

Una macro de Microsoft Office (ja que aquesta funcionalitat s'aplica a diverses de les aplicacions de MS Office) és simplement codi Visual Basic for Applications (VBA) desat dins d'un document. Per obtenir una analogia comparable, penseu en un document com HTML i una macro com Javascript. De la mateixa manera que Javascript pot manipular HTML en una pàgina web, una macro pot manipular un document.

Les macros són increïblement potents i poden fer gairebé qualsevol cosa que la vostra imaginació pugui evocar. Com a llista (molt) curta de funcions que podeu fer amb una macro:

• Apliqueu estil i format.
• Manipular dades i text.
• Comunicar-se amb fonts de dades (base de dades, fitxers de text, etc.).
• Creeu documents completament nous.
• Qualsevol combinació, en qualsevol ordre, de qualsevol de les anteriors.

Crear una macro: una explicació per exemple

Comencem pel fitxer CSV de la vostra varietat de jardins. Aquí no hi ha res d’especial, només un conjunt de números de 10 × 20 entre 0 i 100 amb capçalera de fila i columna. El nostre objectiu és produir un full de dades presentable i ben formatat que inclogui els resums totals de cada fila.

Com hem dit anteriorment, una macro és codi VBA, però una de les coses bones de l'Excel és que podeu crear-les / enregistrar-les amb la codificació zero necessària, com farem aquí.

Per crear una macro, aneu a Visualitza> Macros> Grava macro.

Assigneu un nom a la macro (sense espais) i feu clic a D'acord.

Un cop fet això, tot de les vostres accions es registren: cada canvi de cel·la, acció de desplaçament, redimensionament de la finestra, ho anomeneu.

Hi ha un parell de llocs que indiquen que Excel és el mode de registre. Una d’elles és veure el menú Macro i assenyalar que Aturar la gravació ha substituït l’opció per Enregistrar macro.

L'altre es troba a l'extrem inferior dret. La icona ‘atura’ indica que està en mode macro i si premeu aquí s’aturarà la gravació (de la mateixa manera, quan no estigueu en mode de gravació, aquesta icona serà el botó Grava macro, que podeu utilitzar en lloc d’anar al menú Macros).

Ara que estem enregistrant la nostra macro, apliquem els nostres càlculs de resum. Primer afegiu les capçaleres.

A continuació, apliqueu les fórmules adequades (respectivament):

• = SUMA (B2: K2)
• = MITJÀ (B2: K2)
• = MIN (B2: K2)
• = MÀX. (B2: K2)
• = MITJANA (B2: K2)

Ara ressalteu totes les cel·les de càlcul i arrossegueu la longitud de totes les nostres files de dades per aplicar els càlculs a cada fila.

Un cop fet això, cada fila ha de mostrar els seus respectius resums.

Ara volem obtenir les dades resumides de tot el full, de manera que apliquem uns quants càlculs més:

Respectivament:

• = SUM (L2: L21)
• = MITJÀ (B2: K21) *S'ha de calcular entre totes les dades, perquè la mitjana de les mitjanes de les files no necessàriament és igual a la mitjana de tots els valors.
• = MIN (N2: N21)
• = MAX (O2: O21)
• = MITJANA (B2: K21) * Calculat entre totes les dades pel mateix motiu que l'anterior.

Ara que s'han fet els càlculs, aplicarem l'estil i el format. Primer apliqueu el format general del número a totes les cel·les fent una selecció de totes (Ctrl + A o feu clic a la cel·la entre les capçaleres de fila i columna) i seleccioneu la icona "Estil de coma" al menú Inici.

A continuació, apliqueu un format visual tant a les capçaleres de fila com de columna:

• Atrevit.
• Centrat.
• Color de farciment de fons.

I, finalment, apliqueu algun estil als totals.

Quan hagi acabat tot, així és el nostre full de dades:

Com que estem satisfets amb els resultats, atureu la gravació de la macro.

Enhorabona: acabeu de crear una macro d'Excel.

Per utilitzar la nostra macro recentment gravada, hem de desar el nostre llibre de treball d'Excel en un format de fitxer habilitat per a macros. Tanmateix, abans de fer-ho, primer hem d’esborrar totes les dades existents perquè no s’incorporin a la nostra plantilla (la idea és que cada vegada que utilitzem aquesta plantilla importarem les dades més actualitzades).

Per fer-ho, seleccioneu totes les cel·les i suprimiu-les.

Amb les dades esborrades (però les macros encara s’inclouen al fitxer Excel), volem desar-lo com a fitxer de plantilla habilitada per a macro (XLTM). És important tenir en compte que si el deseu com a fitxer de plantilla estàndard (XLTX), les macros ho faran no poder ser executat des d'ella. Alternativament, podeu desar el fitxer com a fitxer de plantilla heretada (XLT), que permetrà executar macros.

Un cop hàgiu desat el fitxer com a plantilla, continueu i tanqueu Excel.

Utilització d’una macro Excel

Abans d'abordar com podem aplicar aquesta macro recentment gravada, és important tractar alguns punts sobre les macros en general:

• Les macros poden ser malicioses.
• Vegeu el punt anterior.

El codi VBA és en realitat força potent i pot manipular fitxers fora de l’abast del document actual. Per exemple, una macro pot alterar o suprimir fitxers aleatoris de la carpeta Els meus documents. Com a tal, és important assegurar-vos només executeu macros des de fonts de confiança.

Per utilitzar la nostra macro de format de dades, obriu el fitxer de plantilla d'Excel que es va crear anteriorment. Quan feu això, suposant que teniu activats els paràmetres de seguretat estàndard, apareixerà un advertiment a la part superior del llibre que diu que les macros estan desactivades. Com que confiem en una macro creada per nosaltres mateixos, feu clic al botó "Activa el contingut".

A continuació, importarem el conjunt de dades més recent d’un CSV (aquesta és la font que utilitza el full de treball per crear la nostra macro).

Per completar la importació del fitxer CSV, és possible que hàgiu d’establir algunes opcions perquè l’Excel l’interpreti correctament (per exemple, delimitador, capçaleres presents, etc.).

Un cop importades les nostres dades, només cal que aneu al menú Macros (a la pestanya Visualització) i seleccioneu Visualitza macros.

Al quadre de diàleg resultant, veiem la macro "FormatData" que hem gravat més amunt. Seleccioneu-lo i feu clic a Executa.

Un cop executat, és possible que vegeu el cursor saltar uns instants, però tal com ho farà veureu que es manipulen les dades exactament tal com l’hem gravat. Quan tot estigui dit, hauria d’assemblar-se al nostre original, excepte amb dades diferents.

Mirar sota la caputxa: què fa que una macro funcioni

Com hem esmentat un parell de vegades, una macro està impulsada pel codi Visual Basic for Applications (VBA). Quan "enregistreu" una macro, Excel tradueix en realitat tot el que feu a les seves respectives instruccions VBA. Per simplificar-ho, no cal que escriviu cap codi, ja que Excel us escriu el codi.

Per veure el codi que fa executar la nostra macro, des del quadre de diàleg Macros feu clic al botó Edita.

La finestra que s'obre mostra el codi font que es va enregistrar a partir de les nostres accions en crear la macro. Per descomptat, podeu editar aquest codi o fins i tot crear macros noves completament dins de la finestra del codi. Tot i que l'acció de gravació que s'utilitza en aquest article probablement s'adapti a la majoria de les necessitats, accions més personalitzades o accions condicionals requeriran que editeu el codi font.

Prenent el nostre exemple un pas més enllà ...

Hipotèticament, suposem que el nostre fitxer de dades d'origen, data.csv, es produeix mitjançant un procés automatitzat que sempre desa el fitxer a la mateixa ubicació (per exemple, C: \ Data \ data.csv sempre és la dada més recent). El procés d'obertura i importació d'aquest fitxer també es pot convertir fàcilment en una macro:

1. Obriu el fitxer de plantilla d'Excel que conté la nostra macro "FormatData".
2. Enregistreu una nova macro anomenada "LoadData".
3. Amb la gravació de macros, importeu el fitxer de dades com ho faríeu normalment.
4. Un cop importades les dades, deixeu de gravar la macro.
5. Suprimiu totes les dades de la cel·la (seleccioneu-les totes i suprimiu-les).
6. Deseu la plantilla actualitzada (recordeu d'utilitzar un format de plantilla habilitat per a macros).

Un cop fet això, sempre que s'obre la plantilla hi haurà dues macros: una que carrega les nostres dades i l'altra que la formata.

Si realment volíeu embrutar-vos les mans amb una mica d’edició de codi, podríeu combinar fàcilment aquestes accions en una sola macro copiant el codi produït des de “LoadData” i inserint-lo al principi del codi des de “FormatData”.

Descarregueu aquesta plantilla

Per a la vostra comoditat, hem inclòs tant la plantilla Excel produïda en aquest article com un fitxer de dades de mostra amb què podeu jugar.

Descarregueu la plantilla de macro d'Excel de How-To Geek