Comment compter les cellules selon leur couleur sur Excel

Parfois sur Excel il arrive que l’on souhaite compter les cellules selon leur format, par exemple leur couleur de fond. Voici comment procéder :

 

Tout d’abord, nous allons définir une fonction qui retourne la couleur d’une cellule.

Pour cela, il n’existe pas de formule toute faite dans Excel, alors il faudra faire le travail nous-même (avec 2 lignes de VBA)

Pour définir une fonction, il faut ouvrir l’éditeur VBA (Alt +F11) et créer un nouveau module.

Dans ce module, coller le code suivant:

Function couleur(Cellule As Range)
    Application.Volatile
    Couleur = Cellule.Interior.ColorIndex
End Function

Cette fonction calcule tout simplement la valeur de la couleur (et oui, pour Excel une couleur c’est juste un nombre, quel manque de poésie 🙁 ) Vous pouvez ensuite utiliser cette formule de la même manière que toutes les formules par défaut d’Excel. Pour retourner la valeur de la couleur de n’importe quelle cellule, utilisez simplement

=Couleur(A1)

comment compter les cellules de la même couleur sur Excel?
Compter les cellules d’une couleur donnée

La ligne Application.Volatile s’assure que la fonction se recalcule à chaque fois qu’un calcul est fait dans la feuille. Attention, cela n’inclut pas les modifications de format: si vous changez juste la couleur d’une plage, la fonction couleur ne se mettra pas à jour. Il faudra recalculer la feuille manuellement (raccourci clavier: F9)

Il ne vous reste plus qu’a indiquer le critère de couleur à respecter, puis compter les cellules qui le vérifient avec NB.SI

=NB.SI(C5:C13;couleur(C16))

Par extension, on peut imaginer une fonction qui somme tous les montants par couleur: il vous suffit alors de surligner les différentes catégories à sommer. Utile si on étudie un fichier mal structuré dans lequel on surligne ses points de repères!

Compter les cellules lorsque la couleur est issue d’une mis en forme conditionnelle

Lorsque la couleur de la cellule est définie par une mise en forme conditionnelle, il n’est pas possible d’utiliser cette méthode. La couleur imposée par le format conditionnel fait appel à une autre propriété VBA et se superpose à la couleur de la cellule.

Plutôt que d’écrire des pages et des pages de VBA pour recréer la même fonction qui fonctionnerait avec un format conditionnel, il est plus facile de passer par une étape intermédiaire.

Il suffit pour cela d’expliciter dans une cellule intermédiaire si la condition validant le format conditionnel est vérifiée. Par exemple si votre format conditionnel surligne toutes les valeurs inférieures à 10, il suffit d’indiquer à coté de chaque valeur si elle est inférieure à 10 (le résultat peut donc être VRAI ou FAUX) avec une formule. Il suffit alors de compter le nombre de VRAI.

Réponses populaires

  1. Bonjour @ayanlun88,

    Peux-tu nous fournir un petit fichier représentatif avec les MFCs des 10 conditions.

    @+
    Cordialement.

  2. Hello
    Ce n'est pas possible d'afficher directement la couleur issue d'une MFC.

    Si tu as 10 couleurs, je te conseille de créer une colonne supplémentaire "couleur à afficher" dans laquelle tu mettra une formule pour calculer la couleur à afficher (le résultat sera donc de 1 à 10).

    Tu peux ensuite baser ta mise en forme conditionnelle sur cette colonne, et compter les couleurs en fonction des résultats de cette colonne.

    Par exemple:
    - Créer une MFC qui colorie la ligne en bleu si la colonne "Couleur à afficher" indique 3
    - Pour compter les lignes bleues, tu peux alors compter les lignes qui indiquent 3 dans la colonne "Couleur à afficher"

    Evidemment, c'est dur d'en dire plus sans voir ton fichier :wink:

  3. Salut @DocteurExcel,

    C'est ce que je souhaitais proposer, mais tu le constate par toi même, notre ami ayanlun88 n'a jamais répondu à ma demande de fichier.

    Nous ne connaissons pas les 10 règles pour appliquer une formule.

    Bien cordialement.

Continuez la discussion sur le Forum Formule Excel

2 plus réponses

Participants

Anciens commentaires

Comments are closed.