Passer au contenu principal

Astuces Excel : comptez/additionnez les cellules par couleur (arrière-plan, police, mise en forme conditionnelle)

Dans les tâches quotidiennes, le marquage couleur est une méthode populaire pour distinguer et mettre en évidence rapidement les données cruciales. Mais comment compter ou additionner les données des 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 fonctionnalité directe pour compter ou additionner par couleur. Néanmoins, avec quelques astuces et méthodes indirectes, nous pouvons encore y parvenir. Cet article explorera comment compter ou additionner des données par couleur.

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

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

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


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


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

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


Comptez et additionnez les cellules par couleur d'arrière-plan avec la 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. Veuillez suivre les étapes suivantes :

Étape 1 : Ouvrez l'éditeur de module VBA et copiez le code

  1. Actualités Alt + F11 clés pour ouvrir le Microsoft Visual Basic pour applications fenêtre.
  2. Dans la fenêtre ouverte, cliquez sur insérer > Module pour créer un nouveau module vierge.
  3. Ensuite, copiez et collez le code ci-dessous dans le module vide.
    Code VBA : compter et additionner les 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éez des formules pour compter et additionner les 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 :

  • Comptez les cellules en fonction de la couleur d'arrière-plan spécifique :
    Copiez ou tapez la formule fournie ci-dessous dans la cellule souhaitée pour obtenir le résultat. Ensuite, faites glisser la poignée de remplissage vers le bas pour obtenir d'autres résultats. Voir capture d'écran :
    =colorfunction(G2,$B$2:$E$12,FALSE) 
    Notes: 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 ; 2 $ B $ : 12 $ E $ est la plage dans laquelle vous souhaitez compter le nombre de cellules de la couleur de G2 ; FAUX est utilisé pour compter les cellules avec une couleur correspondante.
  • Additionnez les cellules en fonction de la couleur d'arrière-plan spécifique :
    Copiez ou tapez la formule fournie ci-dessous dans la cellule souhaitée pour obtenir le résultat. Ensuite, faites glisser la poignée de remplissage vers le bas pour obtenir d'autres résultats. Voir capture d'écran :
    =colorfunction(G2,$B$2:$E$12,TRUE)  
    Notes: 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 ; 2 $ B $ : 12 $ E $ est la plage dans laquelle vous souhaitez compter le nombre de cellules de la couleur de G2 ; VRAI est utilisé pour additionner les cellules avec la couleur correspondante.

Comptez et additionnez les cellules par couleur d'arrière-plan avec une fonctionnalité puissante

Pour ceux qui ne connaissent pas la programmation, VBA peut paraître assez complexe. Ici, nous présenterons un outil puissant - Kutool pour Excel, il est Compter par couleur La fonctionnalité vous permet de calculer facilement (compte, somme, moyenne, etc.) en fonction de la couleur d'arrière-plan en quelques clics. Impressionnant, Compter par couleur Cette fonctionnalité va au-delà des simples couleurs d’arrière-plan : elle peut également différencier et calculer en fonction des couleurs de police et du formatage conditionnel.

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

Dans le Compter par couleur boîte de dialogue, veuillez préciser les opérations :

  1. Sélectionnez Formatage standard du Méthode de couleur la liste déroulante;
  2. Spécifier Contexte du Type de comptage liste déroulante 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 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:

Pourboires:
  1. La Compter par couleur La fonctionnalité prend également en charge le comptage et la somme des cellules en fonction de la couleur de police standard, de la couleur d'arrière-plan ou de la police issue du formatage conditionnel, ainsi que d'une combinaison de couleurs de remplissage et de formatage conditionnel.
  2. Intéressé par cette fonctionnalité, s'il vous plaît cliquez pour télécharger pour obtenir un essai gratuit pendant 30 jours.

Comptez et additionnez les cellules par couleur d'arrière-plan avec la fonction Filtre et SOUS-TOTAL

Supposons que nous ayons un tableau de ventes de fruits comme illustré ci-dessous, et que nous comptions ou additionnons les cellules colorées dans le Montant colonne.

Étape 1 : Appliquer la fonction SOUS-TOTAL

