Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

Pour expert - Rechercher un mot clé parmi plusieurs mot clé dans une chaîne de caractère et afficher les catégories liées

T

Trashcicci

XLDnaute Nouveau
Bonjour à la communauté.

ça fait bien une dizaine d'heure que je suis sûr le problème mais je n y arrive pas. J'aurais besoin d'une réponse d'un champion dans le domaine.

FICHIER :
J'ai un fichier excel, 2 onglets : un DATA (avec des données) et un CAT (catégorie), voici une capture d'écran de chaque onglet et du résultat que j'aimerais.

ONGLET DATA :


ONGLET CAT :



BESOIN :

L'idée est donc de créer une FONCTION qui se trouve dans les cellules de DATA!D1.

Cette fonction doit CHERCHER les mots clé de la colonne CAT!A et trouver si ces derniers sont présent dans la DESCRIPTION (DATA!C). Si elle trouve des mots clés présents, elle doit alors afficher les catégories référentes (CAT!B) dans la colonne CATEGORIE (DATA!D).

S'il y a plusieurs mots clé de plusieurs catégorie, les catégories seront écrites à la suite (ordre pas important) dans la colonne CATEGORIE (DATA!D).

J'espère que m'a demande est assez clair.

DATA donnée

ID
1
2
4
5
6
7
8
9
Description
tamina set Pompiliani redierit securitas temporis, per omnes tamen quotquot sunt partes terrarum, ut domina suscipitur et regina et ubique patrum voiture reverenda cum auctoritate canities populique Romani nomen circumspectum et verecundum.
provinciae bello quondam piratico catervis mixtae praedonum a Servilio pro consule missae sub iugum factae sunt vectigales. et hae quidem regiones velut in Maison
Nec vox accusatoris ulla licet subditicii in his malorum quaerebatur acervis ut saltem specie tenus crimina praescriptis legum committerentur AviONs
ordinis vertices MAISON sub uno
Haec igitur lex in amicitia HELICOPTER sanciatur, ut neque rogemus res turpes nec Avion faciamus rogati.
Et licet quocumque oculos flexeris feminas adfatim ANANAS multas AVION spectare cirratas, quibus, si nupsissent, per aetatem ter iam nixus poterat suppetere
gyris, dum exprimunt innumera simulacra, quae finxere fabulae ANANAS POmmE theatrales.
et tataki Voiture vox ulla pommE verbis Maison

CAT Donnée :

MotClé
avion, voiture, helicopter
pomme, banane
maison
Catégorie
VEHICULE
FRUIT
IMMOBILIER

Je ne peux pas faire de VPA, j'ai besoin d'une réponse avec uniquement les formules excels. Je n'ai pas Moffice 365 donc je ne peux pas utiliser XLOOKUP.

Quelques liens qui pourront vous être utiles :
Fonction RECHERCHEV - INDEX
Fonction CHERCHE TROUVE

ENCORE merci d'avance à ceux qui prendront le temps de répondre et tester !
J'ai vraiment besoin de vouuuuus !

Bonne fin de journée à vous !
 
Trier par date Trier par votes

Dudu2

XLDnaute Barbatruc
Bonjour,
Je vois pas d'autres issue que de passer par du VBA et un fichier test à fournir.

Cette fonction doit CHERCHER les mots clé de la colonne CAT!A et trouver si ces derniers sont présent dans la DESCRIPTION (DATA!C)
Cliquez pour agrandir...
Ça veut dire quoi ? Si au moins 1 est présent ? Si tous sont présents ?
 
Vote positif 0 Vote négatif

Dugenou

XLDnaute Barbatruc
Bonsoir,
Une proposition avec modification des listes de mots par catégorie pour avoir des zones nommées dynamiques (prévues jusque la ligne 10 de CAT) et une formule matricielle (à valider par ctrl+maj+enter) et comme Dudu : j'aurai apprécié d'avoir un fichier exemple.
Risque de temps de calcul long si beaucoup de catégories
Cordialement
 

