Compter les cellules en fonction du nombre de lettres

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Compter les cellules sur le nombre de caractères

Cette nouvelle astuce Excel démontre comment il est possible, avec la plus grande simplicité, de comptabiliser toutes les cellules comptant exactement le nombre de caractères demandé. Par déclinaison, elle livre le bilan sur toutes les cellules exclues de ce décompte.



Dans l'exemple illustré par la capture, l'utilisateur fournit une indication numérique sous la case intitulée Nb. Car. En fonction de ce chiffre, deux critères se construisent dans les cellules du dessous. Ces contraintes sont alors exploitées par des calculs de dénombrements pour livrer le décompte sur les cellules possédant effectivement ce nombre de lettres et les autres qui diffèrent.

Source de données
Pour focaliser notre attention sur la démonstration de cette nouvelle astuce Excel, nous proposons de récupérer ces éléments déjà structurés.

• Télécharger le fichier compter-sur-nb-caracteres.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Des termes sont énumérés en colonne C. Pour être plus précis, il s'agit de fruits. Ils possèdent tous un nombre bien défini de lettres. L'utilisateur inscrit une information numérique en cellule E4. Ce chiffre doit servir à compter toutes les cellules de la colonne de fruits proposant exactement ce nombre de caractères. En-dessous, respectivement en cellules E7 et E8, les critères à utiliser sont amorcés. L'un et l'autre exploitent la fonction Excel Rept. Cette dernière que nous avons déjà démontrée à l'occasion d'une précédente astuce, permet de répéter un caractère autant de fois que mentionné en second argument de la fonction. Les syntaxes respectives sont les suivantes : =REPT("?";E4) et ="<>" & REPT("?";E4). C'est donc le caractère générique du point d'interrogation qui est répété autant de fois que le stipule l'information numérique saisie en cellule E4. Ce caractère générique ou WildCard représente n'importe quelle lettre. Répété ainsi et utilisé en critère, il permet d'identifier toutes les cellules proposant le même nombre de caractères. Dans le second cas, avec l'opérateur d'inégalité en préfixe (<>), il permet au contraire d'identifier toutes les cellules ne comptant pas ce même nombre de lettres.

Dénombrer sur la quantité de lettres
Pour exploiter ces conditions construites dynamiquement, nous devons utiliser la fonction de dénombrement conditionnel Nb.Si : =Nb.Si(Plage_du_decompte; Critère) .Il est à noter que la colonne des fruits est reconnue sous l'intitulé Fruits. Vous pouvez le constater en déployant la zone Nom en haut à gauche de la feuille Excel. Cet intitulé permettra de simplifier la construction de la formule.

• Sélectionner la première cellule du décompte à livrer, soit F7,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction de dénombrement conditionnel suivie d'une parenthèse, soit : Nb.Si(,
• Désigner la plage d'analyse par son nom, soit : Fruits,
• Taper un point-virgule (;) pour passer dans l'argument du critère à compter,
• Cliquer alors sur la cellule E7,

Grâce à ces caractères génériques au même nombre que la contrainte numérique définie, nous cherchons à comptabiliser tous les textes possédant exactement cette quantité de lettres.

• Fermer la parenthèse de la fonction Nb.Si et valider le calcul par la touche Entrée du clavier,

Avec une syntaxe très simple: =NB.SI(Fruits;E7), nous parvenons à dénombrer toutes les cellules constituées du nombre de caractères défini dynamiquement. Nous le verrons à l'issue, une règle de mise en forme conditionnelle recoupera de façon évidente ces résultats numériques.

Puisque les critères dynamiques sont déjà établis, la formule permettant de compter toutes les cellules ne proposant pas ce nombre de caractères se décline très simplement. Et même au-delà de ces considérations, étant donné que nous exploitons une plage nommée, il n'est pas utile de la construire.

• Sélectionner le précédent résultat en cliquant sur sa cellule F7,
• Tirer la poignée de la cellule sur la ligne du dessous,

La plage de l'analyse reste la même (Fruits). Mais le critère suit le déplacement pour considérer l'inégalité sur les caractères génériques : =NB.SI(Fruits;E8). Les résultats sont parfaitement cohérents dans la mesure où la somme des deux conduit bien au nombre total de fruits recensés dans la colonne C de l'étude. Bien sûr, si vous modifiez la contrainte numérique en cellule E4, les deux résultats dynamiques s'actualisent automatiquement.

Vous l'avez compris, nous aurions très bien pu bâtir des formules uniques sans passer par des calculs intermédiaires. Les syntaxes auraient été les suivantes :

=NB.SI(Fruits; REPT("?";E4)) et =NB.SI(Fruits; "<>" & REPT("?";E4)).

Surligner les concordances
Pour un impact plus évident et pour recouper sans ambiguïté les résultats livrés par les calculs, nous proposons de surligner dynamiquement toutes les cellules dont le contenu est fait du nombre de lettres choisi en cellule E4.

• Sélectionner tous les fruits, soit la plage de cellules C4:C24,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, opter pour l'option Nouvelle règle,
• Dans la boîte de dialogue qui se propose, sélectionner le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone de saisie du dessous pour l'activer,
• Dès lors, taper le symbole égal (=) pour initier la syntaxe de la règle,
• Inscrire la fonction pour compter les caractères d'une cellule suivie d'une parenthèse : NbCar(,
• Désigner le premier des fruits en cliquant sur sa cellule C4, ce qui donne : $C$4,

Nous débutons ainsi l'analyse chronologique de la règle de mise en forme conditionnelle. Mais pour qu'elle puisse se poursuivre sur les cellules du dessous, nous devons totalement libérer la cellule C4.

• Enfoncer trois fois de suite la touche F4 du clavier, ce qui donne : C4,

Ainsi, les dollars figeant la cellule disparaissent. Elle est désormais libre de suivre le mouvement de l'analyse de la règle.

• Fermer la parenthèse de la fonction NbCar,
• Puis, taper le symbole égal (=) pour annoncer la condition à honorer,
• Cliquer sur la cellule E4 de la contrainte numérique à respecter, ce qui donne : $E$4,

Cette fois, nous conservons bien les dollars pour garder la cellule figée. Au gré de l'analyse, Le nombre de caractères de chaque fruit doit être confronté à cette donnée qui ne doit donc pas suivre le déplacement.

Lorsque cette condition est honorée, donc lorsque la cellule en cours d'analyse porte bien le même nombre de lettres que celui mentionné, elle doit ressortir explicitement du lot dans des attributs de format à définir.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Police,
• Avec la seconde liste déroulante, choisir un vert clair pour la couleur du texte,

Il est de bon ton de choisir un vert semblable à celui de la contrainte numérique. Cet indicateur sera ainsi percutant pour l'utilisateur.

• Valider cet attribut de format avec le bouton Ok de la boîte de dialogue,

C'est ainsi que nous retournons sur la première boîte de dialogue. Elle indique explicitement comment doivent ressortir les cellules proposant le même nombre de lettres que celui mentionné par l'utilisateur.

• Valider la création de la règle de mise en forme conditionnelle en cliquant sur le bouton Ok,

De retour sur la feuille, vous notez la cohérence entre le format dynamique ainsi appliqué et le résultat fourni par le premier calcul. Les cellules colorées sont du même nombre que le résultat annoncé.



Et bien entendu, si vous modifiez la contrainte sur le nombre de lettres en cellule E4, en même temps que les calculs de dénombrement s'actualisent, les cellules correspondantes sont automatiquement mises en valeur dans la rangée des fruits. Le repérage des concordances est donc instantané.