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.
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 :
Exemple :
=SOMME_SI_COULEURFOND($B$2:$B$5;65535)
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 :
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 :
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).
Derniers articles parBenoît RIVIERE (voir tous)
- Interpréteur de formules de calcul en Python - dimanche 13 octobre 2024
- Les données de la facturation électronique - mercredi 9 octobre 2024
- VBA/SQL vs Power Query : deux solutions complémentaires - mercredi 2 octobre 2024
- L’IA dans les cabinets comptables : cas concrets - jeudi 26 septembre 2024
- EXCEL : insérer une image ou un logo dans une cellule - lundi 16 septembre 2024
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 .
Bonjour j’ai le même soucis que Julien quelqu’un aurait la solution ?
Bonjour,
Est-ce que ca se met à jour en appuyant sur F9 (raccourci pour calculer le classeur). Si oui, c’est que le calcul automatique est désactivé.
Dans le bandeau Formules, dans “option de Calcul”, passer en “automatique” ?
Appuyer sur F9 pour actualiser