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 dans un tableau, ou pour connaître le chiffre d'affaires total des cellules bleues. Manifestation!
Vous n'êtes pas très familier avec le langage VBA ? Cette astuce vous sera donc certainement utile si vous souhaitez effectuer des calculs basés sur le format des cellules du tableau. En effet, depuis de nombreuses versions, Excel est capable de traiter le contenu, mais aussi le format d'une cellule, avec la fonctionDÉPOSER. 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 aussi l'indice de couleur, ce qui ouvre de multiples possibilités de calcul sans recourir à des codes VBA incompréhensibles pour les débutants. Pour commencer, nous vous proposons une méthode encore plus simple, en activant les filtres Excel.
Si vous avez juste besoin de connaître le nombre de cellules colorées en rouge dans un tableau, ou les valeurs totales saisies dans ces cellules colorées en rouge, pensez aux filtres d'Excel, qui peuvent vous suffire !
- Sélectionnez au moins la cellule qui contient l'en-tête de colonne (sous la celluleA1) ou même toute la plage de cellules en question et, dans l'ongletDonnées, Cliquer surFiltre.
- Maintenant que les filtres sont activés, cliquez sur le triangle noir à droite de l'en-tête de colonne, puis cliquez surfiltrer par couleur. Excel affiche les couleurs des cellules dans la plage filtrée, sélectionnez la couleur qui vous intéresse ouNon rempli.
- Maintenant, vous voyez, par exemple, toutes les cellules en rouge, le reste est masqué.
- Pour trouver le total des globules rouges individuels dans la colonne A, la fonction=SOMA(A:A)ne conviendrait pas Cela vous donnera le total de toutes les cellules, y compris les cellules cachées (dans notre exemple, les jaunes ou vides, plus celles avec un fond rouge, pour un total général de 80).
- Utilisez plutôt la formule=SUS.TOTAL(9;A:A)qui vous donnera juste le nombre total de cellules affichées. la valeur que9correspond à une somme, car la fonction SOUS-TOTAL peut aussi calculer le nombre de cellules, la moyenne, le minimum, le maximum, etc. Recommence àAide de la fonction SOUS-TOTALSConnaître tous les arguments.
- Les filtres ne permettent de voir le résultat que d'une couleur à la fois, si vous préférez voir le total de chaque couleur en même temps, suivez les conseils ci-dessous.
Comment connaître la couleur d'une cellule dans une formule Excel ?
Pour connaître la couleur de fond d'une cellule colorée manuellement, commencez par sélectionner la cellule où sera affiché l'index des couleurs...
- ouvrir legestionnaire de nomet le ventreformules,et créer un champ.
- Définissez un nom et donnez-lui un nom, par exemple :Test
- emRéférence, saisissez une formule telle que :=GET.CELL(code fonction;adresse cellule)
- Le code de fonction pour identifier une couleur est 63.
- En clair, pour connaître l'indice de couleur de la cellule A1, la formule dans le champ sera=LIRE.CELLULE(63,A1)
- Dans la cellule active, tapez=tester
- Pour connaître l'indice de couleur d'une police, le code est 24, la formule sera=LIRE.CELLULE(24,A1)
De plus, pour tester si la police est en gras, la syntaxe READ.CELL renverra TRUE ou FALSE et le code de la fonction est 20. La formule sera =READ.CELL(20;A1)
- A partir de ce résultat, toutes sortes de calculs peuvent être effectués facilement : par exemple, pour compter les cellules de la plage A1 à A20 colorées en rouge, une cellule exemple C1 est activée ; nous créons le champ dansgestionnaire de nom =LIRE.CELLULE(63,A1)et dans la cellule C1 la formule sera=tester
- Copiée et collée, cette formule renverra l'index de couleur des cellules ci-dessous.
- La formule finale pour compter les globules rouges sera :
=NB.SI(C1:C20;3) - Pour additionner les valeurs contenues dans les globules rouges, plusieurs formules de base :
=GUESS(C1:C20,3,A1:A20)
=SOMMEPROD((C1:C20=3)*(A1:A20))
= POCHES(A1:A20;C1:C20;3) - Il est possible, à partir de cet exemple, d'imaginer toutes sortes de calculs sans code VBA, le seul inconvénient est que le fichier doit être enregistré avec l'extension XLS ou XLSM, puisque la syntaxe READ.CELL est considérée par Excel comme une ancienne Macro EXCEL4 .
- Voici la liste de codes pour la fonction READ.CELL :
1Renvoie la référence absolue de la cellule
2 Renvoie le numéro de ligne
3Renvoie le numéro de colonne
4Renvoie le type de référence de cellule
5Renvoie le contenu de la cellule
6Renvoie la référence, sous forme de texte, au format A1 ou R1C1, selon les paramètres régionaux.
7Renvoie le format numérique de la cellule, sous forme de texte (par exemple, "mm/jj/aaaa" ou "Standard") possibilité unique avec Excel de tester un format de date
8 Renvoie le nombre indiquant l'alignement horizontal de la cellule :
1= Par défaut
2= gaucho
3= Centralisé
4= Droite
5= Copier
6= Justifié
7= Centré sur plusieurs colonnes
9 Renvoie le nombre indiquant le style de bordure gauche attribué à la cellule :
0= pas de côtes
1= bord fin
2= bord médian
3= Bordure pointillée
4= bordure pointillée
5= Bord épais
6= double bordure
7= bord de maille
10Renvoie le nombre indiquant le style de bordure droite attribué à la cellule
11Renvoie le nombre indiquant le style de bordure supérieure attribué à la cellule
12Renvoie le nombre indiquant le style de bordure inférieure attribué à la cellule
13 Renvoie le nombre indiquant le motif de la cellule, renvoie 0 s'il n'y a pas de motif
14Si la cellule est verrouillée, retourne VRAI ; sinon FAUX
15Si la formule de la cellule est masquée, renvoie VRAI ; sinon FAUX
16Teste la largeur d'une cellule de tableau, la largeur par défaut renvoie VRAI, sinon renvoie FAUX
17Hauteur de la rangée de cellules, en points
18 Nom de la police
19Taille de la police, en points
20 Cellule de test en gras, renvoie VRAI ; sinon FAUX
21 Teste les caractères en italique dans la cellule, renvoie TRUE ; sinon FAUX
22Vérifie si les caractères sont soulignés, renvoie TRUE ; sinon FAUX
23 Vérifie si la cellule est barrée, renvoie VRAI ; sinon FAUX
24Renvoie l'index de couleur de la police
25, 26, 27, 28, 29, 30 et 31 ne sont pas générés parMicrosoft Excelverser des fenêtres
32Nom du classeur et de la feuille de calcul contenant la cellule active dans le formulaire [Classeur1]Feuille1
33 Test d'ajustement de ligne, renvoie TRUE ; sinon FAUX
34Renvoie l'index de couleur de la bordure gauche sous forme de nombre
35Renvoie l'index de couleur de la bordure droite sous forme de nombre
36Renvoie l'index de couleur de la bordure supérieure sous forme de nombre
37Renvoie l'index de couleur de la bordure inférieure sous forme de nombre
38Tester le premier plan ombré comme un nombre
39Tester le remplissage ombré sous forme de nombre
40Style de cellule, comme du texte
41Renvoie la formule de la cellule active sans la convertir (utile pour les feuilles macro internationales)
42La distance horizontale, mesurée en points, du bord gauche de la fenêtre active au bord gauche de la cellule
43La distance verticale, mesurée en points, entre le bord supérieur de la fenêtre active et le bord supérieur de la cellule
44La distance horizontale, mesurée en points, du bord gauche de la fenêtre active au bord droit de la cellule
45La distance verticale, mesurée en points, entre le bord supérieur de la fenêtre active et le bord inférieur de la cellule
46 Si la cellule contient une annotation textuelle, 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 ; s'il contient une constante, il renvoie FALSE
49Si la cellule appartient à un tableau, retourne VRAI ; sinon FAUX
50Renvoie le nombre indiquant l'alignement vertical de la cellule :
1= Alto
2= centré
3= faible
4= Justifié
51Renvoie le nombre indiquant l'orientation verticale de la cellule :
0= horizontale
1= verticale
2= pour le haut
3= Bas
52Le caractère de préfixe de cellule ou un texte vide ('') si la cellule ne contient pas
53Renvoie le contenu de la cellule sous forme de texte, y compris les nombres
54Renvoie le nom de la vue tableau croisé dynamique.
55Renvoie la position d'une cellule dans la vue 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 colonne
6= élément de page
7= élément de données
8= corps du tableau
56Renvoie le nom du champ de la cellule active qui se trouve dans la vue Tableau croisé dynamique
57Renvoie TRUE si les caractères de la cellule sont au format exposant, renvoie FALSE sinon
58 Renvoie le style de police, Gras ou Italique
59 Renvoie le numéro du style de soulignement :
1= Aucun
2= simple
3= Double
4= Comptabilité simplifiée
5= Double comptage
60 Renvoie TRUE si les caractères de la cellule sont au format index ; sinon, elle renvoie FALSE.
61 Renvoie le nom de l'élément du tableau croisé dynamique dans la cellule active
62 Renvoie le nom du classeur ou de la feuille de calcul en cours sous la forme [Classeur]Feuille1
63 Renvoie l'indice de couleur de fond de la cellule
64Renvoie l'indice de couleur de premier plan de la cellule
66Renvoie le nom du classeur qui contient la cellule active, au format Workbook.XLS.
- Pour mettre à jour le calcul, appuyez sur la toucheF9ou apportez une partie du code VBA dans les propriétés de la feuille de calcul :
Private Sub Worksheet_SelectionChange (ByVal Target As Range)
Calculer
finition sous
Vous trouverez ci-dessous un exemple de fichier XLS lisible par toutes les versions d'Excel.Garder commeavec extension XLSM pour Excel 2007 et versions supérieures.
https://www.cjoint.com/c/GKsnRVQsSaj
grâce àmike-31,Ana Espardietpintudaqui sont la source de cet article.