Sélectionnez des cellules vides pour accéder à la fonction SOUS-TOTAL.

  • Pour compter toutes les cellules avec la même couleur d'arrière-plan, veuillez saisir la formule :
    =SUBTOTAL(102, F2:F16)
  • Pour additionner toutes les cellules avec la même couleur d'arrière-plan, veuillez saisir la formule ;
    =SUBTOTAL(109, F2:F16)
  • Notes: 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 pour résumer les valeurs dans une liste filtrée excluant les cellules masquées ; F2: F16 est la plage sur laquelle le décompte ou la somme sera calculé.

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

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

Résultat:

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

Notes: Cette méthode nécessite que les cellules colorées que vous comptez ou additionnerez soient dans la même colonne.

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

Vous souhaitez compter ou additionner des cellules en fonction de la couleur de leur 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 rend pas cela facile par défaut. Mais ne vous inquiétez pas ! Dans cette section, nous allons vous montrer quelques astuces simples pour y parvenir.


Comptez et additionnez les cellules en fonction de la couleur de la police avec la fonction définie par l'utilisateur

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

Étape 1 : Ouvrez l'éditeur de module VBA et copiez le code

  1. Actualités Alt + F11 clés pour ouvrir le Microsoft Visual Basic pour applications fenêtre.
  2. Dans la fenêtre ouverte, cliquez sur insérer > Module pour créer un nouveau module vierge.
  3. Ensuite, copiez et collez le code ci-dessous dans le module vide.
    Code VBA : comptez et additionnez les cellules en fonction de la couleur de la 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éez des formules pour compter et additionner les cellules par couleur de police

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

  • Comptez les cellules en fonction de la couleur de police spécifique :
    Copiez ou tapez la formule fournie ci-dessous dans la cellule souhaitée pour obtenir le résultat. Ensuite, faites glisser la poignée de remplissage vers le bas pour obtenir d'autres résultats. Voir capture d'écran :
    =ProcessByFontColor($B$2:$E$12,G2, "COUNT")
    Notes: Dans cette formule, G2 est la cellule de référence avec la couleur de police spécifique que vous souhaitez faire correspondre ; 2 $ B $ : 12 $ E $ est la plage dans laquelle vous souhaitez compter le nombre de cellules de la couleur de G2.
  • Additionnez les cellules en fonction de la couleur de police spécifique :
    Copiez ou tapez la formule fournie ci-dessous dans la cellule souhaitée pour obtenir le résultat. Ensuite, faites glisser la poignée de remplissage vers le bas pour obtenir d'autres résultats. Voir capture d'écran :
    =ProcessByFontColor($B$2:$E$12,G2, "SUM")  
    Notes: Dans cette formule, G2 est la cellule de référence avec la couleur de police spécifique que vous souhaitez faire correspondre ; 2 $ B $ : 12 $ E $ est la plage dans laquelle vous souhaitez compter le nombre de cellules de la couleur de G2.

Comptez et additionnez les cellules en fonction de la couleur de la police avec une fonctionnalité simple

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

Après télécharger et installer Kutools pour Excel, commencez par sélectionner la plage de données que vous souhaitez compter ou additionner les cellules en fonction d’une couleur de police spécifique. Puis clique Kutools Plus > Compter par couleur ouvrir le Compter par couleur boite de dialogue.

Dans le Compter par couleur boîte de dialogue, veuillez préciser les opérations :

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

Résultat:

Vous disposez désormais d’un nouveau classeur affichant les statistiques détaillées basées sur la couleur de la police. Voir capture d'écran :

Pourboires: Intéressé par cette fonctionnalité, s'il vous plaît cliquez pour télécharger pour obtenir un essai gratuit pendant 30 jours.

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

Dans Excel, vous pouvez généralement utiliser la mise en forme conditionnelle pour appliquer une couleur spécifique aux cellules qui répondent à certains critères, rendant ainsi la visualisation des données intuitive. Mais que se passe-t-il 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 moyens de contourner cette limitation.


Compter et additionner les cellules formatées conditionnellement avec le code VBA

