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:
- Valor atual: Ele irá ser alterado conforme for movimentada a barra, portanto não é necessário alterá-lo.
- Valor mínimo: Digite 0.
- 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.
- Alteração incremental: A alteração é sempre de 1 neste caso, para ele deslocar de linha em linha.
- Mudança de página: Para ao clicar na barra não no botão pular de 10 em 10.
- 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.
- 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: Baixe a planilha
Abraço
Marcos Rieper