Tutoriel Excel : compter et additionner les cellules par couleur ?

Plan de l'article

Introduction

Microsoft Excel est un outil essentiel dans le monde des affaires actuel pour la gestion des données. Mis à part

ses fonctionnalités connues, il possède certaines caractéristiques sous-estimées qui peuvent être très utiles. Une telle fonctionnalité est la capacité à compter ou à sommer des cellules basées sur leur couleur.

La coloration des cellules est souvent utilisée pour mettre en évidence certaines informations ou catégories dans les bases de données volumineuses. Apprendre à compter et à additionner les cellules par couleur de police ou de cellule dans Excel, est donc un atout. Que les cellules soient colorées manuellement ou par une mise en forme conditionnelle, ces méthodes sont efficaces.

Par exemple, si vous avez une feuille de calcul utilisant diverses couleurs de remplissage et de police, vous pourriez vouloir savoir combien de cellules sont colorées d'une certaine manière. De plus, si vos cellules contiennent des chiffres, vous pouvez sommer automatiquement les cellules de la même couleur. Par exemple, vous pouvez calculer la somme de toutes les cellules rouges.

Néanmoins, il est décevant de noter que Excel ne possède pas de fonction intégrée pour compter les cellules colorées. Heureusement, il existe des solutions de contournement simples et efficaces. Par exemple, la fonction NB dans Excel compte les cellules contenant des nombres, mais elle ne compte pas les cellules colorées.

Dans ce guide, nous allons découvrir comment utiliser trois méthodes pour compiler ces informations. La première méthode utilise le filtre et la fonction SOUS-TOTAL, la deuxième fait appel aux fonctions NB.SI et LIRE.CELLULE. Enfin, la troisième technique exploite la puissance du langage de programmation VBA pour créer une fonction personnalisée.

Que vous soyez un débutant ou un utilisateur expérimenté d'Excel, ce tutoriel vous offrira de nouvelles perspectives sur l'optimisation de votre gestion des données. Vous serez surpris par ce que vous pouvez accomplir avec un peu de connaissances et de créativité.

1. Comment compter les cellules colorées dans Excel ?

Dans le cadre d’une feuille de calcul Excel, on a plusieurs méthodes pour effectuer le compte des cellules colorées. Ces méthodes sont :

L’application de la fonction "SOUS.TOTAL", associée au filtrage des données. Cette option nécessite de posséder une certaine maitrise du filtrage dans Excel.
L'utilisation des fonctions "NB" et "LIRE.CELLULE". Cette méthode est plus directe et est accessible à tous les utilisateurs d'Excel, peu importe leur niveau d’expertise.
L’utilisation d'un code VBA. C'est une option plus avancée qui nécessite une connaissance de codage en VBA. Elle offre plus de souplesse et de précision dans le comptage de cellules colorées.

Rappelons que le choix de votre méthode dépendra de votre aisance avec les outils Excel et de la complexité de votre feuille de calcul.

1.1. Méthode 1 : Comptage des cellules colorées avec filtre et fonction SOUS-TOTAL

Il est possible de dénombrer, dans Excel, les cellules selon leur couleur de remplissage, en utilisant un filtre couplé à la fonction « sous-total ». Cette approche est très utile, notamment dans le cadre de l'analyse des performances commerciales.

Prenons un exemple : nous avons le registre des ventes de dix commerciaux en septembre. Certaines cellules ont été coloriées : en orange, pour signaler les vendeurs qui n'ont pas atteint l'objectif mensuel de 7 000 DH, et en vert pour ceux qui ont obtenu un bonus.

Pour compter ces cellules colorées, deux étapes sont nécessaires :

Premièrement, il faut appliquer un filtre selon la couleur voulue.
Ensuite, utiliser la fonction 'SOUS.TOTAL' pour dénombrer les cellules visibles, c'est-à-dire celles qui ont été retenues par le filtre.

Si l'on veut, par exemple, savoir combien de vendeurs ont réalisé un chiffre d'affaires inférieur à l'objectif, voici la marche à suivre :

Sélectionnez la cellule B12.
Dans celle-ci, entrez la formule =SOUS.TOTAL(102, B2:B11), qui vous donnera le nombre de cellules visibles.

