Formule générique
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Sommaire
Pour compter le nombre de fois qu'un mot spécifique (ou une sous-chaîne) apparaît dans une plage de cellules, vous pouvez utiliser une formule basée sur les fonctions SUBSTITUTE, LEN et SUMPRODUCT. Dans l'exemple illustré, la formule en C11 est:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Remarque: la formule de cette page compte les éléments d'un mot dans une plage. Par exemple, si une cellule contient deux instances d'un mot, elle contribuera à 2 au nombre total. Si vous souhaitez simplement compter les cellules contenant un mot spécifique, consultez cette formule simple basée sur la fonction NB.SI.
Explication
Dans la version générique de la formule, rng représente la plage à vérifier et txt est le mot ou la sous-chaîne à compter.
Dans l'exemple illustré, B5: B8 est la plage à vérifier et C2 contient le texte (mot ou sous-chaîne) à compter.
Pour chaque cellule de la plage, SUBSTITUTE supprime la sous-chaîne du texte d'origine et LEN calcule la longueur du texte sans la sous-chaîne. Ce nombre est ensuite soustrait de la longueur du texte original. Le résultat est le nombre de caractères qui ont été supprimés par SUBSTITUTE.
Ensuite, le nombre de caractères supprimés est divisé par la longueur de la sous-chaîne. Ainsi, si une sous-chaîne ou un mot comporte 5 caractères et qu'il manque 10 caractères après sa suppression du texte d'origine, nous savons que la sous-chaîne / le mot est apparu deux fois dans le texte d'origine.
Étant donné que le calcul ci-dessus est encapsulé dans la fonction SUMPRODUCT, le résultat est un tableau qui contient un nombre pour chaque cellule de la plage. Ces nombres représentent le nombre d'occurrences de la sous-chaîne dans chaque cellule. Pour cet exemple, le tableau ressemble à ceci: (1; 1; 0; 1)
Enfin, SUMPRODUCT additionne tous les éléments du tableau pour obtenir le nombre total d'occurrences de sous-chaîne dans la plage de cellules.
Ignorer le cas
SUBSTITUTE est une fonction sensible à la casse, elle correspond donc à la casse lors de l'exécution d'une substitution. Si vous avez besoin de compter à la fois les occurrences majuscules et minuscules d'un mot ou d'une sous-chaîne, utilisez la fonction UPPER dans SUBSTITUTE pour convertir le texte en majuscules avant d'exécuter la substitution:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
