Como construir um gráfico em cascata

Cada vez mais me encontro nos relatórios de diferentes empresas e ouço pedidos de trainees para explicar como é construído um diagrama em cascata de desvios – também é uma “cachoeira”, também é uma “cachoeira”, também é uma “ponte”. ”, é também uma “ponte”, etc. Parece algo assim:

De longe, parece mesmo uma cascata de cascatas num rio de montanha ou uma ponte suspensa – quem vê o quê 🙂

A peculiaridade de tal diagrama é que:

• Vemos claramente o valor inicial e final do parâmetro (a primeira e a última coluna).
• Alterações positivas (crescimento) são exibidas em uma cor (geralmente verde), e os negativos (declínio) para outros (geralmente vermelho).
  
• Às vezes, o gráfico também pode conter colunas de subtotal (cinzaaterrissou nas colunas do eixo x).
  

Na vida cotidiana, esses diagramas geralmente são usados ​​​​nos seguintes casos:

• visual exibição de dinâmica qualquer processo no tempo: fluxo de caixa (cash-flow), investimentos (investimos em um projeto e obtemos lucro com isso).
• Visualização implementação do plano (a coluna mais à esquerda no diagrama é um fato, a coluna mais à direita é um plano, todo o diagrama reflete nosso processo de avançar em direção ao resultado desejado)
• Quando você precisa de recursos visuais mostrar fatoresque afetam nosso parâmetro (análise fatorial do lucro – em que consiste).
  

Existem várias maneiras de construir esse gráfico – tudo depende da sua versão do Microsoft Excel.

Método 1: Mais fácil: tipo interno no Excel 2016 e mais recente

Se você tiver o Excel 2016, 2019 ou posterior (ou Office 365), não será difícil criar esse gráfico – essas versões do Excel já têm esse tipo integrado por padrão. Será necessário apenas selecionar uma tabela com dados e selecionar na aba inserção (Inserir) Command cascata (Cascata):

Como resultado, obteremos um diagrama quase pronto:

Você pode definir imediatamente as cores de preenchimento desejadas para as colunas positivas e negativas. A maneira mais fácil de fazer isso é selecionar as linhas apropriadas Crescimento и diminuir diretamente na legenda e clicando com o botão direito sobre elas, selecione o comando Preencher (Preencher):

Se você precisar adicionar colunas com subtotais ou um total de colunas final ao gráfico, é mais conveniente fazer isso usando funções SUBTOTAIS (SUBTOTAIS) or UNIDADE (AGREGAR). Eles calcularão o valor acumulado desde o início da tabela, excluindo dela os totais semelhantes localizados acima:

Neste caso, o primeiro argumento (9) é o código da operação de soma matemática, e o segundo (0) faz com que a função ignore os totais já calculados para trimestres anteriores nos resultados.

Após adicionar as linhas com totais, resta selecionar as colunas totais que apareceram no diagrama (faça dois cliques simples consecutivos na coluna) e, clicando com o botão direito do mouse, selecione o comando Definir como total (Definir como total):

A coluna selecionada ficará no eixo x e mudará automaticamente de cor para cinza.

Isso, na verdade, é tudo – o diagrama em cascata está pronto:

Método 2. Universal: colunas invisíveis

Se você tiver o Excel 2013 ou versões anteriores (2010, 2007 etc.), o método descrito acima não funcionará para você. Você terá que dar a volta e cortar o gráfico em cascata ausente de um histograma empilhado regular (somando as barras umas sobre as outras).

O truque aqui é usar colunas de prop transparentes para elevar nossas linhas de dados vermelhas e verdes para a altura correta:

Para construir esse gráfico, precisamos adicionar mais algumas colunas auxiliares com fórmulas aos dados de origem:

• Primeiro, precisamos dividir nossa coluna original separando os valores positivos e negativos em colunas separadas usando a função IF (E SE).  
• Em segundo lugar, você precisará adicionar uma coluna na frente das colunas chupetas, onde o primeiro valor será 0 e, a partir da segunda célula, a fórmula calculará a altura dessas colunas de suporte muito transparentes.
  

