Comment catégoriser efficacement vos transactions bancaires dans Excel ?
Gérer vos finances personnelles ou professionnelles passe souvent par l’analyse détaillée de vos relevés bancaires mensuels. Ces derniers regorgent de descriptions variées — restaurants, magasins, services publics ou autres prestations. Tout devient bien plus clair lorsque chaque transaction est classée dans une catégorie précise, comme « Takeout », « Groceries », « Utilities » ou « Family fee ». En automatisant ce classement dans Excel à l’aide de mots-clés présents dans les libellés des transactions, vous obtenez chaque mois une vision nette et précise de vos dépenses et de vos habitudes de consommation.
Comme le montre la capture d’écran ci-dessous, imaginez que vous disposiez de données brutes avec les noms de fournisseurs ou de services en colonne B, et que vous souhaitiez les regrouper en catégories simplifiées (par exemple, toute transaction contenant «)Mc Donalds» doit être identifiée comme «Takeout», tandis que «Walmart» est classée comme «Family fee»), conformément à vos règles personnelles ou organisationnelles. Ce tutoriel pas à pas vous propose plusieurs solutions concrètes, allant des formules classiques aux méthodes avancées d’automatisation.
Sommaire :
Catégoriser les transactions bancaires à l’aide d’une formule dans Excel
Code VBA – Automatiser la catégorisation avec une macro basée sur une liste prédéfinie
Autres méthodes intégrées d’Excel – Utiliser Power Query avec une logique de colonne conditionnelle
Catégoriser les transactions bancaires à l’aide d’une formule dans Excel
Si vous recherchez une méthode simple et basée sur des formules pour catégoriser vos transactions bancaires dans Excel, suivez ces étapes. Cette approche est idéale si vous souhaitez obtenir rapidement des résultats et que vous êtes à l’aise avec la gestion d’un petit tableau associant mots-clés et catégories.
Commencez par créer deux « colonnes d’aide » en dehors de votre liste principale de transactions : l’une contenant les mots-clés (correspondant aux descriptions possibles des transactions) et l’autre indiquant la catégorie à associer à chaque mot-clé.
1. Dans cet exemple, saisissez tous les mots-clés à rechercher (tels que « Mc Donalds », « Walmart », etc.) en colonne A, des lignes 30 à 41, et les noms des catégories correspondantes (« Takeout », « Family fee », etc.) en colonne B, sur la même plage de lignes.
Si votre liste de mots-clés ou de catégories est plus étendue ou évolue fréquemment, il vous suffit d’ajuster les plages pour couvrir l’ensemble de vos besoins. Voir la capture d’écran :
2. Cliquez ensuite sur la première cellule de votre colonne de sortie souhaitée (par exemple, F3, juste à côté de la dernière colonne de vos données), puis saisissez cette formule matricielle. Appuyez sur Ctrl + Maj + Entrée (et non simplement sur Entrée) pour la valider, car il s’agit d’une formule matricielle. Celle-ci recherchera le premier mot-clé trouvé dans la description et renverra la catégorie correspondante.
=IFERROR(INDEX(B$30:B$41,MATCH(TRUE,ISNUMBER(SEARCH($A$30:$A$41,B3)),0)),«Other»)
Une fois la formule fonctionnelle pour la première ligne, faites glisser la poignée de recopie vers le bas de la colonne pour appliquer la formule de catégorisation à toutes les lignes de transactions restantes.
Explication des paramètres :
Analyse des avantages et inconvénients :Cette approche basée sur des formules permet une configuration rapide et une maintenance minimale, à condition que vos règles de catégorisation évoluent peu. Toutefois, si votre liste de mots-clés gagne en complexité ou si vous devez fréquemment mettre à jour les catégories, la gestion des colonnes d’aide et des formules devient rapidement fastidieuse — auquel cas une automatisation via VBA ou Power Query s’impose.
Conseil pratique : Si plusieurs mots-clés correspondent à une même description, seule la première occurrence figurant dans votre liste déterminera la catégorie. Pour accorder la priorité à un mot-clé particulier, placez-le plus haut dans votre colonne de recherche.
Dépannage courant :Si vous obtenez des résultats inattendus, vérifiez attentivement la plage de recherche et assurez-vous que vos mots-clés sont orthographiés de manière cohérente et complète. Vérifiez également la présence d’espaces superflus ou de différences de mise en forme dans vos descriptions de transactions.
Code VBA – Automatiser la catégorisation avec une macro qui associe les descriptions de transactions à des catégories à partir d’une liste prédéfinie
Cette solution s’appuie sur une macro VBA pour automatiser l’association des transactions à leurs catégories, offrant ainsi un contrôle renforcé et une meilleure évolutivité. Elle s’avère particulièrement utile pour les utilisateurs qui gèrent un volume important de transactions ou souhaitent rendre dynamique le lien entre mots-clés et catégories, tout en réduisant au minimum la gestion manuelle des formules.
Scénario d’application : Lorsque la liste des transactions est longue, les mises à jour fréquentes ou que vous souhaitez éviter la maintenance manuelle des formules, les macros VBA traitent chaque description et attribuent automatiquement la catégorie adéquate — avec une logique entièrement personnalisable et des invites sur mesure.
Étapes opérationnelles :
- Préparez une liste de mots-clés organisés par catégories, à l’image des colonnes d’aide employées dans la méthode par formule (par exemple, dans les colonnes A et B à partir de la ligne 30).
- Appuyez sur Alt + F11 pour ouvrir l’éditeur Visual Basic pour Applications. Dans la fenêtre VBA, cliquez sur Insertion > Module pour ajouter un nouveau module.
Copiez et collez le code suivant dans le 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 SubMode d’emploi :
- Cliquez sur le bouton
Exécuter ou appuyez sur F5 dans l’éditeur VBA pour lancer la macro. Celle-ci analysera chaque description de transaction dans la colonne B, la comparera à la liste de mots-clés, puis inscrira la catégorie correspondante (ou « Other » si aucune correspondance n’est trouvée) dans la colonne F de la même ligne. - Vous pouvez ajuster les Définir le nombre de lignes/plages à l’intérieur de la macro pour l’adapter à la structure de vos données, par exemple en modifiant l’emplacement de départ des descriptions de transactions ou celui de la liste d’aide. Veillez à ce que votre liste de mots-clés ne contienne pas de cellules vides et que les catégories soient claires et uniques pour faciliter leur référencement.
Exécuter ou appuyez sur F5 dans l’éditeur VBA pour lancer la macro. Celle-ci analysera chaque description de transaction dans la colonne B, la comparera à la liste de mots-clés, puis inscrira la catégorie correspondante (ou « Other » si aucune correspondance n’est trouvée) dans la colonne F de la même ligne.Analyse des avantages et inconvénients :La solution VBA gère des règles plus complexes, peut être relancée à tout moment après mise à jour de votre liste de mots-clés ou de catégories, et élimine le recours aux formules matricielles. Toutefois, les macros nécessitent l’activation des opérations automatisées dans Excel, ce qui ne convient pas nécessairement à tous les utilisateurs ni à tous les environnements.
Conseil pratique : Enregistrez votre macro VBA dans un fichier réutilisable et sauvegardez toujours vos données avant d’exécuter des macros, afin d’éviter toute suppression accidentelle.
Suggestions de dépannage :Si les catégories ne se mettent pas à jour comme prévu, vérifiez que vos listes de mots-clés et de catégories sont alignées et qu’il n’y a pas de caractères invisibles dans les cellules. VBA n’est pas sensible à la casse si vous utilisez « vbTextCompare », mais des écarts peuvent néanmoins survenir en raison de la mise en forme.
Autres méthodes intégrées d’Excel – Utiliser Power Query pour configurer une catégorisation basée sur des règles via une logique de colonne conditionnelle
Si vous privilégiez une approche moderne d’automatisation sans code, Power Query vous offre un moyen robuste de catégoriser vos transactions bancaires. Idéale pour importer des données depuis des fichiers CSV ou des sources en ligne, cette méthode excelle également lorsque vous gérez des règles de catégorisation dynamiques et évolutives : elle centralise toute la logique métier et permet des mises à jour simplifiées, sans formules complexes ni scripts VBA.
Étapes opérationnelles :
- Commencez par vous assurer que vos transactions sont organisées sous forme de tableau ou de plage de données bien définie. Sélectionnez n’importe quelle cellule de votre tableau, puis cliquez sur Données > À partir d’un tableau/plage pour charger les données dans Power Query.
- Dans l’éditeur Power Query, cliquez sur Ajouter une colonne > Colonne conditionnelle pour créer une nouvelle colonne basée sur des règles de catégorisation.
- Définissez vos règles de correspondance, telles que :
- Si Descriptioncontient « Mc Donalds », alors renvoyer « Takeout »
- Si Descriptioncontient « Walmart », alors renvoyer « Family fee »
- Sinon, renvoyer « Other »
- Cliquez sur OK pour créer la colonne conditionnelle, puis sélectionnez Fermer et charger pour renvoyer vos résultats catégorisés dans Excel.
Analyse des avantages et inconvénients :Power Query offre une grande flexibilité pour modifier les règles, s’intègre parfaitement aux importations de données externes et permet de rafraîchir instantanément les résultats dès que les données de transactions ou les règles sont mises à jour. Il gère également les très grands jeux de données bien plus efficacement que les formules créées manuellement. Toutefois, Power Query nécessite une configuration initiale et une certaine familiarité avec son interface, ce qui peut représenter une courbe d’apprentissage pour certains utilisateurs.
Conseils pratiques :Classez vos règles par ordre de priorité (du haut vers le bas) dans la boîte de dialogue Colonne conditionnelle ; la première règle correspondante sera appliquée. Vous pouvez facilement modifier, ajouter ou supprimer des règles dans Power Query sans modifier les formules de votre feuille Excel principale.
Rappels en cas d’erreur : Si vous importez de nouvelles transactions et que le résultat ne se met pas à jour comme prévu, cliquez sur Actualiser dans l’onglet Données d’Excel. Veillez à l’orthographe et à la correspondance exacte des mots-clés dans Power Query, car même de légers écarts peuvent empêcher l’attribution de la bonne catégorie.
Suggestion de dépannage :Si vos catégories n’apparaissent pas correctement, examinez vos règles de colonne conditionnelle pour détecter d’éventuels conflits ou cas manquants. Il est utile de tester quelques entrées exemples avant d’appliquer les modifications à l’ensemble du jeu de données.
En résumé, que vous choisissiez les formules matricielles, la flexibilité d’une macro VBA ou l’automatisation simplifiée de Power Query, Excel vous offre plusieurs méthodes pratiques pour catégoriser efficacement vos transactions bancaires. Veillez toujours à vérifier la cohérence de vos règles de catégorisation ainsi que celle de leurs résultats, surtout lorsque vos données de transaction ou vos critères de catégorisation évoluent.
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels