Cómo construir un gráfico de cascada

Cada vez me encuentro más a menudo en los informes de diferentes empresas y escucho solicitudes de los alumnos para explicar cómo se construye un diagrama de cascada de desviaciones: también es una "cascada", también es una "cascada", también es un "puente". ”, también es un “puente”, etc. . Se ve algo como esto:

Desde la distancia, realmente parece una cascada de cascadas en un río de montaña o un puente colgante: quién ve qué 🙂

La peculiaridad de tal diagrama es que:

• Vemos claramente el valor inicial y final del parámetro (la primera y la última columna).
• Los cambios positivos (crecimiento) se muestran en un color (generalmente green), y negativos (declive) a otros (generalmente rojo).
  
• A veces, el gráfico también puede contener columnas de subtotales (grisaterrizó en las columnas del eje x).
  

En la vida cotidiana, tales diagramas generalmente se usan en los siguientes casos:

• Visual visualización dinámica cualquier proceso en el tiempo: flujo de caja (cash-flow), inversiones (invertimos en un proyecto y obtenemos ganancias de él).
• Visualización implementación del plan (la columna más a la izquierda en el diagrama es un hecho, la columna más a la derecha es un plan, todo el diagrama refleja nuestro proceso de avance hacia el resultado deseado)
• Cuando necesites imágenes mostrar factoresque afectan a nuestro parámetro (análisis factorial del beneficio – en qué consiste).
  

Hay varias formas de crear un gráfico de este tipo; todo depende de su versión de Microsoft Excel.

Método 1: el más fácil: tipo integrado en Excel 2016 y versiones posteriores

Si tiene Excel 2016, 2019 o posterior (u Office 365), entonces crear un gráfico de este tipo no es difícil: estas versiones de Excel ya tienen este tipo integrado de forma predeterminada. Solo será necesario seleccionar una tabla con datos y seleccionar en la pestaña recuadro (Insertar) Comando en cascada (Cascada):

Como resultado, obtendremos un diagrama casi listo:

Puede establecer inmediatamente los colores de relleno deseados para las columnas positivas y negativas. La forma más fácil de hacer esto es seleccionar las filas apropiadas aumente и disminuir directamente en la leyenda y haciendo clic derecho sobre ellos, seleccione el comando Llene (Llenar):

Si necesita agregar columnas con subtotales o un total de columna final al gráfico, entonces es más conveniente hacerlo usando funciones SUBTOTALES (SUBTOTALES) or UNIDAD (AGREGAR). Calcularán la cantidad acumulada desde el inicio de la tabla, excluyendo de ella los totales similares ubicados arriba:

En este caso, el primer argumento (9) es el código de la operación de suma matemática, y el segundo (0) hace que la función ignore los totales ya calculados para los trimestres anteriores en los resultados.

Después de agregar filas con totales, queda seleccionar las columnas totales que han aparecido en el diagrama (haga dos clics individuales consecutivos en la columna) y, haciendo clic con el botón derecho del mouse, seleccione el comando Establecer como total (Establecer como total):

La columna seleccionada aterrizará en el eje x y automáticamente cambiará de color a gris.

Eso, de hecho, es todo: el diagrama de cascada está listo:

Método 2. Universal: columnas invisibles

Si tiene Excel 2013 o versiones anteriores (2010, 2007, etc.), el método descrito anteriormente no funcionará para usted. Tendrá que dar la vuelta y recortar el gráfico de cascada que falta de un histograma apilado regular (sumando las barras una encima de la otra).

El truco aquí es usar columnas de accesorios transparentes para elevar nuestras filas de datos rojas y verdes a la altura correcta:

Para crear un gráfico de este tipo, debemos agregar algunas columnas auxiliares más con fórmulas a los datos de origen:

• Primero, necesitamos dividir nuestra columna original separando los valores positivos y negativos en columnas separadas usando la función IF (SI).  
• En segundo lugar, deberá agregar una columna delante de las columnas chupetes, donde el primer valor será 0, y a partir de la segunda celda, la fórmula calculará la altura de esas columnas de apoyo muy transparentes.
  

