Excel : Comment additionner des cellules par couleur ? (Méthodes & Astuces)

Excel : Additionner des cellules par couleur – Le guide complet

L'addition de cellules par couleur dans Excel est une fonctionnalité non native, mais qui peut s'avérer extrêmement utile pour l'analyse de données. Imaginez que vous ayez un tableau avec des données catégorisées par couleur. Au lieu de filtrer manuellement et d'additionner, vous pouvez automatiser ce processus grâce aux méthodes que nous allons explorer.

Pourquoi additionner par couleur ?

L'utilisation de la couleur dans Excel est souvent un moyen simple et rapide de catégoriser ou de mettre en évidence des informations importantes. Voici quelques exemples d'utilisation :

• Gestion de projet : Identifier les tâches en retard (rouge), en cours (jaune) ou terminées (vert).
• Suivi des ventes : Mettre en évidence les produits les plus vendus (vert) ou ceux qui se vendent moins bien (rouge).
• Analyse financière : Indiquer les dépenses supérieures au budget (rouge) ou les bénéfices (vert).

Dans ces cas, pouvoir additionner les valeurs associées à une couleur spécifique peut grandement faciliter l'analyse et la prise de décision.

Méthode 1 : Utiliser un filtre et la fonction SOUS.TOTAL

La méthode la plus simple, bien que manuelle, consiste à utiliser le filtre d'Excel et la fonction SOUS.TOTAL. Cette méthode est idéale si vous n'avez besoin de faire cette opération qu'occasionnellement.

Étapes à suivre :

1. Sélectionnez la plage de données : Sélectionnez la plage de cellules contenant les valeurs que vous souhaitez additionner.
2. Appliquez un filtre : Allez dans l'onglet « Données » et cliquez sur « Filtrer ».
3. Filtrez par couleur : Cliquez sur la flèche du filtre dans la colonne contenant les cellules colorées. Sélectionnez « Filtrer par couleur » et choisissez la couleur désirée. Capture d'écran: menu déroulant du filtre, option "Filtrer par couleur" sélectionnée, liste des couleurs disponibles affichée.
4. Utilisez la fonction SOUS.TOTAL : Dans une cellule vide, entrez la formule =SOUS.TOTAL(9;plage), où plage est la plage de cellules filtrée contenant les valeurs à additionner. Le chiffre 9 indique que vous souhaitez effectuer une somme. Exemple: =SOUS.TOTAL(9;B2:B10).

Avantages :

• Simple à mettre en œuvre.
• Ne nécessite pas de connaissances en VBA.

Inconvénients :

• Manuelle et répétitive si vous devez additionner plusieurs couleurs.
• Nécessite de refaire le filtrage à chaque modification des données.

Méthode 2 : Créer une fonction personnalisée en VBA

Pour une solution plus automatisée, vous pouvez créer une fonction personnalisée en VBA (Visual Basic for Applications). Cette méthode est plus complexe, mais elle vous permet d'additionner les cellules par couleur en utilisant une simple formule.

Étape 1 : Ouvrir l'éditeur VBA

1. Ouvrez Excel.
2. Appuyez sur Alt + F11 pour ouvrir l'éditeur VBA. Capture d'écran: l'éditeur VBA ouvert, montrant la fenêtre "Microsoft Visual Basic for Applications".

Étape 2 : Insérer un nouveau module

1. Dans l'éditeur VBA, allez dans « Insertion » > « Module ».

Étape 3 : Écrire le code VBA

Copiez et collez le code VBA suivant dans le module :

Function SommeSiCouleur(plage As Range, couleur As Range) As Double
    Dim Cellule As Range
    Dim Somme As Double
    Somme = 0
    For Each Cellule In plage
        If Cellule.Interior.Color = couleur.Interior.Color Then
            Somme = Somme + Cellule.Value
        End If
    Next Cellule
    SommeSiCouleur = Somme
End Function

Explication du code :

• Function SommeSiCouleur(plage As Range, couleur As Range) As Double : Définit une fonction nommée SommeSiCouleur qui prend deux arguments : plage (la plage de cellules à additionner) et couleur (une cellule contenant la couleur de référence).
• Dim Cellule As Range : Déclare une variable Cellule de type Range pour parcourir les cellules de la plage.
• Dim Somme As Double : Déclare une variable Somme de type Double pour stocker la somme des cellules colorées.
• For Each Cellule In plage : Boucle à travers chaque cellule de la plage.
• If Cellule.Interior.Color = couleur.Interior.Color Then : Vérifie si la couleur de fond de la cellule correspond à la couleur de la cellule de référence.
• Somme = Somme + Cellule.Value : Si la couleur correspond, ajoute la valeur de la cellule à la variable Somme.
• SommeSiCouleur = Somme : Renvoie la somme totale.

Étape 4 : Utiliser la fonction dans Excel

