Comment réaliser une somme des cellules en fonction de leur couleur sur Excel ? La fonction SOMMESICOULEUR()

Dans ce tutoriel, je vais vous montrer comment réaliser la somme des valeurs des cellules en fonction de leur couleur de fond.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

Partie 1 : Comment créer une SOMME en fonction de la couleur de fond des cellules sur Excel ?

Partie 2 : Comment calculer toutes sortes de calculs (NB, MOYENNE, MIN, MAX...) en fonction de la couleur de fond des cellules sur Excel ?

1. Introduction

Comme nous venons de le voir dans l’introduction de la vidéo, nous allons voir comment calculer la somme des cellules en fonction de la couleur qui est affectée à leur fond :

Malheureusement, Excel ne propose pas de fonction qui pourrait nous permettre de réaliser cette opération directement.

Et à moi d’avoir le courage d’effectuer ces calculs à la main, nous allons devoir créer une fonction personnalisée en VBA :

2. Comment créer une fonction personnalisée en VBA ?

Et pour cela, la première étape va consister à lancer VBE (Visual Basic Editor), qui est une petite application intégrée avec chaque application de la suite Office afin de pouvoir créer des macro-commandes ou des fonctions personnalisées créées en VBA.

La méthode la plus rapide pour lancer VBE consiste à utiliser le raccourci clavier [Alt]+[F11] ;

Une fois VBE lancé, directement dans le projet en cours, nous pouvons insérer un nouveau module en nous rendant dans le menu Insertion > Module :

Un module est feuille de saisie de code.

Ensuite, pour créer une fonction personnalisée, nous utilisons le mot clé Function, suivi du nom que nous souhaitons donner à cette fonction, par exemple SOMMECOULEUR :

Function SOMMECOULEUR() 
 
End Function 

Lorsque nous validons la création de la fonction, en appuyant sur la touche [Entrée] du clavier, nous pouvons constater deux choses :

• Tout d’abord, VBE ajoute automatiquement des parenthèses vides, qui nous permettront comme nous le verrons juste après de saisir un ou plusieurs arguments à notre fonction
• Ensuite, VBE a également ajouté la ligne End Function pour marquer la fin de la fonction. Toutes les lignes de code que nous saisirons entre ces deux lignes seront ainsi exécutées à chaque fois que nous lancerons la fonction

Comme nous venons de le voir, nous pouvons insérer un argument à la fonction en le saisissant entre parenthèses.

3. La fonction SOMMESICOULEUR() : effectuer une somme selon la couleur de fond des cellules

Nous allons justement avoir besoin d’un argument pour préciser à la fonction sur quelles cellules nous allons souhaiter effectuer cette extraction :

Function SOMMECOULEUR(cellules As Range)
 
End Function 

Nous créons cet argument nommé cellules (au pluriel) que nous typons en tant que Range, c’est-à-dire que celui-ci va permettre de gérer des plages de cellules.

Ensuite, pour obtenir la couleur de comparaison qui va nous servir de critère pour sélectionner les cellules, nous allons nous reposer sur la couleur de la cellule active.

En VBA, pour obtenir l’objet Range correspond à la cellule qui a appelé la fonction, nous pouvons utiliser l’objet Application.Caller.

Une fois que nous avons appelé cet objet, nous pouvons récupérer la couleur de fond en appelant la propriété Interior.Color.

Par exemple, pour que la couleur nous retourne la couleur de la cellule, nous utiliserions le code :

Function SOMMECOULEUR(cellules As Range)
    SOMMECOULEUR = Application.Caller.Interior.Color
End Function 

Maintenant, le but de la fonction va être de passer en revue toutes les cellules de la plage cellules afin d’additionner les valeurs de toutes les cellules pour lesquelles la couleur de fond est la même que celle de la cellule Application.Caller.

Pour cela, nous commençons par créer une variable que nous appelons total et que nous typons en tant que Double, c’est-à-dire comme un grand nombre décimal :

    dim total as Double

Puis, pour passer toutes les cellules données en argument en revue, nous allons utiliser une autre variable que nous appelons cellule (au singulier cette fois-ci) et une boucle For Each

    Dim cellule As Range
    
    For Each cellule In cellules
    Next

