Maîtriser les fonctions ADRESSE et JOINDRE.TEXTE dans Excel pour regrouper des données bancaires fragmentées

---

Sommaire

Introduction

La Problématique de regrouper des données bancaires fragmentées

Préparation des données – Ajouter des colonnes auxiliaires essentielles

Colonne « 0 » pour le Numéro d’écriture relevé
Colonne “Nb lignes” pour le Nombre de lignes à regrouper

Les fonctions Excel essentielles à connaître

JOINDRE.TEXTE (TEXTJOIN)
ADRESSE (ADDRESS)
INDIRECT
SI (IF)

Étude de cas : Construction de la formule pas à pas

1. L’objectif : Concaténer les textes avec JOINDRE.TEXTE
2. Déterminer la plage de cellules avec ADRESSE et INDIRECT
3. Ajouter la condition pour n’appliquer la formule qu’aux bonnes lignes

Résultats et avantages de cette méthode
Processus complet de traitement
Autres applications possibles
Conclusion

---

Introduction

Avez-vous déjà passé des heures à essayer de regrouper des données fragmentées dans Excel ? Découvrez une solution simple et efficace pour transformer vos relevés bancaires en informations structurées et exploitables.

Dans un monde professionnel où l’analyse de données devient primordiale, Excel reste un outil incontournable pour la manipulation et le traitement de l’information. Pourtant, les données issues de sources externes, comme les relevés bancaires, présentent souvent des défis particuliers. L’un des plus courants est celui des libellés d’opérations bancaires fragmentés sur plusieurs lignes, rendant l’analyse fastidieuse et imprécise.

Dans cet article, je partage une solution élégante que j’ai développée face à ce problème récurrent. En combinant les puissantes fonctions ADRESSE, JOINDRE.TEXTE, INDIRECT et SI, j’ai pu transformer des données bancaires désordonnées en informations structurées et exploitables.

---

La Problématique de regrouper des données bancaires fragmentées

Après avoir importé un relevé bancaire dans Excel, j’ai constaté que les « natures de l’opération » se retrouvaient éparpillées sur plusieurs lignes, au lieu d’être regroupées sur une seule. Cette structure rend impossible toute analyse pertinente : filtrage, tri, ou recherche deviennent des opérations hasardeuses.

Prenons un exemple concret : une opération de virement peut apparaître ainsi dans un relevé importé :

L’objectif est de regrouper ces trois lignes d’information en une seule entrée cohérente : « VIREMENT EN FAVEUR DE M. DUPONT JEAN REF : VIR25075FR », tout en conservant les données de date et de montant.

---

Préparation des données – Ajouter des colonnes auxiliaires essentielles

Pour résoudre ce problème, la première étape consiste à ajouter deux colonnes auxiliaires qui nous permettront d’identifier correctement les groupes de lignes à fusionner :

 

Colonne « 0 » pour le Numéro d’écriture relevé

Cette colonne attribue un identifiant unique à chaque groupe de lignes appartenant à la même opération bancaire. Elle est nommée “0” pour démarrer le comptage de lignes sans modifier la formule entre F2 et F3.

Formule utilisée : `=SI(A2>0;1+F1;F1)`

Cette formule génère un numéro séquentiel qui augmente uniquement lorsqu’une nouvelle date d’opération apparaît. Toutes les lignes liées à une même opération reçoivent le même numéro, créant ainsi un « groupe » identifiable.

 

Colonne “Nb lignes” pour le Nombre de lignes à regrouper

Cette colonne calcule, pour chaque groupe, le nombre total de lignes à concaténer.

Formule utilisée : `=SI(E2=E1;"";NB.SI(E:E;E2))`

Cette formule compte le nombre de lignes ayant le même identifiant dans la colonne « 0 ». Elle n’affiche ce nombre que sur la première ligne de chaque groupe (laissant les autres lignes du même groupe vides), ce qui nous permettra d’identifier facilement où appliquer notre formule de concaténation.

 

Ces deux colonnes auxiliaires sont donc fondamentales pour que la formule de concaténation sache exactement quelles cellules doivent être regroupées.

Après l’ajout de ces colonnes, notre exemple de données ressemblerait à ceci :

---

Les fonctions Excel essentielles à connaître

Pour compléter notre solution, quatre fonctions Excel s’avèrent particulièrement utiles :

 

JOINDRE.TEXTE (TEXTJOIN)

Cette fonction permet de concaténer plusieurs textes avec un séparateur spécifique. Contrairement à la fonction CONCATENER plus ancienne, JOINDRE.TEXTE offre l’avantage de pouvoir ignorer les valeurs vides et d’accepter une plage de cellules directement.

