Valeur max/min/moyenne d'une colonne avec plusieurs critères

[Neimad Zark]

Bonjour a tous,

Je ne suis pas un habitué des forums, et par conséquent n'hésitez pas à me prévenir si quelque chose ne va pas.


Alors voilà, j'ai un devoirs à faire en VBA mais je ne suis pas très doué en la matière. Je viens donc vous demander un peu d'aide.

Vous trouverez en pièce jointe le document que je dois remplir. L'objectif étant de remplir les onglets "Stock" et "Analyse" par une solution VBA.

Pour ce qui est de l'onglet Stock j'ai de peties idées, on verra bien.

Ma demande est pour l'onglet "Analyse". Je ne vois pas trop comment m'y prendre pour replir le prix min/max/moyen selon le code produit.


Pour ce qui est de la fonction Max j'avais pensé à cela (grâce à ce Forum) :

Sub PRIXMax()

Dim VLanalyse
Dim VMyRange As Range
Dim VPmax As integer
    
For VLanalyse = 3


     Set Myrange = Sheets("Mouvement").Range("B2:B" & Sheets("Mouvement").Range("B" & Rows.Count).End(xlUp).Row)
    
     Pmax= Application.WorksheetFunction.Max(VMyRange)

     Cells("E, VLanalyse") = Pmax

Next VLanalyse     


 End Sub

Voilà, je pense que ce bout de code doit déjà être trufféd 'erreur, et surtout, je ne vois pas comment faire cette "double boucle" (boucle sur les "Code produit" dans l'onglet Stock puis boucle sur les prix dans l'onglet "Mouvement").



Je remercie par avance tout ceux qui sont prêt à m'apporter leur aide.

Bien cordialement,

Neimad ; )


PS : Il faudrait même je pense une tripple boucle :

- boucle 1 : recherche code produit (dans onglet Analyse)
- boucle 2 : recherche de ligne dans l'onglet Mouvlement ayant ce code produit
- boucle 3 : recherche uniquemetn des achats (ou des ventes)

Pour au finaal avoir plusieurs variables différentes :
- PminA
- PmaxA
- PmoyA
- PminV
- PmaxV
- PmoyV

 

[Neimad Zark]

Re : Valeur max/min/moyenne d'une colonne avec plusieurs critères

Bonjour a tous,

Je poste un éème message pour faire remonter le premier, mais également pour préciser que normalement ce devoirs n'est pas sencé être d'un niveau très élevé, et que par conséquent il faudrait que la solution apportée reste relativement simple. (Par exemple boucle avec For/Next plutôt que des While...)

En espèrant que quelqu'un arrice a me débloquer.

Merci à vous.

Neimad ; )

 

[Modeste]

Re : Valeur max/min/moyenne d'une colonne avec plusieurs critères

Bonjour Damien(?) et bienvenue,

Eh quoi, tu n'étais pas au cours!? Sérieusement, le début de solution que tu proposais n'irait pas très loin, tu en seras d'accord, je pense?

Puisque c'est ton devoir, tu n'attendais pas de trouver ici quelqu'un qui le ferait à ta place? Alors, si tu fais un effort, mais que tu rencontres des difficultés, il se trouvera certainement plein de bonnes volontés pour répondre à tes questions! ... Mais il faut que tu y mettes (plus qu') un peu du tien!

 

[vgendron]

Re : Valeur max/min/moyenne d'une colonne avec plusieurs critères

Bonjour Damien
Alors pour commencer. comme le dit Modeste (que je salue), il faut réfléchir un minimum..
à l'école. il y a longtemps ;-) mon prof disait que seule comptait la première étape du programme: à savoir. l'algorithme
ensuite. le codage. c'est secondaire.. c'est juste une traduction avec des mots réservés selon le langage utilisé..
pour un débutant. cette méthode est largement suffisante.

donc. dans ton cas. il faut juste que tu développes ton algo.
en écrivant boucle 1, boucle 2 et boucle 3, tu as déjà commencé. faut juste préciser un peu plus..

