Astuce Excel : Compter/Additionner des cellules par couleur (arrière-plan, police, mise en forme conditionnelle)

Dans les tâches quotidiennes, le marquage par couleur est une méthode populaire pour distinguer et mettre en évidence rapidement des données cruciales. Mais comment compter ou additionner des données de cellules en fonction d'une couleur spécifique (couleur de remplissage, couleur de police, mise en forme conditionnelle) ? Par défaut, Excel n'offre pas de fonction directe pour compter ou additionner par couleur. Cependant, avec quelques astuces et méthodes indirectes, nous pouvons tout de même y parvenir. Cet article explorera comment compter ou additionner des données par couleur.

Compter et additionner des cellules en fonction de la couleur d'arrière-plan

• En utilisant une fonction définie par l'utilisateur
• En utilisant une fonctionnalité puissante – Kutools pour Excel
• En utilisant Filtre et SOUS.TOTAL

Compter et additionner des cellules en fonction de la couleur de police

• En utilisant une fonction définie par l'utilisateur
• En utilisant une fonctionnalité simple – Kutools pour Excel

Compter et additionner des cellules en fonction de la couleur de mise en forme conditionnelle

• En utilisant du code VBA
• En utilisant une fonctionnalité intelligente – Kutools pour Excel

Vidéo : Compter et additionner des cellules en fonction de la couleur

Compter et additionner des cellules en fonction de la couleur d'arrière-plan

Par exemple, si vous avez une plage de données où les valeurs sont remplies avec différentes couleurs d'arrière-plan comme illustré dans la capture d'écran ci-dessous. Pour compter ou additionner les cellules en fonction d'une couleur spécifique, Excel ne propose pas de fonction directe pour compter ou additionner les cellules en fonction de leur couleur d'arrière-plan. Cependant, avec un peu d'ingéniosité et certaines techniques pratiques, vous pouvez accomplir cette tâche. Explorons quelques méthodes utiles dans cette section.

Compter et additionner des cellules par couleur d'arrière-plan avec une fonction définie par l'utilisateur

Ici, nous allons vous montrer comment créer et utiliser une telle fonction définie par l'utilisateur pour résoudre cette tâche dans Excel. Suivez les étapes suivantes :

Étape 1 : Ouvrir l'éditeur de module VBA et copier le code

1. Appuyez sur Alt + F11 pour ouvrir la fenêtre Microsoft Visual Basic for Applications.
2. Dans la fenêtre ouverte, cliquez sur Insertion > Module pour créer un nouveau module vierge.
3. Ensuite, copiez et collez le code ci-dessous dans le module vierge.
   Code VBA : Compter et additionner des cellules en fonction de la couleur d'arrière-plan

   Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
   'Updateby Extendoffice
       Dim rCell As Range
       Dim lCol As Long
       Dim vResult As Double
       lCol = rColor.Interior.ColorIndex
       vResult = 0
       If SUM Then
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + rCell.Value
               End If
           Next rCell
       Else
           For Each rCell In rRange
               If rCell.Interior.ColorIndex = lCol Then
                   vResult = vResult + 1
               End If
           Next rCell
       End If
       ColorFunction = vResult
   End Function
   

Étape 2 : Créer des formules pour compter et additionner des cellules par couleur d'arrière-plan

Après avoir collé le code ci-dessus, fermez la fenêtre du module, puis appliquez les formules suivantes :

• Compter les cellules en fonction de la couleur d'arrière-plan spécifique :
  Copiez ou tapez la formule ci-dessous dans la cellule souhaitée pour obtenir le résultat. Ensuite, faites glisser la poignée de recopie vers le bas pour obtenir d'autres résultats. Voir la capture d'écran :
  

  =colorfunction(G2,$B$2:$E$12,FALSE) 

  Remarque : Dans cette formule, G2 est la cellule de référence avec la couleur d'arrière-plan spécifique que vous souhaitez faire correspondre ; $B$2:$E$12 est la plage où vous souhaitez compter le nombre de cellules de la couleur de G2 ; FAUX est utilisé pour compter les cellules avec une couleur correspondante.
  
