Discussion :

[_cheers]

Bonjour à tous, et merci de m’accueillir sur ce site !

Je suis actuellement étudiant, et j’ai un petit souci en Visual Basic et Excel.
Ce que je souhaiterai faire:
Calculer plusieurs moyennes, et les enregistrer dans une autre cellule.
Ce que je souhaiterai obtenir :

Colonne 1
5
4
7
8
1
4
2
8
3
4

Colonne 2
Moy1 = 4.5
Moy2 = 7.5
Moy3 = 2.5
Moy4 = 5
Moy5 = 3.5

Le pas (nombre de données à moyenner) est "2"

Je souhaiterai faire cette opération sur environs 120000 lignes (2 feuilles excel ), et faire varier ce "pas", donc je dois le prendre en temps que paramètre d'entré impérativement.
C’est pour ça que je me dois d’automatiser tout ça
Ce que j’ai fait :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Public Sub CalculMoyenne()
 
Dim Feuille As Worksheet
 
Dim i, j As Integer
 
Set Feuille = Application.Workbooks ("Classeur1.xls").Worksheets("MaFeuille_1")
 
i = j = 0
 
For j = 1 To Feuille.Cells(5, 5) Step 1
 
    For i = i + 1 To Feuille.Cells(3, 5) Step 1
 
      Feuille.Cells(j, 2) = Feuille.Cells(j, 2) + Feuille.Cells(i, 1)
 
    Next
 
Next
 
End Sub

Je ne suis pas un pro en programmation, mais j’ai besoin de cet outil oh combien utile !!!
Comme vous pouvez le constater il manque des opérations (j’ai pété un cable en fait avec cette chaleur, et je n’arrive plus à avancer !!!) J’ai du mal avec des boucles de boucles, etc.

J'espère que mon énoncé est claire

Je recherche alors une bonne âme pour m’aider à résoudre ce problème
Un grand merci aux personnes qui m’auront lu, et aux personnes qui auront essayé et trouvé la solution !
Bien cordialement,
P.

[bosk1000]

bonjour

j'ai pas bien compris ce que tu veux obtenir
donc pour etre sur
au début tu veux en 2eme colonne la moyenne de la première et deuxième donné( (5+3/)2=4.5), ensuite tu veux la moyenne de la troisiemme et quatrièmme ((7+8)/2=7.5), etc ...
autre chose ?

voici une proposition

en partant du principe que tes donnée démarre à la première ligne de la colonne a
et que le résultat démarre en b2

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
Sub moyenne()
Dim i As Integer
For i = 2 To Range("a65557").End(xlUp).Row
Range("b" & i).FormulaR1C1 = "=IF(R[-1]C="""",(R[-1]C[-1]+RC[-1])/2,"""")"
Next i
End Sub

si t'es donné en colonne a commence à la 5eme ligne par exemple
il faut que tu modifie le "for i=2" en "for i=6"

[DeaD78]

Salut,

Voilà ma solution avec le WorksheetFunction :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Sub test()
 
For i = 1 To Range("a65536").End(xlUp).Row Step 2
    Range("b" & i).Value = Application.WorksheetFunction.Average(Range("a" & i), Range("a" & i + 1))
Next
 
End Sub

Testé et cela fonctionne. C'est pour un pas de 2. Tes données sont dans la colonne A de A1 à AX et les moyennes sont affichées dans la colonne B en face de la première valeur rentrant en compte pour la moyenne.

Pour tes différents pas, si tu ne veux pas te prendre la tête, tu peux faire comme cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sub test()
 
Dim pas As Integer
 
pas = InputBox("Veuillez choisir le pas.")
 
Select Case pas
 
Case 2
 
    For i = 1 To Range("a65536").End(xlUp).Row Step 2
        Range("b" & i).Value = Application.WorksheetFunction.Average(Range("a" & i), Range("a" & i + 1))
    Next
 
Case 3
 
    For i = 1 To Range("a65536").End(xlUp).Row Step 3
        Range("b" & i).Value = Application.WorksheetFunction.Average(Range("a" & i), Range("a" & i + 1), Range("a" & i + 2))
    Next
 
End Select
 
End Sub

Et ainsi de suite, tu adaptes en fonction de tes différents pas. Bien entendu, si tu as 100 pas différents, ça ne va pas le faire et il va falloir trouver autre chose

A+

[Invité]

Bonjour,

Si j'ai bien compris :

et faire varier ce "pas"

vous voulez pouvoir choisir le nombre de données à prendre en compte dans la moyenne.

A essayer (pour commencer) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Public Sub CalculMoyenne()
 
Dim sh  As Worksheet, i&, cpt As Byte, pas As Byte, tot#, moy#
 
