Relatório Excel com Matrizes Dinâmicas
Neste artigo você aprenderá como criar um relatório no Excel usando matrizes dinâmicas.
No relatório você poderá aplicar filtros diversos em tempo real e ainda selecionar os campos que quer que sejam exibidos no seu relatório em tempo real.
O relatório acima é o que nós iremos utilizar no exemplo deste artigo.
Veja que é muito prático e dinâmico, e usamos para isso apenas uma fórmula é realmente incrível.
Base de Dados Relatório Excel
No nosso exemplo iremos criar um relatório de cobrança no Excel que contém os seguintes campos:
- Série NF
- Nota Fiscal
- Valor
- Vencimento
- Cliente
- Vendedor
- Endereço
- Cidade
- UF
- Username
- Telefone
- Ocupação
- Empresa
O nosso desafio é filtrar estas informações pelo vendedor, vencimento e ainda pelos campos que queremos exibir no relatório.
A nossa base de dados está disposta em uma planilha chamada Base relatório na nossa pasta de trabalho.
Configurações do Relatório
No nosso relatório criamos mais duas planilhas, a planilha de configurações de relatório e a própria planilha do relatório.
A planilha de configuração do relatório utilizaremos para criar todos os cálculos e configurações que irão apoiar os filtros do relatório.
Filtro das colunas
Para filtrar as colunas criamos uma tabela de apoio aonde colocamos todos os nomes das colunas do relatório, visto abaixo em Campos.
Na coluna Filtro colocamos a fórmula =SE(SUBTOTAL(3;B8)=1;[@Campos];””) aonde temos a verificação se a coluna está ou não sendo exibida.
A fórmula acima faz o seguinte: Se a informação na coluna Campos da mesma linha não estiver oculta, ou seja o subtotal de contagem retornar 1, então retornar o próprio nome do campo, senão retornar vazio.
Este cálculo é importante para exibirmos apenas os campos que desejamos no nosso relatório, veja na vídeo-aula neste artigo como utilizar.
Ao final clique sobre a tabela e na guia Inserir e selecione a opção Segmentação de Dados, selecione o campo Campos.
Coloque esta segmentação no relatório.
Filtro por Vendedores
Insira uma tabela dinâmica clicando em Inserir->Tabela dinâmica e selecione os dados do relatório.
Em seguida selecione o campo Vendedores e adicione na tabela dinâmica em Linhas.
Ao lado da tabela dinâmica adicione a fórmula =FILTRO(E8:E93;(E8:E93<>””)) substitua o intervalo da fórmula pelos dados da tabela dinâmica e selecione mais linhas abaixo.
Esta fórmula irá filtrar dinamicamente somente os vendedores que estiverem com o campo diferente de vazio, trazendo então uma lista única e limpa de vendedores que utilizaremos no relatório.
Ao final clique sobre a tabela e na guia Inserir e selecione a opção Segmentação de Dados, selecione o campo Vendedores.
Coloque esta segmentação no relatório.
Filtro de Relatório Excel por Data
O filtro de data do relatório é semelhante ao filtro por vendedor.
Clique em Inserir->Tabela dinâmica e selecione somente o campo Vencimento arrastando ele para Linhas.
Na coluna ao lado coloque a seguinte fórmula =FILTRO($I$8:$I$802;($I$8:$I$802<>””))
E por fim clique na tabela dinâmica criada e em inserir selecione a opção Linha do Tempo, esta opção irá criar um filtro de data, coloque o mesmo também no relatório.
Criando o Relatório Dinâmico no Excel
Agora que as configurações estão concluídas clique no relatório e no cabeçalho use a seguinte fórmula:
=TRANSPOR(
FILTRO(Tabela1[Campos];
(Tabela1[Campos]=Tabela1[Filtro]))
)
Esta fórmula realiza a transposição dos campos de filtro do relatório quando os mesmos estão selecionados nos filtros de colunas.
Veja que o filtro retorna todos os campos da Tabela1 quando os campos forem iguais aos dados da coluna Filtro da mesma tabela.
E por fim inclua a fórmula abaixo:
=SEERRO(FILTRO(
CLASSIFICAR(FILTRO(tCobranca;
(ÉNÚM(CORRESP(tCobranca[Vencimento];’Config relatório’!K7#;0)))*
(ÉNÚM(CORRESP(tCobranca[Vendedor];’Config relatório’!G7#;0))));4);
ÉNÚM(CORRESP(tCobranca[#Cabeçalhos];Tabela1[Filtro];0));
“Não há dados”);”Não há dados”)
A função filtro utilizada acima realiza uma busca nos dados da matriz, no caso a tabela tCobranca (vermelho) quando o s vencimentos da coluna Vencimento existirem na célula do relatório referida ao filtro de vencimento. Em amarelo
Da mesma forma temos também a coluna Vendedor que é filtrada para retornar penas quando os dados coincidirem com os dados filtrados de vendedores pela segmentação de dados. Em amarelo.
E por fim é aplicado um filtro (roxo), aonde são filtrados os dados da matriz criada anteriormente, já com os filtros de data e vendedor, e filtramos então as colunas.
Download da planilha
Para que você possa ver todas as fórmulas e entender melhor o seu uso, recomendo baixar a planilha abaixo e também assistir a vídeo-aula no topo deste artigo.
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:
