martes, noviembre 17, 2009

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)



dashboard sencillo



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

propiedades del scrollbar

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”

acceso al módulo Vba

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


código del control

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

celdas auxiliares

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.

15 comentarios:

  1. Buen dia, es algo sencillo pero que busque por mucho tiempo sigo fielmente el blog y agradezco todo lo que e aprendido, Gracias Jorge.Csr Parada

    ResponderBorrar
  2. Hola Jorge estoy tratando de crear un listbox, que permita tener las columnas movibles así como en el explorador de Windows, sabes como puedo hacerlo?

    ResponderBorrar
  3. Jair,
    con movibles ¿te referís a que el ancho de la columna cambie de acuerdo al contenido (dinámicas)?

    ResponderBorrar
  4. Por movible quiero decir, que el usuario pueda ajustar el ancho de la columna de acuerdo a sus necesidades.

    ResponderBorrar
  5. Jair,
    el objeto ListBox tiene la propiedad ColumnWidth que te permite determinar el ancho de las columnas programáticamente (Vba) o por intermedio del cuadro de propiedades del objeto.

    ResponderBorrar
  6. Ok. Eso lo entiendo. Puedo definir el ancho a través de VBA y me quedan fijas las columnas; pero lo que requiero es que el usario tenga la facilidad de ampliar o reducir las columnas de acuerdo a su necesidad.

    ResponderBorrar
  7. Jair,
    para eso tendrías que poner un control (por ejemplo un botón) que corra una macro donde el usuario puede establecer el ancho de las columnas

    ResponderBorrar
  8. jorge muchas gracias por publicar este tema es muy interesante y de hecho estaba necesitando algo asi para manejar y gestionar mmis indicadores de calidad, sin embargo trate de hacerlo pero por alguna razon no me sale, creo que el problema esta en la formula Desref que apunta al nombre (inicio), busque el archivo de ejemplo que normalmente subes cuando haces publicaciones pero no hay, que puedo hacer?? aca te dejo el archivo para que lo chekes http://www.esnips.com/web/jcmunevar-Excel o dime de donde puedo descargar el archivo de ejemplo para estudiarlo... Gracias Juan Ka

    ResponderBorrar
  9. Hey jorge cheka el video de laparte inferior de esta pagina, creo que es algo mas complejo no se si puedas hablar de ello en tu blog: http://charts.jorgecamoes.com/how-to-create-an-excel-dashboard/

    ResponderBorrar
  10. Hola Juan,
    En breve pondré el archivo de esta nota para descagar.
    Estoy planificando una serie de notas sobre el tema. Algo similar a lo publicado por Camoes, pero para el público de habla española. Básicamente será una serie de tres notas sobre dashboards que tratarán sobre: diseño, obtención de datos y mecanismo.

    ResponderBorrar
  11. NO hay problema con el archivo, ya lo pude hacer Jorge, solo se trata de leer con calma y entender lo que estas escribiendo.... mi problema estuvo al definir los nombres... pero ya funcion excelente. gracias

    ResponderBorrar
  12. Hola jorge

    Una pregunta, es posible realizar la actualizacion de los datos con el uso de tablas en lugar de utilizar el DESREF.

    Saludos y Gracias por tu Blog

    ResponderBorrar
  13. Can anyone translate it into English??

    ResponderBorrar
  14. Just select English in the dropdown list in "Traducir esta página" (in the right column, on the top).

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.