Crie gráficos com mudança de cor em relação à meta

22
18590
Planilhas Prontas em Excel

Existem vários recursos avançados no Excel que contribuem para uma melhor visualização dos dados em um gráfico. Neste artigo vamos apresentar uma possibilidade muito interessante, que é a mudança de cor em relação a uma meta pré-estabelecida, que resultará em um significativo impacto visual, como poderemos perceber. Basicamente, construiremos uma tabela onde será estabelecida uma meta para determinado período e iremos inserir os resultados obtidos, que serão organizados de acordo com sua performance. A seguir, construiremos o gráfico e demonstraremos como proceder com a mudança de cor.

Curso de Gráficos Avançados no Excel

Criando a tabela

Para criar a tabela com o período de tempo a ser analisado, utilizaremos cinco colunas. A primeira delas é destinada aos meses do período, na segunda colocaremos a meta estabelecida e na terceira vamos inserir os resultados atingidos em cada mês. As colunas seguintes, que denominaremos “acima” e “abaixo” servirão para organizar as informações para que possamos construir o gráfico com as características desejadas.

Imagem 1

Nestas duas colunas, vamos lançar mão da função “SE”, para que não seja necessário efetuar manualmente a organização dos valores que estão acima ou abaixo da meta. Na coluna “acima”, mais precisamente na célula “D2” em nosso exemplo, vamos inserir a função da seguinte forma: =SE(C2>=B2;C2;””). A ideia é que sejam exibidos os valores quando os resultados forem maiores ou iguais à meta. Já na coluna “abaixo”, de maneira inversa, a função será =SE(C2<B2;C2;””), fazendo com que os resultados inferiores à meta sejam exibidos. Caso a tabela ainda não tenha sido formatada, será preciso clicar e arrastar a função para as demais células em cada coluna.

Imagem 2

Curso de Gráficos Avançados no Excel

Construindo nosso gráfico

Agora que já criamos a tabela, temos as informações necessárias para confeccionarmos o nosso gráfico. Assim, vamos inserir um gráfico de colunas 2D, na guia “Inserir”, “Gráficos”, encontrando a opção desejada. Em “Ferramentas de gráficos”, na aba “Design”, clicaremos em “Selecionar dados”. A tabela toda será automaticamente selecionada, por isso será necessário remover a entrada de série existente e depois clicar em “Adicionar”. Na tela a seguir, o nome de série será o campo “D1”, relacionado aos valores acima da meta, e para os valores da série vamos selecionar os demais campos desta coluna.

Imagem 3

Na sequência, vamos clicar novamente em “Adicionar” e repetir os passos, desta feita utilizando os dados da coluna “E” e depois clicando em “OK”.

Curso de Gráficos Avançados no Excel

Selecionando a coluna azul, com o botão direito, vamos até “Formatar os dados da série” definir os parâmetros a seguir: “Sobreposição de séries” será 100% e “Largura do espaçamento”, 30%. Ao clicar sobre o gráfico e retornar à opção “Selecionar dados”, vamos clicar em “Adicionar” e definir o nome de série como o campo “B1” e os valores da série os demais dados da coluna, que se referem à meta.

Imagem 4

Na série que surgiu, sobrepondo-se em relação às já existentes, vamos clicar com o botão direito em uma das colunas e escolher a opção “Alterar tipo de gráfico da série”. As três séries existentes estarão como “Colunas agrupadas”. Iremos então alterar a série “B1” para “Linhas”, e clicar em “OK”.

Imagem 5

Neste ponto você perceberá que o gráfico está praticamente pronto. Vamos apenas alterar as categorias, acessando novamente a opção “Selecionar dados”. É só clicar em “Editar”, nos rótulos do eixo horizontal e selecionar os meses, constantes na coluna “A” (com o cuidado de não incluir o cabeçalho). Assim, basta formatar a tabela conforme desejado e pronto: temos os resultados acima da média em azul, os resultados inferiores em vermelho e a meta na linha que corta o gráfico, proporcionando uma rápida percepção visual dos resultados ao longo dos meses.

Imagem 6

Um gráfico com mudança de cor em relação à meta oferece um ótimo resultado quanto à visualização, além de ser concebido com recursos avançados. Apresentamos aqui um exemplo prático para que você possa aplicar em seus projetos. Aproveite, e conheça mais dicas e tutoriais do Excel em nosso blog.

Curso de Gráficos Avançados no Excel

Planilhas Prontas em Excel