1. Fermez l'éditeur VBA.
2. Dans Excel, sélectionnez une cellule vide.
3. Entrez la formule =SommeSiCouleur(plage;couleur), où plage est la plage de cellules à additionner et couleur est une cellule contenant la couleur de référence. Exemple: =SommeSiCouleur(B2:B10;D2) où D2 est une cellule avec la couleur à rechercher..

Avantages :

• Automatisée et facile à utiliser une fois la fonction créée.
• Plus rapide que la méthode du filtre si vous devez additionner plusieurs couleurs.

Inconvénients :

• Nécessite des connaissances en VBA.
• La fonction ne se met pas à jour automatiquement si les couleurs sont modifiées. Vous devrez recalculer la feuille de calcul (appuyez sur F9).

Amélioration de la fonction VBA (Actualisation automatique)

Pour que la fonction se mette à jour automatiquement lorsque les couleurs changent, vous pouvez ajouter la ligne Application.Volatile au début de la fonction VBA. Cela forcera Excel à recalculer la fonction à chaque changement dans la feuille de calcul.

Function SommeSiCouleur(plage As Range, couleur As Range) As Double
    Application.Volatile
    Dim Cellule As Range
    Dim Somme As Double
    Somme = 0
    For Each Cellule In plage
        If Cellule.Interior.Color = couleur.Interior.Color Then
            Somme = Somme + Cellule.Value
        End If
    Next Cellule
    SommeSiCouleur = Somme
End Function

Méthode 3 : Utiliser Power Query (Get & Transform Data)

Power Query est un outil puissant d'Excel qui permet d'importer, transformer et charger des données. Bien qu'il ne gère pas directement les couleurs, nous pouvons l'utiliser en combinaison avec une colonne auxiliaire pour obtenir le résultat souhaité.

Étapes à suivre :

1. Ajouter une colonne auxiliaire : Insérez une nouvelle colonne à côté de la colonne contenant les cellules colorées. Capture d'écran: tableur Excel avec une nouvelle colonne insérée, intitulée "Couleur".
2. Déterminer l'index de la couleur : Dans cette colonne, utilisez une fonction VBA (similaire à la méthode 2) pour obtenir l'index de la couleur de chaque cellule. Voici le code VBA à utiliser dans la colonne auxiliaire: =ObtenirCouleur(A2) où A2 est la cellule dont on veut connaitre l'index de couleur.

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

1. Charger les données dans Power Query : Sélectionnez votre tableau, allez dans l'onglet « Données » et cliquez sur « Du tableau/plage ».
2. Grouper et additionner : Dans l'éditeur Power Query, sélectionnez la colonne contenant l'index de la couleur, puis allez dans « Accueil » > « Grouper par ».
3. Configurer le groupement : Dans la fenêtre « Grouper par », choisissez la colonne de l'index de la couleur comme colonne de groupement. Ajoutez une nouvelle opération pour additionner les valeurs de la colonne contenant les données. Capture d'écran: fenêtre "Grouper par" dans Power Query, colonne "Couleur" sélectionnée pour le groupement, et une opération "Somme" configurée pour la colonne des valeurs..
4. Charger les résultats : Fermez et chargez les résultats dans une nouvelle feuille de calcul.

Avantages :

• Solution plus robuste et flexible pour l'analyse de données.
• Permet de combiner des données provenant de différentes sources.

Inconvénients :

• Plus complexe à mettre en œuvre que les autres méthodes.
• Nécessite une colonne auxiliaire.

Bonnes pratiques et erreurs à éviter

• Cohérence des couleurs : Assurez-vous d'utiliser des couleurs cohérentes dans votre feuille de calcul. Évitez d'utiliser des nuances subtiles qui pourraient être difficiles à distinguer.
• Utiliser les styles : Utilisez les styles d'Excel pour appliquer les couleurs. Cela permet de modifier facilement les couleurs et de garantir la cohérence.
• Ne pas abuser des couleurs : Trop de couleurs peuvent rendre votre feuille de calcul difficile à lire. Utilisez les couleurs avec parcimonie et uniquement pour mettre en évidence des informations importantes.
• Tester la fonction VBA : Avant d'utiliser la fonction VBA dans votre feuille de calcul, testez-la soigneusement pour vous assurer qu'elle fonctionne correctement.
• Recalculer la feuille : N'oubliez pas de recalculer la feuille de calcul (appuyez sur F9) après avoir modifié les couleurs si vous n'avez pas inclus Application.Volatile dans votre code VBA.

Conclusion

Additionner des cellules par couleur dans Excel peut sembler complexe, mais avec les méthodes présentées dans cet article, vous pouvez facilement automatiser ce processus. Que vous choisissiez d'utiliser le filtre et la fonction SOUS.TOTAL, de créer une fonction personnalisée en VBA ou d'utiliser Power Query, vous disposez désormais des outils nécessaires pour analyser vos données d'une manière nouvelle et colorée. N'hésitez pas à expérimenter et à adapter ces méthodes à vos besoins spécifiques. Excel regorge de possibilités, et la maîtrise de ces techniques vous permettra d'optimiser votre travail et de gagner un temps précieux.