Construir un tablero de comandos (Dashboard) en Excel – segunda nota

lunes, diciembre 07, 2009

En la nota anterior sobre cómo crear un tablero de comandos (dashboard) con Excel dijimos que el primer paso es definir cuál es el objetivo, que información debe ser presentada y de qué manera. A partir de ahí tenemos que solucionar tres cuestiones: diseño, datos y mecanismo, temas que serán abordados en una próxima nota.

En esta entrada me centraré en la explicación del modelo de nuestro ejemplo mostrando los elementos más importantes y las fórmulas en uso.

Desde ya, la mejor forma de entenderlo y estudiarlo es descargar y “disecar” el archivo.

Para descargar el archivo hay que seguir este enlace.

El modelo cuenta con siete hojas



Dashboard - Tablero de Comandos Excel

La hoja Dashboard contiene el tablero de comandos y es en la que el usuario interactúa. Esta hoja tiene dos columnas ocultas: A y B

Dashboard - Tablero de Comandos Excel

A y B son columnas auxiliares que controlan los datos visibles en la tabla Detalle por Sucursal

Dashboard - Tablero de Comandos Excel

En el rango B23:B32 ponemos la fórmula

=DERECHA(D23;LARGO(D23)-HALLAR(" ";D23))

cuyo cometido es extraer el número de sucursal.

En el rango A23:A32 ponemos la expresión

D23=0

Al elegir una de las zonas de la lista desplegable, sólo aparecen las sucursales pertenecientes a la zona. Esto hace que las celdas relevantes del rango A23:A32 den el resultado VERDADERO. Ese resultado controla el formato condicional de la tabla que es el responsable de ocultar las celdas de las sucursales que no pertenecen a la zona elegida

Dashboard - Tablero de Comandos Excel

La hoja tiene tres listas desplegables. Las dos superiores, que controlan la fecha de comienzo y fin del período analizado, están hechas usando los controles de la barra de Formularios.

Dashboard - Tablero de Comandos Excel

La lista de valores está controlado por el nombre tdPeriodos que es un rango dinámico basado en la tabla dinámica de la hoja “tdZonas” que analizaremos más adelante. Todos los nombres usados en el modelo están descritos en la hoja “Documentacion”.

La lista desplegable para elegir las zonas en la tabla Detalle por sucursal está basada en un control de la barra Cuadro de Controles (ActiveX).


Dashboard - Tablero de Comandos Excel

Los valores de la lista están controlados por el nombre “Zonas” y el valor elegido es guardado en la celda E18 quedando oculto por el control.
El valor de esta celda determina qué sucursales serán exhibidas en la tabla. Para este objetivo usamos la fórmula

=INDICE(INDIRECTO("col"&$E$18);FILA()-20)


Dashboard - Tablero de Comandos Excel

donde INDIRECTO("col"&$E$18) componen el nombre del rango dinámico que contiene las sucursales de cada zona.
Así si elegimos la zona Norte, por ejemplo, esta expresión da como resultado “colNorte”, cuya definición puede verse en la hoja Documentacion.

Los datos residen en la hoja BD (obviamente, base de datos), donde son captados con alguno de los métodos mencionados en la nota anterior.
Estos datos son elaborados por tablas dinámicas en las hojas “tdZonas” y “tdSucursales”.

Estas hojas son los verdaderos motores del modelo. Cada una de ellas contiene una tabla dinámica (de allí “td”) que totalizan los datos de acuerdo al corte necesario (por zona o por sucursal).

Los rangos de estas tablas están definidos por el mismo rango dinámico (“tdRango”, documentado en la hoja Documentacion) de manera que no haga falta actualizar el rango cada vez que agregamos datos.

Tanto la tabla de Zonas como la de Sucursales utilizan una fórmula compuesta por las funciones SUMA, INDIRECTO y DIRECCION para extraer los datos correspondientes.

La fórmula en la celda E7 es

=SUMA(INDIRECTO(DIRECCION($E$3+5;3;;;"tdZonas")&":"&DIRECCION($E$4+5;3)))

En esta fórmula $E$3 es el número de orden de la fecha de incio (así funciona el control combobox de la barra de Formularios); le sumamos 5 para ubicarnos en la fila correspondiente de la hoja “tdZonas” (ver la explicación en el párrafo siguiente).
Lo mismo con $E$4. Usamos INDIRECTO para convertir el texto resultado de DIRECCION en un rango que la sunción SUMA pueda usar.

