VBA: Calcul moyenne d'une plage sous condition

Provence Vintage · 21 Décembre 2009

Bonjour le Forum,

Je cherche à calculer la moyenne de la plage "C4:C65536",
pour toutes les valeurs avec "X" dans le colonne "P4😛65536" et "T4" en colonne "V4:V65536"

Voici le code que j'essaye, mais niet!:

#Dim cel As Range, n As Byte

For Each cel In Sheets("bdd acheteurs").Range("P4😛" & Sheets("bdd acheteurs").Range("P65536").End(xlUp).Row)
If cel = "X" And cel.Offset(, 6) = "T4" Then n = Application.Average(Range("C4:C65536"))
Next
Label6 = n#

Ci joint Fichier
Merci à toutes et tous pour vos idées,
bonne journée!

Cijoint.fr - Service gratuit de dépôt de fichiers

Provence Vintage · 23 Décembre 2009

Re : VBA: Calcul moyenne d'une plage sous condition

Bonjour Roger, Le Fil,

Merci de m'avoir répondu Roger,
ci joint les commentaires pour vous permettre de voir ce que je recherche au mieux et quel type de code peux s'adapter!

A plus tard sur le Fil
bonne journée

#With Sheets("bdd acheteurs")
d = .Range("C4:C65536").Value
c1 = .Range("P4😛65536").Value
c2 = .Range("Q4:Q65536").Value
c3 = .Range("R4:R65536").Value
c4 = .Range("S4:S65536").Value
c5 = .Range("T4:T65536").Value
e = .Range("V4:V65536").Value
End With
For i = 1 To UBound(d, 1)
If c1(i, 1) = "X" And e(i, 1) = "T2" And d(i, 1) <> "" Then _
s = s + d(i, 1): n = n + 1
Next i
If n Then m = s / n Else m = "" 'm=moyenne des éléments de "C4:C65536" _
associés à "X" en colonne P et à "T2" en colonne V.
T2MDVO = Format(CDbl(m) / 1000, "# ##0 K€")
For i = 1 To UBound(d, 1)
If c1(i, 1) = "X" And e(i, 1) = "T3" And d(i, 1) <> "" Then _
s = s + d(i, 1): n = n + 1
Next i
If n Then m = s / n Else m = "" 'm=moyenne des éléments de "C4:C65536" _
associés à "X" en colonne P et à "T3" en colonne V.
T3MDVO = Format(CDbl(m) / 1000, "# ##0 K€")
For i = 1 To UBound(d, 1)
If c1(i, 1) = "X" And e(i, 1) = "T4" And d(i, 1) <> "" Then _
s = s + d(i, 1): n = n + 1
Next i
If n Then m = s / n Else m = "" 'm=moyenne des éléments de "C4:C65536" _
associés à "X" en colonne P et à "T4" en colonne V.
T4MDVO = Format(CDbl(m) / 1000, "# ##0 K€")
For i = 1 To UBound(d, 1)
If c1(i, 1) = "X" And e(i, 1) = "T5" And d(i, 1) <> "" Then _
s = s + d(i, 1): n = n + 1
Next i
If n Then m = s / n Else m = "" 'm=moyenne des éléments de "C4:C65536" _
associés à "X" en colonne P et à "T5" en colonne V.
T5MDVO = Format(CDbl(m) / 1000, "# ##0 K€")
For i = 1 To UBound(d, 1)
If c1(i, 1) = "X" And e(i, 1) = "T6" And d(i, 1) <> "" Then _
s = s + d(i, 1): n = n + 1
Next i
If n Then m = s / n Else m = "" 'm=moyenne des éléments de "C4:C65536" _
associés à "X" en colonne P et à "T6" en colonne V.
T6MDVO = Format(CDbl(m) / 1000, "# ##0 K€")
For i = 1 To UBound(d, 1)
If c1(i, 1) = "X" And e(i, 1) = "T7" And d(i, 1) <> "" Then _
s = s + d(i, 1): n = n + 1
Next i
If n Then m = s / n Else m = "" 'm=moyenne des éléments de "C4:C65536" _
associés à "X" en colonne P et à "T7" en colonne V.
T7MDVO = Format(CDbl(m) / 1000, "# ##0 K€")

'ET ENSUITE, On bascule de colonne, de P pour MDV, on cherche maintenant les moyennes pour
'Appartement en colonne Q, avec "X" en colonne Q, T2, T3, …..T7 en colonne V
'et les montants toujours en colonne C#

