Como categorizar transações bancárias no Excel?
Gerir finanças pessoais ou empresariais exige frequentemente a análise de uma lista detalhada de transações bancárias mensais. Esses registos podem incluir uma grande variedade de descrições — desde restaurantes e lojas até serviços públicos ou outros fornecedores. O acompanhamento e a análise das despesas tornam-se muito mais claros quando cada transação é atribuída a uma categoria distinta, como «Takeout», «Groceries», «Utilities» ou «Family fee». Ao automatizar a classificação no Excel com base em palavras-chave presentes nas descrições das transações, obtém uma visão precisa dos seus custos e padrões de gastos mensais.
Tal como ilustrado na imagem abaixo, imagine que tem dados brutos com detalhes do fornecedor ou serviço na coluna B e pretende obter um conjunto simplificado de categorias (por exemplo, qualquer transação que contenha ")Mc Donalds« deve ser identificada como »Takeout«, enquanto »Walmart« é classificada como »Family fee") de acordo com as suas regras pessoais ou organizacionais. Este tutorial passo a passo explora várias soluções práticas, incluindo métodos baseados em fórmulas e abordagens avançadas de automação.
Conteúdo:
Categorizar transações bancárias com fórmula no Excel
Código VBA – Automatizar a categorização com uma macro com base numa lista predefinida
Outros métodos incorporados no Excel – Utilizar o Power Query com lógica de coluna condicional
Categorizar transações bancárias com fórmula no Excel
Se pretender uma abordagem simples e baseada em fórmulas para categorizar transações bancárias no Excel, siga estes passos. Este método é ideal para utilizadores que procuram resultados rápidos e estão à vontade para manter uma pequena tabela de referência com palavras-chave e respetivas categorias.
Em primeiro lugar, crie duas «colunas auxiliares» fora da sua lista principal de transações: uma com palavras-chave (correspondentes ao conteúdo que pode surgir nas descrições das transações) e outra com a categoria que pretende associar a cada palavra-chave.
1. Neste exemplo, liste todas as palavras-chave a identificar (como «McDonald's», «Walmart», etc.) na coluna A, das linhas 30 a 41, e os respetivos nomes das categorias («Takeout», «Family fee», etc.) na coluna B, das linhas 30 a 41.
Se o seu conjunto de palavras-chave ou categorias for mais extenso ou mudar frequentemente, basta ajustar os intervalos para abranger todas as suas necessidades. Veja a imagem:
2. Em seguida, clique na primeira célula da coluna de saída pretendida (por exemplo, F3, ao lado da última coluna dos seus dados) e introduza esta fórmula matricial. Prima Ctrl + Shift + Enter (e não apenas Enter) para confirmar, pois trata-se de uma fórmula matricial. Esta procurará a primeira palavra-chave encontrada na descrição e devolverá a categoria correspondente.
=IFERROR(INDEX(B$30:B$41,MATCH(TRUE,ISNUMBER(SEARCH($A$30:$A$41,B3)),0)),«Other»)
Assim que a fórmula estiver a funcionar na primeira linha, arraste a alça de autorreenchimento ao longo da coluna para aplicar a fórmula de categorização a todas as restantes linhas de transações.
Explicação dos parâmetros:
Análise de vantagens e desvantagens:Esta abordagem baseada em fórmulas permite uma configuração rápida e baixa manutenção, desde que as suas regras de categorização não mudem frequentemente. Contudo, se a sua lista de palavras-chave se tornar mais complexa ou precisar de atualizar regularmente as categorias, gerir colunas auxiliares e fórmulas poderá tornar-se tedioso — nesse caso, será preferível considerar a automação com VBA ou Power Query.
Dica prática: Se várias palavras-chave corresponderem a uma descrição, apenas a primeira encontrada na sua lista determinará a categoria. Para dar prioridade a uma palavra-chave específica, coloque-a mais cedo na sua coluna de referência.
Resolução de problemas comuns:Se obtiver resultados inesperados, verifique novamente o intervalo de procura e certifique-se de que as palavras-chave estão escritas de forma consistente e completa. Verifique também a existência de espaços extra ou diferenças de formatação nas descrições das transações.
Código VBA – Automatizar a categorização com uma macro que associa descrições de transações a categorias com base numa lista predefinida
Esta solução recorre a uma macro VBA para automatizar a correspondência entre transações e categorias, proporcionando maior controlo e escalabilidade. É ideal para utilizadores com elevados volumes de transações ou que pretendam estabelecer uma ligação dinâmica entre palavras-chave e categorias, reduzindo ao mínimo a gestão manual de fórmulas.
Cenário aplicável: Quando a lista de transações é longa, as atualizações são frequentes ou pretende evitar a manutenção manual de fórmulas, as macros VBA conseguem processar cada descrição e atribuir automaticamente a categoria adequada — de forma mais eficiente, com lógica personalizável e possibilidade de alertas.
Passos operacionais:
- Prepare uma lista de palavras-chave para categorias, semelhante às colunas auxiliares usadas no método com fórmulas (por exemplo, nas colunas A e B a partir da linha 30).
- Prima Alt + F11 para abrir o editor do Visual Basic for Applications. Na janela do VBA, clique em Inserir > Módulo para adicionar um novo módulo.
Copie e cole o seguinte código no módulo:
Sub CategorizeTransactions()
Dim lastRow As Long
Dim i As Long
Dim descCell As Range
Dim kwRow As Long
Dim kwRange As Range
Dim catRange As Range
Dim kwCount As Long
Dim catResult As String
Dim matched As Boolean
On Error Resume Next
xTitleId = "KutoolsforExcel"
kwCount = Cells(Rows.Count, "A").End(xlUp).Row - 29
Set kwRange = Range("A30:A" & 29 + kwCount)
Set catRange = Range("B30:B" & 29 + kwCount)
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 3 To lastRow
Set descCell = Cells(i, "B")
catResult = "Other"
matched = False
For kwRow = 1 To kwCount
If InStr(1, descCell.Value, kwRange.Cells(kwRow, 1).Value, vbTextCompare) > 0 Then
catResult = catRange.Cells(kwRow, 1).Value
matched = True
Exit For
End If
Next kwRow
Cells(i, "F").Value = catResult
Next i
End SubComo utilizar:
- Clique no botão
Executar ou prima F5 no editor do VBA para executar a macro. A macro irá processar cada descrição de transação na coluna B, compará-la com a lista de palavras-chave e escrever a categoria correspondente (ou «Other», se não for encontrada nenhuma correspondência) na coluna F da mesma linha. - Pode ajustar o número de linhas e intervalos definidos na macro para adaptá-los à disposição dos seus dados — por exemplo, alterando onde começam as descrições das transações ou a localização da lista auxiliar. Certifique-se de que a sua lista de palavras-chave não contém células vazias e de que as categorias são claras e únicas, facilitando assim a referência.
Executar ou prima F5 no editor do VBA para executar a macro. A macro irá processar cada descrição de transação na coluna B, compará-la com a lista de palavras-chave e escrever a categoria correspondente (ou «Other», se não for encontrada nenhuma correspondência) na coluna F da mesma linha.Análise de vantagens e desvantagens:A solução VBA adapta-se a regras mais complexas, pode ser executada novamente sempre que atualizar a sua lista de palavras-chave ou categorias e elimina a necessidade de fórmulas matriciais. No entanto, as macros exigem a ativação de operações automatizadas no Excel, o que poderá não ser adequado a todos os utilizadores ou ambientes.
Dica prática: Guarde a sua macro VBA num ficheiro reutilizável e faça sempre cópia de segurança dos dados antes de executar macros, caso ocorra uma substituição acidental.
Sugestões para resolução de problemas:Se as categorias não forem atualizadas conforme esperado, verifique se as suas listas de palavras-chave e categorias estão alinhadas e se não existem caracteres ocultos nas células. O VBA não diferencia maiúsculas/minúsculas se utilizar «vbTextCompare», mas ainda podem ocorrer discrepâncias devido à formatação.
Outros métodos incorporados no Excel – Utilizar o Power Query para configurar uma categorização baseada em regras através da lógica de coluna condicional
Se preferir uma abordagem moderna e sem programação, o Power Query oferece uma solução robusta para categorizar as suas transações bancárias. Este método é ideal quando importa dados de transações a partir de ficheiros CSV ou fontes online, ou quando gere regras de categorização dinâmicas e em constante evolução, pois centraliza toda a lógica e permite atualizações simples, sem recorrer a fórmulas complexas ou scripts VBA.
Passos operacionais:
- Em primeiro lugar, certifique-se de que as suas transações estão formatadas como uma tabela ou como um intervalo de dados estruturado. Selecione qualquer célula na sua tabela e, em seguida, vá a Dados > A partir de Tabela/Intervalo para carregar os dados no Power Query.
- No Editor do Power Query, clique em Adicionar Coluna > Coluna Condicional para criar uma nova coluna com base em regras para as categorias.
- Defina as suas regras de correspondência, tais como:
- Se Descriçãocontiver «Mc Donalds», então devolva «Takeout»
- Se Descriçãocontiver «Walmart», então devolva «Family fee»
- Caso contrário, devolva «Other»
- Clique em OK para criar a coluna condicional e, em seguida, selecione Fechar e Carregar para devolver os seus resultados categorizados ao Excel.
Análise de vantagens e desvantagens:O Power Query oferece flexibilidade para ajustar regras, integra-se perfeitamente com importações de dados externos e atualiza os resultados em tempo real sempre que forem alterados os dados das transações ou as regras. Além disso, revela-se significativamente mais eficiente do que fórmulas manuais ao lidar com conjuntos de dados muito grandes. No entanto, exige uma configuração inicial básica e alguma familiaridade com a sua interface — um passo novo para alguns utilizadores.
Dicas práticas:Organize as suas regras por ordem de prioridade (de cima para baixo) dentro da caixa de diálogo da Coluna Condicional; será aplicada a primeira regra que corresponder. Pode editar, adicionar ou eliminar facilmente regras no Power Query sem alterar as fórmulas principais da sua folha de cálculo do Excel.
Lembretes de erro: Se importar novas transações e o resultado não for atualizado como esperado, clique em Atualizar no separador Dados do Excel. Preste atenção à ortografia e às correspondências exatas das palavras-chave no Power Query, pois correspondências parciais podem impedir que a categoria correta seja atribuída.
Sugestão de resolução de problemas:Se as suas categorias não estiverem a aparecer corretamente, reveja as regras da coluna condicional quanto a conflitos ou casos em falta. É útil testar algumas entradas de exemplo antes de aplicar as alterações a todo o conjunto de dados.
Em resumo, quer opte por fórmulas matriciais, pela flexibilidade de uma macro VBA ou pela automatização simplificada do Power Query, o Excel oferece várias formas práticas de categorizar as suas transações bancárias de forma eficiente. Reveja sempre as suas regras de categorização e a consistência dos resultados à medida que os dados das transações ou os critérios de categorização evoluem.
As Melhores Ferramentas de Produtividade para o Office
Potencie as suas competências no Excel com Kutools para Excel e experimente uma eficiência como nunca antes.Kutools para Excel Oferece mais de 300 funcionalidades avançadas para impulsionar a produtividade e Economizar Tempo.Clique aqui para obter a funcionalidade de que mais precisa...
Office Tab Traz uma interface com separadores para o Office e torna o seu trabalho muito mais fácil
- Ative a edição e leitura com separadores no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novos separadores da mesma janela, em vez de em janelas separadas.
- Aumente a sua produtividade em 50 % e elimine centenas de cliques do rato todos os dias!
Todos os suplementos Kutools — num único instalador.
Kutools for Office reúne suplementos para Excel, Word, Outlook e PowerPoint, além do Office Tab Pro, sendo a solução ideal para equipas que trabalham em várias aplicações do Office.
- Pacote tudo-em-um— suplementos para Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Um instalador, uma licença— configuração em minutos (pronto para MSI)
- Funciona melhor em conjunto— produtividade simplificada em todas as aplicações do Office
- Teste gratuito de 30 dias com todas as funcionalidades— sem registo, sem cartão de crédito
- Melhor relação qualidade-preço— poupe face à compra individual dos suplementos