Gráfico interactivo a partir de un valor de celda

Supongamos que tenemos este cuadro de ventas por año y por departamento de una empresa imaginaria



El gráfico al pie de la tabla muestra las ventas por año del Departamento 1.

Nuestro objetivo es convertir este gráfico en un gráfico interactivo, de manera que cuando seleccionamos una celda del rango A3:A12, el gráfico muestre las ventas del departamento seleccionado.

Para lograr esto usaremos las siguientes técnicas:

1 - Uso de nombres en la fórmula SERIES del gráfico, como mostramos en la nota sobre gráficos Excel dinámicos;

2 - Programación de eventos de la hoja que contiene la tabla de datos y el gráfico. Quien no esté familiarizado con la programación de eventos en Excel puede leer mis notas sobre el tema en mi blog sobre Excel.

Empezamos por definir los nombres

Departamento =DESREF(Hoja1!$A3;0;0)

Datos =DESREF(Hoja1!$A3;0;1;1;4)


Al definir los nombres, la celda activa tiene que ser la celda A3 (en nuestro ejemplo, por supuesto). Además debemos prestar atención al hecho que la dirección de la celda es semi-variable (absoluta para la columna y variable para la fila).



Una vez definidos los nombres, reemplazamos el rango del título y el rango de los datos con los nombre. Antes de reemplazar los rango, la fórmula SERIES se ve así



Para reemplazar los rangos, seleccionamos la series de datos en el gráfico y abrimos el diálogo de Datos de origen



En la casilla Nombre, dejamos la referencia Hoja1! Y reemplazamos A3 por el nombre "Departamento". En la casilla Valores hacemos lo mismo con "Datos"



Ahora, si seleccionamos un departamento en el rango A3:A12 y apretamos F9 para forzar a Excel a recalcular la hoja, veremos que el gráfico muestra los datos del departamento elegido.
Nuestro gráfico, si bien es interactivo, es "semiautomático" ya que debemos apretar F9 para que se produzcan los cambios.
Para automatizar completamente nuestro gráfico programamos un evento para la Hoja1 (en nuestro ejemplo).
En la pestaña de la hoja apretamos el botón derecho del mouse y elegimos la opción "ver código"



Esto nos lleva al módulo de la hoja en el editor de Visual Basic. Aquí ponemos este código

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Union(Range("A3:A12"), Target).Address = Range("A3:A12").Address _
Then ActiveSheet.Calculate
End Sub

Este código programa el evento SelectionChange, es decir, cada vez que seleccionamos una celda, si ésta está dentro del rango A3:A12, la hoja será recalculada.

Con esto nuestro gráfico es totalmente automático y cada vez que seleccionemos un departamento, los datos relevantes aparecerán representados en el gráfico.

El archivo con el ejemplo se puede descargar aquí.

Gráficos tipo pirámide.

Los gráficos de tipo pirámide, que podemos llamar por su apariencia también embudo o tornado, se usan por lo general para comparar las características de dos poblaciones.
Este tipo de gráficos son utilizados en especial en el área de la demografía, por ejemplo, para mostrar la distribución de la población de un país. Un ejemplo, tomado de Wikipedia, muestra la distribución de la población de Angola por grupos de edad y según hombres y mujeres



Si invertimos la pirámide veremos que toma la forma de un embudo o tornado.

Otro ejemplo es éste, que muestra la distribución de la población de la Argentina en el 2005



En esta nota veremos cómo crear este tipo de gráficos en Excel. Empecemos por considerar los datos de la población de la Argentina en el año 2000



El primer paso es agregar dos columnas auxiliares a nuestra tabla, mostrando el porcentaje de cada intervalo de edad. En nuestro ejemplo, los porcentajes de la población masculina están calculados como números negativos



Ahora creamos un gráfico de barras apiladas usando los datos de las columnas C y E y los de la columna A para los rótulos de las categorías (no incluir la línea del Total)



Ahora haremos algunas correcciones a este gráfico:

- Quitamos el fondo y los bordes del área de trazado.
- Ubicamos la leyenda en la parte inferior del área del gráfico.
- Seleccionamos una de las series y abrimos el menú de formato de series de datos. En la pestaña Opciones, ponemos 0 en Ancho del rango.
- Seleccionamos el eje de las categorías (vertical), abrimos el menú de formato de ejes y en la pestaña Tramas ponemos "inferior" como valor de "Rótulos de marca de graduación".
- En el menú Opciones del gráfico, en la pestaña líneas de división, marcamos la opción Líneas de división principales del eje de las categorías.
- Seleccionamos el eje de valores Y (horizontal), abrimos el menú de formato del eje y en la pestaña Número ponemos este formato personalizado: 0,0%;0,0%;0,0%. Esto hará que los números negativos aparezcan como si fueran positivos.

El resultado final será



Para invertir la pirámide seleccionamos el eje de las categorías, abrimos el menú de formato del eje y en la pestaña Escala marcamos las opciones Categorías en orden inverso y Eje de valores (Y) cruza en máxima categoría



El archivo con el ejemplo se puede descargar aquí

Gráfico dinámico a partir de una matriz

En mi blog sobre Excel acabo de publicar una nota sobre como convertir una matriz de valores a una única columna o fila.
Para escribir el artículo he estado consultando la página de Chip Pearson . Allí se menciona la posibilidad de crear gráficos dinámicos a partir de una matriz de datos, extrayendo los datos de una columna o una fila determinada de la matriz con fórmulas.

Supongamos esta matriz de datos sobre tres personas a lo largo de tres años (no importa qué representan los datos).



Podemos representar todos los datos en un gráfico de columnas agrupadas, con los datos por filas



o por columnas



Podemos crear un gráfico dinámico en el cual elegimos el nombre de la persona y veremos sus datos a lo largo de los años.

Empezamos creando un nombre que contiene el rango de la matriz

matriz =ejemplo!$A$1:$D$4

y una lista desplegable con Validación de Datos en la celda B7



Luego ponemos esta fórmula matricial en el rango A8:D8

=DESREF($A$1;COINCIDIR(B7;$A$2:$A$4;0);0;1;COLUMNAS(matriz))

En el rango A8:D8 veremos los valores de la matriz que corresponden a la fila del nombre elegido en B7



A partir de estos datos construimos un gráfico de columnas agrupadas



Este gráfico es dinámico ya que al cambiar la selección de nombre en B7, el gráfico se actualizará con los datos de la fila correspondiente al nombre elegido.

Si queremos representar los datos según las columnas, es decir por año en lugar de por nombre tendremos que usar esta fórmula

=TRANSPONER(DESREF($A$1;0;COINCIDIR(B29;$B$1:$D$1;0);FILAS(matriz);1))

Usamos la función TRANSPONER para poder exhibir los datos extraídos en un rango horizontal. Si no usamos TRANSPONER tendremos que copiar la fórmula en un rango vertical.



El archivo con el ejemplo se puede descargar aquí