Calculs sur des couleurs

Calculs sur des couleurs avec ou sans macro

On a parfois besoin d'effectuer des calculs sur des cellules colorées, en fonction de leur couleur. Dans l'exemple présent, on s'intéressera à la couleur de fond de la cellule. Le même raisonnement pourrait s'appliquer à la couleur de caractère.
Nous allons construire la feuille bigarrée ci-dessous.

En colonne E nous mettons toutes les couleurs de fond possibles. Leur n° est indiqué en colonne D (de 1 à 56). La cellule E1 n'a aucune couleur de fond (couleur automatique de n° 0).
Pour cette opération, le plus simple est d'exécuter la macro ci-dessous :

Sub appliqueCouleurs()
For i = 0 To 56
    Cells(i+1, 5).Interior.ColorIndex = i
Next i
End Sub

En colonne A nous entrons 100 nombres quelconques compris entre 0 et 99, par exemple par la formule
=ent(alea()*100)
Puis nous copions la plage A1:A100 dans le presse-papier et nous faisons un collage spécial valeurs seulement en A1, ce qui figera les nombres en A1:A100.
Appliquons enfin des couleurs de fond également aléatoires aux cellules A1:A100.
Ici encore on exécutera la macro suivante :

Sub appliqueCouleursalea()
For i = 1 To 100
    Cells(i, 1).Interior.ColorIndex = Int((56 * Rnd) + 1)
Next i
End Sub

L'objet de cette page est de montrer comment on peut faire la somme des cellules de la colonne A en fonction de leur couleur.

La colonne A contient des nombres entiers de 0 à 99, ses cellules ont une couleur de fond aléatoire.
La colonne B contiendra le n° de la couleur de fond de la cellule de la colonne A correespondante.
En colonne C, nous mettrons la formule magique qui calcule la somme des cellules de la colonne A dont la couleur de fond a comme n° le chiffre indiqué en colonne D.
Enfin la colonne E a comme n° de couleur de fond le chiffre trouvé en colonne D.

La colonne C contient la formule magique. Par exemple :
C7=somme des cellules de la colonne A dont le n° de couleur est 6 (jaune clair)
Les cellules A7, A13, A41, A46, A47 et A80 sont de couleur jaune clair (index n° 6)
Elles contiennent respectivement 19, 2, 93, 27, 32 et 22, dont la somme est 195.
C'est bien le résultat trouvé en C6.

Pour arriver à ce résultat, il faut d'abord trouver une fonction qui renvoie la couleur de fond. Excel ne dispose pas en standard d'une telle fonction.
Il est possible de créer une fonction complémentaire en VBA qui la calcule.
La propriété VBA est : c.Interior.ColorIndex où c est l'objet représentant la cellule.
Le code est le suivant :

Function CouleurDeFond(c As Range)
   CouleurDeFond = c.cells(1).Interior.ColorIndex
End Function

Notez c.cells(1) qui renvoie la première cellule de la plage à laquelle s'applique la fonction. Si on n'avait pas pris cette précaution, la fonction renverrait toujours 0 pour les plages où les cellules seraient de couleurs différentes.

Ensuite il suffit d'appliquer la fonction CouleurDeFond avec comme argument l'adresse de la cellule dont on recherche la couleur de fond.
Par exemple, =CouleurDeFond($A$1) renverra 40.

Une autre possibilité, sans macro VBA, est d'utiliser les noms Excel en se basant sur les macros d'Excel 4.

Il suffit de se positionner en B1, de créer le nom CouleurFond et de lui donner la valeur :
=LIRE.CELLULE(63;!A1)+MAINTENANT()*0
La formule, créée à partir de B1, se réfère à A1. Ce qui veut dire que le nom CouleurFond donnera comme résultat l'index de la couleur de fond de la cellule située immédiatement à gauche de la cellule courante.
La formule =CouleurFond appliquée à n'importe quelle cellule de la feuille Feuil1 (excepté en colonne A) renverra comme résultat le n° de la couleur de fond de la cellule située à gauche. Ici la colonne B renvoie la couleur de fond de la colonne A
La fonction LIRE.CELLULE(63;cellule) est une fonction héritée de Excel4 qui ne peut s'utiliser que lors de la création de noms Excel.
La plupart des fonctions-macros Excel4 qui ne modifient pas la feuille (celles qui n'agissent qu'en lecture) peuvent être utilisées lors de la création de noms.

La documentation des fonctions-macros Excel4 peut être téléchargée à partir du lien suivant :
http://download.microsoft.com/download/excel97win/utility4/1/win98/en-us/macrofun.exe

Enfin, pour calculer la somme des valeurs en focnction de la couleur de fond, il suffit d'appliquer en C1 la formule :
=SOMME.SI($B$1:$B$100;D1;$A$1:$A$100)
et de la recopier jusqu'à C57.
Cette formule additionne les valeurs trouvées en colonne A, en fonction de la colonne B.

Retour à la page d'accueil