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

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

A su vez, hacer 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 agrega o elimina elementos de los datos de origen subyacentes.

Por lo tanto, crear gráficos interactivos con elementos dinámicos le permite matar dos pájaros de un tiro.

Para empezar, los gráficos interactivos lo ayudan a mostrar de manera ordenada cantidades masivas de datos con un solo gráfico:

Además, convertir algunos elementos del gráfico de estáticos a dinámicos ahorra innumerables horas a largo plazo y evita que se olvide de 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á a crear gráficos interactivos tanto con una lista desplegable como con una barra de desplazamiento, además de dominar las formas de crear el título de su gráfico. y rango dinámico de datos de gráficos.

Data de muestra

Para mostrarte qué es qué, necesitamos algunos datos sin procesar 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 en una hoja de trabajo para que cualquier cambio en el valor en esa celda se refleje automáticamente en el título del gráfico.

Y la belleza de este método es que solo se necesitan unos pocos pasos para cambiar el título de su gráfico de estático a dinámico:

1. Haz doble clic en el título de tu gráfico.
2. Haga clic en el Barra de formulas y escriba "=" en ello.
3. Seleccione cualquier celda para asignarla como su nuevo título de gráfico.

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

Cómo crear un rango de gráfico dinámico

A continuación: configurar un rango de gráfico dinámico. Nuevamente, de manera convencional, cada vez que agrega o elimina elementos del rango de datos utilizado para crear un gráfico de Excel, los datos de origen también deben ajustarse manualmente.

Afortunadamente, puede ahorrarse la molestia de tener que lidiar con todo eso creando un rango de gráfico dinámico que actualiza automáticamente el gráfico cada vez que se modifican los datos de origen.

Esencialmente, hay dos formas 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 idea completa de cómo aprovechar el poder de los rangos de gráficos dinámicos en Excel, consulte el tutorial que cubre todos los entresijos con mayor detalle.

De todos modos, volvamos al método de la tabla. Primero, convierta el rango de celdas que desea usar para construir un gráfico en una tabla.

1. Seleccione todo el rango de datos (A2: G6).
2. Dirígete al Insertar pestaña.
3. Haga clic en el "Mesa" botón.

Si sus datos no tienen encabezados, desmarque la casilla "Mi mesa tiene encabezados" caja.

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

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

1. Resalta la mesa (A2: G6).
2. Navega al Insertar pestaña.
3. Cree cualquier gráfico 2-D, por ejemplo, un gráfico de barras agrupadas (Insertar gráfico de columnas o de barras> Barra agrupada).

¡Voilà! Ya está todo listo. Ahora, pruébelo insertando nuevos elementos en la tabla para verlos automáticamente en el gráfico.

Cómo crear un gráfico interactivo con una lista desplegable

Ahora que ha dado sus primeros pasos en el ámbito 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 tiene muchos datos para graficar, la introducción de funciones interactivas en sus gráficos de Excel, ya sean listas desplegables, casillas de verificación o barras de desplazamiento, lo ayuda a preservar el espacio del tablero y ordenar el diagrama del gráfico mientras lo hace más cómodo para el usuario. para analizar los datos.

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

Simplemente siga los pasos detallados que se describen a continuación para aprender cómo puede hacer lo mismo.

Paso # 1: Sentar las bases.

Antes de comenzar, acceda al Desarrollador pestaña que contiene algunas de las funciones avanzadas de Excel que veremos más adelante. La pestaña está oculta de forma predeterminada, pero solo se necesitan unos pocos clics para agregarla el lazo.

Primero, haga clic derecho en cualquier espacio vacío en el lazo y seleccione "Personaliza la cinta.”

En el Opciones de Excel cuadro de diálogo, navegue hasta el Pestañas principales lista, marque la "Desarrollador"Cuadro y presione"está bien.”

los Desarrollador pestaña ahora debería aparecer en el lazo. En este punto, elija cualquier celda vacía para mostrar la salida del menú desplegable (B8). Esto ayudará a Excel a mostrar los datos correctos según la opción seleccionada en la lista desplegable.

Paso # 2: agregue la lista desplegable.

Nuestro siguiente paso es agregar el menú desplegable a la hoja de trabajo:

1. Ve a la Desarrollador pestaña.
2. Haga clic en "Insertar.”
3. Debajo "Controles de formulario," Seleccione "Caja combo.”

Dibuja ese cuadro combinado vacío donde quieras. Para ilustrar, así es como se ve un menú desplegable vacío:

Paso 3: vincule la lista desplegable a las celdas de la hoja de trabajo.

Para que el menú importe la lista de años en la tabla de datos, debe vincularla al rango de datos respectivo en la hoja de trabajo. Para hacerlo, haga clic con el botón derecho en el cuadro combinado y seleccione "Control de formato.”

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

1. Para "Rango de entrada, ”Resalte todos los valores reales en la tabla de datos ($ A $ 3: $ G $ 6).
2. Para "Enlace de celda, ”Seleccione la celda vacía asignada para almacenar la salida del menú desplegable ($ B $ 8).
3. Para "Líneas desplegables, ”Ingrese el número de puntos de datos en su conjunto de datos (en nuestro caso, cuatro) y haga clic en“está bien.”

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

