Description de formules permettant de compter les occurrences de texte, de caractères et de mots dans Excel
Résumé
Cet article contient et décrit des formules qui calculent les éléments suivants :
- le nombre d’occurrences d’une chaîne de texte dans une plage de cellules ;
- le nombre d’occurrences d’un caractère dans une cellule ;
- le nombre d’occurrences d’un caractère dans une plage de cellules ;
- le nombre de mots (ou de chaînes de texte) séparés par un caractère dans une cellule.
Informations supplémentaires
Formule permettant de compter le nombre d’occurrences d’une chaîne de texte dans une plage
=SOMME(NBCAR(plage)-NBCAR(SUBSTITUE(plage,"texte","")))/NBCAR(« texte »)
Où plage est la plage de cellules en question et « texte » est remplacé par la chaîne de texte spécifique que vous souhaitez compter.
Remarque
La formule ci-dessus doit être entrée comme formule matricielle. Pour entrer une formule comme matrice dans Excel pour Windows, appuyez sur CTRL+MAJ+ENTRÉE. Pour entrer une formule comme matrice dans Excel pour Macintosh, appuyez sur COMMANDE+RETOUR.
La formule doit être divisée par la longueur de la chaîne de texte, car la somme de la longueur de caractères de la plage est réduite par un multiple de chaque occurrence de la chaîne de texte. Cette formule peut remplacer toutes les formules citées ultérieurement dans cet article, sauf celle permettant de compter le nombre de mots dans une cellule.
Exemple 1 : Pour compter le nombre d’occurrences d’une chaîne de texte dans une plage
Démarrez Excel, puis ouvrez un nouveau classeur.
Tapez les données suivantes dans Feuil1 :
A1: Fruit A2: apple,apple A3: orange A4: apple,orange A5: grape A6: orange,grape A7: grape, apple A8: =SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,"apple","")))/LEN("apple")La valeur de la cellule A8 est 4 parce que le texte « pomme » figure à quatre reprises dans la plage.
Formule permettant de compter le nombre d’occurrences d’un caractère dans une cellule
=NBCAR(réf_cell)-NBCAR(SUBSTITUE(réf_cell,"a",""))
où cell_ref est la référence de la cellule et « a » est remplacé par le caractère que vous souhaitez compter.
Remarque
Il n’est pas nécessaire d’entrer cette formule comme formule matricielle.
Exemple 2 : Pour compter le nombre d’occurrences d’un caractère dans une cellule
Utilisez les mêmes données que dans l’exemple précédent. Supposons que vous souhaitiez compter le nombre d’occurrences du caractère « p » dans A7. Tapez la formule suivante dans la cellule A9 :
A9: =LEN(A7)-LEN(SUBSTITUTE(A7,"p",""))
La valeur de la cellule A9 est 3 parce que le caractère « p » figure trois fois dans A7.
Formule permettant de compter le nombre d’occurrences d’un caractère dans une plage
=SOMME(NBCAR(plage)-NBCAR(SUBSTITUE(plage,"a","")))
Où plage est la plage de cellules en question et « a » est remplacé par le caractère que vous souhaitez compter.
Remarque
La formule ci-dessus doit être entrée comme formule matricielle. Pour entrer une formule comme formule matricielle dans Excel, appuyez sur CTRL+MAJ+ENTRÉE.
Exemple 3 : Pour compter le nombre d’occurrences d’un caractère dans une plage
Utilisez les mêmes données que dans l’exemple précédent. Supposons que vous souhaitiez compter le nombre d’occurrences du caractère « p » dans A2:A7. Tapez la formule suivante dans la cellule A10 :
A10: =SUM(LEN(A2:A7)-LEN(SUBSTITUTE(A2:A7,"p","")))
Remarque
La formule ci-dessus doit être entrée comme formule matricielle. Pour entrer une formule comme formule matricielle dans Excel, appuyez sur CTRL+MAJ+ENTRÉE.
La valeur de la cellule A10 est 11 parce que le caractère « p » figure 11 fois dans A2:A7.
Formule permettant de compter le nombre de mots séparés par un caractère dans une cellule
=SI(NBCAR(SUPPRESPACE(réf_cell))=0;0;NBCAR(réf_cell)-NBCAR(SUBSTITUE(réf_cell;caractère,""))+1)
où cell_ref est la référence de cellule et char est le caractère qui sépare les mots.
Remarque
La formule ci-dessus ne contient pas d’espaces. Elle est tapée sur plusieurs lignes à des fins de lisibilité dans ce document. N’incluez pas d’espaces lorsque vous la tapez dans la cellule. Il n’est pas nécessaire d’entrer cette formule comme formule matricielle.
Exemple 4 : Pour compter le nombre de mots séparés par un espace dans une cellule
Pour compter le nombre de mots d’une cellule dans laquelle les mots sont séparés par un espace, procédez comme suit :
Démarrez Excel, puis ouvrez un nouveau classeur.
Tapez les données suivantes dans Feuil1 :
A1: The car drove fast A2: =IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)
La formule de la cellule A2 renvoie la valeur 4 car la chaîne contient quatre mots séparés par des espaces. Si les mots sont séparés par plusieurs espaces ou s’ils commencent ou se terminent par un espace, cela n’a pas d’importance. La fonction SUPPRESPACE supprime les caractères espace supplémentaires et les caractères espace de début ou de fin du texte de la cellule.
Dans Excel, vous pouvez également utiliser une macro pour compter les occurrences d’un caractère spécifique dans une cellule ou une plage de cellules.
Références
Pour plus d’informations sur la façon de compter des occurrences de texte, cliquez sur le numéro ci-dessous pour afficher l’article correspondant dans la Base de connaissances Microsoft :
89794 Comment pour utiliser Visual Basic pour Applications pour compter les occurrences d’un caractère dans une sélection dans Excel