Compter et additionner les cellules formatées conditionnellement dans Excel n'est pas simple à l'aide des fonctions intégrées. Cependant, vous pouvez accomplir cette tâche à l'aide du code VBA. Voyons comment utiliser VBA pour cela :

Étape 1 : Ouvrez l'éditeur de module VBA et copiez le code

  1. Actualités Alt + F11 clés pour ouvrir le Microsoft Visual Basic pour applications fenêtre.
  2. Dans la fenêtre ouverte, cliquez sur insérer > Module pour créer un nouveau module vierge.
  3. Ensuite, copiez et collez le code ci-dessous dans le module vide.
    Code VBA : comptez et additionnez les 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écutez 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 dans laquelle vous souhaitez compter et additionner les cellules en fonction du formatage conditionnel. Puis clique OK, Voir capture d'écran:
  2. Dans une autre boîte de dialogue, sélectionnez une couleur de mise en forme conditionnelle spécifique que vous souhaitez compter et additionner, puis cliquez sur OK bouton, voir capture d'écran:

Résultat:

Désormais, le résultat, qui inclut à la fois le nombre et la somme des cellules avec la couleur de mise en forme conditionnelle spécifiée, sera affiché dans la zone contextuelle. Voir capture d'écran :


Comptez et additionnez les cellules formatées de manière conditionnelle avec une fonctionnalité intelligente

Si vous recherchez d'autres méthodes simples et rapides pour compter et additionner les cellules formatées de manière conditionnelle, Kutools for Excel est votre solution de prédilection. C'est Compter par couleur La fonctionnalité 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 télécharger et installer Kutools pour Excel, commencez par sélectionner la plage de données que vous souhaitez compter ou additionner les cellules en fonction d’une couleur de mise en forme conditionnelle spécifique. Puis clique Kutools Plus > Compter par couleur ouvrir le Compter par couleur boite de dialogue.

Dans le Compter par couleur boîte de dialogue, veuillez préciser les opérations :

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

Résultat:

Vous disposez désormais d’un nouveau classeur affichant les statistiques détaillées basées sur la couleur de mise en forme conditionnelle. Voir capture d'écran :

Pourboires: Intéressé par cette fonctionnalité, s'il vous plaît cliquez pour télécharger pour obtenir un essai gratuit pendant 30 jours.

Articles connexes:

  • Si la couleur de la police est rouge, renvoie un texte spécifique
  • Comment pourriez-vous renvoyer un texte spécifique si la couleur de la police est rouge dans une autre cellule, comme illustré ci-dessous ? Dans cet article, je présenterai quelques astuces pour effectuer certaines opérations basées sur le texte de police rouge dans Excel.
  • Filtrer les données par plusieurs couleurs
  • Normalement, dans Excel, vous pouvez filtrer rapidement les lignes avec une seule couleur, mais avez-vous déjà envisagé de filtrer des lignes avec plusieurs couleurs en même temps? Cet article, je vais parler de truc rapide pour vous permettre de résoudre ce problème.
  • Ajouter une couleur à la liste déroulante
  • Dans Excel, créer une liste déroulante peut vous aider beaucoup, et parfois, vous devez coder par couleur les valeurs de la liste déroulante en fonction de la sélection correspondante. Par exemple, j'ai créé une liste déroulante des noms de fruits, lorsque je sélectionne Apple, j'ai besoin que la cellule soit automatiquement colorée en rouge, et lorsque je choisis Orange, la cellule peut être colorée en orange.
  • Colorer les lignes alternatives pour les cellules fusionnées
  • Il est très utile de formater des lignes alternées avec une couleur différente dans une grande quantité de données pour que nous analysions les données, mais, parfois, il peut y avoir des cellules fusionnées dans vos données. Pour mettre en surbrillance les lignes en alternance avec une couleur différente pour les cellules fusionnées, comme illustré ci-dessous, comment pourriez-vous résoudre ce problème dans Excel ?