Las listas de zonas y sucursales están ubicadas en la hoja “Parametros”.

Los gráficos de la hoja Dashboard son imágenes capturadas con la herramienta Cámara fotográfica de Excel. Los verdaderos gráficos están en la hoja “graficos”.

Dashboard - Tablero de Comandos Excel

Esta técnica nos permite no sólo controlar mejor el diseño de los gráficos sino también documentar el funcionamiento de éstos. Por ejemplo, los nombres en uso en el gráfico del detalle mensual

Dashboard - Tablero de Comandos Excel

El último elemento importante del modelo es una pequeña macro que nos asegura que cada vez que terminamos de agregar datos en la hoja BD, las tablas dinámicas sean actualizadas.
Como sabemos, las tablas dinámicas no tienen un mecanismo de actualización automático. Esta macro es un evento de la hoja BD que es disparado cada vez que desactivamos la hoja



Dashboard - Tablero de Comandos Excel




Technorati Tags:



9 comments:

Ariel 08 diciembre, 2009 23:06  

Primero que todo muchas gracias por la informacion que entregas.
Segundo, las personas que vivimos en America y no tenemos tarjeta de credito, que posibilidad tenemos de obtener el archivo?...

joaquin 09 diciembre, 2009 03:44  

Hola Jorge...estoy muy interesado en revisar tu archivo, sin embargo, no dispongo de euros para pagar. Es posible que se pueda pagar en dolares?. en caso de ser afirmativa tu respuesta, cuanto seria el costo?
Gracias por tu atencion

Jorge L. Dunkelman 09 diciembre, 2009 04:22  

Ariel, creo que PayPal te permite también hacer pagos con otros medios como cuenta bancaria o tarjeta de débito. Te sugiero que te fijes en el sitio de PayPal cuáles son las posibilidades. Por mi parte estoy tratando de averiguar si existen otras posibilidades seguras de aceptar pagos no basados en tarjetas de crédito.

Jorge L. Dunkelman 09 diciembre, 2009 04:28  

Hola Joaquín,
el pago se hace en Euros, pero en tu cuenta aparecerá la suma en la moneda local (o la moneda de tu cuenta). PayPal está convirtiendo los 5 Euros en algo algo así como 7,15 Dólares

Francisco,  09 diciembre, 2009 17:30  

Hola, Jorge:
me interesa mucho este tema, pero tengo problema con la planilla, al ingresar a la hoja BD, me arroja errores ya que en las columnas venta, clientes y ganancia no hay datos lo que provocan datos de error en las tablas dinamicas, dejando el dashboard con error, Saludos

Jorge L. Dunkelman 09 diciembre, 2009 19:33  

Hola Francisco,
el modelo viene con datos que podés reemplazar con los tuyos. Si borrás todos los datos de BD y tratas de actualizar las tablas dinámicas, recibirás un mensaje que no se puede actualizar ya que no hay datos.
De todas maneras, puede ser que el nombre dinámico que define la base de datos (teRango)de las tablas dinámicas se haya corrompido. Podés reestablecerlo con est fórmula

=DESREF(BD!$B$2;0;0;CONTARA(BD!$B:$B);CONTARA(BD!$2:$2))

Si no lográs corregir el problema, mandame el archivo por mail.

Francisco,  11 diciembre, 2009 01:56  

Estimado Jorge:
El problema era que la formula de la tabla estaba en ingles, lo solucioné con la función "aleatorio.ente()".
seguire descubriendo la planilla, excelente trabajo.

Gracias. un Saludo.

Anónimo,  30 diciembre, 2009 00:23  

Hola Jorge, me he descargado tu archivo y he intentado hacerlo yo paso por paso, pero me es imposible....
Podrias publicar los pasos a seguir en orden?
Estoy muy interesada en el tema de los cuadros de mando, creo que son muy útiles.
Un saludo y muchisimas gracias por adelantado!!

Jorge L. Dunkelman 30 diciembre, 2009 07:08  

Más que una nota estoy pensando en armar una animación que muestre los pasos. Pero llevará algún tiempo.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP