Gráfico de Meta x Realizado no Excel

Nessa publicação vou te mostrar como criar um gráfico de meta x realizado para comparar o que foi previsto e o que realmente aconteceu!

O gráfico de meta x realizado no Excel mostra quanto já foi alcançado e quanto ainda falta para bater a meta. Crie uma coluna de faturamento acumulado (acumulado do mês anterior mais o faturamento do mês) e outra de meta restante (meta travada menos o acumulado). Depois insira um gráfico de Coluna Empilhada com as duas séries.

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso Canal do YouTube para mais vídeos!

O que você aprende neste vídeo

Resposta rápida: O gráfico de meta x realizado no Excel é um gráfico de Coluna Empilhada com duas séries: o faturamento acumulado e a meta restante. O acumulado soma o valor da linha de cima ao faturamento do mês, e a meta restante é a meta do ano menos esse acumulado, com a célula da meta travada. As duas sempre somam a meta.

Neste vídeo (17 min):

• 1:00 - A base do exemplo: os 12 meses numa coluna e o faturamento de cada mês na coluna ao lado.
• 2:00 - As duas colunas que o gráfico precisa: faturamento acumulado e meta restante.
• 3:00 - A conta da meta restante: o total do ano menos o acumulado até aquele mês.
• 4:00 - Por que as duas séries sempre somam a meta, que é o que cria o efeito visual da barra cheia.
• 5:00 - Criar os cabeçalhos das duas colunas novas e herdar o formato de moeda com o pincel de formatação.
• 7:00 - A fórmula do acumulado: =C2+B3, o valor de cima mais o faturamento do lado.
• 9:00 - Puxar a fórmula até dezembro com duplo clique na alça de preenchimento.
• 10:00 - Travar a célula da meta com F4 antes de arrastar a coluna de meta restante.
• 11:00 - Inserir o gráfico de Coluna Empilhada, que é o tipo que empilha as duas séries na mesma barra.
• 12:00 - Abrir Selecionar Dados no botão direito para montar as séries à mão.
• 13:00 - Adicionar as duas séries, apontando cada uma para a sua coluna de valores.
• 14:00 - Editar os rótulos do eixo horizontal para mostrar os meses no lugar de 1 a 12.
• 15:00 - Por que empilhada e não agrupada: a agrupada põe as séries lado a lado e perde a leitura da meta.

Trechos do vídeo:

• A lógica do acumulado é sempre a mesma: somar o valor da célula de cima com o faturamento da linha atual, o que permite arrastar a mesma fórmula até o último mês.
• A célula da meta do ano precisa ser travada antes de arrastar a coluna de meta restante, senão a referência desce uma linha a cada mês e a conta quebra.

Para baixar a planilha utilizada nessa aula clique aqui!

O que é um Gráfico de Meta?

O gráfico de metas é um gráfico que mostra ao usuário quanto foi atingido por mês em uma empresa, por exemplo, e quanto está faltando para que essa empresa possa atingir a meta. É basicamente para mostrar como está o desempenho da empresa e o que está faltando para concluir a meta. Isso é bom para que a empresa verifique se precisa se esforçar mais em um determinado mês para poder chegar nas metas.

Quando utilizar o Gráfico de Meta?

Esse gráfico será utilizado sempre que for necessário analisar o faturamento em relação as metas em um determinado período. Neste caso vamos analisar o faturamento de uma empresa no ano de 2019 e verificar como a empresa se comporta durante esse tempo para pode atingir essa meta ao final do ano.

Excel Impressionador

Tudo que você precisa saber, do Básico ao Avançado, pra dominar a ferramenta mais importante do Mercado de Trabalho. Aprenda as principais ferramentas, funções e recursos do Excel para deixar qualquer planilha mais chamativa e eficiente.

Começar agora

Como utilizar esse tipo de Gráfico?

O primeiro passo é obter a tabela com as datas, os faturamentos e a meta.

O primeiro passo é criarmos uma coluna de faturamento acumulado, que é a quantidade somada até o mês analisado, ou seja, para o mês de fevereiro teremos o faturamento de janeiro mais fevereiro totalizando 58 mil, para o mês de março teremos 58 mil mais 25 mil e assim por diante.

Resumindo, o faturamento acumulado é a soma dos faturamentos até o mês de análise.

Lembrando que como janeiro é o primeiro mês e não temos outro mês anterior a ele o faturamento acumulado deste mês será ele mesmo.

A fórmula é bem simples, é o valor acumulado do mês anterior somado do valor do mês atual. Depois basta copiar ou arrastar a fórmula para o restante da tabela.

Desta forma temos o faturamento acumulado mês a mês durante todo o ano de 2019. Agora vamos criar mais uma coluna que é da meta restante, ou seja, de quanto falta para alcançarmos a meta estipulada que foi de R$ 530.000,00. Então para isso o cálculo será simples, vamos subtrair da meta o valor que temos no faturamento acumulado para todos os meses.

É importante lembrar de trancar a célula B15 que contém o valor da meta para que ele não se altere quando copiarmos/arrastar a fórmula para as demais células. O trancamento pode ser feito de forma manual colocando os $ antes da linha e da coluna, ou pressionando a tecla F4 depois de selecionar a célula desejada. Feito isso basta completar a tabela com a fórmula.

Agora é possível analisar o faturamento acumulado de todos os meses até dezembro e a meta restante também até dezembro.

O próximo passo para finalizarmos com nossa análise e visualizar melhor os dados é a criação do gráfico, para isso vamos até a guia Inserir, vamos até a opção de Gráfico de coluna/barras e selecionar a opção de Coluna Empilhada.

Ao selecionar essa opção teremos um retângulo branco, que é o nosso gráfico, no entanto sem nenhum dado. Para inserir os dados vamos pressionar com o botão direito do mouse em cima do gráfico e selecionar a opção Selecionar Dados.

Será aberta uma nova janela para a seleção desses dados.

Aqui vamos preencher as informações que queremos em cada um dos eixos. Na caixa a esquerda serão as informações para o eixo X, ou seja, o eixo horizontal do gráfico e do lado direito são as informações para o eixo Y, que é o eixo vertical. Vamos agora clicar na opção Adicionar.

No nome da série vamos selecionar a célula C1 que contém o cabeçalho dos nossos dados, e em valores da série vamos selecionar da célula C2 até C13, pois é onde estão nossos dados de faturamento acumulado. Em seguida basta pressionar OK.

Agora vamos repetir o mesmo procedimento para a coluna da meta restante.

É possível verificar que na caixa a esquerda agora temos os dados das duas colunas. Agora precisamos ajeitar a caixa da direita que é a legenda do nosso gráfico, como nosso gráfico está em mês não queremos que fiquem os números de 1 a 12, é melhor que tenhamos os nomes dos meses. Para isso vamos clicar em Editar.

Aqui vamos selecionar os dados da célula A2 até A13 que são exatamente os meses que estamos analisando os dados. Em seguida basta pressionar OK e OK novamente.

Feito isso temos nosso gráfico de metas completo. Em azul temos o faturamento acumulado e em laranja temos a meta restante. É possível analisar que o gráfico representa exatamente o que temos na tabela, o faturamento acumulado vai aumentando até chegar no seu valor máximo em dezembro, no entanto notamos que a meta não foi atingida ainda, pois temos uma parte laranja no gráfico.

Para acessar conteúdo de filtro no Excel, clique aqui: https://www.hashtagtreinamentos.com/formula-filtro

Perguntas frequentes

Hashtag Treinamentos

Para acessar outras publicações de Excel Intermediário, clique aqui!