Cómo crear un diagrama de Sankey en Excel
Los diagramas de Sankey se utilizan para mostrar el flujo entre dos o más categorías, donde el ancho de cada elemento individual es proporcional al caudal. Estos tipos de gráficos están disponibles en Power BI, pero no están disponibles de forma nativa en Excel. Sin embargo, hoy quiero mostrarles que es posible crear un diagrama de Sankey en Excel con la combinación adecuada de técnicas simples.
Si bien los diagramas de Sankey se usan a menudo para mostrar el flujo de energía a través de un proceso, siendo un experto en finanzas, he decidido mostrar el flujo de efectivo. El diagrama de Sankey simple anterior muestra cuatro flujos de ingresos y cómo ese efectivo luego fluye hacia gastos o ahorros.
Tabla de contenido
- El secreto para crear un diagrama de Sankey en Excel
- Datos iniciales
- Tablas de cálculo provisionales
- Tabla SankeyLines
- Puntos de datos adicionales de la tabla SankeyLines
- Mesa SankeyStartPillars
- Pilares finales
- Rango con nombre de espaciado
- Capas de gráficos
- Crea las líneas Sankey sombreadas individuales
- Crea los pilares Sankey
- Conclusión
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: 0050 Diagramas de Sankey en Excel.xlsx
Tener acceso
Esta publicación de blog será ligeramente diferente de otras; En lugar de mostrarle cómo realizar cada paso, le demostraré el enfoque general. Hay detalles adicionales cubiertos en el video de YouTube.
Si bien debería esperar para revelar el secreto hasta el final de la publicación, creo que será útil explicar cómo encaja todo si lo revelo por adelantado.
Aquí está el secreto: el diagrama de Sankey que ve en el archivo de ejemplo no es un solo gráfico, ni mucho menos. En realidad, hay 21 gráficos, todos apilados uno encima del otro.
La siguiente imagen muestra los 21 gráficos individuales que podrían formar un diagrama de Sankey.
Configurando los fondos para que sean completamente transparentes y las líneas sombreadas como parcialmente transparentes; superponer los gráficos uno encima del otro crea el efecto Sankey. Los pilares en cada extremo son gráficos de columnas 100% apiladas, que también están superpuestas en la parte superior.
Ahora que he revelado lo simple que es este secreto, veamos cada etapa con un poco más de detalle. Luego podrás crear tus propias plantillas de diagramas Sankey.
Datos iniciales
Nuestros datos iniciales son una tabla bidimensional. Las filas son el punto inicial de nuestro diagrama de Sankey, mientras que las columnas son el punto final. El valor en la intersección de estos es el caudal.
Dentro de la plantilla, también tenemos un rango con nombre llamado En blanco . El valor aquí representa el espacio que se aplicará entre cada categoría.
Tablas de cálculo provisionales
Para crear el efecto Sankey a partir de nuestros datos iniciales, necesitamos crear cálculos provisionales.
- Tabla SankeyLines
- SankeyStartMesa de pilares
- Mesa SankeyEndPillar
- Rango con nombre de espaciado
Veamos cada uno por separado.
Tabla SankeyLines
A partir de los datos de origen, creamos una tabla con una fila para cada combinación posible de filas y columnas. Esta tabla se llama tabla SankeyLines en el archivo de ejemplo. Cada categoría de fila está separada por una línea adicional que crea los espacios en blanco que vemos en la gráficos.
Nuestra tabla SankeyLines inicial debe contener la siguiente información básica:
Valor
La fórmula en la columna Valor es:
=SI(IZQUIERDA([@From],5)="En blanco",En blanco,INDEX(SankeyData,MATCH([@From],SankeyData[From / To],0),MATCH([@To],SankeyData[# Encabezados],0)))
Esta fórmula recupera:
- Valor de la tabla fuente
- Valor del rango con nombre en blanco (donde es una fila utilizada para espaciar entre categorías).
Posición final
La columna Posición final determina el orden de las líneas al final del diagrama de Sankey. Donde 1 termina en la parte superior, 2 es el segundo elemento desde arriba, etc.
Puntos de datos adicionales de la tabla SankeyLines
Para crear los datos para el gráfico de áreas 100% apiladas, necesitamos calcular algunos puntos de datos adicionales:
- Espacio sobre la línea sombreada de Sankey
- Valor de la línea sombreada de Sankey
- Espacio debajo de la línea sombreada de Sankey
Nuestra tabla SankeyLines necesita expandirse con los siguientes cálculos.
Las fórmulas de cada columna son las siguientes:
Arriba del inicio
=SUMA(SankeyLines[[#Encabezados],[Valor]]:[@Value])-[@Value]
Esto calcula la cantidad de espacio requerido sobre la línea Sankey en el punto inicial.
Por encima de la mitad 1
=[@[Arriba del inicio]]
Esto se envía para igualar la columna Inicio anterior.
Por encima de mediados de 2
=[@[Arriba del final]]
Esto está configurado para ser igual a la columna Arriba del extremo (ver más abajo)
Por encima del extremo
=SUMA([Valor])-SUMIFS([Valor],[Posición final],"="[@[Posición final]])
Esto calcula la cantidad de espacio requerido sobre la línea Sankey en el punto final.
Valor inicial, valor medio 1, valor medio 2, valor final
=[@Valor]
La altura de la línea sombreada de Sankey nunca cambia. Por lo tanto, podemos establecer el valor de las 4 columnas para que sea igual a la columna Valor.
Por debajo del inicio, por debajo de la mitad 1, por debajo de la mitad 2, por debajo del final
Las columnas calculan la cantidad de espacio requerido debajo de la línea sombreada de Sankey. Cada cálculo se construye de la misma manera. Toma la SUMA ([Valor]) y luego quita el valor de las columnas equivalentes Inicio, Medio 1, Medio 2 o Fin.
Abajo Inicio:
=SUMA([Valor])-[@[Inicio superior]]-[@[Valor inicial]]
Por debajo de Mid 1:
=SUMA([Valor])-[@[Arriba de mitad 1]]-[@[Valor mitad 1]]
Por debajo de mediados de 2:
=SUMA([Valor])-[@[Arriba de mitad 2]]-[@[Valor mitad 2]]
Debajo del final:
=SUMA([Valor])-[@[Fin superior]]-[@[Fin del valor]]
Mesa SankeyStartPillars
La tabla SankeyStartPillar es razonablemente fácil de entender. Solo necesita cada categoría de fila de los datos de origen enumerados con un elemento "en blanco" en el medio.
La fórmula para la columna Valor es:
=SUMIFS(SankeyLines[Valor],SankeyLines[De],[@De])
Pilares finales
La tabla SankeyEndPillar es similar a la tabla SankeyStartPillar. Solo necesita cada categoría de columna de los datos de origen enumerados con un elemento "en blanco" en el medio.
La fórmula para el Valor es:
=SUMIFS(SankeyLines[Valor],SankeyLines[A],[@To])
Rango con nombre de espaciado
La parte final de los cálculos provisionales es un rango con nombre llamado Espaciado. Se utiliza como eje de categorías (horizontal) del gráfico. Determina en qué punto comienza y termina la pendiente del gráfico.
Capas de gráficos
Una vez que todos los cálculos provisionales estén listos, puede comenzar la creación del gráfico.
Crea las líneas Sankey sombreadas individuales
Cada fila de la tabla SankeyLines debe ser un gráfico de áreas 100 % apiladas independiente con 3 series de datos:
Una vez que el gráfico tiene los datos de serie correctos, todo se reduce al formato.
Una vez que se haya completado todo el formato, el gráfico se convertirá en una única línea gris.
Si la fila en la tabla SankeyLines comienza con "En blanco", entonces técnicamente se puede eliminar; sin embargo, para que esté completo, prefiero mantenerla allí, pero configurarla en Sin relleno. Significa que si alguna vez necesito usarlo, puedo cambiar el color de relleno y puedo usarlo como una línea Sankey sombreada estándar.
Una vez que se hayan creado todas las líneas Sankey sombreadas, se pueden superponer unas sobre otras.
Crea los pilares Sankey
La mayor parte del trabajo duro ya se ha completado. Solo necesitamos crear dos gráficos de columnas 100 % apiladas : uno para el pilar inicial y otro para el pilar final.
Los pilares tendrán el siguiente formato:
- Trazar series en orden inverso
- Tus colores de relleno preferidos
- Establecer las secciones "en blanco" sin relleno
- Agregue etiquetas de datos a los puntos relevantes.
Después de todo eso, superamos los pilares en la parte superior de nuestro gráfico. Finalmente, nuestro diagrama de Sankey está completo.
Conclusión
Eso es todo. Individualmente no hay nada demasiado difícil con ninguna de las técnicas involucradas. Pero es necesario combinarlos de la manera correcta para crear el efecto Sankey. Inicialmente, esto lleva bastante tiempo crearlo, pero una vez configurado, se puede usar una y otra vez con diferentes categorías. Definitivamente es el caso de invertir el tiempo una vez, para crear una plantilla lo suficientemente grande y luego reutilizarla.
Artículos Relacionados:
- Guía definitiva: VBA para gráficos en Excel (más de 100 ejemplos)
- Gráficos de columnas de ancho variable e histogramas en Excel
- Crear gráficos de mapas personalizados usando formas y VBA
Deja una respuesta