Comment compter le nombre de cellules contenant un nombre de caractères sur Excel sans VBA ?

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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 :

Excel formation - Compter cellules nombre caractères - 01

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 :

Excel formation - Compter cellules nombre caractères - 02

 

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];"????????") 

Excel formation - Compter cellules nombre caractères - 03

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 :

Excel formation - Compter cellules nombre caractères - 04

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) 

Excel formation - Compter cellules nombre caractères - 05

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 :

Excel formation - Compter cellules nombre caractères - 06

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 :

Excel formation - Compter cellules nombre caractères - 07

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() :

Excel formation - Compter cellules nombre caractères - 08

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 » :

Excel formation - Compter cellules nombre caractères - 09

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.

 



Articles qui pourraient vous intéresser

Comment créer un graphique de distribution en 5 minutes chrono ?
L’astuce Excel pour imprimer le tableau que vous voulez en un clic !
Comment créer facilement des QR codes sans VBA avec Excel ?
Transformez vos données en graphique tornade interactif facilement sur Excel !
Comment créer un graphique Iceberg dans Excel ?
Comment créer un graphique dynamique en quelques minutes sur Excel sans VBA ni TCD ?
Comment créer des feux de signalisation tricolores dynamiques dans Excel sans VBA ?
Comment créer un graphique englobant 2 en 1 sur Excel ?
Évitez ces erreurs fatales dans vos graphiques Excel !
Comment créer un graphique jauge (compteur de vitesse) interactif sous Excel en quelques minutes ?
Le secret d'un cumul instantané de vos données dans Excel !
Comment insérer des commentaires automatiques et intelligents dans Excel !

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2024 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.