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.

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

Crie uma tabela com as colunas Série, Acima, Abaixo, Meta e Valor.
A primeira coluna é o nome do indicador, por exemplo o nome filial no nosso exemplo.
Preencha os valores para as metas e os valores dos resultados.
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.
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.

Selecione os valores da coluna Meta e pressione CTL+F1, o Excel irá criar um gráfico com os dados das metas.
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.
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.
Clique no botão Editar do Rótulo do Eixo Horizontal e selecione os dados da série. A sua tela ficará conforme abaixo.
O resultado do gráfico de meta x realizado é o abaixo.

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.

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.
Veja que no gráfico acima as séries ficaram sobrepostas, tendo a meta sobre as outras barras de Acima e Abaixo.
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.
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.
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.
Clique com o botão direito sobre o gráfico de Acima e clique na opção Adicionar Rótulos de Dados.
Clique sobre o rótulo de dados com o botão direito e selecione a opção Formatar Rótulos de Dados.
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.
Veja que os valores 0 foram formatados como vazio.
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.
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;;@.

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.

Baixe a planilha

Abraço

Marcos Rieper

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo:

Avalie este post