Comment compter et additionner par couleur de cellule sans VBA sur Excel
Dans ce tutoriel, je vais vous montrer comment compter et additionner automatiquement des cellules selon leur couleur de fond, sans avoir besoin de créer des macros VBA compliquées.
Pour cela, nous allons utiliser une fonction secrète d'Excel 4.0 qui fonctionne encore parfaitement aujourd'hui.
Cette technique va vous faire gagner un temps considérable si vous utilisez des codes couleurs pour organiser vos données.
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous gérons le suivi des candidatures pour un recrutement.
Ce tableau contient les informations essentielles sur chaque candidat qui a postulé à différents postes.
Pour chaque ligne, nous utilisons un code couleur simple.
- Nous appliquons un fond vert aux candidats retenus (Martin Dupont, Sophie Leroux, Camille Robert et Marie Laurent).
- Le fond orange est réservé aux candidats en attente (Thomas Bernard, Claire Dubois et Nicolas Simon).
- Le fond rouge marque les candidats refusés (Antoine Moreau, Julien Petit et Emma Michel).
L’objectif de ce tutoriel est double : compter automatiquement combien de candidats se trouvent dans chaque catégorie et calculer le montant moyen des salaires pour chaque statut.
Tout cela de manière automatique, sans filtrer manuellement.
2. Comprendre la fonction LIRE.CELLULE
La fonction LIRE.CELLULE provient d'Excel 4.0, sorti en 1992, qui disposait de son propre langage de macros.
Bien que Microsoft ait abandonné ce système au profit de VBA dès 1993, ces anciennes fonctions continuent de fonctionner dans les versions modernes.
LIRE.CELLULE peut extraire des informations qu'aucune fonction classique ne peut obtenir.
Par exemple, elle peut détecter la couleur de fond d'une cellule, la taille de police ou le type d'alignement. La syntaxe est :
=LIRE.CELLULE(no_type;référence)
Le premier argument « no_type » est un numéro entre 1 et 66 indiquant le type d'information à extraire.
Pour la couleur de remplissage, nous utilisons le numéro 63.
Le second argument « référence » est l'adresse de la cellule à analyser.
Lorsque nous utilisons =LIRE.CELLULE(63;A2), Excel renvoie un code numérique représentant la couleur.
Par exemple, le vert peut renvoyer 35, l'orange 46, le rouge 3, et l'absence de couleur renvoie 0.
Si nous essayons de taper cette formule directement dans une cellule, Excel affiche un message d'erreur.
C'est normal car LIRE.CELLULE n'est plus acceptée directement dans les cellules.
Cependant, Excel continue d'accepter ces fonctions macro dans les noms définis.
C'est cette porte dérobée que nous allons exploiter.
3. Créer le nom défini pour capturer la couleur
Ici, nous sélectionnons la cellule E7, c’est-à-dire celle située juste à droite de la première cellule dont nous souhaitons identifier la couleur (autrement dit, la cellule D7).
Ensuite, nous nous rendons dans l’onglet « Formules » du ruban Excel, puis nous cliquons sur « Gestionnaire de noms ».
Une nouvelle fenêtre s’ouvre, affichant la liste de tous les noms définis existants dans le classeur. Ces noms sont très pratiques pour créer des références dynamiques et réutilisables dans vos formules.
Nous cliquons ensuite sur « Nouveau », en haut à gauche, afin de créer un nouveau nom.
Dans le champ « Nom », nous saisissons COULEUR_GAUCHE.
Ce nom est explicite : il décrit précisément la fonction que nous allons lui attribuer, à savoir récupérer le code couleur de la cellule située immédiatement à gauche de celle où il est utilisé.
La portée reste sur Classeur, ce qui signifie que ce nom sera accessible depuis toutes les feuilles du fichier, et non pas uniquement depuis une feuille spécifique.
Dans le champ « Fait référence à », nous saisissons la formule suivante :
=LIRE.CELLULE(63;!D7)
Où :
- 63 correspond à l’argument utilisé par la fonction LIRE.CELLULE pour retourner le code couleur de fond (ou couleur de remplissage) de la cellule.
- !D7 indique que l’on souhaite lire la cellule située à gauche de la cellule active (ici, D7 par rapport à E7).
Le point d’exclamation (!) indique que la référence est relative à la feuille courante, et non figée dans une feuille précise. Si nous ne le mettons pas, Excel va automatiquement l’ajouter avec le nom de la feuille de calcul, ce qui reviendra exactement au même.
Nous n’utilisons pas de dollars ($) dans la référence (donc D7 et non $D$7) afin de préserver la nature relative de cette adresse.
C’est ce point qui va rendre notre nom dynamique : quelle que soit la cellule dans laquelle nous appellerons le nom COULEUR_GAUCHE, il se reportera toujours sur la cellule immédiatement à sa gauche, sans qu’il soit nécessaire de modifier la formule.
Nous cliquons enfin sur « OK » pour valider la création, puis sur « Fermer » pour quitter le Gestionnaire de noms.
4. Utiliser le nom défini dans notre tableau
Toujours depuis notre cellule E7, nous saisissons maintenant la formule :
=COULEUR_GAUCHE
Après avoir appuyé sur [Entrée], un nombre apparaît représentant le code couleur de A2.
Nous sélectionnons la cellule suivante, puis utilisons [Ctrl]+[C] pour copier.
Ensuite, nous sélectionnons les autres cellules et appuyons sur [Ctrl]+[V] pour coller.
Excel remplit automatiquement la colonne avec les codes couleurs.
Nous remarquons que différents nombres apparaissent : le même pour toutes les lignes vertes, le même pour les oranges, et le même pour les rouges.
Notre système de détection fonctionne parfaitement.
Chaque candidat a maintenant un code numérique correspondant à sa couleur.
5. Compter le nombre de candidats par statut
Nous créons un tableau de synthèse dans une zone vide, par exemple à partir de G1. En G1, nous tapons « Statut », en H1 « Couleur référence », et en I1 « Nombre ». Nous remplissons G2 avec « Retenu », G3 avec « En attente », et G4 avec « Refusé ».
Dans la colonne H, nous appliquons manuellement les couleurs de référence. H2 reçoit un fond vert, H3 un fond orange, et H4 un fond rouge. Ces cellules serviront de référence pour nos formules.
Nous ajoutons une colonne « Code ref » en J1 pour vérifier.
En J2, nous tapons =COULEUR_GAUCHE et copions en J3 et J4.
Les codes couleurs de nos cellules de référence s'affichent.
En I2, nous saisissons la formule de comptage : =NB.SI($B$2:$B$11;J2)
La fonction NB.SI compte les cellules remplissant un critère. Le premier argument $B$2:$B$11 est notre colonne de codes couleurs.
Les signes dollar fixent cette référence pour la copie.
Le second argument J2 est notre critère, le code couleur de référence.
Sans dollar, il deviendra J3 puis J4 lors de la copie.
Nous appuyons sur [Entrée] et voyons le nombre de candidats verts. Nous copions cette formule en I3 et I4. Le décompte exact de chaque catégorie apparaît : combien de candidats retenus, en attente et refusés.
6. Forcer la mise à jour automatique
Testons quelque chose.
Changeons Thomas Bernard d'orange à vert.
Nous constatons que les chiffres ne se mettent pas à jour.
C'est normal car LIRE.CELLULE est une fonction macro, pas une fonction standard.
Nous pouvons forcer le recalcul avec [F9], mais c'est peu pratique.
La solution consiste à inclure une fonction volatile dans notre nom défini.
Les fonctions volatiles comme MAINTENANT() se recalculent automatiquement.
Nous retournons dans le Gestionnaire de noms, sélectionnons « COULEUR_GAUCHE » et cliquons sur « Modifier ».
Nous modifions la formule en ajoutant : +MAINTENANT()*0
La formule complète devient :
= LIRE.CELLULE(63;!D7)+MAINTENANT()*0
MAINTENANT() renvoie la date et l'heure actuelles et force le recalcul.
En multipliant par zéro, nous annulons l'effet numérique tout en conservant le caractère volatile.
Nous cliquons sur « OK » puis « Oui » pour confirmer.
Testons à nouveau. Nous changeons une couleur et après quelques secondes, les formules se mettent à jour automatiquement. La mise à jour se déclenche dès qu'Excel recalcule la feuille. Si nécessaire, [F9] force le recalcul immédiat.