Cómo Crear Gráficos Interactivos con Elementos Dinámicos en Excel

Este tutorial demostrará cómo crear gráficos interactivos con elementos dinámicos en todas las versiones de Excel: 2007, 2010, 2013, 2016 y 2019.

Un gráfico interactivo es un gráfico que permite al usuario controlar qué series de datos deben ilustrarse en el gráfico, haciendo posible mostrar u ocultar datos mediante el uso de controles de formulario. Los hay de muchas formas, desde botones de opción hasta barras de desplazamiento y listas desplegables.

A su vez, el hecho de que algunos elementos del gráfico sean dinámicos (como el título del gráfico) permite automatizar el proceso de actualización de los gráficos cada vez que se añaden o eliminan elementos de los datos de origen subyacentes.

Así pues, crear gráficos interactivos con elementos dinámicos permite matar dos pájaros de un tiro.

Para empezar, los gráficos interactivos le ayudan a mostrar de forma ordenada grandes cantidades de datos con un solo gráfico:

 

Además, convertir algunos elementos del gráfico de estáticos a dinámicos le ahorrará incontables horas a largo plazo y evitará que olvide actualizar el gráfico cada vez que modifique su tabla de datos.

Dicho esto, este tutorial es un curso intensivo sobre técnicas avanzadas de creación de gráficos en Excel: aprenderás a crear gráficos interactivos tanto con una lista desplegable como con una barra de desplazamiento, y dominarás las formas de hacer dinámicos el título y el rango de datos de tu gráfico.

Datos de Muestra

Para mostrarle qué es qué, necesitamos algunos datos brutos con los que trabajar. Por esa razón, considere la siguiente tabla que ilustra las cifras de ventas de libros de una librería en línea ficticia:

Cómo Crear un Título de Gráfico Dinámico

Primero abordemos la parte fácil: hacer que el título de su gráfico sea dinámico.

Puede hacer que el título sea dinámico vinculándolo a una celda específica de una hoja de cálculo, de modo que cualquier cambio en el valor de esa celda se refleje automáticamente en el título del gráfico.

Y lo mejor de este método es que sólo hay que seguir unos sencillos pasos para convertir el título del gráfico de estático a dinámico:

1. Haga doble clic en el título del gráfico.
2. Haga clic en la barra de fórmulas y escriba “=” en ella.
3. Seleccione cualquier celda para asignarla como nuevo título del gráfico.

 

A primera vista, la idea parece bastante primitiva. Pero apenas hemos arañado la superficie de las cosas que son posibles aplicando esta sencilla técnica. Para obtener más información, consulte el tutorial en profundidad que cubre los títulos de gráficos dinámicos en Excel de forma más exhaustiva.

Cómo Crear un Rango de Gráfico Dinámico

El siguiente paso es crear un rango dinámico de gráficos. De nuevo, convencionalmente, cada vez que se añaden o eliminan elementos del rango de datos utilizado en la construcción de un gráfico de Excel, los datos de origen deben ajustarse manualmente también.

Por suerte, puede ahorrarse la molestia de tener que ocuparse de todo eso creando un rango dinámico de gráficos que actualice automáticamente el gráfico cada vez que se modifiquen los datos de origen.

Esencialmente, hay dos maneras de hacerlo: el método de tabla y la técnica de rangos con nombre. Vamos a cubrir brevemente el método de tabla más fácil aquí, pero para obtener una comprensión completa de cómo aprovechar el poder de los rangos de gráficos dinámicos en Excel, echa un vistazo al tutorial que cubre todos los entresijos en mayor detalle.

De todos modos, volvamos al método de tabla. En primer lugar, convierta el rango de celdas que desea utilizar para construir un gráfico en una tabla.

1. Selecciona todo el rango de datos(A2:G6).
2. Ve a la pestaña Insertar.
3. Haz clic en el botón “Tabla”.

 

Si tus datos no tienen cabeceras, desmarca la casilla “La tabla tiene encabezados”.

 

Una vez ahí, el rango de datos se convertirá en una tabla.

 

Ahora, todo lo que tiene que hacer es trazar su gráfico utilizando la tabla como fuente de datos del gráfico:

1. Resalte la tabla(A2:G6).
2. Vaya a la pestaña Insertar.
3. Crea cualquier gráfico 2D, por ejemplo, un gráfico de Barras agrupadas (Insertar gráfico de columnas o de barras > Barras agrupadas).

 

¡Listo! Ya está todo listo. Ahora, pruébalo insertando nuevos elementos en la tabla para ver cómo se reajustan automáticamente en el gráfico.

 

Cómo Crear un Gráfico Interactivo con una Lista Desplegable

Ahora que has dado tus primeros pasos en el reino de las herramientas avanzadas de visualización de datos en Excel, es hora de pasar a la parte difícil: los gráficos interactivos de Excel.

Cuando se tienen muchos datos que representar, la introducción de funciones interactivas en los gráficos de Excel (ya sean listas desplegables, casillas de verificación o barras de desplazamiento) ayuda a preservar el espacio del panel de control y a despejar el gráfico, al tiempo que facilita al usuario el análisis de los datos.