Sélectionnez ensuite toutes les cellules de votre tableau, de A1 à B11.
Sur le menu principal, cliquez sur l’onglet « Données» puis dans le groupe « Trier et filtrer», pressez "Filtrer".
Une liste déroulante apparaîtra en B1. Cliquez dessus.

Optez pour l’option « Filtrer par couleur» et choisissez la couleur orange.

Une fois le filtre appliqué, la valeur affichée en B12, grâce à la fonction SOUS.TOTAL, correspondra uniquement aux cellules visibles de couleur orange.

L'argument 102 dans la fonction SOUS.TOTAL est spécifique à cette fonction : il désigne le comptage des cellules visibles uniquement. Si aucune couleur n'est sélectionnée, la valeur renvoyée sera de 10, le nombre total de vendeurs. Une fois le filtre orange appliqué, elle sera ajustée pour ne compter que les vendeurs sous l'objectif.

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

La fonction LIRE.CELLULE, bien qu'ancienne, demeure utilisable. Nous allons l'exploiter ici dans un contexte Excel, pour effectuer un décompte de cellules colorées.

Concrètement, nous allons définir une plage à l'aide de LIRE.CELLULE, récupérer le code couleur de chaque cellule, puis totaliser le nombre de cellules possédant le code couleur recherché. A noter toutefois que LIRE.CELLULE est une fonction Macro 4 et ne s'allie pas aux fonctions classiques. Elle s'associe en revanche aux plages nommées.

Etape 1: Créez une plage nommée :

Dans le module Formules, sélectionnez Définir un nom dans l'onglet Noms définis.

A l'aide de la boîte de dialogue Nouveau nom, créez une plage appelée CodeCouleur pour le classeur. Dans le champ Référence, introduisez la formule "=LIRE.CELLULE(38;$B2)". Cette formule prend en compte la colonne où se trouvent les cellules colorées.

=LIRE.CELLULE(38; $B2)

Etape 2 : Assignation de code couleur.

Introduisez la formule "=CodeCouleur" dans la cellule C2, puis faites défiler la poignée de recopie jusqu'à C11.

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

Si aucune couleur de remplissage n'est attribuée à une cellule, cette formule renverra 0. Si une couleur est assignée, un numéro spécifique à cette couleur sera généré.

Etape 3 : Décompte des cellules colorées.

En suivant ces étapes, il est possible d'obtenir une colonne de numéros correspondant à chaque code couleur. Pour effectuer le décompte d'une couleur particulière, suivez ces instructions :

Attribuez la même couleur de remplissage que vous souhaitez dénombrer à une cellule de la même colonne que celle où la plage a été créée (dans notre exemple, la colonne B). Ensuite, dans la cellule adjacente, insérez la formule

=NB.SI($C$2:$C$11; CodeCouleur)

Cette formule recherche le nombre de cellules colorées en orange, correspondant à des ventes inférieures à 7000 DH, et renvoie le chiffre 5.

La fonction NB.SI utilise le nom de plage "CodeCouleur" comme critère. Ce nom fait référence à la cellule qui est adjacente à gauche (colonne B) et renvoie son code couleur. Ce nombre est ensuite utilisé comme critère de décompte. Enfin, NB.SI exploite la plage "$C$2:$C$11" pour créer son décompte.

Méthode 3 : Compter les couleurs en utilisant VBA (en créant une fonction personnalisée)

Une alternative pour compter les cellules colorées dans Excel est d'utiliser une fonction personnalisée VBA.

Créer une fonction VBA personnalisée fonctionne de manière similaire à la fonction NB.SI. Elle renverra le nombre de cellules contenant une couleur spécifique. La syntaxe à suivre est :

=CompterCouleur(PlageCouleur; Couleur)

Cette syntaxe fonctionnera comme n'importe quelle autre fonction régulière.

Dans cette syntaxe, "Couleur" est la couleur des cellules que vous voulez compter. "PlageCouleur" est la plage où vous voulez compter les cellules avec une couleur d'arrière-plan précise.

Pour créer cette fonction, suivez les étapes ci-dessous :

Appuyez sur Alt + F11 pour ouvrir l'éditeur VBA lorsque vous ouvrez le classeur contenant vos données. Vous pouvez également ouvrir l'éditeur en cliquant avec le bouton droit sur l'onglet du tableau et en choisissant "Visualiser le code".

