Gráficos Interactivos con Controles en Excel

¿Qué son los Gráficos Interactivos con Controles?

Para nosotros son una frecuente y útil funcionalidad que podemos encontrar en la mayoría de las herramientas de visualización de información que nos permite interactuar con los gráficos a través de sencillos controles que nos permite determinar, por ejemplo, que período de datos deseamos ver.

Aunque por defecto en Excel no contamos con la posibilidad de crear de forma directa graficos interactivos con controles, es muy sencillo incorporar a nuestras gráficas controles básicos que nos provean valor agregado a las labores de análisis incorporando mayor interactividad y flexibilidad, obteniendo resultados como este:

Gráficos interactivos con controles – Caso barras de desplazamiento.

Supongamos que disponemos de datos de un año de las ventas totales de nuestra empresa, pero para no saturar la visual solo queremos graficar las ventas de seis meses de los productos A,B,C,D,E, y F, dejando abierta la posibilidad de consultar datos históricos interactuando con una sencilla barra de desplazamiento.

Básicamente utilizaremos rangos dinámicos con nombre para crear interfaces que controlen que datos deben ser dibujados por nuestros gráficos, vinculando los rangos dinámicos con controles personalizados para permitir al usuario cambiar los datos de los gráficos mediante dichos controles que le permitirán actualizar de forma automática los datos.

C59 - Crear un Gráfico Interactivo

E1: Organizar nuestros datos en columnas.

Como toda buena historia para elaborar nuestros gráficos interactivos con controles debemos comenzar por preparar los datos.

Por ello el primer paso a seguir será tener organizados nuestros datos en columnas como en la imagen anexa.

E2: Crear rangos dinámicos: SCROLL_MESES y SCROLL_PRODUCTOS.

Una vez organizados nuestros datos nuestro siguiente paso será crear dos rangos dinámico con nombre a partir de nuestros datos de partida, es decir, los datos que deseamos graficar. Para ello debemos seleccionar Fórmulas > Nombres definidos > Asignar nombre a un rango >Definir nombre (en versiones anteriores a Excel 2007 la ruta a seguir será Insertar > Nombre > Definir).

En nuestro caso le daremos los nombres SCROLL_MESES, que nos permitirá controlar el cambio progresivo de las leyendas y SCROLL_PERIODO, que nos permitirá establecer los datos a visualizar en nuestros gráficos interactivos con controles.

Para el rango dinámico SCROLL_MESES en el cuadro de texto Se refiere a, escribiremos lo siguiente, empleando la función DESREF:

=DESREF(Hoja1!$A$1;Hoja1!$E$5;0;Hoja1!$D$5;1)

Luego damos clic en Aceptar.

Para el rango dinámico SCROLL_PRODUCTOS en el cuadro de texto Se refiere a, escribiremos lo siguiente, empleando la función DESREF:

=DESREF(Hoja1!$B$1;Hoja1!$E$5;0;Hoja1!$D$5;1)

Para finalizar volvemos a dar clic al botón Aceptar.

La función DESREF nos permitirá usar las celdas $D$5 y $E$5 para forzar los rangos referenciado en SCROLL_PRODUCTOS y SCROLL_MESES a expandirse tanto arriba como abajo en función del número que aparezca en estas celda. $D$5 nos permitirá controla la cantidad de períodos que deseamos visualizar en nuestra gráfica, conforme la celda $E$5 nos permitirá controlar el rango de tiempo que deseamos visualizar.

Por ejemplo, si cambiamos el número que hay en la celda $D$5 por 6, estaremos obligando a que el rango dinámico incorpore las celdas B6:B11.

Si no deseamos que los usuarios vean el contenido de las celda $D$5 y $E$5 siempre podemos ocultar su contenido haciendo clic derecho en el ratón sobre ella y seleccionando la opción Formato de celdas. Luego tan solo nos basta en seleccionar la categoría Personalizado e introducir el formato. Hacemos clic en Aceptar y listo. ¡El contenido de la celda estará oculto!

E3: Crear un gráfico de columnas.

Una vez definido nuestros rangos dinámicos procederemos a insertar un gráfico de columnas. Para ello basta que vayamos a nuestra ficha Insertar >Cuadro Gráficos > Columna.

Luego Vamos a nuestra ficha Herramientas de gráficos > Diseño y se elije Datos > Seleccionar datos, y elegimos nuestra columna de datos de Productos y pulsar Editar.

En el cuadro de texto Valores debemos cambiar a:

=Graficos_interactivos_con_controles.xlsx!SCROLL_PRODUCTOS
Luego cambiamos las etiquetas del eje horizontal, haciendo clic en Editar, y cambiando el rango de rótulos del eje por:

=Graficos_interactivos_con_controles.xlsx!SCROLL_MESES
Con eso ya elaboramos un gráfico interactivo. Sólo nos falta incorporar las funciones de interactividad para culminar este post sobre gráficos interactivos con controles.

E4: Agregar interactividad a nuestros graficos interactivos con controles – barra de desplazamiento.

El último paso es muy sencillo. Iremos a nuestra ficha Programador > Controles > Insertar > Controles de Formulario > Barra de desplazamiento.

Seleccionamos la barra de desplazamiento insertada le damos forma para ajustarla a la gráfica (aunque por defecto esta sea vertical, si dibujamos el control en forma horizonal, Excel interpretará que ésta es la orientación que queremos) y procedemos a configurarla haciendo clic derecho sobre la misma y seleccionando la opción Formato de Control. Luego elegimos la ficha Control y configuramos elvalor mínimo (para este ejemplo 1), el valor máximo (para este ejemplo 60), el valor de incremento (para este ejemplo 1) y la celda con la que lo vamos a vincular, en nuestro caso $E$5.

¡Listo! ¡Siguiendo estos pasos e incorporando tus propias variaciones es posible que configures otros tipos de graficos interactivos con controles!

Reflexión: 

Existen múltiples aplicaciones que podemos darle a los graficos interactivos con controles. Este tipo de técnica puede resultar particularmente útil cuando se trata de elaborar tableros de control, paneles de control o dashboards. Con tan solo cambiar el tipo de gráficos ya es posible crear gráficos interactivos con controles de líneas, por ejemplo.

Fuente

Excellentias - URL: http://www.excellentias.com/tutorial-excel-graficos-interactivos-con-controles/ - Consultada 30/09/2014