ex pour débuter:
for chaque CODEcherché dans la colonne A de la feuille Analyse
Chercher dans la feuille mouvements toutes les lignes qui ont ce CODEcherché
... ... ...que fais tu ensuite?? maintenant que tu as les lignes? comment calcules tu les prix moyens mini et maxi?


fin for
....

une fois que ton algo est défini, tu auras sans doute besoin de la touche F1 et de ce forum

bon courage
PS: on pourrait te donner la solution directement.. mais tu n'en tirerai rien. et tu risquerais meme d"être incapable d'expliquer le programme à ton prof..et ca.. le jour d'un exam.. je peux t'assurer que c'est pas bon du tout...

 

[Neimad Zark]

Re : Valeur max/min/moyenne d'une colonne avec plusieurs critères

Merci pour vos réposnes.

Pour ce qui est de l'algo j'avais établi le pseudo-algorithme suivant :

Pour chaque Code produit (onglet Analyse)
	Rechercher dans l’onglet Mouvement chaque achat (Nature Opération = 1)
		Pour chaque Nat. Op. = 1 incrémenter une variable Max, Min, Moyenne
		Remplir les colonnes du tableau de l’onglet Analyse avec les variables
	Passer à la Nature d’Opé suivante (ventes)
Passer au Code produit suivant

Pensez vous qu'il soit correcte ? (désolé, j'aurai dû le mettre dans mon premier poste).

@ Modeste : non je ne suis aps en cours car je suis salarié ^^ [en contrat pro]. Je ne fais pas du tout des études d'informatique, et c'set plus moi qui m'interesse à VBA.

@Modeste 2 : effectivement, Neimad | damieN (mais on va garder Neimad ^^)

@ vgendron : j'ai déjà fait 2~3 "projets" VBA dont je suis plutôt content (mais si bien sûr ça n'ai pas du top level ^^) Mais pour celui là, je n'arrive pas du tout à commencer...

Ce que je ne demande ce n'est pas la solution, ou en tout cas surtout pas la solution sans explication ! J'aimerais progresser et être capable de me débrouiller sur des projets simples sans me prendre trop la tête c'est tout [Je ne cherche pas à être un "pro" mais à avoir un minimum de bases quoi].


PS : je posterai un projet que j'avais fait, vous pourrez peut être me dire si il y a des points communs entre les 2 projets.


Encore merci

PS 2 : Ce qui me pose vraiment problème là c'set que je n'ai jamais utilisé de fonction min/max/moyenne en VBA (et je ne comprend pas trop les codes correspondants), et en faisaitn des recherche j'ai vu que les autres eprsonnes utilisaient des variable de type : "MyRange As Range" or moi je ne connais que les variable de type Integer, Double, [ou String...])


Bonne journée.

Cordialement

 

[vgendron]

Re : Valeur max/min/moyenne d'une colonne avec plusieurs critères

Bon alors,
deux pistes
1)
pour utiliser les fonctions Max, min d'excel en VBA, il suffit d'écrire

PrixMax=application.max(val1, val2)
PrixMin=application.min(val1,val2)

pour la valeur moyenne. suffit d'en faire la somme avant et de compter le nombre de valeur
som=som+valeur
NbVal=NbVal+1

Moyenne=som/NbVal

ca. c'est pour la partie VBA: dans ce cas. tu imagines bien qu'il va falloir boucler sur tout le tableau avec beaucoup d'itérations (dont la plupart seront inutiles)

2)
et pour tout ca. finalement. tu n'as meme pas besoin de VBA.. une "simple" formule peut faire l'affaire..
en utilisant SommeProd

après. je crois comprendre que tu dois le faire en VBA

ca donnerait donc un truc du genre

déterminer le nb de codes présents dans la feuille Analyse
NbCodes=sheets("Analyse").range("A2").end(xldown)-2
Détermine le nb de codes présents dans la feuille Mouvements
NbMvts....