Cliquez avec le bouton droit sur l'une des feuilles de calcul sous le nom du classeur sur le volet gauche, sélectionnez "Insertion" et "Module". Cela créera un nouveau module.

Il doit ensuite y coller le code, que l'on trouve dans la fenêtre du 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

Le code que nous proposons définit une fonction nommée "CompterCouleur" qui possède deux arguments : PlageCouleur et Couleur. La valeur de la couleur de remplissage contenu dans la cellule B2 est enregistrée dans CodeCouleur.

Nous exécutons ensuite une boucle FOR où le NbrCouleur sera incrementé si la couleur de la cellule correspond à celle de CodeCouleur. Enfin, la valeur de NbrCouleur est renvoyée par la fonction. Il s'agit du nombre de cellules avec la même couleur de fond.

Une fois le code entré, fermez l'éditeur VB. Vous possédez maintenant une fonction personnalisée appelée CompterCouleur dans votre feuille de calcul.
Utilisons maintenant cette formule. Entrez la couleur de fond que vous reconnaîtrez dans une cellule (ici, B13), et mettez la formule suivante dans la cellule adjacente (C13):

=CompterCouleur(B2:B11;B13)

B13 sera la cellule avec la couleur que vous souhaitez compter. B2:B11 est la plage de cellules où vous souhaitez compter la couleur.

Cela renverra le nombre de vendeurs qui réalisent des ventes inférieures à 7000 DH en septembre, soit 5 personnes.

Il est à noter que s'il faut colorer manuellement des cellules supplémentaires une fois que le code VBA a été appliqué, la somme et le nombre de cellules de couleur ne seront pas recalculés automatiquement. Ce n'est pas un bug de code, mais le comportement normal des macros Excel, scripts VBA et fonctions personnalisées.

Ces fonctions sont enclenchées seulement lorsqu'il y a modification des données sur la feuille de calcul. Excel ne voit pas un changement de couleur de police ou de cellule comme une modification des données. Par conséquent, après avoir appliqué une couleur de fond à des cellules à la main, il suffit de mettre le curseur sur n'importe quelle cellule, d'appuyer sur F2 et de valider avec "Entrée" pour que la somme et le nombre se mettent à jour.

Enfin, si votre classeur contient du code, enregistrez-le avec une extension .xls ou .xlsm.

2. Additionner des cellules selon leur couleur avec Visual Basic pour Applications (VBA)

Par la suite, nous allons apprendre à réaliser la somme de cellules en se basant sur la couleur de leur police. Nous utiliserons ici un code VBA dans Excel.

2.1. Effectuer une somme selon la couleur de cellules sous Excel grâce à VBA

Avec Visual Basic for Applications ou VBA, il est possible de sommer des valeurs de cellules selon leur couleur. En effet, cette manipulation vous permet d'obtenir une fonction personnalisée qui effectue la somme des cellules dans Excel selon leur couleur de fond. Pour ce faire, veuillez suivre les instructions suivantes.

Pour débuter, ouvrez l'éditeur VBA. Utilisez le raccourci Alt + F11, ou faites un clic gauche sur « Visual Basic» apparaissant sous l'onglet « DÉVELOPPEUR ».
Ensuite, créez un nouveau module. Pour cela, effectuez un clic droit sur une feuille de calcul de votre classeur. Sélectionnez ensuite "Insertion", puis "Module".

A présent, copiez et collez le code VBA ci-après dans la fenêtre de code. N'oubliez pas de sauvegarder en cliquant sur le bouton "Enregistrer", puis 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

Une fois le code VBA inséré et enregistré, retournez sur la feuille comportant vos données. Tapez la formule désormais disponible grâce au code VBA dans une cellule vide: =SommeParCouleur(B2:B11;B13). Pour valider, pressez la touche Entrée.

=SommeParCouleur(B2:B11;B13)

2.2. La procédure de somme des cellules par couleur de remplissage avec code VBA

Dans cette section, nous sommes amenés à réaliser l’addition de cellules en fonction de la couleur de leur police. Cela se fait dans une plage de cellules spécifique pour une feuille de calcul Excel. Nous allons coder une fonction définie par l'utilisateur afin d’obtenir un résultat facile à utiliser. Vous n’aurez qu’à suivre les étapes ci-dessous :

