Comment compter les cellules selon leur couleur sur Excel

Publié initialement sur: https://www.formuleexcel.com/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 :

En Cadeau: Télécharge gratuitement le fichier prêt à l’emploi

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

[caption id=« attachment_314 » align=« alignnone » width=« 454 »]comment compter les cellules de la même couleur sur Excel? Compter les cellules d’une couleur donnée[/caption]

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

En Cadeau: Télécharge gratuitement le fichier prêt à l’emploi

=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.

1 « J'aime »

Bonsoir, Docteur Excel,

J’aurais besoin de votre aide sur la 2ème partie de votre article, les couleurs sous la mise en forme conditionnelle.
Je pense pas que je puisse utiliser le vrai et le faux, car ma mise en forme conditionnelle à 10 règles. Donc 10 couleurs différentes. Commennt puis-je compter combien j’ai de cellule de la même couleur sachant que j’ai 10 couleurs et elles ont été mises en MFC?

Merci d’avance pour votre aide,

Cordialement,

ayanlun88

Bonjour @ayanlun88,

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

@+
Cordialement.

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:

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.

Bonjour,
Merci pour l’info.
Pour moi, certaines couleurs renvoient le même code. Il vaut donc mieux vérifier les couleurs utilisées avant.
J’aimerai trouver plus d’info sur la propriété ColorIndex afin de contrôler ce désagrément.
Bien cordialement,

Bonjour,

Désolée mais je suis une débutante et je ne sais pas ouvrir VBA et créer un module. J’ai un mac, est-ce que je dois bien faire outil-macro-Visual Basic Editor ? Si oui, ensuite comment créer un module?

Merci pour ton aide

Bonjour, merci pour cet article! La fonction couleur(cellule) fonctionne bien. En revanche, la formule NB.SI ne fonctionne pas avec. Par exemple : =NB.SI(X11;couleur(X11)) me remonte 0.Merci pour vote aide! bonne journée. Aurélia

Bonjour docteur excel,
Le lien ne marche plus pour le fichier complet car je n’arrive pas à le faire seul en utilisant la feuille de code et le retour de valeur…
Pourriez vous le relinker svp ?
Cordialement,