Il ne reste plus qu’à comparer la couleur de fond de la cellule identifié par la variable cellule et celle de la cellule récupérée par Application.Caller, et lorsque ces deux couleurs sont égales, alors nous pourrons ajouter le résultat correspond à la variable total :

        If Application.Caller.Interior.Color  = cellule.Interior.Color Then
            total = total + cellule
        endif

Et enfin, pour terminer notre fonction, nous retournons à la fonction la valeur de la variable total :

    SOMMECOULEUR = total

Et voilà, notre fonction personnalisée est maintenant tout à fait fonctionnelle.

4. Comment rendre dynamique une fonction personnalisée ?

Par contre, si nous changeons la couleur de fond de l’une des cellules, nous pourrons constater que le résultat restera inchangé :

Pour corriger ce problème, nous devrons simplement rendre la fonction dynamique en ajoutant l’instruction Application.Volatile au tout début de la fonction :

Function SOMMECOULEUR(cellules As Range)
    Application.Volatile
    …
End Function 

Il suffit maintenant de revenir sur la feuille de calcul, et d’appuyer sur la touche [F9] pour actualiser le résultat de la fonction :

5. Comment effectuer des calculs en fonction de leur couleur sur Excel ? (SOMME, MOYENNE, NB, MAX, MIN…)

Maintenant que nous avons découvert une méthode permettant de calculer la somme de plusieurs cellules, en fonction de leur couleur de fond, voyons comment améliorer cette la fonction personnalisée, afin de pouvoir réaliser toutes sortes de calculs comme un calcul de moyenne, le dénombrement des cellules de même couleur, la détermination de la valeur maximale ou minimale…

Et pour cela, nous allons pouvoir nous reposer sur l’utilisation d’une fonction Excel peu utilisée, mais que nous avions déjà découverte dans un tutoriel précédent : la fonction SOUS.TOTAL().

   5.1. Obtenir la plage des cellules correspondantes

Pour rappel, la fonction SOUS.TOTAL() attend deux arguments :

=SOUS.TOTAL(no_fonction;réf1;…)

Où :

• No_fonction : correspond au type d’opération à mettre en place dans le sous-total :

• Réf1, réf2,… il s’agit ici de venir saisir les références à inclure dans le calcul du sous-total. Il est possible de renseigner entre 1 et 254 références.

Les fonctions sont réunies dans deux groupes :

• De 1 à 11
• Et de 101 à 111, la différence avec le premier groupe c’est qu’ici le calcul ignore les cellules masquées (attention, les cellules filtrées par un filtre classique seront toujours exclues)

Dans notre exemple, pour récupérer le montant des ventes réalisées par magasin, nous allons vouloir utiliser la fonction SOMME.

La première chose à faire va donc consister à obtenir une variable de type Range dans laquelle nous allons venir stocker la plage de toutes les cellules pour lesquelles la couleur de fond correspond à celle de la cellule appelante.

Nous allons donc partir de la fonction SOMMESICOULEUR que nous avions développée dans la partie précédente.

Nous allons commencer par remplacer la variable total que nous avions typée en tant Double par une variable que nous appellerons plage et que nous typerons logiquement en tant que Range.

La ligne suivante :

    Dim total As Double 

Devient ainsi :

    Dim plage As Range 

La seule difficulté de ce tutoriel sera donc d’alimenter cette variable plage en venant y intégrer toutes les cellules correspondantes au critère.

Pour cela, nous allons pouvoir utiliser la fonction VBA UNION() qui permet comme nous l’avions vu dans un tutoriel précédent de combiner deux plages de cellules pour n’en former qu’une seule.

Nous modifions donc l’intérieur de la boucle :

    For Each cellule In cellules
        If cellule.Interior.Color =  Application.Caller.Interior.Color Then
            total = total + cellule
        End If
    Next

Par :

    For Each cellule In cellules
        If cellule.Interior.Color =  Application.Caller.Interior.Color Then
            Set plage = Union(plage, cellule)
        End If
    Next

Ce qui nous permet d’ajouter la plage cellule à la suite de la plage plage.

Ici, nous devons être vigilants sur deux points :

