Gráfico previsto e realizado no Excel com variação percentual

Neste artigo, você aprenderá a criar um gráfico previsto e realizado no Excel com variação percentual.

Existem técnicas diferentes para criar este tipo de gráfico. Para um gráfico diferenciado, vamos precisar combinar recursos do Excel.

Neste exemplo o destaque fica por conta da Barra de Erros, que é um recurso que faz parte dos elementos do gráfico do Excel.

Vamos trabalhar por etapas. Primeiro criar e formatar o gráfico, depois criar fórmulas e por fim, adicionar os resultados das fórmulas ao gráfico.

Criando e formatando o gráfico

• Clique aqui para fazer o download do arquivo de exemplo.
• Com arquivo aberto, selecione o intervalo B3:C11.
• Clique na guia Inserir → grupo Gráficos → Inserir Gráfico de Colunas ou de Barras → Barras Agrupadas.

O gráfico foi criado com os dados selecionados. Vamos agora formatá-lo.

• Clique no gráfico para selecioná-lo.
• Altere a cor da fonte para preto.
• Altere o título para: “Variação Percentual – Previsto x Realizado, Vendas 2020”.
• Exclua as linhas de grade.
• Inverta a ordem do eixo de categorias.
• Altere a largura do espaçamento das séries para 70%.
• Altere o preenchimento da série para preto.
• Adicione rótulos de dados com posicionamento na base interna, cor branco.

Com o gráfico criado e formatado, vamos partir para a criação das fórmulas.

Gráfico previsto e realizado no Excel – fórmulas

A primeira fórmula será para criar uma sequência decrescente de 0,5 em 0,5. Em seguida, estes dados serão adicionados ao gráfico.

• Clique na célula F4.
• Clique na guia Fórmulas → grupo Biblioteca de Funções → Mais Funções → Estatística → CONT.VALORES.
• Em Valor1, selecione o intervalo B4:B11 e clique em OK.
• Na barra de fórmulas, digite -0,5 após o parêntese de fechamento da função e pressione a tecla Enter.
• Copie a fórmula para baixo.
• Clique no gráfico para selecioná-lo.
• Clique na guia Design → grupo Dados → Selecionar Dados.

• Na caixa Selecionar Fonte de Dados, em Entradas de Legenda (série), clique em Adicionar.
• Em Nome da série, digite “Realizado Mais”. Em Valores da série, deixe como está.
• Clique em OK e em OK novamente.
• Clique em uma das barras do gráfico.
• Clique na guia Design → grupo Tipo → Alterar Tipo de Gráfico.
• Altere o gráfico da série “Realizado Mais” para Dispersão.

• Clique em OK. Clique na guia Design → grupo Dados → Selecionar Dados.
• Na caixa Selecionar Fonte de Dados, em Entradas de Legenda (série), clique na série ”Realizado Mais” e em seguida em Editar.
• Em Valores X da série, selecione o intervalo C4:C11. Em Valores Y da série, selecione o intervalo F4:F11.
• Clique em Ok e em Ok novamente.

 O resultado deve mostrar os marcadores do gráfico de dispersão no final da barra:

Agora vamos criar as fórmulas para as colunas “Mais” e “Menos”.

• Clique na célula G4.
• Clique na guia Fórmulas → grupo Biblioteca de Funções → Lógica → SE.
• Em Teste_lógico, clique na célula C4, digite o sinal de maior (>) e clique na célula D4.
• Em Valor_se_verdadeiro, digite a fórmula: C4-D4.
• Em Valor_se_falso, digite duas aspas duplas.

• Clique em OK. Copie a fórmula para baixo.
• Clique na célula H4.
• Clique na guia Fórmulas → grupo Biblioteca de Funções → Lógica → SE.
• Em Teste_lógico, clique na célula C4, digite o sinal de menor (<) e clique na célula D4.
• Em Valor_se_verdadeiro, digite a fórmula: D4-C4. Em Valor_se_falso, digite duas aspas duplas.

• Clique em OK. Copie a fórmula para baixo.

A coluna “Mais” mostra a diferença quando o realizado for maior que o previsto. A coluna “Menos” mostra a diferença quando o realizado for menor que o previsto.

Inserindo barra de erros

A barra de erros vai exibir duas informações: uma barra verde informando o percentual “mais” do previsto x realizado e uma barra vermelha informando o percentual “menos”.

