Como criar relatório no Excel
Neste artigo você verá como criar relatório no Excel é rápido e simples. Esta técnica irá encantar o seu cliente com a praticidade e velocidade.
O resultado final do relatório será que ao digitar os parâmetros do filtro de data inicial e final e selecionar um vendedor em uma combo, os dados serão automaticamente filtrados no relatório do seu cliente.
Neste artigo iremos focar nas novas funções de matrizes dinâmicas, disponíveis no Microsoft Excel 365, logo se a sua versão não é esta não irá funcionar.
Estrutura do relatório
Para a estrutura da pasta de trabalho do relatório temos três planilhas:
- Base relatório: Nesta planilha temos os dados da base que será utilizados na criação do relatório. Eles podem ter sido colados ou retornados por consultas automáticas do PowerQuery no Excel.
- Config relatório: Planilha auxiliar aonde são realizados cálculos ou criadas listas que auxiliam no relatório.
- Relatório: Utilizando os dados da base, retornamos conforme os parâmetros definidos somente os dados solicitados pelo cliente.
Preparando os parâmetros do relatório
Para este exemplo estamos criando um relatório Excel de cobrança, aonde temos os dados das vendas incluindo os vendedores, que serão responsáveis por acionar os clientes.
Teremos três campos de filtro, Data inicial, Data final e Vendedor.
Nos campos de data iremos apenas digitar a data inicial e final, e para o filtro de vendedor iremos selecionar em uma combo um dos vendedores para o relatório.
Clique na planilha Config relatório e selecione uma célula, nela iremos realizar a nossa lista com vendedores únicos e classificada que será utilizada no Relatório.
Para isso usamos a fórmula seguinte:
=CLASSIFICAR(ÚNICO(Tabela4[Vendedor]);1)
A função Único, é utilizada para que seja retornada uma lista sem repetições de forma automática, basta colocar a coluna Vendedor da tabela 4 como parâmetro.
Após esta lista de dados únicos retornado pela função, a Classificar realiza a classificação automática destes dados conforme a coluna 1, dado que só temos uma lista com uma coluna, a coluna Vendedor.
O resultado é uma lista de vendedores sem repetição e classificada conforme a imagem.
Vale ressaltar que mudando os dados da coluna Vendedores na tabela, os mesmos serão alterados dinamicamente nesta fórmula, graças às funções de matrizes dinâmicas.
Parâmetros e estrutura do relatório Excel
Agora para o relatório criamos primeiro uma estrutura simples aonde temos os parâmetros e os cabeçalhos das colunas.
Clique no parâmetro Vendedor em C5, e depois em Dados->Validação de dados e selecione em Configurações -> Permitir a opção Lista.
Conforme na imagem, em Fonte, selecione a primeira célula da lista que criamos em Config relatório no e digite # ao final.
Isto fará com que a sua célula seja entendida como um intervalo de matriz dinâmica, retornando então toda a lista de vendedores conforme apresentada na planilha.
Sendo assim, com a estrutura preparada, já podemos criar o relatório.
Relatório automático no Excel
Clique na primeira célula do Relatório, logo abaixo do cabeçalho e coloque a seguinte fórmula:
=SEERRO(CLASSIFICAR(
FILTRO(Tabela4;
(Tabela4[Vencimento]>=Relatório!$C$3)*
(Tabela4[Vencimento]<=Relatório!$E$3)*
(Tabela4[Vendedor]=Relatório!$C$5);
“Não há dados”);
4);
“Não há dados”)
Função Filtro
Em vermelho nós temos a função FILTRO, esta função permite que sejam filtrados automaticamente somente os dados definidos pelos critérios.
O primeiro parâmetro desta função é a fonte de dados, de onde serão buscados os dados.
O segundo parâmetro são os filtros. Para adicioná-los você deve utilizar ( ) para cada parâmetro, por exemplo (Tabela4[Vencimento]>=Relatório!$C$3) é a primeira data, a data inicial, veja que ela verifica se na fonte de dados, tabela4, na coluna Vencimento o valor é maior ou igual (>=) que o parâmetro de data inicial do relatório.
Para adicionar novos parâmetros você deve sempre usar um operador * que é equivalente a E ou o operador + que é equivalente a OU. No nosso caso usamos apenas o operador E(*).
No segundo parâmetro de filtro unimos ele então utilizando * (Tabela4[Vencimento]<=Relatório!$E$3) , aqui verificamos se a data é menor ou igual a data final no parâmetro do relatório.
E por último verificamos na coluna Vendedor se o nome do vendedor é igual ao que está selecionado no parâmetro do relatório (Tabela4[Vendedor]=Relatório!$C$5).
Feito isso, o último parâmetro da função FILTRO é semelhante ao da função SEERRO, ou seja, se não encontrar dados, o que ela deve retornar. No nosso caso colocamos o texto “Não ha dados”.
Função Classificar
Acima desta função nós utilizamos novamente, como fizemos para a nossa lista de Vendedores, o uso da função CLASSIFICAR.
Nela realizamos a classificação dos dados filtrados pela coluna 4, no caso a coluna referente a data de vencimento.
Função Seerro
E por último usamos a função SEERRO, pois se não retornar dados deve retornar “Não há dados”.
O resultado final é um relatório automático que ao mudar qualquer parâmetro é recalculado imediatamente e de forma muito rápida.
Download Relatório Excel
Faça o download da planilha de exemplo preenchendo o formulário de e-mail para o download. E no vídeo no topo do artigo você pode assistir esta explicação.
Baixe a planilhaCurso Excel Completo – Do Básico ao VBA
Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo:
