Tutoriel Excel : compter et additionner les cellules par couleur ?

Introduction

Ce tutoriel vous explique comment compter et additionner les cellules par couleur de police ou couleur de cellule dans Excel. Ces solutions fonctionnent à la fois pour les cellules colorées manuellement et avec une mise en forme conditionnelle.

Si vous avez une feuille de calcul dans laquelle vous utilisez diverses couleurs de remplissage et de police pour différencier les types de cellules ou les valeurs, vous souhaiterez peut-être savoir combien de cellules sont mises en surbrillance avec une certaine couleur. Si le contenu de vos cellules est sous forme de nombres, vous pouvez également calculer automatiquement la somme des cellules ayant la même couleur de remplissage, par exemple la somme de toutes les cellules rouges.

Ne serait-il pas génial s'il y avait une fonction capable de compter les cellules colorées dans Excel ? Malheureusement, il n’existe aucune fonction intégrée pour cela. Mais, vous pouvez le faire facilement.

La fonction NB dans Excel compte les cellules contenant des nombres dans la plage de cellule en argument. Vous ne pouvez pas utiliser cette fonction pour compter les cellules colorées ou en surbrillance. Cependant, vous pouvez suivre quelques solutions de contournement pour compter les cellules colorées dans Excel. Suivez le reste du tutoriel pour plus de détails.

Table des matières

Comment compter les cellules colorées dans Excel

Méthode 1 : compter les cellules colorées à l'aide du filtre et du SOUS-TOTAL

Méthode 2 : utilisation des fonctions NB.SI et LIRE.CELLULE

Méthode 3 : compter les couleurs à l'aide de VBA (en créant une fonction personnalisée)

Somme des cellules par couleur de remplissage avec code VBA

Somme des cellules par couleur de police avec code VBA

Compter les cellules par couleur de police avec le code VBA

Comment compter les cellules colorées dans Excel ?

Dans une feuille de calcul Excel, vous pouvez compter les cellules en surbrillance en utilisant les solutions de contournement suivantes :

  • Application de SOUS.TOTAL et le filtrage des données.
  • Utilisation des fonctions NB et LIRE.CELLULE
  • Utilisation de code VBA

Méthode 1 : Compter les cellules colorées à l'aide du filtre et du SOUS-TOTAL

Vous pouvez compter les cellules ayant une certaine couleur de remplissage dans Excel en sous-totalisant les cellules visibles et en appliquant un filtre basé sur les couleurs.

Dans cet exemple, nous disposons de l'enregistrement des ventes de dix vendeurs pour le mois de septembre. Les lignes contenant les vendeurs ayant des ventes inférieures à 7 000 DH sont surlignées en orange, les autres cellules avec les vendeurs ayant un bonus sont surlignées en vert.

Pour compter les cellules colorées dans Excel, vous devez suivre les deux étapes suivantes :

  • Filtrer les cellules colorées.
  • Utilisez la fonction SOUS.TOTAL pour compter les cellules colorées visibles (après le filtrage).

Deux couleurs d'arrière-plan sont utilisées dans cet ensemble de données (vert et orange). Pour calculer le nombre de vendeurs surlignés en orange, suivez les étapes suivantes :

  1. Sélectionnez la cellule B12.
  2. Entrez la formule =SOUS.TOTAL(102; B2:B11).Le premier argument 102 compte les cellules visibles dans la plage spécifiée :

  1. Sélectionnez les cellules A1:B11en cliquant sur la cellule A1 et en la faisant glisser jusqu'à B11 avec votre souris.
  2. Accédez à l’onglet « Données».
  3. Dans le groupe « Trier et filtrer» cliquez sur « Filtrer ». Cela appliquera un filtre à tous les en-têtes de votre tableau.

  1. Cliquez sur la liste déroulante des filtres en B1.
  2. Cliquez sur l’option « Filtrer par couleur». Dans le jeu de données ci-dessus, étant donné que deux couleurs sont utilisées pour mettre en évidence les cellules, le filtre affiche deux couleurs pour filtrer ces cellules. Sélectionnez la couleur orange pour découvrir les vendeurs surlignés en orange.

Dès que vous filtrerez les cellules, vous remarquerez le changement de la valeur de la fonction SOUS.TOTAL qui va uniquement renvoyer le nombre de cellules visibles après le filtrage. La fonction SOUS.TOTAL utilise le nombre 102 comme premier argument, qui est utilisé pour compter les cellules visibles (les lignes masquées ne sont pas comptées) dans la plage spécifiée.

Si les données ne sont pas filtrées, elles retournent 10, mais si elles sont filtrées, elles ne retournent que le nombre de cellules visibles.

Méthode 2 : Utilisation des fonctions NB.SI et LIRE.CELLULE

Dans cette méthode, nous allons créer une plage nommée avec la fonction LIRE.CELLULE, obtenir le code de la couleur de chaque cellule, puis compter ou additionner facilement le code couleur souhaité dans Excel.

