Hoe categoriseert u banktransacties in Excel?
Het beheren van persoonlijke of zakelijke financiën vraagt vaak om het doornemen van een gedetailleerde lijst met maandelijkse banktransacties. Deze transactieregistraties kunnen uiteenlopende beschrijvingen bevatten—van restaurants en winkels tot nutsbedrijven en diensten. Uw uitgaven bijhouden en analyseren wordt veel overzichtelijker wanneer u elke transactie onderbrengt in een duidelijke categorie, zoals „Afhaalmaaltijd”, „Boodschappen”, „Nutsbedrijven” of „Familiekosten”. Door dit classificatieproces in Excel te automatiseren op basis van trefwoorden in de transactiebeschrijvingen, krijgt u direct inzicht in uw maandelijkse kosten en uitgavenpatronen.
Zoals in de onderstaande schermafbeelding is geïllustreerd, stel dat u ruwe gegevens hebt met leveranciers- of dienstdetails in kolom B en een vereenvoudigde set categorieën wilt toepassen (bijv. elke transactie die „)Mc Donalds” bevat, moet worden geclassificeerd als „Afhaalmaaltijd”, terwijl „Walmart” wordt ingedeeld als „Familiekosten”), volgens uw persoonlijke of organisatieregels. Deze stapsgewijze handleiding behandelt diverse praktische oplossingen, van formulegebaseerde methoden tot geavanceerde automatisering.
Inhoud:
Banktransacties categoriseren met een formule in Excel
VBA-code – Categorisering automatiseren met een macro op basis van een vooraf gedefinieerde lijst
Andere ingebouwde Excel-methoden – Power Query gebruiken met voorwaardelijke kolomlogica
Banktransacties categoriseren met een formule in Excel
Als u banktransacties in Excel eenvoudig en op formules wilt categoriseren, volgt u deze stappen. Deze aanpak is perfect voor wie snel resultaten wil en comfortabel is met het beheren van een compacte opzoeklijst met trefwoorden en bijbehorende categorieën.
Maak eerst twee hulpkolommen buiten uw hoofdtransactielijst: één kolom met trefwoorden (die overeenkomen met mogelijke inhoud in de transactiebeschrijving) en een tweede kolom met de categorie die u aan elk trefwoord wilt koppelen.
1. In dit voorbeeld voert u alle trefwoorden die u wilt matchen (zoals „McDonald’s”, „Walmart”, enzovoort) in kolom A van rij 30 tot en met 41 in, en de bijbehorende categorienamen (zoals „Afhaalmaaltijd”, „Familiekosten”, enzovoort) in kolom B van rij 30 tot en met 41.
Als uw lijst met trefwoorden of categorieën uitgebreider is of regelmatig wijzigt, past u eenvoudigweg de bereiken aan zodat ze al uw behoeften dekken. Zie schermafbeelding:
2. Klik vervolgens op de eerste cel in uw gewenste uitvoerkolom (bijvoorbeeld F3, naast de laatste kolom van uw gegevens) en voer deze matrixformule in. Druk op Ctrl + Shift + Enter (niet alleen op Enter) om te bevestigen, want het betreft een matrixformule. Zo zoekt u het eerste trefwoord dat in de beschrijving wordt gevonden en wordt de bijbehorende categorie geretourneerd.
=IFERROR(INDEX(B$30:B$41,MATCH(TRUE,ISNUMBER(SEARCH($A$30:$A$41,B3)),0)),„Other")
Zodra de formule voor de eerste rij werkt, sleept u het Automatisch-aanvullen-handvat naar beneden in de kolom om de categoriseringsformule toe te passen op alle overige transactierijen.
Uitleg van parameters:
Voor- en nadelenanalyse:Deze formulegedreven aanpak zorgt voor een snelle instelling en vereist weinig onderhoud, mits uw categorisatieregels niet vaak veranderen. Wordt uw lijst met trefwoorden echter complexer of moet u categorieën regelmatig bijwerken, dan kan het beheren van hulpkolommen en formules tijdrovend worden – en kunt u overwegen het proces te automatiseren via VBA of Power Query.
Praktische tip: Als meerdere trefwoorden in een beschrijving overeenkomen, bepaalt alleen het eerste trefwoord in uw lijst de categorie. Geef een trefwoord prioriteit door het bovenaan uw opzoekkolom te plaatsen.
Veelvoorkomende problemen oplossen:Als u onverwachte resultaten krijgt, controleert u het opzoekbereik en zorgt u ervoor dat uw trefwoorden consistent en volledig zijn gespeld. Controleer ook op extra spaties of opmaakverschillen in uw transactiebeschrijvingen.
VBA-code – Categorisering automatiseren met een macro die transactiebeschrijvingen koppelt aan categorieën op basis van een vooraf gedefinieerde lijst
Deze oplossing maakt gebruik van een VBA-macro om het koppelen van transacties aan categorieën te automatiseren, met verbeterde controle en schaalbaarheid. Dit is vooral voordelig voor gebruikers met grote transactievolumes of wanneer u de verwijzing tussen trefwoorden en categorieën dynamisch wilt houden en handmatig formulebeheer tot een minimum wilt beperken.
Toepasselijk scenario: Wanneer de transactielijst lang is, updates frequent zijn of u handmatige formules wilt vermijden, verwerken VBA-macro’s elke beschrijving efficiënter en wijzen ze automatisch de juiste categorie toe—met aanpasbare logica en prompts.
Werkstappen:
- Stel een lijst op met trefwoorden en bijbehorende categorieën, net als de hulpkolommen die worden gebruikt bij de formulemethode (bijvoorbeeld in kolommen A en B, vanaf rij 30 naar beneden).
- Druk op Alt + F11 om de editor voor Visual Basic for Applications te openen. Klik in het VBA-venster op Invoegen > Module om een nieuwe module toe te voegen.
Kopieer en plak de volgende code in de module:
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 SubGebruiksaanwijzing:
- Klik op de knop
Uitvoeren of druk op F5in de VBA-editor om de macro uit te voeren. De macro verwerkt elke transactiebeschrijving in kolom B, vergelijkt deze met de lijst van trefwoorden en schrijft de bijbehorende categorie (of „Overig" als er geen overeenkomst wordt gevonden) naar kolom F van dezelfde rij. - U kunt het aantal rijen dat binnen de macro wordt ingesteld of bereikt, aanpassen aan uw gegevensindeling—bijvoorbeeld door het startpunt van de transactiebeschrijvingen of de locatie van de hulplijst te wijzigen. Zorg ervoor dat uw trefwoordenlijst geen lege cellen bevat en dat de categorieën duidelijk en uniek zijn voor eenvoudige verwijzing.
Uitvoeren of druk op F5in de VBA-editor om de macro uit te voeren. De macro verwerkt elke transactiebeschrijving in kolom B, vergelijkt deze met de lijst van trefwoorden en schrijft de bijbehorende categorie (of „Overig" als er geen overeenkomst wordt gevonden) naar kolom F van dezelfde rij.Voor- en nadelenanalyse:De VBA-oplossing is aanpasbaar aan complexere regels, kan op elk moment opnieuw worden uitgevoerd na een update van uw lijst met trefwoorden of categorieën en maakt matrixformules overbodig. Macro’s vereisen echter dat u geautomatiseerde bewerkingen in Excel inschakelt – iets wat niet voor alle gebruikers of omgevingen geschikt is.
Praktische tip: Sla uw VBA-macro op in een herbruikbaar bestand en maak altijd een back-up van uw gegevens voordat u macro’s uitvoert—voor het geval per ongeluk gegevens worden overschreven.
Suggesties voor probleemoplossing:Als categorieën niet worden bijgewerkt zoals verwacht, controleert u of uw trefwoorden- en categorielijsten overeenkomen en of er geen verborgen tekens in de cellen zitten. VBA is hoofdletterongevoelig als u „vbTextCompare” gebruikt, maar mismatchen kunnen nog steeds optreden door opmaakverschillen.
Andere ingebouwde Excel-methoden – Power Query gebruiken om regelgebaseerde categorisering in te stellen via voorwaardelijke kolomlogica
Als u liever kiest voor een moderne, no-code automatiseringsaanpak, biedt Power Query een krachtige oplossing om uw banktransacties te categoriseren. Deze methode is perfect bij het importeren van transactiegegevens uit CSV-bestanden of online bronnen, en bij het beheren van dynamische, evoluerende categorisatieregels — omdat de logica gecentraliseerd blijft en updates eenvoudig zijn zonder complexe formules of VBA-scripts.
Werkstappen:
- Zorg eerst dat uw transacties zijn opgemaakt als een tabel of een duidelijk gegevensbereik. Selecteer een willekeurige cel in uw tabel en ga vervolgens naar Gegevens > Van tabel/bereik om de gegevens in Power Query te laden.
- Klik in de Power Query Editor op Kolom toevoegen > Voorwaardelijke kolom om een nieuwe, regelgebaseerde kolom voor categorieën te maken.
- Definieer uw overeenkomstregels, zoals:
- Als Beschrijving„Mc Donalds" bevat, geef dan „Afhaalmaaltijd" als uitvoer
- Als Beschrijving„Walmart" bevat, geef dan „Familiekosten" als uitvoer
- Geef anders „Overig" als uitvoer
- Klik op OK om de voorwaardelijke kolom te maken en selecteer vervolgens Sluiten en laden om uw gecategoriseerde resultaten terug te sturen naar Excel.
Voor- en nadelenanalyse:Power Query biedt flexibiliteit bij het aanpassen van regels, integreert naadloos met externe gegevensimports en vernieuwt resultaten razendsnel zodra transactiegegevens of regels worden bijgewerkt. Bovendien verwerkt het zeer grote datasets efficiënter dan handmatige formules. Power Query vereist echter wel een basissetup en enige vertrouwdheid met de interface – wat voor sommige gebruikers een nieuwe ervaring kan zijn.
Praktische tips:Orden uw regels in prioritair volgorde (van boven naar beneden) in het dialoogvenster Voorwaardelijke kolom; de eerste overeenkomende regel wordt toegepast. U kunt regels eenvoudig bewerken, toevoegen of verwijderen in Power Query zonder uw hoofdformules in het Excel-werkblad aan te passen.
Foutmeldingen: Als u nieuwe transacties importeert en het resultaat wordt niet vernieuwd zoals verwacht, klikt u op Vernieuwen in het tabblad Gegevens van Excel. Let op de spelling en exacte trefwoordovereenkomsten in Power Query: zelfs kleine afwijkingen kunnen ervoor zorgen dat de juiste categorie niet wordt toegewezen.
Probleemoplossingsuggestie:Als uw categorieën niet correct worden weergegeven, controleer dan uw regels voor voorwaardelijke kolommen op conflicten of ontbrekende gevallen. Het is handig om eerst een paar voorbeeldregels te testen voordat u de wijzigingen op de volledige dataset toepast.
Kort samengevat: of u nu kiest voor matrixformules, de flexibiliteit van een VBA-macro of de gestroomlijnde automatisering van Power Query – Excel biedt diverse praktische manieren om uw banktransacties efficiënt te categoriseren. Controleer altijd uw categorisatieregels en de consistentie van de resultaten wanneer transactiegegevens of categorisatiecriteria wijzigen.
Beste Office-productiviteitshulpmiddelen
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie zoals nooit tevoren.Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en Tijd besparen.Klik hier om de functie te krijgen die u het meest nodig heeft...
Office Tab brengt een tabbladinterface naar Office en maakt uw werk veel eenvoudiger
- Schakel tabbladbewerking en -lezen in voor Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten aan in nieuwe tabbladen binnen hetzelfde venster, in plaats van in afzonderlijke vensters.
- Verhoogt uw productiviteit met 50 % en bespaart u dagelijks honderden muisklikken!
Alle Kutools-add-ins in één installatieprogramma.
Kutools for Office bundelt add-ins voor Excel, Word, Outlook en PowerPoint, plus Office Tab Pro—ideaal voor teams die met meerdere Office-apps werken.
- Alles-in-één suite— add-ins voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro
- Één installatieprogramma, één licentie— binnen enkele minuten klaar (MSI-geschikt)
- Werkt beter samen— gestroomlijnde productiviteit in alle Office-apps
- 30 dagen volledig functionele proefversie— geen registratie, geen creditcard
- Beste prijs-kwaliteitverhouding— bespaar ten opzichte van het afzonderlijk kopen van add-ins