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 à tout les deux !

@ Modeste :

Merci pour cette autre façon de faire et la correction des erreurs.

J'ai eu le temps de regarder un peu et voilà :

Dim PMinAchats As Double ' *** PAS INTEGER !!
Dim PMaxAchats As Double
Dim SomAchats As Double
Dim PMoyAchats As Double

Dim PMinVentes As Double
Dim PMaxVentes As Double
Dim SomVentes As Double
Dim PMoyVentes As Double

Ca m'énerve de faire encore des fautes aussi bête que ça...

For VLigneA = 3 To NbCodes
    For VLigneM = 2 To NbMouv
         If Sheets("Mouvements").Cells(VLigneM, 1) = Sheets("analyse").Cells(VLigneA, 1) Then 'PAS NbCodes Then

--> Le "3" et le "2" ne sont que des optimisation/amélioration non ? si on avait laissé "1" cela aurait juste fait des "boucle vide" (qui ne servent à rien) ?
--> effectivement Cells(VLigneA, 1) parrait beaucop plus logique (et correct).

En fait je pense que mon problème c'est que je n'arrive pas [encore^^] à "traduire" du français en VBA ou du VBA en français de façon intuitive...

If PMinAchats > Sheets("Mouvements").Cells(VLigneM, 7) Then PMinAchats = Sheets("Mouvements").Cells(VLigneM, 7)
                            If PMaxAchats < Sheets("Mouvements").Cells(VLigneM, 7) Then PMaxAchats = Sheets("Mouvements").Cells(VLigneM, 7)

--> Merci beaucoup ! ça me parrait beaucoup plus claire comme ça (en tout cas pour moi, ça m'est plus lisible).


--> Mon Next VLigneM mal placé à la fin c'est pareil c'est parce que j'arrive pas à faire une "traduction en directe" mais c'est vrai que c'est erreur idiote car c'est logique...

Ajouté aussi dans le fichier, une formule matricielle, pour comparer les chiffres obtenus selon les deux méthodes.

Tu parles de ça :

Application.ScreenUpdating = True

?

Je ne comprend pas trop ce que tu comparre ? quelles 2 méthodes ? (avec et sans formule ?)


@ vgendron :Merci aussi ! ta solution avec l'utilisation de formules est plus complèxe je trouve, tout du moins elle est moins intuitive pour moi, je me pencherai dessus quand j'aurai un peu plus de temps pour essayer denmieux l'appréhender.



@ tout les deux :
Du coup je vais essayer de finir cet onglet ce soir (et pourquoi pas commencer l'autre onglet aussi : algo, début de code etc...)

 

[Modeste geedee]

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

Bonsour®

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)

voir https://www.excel-downloads.com/threads/sommeprod-max-ok-sommeprod-min-pas-ok.221234/
le B.A. BA par formule me parait plus simple ...
pour une solution VBA :
- insérer les formules par macro
- générer le TCD par macro

ou mieux et plus "Pro"
un requete SQL en tuilisant la feuille "Mouvements" comme source de donnée...



Hors sujet
à part pour le fun... (je suis indécrottable! )
à mon sens si c'est pour faire de la programmation à tout crins,
Excel (VBA) ne me semble pas la solution la plus pertinente (rigueur, stabilité, pérennité, intégrité, sécurité)

 

[Modeste]

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

Salut tout le monde,

Avec la demande de départ, telle que je l'avais comprise, je me suis dit qu'on était "obligés" d'utiliser VBA et des boucles For (pour un devoir, un exercice ou pour le fun et l'intérêt personnel, peu importe! ). Les boucles imbriquées et l'écriture dans les cellules à chaque passage ralentissent fort l'exécution; ce n'est effectivement pas ce qu'on appelle un code optimisé (mais si ça permet à Neimad Zark de s'initier aux joies de la programmation )

@Neimad Zark: si tu arrives à repérer les différences dans le code et, surtout, à comprendre la raison des modifications, il me semble qu'il n'y a pas lieu de te morfondre: tu es "en route" alors tu n'as plus qu'à continuer. Bon courage et surtout ... amuse-toi!

@Modeste geedee: "un requete SQL en tuilisant la feuille "Mouvements" comme source de donnée... " J'ai d'abord cru à une coquille: tuilisant au lieu de utilisant ... mais ce serait trop simple. Qu'ont donc en commun Excel et les travaux de toiture ? Sans nous faire une démonstration complète (à moins que tu t'ennuies) tu veux bien expliquer au jeune ignare que je suis, en un langage plus accessible, cette histoire de tuiles?

 