Pièces jointes

  • Trashcicci.xlsx
    12.2 KB · Affichages: 20
Vote positif 0 Vote négatif
T

Trashcicci

XLDnaute Nouveau
Bonjour Dudu2 et Dugenou. Oui je voulais mettre mon fichier mais je savais pas comment procéder sur ce forum car je présume qu'il faut quand même protéger la communauté de fichier infecté par des virus. Comment procédez-vous ? Avec un lien style WeTransfer / SwissTransfer ?

Merci Dugenou, je regarderais ta proposition dans la journée. Merci encore pour le temps consacré !!

Ah ben je viens de voir un ptit bouton "joindre fichier"
 

Pièces jointes

  • TrieEtAttributionCategorieSelonMotCleFonction.xlsx
    10.8 KB · Affichages: 6
Vote positif 0 Vote négatif

Dugenou

XLDnaute Barbatruc
Bonjour,
Voici la formule adaptée à ton fichier. J'ai modifié la présentation des mots clés (1 par cellule) et créé les zone nommées dynamiques (formules/gestionnaire de noms)
Cordialement
 

Pièces jointes

  • TrieEtAttributionCategorieSelonMotCleFonction.xlsx
    11.7 KB · Affichages: 5
Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc
Bonjour Trashcicci, Dudu2, Dugenou,

Une formule plus simple qui ne nécessite pas de validation matricielle en D2 :
Code: 
=SUBSTITUE(SUPPRESPACE(REPT(" "&CAT!B$2;SIGNE(SOMMEPROD(--ESTNUM(CHERCHE(CAT!C$2:Z$2;C2)/ESTTEXTE(CAT!C$2:Z$2)))))&REPT(" "&CAT!B$3;SIGNE(SOMMEPROD(--ESTNUM(CHERCHE(CAT!C$3:Z$3;C2)/ESTTEXTE(CAT!C$3:Z$3)))))&REPT(" "&CAT!B$4;SIGNE(SOMMEPROD(--ESTNUM(CHERCHE(CAT!C$4:Z$4;C2)/ESTTEXTE(CAT!C$4:Z$4))))));" ";", ")
à tirer vers le bas.

La feuille CAT a été adaptée en utilisant la commande Convertir.

A+
 

Pièces jointes

  • TrieEtAttributionCategorieSelonMotCleFonction(1).xlsx
    12.4 KB · Affichages: 13
Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc
Bonjour le forum,

La solution par formules est jouable parce qu'il n'y a que 3 lignes à étudier dans la feuille CAT.

S'il y en a beaucoup plus il faut utiliser une fonction VBA :
VB: 
Function Categorie$(texte$, P As Range)
Dim tablo, repere$(), i&, s, j%
texte = LCase(texte) 'minuscules
tablo = Intersect(P, P.Parent.UsedRange.EntireRow).Resize(, 2) 'matrice, plus rapide
ReDim repere(1 To UBound(tablo))
For i = 2 To UBound(tablo)
    s = Split(tablo(i, 1), ",")
    For j = 0 To UBound(s)
        If InStr(texte, LCase(Trim(s(j)))) And repere(i) = "" Then Categorie = Categorie & ", " & tablo(i, 2): repere(i) = "x"
Next j, i
Categorie = Mid(Categorie, 3)
End Function
Le code doit être placé impérativement dans un module standard, fichier joint.

Edit : je crains que la fonction Split n'existe pas sur MAC

A+
 

Pièces jointes

  • VBA-TrieEtAttributionCategorieSelonMotCleFonction(1).xlsm
    19.9 KB · Affichages: 10