Démarrons par l’ouverture de l’éditeur VBA. Utilisez le raccourci clavier Alt + F11. Vous pouvez aussi cliquer sur « Visual Basic » dans l'onglet « DÉVELOPPEUR ».
Ensuite, effectuez un clic droit sur une feuille de calcul dans le classeur. Choisissez « Insertion » puis « Module » pour créer un nouveau module.

Le code VBA à coller dans la fenêtre de code se trouve ci-dessous.

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

Une fois le code collé, revenez sur votre feuille de calcul. Ensuite, saisissez la formule suivante dans une cellule vide. Tapez :

=SommePrCoulDeFond(B2:B11; B13)

3. Compter les cellules par couleur de police avec le code VBA

Dans cette section nous allons nous intéresser à comment compter les cellules en fonction de la couleur de police par le biais du code VBA. Plus précisément, nous ciblerons les cellules avec une couleur de police blanche présentes dans l'étendue B2:B11.

Pour cela, nous allons employer une fonction spécifique. Son détail est donné ci-dessous :

Fonction Publique CompterParCouleurDePolice(Plage As Range, Couleur As Range)
     Application.Volatile
     Pour chaque Cellule dans la Plage
          Si la couleur de la police de la Cellule est égale à celle de la Couleur, alors
              On ajoute 1 à CompterParCouleurDePolice
         Fin Si
     Suivant
Fin Fonction

Cette fonction parcourt chaque cellule dans une plage donnée. Si la couleur de la police de la cellule est identique à la couleur indiquée, la fonction ajoute 1 au compteur. À la fin, elle renvoie le nombre total de cellules avec la couleur de police spécifiée.

4. Guide pour une utilisation optimale des couleurs dans Excel

La manipulation des couleurs dans Excel peut améliorer la lisibilité et la clarté. En sélectionnant judicieusement, vous pouvez permettre aux utilisateurs d'identifier rapidement les informations essentielles. De plus, cela aidera à repérer facilement les données recherchées.

Astuces pour utiliser les couleurs dans Excel

Choisissez des couleurs distinctes pour représenter des informations différentes. Par exemple, utilisez le bleu pour les valeurs positives, le rouge pour les valeurs négatives et le vert pour les valeurs nulles. Assurez-vous que les couleurs sont agréables à regarder et ne sont ni trop vives ni trop contrastées.

Automatisation de la coloration des cellules

La mise en forme conditionnelle est un outil précieux. Elle permet d'automatiser la coloration des cellules, vous faisant gagner du temps. Vous pouvez l'utiliser pour colorer en rouge les cellules contenant des valeurs négatives.

Utilisation de la couleur pour améliorer la lisibilité

Utilisez des couleurs pour représenter différents types de données, comme les ventes, les dépenses et les profits. De plus, utilisez des couleurs pour mettre en évidence les données importantes, comme les valeurs maximales et minimales. Cela permet de créer un visuel attrayant, comme un graphique ou un diagramme.

Importance du choix des couleurs dans Excel

Le choix des couleurs dans Excel est essentiel. Il donne un aspect professionnel à vos tableaux. Les couleurs vives et attrayantes sont à privilégier pour un meilleur rendu. Ce choix contribue à une meilleure présentation et facilite l'analyse rapide des informations.

La mise en forme conditionnelle sur Excel: Outil précieux

La mise en forme conditionnelle sur Excel est pratique. Elle automatise la coloration des cellules et offre une meilleure lisibilité. De plus, elle simplifie le marquage des cellules, idéal pour la gestion des données. Ce dispositif optimise la gestion de vos données.

Vous pouvez rendre vos feuilles de calcul Excel plus faciles à lire et à comprendre en choisissant judicieusement les couleurs.

5. Questions fréquemment posées avec leurs Réponses

Voici les questions les plus fréquemment posées sur le sujet compter et additionner les cellules par couleur sur Excel, ainsi que leurs réponses :

Question : Comment compter le nombre de cellules colorées dans une plage donnée ?

Réponse : La première question concerne le comptage du nombre de cellules colorées dans une sélection précise. Grâce à la fonction COUNTA de Excel, cela est possible. La syntaxe de cette fonction est COUNTA(valeur1, valeur2, ...).

La valeur1 représente la première valeur à compter, la valeur2 est la seconde, et ainsi de suite. Pour compter toutes les cellules colorées dans une plage, par exemple de A1 à A100, la formule serait : COUNTA(A1:A100). Cette formule va chercher et compter toutes les cellules colorées dans la plage A1:A100.