Después de eso, queda seleccionar toda la tabla excepto la columna original. Flujo y crear un histograma apilado regular a lo largo Recuadro — Histograma (Insertar — Gráfico de columnas):

Si ahora selecciona las columnas azules y las hace invisibles (haga clic derecho sobre ellas – Formato de fila – Relleno – Sin relleno), entonces solo obtenemos lo que necesitamos. 

La ventaja de este método es la simplicidad. En los menos: la necesidad de contar columnas auxiliares.

Método 3. Si entramos en números rojos, todo es más difícil

Desafortunadamente, el método anterior solo funciona adecuadamente para valores positivos. Si al menos en algún área nuestra cascada cae en un área negativa, entonces la complejidad de la tarea aumenta significativamente. En este caso, será necesario calcular cada fila (ficticia, verde y roja) por separado para las partes negativa y positiva con fórmulas:

Para no sufrir mucho y no reinventar la rueda, se puede descargar una plantilla preparada para tal caso en el título de este artículo.

Método 4. Exótico: bandas arriba-abajo

Este método se basa en el uso de un elemento especial poco conocido de gráficos planos (histogramas y gráficos): bandas arriba-abajo (Barras arriba-abajo). Estas bandas conectan los puntos de dos gráficos en pares para mostrar claramente cuál de los dos puntos es más alto o más bajo, lo que se usa activamente al visualizar el plan-hecho:

Es fácil darse cuenta de que si eliminamos las líneas de los gráficos y dejamos solo las bandas de arriba hacia abajo en el gráfico, obtendremos la misma "cascada".

Para tal construcción, necesitamos agregar dos columnas adicionales a nuestra tabla con fórmulas simples que calcularán la posición de los dos gráficos invisibles requeridos:

Para crear una "cascada", debe seleccionar una columna con meses (para firmas a lo largo del eje X) y dos columnas adicionales Anexo 1 и Anexo 2 y construye un gráfico regular para principiantes usando Insertar – Gráfico (Insertar — Gráfico de líneas):

Ahora agreguemos bandas arriba-abajo a nuestro gráfico:

• En Excel 2013 y posteriores, esto debe seleccionarse en la pestaña Constructor Comando Agregar elemento de gráfico — Bandas de aumento-disminución (Diseño — Agregar elemento de gráfico — Barras arriba-abajo)
• En Excel 2007-2010: vaya a la pestaña Diseño – Barras de avance-decremento (Diseño — Barras arriba-abajo)

El gráfico se verá así:

Queda por seleccionar los gráficos y hacerlos transparentes haciendo clic en ellos a su vez con el botón derecho del mouse y seleccionando el comando Formato de serie de datos (Serie de formato). Del mismo modo, puede cambiar los colores de rayas blanco y negro estándar, bastante desgastados, a verde y rojo para obtener una imagen más agradable al final:

En las últimas versiones de Microsoft Excel, el ancho de las barras se puede cambiar haciendo clic en uno de los gráficos transparentes (¡no en las barras!) con el botón derecho del mouse y seleccionando el comando Formato de serie de datos: espacio libre lateral (Serie de formato — Ancho del espacio).

En versiones anteriores de Excel, tenía que usar el comando de Visual Basic para arreglar esto:

1. Resaltar el diagrama construido
2. Presione el atajo de teclado otro+F11para entrar en el Editor de Visual Basic
3. Presione el atajo de teclado Ctrl+Gpara abrir la entrada de comando directo y el panel de depuración inmediata (generalmente ubicado en la parte inferior).

4. Copie y pegue el siguiente comando allí: ActiveChart.ChartGroups(1).GapWidth = 30 y pulse Participar:

Por supuesto, puede jugar con el valor del parámetro si lo desea. Anchura de rendijapara lograr la holgura deseada:

• Cómo construir un gráfico de viñetas en Excel para visualizar KPI  
• Novedades en gráficos en Excel 2013
• Cómo crear un gráfico interactivo "en vivo" en Excel