Quando o objetivo é montar um dashboard Excel avançado com alta velocidade de edição, extrema liberdade de layout, impressão perfeita e zero dependência de serviço online, o Excel ainda brilha.
Com Tabelas, funções dinâmicas, segmentadores, Gráficos e Power Query, é possível entregar uma visualização de dados no Excel tão responsiva quanto em Power BI.
A seguir, vamos trazer um passo a passo completo usando a base anexada.
Vamos lá!
Estruturando a Base de Dados no Excel
Observe que, temos uma extensa base de dados no Excel contendo o registro completo de vendas de uma determinada loja:
Lembre-se que é de extrema importância deixar sua base de dados formatada como tabela e devidamente nomeada, conforme verificamos acima.
Como é possível observar abaixo, temos um modelo de layout para criarmos o nosso dashboard de vendas:
Contudo, caso queira montar o seu, deixamos um vídeo do Ninja que pode te ajudar com esse ponto:
Além disso, realizamos alguns cálculos, como o custo total, onde multiplicamos a Quantidade pelo Custo Unitário:
Criamos ainda, uma coluna de Lucro, no qual subtraímos o Valor Total pelo Custo Total, obtendo o lucro por venda:
Por fim, extraíamos o ano e mês das datas, com a função TEXTO:
Cálculos com Tabela Dinâmica para Visuais
Com a base já estruturada, vamos agora criar as Tabelas Dinâmicas, que serão a espinha dorsal do nosso dashboard.
Sendo assim, selecione a tabela, vá até a guia de Inserir e clique em Tabela Dinâmica:
Após confirmar a ação, nomeie a aba como Cálculos:
Primeiramente, vamos trazer um total de vendas por ano e mês então, arraste o campo de Ano e Mês para Linhas e Valor Total em Valores:
Para o ranking de vendedores, crie uma tabela onde o Nome o Vendedor está Linhas e Valor Total em Valores:
Para o Status dos Pedidos, arraste o campo de Status para Linhas e ID em Valores para a contagem:
Lembrando que, você poderá trazer mais cálculos de acordo com a necessidade de informações que você precisa apresentar em seu dashboard.
Cálculos de KPIs
Por fim, para os KPIs, vamos com o total de vendas.
Na tabela dinâmica, arraste o campo de Valor Total para Valores, trazendo automaticamente o total:
Para o Lucro, arraste o campo de Lucro em Valores, obtendo o total
Já para o Ticket Médio, arraste o campo de Valor Total para Valores, mas, em Configurações de Campo, altere para Média:
Com isso, teremos todos os cálculos necessários para montar nosso dashboard.
Criando os Gráficos
Com as tabelas dinâmicas prontas, precisamos transformá-las em gráficos.
Então, selecione a tabela desejada, vá até a guia Inserir e selecione um modelo de gráfico:
Para o total de vendas por mês/ano, trouxemos um Gráfico de Linhas:
Para o Ranking de Vendedores, temos um Gráfico de Colunas.
Lembre-se de classificar o rótulo de linhas em ordem decrescente, considerando a coluna de valor.
Já para o Status dos Pedido, optamos pelo Gráfico de Rosca:
Por fim, você pode personalizar os visuais utilizando as cores que preferir, seja aplicando a paleta da identidade da sua empresa ou escolhendo combinações harmônicas.
Essa etapa dá um ar profissional ao dashboard e reforça a credibilidade da apresentação, chegando aos visuais abaixo:
Não se esqueça de incluir os cálculos das KPIs nos devidos visuais, conforme exemplo abaixo:
Segmentação de Dados
Como reta final, iremos acrescentar a segmentação de dados.
Então, selecione uma das tabelas dinâmicas, vá até a guia Inserir e clique em Segmentação de Dados:
Ao abrir uma janela, escolha as opções que desejar, no caso deste exemplo, vamos selecionar: Estado, Região, Forma de Pagamento e Fornecedor.
Após confirmar em OK, selecione uma das segmentações e na guia de Segmentação, clique em Conexões de Relatório:
Com isso, você poderá vincular todas as tabelas na segmentação selecionada:
No entanto, lembre-se de fazer esse processo com todas as segmentações inseridas.
Por fim, personalize as segmentações na guia acima e posicione elas no layout do dashboard, ficando desta maneira:
Assim, após alguns ajustes visuais no layout, temos o resultado abaixo, um dashboard de vendas, sendo este a primeira parte:
E esta, a segunda parte:
Portanto, com as dicas acima, você conseguirá criar um dashboard de vendas no Excel de forma simples, prática e totalmente funcional, utilizando apenas os recursos nativos da ferramenta, sem depender do Power BI.
Ou seja, esse processo que você acabou de fazer no Excel é algo que muitos só pensam que dá para fazer no Power BI, mas na verdade pode ser construído diretamente nas planilhas, com a mesma eficiência e liberdade de personalização.
Curso de Excel Completo: Do Básico ao Avançado.
Quer aprender Excel do Básico ao Avançado, passando por Dashboards? Clique na imagem abaixo e saiba mais sobre este Curso de Excel Completo.
Por fim, deixe seu comentário sobre se gostou deste post tutorial e se você irá usar essas técnicas em suas planilhas de Excel. E você também pode enviar a sua sugestão para os próximos posts. Até breve!