Question : Comment compter et additionner les cellules colorées par couleur dans une plage donnée ?

Réponse : Pour compter et additionner les cellules colorées par couleur dans une plage donnée, vous pouvez utiliser la fonction COUNTIF. La syntaxe de la fonction COUNTIF est la suivante :

COUNTIF(range,criteria)

La plage est la plage de cellules à compter, et les critères sont les critères à utiliser pour compter les cellules. Par exemple, si vous souhaitez compter toutes les cellules colorées en rouge dans la plage A1:A100, vous pouvez utiliser la formule suivante :

COUNTIF(A1:A100,"red")

Cette formule comptera toutes les cellules colorées en rouge dans la plage A1:A100.

Pour additionner les valeurs des cellules colorées par couleur dans une plage donnée, vous pouvez utiliser la fonction SUMIF. La syntaxe de la fonction SUMIF est la suivante :

SUMIF(range,criteria,sum_range)

La plage est la plage de cellules à compter, les critères sont les critères à utiliser pour compter les cellules, et la plage de somme est la plage de cellules à additionner. Par exemple, si vous souhaitez additionner toutes les valeurs des cellules colorées en rouge dans la plage A1:A100, vous pouvez utiliser la formule suivante :

SUMIF(A1:A100,"red",A1:A100)

Cette formule additionnera toutes les valeurs des cellules colorées en rouge dans la plage A1:A100.

Question : Comment compter le nombre de cellules colorées dans Excel?

Réponse : Pour compter le nombre de cellules colorées, vous pouvez utiliser la fonction Excel COUNTA. Cette fonction prend en compte toutes les cellules non vides, y compris les cellules colorées dans une plage spécifique. Par exemple, pour une plage allant de A1 à A10 dans laquelle A2, A4, A6 et A8 sont colorées, utilisez cette formule : =COUNTA(A1:A10).

Question : Comment additionner les valeurs des cellules colorées dans Excel?

Réponse : Pour additionner les valeurs des cellules colorées, la fonction SUM d'Excel s'avère utile. Elle additionne les valeurs d'une plage donnée. Donc, pour une plage de A1 à A10, dont les cellules colorées sont A2, A4, A6 et A8, utilisez la formule suivante : =SUM(A2,A4,A6,A8).

Question : Comment filtrer les cellules colorées dans Excel?

Réponse : Pour filtrer les cellules colorées, vous pouvez utiliser la fonction FILTER base de données d'Excel. Elle affiche uniquement les cellules répondant à certains critères. Par exemple, pour filtrer les cellules rouges d'une plage allant de A1 à A10, la formule serait : =FILTER(A1:A10, (A1:A10="Rouge")).

Question : Comment mettre en forme les cellules colorées dans Excel?

Réponse : Pour cela, vous pouvez utiliser les options de mise en forme conditionnelle d'Excel. Ces options permettent d'appliquer un style spécifique selon des critères précis. Supposons que vous souhaitez mettre en surbrillance les cellules rouges à partir de la plage A1:A10. Vous pouvez utiliser cette série d'instructions : Sélectionnez la plage, cliquez sur Mise en forme conditionnelle, Nouvelle règle, Formater uniquement les cellules qui contiennent, Cellule contenant, Texte spécifique, égal à, "Rouge", choisissez votre mise en forme, puis OK.

Question : Comment trier les cellules colorées dans Excel?

Réponse : Pour trier les cellules colorées, vous pouvez utiliser la fonction SORT d'Excel. Elle trie les cellules dans une plage selon un certain ordre. Considérez que vous souhaitez trier les cellules d'une plage A1:A10 de manière croissante. Pour cela, utilisez la formule suivante : =SORT(A1:A10, 1, TRUE).

Question : Comment rechercher des cellules colorées dans Excel?

Réponse : En utilisant la fonction FIND d'Excel, il est possible de rechercher les cellules colorées. Cette fonction localise la première cellule correspondant à certains critères. Par exemple, pour trouver la première cellule rouge dans la plage A1:A10, la formule serait : =FIND("Rouge", A1:A10).

Conclusion

Télécharger le fichier Excel de ce tutoriel

Article publié le 18 Septembre 2020par Salim KHALIL