[Excel] Identification de citations très proches (coeff. Jaccard)

Marsh Posté le 10-11-2023 à 22:59:59

Bonjour,
J'ai une liste de citations dans la colonne A d'une feuille Excel ; une citation par ligne. Certaines sont des variantes très proches, mais différentes.

Par exemple :
Pour moi, je n'ai qu'un besoin, celui de réussir
Je n'ai qu'un besoin, celui de réussir.

J'aimerais identifier (pour ensuite supprimer) les doublons et les phrases très proches. Je n'ai jamais réussi à construire cette formule... :cry:

Mes divers tests infructueux m'ont mené vers le coefficient de Jaccard (que je ne connaissais pas) et qui fonctionne comme expliqué ici : https://www.statisticshowto.com/jaccard-index/

Je ne suis plus sûr de rien, même pas qu'il faille utiliser le coefficient de Jaccard...
Je n'ai rien trouvé sur internet ; pourtant je pense que ce genre de besoin (trouver des presques doublons) doit être assez fréquent, et Excel devrait être l'outil pour y parvenir...

Je suis preneur de vos suggestions de formules ou de façons de faire pour parvenir à mes fins.
Merci par avance.

Julien

Message édité par jultey le 12-11-2023 à 11:41:29

Reply

Marsh Posté le 10-11-2023 à 22:59:59

Reply

Marsh Posté le 11-11-2023 à 10:38:28

Hello,

je suis pas du tout expert excel / vba mais il est possible que ca se gère avec une macro et effectivement coeff de Jaccard pour lequel tu définira à partir de quel indice de similarité tu considère que c'est un doublon ou pas.

Je ne sais vraiment pas si c'est faisable en VBA par contre en Python ou PHP ca se fait sans pb

bon courage

Message cité 1 fois

Reply

Marsh Posté le 11-11-2023 à 10:53:37

eldoy a écrit :

Hello,

je suis pas du tout expert excel / vba mais il est possible que ca se gère avec une macro et effectivement coeff de Jaccard pour lequel tu définira à partir de quel indice de similarité tu considère que c'est un doublon ou pas.

Je ne sais vraiment pas si c'est faisable en VBA par contre en Python ou PHP ca se fait sans pb

bon courage

J'ai demandé à ChatGPT de m'aider. Il a tenté avec plusieurs formules différentes mais n'a jamais pu m'en proposer une qui fonctionne (ci-dessous ses propositions):

=NB.SI.ENS(MOTS.CLE(TEXTE.EN.MINUSCULE(A1)); MOTS.CLE(TEXTE.EN.MINUSCULE($A$1:$A$100))) / NB.SI(UNIQUE(MOTS.CLE(TEXTE.EN.MINUSCULE($A$1:$A$100))); "<>" )

=NB.SI.ENS(MOTS.CLE(MINUSCULE(A1)); MOTS.CLE(MINUSCULE($A$1:$A$100))) / NB.SI(UNIQUE(MOTS.CLE(MINUSCULE($A$1:$A$100))); "<>" )

=NB.SI.ENS(UNIQUE(SPLIT(MINUSCULE(A1), " " )); UNIQUE(SPLIT(MINUSCULE($A$1:$A$100), " " ))) / NB.SI(UNIQUE(SPLIT(MINUSCULE($A$1:$A$100), " " )); "<>" )

=NB.SI.ENS(UNIQUE(DÉCOUPER(MINUSCULE(A1); " " )); UNIQUE(DÉCOUPER(MINUSCULE($A$1:$A$100); " " ))) / NB.SI(UNIQUE(DÉCOUPER(MINUSCULE($A$1:$A$100); " " )); "<>" )

=NB.SI.ENS(UNIQUE(FILTRE(CARACT(ADRESSE(LIGNE($A$1:$A$100); COLONNE($A$1)); 1)); MINUSCULE($A$1:$A$100)=MINUSCULE(A1))); UNIQUE(FILTRE(CARACT(ADRESSE(LIGNE($A$1:$A$100); COLONNE($A$1)); 1)); MINUSCULE($A$1:$A$100)<>MINUSCULE(A1)))) / NB.SI(UNIQUE(FILTRE(CARACT(ADRESSE(LIGNE($A$1:$A$100); COLONNE($A$1)); 1)); MINUSCULE($A$1:$A$100)<>MINUSCULE(A1))); "<>" )

