1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169
| Sub moyenne()
Dim zone As Range
Dim c As Variant
Dim n As Variant
Dim deja As Boolean
'pour mise en collection des noms et ID en enlevant les doublons
Dim noms As New Collection
Dim cond As New Collection
Dim pass As New Collection
Dim traj As New Collection
Dim voit As New Collection
'zone concernée
Set zone = Feuil1.Range("D2", Feuil1.Range("D2").End(xlDown))
For Each c In zone 'entrées des collections
If c.Value <> "" Then
If noms.Count = 0 Then
noms.Add (c.Value) 'premier nom
Else
deja = False
i = 0
For Each n In noms 'voir si doublon
deja = n = c.Value
If deja Then i = 1
Next n
If i = 0 Then noms.Add (c.Value) 'nom suivant
End If
End If
If c.Offset(0, 3).Value <> "" Then 'idem autres collections...
If cond.Count = 0 Then
cond.Add (c.Offset(0, 3))
Else
deja = False
i = 0
For Each n In cond
deja = n = c.Offset(0, 3)
If deja Then i = 1
Next n
If i = 0 Then cond.Add (c.Offset(0, 3))
End If
End If
If c.Offset(0, 1).Value <> "" Then
If pass.Count = 0 Then
pass.Add (c.Offset(0, 1))
Else
deja = False
i = 0
For Each n In pass
deja = n = c.Offset(0, 1)
If deja Then i = 1
Next n
If i = 0 Then pass.Add (c.Offset(0, 1))
End If
End If
If c.Offset(0, 2).Value <> "" Then
If traj.Count = 0 Then
traj.Add (c.Offset(0, 2))
Else
deja = False
i = 0
For Each n In traj
deja = n = c.Offset(0, 2)
If deja Then i = 1
Next n
If i = 0 Then traj.Add (c.Offset(0, 2))
End If
End If
If c.Offset(0, 4).Value <> "" Then
If voit.Count = 0 Then
voit.Add (c.Offset(0, 4))
Else
deja = False
i = 0
For Each n In voit
deja = n = c.Offset(0, 4)
If deja Then i = 1
Next n
If i = 0 Then voit.Add (c.Offset(0, 4))
End If
End If
Next c
i = 1
For Each n In noms 'ecriture sur feuill2 des titres
Feuil2.Cells(i, 2) = Right(n, Len(n) - 3) & "s"
Feuil2.Cells(i + 1, 1) = "ID"
Feuil2.Cells(i + 2, 1) = "moyenne"
Select Case n 'ecriture et calcul des moyennes de chacun
Case "un conducteur"
j = 2
For Each c In cond
Feuil2.Cells(i + 1, j) = c.Value 'ecriture de l'ID
Call moy(n, c, Feuil2.Cells(i + 2, j)) 'routine de moyenne
j = j + 1
Next c
Case "un passager"
j = 2
For Each c In pass
Feuil2.Cells(i + 1, j) = c.Value 'idem...
Call moy(n, c, Feuil2.Cells(i + 2, j))
j = j + 1
Next c
Case "un trajet"
j = 2
For Each c In traj
Feuil2.Cells(i + 1, j) = c.Value
Call moy(n, c, Feuil2.Cells(i + 2, j))
j = j + 1
Next c
Case "une voiture"
j = 2
For Each c In voit
Feuil2.Cells(i + 1, j) = c.Value
Call moy(n, c, Feuil2.Cells(i + 2, j))
j = j + 1
Next c
End Select
i = i + 4 '4 ligne après
Next n
End Sub
'procédure de calcul de la moyenne
Sub moy(ByVal s As String, ByVal id As Integer, ByRef cel As Range)
' s pour le nom id en cours cellule destination
Dim zone As Range
Dim c As Range
Dim col As Integer
Dim nbeval As Integer, v As Integer
col = 7
Set zone = Feuil1.Range("D2", Feuil1.Range("D2").End(xlDown))
Select Case s 'zone de l'id en cours
Case "un conducteur"
col = 7
Case "un passager"
col = 5
Case "un trajet"
col = 6
Case "une voiture"
col = 8
End Select
nbeval = 0
cel.Value = 0
v = 0
For Each c In zone 'calcul somme et nombre d'eval
If c = s And id = Feuil1.Cells(c.Row, col) Then
v = v + Feuil1.Cells(c.Row, 3)
nbeval = nbeval + 1
End If
Next c
If nbeval = 0 And v = 0 Then
cel.Value = "" 'si pas d'évaluation
Else
cel.Value = v / nbeval 'moyenne enfin ....
End If
End Sub |
Partager