Por ejemplo, observe el gráfico de columnas que se muestra a continuación. En lugar de mostrar un gráfico desordenado que contenga toda la tabla de datos, optar por este gráfico interactivo con una lista desplegable permite al usuario ampliar las cifras de ventas de un determinado año con sólo dos clics.

Siga los pasos detallados que se indican a continuación para saber cómo puede hacer lo mismo.

Paso #1: Sentar las Bases.

Antes de empezar, accede a la pestaña Programador que contiene algunas de las funciones avanzadas de Excel que vamos a utilizar más adelante. La pestaña está oculta por defecto, pero basta con hacer unos pocos clics para añadirla a la cinta de opciones.

Primero, haz clic con el botón derecho en cualquier espacio vacío de la cinta y selecciona “Personalizar la cinta”

 

En el cuadro de diálogo Opciones de Excel, desplázate hasta la lista Pestañas principales , marca la casilla “Programador” y pulsa “Aceptar”

 

La pestaña Programador debería aparecer ahora en la cinta de opciones. En este punto, elige cualquier celda vacía para mostrar la salida del menú desplegable(B8). Esto ayudará a Excel a mostrar los datos correctos en función de la opción seleccionada en la lista desplegable.

 

Paso #2: Añadir la Lista Desplegable.

Nuestro siguiente paso es añadir el menú desplegable a la hoja de cálculo:

1. Ve a la pestaña “Programador”.
2. Haz clic en “Insertar”
3. En “Controles de formulario”, selecciona “Cuadro combinado”

 

Dibuja ese cuadro combinado vacío donde quieras. Para ilustrarlo, aquí tienes el aspecto de un menú desplegable vacío:

 

Paso #3: Vincular la Lista Desplegable a las Celdas de la Hoja de Cálculo.

Para que el menú importe la lista de los años de la tabla de datos, tienes que vincularlo al rango de datos respectivo de la hoja de cálculo. Para ello, haz clic con el botón derecho en el cuadro combinado y elige “Formato de control”

 

En el cuadro de diálogo, haz lo siguiente:

1. En “Rango de entrada”, resalte todos los valores reales de la tabla de datos($A$3:$G$6).
2. En “Vincular con la celda”, seleccione la celda vacía asignada para almacenar la salida del menú desplegable($B$8).
3. Para “Líneas de unión verticales”, introduzca el número de puntos de datos en su conjunto de datos (en nuestro caso, cuatro) y haga clic en “Aceptar”.

 

Esta acción debería dar como resultado que los nombres de los puntos de datos se añadan a la lista desplegable mientras que el valor de la celda de ayuda(B8) caracteriza el elemento actualmente seleccionado del menú como opción 1, 2, 3 o 4:

 

Paso #4: Configurar la Tabla de Datos del Gráfico.

Dado que el gráfico debe ilustrar sólo una serie de datos a la vez, como solución alternativa, configure una tabla de ayuda dinámica que filtre los datos sin procesar y devuelva únicamente las cifras de ventas del año seleccionado de la lista.

Esta tabla se utilizará finalmente como fuente de datos del gráfico, lo que permitirá al usuario cambiar de vista con el menú desplegable.

En primer lugar, copie la cabecera de la tabla de datos de ventas y deje una fila vacía para mostrar las cifras de ventas filtradas.

 

Pero, ¿cómo asegurarse de que los datos se extraen correctamente? Aquí es donde entra en juego la función INDICE. Introduce la siguiente fórmula en A11 y cópiala hasta G11:

=INDICE(A3:A6; $B$8)

Básicamente, la fórmula filtra la columna Año(columna A) y devuelve únicamente el valor de la fila cuyo número corresponde al elemento seleccionado actualmente en la lista desplegable.

 

Paso #5: Configurar un gráfico Basado en los Datos del Gráfico Dinámico.

Ya ha superado la parte más difícil, así que lo único que le queda por hacer es crear un gráfico 2D basado en la tabla deayuda (resalte la tabla de ayuda [A10:G11] > Insertar > crear un gráfico de columnas agrupadas ).

 

Como toque final, si desea que el menú desplegable se coloque en la parte superior del gráfico, haga clic con el botón derecho en el cuadro combinado, haga clic en “Ordenar” y elija “Traer al frente” (Como alternativa, haga clic con el botón derecho en el gráfico, haga clic en “Ordenar” y elija “Enviar al fondo”)

 

Ya está Ya tienes tu gráfico de columnas interactivo vinculado al menú desplegable.

 

Cómo Crear un Gráfico Interactivo con una Barra de Desplazamiento

A decir verdad, el proceso de añadir una barra de desplazamiento a sus gráficos de Excel es similar a los pasos descritos anteriormente.

Pero, ¿qué ocurre si desea analizar la dinámica de ventas de cada categoría de libros (las columnas), en lugar de mostrar las cifras de ventas anuales (las filas)? Para ilustrarlo, eche un vistazo al siguiente gráfico de líneas interactivo:

 

¿Cómo hacerlo? Bueno, aquí es donde las cosas empiezan a ponerse un poco más complejas. Sin embargo, si te ciñes al proceso paso a paso que se describe a continuación, seguir las instrucciones debería parecerte un paseo por el parque, incluso si eres un completo novato.