Depois disso, resta selecionar a tabela inteira, exceto a coluna original Fluxo e crie um histograma empilhado regular em Inserir — Histograma (Inserir - Gráfico de Colunas):

Se você agora selecionar as colunas azuis e torná-las invisíveis (clique com o botão direito nelas – Formato de Linha – Preenchimento – Sem Preenchimento), então temos apenas o que precisamos. 

A vantagem deste método é a simplicidade. Nas desvantagens – a necessidade de contar colunas auxiliares.

Método 3. Se formos para o vermelho, tudo fica mais difícil

Infelizmente, o método anterior só funciona adequadamente para valores positivos. Se pelo menos em alguma área nossa cascata entrar em uma área negativa, a complexidade da tarefa aumenta significativamente. Nesse caso, será necessário calcular cada linha (dummy, verde e vermelha) separadamente para as partes negativas e positivas com as fórmulas:

Para não sofrer muito e não reinventar a roda, um modelo pronto para esse caso pode ser baixado no título deste artigo.

Método 4. Exótico: bandas de cima para baixo

Este método é baseado no uso de um elemento especial pouco conhecido de gráficos planos (histogramas e gráficos) – bandas de cima para baixo (Barras de cima para baixo). Essas bandas conectam os pontos de dois gráficos em pares para mostrar claramente qual dos dois pontos é mais alto ou mais baixo, o que é usado ativamente ao visualizar o fato do plano:

É fácil descobrir que, se removermos as linhas dos gráficos e deixarmos apenas as bandas de cima para baixo no gráfico, obteremos a mesma “cascata”.

Para tal construção, precisamos adicionar mais duas colunas adicionais à nossa tabela com fórmulas simples que calcularão a posição dos dois gráficos invisíveis necessários:

Para criar uma “cascata”, você precisa selecionar uma coluna com meses (para assinaturas ao longo do eixo X) e duas colunas adicionais Schedule 1 и Schedule 2 e construir um gráfico regular para iniciantes usando Inserir - Gráfico (Inserir — Linha Сhart):

Agora vamos adicionar bandas de cima para baixo ao nosso gráfico:

• No Excel 2013 e mais recente, isso deve ser selecionado na guia Construtor Command Adicionar elemento gráfico - Bandas de aumento-diminuição (Design - Adicionar elemento de gráfico - Barras para cima e para baixo)
• No Excel 2007-2010 - vá para a guia Layout - Barras de Avanço-Decremento (Layout - Barras de cima para baixo)

O gráfico então ficará mais ou menos assim:

Resta selecionar os gráficos e torná-los transparentes clicando neles sucessivamente com o botão direito do mouse e selecionando o comando Formato da série de dados (Formato série). Da mesma forma, você pode alterar as cores padrão das listras pretas e brancas para verde e vermelho para obter uma imagem melhor no final:

Nas últimas versões do Microsoft Excel, a largura das barras pode ser alterada clicando em um dos gráficos transparentes (não nas barras!) com o botão direito do mouse e selecionando o comando Formato da Série de Dados - Folga Lateral (Série de formatos — Largura do intervalo).

Em versões mais antigas do Excel, você precisava usar o comando Visual Basic para corrigir isso:

1. Destaque o diagrama construído
2. Pressione o atalho do teclado outro+F11para entrar no Editor do Visual Basic
3. Pressione o atalho de teclado Ctrl+Gpara abrir a entrada de comando direto e o painel de depuração Imediato (geralmente localizado na parte inferior).

4. Copie e cole o seguinte comando lá: ActiveChart.ChartGroups(1).GapWidth = 30 e imprensa Entrar:

Você pode, é claro, brincar com o valor do parâmetro, se desejar. Largura do intervalopara obter a folga desejada:

• Como construir um gráfico de marcadores no Excel para visualizar o KPI  
• O que há de novo em gráficos no Excel 2013
• Como criar um gráfico interativo “ao vivo” no Excel