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

22
12774

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

COMPARTILHAR
Artigo anteriorComo calcular riscos no Excel?
Próximo artigoConheça as funções condicionais no Excel
Rafael Ávila
Administrador e turismólogo, apaixonado pelas possibilidades que o Excel dá para gestores, empreendedores e estudantes. É sócio diretor da área de planilhas na LUZ - Planilhas Empresariais (luz.vc) e é o responsável pelo desenvolvimento das mais de 150 planilhas com foco empresarial e pessoal comercializadas em seu site. Também ministra os cursos online de Excel (cursos.luz.vc) e gosta de ajudar milhares de planilheiros por meio do Fórum e Blog da LUZ.

22 COMENTÁRIOS

  1. 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.

    • 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!

      • 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.

        • 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

  2. 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

        • 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

  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…

    • 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

DEIXE UMA RESPOSTA

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