Como Crear un gráfico de abanico en Excel
Un gráfico de abanico, o gráfico de incertidumbre, como me gusta llamarlos, es una forma de mostrar datos históricos junto con una predicción de valores futuros. Por ejemplo, a menudo se utilizan para indicar predicciones de inflación o tipo de cambio, pero pueden mostrar cualquier dato con un valor futuro incierto. Entonces, ¿cómo podemos crear un gráfico de abanico en Excel? Se parecen a los gráficos de líneas, pero los valores futuros se abren en abanico para representar el rango de posibles valores futuros.
El término “gráfico de abanico” fue acuñado por el Banco de Inglaterra , que comenzó a utilizar este método para mostrar la inflación en 1997.
La siguiente imagen muestra el diagrama de abanico terminado que crearemos en este tutorial.
Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.
Nombre del archivo: 0069 Gráfico de abanico en Excel.zip
Tener acceso
Los datos
El diseño de los datos es uno de los factores más críticos para crear un gráfico en abanico. Si nos equivocamos en esta parte, el gráfico no se verá como esperábamos. Entonces, tomemos un poco de tiempo para entenderlo.
En nuestro ejemplo, hemos tomado 12 períodos de resultados reales (Celdas B7:B18) y 12 períodos de predicción futura (Celdas C19:C30).
Las celdas D3 y E3 se configuran con los valores mínimo y máximo de la predicción. Estos son sólo para ilustración; deberá calcular estimaciones adecuadas para su escenario.
La celda G9 representa el número de ventiladores a mostrar. Debe ser un número impar para garantizar que haya un ventilador central, con un número par de ventiladores arriba y abajo.
Los cálculos
He utilizado una tabla para calcular y almacenar los valores en este escenario. Es posible realizar cálculos utilizando rangos de celdas estándar, pero deberá ajustar las fórmulas en consecuencia.
Los cálculos requeridos son:
Pronóstico final
La celda C3 calcula el último valor de la columna Pronóstico. Usamos esto en algunos de los próximos cálculos.
=ÍNDICE(Datos[Pronóstico],FILAS(Datos[Pronóstico]))
Columna mínima
La fórmula en la columna Min es:
=[@Forecast]+($D$3-$C$3)/COUNT([Forecast])* COUNT(INDEX([Forecast],1):[@Forecast])
Esta es la fórmula más compleja de toda la tabla, así que déjame desglosarla.
La diferencia entre el mínimo y el pronóstico al final del gráfico es -2,2 (D3 menos C3). Esto se calcula en la sección resaltada a continuación.
=[@Forecast]+ ($D$3-$C$3) /COUNT([Forecast])*COUNT(INDEX([Forecast],1):[@Forecast])
Queremos que el tamaño del ventilador aumente cuanto más avanza el gráfico en el futuro. En nuestro escenario, tenemos 12 períodos de pronóstico, así que dividimos el valor calculado anteriormente por el número de períodos de pronóstico (consulte la sección resaltada a continuación).
=[@Forecast]+($D$3-$C$3)/ COUNT([Forecast]) *COUNT(INDEX([Forecast],1):[@Forecast])
Habiendo dividido el valor por el número de períodos, a continuación queremos multiplicarlo por el número de períodos. Esto debe aumentar a medida que avanzamos en la tabla.
El primer período de pronóstico debe calcularse como 1, el segundo período debe calcularse como 2, el tercer período debe calcularse como 3, etc. Logramos esto con un rango dinámico (consulte la sección resaltada a continuación).
=[@Forecast]+($D$3-$C$3)/COUNT([Forecast])* COUNT(INDEX([Forecast],1):[@Forecast])
Finalmente, sumamos el valor previsto (consulte la sección resaltada a continuación) para calcular el punto más bajo del ventilador.
= [@Forecast] +($D$3-$C$3)/COUNT([Forecast])*COUNT(INDEX([Forecast],1):[@Forecast])
máx.
El cálculo del valor máximo es similar al cálculo mínimo. La única diferencia es que el cálculo apunta al valor máximo, en lugar del mínimo. Las diferencias se destacan a continuación.
=[@Forecast]+( $E$3 -$C$3)/COUNT([Forecast])*COUNT(INDEX([Forecast],1):[@Forecast])
Base
La columna Base calcula el área debajo de la línea y el abanico.
=[@Actual]+[@Min]
Tamaño del ventilador
El tamaño de cada ventilador se calcula como:
=([@Max]-[@Min])/$G$3
Crear el diagrama de abanico
Tenemos todos los cálculos; ahora es el momento de crear el gráfico.
Insertar el gráfico
Seleccione todos los datos en las columnas Fecha , Base y Tamaño del ventilador .
Haga clic en Insertar área apilada de gráficos para insertar un gráfico.
Actualmente solo tenemos un ventilador en nuestro gráfico, así que agreguemos 8 más.
- Seleccione la columna Tamaño del ventilador
- Copiar los datos (Ctrl + C)
- Seleccione el área de trazado del gráfico
- Pegue los datos en el gráfico (Ctrl + V)
Repita los pasos anteriores hasta que haya 9 ventiladores.
Elimine la leyenda y el título del gráfico.
El gráfico ahora se ve así:
A partir de ahora, todo es cuestión de formatear.
Formatee la base (la sección azul debajo de la línea y los abanicos)
- Seleccione el área azul inferior del gráfico
- Haga clic derecho y seleccione Formatear serie de datos ... en el menú
- Cambie los formatos para que no tengan relleno ni línea
Formatear el ventilador central
Seleccione el ventilador central.
Formato con relleno sólido y línea continua con color y 0% de transparencia .
Formatear los otros fans
Finalmente, formatee cada abanico con el mismo color que el abanico central, pero con una transparencia cada vez mayor. Cuanto más lejos esté el ventilador del centro, mayor será el valor de transparencia.
En nuestro ejemplo, tenemos 4 ventiladores arriba y 4 ventiladores abajo. Por tanto, los valores de transparencia aplicados son 20%, 40%, 60% y 80%.
El cuadro final
Y hemos terminado. El gráfico final ahora se ve así:
Deja una respuesta