[vgendron]

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

et donc. pour finir
grace à l'intervention de Job ici
voir https://www.excel-downloads.com/threads/sommeprod-max-ok-sommeprod-min-pas-ok.221234/

on a les troix formules
L3=(SOMMEPROD((Codes=A3)*(Libelles=$C$1)*Prix))/SOMMEPROD((Codes=A3)*(Libelles=$C$1))
à tirer jusqu'en bas

M3=MIN(SI((Codes=A3)*(Libelles=$C$1);Prix))
formule matricielle à valider par Ctrl+Shift+Entrée
puis tirer vers le bas

N3=SOMMEPROD(MAX((Codes=A3)*(Libelles=$C$1)*Prix))
à tirer vers le bas
OU
N3=MAX((Codes=A3)*(Libelles=$C$1)*Prix)
formule matricielle à valider par Ctrl+Shift+Entrée
puis tirer vers le bas

 

[Neimad Zark]

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

Bonsoir tout le monde !


Tout d'abord, merci encore de continuer de suivre mon avancée.

@Modeste geedee:
Merci pour tes conseils/précisions, mais effectivement, le but ici, pour moi, de faire un projet en utilisant uniquement du VBA (pour me familiariser et m'habituer).

@Modeste:
Merci pour tes encouragements


@Tout le monde:

Bon je pense avoir pas mal avancé ce soir dans le "projet" (mais bon j'ai fini assez tard donc j'ai pas pu m'y mettre des heures).

Je vous met en pièce jointe l'avancement du projet (j'ai fait de la mise en forme aussi, histoire que ce soit moins "moche" a regarder).

Ce que j'ai fait :

--> J'ai crée un nouvel onglet qui me sert "d'écran principal" et me permet d'ouvrir mes userfom avec des boutons.
--> J'ai terminé (grâce à vous) ce qui doit être fait pour l'onglet "Analyse"
--> J'ai quasiment terminé pour l'onglet "Mouvement" mais je rencontre 2 petits problèmes dans mon code :

Dans mon userform "Ajouter un mouvement" (btm Gérer les mvt > btn valider) j'ai cette partie du code qui m’embête :

'Ajout des articles dans le tableau
Dim ligne As Double 'je déclare ma variable afin d'en donner une valeur par la suite

ligne = Range("A1").End(xlDown).Row + 1  'je selectionne la première ligne vide en partant du haut et allant vers le bas

Cells(ligne, 1).Value = Code_Prod

    'Cells(ligne, 2).Value = RechercheV(Cells(ligne, 1).Value, Sheets("Stock").Range("A2:B2").End(xlDown).Row, 2, VRAI)

Cells(ligne, 3).Value = CDbl(Nat_OP) 'la fonction CDbl permet de transformer un texte en format chiffre

    'If Cells(ligne, 3).Value = 1 Then Cells(ligne, 4).Value = "ACHAT"
    'End If
    'If Cells(ligne, 3).Value = 2 Then Cells(ligne, 4).Value = "VENTE"
    'End If

Cells(ligne, 5).Value = CDbl(Quantité)
Cells(ligne, 7).Value = CDbl(PU)
Cells(ligne, 6).Value = Cells(ligne, 7).Value * Cells(ligne, 5).Value

1)

Pour les 2 blocs If / End If j'ai une erreur : "End If sans bloc If" (à la base j'avais même fait un seul If puis Then puis Else puis End If) mais là le problème c'est que cela me mettait des ":" après le "Else" et que du coup ça ne fonctionne pas.

Pouvez(vous m'expliquer la cause de mes problèmes ?


2)

Pour la colonne B (Libellé) j'avais pour idée de faire comme un RechercheV type Excel afin que le libellé soit corrélé au numéro de produit. Mais 2 chose :
- je ne sais pas (plus^^) utiliser une formule excel dans du code VBA.
- j'aimerai savoir s'il existe un moyen d'arriver au même résultat en utilisant uniquement du VBA (pas de formule Excel)



Par avance merci encore :


Neimad



PS : @ vgendron :

c'est cool que tu te sois servi de mon document pour t’interroger sur SOMPROD / Min, au moins j'ai l'impression d'avoir servi un peu à quelque chose ^^


EDDIT :

J'avais pas vu, je n'ai pas pu mettre mon fichier car maintenant il est trop volumineux...

Il y a moyen de passer par un site hébergeur pour vous l'envoyer ?