LIRE.CELLULE est une ancienne fonction Macro4 et ne fonctionne pas avec les fonctions normales. Cependant, elle fonctionne toujours avec des plages nommées. Pour compter les cellules colorées avec LIRE.CELLULE, vous devez extraire les codes couleur avec GET.CELL et les compter pour connaître le nombre de cellules mises en surbrillance avec la même couleur. Pour compter les cellules à l'aide de LIRE.CELLULE et NB.SI, suivez les étapes suivantes :

  1. Créez une plage nommée :

Accédez à l’onglet « Formules » et cliquez sur « Définir un nom » dans le groupe « Noms définis ».

Dans la boîte de dialogue « Nouveau nom », entrez :

o   Nom : CodeCouleur

o   Zone : Classeur

o   Fait référence à : =LIRE.CELLULE(38; $B2)

Cliquez sur « Ok » pour fermer la boite de dialogue « Nouveau nom ».

Dans la formule précédente, nous avons utilisé B2 comme deuxième argument. Vous devez utiliser la référence de la colonne dans laquelle vous avez les cellules avec la couleur d'arrière-plan.

  1. Obtenir le code couleur pour chaque cellule :

Entrez la formule =CodeCouleur dans la cellule C2.

Faites glisser la poignée de recopie jusqu'à C11 avec votre souris :

Cette formule renvoie 0 s'il n'y a pas de couleur de remplissage dans une cellule et renvoie un nombre spécifique s'il existe une couleur de remplissage. Ce nombre est spécifique à une couleur, donc toutes les cellules avec la même couleur d'arrière-plan reçoivent le même nombre.

  1. Compter les cellules colorées à l'aide du code couleur calculé :

Si vous suivez le processus précédent, vous auriez une colonne avec des nombres correspondants à la couleur de remplissage de chaque cellule. Pour compter les cellules d'une couleur spécifique suivez les étapes suivantes :

  • Quelque part sous le jeu de données, appliquez la même couleur de remplissage que vous souhaitez compter à une cellule donnée. Assurez-vous de le faire dans la même colonne que celle que vous avez utilisée pour créer la plage nommée. Par exemple, nous avons utilisé la colonne B, et par conséquent, nous n'utiliserons que les cellules de la colonne « B ».
  • Dans la cellule adjacente (C13 dans cet exemple), utilisez la formule suivante : =NB.SI($C$2:$C$11; CodeCouleur) :

Cette formule comptera les cellules colorées en orange pour trouver le nombre de vendeurs avec des ventes inférieures à 7000 DH et renvoyer le nombre 5.

La fonction NB.SI utilise la plage nommée « CodeCouleur », que nous avons créé, comme critère. La plage nommée dans la formule fait référence à la cellule adjacente à gauche (dans la colonne B) et renvoie le code couleur de cette cellule. Par conséquent, ce numéro de code couleur est le critère.

La fonction NB.SI utilise la plage « $C$2:$C$11 » qui contient les numéros de code couleur de toutes les cellules et renvoie le nombre basé sur le code renvoyé par CodeCouleur dans le critère.

Méthode 3 : Compter les couleurs à l'aide de VBA (en créant une fonction personnalisée)

Une autre méthode de contournement consiste à créer une fonction personnalisée avec VBA pour compter les cellules avec une couleur dans Excel. Pour ce faire, vous devez créer une fonction personnalisée à l'aide de VBA qui fonctionne comme une fonction NB.SI et renvoie le nombre de cellules pour une certaine couleur.

Vous suivrez la syntaxe : =CompterCouleur(PlageCouleur ; Couleur) et l'utiliserez comme les autres fonctions régulières. Ici, Couleur est la couleur pour laquelle vous souhaitez compter les cellules. PlageCouleur est la plage dans laquelle vous souhaitez compter les cellules avec la couleur d'arrière-plan spécifiée.

Pour créer cette fonction personnalisée suivez les étapes suivantes :

  1. Ouvrez le classeur contenant vos données, appuyez sur Alt + F11pour ouvrir l'éditeur VBA. Vous pouvez également l’ouvrir en cliquant avec le bouton droit sur l'onglet de la feuille de calcul et sélectionnez « Visualiser le code ».

  1. Dans le volet gauche, sous le nom du classeur dans lequel vous travaillez, cliquez avec le bouton droit sur l'une des feuilles de calcul et sélectionnez « Insertion »et choisissez « Module ». Cela insérerait un nouveau module.

  1. Copiez et collez le code dans la fenêtre de code du module.

Function CompterCouleur(PlageCouleur As Range, Couleur As Range)

Dim CodeCouleur As Integer

Dim NbrCouleur As Integer

CodeCouleur = Couleur.Interior.ColorIndex

Set CCell = PlageCouleur

For Each CCell In PlageCouleur

  If CCell.Interior.ColorIndex = CodeCouleur Then

    NbrCouleur = NbrCouleur + 1

  End If

Next CCell

CompterCouleur = NbrCouleur

End Function