• Additionner les cellules en fonction de la couleur d'arrière-plan spécifique :
  Copiez ou tapez la formule ci-dessous dans la cellule souhaitée pour obtenir le résultat. Ensuite, faites glisser la poignée de recopie vers le bas pour obtenir d'autres résultats. Voir la capture d'écran :
  

  =colorfunction(G2,$B$2:$E$12,TRUE)  

  Remarque : Dans cette formule, G2 est la cellule de référence avec la couleur d'arrière-plan spécifique que vous souhaitez faire correspondre ; $B$2:$E$12 est la plage où vous souhaitez compter le nombre de cellules de la couleur de G2 ; VRAI est utilisé pour additionner les cellules avec une couleur correspondante.
  

Compter et additionner des cellules par couleur d'arrière-plan avec une fonctionnalité puissante

Pour ceux qui ne connaissent pas la programmation, VBA peut sembler assez complexe. Ici, nous allons introduire un outil puissant - Kutools pour Excel, sa fonctionnalité Compter par couleur vous permet de calculer facilement (compter, additionner, moyenne, etc.) en fonction de la couleur d'arrière-plan en quelques clics seulement. Impressionnamment, la fonctionnalité Compter par couleur va au-delà des simples couleurs d'arrière-plan – elle peut également différencier et calculer en fonction des couleurs de police et de la mise en forme conditionnelle.

Après avoir téléchargé et installé Kutools pour Excel, commencez par sélectionner la plage de données que vous souhaitez compter ou additionner en fonction d'une couleur d'arrière-plan spécifique. Ensuite, accédez à Kutools Plus et choisissez Compter par couleur.

Dans la boîte de dialogue Compter par couleur, veuillez spécifier les opérations :

1. Sélectionnez Mise en forme standard dans la liste déroulante Méthode de couleur ;
2. Spécifiez Arrière-plan dans la liste déroulante Type de décompte, et vous pouvez prévisualiser les résultats statistiques pour chaque couleur d'arrière-plan dans la boîte de dialogue ;
3. Enfin, cliquez sur Générer un rapport pour exporter les résultats calculés vers un nouveau classeur.

Résultat :

Vous obtiendrez maintenant un nouveau classeur avec les statistiques. Voir la capture d'écran :

Compter et additionner des cellules par couleur d'arrière-plan avec Filtre et la fonction SOUS.TOTAL

Supposons que nous ayons un tableau de ventes de fruits comme indiqué dans la capture d'écran ci-dessous, et nous allons compter ou additionner les cellules colorées dans la colonne Montant colonne.

Étape 1 : Appliquer la fonction SOUS.TOTAL

Sélectionnez des cellules vides pour entrer la fonction SOUS.TOTAL.

• Pour compter toutes les cellules ayant la même couleur d'arrière-plan, veuillez entrer la formule :

  =SUBTOTAL(102, F2:F16)

• Pour additionner toutes les cellules ayant la même couleur d'arrière-plan, veuillez entrer la formule ;

  =SUBTOTAL(109, F2:F16)

• Remarque : dans les formules ci-dessus, 102 représente le comptage des valeurs numériques dans une liste filtrée tout en excluant les cellules masquées ; 109 représente la somme des valeurs dans une liste filtrée en excluant les cellules masquées ; F2:F16 est la plage sur laquelle soit le comptage soit la somme sera calculée.
• 

Étape 2 : Filtrer les cellules en fonction d'une couleur spécifique

1. Sélectionnez l'en-tête du tableau, puis cliquez Données > Filtrer. Voir la capture d'écran :
   
2. Cliquez sur l' icône Filtrer icône  dans la cellule d'en-tête de la Montant colonne, et cliquez Filtrer par couleur et la couleur spécifiée que vous allez compter successivement. Voir la capture d'écran :
   

Résultat :

Après filtrage, les formules SOUS.TOTAL comptent et additionnent automatiquement les cellules colorées dans la Montant colonne. Voir la capture d'écran :

Compter et additionner des cellules en fonction de la couleur de police

Souhaitez-vous compter ou additionner des cellules en fonction de leur couleur de police dans Excel ? Disons que vous avez les données, comme dans la capture d'écran donnée, avec des cellules contenant des textes en rouge, bleu, orange et noir. Excel ne facilite pas cela par défaut. Mais ne vous inquiétez pas ! Dans cette section, nous allons vous montrer quelques astuces simples pour y parvenir.

Compter et additionner des cellules en fonction de la couleur de police avec une fonction définie par l'utilisateur

