Comment compter le nombre de cellules contenant un nombre de caractères sur Excel sans VBA ?
Dans ce tutoriel, je vais vous montrer deux techniques qui permettent de compter rapidement le nombre de cellules contenant un nombre donné de caractères. Nous verrons dans un premier temps comment effectuer ce dénombrement lorsque les cellules contiennent uniquement des caractères alphanumériques. Puis dans un deuxième temps, nous verrons une technique avancée, qui permettra de compter aussi bien des cellules contenant des lettres que des nombres.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Présentation
Comme nous venons de le voir dans l’introduction de cette vidéo, je vais vous montrer comment compter les cellules contenant un certain de nombre de caractères.
Pour cela, nous allons utiliser la base de données de gestion des mots de passe des utilisateurs d’un service web :
Cette base de données reprend donc les noms et prénoms de ces utilisateurs, ainsi que leur adresse email et leur mot de passe.
Ici, nous allons souhaiter dénombrer le nombre d’utilisateurs en fonction de la longueur des mots de passe :
2. Compter des cellules en fonction du nombre de caractères alphanumériques
Pour commencer, nous allons souhaiter compter cellules contenant un nombre déterminé de caractères alphanumériques.
En effet, comme nous le verrons juste après, la formule que nous allons mettre en place ici ne sera opérationnelle que dans le cas où il ne s’agit pas de devoir dénombrer des cellules contenant des nombres.
La formule que nous allons mettre en place ici repose sur la fonction NB.SI() qui permet de dénombrer des cellules en fonction d’un critère donné.
Nous allons également utiliser la notion de caractères jokers, et notamment le point d’interrogation.
Celui-ci permet en effet de remplacer un seul caractère donné, dans le cadre par exemple d’une recherche.
Sur la première ligne du tableau, nous souhaitons par exemple connaître le nombre de mots de passe contenant huit caractères, nous allons donc saisir huit points d’interrogation en tant que critère de recherche :
=NB.SI(baseUtilisateurs1[Mot de passe];"????????")
Nous noterons au passage, que nous pouvons remplacer l’un des points d’interrogation de la chaîne par un caractère en particulier pour dénombrer non seulement les mots de passe en fonction de leur taille, mais également en fonction de ce caractère.
Par exemple, pour connaître le nombre de mots de passe de huit caractères, et commençant par un « a », nous utiliserons la formule suivante :
Mais revenons sur notre formule classique avec les huit points d’interrogation.
Maintenant, pour la ligne suivante, nous souhaitons connaître le nombre de mots de passe constitués d’exactement neuf caractères.
Pour cela, nous pourrions évidemment reprendre la même formule, en ajoutant un simple point d’interrogation dans la chaîne :
=NB.SI(baseUtilisateurs1[Mot de passe];"?????????")
Mais il existe une autre méthode encore plus simple à mettre en place qui repose sur l’utilisation de la fonction REPT().
Celle-ci permet en effet de répéter un caractère un nombre de fois défini pour générer une chaîne de caractères.
Ainsi, pour créer une chaîne de neuf points d’interrogation, nous utiliserons la fonction REPT() de la manière suivante :
=REPT("?";9)
Il suffit donc d’utiliser cette fonction REPT() comme argument d’analyse de la fonction NB.SI() :
=NB.SI(baseUtilisateurs1[Mot de passe];REPT("?";9))
Encore mieux, nous pouvons maintenant rendre la fonction dynamique en reprenant le nombre de répétitions directement dans la colonne « Caractères » du tableau :
=NB.SI(baseUtilisateurs1[Mot de passe];REPT("?";[@Caractères]))
Ce qui nous permet ensuite d’étendre la formule sur toute la colonne pour calculer automatiquement le nombre de mots de passe en fonction de leur taille :
Par contre, si cette formule est simple et rapide à mettre en place, il faut savoir que celle-ci ne permettra pas de dénombrer des cellules contenant des valeurs numériques.
3. Compter des cellules en fonction du nombre de chiffres
Comme nous venons de le voir, si les cellules à dénombrer ne sont constituées que de chiffres, alors la formule que nous venons de voir à l’instant sera sans effet, et ne renverra que des résultats nuls :
Il sera alors nécessaire d’utiliser une formule totalement différente, que nous ferons ici reposer sur l’utilisation de la fonction SOMMEPROD().
Comme nous avons déjà eu l’occasion de le voir dans un tutoriel dédié à cette fonction SOMMEPROD(), il est en effet possible de détourner cette fonction de son usage d’origine afin de pouvoir effectuer des calculs numériques sur des bases de données.
La formule est alors la suivante :
=SOMMEPROD((NBCAR(baseUtilisateurs1[Mot de passe])=[@Caractères])*1)
Pour comprendre comment cette formule fonctionne, nous pouvons utiliser l’outil d’évaluation des Formules, que nous retrouverons dans le menu Formules du ruban.
Nous pouvons alors nous rendre compte que cette fonction va commencer par prendre toutes les cellules de la colonne « Mot de passe », afin de regarder si le nombre de caractères est égal à huit, grâce à la fonction NBCAR() :
Cela va permettre de constituer une matrice constituée de VRAI et de FAUX, que nous demanderons ensuite à Excel de multiplier par « 1 ».
De cette manière, tous les VRAI de la matrice seront multipliés par « 1 » pour donner des « 1 », tandis que les FAUX multipliés également par « 1 » donneront « 0 » :
Pour finir, la fonction SOMMEPROD() va additionner toutes ces valeurs afin de calculer le nombre de cellules contenant huit caractères.
Pour conclure, si vous ne deviez retenir qu’une seule formule, il s’agira de cette dernière. En effet, même si elle est moins intuitive, elle a pour caractéristique de permettre de compter le nombre de cellules contenant un nombre défini de caractères, quelqu’en soit son contenu : alphanumérique ou numérique.