Compter les lignes de la valeur cherchée avec Excel

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 lignes d'une valeur cherchée

L'objectif de cette nouvelle astuce Excel n'est pas de compter le nombre de fois qu'une valeur est répétée dans un tableau. Cette démarche serait triviale grâce à la fonction Nb.Si. Son objectif est de montrer comment compter le nombre de lignes sur lesquelles cette valeur est présente, même si elle est répétée à plusieurs reprises dans une même rangée.



Dans l'exemple illustré par la capture, des nombres avec répétitions sont présentés dans un tableau constitué de trois colonnes. L'utilisateur mentionne la valeur cherchée dans une première case sur la droite de ce tableau. Un premier calcul répond par le nombre total de répétitions trouvées dans une deuxième case. Puis dans une troisième case, un second calcul renseigne sur le nombre de lignes portant cette valeur cherchée.

Classeur source
Pour commettre cette astuce, nous proposons d'appuyer notre étude sur un classeur offrant ces nombres avec répétitions.

• Télécharger le classeur compter-lignes.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é,

La valeur cherchée doit donc être inscrite en cellule F4. Pour l'instant, il s'agit du nombre 25. Le nombre total de répétitions doit être calculé en cellule F7. Enfin, le nombre total de lignes portant la valeur cherchée doit être fourni en cellule F10.

Compter les répétitions
Comme nous l'avons évoqué précédemment, compter les répétitions d'une valeur cherchée dans un tableau est un jeu d'enfant grâce à la fonction de dénombrement conditionnel Nb.Si. Si vous déployez la zone Nom en haut à gauche de la feuille Excel, vous constatez que le tableau des nombres est identifié sous l'intitulé Serie. Ce nom va s'avérer fort précieux pour la construction des formules.

• En cellule F7, construire la formule suivante : =NB.SI(Serie;F4) et la valider par Entrée,

Très simplement, nous comptabilisons le nombre de fois que la valeur inscrite en cellule F4 est trouvée dans le tableau nommé Serie.



Bien sûr, si vous changez la valeur cherchée en cellule F4, le résultat du calcul s'actualise automatiquement.

Compter les lignes de la valeur trouvée
Pour réaliser la prouesse consistant à dénombrer les lignes portant la valeur cherchée, un raisonnement matriciel est nécessaire. Tout d'abord, la fonction ProduitMat s'impose. Il s'agit de l'abréviation de Produit Matrice. Elle réalise la multiplication de deux matrices sous forme de critères. Nous le verrons la fonction Transpose est elle-aussi essentielle. Elle permet de retourner un tableau. Le tout doit être englobé dans la fonction Somme pour additionner toutes les lignes concordantes trouvées.

