Discussion :

[VanlindtMarc]

Bonjour à tous,

dans le cadre de mon emploi actuel, il me faut réaliser une feuille dans laquelle il est fait appel à des prix dégressifs.

L'idée de départ des personnes ici était de partir sur la bonne vieille idée des paliers et de créer autant de tableaux que nécessaires où aller rechercher l'information.

Après quelques recherches, il me semble que c'est, en effet, la solution proposée partout.

Néanmoins, aussi bien dans les tableaux que j'ai pu voir ici que dans ceux sur internet, il n'était pas rare de rencontrer le problème suivant :



où nous pouvons voir qu'au passage de palier, il devient moins cher d'acheter une pièce en plus.

Quitte à vouloir faire une courbe de prix, autant faire une "vraie" courbe et ne plus passer par des tableaux. J'ai donc écrit une petite fonction automatisant cela. J'aurais voulu savoir ce que vous en pensiez et, pour ceux étant souvent confrontés aux prix dégressifs, d'éventuels conseils sur la réalité de terrain pour mieux y correspondre.

Voici l'aide que je fournis concernant l'utilisation :



et le code :

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
37
38
39
40
41
42
43
44
45
46
47
Function JGCOURBE(Quantite, Prix, Prix_Min, Quantite_Max, Format, Optional paliers)
 
' Déclaration variables
' Je ne déclare pas PPP et ESP dans les variables globales car elles font appel à un argument optionnel. La déclaration de ces variables doit donc se trouver
' au sein des conditions où la variable "paliers" est appelée.
 
 
' Déclaration variables
'                              ' Déclaration PiJe ne déclare pas PPP et ESP dans les variables globales car elles font appel à un argument optionnel. La déclaration de ces variables doit donc se trouver
' au sein des conditions où la variable "paliers" est appelée.
 
Pi = WorksheetFunction.Pi()                             ' Déclaration Pi
 
' Calcul de base, au-delà des limites de la dégression.
If Quantite >= Quantite_Max Then                        ' Si la quantité est supérieure ou égale à la quantité maximale le calcul sera le suivant :
    JGCOURBE = Quantite * Prix_Min
End If
' Calcul selon méthode 1 : courbe
If Quantite < Quantite_Max And Format = 1 Then          ' Si la quantité est inférieure à la quantité maximale et le format égale à "1" alors c'est le type cosinusoïdale qui s'applique
    JGCOURBE = Quantite * (Prix_Min + ((Prix - Prix_Min) * Cos((90 / (Quantite_Max - 1) * (Quantite - 1) / 180) * Pi)))
End If
' Calcul selon méthode 2 : linéaire
If Quantite < Quantite_Max And Format = 2 Then          ' Si la quantité est inférieure à la quantité maximale et le format égale à "2" alors c'est le type linéaire qui s'applique
    JGCOURBE = (Prix - ((Prix - Prix_Min) / Quantite_Max) * (Quantite - 1)) * Quantite
End If
' Calcul selon méthode 3 : paliers 1
If Quantite < Quantite_Max And Format = 3 Then          ' Si la quantité est inférieure à la quantité maximale et le format égale à "3", alors c'est un premier type par paliers avec le nombre de paliers indiqué.
    PPP = (Prix - Prix_Min) / paliers                       ' Déclaration Prix par palier
    ESP = Quantite_Max / paliers                            ' Déclaration Espacement entre paliers
    For i = 0 To paliers                                ' Nous faisons une boucle qui passe par tous les paliers et vérifie si la quantité voulue est sur ce palier ou non. Si elle y est nous appliquons le calcul.
 
        If Quantite > i * ESP And Quantite <= (i + 1) * ESP Then  ' La différence entre paliers type 3 et 4 est ici.
            JGCOURBE = (Prix - (i * PPP)) * Quantite
        End If
    Next
End If
' Calcul selon méthode 4 : paliers 2
If Quantite < Quantite_Max And Format = 4 Then          ' Si la quantité est inférieure à la quantité maximale et le format égale à "4", alors c'est un second type par paliers avec le nombre de paliers indiqué.
    PPP = (Prix - Prix_Min) / paliers                       ' Déclaration Prix par palier
    ESP = Quantite_Max / paliers                            ' Déclaration Espacement entre paliers
    For i = 0 To paliers
        If Quantite >= i * ESP And Quantite < (i + 1) * ESP Then
            JGCOURBE = (Prix - (i * PPP)) * Quantite
        End If
    Next
End If
End Function

[Pierre Fauconnier]

Salut Marc,

Heureux de te voir ici...

Je n'ai, personnellement, pas bien compris le besoin. Un Index/Equiv permet, en un seul tableau, de créer des paliers de prix dégressifs, non?

[VanlindtMarc]

Salut Marc,

Heureux de te voir ici...

Je n'ai, personnellement, pas bien compris le besoin. Un Index/Equiv permet, en un seul tableau, de créer des paliers de prix dégressifs, non?

