Changer de moyenne par action dans le classeur

Question

Bonjour à tous. 

J'ai une petite modification à apporter à mon programme de pesée réalisé il y à quelques semaines par votre précieuse aide. 

Je m'explique, Mon classeur se remplit ligne par ligne suivant les mesures réceptionnées. 
A partir de ces lignes, j'effectue une moyenne dans une autre cellule par la formule Excel => =MOYENNE(B1:B5000;0). La valeur de cette cellule est considérée dans mon programme comme poids de référence.

Malheureusement, certaines commandes dans mon entreprise se déroulant sur plusieurs jours, le poids des composants ou des cartons peux varier d'une façon relativement importante. J'aimerais donc qu'une action (J'ai déjà le bouton de reprise). Reconsidère les nouvelles valeurs enregistrées et calcule une nouvelle moyenne à partir de la dernière cellule enregistrée jusqu'à la fin de ma plage. J'ai tenté plusieurs petites ligne mais rien ne marche :/

Je m'en remet donc à votre aide précieuse :)

En vous remerciant d'avance.

J.

ucfoutu · Answer

Bonjour,

Pas vraiment clair, mais d'instinct :

Intéresse-toi  à la fonction Excel NBVAL


____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

ucfoutu · Answer

ou crée une fonction personnalisée si nécessaire.
Mais si tu veux de l'aide pour la créer, il nous faudra des bexplications plus précises que celles données  (avec exemple succinct si besoin est d'un exemple pour s'expliquer)

____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

shinrei69 · Answer

Bonjour et merci de répondre aussi vite.

Je vais essayer de détailler :)

Donc, j'ai deux cas de figures qui s'offrent à moi. 
- Soit il s'agit d'une nouvelle commande, au quel cas mon classeur se réinitialise et une cellule calcule la moyenne d'une plage (de B2 à B5000).

- Soit, il s'agit d'une reprise (commande sur plusieurs jours). Ou mon classeur enregistre à partir de la dernière cellule active (qui est totalement aléatoire) toujours dans la même plage. 

Ces deux parties la fonctionnent. 

Dans le premier cas de figure, la formule me convient parfaitement puisqu'elle fait, comme demandé, la moyenne de la plage.

Dans le deuxième cas, elle ne me convient plus, puisque suivant les jours, tout les poids varient infimement. 


Ce que je veux, c'est que lorsque j'appuie sur le bouton "début", la formule => =MOYENNE(B1:B5000;0) s'applique.

Et lorsque j'appuie sur le bouton "reprise", ce soit une formule du style =MOYENNE(LastactiveCells:B5000,0)


J'espère avoir été assez clair :/

J.

shinrei69 · Answer

J'ai étudier la fonction NBVAL comme tu me le conseillais. 


Je pense que ce doit être possible de résoudre mon problème en appliquant dans un premier cas. La formule =MOYENNE(B1:B5000;0) 

Puis en extrayant la valeur d'une case soumis à NBVAL dans une variable. (x ici)

Et ensuite en faisant redéfinissant ma case excel de telle façon : AVERAGE(x:B5000)

Mais je voit pas trop comment m'y prendre. (L'inconvénient de ne rien connaître en programmation, c'est pas les idées, c'est la manière)

J.

ucfoutu · Answer

Ce'est bien évidemment possible, dès lors que tu as mémorisé quelque part le point de départ :
La lecture de 
cette discussion devrait te permettre un mise en oeuvre sans faille.

____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

shinrei69 · Answer

Je peux faire en appuyant sur début : 

Range("A1").Text = "=MOYENNE(B1:B5000;0)"


Appliquer une fonction NBVAL dans A2 par exemple

Dim nombredepesée as string
noombredepesée = Range("A2").Value

En cas de reprise : 

Range("A1").Text = AVERAGE(nombredepesée:A5000)


C'est l'idée non?

ucfoutu · Answer

C'est l'idée non?
Je pense que tu te doutes de la réponse que je fais habituellement : ===>> teste et tu sauras 


____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

ucfoutu · Answer

