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
| Sub CalculBlanc()
Dim TabInit As Variant
Dim TabFinal As Variant
Dim MoyBlc() As Double
Dim NbBlc As Long
Dim PosTabSortie As Long
Dim NbLigneTabInit As Long
Dim NbColonneTabInit As Long
TabInit = Cells(1, 1).CurrentRegion
NbLigneTabInit = UBound(TabInit, 1)
NbColonneTabInit = UBound(TabInit, 2)
ReDim MoyBlc(1 To NbColonneTabInit - 1)
'Comptage des blancs
For x = 1 To NbLigneTabInit
If TabInit(x, 1) = "Blanc" Then
NbBlc = NbBlc + 1
For y = 1 To UBound(MoyBlc, 1)
MoyBlc(y) = MoyBlc(y) + TabInit(x, y + 1)
Next y
End If
Next x
'Calcul des moyennes
For y = 1 To UBound(MoyBlc, 1)
MoyBlc(y) = MoyBlc(y) / NbBlc
Next y
'Création du tableau de sortie
ReDim TabFinal(1 To NbLigneTabInit - NbBlc, 1 To NbColonneTabInit)
'ligne de titre
PosTabSortie = 1
For y = 1 To NbColonneTabInit
TabFinal(PosTabSortie, y) = TabInit(PosTabSortie, y)
Next y
'Autres lignes
For x = 2 To NbLigneTabInit
If TabInit(x, 1) <> "Blanc" Then
PosTabSortie = PosTabSortie + 1
TabFinal(PosTabSortie, 1) = TabInit(x, 1)
For y = 2 To NbColonneTabInit
TabFinal(PosTabSortie, y) = TabInit(x, y) * 100 / MoyBlc(y - 1)
Next y
End If
Next x
Range(Cells(1, NbColonneTabInit + 2), Cells(UBound(TabFinal, 1), UBound(TabFinal, 2) + NbColonneTabInit + 1)) = TabFinal
End Sub |
Partager