¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
  1. Libros
  2. Cuadros de mando
  3. Técnicas avanzadas de automatización
Extrait - Cuadros de mando Gestione su información para optimizar la toma de decisiones con Excel (versiones Microsoft 365, 2019, 2021)
Extractos del libro
Cuadros de mando Gestione su información para optimizar la toma de decisiones con Excel (versiones Microsoft 365, 2019, 2021) Volver a la página de compra del libro

Técnicas avanzadas de automatización

Introducción

En este último capítulo, veremos la automatización de la recopilación, procesamiento y visualización de datos. Nos basaremos en las macros de comandos de Excel y el lenguaje VBA (Visual Basic for Applications) asociado.

Por lo tanto, comenzaremos descubriendo la implementación de macros usando el grabador, aplicaremos su uso a la adición de datos de un formulario a una base de datos completa, como se explicó en la sección La entrada de datos - Uso de un formulario de entrada del capítulo Los datos.

Continuaremos descubriendo el lenguaje mediante la creación de macros de organización, como la recuperación de la lista de archivos presentes en un directorio, la lista de los nombres de las pestañas presentes en un libro de trabajo y la lista de los nombres utilizados en un libro de trabajo.

Automatizaremos la conversión de datos supuestamente recuperados de software de terceros que no respetan los formatos de fecha habituales, así como la conversión de datos fusionados erróneamente en una celda (nombre y apellidos).

Nos ocuparemos de la clasificación sistematizada en un rango variable de datos y terminaremos con una macro de envío de una hoja de un libro de trabajo por correo electrónico, tan pronto como un indicador supere un umbral previsto.

Usar macros

Anteriormente en este libro, hemos habilitado el acceso a la pestaña Programador, que aún debería estar operativa. Ahora nos centraremos en el grupo Código.

images/cap12_1.png

Contiene los siguientes botones:

images/C12-002N3.png

Grabar una macro (que se convierte en macro entre el inicio y el final de la grabación).

images/C12-003N3.png

Mostrar las macros: para llamar a una macro, siempre y cuando no esté vinculada a un botón.

images/C12-004N3.png

Visual Basic (el atajo de teclado es AltF11): la ventana del editor de VBA está compuesto por las siguientes secciones.

images/cap12_2.png

En 1, el área Explorador de proyectos (visible por Ctrl R, si se ha cerrado por error), el libro, las hojas y los módulos que pueden contener código.

En 2, el área del código, que se puede editar pulsando dos veces seguidas en la zona 1 o después de elegir el objeto con un solo clic.

En 3, en la ventana Propiedades, las propiedades del objeto seleccionado en 1.

En 4, la ventana Ejecución.

En la barra de herramientas Estándar se encuentran los botones clásicos Cortar, Copiar, Pegar, Buscar, Cancelar, Repetir, complementados por los botones Ejecutar, Detener y Restablecer.

Macros imprescindibles para el panel de control

Historización

 Abra el libro llamado Agregar cliente.xlsm, pestaña Agregar.

 Copie los datos de las celdas O9 a O35.

 Péguelos en D9.

Los datos presentes en el rango D9 a D35 no cambian. Lo único que falta por hacer es transferirlos a la pestaña lista_cliente. Hay un "búfer" en la pestaña Agregar en las celdas de Y97 a An101, al que se puede acceder mediante la flecha en L3. Esta zona muestra los valores de las celdas D9 a D35, en el sentido y el orden de la tabla en la pestaña lista_cliente.

 En la pestaña Programador - grupo Código, haga clic en Grabar macro. En la ventana que aparece, escriba Agregar en el cuadro Nombre de la macro. Lo almacenaremos en este libro porque no necesitaremos usarlo en otro libro (está en formato .xlsm, el formato de libro de Excel que tiene en cuenta las macros).

images/cap12_3.png

 Confirme pulsando en Aceptar.

No haga acciones innecesarias como desplazarse hacia abajo o hacia la derecha porque todo lo que haga ahora será grabado y convertido en código VBA.

 Haga clic en la pestaña lista_cliente, seleccione la fila 9, inserte una fila y marque el mismo formato que se muestra a continuación.

images/cap12_4.png

 Haga clic en la pestaña Agregar, seleccione las celdas Y100 a AM100, y pulse en Copiar. 

 Haga clic en la pestaña lista_cliente, en B9, haga Pegar - Pegar especial - Valores.

 Pulse en la tecla Esc para desactivar la función de copia.

 Regrese a la pestaña Agregar, seleccione la celda D9.

Detengamos la macro aquí:

 En la pestaña Programador - grupo Código, haga clic en Detener grabación.

images/cap12_5.png

El botón para detener la grabación también está presente en la barra de tareas.

Vamos a crear una macro desde cero.

 En la pestaña Programador - grupo Código, haga clic en Grabar macro. En la ventana que aparece, escriba RAZ en el cuadro Nombre de macro y confirme.

 Seleccione las celdas D9 a D35, borre el contenido. A continuación, vuelva a hacer clic en D9.

 Haga clic en Detener grabación.

Vamos a comprobar el código generado.

 Abra el editor de VBA con AltF11 y, a continuación, haga doble clic en Módulo 1. En el cuadro de código, aparece el siguiente código:

images/cap12_5_b.png

Contiene cada una de las acciones realizadas paso a paso, traducidas a código ejecutable. La transcripción es bastante fácil para los anglófonos: Sheets("lista_cliente").Select significa seleccionar la hoja lista_cliente, Range("B9").Select significa seleccionar la celda B9, Selection.ClearContents significa borrar el contenido de la selección, por ejemplo.