Ensuite, il a effectivement tenté une fonction en VBA :

Function ListeMots(phrase As String) As Variant
Dim mots() As String
mots = Split(phrase, " " )
ListeMots = mots
End Function

associée avec cette formule :

=SI(ET(NB.SI.ENS(UNIQUE(ListeMots(A1)); UNIQUE(ListeMots($A$1:$A$100))) / NB.SI(UNIQUE(ListeMots($A$1:$A$100)); "<>" ) > 0,8; LIGNE(A1)<>LIGNE($A$1:$A$100)); "Doublon ou presque doublon"; "Pas de correspondance" )

et ensuite toujours la méthode ListeMots associée avec

=CONCAT(UNIQUE(ListeMots(A1)))

et

=SI(ET(NB.SI.ENS(UNIQUE(B1); UNIQUE($B$1:$B$100)) / NB.SI(UNIQUE($B$1:$B$100); "<>" ) > 0,8; LIGNE(A1)<>LIGNE($A$1:$A$100)); "Doublon ou presque doublon"; "Pas de correspondance" )

Et là j'ai abandonné...
Mais tout faire en VBA, ça serait peut-être la solution...

Merci pour ta réponse eldoy

Message édité par jultey le 11-11-2023 à 10:54:15

Reply

Marsh Posté le 11-11-2023 à 15:10:43

La fonction UNIQUE est récente, donc ne marchera pas si tu as une version 2016 ou antérieure.

En VBA il y a ce code qui compare 2 phrases et met en rouge les mots différents ;
https://fr.extendoffice.com/documen [...] arity.html

Ou ce tuto qui montre comment trouver un mot dans une phrase, même si j'ai bien compris qu'il te fallait qqch de plus complexe ;
https://www.bonbache.fr/recherche-e [...] s-529.html

Tu auras plus de réponses dans le topic Excel ;
https://forum.hardware.fr/hfr/Windo [...] 3434_1.htm

Message cité 1 fois

Reply

Marsh Posté le 11-11-2023 à 19:39:24

arnuche a écrit :

La fonction UNIQUE est récente, donc ne marchera pas si tu as une version 2016 ou antérieure.

En VBA il y a ce code qui compare 2 phrases et met en rouge les mots différents ;
https://fr.extendoffice.com/documen [...] arity.html

Ou ce tuto qui montre comment trouver un mot dans une phrase, même si j'ai bien compris qu'il te fallait qqch de plus complexe ;
https://www.bonbache.fr/recherche-e [...] s-529.html

Tu auras plus de réponses dans le topic Excel ;
https://forum.hardware.fr/hfr/Windo [...] 3434_1.htm

Cette fonction VB fait le job. Il faut l'appeler en colonne B. =CoefficientJaccard(A2; LIGNE(A2)) et ainsi de suite pour les lignes suivantes.
Merci arnuche pour ta réponse

Function CoefficientJaccard(phrase As String, ligneActuelle As Integer) As String
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Feuil1" )

Dim motsPhrase As Variant
Dim motsComparee As Variant
Dim longueurIntersection As Double
Dim longueurUnion As Double
Dim coefficient As Double
Dim phraseSimilaire As String
Dim ligneSimilaire As Integer
Dim i As Integer, j As Integer

' Initialisation des variables
coefficient = 0
phraseSimilaire = ""
ligneSimilaire = 0

' Découper la phrase actuelle en mots
motsPhrase = Split(phrase, " " )

' Boucle à travers toutes les lignes de la colonne A
For i = 1 To ws.Cells(ws.Rows.Count, "A" ).End(xlUp).Row
' Ignorer la comparaison avec la même ligne
If i <> ligneActuelle Then
' Découper la phrase comparée en mots
motsComparee = Split(ws.Cells(i, 1).Value, " " )

' Calcul de l'intersection et de l'union des ensembles de mots
longueurIntersection = 0
longueurUnion = 0

