Una de las funciones de Excel más poderosas, pero rara vez utilizada, es la capacidad de crear tareas automatizadas y lógica personalizada en macros con mucha facilidad. Las macros son una forma ideal de ahorrar tiempo en tareas predecibles y repetitivas, así como de estandarizar formatos de documentos varias veces sin tener que escribir una sola línea de código.
Si tiene curiosidad sobre qué son las macros o cómo crearlas, no hay problema: lo guiaremos a través del proceso.
Nota: El mismo proceso debería funcionar en la mayoría de las versiones de Microsoft Office. Las capturas de pantalla pueden ser ligeramente diferentes.
¿Qué es una macro?
Una macro de Microsoft Office (porque esta funcionalidad se aplica a muchas de las aplicaciones de MS Office) es simplemente código de Visual Basic para Aplicaciones (VBA) guardado en un documento. Para una analogía comparable, piense en un documento en HTML y una macro en Javascript. Así como 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 se le ocurra a su imaginación. Como una lista (muy) corta de funciones que puede hacer con una macro:
- Aplicar estilo y formato.
- Trabaja con datos y texto.
- Comunicarse con fuentes de datos (base de datos, archivos de texto, etc.).
- Cree documentos completamente nuevos.
- Cualquier combinación, en cualquier orden, de cualquiera de los anteriores.
Crear una macro: una explicación con un ejemplo
Comenzamos con su archivo CSV de variedad de jardín. Nada especial aquí, solo un conjunto de 10 × 20 números 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 resúmenes totales para cada fila.
Como dijimos anteriormente, una macro es código VBA, pero una de las mejores cosas de Excel es que puede crearlas / guardarlas sin necesidad de codificación, como lo haremos aquí.
Para crear una macro, vaya a Ver> Macros> Grabar macro.
Ingrese un nombre para la macro (sin espacios) y haga clic en Aceptar.
Una vez hecho esto, se registran todas sus acciones: cada cambio de celda, acción de desplazamiento, cambio de tamaño de ventana, lo que sea.
Hay algunos lugares que indican que Excel está en modo de grabación. La primera es abrir el menú Macro y notar que Detener grabación ha reemplazado a la opción Grabar macro.
El otro está en la esquina inferior derecha. El icono ‘detener’ indica que está en modo macro y presionar aquí detendrá la grabación (de la misma manera, cuando no esté en modo grabación, este icono será el botón Grabar macro, que puede usar en 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ÁX (B2: K2)
- = MEDIANO (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 las filas no es necesariamente igual al promedio de todos los valores.
- = MÍNIMO (N2: N21)
- = MAX (O2: O21)
- = MEDIANA (B2: K21) * Calculado sobre todos los datos por el mismo motivo que el anterior.
Ahora que los cálculos están completos, aplicaremos el estilo y el formato. Primero aplique el formato numérico general a todas las celdas haciendo un Seleccionar todo (ya sea 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 formato visual a los encabezados de fila y columna:
- Gordo.
- Centrar.
- Color de relleno de fondo.
Y finalmente, aplique un poco de estilo a los totales.
Cuando todo está hecho, así es como se ve nuestra hoja de datos:
Como estamos satisfechos con los resultados, deje de grabar la macro.
Felicitaciones, acaba de crear una macro de Excel.
Para usar nuestra macro recién guardada, necesitamos guardar nuestro libro de Excel en un formato de archivo compatible con macros. Sin embargo, antes de hacer esto, primero debemos borrar todos los datos existentes para que no entren en nuestro modelo (la idea es que cada vez que usemos este modelo, importaremos el más reciente).
Para hacer esto, seleccione todas las celdas y elimínelas.
Con los datos ahora borrados (pero las macros aún incluidas en el archivo de Excel), queremos guardar el archivo como un archivo de plantilla compatible con macros (XLTM). Es importante tener en cuenta que si lo guarda como un archivo de plantilla estándar (XLTX), las macros no para poder ser expulsado. También puede guardar el archivo como un archivo de plantilla heredado (XLT), lo que permitirá que se ejecuten macros.
Una vez que haya guardado el archivo como plantilla, continúe y cierre Excel.
Usando 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.
- Vea el punto anterior.
El código VBA es bastante poderoso y puede manipular archivos fuera del alcance del documento actual. Por ejemplo, una macro puede modificar o eliminar archivos aleatorios en su carpeta Mis documentos. Como tal, es importante asegurarse de que solo esté ejecutando macros de fuentes confiables.
Para usar nuestra macro de formato de datos, abra el archivo de plantilla de Excel creado anteriormente. Cuando haga esto, suponiendo que la configuración de seguridad estándar esté habilitada, verá una advertencia en la parte superior del libro de trabajo que indica que las macros están deshabilitadas. Como confiamos en una macro creada por nosotros mismos, haga clic en el botón «Activar contenido».
A continuación, vamos a importar el último conjunto de datos de un archivo CSV (esta es la fuente que usó la hoja de cálculo para crear nuestra macro).
Para terminar de importar el 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 Mostrar macros.
En el cuadro de diálogo resultante, vemos la macro «FormatData» que registramos anteriormente. Selecciónelo y haga clic en Ejecutar.
Una vez que se ejecuta, puede ver que el cursor salta por unos momentos, pero a medida que lo hace, verá que los datos se manipulan exactamente como grabamos. Al final, debería verse como nuestro original, excepto con datos diferentes.
Mirar bajo el capó: qué hace que una macro funcione
Como hemos mencionado varias veces, una macro es impulsada por el código de Visual Basic para Aplicaciones (VBA). Cuando «graba» una macro, Excel realmente traduce todo lo que hace en sus respectivas instrucciones de VBA. En pocas palabras, no necesita escribir ningún código porque Excel escribe el código por usted.
Para ver el código que hace que nuestra macro funcione, en el cuadro de diálogo Macros, haga clic en el botón Editar.
La ventana que se abre muestra el código fuente que se guardó de nuestras acciones al crear la macro. Por supuesto, puede modificar este código o incluso crear nuevas macros completamente dentro de la ventana de código. Si bien la acción de guardar utilizada en este artículo probablemente satisface la mayoría de las necesidades, más acciones personalizadas o acciones condicionales requerirán que modifique el código fuente.
Lleve nuestro ejemplo un poco más lejos …
Hipotéticamente, suponga que nuestro archivo de datos de origen, data.csv, es producido por un proceso automatizado que siempre guarda el archivo en la misma ubicación (por ejemplo, C: Data data.csv es siempre el dato 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 de macros, importe el archivo de datos como lo haría normalmente.
- Después de importar los datos, deje de grabar la macro.
- Elimine todos los datos de la celda (seleccione todo y luego elimine).
- Guarde la plantilla actualizada (recuerde utilizar un formato de plantilla compatible con macros).
Una vez hecho esto, cada vez que se abra el modelo habrá dos macros, una que carga nuestros datos y la otra que los formatea.
Si realmente desea ensuciarse las manos con un poco de edición de código, puede combinar fácilmente estas acciones en una sola macro copiando el código producido de «LoadData» e insertándolo al principio del código de «FormatData».
Descarga esta plantilla
Para su comodidad, hemos incluido tanto la plantilla de Excel producida en este artículo como un archivo de datos de muestra con el que puede jugar.
Descargar plantilla de macro de Excel How-To Geek