(je ne me sens pas du tout capable de simplifier le document maintenant que tout est imbriqué avec des userform et des boutons...

EDDIT 2 :

Voici 2 liens pour télécharger le fichier (je ne sais pas si ça va marcher)

Partage-Facile : Fichier projet_vba_test__v6_.xlsm.html

Ce lien n'existe plus

Autrement je peux envoyer par mail ou par Skype

 

[Modeste geedee]

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

Bonsour®

Pour les 2 blocs If / End If j'ai une erreur :
'If Cells(ligne, 3).Value = 1 Then Cells(ligne, 4).Value = "ACHAT"
'End If
'If Cells(ligne, 3).Value = 2 Then Cells(ligne, 4).Value = "VENTE"
'End If

au plus court
en supposant que la cellule contienne toujours et exclusivement les valeurs 1 ou 2

Cells(ligne, 4).Value =IIf(Cells(ligne, 3).Value = 1,"ACHAT","VENTE")

ou plus didactique

Select Case Cells(ligne, 3).Value 
    Case  1
        Cells(ligne, 4).Value = "ACHAT"
    Case  2
        Cells(ligne, 4).Value = "VENTE"
    Case else ' au cas ou la cellule contient autre chose que 1 ou 2
        'Cells(ligne, 4).Value = "Bla-bla-bla"
End Select

 

[Neimad Zark]

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

Merci,

@ Modeste geedee :

Cells(ligne, 4).Value =IIf(Cells(ligne, 3).Value = 1,"ACHAT","VENTE")

J'aime beaucoup cette solution là ! je ne connaissais pas le " IIf " en faite cela fonctionnne de la façon suivante :
Si(cellule = critère, alors solution 1, sinon solution 2) c'est bien ça ?



@ tous :

Une solution pour mon "Recherche V" ?


Je suis quand même plutôt content je trouve que le fichier est pas trop mal (en grande partie grâce à vous bien sûr) pour quelqu'un qui ne connaissais même pas VBA il y a encore 4 mois.

Est-ce que sur ce qui a été fait pour le moment vous auriez des remarques, critiques ?

PS : sur le bouton graphique je vais codé une macro qui me créra des pages_graphiques en fonction des prix calculés sur la feuille "Analyse". Normalement ça, je l'ai déjà fait donc ça ne devrait pas poser de problèmes.

Il me reste maintenant à faire l'onglet "Stock" je m'y met ce soir : )

 

[Neimad Zark]

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

Bonjour a tous,

J'ai pas eu beaucoup de temps pour VBA, mais je reviens vers vous car j'ai "un peu" avancé.

Pour mon histoire de "rechercheV" j'ai trouvé la formule suivante :

Application.VLookup(critère cherché, plage de recherche, colonne du résultat, précision)

Lorsque je l'utilise de façon "simple" du style :

Cells(606, 2).Value = Application.VLookup(Cells(606, 1), Range("A2:B10"), 2, VRAI)

Cela fonctionne sans problème, mais maintenat j'aimerais obtenir la même formule mais de façon dynamique. J'ai essayé :

Cells(ligne, 2).Value = Application.VLookup(Cells(ligne, 1), Sheets("Stock").Range("A2:B1048576"), 2, VRAI)

Mais là, ça ne fonctionne plus... cela m'affiche " #N/A "

Quelqu'un aurait une solution ?

Merci,


Neimad ; )



EDDIT : Je viens de me rendre compte que cela n'a plus rien a voir avec le sujet de base (min/max etc...) est-ce que je dois recréer un topic ? où alors je reste ici comme c'est la continuïté du même projet ?

 

[Modeste]

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

Bonjour Neimad Zark, le fil,

Je viens de tester

Cells(ligne, 8).Value = Application.VLookup(Cells(ligne, 1), Sheets("Stock").Range("A2:B31"), 2, True)

sur le fichier précédent et ça fonctionne (avec le 'True' au lieu du 'VRAI')

Si tu as un #N/A, c'est peut-être la correspondance qui n'est pas trouvée?

 

[Neimad Zark]

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

Merci pour la réponse,

Malheuresement, cela ne fonctionne toujours pas...

voilà mon code dans VBE :

'VOILA MON CODE EN ERREUR

Cells(ligne, 2).Value = Application.VLookup(Cells(ligne, 1), Sheets("Stock").Range("A2:B31"), 2, True)

'VOILA MON CODE EN ERREUR