' Boucle à travers les mots de la phrase actuelle
For j = LBound(motsPhrase) To UBound(motsPhrase)
' Vérifier si le mot est présent dans la phrase comparée
If UBound(Filter(motsComparee, motsPhrase(j))) > -1 Then
longueurIntersection = longueurIntersection + 1
End If
Next j

' Calcul de la longueur de l'union
longueurUnion = UBound(motsPhrase) + UBound(motsComparee) + 2 - longueurIntersection

' Calcul du coefficient de Jaccard
If longueurUnion > 0 Then
Dim coefficientLocal As Double
coefficientLocal = longueurIntersection / longueurUnion

' Mettre à jour le coefficient et la phrase similaire si nécessaire
If coefficientLocal > coefficient Then
coefficient = coefficientLocal
phraseSimilaire = ws.Cells(i, 1).Value
ligneSimilaire = i
End If
End If
End If
Next i

' Retourner le résultat sous la forme "Coefficient-Jaccard-PhraseSimilaire-LigneSimilaire"
CoefficientJaccard = coefficient & " - " & phraseSimilaire & " - " & ligneSimilaire
End Function

Message édité par jultey le 11-11-2023 à 19:39:57

Reply

Marsh Posté le 13-11-2023 à 16:19:13

Chapeau pour ton boulot, tu as dû apporter pas mal de modifications au code original.
En gros ça fonctionne comment : en voyant si un certain nombre de mots sont communs à 2 phrases ?
Si oui quel nombre ? Parce que je n'en vois pas dans ton code.

Message cité 1 fois

Reply

Marsh Posté le 13-11-2023 à 18:48:54

arnuche a écrit :

Chapeau pour ton boulot, tu as dû apporter pas mal de modifications au code original.
En gros ça fonctionne comment : en voyant si un certain nombre de mots sont communs à 2 phrases ?
Si oui quel nombre ? Parce que je n'en vois pas dans ton code.

Version courte à tes questions : Oui, en voyant si un certain nombre de mots sont en commun. Pas de nombre dans l'algo, j'ai filtré ensuite dans Excel.

Version longue :
Pour chaque ligne, la phrase est découpée en mots.
Puis pour chaque ligne, le coefficient de Jaccard (que je rappelle j'ai découvert dans le cadre de mon besoin ; et donc que je ne maîtrise pas du tout) est calculé : (A,B) = (A ∩ B) / (A ∪ B)
En gros, c'est le rapport de l'intersection sur l'union de 2 ensembles :
Mon premier ensemble (A) (de mots) est la phrase que je compare, et l'autre ensemble (B) (de mots) est (tour à tour) toutes les autres phrases. Donc pour chaque mot de ma phrase à comparer (A), l'algo compte le nombre de mots en commun (entre phrase A et phrase B) (l'intersection), et le divise par le nombre de mots total (nombre de mots "différents" de A + B). Ca donne un nombre qui est 0 si tous les mots sont différents et 1 si tous les mots sont identiques.
Et comme pour une phrase donnée (A), ce coefficient est calculé entre cette phrase et toutes les autres phrases, on peut identifier la phrase (B) qui a le coefficient le plus élevé, et le faire afficher à côté de la phrase A, avec le numéro de ligne de sa plus proche jumelle.

"Je suis un homme" et "Je suis une femme"
Ca va donner :
(2 mots en commun) / (6 mots différents en tout) = 0,33

"Je suis un homme" et "Je suis un garçon"
(3 mots en commun) / (5 mots différents en tout) = 0,40

On voit bien que dans le 2e exemple, les phrases sont plus proches ; et le coeff est plus proche de 1.

Quand j'ai mis la formule dans mon fichier Excel, j'ai ensuite mis un filtre sur toutes les lignes dont le coefficient était supérieur à 0,8 et ça m'a fait apparaitre toutes les phrases similaires.

Message édité par jultey le 13-11-2023 à 18:55:25

Reply

Marsh Posté le 13-11-2023 à 18:57:33

Astucieux, merci pour les infos.

Reply

[Excel] Identification de citations très proches (coeff. Jaccard)

Sujets relatifs:

Leave a Replay