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)
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.
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:
Evidemment, c’est dur d’en dire plus sans voir ton fichier
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,
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