Recherche de mots clé dans une cellule

[bouhh01142]

Bonjour à tous,

Ceci est mon premier post sur ce forum, et je pars confiant en ce qui concerne vos future réponses
Pour ce qui est de mon "problème", je cherche donc un moyen de pouvoir extraire dans une cellule les mots clé en me basant sur leur nombre d’occurrences. Quelle démarche me conseilleriez-vous?

Merci d'avance,
Bouhhh

 

[bouhh01142]

Re : Recherche de mots clé dans une cellule

Bonjour à vous,
Voila, je relance le sujet car j'aurais quelques petites modifications à faire sur les fonctions existantes. J'ai essayé de me pencher sur le code mais cela a été peu concluant
Ce que j'aimerais faire dans le document en pièce jointe ce serais d'ajouter à la colonne migration un 1 si la date de publication est supérieur au 1er Janvier 2010 et un 0 si elle est inférieur.
Il y autre chose qui me parait un peu (vraiment) plus compliqué, ce serait d'ajouter comme condition au booléen de "migration" de lui attribuer un 1 si pour chaque article au moins 5 mot_cles sont présents dans la liste de mots présente dans les feuilles au nom des catégories J’espère ne pas vous avoir perdu.
En gros pour que migration soit égal à un, il faut que la date soit supérieur au 1er Janvier 2010 et qu'au moins 5 de ses mots clés soit présents dans la feuille portant le nom de sa catégorie.
N'hésitez pas pour les questions, je suis conscient que ça parait difficile à comprendre.

Merci beaucoup

 

[david84]

Re : Recherche de mots clé dans une cellule

Bonjour,

J'ai essayé de me pencher sur le code mais cela a été peu concluant

Même si en l'occurrence l'utilisation d'expression rationnelle ne rend pas le code facile à comprendre, ce genre de phrase ne fait pas avancer le smilblick en ce sens qu'il ne me donne pas d'indication sur ce que tu as ou non compris, des essais que tu as fait,etc, et donc je ne sais quoi te dire pour te le faire comprendre...

Ce que j'aimerais faire dans le document en pièce jointe ce serais d'ajouter à la colonne migration un 1 si la date de publication est supérieur au 1er Janvier 2010 et un 0 si elle est inférieur.

Je ne connais pas ton niveau en formule sur Excel, mais cela n'est pas très compliqué en soit de tester si une date est antérieure, égale ou postérieure à une autre date. La difficulté dans ton cas est que le mode de format des dates que tu utilises te complique la tâche : tu peux t'en sortir en utilisant la fonction TEXTE pour la reformater, puis multiplier par 1 la chaîne obtenue pour en faire une date

TEXTE(B2;"jj/mm/aaaa")*1

.
Il ne te reste plus qu'à la comparer avec la date voulue pour obtenir le résultat de ton test.

Il y autre chose qui me parait un peu (vraiment) plus compliqué, ce serait d'ajouter comme condition au booléen de "migration" de lui attribuer un 1 si pour chaque article au moins 5 mot_cles sont présents dans la liste de mots présente dans les feuilles au nom des catégories

Cela, effectivement est plus compliqué mais faisable, mais pour savoir ce que je peux te proposer, j'ai besoin d'infos :
- OccurrenceMot2 te donne les mots les plus utilisés avec leur occurrence entre parenthèse : as-tu besoin de l'indication de ces occurrences ou pas ? Cela n'est pas problématique en soit mais en fonction de ta réponse, la solution proposée sera plus ou moins complexe, et donc plus ou moins facile pour toi à comprendre.

Si c'est une indication utile, je te conseille de la garder mais cela ne te sert à rien, autant ne pas la prendre en compte.

- j'ai crû comprendre que Migration doit être égale à 1 si la date est postérieure au 01/01/2010 et si le nombre de mots retrouvé est au moins égal à 5 dans ton exemple : est-ce bien cela ?
A+

 

[bouhh01142]