et tu n'appliques très manifestement pas ce que tu peux lire dans la discussion dont je t'ai donné le lien plus haut !
(c'est du moins ce que me donne à penser ta variable noombredepesée.
Fais donc un effort de lecture, d'analyse et de compréhension. Ce sont (avant tout développement) les bases même de la mise en oeuvre d'un développement.
Je le dis avec beaucoup d'amitié et souhaite que tu n'interprêtes pas mes propos comme la marque de quoi que ce soit d'autre.


____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

shinrei69 · Answer

Je le dis avec beaucoup d'amitié et souhaite que tu n'interprêtes pas mes propos comme la marque de quoi que ce soit d'autre. 

Il n'y aucun problèmes venant de ma part ne t'inquiètes pas pour ça, je comprend totalement et admire même parfois. 

Je vais essayer et rendre compte si problèmes il y a :)

shinrei69 · Answer

Cells(30, 3).Value = Application.Average(Range(Cells(3, 6), Cells(ligne, 6))) 

Je ne voit pas trop comment adapter finalement. 

Je ne voit nul part comment définir la cellule ou je veux afficher le résultat, ni comment définir dans quelle colonne je veux appliquer la moyenne.

ucfoutu · Answer

Cells(30, 3).Value = Application.Average(Range(Cells(3, 6), Cells(ligne, 6))) 
C'est pourtant simple :
1) Cells(30, 3) est la cellule d'affichage, que tu peux remplacer par (exemple pour la cellule A3 :
Range("A3).value
Tu peux même avoir, si tu le préfères, avoir ton résultat dans une variable nommée par exemple toto, ainsi :
toto = Application.Average(Range(Cells(3, 6), Cells(ligne, 6))) 
2) analyse la partie :
Range(Cells(3, 6), Cells(ligne, 6))
elle ne représente que la plage à laquelle appliquer ta moyenne !
C'est, dans ton cas : la plage Range("B": & dernière_ligne_mémorisée & ":B5000")
Allez : essaye donc.


____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

shinrei69 · Answer

Voila ce que j'ai fait : 

dernière_ligne_mémorisée = Range("G2").Value
Range("D2").Value = Application.Average(Range("B" & dernière_ligne_mémorisée) & ":B5000")

Mais ça me renvoit la valeur #VALEUR puisque je réinitialise mon classeur Excel à chaque démarrage si ce n'est pas une reprise :/

bigfish_le vrai · Answer

Bonjour,

bon OK , j'arrive avec le travail mâché, mais c'est un peu comme si un snippet existait. J'utilise souvent une petite fonction perso pour ce type de besoin :

Donc voici la démo qui répond à ton besoin, plus la fonction:

Sub Demo()
    Dim MaColonne As String, DerniereValeur As String, MaPlage As Range
    MaColonne = "B"
    'recherche de la derniere valeur
    DerniereValeur = LastValue(MaColonne)
    '
    If Not DerniereValeur = vbNullString Then
        'on definit la plage de travail
        Set MaPlage = Range(MaColonne & "1:" & DerniereValeur)
        'y a t'il plus d'une valeur sur MaPlage
        If Application.WorksheetFunction.CountA(MaPlage) > 1 Then
            'si oui
            MsgBox Application.WorksheetFunction.Average(MaPlage) 'à remplacer par la cellule qui reçoit le résultat
        End If
    End If
End Sub

'la fameuse fonction:
Function LastValue(ByVal MaCol As String, Optional ByRef MaFeuil As Worksheet) As String  'recherche de la derniere valeur d'une colonne
    'cette fonction renvoi l'adresse de la derniere cellule non vide
    If MaFeuil Is Nothing Then Set MaFeuil = ActiveSheet
    LastValue = vbNullString
    On Error Resume Next
        LastValue = MaFeuil.Range(MaCol & Columns(MaCol).Cells.Count).End(xlUp).Address(False, False)
    On Error GoTo 0
End Function

A+

shinrei69 · Answer

Hum je ne décode pas tout mais si je fait un copié collé en remplaçant par la cellule ça marche?

Je trouve étrange qu'il n'y ai pas de formules plus simples pour ça :O

Je pensait juste qu'en définissant la dernière cellule active je pourrais faire une moyenne en appliquant un  Range("XX").Value = "= moyenne(Lastcell:B5000)"

:/

ucfoutu · Answer

shinrei69 :
1) tu nas nune parenthèse (en rouge ici) de trop et une autre (en orange ici) en moins !
Range("D2").Value = Application.Average(Range("B" & dernière_ligne_mémorisée) & ":B5000"))
2) si j'ai bien compris, tu veux également éviter le cas où aucun N° de ligne ne figurerait en G2 
Voilà donc :

