Gráfico de meta x realizado com duas cores

Gráfico Google Analytics 7 capa
Gráfico Google Analytics em Excel
24 de junho de 2017
Como fazer Curva ABC – Como criar gráfico curva ABC no Excel
15 de julho de 2017

Gráfico de meta x realizado com duas cores

Neste artigo é demonstrado como criar um gráfico de meta x realizado com cores diferentes para os valores abaixo ou acima da meta.

Gráfico de meta x realizado

No gráfico acima temos as metas destacadas em um gráfico de barras em azul claro que é sobreposto ao valor realizado, estando em amarelo os valores abaixo das metas e em vermelho as barras nas quais o realizado ultrapassa a meta do indicador.

Para criar este gráfico de meta x realizado siga os passos seguintes:

1. Criando a tabela para o gráfico

  1. Crie uma tabela com as colunas Série, Acima, Abaixo, Meta e Valor.Gráfico de meta x realizado
  2. A primeira coluna é o nome do indicador, por exemplo o nome filial no nosso exemplo.
  3. Preencha os valores para as metas e os valores dos resultados.Gráfico de meta x realizado
  4. As colunas de acima e abaixo são as colunas que servirão para exibir os gráficos com cores diferentes para acima ou abaixo da meta. Para isso coloque a seguinte fórmula para a coluna acima: =SE(F10>=E10;F10;””), sendo F a coluna Valor, E a coluna meta. A fórmula verifica se o valor é acima da meta e retorna este valor, senão retorna vazio.
  5. Para a coluna Abaixo insira a fórmula =SE(C10=””;F10;””), sendo da mesma forma a coluna F a coluna de valor e a coluna C a coluna do Acima da meta. Caso o valor em da coluna Maior estiver vazio então ele retorna o valor da coluna de resultado, senão retorna vazio.

Agora nossa base de dados está ajustada, vamos agora criar o gráfico.

2. Criando o gráfico

Agora iremos demonstrar como criar passo-a-passo o gráfico de meta x realizado com base na nossa tabela.

  1. Selecione os valores da coluna Meta e pressione CTL+F1, o Excel irá criar um gráfico com os dados das metas.Gráfico de meta x realizado
  2. O gráfico criado é de barras verticais, você pode usar este gráfico como vertical ou horizontal. No nosso exemplo iremos fazer um gráfico horizontal. Então clique com o botão direito sobre o gráfico e clique em Alterar tipo de gráfico e altere para barras.
  3. Clique agora sobre o gráfico e clique na guia Design e na opção Selecionar Dados. Clique no botão Adicionar e adicione as séries de dados das colunas Acima e depois a coluna Abaixo.
  4. Clique no botão Editar do Rótulo do Eixo Horizontal e selecione os dados da série. A sua tela ficará conforme abaixo.
    Gráfico de meta x realizado
  5. O resultado do gráfico de meta x realizado é o abaixo.Gráfico de meta x realizado

Neste momento o gráfico já está criado e as três barras já estão criadas, basta agora realizar a formatação do mesmo.

3. Formatando o gráfico

Nesta sessão iremos realizar a formatação para que o gráfico fique conforme a imagem inicial deste artigo.

  1. Clique no gráfico sobre a barra da meta com o botão direito e selecione a opção Formatar série de dados. Altere em Opções de série para Plotar Série no eixo secundário.Gráfico de meta x realizado
  2. Veja que no gráfico acima as séries ficaram sobrepostas, tendo a meta sobre as outras barras de Acima e Abaixo.
  3. Ainda nestas opções altere a Sobreposição de Séries para 0% e a Largura do Espaçamento para 20%. O gráfico deve ficar conforme abaixo.Gráfico de meta x realizado
  4. Ainda em nestas opções clique sobre o ícone do Balde e altere o Preenchimento para Preenchimento Sólido. Altere a cor e coloque a transparência em 74%. Marque também a opção Linha Sólida em Borda.Gráfico de meta x realizado
  5. Clique sobre a barra de Acima com o botão direito e selecione a opção Formatar Série de Dados. Altere as opções Sobreposição de Séries para 100% e a opção Largura do Espaçamento para 80%. Desta forma o gráfico ficará centralizado e um pouco menor do que o gráfico de metas.Gráfico de meta x realizado
  6. Clique com o botão direito sobre o gráfico de Acima e clique na opção Adicionar Rótulos de Dados.
  7. Clique sobre o rótulo de dados com o botão direito e selecione a opção Formatar Rótulos de Dados.
  8. Clique na opção Número e altere o Código de Formatação para Geral;-Geral;;@. Esta formatação funciona da seguinte forma, o primeiro parâmetro Geral é a formatação para valores positivos, o segundo para valores negativos, o terceiro para valores brancos e nulos, veja que colocamos vazia esta configuração e a última é para formatação de textos.Gráfico de meta x realizado
  9. Veja que os valores 0 foram formatados como vazio.
  10. Clique na tabela de dados em qualquer valor da coluna abaixo e coloque nesta coluna um valor acima da meta, apenas para que possamos incluir o rótulo de dados para os valores abaixo da meta.
  11. Na nossa barra clique com o botão direito e inclua o rótulo de dados na barra de abaixo e configure a formatação do número da mesma forma que do acima Geral;-Geral;;@.Gráfico de meta x realizado

Agora apenas insira o título do gráfico, retire a linha do eixo secundário que por ventura tenha ficado acima do gráfico de meta x realizado e ajuste o que necessitar das cores.

4. DOWNLOAD

Você pode fazer o download da planilha digitando o seu e-mail para assinar a nossa newsletter gratuita abaixo.

Abraço

Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR
Cursos

Curso Excel Web – Curso VBA Excel + Lógica de programação + Curso Web Scraping VBA- Acesso Vitalício

R$388,90 R$309,00

COMPRAR

5 Comentários

  1. tarso disse:

    Marcos, vc se confundiu logo no início, não é ctrl + f1 que cria o gráfico com a seleção, é o alt 😉

  2. Ricardo Dettogni disse:

    Excelente Marcos,

    Estou montando uma planilha de controle de combustível para veículos, teria como colocar os meses dentro desta planilha de metas, realizado, abaixo e acima?

    Se tiver uma dica, desde já agradeço.

  3. Jaime Denardi disse:

    Boa tarde

    Marco, é possível desenvolver esse mesmo gráfico com três informações? onde fique uma dentro da outra, por exemplo(Orçado – Realizado – Meta) já fiz diversas tentativas mas infelizmente não consegui, trabalho com excel 2010, pode ser o caso por não conseguir. Com gráfico de duas colunas mais um gráfico de linhas sem problemas, mas gostaria de desenvolver com três colunas, pois tenho cinco informações ao todo.

    Jaime Denardi

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.