PS: je viens de m'apercevoir de votre message, je regarde, merci!

ROGER2327 · 24 Décembre 2009

Re : VBA: Calcul moyenne d'une plage sous condition

Re...

Provence Vintage à dit:
(...)
'ET ENSUITE, On bascule de colonne, de P pour MDV, on cherche maintenant les moyennes pour
'Appartement en colonne Q, avec "X" en colonne Q, T2, T3, …..T7 en colonne V
'et les montants toujours en colonne C#
(...)

Il ne s'agit donc qu'incidemment de

calculer la moyenne de la plage "C4:C65536",
pour toutes les valeurs avec "X" dans le colonne "P4😛65536" et "T4" en colonne "V4:V65536"

En clair, peut-on dire :
Pour chaque colonne de P à T (cinq colonnes) et chaque valeur de la colonne V (six valeurs possibles), calculer, pour chacune de ces valeurs possibles, la moyenne des valeurs de la colonne C situées sur les lignes où X figure dans la colonne choisie ?

« Ce que l'on conçoit bien s'énonce clairement,
Et les mots pour le dire arrivent aisément. »
(Nicolas BOILEAU, 1636 - 1711)

S'il s'agit de cela, on a trente moyennes à calculer.
Le code que vous proposez fonctionnera probablement avec quelques aménagements. Il faudra seulement écrire trente fois le même calcul, en veillant à réinitialiser les variables de comptage (s et n) à zéro entre chaque calcul. Ce sera effectivement fastidieux, et je ne le ferai pas.
Mais le code que je vous propose avec le message #16 le fait presque : en fin de procédure, le tableau r contient ces trente moyennes, pour autant qu'elles existent. Il suffit de les affecter aux variables ad hoc.
On aurait alors quelque chose comme ceci :

Code:

[COLOR="DarkSlateGray"][B]Sub toto3()
[COLOR="SeaGreen"]'[/COLOR]
[COLOR="Sienna"]Dim P_T2, P_T3, P_T4, P_T5, P_T6, P_T7
Dim Q_T2, Q_T3, Q_T4, Q_T5, Q_T6, Q_T7
Dim R_T2, R_T3, R_T4, R_T5, R_T6, R_T7
Dim S_T2, S_T3, S_T4, S_T5, S_T6, S_T7
Dim T_T2, T_T3, T_T4, T_T5, T_T6, T_T7[/COLOR]
[COLOR="SeaGreen"]'[/COLOR]
Dim d, c1, c2, r(1 To 6, 1 To 5) As Variant, p(1 To 6, 1 To 5) As Long, z
Dim i As Long, j As Long, k As Long
   With Sheets("bdd acheteurs")
      d = .Range("C4:C65536").Value
      c1 = .Range("P4:T65536").Value
      c2 = .Range("V4:V65536").Value
   End With
   z = Array("", "T2", "T3", "T4", "T5", "T6", "T7")
   For i = 1 To UBound(d, 1)
      If d(i, 1) <> "" Then
         For k = 1 To 6
            If c2(i, 1) = z(k) Then
               For j = 1 To 5
                  If c1(i, j) = "X" Then _
                     r(k, j) = r(k, j) + CDbl(d(i, 1)): p(k, j) = p(k, j) + 1
               Next j
            End If
         Next k
      End If
   Next i
   For j = 1 To 5
      For k = 1 To 6
         If p(k, j) <> 0 Then r(k, j) = r(k, j) / p(k, j) Else r(k, j) = ""
      Next k
   Next j
[COLOR="SeaGreen"]'[/COLOR]
   [COLOR="Sienna"]P_T2 = r(1, 1): P_T3 = r(2, 1): P_T4 = r(3, 1): P_T5 = r(4, 1): P_T6 = r(5, 1): P_T7 = r(6, 1)
   Q_T2 = r(1, 2): Q_T3 = r(2, 2): Q_T4 = r(3, 2): Q_T5 = r(4, 2): Q_T6 = r(5, 2): Q_T7 = r(6, 2)
   R_T2 = r(1, 3): R_T3 = r(2, 3): R_T4 = r(3, 3): R_T5 = r(4, 3): R_T6 = r(5, 3): R_T7 = r(6, 3)
   S_T2 = r(1, 4): S_T3 = r(2, 4): S_T4 = r(3, 4): S_T5 = r(4, 4): S_T6 = r(5, 4): S_T7 = r(6, 4)
   T_T2 = r(1, 5): T_T3 = r(2, 5): T_T4 = r(3, 5): T_T5 = r(4, 5): T_T6 = r(5, 5): T_T7 = r(6, 5)[/COLOR]