Je mets en pièce jointe un fichier simplifié (finalement, j'ai réussi facilement à en faire un !)

En attente de vos explications.

Cordialement,

Neimad






EDDIT :

Je pense que ce "projet" est quasiment "optimisé" au maximum. J'ai terminé tout ce que 'javais envisagé de faire dessus (tout les onglets [j'en ai même rajouté depuis], tout les userforms etc...)

Il me reste juste la petite lignede code avec ce " Application.VLookup ", et faire une page de graphiques. J'espère que j'aurai le temps de terminer tout cela ce soir et de vous le mettre le résultat final sur le forum
(je crois que j'ai compris ce qui rendaient mon fichier beaucoup trop gros : les images en fond sur mes userforms et sur mon "Menu Principal" ^^)


Merci beaucoup à tous pour votre aide.


Neimad

 

[Modeste]

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

Re-bonjour,

Lorsque tu écris

Cells(ligne, 1).Value = Code_Prod

... sachant que Code_Prod est le nom d'une TextBox, quel est le type des données qui y est stocké? Du texte, bien sûr

Regarde attentivement tes deux derniers codes en A600 et A601 (n'y a-t-il pas un petit triangle de couleur qui s'affiche?) Comme ces valeurs sont du texte et que dans ta feuille "Stock", tes codes sont de type numérique, le #N/A est bien le message disant que la valeur n'a pas été trouvée!

Donc, soit tu écris

Cells(ligne, 1).Value = Code_Prod * 1

... ce qui devrait convertir ton texte en donnée numérique

soit, au moment de ta recherche, tu fais

Application.VLookup(Cells(ligne, 1) * 1, ...

PS: on dit d'un code qu'il est optimisé lorsqu'on a mis en oeuvre toutes les techniques permettant qu'il s'exécute en un minimum de temps et en "économisant" autant que faire se peut, la mémoire et/ou le processeur. Dans ton cas, je ne suis pas certain que ce soit le cas, mais s'il fait ton bonheur et ta fierté, alors ... que demander de plus!?

 

[Neimad Zark]

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

@ Modeste :

Avec le " * 1 " ça marche effectivement beaucoup mieux merci.
Je comprends le principe mais je suis pas sûr de réussir à le retrouver dans un autre cas, (l'entrainement le dira ^^)

on dit d'un code qu'il est optimisé lorsqu'on a mis en œuvre toutes les techniques permettant qu'il s'exécute en un minimum de temps et en "économisant" autant que faire se peut, la mémoire et/ou le processeur. Dans ton cas, je ne suis pas certain que ce soit le cas

Houla bien loin de moi l'idée de dire que mon code est optimisé !!! ce que je disais c'est que mon "projet" est complet, qu'il n'y a plus beaucoup de fonctionnalités à pouvoir rajouter (aux vues du peu de données initiales).


@ tous :

Si, j'ai un petit problème, toujours dans la macro "Gestion des mouvements". J'ai le code suivant :

'Champs obligatoires

Dim Rep As Integer

If Code_Prod.Text = "" Then
Rep = MsgBox("Saisir le code du produit", vbExclamation, "Erreur de saisie")
End If

If Nat_OP.Text = "" Then
Rep = MsgBox("Saisir la nature de l'opération" & Chr(10) & "1 pour un achat" & Chr(10) & "2 pour une vente", vbExclamation, "Erreur de saisie")
End If

If Quantité.Text = "" Then
Rep = MsgBox("Saisir le montant restant en stock", vbExclamation, "Erreur de saisie")
End If

If PU.Text = "" Then
Rep = MsgBox("Saisir le Prix unitaire de l'article", vbExclamation, "Erreur de saisie")
End If

Lorsque toutes les textbox sont remplies, tout est OK, mais lorsque 1 ou plusieurs ne le sont pas :
j'ai bien la ou les msgbox qui s'affichent, mais ensuite cela me met une "erreur" et cela me propose le débogage du code.

Pouvez vous m'aider a améliorer cette partie du code afin que :
- il n'y est plus d'erreur
- l'absence d'un renseignement sans bloquant (pas de validation, donc rien qui se rempli dans le tableau)
- sur le même principe je mettrais que pour la "Nature de l'opération" on ne puisse mettre que 1 ou 2.

Voilà, je pense que ce sera mes 3 dernières questions.


Je vous met à disposition le fichier complet (hors graphiques)

Si vous avez des remarques/recommandations/critiques, je suis preneur.


Encore merci pour tout !


Neimad