Excel es una de la herramientas más potentes y utilizadas en el mundo, y sobre todo en consultoría.
Ser capaces de presentar tus resultados es fundamental para poder progresar en consultoría.
Seguro que te ha pasado que tienes un gráfico que actualizas semanal o mensualmente y estás cansado de editar el gráfico para que se actualice...
Por eso hoy quiero compartir contigo un caso de uso básico de Excel muy útil para tus inversiones, control de gastos o seguimiento de incidencias:
¿Cómo crear un gráfico con rango dinámico (rolling graph)?
Si además te interesan los siguientes casos de usos te invito a que profundices en ellos:
Antes de empezar y crear una tabla profunda, me gustaría que tuvieras las bases asentadas:
Conocer las fórmulas INDEX () y MATCH() – recordatorio
Conocer OFFSET() – recordatorio
Teniendo estas bases claras vamos a cubrir cómo crear un gráfico que se actualice automáticamente a medida que añadimos más filas.
Paso 1 – los datos
Esta tabla empieza con el valor "Date" en la celda "A5", de esta forma podréis copiar directamente las fórmulas para que os funcionen:
Paso 2 – Las fórmulas
Antes de crear el gráfico tendremos que crear unas fórmulas que hagan el trabajo de actualizar los datos. Para ello haremos uso de la fórmula OFFSET ().
En la pestaña de “formulas” seleccionamos gestor de etiquetas/nombres (name manager):
En el panel seleccionamos crear nueva:
Le pondremos de nombre “Datos” para referirnos más tarde a esta fórmula, en el renglón inferior “Refers to” utilizaremos la fórmula OFFSET ():
=OFFSET($B$5;COUNT(B:$B);0;-30;1)
Referencia (reference) = la cabecera de la tabla dónde queremos empezar a contar
Filas (rows) = la columna donde están los datos (en nuestro ejemplo el precio de la acción)
Columnas (columns) = 0 (zero) pues no queremos desplazamiento lateral
Altura (height) = -30 pues queremos que del recuento de filas, nos coja las últimas 30
Ancho (width) = 1 pues sólo tenemos una columna
Repetimos la acción de crear un nombre, pero esta vez en el renglón inferior “Refers to” utilizaremos la siguiente fórmula OFFSET ():
=OFFSET(Datos;0;-1)
Esto lo que hará es devolvernos las fechas de cada precio.
Paso 3 – El gráfico
Crearemos un gráfico de línea normal:
Ahora nos toca vincular las etiquetas (names) que hemos creado. Para ello, hacemos clic derecho en el gráfico y seleccionamos datos:
En el panel que nos surge hacemos clic en el “Edit” del bloque izquierdo, esto son los datos del precio de cotización:
Para hacer que los datos cambien cada vez que añadamos y sólo muestren el último mes en el renglón inferior (Series values) añadiremos lo siguiente:
Recuerda que Datos es la primera fórmula offset que creamos.
Ahora editaremos el bloque de la derecha:
En este caso vincularemos a la otra fórmula creada “Titulos”:
Y voilà tenemos nuestro gráfico mostrando 30 días:
Si añades más datos debajo se te actualizará el gráfico:
Si necesitas ampliar conocimientos en Excel apúntate a los cursos de esta página. ¡A mí me han ayudado a mejorar!
¡Suerte!
Diario del Consultor