Macro pour retenir un prix selon la tranche

Bonjour ,

J'aimerai une macro qui lorsqu'on saisit une quantité, en besoin exprimé : si la quantité saisie est < tranche 1, qu'elle retient le prix révisé 1, sinon si tranche 1 <= quantité saisie < tranche 2, qu'elle retient le prix révisé 1; etc jusqu'à la fin, si quantité saisie >= tranche 20, qu'elle retient le prix révisé 20.

De plus si, pour un "NOI article MAR" donné, il existe plusieurs marchés (Numéro marché MAR), qu'on retienne le marché le plus cher.

Merci d'avance pour vos aides

82tab.xlsx (31.92 Ko)

Bonjour et bienvenue,

merci d'avoir posté un fichier ...

le résultat doit être porté sur quelle colonne ?

On peut le faire sans VBA avec une formule matricielle (à valider par Ctrl+Maj+Entrée)

=SIERREUR(INDEX(Tableau1[@[Prix révisé 1]:[Prix révisé 20]];EQUIV([@[Besoin Exprimé]];
SI((Tableau1[@[Tranche 1]:[Tranche 20]]<>"")*(MOD(COLONNE(Tableau1[@[Tranche 1]:[Tranche 20]]);2)=1);Tableau1[@[Tranche 1]:[Tranche 20]];9^9);1));"")

Merci de votre précieux aide.

J'aimerai en plus que si l'on saisisse une quantité (besoin exprimé) < à la tranche 1, qu'il nous retient, le prix de la tranche 1.

Par exemple à la première ligne, si je mets "35"(besoin< tranche1), il retient rien, alors qu'il devrait prendre le prix affecté à la tranche 1.

Merci encore !

C'est pas super-logique, mais une solution rapide est de mettre la quantité 0 pour la tranche 1. Parce que in fine le seuil de la tranche 1 ne sert pas à grand chose !

Merci ,mais j'ai constaté qu'avec votre formule si je mets 1500 en besoin à la première ligne, il me ramène pas le bon prix.

Si, mais le problème est qu'en K2 il est écrit 1 500 avec un espace (donc du texte) et non 1500 (que tu peux mettre avec un format séparateur des milliers).

ça marche!

Dernière question, si je tape 400 dans la première ligne au lieu de ramener le prix de 6,83; il amène le prix précédent 6,95.

Pouvez vous m'ajustez ça s'il vous plait

En effet !! correction

=SIERREUR(INDEX(Tableau1[@[Prix révisé 1]:[Prix révisé 20]];EQUIV([@[Besoin Exprimé]];SI(Tableau1[@[Tranche 1]:[Tranche 20]]<>"";(Tableau1[@[Tranche 1]:[Tranche 20]]<>"")*(MOD(COLONNE(Tableau1[@[Tranche 1]:[Tranche 20]]);2)=1)*Tableau1[@[Tranche 1]:[Tranche 20]];9^9);1));"")

désolé, je n'ai même pas compris pourquoi ce fonctionnement curieux !!

J'ai tout formaté en standard ... ce qui permet de voir les nombres saisis sous forme de texte (calés à gauche).

A nouveau si je saisie à la première ligne: 1 comme besoin il m'affiche 400 comme Prix.

Tu as raison ... je ne comprends pas ce qui se passe ...

Bon, tu as raison, assez bricolé avec les matricielles, je vais le faire en fonction personnalisée

Function prix(cel As Range)
prix = 0
For i = Cells(cel.Row, Columns.Count).End(xlToLeft).Column To cel.Column + 3 Step -2
    If Cells(cel.Row, i - 1) >= cel Then prix = Cells(cel.Row, i) * 1
Next
End Function

En même temps c'est plus tolérant à l'égard des nombres entrés comme texte avec un espace des milliers !

C'est parfait !

La seule et dernière modification que j'aimerai, après je ne vous embête plus c'est: que la macro tienne en compte que si la quantité saisie est inférieure à la tranche 1, de prendre le prix associé à cette tranche (même si ça vous parait non logique), et si la quantité saisi est supérieure à la dernière tranche connue, de prendre le prix de cette tranche: exemple à la 1ère ligne si on met 2000 comme besoin, au lieu d'afficher 0 comme prix, j'aimerai qu'il affiche 5,24 le prix de la tranche 1000 (dernière tranche connue).

Si j'ai ça c'est top !

Pas de soucis, il fat que cela donne le résultat attendu !

Function prix(cel As Range)
prix = Cells(cel.Row, cel.Column + 3) * 1
For i = cel.Column + 2 To Cells(cel.Row, Columns.Count).End(xlToLeft).Column Step 2
    If Cells(cel.Row, i) > cel Or Cells(cel.Row, i) = "" Then Exit For
    prix = Cells(cel.Row, i + 1) * 1
Next
End Function
9tab.xlsm (25.54 Ko)

Super !

Maintenant j'aimerai créer une nouvelle colonne de besoin réel selon prix total, qui:

si le besoin exprimé est inférieur à la première tranche, qu'il retienne la valeur de la première tranche: ensuite si en faisant les produits 2 à 2 (tranche1*prix1 à comparer avec tranche2*prix2, etc...) si on se rencontre qu'il y'a une tranche dont le prix total (tranche*prix) est inférieur ou égal au prix de la tranche retenue* son prix , notre tranche initialement retenue bascule à cette nouvelle tranche .

Si par contre le besoin exprimé est supérieur ou égal à la première tranche, on le retient, puis on fait la comparaison comme précédemment (prix*tranche) pour retenir le moins cher ou égal au prix total de la tranche initialement prévue.

J'ai repris le fichier, supprimé les blancs insécables des valeurs supérieures à 1000 et ajouté des colonnes Totaux.

Pourrais-tu illustrer sur le fichier les exemples que tu donnes ?

Merci.

2tab-1.xlsm (30.23 Ko)

D'abord je veux créer une nouvelle colonne "Besoin réel" tel que :

Besoin réel=SI([@[Besoin Exprimé]]<[@[Tranche 1]];[@[Tranche 1]];[@[Besoin Exprimé]]).

Et modifier la formule de la colonne "Total" en: Total=[@[Besoin réel]]*[@Prix]

2tab-1.xlsm (32.39 Ko)

Enfin j'aimerai créer une nouvelle colonne notée "Quantité optimisée", qui ramène une quantité tel que:

s'il y'a qu'une tranche de prix dans l'historique, qu'il retienne le "besoin réel" saisi.

(On compare les totaux de prix) Si Total<=Total tranche (de là où démarre le besoin réel): ici je veux dire par exemple à la ligne 1, on a un besoin réel de "400" , donc Total tranche (de là où démarre le besoin réel)=Total tranche 4: Ainsi on s'intéresse au Total tranche suivant: c'est à dire "Total tranche 7"

Par la suite à partir de Total tranche 7, Total tranche 10,....,Total tranche 58 , on cherche quel "Total tranche i" est <= Total tranche 4

Si on en trouve, on ramène la tranche correspondante. Si jamais il y'a plusieurs "Total tranche i" qui satisfont cette condition on ramène la tranche maximale correspondante.

Merci encore !

J'espère avoir bien saisi !!

Function prix(cel As Range) ' cel étant la quantité réelle
For i = (Asc("J") - 64) To Cells(cel.Row, Columns.Count).End(xlToLeft).Column Step 3
    If Cells(cel.Row, i) > cel Or Cells(cel.Row, i) = "" Then Exit For
    prix = Cells(cel.Row, i + 1)
Next
End Function

Function optimum(cel As Range) ' cel étant la quantité réelle
For i = (Asc("J") - 64) To Cells(cel.Row, Columns.Count).End(xlToLeft).Column Step 3
    If Cells(cel.Row, i + 2) < cel.Offset(0, 2) And Cells(cel.Row, i + 2) <> "" Then optimum = Cells(cel.Row, i)
Next
optimum = Application.Max(optimum, cel.Value)
End Function
5tab-1.xlsm (37.59 Ko)
Rechercher des sujets similaires à "macro retenir prix tranche"