Cuando nos referimos a crear un gráfico dinámico queremos crear un gráfico cuya cantidad de valores pueda aumentar o disminuir. No nos referimos a un gráfico a partir de una tabla dinámica sino a partir de un listado cuya longitud varíe. Por ejemplo, si en un mismo gráfico queremos ver los n últimos días de un valor en bolsa.
Introducción
Vamos a ilustrar este tema con un ejemplo para facilitarnos su entendimiento. Queremos conseguir un gráfico que, escribiendo en una celda un número de días, cambie el número de días de los que nos muestra valores. Es decir, puedes indicarle que muestre 90 días en la gráfica o 7 días. Para esto necesitaremos que nuestra gráfica se adecue puesto que no es lo mismo enseñar 7 valores que 90 como es lógico. Puedes aprender todo sobre gráficos dinámicos en nuestra mega guía de gráficos dinámicos.
El ejemplo puede encontrarse en el siguiente link: Crear un gráfico dinámico
Para crear un gráfico dinámico deberemos crear un rango dinámico por cada una de las series de datos que tengamos y otro rango dinámico para los valores del eje de las x.
Para crear un rango dinámico usaremos la utilidad de excel de nombres (ver más) y la función DESREF (ver más). Suponiendo que en la columna A tenemos las fechas de los valores bursátiles que usaremos en el eje X y en la columna B los valores bursátiles crearemos dos “nombres” para cada uno. Al del eje x le llamaremos le llamaremos días y a la serie de la cotización la llamaremos valores.
Nombre en el eje X
Como decíamos, vamos a crear un nombre que contenga los días en función del número de días que le indiquemos en una celda aparte, en nuestro ejemplo la celda es F2.
Para crear un nombre vamos a Fórmulas >> Administrador de nombres >> Nuevo
Ponemos un nombre que nos convenza, en nuestro caso hemos usado “Dias” y después introducimos nuestra fórmula en “Se refiere a” como en la siguiente imagen:
La fórmula que hemos añadido es la siguiente:
=DESREF('Rango dinámico'!$A$1 ; 1 ; 0 ; 'Rango dinámico'!$F$2)
Que traducido a un idioma un poco más comprensible significa:
=DESREF( donde apunto inicialmente ; lo bajo una fila; lo muevo 0 columnas ; y le doy un valor para el número total de filas)
Podemos hacerlo de otras maneras pero de esta manera usamos casi todos los argumentos y así es más didáctico.
Serie de valores bursátiles
De manera prácticamente igual a como hemos creado el nombre anterior crearemos un nombre para la serie de los valores bursátiles. A este nombre lo llamaremos Valores y tendrá la siguiente fórmula:
=OFFSET('Rango dinámico'!$B$1;1;0;'Rango dinámico'!$F$2)
que es prácticamente lo mismo que en el caso anterior pero sólo varía el argumento que hemos puesto como “donde apunto inicialmente”, es decir, la cabecera. En la siguiente imagen podemos ver como queda:
Poner rangos dinámicos en la series de un gráfico dinámico
Por último, y una vez que hemos creado nuestros dos nombres con la fórmula DESREF para convertirlos en rangos dinámico, crearemos nuestro gráfico dinámico. Para ello creamos un gráfico vacío del tipo que queramos. En nuestro ejemplo, hemos usado un gráfico de líneas.
Una vez en nuestro gráfico hacemos click con el botón derecho sobre el gráfico y seleccionamos la opción de “Seleccionar datos”. Esto nos llevará a un menú donde podremos elegir agregar una serie. Al darle a agregar volverá a saltar un menú y veremos un pantalla como la siguiente:
Donde pondremos un nombre a nuestra serie y luego, aquí llega lo que no es tan evidente, el nombre de la serie precedido del nombre de la hoja donde se encuentra el rango. No es que esto sea ingeniería nuclear pero es donde la mayoría de la gente suele perderse por dos motivos… uno, no saben que hay que poner el nombre de la hoja y dos, la sintaxis no es tan sencilla. Para poner correctamente el valor de la serie con un nombre creado con la función DESREF habremos de escribir lo siguiente:
='nombre hoja'!Nombre_rango
Fijaros que el nombre de la hoja va entre comillas simples y luego va un símbolo de admiración final “!” seguido de el nombre del rango… ahí está el problema, hay varios detalles difíciles de ver.
Finalmente nuestro resultado es un gráfico que varía en función del número de una celda:
En los gráficos anteriores, lo único que hemos hecho ha sido cambiar el número de días de 120 a 60 y ya hemos obtenido nuestro nuevo gráfico perfectamente “cuadrado”.