Pour compter et additionner des cellules avec des couleurs de police spécifiques, la fonction définie par l'utilisateur suivante peut vous aider à résoudre cette tâche. Suivez les étapes suivantes :

Étape 1 : Ouvrir l'éditeur de module VBA et copier le code

1. Appuyez sur Alt + F11 pour ouvrir la fenêtre Microsoft Visual Basic for Applications.
2. Dans la fenêtre ouverte, cliquez sur Insertion > Module pour créer un nouveau module vierge.
3. Ensuite, copiez et collez le code ci-dessous dans le module vierge.
   Code VBA : Compter et additionner des cellules en fonction de la couleur de police

   Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
   'Updateby Extendoffice
       Application.Volatile
       Dim rng As Range
       Dim xTotal As Double
       Dim xCount As Double
       xTotal = 0
       xCount = 0
       For Each rng In pRange1
           If rng.Font.Color = pRange2.Font.Color Then
               If UCase(FunctionType) = "SUM" Then
                   xTotal = xTotal + rng.Value
               ElseIf UCase(FunctionType) = "COUNT" Then
                   xCount = xCount + 1
               End If
           End If
       Next
       If UCase(FunctionType) = "SUM" Then
           ProcessByFontColor = xTotal
       ElseIf UCase(FunctionType) = "COUNT" Then
           ProcessByFontColor = xCount
       Else
           ProcessByFontColor = CVErr(xlErrValue)
       End If
   End Function
   

Étape 2 : Créer des formules pour compter et additionner des cellules par couleur de police

Après avoir collé le code ci-dessus, fermez la fenêtre du module, puis appliquez les formules suivantes :

• Compter les cellules en fonction de la couleur de police spécifique :
  Copiez ou tapez la formule ci-dessous dans la cellule souhaitée pour obtenir le résultat. Ensuite, faites glisser la poignée de recopie vers le bas pour obtenir d'autres résultats. Voir la capture d'écran :
  

  =ProcessByFontColor($B$2:$E$12,G2, "COUNT")

  Remarque : Dans cette formule, G2 est la cellule de référence avec la couleur de police spécifique que vous souhaitez faire correspondre ; $B$2:$E$12 est la plage où vous souhaitez compter le nombre de cellules de la couleur de G2.
  
• Additionner les cellules en fonction de la couleur de police spécifique :
  Copiez ou tapez la formule ci-dessous dans la cellule souhaitée pour obtenir le résultat. Ensuite, faites glisser la poignée de recopie vers le bas pour obtenir d'autres résultats. Voir la capture d'écran :
  

  =ProcessByFontColor($B$2:$E$12,G2, "SUM")  

  Remarque : Dans cette formule, G2 est la cellule de référence avec la couleur de police spécifique que vous souhaitez faire correspondre ; $B$2:$E$12 est la plage où vous souhaitez compter le nombre de cellules de la couleur de G2.
  

Compter et additionner des cellules en fonction de la couleur de police avec une fonctionnalité simple

Vous cherchez à compter ou additionner facilement les valeurs des cellules dans Excel en fonction de la couleur de police ? Plongez dans la fonctionnalité Compter par couleur de Kutools pour Excel ! Avec cet outil intelligent, compter et additionner des cellules par une couleur de police spécifique devient un jeu d'enfant. Découvrez comment Kutools peut transformer votre expérience Excel.

Après avoir téléchargé et installé Kutools pour Excel, commencez par sélectionner la plage de données que vous souhaitez compter ou additionner en fonction d'une couleur de police spécifique. Ensuite, cliquez sur Kutools Plus > Compter par couleur pour ouvrir la boîte de dialogue Compter par couleur.

Dans la boîte de dialogue Compter par couleur, veuillez spécifier les opérations :

1. Sélectionnez Mise en forme standard dans la liste déroulante Méthode de couleur ;
2. Spécifiez Police dans la liste déroulante Type de décompte, et vous pouvez prévisualiser les résultats statistiques pour chaque couleur de police dans la boîte de dialogue ;
3. Enfin, cliquez sur Générer un rapport pour exporter les résultats calculés vers un nouveau classeur.

Résultat :

Maintenant, vous avez un nouveau classeur affichant les statistiques détaillées en fonction de la couleur de police. Voir la capture d'écran :

Compter et additionner des cellules en fonction de la couleur de mise en forme conditionnelle