• Clique no gráfico para selecioná-lo.
• Clique na guia Formatar → grupo Seleção Atual → Elementos do Gráfico → Série “Realizado Mais”.
• Clique em Formatar Seleção.

• No painel à direita, clique em Linha de Preenchimento (ícone do balde). Em Marcador, clique em Opções de Marcador e escolha Nenhum.
• Clique na guia Design → grupo Layout de Gráfico → Adicionar Elemento Gráfico → Barra de erros → Erro Padrão.
• Clique na barra vertical e pressione a tecla Delete.
• Clique com o botão direito do mouse sobre a barra de erro horizontal e escolha Formatar Barras de Erros.
• No painel à direita, em Direção, marque a opção Menos. Em Estilo Final, Sem legenda. Em Erro, marque a opção Personalizado e clique no botão Especificar Valor.
• Na caixa Barras de Erros Personalizadas, em Valor de Erro Negativo, selecione o intervalo G4:G11 e clique em OK.
• Mantenha a barra de erro selecionada e na guia Formatar → grupo Estilos de Forma, altere o contorno para verde com espessura 4 ½.

Veja o resultado:

Vamos fazer uma configuração parecida para a série “Realizado Menos”.

• Mantenha o gráfico selecionado.
• Clique na guia Design → grupo Dados → Selecionar Dados.
• Na caixa Selecionar Fonte de Dados, em Entradas de Legenda (série), clique em Adicionar.
• Em Nome da série, digite “Realizado Menos”. Em Valores X da série, selecione o intervalo C4:C11.
• Em Valores Y da série, selecione o intervalo F4:F11.
• Clique em Ok e em Ok novamente.
• Clique na guia Formatar → grupo Seleção Atual → Elementos do Gráfico → Série “Realizado Menos”.
• Clique na guia Design → grupo Layout de Gráfico → Adicionar Elemento Gráfico → Barra de erros → Erro Padrão.
• Clique na barra vertical e pressione a tecla Delete.
• Clique com o botão direito do mouse sobre a barra de erro horizontal e escolha Formatar Barras de Erros.
• No painel à direita, em Direção, marque a opção Mais. Em Estilo Final, Sem legenda. Em Erro, marque a opção Personalizado e clique no botão Especificar Valor.
• Na caixa Barras de Erros Personalizadas, em Valor de Erro Positivo, selecione o intervalo H4:H11 e clique em OK.
• Mantenha a barra de erro selecionada e na guia Formatar → grupo Estilos de Forma, altere o contorno para vermelho com espessura 4 ½. 

Veja o resultado:

Inserindo rótulos de dados

Para finalizar, vamos criar fórmulas e adicionar ao gráfico para servir de rótulo.

• Clique na célula I4, digite a fórmula: =(C4-D4)/D4 e pressione a tecla Enter.
• Clique na célula I4 e copie a fórmula para baixo.
• Clique na célula J4 digite a fórmula: =MÁXIMO(C4: D4) e pressione a tecla Enter.
• Clique na célula J4 e copie a fórmula para baixo.
• Clique no gráfico para selecioná-lo.
• Clique na guia Design → grupo Dados → Selecionar Dados.
• Na caixa Selecionar Fonte de Dados, em Entradas de Legenda (série), clique em Adicionar.
• Em Nome da série, digite “Rótulos”. Em Valores X da série, selecione o intervalo J4:J11.
• Em Valores Y da série, selecione o intervalo F4:F11.
• Clique em Ok e em Ok novamente.
• Clique na guia Formatar → grupo Seleção Atual → Elementos do Gráfico → Série “Rótulos”.
• Clique na guia Design → grupo Layout de Gráfico → Adicionar Elemento Gráfico → Rótulos de Dados → Direita.
• Clique com o botão direito do mouse sobre um dos rótulos e escolha Formatar Rótulos de Dados.
• No painel à direita, marque a caixa Valor a Partir de Células.
• Selecione o intervalo I4:I11 e clique em OK.
• Desmarque as caixas Valor Y e Mostrar Linhas de Preenchimento.
• Exclua o eixo secundário (à direita).

Veja o resultado:

Faça testes, alterando os valores da coluna C e veja a alteração no gráfico.

Veja a videoaula sobre gráfico previsto e realizado no Excel.

Participe

O que achou do gráfico previsto e realizado no Excel?

Se este conteúdo foi relevante para você, use os botões de compartilhamento abaixo e convide seus amigos para que eles também fiquem sabendo deste material.

Um abraço e até próxima.