Gráficos

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

Escrito por Adriano Pataro
em 24/10/2021

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.
Inserir gráfico de barras no Excel

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.
gráfico de barras no Excel

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.
Selecionar dados para o gráfico
  • 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.
Alterar tipo de gráfico no Excel
  • 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”.

Baixe o Infográfico Tipos de Gráficos do Excel
  • 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.
Função Se no Excel
  • 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.
Função Se no Excel
  • 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.
Formatar série do gráfico do Excel
  • 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:

Gráfico com barra de erro no Excel

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:

Gráfico do Excel com barras de erros

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:

Gráfico no Excel com rótulos de dados

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.

Baixe o Infográfico Tipos de Gráficos do Excel
Compartilhe este conteúdo

Adriano Pataro

Meu nome é Adriano Pataro, sou certificado pela Microsoft como Excel Specialist e autor do livro Dominando o Excel 2019. Graduando ciência de dados, trabalho como analista de dados e uso o Excel há mais de 15 anos. Em 2005 resolvi trazer meu conhecimento para a internet e compartilhar com você aqui no meu site e nas redes sociais.

Artigos Relacionados

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Fique por dentro das novidades

Cadastre seu e-mail e receba os conteúdos em primeira mão.

>

Damos valor à sua privacidade

Nós e os nossos parceiros armazenamos ou acedemos a informações dos dispositivos, tais como cookies, e processamos dados pessoais, tais como identificadores exclusivos e informações padrão enviadas pelos dispositivos, para as finalidades descritas abaixo. Poderá clicar para consentir o processamento por nossa parte e pela parte dos nossos parceiros para tais finalidades. Em alternativa, poderá clicar para recusar o consentimento, ou aceder a informações mais pormenorizadas e alterar as suas preferências antes de dar consentimento. As suas preferências serão aplicadas apenas a este website.

Cookies estritamente necessários

Estes cookies são necessários para que o website funcione e não podem ser desligados nos nossos sistemas. Normalmente, eles só são configurados em resposta a ações levadas a cabo por si e que correspondem a uma solicitação de serviços, tais como definir as suas preferências de privacidade, iniciar sessão ou preencher formulários. Pode configurar o seu navegador para bloquear ou alertá-lo(a) sobre esses cookies, mas algumas partes do website não funcionarão. Estes cookies não armazenam qualquer informação pessoal identificável.

Cookies de desempenho

Estes cookies permitem-nos contar visitas e fontes de tráfego, para que possamos medir e melhorar o desempenho do nosso website. Eles ajudam-nos a saber quais são as páginas mais e menos populares e a ver como os visitantes se movimentam pelo website. Todas as informações recolhidas por estes cookies são agregadas e, por conseguinte, anónimas. Se não permitir estes cookies, não saberemos quando visitou o nosso site.

Cookies de funcionalidade

Estes cookies permitem que o site forneça uma funcionalidade e personalização melhoradas. Podem ser estabelecidos por nós ou por fornecedores externos cujos serviços adicionámos às nossas páginas. Se não permitir estes cookies algumas destas funcionalidades, ou mesmo todas, podem não atuar corretamente.

Cookies de publicidade

Estes cookies podem ser estabelecidos através do nosso site pelos nossos parceiros de publicidade. Podem ser usados por essas empresas para construir um perfil sobre os seus interesses e mostrar-lhe anúncios relevantes em outros websites. Eles não armazenam diretamente informações pessoais, mas são baseados na identificação exclusiva do seu navegador e dispositivo de internet. Se não permitir estes cookies, terá menos publicidade direcionada.

Visite as nossas páginas de Políticas de privacidade e Termos e condições.

O site utiliza cookies para melhorar sua experiência de navegação.
Success message!
Warning message!
Error message!