Nessa publicação vou te mostrar como construir um dashboard no excel (gráfico dinâmico) apenas com duas fórmulas!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para baixar a planilha utilizada nessa aula clique aqui!
Dashboard é um painel visual com o objetivo de mostrar um conjunto de informações em forma de gráficos para que seja feita uma análise de forma mais fácil e rápida desses dados. Existem diversos tipos de painéis e gráficos que podem ser utilizados, no entanto a diferença de um Dashboard para um gráfico comum é que o Dashboard funciona como um painel, ou seja, é possível interagir com ele para que sejam mostradas diferentes informações.
Vamos nesta aula aprender sobre as funções COL e LIN para aplicar juntamente com o PROCV para a criação de um Dashboard simples, mas que poderá ser utilizado em diversas aplicações.
Teremos o seguinte Dashboard, sendo que a célula B3 tem uma validação de dados de lista onde o usuário poderá modificar qual produto deseja analisar. Ao selecionar terá os dados desse produto na tabela e também seu gráfico atualizado.
Vamos iniciar com uma explicação das funções COL e LIN para ver o funcionamento de cada uma delas.
Vamos utilizar as funções COL e LIN para preencher a coluna D desta tabela para verificar o resultado em cada uma delas, e na coluna E para facilitar será mostrada a fórmula utilizada em cada uma das células.
Temos aqui que as duas funções podem ou não ter algum argumento, sendo que quando não possuem argumento a referência delas é exatamente a célula em que a fórmula está escrita. No caso de termos uma referência o Excel irá pegar a linha ou coluna (dependendo da fórmula) da referência em questão.
Veja que o número da linha é mais intuitivo, pois o Excel nos colocar a letra da coluna e o número da linha, enquanto que para o número da coluna teríamos que contar para descobrir qual é, no entanto podemos utilizar essa fórmula para facilitar o procedimento.
Vamos agora fazer uma breve análise de uma pequena tabela de 4 produtos vendidos durante o ano que nos mostram as vendas mensais de cada um desses produtos.
Nesta tabela podemos ver a quantidade de produtos vendidos mensalmente durante um ano em uma empresa e quais foram cada um desses produtos. Na outra aba vamos criar um dashboard para obter os dados do produto selecionado e também criar um gráfico desse produto ao longo do ano para que possamos verificar de forma visual o comportamento desse produto durante o ano.
Aqui temos uma formatação prévia do nosso dashboard com uma validação de dados na célula B3. Essa célula possui uma validação de dados em lista com todas as opções de produtos que foi apresentada na tabela anterior.
Link para a aula da validação de dados: https://www.hashtagtreinamentos.com/validacao-de-dados/
Nesta tabela queremos que em cada coluna de mês apareçam as vendas que foram realizadas do produto que está na célula B3, para isso vamos utilizar a fórmula PROCV.
Link para aula de PROCV: https://www.hashtagtreinamentos.com/procv-aprenda-de-uma-vez-por-todas/
Para a célula C3 que é onde temos o primeiro valor a ser preenchido vamos utilizar a seguinte fórmula.
Essa é uma fórmula de PROCV com a utilização da função COL no argumento de coluna.
A diferença entre utilizar o COL()-1 e um número para indicar a coluna neste caso é que a fórmula não precisar ser alterada para as outras células, porque neste caso como estamos fazendo uma análise variando as colunas sempre teremos o valor da coluna sendo atualizado de acordo com a célula atual. Então para que o usuário não tenha que ficar alterando o valor das colunas de 1 até 12 para indicar os meses do ano basta utilizar como argumento de coluna a fórmula COL()-1.
Portanto ao arrastar a fórmula para as outras células teremos nossa tabela completa exatamente com os mesmos dados que temos na tabela de análise que contém todos os produtos.
E ao modificar o produto na célula B3 os dados serão atualizados para as quantidades corretas daquele produto em questão.
Para finalizar vamos a criação do gráfico para podermos observar de forma visual o que acontece com a quantidade de produtos ao longo do ano. Para isso basta selecionar os meses e as quantidades e em seguida utilizar o atalho ALT + F1.
Feito isso o gráfico será criado, em seguida basta apenas formatá-lo para ficar da melhor forma a apresentar os dados obtidos.
Link para aula de gráficos: https://www.hashtagtreinamentos.com/graficos-no-excel/
Então como resultado final teremos o seguinte gráfico formatado.
Curso de Excel no Rio de Janeiro ou em Niterói não tem nem o que pensar, nossos mais de 13.000 alunos podem atestar a qualidade e a didática de nossos cursos!
Quer aprender tudo de Excel para se tornar o destaque de qualquer empresa?
Sócio fundador e especialista em Python da Hashtag Treinamentos. Criador do Método Impressionador para ensinar pessoas comuns a crescerem na carreira e se destacarem na empresa usando o Python.