Compter les cellules de couleur sans VBA 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 cellules de couleur

Grâce à VBA Excel, nous avions créé une fonction permettant notamment de compter les cellules en couleur sur une plage désignée. Avec cette nouvelle astuce Excel, nous allons voir comment il est possible de réaliser le même décompte conditionnel, sans l'appui du code VBA.



Sur l'exemple illustré par la capture, nous travaillons sur un tableau résumant les résultats obtenus par les commerciaux d'une entreprise. Une mise en forme conditionnelle est en place pour repérer automatiquement en vert les bons résultats. Les moins bons sont quant à eux repérés dynamiquement en orange. Des flèches en entête permettent d'engager des filtres notamment sur ces couleurs conditionnelles. Et une fois les scores ainsi isolés, un calcul de synthèse, placé en haut à droite du tableau, compte toutes les lignes de la couleur filtrée.

Classeur source
Pour la mise en place de cette nouvelle astuce, nous suggérons de récupérer ce tableau des résultats.

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

Nous découvrons donc ce tableau des résultats avec ses jeux de couleurs déjà définis. Deux règles de mise en forme conditionnelle existent : =$E6<=$F6 et =$E6>=$F6. Vous pouvez le constater en sélectionnant l'une des cellules du tableau, en cliquant sur le bouton Mise en forme conditionnelle dans la section Styles du ruban Accueil et en cliquant sur l'option Gérer les règles. Toutes deux comparent le chiffre réalisé par le commercial à l'objectif qui lui a été fixé. Lorsque l'objectif n'est pas atteint, la ligne du commercial est surlignée avec un rouge qui tire vers l'orange. Lorsqu'il est atteint, la symbolique est positive avec une couleur de surlignement verte. Ce sont ces couleurs que nous souhaitons compter pour offrir une synthèse des résultats sans équivoque.

Filtrer par couleur
Excel offre un outil puissant permettant de filtrer les données d'un tableau sur des critères multiples.

Et parmi les leviers disponibles, il est possible d'isoler les informations d'une même couleur. C'est ce que nous proposons de découvrir.

• Cliquer dans l'une des cellules du tableau, par exemple C7, pour le désigner,
• En haut de la fenêtre Excel, cliquer sur l'onglet Données pour activer son ruban,
• Dans la section Trier et filtrer du ruban, cliquer sur le bouton Filtrer,

Instantanément, les flèches de filtres apparaissent en entête du tableau dans chaque case de titre.

• Cliquer sur n'importe quelle flèche pour déployer ses propositions,
• Dans la liste qui se suggère, pointer sur la commande Filtrer par couleur,
• Dans le sous menu qui s'affiche, cliquer sur la vignette de la couleur Orange,

Instantanément, toutes les lignes des bons résultats, identifiés par la couleur verte, sont masquées. Seules subsistent les lignes orange, celles des résultats non satisfaisants.

Compter les lignes restantes
Désormais, sur ces filtres, l'astuce consiste à compter les lignes visibles. Nous en déduirons tous les commerciaux repérés par un même jeu de couleur. Mais, cette addition ne peut s'opérer avec une fonction classique. Cette dernière, appliquée sur la hauteur du tableau, inclurait dans son calcul toutes les lignes masquées par l'utilisateur. A l'occasion d'une formation spécifique, nous avions appris comment réaliser des calculs sur les tableaux filtrés. La technique consiste à employer la fonction Excel Sous.Total. En même temps qu'elle peut faire varier la nature de son calcul, elle a la capacité d'ignorer les cellules masquées.

• Cliquer sur la flèche du filtre précédent,
• Dans les propositions, choisir la commande Effacer le filtre de...,
• En haut à droite du tableau Excel, cliquer sur la cellule G3,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction de calcul sur des filtres, suivie d'une parenthèse, soit : Sous.Total(,

Une info-bulle apparaît offrant un large choix de calculs.

• Double cliquer sur la proposition de la fonction NbVal,

Comme vous pouvez le voir, c'est sa valeur numérique (3) associée qui est ainsi inscrite en premier argument de la fonction Sous.Total.

• Taper un point-virgule (;) pour passer dans le deuxième argument de la fonction Sous.Total,
• Sélectionner l'une des colonnes du tableau, par exemple la plage B6:B29,

En effet, la colonne importe peu. Avec la fonction NbVal stipulée en premier paramètre, nous engageons un décompte des cellules non vides. Et avec la fonction Sous.Total, ce décompte est engagé uniquement sur les lignes visibles.

• Fermer la parenthèse de la fonction Sous.Total,
• Puis, valider la formule avec la touche Entrée du clavier,

Comme aucun filtre n'est appliqué pour l'instant, le calcul (=SOUS.TOTAL(3;B6:B29)) conduit à un résultat de 24 cellules non vides correspondant aux 24 lignes d'origine du tableau. Mais désormais, si vous appliquez un filtre par couleur sur n'importe quelle colonne, le calcul s'actualise automatiquement pour dénombrer les lignes appartenant uniquement à la couleur choisie.