Syntaxe : `JOINDRE.TEXTE(délimiteur; ignorer_vide; texte1; [texte2]; ...)`

 

ADRESSE (ADDRESS)

Cette fonction convertit des valeurs numériques de ligne et de colonne en une référence de cellule textuelle.

Syntaxe : `ADRESSE(num_ligne; num_colonne; [type_ref]; [type_a1]; [nom_feuille])`

 

INDIRECT

INDIRECT transforme une référence textuelle en une référence réelle de cellule utilisable dans les calculs.

Syntaxe : `INDIRECT(ref_texte; [type_a1])`

 

SI (IF)

Cette fonction conditionnelle permet d’exécuter différentes actions selon qu’une condition est vraie ou fausse.

Syntaxe : `SI(condition; [valeur_si_vrai]; [valeur_si_faux])`

---

Étude de cas : Construction de la formule pas à pas

La formule finale que j’ai développée pour résoudre ce problème est la suivante (à placer dans la colonne G) :

=SI(F2="";"";
SI(F2>=1;
JOINDRE.TEXTE(" ";VRAI;
INDIRECT(ADRESSE(LIGNE();2;3;1)&":"&ADRESSE(LIGNE()+F2-1;2;3;1)));
""))

 

Décomposons cette formule en suivant sa logique de construction :

1. L’objectif : Concaténer les textes avec JOINDRE.TEXTE

Tout d’abord, la base de notre solution est la fonction JOINDRE.TEXTE qui permet de fusionner plusieurs cellules en une seule :

JOINDRE.TEXTE(" ";VRAI;[plage de cellules à concaténer])

– Le premier paramètre ` » « ` est le séparateur (ici un espace)

– Le deuxième paramètre `VRAI` indique que nous voulons ignorer les cellules vides

– Le troisième paramètre sera la plage de cellules à concaténer

 

2. Déterminer la plage de cellules avec ADRESSE et INDIRECT

Ensuite, pour construire dynamiquement la plage de cellules à concaténer, nous utilisons une combinaison de fonctions :

INDIRECT(ADRESSE(LIGNE();2;3;1)&":"&ADRESSE(LIGNE()+F2-1;2;3;1))

Cette expression complexe se construit comme suit :

– `LIGNE()` renvoie le numéro de la ligne actuelle → résultat : 2 dans notre exemple

– `ADRESSE(LIGNE();2;3;1)` crée une référence textuelle à la cellule de la colonne C (colonne 3) sur la ligne courante → résultat : « B2 »

– `ADRESSE(LIGNE()+F2-1;3;3;1)` crée une référence à la cellule se trouvant F2-1 lignes plus bas (où F2 contient le nombre de lignes à concaténer) → résultat : « B4 » (car F2 = 3)

– Ces deux références sont jointes par le caractère « : » pour créer une plage → résultat : « B2:B4 »

– `INDIRECT(…)` convertit cette plage textuelle en une référence utilisable par JOINDRE.TEXTE → résultat : la plage réelle B2:B4

 

3. Ajouter la condition pour n’appliquer la formule qu’aux bonnes lignes

Enfin, nous encapsulons tout cela dans des conditions SI pour traiter correctement les différents cas :

SI(F2="";"";SI(F2>=1;[notre formule de concaténation];""))

– La première condition `G2= » »` vérifie si la cellule dans la colonne G (n° lignes) est vide

– Si elle est vide, on renvoie une chaîne vide

– Sinon, on vérifie si G2 est supérieur ou égal à 1

– Si c’est le cas, on applique notre formule de concaténation

– Sinon, on renvoie une chaîne vide

 

Cette structure garantit que la concaténation ne s’applique qu’aux lignes où c’est nécessaire, tout en laissant les autres lignes vides.

---

Résultats et avantages de cette méthode

Après application de cette formule dans la colonne H, notre exemple de données se présente ainsi :

Notez que la formule s’applique uniquement à la première ligne de chaque groupe d’opérations, en regroupant toutes les informations fragmentées en une seule chaîne de texte dans la colonne G, tandis que la colonne B (Nature de l’opération) reste inchangée.

---

Processus complet de traitement

Voici le workflow complet pour transformer un relevé bancaire avec des entrées fragmentées :

1. Importer les données du relevé bancaire dans Excel, soit par un fichier téléchargé sur le site de la banque, soit en traitant les données du relevé bancaire PDF grâce à Power Query
2. Ajouter la colonne « 0 » équivalant au n° d’écriture du relevé pour identifier les groupes d’opérations à concaténer
3. Ajouter la colonne “nb lignes” pour calculer le nombre de lignes par groupe
4. Appliquer la formule de concaténation dans la colonne G pour regrouper les descriptions
5. Copier-coller en valeur le contenu de la colonne G dans la colonne B (Nature de l’opération) et supprimer les colonnes 0 et 1 désormais inutiles

 

