Aprenda a criar gráficos que alteram a cor em relação ao valor de uma meta.

Montar apresentações são sempre demoradas e é necessário estar bem atento a detalhes, principalmente aos padrões utilizados na empresa.

Imagine que um desses padrões é deixar as barras do gráfico com a cor verde quando uma meta for pelo menos atingida e com a cor vermelha quando não estiver batendo a meta. E agora imagina alterar as cores dos gráficos de forma manual e para mais de 20 gráficos. Isso vai dar muito trabalho.

Neste artigo iremos mostrar como inserir uma “formatação condicional” no gráfico de colunas , que irá alterar as suas cores de acordo com o valor de uma meta.

Quando o gráfico com formatação condicional estiver finalizado, você poderá utilizá-lo para controlar as suas metas.

Para mostrar como configurar a formatação condicional em um gráfico de colunas, será necessário a utilização de uma combinação de dois tipos de gráficos, o gráfico de coluna e o de linha, assim como a função SE.

Começaremos montando uma tabela aonde iremos inserir os valores das metas mensais, assim como os valores dos resultados mensais. Depois iremos inserir uma combinação do gráfico de coluna com o de linha e mostraremos como configurar o gráfico com a formatação condicional para que ocorra a mudança de cor das colunas.

Youtube

Assista ao vídeo abaixo, caso prefira aprender como fazer para criar gráficos que alteram a cor em relação ao valor de uma meta assistindo a um vídeo.

Ah, e não se esqueça de se INSCREVER no nosso canal.

E coloque o seu melhor e-mail abaixo para receber todas as nossas novidades em primeira mão.

Montar a tabela

Antes de pensarmos em ter um gráfico, primeiro precisamos elaborar a tabela de dados que irá alimentar o gráfico. A nossa tabela de dados terá os meses do ano, os valores de meta e os valores do realizado.

Iremos montar a nossa base de dados de acordo com a imagem abaixo.

As linhas referentes ao “Realizado – Acima” e “Realizado – Abaixo” irão ser preenchidas com a função SE, que será responsável por alimentar a célula de acordo com a condição que iremos estabelecer.

Inserir a função SE

Na linha referente a “Realizado – Acima”, iremos inserir uma função SE, aonde ela irá verificar se o valor do realizado é maior ou igual ao valor da meta, caso seja verdadeiro irá inserir o valor do realizado na célula. Caso contrário irá inserir o valor zero(0).

A função irá ficar da seguinte forma:

• =SE(C$5>=C$2;C$5;0)

Atente para o cifrão($). Ele irá “travar” a função nas linhas 2 e 5 e ficará dependente somente da posição da coluna.

Na linha referente a “Realizado – Abaixo”, iremos inserir a função SE, mas dessa vez iremos verificar se o valor do realizado é menor que o valor da meta. Caso seja verdadeiro, a função irá inserir o valor do realizado na célula. Senão irá inserir o valor zero(0).

No nosso exemplo, iremos inserir a função na célula C4, e ela irá ficar da seguinte forma.

• =SE(C$5<C$2;C$5;0)

Atente para o cifrão($). Ele irá “travar” a função nas linhas 2 e 5 e ficará dependente somente da posição da coluna.

As fórmulas na tabela devem ficar da seguinte forma:

Após inserir as fórmulas na coluna C, precisamos arrastá-las para as outras células. Para conseguir isso você tem duas alternativas.

Copiar as duas células(CTRL + C) e colá-las(CTRL + V) nas outras células.

Uma outra alternativa é selecionar as duas células.

Após selecionar as duas células, traga o mouse até o quadrado que está na parte inferior direita da seleção. Clique e arraste ele até a coluna N.

Tabela finalizada

Após inserir a função SE nas outras colunas, a sua tabela deve estar com os seguintes valores.

Caso você não tenha utilizado os mesmos valores, não tem problema. So verifique se a função SE está preenchida de forma correta nas células.

Inserir o gráfico

