Explicamos la tecnología / / Explicativo / Aprenda a usar macros de Excel para automatizar tareas tediosas

Aprenda a usar macros de Excel para automatizar tareas tediosas

Uno de los más poderosos, pero rara vez se usaLas funciones de Excel es la capacidad de crear fácilmente tareas automatizadas y lógica personalizada dentro de las macros. Las macros proporcionan una forma ideal de ahorrar tiempo en tareas predecibles y repetitivas, así como de estandarizar formatos de documentos, muchas veces sin tener que escribir una sola línea de código.

Si tiene curiosidad por saber qué son las macros o cómo crearlas realmente, no hay problema: lo guiaremos a través de todo el proceso.

Nota: El mismo proceso debería funcionar en la mayoría de las versiones de Microsoft Office. Las capturas de pantalla pueden verse ligeramente diferentes.

¿Qué es una macro?

Una macro de Microsoft Office (como esta funcionalidadse aplica a varias de las aplicaciones de MS Office) es simplemente un código de Visual Basic para Aplicaciones (VBA) guardado dentro de un documento. Para una analogía comparable, piense en un documento como HTML y una macro como Javascript. De la misma manera que Javascript puede manipular HTML en una página web, una macro puede manipular un documento.

Las macros son increíblemente poderosas y pueden hacer casi cualquier cosa que su imaginación pueda conjurar. Como una (muy) breve lista de funciones que puede hacer con una macro:

Aplicar estilo y formato.
Manipular datos y texto.
Comuníquese con fuentes de datos (base de datos, archivos de texto, etc.).
Crea documentos completamente nuevos.
Cualquier combinación, en cualquier orden, de cualquiera de los anteriores.

Crear una macro: una explicación por ejemplo

Comenzamos con su archivo CSV de variedad de jardín. Nada especial aquí, solo un conjunto de números 10 × 20 entre 0 y 100 con un encabezado de fila y columna. Nuestro objetivo es producir una hoja de datos presentable y bien formateada que incluya totales de resumen para cada fila.

Como dijimos anteriormente, una macro es el código VBA, pero una de las cosas buenas de Excel es que puede crearlos / grabarlos con cero codificación requerida, como haremos aquí.

Para crear una macro, vaya a Ver> Macros> Grabar macro.

Asigne un nombre a la macro (sin espacios) y haga clic en Aceptar.

Una vez hecho esto, todas de sus acciones se registran: cada cambio de celda, acción de desplazamiento, cambio de tamaño de ventana, lo que sea.

Hay un par de lugares que indican que Excel es el modo de grabación. Una es ver el menú Macro y observar que Parar grabación ha reemplazado la opción de Grabar macro.

El otro está en la esquina inferior derecha. El ícono "detener" indica que está en modo macro y presionar aquí detendrá la grabación (del mismo modo, cuando no esté en modo de grabación, este ícono será el botón Grabar macro, que puede usar en lugar de ir al menú Macros).

Ahora que estamos grabando nuestra macro, apliquemos nuestros cálculos de resumen. Primero agregue los encabezados.

Luego, aplique las fórmulas apropiadas (respectivamente):

= SUMA (B2: K2)
= PROMEDIO (B2: K2)
= MIN (B2: K2)
= MÁXIMO (B2: K2)
= MEDIA (B2: K2)

Ahora, resalte todas las celdas de cálculo y arrastre la longitud de todas nuestras filas de datos para aplicar los cálculos a cada fila.

Una vez hecho esto, cada fila debe mostrar sus respectivos resúmenes.

Ahora, queremos obtener los datos de resumen de toda la hoja, por lo que aplicamos algunos cálculos más:

Respectivamente:

= SUMA (L2: L21)
= PROMEDIO (B2: K21) *Esto debe calcularse en todos los datos porque el promedio de los promedios de fila no necesariamente es igual al promedio de todos los valores.
= MIN (N2: N21)
= MÁXIMO (O2: O21)
= MEDIA (B2: K21) * Calculado en todos los datos por el mismo motivo que el anterior.

Ahora que los cálculos están hechos, aplicaremosEl estilo y el formato. Primero aplique el formato de número general en todas las celdas seleccionando Seleccionar todo (Ctrl + A o haga clic en la celda entre los encabezados de fila y columna) y seleccione el icono "Estilo de coma" en el menú Inicio.

A continuación, aplique un formato visual a los encabezados de fila y columna:

Negrita.
Centrado.
Color de relleno de fondo.

Y finalmente, aplique un poco de estilo a los totales.

Cuando todo esté terminado, así es como se ve nuestra hoja de datos:

Como estamos satisfechos con los resultados, detenga la grabación de la macro.

Felicitaciones: acaba de crear una macro de Excel.

