fbpx

Comment compter les cellules selon leur couleur sur Excel

Feuilles d'automnes de différentes couleurs

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 Excel 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 🙁 ) 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

En Cadeau: Télécharge gratuitement le fichier Excel 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.

About Docteur Excel
Tu souffres sur Excel ? Ça se soigne ! Un traitement de choc pour tous tes problèmes de tableur! Docteur Excel t'apprends à manipuler Excel avec une précision chirurgicale pour gagner un temps fou, éblouir ton patron et devenir un pro du tableur.

Réponses populaires

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

  2. Bonjour @ayanlun88,

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

    @+
    Cordialement.

  3. 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:

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

  5. EMEM says:

    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

Continuez la discussion sur le Forum Formule Excel

4 plus réponses

Participants

Anciens commentaires

39 Comments

  1. VERROUST PASCALE22 janvier 2016 at 13 h 01 min

    j’ai suivi les instructions pour sommer des cellules de couleur et pourtant cela ne marche pas. j’ai bien rajouté ds VBA les lignes par contre la fonction couleur n’est pas reconnue car dés que je tape =couleur(c5) par ex il me sort #NOM?
    j’AI BIEN CELA SI JE FAIS ALT+F11
    Function couleur(Cellule As Range)
    Application.Volatile
    couleur = Cellule.Interior.ColorIndex
    End Function
    ?????
    pourquoi?
    GRD MERCI PAR AVANCE
    Pascale

    • Bonjour Pascale,
      As-tu bien collé le code dans un nouveau module?
      Quand tu ouvres l’éditeur VBA par défaut les seuls objets présents sont ton classeur et les onglets.
      Ce n’est pas ici qu’il faut coller le code.
      Il faut créer un nouveau module (objet contenant la plupart des fonctions et macros) en faisant clic-droit > Insérer > Module
      Un nouvel objet apparaitra appelé « Module1 » par défaut. C’est à cet endroit que tu dois copier le code.

      • grd merci WILL!
        j’ai de nouveau essayé et j’ai compris pq cela ne marche pas ds mon fichier : j’utilise des formats conditionnels ….effectivement si j’utilise une cellule colorée « normalement » cela marche ….si par contre, je mets des couleurs liées à des conditions, cela ne marche pas .. or ds mon fichier, les cellules se colorent différemment en fonction des montants ou en fct des caractéristiques et je veux calculer le nb de cellules par couleur …….

        • C’est intéressant de voir que les formats conditionnels ne sont pas traités de la même manière. Je vais regarder pour voir s’il existe une manière de les inclure également

  2. Ca marche pour une plage verticale (ex: b3:b14) mais pas pour une plage horizontale (ex: b3:j3). Quelqu’un a une idée ? Merci

    • Bonjour Christian
      Cela fonctionne chez moi pour une plage horizontale. As-tu bien utilisé la formule NB.SI sur la ligne contenant les couleurs de chaque cases?
      Compter les cases horizontalement selon leur couleur

      • OK je comprends qu’en fait on ne peut faire une somme que sur un nombre, et le nombre (ici 34 correspondant au gris) est la valeur recherchée dans la ligne 3. Et là ça fonctionne parfaitement.

        Par contre, si on ne mets pas le nombre de la couleur (34) dans la ligne 3, mais qu’on veut compter directement le nombre de cases dont le fond est gris, la formule de « nb.si » ne fonctionne plus, puisque sur la plage de données il n’y a aucun nombre (les cases sont vides).

        Comment faire dans ce cas, sans devoir mettre le chiffre correspondant à la couleur dans un tableau à part ?

  3. Salut, je voit que des commentaires on etait reouvert il ya pas longtemps.

    Ca ne marche pas.

    J’ai pourtant bien suivie toute la procédure mais rien. Impossible d’avoir le nombre de cellules qui sont coloré sous formes conditionnelle et en plus a l’horizontal!
    Je ne suis pas un expert mais la c’est le dernier espoir pour automatiser mon petit tableau.

    Si ya une solution je suis preneur. pour le moment je n’ai pas un max de donner a calculer donc je ferais manuellement.
    D’avance merci

  4. Bonjour, je cherche à faire cette manipulation avec un formatage conditionnel, comment faire ?

    merci d’avance !

    • C’est très complexe à réaliser avec des formats conditionnels. La couleur de fond n’est pas calculée de la même manière. Il est beaucoup plus simple de sommer les cellules validant la formule du format conditionnel. Par exemple si on affiche en rouge les numéros pairs, il faut directement sommer le nombre de cases avec des chiffres pairs, sans faire intervenir la couleur.

      • Oui je m’en suis rendu compte ! J’ai réussis à trouvé une parade pour faire la même chose mais en ayant attribué des valeurs plutôt sur des couleurs, de ce fait pour faire un truc et ensuite des graphs c’est bcp plus simple mais merci quand même !

        • Bonjour, j’ai un soucis pour réaliser une manipulation qui ne correspond pas vraiment à la question de cette page. Voici la situation.

          -La cellule H5 contient un lieu avec une codification. (ex: Nantes_123456_test). (Le contenu de cette cellule changera régulièrement avec un système de tri déjà mis en place.)

          -dans une autre feuille j’ai une liste de codification identique à celles qui peuvent se trouver dans ma cellule H5, H6, H7…… et cette liste contient près de 250 codifications.

          -Toujours dans cette meme feuille, chaque codification correspond à une personne et une meme personne peut avoir plusieurs codification. (ex: en A1″Nantes_123456_test » et en B1″Marc DUPONT » / en A2″Rennes_123456_test » et en B2″Marc DUPONT »)

          -Enfin je voudrais que selon le contenu de la cellule H de ma première feuille cela me renvoie le nom de la personne dans une colonne située adjacente.

          Merci de votre réponse.

  5. Hello Will, j’ai hier été ravie de trouver votre article pour automatiser des calculs dans mon tableau…

    Cependant en l’ouvrant ce matin badaboum, plus rien. En effet il semblerait que le format que j’avais utilisé ne supportait pas le module à la sauvegarde?
    Quel format faudrait-il utiliser?
    Et plus embêtant encore, j’ai essayé de refaire la manipulation et ma fonction « couleur » ne me renvoie plus que des 0 ! et pas les nombres correspondants aux couleurs…

    Que se passe-t-il?

    • Hello, attention il faut bien sauvegarder le module dans le fichier concerné (attention si tu as plusieurs fichiers ouverts)

  6. Bonjour,
    Je cherche une solution pour compter le nombre de cellules coloriées PAR LIGNE, le coloriage conditionnel résulte d’une recherche des doublons par ligne. Je ne peux pas donc appliquer dans ce cas votre conseil d’ajouter une vérification de la condition… que faire ,
    Merci!

  7. Bonjour,

    Est-ce possible d’utiliser une formule similaire à « =NB.SI(C5:C13;couleur(C16)) » sans VBA ou macro?

    Le but est de signaler si dans un tableau il y a une case rouge (qui est mise grâce à une mise en forme conditionnelle). Dans mon entreprise, on ne peut pas utiliser VBA ou macro.

    Merci d’avance.

  8. salut a tous 🙂

    j’ai fais plusieurs test avec cette fonction ca ne marche pas si possible de nous faire un tutorial pour le bien explique merci

  9. ANTOINE DERBOIS22 novembre 2016 at 20 h 06 min

    Salut, question:

    J »ai une colonne avec des résultats positifs. J’aimerais que lorsque les cases sont d’une certaine couleur alors le résultat indiqué dans la case soit comptabilisé dans mon total.

    J’utilise pour cela la formule suivante:

    =SOMME.SI(O3:O271;couleur(G276))
    où couleur(G276) est ma couleur sine qua none.

    Pourtant, le résultat est nul … Une idée de ce qui ne va pas ?

    Merci

  10. Bonjour, il me semble que la fonction couleur( ) ne prend pas un compte toutes les nuances de couleurs mais uniquement 64 couleurs standards. Si j’éclaircis légèrement une couleur, son numéro ne change pas (même après avoir recalculé la feuille).

  11. Bonjour Will,
    Merci pour cet article, cependant j’ai un petit soucis tu t’en doutes..
    Lors de calcul avec =NB.SI(C5:C13;couleur(C16)) j’obtiens comme réponse zéro, aurai-tu une idée ou j’ai fait fausse route !? Par ailleurs la fonction =Couleur (A1) marche comme prévu

    Merci de ta réponse,

  12. bonsoir,

    quant à moi, j’aimerais compter les cellules de toute une colonne mais elles comportent des couleurs différentes …. d’après ce que je viens de lire … j’ai du mal à m’y retrouver car j’ai l’impression que vous ne comptez qu’une seule couleur.

    un fortiche d’Excel pourrait m’aider avec une unique formule ? 😉

    merciii

    adam

Comments are closed.