22 COMENTÁRIOS

  1. Excelente Elise, fico feliz de saber que você conseguiu chegar em um bom resultado ai

  2. Nunca tentei fazer em tabela dinâmica não Filipe, então não saberia dizer. Mas realmente a tabela e gráfico dinâmico possuem características diferentes de uma tabela normal e isso pode gerar esse tipo de problema

  3. Há como fazer este gráfico para uma tabela dinâmica? Tentei, sem sucesso, colocar um “valor calculado” na tabela para sinalizar o que é maior e o que é menor, porém não consegui…

  4. Sim, e o meu gráfico ficou igual ao da imagem do post. Com a linha de meta começando no meio da primeira barra e terminando no meio da última barra.

  5. Como faço para que a linha de meta cruze todo o gráfico? Para não começar e acabar no meio das barra.
    Obrigado

  6. Oi João, pelo que eu vi você não criou as colunas auxiliares mostrando quando os valores ficam acima e quando ficam abaixo da meta. Pelo que aparece no gráfico do seu print, você apenas criou o gráfico de colunas para os valores que possui.

    O primeiro passo para criar esse gráfico é organizar a sua tabela para que fique em uma estrutura lógica que funcione quando você adicionar os dados do gráfico

  7. preciso mota o gráfico, quando ultrapassar o valor previsto fique vermelho, minha unica opção ate o momento e inserir a cor manual.

  8. Sim, a dificuldade e que tem muitos dados e valores a serem inseridos.

    segue print..

  9. Oi João,

    você chegou a ler o post todo e seguiu o passo a passo sugerido? Se sim, onde está com dificuldade?

  10. boa tarde estou tentando efetuar uma planilha de otimização de gasto mensal anual, energia agua telefone. com grafico conforme modelo mais esta complicado. poderia me da uma direção de como fazer

  11. Pelo que entendi,
    Você quer selecionar o período de analise, certo?
    Fiz um gráfico dinâmico com isso, dê uma olha e veja
    se ficou como você quer. Caso não seja ou tenha mais dúvidas
    me mande por e-mail a sua planilha e explicando o que você quer, certo?

    Hector@luz.vc

    Abraço e espero ter ajudado

  12. Oi Hector! Obrigado pela resposta rápida.
    Na verdade é um pouco diferente o que procuro.

    Anexei uma imagem.
    No gráfico esquerdo, tinha uma tabela assim:
    PERÍODO (jan/2014,etc) | VENDAS

    Você pode ver que o período se estende no gráfico. Os meses não se agrupam. Seria ideal para um gráfico de linhas, contínuo. Mas não para o de barras, que considero melhor para ver essa evolução ano a ano.

    No segundo gráfico, alterei a tabela para a seguinte:
    MÊS | ANO | VENDAS

    Dessa forma consigo utilizar a tabela dinâmica para colocar o Ano como Legenda e acaba agrupando pelos meses, mostrando a evolução de determinado mês em cada ano.
    Não consegui trabalhar dessa forma utilizando o período.

    Não sei se tem como fazer isso de outra forma, mas não queria ter de refazer a tabela. Até porque vou montar o Dashboard, e é mais fácil procurar pelo período depois, do que ter de misturar duas colunas (mês/ano) nas fórmulas.

    Acho que não fui muito claro na pergunta. Hehe.
    Se tiver alguma dica de como fazer isso sem ter de recorrer a duas colunas, lhe agradeço.

  13. Boa tarde Otavio,

    Tudo bem?

    Pelo que entendi, você quer mostrar meses de um mês escolhido por você, certo?
    Vou anexar uma imagem, e caso seja isso, você vai precisar de duas colunas de auxilio, uma voltando a função =Não.Disp() e outra voltando “” para as legendas.
    Caso seja isso a as fórmulas serão essas:

    =SE(DIREITA($D$2;2)=DIREITA(B4;2);B3;””), Coluna de legenda;

    =SE(DIREITA($D$2;2)=DIREITA(B4;2);B5;NÃO.DISP()), Coluna de valores;

    Caso tenha alguma dúvida é só falar!

  14. Olá! Gostaria de parabenizar pelos posts sobre excel. Excelente didática e material.

    Estou quebrando a cabeça com o seguinte:

    Criei uma tabela parecida com esse do post – com o MÊS da seguinte forma: jan/14, fev/14, etc.
    Quando tento criar um gráfico de evolução das vendas com barras agrupadas, não consigo agrupar os meses (deixar os meses no eixo e ter as barras correspondentes ao mês de cada ano). Eles aparecem em sequência -> dez/14, jan/15, fev/15 – por exemplo.

    A solução que encontrei foi criar uma coluna a mais – ANO – e na tabela dinâmica coloco o ano como legenda (série).
    Dessa forma consigo ter um gráfico com os 12 meses e as barras correspondentes a cada ano.

    Há alguma forma de agrupar eles nessa tabela?
    Muito obrigado e parabéns.

  15. Tentei o desafio e consegui! Muito bom esse gráfico e ajuda na interpretação dos dados!

DEIXE UMA RESPOSTA

Por favor, escreva o seu comentário
Por favor, digite seu nome