Agora que já temos a nossa tabela de dados montada, iremos inserir um gráfico combinando o gráfico de colunas e o gráfico de linhas.

Selecione as seguintes células(B1:N4).

Certifique-se de que as informações referentes ao “Realizado” não estejam selecionadas.

Com as células selecionadas, vá na parte superior do Excel e selecione a aba “Inserir” e depois clique em “Gráficos Recomendados”.

Uma janela irá aparecer. Nessa janela iremos configurar qual os tipos de gráficos que iremos utilizar. Que será uma combinação entre o gráfico de colunas agrupadas e o gráfico de linhas.

1. Assim que a janela aparecer, a aba “Gráficos Recomendados” estará selecionada. Selecione a aba do lado, “Todos os Gráficos”.
2. No lado esquerdo da janela, selecione o item “Combinação”.
3. Na parte superior da janela, aonde é possível ver 4 gráficos diferentes, selecione o primeiro gráfico da direita para a esquerda, que é a “Combinação Personalizada”.
4. Na parte de baixo da janela, é possível escolher o tipo de gráfico, assim como o eixo da série de dados. No nosso exemplo, iremos escolher somente o tipo de gráfico. Para o item “Meta”, vamos escolher o tipo de gráfico “Linhas”.
5. Para o “Realizado – Acima” iremos escolher o tipo de gráfico “Colunas Agrupadas”.
6. Para o “Realizado – Abaixo” vamos escolher o tipo de gráfico “Colunas Agrupadas”.

Ao clicar em “OK”, o gráfico abaixo deverá aparacer na sua planilha de Excel.

Configuração do Gráfico com Formatação Condicional

Agora que já temos o gráfico na tela, precisamos realizar algumas alterações nele, para que fique igual ao gráfico que foi apresentado no começo do artigo.

Vamos começar alterando as cores das barras do gráfico de colunas agrupadas.

Alterar as cores das colunas

Clique com o botão direito do mouse encima de uma das colunas CINZAS do gráfico. Uma janela irá aparecer abaixo do gráfico com algumas opções. Clique em “Preenchimento”.

Ao clicar no item “Preenchimento” uma segunda janela irá aparecer, aonde é possível substituir a cor cinza do gráfico, por alguma outra cor.

Vamos escolher a cor vermelha, que está marcada na imagem. Essa cor indicará que o resultado ficou abaixo da meta.

Vamos realizar novamente o passo acima, mas dessa vez iremos clicar nas barras laranjas, cujos valores ficaram acima da meta e escolher a cor verde.

Após alterar as cores, o seu gráfico deverá ficar como a figura abaixo:

Aqui nos já temos um gráfico que altera as cores de acordo com a linha da meta, ficando vermelho caso a coluna esteja abaixo da linha da meta, e verde caso a coluna esteja acima da linha da meta. Com isso você já tem um controle dos seus resultados.

Inserir os rótulos dos dados

Apesar do nosso controle de resultados já estar funcionando do jeito que foi planejado, precisamos inserir os rótulos dos dados, assim quando alguém bater o olho no controle de resultados, ela além de saber se o resultado está abaixo ou acima da meta, saberá também quais são os valores exatos do resultado.

Clique encima de uma das barras vermelhas do gráfico e certifique-se que as outras barras vermelhas fiquem selecionadas também.

Ao clicar em uma das barras, as outras de mesma cor deveram também ficar selecionadas. Após o clique, um sinal de adição irá aparecer do lado direito do gráfico, clique encima dele.

Ao clicar no sinal de adição, uma janela com os elementos do gráfico irá aparecer. Vá em “Rótulos de Dados”, certifique-se que ele esteja desmarcado e clique na seta apontando para a direita, do lado do texto “Rótulos de Dados”. Uma outra janela irá aparecer, agora clique em “Mais Opções”.

