Cuadro de control (dashboard) sencillo con Excel – Segunda nota

En la nota anterior vimos cómo construir un dashboard sencillo en Excel usando un control de la barra de Formularios y la función DESREF.

Vimos también que el control de la barra de Formularios tiene un serio inconveniente: no podemos establecer los parámetros dinámicamente. En nuestro ejemplo vimos que al agregar líneas a la base de datos tenemos que modificar manualmente la definición de valor máximo del control.

Para crear un modelo que se adapte automáticamente a los cambios en la base de datos tendremos que usar otro tipo de controles, los controles ActiveX.

Empezamos por reemplazar la barra de desplazamiento que pusimos ayer por el mismo control pero de la barra de herramienta Cuadro de Controles (ActiveX).
Para eso activamos la barra (Herramientas-Personalizar-Barra de Herramientas o mejor haciendo un clic con el botón derecho del mouse sobre el área de la barras de herramientas)

Dibujamos el control en la posición deseada (tip: mantenemos apretada la tecla ALT para que los bordes del control encuadren con los bordes de las celdas).

Al dibujar el control Excel activa automáticamente el modo de Diseño (veremos que el icono con la escuadra y la regla aparece “apretado”). Estando en modo Diseño apretamos el icono Propiedades

Establecemos estas propiedades:

LinkedCell = inicio (el rango control!C3 definido con ese nombre)

Min = 0

SmallChange = 1 (esto siginifica que cada vez que apretamos la flecha superior o inferior movemos una fila)

LargeChange = 12 (esto hace que si apretamos en algún punto de la barra se muevan 12 filas)

Max = por defecto aparece 32767. Este número lo manejaremos con una macro para evitar tener que editarlo con cada cambio en la base de datos.

Con el control seleccionado apretamos el icono “ver código”

Esta acción abre un módulo de VBa en la hoja pertinente (la hoja Informe en nuestro caso) .

En ese módulo ponemos esta macro

Private Sub ScrollBar1_Change()
ScrollBar1.Max = Sheets("control").Range("D3")
End Sub

Antes de poder usar esta macro para controlar el valor de la propiedad Max tenemos que hacer algunos agregados a nuestro modelo:

# definimos el nombre Periodos como rango dinámico con esta fórmula

=DESREF('base de datos'!$B$3;0;0;CONTARA('base de datos'!$B:$B)-1;1)

Esto nos permitirá saber en todo momento cuantas líneas de datos hay en nuestra tabla

# en la hoja Control definimos tres celdas con nombres que contendrán las siguientes fórmulas:

B3 (control_meses) : =CONTARA(periodos)

C3 (inicio): 0 o en blanco; su valor es establecido por el control (LinkedCell)

D3(max_periodo): =control_meses-12

Ahora podemos ver que el valor Max está determinado por la cantidad de períodos en la base de datos menos 12 (la cantidad de líneas que aparecen en el dashboard).

La rutina ScrollBar1_Change es una macro (evento) que es disparada cada vez que accionamos la barra de desplazamiento.

Nuestro modelo se adapta ahora automáticamente a medida que vamos agregando o quitando datos en la base de datos.

Cuadro de control (dashboard) sencillo con Excel – Primera nota

Ya habíamos tocado el tema del uso de controles en hojas. En esta nota mostraré como crear un informe dinámico o cuadro de control (dashboard) sencillo.

En esta primera nota veremos cómo hacerlo con los controles de la barra de Formulario. Estos controles tienen la ventaja de ser sencillos de usar. La desventaja es que son poco flexibles, como veremos más adelante.

Supongamos que tenemos una lista de datos de ventas (plan y realizado) de una empresa para desde enero del 2007 hasta octubre del 2009.
Nuestro objetivo es crear este cuadro de control (dashboard)





Hemos puesto una barra de desplazamiento que nos permite cambiar las fechas del informe con un clic en las flechas de la barra



Nuestro modelo tiene tres hojas:





informe – donde ponemos el cuadro de control y el gráfico
base de datos – que contiene los datos de ventas. Como pueden ver, las fechas están formateadas como mm-aaaa (o mm-yyyy)

control – ligaremos el control (la barra de desplazamiento) a la celda B3 de esta hoja.
También hemos definidos este nombres

inicio=control!$B$3
Ahora creamos el cuadro de control en la hoja “informe”.



En el rango C3:C14 ponemos la barra de desplazamiento. Ese es el motivo por el cual la columna C es más angosta que las restantes.
Activamos la barra de herramientas de Formularios y seleccionamos la barra de desplazamiento



