quinta-feira, 3 de julho de 2014

Excel - Criando o meu primeiro Dashboard


Segundo definição da Microsoft, "um dashboard é um contentor de vários tipos de relatório, incluindo tabelas de indicadores. Pode ser composto por uma ou mais páginas (ou planilhas) e pode ter mais do que um gráfico ou tabela em cada uma."



Queremos demonstrar neste artigo o passo-a-passo, conceitual e também na prática, todo o processo de criação de um pequeno Dashboard. Os conhecimentos adquiridos neste exercício poderão ser utilizados e infinitamente ampliados.








Definição do nosso dashboard modelo

Dashboard modelo que iremos desenvolver neste artigo
Nosso pequeno dashboard apresentará uma tabela com os vendedores da empresa e seus respectivos desempenhos mensais de venda. A partir dessa tabela teremos dois gráficos: 

- O primeiro será um gráfico de linhas demonstrando a evolução mensal das vendas de um vendedor selecionado;
- o segundo será um gráfico de torta com o comparativo entre vendedores de um mês específico.








Criando a tabela de vendas

Crie uma tabela contendo os meses de Janeiro a Junho, dois vendedores e seus respectivos valores mensais conforme o modelo ao lado. Tente respeitar, rigorosamente, as posições para evitar problemas no acompanhamento dos exercícios.
















Criando os controles para seleção de mês e vendedor

Para a criação dos controle utilizaremos uma técnica discutida no artigo Excel - Você sabe utilizar "Controles de Formulários"?

- Crie uma caixa de combinação mais ou menos na mesma localização e proporção da apresentada abaixo:



- Iremos agora definir qual a origem dos valores que irão preencher a caixa e qual a sua célula vinculada (a célula que armazenará o número do mês selecionado). Clique com o botão da direita sobre a caixa e selecione FORMATAR CONTROLE:


- Na aba Controle, selecione os meses (b3:b8) em INTERVALO DE ENTRADA e a célula K16 como célula vinculada


Note, na tela abaixo, que o mês selecionado na caixa é FEVEREIRO. Fevereiro é o segundo elemento da lista, logo, a célula vinculada (K16) recebeu o número 2. Não tem nada a ver com o fato de fevereiro ser o mês número 2. Se alterarmos a seleção para MAIO, por exemplo, a célula vinculada receberá o valor 5 por ser o quinto da lista e assim por diante. 



- Crie o segundo controle caixa de combinação, o que será utilizado para selecionarmos o vendedor.


Dica Importante: O intervalo de entrada para caixas de combinação precisa ser informados SEMPRE NA VERTICAL. Abaixo demonstraremos o que acontece quando não observamos essa regra:




Portanto, devemos criar, em algum locar da planilha, uma lista VERTICAL contendo os nomes dos vendedores que serão utilizados no preenchimento da caixa de combinação. Veja abaixo:

 

- Na concepção deste artigo, para criar a lista de vendedores na vertical, optamos por utilizar a FUNÇÃO MATRICIAL TRANSPOR, mas podemos simplesmente redigitar os nome, uma vez que são apenas dois no exercício.

- Clique com o botão da direita sobre esse controle e selecione FORMATAR CONTROLE.
- Na aba Controle, selecione os meses (b11:b12) em INTERVALO DE ENTRADA e a célula K15 como célula vinculada

- Note que quando alteramos o VENDEDOR SELECIONADO e o MÊS SELECIONADO as células K15 e K16 alteram recebem os números dos elementos escolhidos.


Agora, para apresentarmos os gráficos ao final, respeitando os elementos selecionados, precisaremos criar uma área intermediária de dados. Essa área terá dois resumos: O primeiro será a evolução das vendas de um determinado vendedor nos meses de janeiro a junho e o segundo terá o comparativo entre os vendedores no mês escolhido.

Criando a tabela que alimentará o gráfico de desempenho do vendedor

- Crie um quadro conforme abaixo:

 O nome do vendedor deverá ser uma fórmula que utilizará a FUNÇÃO ÍNDICE para buscar o nome conforme o nome escolhido na caixa de combinação VENDEDORES. Escreva conforme a ilustração abaixo demonstra:


- Uma vez feita a primeira fórmula (NOME DO VENDEDOR), copie para as células abaixo para que os valores mensais do vendedor também sejam trazidos. Abaixo a fórmula já copiada:



Note que, quando alteramos o vendedor selecionado na caixa de combinação, o índice é alterado (ORDEM DO VENDEDOR) e por consequência o resumo todo altera pois está vinculado ao índice.


Criando o Gráfico de Desempenho Mensal

- Selecione o resumo que acabamos de criar, que está no endereço F11:G17, conforme abaixo:


- Com o intervalo selecionado, vamos no menu INSERIR, e selecionamos GRÁFICO DE LINHA, conforme a ilustração abaixo:


- Reposicione e formate o gráfico do modo que desejar. Sugerimos que seja posicionado conforme abaixo para facilitar a compreensão.


- A formatação é livre. Note nas ilustrações abaixo que, ao selecionarmos um vendedor, o índice (K15) altera, alterando o resumo e por consequência, altera o gráfico.



Criando a tabela que alimentará o gráfico de torta

Seguindo as mesmas premissas do resumo criado anteriormente, criaremos agora outro que será utilizado como origem de dados para o gráfico de torta.

- Crie, o novo resumo, também utilizando a função índice para buscar os dados do mês selecionado e os respectivos valores dos vendedores.




Note que ao alterarmos o mês selecionado, o índice (K16) é alterado e o resumo também, por consequência.




-Selecione agora as células de I12:K13, conforme abaixo:


- Agora crie um gráfico de torta, baseado na seleção, e posicione-o conforme ilustrado:



Pronto! Seu primeiro dashboard já funciona. Altere o mês e o vendedor e veja que agora, com apenas dois gráficos, você consegue efetuar um número grande de combinações de exibição.

Para melhorar a apresentação, podemos esconder os resumos de dados, abaixo ilustrados em amarelo. Podemos mover para atrás dos gráficos, ou para algumas linhas abaixo do campo de visão ou mesmo pintar FUNDO, LETRA E BORDAS de branco.


Neste artigo optamos por mover os quatros para atrás dos gráficos. Note como fica mais limpa a apresentação:






Para dar um pouco mais de noção de os dados mudam constantemente, coisa típica dos dashboards, faremos uma brincadeira aqui utilizando uma função que, apesar de pouco utilizada, tem grande importância quando queremos fazer simulações. É a função "ALEATORIO()".

A função ALEATORIO() retorna, aleatoriamente, um valor entre 0 e 1 a cada vez que a planilha é recalculada. Ouseja, quando ocorre qualquer alteração de valor na planilha, os valores da ALEATORIO() mudam.

Aplicando ao modelo...

Selecionem o intervalo C3:D8, e deletem seu conteúdo


Com o intervalo C3:D8 marcado, digitem a fórmula "=ALEATÓRIO()*1000" e em seguida, teclem CTRL+ENTER para replicar automaticamente a fórmula digitada para todas as células do intervalo.


Estamos multiplicando por 1000 para que possamos ter valores mais altos, que estarão na faixa entre 0 e 1000. Notem abaixo que as células foram preenchidas conforme esperávamos.


A cada recálculo os valores mudam aleatoriamente. Notem abaixo:



Agora iremos explorar um pouco os recursos de FORMATAÇÃO AUTOMÁTICA

Com o intervalo C3:D8 selecionado, podemos escolher uma detre tantas formas de formatação automática. Abaixo escolhemos destacar os valores que estiverem abaixo da média (ele apura a média com base no próprio intervalo selecionado)


Definimos como destacar os valores....


Abaixo vemos que os valores abaixo da média estão devidamente destacados.


Agora estamos mudando a formatação automática para o padrão CONJUNTO DE ÍCONES.


Notem que o Excel dividiu os valores por faixas e, dependendo da faixa, aplicou um ícone diferente.


A cada recálculo, os valores mudam aleatoriamente e, por consequência, todos os ícones.






Esperamos que você tenha conseguido executar nosso passo-a-passo.

Assistam também ao vídeo "Videoaula Excel: Já ouviu falar em Segmentação de Dados?". Nele, criamos um Dashboard mais sofisticado, já utilizando tabelas dinâmicas e segmentação de dados.



Compre nosso material de estudo:



Para fazer o download do modelo utilizado, já concluído, CLIQUE AQUI


Estamos aqui, à disposição, aceitando sugestões para novos artigos.

Aproveitem e conheçam o site da nossa empresa: www.excelline.net.


Leiam também outros artigos em NOSSO BLOG.

Contem conosco!

Agosto/2016


ExcelLine - Office World


Related Posts Plugin for WordPress, Blogger...

Compartilhe