Cambiar gráfico de Excel con una lista desplegable

Hoy demostraré cómo tener el control sobre el contenido de un gráfico de Excel utilizando una lista desplegable. A través de la lista desplegable podremos decidir el rango de datos que deseamos mostrar dentro del gráfico.

Observa la siguiente animación para tener una idea clara del resultado que obtendremos al finalizar este ejercicio:

El gráfico de Excel original

Originalmente tenía un reporte de ventas de cada uno de los trimestres del año y cada una de las diferentes regiones de una empresa.

Lo que deseamos hacer con este gráfico es separar cada una de las regiones y controlar su presentación a través de una lista desplegable de manera que se muestre una sola región a la vez sin necesidad de crear cuatro gráficos diferentes.

Crear la lista desplegable

El primer paso será crear una lista desplegable. Para ello debemos ir a la ficha Programador y agregar un control de formulario conocido como Cuadro combinado.

Una vez que hayas agregado el Cuadro combinado a la hoja de Excel debes hacer clic derecho sobre el control y seleccionar la opción Formato de control. En este cuadro de diálogo se debe especificar el Rango de entrada que es el rango donde se encuentran los nombres de las regiones. Para nuestro ejemplo ese rango es el A2:A5.

Adicionalmente he especificado la celda A7 como la celda vinculada con este control de manera que dicha celda despliegue en todo momento el elemento seleccionado de la lista. Observa cómo se comporta la lista con estos parámetros, en especial observa la celda A7 que cambiará de acuerdo a la selección:

Obtener los valores a graficar

A través de lista desplegable y la celda A7 sabemos la región que se desea graficar. Ahora será necesario obtener los datos de dicha región para poder crear la gráfica y para ello utilizaremos la función INDICE.

La función INDICE nos ayuda a obtener una celda dentro de una matriz con solo indicar la fila que necesitamos. Por ejemplo, para obtener el nombre de la región que tiene la posición especificada en la celda A7 podemos utilizar la siguiente fórmula:

=INDICE(A2:A5, $A$7)

Una vez que hemos creado esta fórmula podemos asegurar lo siguiente. Cuando seleccionemos un valor de la lista desplegable se modificará automáticamente el valor de la celda A7 y por lo tanto también se modificará el valor de la función INDICE al traer el nombre de la región correspondiente.

El paso final para obtener los datos a graficar es copiar la fórmula que he colocado en la celda A8 hacia la derecha de manera que podamos obtener también los valores de todos los trimestres para la región:

Finalmente observa cómo al copiar la fórmula hacia la derecha, en la fila 8 de la hoja, obtengo todos los datos correspondientes a la región. Estos datos cambiarán automáticamente al seleccionar un nuevo valor de la lista:

Crear el gráfico

Como tal vez lo puedes imaginar, solo debemos crear un gráfico que tenga su fuente de datos en las celdas de la fila 8 que son las que cambian con la selección de la lista. Así que solo selecciono el rango A8:E8 e inserto un gráfico de barras.