Dashboard Excel 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.

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

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

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

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

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

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

• Agora vamos colocar a barra de rolagem. Para isso clique na guia Desenvolvedor, caso não seja habilitada siga este artigo http://guiadoexcel.com.br/habilitando-a-guia-desenvolvedor-e-copiando-procedimentos-vba-sub-da-internet.
• Na guia Desenvolvedor clique no botão Inserir e selecione a barra de rolagem, desenhe-a ao lado da tabela.

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

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.

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.

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

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.

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:

=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:

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

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

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:

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

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

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

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

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

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

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:

Abraço

Marcos Rieper