KHIZHNYAKOVComo montar um relatório a partir de uma exportação de dados no Excel
Relatório a partir de Exportação de Dados em Excel — Modelo Excel — Ver a planilha →
Quase todo sistema de gestão tem um botão "exportar para Excel". O problema vem depois: o arquivo é uma lista de milhares de linhas e alguém precisa transformá-la, todo mês, em totais, comparativos e gráficos. Esse trabalho manual consome horas e é fácil errar.
Existe um jeito mais metódico: preparar uma estrutura fixa em que você cola os dados e o resumo se atualiza sozinho. Este guia mostra como.
Passo 1: padronize as colunas da exportação
Uma exportação típica tem data, categoria, contraparte (cliente ou fornecedor), quantidade e valor. Cole-a numa aba de dados, com cabeçalho na primeira linha, e ao lado crie colunas auxiliares:
| Coluna auxiliar | Fórmula | |
|---|---|---|
| Mês | =MÊS(A2) | |
| Ano | =ANO(A2) | |
| Chave mês-categoria | `=B2&" | "&MÊS(A2)` |
As colunas auxiliares transformam dados soltos em algo que SOMASES consegue agrupar.
Passo 2: totais por mês
Num resumo, liste os meses de 1 a 12 e some:
=SOMASES(Dados!$E:$E;Dados!$G:$G;A2;Dados!$H:$H;AnoDoRelatório)
Aqui E é o valor, G o mês e H o ano. O ano do relatório fica em uma célula de parâmetros; mudar de ano é trocar um número.
Passo 3: crescimento mês a mês e ano a ano
Com os totais, o crescimento sobre o mês anterior é:
=SEERRO(B3/B2-1;"")
Se janeiro somou R$ 80.000,00 e fevereiro R$ 92.000,00, o crescimento é 92.000 ÷ 80.000 - 1 = 15%. Para comparar com o mesmo mês do ano anterior, repita a SOMASES com o ano menos um e divida.
Passo 4: ranking de categorias e contrapartes
Para encontrar as 5 principais categorias, calcule o total de cada uma e use MAIOR:
=MAIOR(Totais!B:B;1) até 5, e =ÍNDICE(Totais!A:A;CORRESP(D2;Totais!B:B;0)) para buscar o nome. Em caso de empate, o nome sai repetido; resolva somando uma fração mínima por linha na coluna auxiliar.
Passo 5: matriz categoria por mês
Uma tabela com categorias nas linhas e meses nas colunas, cada célula com SOMASES, mostra de imediato onde está a sazonalidade. Aplique escala de cores em Formatação Condicional para destacar os picos.
Passo 6: valide antes de confiar
A maioria dos relatórios errados nasce de dados ruins, não de fórmulas ruins. Crie uma aba de verificação que conte:
- datas em branco:
=CONT.VAZIO(A2:A5000) - números gravados como texto:
=SOMARPRODUTO(--É.NÃO.NUM(E2:E5000)) - datas fora do período do relatório
- linhas duplicadas, com
=CONT.SES(A:A;A2;B:B;B2;E:E;E2)>1
Se aparecer algo, corrija na fonte antes de usar o resumo.
Erros comuns
- Colar a exportação por cima das colunas auxiliares e apagar as fórmulas.
- Misturar exportações de períodos diferentes sem filtrar o ano.
- Esquecer que o sistema exporta datas como texto em certos formatos.
- Calcular crescimento com mês anterior vazio, causando divisão por zero.
- Fazer o ranking sem tratar empates.
Modelo pronto de relatório a partir de exportação
Para quem não quer construir isso do zero, há um modelo pago. A aba «Parâmetros» tem o ano do relatório e os nomes das colunas da sua exportação, que se ajustam ao seu sistema. «Dados» traz o cabeçalho e 5000 linhas para colar, com colunas auxiliares de normalização (mês, ano, chaves).
A aba «Resumo» mostra totais por mês, crescimento sobre o mês anterior e sobre o mesmo mês do ano passado, as 5 principais categorias, as 10 principais contrapartes, a matriz «categoria × mês» e gráficos de colunas e de pizza. Em «Seleção de período» você vê valor e quantidade de qualquer período para as categorias escolhidas, e «Verificação de dados» aponta datas em branco, texto no lugar de números, datas fora do período e duplicatas, com contadores e explicações. Os limites são 5000 linhas, 30 categorias e 200 contrapartes. Funciona no Excel 2007 e posteriores e no LibreOffice, sem macros e sem funções novas, em português do Brasil.
Perguntas frequentes
Qualquer exportação serve?
Serve qualquer tabela com data, categoria, quantidade e valor. Se os nomes das colunas forem diferentes, ajuste-os nos parâmetros do modelo ou na sua estrutura.
Por que não usar logo uma tabela dinâmica?
A tabela dinâmica é ótima para explorar, mas exige atualização e reconfiguração a cada nova exportação. Uma estrutura com fórmulas fixas é mais previsível para relatórios recorrentes.
O que fazer se tiver mais de 5000 linhas?
Divida a exportação por trimestre ou por categoria, gerando um relatório para cada parte, ou consolide primeiro no sistema de origem.
Como garantir que os totais batem com o sistema?
Compare o total geral do resumo com o total exibido no próprio sistema para o mesmo período. Diferenças quase sempre apontam para duplicatas ou datas fora do período.