[COLOR="SeaGreen"]'[/COLOR]
End Sub[/B][/COLOR]

Pour l'adapter à votre procédure, il ne reste qu'à remplacer les noms de variables de P_T2 (moyenne associée à la valeur X en colonne P et à la valeur T2 en colonne V) à T_T7 (moyenne associée à la valeur X en colonne T et à la valeur T7 en colonne V) par les noms qui vous conviennent.
Est-ce bien de cela qu'il s'agit ?

ROGER2327
#2471

Provence Vintage · 28 Décembre 2009

Re : VBA: Calcul moyenne d'une plage sous condition

Bonjour Roger, Le Fil,

Tout d'abord, de bonnes fêtes ;-)

merci beaucoup pour vos réponses.
Le code que vous m'avez fourni fonctionne très bien pour les 4 premières colonnes de P à S;
avec les valeurs de la colonne V de T2 à T7!

Le seul bémol est pour Terrain, Colonne T; le calcul prend en compte pour Terrain toutes les lignes ou il y a "X" et une valeur de T2 à T7, jusqu'a là tout va bien;
Par contre, il y a une exception que j'ai omis de préciser pour ce dernier:
le Calcul pour Terrain se fait avec ces paramètres, mais aussi juste avec "X" (le choix peux être multiple, par exemple: une maison T4 ou un Terrain, ou seulement un Terrain)

#Sub MoyennesAcheteurs()

'Acheteurs: Calcul des Quantités, Prix, Moyennes, Alimentation résultats sur feuille "Calcul des Moyennes"

Dim d, c1, c2, r(1 To 6, 1 To 5) As Variant, p(1 To 6, 1 To 5) As Long, z
Dim i As Long, j As Long, k As Long
With Sheets("bdd acheteurs")
d = .Range("C4:C65536").Value
c1 = .Range("P4:T65536").Value
c2 = .Range("V4:V65536").Value
End With
z = Array("", "T2", "T3", "T4", "T5", "T6", "T7")
For i = 1 To UBound(d, 1)
If d(i, 1) <> "" Then
For k = 1 To 6
If c2(i, 1) = z(k) Then
For j = 1 To 5
If c1(i, j) = "X" Then _
r(k, j) = r(k, j) + CDbl(d(i, 1)): p(k, j) = p(k, j) + 1
Next j
End If
Next k
End If
Next i
With Sheets("Calcul des Moyennes")
.Range("C3:G8").Value = r
For j = 1 To 5
For k = 1 To 6
If p(k, j) <> 0 Then r(k, j) = r(k, j) / p(k, j) Else r(k, j) = ""
Next k
Next j
.Range("C11:G16").Value = p
.Range("C19:G24").Value = r
End With
End Sub#

#z = Array("", "T2", "T3", "T4", "T5", "T6", "T7")
For i = 1 To UBound(d, 1)
If d(i, 1) <> "" Then
For k = 1 To 6#
j'ai essayé de rajouter au code: Or d(i, 1) = "" Then
et For k = 1 To 7 pour prendre en compte le fait qu'il pouvait en colonne V y avoir les valeurs"T2, T3...T7 ou rien!
mais......

Merci

ROGER2327 · 29 Décembre 2009

Re : VBA: Calcul moyenne d'une plage sous condition

Re...

Est-il possible d'avoir l'ensemble du cahier des charges, rédigé dans un langage clair ?
J'ai regardé le code précédent : il fait exactement le travail pour toutes les colonnes selon les critères fixés précédemment, sans dièse ni bémol.
Mais il ne peut pas tenir compte de critères secrets.
Il semble qu'on passe maintenant de trente à trente-et-une moyennes. N'est-il pas possible d'avoir en une fois l'ensemble du cahier des charges, rédigé dans un langage clair ? On y gagnerait tous notre temps...

Une nouvelle (et dernière ?) mouture pour compléter les résultats précédents par celui-ci : calcul de la moyenne des valeurs de la colonne C associée à la valeur X dans la colonne T, sans tenir compte de la valeur associée en colonne V.