Para usar nuestra macro recién grabada, tenemospara guardar nuestro libro de Excel en un formato de archivo habilitado para macros. Sin embargo, antes de hacer eso, primero debemos borrar todos los datos existentes para que no estén incrustados en nuestra plantilla (la idea es que cada vez que usemos esta plantilla, importaremos los datos más actualizados).

Para hacer esto, seleccione todas las celdas y elimínelas.

Con los datos ahora borrados (pero las macros aúnincluido en el archivo Excel), queremos guardar el archivo como un archivo de plantilla habilitada para macros (XLTM). Es importante tener en cuenta que si guarda esto como un archivo de plantilla estándar (XLTX), las macros no ser capaz de huir de él. Alternativamente, puede guardar el archivo como un archivo de plantilla heredada (XLT), que permitirá ejecutar macros.

Una vez que haya guardado el archivo como plantilla, continúe y cierre Excel.

Usar una macro de Excel

Antes de cubrir cómo podemos aplicar esta macro recién grabada, es importante cubrir algunos puntos sobre las macros en general:

Las macros pueden ser maliciosas.
Ver el punto de arriba.

El código VBA es realmente bastante poderoso y puedemanipular archivos fuera del alcance del documento actual. Por ejemplo, una macro podría alterar o eliminar archivos aleatorios en su carpeta Mis documentos. Como tal, es importante asegurarse de que solamente ejecutar macros de fuentes confiables.

Para utilizar nuestra macro de formato de datos, abra elArchivo de plantilla de Excel que se creó anteriormente. Cuando haga esto, suponiendo que tenga habilitadas las configuraciones de seguridad estándar, verá una advertencia en la parte superior del libro que dice que las macros están deshabilitadas. Como confiamos en una macro creada por nosotros mismos, haga clic en el botón "Habilitar contenido".

A continuación, vamos a importar el conjunto de datos más reciente de un CSV (esta es la fuente de la hoja de trabajo utilizada para crear nuestra macro).

Para completar la importación del archivo CSV, es posible que deba configurar algunas opciones para que Excel lo interprete correctamente (por ejemplo, delimitador, encabezados presentes, etc.).

Una vez que se importan nuestros datos, simplemente vaya al menú Macros (en la pestaña Ver) y seleccione Ver macros.

En el cuadro de diálogo resultante, vemos la macro "FormatData" que grabamos arriba. Selecciónelo y haga clic en Ejecutar.

Una vez que se ejecuta, es posible que vea que el cursor salta por unos momentos, pero al hacerlo verá que los datos están siendo manipulados exactamente como lo grabamos Cuando todo está dicho y hecho, debería parecerse a nuestro original, excepto con datos diferentes.

Mirando bajo el capó: lo que hace que una macro funcione

Como hemos mencionado un par de veces, una macroestá controlado por el código de Visual Basic para Aplicaciones (VBA). Cuando "graba" una macro, Excel en realidad está traduciendo todo lo que hace en sus respectivas instrucciones de VBA. En pocas palabras: no tiene que escribir ningún código porque Excel está escribiendo el código por usted.

Para ver el código que hace que se ejecute nuestra macro, desde el cuadro de diálogo Macros, haga clic en el botón Editar.

La ventana que se abre muestra el código fuenteeso fue registrado de nuestras acciones al crear la macro. Por supuesto, puede editar este código o incluso crear nuevas macros completamente dentro de la ventana de código. Si bien la acción de grabación utilizada en este artículo probablemente se ajuste a la mayoría de las necesidades, las acciones más personalizadas o condicionales requerirán que edite el código fuente.

Llevando nuestro ejemplo un paso más allá ...

Hipotéticamente, asuma nuestro archivo de datos fuente, datos.csv, es producido por un proceso automatizado que siempre guarda el archivo en la misma ubicación (por ejemplo, C: Datadata.csv es siempre la información más reciente). El proceso de abrir este archivo e importarlo también se puede convertir fácilmente en una macro:

Abra el archivo de plantilla de Excel que contiene nuestra macro "FormatData".
Grabe una nueva macro llamada "LoadData".
Con la grabación macro, importe el archivo de datos como lo haría normalmente.
Una vez que se importan los datos, deje de grabar la macro.
Elimine todos los datos de la celda (seleccione todos y luego elimine).
Guarde la plantilla actualizada (recuerde utilizar un formato de plantilla habilitado para macros).

Una vez hecho esto, cada vez que se abra la plantilla habrá dos macros: una que carga nuestros datos y otra que los formatea.

Si realmente quisieras ensuciarte las manosun poco de edición de código, puede combinar fácilmente estas acciones en una sola macro copiando el código producido a partir de "LoadData" e insertándolo al comienzo del código desde "FormatData".