6. Trier les données sur la colonne “Date” pour ne conserver que les lignes contenant des informations complètes (première ligne de chaque groupe), puis supprimer les lignes vides si besoin

7. Nettoyer et analyser les données désormais structurées

 

Une fois ce processus terminé, votre tableau résultant ressemblera à ceci :

 

Les avantages sont nombreux :

– Lisibilité améliorée : toutes les informations pertinentes sont regroupées

– Facilité d’analyse : les données peuvent être filtrées et triées efficacement

– Automatisation : la formule s’adapte à différentes longueurs de descriptions

– Précision : aucune information n’est perdue dans le processus

– Traçabilité : les colonnes auxiliaires permettent d’identifier facilement les groupes d’opérations

– Préservation des données d’origine : le processus permet de garder les données originales intactes jusqu’à la finalisation du traitement

 

Bonnes pratiques :
Pour garantir un traitement sécurisé et efficace de vos données, suivez ces bonnes pratiques :

1.     Utilisation d’un onglet dédié : Effectuez le travail de traitement dans un onglet distinct intitulé « Traitement ». Cela permet de conserver vos données sources intactes et d’organiser votre travail de manière structurée.

Capture d’écran de l’onglet « Traitement »

2.     Copier-coller avec liaison : Une fois le traitement terminé, copiez des colonnes pertinentes (par exemple, A, G, C et D) de l’onglet « Traitement » vers un onglet « Résultat » en utilisant un copier-coller avec liaison. Cela vous permet de conserver une trace des modifications tout en gardant les données sources accessibles.
3.     Conversion en valeurs : Après avoir copié les données dans l’onglet « Résultat », convertissez-les en valeurs pour éviter toute modification accidentelle des formules.
4.     Tri et nettoyage : Triez les données dans l’onglet « Résultat » par date pour isoler les lignes vides en fin de tableau. Supprimez ces lignes pour obtenir un tableau propre et exploitable.

Capture d’écran de l’onglet « Résultat »

5.     Sauvegarde régulière : Sauvegardez régulièrement votre travail, surtout après des modifications importantes, pour éviter toute perte de données.
6.     Documentation : Documentez chaque étape de votre processus de traitement. Cela facilitera la reproductibilité et permettra à d’autres utilisateurs de comprendre votre méthodologie.

En suivant ces bonnes pratiques, vous assurerez une gestion optimale de vos données tout en préservant leur intégrité et leur traçabilité.

---

Autres applications possibles

Cette technique ne se limite pas aux relevés bancaires. Elle peut être appliquée à de nombreux cas où des données sont fragmentées :

1. Factures clients où les descriptions de produits s’étendent sur plusieurs lignes
2. Inventaires avec des caractéristiques de produits dispersées
3. Commentaires issus de formulaires ou d’enquêtes
4. Données bibliographiques où les informations (auteurs, titres, etc.) sont séparées

 

Il est possible d’adapter la formule en modifiant :

– Les références de cellules pour cibler d’autres colonnes

– Le délimiteur dans JOINDRE.TEXTE pour correspondre à vos besoins de formatage

– La condition dans la fonction SI pour appliquer la concaténation selon d’autres critères

– Les formules des colonnes auxiliaires pour s’adapter à la structure de vos données

---

Conclusion

La maîtrise des fonctions avancées d’Excel comme ADRESSE et JOINDRE.TEXTE, combinée à une structure intelligente de colonnes auxiliaires, ouvre des possibilités considérables pour le traitement de données complexes. La solution présentée dans cet article permet de résoudre efficacement le problème courant des données fragmentées dans Excel, particulièrement dans les relevés bancaires.

 

L’approche en deux étapes — d’abord identifier et numéroter les groupes d’opérations, puis appliquer une concaténation intelligente — illustre parfaitement comment des problèmes apparemment complexes peuvent être résolus avec élégance en décomposant le défi en sous-tâches gérables.

 

En comprenant la logique derrière chaque composant de cette solution, vous pouvez l’adapter à vos besoins spécifiques et l’intégrer dans vos propres workflows Excel. Cette méthode démontre qu’avec les bonnes fonctions et une approche structurée, Excel peut transformer des données chaotiques en informations exploitables.

 

N’hésitez pas à partager vos propres solutions ou à poser des questions sur d’autres défis que vous rencontrez avec Excel. La communauté des utilisateurs est une source inépuisable d’astuces et de techniques innovantes !

 

—

 

*Mots-clés : Excel, JOINDRE.TEXTE, ADRESSE, INDIRECT, relevé bancaire, concaténation, manipulation de données, formules imbriquées, automatisation Excel, nettoyage de données*