En espérant que j'ai compris ce qu'il faut comprendre.

Cette nouvelle moyenne est affectée à la variable T_Tn.

Code:

[COLOR="DarkSlateGray"][B]Sub toto4()
Dim P_T2, P_T3, P_T4, P_T5, P_T6, P_T7
Dim Q_T2, Q_T3, Q_T4, Q_T5, Q_T6, Q_T7
Dim R_T2, R_T3, R_T4, R_T5, R_T6, R_T7
Dim S_T2, S_T3, S_T4, S_T5, S_T6, S_T7
Dim T_T2, T_T3, T_T4, T_T5, T_T6, T_T7[COLOR="Red"], T_Tn[/COLOR]
Dim d, c1, c2, r(1 To [COLOR="Red"]7[/COLOR], 1 To 5) As Variant, p(1 To [COLOR="Red"]7[/COLOR], 1 To 5) As Long, z
Dim i As Long, j As Long, k As Long
   With Sheets("bdd acheteurs")
      d = .Range("C4:C65536").Value
      c1 = .Range("P4:T65536").Value
      c2 = .Range("V4:V65536").Value
   End With
   z = Array("", "T2", "T3", "T4", "T5", "T6", "T7")
   For i = 1 To UBound(d, 1)
      If d(i, 1) <> "" Then
         For k = 1 To 6
            If c2(i, 1) = z(k) Then
               For j = 1 To 5
                  If c1(i, j) = "X" Then
                     r(k, j) = r(k, j) + CDbl(d(i, 1)): p(k, j) = p(k, j) + 1
                     [COLOR="Red"]If j = 5 Then r(7, j) = r(7, j) + CDbl(d(i, 1)): p(7, j) = p(7, j) + 1[/COLOR]
                  End If
               Next j
            End If
         Next k
      End If
   Next i
   For j = 1 To 5
      For k = 1 To [COLOR="Red"]7[/COLOR]
         If p(k, j) <> 0 Then r(k, j) = r(k, j) / p(k, j) Else r(k, j) = ""
      Next k
   Next j
   P_T2 = r(1, 1): P_T3 = r(2, 1): P_T4 = r(3, 1): P_T5 = r(4, 1): P_T6 = r(5, 1): P_T7 = r(6, 1)
   Q_T2 = r(1, 2): Q_T3 = r(2, 2): Q_T4 = r(3, 2): Q_T5 = r(4, 2): Q_T6 = r(5, 2): Q_T7 = r(6, 2)
   R_T2 = r(1, 3): R_T3 = r(2, 3): R_T4 = r(3, 3): R_T5 = r(4, 3): R_T6 = r(5, 3): R_T7 = r(6, 3)
   S_T2 = r(1, 4): S_T3 = r(2, 4): S_T4 = r(3, 4): S_T5 = r(4, 4): S_T6 = r(5, 4): S_T7 = r(6, 4)
   T_T2 = r(1, 5): T_T3 = r(2, 5): T_T4 = r(3, 5): T_T5 = r(4, 5): T_T6 = r(5, 5): T_T7 = r(6, 5)
   [COLOR="Red"]T_Tn = r(7, 5)[/COLOR]
End Sub[/B][/COLOR]

La rustine est en rouge.
Voir la version de test dans le fichier joint.

ROGER2327
#2484

Provence Vintage · 29 Décembre 2009

Re : VBA: Calcul moyenne d'une plage sous condition

Roger, le Fil

Bonjour, je teste et vous tient au courant!
Merci beaucoup!

VBA: Calcul moyenne d'une plage sous condition

Provence Vintage

XLDnaute Occasionnel

Provence Vintage

XLDnaute Occasionnel

ROGER2327

XLDnaute Barbatruc

Provence Vintage

XLDnaute Occasionnel

ROGER2327

XLDnaute Barbatruc

Pièces jointes

Provence Vintage

XLDnaute Occasionnel

Discussions similaires

VBA: Calcul moyenne d'une plage sous condition

Provence Vintage

XLDnaute Occasionnel

Provence Vintage

XLDnaute Occasionnel

ROGER2327

XLDnaute Barbatruc

Provence Vintage

XLDnaute Occasionnel

ROGER2327

XLDnaute Barbatruc

Pièces jointes

Provence Vintage

XLDnaute Occasionnel

Discussions similaires

Privacy & Transparency

Privacy & Transparency