Comment calculer la similarité de 2 textes avec Google Sheets ?

Aujourd'hui, on se plonge à nouveau dans Google Sheets avec un tuto rapide pour te montrer comment mesurer la similarité entre 2 contenus.

Choix de la métrique : l'indice de Jaccard

Il existe énormément de façons de mesurer un taux de similarité entre deux textes : distance de Levenshtein, indice de Dice, similarité cosinus, etc.

Ici, je vais utiliser l'indice de Jaccard, aussi appelé coefficient de Jaccard.

Essentiellement pour deux raisons :

• sa simplicité de calcul
• la littérature abondante sur le sujet dans le milieu du SEO

L'indice de Jaccard se calcule de la façon suivante (A et B étant ici les 2 ensembles à comparer) :

J(A,B) = |A ∩ B| / |A ∪ B|

Autrement dit, c'est le rapport entre l'intersection de 2 ensembles et l'union de ces 2 ensembles.

Calculer l'intersection

1. Créer une liste avec chaque mot

Avant tout, il faut découper chaque texte en mots.

Je vais donc utiliser la formule que j'avais présentée il y a quelques semaines pour compter le nombre de mots dans une cellule Google Sheets, en enlevant simplement le COUNTA.

Ce qui donne :

=SPLIT(REGEXREPLACE(A2;"[!""#$%&()*+,./:;<=>[email protected][\]^_`{|}~]";"");" ")

Je vais ensuite afficher les valeurs les unes à la suite des autres avec TRANSPOSE.

Je montre uniquement le process pour le texte 1, mais bien entendu c'est la même chose pour le texte 2.

2. Trouver les mots-clés en commun

Pour trouver les mots-clés en commun entre les 2 ensembles, je vais reprendre la formule que j'avais utilisée pour comparer deux colonnes dans Google Sheets.

Je vérifie donc si chaque mot figurant dans la plage de mots issue du découpage de Texte 1 est présent dans la plage de mots issue du découpage de Texte 2.

Si oui, je l'affiche.

Sinon, je n'affiche rien.

Au passage, pas besoin de tout passer en minuscule, la comparaison via COUNTIF ne tient pas compte de la casse :)

3. Dédoublonner et filtrer

Il ne reste plus qu'à :

• dédoublonner en ajoutant UNIQUE devant la formule
• filtrer les cellules vides avec FILTER

Et voilà : la première étape est OK !

Calculer l'union

1. Concaténer les 2 textes

D'abord, je concatène les deux textes en prenant soin, comme pour l'étape précédente :

• de supprimer la ponctuation (avec REGEXREPLACE)
• de supprimer les espaces superflus (avec TRIM)
• de supprimer les éventuels caractères indésirables (avec CLEAN)

2. Transformer en liste de mots

Ensuite, j'utilise SPLIT pour convertir le texte en liste de mots.

3. Dédoublonner

Enfin, j'utilise UNIQUE pour dédoublonner.

La fonction UNIQUE ne fonctionne qu'avec des données verticales, je dois donc intercaler la fonction TRANSPOSE.

Compter et diviser

Il ne reste plus qu'à compter les valeurs dans chaque plage (l'intersection et l'union).

Puis faire la division.

Formule finale

Et voilà la formule finale !

=COUNTA(FILTER(UNIQUE(ARRAYFORMULA(LOWER(IF(COUNTIF(TRANSPOSE(SPLIT(REGEXREPLACE(B2;"[!""#$%&()*+,./:;<=>[email protected][\]^_`{|}~]";"");" "));TRANSPOSE(SPLIT(REGEXREPLACE(A2;"[!""#$%&()*+,./:;<=>[email protected][\]^_`{|}~]";"");" ")))>0;TRANSPOSE(SPLIT(REGEXREPLACE(A2;"[!""#$%&()*+,./:;<=>[email protected][\]^_`{|}~]";"");" "));""))));UNIQUE(ARRAYFORMULA(LOWER(IF(COUNTIF(TRANSPOSE(SPLIT(REGEXREPLACE(B2;"[!""#$%&()*+,./:;<=>[email protected][\]^_`{|}~]";"");" "));TRANSPOSE(SPLIT(REGEXREPLACE(A2;"[!""#$%&()*+,./:;<=>[email protected][\]^_`{|}~]";"");" ")))>0;TRANSPOSE(SPLIT(REGEXREPLACE(A2;"[!""#$%&()*+,./:;<=>[email protected][\]^_`{|}~]";"");" "));""))))<>""))/COUNTA(UNIQUE(TRANSPOSE(SPLIT(TEXTJOIN(" ";TRUE;CLEAN(TRIM(REGEXREPLACE(A2;"[!""#$%&()*+,./:;<=>[email protected][\]^_`{|}~]";"")));CLEAN(TRIM(REGEXREPLACE(B2;"[!""#$%&()*+,./:;<=>[email protected][\]^_`{|}~]";""))));" "))))