Compter les mots et répétitions dans les cellules Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Compter sauts, répétitions et mots

C'est une petite série d'astuces exploitant des techniques similaires que nous abordons ici. L'idée est de pouvoir compter des faits remarquables dans les cellules Excel. Il peut s'agir du nombre de répétions d'un mot. Mais il peut aussi s'agir de compter le nombre de mots présents dans la cellule. Enfin, il est aussi question de dénombrer les sauts de lignes dans les cellules. Et ce sont quelques astucieux calculs qui livrent la solution.

Source et procédure
Nous suggérons d'appuyer nos démonstrations sur des tableaux typiques qu'il convient de récupérer.

• Télécharger le classeur compter-mots-et-sauts.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous trouvons une feuille enrichie de trois petits tableaux. En deuxième colonne du premier, il est question de compter tous les sauts de ligne détectés dans chaque cellule de la première colonne. Nous en déduirons le nombre de lignes dans chaque case. En troisième colonne du deuxième tableau, nous devons compter le nombre de fois qu'un mot, stipulé en deuxième colonne, est trouvé dans la phrase inscrite en première colonne. Enfin, en seconde colonne du troisième tableau, nous devons compter le nombre de mots présents dans chaque cellule de la première colonne.

Compter les lignes d'une cellule
Pour dénombrer les lignes d'une cellule, l'astuce consiste à calculer la différence entre le nombre de caractères de la chaîne et le nombre de caractères de cette même chaîne sans les sauts de ligne. Pour compter les caractères, il suffit d'employer la fonction Excel NbCar. Pour supprimer les sauts de ligne, nous devons exploiter les fonctions Substitue et Car.

• Sélectionner la cellule du premier décompte à effectuer, soit C4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction pour compter les caractères, suivie d'une parenthèse, soit : NbCar(,
• Désigner la première expression en cliquant sur sa cellule B4,
• Puis, fermer la parenthèse de la fonction NbCar,

A ce stade, nous venons simplement de compter les caractères contenus dans la cellule. Nous devons leur soustraire le même décompte sans les sauts de ligne. Un saut de ligne est effectivement considéré comme un caractère.

• Taper le symbole moins (-) pour engager la soustraction,
• Inscrire de nouveau la fonction de décompte, suivie d'une parenthèse, soit : NbCar(,
• Inscrire la fonction de remplacement suivie d'une parenthèse, soit : Substitue(,
• Désigner le texte dans lequel réaliser ce remplacement avec les coordonnées de la cellule B4,
• Taper un point-virgule (;) pour passer dans l'argument du caractère à remplacer,

Il s'agit du saut de ligne. Il peut être désigné par la fonction Car avec l'attribut 10 en paramètre.

• Inscrire cette fonction suivie d'une parenthèse, soit : Car(,
• Lui passer la valeur 10 et fermer la parenthèse de la fonction,
• Taper un point-virgule (;) pour passer dans l'argument du texte de remplacement,
• Inscrire simplement deux guillemets, soit : "",

Ainsi, en remplaçant chaque saut de ligne par une chaîne vide, nous les supprimons tous. S'ils existent, le décompte sera forcément inférieur au précédent. Et cette différence nous livrera le nombre de lignes.

• Fermer la parenthèse de la fonction Substitue,
• Fermer la parenthèse de la fonction NbCar,
• Puis, ajouter une unité à ce résultat, soit : +1,

On ajoute une unité au résultat car chaque remplacement grandit l'écart d'un point. Un saut correspond bien à deux lignes, soit 1 + 1.

• Valider la formule par le raccourci clavier CTRL + Entrée,

Ainsi, nous gardons active la case du résultat pour l'exploiter dans l'enchaînement.

• Double cliquer sur la poignée de la cellule pour répliquer la logique sur le tableau,

Comme vous pouvez le voir, tous les décomptes de lignes sont parfaitement cohérents. Et la formule que nous avons construite est fort simple: =NBCAR(B4)-NBCAR(SUBSTITUE(B4; CAR(10); ""))+1

