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
Excelente tutorial.
ResponderBorrarMis más sinceros agradecimientos.
JLGG
Hola buenas tardes, excelente explicación sólo que no entiendo una parte, donde coloco esto:
ResponderBorrarinicio=control!$B$3
me podría explicar por favor. Gracias de antemano.
"inicio" es un nombre que representa un rango (en este caso la celda B3 de la hoja Control).
ResponderBorrarTe sugiero leer esta nota sobre el uso de nombres en Excel.
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"?
ResponderBorrarPareciera ser que hay un error en tu modelo. Puedes mandarme el archivo (instruciones en el enlace Ayuda, en la parte superior del blog).
ResponderBorrarNo entiendo eso de la formula
ResponderBorrar=DESREF('base de datos'!$C$3;inicio+FILA()-3;0)
especificamente en inicio+FILA()-3;0)
Rodrigo,
ResponderBorrarDESREF 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.
Yo tengo office en ingles y me a costado "un mundo" descifrar esta formula y colocarla en inglés. Me puedes ayudar?
ResponderBorrarAdemás, igual que a Rodrigo, en el dato "FILA ()", que celda se debe colocar? no comprendo esa ultima parte.
Gracias!!
Juan Esteban,
ResponderBorrarDESREF 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.
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?
ResponderBorrarEstimado, 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?
ResponderBorrargracias
Rodrigo,
ResponderBorrarpara saber donde pueda estar tu problema tendrías que mandarme el archivo (fijate en el elnace Ayuda).
Gracias por publicar estas breves ayudas, que son de gran ayuda
ResponderBorrarBuenas tardes estimado Jorge;
ResponderBorrarDe casualidad tendras el archivo para entenderlo? Me surge la duda que hay en la pestaña control, entiendo que el archivo es del 2007 y que posiblemente ya no exista.. Gracias !
Prometo buscar (aunque no puedo prometer encontrar). Si lo encuentro pondré un enlace para la descarga en la nota.
ResponderBorrar