Crear gráficos dinámicos en Excel

No hay nada más emocionante para un controlador que presentar sus datos de manera visual. En este tutorial te mostraré cómo crear gráficos dinámicos en Excel. Aprenderás a utilizar fórmulas del administrador de nombres para hacer tus gráficos interactivos y personalizables. El proyecto que encontrarás en esta guía consta de varios pasos que son esenciales para utilizar Excel de manera efectiva.

Principales conclusiones

• Aprenderás a crear un menú desplegable dinámico para los meses.
• Se explica y aplica la fórmula "DESREF".
• Se utiliza el administrador de nombres para crear referencias dinámicas.
• Crearás un gráfico interactivo para diferentes escenarios.

Guía paso a paso

Paso 1: Crear menú desplegable dinámico para los meses

Para comenzar con nuestro gráfico dinámico, debes crear un menú desplegable de los meses. Ve a la pestaña "Datos" y selecciona la opción "Validación de datos". En la ventana emergente, elige el criterio de validez "Lista". Aquí introduce los nombres de los meses como fuente para generar un menú desplegable.

Paso 2: Mostrar selección de números mensuales

Permite determinar el número del mes aplicando la fórmula "COINCIDIR". En este caso, se utiliza el criterio de búsqueda del mes seleccionado en la matriz de búsqueda para encontrar la posición correcta. Así puedes mostrar directamente el número correspondiente al mes, por ejemplo, enero = 1.

Paso 3: Obtener presupuestos

Ahora queremos obtener los presupuestos correspondientes. En este caso, utilizamos la fórmula "BUSCARV" o una alternativa como "ÍNDICE" y "COINCIDIR". En mi ejemplo, rápidamente notarás que la fórmula "DESREF" es la más efectiva para determinar estos valores. Te devuelve una referencia desplazada.

Paso 4: Cálculo acumulado

Para calcular los valores acumulados, utilizamos la fórmula "SUMA". Sumarás los valores de los meses desde enero hasta el mes elegido: la fórmula se complementa con la función "DESREF", para que el rango se ajuste dinámicamente. Así obtendrás el presupuesto acumulado.

Paso 5: Utilizar el administrador de nombres

Ahora llegamos a otro aspecto importante: el administrador de nombres. A través del administrador de nombres, puedes crear y gestionar fórmulas dinámicas. Comienza eliminando todas las entradas existentes para hacer lugar a las nuevas. Luego crea una nueva fórmula para el año 2020 y una para el año anterior, 2019, y nómalas apropiadamente.

Paso 6: Crear más fórmulas

Crea las fórmulas "DESREF" para los meses, similar a los pasos anteriores. Asegúrate de especificar correctamente los parámetros para que los meses del 1 al 12 se representen correctamente. Debes asegurarte de que todas las fórmulas estén bien estructuradas y nombradas.

Paso 7: Crear un gráfico

Ahora puedes crear tu gráfico. Selecciona un gráfico de columnas 2D para representar visualmente la información. Ve a "Seleccionar datos" y agrega la serie para el año 2020. Es importante enlazar las fórmulas de manera precisa a tu gráfico, de modo que los nombres dinámicos del administrador de nombres se procesen correctamente.

Paso 8: Finalizar el gráfico

Para finalizar el gráfico, agrega las etiquetas de datos y ajustes como leyendas y título. Quieres que el gráfico no solo sea informativo, sino también visualmente atractivo. Aquí se requiere creatividad: ajusta líneas y colores para optimizar el gráfico.

Resumen

En este tutorial has aprendido de manera exhaustiva cómo crear gráficos dinámicos en Excel. Has creado un menú desplegable para los meses, has obtenido los valores correspondientes mediante fórmulas y has generado un gráfico interactivo. Al utilizar el Administrador de nombres, obtendrás referencias aún más dinámicas, lo que mejora significativamente la presentación de tus datos.