Sommeprod et couleur

[binoute]

Bonjour le forum,
je veux compter des cellules de couleur avec sommeprod. par feuille mensuelle
je me demene avec cette formule depuis plus d'une heure,
qu'est ce qui peche ?

=SOMMEPROD(((MOIS(A$2:A$100)=MOIS($J2)))*(colorcountif(F$2:F$100;N1)))

en N1 il y a la couleur,

une bonne idee ?
merci d'avance et @+

 

[binoute]

Bonjour tous le monde,

je crois que je suis tombé sur un os avec cette fonction !
il ne me reste plus que la mise en forme conditionnelle dans une colonne masquée avec un nbval qui compte les cellules à 1 !

D'ailleurs peut on tester une couleur de cellule avec la MFC ?

@+

 

[Sylvie]

Bonjour Binoute,

la réponse est oui car le cas a déjà été traité ici; fait une recherche sur les mots compter et couleur et tu trouveras ton bonheur ( la rime n'est pas volontaire mais un peu de poésie ne fait pas de mal après tout).

A+
Sylvie

 

[binoute]

bonjour,
je me permet d'insister, mais les codes utilisés pour compter les couleurs ne permettent pas un actualisation automatique.
en l'occurence mes listes s'actualisent tous les jours (gestion de délais), il m'est donc difficile de refaire à chaque fois le recalcul des cellules rouges. (surtout que cela me genere des statistiques dans d'autres classeurs.

personne n'aurait une solution simple pour, au moins, par feuille avoir une formule qui compterait automatiquement ?

Au secours ! et à plus

 

[Monique]

Bonjour,

Avec ou sans couleurs, tu peux compter les cellules contenant un mois donné et telle ou telle condition.
=SOMMEPROD((Plage de dates<>'')*(MOIS(Plage de dates) = MOIS(E3))*(la condition qui t'a fait mettre les cellules en rouge))

 

[Sylvie]

Re bonjour Binoute, Monique, le Forum,

Tes cellules sont elles coloriées en fonction par exemple d'un délai de règlement dépassé ? Leur appliques tu une mise en forme conditionnelle ?


A+
Sylvie

 

[JeanMarie]

Bonsoir tout le monde

En reprenant le 1er fichier de binoute, qui contient de cellule coloriée issue d'une action et non d'une mise en forme conditionnelle.

En utilisant cette fonction
=SOMMEPROD((A$2:A$100>0)*(MOIS(A$2:A$100)=MOIS($E2&'1904'))*MatriceColorIf($A$2:$A$100;$I$1))

et une modification de la fonction

Function MatriceColorIf(SearchArea As Range, BgColor As Range) As Variant
Dim Matrice() As Byte
Dim cell As Range
Application.Volatile True
ReDim Matrice(SearchArea.Rows.Count - 1)
MaCoul = BgColor.Interior.ColorIndex
J = 0
For Each cell In SearchArea
   Matrice(J) = IIf(cell.Interior.ColorIndex = MaCoul, 1, 0)
   J = J + 1
Next cell
MatriceColorIf = Application.Transpose(Matrice)
End Function

La function ne retourne plus une somme du nombre d'éléments ayant la bonne couleur, mais une matrice composée de 1 ou de 0, qui indique l'état du comparateur. Cette matrice est exploitable dans les différentes fonction des feuilles de calcul.

@+Jean-Marie

 

[Gael]

Bonjour à tous,

Félicitations Jean-Marie, ça marche très bien chez moi.

C'est une fonction très intéressante car on a souvent ce problème de couleur de cellule dans des sélections.

Je savais bien qu'un pro du VBA nous trouverait une solution!

@+

Gael

 

[Sylvie]

Bonsoir à tous,

J'en connais qui va être content ! N'est ce pas Binoute ?
Ton code et ta fonction marchent superbement. Bravo Jean Marie ! J'en étais encore à du '100 % formules' et je n'arrêtais pas de tourner en rond.
Il était donc temps que tu interviennes Jean Marie pour permettre à mes petits neurones de reprendre leurs esprits.

Bonne soirée
Sylvie

 

[JeanMarie]

Re...

Gael, un pro du VBA, je ne suis qu'un tout petit débutant, et je préfère les fonctions de feuilles de calcul. Alors une matrice qui laisse le choix à utilisateur de la fonction à utiliser, j'adore.

Je n'ai pas essayer sur une plage à deux dimensions (x lignes, x colonnes).

@+Jean-Marie

 

[binoute]

Bonjour à tous les acteurs de ce fil,

ALORS LA JE DIS BRAVO !

C'est exactement ce que je recherchais, merci à jean marie, gael, monique et sylvie qui se sont acharnés à résoudre ce probleme, dont la solution en interessera plus d'un je pense.

merci à tous et @+ vers de nouvelles aventures

 

[Gael]

Bonjour à tous,

Pour continuer sur ce problème de couleur de cellule qui revient souvent, j'ai légèrement modifié les fonctions de Binoute et Jean-Marie pour qu'elles utilisent l'index de la couleur au lieu d'une cellule colorée de référence.

l'argument BgColor contient l'index de la couleur.

En résumé:

1 - Pour compter les cellules d'une couleur dans une plage:

Function CCF(SearchArea As Object, BgColor As Byte) As Integer
Application.Volatile True
CCF = 0
For Each cell In SearchArea
If cell.Interior.ColorIndex = BgColor Then CCF = CCF + 1
Next cell
End Function

Exemple: =CCF(B16:B29;3)

2 - Pour renvoyer une matrice de 0 et 1 en fonction d'un index:

Function MCF(SearchArea As Range, BgColor As Byte) As Variant
Dim Matrice() As Byte
Dim cell As Range
Application.Volatile True
ReDim Matrice(SearchArea.Rows.Count - 1)
J = 0
For Each cell In SearchArea
Matrice(J) = IIf(cell.Interior.ColorIndex = BgColor, 1, 0)
J = J + 1
Next cell
MCF = Application.Transpose(Matrice)
End Function

Exemple: {=MCF(B16:B21;53)}

3 - Pour connaître l'index des couleurs:

2 méthodes:

(a) - Autoenregistrer une macro en appliquant les couleurs souhaitées sur une ou plusieurs cellules puis récupérer dans la macro le code, par exemple: '.ColorIndex = 7'

(b) - A partir de la méthode développée dans ce forum par Jean-Marie, Celeda et d'autres sur le sujet 'Astuces récentes':

* Mettre une couleur quelconque dans une cellule (ex:B16)
* Se positionner sur la cellule située à sa droite
* Faire Insertion / nom / définir puis tapez 'Indcol' dans le champ Nom dans le classeur
* Dans le champ Fait référence à tapez: '=LIRE.CELLULE(38;!B16)'
* Validez

En tapant '=Indcol' dans n'importe quelle cellule du tableau, l'index couleur de la cellule située à sa gauche s'affichera.

Je voulais faire un lien vers le sujet 'Astuces récentes' mais je ne sais pas quelle est la manip. Si quelqu'un peut me l'expliquer, ce serait super.

Merci à tous pour ce FIL très enrichissant.

@+

Gael

 

[binoute]

Bonjour à tous,

Damned, je pensais que j'y arriverais !
MAIS NON !
ci joint classeur avec configuration finale d'utilisation de la formule de Jean marie. et bien ca ne marche pas !
la boulette est ou ?

si quelqu'un la voit, merci
a+ [file name=essaicouleur1.zip size=9635]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/essaicouleur1.zip[/file]

 

[Sylvie]

Bonjoru Binoute, le fil, le forum,

je pense que compte tenu du fait que toutes tes valeurs en F ne sont pas des dates, qu'il faudrait également rajouter dans la formule SOMMEPROD un SI ESTNUM ... pour éviter le message d'erreur en K9.
Je vais regarder le reste mais le VBA est au dessus de mes possibilités actuelles

Sylvie

 

[JeanMarie]

Bonjour tout le monde

Binoute, comme le fait remarquer Sylvie, il faut déjà corriger les plages de ta formule, avant de passer à la function VBA, qui entre nous ne te servira à rien. Cette function VBA ne prend en compte que les couleurs fixées dans les cellules, et non les couleurs modifiées par une MFC.

Dans ton cas, tu reviens à utiliser la solution de Monique décrite précédemment dans ce fil.

Pour les cellules de couleur verte, elles contiennent un 'C', remplace MatriceColorIf($F$2:$F$100;$M$1) par (F$2:F$100='C')

Pour les cellules de couleur rouge, la date est inférieure ou égale à la date du jour, remplace MatriceColorIf($F$2:$F$100;$M$1) par (F$2:F$100<=AUJOURDHUI()).

@+Jean-Marie

 

[binoute]

Bonjour sylvie et jean marie,
en éffet, je viens de comprendre que la mfc et vba ne font pas bon ménage ensemble.
Pour résumé : dés qu'il y a mfc => formule sinon vba
vous confirmerez pour etre sur mais c'est vrai qu'une fois modifié ça à l'air de marcher.
en complément, puis je asservir la formule aux onglets de mois (soit 12 onglets janvier à decembre) pour faire un récapitulatif sur une seule feuille avec la formule suivante.
=SOMMEPROD((A$2:A$100>0)*(????????????)*($F$2:$F$100<=AUJOURDHUI()))
Je sais j'abuse et le week end est plutot prometeur au niveau beau temps mais juste pour une fois ....
merci à tous