Uma terceira janela irá aparecer do lado direito da tela. Certifique-se que o ícone do gráfico de barras esteja selecionado. Depois role a barra de rolagem para baixo até encontrar o item “Número”. Antes de clicar encima do item “Número”, vá no item “Posição do Rótulo”, que fica logo acima, e selecione o item “Base Interna”. Agora sim, clique encima do item “Número”.

Ao clicar no item “Número” uma nova sessão irá abrir embaixo dele. Role a barra de rolagem novamente para baixo.

Na “Categoria” selecione a opção “Personalizado”. Vá em “Código de Formatação” e insira o seguinte código:

• [=0]””Geral

Depois clique em “Adicionar”. O código que você acabou de inserir deve aparecer em “Tipo”.

Após realizar essas configurações o seu controle de resultados deve estar com o seguinte layout:

Agora precisamos realizar o mesmo procedimento, só que nas barras verdes e para isso, clique com o botão direito do mouse em uma das barras verdes, certificando que todas as barras verdes estejam selecionadas, e repita novamente o procedimento explicado logo acima.

Após realizar essa configuração, o seu controle de meta e resultado, deverá estar da seguinte forma:

Agora para finalizar essa parte, só falta incluir os rótulos das metas. E isso é bem fácil.

Clique em cima da linha azul. O sinal de adição irá aparecer do lado direito do gráfico. Clique encima dele.

Uma janela irá se abrir do lado direito. Vá em “Rótulos de Dados” e clique encima da seta apontando para a direita.

Na janela que irá se abrir clique em “Mais Opções”.

Uma última janela irá se abrir do lado direito da tela. Role a barra de rolagem para baixo até o final. Em “Posição do Rótulo” selecione a opção “Acima”.

Após realizar a configuração da posição do rótulo encima da linha da meta. O controle de resultados deve estar da seguinte forma.

Últimos Detalhes do Gráfico de Meta e Realizado

Para concluir o nosso gráfico de meta e resultado, para que ele fique igual ao apresentado no início desse artigo,  iremos fazer mais duas configurações bem simples. A primeira é centralizar as barras. Se você notar, a barras estão um pouco desalinhadas em relação ao meses. E por último iremos aumentar a largura das barras. Dessa forma o controle de resultados irá ficar melhor para visualizar em apresentações.

Para centralizar as barras do gráfico e também aumentar a largura das barras, é uma configuração bem simples e feita na mesma janela. Clique com o botão direito do mouse encima de uma das barras e depois clique em “Formatar Séries de Dados”.

Uma nova janela irá se abrir do lado direito da tela.

1. Garanta que o ícone do gráfico de barras(Opções de Série) esteja selecionado.
2. Altere os dados do item “Sobreposição de Séries” para 100%. Isso fará com que as barras visíveis sobreponham as barras que tem valor zerado.
3. Altere o valor da “Largura do Espaçamento” para 50%. Esse alteração fará com que somente 25% de cada lado da barra tenha espaço em branco.

Após realizar as configurações o seu controle de metas e resultados deve estar da seguinte forma:

Pronto, agora sim nós temos um controle de meta e resultado finalizado e bem claro, sabendo quais os valores das metas mensais assim como os resultados. Sem contar que você pode alterar os valores do resultados mensais que as cores das barras irão alterar conforme a meta. Ficando vermelho se ficar abaixo da meta e verde se ficar igual ou  acima da meta.

Só falta você decidir qual o título que irá colocar no gráfico e ele estará pronto para ser utilizado em uma apresentação.

Conclusão

Nesse artigo você aprendeu a como montar um controle de meta e resultado, aonde a cor do gráfico de barras é definido de acordo com o valor da meta. Se o valor do resultado ficar abaixo do resultado da meta, a cor do gráfico de colunas irá ficar vermelho e se for igual ou acima do valor da meta, a barra do gráfico de colunas irá ficar verde.

Mostramos também, como utilizar a função SE.

Compartilhe ou Comente

Chegamos ao final do artigo. Se você curtiu esse artigo compartilhe com as suas redes sociais e não se esqueça de deixar um comentário aqui embaixo.