Contrairement aux idées reçues, il n'est pas obligatoire de créer une macro Excel pour compter le nombre de cellules rouges ou jaunes d'un tableau, ou connaître le chiffre d'affaires total des cellules en bleu. Démonstration !

Le langage VBA ne vous est pas très familier ? Alors cette astuce vous sera sûrement très utile si pour effectuer des calculs en fonction de la mise en forme des cellules d'un tableau. En effet, depuis de nombreuses versions, Excel est capable de traiter le contenu, mais également le format d'une cellule, avec la fonction LIRE.CELLULE. Cette fonction ne peut être utilisée qu'à l'intérieur d'un champ nommé et renvoie VRAI ou FAUX, selon la condition, mais également l'index couleur, ce qui ouvre de multiples possibilités de calculs sans avoir recours à des codes VBA incompréhensibles pour les débutants. Pour commencer, nous vous proposons une méthode encore plus facile, en activant les filtres d'Excel.

Comment faire des calculs selon la couleur des cellules grâce aux filtres d'Excel ?

Si vous avez juste besoin de connaître le nombre de cellules colorées en rouge dans un tableau, ou le total des valeurs inscrites dans ces cellules en rouge, pensez aux filtres d'Excel, qui pourraient peut-être vous suffire !

• Sélectionnez au minimum la cellule contenant l'en-tête de colonne (ci-dessous la cellule A1) ou même toute la plage de cellules concernées et, dans l'onglet Données, cliquez sur Filtrer.

• Maintenant que les filtres sont activés, cliquez sur le triangle noir à droite de l'en-tête de colonne puis sur Filtrer par couleur. Excel vous montre les couleurs des cellules dans la plage filtrée, sélectionnez la couleur qui vous intéresse, ou Aucun remplissage.

• À présent, vous voyez par exemple toutes les cellules en rouge, les autres sont masquées.

• Pour connaître le total des seules cellules en rouge en colonne A, la fonction =SOMME(A:A) ne conviendrait pas. Elle vous donnera le total de toutes les cellules, y compris celles masquées (dans notre exemple, celles en jaune ou sans remplissage, donc, en plus de celles sur fond rouge, pour un total général de 80).
• Utilisez plutôt la formule =SOUS.TOTAL(9;A:A) qui vous donnera uniquement le total des cellules affichées. La valeur 9 correspond à une somme, car la fonction SOUS.TOTAL peut aussi calculer le nombre de cellules, la moyenne, le min, le max, etc. Reportez-vous à l'aide sur la fonction SOUS.TOTAL pour en connaître tous les arguments. 

• Les filtres ne vous permettent de voir le résultat que d'une couleur à la fois. Si vous préféreriez voir en même temps le total de chaque couleur, suivez les conseils qui suivent.  

Comment connaître la couleur d'une cellule dans une formule Excel ?

Pour connaître la couleur de fond d'une cellule colorisée manuellement, commencez par sélectionner la cellule où sera affiché l'index couleur...

• Ouvrez le Gestionnaire de noms dans l'onglet Formules, et créez un champ.
• Définir un nom et le nommer, par exemple : Test
• Dans Référence, saisir une formule du type : =LIRE.CELLULE(code fonction;adresse cellule)
• Le code fonction pour identifier une couleur est le 63.
• En clair, pour connaître l'index couleur de la cellule A1, la formule dans le champ sera =LIRE.CELLULE(63;A1)
• Dans la cellule active écrire =Test
• Pour connaître l'index couleur d'une police, le code est 24, la formule sera =LIRE.CELLULE(24;A1)

• Également, pour tester si la police est en gras, la syntaxe LIRE.CELLULE renverra VRAI ou FAUX et le code fonction est 20. La formule sera =LIRE.CELLULE(20;A1)

• A partir de ce résultat, toutes sortes de calculs peuvent se faire simplement. Par exemple, pour compter les cellules de la plage A1 à A20 colorisées en rouge, on active une cellule exemple C1 ; on crée le champ dans le Gestionnaire des noms =LIRE.CELLULE(63;A1) et en cellule C1, la formule sera =Test
• Copiée-collée vers le bas, cette formule renverra l'index couleur des cellules en-dessous.
• La formule finale pour compter les cellules rouges sera :
  =NB.SI(C1:C20;3)
• Pour faire la somme des valeurs contenues dans les cellules rouges, plusieurs formules basiques :
  =SOMME.SI(C1:C20;3;A1:A20)
  =SOMMEPROD((C1:C20=3)*(A1:A20))
  =SOMME.SI.ENS(A1:A20;C1:C20;3)
• Il est possible, à partir de cet exemple, d'imaginer toutes sortes de calculs sans code VBA. Seul bémol, le fichier devra être enregistré avec l'extension XLS ou XLSM, car la syntaxe LIRE.CELLULE est considérée par Excel comme une ancienne Macro EXCEL4.
• Voici la liste des codes de la fonction LIRE.CELLULE :

1 Renvoie la référence absolue de la cellule
2 Renvoie Numéro de ligne
3 Renvoie Numéro de colonne
4 Renvoie le type de référence de la cellule
5 Renvoie le contenu de la cellule
6 Renvoie la référence, sous forme de texte, au format A1 ou L1C1, selon les paramètres régionaux
7 Renvoie le Format de nombres de la cellule, sous forme de texte (exemple, "jj/mm/aaaa" ou "Standard") unique possibilité avec Excel de tester un format date
8 Renvoie le numéro indiquant l'alignement horizontal de la cellule :
  1 = Standard
  2 = Gauche
  3 = Centré
  4 = Droite
  5 = Recopié
  6 = Justifié
  7 = Centré sur plusieurs colonnes
