Gráficos dinámicos según valor de lista desplegable

Los más memoriosos lectores de este blog recordarán seguramente aquella nota que describía la técnica para crear un gráfico interactivo según el valor de la celda activa (la nota completa aparece en mi blog sobre gráficos, actualmente inactivo). También recordarán que esa técnica no funciona en las versiones posteriores a Excel 2003.

En esta nota mostraré una técnica que funciona con todas las versiones de Excel. El modelo es distinto y se basa en los valores de una lista desplegable. En esta nota mostraré un modelo sencillo y señalaremos sus limitaciones. En las próximas notas veremos otras soluciones que superaran esas limitaciones.

Y yendo al grano, supongamos esta matriz que muestra las ventas por trimestres de los vendedores de una firma

Queremos crear un modelo que permita representar en gráfico las ventas por vendedor, eligiéndolos de una lista desplegable. Las técnicas para lograr esto ya han sido expuestas en este blog en el pasado, por lo que haremos una explicación sucinta.

Lo que queremos lograr es esto:



Lo primero es crear un nombre que se refiera al rango que contiene los nombres de los vendedores. Lo hacemos fácilmente usando la opción “crear desde la selección” del grupo “nombres definidos” en el menú “Fórmulas”

Creamos la lista desplegable en la celda C5 y definimos el nombre “grfTitulo” que se refiere a esta celda. Para crearlo usamos el cuadro de nombres (seleccionamos al celda, escribimos el nombre en el cuadro y apretamos Enter)

Ahora definimos un nombre para cada uno de los rangos que contienen las ventas de los distintos vendedores. Esto también lo haremos usando la opción “crear desde la selección”

Ahora podemos usar los nombres de los vendedores para definir los rangos de los valores que queremos ver en el gráfico. Pero aquí se nos presenta un problema. Dado que los espacios no están permitidos en los nombres definidos, Excel crea los nombres poniendo un “_” (underline) entre el nombre y el apellido. El valor que obtenemos de la lista desplegable no contiene el guión. La solución es transformar el valor obtenido de la lista desplegable agregándole el guión. Esto la hacemos con la función SUSTITUIR en una celda oculta (en este ejemplo en la celda A5)

El próximo paso es crear un gráfico, de líneas en nuestro caso, usando la primer fila de la tabla

Para que nuestro gráfico sea dinámico creamos este nombre definido

grfSeriesX =INDIRECTO(reporte!$A$5)

La función INDIRECTO interpreta el texto en la celda A5 y lo convierte en el rango que hemos definido previamente.

Ahora reemplazamos los rangos en la función SERIES del gráfico de la siguiente manera

Para ver los nombres definidos apretamos F3.

Nuestro modelo funciona de la siguiente manera:

• Elegimos un valor de la lista deplegable
• El valor es transformado en la celda A5
• La función INDIRECTO en el nombre grfSeriesX lo transforma en el rango del vendedor elegido
• El nombre grfTitulo pone el rango que contiene el nombre del vendedor de manera que aparezca en el título del gráfico.

Este modelo tiene varias limitaciones; la más grave es que si agregamos trimestres y/o vendedores tenemos que modificar los nombres definidos. Podemos, por supuesto, crear nombres dinámicos con DESREF o INDICE como ya hemos mostrado en varias oportunidades en este blog. Pero hay soluciones mejores que mostraremos en las próximas notas.

El archivo con el ejemplo se puede descargar aquí.