Como usar Power Query e tabelas dinâmicas?
Por que o Excel ainda e central no escritório, Power Query para dados do SPED, tabelas dinamicas aplicadas a balancetes e formulas avancadas.
O Power Query, integrado ao Excel (Dados > Obter Dados), permite importar e transformar arquivos SPED (delimitador pipe) em tabelas tratadas que se atualizam com um clique no mês seguinte. Tabelas dinâmicas aplicadas a balancetes criam resumos de receitas, custos e despesas por grupo de contas em minutos. Para volumes grandes (milhões de linhas), carregue os dados como Conexão Apenas + Modelo de Dados (Power Pivot) em vez de direto na planilha.
## Antes de big data, Excel bem usado
Nos últimos anos, a palavra de ordem no mundo dos dados foi sempre 'sair do Excel'.
Power BI, Python, Tableau — sempre tem alguma ferramenta nova prometendo ser o fim do Excel.
Mas quem trabalha em escritório contábil sabe: o Excel não vai a lugar nenhum.
E por boas razões.
Primeiro, ele e universal — todo contador sabe usar, todo cliente reconhece um arquivo .xlsx, e qualquer sistema exporta para Excel.
Segundo, ele e flexivel — você pode combinar dados, calcular, formatar e apresentar tudo na mesma ferramenta.
Terceiro, ele evoluiu muito — o Excel de 2024 com Power Query e Power Pivot e uma ferramenta de BI em si mesma, capaz de processar milhoes de linhas de dados.
Dominar o Excel avancado não é ficar para trás — e ter a ferramenta certa para o trabalho certo.
## O ponto de inflexão
O ponto de inflexao para o contador que quer dar um salto de produtividade no Excel e o Power Query.
Antes do Power Query, importar dados de um sistema externo — o SPED, o EFD, um CSV do sistema contábil — era um processo manual e repetitivo: baixar o arquivo, abrir no Excel, tratar as colunas, limpar os dados sujos, repetir no mes seguinte.
Com o Power Query, você faz esse processo uma vez, e na próxima vez basta clicar em 'Atualizar'.
O Power Query e um editor de transformação de dados integrado ao Excel (guia Dados > Obter Dados), que registra cada etapa da transformação como um passo que pode ser repetido automaticamente.
## Power Query com SPED e EFD
Como usar o Power Query com dados do SPED e EFD na prática? O arquivo SPED (ECF, EFD ICMS/IPI, EFD-Contribuições) e um arquivo de texto com registros separados por pipes (|).
Para importar: Dados > Obter Dados > De Arquivo > De Texto/CSV, selecionar o arquivo, definir o delimitador como pipe.
O Power Query ja faz a separação das colunas.
Em seguida, e necessário filtrar apenas os registros relevantes — por exemplo, apenas os registros C170 (itens de documento fiscal) ou os registros do bloco K (estoque).
Você remove as colunas desnecessarias, renomeia as que ficam, converte tipos de dado (texto para número, texto para data), e carrega a tabela tratada.
Na próxima vez que precisar atualizar com um novo arquivo, e so substituir o arquivo na mesma pasta e clicar em Atualizar — o Power Query refaz todas as etapas automaticamente.
## Tabelas dinâmicas em balancete e DRE
Tabelas dinamicas aplicadas a balancetes e DRE: a tabela dinamica e a ferramenta mais poderosa do Excel para resumir e analisar dados contábeis.
Com um balancete exportado do sistema contábil (conta, descrição, saldo), você pode criar em minutos: resumo de receitas, custos e despesas por grupo de contas; comparativo de períodos (esse mes vs. mes anterior, esse ano vs. ano anterior); análise de variação por centro de custo; e ranking de contas por saldo.
O segredo esta na preparação dos dados de origem — as colunas precisam ter cabecalhos claros, sem celulas mescladas, sem linhas em branco, sem totais dentro da tabela.
Com os dados limpos, a tabela dinamica faz o resumo automaticamente.
Agrupamento de contas por classe contábil (1=Ativo, 2=Passivo, 3=PL, 4=Receita, etc.) permite filtrar e detalhar em segundos.
## Fórmulas para conferência
Formulas avancadas para conferência contábil: além do basico (SOMA, MÉDIA, SE), o contador que domina PROCV, SOMASES, ÍNDICE+CORRESP e CONT.SES tem uma vantagem enorme na conferência e validação de dados.
PROCV (ou PROCX no Excel 365) busca um valor em uma tabela e retorna uma informação correspondente — ideal para cruzar dois arquivos diferentes (por exemplo, cruzar o SPED com o balancete usando o código da conta como chave).
SOMASES soma valores que atendem a multiplos criterios — somar apenas as receitas de um determinado centro de custo em um determinado mes. ÍNDICE+CORRESP e mais flexivel que o PROCV e não tem a limitação de buscar apenas para a direita.
CONT.SES conta registros que atendem a multiplos criterios — quantos lancamentos de uma determinada conta foram feitos em um determinado período.
Combinar essas formulas com tabelas dinamicas cria um sistema de conferência que identifica discrepancias automaticamente.
## Performance com arquivo grande
Performance com grandes volumes de dados fiscais: arquivos SPED podem ter milhoes de linhas.
Para não travar o Excel, algumas práticas são essenciais.
Primeiro, use tabelas Excel (Ctrl+T) em vez de intervalos de celulas — as tabelas são mais eficientes em memoria e permitem referenciar colunas pelo nome.
Segundo, carregue os dados do Power Query como Conexao Apenas + Adicionar ao Modelo de Dados (Power Pivot) em vez de carregar direto na planilha — o modelo de dados suporta volumes muito maiores.
Terceiro, evite formulas volateis (INDIRETO, DESLOC, AGORA, HOJE) em tabelas grandes — elas recalculam a cada alteração.
Quarto, mantenha os cálculos em abas separadas dos dados brutos — dados em uma aba, cálculo em outra, apresentação em outra.
Essa separação de camadas e a fundação de uma planilha bem estruturada, fácil de manter e auditar.
## Um fluxo mensal
Um fluxo de trabalho prático para o mes: no primeiro dia útil do mes, você abre o arquivo Excel de fechamento, atualiza as conexoes do Power Query (que automaticamente importam os dados do período anterior do sistema contábil), e as tabelas dinamicas e formulas ja mostram os números atualizados.
Você confere, ajusta eventuais problemas, e tem o relatório pronto em fração do tempo que levaria manualmente.
Esse e o nivel de automação que o Excel avancado permite — sem precisar de programação, sem precisar de Power BI, usando uma ferramenta que todo o escritório ja tem e conhece.
As informações deste conteúdo têm caráter educativo e não constituem consultoria jurídica ou fiscal individualizada. Consulte um profissional habilitado para análise do seu caso específico.
Perguntas frequentes — Como usar Power Query e tabelas dinâmicas?
Como usar o Power Query no Excel para dados do SPED?+
No Excel, acesse Dados > Obter Dados > De Arquivo > De Texto/CSV, selecione o arquivo SPED e defina o delimitador como pipe (|). O Power Query separa as colunas automaticamente; você filtra os registros desejados (ex: C170), remove colunas desnecessárias e carrega a tabela tratada. Na próxima atualização mensal, basta substituir o arquivo na pasta e clicar em Atualizar — o Power Query repete todas as etapas automaticamente sem retrabalho manual.
Como fazer tabela dinâmica de balancete no Excel?+
Exporte o balancete do sistema contábil com colunas de conta, descrição e saldo — sem células mescladas, sem linhas em branco e sem totais internos. Selecione os dados, insira uma Tabela Dinâmica e arraste conta ou grupo de contas para linhas e saldo para valores. O agrupamento por classe contábil (1=Ativo, 2=Passivo, 3=PL, 4=Receita) permite filtrar e detalhar em segundos, criando em minutos um resumo que levaria horas manualmente.
Qual a diferença entre PROCV e ÍNDICE+CORRESP no Excel?+
O PROCV busca um valor em uma tabela e retorna a informação de uma coluna à direita — tem a limitação de não funcionar para colunas à esquerda da chave de busca. O ÍNDICE+CORRESP não tem essa restrição: pode retornar colunas à esquerda e é mais robusto quando as tabelas mudam de estrutura. Para cruzar o SPED com o balancete usando o código da conta como chave, ambas funcionam, mas ÍNDICE+CORRESP é a opção mais flexível e profissional.
Como o Excel aguenta arquivos SPED com milhões de linhas?+
Para grandes volumes, use o Power Query carregando os dados como Conexão Apenas + Adicionar ao Modelo de Dados (Power Pivot) em vez de carregar direto na planilha — o modelo de dados suporta volumes muito maiores. Use Tabelas Excel (Ctrl+T) em vez de intervalos de células, evite fórmulas voláteis como INDIRETO e DESLOC em tabelas grandes, e separe dados, cálculos e apresentação em abas distintas para facilitar a manutenção.
Ainda vale aprender Excel avançado ou é melhor ir direto para o Power BI?+
Vale muito aprender Excel avançado antes do Power BI. O Excel com Power Query e Power Pivot já é uma ferramenta de BI capaz de processar milhões de linhas, é universal (todo cliente o reconhece), flexível para combinar dados e cálculos e está disponível no mesmo ambiente que o escritório já usa. O Power BI adiciona interatividade e escalabilidade, mas a base de Power Query aprendida no Excel é totalmente transferível para o Power BI — dominar um facilita o outro.
Dica pro quiz
O Excel não vai a lugar nenhum nos escritórios contábeis. Domina-lo de verdade — Power Query, tabelas dinamicas, formulas avancadas — e o investimento de menor custo e maior retorno que um contador pode fazer hoje.
Teste seus conhecimentos
Qual a principal vantagem do Power Query para o trabalho mensal com dados do SPED e EFD?
Ficou com duvida?
O Gervinha pode explicar qualquer conceito deste modulo de forma simples e prática.