dernière_ligne_mem = Range("G2").Value
 If dernière_ligne_mem > 0 Then
   Range("D2").Value = Application.Average(Range("B" & dernière_ligne_mem & ":B8"))
 Else
   Range("D2").Value = ""
 End If

Code à placer dans l'évènement que tu veux

____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

ucfoutu · Answer

cortrige B8 en B5000 (bien sûr).


____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

shinrei69 · Answer

Je me disait que je trouvais pas l'erreur aussi :O

Et donc par ce moyen je peux carrément enlever la fonction =MOYENNE toussa toussa dans la cellule non? :D

ucfoutu · Answer

évidemment !

____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

ucfoutu · Answer

Et quand tu y seras, on pourra passer à une fonction personnalisée en lieu et place (plus pratique). Mais j'attends que tu te sortes d'abord de ce que je t'ai déjà dit (et on y viendra ensuite).

____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

shinrei69 · Answer

Erreur 1004 sur ta ligne ;O

J ai mis comme tu me l as conseillé le code suivant ; 

dernière_ligne_mem = Range("G2").Value
 If dernière_ligne_mem > 0 Then
   Range("D2").Value = Application.Average(Range("B" & dernière_ligne_mem & ":B8"))
 Else
   Range("D2").Value = ""
 End If

shinrei69 · Answer

En remplaçant ce que tu m a dit!

bigfish_le vrai · Answer

Salut,

je comprend pas vraiment ton besoin mais ce qui est sur, c'est que sur la ligne:

Range("D2").Value = Application.Average(Range("B" & dernière_ligne_mem & ":B8"))

si la variable "dernière_ligne_mem" est supérieur ou égale au 8 de "B8", tu auras effectivement une erreur 1004

A+

shinrei69 · Answer

Salut, 

je comprend pas vraiment ton besoin mais ce qui est sur, c'est que sur la ligne: 

Code Visual Basic :
Range("D2").Value = Application.Average(Range("B" & dernière_ligne_mem & ":B8"))


si la variable "dernière_ligne_mem" est supérieur ou égale au 8 de "B8", tu auras effectivement une erreur 1004 

Il s'agit ici d'un problème pour palier à une variation de moyenne de poids lors d'une commande sur plusieurs jours (Le poids de mes composants évolue infimement et passe en dehors des intervalles).

J'ai aussi l'erreur alors que j'utilise B5000 dans mon cas, pas B8 :/

bigfish_le vrai · Answer

re,

oubli ce que je t'ai dit en faite cela fonctionne même si "dernière_ligne_mem" est supérieur ou égale au 8 de "B8".

peux-tu nous donner un peu plus de code autour de cette ligne ?

A+

shinrei69 · Answer

peux-tu nous donner un peu plus de code autour de cette ligne ? 


Bien sur mais ce n'est pas forcément lié avec le principe de la moyenne :/


If test12 = vbYes Then 'si la réponse à msgbox est oui
IS_NEW = False 'indique qu'il ne s'agit pas d'un nouvel OF

dernière_ligne_mem = Range("G2").Value
 If dernière_ligne_mem > 0 Then
   Range("D2").Value = Application.Average(Range("B" & dernière_ligne_mem & ":B5000"))
 Else
   Range("D2").Value = ""
 End If
Dim Nb_l '
Nb_l = Range("A5000").End(xlUp).Row - 1 'selectionne la dernière cellule active
Nb_line = Nb_l 'continue l'enregistrement à Ligne + 1 à chaques enregistrements
OF = "TestOF"
ButtonBegin.Enabled = False
ordredefabrication = Range("I2").Value
Labdel6 = "Ordre de fabrication N° : " & Range("I2").Value
Label6.TextAlign = fmTextAlignCenter


End If



Voila le bloc en question.

bigfish_le vrai · Answer

en fait ce que j&#8217;espérai voir, c'est comment tu renseignes la valeur en G2 ! et comment est dimensionnée la variable "dernière_ligne_mem"

mais bon ! 
En générale l'erreur 1004 apparaît quand on fait appel à un objet qui n'existe pas ou quand on fait appel à un membre d'une collection qui n'appartient pas à la collection ou qui n'existe pas. Ou bien encore quand l'objet en question n'est pas utilisé correctement dans la méthode.

Dans ton cas, une mauvaise utilisation de l'objet pourrait être le fait que "Range("B" & dernière_ligne_mem & ":B5000")" ne pointe pas sur une plage qui contient des valeurs. Cela peu se produire si la feuille active n'est pas la feuille qui contient la plage de "B" & dernière_ligne_mem & ":B5000" car ici tu ne spécifies aucune feuille !
Ou encore si la cellule de destination "D2" est verrouillée dans une feuille protégée.

A+

ucfoutu · Answer

Ton erreur 1004 signifie que le contenu de ta cellule G2 est mauvais !
G2 doit nécessairement  contenir un nombre entier, sans décimales (le numéro de la ligne de départ) et rien d'autre .


____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

shinrei69 · Answer

La valeur en G2 est renseignée dans le tableau directement par la fonction "NBVAL". 
Pour la variable j'ai juste copié et adapté ce que ucfoutu m'a mis :/

ucfoutu · Answer

La valeur en G2 est renseignée dans le tableau directement par la fonction "NBVAL". 

Il te faut oublier NBVAL !
C'est quand tu choisis de "clore" une "session", que tu dois renseigner G2. Et le faire en y mettant le dernier n° de ligne (la dernière ligne non vide de la colonne B) !

____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

ucfoutu · Answer

C'est simple :
Tu sais à quel moment tu décides de terminer une "tranche" (je suppose que tu le fais par un bouton cliqué, à moins que ce ne soit dans l'évènement de fermeture de ton classeur ... Ca, tu es le seul à le savoir).
C'est à ce moment, que tu dois renseigner G2, de cette manière :
Range("G2").Value = Range("B" & Rows.Count).End(xlUp).row

____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

shinrei69 · Answer

J'y ai pensé mais si je renseigne G2 à la fin, du coup, ça renseigne aussi D2 à la fin aussi, et ma moyenne "changeante" est mon poids de référence. :/

ucfoutu · Answer

si je renseigne G2 à la fin, du coup, ça renseigne aussi D2 à la fin aussi
J'ai des difficultés à te comprendre, ne sachant bpas ce que :
- tu souhaites mettre en oeuvre (en détail et avec explications très précises - avec un exemple de séquences au besoin)
- tu as mis en oeuvre

Une chose me parait certaine, par exemple :
- G2 n'est modifiée que si tu la modifies, quand tu décides de la modifier
- D2 n'est modifiée que lorsque tu décides de calculer (mon code plus haut) la moyenne de la dernière tranche. Et je ne vois pas ce qui oblige à changer D2 chaque fois que tu change G2.
Je ne suis à ce stade ni dans ta teête, ni au dessus de ton épaule pour voir le déroulement et la raison de ton mécanisme (celui mis en place et celui que tu souhaiterais mettre en place).
Explique-nous pas à pas, genre :
1) J'ouvre ma première session de pesage
et à cette occasion ==>> énumère avec détails
2)J'ouvre une seconde session et à cette occasion ===>> énumère avec détails


____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

shinrei69 · Answer

Ok ok je vais tenter de faire ça avec clarté.

B2:B5000=> Plage de mesure
D2=> Moyenne de la plage de mesure et poids de référence. (Qui évolue constamment)
G2 => Nombre de pesée effectuées. 


1) Je débute le programme, mon classeur se remet à Zéro.

2)Deux choix s'offrent, soit c'est une nouvelle commande, soit une reprise de commande

3) Si il s'agit d'une nouvelle commande, ma colonne B se remplie au fur et à mesure, D calcule la moyenne de cette plage. (D2 est aussi mon poids de référence pour comparaison de pesée)

4) Si il s'agit d'une reprise, le classeur ne se remet pas à zéro et enregistre à N+1 mes nouvelles valeurs mesurées. 

5) Dans le cas de la reprise, je voudrais que la moyenne faite précédemment (Lorsque la reprise n'était alors qu'une nouvelle commande) se réinitialise et qu'on commence une nouvelle moyenne à partir de l"enregistrement N+1 jusqu'à B5000.

6) Soit j'enregistre soit je quitte.


J'ai donc uniquement deux cas, lorsqu'il s'agit d'une nouvelle commande on applique une moyenne normale à D2

Et lorsqu'il s'agit d'une reprise, D2 (qui est toujours ma moyenne), est la moyenne de la plage depuis l&#8217;arrêt jusqu'à la fin.


Plus de précisions?

ucfoutu · Answer

Cela commence à devenir moins flou.
Il nous reste maintenant à savoir où et comment (par quelle méthode et quel code) se détermine ceci :
2)Deux choix s'offrent, soit c'est une nouvelle commande, soit une reprise de commande 

Car c'est à mon avis à la suite du choix fait ici que tout doit être déclenché
Montre ce code en nous précisant tout (bouton, évènement, etc ...)
____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

shinrei69 · Answer

Car c'est à mon avis à la suite du choix fait ici que tout doit être déclenché 
Montre ce code en nous précisant tout (bouton, évènement, etc ...) 


C'est définit suivant le code suivant régit par un commandbutton : 

Private Sub ButtonBegin_Click()
Dim test7 As Integer 'annonces des msgbox
Dim test12 As Integer 'annonces des msgbox
Range("D28").Value = (UserForm4.Label4.Caption / 100) 'divise les valeurs par 100 pour les calculs
Range("D29").Value = (UserForm4.Label3.Caption / 100) ' pareil

 
test12 = MsgBox("S'agit-il d'une reprise?", vbYesNoCancel + vbExclamation, "Programme de pesée")
If test12 = vbNo Then 'si la réponse est "non" dans la msgbox
    IS_NEW = True 'indique qu'il s'agit d'un nouvel OF
    Range("A2:A5000").Value = "" 'rend vierge les case de l'intervalle
    Range("B2:B5000").Value2 = "" ' pareil
    ColorBox.BackColor = &H8000000F 'code gris
    Nb_line = 0 'indique que le classeur doit selectionner les cellules 0
    OF = "TestOF"
    Range("A2:A5000").Offset(Nb_line, 0).Interior.Color = vbWhite 'rend blanches les cases     
    Range("B2:B5000").Offset(Nb_line, 0).Interior.Color = vbWhite 'pareil
    ButtonBegin.Enabled = False 'rend la bouton "début" inactive
    UserForm2.TextBox1.Value = "" 'remise à 0 de l'ordre de fabrication
    UserForm2.Show 'affichage de la form pour rentrer l'ordre de fabrication
    ordredefabrication = UserForm2.TextBox1.Value 'definition de la variable
    Range("I2").Value = ordredefabrication 'ecrit le nombre rentré dans la textbox dans I2
    Label6.TextAlign = fmTextAlignCenter 'style d'écriture du label6
    Label6.Caption = "Ordre de fabrication N°  " & ordredefabrication 
 
If ordredefabrication = "" Then 'si aucun OF est rentré
    test7 = MsgBox("Vous n'avez pas rentré de N° d'OF", vbOKOnly + vbExclamation,   "Programme de pesée")
    ButtonBegin.Enabled = True
End If


If test12 = vbYes Then 'si la réponse à msgbox est oui
   IS_NEW = False 'indique qu'il ne s'agit pas d'un nouvel OF

    Dim Nb_l '
    Nb_l = Range("A5000").End(xlUp).Row - 1 'selectionne la dernière cellule active
    Nb_line = Nb_l 'continue l'enregistrement à Ligne + 1 à chaques enregistrements
    OF = "TestOF"
    ButtonBegin.Enabled = False
    ordredefabrication = Range("I2").Value
    Labdel6 = "Ordre de fabrication N° : " & Range("I2").Value
    Label6.TextAlign = fmTextAlignCenter


End If



End Sub



Avec en rouge les deux parties faisant la différence. (Ne vous moquez pas pour le code, y'a certainement plus simple mais il marche ^^)

shinrei69 · Answer

Les balises couleurs n'ont pas fonctionné. Srry!

ucfoutu · Answer

Ben ! C'est tout vu, dans ce cas !
If test12 = vbNo Then 
.....
End if (le dernier, tout en bas)
C'est juste avant ce dernier end if que tu dois modifier G2


____________________
Utiliser le bouton "REPONSE ACCEPTEE" sur une réponse exacte facilite les recherches ultérieures d'autres forumeurs. PENSEZ-Y SVP

Changer de moyenne par action dans le classeur

37 réponses

Votre réponse

Discussions similaires