Set sh = Worksheets("MaFeuille_1")
dl = Range("A65536").End(xlUp).Row
'nombre de données à prendre en compte pour la moyenne
pas = 2
'1ère ligne contenant les données
pl = 2
cpt = 0
tot = 0
    For i = pl To dl
    cpt = cpt + 1
     tot = tot + sh.Cells(i, 1)
        If cpt = pas Then
        sh.Cells(i, 2) = tot / pas
        tot = 0
        cpt = 0
        End If
     Next i
End Sub

Bien évidemment on peut remplacer "pas = 2" par :

pas = Inputbox ......

D'autre part vous dîtes avoir environ 170 000 lignes sur 2 feuilles, or vous ne précisez pas votre version d'Excel (pour ce faire vous cliquez sur le bouton "Editer" de votre 1er message puis à gauche de votre titre vous sélectionnez la version dans la liste déroulante "Intitulé").

En effet si vous n'avez pas la version 2007 vous ne pouvez avoir plus de 65 536 lignes par feuille soit 131 072 lignes, et encore ! Il vaut mieux ne pas utiliser le maximum.

[DeaD78]

Re,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
Sub test()
 
Dim pas As Integer
 
pas = InputBox("Veuillez choisir le pas.")
 
For i = 1 To Range("a65536").End(xlUp).Row Step pas
    Range("b" & i).FormulaLocal = "=MOYENNE(" & Cells(i, 1).Address & ":" & Cells(i + pas - 1, 1).Address & ")"
Next
 
End Sub

Voilà ce que tu voulais à la base. Tu choisis ton pas et cela est fait de façon dynamique.

Testé et approuvé.

Bonne chance pour la suite.

[Invité]

Bonjour Dead78,

Ok, pas de problème, cela fonctionne, mais quitte à utiliser VBA, pourquoi entrer quelques 130 000 formules et peut-être plus au lieu d'entrer les valeurs.

Si c'est pour que la formule soit recalculée dès que l'on modifie une valeur alors OK, mais je vois mal un utilisateur modifier des valeurs manuellement dans des feuilles contenant ce nombre de lignes.

[DeaD78]

Bonjour jacques_jean,

Effectivement, la remarque est justifiée. Il est vrai que rentrer la valeur "désirée" au bon endroit par VBA est une meilleure solution plutôt que d'y rentrer des formules

J'étais juste parti là-dessus sur un coup de tête sans réfléchir à l'optimisation

Bonne soirée !

[_cheers]

J'avais pas vu toutes vos réponses !!!! J'ai pas reçu de mail me l'indiquant!!!!!
Entre temps du coup, j'ai trouvé un code, pas aussi condensé que les votres, mais plus lisible pour un novice comme moi D
Dans tout les cas, merci beaucoup à tout le monde !

ColonneA: les données d'origine
ColonneB: contenant les moyennes ainsi calculées

D'autres colonnes comme la J et K me servant de mémoire tampons pour les bornes de mes pas. Bref un peu compliqué, mais assez rapide malgré tout.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Public Sub CalculMoyenne()
 
Dim Feuille As Worksheet
 
Set Feuille = Application.Workbooks("Calcul Moyenne en fonction d'un pas.xls").Worksheets("MaFeuille_1")
 
Dim i, j As Single
 
'Colonne B, J et K remise à vide à chaque exécution du programme'
For i = 1 To Feuille.Cells(7, 5) Step 1
  Feuille.Cells(i, 2) = Null  'B = NULL'
  Feuille.Cells(i, 10) = Null 'J = NULL'
  Feuille.Cells(i, 11) = Null 'K = NULL'
Next
 
'valeur des cellules de départ de la moyenne en fonction du pas, enregistrée dans la colonne J ou Feuille.Cells(i,10)'
Feuille.Cells(1, 10) = 1
For i = 2 To Feuille.Cells(7, 5) Step 1
  Feuille.Cells(i, 10) = Feuille.Cells(i - 1, 10) + Feuille.Cells(3, 5)
Next
 
'valeur des cellules d'arrivée de la moyenne en fonction du pas, enregistrée dans la colonne K ou Feuille.Cells(i,11)'
Feuille.Cells(1, 11) = Feuille.Cells(3, 5)
For i = 2 To Feuille.Cells(7, 5) Step 1
  Feuille.Cells(i, 11) = Feuille.Cells(i - 1, 11) + Feuille.Cells(3, 5)
Next
 
'Remplissage des cellules contenant les moyennes en fonction du pas'
For j = 1 To Feuille.Cells(5, 5) Step 1
    For i = Feuille.Cells(j, 10) To Feuille.Cells(j, 11) Step 1
        Feuille.Cells(j, 2) = Feuille.Cells(j, 2) + Feuille.Cells(i, 1)
    Next
    Feuille.Cells(j, 2) = Feuille.Cells(j, 2) / Feuille.Cells(3, 5)
Next
 
End Sub