Demande de l'utilisateur du forum :
J'importe mon relevé de banque et j'aimerais catégoriser chaque mouvement en se basant sur la présence de certains mots dans le libellé de ceux-ci.
Problématique
Avec la fonction RECHERCHEX, il est possible de rechercher un texte dans une base de données recensant des mots clés et de ramener une valeur qui lui est liée (ici la catégorie).
Exemple : On recherche "Banane" et on récupère la catégorie "Fruit" (un classique).
Mais elle ne sera pas utilisable dans ce cas !
On part d'un libellé dont seulement une partie correspond à notre clé de recherche !
Exemple : Les libelles "Une banane" ou "3 bananes vertes" doivent correspondre à "Banane" pour permettre de récupérer la catégorie "Fruit".
Remarque :
> L'option de correspondance "Caractères générique" de la fonction RECHERCHEX ne nous aidera pas ici car elle correspond à la recherche inverse.
Exemple : Faire correspondre "Banane" à "Une banane" ou à "3 bananes vertes".
Les tableaux de départ
Le tableau correspondant au relevé de banque (TabRB)
Avec la colonne vide Catégorie à alimenter avec notre formule.
Le tableau des catégories (TabCateg)
La formule proposée
= JOINDRE.TEXTE("-" ;
VRAI ;
FILTRE(TabCateg[Catégorie] ;
ESTNUM(CHERCHE(TabCateg[Libellé] ; TabRB[@Libellé]))))Résultat de la formule
Voir les valeurs de la colonne vide Catégorie.
Pour la dernière ligne, on est dans un cas de double correspondance !
Expliquons un peu cette formule
- La fonction CHERCHE renvoie la matrice des positions des libellés (TabCateg) trouvés dans le libellé (TabRB) ou l'erreur #VALEUR! quand cette position n'a pas été trouvé (pas de correspondance).
- La fonction ESTNUM permet d'obtenir une matrice de VRAI (=Trouvé)/FAUX (Non trouvé).
- Les valeurs de position (valeurs numériques) sont transformé en VRAI.
- Les valeurs d'erreur (#VALEUR!) sont transformé en FAUX.
- La fonction FILTRE récupère les catégories (toutes celles ou la matrice ESTNUM = VRAI).
- On obtient un tableau dynamique (voir Formules de tableaux dynamiques, une nouvelle logique de conception)/matrice que l'on "aplatie" en concaténant les valeurs avec la fonction JOINDRE.TEXTE pour les cas de correspondance multiple.
Autre formule possible
Pour le plaisir d'utiliser la fonction MAP/LAMBDA.
= JOINDRE.TEXTE("-" ; VRAI ;
MAP(SEQUENCE(NBVAL(TabCateg[Libellé])) ;
LAMBDA(v ;
SI(NB.SI.ENS([@Libellé] ; "*"&INDEX(TabCateg[Libellé] ; v) &"*") ;
INDEX(TabCateg[Catégorie] ; v) ;
"" ) ) ) )Expliquons un peu cette formule
- Le principe est de rechercher tous les libellés de TabCateg dans le libellé testé de TabRB.
- NBVAL donne le nombre de valeurs dans TabCateg.
- SEQUENCE permet de générer la liste 1, 2, 3... jusq'au nombre de valeur de TabCateg.
- MAP/LAMBDA permet de parcourir chaque ligne de TabCateg pour rechercher si le libellé de Tabcateg (1er fonction INDEX) se trouve dans le libellé de TabRB.
- Si le libellé de TabCateg est trouvé dans le libellé testé de TabRB (via NB.SI.ENS) alors on récupère la catégorie correspondante (2eme fonction INDEX) sinon une chaîne vide "" (fonction SI).
- On obtient un tableau dynamique (voir Formules de tableaux dynamiques, une nouvelle logique de conception) que l'on "aplatie" en concaténant les valeurs avec la fonction JOINDRE.TEXTE pour les cas de correspondance multiple.
Autres solution pour d'ancienne versions d'Excel
= RECHERCHE(2^20 ; CHERCHE(TabCateg[Libellé];TabRB[@Libellé]) ; TabCateg[Catégorie])A valider en matricielle (Ctrl + Maj + Entrer) !
Formule ne gérant pas le cas des correspondances multiples comme par exemple la dernière ligne de TabRB (seul "Alimentation" sera retourné).
- La fonction RECHERCHE ne trouvant pas la valeur 2^20 (=1048576) prendra la dernière valeur numérique de la matrice renvoyée par CHERCHE.
Merci pour votre attention bienveillante.