Comment compter les cellules COLORÉES dans Excel (Guide étape par étape + VIDÉO)

Regarder la vidéo - Comment compter les cellules colorées dans Excel

Ne serait-ce pas génial s'il y avait une fonction qui pourrait compter les cellules colorées dans Excel ?

Malheureusement, il n'y a pas de fonction intégrée pour le faire.

MAIS…

Cela peut être facilement fait.

Comment compter les cellules colorées dans Excel

Dans ce tutoriel, je vais vous montrer trois façons de compter les cellules colorées dans Excel (avec et sans VBA) :

1. Utilisation de la fonction Filtre et SOUS-TOTAL
2. Utilisation de la fonction GET.CELL
3. Utilisation d'une fonction personnalisée créée à l'aide de VBA

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

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 qui sont visibles (après filtrage).

Supposons que vous ayez un ensemble de données comme indiqué ci-dessous :

Deux couleurs d'arrière-plan sont utilisées dans cet ensemble de données (vert et orange).

Voici les étapes qui comptent les cellules colorées dans Excel :

1. Dans n'importe quelle cellule sous l'ensemble de données, utilisez la formule suivante : =SOUS-TOTAL(102,E1:E20)
   
2. Sélectionnez les en-têtes.
3. Allez dans Données -> Trier et filtrer -> Filtrer. Cela appliquera un filtre à tous les en-têtes.
4. Cliquez sur l'une des listes déroulantes de filtres.
5. Allez dans « Filtrer par couleur » et sélectionnez la couleur. Dans l'ensemble 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.

Dès que vous filtrez les cellules, vous remarquerez que la valeur de la fonction SOUS-TOTAL change et renvoie uniquement le nombre de cellules visibles après filtrage.

Comment cela marche-t-il?

La fonction SUBTOTAL utilise 102 comme premier argument, qui est utilisé pour compter les cellules visibles (les lignes cachées ne sont pas comptées) dans la plage spécifiée.

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

Essayez-le vous-même… Téléchargez le fichier d'exemple

#2 Compter les cellules colorées à l'aide de la fonction GET.CELL

GET.CELL est une fonction Macro4 qui a été conservée pour des raisons de compatibilité.

Il ne fonctionne pas s'il est utilisé comme fonctions normales dans la feuille de calcul.

Cependant, cela fonctionne dans les plages nommées Excel.

Voir également: En savoir plus sur la fonction GET.CELL.

Voici les trois étapes pour utiliser GET.CELL pour compter les cellules colorées dans Excel :

• Créer une plage nommée à l'aide de la fonction GET.CELL
• Utilisez la plage nommée pour obtenir le code de couleur dans une colonne
• Utilisation du numéro de couleur pour compter le nombre de cellules colorées (par couleur)

Approfondissons et voyons quoi faire dans chacune des trois étapes mentionnées.

Création d'une plage nommée

• Allez dans Formules -> Définir le nom.
• Dans la boîte de dialogue Nouveau nom, saisissez :
  • Nom : GetColor
  • Portée : Cahier d'exercices
  • Fait référence à : =GET.CELL(38,Sheet1!$A2)
    Dans la formule ci-dessus, j'ai utilisé Feuille1!$A2 comme deuxième argument. Vous devez utiliser la référence de la colonne où vous avez les cellules avec la couleur de fond.

Obtenir le code couleur pour chaque cellule

Dans la cellule adjacente aux données, utilisez la formule =GetColor

Cette formule renverrait 0 s'il n'y a PAS de couleur d'arrière-plan dans une cellule et renverrait un nombre spécifique s'il y a une couleur d'arrière-plan.

Ce numéro est spécifique à une couleur, donc toutes les cellules avec la même couleur d'arrière-plan reçoivent le même numéro.

Compter les cellules colorées à l'aide du code couleur

Si vous suivez le processus ci-dessus, vous auriez une colonne avec des nombres correspondant à la couleur de fond.

Pour obtenir le nombre d'une couleur spécifique :

• Quelque part en dessous de l'ensemble de données, attribuez la même couleur d'arrière-plan à une cellule que vous souhaitez compter. 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, j'ai utilisé la colonne A et j'utiliserai donc uniquement les cellules de la colonne "A".
• Dans la cellule adjacente, utilisez la formule suivante :

=COUNTIF($F$2:$F$20,ObtenirCouleur)

Cette formule vous donnera le nombre de toutes les cellules avec la couleur d'arrière-plan spécifiée.

Comment ça marche?

La fonction COUNTIF utilise la plage nommée (GetColor) comme critère. La plage nommée dans la formule fait référence à la cellule adjacente à gauche (dans la colonne A) et renvoie le code de couleur de cette cellule. Par conséquent, ce numéro de code de couleur est le critère.

La fonction COUNTIF utilise la plage ($F$2:$F$18) qui contient les numéros de code couleur de toutes les cellules et renvoie le nombre en fonction du nombre de critères.

Essayez-le vous-même… Téléchargez le fichier d'exemple

# 3 Compter en couleur à l'aide de VBA (en créant une fonction personnalisée)

Dans les deux méthodes ci-dessus, vous avez appris à compter les cellules colorées sans utiliser VBA.

Mais, si vous êtes d'accord avec l'utilisation de VBA, c'est la plus simple des trois méthodes.

En utilisant VBA, nous créerions une fonction personnalisée, qui fonctionnerait comme une fonction COUNTIF et renverrait le nombre de cellules avec la couleur d'arrière-plan spécifique.

Voici le code :

'Code créé par Sumit Bansal à partir de https://trumpexcel.com Fonction GetColorCount (CountRange As Range, CountColor As Range) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Set rCell = CountRange pour chaque rCell In CountRange If rCell.Interior.ColorIndex = CountColorValue Then TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount End Function

Pour créer cette fonction personnalisée :

• Avec votre classeur actif, appuyez sur Alt + F11 (ou faites un clic droit sur l'onglet de la feuille de calcul et sélectionnez Afficher le code). Cela ouvrirait l'éditeur VB.
• Dans le volet gauche, sous le classeur dans lequel vous travaillez, cliquez avec le bouton droit sur l'une des feuilles de calcul et sélectionnez Insérer -> Module. Cela insérerait un nouveau module. Copiez et collez le code dans la fenêtre de code du module.
• Double-cliquez sur le nom du module (par défaut le nom du module dans Module1) et collez le code dans la fenêtre de code.
• Fermez l'éditeur VB.
• C'est ça! Vous avez maintenant une fonction personnalisée dans la feuille de calcul appelée GetColorCount.

Pour utiliser cette fonction, utilisez-la simplement comme n'importe quelle fonction Excel normale.

Syntaxe: =GetColorCount(CountRange, CountColor)

• CountRange : la plage dans laquelle vous souhaitez compter les cellules avec la couleur d'arrière-plan spécifiée.
• CountCouleur : la couleur pour laquelle vous voulez compter les cellules.

Pour utiliser cette formule, utilisez la même couleur d'arrière-plan (que vous souhaitez compter) dans une cellule et utilisez la formule. L'argument CountColor serait la même cellule où vous entrez la formule (comme indiqué ci-dessous):

Noter: Puisqu'il y a un code dans le classeur, enregistrez-le avec une extension .xls ou .xlsm.

Essayez-le vous-même… Téléchargez le fichier d'exemple

Connaissez-vous un autre moyen de compter les cellules colorées dans Excel ?

Si oui, partagez-le avec moi en laissant un commentaire.