Blog

Postado em em 17 de outubro de 2019

Dashboard com apenas duas fórmulas: PROCV e COL

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!

O que é um Dashboard?

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.

Quando utilizar um Dashboard utilizando as funções PROCV e COL?

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.

Dashboard que será construído

Dashboard que será construído

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.

Como utilizar as funções PROCV, COL e LIN?

Vamos iniciar com uma explicação das funções COL e LIN para ver o funcionamento de cada uma delas.

Tabela para exemplificar as fórmulas COL e LIN

Tabela para exemplificar as fórmulas COL e LIN

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.

Resultado e fórmulas utilizando COL e LIN

Resultado e fórmulas utilizando COL e LIN

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.

  • No primeiro caso temos a fórmula LIN para obter a linha da célula atual, que é 2.
  • No segundo caso temos a fórmula LIN para obter a linha da célula D9, que é 9.
  • No terceiro caso temos a fórmula COL para obter a coluna da célula atual, que é 4.
  • No quarto caso temos a fórmulas COL para obter a coluna da célula J2, que é 10.

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.

Tabela de produtos com a quantidade mensal de vendas

Tabela de produtos com a quantidade mensal de vendas

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.

Tabela que será utilizada para a criação do Dashboard

Tabela que será utilizada para a criação do Dashboard

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.

Fórmula PROCV utilizada para preencher a tabela simplificada

Fórmula PROCV utilizada para preencher a tabela simplificada

Essa é uma fórmula de PROCV com a utilização da função COL no argumento de coluna.

  • No primeiro argumento temos a seleção da célula B3 que é onde temos o produto que estamos procurando, essa célula está trancada porque nossa procura sempre será essa.
  • No segundo argumento temos a seleção de onde temos os nossos dados (da tabela completa), neste caso estamos selecionando da coluna B até a coluna N.
  • No terceiro argumento temos a indicação da coluna em que vamos procurar aquele dado e para isso vamos utilizar a função COL(). É possível verificar que neste caso estamos subtraindo 1 dessa função porque ao utilizar apenas COL() estamos obtendo o valor da coluna da célula atual, que neste caso é igual a 3, porque é a coluna C. Mas a coluna que queremos está indo da coluna B até a N, no entanto é importante lembrar que dentro do PROCV o valor das colunas é considerado apenas na seleção de onde estamos procurando o valor, neste caso a coluna B é considerada como coluna 1, por isso vamos subtrair 1 para que possamos compensar a coluna A que não foi incluída.
  • No quarto argumento temos o tipo de procura que será exata, pois queremos exatamente o que está escrito na célula B3 para ser encontrado na nossa tabela de análise.

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.

Tabela após a utilização da fórmula PROCV

Tabela após a utilização da fórmula PROCV

E ao modificar o produto na célula B3 os dados serão atualizados para as quantidades corretas daquele produto em questão.

Atualização da fórmula após modificar o produto

Atualização da fórmula após modificar o produto

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.

Criação do gráfico para o Dashboard

Criação do gráfico para o Dashboard

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.

Dashboard completo

Dashboard completo

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?


Quer ter acesso a um Minicurso de Finanças no Excel de forma 100% gratuita? Preencha seu e-mail abaixo!