La arrastramos y hacemos que coincida con el rango C3:C14. Luego abrimos el menú de formato del control



Ponemos estos valores:
Valor actual = 0
Valor Mínimo = 0
Valor Máximo = 22
Incremento = 1
Cambio de Página = 12
Vincular con celda - control!$B$3 (o poner el nombre “inicio”)


Seguimos. En la celda B3 ponemos esta fórmula

=DESREF('base de datos'!$B$3;inicio+FILA()-3;0)


y la copiamos hasta la celda B14.
En la celda D3 ponemos la misma fórmula pero referenciando la columna C en la hoja “base de datos”

=DESREF('base de datos'!$C$3;inicio+FILA()-3;0)

y lo mismo en la celda E3, con referencia a la columna D en “base de datos”


En la celda F3 ponemos la fórmula =E3/D3-1

Copiamos todas las fórmulas al resto de la tabla. Todo lo que nos queda por hacer es crear el gráfico


Este gráfico es absolutamente dinámico e irá cambiando a medida que cambien los datos en la tabla.

Este modelo tiene un serio inconveniente. Cada vez que agreguemos o quitemos filas de la base de datos tendremos que corregir manualmente las definiciones de la barra de desplazamiento. Por ejemplo, si agregamos tres meses más y no corregimos la definición del valor máximo de la barra no veremos los datos que acabamos de agregar.

Sólo después de corregir la definición del valor a 25


veremos los nuevos datos.

Podemos hacer que el modelo se actualice automáticamente usando los controles ActiveX en lugar de los de la barra de formularios. Este será el tema de la próxima nota.



Technorati Tags: MS Excel

Comparación rápida de tablas con Datos-Consolidar

Una tarea común en Excel es comparar tablas (o listas) para encontrar diferencias o cambios. En varias notas de este blog hemos mostrado distintas técnicas para hacerlo. Existen varios complementos (Add Ins), algunos gratuitos, para este tipo de tareas.

Hoy mostraremos un pequeño truco.

Cuando queremos comparar qué datos han sido agregados, quitados o cambiados, una posibilidad es usar funciones de búsqueda como BUSCARV o COINCIDIR. Pero esta técnica tiene el inconveniente que sólo puede encontrar lo que existe en la lista de búsqueda o informarnos que lo que buscamos no se encuentra (o no coincide) con un resultado #NA.

Con Datos-Consolidar podemos ir más lejos. Supongamos que recibimos un reporte de ventas y unos días más tarde una segunda versión.




















Como podemos apreciar, en el informe 1 faltan las sucursales 3, 4 y 5; también los datos de las sucursales 1, 2 y 9 son distintos.

El primer paso que damos es cambiar el encabezamiento de la columna B en ambas hojas. En lugar de Ventas pondremos Ventas1 y Ventas2 respectivamente.

El segundo paso es agregar una tercer hoja, “comparación” (podemos darle cualquier nombre que queramos). Seleccionamos la celda A1 de la nueva hoja y abrimos el menú Datos-Consolidar

En el formulario que se abre marcamos las opciones “Fila Superior” y “Columna Izquierda” de “Usar Rótulos en”; en la ventanilla “Examinar” seleccionamos el rango relevante de la hoja Informe1



Apretamos el botón Agregar y seleccionamos el rango relevante en la hoja Informe2

Volvemos a apretar Agregar y luego Aceptar. Excel crea en forma automática esta tabla

Podemos ver con facilidad con facilidad todos los cambios entre ambas listas. Con unas fórmulas sencillas tenemos en segundos un informe detallado de las diferencias

Una complicación puede surgir cuando tenemos más de una columna descriptiva a la izquierda de los datos que queremos comparar. Por ejemplo, si en nuestras tablas además de las sucursales también existe un campo (columna) con la zona



Como Excel consolida en referencia a la columna izquierda, el detalle de las sucursales no es tomado en cuenta. La solución es crear una columna auxiliar concatenando los valores de ambos campos



Enseguida explicaremos por qué separamos los valores con una coma. Ahora volvemos al proceso de consolidar partiendo de la columna auxiliar



El resultado es el siguiente



La coma que hemos agregado en la concatenación nos ayuda a separar la columna auxiliar en las dos originales usando Datos-Texto en columnas.

Primero insertamos una columna en blanco entre las columnas A y B

Luego seleccionamos el rango con valores en la columna A y usamos el menú Datos-Texto en columnas con la opción de separadores “coma”

El resultado:




Todo lo que nos queda por hacer es agregar las fórmulas y los formatos que queramos.

Technorati Tags: MS Excel