Ahora vamos a asignar las dos macros a los botones provistos para este propósito.

 Cierre el editor de VBA. Una vez en el libro, haga clic con el botón derecho del ratón en el botón del triángulo verde y haga clic en Asignar macro:

images/cap12_6.png

 En la ventana Asignar macro, haga clic en Agregar y, a continuación, en Aceptar.

images/cap12_6_b.png

 Haga lo mismo con el botón del triángulo rojo para asignarle la macro RAZ.

Ahora puede usar la macro para insertar tantos clientes nuevos como desee, el número se incrementará automáticamente. En caso...

Las macros de reprocesamiento

En las fechas

Supongamos que ha importado datos de su aplicación de gestión. Las fechas de esta lista tienen el formato AAAAMMDD y Excel no puede procesarlas directamente como fechas.

Vamos a crear una función personalizada para transformar estas fechas en DD/MM/AAAA. Esta característica puede ser útil en otros casos, por lo que se debe colocar en su libro de macros personales.

 Abra el libro CorreccionFechas.xlsm.

 Abra el editor Visual Basic VBA en el módulo GestionFechas (también puede copiar código del archivo Sub Fechas.txt):

Function TDate(VDate As String) As Variant 
   If Len(VDate) <> 0 Then 
       TDate = CDate(Right(VDate, 2) & "/" & Mid(VDate, 5, 2) & "/" & Left(VDate, 4)) 
       Else 
           TDate = "" 
   End If 
End Function 

Una vez creada la función, se puede utilizar directamente en los cálculos:

 Escriba en B2 =tdate (A2).

images/cap12_15.png

Sobre los nombres/apellidos

Entre las importaciones realizadas, algunas veces tendrá que corregir cierta información. En el siguiente ejemplo, los nombres y apellidos de nuestros empleados se han introducido en la misma columna. Además, no se respetaron las reglas de entrada para letras minúsculas/mayúsculas.

Excel...

Clasificar sus datos

Al procesar datos, a menudo tendrá que clasificarlos. Puede ser interesante utilizar una macro para hacer clasificaciones rápidamente.

Para simplificar futuras clasificaciones, nuestro objetivo realizar únicamente las siguientes acciones:

  • Hacer clic en la columna que se desea ordenar.

  • Ejecutar un atajo de teclado (por ejemplo, CtrlMayúsJ).

Por lo tanto, nuestra macro tendrá que seleccionar el rango, identificar la columna a ordenar y luego ordenar los datos.

 Abra el libro ListEmpleados.xlsx.

 Abra el editor de VBA.

 Acceda al libro de macros personal: en la pestaña Proyecto, despliega VBAProject (PERSONAL. XLSB) y pulse dos veces seguidas en el módulo.

 Inserte el siguiente código en un módulo del libro de macros personales (ubicado en el archivo Sub Ordenacion.txt).

Sub ordCol() 
Dim NumCol As Integer, RangoActivo As Range 
Set RangoActivo = ActiveCell.CurrentRegion 
NumCol = ActiveCell.Column 
With ThisWorkbook.ActiveSheet.Sort 
   .SortFields.Clear 
   .SetRange RangoActivo 
   .Header = xlYes 
   .MatchCase = False 
   .SortFields.Add Key:=Cells(, NumCol), SortOn:=xlSortOnValues, Order:=xlAscending 
   .Orientation = xlTopToBottom 
   .Apply 
End With 
End Sub 

 Salga del editor de VBA y guarde el libro de macros personales.

Si el libro...

Enviar una hoja del cuadro de mando con Outlook

A veces puede ser interesante enviar automáticamente una hoja representativa de su cuadro de mando por correo electrónico a un destinatario específico, en función del valor de un indicador en particular.

 Abra el libro MiniTab.xlsm.

Es probable que la hoja que va a enviar tenga fórmulas que dependan de otras hojas o incluso de otros libros de trabajo. Si esta hoja de cálculo se envía de manera aislada, las fórmulas ya no funcionarán. Por lo tanto, será necesario enviar esta hoja con los resultados de los cálculos y no las fórmulas.

Aquí, consideraremos que si nuestro indicador de tasa de entrega, ubicado en la celda B1 de nuestra hoja de tablero, supera el 10%, será necesario enviar una alerta por correo electrónico al destinatario especificado previamente.

Para que este envío se pueda automatizar, debe estar asociado a un evento que se active en función del valor de nuestra celda de referencia (B1). Outlook debe estar abierto y configurado correctamente.

 Haga clic con el botón derecho del ratón en la pestaña CuadMando que contiene el valor de referencia.

 En el menú emergente que aparece, seleccione la opción Ver código.

 Escriba el siguiente código directamente en el módulo o use el archivo Sub EnvioMel.txt :

Private...

En conclusión

Para terminar este libro, estos son los últimos consejos que puede aplicar a sus cuadros de mando:

  • No introduzca datos innecesarios.

  • Evite la información redundante

  • No mezcle el contenido de las celdas.

  • Coloque cálculos resumidos en la parte superior de sus listas de datos.

  • Utilice los vínculos entre las hojas y las carpetas.

  • Use los nombres de las celdas.

  • Aplique formato condicional pero sin sobrecargar las tablas con varios colores.

  • Y, por supuesto, revise siempre sus cálculos.