• Cliquer sur la cellule F10 pour la sélectionner,
• Taper le symbole égal (=) pour démarrer la formule,
• Inscrire la fonction d'addition suivie d'une parenthèse, soit : Somme(,
• Ouvrir une nouvelle parenthèse pour entreprendre une factorisation,
• Inscrire la fonction de multiplication matricielle suivie d'une parenthèse, soit : ProduitMat(,
• Ouvrir une nouvelle parenthèse pour entreprendre une autre factorisation,
• Désigner le tableau des nombres par son nom, soit : Serie,
• Taper le symbole égal (=) pour annoncer le critère à honorer,
• Puis, désigner la valeur cherchée en cliquant sur sa cellule F4,
• Fermer alors la parenthèse de la factorisation,
• Multiplier ensuite ce critère par le chiffre 1, soit : *1,

Explications de ce calcul, (Serie=F4)*1 :

La recherche matricielle de correspondances sur la plage nommée va générer des résultats booléens (Vrai / Faux) pour chaque valeur dans les données. Ces indicateurs sont donc agencés sous forme de matrice. La multiplication par 1 (*1) permet de transformer les valeurs booléennes Vrai et Faux en valeurs numériques 1 et 0. Il en résulte un tableau (matrice) de chiffres.

• Taper un point-virgule (;) pour passer dans l'argument suivant de la fonction ProduitMat,

Il s'agit de l'argument de la seconde matrice à multiplier avec celle des chiffres que nous venons de construire.

• Inscrire la fonction pour retourner un tableau, suivie d'une parenthèse, soit : Transpose(,
• Inscrire la fonction donnant l'indice de colonne suivie d'une parenthèse, soit : Colonne(,
• Désigner de nouveau le tableau des nombres par son nom, soit : Serie,
• Fermer la parenthèse de la fonction Colonne,
• Fermer la parenthèse de la fonction Transpose,
• Fermer la parenthèse de la fonction ProduitMat,
• Puis, taper l'inégalité suivante : >0, pour extraire uniquement les concordances,

Explications du calcul TRANSPOSE(COLONNE(Serie)) :

Colonne(Serie) permet de travailler sur des matrices de mêmes hauteurs. Les positions des concordances y sont repérées. En d'autres termes, la fonction Colonne est utilisée simplement pour des raisons de commodité pour générer un tableau numérique de la bonne taille. Transpose transforme ce résultat en lignes pour observer le but cherché, celui du repérage des concordances en lignes, notamment grâce à l'opérateur d'inégalité qui suit.

• Fermer la parenthèse de la première factorisation,
• Transformer les booléens en chiffres par multiplication, soit : *1,

Désormais, puisque les booléens sont forcés en chiffres (*1), il doit donc en résulter les emplacements des lignes portant la valeur cherchée.

• Fermer la parenthèse de la fonction Somme,
• Enfin, valider nécessairement la formule matricielle par le raccourci CTRL + MAJ + Entrée,

Pour le nombre 27 par exemple, le premier calcul le repère à sept reprises. Le second indique qu'il n'est présent que sur cinq lignes. Donc certaines d'entre elles le portent à plusieurs reprises. Et il sera d'autant plus simple de confirmer ces résultats lorsque nous aurons mis en place une judicieuse mise en forme conditionnelle. La syntaxe du calcul matriciel que nous avons construit est la suivante :

{=SOMME((PRODUITMAT((Serie=F4)*1; TRANSPOSE(COLONNE(Serie)))>0)*1)}

Et bien entendu, si vous changez la valeur cherchée, les deux résultats s'actualisent parfaitement.

Repérer les lignes de la valeur cherchée
Désormais, il est temps de bâtir une règle de mise en forme conditionnelle destinée à surligner dynamiquement l'intégralité de la ligne où la valeur a été repérée à une ou plusieurs reprises. L'astuce consiste à mettre en place un raisonnement matriciel assimilé, à l'aide de la fonction SommeProd. Il suffit de recouper le critère sur la valeur cherchée avec la matrice des nombres. Chaque présence sera repérée par un booléen à convertir en chiffre. Ces chiffres seront ensuite naturellement additionnés par la fonction SommeProd.

• Sélectionner tous les nombres du tableau, soit la plage de cellules B4:D13,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir la commande Nouvelle règle,
• Dans la boîte de dialogue qui suit, 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 de mise en forme,
• Inscrire la fonction matricielle suivie d'une parenthèse, soit : SommeProd(,
• Ouvrir deux nouvelles parenthèses pour la factorisation à entreprendre,
• Sélectionner la première ligne de nombres, soit la plage de cellules $B$4:$D$4,
• Enfoncer deux fois de suite la touche F4 du clavier, ce qui donne : $B4:$D4,

Vous le savez, l'analyse d'une mise en forme conditionnelle est chronologique. La présence de la valeur cherchée doit être scrutée dans chaque ligne, tour à tour. C'est la raison pour laquelle nous libérons les indices de ligne et conservons figés les indices de colonne. De cette manière, dès lors que la valeur est repérée, c'est l'intégralité de la ligne qui est concernée pour subir les attributs dynamiques de format.

• Fermer la parenthèse de cette matrice,
• Taper le symbole égal (=) pour annoncer le critère à vérifier,
• Désigner la valeur cherchée en cliquant sur sa cellule F4, ce qui donne : $F$4,
• Fermer alors la parenthèse de la factorisation,

A ce stade, toutes les positions pour la valeur trouvée sont repérées par un booléen Vrai et Faux le cas échéant.

• Forcer la conversion de ces booléens en chiffres, soit : *1,
• Fermer la parenthèse de la fonction SommeProd,
• Puis, taper le critère d'inégalité suivant : >0,

Les chiffres vont être additionnés naturellement par la fonction SommeProd. Si le résultat est positif, nous savons que la valeur cherchée appartient à la ligne en cours d'analyse par la règle de mise en forme conditionnelle. Dans ces conditions, nous devons la faire ressortir dans des attributs de format différents.

• Pour cela, 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 assez clair pour la couleur du texte,
• Valider ces attributs de format en cliquant sur le bouton Ok,
• De retour sur la première boîte de dialogue, valider la création de la règle par le bouton Ok,

Aussitôt, les lignes portant la valeur cherchée sont effectivement mises en lumière. Il devient donc évident de recouper ces indicateurs avec le résultat fourni par la formule matricielle.



Et bien entendu, si vous modifiez la valeur cherchée, en même temps que les résultats de calculs s'actualisent, les jeux de couleurs s'adaptent pour repérer les lignes identifiées. La syntaxe complète de la règle de mise en forme conditionnelle que nous avons construite est la suivante :

=SommeProd((($B4:$D4)=$F$4)*1)>0

Certes, nous sommes restés dans un raisonnement matriciel. Mais pour une mise en oeuvre encore plus simple de cette règle, nous aurions tout aussi bien pu exploiter la fonction de dénombrement Nb.SI :

=Nb.Si($B4:$D4;$F$4)>0