Dans ce code, nous avons défini une fonction avec deux arguments PlageCouleur et Couleur. Nous allons enregistrer la valeur de la couleur de remplissage de la cellule B2 dans CodeCouleur. Ensuite, nous allons exécuter une boucle FOR où NbrCouleur serait incrémenté si la couleur d'arrière-plan de la cellule correspond à la couleur de CodeCouleur. Cette fonction renvoie la valeur de NbrCouleur, qui est le nombre de cellules ayant la même couleur d'arrière-plan.

  1. Fermez l'éditeur VB. C'est tout ! Vous avez maintenant une fonction personnalisée dans la feuille de calcul appelée CompterCouleur.
  2. Maintenant, nous allons utiliser cette formule. Utilisez la couleur d'arrière-plan que vous souhaitez compter dans une cellule (Ici, B13)et utilisez la formule suivante dans la cellule adjacente (C13) :

=CompterCouleur(B2:B11;B13)

Ici B13 est la cellule avec la couleur de fond (orange) que vous voulez compter. B2:B11 est la plage de cellules dans laquelle vous souhaitez compter la couleur.

Cela renverrait le nombre de vendeurs avec des ventes inférieures à 7000 DH au mois de Septembre, soit 5.

Si, après avoir appliqué le code VBA mentionné ci-dessus, vous devez colorer manuellement quelques cellules supplémentaires, la somme et le nombre des cellules colorées ne seront pas recalculés automatiquement pour refléter les changements. Ne soyez pas en colère contre nous, ce n'est pas un bug du code.

En fait, c'est le comportement normal de toutes les macros Excel, scripts VBA et fonctions définies par l'utilisateur. Le fait est que toutes ces fonctions sont appelées avec une modification des données d'une feuille de calcul uniquement et Excel ne perçoit pas la modification de la couleur de police ou de la couleur de cellule comme une modification des données. Ainsi, après avoir appliqué une couleur de remplissage à des cellules manuellement, placez simplement le curseur sur n'importe quelle cellule et appuyez sur F2 et « Enter », la somme et le nombre seront mis à jour.

Remarque : étant donné qu'il existe un code dans le classeur, enregistrez-le avec une extension .xls ou .xlsm.

Somme des cellules par couleur de remplissage avec un code VBA

Vous pouvez également créer une fonction VBA pour obtenir le résultat de la somme des cellules par couleur de remplissage de cellule dans Excel. Suivez simplement les étapes suivantes :

  1. Ouvrez l’éditeur VBA en utilisant le raccourcie Alt + F11, ou en cliquant sur la commande « Visual Basic» sous l'onglet « DÉVELOPPEUR ».
  2. Faites un clic droit sur une feuille de calcul du classeur et cliquez sur « Insertion» puis sur « Module » pour créer un nouveau module.

  1. Copiez et collez le code VBA ci-dessous dans la fenêtre de code.
  2. Cliquez ensuite sur le bouton « Enregistrer »et fermez l’éditeur VBA.

Public Function SommeParCouleur(plage As Range, Couleur As Range)

      Somme = 0

      For Each rCell In plage

          If rCell.Interior.Color = Couleur.Interior.Color Then

              Somme = Somme + rCell.Value

          End If

      Next

      SommeParCouleur = Somme

     

End Function

  1. Retournez à la feuille de calcul contenant vos données, puis tapez la formule suivante dans une cellule vide. Appuyez sur la touche Entrée :

=SommeParCouleur(B2:B11;B13)

Somme des cellules par couleur de police avec un code VBA

Dans cette section vous apprendrez à additionner les cellules par couleur de police dans une plage de cellules donnée dans une feuille de calcul Excel. Nous allons également écrire une fonction définie par l'utilisateur pour obtenir le résultat. Suivez simplement les étapes suivantes :

  1. Ouvrez l’éditeur VBA en utilisant le raccourcie Alt + F11, ou en cliquant sur la commande « Visual Basic» sous l'onglet « DÉVELOPPEUR ».
  2. Faites un clic droit sur une feuille de calcul du classeur et cliquez sur « Insertion» puis sur « Module » pour créer un nouveau module.

  1. Collez le code VBA suivant dans la fenêtre de code.

Public Function SommePrCoulDeFond(Plage As Range, Couleur As Range)

     Somme = 0

     For Each rCell In Plage

         If rCell.Font.Color = Couleur.Font.Color Then

             Somme = Somme + rCell.Value

         End If

     Next

     SommePrCoulDeFond = Somme

End Function

  1. Revenez sur la feuille de calcul actuelle, puis tapez la formule suivante dans une cellule vide :

=SommePrCoulDeFond(B2:B11; B13)

Compter les cellules par couleur de police avec le code VBA

Dans cet exemple nous allons compter les cellules ayant une couleur de police blanche dans la plage de cellule B2:B11. Nous allons utiliser la fonction ci-dessous pour obtenir le résultat.

Public Function CompterParCouleurFond(Plage As Range, Couleur As Range)

     Application.Volatile

     For Each Cellule In Plage

         If Cellule.Font.Color = Couleur.Font.Color Then

             CompterParCouleurFond = CompterParCouleurFond + 1

         End If

     Next

End Function

Télécharger le fichier Excel de ce tutoriel

Tutoriel Excel