Excel : effectuer des calculs conditionnels en fonction de la couleur des cellules

EXCEL offre un large panel de fonctions évoluées ; parmi celles-ci figurent les fonctions de calcul conditionnel à l’image des fonctions SOMME.SI, NB.SI, SOMME.SI.ENS et NB.SI.ENS. Les calculs conditionnels permettent d’effectuer des calculs en cas de réalisation d’une ou plusieurs conditions.

Malheureusement, à l’heure actuelle, Excel ne dispose d’aucune fonction conditionnelle basée sur la couleur des données.

SOMME.SI.ENS couleur Résultat

Cet article se propose de remédier à cette omission par la création d’une fonction Excel, la fonction SOMME_SI_COULEURFOND(plage ; codecouleur).

Cette fonction requiert deux paramètres :

  • Plage : plage de cellules à additionner
  • Codecouleur : code couleur servant de critère

Echantillon de données :

SOMME_SI_COULEURFONDS Données

Exemple :

=SOMME_SI_COULEURFOND($B$2:$B$5;65535)

Résultat :

SOMME_SI_COULEURFOND Résultat

Explications :

La formule de calcul =SOMME_SI_COULEURFOND($B$2:$B$5;65535) additionne tous les montants (soit 10 +50 + 100 = 160) dont le fond de la cellule est jaune (code couleur = 65535).

Pour connaître le code couleur du fond d’une cellule, il suffit d’utiliser la fonction CODE_COULEUR FOND, fonction également à créer.

La fonction CODE_COULEURFOND(Cellule) renvoie le code couleur du fond d’une cellule.

Exemple :

CODE_COULEURFOND Résultat

La formule =CODE_COULEURFOND(M2) retourne 5287936 soit le code de la couleur verte. Pour la couleur jaune située en cellule M5, cette même formule renvoie 65535. C’est ce code qui doit être utilisé avec la fonction SOMME_SI_COULEURFOND.

Pour effectuer des calculs multicritères avec SOMME.SI.ENS en tenant compte entre autres des couleurs, il suffit d’ajouter aux données, une colonne (ici colonne C) contenant le code couleur (de la colonne B) calculé à l’aide de la fonction CODE_COULEURFOND :

SOMME.SI.ENS couleur Données

Ainsi, pour calculer la somme des valeurs de la colonne B dont la couleur est jaune (code 65535) et dont le montant est supérieur à 20, la formule de calcul à rédiger est :

=SOMME.SI.ENS($B$16:$B$22;$C$16:$C$22;65535;$B$16:$B$22;”>20″)

Cette formule retournera 150 soit 50 + 100.

Le classeur Excel comprenant les deux fonctions SOMME_SI_COULEURFOND et CODE_COULEURFOND est téléchargeable ci-après.

Le code source VBA des deux fonctions SOMME_SI_COULEURFOND et CODE_COULEURFOND :

'Fonction SOMME_SI_COULEURFOND
'
'Plus d'infos : https://www.auditsi.eu/?p=11383
 
 
Option Explicit
 
 
Function SOMME_SI_COULEURFOND(Plage As Range, CodeCouleur As Long) As Double
    Dim Total As Double
    Dim Cellule As Range
    
    Total = 0

    For Each Cellule In Plage
        If Cellule.Interior.Color = CodeCouleur Then
            Total = Total + Cellule.Value
        End If
    Next
    
    SOMME_SI_COULEURFOND = Total
End Function


Function CODE_COULEURFOND(Cellule As Range) As Long
    CODE_COULEURFOND = Cellule.Interior.Color
End Function

La fonction SOMME_SI_COULEURFOND comprend une boucle compteur For Each Cellule In Plage… Next (c’est-à-dire : pour chaque Cellule comprise dans la plage…) qui scrute chaque cellule et additionne son contenu (Total = Total + Cellule.Value) dès lors que la couleur correspond (If Cellule.Interior.Color = CodeCouleur).

La fonction CODE_COULEURFOND est encore plus simple. Elle se contente de lire le code couleur et de le retourner (CODE_COULEURFOND = Cellule.Interior.Color).

Plus d’informations sur la création de fonctions Excel : formation analyse de données et automatisation (6ème volet).

Volet 6 VBA fonction

Share Button
Fonction SOMME SI COULEURFOND (VBA)
Fonction SOMME SI COULEURFOND (VBA)
Fonction-SOMME_SI_COULEURFOND-VBA.xlsm
17 KiB
952 téléchargements
Détails...
The following two tabs change content below.
Après seize années passées en cabinet d’expertise-comptable et de commissariat aux comptes (où j’ai exercé comme expert-comptable et chef de mission audit), j’ai pris le poste de directeur comptable d’un groupe de distribution automobile en novembre 2014. Au cours de ma carrière, j’ai acquis une expérience significative en audit et en exploitation des systèmes d’information (analyse de données, automatisation des tâches, programmation informatique) au service de la production des comptes annuels et consolidés. C’est cette expérience personnelle et ma passion pour l’informatique que je partage sur ce blog. Mon CV / Réalisations personnelles et projets informatiques / Ma collection / Me contacter

2 commentaires

  1. bonjour, j’ai créer la fonction SOMME_SI_COULEURFOND comme indiqué, par contre elle ne s’actualise pas automatiquement, c’est a dire que si je change la couleur de fond le résultat ne change pas, ou si je rajoute une cellule avec la couleur de fond le résultat ne change pas .

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.