Dernière édition:
Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc
Si la fonction Split n'existe pas sur MAC on revient au tableau éclaté de la feuille CAT :
VB: 
Function Categorie$(texte$, P As Range)
Dim tablo, ncol%, repere$(), i&, j%, x$
texte = LCase(texte) 'minuscules
tablo = Intersect(P, P.Parent.UsedRange)
ncol = UBound(tablo, 2)
ReDim repere(1 To UBound(tablo))
For i = 2 To UBound(tablo)
    For j = 3 To ncol
        x = LCase(Trim(tablo(i, j)))
        If x <> "" Then If InStr(texte, x) And repere(i) = "" Then _
            Categorie = Categorie & ", " & tablo(i, 2): repere(i) = "x"
Next j, i
Categorie = Mid(Categorie, 3)
End Function
Fichier (2).
 

Pièces jointes

  • VBA-TrieEtAttributionCategorieSelonMotCleFonction(2).xlsm
    20 KB · Affichages: 5
Vote positif 0 Vote négatif
T

Trashcicci

XLDnaute Nouveau
Hello l'équipe ! Merci pour toutes ces réponses. J'ai du trop courir la semaine passée j'ai pas eu le temps de vous faire mon retour ! J'espère avoir le temps today ! En tt cas merci, vu les commentaires on dirait qu'il y a eu des coups de génies !
 
Vote positif 0 Vote négatif
T

Trashcicci

XLDnaute Nouveau


Bonjour Job75 !

Merci pour ton incroyable formule qui match bien avec mon besoin.

C'est un sacré boulot ! Merci merci et encore merci !

J'aurais du coup un 2ème chalenge à te faire relever : est-ce qu'il est possible d'inversé les paramètres ? je m'explique :

En lisant ta formule, je comprends que tu répètes le meme code pour les différentes CATEGORIE (j'ai rajouté des catégories) et que les mots clés eux sont défini par une PLAGE :


Est-ce qu'il serait possible d'inverser cela ? C'est à dire que les CATEGORIES soient séléctionnées par des plages et que les mots clés soient les "REPT" ? Ainsi cela fera moins de répète (de ligne) car je pense pas dépasser les 3-4 mots clés, par contre les catégories, c'est possible que j'en ai une 20ène environ.

Merci encore pour ton aide passé, l'aide présent que tu as donné grâce à ton savoir faire et l'aide futur potentiel si tu arrives à résoudre cette 2ème demande !

XXX
 

Pièces jointes

  • TrieEtAttributionCategorieSelonMotCleFonction_SolutionDeJob75.xlsx
    12.9 KB · Affichages: 5
Vote positif 0 Vote négatif

job75

XLDnaute Barbatruc
Bonjour,

Je vois que vous n'avez pas compris la formule.

Puisqu'en feuille CAT il n'y a que 5 catégories il suffit de 5 REPT en D2 :
Code: 
=SUBSTITUE(SUPPRESPACE(REPT(" "&CAT!B$2;SIGNE(SOMMEPROD(--ESTNUM(CHERCHE(CAT!C$2:Z$2;C2)/ESTTEXTE(CAT!C$2:Z$2)))))
&REPT(" "&CAT!B$3;SIGNE(SOMMEPROD(--ESTNUM(CHERCHE(CAT!C$3:Z$3;C2)/ESTTEXTE(CAT!C$3:Z$3)))))
&REPT(" "&CAT!B$4;SIGNE(SOMMEPROD(--ESTNUM(CHERCHE(CAT!C$4:Z$4;C2)/ESTTEXTE(CAT!C$4:Z$4)))))
&REPT(" "&CAT!B$5;SIGNE(SOMMEPROD(--ESTNUM(CHERCHE(CAT!C$5:Z$5;C2)/ESTTEXTE(CAT!C$5:Z$5)))))
&REPT(" "&CAT!B$6;SIGNE(SOMMEPROD(--ESTNUM(CHERCHE(CAT!C$6:Z$6;C2)/ESTTEXTE(CAT!C$6:Z$6))))));" ";", ")
A+
 

Pièces jointes

  • TrieEtAttributionCategorieSelonMotCleFonction_SolutionDeJob75 (1).xlsx
    12.9 KB · Affichages: 9
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…