Paso #1: Sentar las Bases.

Antes de empezar, añade la pestaña Programador a la cinta de opciones, ya que contiene la función que nos permite dibujar una barra de desplazamiento (haz clic con el botón derecho en cualquier espacio vacío de la cinta de opciones > personalizar la cinta de opciones > pestañas principales > marca la casilla “Programador” > Aceptar).

Para que todo funcione como un reloj, asigna una celda vacía para mostrar la salida de la barra de desplazamiento, de modo que Excel pueda colocar correctamente el cuadro de desplazamiento en la barra de desplazamiento(B8).

 

Paso #2: Dibujar la Barra de Desplazamiento.

Nuestro siguiente paso es insertar la barra de desplazamiento en la hoja de cálculo:

1. Ve a la pestaña Programador.
2. Pulsa el botón “Insertar”.
3. En “Controles de formulario”, selecciona “Barra de desplazamiento”

 

En este punto, puedes dibujar la barra de desplazamiento donde quieras. La colocaremos en su sitio más adelante.

Paso #2: Vincular la Barra de Desplazamiento a los Datos de la Hoja de Trabajo.

Haga clic con el botón derecho en la barra de desplazamiento y seleccione “Formato de Control”

 

En el cuadro de diálogo Formato de Control, modifique la configuración de la barra de desplazamiento:

• Valor actual: Este valor define la posición por defecto del cuadro de desplazamiento en la barra de desplazamiento. Establezca el valor en “1”
• Valor mínimo: Este ajuste significa el valor más bajo de la barra de desplazamiento. En nuestro caso, escriba “1” en el campo.
• Valor máximo: Este parámetro determina el valor más alto de la barra de desplazamiento y debe corresponderse con el número de puntos de datos que se utilizarán para trazar el gráfico interactivo. Dado que la tabla de datos se compone de seis categorías de libros, cambie el valor a “6”
• Incremento: Este valor determina la suavidad con la que el cuadro de desplazamiento cambia de posición al moverlo. Establezca siempre el valor en “1”
• Cambio de página: Este valor determina la suavidad con la que el cuadro de desplazamiento cambia de posición al hacer clic en la zona situada entre el cuadro de desplazamiento y las flechas. Ajuste siempre el valor a “1”
• Vincular con la celda: Este valor caracteriza la posición actual del cuadro de desplazamiento. Simplemente resalte la celda en la hoja de trabajo previamente asignada para este propósito($B$8).

 

Paso #3: Crear la Tabla de Datos del Gráfico.

Una vez finalizado el proceso de configuración, cree una nueva tabla auxiliar para almacenar los datos dinámicos del gráfico extraídos de los datos brutos. Los datos del gráfico ajustarán automáticamente los valores en función de la posición actual del cuadro de desplazamiento, vinculando así la barra de desplazamiento al gráfico.

Para ello, comience copiando la columna Año como se muestra en la siguiente captura de pantalla(A2:A6).

 

Una vez allí, despliegue la siguiente función INDICE que recoge el rango de celdas que contiene los nombres de las categorías de libros y devuelve un valor correspondiente basado en la posición del cuadro de desplazamiento.

Escribe la siguiente fórmula en la celda B10:

=INDICE($B$2:$G$2; $B$8)

 

Ahora, importa las cifras de ventas correspondientes a la categoría de libro actual introduciendo esta fórmula en B11 y cópiala hacia abajo(B11:B14):

=INDICE($B$3:$G$6; COINCIDIR(A11;$A$3:$A$6;0); COINCIDIR($B$10; $B$2:$G$2;0))

La combinación de las funciones INDICE y COINCIDIR ayuda a devolver con precisión el valor del número de fila y columna que coincide con los criterios especificados.

La fórmula filtra primero la columna Año($A$3:$A$6) y selecciona la fila que coincide con el año correspondiente en la tabla auxiliar(A11). A continuación, la fórmula filtra los nombres de las categorías de libros de la misma manera($B$2:$G$2). Como resultado, la fórmula devuelve el valor dinámico correcto que se ajusta cada vez que se mueve el cuadro de desplazamiento.

 

Paso #4: Crear un Gráfico Basado en la Tabla de Ayuda.

Una vez armada la tabla de datos del gráfico, construya un gráfico simple para ver la barra de desplazamiento en acción.

Resalte toda la tabla de ayuda(A10:B14), vaya a la pestaña Insertar y trace cualquier gráfico 2D, por ejemplo, un gráfico de líneas (Insertar gráfico de líneas o de áreas > Líneas con marcadores).

 

NOTA: Si desea que la barra de desplazamiento se muestre en la parte superior del gráfico, haga clic con el botón derecho del ratón en la barra de desplazamiento, elija “Orden” y seleccione “Traer al Frente” (Como alternativa, haga clic con el botón derecho en el trazado del gráfico, haga clic en “Ordenar” y seleccione “Enviar al fondo”)

Y así es como se hace. Acaba de aprender todo lo que necesita saber sobre las poderosas técnicas avanzadas que mejorarán su juego de visualización de datos a pasos agigantados.