EXCEL: Compter ou additionner les cellules colorisées manuellement

Décembre 2017


Contrairement aux idées reçues il n’est pas obligatoire de traiter ce problème uniquement par VBA, il existe depuis de nombres versions d’Excel la possibilité de traiter le contenu mais également le format d’une cellule avec la syntaxe LIRE.CELLULE.
Cette syntaxe ne peut être utilisée qu’à l’intérieur d’un champ nommé et renvoie VRAI ou FAUX suivant la condition mais également l’index couleur ce qui ouvre des possibilités de calcul sans avoir recours à des codes VBA incompréhensibles pour certains.
Par exemple pour connaitre la couleur de font d’une cellule colorisée manuellement il suffit d’activer la cellule ou sera afficher l’index couleur, d’ouvrir le gestionnaire des noms et créer un champ
Définir un nom et le nommer exemple Test
Dans référence saisir la formule =NOM de la syntaxe(code fonction;adresse cellule)
Le code fonction pour identifier une couleur est le 63
En clair pour connaitre l’index couleur de la cellule A1 la formule dans le champ sera =LIRE.CELLULE(63;A1)
Dans la cellule active écrire =Test
Idem pour connaitre 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 et la formule sera =LIRE.CELLULE(20;A1) etc...
A partir de ce résultat toute sorte de calcul peut se faire simplement, 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) en cellule C1 la formule sera =Test et incrémentée vers le bas et renverra l’index couleur
La formule finale pour compter les cellules rouge sera
=NB.SI(C1:C20;3)
Pour faire la somme des valeurs contenues dans les cellules rouge plusieurs formules basique
=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 toute sorte de calcul sans code VBA, seul bémol le fichier devra être enregistré avec l’extension XLS ou XLSM la syntaxe LIRE.CELLULE encore considérée par Excel comme Macro EXCEL4


Liste des codes fonction de la syntaxe
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 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 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 le caractère dans la cellule si caractères 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 convient de 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 joint un exemple de fichier XLS pour être lu sur toutes les versions Excel, il est préférable de l' Enregistrer Sous avec l'extension XLSM pour les version Excel 2007 et supérieur
http://www.cjoint.com/c/GKsnRVQsSaj

A voir également


Publié par Mike-31.
Ce document intitulé «  EXCEL: Compter ou additionner les cellules colorisées manuellement  » issu de CommentCaMarche (www.commentcamarche.net) est mis à disposition sous les termes de la licence Creative Commons. Vous pouvez copier, modifier des copies de cette page, dans les conditions fixées par la licence, tant que cette note apparaît clairement.
Liste déroulante en cascade sans plage nommée
Excel - Chemin et nom de stockage de feuille de calcul