les fameuses boucles
pour i=1 to NbCodes
    Pour j=2 to NbMvts
         si la cellule Aj contient le code
                  si c'est un achat
                           NbAchat=NbAchat+1
                           SomAchat=SomAchat+cellule Gj
                           MaxAchat=application.max(MaxAchat,Gj)
                           MinAchat=application.min(MinAchat,Gj)
                 sinon (c'est donc forcément une vente
                           NbVente=....
                           SomVente..
         sinon cellule suivante
  Next j
  calcul des moye, et mise à jour du tableau Analyse avec les valeurs MaxAchat, MinAchat MoyAchat MaxVente....
remise à zero des variable NbAchat moy min max....
NExt i (code suivant)

 

[Neimad Zark]

Re : Valeur max/min/moyenne d'une colonne avec plusieurs critères

Merci beaucop pour cette réponse !

Bon alors j'ai quand même quelques petites questions de compréhension :

- pourrais -tu m'expliquer

NbCodes=sheets("Analyse").range("A2").end(xldown)-2

?

--> le " -2 " signifie que la boucle va "descendre" de 2 lignes à chaque fois non ? (donc on va sauter des valeurs ?)
--> le " sheets("Analyse").range("A2") " signifie que l'on va commencer à dessendre à partir de A2 ?

Après je n'ai pas de consignes, mais je préfèrerais avoir un code permettant de calculer sur un nombre de ligne indéfini (c'est à dire que je puisse rajouter des articles [nouveaux codes], et également de nouveaux mouvements (et pas uniquement 599 comme dans le fichierque j'ai mis à disposition).

Par conséquent je ne pourrai pllus utiliser une construction de type

Dim VCode as Integer      'car là c'est forcément un entier

           For VCode = 1 To VNbCode
                     (...)
           Next VCode

Est-ce que tu pourrais juste m'aiguiller encore là dessus ?


Pour ce qui est des Max, Min, Moy, merci beacoup !!!

--> Juste une pettie question encore [et oui, désole ^^]

Dans

MaxAchat=application.max(MaxAchat,Gj)

par exemple, à quoi correspond le " Gj " ?

Si j'ai bien compris, dans ton exemple, "j" est une variable, et donc cette "formule" permet de donenr à la variable MaxAchat le montant maximum qui se trouve dans l'ensemble des cellules Cells(G, variable j)

Désolé si mes questions sont très scolaires et pas très claires...


Un grand merci encore, j'essayerai de coder ça ensuite ce soir, et je posterai le résultat.


Neimad

 

[vgendron]

Re : Valeur max/min/moyenne d'une colonne avec plusieurs critères

Alors
d'une. je me suis trompé. désolé.. à la place de

NbCodes=sheets("Analyse").range("A2").end(xldown)-2

il faut mettre

NbCodes = Sheets("Analyse").Range("A2").End(xlDown).Row - 2

je me place en cellule A2 de ta feuille Analyse (A2 et pas A1 parce que les mouvements commencent à la ligne2: la ligne 1 c'est juste les entetes..
pour le End(xldown): à partir de A2, il va à la fin vers le bas:
pour comprendre: dans ta feuille excel Analyse: clique sur la cellule A2:

et fais un double clic sur le bas de cette cellule(le curseur en forme de croix se transforme en un curseur avec la forme de "déplacement") , excel se déplace alors jusqu'à la dernière cellule en ligne 32
c'est donc ce que code le VBA.

Note (si en plus du double clic, tu maintiens la touche Shift), tu verras que excel sélectionne de A2 à A32
Tu peux faire la meme chose vers la droite, le haut ou la gauche. tout dépend sur quel coté de la cellule tu double cliques

pourquoi -2: parce que comme ton tableau commence en ligne 2, je dois ajuster la ligne (....row = 32) en faisant -2 pour avoir les 30 codes

dans le cas de NbMvts.. ce sera -1


Gj, c'était bien la cellule située en colonne G et en ligne j (j étant une variable qui change à chaque fois)
en fait: pour définir la celulle Gj, il faut écrire: Cells(j, 7): ligne j, colonneG (7eme lettre de l'alphabet)
ou encore cells(j,"G") ou meme Range("G" &j)

dans la boucle à laquelle je pense:
pour chaque code de la feuille Analyse (exemple i=1)
pour chaque ligne j de la feuille Mouvements
je mets à jour le max et le min
MaxVentes=application.max(Maxventes,Cells(j,7))

 

[Neimad Zark]

Re : Valeur max/min/moyenne d'une colonne avec plusieurs critères

Totud 'abord, encore merci.

Par contre je suis désolé mais pour le " -2 " et le "Range("A2") je ne comprend toujrosu aps, moi j'aurais coeé :

NbCodes = Sheets("Analyse").Range("[B]A3[/B]").End(xlDown).Row

(on m'avais dis que le mettre le " -1 " n'était pas obligatoire contrairement au " +1 " dans .end(xlup)

NbCodes = Sheets("Analyse").Range("[B]A3[/B]").End(xlDown).Row -[B]1[/B]

Dans ma logique (qui vaut ce qu'elle vaut ^^) là, on part de A3, la première cellule contenant un Code produit, puis on dessend de 1 ligne par 1 ligne.
Mais tu as l'air de me dire que je me trompe c'est ça ? je comprend mal la construction de la "formule" ?



Pour le " Gj " oui ok c'est bon j'avais donc bien compris, merci.


En réfléchissant mieux, je pense que le code que tu m'as donné me permet de rajouter des lignes dans mes tableaux sans avoir à recodé non ?

--> si je passe de 30 à 48 produit, sans changé le code, j'abtiendrais NbCode = 48 et plus NbCode = 30 c'est bon non ?



Dernière petite question, on m'avait, il me semble, conseillé d'utilisé plutôt (xlup) que (xldown), mais je ne me souviens plus pourquoi, est-ce vrai ? (ce n'est pas une histoire de pouvoir passer outre des lignes vides ?)

exemple : de A1 à A30 c'est rempli, puis A31 est vide, puis de A32 à A60 c'est rempli.
Bon là, ça n'a vraiment plus rien à voir avec mon cas, donc si ça t'embète de répondre à cela n'hésite pas à ne pas y répondre ^^



Neimad : D

 

[vgendron]

Re : Valeur max/min/moyenne d'une colonne avec plusieurs critères

en fait. que tu commences en A2 (ligne d'entete) ou A3 première donnée, le .end(xldown) va aller sur la ligne 32
or. tu es bien d'accord que 32, n'est pas la valeur attendue. entre la ligne 3 et la ligne 32,tu as bien 30 codes

essaie ceci pour t'en convaincre
sub test()
Sheets("Analyse").Range("A1").End(xlDown).Select
MsgBox ("la cellule sélectionnée est A" & Selection.Row)
Sheets("Analyse").Range("A2").End(xlDown).Select
MsgBox ("la cellule sélectionnée est A" & Selection.Row)
Sheets("Analyse").Range("A3").End(xlDown).Select
MsgBox ("la cellule sélectionnée est A" & Selection.Row)
end sub

pour le xlup, c'est effectivement préférable surtout si ton tableau contient des lignes vides: en effet, le .end va dans le sens spécifié (xldown, xlup, rltoright xltoleft) jusqu'à la première cellule NON vide si tu pars d'une cellule NON vide (ex: de A2 vers A32) ou jusqu'à la dernière cellule Vide si tu pars d'une cellule vide
range("65536").end(xlup).row

65536 étant la dernière ligne d'une feuille excel......2003.. en 2007, je ne sais jamais..

--> si je passe de 30 à 48 produit, sans changé le code, j'abtiendrais NbCode = 48 et plus NbCode = 30 c'est bon non ?

Exactement !

 

[Neimad Zark]

Re : Valeur max/min/moyenne d'une colonne avec plusieurs critères

Bonsoir,

Alors voilà, j'ai essayé de codé avec le code que tu m'as proposé, mais il y a quelque chose qui ne va pas, car au final, j'ai juste la colonne du prix moyen des achats qui se rempli pour 0.00 € (mêmes les entêtes).

Pourrais-tu me dire d'où viens mon erreur ?


Encore merci

Neimad

 

[Modeste]

Re : Valeur max/min/moyenne d'une colonne avec plusieurs critères

Bonsoir Neimad Zark , vgendron

Ça fait plaisir de voir que la première impression n'était pas la bonne

En ligne 72 et suivantes (la 1ère instruction étant Sheets("Analyse").Cells(VLigneA, 3) = PMoyAchats) tu oublies de changer de colonne: c'est la colonne 3 pour les 6 lignes
Tu insères 2 x le PMoyAchats par ailleurs

... pas regardé le reste

[Edit:] en regardant la suite, il manque à différents endroits, une référence à la feuille concernée :
il faut écrire PMinAchats = Application.Min(PMinAchats, Sheets("Mouvements").Cells(VLigneM, 7)) à chaque fois

 

[Neimad Zark]

Re : Valeur max/min/moyenne d'une colonne avec plusieurs critères

Bonsoir Modeste,

Ça fait plaisir de voir que la première impression n'était pas la bonne

C'est moi qui avais fais une mauvaise impression ? si c'est le cas je m'en excuse. [pourquoi ?]


Pour l'oublie de changement de colonne, j'étais tellement sûr de l'avoir fait que je le remarquais même pas en me relisant ^^

Pour le reste j'en prend note

J'essaye cela et je reviens vous dire ce qu'il en est !


Merci pour la réponse,

Neimad


EDIT: J'ai apporté les modifications dans la foulée, et effectivement j'ai maintenant les 6 colonnes de mon tableau qui se remplissent (mais toujours avec de 0.00 € partout), et toujours les entêtes qui sont touchées.

Je présume que pour le problème dse "0.00 €" cela vient de mes lignes

'Initialisation des compteurs:

    NbCodes = 0
    NbMouv = 0

    NbAchats = 0
    PMinAchats = 0
    PMaxAchats = 0
    SomAchats = 0
    PMoyAchats = 0
      
    NbVentes = 0
    PMinVentes = 0
    PMaxVentes = 0
    SomVentes = 0
    PMoyVentes = 0

non ?

Pourtant c'est bien avant ma première boucle, du coup lorsque l'on arrive en fin de boucle (mais avant la remise à 0), mes variable devrait avoir une somme bien différente de 0.00 € non ?

Et toujours dans la même logique, me "remise à 0 des variables concernées" se fait bien après l'attribution dans valeurs dans les cellules. Par conséquent je ne comprends pas.


PJ : le fichier avec les modifications

 

[Modeste]

Re : Valeur max/min/moyenne d'une colonne avec plusieurs critères

Re²,

La première impression c'est (comme on l'avait dit vgendron et moi), comme si tu demandais qu'on fasse tes "devoirs" ... tu t'en es expliqué depuis et puis voilà, ce sont des choses qui arrivent sur un forum. L'essentiel est que tu as pris les choses en main

Dans le code, j'ai touvé quelques autres erreurs ... plus le courage de faire la liste maintenant (demain, si tu veux) à moins que vgendron ne hante le forum la nuit!

Ton fichier avec quelques corrections donc ... Quand tu auras le temps et la tête à ça, compare les codes et relève les différences. J'ai mis en commentaire le calcul des min et max pour les ventes (tu sauras adapter)
Ajouté aussi dans le fichier, une formule matricielle, pour comparer les chiffres obtenus selon les deux méthodes.

A demain, pour ma part

 

[vgendron]

Re : Valeur max/min/moyenne d'une colonne avec plusieurs critères

Hello !!
Bon je vois qu'on progresse à grands pas ;-)

j'ai joins les deux solutions dans le meme fichier (la logique est la meme)
et j'ai mis une solution avec formule (juste pour le prix moyen)