Como uso o Excel para categorizar as transações bancárias em categorias facilmente compreensíveis para ver onde está indo o meu dinheiro?

Question

Meu chefe quer que nossas transações bancárias sejam categorizadas manualmente. Neste momento, minha tarefa é percorrer as transações bancárias e categorizá-las em categorias com base no que cada item é. Para despesas aleatórias (apenas uma vez) faz sentido procurar e digitar em que categoria está, mas para coisas recorrentes (mesma empresa que fornece o mesmo serviço todas as vezes) faz sentido que seja automatizado, especialmente quando há muito de transações.

As transações bancárias exportam para o excel por Data, Descrição, Valor, Tipo (débito, crédito etc).

O que eu quero fazer é ter um excel através da "coluna de descrição" e convertê-lo em algo simples e legível. Exemplo Descrição coluna contém "Empresa xyx 4003 ADP" Eu quero excel para retornar "folha de pagamento" a próxima célula contém "# 3k322 Phil Marqu LLC" e eu quero voltar "Cafe suprimentos". Eu quero fazer o mesmo para depósitos exemplo "Depósito WEB PMTS" deve se tornar "vendas on-line"

Dessa forma, posso criar uma tabela e classificar por categoria, ou seja, "suprimentos de café" e "vendas on-line" para criar um relatório mensal simples mostrando o dinheiro que entra e sai.

Minha situação ideal envolve uma tabela de referência separada que eu posso continuar adicionando e que teria 2 colunas. A coluna A seria a substring a ser correspondida e a coluna B seria o que deveria ser retornada. Exemplo eu posso colocar na coluna A "# 3k322" porque eu sei que sempre vai aparecer para o fornecedor de suprimentos de café e coluna B seria "suprimentos de café" e se no futuro adicionarmos outro café fornece fornecedor posso apenas adicionar outra linha para minha tabela de referência.

Eu li muitas coisas on-line sobre index, match e vlookup, mas não sou um profissional do excel e estou tendo dificuldades em descobrir isso. Eu vou aprender todas as funções que você joga em mim. Estou muito motivado para fazer isso.

Obrigado.

    

Answer 1

Você pode criar uma matriz de índice onde você escreve a palavra-chave em uma coluna e a categoria correspondente na segunda linha da mesma coluna:

# 3k322 Artigos para café
| Vendas on-line da web

a palavra-chave deve estar sempre contida na descrição que você já tem. Crie uma nova coluna vazia perto da descrição da categoria e escreva essa fórmula na frente da primeira descrição:

=OFFSET($I$2,SUMPRODUCT(--ISNUMBER(FIND($I$2:$I$4,B2,1))*ROW($I$2:$I$4))-2,1)

Onde $ I $ 2: $ I $ 4 é a coluna de palavras-chave começando em I2
J2: J4 é a coluna das categorias correspondentes
B2 é a primeira descrição O Sumproduct encontrará a palavra-chave correspondente à descrição em B2 e fornecerá o número da linha dele. Offset encontrará a categoria correspondente à linha encontrada -2 porque I2 é a origem e 1 para a segunda coluna
Você pode arrastar a fórmula para baixo manter o $ onde eu escrevi na fórmula e alterar a referência para corresponder ao seu Data
Atualizar
Para anexar a categoria sem alterar a fórmula, use o seguinte

=OFFSET($I$2,SUMPRODUCT(--ISNUMBER(FIND(INDIRECT($L$1),B2,1))*ROW(INDIRECT($L$1)))-2,1)

onde L1 é uma referência da coluna de palavras-chave, toda vez que você alterar as categorias (anexar ou remover) escreva em L1 as referências: $ I $ 2: $ I $ 4
Lembre-se de escrever a palavra-chave exatamente como está escrito na descrição (letras maiúsculas ou minúsculas são sensíveis a maiúsculas e minúsculas)
INDIRECT lerá L1 e converterá para $ I $ 2: $ I $ 4

    

Answer 2

Eu criei uma macro para um projeto semelhante, basicamente atribuindo uma categoria a uma entrada.

Aviso: é ineficiente. não é um grande negócio para uso pessoal normalmente.

Requisitos:

duas planilhas:

um com nomes de transação na coluna um, coluna vazia 2

um com nomes de transação exclusivos na coluna um e sua categoria na coluna 2

sem linhas em branco

altere o for i = 1 - X para igual ao número de transações processadas

altere a linha p para o número de categorias que você tem

veja: link

Sub AddDepartment()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim i As Long, j As Long, p As Long
    Dim isMatch As Boolean

Set ws1 = ActiveWorkbook.Sheets("Sheet1") 'transactions sheet
Set ws2 = ActiveWorkbook.Sheets("Sheet2") 'categorized sheet

'Initial position of first element in sheet2
p = 1

For i = 1 To 100 'last row of transaction sheet necessary
    isMatch = False
    For j = 1 To 100 'last row of category sheet necessary
        If ws1.Cells(i, 1).Value = ws2.Cells(j, 1).Value Then 'if transactions.transactions = category.transactions
            ws2.Cells(j, 2).Copy ws1.Cells(p, 2) 'write category.category to transactions.category
            isMatch = True
            p = p + 1
        End If
    Next j
    If isMatch = False Then
        ws1.Cells(p, 2) = "OTHER" 'set anything that does not match to "other" category
        p = p + 1
    End If
Next i
End Sub