Dashboard Excel Gráfico com Meta

5
Dashboard Gráfico com Meta

Objetivo: Demonstrar como criar um Dashboard Excel com gráfico e meta, barra de rolagem, botões de seleção e gráfico que altera as cores para acima e abaixo da meta.

Dashboard Gráfico com MetaA primeira etapa para criarmos o nosso dashboard é você criar uma base de dados conforme a sua necessidade.

No nosso exemplo utilizaremos um exemplo de faturamento por loja e ano. O objetivo do dashboard é demonstrar o faturamento de cada loja por ano.

Sendo assim, vamos criar primeiro a tabela e depois o gráfico, conforme seguimos.

  • Crie uma base de dados em uma planilha que servirá somente como repositório. Veja o exemplo e adapte a tabela a sua necessidade.

Base dashboard Excel

  • Em uma planilha que servirá a visualização dos dados crie uma grade neste layout:

Base dashboard Excel 2

  • Na célula logo abaixo da palavra Loja digite a seguinte fórmula, julgando que a sua base de dados esteja na Plan2 e o seu Dashboard Excel esteja na Plan1:

Base dashboard Excel 3

=DESLOC(Plan2!A2;$A$1;0)

Esta função fará com que ao ser alterado o valor da célula $A$1 seja deslocado o resultado da célula em quantas colunas estiverem demonstradas nesta célula, exemplo: 1 uma linha para cima, 2 duas linhas para baixo e assim por diante.

Não é o caso do nosso exemplo, mas caso houvessem números negativos nesta célula a linha seria deslocada acima.

Parâmetros da função DESLOC:

ref: referência de células, no caso foi selecionada a célula Plan2!A2.

lins: passado como 0, deslocaria células para baixo ou para cima de um intervalo, exemplo caso a referência fosse A1 e o parâmetro lins fosse 1, deslocaria a referência para A2 ou seja uma linha para baixo.

cols: quantidade de colunas deslocada para a esquerda ou direita. Exemplo para a referência Plan2!A2 e caso o parâmetro cols fosse 1, deslocaria para Plan2!B2, ou seja, uma coluna para a direita.

[altura]: opcional. A altura, em número de linhas, que se deseja para a referência fornecida. Altura deve ser um número positivo.

[largura]: opcional. A largura, em número de colunas, que se deseja para a referência fornecida. Largura deve ser um número positivo.

  • Arraste a função para as outras células abaixo na mesma coluna para que sejam preenchidas automaticamente.

Base dashboard Excel 4

  • Na coluna logo á direita digite a seguinte fórmula:

Base dashboard Excel 5=DESLOC(Plan2!B2;Plan1!$A$1;Plan1!$C$1-1)

Note que nesta função também é alterada a coluna, não somente a linha como também é deslocada a coluna conforme os parâmetros vistos na função DESLOC.

Base dashboard Excel 6

  • Clique na barra de rolagem e coloque os seguintes dados:

Base dashboard Excel 7

Os campos que devem ser preenchidos na barra de rolagem Excel são:

  1. Valor atual: Ele irá ser alterado conforme for movimentada a barra, portanto não é necessário alterá-lo.
  2. Valor mínimo: Digite 0.
  3. Valor máximo: O valor máximo é a quantidade de registros da sua lista menos a posição em que inicia a sua planilha, no nosso caso como inicia na linha 5 e temos 58 linhas na base de dados temos 58-5 = 53.
  4. Alteração incremental: A alteração é sempre de 1 neste caso, para ele deslocar de linha em linha.
  5. Mudança de página: Para ao clicar na barra não no botão pular de 10 em 10.
  6. Vínculo da célula: É esta célula que é lida pela nossa fórmula criada anteriormente, portando você deve colocar a mesma da fórmula, no nosso exemplo é a $A$1.
  7. Sombreamento 3D: Efeito para ficar 3D a barra.

Agora que a barra já está configurada você já pode testar movimentando-a e verificando se os valores da tabela são alterados.

  • Agora que já temos a barra configurada clique novamente na guia Desenvolvedor e selecione o botão de opção, desenhando 4 sobre a tabela.

Base dashboard Excel 8

Após desenhados clique sobre cada um deles e clique em formatar Controle, mude a propriedade Valor para Selecionado e o vínculo de célula para a célula apontada na sua função. No nosso caso é a célula $C$1 e para alterar o valor que está escrito nos botões clique em Texto Alt. e altere o valor conforme a sua necessidade, no nosso caso são 2009, 2010, 2011 e 2012.

Base dashboard Excel 9

  • Como todas estão apontando para a mesma célula esta será alterada para 1, 2, 3 ou 4 conforme a série em que foram inseridos, teste se os valores estão respeitando as colunas conforme são alteradas.
  • Teremos então como resultado:

Base dashboard Excel 10