Dans Excel, vous utilisez souvent la mise en forme conditionnelle pour appliquer une couleur spécifique aux cellules qui répondent à certains critères, rendant la visualisation des données intuitive. Mais que faire si vous devez compter ou additionner ces cellules spécialement formatées ? Bien qu'Excel n'offre pas de moyen direct pour cela, voici des façons de contourner cette limitation.

Compter et additionner des cellules mises en forme conditionnellement avec du code VBA

Compter et additionner des cellules mises en forme conditionnellement dans Excel n'est pas simple en utilisant les fonctions intégrées. Cependant, vous pouvez accomplir cette tâche en utilisant du code VBA. Passons en revue comment vous pouvez utiliser VBA pour cela :

Étape 1 : Ouvrir l'éditeur de module VBA et copier le code

1. Appuyez sur Alt + F11 pour ouvrir la fenêtre Microsoft Visual Basic for Applications.
2. Dans la fenêtre ouverte, cliquez sur Insertion > Module pour créer un nouveau module vierge.
3. Ensuite, copiez et collez le code ci-dessous dans le module vierge.
   Code VBA : Compter et additionner des cellules en fonction de la couleur de mise en forme conditionnelle

   Sub SumCountByConditionalFormat()
   'Updateby Extendoffice
       Dim sampleColor As Range
       Dim selectedRange As Range
       Dim cell As Range
       Dim countByColor As Long
       Dim sumByColor As Double
       Dim refColor As Long
       Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                "Kutools for Excel", _
                                                Type:=8)
       If selectedRange Is Nothing Then Exit Sub
       Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                              "Kutools for Excel", _
                                              Type:=8)
       If Not sampleColor Is Nothing Then
           refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
           For Each cell In selectedRange
               If cell.DisplayFormat.Interior.color = refColor Then
                   countByColor = countByColor + 1
                   sumByColor = sumByColor + cell.Value
               End If
           Next cell
           MsgBox "Count: " & countByColor & vbCrLf & _
                  "Sum: " & sumByColor, _
                  vbInformation, "Results based on Conditional Format Color"
       End If
   End Sub
   

Étape 2 : Exécuter ce code VBA

1. Après avoir collé le code, appuyez sur F5 pour exécuter ce code, une boîte de dialogue apparaîtra, veuillez sélectionner la plage de données où vous souhaitez compter et additionner les cellules en fonction de la mise en forme conditionnelle. Ensuite, cliquez OK, Voir la capture d'écran :
   
2. Dans une autre boîte de dialogue, sélectionnez une couleur spécifique de mise en forme conditionnelle que vous souhaitez compter et additionner, puis cliquez OK bouton, voir la capture d'écran :
   

Résultat :

Maintenant, le résultat, qui inclut à la fois le comptage et la somme des cellules avec la couleur de mise en forme conditionnelle spécifiée, sera affiché dans la boîte qui s'affiche. Voir la capture d'écran :

Compter et additionner des cellules mises en forme conditionnellement avec une fonctionnalité intelligente

Si vous recherchez d'autres méthodes rapides et faciles pour compter et additionner des cellules mises en forme conditionnellement, Kutools pour Excel est votre solution idéale. Sa fonctionnalité Compter par couleur peut résoudre cette tâche en quelques clics. Plongez pour découvrir l'efficacité et la précision que Kutools peut apporter à votre flux de travail.

Après avoir téléchargé et installé Kutools pour Excel, commencez par sélectionner la plage de données que vous souhaitez compter ou additionner en fonction d'une couleur spécifique de mise en forme conditionnelle. Ensuite, cliquez sur Kutools Plus > Compter par couleur pour ouvrir la boîte de dialogue Compter par couleur.

Dans la boîte de dialogue Compter par couleur, veuillez spécifier les opérations :

1. Sélectionnez Mise en forme conditionnelle dans la liste déroulante Méthode de couleur ;
2. Spécifiez Arrière-plan dans la liste déroulante Type de décompte, et vous pouvez prévisualiser les résultats statistiques pour chaque couleur de mise en forme conditionnelle dans la boîte de dialogue ;
3. Enfin, cliquez sur Générer un rapport pour exporter les résultats calculés vers un nouveau classeur.

Résultat :

Maintenant, vous avez un nouveau classeur affichant les statistiques détaillées en fonction de la couleur de mise en forme conditionnelle. Voir la capture d'écran :

Articles connexes :