Paso # 4: Configure la tabla de datos del gráfico.

Dado que el gráfico debe ilustrar solo una serie de datos a la vez, como solución alternativa, configure una tabla auxiliar dinámica que examinará los datos sin procesar y devolverá únicamente las cifras de ventas para el año seleccionado de la lista.

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

Primero, copie el encabezado de la tabla de datos de ventas y deje una fila vacía para mostrar las cifras de ventas filtradas.

Pero, ¿cómo se asegura de que los datos se extraigan correctamente? Ahí es donde entra en juego la función INDICE. Ingrese la siguiente fórmula en A11 y copiarlo en G11:

1	= ÍNDICE (A3: A6, $ B $ 8)

Básicamente, la fórmula se filtra a través de 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: Configure un gráfico basado en los datos del gráfico dinámico.

Has superado la parte más difícil, por lo que todo lo que queda por hacer es crear un gráfico 2-D basado en la tabla de ayuda (resaltar la tabla auxiliar [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 "Pedido," y elige "Traer al frente. " (Alternativamente, haga clic con el botón derecho en el gráfico del gráfico, haga clic en "Pedido," y elige "Enviar al fondo.”)

¡Eso es todo! Allí tiene su 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 agregar una barra de desplazamiento a sus gráficos de Excel es similar a los pasos descritos anteriormente.

Pero, ¿qué sucede 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 ilustrar, eche un vistazo al gráfico de líneas interactivo a continuación haciendo precisamente eso:

¿Cómo lo logras? Bueno, ahí es donde las cosas comienzan a ponerse un poco más complejas. Sin embargo, si se limita al proceso paso a paso que se indica a continuación, seguir las instrucciones debe sentirse como un paseo por el parque, incluso si es un novato.

Paso # 1: Sentar las bases.

Antes de comenzar, agregue el Desarrollador pestaña para el lazo, ya que contiene la función que nos permite dibujar una barra de desplazamiento (haga clic con el botón derecho en cualquier espacio vacío en la cinta de opciones> personalice la cinta de opciones> Pestañas principales> marque la casilla "Desarrollador"> Aceptar).

Para que las cosas funcionen como un reloj, asigne una celda vacía para mostrar la salida de la barra de desplazamiento para que Excel pueda colocar correctamente el cuadro de desplazamiento en la barra de desplazamiento (B8).

Paso # 2: Dibuja la barra de desplazamiento.

Nuestro siguiente paso es insertar la barra de desplazamiento en la hoja de trabajo:

1. Ve a la Desarrollador pestaña.
2. Golpea el "Insertar" botón.
3. Debajo "Controles de formulario," Seleccione "Barra de desplazamiento.”

En este punto, puede dibujar la barra de desplazamiento donde desee. Lo colocaremos en su lugar más tarde.

Paso 2: vincule 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 "Control de formato.”

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

• Valor actual: Este valor define la posición predeterminada del cuadro de desplazamiento en la barra de desplazamiento. Establezca el valor en "1.”
• Valor mínimo: Esta configuración 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 corresponder 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.”
• Cambio incrementado: Este valor determina la suavidad con que el cuadro de desplazamiento cambia de posición cuando lo mueve. 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 cuando hace clic en el área entre el cuadro de desplazamiento y las flechas. Establezca siempre el valor en "1.”
• Enlace de 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: Cree la tabla de datos del gráfico.

Una vez que haya terminado el proceso de configuración, cree una nueva tabla auxiliar para almacenar los datos del gráfico dinámico extraídos de los datos sin procesar. Los datos del gráfico ajustarán automáticamente los valores en función de la posición actual del cuadro de desplazamiento, vinculando efectivamente la barra de desplazamiento al gráfico del gráfico.

Para hacer eso, empieza copiando columna año como se muestra en la captura de pantalla a continuación (A2: A6).

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

Escriba la siguiente fórmula en la celda B10:

1	= ÍNDICE ($ B $ 2: $ G $ 2, $ B $ 8)

Ahora, importe las cifras de ventas correspondientes a la categoría de libros actual ingresando esta fórmula en B11 y copiarloB11: B14):

1	= ÍNDICE ($ 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 primero se filtra a través de columna año ($ A $ 3: $ A $ 6) y elige la fila que coincide con el año correspondiente en la tabla auxiliar (A11). Luego, la fórmula examina 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 mueve el cuadro de desplazamiento.

Paso 4: crea un gráfico basado en la tabla de ayuda.

Una vez que se haya elaborado la tabla de datos del gráfico, simplemente cree un gráfico simple para ver la barra de desplazamiento en acción.

Resalte toda la tabla de ayuda (A10: B14), Ve a la Insertar pestaña y trazar cualquier gráfico 2-D, por ejemplo, un gráfico de líneas (Insertar gráfico de líneas o áreas> Línea 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 en la barra de desplazamiento y seleccione "Pedido, "Y seleccione"Adelantar. " (Alternativamente, haga clic con el botón derecho en el gráfico del gráfico, haga clic en "Pedido," y elige "Enviar al fondo.”)

Y así es como lo haces. 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.