Agora vamos criar o gráfico:

  • Vamos primeiro preparar os dados do gráfico. Clique na Plan2 e na coluna G digite os seguintes valores que serão a nossa meta.

Base dashboard Excel 11

Perceba que os dados são iguais porque é como se a meta da empresa fosse fixa para todos, porém também poderia ser alcançado o mesmo resultado se fosse inclusa a meta por loja na tabela que criamos anteriormente.

  • Nas células I1 e I2 digite os seguintes valores. I1 -> Acima da meta, I2 ->Abaixo da meta
  • Na célula I2 digite a seguinte função:

Base dashboard Excel 12

=SE(Plan1!D5>=Plan2!$G$2;Plan1!D5;””)

Esta função está apontando para a planilha 1 e verifica se o valor da célula D5 que é a primeira célula da tabela é maior ou igual ao valor da meta.

 

  • Arraste a função para as 3 linhas logo abaixo desta célula em que foi adicionada a função.
  • Repita a mesma função, na coluna Abaixo da meta alterando a função apenas o sinal de >= para < conforme a fórmula:

Base dashboard Excel 13

=SE(Plan1!D5<Plan2!$G$2;Plan1!D5;””)

  • Sendo assim teremos em uma coluna somente os valores que estão acima da meta e na outra somente os valores abaixo da meta.
  • Clique na Plan1 e clique em Inserir->Gráfico de Colunas.

Base dashboard Excel 14

  • Selecione o gráfico de barras e coloque-o logo abaixo da tabela, com um tamanho adequado ao seu layout.
  • Clique no gráfico e na opção guia Design->Selecionar Dados.
  • Em Entradas de Legenda (Série) clique em adicionar e selecione:

Base dashboard Excel 15

Perceba que no nome da série foi selecionada a célula em que se encontra o nome Acima da meta e na coluna de valores da série os valores desta coluna.

  • Repita esta operação para as colunas Abaixo da meta e Meta de modo que teremos três séries:

Base dashboard Excel 16

  • O gráfico ficará com uma aparência semelhante a abaixo.

Base dashboard Excel 17

  • Perceba que a meta está também como uma coluna, assim como as outras séries, vamos alterá-la.
  • Clique sobre a coluna verde e em Alterar tipo de gráfico de série, selecione a primeira opção de gráfico de linha.

Base dashboard Excel 18

  • Clique agora sobre uma das barras e clique em Formatar série de dados.
  • Na opção Separado altere para 100% Sobreposto e na opção Sem intervalo altere para 30%

Base dashboard Excel 19

  • Ajuste o gráfico retirando as linhas clicando sobre as mesmas e pressionando o botão Delete.
  • Clique sobre o eixo lateral esquerdo e clique em Formatar Eixo.

Base dashboard Excel 20

[saiba_mais]
  • Altere os valores Máximo para um pouco acima do maior valor da sua tabela, pode usar a função =MAIOR(DADOS, 1) para identificar o maior valor, substituindo DADOS pelo seu intervalo.
  • Altere a unidade principal conforme a necessidade, no nosso exemplo alteramos para 25.000.
  • Clique em Número ainda na mesma tela de opções e clique em Personalizado, digite R$ 0. “K” e clique no botão Adicionar, depois selecione o mesmo.
  • E está pronto o seu gráfico, ele deve ficar com a seguinte aparência:

Base dashboard Excel 21

  • A aparência final do seu Dashboard deve ser a seguinte:

Base dashboard Excel 22

Então é isso, muito obrigado por ter acompanhado mais este artigo, vocês são muito especiais para mim. O download deste exemplo está disponível no botão abaixo:

DIGITE O SEU EMAIL PARA FAZER O DOWNLOAD DOS ARQUIVOS:

Seu nome (obrigatório)

Seu e-mail (obrigatório)

Abraço

Marcos Rieper

5 COMENTÁRIOS

  1. Estou tentando criar Desloc mas não estou conseguindo. criei na Plan2 a base conforme você informou sendo que o ano de 2009 está na célula B1 e o nome “Loja 1” está na célula A2. Também criei a plan1 sendo que o nome Loja está na célula A1 e o nome valor está na célula B1, e a função =DESLOC(Plan2!A2;A1;0) que eu adicionei a célula A2 da Plan1 está dando erro. O que fiz de errado… poderia me ajudar!!

    • A função DESLOC funciona da seguinte maneira:
      Deslocar(A partir de;Começar a partir de quantas linhas abaixo;Quantas colunas a direita;Quantidades de linhas a incluir na seleção;Quantidades de colunas a incluir na seleção)
      Então:
      =DESLOC(A2;1;1;0;0)
      Significa que a função irá selecionar: B3

      Talvez isso te ajude a encontrar o problema!
      Abraços!

DEIXE UMA RESPOSTA

Please enter your comment!
Please enter your name here