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.