Dans ce tutoriel, nous allons voir comment compter simplement le nombre fois qu'un caractère, ou une chaîne de caractères, se répète dans une cellule. Dans un premier temps, nous utiliserons une série de formules que nous imbriquerons les unes dans les autres. Puis, dans un second temps, nous verrons comment utiliser l'outil de développement d'Excel pour créer une fonction personnalisée pour arriver au même résultat, en une poignée de secondes seulement.
Compter les caractères par une formule Excel
- Tout d'abord dans la chaîne principale, saisie par exemple dans la cellule $A$1 ;
- Que l'on compare avec la même chaîne de caractère à laquelle nous allons ôter le caractère que nous souhaitons compter :
Pour commencer, voyons comment créer une formule qui va nous permettre de compter le nombre de fois qu'un simple caractère se répète dans une chaîne.
Cette formule va consister à comparer le nombre de caractères contenus :
Pour l'exemple, nous souhaitons savoir combien de fois nous retrouvons la lettre "o" dans la phrase "Découvrez nos formations gratuites sur votreassistante.net et sur excelformation.fr".
Pour commencer, nous allons saisir cette phrase dans la cellule A7 :
Pour calculer le nombre de caractères contenus dans une chaîne, nous allons utiliser la formule NBCAR(). Cette formule n'admet qu'un seul paramètre nommé "texte" et qui correspond au texte (saisi entre guillemets ou qui peut être une référence à une autre cellule contenant du texte) dont nous souhaitons connaître le nombre de caractères : =NBCAR(A7)
- texte : il s'agit du texte d'origine sur lequel nous souhaitons effectuer l'opération de substitution ;
- ancien_texte : il s'agit de la lettre ou du texte que nous souhaitons voir remplacé par un autre ;
- nouveau_texte : il s'agit du texte que nous souhaitons insérer à la place d'ancien_texte.
Pour obtenir une chaîne de caractères amputée d'un caractère spécifique, nous allons utiliser la formule SUBSTITUE() qui permet de remplacer un ou plusieurs caractères par une chaîne que l'on définit. Cette formule utilise les trois paramètres suivants :
Ici, nous allons remplacer tous les "o" par des espaces vides dans le texte saisi dans la cellule A7 : =SUBSTITUE(A7;"o";"")
Nous allons ensuite compter le nombre de caractères de cette nouvelle chaîne en utilisant à nouveau la formule NBCAR(), mais cette fois-ci sur la cellule B9 : =NBCAR(B9)
Pour finir, nous allons calculer la différence entre ces deux comptages en réalisant une simple soustraction du résultat contenu dans la cellule B10 avec celui contenu en cellule B8 : =B8-B10
Pour simplifier la construction du document, nous pouvons également réaliser cette série d'opérations en une seule cellule, en imbriquant les formules les unes dans les autres : =NBCAR(A7)-NBCAR(SUBSTITUE(A7;"o";""))
Si cette fois nous souhaitons calculer le nombre de fois qu'une chaîne constituée de plusieurs caractères se répète, il suffit de diviser le résultat obtenu par le nombre de caractères contenus dans cette chaîne. Pour cela, nous allons utiliser à nouveau la formule NBCAR().
Cette fois, nous souhaitons savoir combien de fois le texte " formation" se répète dans le texte suivant "Découvrez nos Formations gratuites sur votreassistante.net et sur excelformation.fr" : =(NBCAR(A7)-NBCAR(SUBSTITUE(A7;"formation";"")))/NBCAR("formation")
Attention !! Si nous saisissons simplement la formule ci-dessus avec la phrase d'exemple "Découvrez nos Formations gratuites sur votreassistante.net et sur excelformation.fr" (avec un " F" majuscule sur le mot " Formations"), nous allons immédiatement nous apercevoir que le résultat retourné ("1") est erroné. En effet, la formule SUBSTITUE() est sensible à la casse, ce qui signifie qu'elle fait la différence entre les minuscules et les majuscules. Ainsi, l'occurrence " Formations", qui commence par une majuscule, n'est pas remplacée par une espace vide comme dans les exemples précédents. Pour régler ce problème, nous allons devoir modifier le premier paramètre de la formule SUBSTITUE() afin d'utiliser la valeur contenue dans la cellule A7 en minuscule.
Pour ce faire, nous allons utiliser la formule MINUSCULE() : =(NBCAR(A7)-NBCAR(SUBSTITUE(MINUSCULE(A7);"formation";"")))/NBCAR("formation")
Le résultat retourné ("2") est maintenant correct !
La formule que nous venons de créer fonctionne à merveille, par contre, compte tenu de sa longueur, elle est difficilement lisible, et peut être compliquée à réutiliser. C'est pour ce genre de traitement un peu complexe qu'Excel permet de créer des fonctions personnalisées, en VBA.
Créer une fonction personnalisée
Pour créer une fonction personnalisée, il nous faut nous rendre dans l' outil de développement de Microsoft Excel. Il existe plusieurs méthodes pour cela, la plus simple étant d'effectuer un clic-droit sur l'onglet de la feuille de calcul, puis de choisir Visualiser le code.
VBA s'ouvre alors sur une feuille blanche qui va nous permettre de saisir du code intégré directement dans la feuille de calcul. Pour créer une fonction personnalisée, nous allons insérer un nouveau module (Menu Insertion, puis Module afin que VBA ouvre à nouveau une feuille blanche) :
Une fonction personnalisée est une macro-commande dont la particularité va être de renvoyer un résultat. Nous allons saisir la fonction suivante dont nous verrons le détail juste après :
- Function : pour créer une nouvelle fonction personnalisée, nous utilisons le mot clé "Function" ;
- compteCaracteres : pour définir un nom pour cette fonction : "compteCaracteres" ;
- (texte As String, compte As String) : pour demander à l'utilisateur de saisir deux informations qui devront prendre la forme d'une chaîne de caractères (variable typée en tant que string). Tout d'abord, le texte dans lequel nous souhaitons effectuer le décompte (qui peut être soit un texte saisi entre guillemets, soit une référence à une cellule), et, en second paramètre, une sous-chaîne que nous allons vouloir décompter (celle-ci peut également être soit un texte saisi entre guillemets, soit une référence à une cellule) ;
- As Long : pour spcifier à Excel que le résultat retourné devra obligatoirement être sous la forme d'un grand nombre ;
- compteCaracteres = (Len(texte) - Len(Replace(texte, compte, ""))) / Len(compte) : le cœur de notre fonction personnalisée se trouve dans cette ligne. Nous affectons comme résultat à la fonction le nombre de fois que nous retrouvons la sous-chaîne "compte" dans la chaîne principale "texte". Nous n'allons pas rentrer dans le détail du calcul "(Len(texte) - Len(Replace(texte, compte, ""))) / Len(compte)", il s'agit en effet de l'équivalent en VBA de la formule que nous venons de créer juste au-dessus ;
- End Function : pour indiquer à Excel que notre fonction personnalisée est maintenant terminée.
Function compterCaracteres(texte As String, compte As String) As Long
compterCaracteres = (Len(texte) - Len(Replace(texte, compte, ""))) / Len(compte)
End Function
- Nous commençons par saisir le signe égal ("=") ;
- Puis le nom de la fonction : "=compteCaracteres"
- Puis l'ensemble des arguments nécessaires :
=compterCaracteres(A7;"formation") Pour utiliser cette fonction, nous allons procéder de la même manière que pour l'insertion de n'importe quelle autre formule Excel :
Vous noterez au passage que notre formule est sensible à la casse, tout comme la formule que nous avions utilisée précédemment. Pour dénombrer indifféremment les caractères en minuscules et ceux en majuscules, il nous faudra donc utiliser la formule MINUSCULE() comme ceci : =compterCaracteres(MINUSCULE(A7);"formation")
Function compterCaracteres(texte As String, compte As String) As Long
texte = LCase(texte) ' Modification de la variable texte en minuscule
compterCaracteres = (Len(texte) - Len(Replace(texte, compte, ""))) / Len(compte)
End Function Nous aurions pu intégrer directement le remplacement de la chaîne "texte" par son équivalent sous forme minuscule, mais le fait de procéder comme nous le faisons permet de laisser à l'utilisateur le choix de différencier ou non les différentes casses.
À titre d'exemple, voici la fonction compteCaracteres() modifiée faire directement abstraction des différences entre les majuscules et les minuscules :
Nous utilisons ici la fonction LCase() qui permet de passer une chaîne de caractères en minuscule. Il s'agit d'un équivalent en VBA de la formule MINUSCULE().
Le fichier complet est disponible à cette adresse.
Téléchargez "Comment compter les répétitions de caractères dans une cellule et créer une fonction personnalisée sur Excel ?" au format PDF
Jeu de briques
Snake
Pacman
Bubble