Une exception subsiste néanmoins. Il s'agit du cas d'une cellule vide. Du fait de l'unité ajoutée (+1), notre calcul considèrera la présence d'une ligne. Pour pallier ce défaut, il suffit d'englober la syntaxe dans une fonction conditionnelle :

=SI(B4=""; 0; NBCAR(B4)-NBCAR(SUBSTITUE(B4; CAR(10); ""))+1)

Compter les répétitions
Désormais, nous devons compter les répétitions d'un mot dans une phrase. L'astuce consiste à calculer la différence entre le nombre de caractères dans la cellule et ce même nombre soustrait des caractères issus du mot cherché. Pour cela, nous avons une fois encore besoin des fonctions Excel NbCar et Substitue.

• Sélectionner la première répétition à trouver en cliquant sur sa cellule G4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Ouvrir une parenthèse,

Nous le verrons, le calcul devra être divisé par un facteur pour aboutir au résultat escompté. C'est la raison de cette factorisation amorcée.

• Inscrire la fonction de décompte suivie d'une parenthèse, soit : NbCar(,
• Désigner la première phrase en cliquant sur sa cellule E4,
• Puis, fermer la parenthèse de la fonction NbCar,

A ce stade, nous sommes donc censés connaître le nombre total de caractères contenus dans la cellule. Nous devons lui imputer tous les caractères issus du mot clé cherché.

• Taper le symbole moins (-) pour annoncer la soustraction à suivre,
• Inscrire de nouveau la fonction de décompte, suivie d'une parenthèse, soit : NbCar(,
• Inscrire la fonction de remplacement, suivie d'une parenthèse, soit : Substitue(,
• Désigner le texte où il s'agit de remplacer en cliquant sur sa cellule E4,
• Taper un point-virgule (;) pour passer dans l'argument du texte à remplacer,
• Désigner le mot clé de la même ligne par ses coordonnées, soit F4,
• Taper un point-virgule (;) pour passer dans l'argument du texte de remplacement,
• Taper deux guillemets ("") pour supprimer le mot clé à chaque fois qu'il est trouvé,
• Fermer la parenthèse de la fonction Substitue,
• Fermer la parenthèse de la fonction NbCar,
• Puis, fermer la parenthèse de la factorisation,

Cette différence livre le nombre de caractères manquants suite à l'opération. Ce nombre est forcément un multiple du nombre de mots supprimés. Pour obtenir la quantité de mots répétés, nous devons diviser ce résultat par le nombre de caractères du mot cherché.

• Taper le symbole slash (/) du pavé numérique pour engager la division,
• Inscrire de nouveau la fonction de décompte suivie d'une parenthèse, soit : NbCar(,
• Désigner le texte cherché par les coordonnées de sa cellule, soit : F4,
• Fermer la parenthèse de la fonction NbCar,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

Le premier résultat tombe. Il semble parfaitement cohérent. Il indique que le mot Cuisine est présent deux fois dans la première phrase. Effectivement, on le trouve une seconde fois tel quel et une première fois dans le verbe Cuisiner.

• Double cliquer sur la poignée du résultat pour répliquer la logique sur tout le tableau,

Le mot mari n'est compté qu'à deux reprises une première fois et considéré comme unique une seconde fois. Pourtant la phrase de recherche est strictement identique. L'explication tient à la différence de casse. La première lettre est en majuscule dans le second cas. La fonction Substitue est effectivement sensible à la casse. Ce mot devrait être trouvé trois fois. Le constat est le même pour la recherche sur le prénom Marie. Il est présent une première fois tel quel. Puis on le trouve une seconde fois dans le terme bain-marie. Pourtant, la formule ne le compte qu'une seule fois. Pour pallier ce défaut, nous devons neutraliser ces différences de casse dans la fonction de remplacement. L'astuce consiste à effectuer la recherche dans la phrase convertie en majuscules pour le mot cherché, lui-même converti en majuscule.



Pour cela, en cellule G4, la syntaxe doit être adaptée comme suit :

