Exceller

Compter des mots spécifiques dans une plage

Count Specific Words Range

Formule Excel: compter des mots spécifiques dans une plageFormule générique
= SUMPRODUCT (( LEN (rng)- LEN ( SUBSTITUTE (rng,txt,'')))/ LEN (txt))
Résumé

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 i les substances d'un mot dans une plage. Par exemple, si une cellule contient deux instances d'un mot, elle contribuera à 2 au nombre total. Si tu veux juste compter cellules contenant un mot spécifique , voir cette formule simple basée sur la fonction COUNTIF .



Explication

Dans la version générique de la formule, rng représente la plage à vérifier, et SMS est le mot ou la sous-chaîne à compter.



comment faire la moyenne des données dans Excel

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.

quelle est la différence entre les formules et les fonctions dans Excel

É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: {1101}

comment calculer l'écart moyen dans Excel

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, donc elle correspondra à 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))
Auteur Dave Bruns


^