9 Renvoie le numéro indiquant le style de bordure gauche assigné à la cellule :
  0 = Pas de bordure
  1 = Bordure fine
  2 = Bordure moyenne
  3 = Bordure en tirets
  4 = Bordure en pointillé
  5 = Bordure épaisse
  6 = Bordure double
  7 = Bordure en filet
10 Renvoie le numéro indiquant le style de bordure droite assigné à la cellule
11 Renvoie le numéro indiquant le style de bordure supérieure assigné à la cellule
12 Renvoie le numéro indiquant le style de bordure inférieure assigné à la cellule
13 Renvoie le numéro indiquant le motif de la cellule, renvoie 0 si aucun motif
14 Si la cellule est verrouillée, renvoie VRAI ; sinon, FAUX
15 Si la formule de la cellule est masquée, renvoie VRAI ; sinon, FAUX
16 Teste la largeur de la cellule d'un tableau, largeur standard renvoie VRAI, sinon renvoie FAUX
17 Hauteur de ligne de la cellule, en points
18 Nom de la police
19 Taille de la police, en points
20 Teste caractères gras de la cellule, renvoie VRAI ; sinon, FAUX
21 Teste caractères en italique de la cellule, renvoie VRAI ; sinon, FAUX
22 Teste si les caractères sont soulignés, renvoie VRAI ; sinon, FAUX
23 Teste si la cellule est en caractère barré, renvoie VRAI ; sinon, FAUX
24 Renvoie l'index couleur de la police
25, 26, 27,28, 29, 30 et 31 ne sont pas générés par Microsoft Excel pour Windows
32 Nom du classeur et de la feuille contenant la cellule active sous la forme [Classeur1]Feuil1
33 Teste le renvoi à la ligne, renvoie VRAI ; sinon, FAUX
34 Renvoie l'index couleur de bordure gauche sous forme d'un nombre
35 Renvoie l'index couleur de bordure droite sous forme d'un nombre
36 Renvoie l'index couleur de bordure supérieure sous forme d'un nombre
37 Renvoie l'index couleur de bordure inférieure sous forme d'un nombre
38 Teste premier plan ombré sous forme d'un nombre
39 Teste Fond ombré sous forme d'un nombre
40 Style de la cellule, sous forme de texte
41 Renvoie la formule de la cellule active sans la convertir (utile pour les feuilles macro internationales)
42 La distance horizontale, mesurée en points, à partir du bord gauche de la fenêtre active jusqu'au bord gauche de la cellule
43 La distance verticale, mesurée en points, à partir du bord supérieur de la fenêtre active jusqu'au bord supérieur de la cellule
44 La distance horizontale, mesurée en points, à partir du bord gauche de la fenêtre active jusqu'au bord droit de la cellule
45 La distance verticale, mesurée en points, à partir du bord supérieur de la fenêtre active jusqu'au bord inférieur de la cellule
46 Si la cellule contient une annotation texte, renvoie VRAI ; sinon, FAUX
47 Si la cellule contient une annotation audio, renvoie VRAI ; sinon, FAUX
48 Si la cellule contient une formule, renvoie VRAI ; si elle contient une constante, renvoie FAUX
49 Si la cellule appartient à un tableau, renvoie VRAI ; sinon, FAUX
50 Renvoie le numéro indiquant l'alignement vertical de la cellule :
  1 = Haut
  2 = Centré
  3 = Bas
  4 = Justifié
51 Renvoie le numéro indiquant l'orientation verticale de la cellule :
  0 = Horizontale
  1 = Verticale
  2 = Vers le haut
  3 = Vers le bas
52 Le caractère préfixe de la cellule ou un texte vide ('') si la cellule n'en contient pas
53 Renvoie le contenu de la cellule sous forme de texte, y compris les nombres
54 Renvoie le nom de la vue du tableau croisé dynamique
55 Renvoie la position d'une cellule dans la vue du tableau croisé dynamique
  0 = étiquette de ligne
  1 = étiquette de colonne
  2 = en-tête de page
  3 = étiquette de données
  4 = élément de ligne
  5 = élément de colonne
  6 = élément de page
  7 = élément de données
  8 = corps du tableau
56 Renvoie le nom du champ de la cellule active qui se trouve dans la vue du tableau croisé dynamique
57 Renvoie VRAI si les caractères dans la cellule sont mis en forme exposant, sinon, renvoie FAUX
58 Renvoie le style de la police, Gras ou Italique
59 Renvoie le numéro du style de soulignement :
  1 = Aucun
  2 = Simple
  3 = Double
  4 = Comptabilité simple
  5 = Comptabilité double
60 Renvoie VRAI si les caractères dans la cellule sont mis en forme indice ; sinon, renvoie FAUX.
61 Renvoie le nom de l'élément du tableau croisé dynamique de la cellule active
62 Renvoie le nom du classeur ou de la feuille en cours sous la forme [Classeur]Feuil1
63 Renvoie l'index couleur de l'arrière-plan de la cellule
64 Renvoie l'index couleur du premier plan de la cellule
66 Renvoie le nom du classeur contenant la cellule active, sous la forme Classeur.XLS.

• Pour actualiser le calcul, il faut presser la touche F9 ou apporter dans les propriétés de la feuille une part de code VBA :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Calculate
End Sub
Ci-dessous, un exemple de fichier XLS pouvant être lu par toutes les versions d'Excel. Il est préférable de l'Enregistrer sous avec l'extension XLSM pour les version Excel 2007 et supérieures.
https://www.cjoint.com/c/GKsnRVQsSaj

Merci à Mike-31, Ana Sparadi et pintuda qui sont à l'origine de cet article.