=(NBCAR(E4)-NBCAR(SUBSTITUE(MAJUSCULE(E4); MAJUSCULE(F4); "")))/NBCAR(F4)

Ensuite et bien entendu, la formule doit être répliquée sur les cellules du dessous.

Compter les mots
Pour compter les mots d'une phrase, nous devons faire la différence entre les caractères de la chaîne et les caractères de cette même chaine sans les espaces. A chaque espace, correspondent deux mots. Donc il s'agira ensuite d'ajouter une unité à ce décompte pour délivrer le total de mots trouvés.

• Sélectionner la case du premier décompte, soit la cellule J4,
• Taper le symbole égal (=) pour initier la formule,
• Inscrire la fonction de décompte des caractères suivie d'une parenthèse, soit : NbCar(,
• Désigner la première phrase en cliquant sur sa cellule I4,
• Puis, fermer la parenthèse de la fonction NbCar,
• Taper le symbole moins (-) pour engager la soustraction,
• Inscrire encore la fonction de décompte des caractères, suivie d'une parenthèse, soit : NbCar(,
• Inscrire la fonction de remplacement suivie d'une parenthèse, soit : Substitue(,
• Désigner la cellule I4 pour le texte à analyser,
• Taper un point-virgule (;) pour passer dans l'argument du texte à remplacer,
• Inscrire un espace entre guillemets, soit : " ",
• Taper un point-virgule (;) pour passer dans l'argument du texte de remplacement,
• Inscrire deux guillemets consécutifs ("") pour supprimer chaque espace trouvé,
• Fermer la parenthèse de la fonction Substitue,
• Fermer la parenthèse de la fonction NbCar,
• Ajouter une unité à ce résultat, soit : +1,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

Le premier résultat tombe et il semble pour le moins surprenant, en tous cas incohérent. Trois mots sont décelés tandis que deux seulement sont présents. Les apparences sont parfois trompeuses.

• Double cliquer sur la cellule I4,

Cette astuce permet de l'atteindre en mode saisie. Et comme vous pouvez le voir, le point d'insertion est placé un caractère après le dernier mot. Cette chaîne propose donc des espaces dissimulés, alors que leur vocation originelle est de séparer les mots. Qu'à cela netienne, nous corrigerons le défaut après coup en espérant que les premières pierres ont permis de bâtir une formule suffisamment efficace.

• Enfoncer la touche Echap du clavier pour sortir du mode saisie,
• Sélectionner de nouveau le résultat en cliquant sur sa cellule J4,
• Double cliquer sur la poignée du résultat pour répliquer la logique sur tout le tableau,

En l'absence d'espace résiduel, tous les autres décomptes semblent effectivement parfaitement cohérents. Si vous ajoutez un mot à l'une des expressions, le calcul s'actualise instantanément pour le considérer.

La formule que nous avons bâtie est la suivante :

=NBCAR(I4)-NBCAR(SUBSTITUE(I4; " "; ""))+1

Mais nous devons l'adapter et l'améliorer pour deux raisons. Elle doit ignorer les cellules vides. De plus, elle doit neutraliser tous les espaces situés aux extrémités des chaînes et non entre les mots. Et c'est l'occasion de présenter une nouvelle astuce. Elle consiste à découvrir la fonction Excel Supprespace. Cette dernière permet de supprimer tous les espaces qui ne sont pas intercalés entre deux mots.

En conséquence, il convient d'adapter la précédente syntaxe comme suit :

=SI(I4=""; ""; NBCAR(SUPPRESPACE(I4))-NBCAR(SUBSTITUE(I4; " "; ""))+1)

Après validation, il est bien sûr nécessaire de double cliquer sur la poignée du résultat pour répliquer la formule sur les autres cellules et ainsi corriger le précédent calcul.



Et comme vous pouvez le voir, nous avons parfaitement réussi à compter les mots présents dans chaque cellule du tableau et ce, sans la moindre erreur, malgré certaines embûches bien cachées.