Scénario vente si je réaffecte un % de qté à un autre client
Bonjour,
Désolé de ce long titre. Pas évident de trouver un texte explicite !
Dans le fichier ci-joint, j'ai 5 catégories de client avec des remises différentes (attention, parfois pour une même catégorie, le % de remise est différent)
Sont indiquées les qtés facturées, ainsi que les reliquats.
Je voudrais créer un scénario de vente, sous entendu :
Pour chaque ligne, si je réaffecte X % de la qté facturée (on peut arrondir la qté à l'entier supérieur), vers un client de la catégorie inférieure et dans la limite de son reliquat, quel montant cela donne ?
Je souhaiterais avoir le choix du pourcentage à réaffecter.
Je ne sais pas si je suis très clair...mais merci à ceux qui prendront le temps de se pencher sur ma demande
Bonjour,
Je ne sais pas si je suis très clair...
Pour moi, absolument pas !
Comment veux réaffecter une quantité que tu as facturé ? Si je comprend bien, tu veux donner à un autre client les fournitures payées par le premier ! Je pense que un peu plus d'explications s'impose.
Pas de souci.
Il faut prendre ça comme une simulation.
Fichier de départ = ce que j ai réellement facturé.
Fichier d arrivée = si j avais fait autrement avec telles qté reaffectées, alors ma facturation aurait été de tel montant.
L idée est de comparer ce qui est réellement facturé à ce qui aurait pu l être dans d autres conditions.
Rajout d'info, peut-être que ça parlera à qq'un.
Reliquat= qté max commandée par le client et pas encore livrée.
En résumé :
- Client 1 a remise de 50%. Il commande 100pcs. Je livre 80.
- Client 2 a remise de 30%. Il commande 70pcs. Je livre 20.
Cela me donne un certain montant total facturé.
Quel serait ce montant si j'avais décidé d'affecter 10% du client 1 au client 2 (donc 8pcs en moins pour client 1 et 8 en plus pour client 2), sans dépasser son reliquat
Je voudrais avoir le choix du pourcentage. Je suppose que ça passe par du VBA...je ne sais pas faire.
Bon ben je suis quelque part content d'avoir posé une colle
Merci tout de même à ceux qui auront jeter un coup d'œil et lâchement abandonné
Vais essayer de tourner le problème autrement. Quand ça ne passe pas dans un sens, faut tenter dans l'autre
Bonjour,
Merci tout de même à ceux qui auront jeter un coup d'œil et lâchement abandonné
Avec les explications données tu ne peux pas prétendre à plus (après plus de 70 vues, je suis le seul à avoir essayé de comprendre ta demande !)
Tu souhaites réaffecter des quantités d'un client à un autre mais tu ne dis pas comment s'effectue le choix du second client, est-il le seul, faut-il prendre tous ceux qui pourraient correspondre ?
Expliques avec précisions comment tu veux que ton scénario se déroule. Tu veux des formules Excel, une procédure VBA ?
Et je te remercie d'avoir pris du temps pour ma demande.
Traduire une pensée "quand on a le nez dedans" en logique Excel, pas simple.
Ci-joint une version remaniée du fichier (y avait un oubli de calcul) et surtout le déroulé de ma pensée. Si ça peut aider.
Le fichier s'ouvre sur la feuille explicative.
A mon avis, c'est du VBA au niveau complexité. Mon "seul" besoin est de pouvoir choisir le taux de % que je réaffecte.
Je suis sur mon iPhone mais je regarde demain si je peux t’aider et ce sera avec plaisir !
Bonjour,
Je suis parti du principe de ne récupérer que les reliquats les plus importants de chaque catégorie pour le produit choisi. Par exemple, si je prend le produit "BONBON", c'est le client "Client 1" qui a le reliquat le plus élevé (1273) et pour les clients de catégorie inférieure, il y a les clients "Client 66", "Client 91" et "Client 129" avec comme reliquat respectif 51, 13 et 1.
Pour ce premier jet, j'ai pris la valeur totale du reliquat du client "inférieur" pour faire la simulation qui le concerne à savoir par exemple que si on redistribue 51 produits du client "Client 1" au client "Client 66" (il a 51 produits en reliquats) la simulation donne 715,24 HT Net contre 522,82 HT Net dans l'état réel soit un Delta de 192,42. Ceci se fait pour chaque catégorie de client. Les résultats sont affichés dans la feuille "Résultat" (feuille que j'ai ajouté). Il faut renseigner la cellule B1 du produit recherché et cliquer sur le bouton :
Wouah, sacrée avancée ! Ah c'est beau de savoir faire
Merci pour les explications dans la macro, ça m'aide à suivre les étapes de calcul.
L'approche par le reliquat est intéressante, c'est vrai que je n'avais pas envisagé le problème par ce sens.
Mais je ne souhaite pas nécessairement livrer l'ensemble du reliquat, même lorsque c'est possible. C'est bien tout mon problème.
C'est pour cette raison que j'avais opté pour un % de qté qui me semble être le meilleur compromis.
(en gros, c'est un peu fromage OU dessert...je ne peux pas livrer tout le monde, je cherche donc à trouver le meilleur équilibre possible pour ménager la chèvre et le choux)
Autre point, est-il possible de déclencher la simulation pour l'ensemble des produits, sans avoir à choisir "BONBON" ou "LEGUMES" ?
En tous cas, grand merci, cela me permet déjà de me faire une idée (bon faut que transcrive ça sur mon fichier réel, plus complexe, mais avec tes explications de la macro je devrais y arriver
Et question subsidiaire, est-il possible de faire apparaître une colonne spécifiant le nombre de produit réaffecté ? (pour chaque ligne, pas un total général).
Bonjour,
A tester en entrant le pourcentage de réaffectation en B1 de la feuille "Résultat" :
Je vais prendre le temps d'étudier tout ça demain en détail mais à première vue ça m'a l'air royal.
D'ors et déjà, un très très grand merci pour ton aide, ton temps et ta patience
Bonjour,
J'ai modifié le code pour prendre en compte la quantité disponible afin que la quantité réaffectée ne dépasse pas cette dernière. Si tu ne veux pas en tenir compte, j'ai indiqué les endroits où supprimer les lignes de code. J'ai aussi fais en sorte de ne pas afficher les valeurs = à 0. Je ne re poste pas le classeur mais juste le code :
Sub Simulation()
Dim Tbl()
Dim TblCritere() As String
Dim Dico As Object
Dim ListeCle As Variant
Dim Cle As Variant
Dim Plage As Range
Dim PlageResult As Range
Dim Cel As Range
Dim Pourcent As Single
Dim I As Integer
Dim J As Integer
Dim K As Integer
Dim L As Integer
Dim Total As Long '<---- à supprimer si on ne veut pas tenir compte du total !
'pourcentage de produits réaffecté (entrer sous cette forme : 50 pour 50 pourcent)
Pourcent = Worksheets("Résultat").Range("B1").Value / 100
Set Dico = CreateObject("Scripting.Dictionary")
'défini la plage sur toute la feuille
Set Plage = DefPlage(Worksheets("Page 1"))
'plage pour les résultats (la vide si présence de valeurs)
Set PlageResult = DefPlage(Worksheets("Résultat"), 4, 1)
If PlageResult(1, 1).Row > 3 Then PlageResult.Clear
'tri sur "catégorie client" de la plus grande à la plus petite
Plage.Sort Plage(1, 3), xlDescending, , , , , , xlYes
'récupère les produits en dédoublonnant
For Each Cel In Plage.Columns(2).Cells
If Cel.Row > 1 Then Dico(Cel.Value) = Cel.Value
Next Cel
'transfère dans un tableau afin de réutiliser le dictionnaire...
ReDim TblCritere(1 To Dico.Count)
ListeCle = Dico.Keys
For I = 1 To UBound(TblCritere): TblCritere(I) = ListeCle(I - 1): Next I
'commence les résultats après la ligne 3 (entêtes)
K = 3
'boucle sur les produits
For I = 1 To UBound(TblCritere)
'vide le dictionnaire et le tableau pour chaque produit et remise à 0 de J
Dico.RemoveAll
Erase Tbl()
J = 0
'filtre sur la colonne des produits ("Famille UH")
Plage.AutoFilter 2, "=" & TblCritere(I)
For Each Cel In Plage.Columns(3).Cells.SpecialCells(xlCellTypeVisible)
'évite de prendre en compte la ligne d'entêtes
If Cel.Row > 1 Then
'récupère les clients ayant le reliquat le plus important de chaque catégorie pour le produit en cours
If Cel.Offset(, 6).Value > Dico(Cel.Value) Then
'ne redimensionne que si c'est une nouvelle catégorie
If IsEmpty(Dico(Cel.Value)) Then J = J + 1: ReDim Preserve Tbl(1 To 6, 1 To J)
'stocke le reliquat dans le dictionnaire (valeur de la clé), (la clé étant la catégorie client)
Dico(Cel.Value) = Cel.Offset(, 6).Value
Tbl(1, J) = Cel.Offset(, -2).Value 'nom du client
Tbl(2, J) = Cel.Offset(, 2).Value 'prix du produit
Tbl(3, J) = Cel.Offset(, 4).Value 'remise
Tbl(4, J) = Cel.Offset(, 5).Value 'HT net
Tbl(5, J) = Cel.Offset(, 6).Value 'reliquat
Tbl(6, J) = Cel.Value 'catégorie client
End If
End If
Next Cel
'si le tableau a été initialisé (produit trouvé)
If Not Not Tbl Then
With Worksheets("Résultat")
K = K + 1
'nom du produit en cours
.Cells(K, 1).Value = TblCritere(I)
'catégorie supérieure...
For L = 1 To UBound(Tbl, 2) - 1
K = K + 1
.Cells(K, 1).Value = Tbl(6, L) 'catégorie
.Cells(K, 2).Value = Tbl(1, L) 'nom client
.Cells(K, 4).Value = Tbl(2, L) 'prix de l'article
.Cells(K, 5).Value = Tbl(3, L) 'montant de la remise
.Cells(K, 6).Value = Round(Tbl(4, L), 2) 'montant HT Net
.Cells(K, 7).Value = Tbl(5, L) 'reliquat
'...catégorie inférieure
For J = L + 1 To UBound(Tbl, 2)
'ne prend pas en compte ceux dont la valeur est égale à 0
'(Int() supprime la partie décimale donc si reliquat = 1 avec 50 % de réaffectation celà donne 0,5 donc 0 !)
If Int(Tbl(5, J) * Pourcent) > 0 Then
'totalise le nombre de produits réaffectés...
Total = Total + Int(Tbl(5, J) * Pourcent) '<---- à supprimer si on ne veut pas tenir compte du total !
'...de façon à ce que ce nombre ne puisse dépasser la quantité disponible
If Total <= Tbl(5, L) Then '<---- à supprimer si on ne veut pas tenir compte du total !
K = K + 1
.Cells(K, 1).Value = Tbl(6, J) 'catégorie
.Cells(K, 3).Value = Tbl(1, J) 'nom client
.Cells(K, 5).Value = Tbl(3, J) 'montant de la remise
.Cells(K, 6).Value = Round(Tbl(4, J), 2) 'montant HT Net
.Cells(K, 7).Value = Tbl(5, J) 'reliquat
.Cells(K, 8).Value = Round(Tbl(4, L) + Tbl(4, J), 2) 'total des HT Nets
.Cells(K, 9).Value = Round(Tbl(4, L) + Tbl(4, J) + Tbl(2, J) * (1 - Tbl(3, L) / 100) * Int(Tbl(5, J) * Pourcent), 2) 'total des HT Nets en simulation
.Cells(K, 10).Value = .Cells(K, 9).Value - .Cells(K, 8).Value 'Delta
.Cells(K, 11).Value = Int(Tbl(5, J) * Pourcent) 'quantité réaffectée
End If '<---- à supprimer si on ne veut pas tenir compte du total !
End If
Next J
K = K + 1 'ligne vide entre les comparaisons
Total = 0 'pour la catégorie suivante <---- à supprimer si on ne veut pas tenir compte du total !
Next L
End With
End If
'supression du filtrage
Plage.AutoFilter
Next I
End SubBonjour Theze,
Désolé pour ma réponse tardive mais je me suis un peu embêté pour retranscrire tout ça dans mon fichier réel.
J'ai fini par y arriver heuresuemetn et cela répond totalement à mon besoin.
Donc un très grand merci pour ton aide et le temps que tu m'as consacré.
Demande résolue !
Ah si, une petite chose.
J'ai rajouté quelques mises en forme ici et là (des sommes, des données en gras, des décimales,...) mais elle sont perdues à chaque fois que je lance la macro.
Je n'ai pas trouvé dans le code l'élément à modifier.
Bonjour,
C’est «PlageResult.Clear» qui supprime tout se qui se trouve sous la ligne d’entêtes !
Je vois bien la ligne, j'ai fouiné partout dans le code, tenté des supprimer ce qui me semblait le plus cohérent, et bon...échec cuisant
Tout se décale, et ça met le bazar sans que je ne "comprenne" la corrélation.
Merci tout de même, ça m'encourage à m'acheter un bouquin pour y voir plus clair.
Vachement puissant quand même le vba, quand on sait l'utiliser....Et qu'on arrive à définir son besoin dès le début !
Bonjour,
Postes un classeur avec tes formules que je puisse éventuellement adapter !