Re : Recherche de mots clé dans une cellule

Merci de ta réponse.
Tout d'abord en ce qui concerne mon niveau de programmation sur Excel j'en ai fais fais que très et ça remonte à facile 4 ans, donc je repars de zero, mais mes connaissances en programmation d'autres langages me permettent de m'y retrouver sur certaines choses.
Pour ce qui est de la fonction occurrence mots, Non le nombre d’occurrence ne m'est pas utile.

- j'ai crû comprendre que Migration doit être égale à 1 si la date est postérieure au 01/01/2010 et si le nombre de mots retrouvé est au moins égal à 5 dans ton exemple : est-ce bien cela ?

Si par "le nombre de mots retrouvé est au moins égal à 5" tu entends que le nombre de mot_clé concordant avec ceux des liste dans les feuilles est au moins égal à 5, oui c'est bien ça.

 

[david84]

Re : Recherche de mots clé dans une cellule

Re
Comme je n'ai que 3 exemple, difficile de tester : c'est donc à toi de la faire.
Je t'ai placé les 2 solutions : en gardant ou non les occurrences.
Sans les occurrences :
La fonction OccurrenceMot devient :

Function OccurrenceMot3(Chaine As String, occurrence As Double) As String
Dim s As Variant, T(), T2(), TOc(), oRegExp As Object, Matches As Object
Dim i As Double, dico As Object, k As Byte, Borne As Byte
Application.Volatile
Chaine = Trim(LCase(Replace(Chaine, """", "")))
Set oRegExp = CreateObject("vbscript.regexp")
With oRegExp
  .Global = True
  .MultiLine = True
    'traitement des espaces
    .Pattern = "\s+"
    If .Test(Chaine) = True Then Chaine = .Replace(Chaine, " ")
        
    'traitement des caractères ponctuation
    .Pattern = "[,?!;.:…_()\[\]{}“”«»\\|/§~#`^@]+" ' '’-–—
    If .Test(Chaine) = True Then Chaine = .Replace(Chaine, " ")
      
    'traitement des apostrophes
    .Pattern = "(^|\s)(c|d|l|n|qu|s)(’|')"
    If .Test(Chaine) = True Then Chaine = .Replace(Chaine, " ")
      
    'épurage des espaces en trop "
    .Pattern = "(\s){2,}"
    If .Test(Chaine) = True Then Chaine = .Replace(Chaine, " ")
End With

Set dico = CreateObject("scripting.dictionary")
s = Split(Trim(Chaine))
For i = LBound(s) To UBound(s)
    If Application.CountIf([Mots], s(i)) = 0 Then
        dico(s(i)) = dico(s(i)) + 1
    End If
Next i
T = dico.keys
T2 = dico.items
Borne = Application.Min(dico.Count, occurrence)
ReDim TOc(1 To Borne)
For i = 1 To Borne
    k = Application.Match(Application.Max(T2), T2, 0) - 1
    TOc(i) = T(k): T2(k) = ""
Next i
OccurrenceMot3 = Join(TOc, vbLf)
End Function

Et la formule :

=SI((TEXTE(B2;"jj/mm/aaaa")*1>DATE(2010;1;1))*SOMMEPROD(N(ESTNUM(CHERCHE(INDIRECT(C2&"!$A$1:$A"&NBVAL(INDIRECT(C2&"!A:A")));Feuil1!I2))))>4;1;0)

Avec les occurrences :
une fonction personnalisée pour traiter en fonction des onglets :

Function OccurrenceMotOnglet(Chaine As Range, occurrence As Double, Onglet As String) As Boolean
Dim s As Variant, oRegExp As Object, Matches As Object, Tablo As Variant, Nb As Double, Test, sChaine As String, i As Byte
sChaine = Chaine.Text
Set oRegExp = CreateObject("vbscript.regexp")
With oRegExp
  .Global = True
  .MultiLine = True
    'suppression des parenthèses et de leur contenu
    .Pattern = "\s\(\d\)\s?"
    If .Test(sChaine) = True Then sChaine = .Replace(sChaine, " ")
End With
s = Split(Trim(sChaine))
Tablo = Sheets(Onglet).Range("A1:A" & Sheets(Onglet).Range("A" & Sheets(Onglet).Rows.Count).End(xlUp).Row)
For i = LBound(s) To UBound(s)
    Test = Application.Match(s(i), Tablo, 0)
    If Not IsError(Test) Then Nb = Nb + 1
Next i
If Nb >= occurrence Then OccurrenceMotOnglet = True
End Function

Les 3 arguments de la fonction :
- la cellule où se trouve la chaîne de caractères
- le nombre d'occurrences minimum permettant de valider la condition
- la cellule où se trouve l'onglet à traiter
La formule :

=SI(ET(TEXTE(B2;"jj/mm/aaaa")*1>DATE(2010;1;1);OccurrenceMotOnglet(E2;5;C2)=VRAI);1;0)

A+

 

[bouhh01142]

Re : Recherche de mots clé dans une cellule

Ça m'as l'air parfait, je ne trouve pas de faille
Par contre pourrais tu m'indiquer les différence entre la formule en :

- F2 : =SI(ET(TEXTE(B2;"jj/mm/aaaa")*1>DATE(2010;1;1);OccurrenceMotOnglet(E2;5;C2)=VRAI);1;0)​

- J2 : =SI((TEXTE(B2;"jj/mm/aaaa")*1>DATE(2010;1;1))*SOMMEPROD(N(ESTNUM(CHERCHE(INDIRECT(C2&"!$A$1:$A"&NBVAL(INDIRECT(C2&"!A:A")));Feuil1!I2))))>4;1;0)​

Étant donné qu'elle donne le même résultat.

 

[david84]

Re : Recherche de mots clé dans une cellule

Re
La 1ère formule utilise une fonction personnalisée qui te permet de continuer à utiliser la fonction OccurrenceMot2 qui te donne le nombre d'occurrence entre parenthèses.
La 2ème est utilisable à partir du moment où tu utilises OccurrenceMot3 (qui ne te donne pas le nombre d'occurrences entre parenthèses) à la place de OccurrenceMot2.
Dans ce cas, on s'en sort avec les fonctions natives d'Excel (INDIRECT notamment).
A+
A+

 

[bouhh01142]

Re : Recherche de mots clé dans une cellule

Okay, merci!!
J'ai aussi remarqué que la premiere formule ne prenait pas en compte les doublons de differentes listes,
mais la deuxieme les comptes.
Donc serait-ce plus simple d'empecher la saisie des doublons?

 

[david84]

Re : Recherche de mots clé dans une cellule

Re
Comme tu as le choix, c'est à toi de choisir la solution qui te sembles la plus appropriée...par contre je ne vois pas à quoi tu fais référence puisque les différentes versions de OccurrenceMot éliminent les doublons dès le départ (utilisation de Dictionary), et je ne vois pas de doublon dans la colonne A de chaque onglet (aucun intérêt d'ailleurs).
Par contre, ce qui peut se passer, c'est que si tu as maison et maisonnée, CHERCHE te trouve les 2. Si c'est cela qui pose problème, utilise la solution avec la fonction personnalisée OccurrenceMotOnglet.
A+

 

[bouhh01142]

Re : Recherche de mots clé dans une cellule

En pièce jointe pour le même article migration prends 2 valeurs differentes.
Dans l'onglet maison il y a 5 fois le même mots.
La formule en K2 ne réagit donc pas correctement

 

[david84]

Re : Recherche de mots clé dans une cellule

Re
Ton fichier exemple ne comportait aucun doublon (d'ailleurs je n'en vois pas l'intérêt !).
J'ai donc travaillé selon ton exemple doit se doit de correspondre à ton fichier original.
Si la 2ème solution ne te convient pas, tu n'as plus qu'à utiliser la 1ère solution.
La question est : la 1ère solution te convient-elle ? Si non, pourquoi ?
A+

 

[bouhh01142]

Re : Recherche de mots clé dans une cellule

La 1ère solution me convient dans le sens où l'appel dans la formule est plutôt simple.
La deuxième car dans la liste des mots clés il n'y a plus le nombre d’occurrence.

Je pense que je préférerais utiliser la deuxième, mais étant donnée que les listes dans les onglets évolueront, ce serais pas mal d'ajouter une MFC comme tu me l'as fait précédemment afin de signaler les doublons.
Et facultativement, si l'appel pour la formule de la solution n 2 pouvait être simplifier ce serais pas mal. Mais c'est du bonus.

 

[david84]

Re : Recherche de mots clé dans une cellule

Re
Dans ce cas, puisque tu ne veux pas le nombre d'occurrences, OccurrenceMot devient :

Function OccurrenceMot(Chaine As String, occurrence As Double) As String
Dim s As Variant, T(), T2(), TOc(), oRegExp As Object, Matches As Object
Dim i As Double, dico As Object, k As Byte, Borne As Byte
Application.Volatile
Chaine = Trim(LCase(Replace(Chaine, """", "")))
Set oRegExp = CreateObject("vbscript.regexp")
With oRegExp
  .Global = True
  .MultiLine = True
    'traitement des espaces
    .Pattern = "\s+"
    If .Test(Chaine) = True Then Chaine = .Replace(Chaine, " ")
        
    'traitement des caractères ponctuation
    .Pattern = "[,?!;.:…_()\[\]{}“”«»\\|/§~#`^@]+" ' '’-–—
    If .Test(Chaine) = True Then Chaine = .Replace(Chaine, " ")
      
    'traitement des apostrophes
    .Pattern = "(^|\s)(c|d|l|n|qu|s)(’|')"
    If .Test(Chaine) = True Then Chaine = .Replace(Chaine, " ")
      
    'épurage des espaces en trop "
    .Pattern = "(\s){2,}"
    If .Test(Chaine) = True Then Chaine = .Replace(Chaine, " ")
End With

Set dico = CreateObject("scripting.dictionary")
s = Split(Trim(Chaine))
For i = LBound(s) To UBound(s)
    If Application.CountIf([Mots], s(i)) = 0 Then
        dico(s(i)) = dico(s(i)) + 1
    End If
Next i
T = dico.keys
T2 = dico.items
Borne = Application.Min(dico.Count, occurrence)
ReDim TOc(1 To Borne)
For i = 1 To Borne
    k = Application.Match(Application.Max(T2), T2, 0) - 1
    TOc(i) = T(k): T2(k) = ""
Next i
OccurrenceMot = Join(TOc, vbLf)
End Function

La fonction personnalisée OccurrenceMotOnglet peut être simplifiée ainsi :

Function OccurrenceMotOnglet(Chaine As Range, occurrence As Double, Onglet As String) As Boolean
Dim s As Variant, Tablo As Variant, Nb As Double, Test, sChaine As String, i As Byte
Application.Volatile
sChaine = Chaine.Text
s = Split(sChaine, vbLf)
Tablo = Sheets(Onglet).Range("A1:A" & Sheets(Onglet).Range("A" & Sheets(Onglet).Rows.Count).End(xlUp).Row)
For i = LBound(s) To UBound(s)
    Test = Application.Match(s(i), Tablo, 0)
    If Not IsError(Test) Then Nb = Nb + 1
Next i
If Nb >= occurrence Then OccurrenceMotOnglet = True
End Function

Et la formule permettant de vérifier "Migration" devient :

=SI(ET(TEXTE(B2;"jj/mm/aaaa")*1>DATE(2010;1;1);OccurrenceMotOnglet(E2;5;C2)=VRAI);1;0)

Concernant la MFC, je te l'ai déjà donnée dans les fichiers de mes réponses précédentes (tu n'as plus qu'à l'adapter).
A+