• En effet, étant donné qu’ici nous manipulons un objet, et non plus une simple variable, nous devons utiliser le mot clé Set pour redéfinir l’objet plage
• Ensuite, il faut savoir que pour utiliser la fonction Union, il est nécessaire que les deux plages contiennent au moins une cellule sous peine de générer une erreur.

Nous devons donc effectuer un test avant de pouvoir utiliser la fonction Union comme nous venons de le faire.

Pour cela, nous regardons si l’objet plage est égal à Nothing, afin de pouvoir lui affecter directement la cellule cellule, et dans le cas contraire, nous pourrons utiliser la fonction UNION() comme nous venons de le voir :

            If plage Is Nothing Then
                Set plage = cellule
            Else
                Set plage = Union(plage,  cellule)
            End If

   5.2. Utiliser la fonction SOUS.TOTAL en VBA

Maintenant que nous disposons de la plage des cellules pour lesquelles nous souhaitons réaliser le calcul, il va nous rester à l’utiliser en tant que paramètre de la fonction SOUS.TOTAL().

Attention toutefois, cette fonction SOUS.TOTAL() est une fonction Excel, or ici nous sommes en VBA.

Mais pas de problème, pour utiliser une fonction Excel dans VBA, il suffit de l’appeler en tant que sous objet de Application.WorksheetFunction, dans le langage de VBA, c’est-à-dire en anglais (soit Subtotal) !

    SOMMESICOULEUR = Application.WorksheetFunction.Subtotal(9,  plage)

Ce qui nous permet de retourner à la fonction SOMMESICOULEUR() la somme des cellules de la même couleur que celle de la cellule appelante.

   5.3. Choix de la fonction à utiliser

Il ne reste maintenant plus qu’à laisser à l’utilisateur le choix de pouvoir choisir la fonction à utiliser pour réaliser son calcul.

Pour cela, nous allons ajouter un paramètre Integer à la fonction, que nous appellerons fonction :

Function SOMMESICOULEUR(cellules As Range,  fonction as Integer)

Puis nous utilisons ce paramètre dans la fonction Subtotal :

    SOMMESICOULEUR = Application.WorksheetFunction.Subtotal(fonction,  plage)

Il suffira donc de préciser en tant que second paramètre de la fonction SOMMESICOULEUR() la fonction à utiliser.

Ainsi, pour obtenir la valeur maximale, nous saisirons :

=SOMMESICOULEUR(B7:F22;4)

Par contre, ce fonctionnement suppose que l’utilisateur connaisse tous les codes par cœur, en sachant qu’il n’est pas possible de les afficher directement en tant que suggestion lors de la saisie de la fonction…

Pour simplifier la rédaction de la fonction, nous préfèrerons que celui-ci puisse saisir directement le nom de la fonction.

Pour cela, nous allons commencer par modifier le type de l’argument fonction, que nous passons en String :

Function SOMMESICOULEUR(cellules As Range,  fonction As String)

Et ensuite, nous allons créer un tableau de variables dans lequel nous venons saisir les fonctions dans l’ordre présenté dans la fonction SOUS.TOTAL() :

    Dim listeFonctions() As Variant
    listeFonctions = Array("NB",  "NBVAL", "MAX", "MIN", "PRODUIT",  "ECARTYPE", "ECARTYPEP", "SOMME",  "VAR", "VAR.P")

Et enfin, pour obtenir la position de l’argument saisi au sein de cette liste et donc convertir l’argument saisi en toutes lettres en tant que numéro reconnu par la fonction SOUS.TOTAL(), nous utiliserons la méthode WorksheetFunction.Match qui permet d’obtenir la position d’un élément au sein d’une liste :

    SOMMESICOULEUR = Application.WorksheetFunction.Subtotal(WorksheetFunction.Match(fonction,  listeFonctions, False), plage)

Et voilà, notre fonction est terminée, il reste plus qu’à la renommer étant que celle-ci d’effectuer bien plus de calcul qu’une simple somme :

Function SOUSTOTALSICOULEUR(cellules As Range,  fonction As String)
    …
    SOUSTOTALSICOULEUR = Application.WorksheetFunction.Subtotal(WorksheetFunction.Match(fonction,  listeFonctions, False), plage)

Puis de la tester :