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

lunes, noviembre 16, 2009

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)



dashboard sencillo

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:



base de datos en hoja Excel

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”.

cuadro de control en hoja de Excel

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

selección de barra de desplazamiento en Excel

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

formulario del 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”)
formulario delas propiedades del control

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
gráfico dinámico en el dashboard

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
correción del valor Max en las porpiedades del control

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:


13 comments:

Anónimo,  08 septiembre, 2010 23:16  

Excelente tutorial.
Mis más sinceros agradecimientos.

JLGG

Anónimo,  11 enero, 2011 23:30  

Hola buenas tardes, excelente explicación sólo que no entiendo una parte, donde coloco esto:

inicio=control!$B$3

me podría explicar por favor. Gracias de antemano.

Jorge L. Dunkelman 12 enero, 2011 07:13  

"inicio" es un nombre que representa un rango (en este caso la celda B3 de la hoja Control).
Te sugiero leer esta nota sobre el uso de nombres en Excel.

Anónimo,  12 enero, 2011 21:58  

Gracias por la respuesta. Ya verifique la parte de uso de nombres y entendí. Ahora bien, sigo con una duda, en la celda B3 de la hoja Control me aparece un dato que se genera solo al momento cuando comienzo a manipular la barra de desplazamiento, cuando llego al final de la barra me aparece el dato B3 de la hoja de control al final del gráfico (el dato es este: ENE-00), y se genera un error en la tabla de los datos. Me puedes ayudar en decirme ¿qué datos contiene la hoja "Control"?

Jorge L. Dunkelman 13 enero, 2011 18:51  

Pareciera ser que hay un error en tu modelo. Puedes mandarme el archivo (instruciones en el enlace Ayuda, en la parte superior del blog).

Rodrigo 21 enero, 2011 17:54  

No entiendo eso de la formula
=DESREF('base de datos'!$C$3;inicio+FILA()-3;0)

especificamente en inicio+FILA()-3;0)

Jorge L. Dunkelman 22 enero, 2011 07:50  

Rodrigo,
DESREF es una función que crea una referencia a una celda (o a un rango) en forma dinámica. Para más información sobre la función te sugiero leer esta nota.
Como pongo en la nota, "inicio" es un nombre que se refiere a la celda B3 de la hoja "control". Puedes leer esta nota sobre el uso de nombres en Excel.
FILA() da el número de fila de la celda donde se encuentra la fórmula.
Espero que todo esto te ayude a entender la fórmula.

Juan Esteban 29 enero, 2011 01:00  

Yo tengo office en ingles y me a costado "un mundo" descifrar esta formula y colocarla en inglés. Me puedes ayudar?

Además, igual que a Rodrigo, en el dato "FILA ()", que celda se debe colocar? no comprendo esa ultima parte.


Gracias!!

Jorge L. Dunkelman 29 enero, 2011 07:12  

Juan Esteban,

DESREF es OFFSET en inglés; FILA es ROW.
FILA() es una función, no un dato, y no lleva ningún argumento en nuestro caso. El resultado de la función FILA() es el número de fila de la celda en que se encuentra.

Rodrigo 25 febrero, 2011 17:20  

Una consulta, segui todos tus paso pero tengo un problema. Al desplazar la barra de desplazamiento los datos solamente se desplazan en la fila 3. Se entiende?

Rodrigo 25 febrero, 2011 17:22  

Estimado, hice todo lo del manual, pero tengo problemas al desplazar con la barra. Ocurre que bajo y los datos solamente cambian en la fila 3 y de la fila 4 hacia bajo fijos. por que?

gracias

Jorge L. Dunkelman 25 febrero, 2011 17:56  

Rodrigo,
para saber donde pueda estar tu problema tendrías que mandarme el archivo (fijate en el elnace Ayuda).

Anónimo,  16 mayo, 2011 18:08  

Gracias por publicar estas breves ayudas, que son de gran ayuda

Publicar un comentario

Seguidores

Google+ Followers

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP