Gráfico com Cores Diferentes para Abaixo ou Acima da Meta

Novo layout do site
Novo layout do site
13 de março de 2012
Lista Dinâmica Excel com Imagens - Procv com Imagens
Lista Dinâmica Excel com Imagens – Procv com Imagens
21 de março de 2012

Objetivo: Demonstrar a criação de um gráfico que a barra mude de cor caso o valor esteja acima ou abaixo da meta determinada.

     Conforme a WikipediaMeta (do conceito grego τέλος [telos]; do latim finis) marca a pessoa se posicionando no futuro, desejável e se esforçado para implementar as condições (objetivo). Uma meta não é a mesma coisa que um objetivo e vice-versa. Uma meta é um objetivo relacionada a tempo e valor, ou seja: “Vou comprar um carro (objetivo) de R$ 20.000,00 (valor) até dezembro de 2008 (tempo/prazo). “Uma meta é um objetivo traduzido em termos quantitativos, por exemplo, o objetivo é aumentar a temperatura de fusão e a meta é chegar a 1000 graus centígrados.

     Neste sentido muitas empresas estabelecem suas metas para diversos indicadores da empresa a serem alcançados, como por exemplo vendas, custos, despesas, turnover, entre outros.

     Neste artigo demonstrarei como criar um gráfico que representa graficamente os pontos aonde estas metas foram ou não alcançadas, mudando a cor para exibir de uma melhor forma estes dados.

1. Crie uma planilha com o período que será analisado, uma coluna para a meta e outra para o resultado.

Mês Meta Resultado Acima Abaixo
jan/12 7000 9417
fev/12 7000 7239
mar/12 7000 4998
abr/12 7000 9936
mai/12 7000 8281
jun/12 7000 1570
jul/12 7000 3289
ago/12 7000 4698
set/12 7000 2371
out/12 7000 6288
nov/12 7000 6065
dez/12 7000 10506

2. Na coluna “Acima” digite a função =SE(C2>=B2;C2;””), sendo C2 a coluna “Resultado” e B2 a coluna “Meta”. Arraste a fórmula até o final desta tabela, ela irá trazer os valores apenas quando o valor for maior ou igual a meta.

3. Na coluna “Abaixo” digite a função =SE(C2<B2;C2;””), sendo C2 a coluna “Resultado” e B2 a coluna “Meta”. Arraste a fórmula até o final desta tabela, ela irá trazer os valores apenas quando o valor for menor do que a meta.

4. A tabela deverá ficar conforme abaixo.

Mês Meta Resultado Acima Abaixo
jan/12 7000 9417 9417
fev/12 7000 7239 7239
mar/12 7000 4998 4998
abr/12 7000 9936 9936
mai/12 7000 8281 8281
jun/12 7000 1570 1570
jul/12 7000 3289 3289
ago/12 7000 4698 4698
set/12 7000 2371 2371
out/12 7000 6288 6288
nov/12 7000 6065 6065
dez/12 7000 10506 10506

5. Clique na guia “Inserir” e selecione o tipo de gráfico de colunas 2D.

6. Selecione o gráfico e na guia “Ferramentas de Gráfico->Design” clique no botão “Selecionar Dados”.

7. Na tela que surge clique no botão “Adicionar” e na tela que segue selecione para o campo “Nome de série” o campo D1 aonde está escrito “Acima” e para o campo “Valores da série” apague o conteúdo deste campo e depois selecione a lista de dados desta coluna, sem incluir o nome da série.

8. Clique em OK, e clique novamente no botão “Adicionar”.

9. Na tela que surge clique no botão “Adicionar” e na tela que segue selecione para o campo “Nome de série” o campo D1 aonde está escrito “Acima” e para o campo “Valores da série” apague o conteúdo deste campo e depois selecione a lista de dados desta coluna, sem incluir o nome da série.

10. A lista ficará conforme abaixo.

11. Clique sobre a coluna azul com o botão direito e em “Formatar série de dados”.

12. Na tela que segue clique em “Opções de série” e no campo “Sobreposição de Séries” selecione 100%.

 [saiba_mais] 13. Ainda nesta tela altere o valor do campo “Largura do Espaçamento” para 30%.

14. Clique sobre o gráfico e na guia “Ferramentas de Gráfico”->”Design” clique no botão “Selecionar Dados”.

15. Na tela que surge clique no botão “Adicionar” e na tela que segue selecione para o campo “Nome de série” o campo B1 aonde está escrito “Meta” e para o campo “Valores da série” apague o conteúdo deste campo e depois selecione a lista de dados desta coluna, sem incluir o nome da série.

16. Na barra verde que sobrepõe as outras clique com o botão direito e selecione a opção “Alterar Tipo de Gráfico de Série” selecionando o tipo Linha.

17. Clique novamente no botão “Selecionar Dados” na guia superior e na opção “Rótulo do Eixo Horizontal (Categorias)” apague o conteúdo e selecione apenas os meses sem o cabeçalho na coluna A.

18. O resultado deve ser conforme a imagem abaixo.

     Note que as barras azuis por estarem acima da linha verde da meta, ficam nesta cor e as vermelhas por estarem abaixo da meta ficam nesta outra cor.

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/

29 Comentários

  1. Amigo estou com uma dúvida no excel e só for possível eu poder tirar com você gostaria que você me enviasse seu e-mail, pois eu gostaria de lhe enviar uma planilha como modelo para você poder me orientar qual seria a fórmula ideal para extrair algumas informações dela, obg

  2. Airton disse:

    Poderia me ajudar com execel VBA?
    Eu poderi até mesmo pagar para isso.

  3. Luiz Fernando disse:

    Gostaria de parabeniza-los a todos do site pelo ótimo trabalho de vcs.

  4. WESLEY disse:

    Eu queria um gráfico que as barras acima da meta mudassem de cor.
    teria algo assim.

  5. Gilmar disse:

    Caro Marcos;
    Verifiquei seu material e ajudou imensamente em algo que eu necessitava! Obrigado

  6. Alessandro disse:

    Opah… e se eu precisar incluir o rotulo neste gráficos como faço ? certamente ele trará a informação dos dois graficos dcerto ?

  7. Felipe disse:

    Bom dia,

    Inseri o rotulo de dados nesse exemplo de gráfico, mas aparece das duas colunas, e cada barra aparece seu valor correto e mais um valor zerado. Tem como corrigir isso?

    Obrigado!

  8. Bom dia!
    Marcos, parabéns pelo site ! Gostaria da sua ajuda, é possível amarrar as cores de um gráfico na planilha de orgigem? Por exemplo: Tenho uma planilha com valores e descriçao dos valores cada qual com sua cor, gostaria que meu gráfico adotasse a mesma cor das descrição de cada valor.
    Obrigado pela atenção !

  9. Sergio disse:

    Marcos, estou muito satisfeito com o treinamento em VBA. Muito fácil o entendimento. E mais, as planilhas dispostas no site são fantásticas.
    Parabéns.

    Sérgio

  10. Tatiana disse:

    Boa tarde, você me ajudou muito! Eu também tenho um blog (owsheet.blogspot.com.br) sobre excel. Comecei agora mas isso era uma das coisas que eu não sabia fazer!
    Eu vou escrever um artigo no meu blog e vou mencionar o seu. Pode ser? Se quiser eu te mando o artigo antes para vc dar uma olhada 😉

    • Marcos Rieper disse:

      Bom dia Tatiana,

      Parabéns pela iniciativa é sempre bom ver novos blogs sobre Excel, acredito que o quanto mais pudermos ajudar as pessoas com o que podemos melhor.

      Pode manter o artigo e mencionar o site, ficou bem legal o seu site, um forte abraço.

      Marcos Rieper

  11. Silvio Moser disse:

    Bom artigo, ajudou bastante, mas me parece que os ítens 7 e 9 são iguais, o 9 deve ser editado, estou certo? Abraços,

  12. Marcos, estou muito satisfeito com o treinamento em Excel Avançado. Muito fácil o entendimento. E mais, os videos são ótimos e os exemplos dispostos no site são fantásticas.
    Parabéns.

    Carlos Alberto

  13. ruberval rodrigues de souza disse:

    bom dia!

    tem como deixar os valores do gráfico com cores diferente por exemplo
    <80 uma cor
    entre 80 e 85 outra cor
    e acima de 85 outra cor

    • Marcos Rieper disse:

      Olá Ruberval,

      Sim, mas somente com VBA neste caso, porque o Excel não possui recurso de formatação condicional ou processo para retornar a formatação nos comentários do gráfico.

      Abraço
      Marcos Rieper

      • Samuel Garcia disse:

        Na verdade tem sim, basta colocar na fórmula do se os argumentos de acordo com o esperado. Neste caso, uma linha seria =se(C2<B2*0,80;B2;""), a outra seria =se(C2B2*0,80;B2;””);””) e, por fim, =se(C2>B2;B2;””)
        Depois de colocado esses argumentos, basta seguir os passos para elaborar os gráficos.

        • Samuel Garcia disse:

          Na verdade tem sim, basta colocar na fórmula do se os argumentos de acordo com o esperado. Neste caso, uma linha seria =se(C2<B2*0,80;B2;""), a outra seria =se(C2B2*0,80;B2;””);””) e, por fim, =se(C2>B2;B2;””)
          Depois de colocado esses argumentos, basta seguir os passos para elaborar os gráficos.

  14. Meu caro professor eu estou precisando aprender fazer gráficos com uma única linha com informações diversas através das cores – isto é, uma única linha com diversas cores de identificação.
    Att.
    Carlos Welsing

  15. Isabella disse:

    Marcos,

    preciso fazer este mesmo gráfico, porém com 2 barras ao invés de 1:

    Ex:

    Mês: Jan/19

    Barra 1(Carros Azuis Vendidos):5 Objetivo: 6
    Barra 2 (Carros Verdes Vendidos): 4 Objetivo: 3

    Mês: Fev/19

    Barra 1(Carros Azuis Vendidos):7 Objetivo: 6
    Barra 2 (Carros Verdes Vendidos): 2 Objetivo: 3

    Preciso que tenha as 2 barras para cada mês, com as 2 linhas de objetivo e que as cores de cada barra esteja amarrado com o objetivo com o decorrer dos meses (vermelho ou verde).

    Isto é possível?

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.