Salut Pierre!

Le but justement est de ne passer par aucun tableau car il y a énormément de produits avec toutes des données différentes du coup il faudrait un tableau par produit :/ Là chaque produit n'a que 3 données permettant de tout calculer.

Le second but est de pouvoir se passer des paliers. (j'ai quand même mis deux formats de paliers dans la fonction au cas où, mais c'était plus pour me permettre de faire un graphique comparatif avec la courbe en partant des mêmes données)

Là également, je me trompe sans doute et attend l'avis de personnes plus souvent confrontées à cela, mais mon impression est que le palier est un moyen simple pour simuler une courbe, utilisé depuis des lustres car il était plus facile à l'époque de passer par un palier que de faire un calcul à la main où rentraient en compte Pi, un cosinus, etc...

A l'heure de l'informatique où ce calcul n'est plus à faire manuellement, j'ai donc du mal à comprendre pourquoi nous en sommes resté aux paliers.

[Pierre Fauconnier]

Je suis têtu... ^^

Et vu ma signature, je préfère (souvent) penser en Excel lorsque je développe du VBA pour Excel.

Dans le code que tu donnes, la dégressivité est régulière, et donc l'élaboration d'un tableau, et donc du graphique qui en dépend, ne pose pas trop de problème. Mais lorsque la dégressivité n'est pas régulière et ne dépend donc pas d'un algorithme, il est intéressant d'envisager INDEX/EQUIV.

Je trouve donc préférable de sortir au maximum la logique métier du code, ce qui peut se faire justement assez facilement avec Excel puisque, par le truchement de tableau et/ou de formules, éventuellement dans des feuilles masquées, on peut déléguer le gros du travail à Excel, se servant de VBA uniquement pour orchestrer le tout.

Un INDEX/EQUIV matriciel permet d'avoir un seul tableau pour plusieurs produits. Il suffit qu'il soit ordonné sur l'article puis sur les Qtés plancher des dégressivités.

Or, ce tableau, sans dégressivité régulière, et donc sans algorithme, tu devras de toute façon le créer, soit en le hardcodant () soit en lisant une ou plusieurs tables, non?

Voici une illustration d'un INDEX/EQUIV matriciel. La formule illustrée est en H2 et est recopiée sur H3:H5.





Il est évidemment possible de construire le raisonnement en VBA, mais le code sera plus simple à gérer en utilisant INDEX/EQUIV qu'en créant la logique de dégressivité par code VBA, sauf à vouloir se faire plaisir à coder du VBA, évidemment.Si ce sont l'efficacité et la flexibilité qui sont attendues, je privilégie ma solution qui ne fait entrer aucune logique métier dans le code VBA, celui-ci se contentant de singer INDEX/EQUIV. On peut ainsi envisager une fonction perso plus lisible et manipulabe par un utilisateur lambda.

Code :

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

1
2
3
4
5
6
7
8
9
Function PrixUnitaireDegressif(Article As String, Quantite As Long)
  Dim Formula As String
 
  Application.Volatile True
  Formula = "INDEX(t_PrixDegressifs[P.U.],MATCH(""{article}""&TEXT({quantite},""000000""),t_PrixDegressifs[Article]&TEXT(t_PrixDegressifs[Qté],""000000"")))"
  Formula = Replace(expression:=Formula, Find:="{article}", Replace:=Article, compare:=vbTextCompare)
  Formula = Replace(expression:=Formula, Find:="{quantite}", Replace:=Quantite, compare:=vbTextCompare)
  PrixUnitaireDegressif = Evaluate(Formula)
End Function

Voici l'illustration de son utilisation en H7. Simple pour l'utilisateur, non?




On pourra bien entendu créer une courbe, éventuellement lissée, sur base d'un tableau qui utilise cette formule (Excel ou VBA)...

[VanlindtMarc]

Ahahah ^^ Moi aussi je suis têtu ^^

Ta progression semble suivre une évolution exponentielle inversée, ce qui est peut être facilement pris en compte via un argument proportionnel indiquant la "puissance" du facteur exponentiel. Il n'y a pas spécialement à "hardcoder" tout ça, je pense que ça peut facilement être formulé.

Si on analyse la formule que j'utilise

Code :

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

Quantite * (Prix_Min + ((Prix - Prix_Min) * Cos((90 / (Quantite_Max - 1) * (Quantite - 1) / 180) * Pi)))

L'évolution est "uniforme" mais on peut la forcer soit en jouant sur l'ordonnée via les prix ou sur l'abscisse via la quantité et déjà forcer un côté exponentiel.

Mais je n'avais pas pensé à ce genre de cas de figures, les produits dont je m'occupe sont des châssis et les quantités vont de 2 à très rarement au-delà de 15, du coup, les paliers déjà créés étaient nets comme des escaliers.

Je vais réfléchir à ça du coup ^^