Comments (237)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
嗨~版主好,我用了VBA 模塊,但是完全沒有動靜,沒有出現顏色儲存格的統計數量,Count欄一片空白~~請問是為什麼呢?跟office版本有關嗎?謝謝
This comment was minimized by the moderator on the site
嗨,MINA,
文章中的VBA代碼,微軟office版本基本上都可以適用,我這代碼可以正常使用。 如果你那邊還用不了,可以上傳你的文件,我們可以幫忙看看哪裡的問題,謝謝!
This comment was minimized by the moderator on the site
I am using =IF(D272>F272,D272-F272,if(F272>D272,F272-D272,"")) formula for subtraction, and I want it will coloured also??
This comment was minimized by the moderator on the site
Hi namrata,
Do you want to fill color for the result of your formula?
So, if the result is D272-F272, you want it, say, red; If the result is F272-D272, you want it, say, green; If blank, blank?
Amanda
This comment was minimized by the moderator on the site
I ran into problems when trying to run the function. Macro errors telling me: No RETURN() or HALT() function found on macro sheet. perhaps somebody could assist here. ThanksPaul
This comment was minimized by the moderator on the site
Hi, How to make a diagram based on the colors in the table? For example, I want to count all the red, green and yellow colors in the cells in a table and make a diagram. How to do this? Please
This comment was minimized by the moderator on the site
Is there a way to count different color backgrounds from conditional formatting? The current code as of 7/14/2020 counts them all as default yellow or not at all.
This comment was minimized by the moderator on the site
Hi Dusty,
You can try the Count by Color feature of Kutools for Excel. This feature will help you quickly calculate (count, sum, average, etc.) cells by cell background color or font color, no matter they are formatted by conditional formatting or solidly format.
This comment was minimized by the moderator on the site
Did anyone find a solution to auto-refresh? I have to manually refresh for it to update. Otherwise, it works great!
This comment was minimized by the moderator on the site
Hi Dennis,
By default, formulas are calculated in Excel until you are turning off the Automatic Formula Calculation. You can enable it by clicking Formulas > Calculation Options > Automatic.
This comment was minimized by the moderator on the site
Anyone have tips on a max by color VBA?
This comment was minimized by the moderator on the site
Hi Natasha,VBA is good but hard to apply. But below methods may solve your work easily too.
Method 1: Use Find & Replace feature to select and statistic the color cells(1) Press Ctrl + H keys to open the Find and Replace dialog, and then enable the Find tab.
(2) In the dialog, click Options to show advanced find options.
(3) Then click Format > Choose Format From Cell, and select one of the specified color cells.
(4) Click Find All. Now all cells with the same fill color are found out and listed at the bottom of the dialog.
(5) Select one of found cells, and press Ctrl + A to select all found cells, so that these cells are selected in the worksheet.
(6) Now you can get the count, average, sum, min, max, etc. of these cells in the task bar.
Note: If a certain statistic result cannot be found one the taskbar, you can right click the task bar, and then tick the specified item to show it.

Method 2: Kutools for Excel
Kutools for Excel supports 30-day free trial. Therefore, you can download it and try its Count by color feature to solve your problem with several clicks only.
This comment was minimized by the moderator on the site
awesome fix! count by color over an entire sheet was just what i was looking for and your VBA code was tighter than others that i have looked at. Works like a charm. Thank you, and again, well done.
This comment was minimized by the moderator on the site
I copied and paste but calculation result is "0" why?? I am using Office 2016.
This comment was minimized by the moderator on the site
I try the same you this command =COUNTBYCELLCOLOR is counting only fill color but is not count by condition formating. Please help to improve code thx.
This comment was minimized by the moderator on the site
I've copied and pasted as stated and have used this formula for over a year but recently saved the workbook to a new name and now the function doesn't work! I can't figure out what the deal is and I'm losing hair and sleep over it! Loading the original workbook, the formula works like a charm but going back to the new one, it doesn't! I've loaded VBA and tried re-creating the function but it doesn't work. Using Office 2019 - any help appreciated.
This comment was minimized by the moderator on the site
#NAME clearly indicates some keyword is not used in 2019, say, Interior.ColorIndex. Try to figure out changing the code from minimum lines to the full by adding one by one or search for the keywords in Excel/VBA in 2019
This comment was minimized by the moderator on the site
I should probably have also noted that I get a #NAME? error in the cell in which I try to use the function.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations