Como construír un gráfico en cascada

Cada vez me encontro con máis frecuencia nos informes de diferentes empresas e escoito as solicitudes dos alumnos para explicar como se constrúe un diagrama en cascada de desviacións: tamén é unha "fervenza", tamén é unha "fervenza", tamén é unha "ponte". ”, tamén é unha “ponte”, etc. Parece algo así:

Desde a distancia, realmente parece unha fervenza de fervenzas nun río de montaña ou unha ponte colgante: quen ve que 🙂

A peculiaridade deste diagrama é que:

• Vemos claramente o valor inicial e final do parámetro (primeira e última columna).
• Os cambios positivos (crecemento) móstranse nunha cor (normalmente verde), e os negativos (declive) aos demais (normalmente vermello).
  
• Ás veces, o gráfico tamén pode conter columnas de subtotal (grayaterrou nas columnas do eixe x).
  

Na vida cotiá, estes diagramas adoitan usarse nos seguintes casos:

• Visual visualización dinámica calquera proceso no tempo: fluxo de caixa (cash-flow), investimentos (investimos nun proxecto e obtemos beneficios del).
• Visualización implantación do plan (a columna máis á esquerda do diagrama é un feito, a columna máis á dereita é un plano, todo o diagrama reflicte o noso proceso de avanzar cara ao resultado desexado)
• Cando necesites visual mostrar factoresque afectan ao noso parámetro (análise factorial do beneficio – en que consiste).
  

Hai varias formas de construír un gráfico deste tipo: todo depende da túa versión de Microsoft Excel.

Método 1: o máis sinxelo: tipo incorporado en Excel 2016 e máis recentes

Se tes Excel 2016, 2019 ou posterior (ou Office 365), crear tal gráfico non é difícil: estas versións de Excel xa teñen este tipo integrado por defecto. Só será necesario seleccionar unha táboa con datos e seleccionar na pestana Inserir (Inserir) Mando en cascada (Fervenza):

Como resultado, obteremos un diagrama case preparado:

Podes definir inmediatamente as cores de recheo desexadas para as columnas positivas e negativas. O xeito máis sinxelo de facelo é seleccionar as filas adecuadas Aumentar и diminuír directamente na lenda e facendo clic co botón dereito sobre eles, selecciona o comando Encher (Recheo):

Se precisa engadir columnas con subtotais ou un total de columnas final ao gráfico, entón é máis conveniente facelo usando funcións SUBTOTAIS (SUBTOTAIS) or UNIDADE (AGREGADO). Calcularán o importe acumulado desde o inicio da táboa, excluíndo dela os totais similares situados anteriormente:

Neste caso, o primeiro argumento (9) é o código da operación de suma matemática e o segundo (0) fai que a función ignore os totais xa calculados dos trimestres anteriores nos resultados.

Despois de engadir filas con totais, queda seleccionar as columnas totais que apareceron no diagrama (facer dous clics consecutivos únicos na columna) e, facendo clic co botón dereito do rato, seleccionar o comando Establecer como total (Establecer como total):

A columna seleccionada aterrará no eixe x e cambiará automaticamente a cor a gris.

Isto, de feito, é todo: o diagrama da fervenza está listo:

Método 2. Universal: columnas invisibles

Se tes Excel 2013 ou versións anteriores (2010, 2007, etc.), o método descrito anteriormente non funcionará para ti. Terás que dar unha volta e recortar o gráfico de fervenza que falta dun histograma regular apilado (sumando as barras unha encima das outras).

O truco aquí é usar columnas de apoio transparentes para elevar as nosas filas de datos vermellas e verdes á altura correcta:

Para construír un gráfico deste tipo, necesitamos engadir algunhas columnas auxiliares máis con fórmulas aos datos de orixe:

• En primeiro lugar, necesitamos dividir a nosa columna orixinal separando os valores positivos e negativos en columnas separadas usando a función IF (SI).  
• En segundo lugar, terás que engadir unha columna diante das columnas chupetes, onde o primeiro valor será 0, e partindo da segunda cela, a fórmula calculará a altura desas columnas de apoio moi transparentes.
  

Despois diso, queda seleccionar toda a táboa excepto a columna orixinal Fluxo e cree un histograma regular apilado Inserción - Histograma (Inserir — Gráfico de columnas):

Se agora selecciona as columnas azuis e as fai invisibles (faga clic co botón dereito sobre elas - Formato de fila - Recheo - Sen recheo), entón só conseguimos o que necesitamos. 

A vantaxe deste método é a sinxeleza. Nos menos - a necesidade de contar columnas auxiliares.

Método 3. Se entramos en vermello, todo é máis difícil

Desafortunadamente, o método anterior só funciona adecuadamente para valores positivos. Se polo menos nalgunha zona a nosa fervenza entra nunha zona negativa, entón a complexidade da tarefa aumenta significativamente. Neste caso, será necesario calcular cada fila (maniquí, verde e vermello) por separado para as partes negativas e positivas con fórmulas:

Para non sufrir moito e non reinventar a roda, pódese descargar un modelo preparado para tal caso no título deste artigo.

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

Este método baséase no uso dun elemento especial pouco coñecido de gráficos planos (histogramas e gráficos) - Bandas arriba-abajo (Barras arriba-abajo). Estas bandas conectan os puntos de dúas gráficas por parellas para mostrar claramente cal dos dous puntos é máis alto ou máis baixo, que se usa activamente ao visualizar o plan-feito:

É fácil descubrir que se eliminamos as liñas dos gráficos e deixamos só as bandas arriba e abaixo no gráfico, entón teremos a mesma "fervenza".

Para tal construción, necesitamos engadir dúas columnas adicionais á nosa táboa con fórmulas sinxelas que calcularán a posición dos dous gráficos invisibles necesarios:

Para crear unha "fervenza", cómpre seleccionar unha columna con meses (para sinaturas ao longo do eixe X) e dúas columnas adicionais Axenda 1 и Axenda 2 e constrúe unha gráfica regular para comezar a usar Inserir - Gráfico (Inserir — Gráfico de liñas):

Agora imos engadir bandas de arriba abaixo ao noso gráfico:

• En Excel 2013 e máis recentes, debe seleccionarse na pestana Construtor Mando Engadir elemento gráfico - Bandas de aumento-diminución (Deseño — Engadir elemento gráfico — Barras arriba e abaixo)
• En Excel 2007-2010: vai á pestana Disposición - Barras de decremento avanzado (Disposición - Barras arriba e abaixo)

O gráfico terá entón un aspecto así:

Queda por seleccionar os gráficos e facelos transparentes premendo sobre eles á súa vez co botón dereito do rato e seleccionando o comando Formato de serie de datos (Formato serie). Do mesmo xeito, podes cambiar as cores estándar de raias brancas e negras de aspecto bastante desordenado a verde e vermello para obter unha imaxe máis agradable ao final:

Nas últimas versións de Microsoft Excel, o ancho das barras pódese cambiar facendo clic nun dos gráficos transparentes (non nas barras!) Co botón dereito do rato e seleccionando o comando Formato da serie de datos: separación lateral (Formato de serie — Ancho de separación).

Nas versións anteriores de Excel, tivo que usar o comando Visual Basic para solucionar isto:

1. Resalte o diagrama construído
2. Preme o atallo do teclado alt+F11para entrar no Editor de Visual Basic
3. Preme o atallo de teclado Ctrl+Gpara abrir o panel de entrada de comandos directos e depuración inmediato (normalmente situado na parte inferior).

4. Copia e pega o seguinte comando alí: ActiveChart.ChartGroups(1).GapWidth = 30 e prensa Entrar:

Por suposto, pode xogar co valor do parámetro se o desexa. GapWidthpara conseguir a autorización desexada:

• Como construír un gráfico de viñetas en Excel para visualizar KPI  
• Novidades nos gráficos en Excel 2013
• Como crear un gráfico interactivo "en vivo" en Excel