Calcul de braquettes de prix Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
u
unwind06
Jeune membre
Jeune membre
Messages : 11
Inscrit le : 24 mai 2019
Version d'Excel : 2016 MSO

Message par unwind06 » 24 mai 2019, 16:18

Bonjour à tous!

J'ai cherché un titre pertinent pour mon problème mais je n'ai pas réussi à faire mieux! J'ai une situation particulière (du moins, selon mon niveau de connaissance d'Excel!) qui va comme suit. Je tente de créer un fichier d'évaluation de prix pour un groupe de collègues qui travaillent relativement souvent à rechercher des produits et services qui sont vendus avec un rabais volume.

Mon ficher tente d'être explicite dans le sens ou, visuellement, je ne montre que ce qui est utile et laisse uniquement certaines cases modifiables. Ce que je veux c'est que les personnes puissent entrer le nom du produit convoité et le prix régulier. Pour l'instant ça va. Ces données sont reportées sur la feuille du produit (une feuille par produit) dans un tableau qui montre en ordre:
(Image "produit")
- Nom du produit
- Prix régulier
- Quantité minimale pour obtenir un rabais
- Prix réduit
- Rabais volume ($)
- Rabais volume (%)
- Budget minimal nécessaire pour obtenir un rabais
- Frais supplémentaire à ajouter
- Quantité commandée
- Prix total final

Les cases jaunes du tableau oranges sont modifiables. Donc le premier nom se recopie dans les 6 lignes du bas et le tableau bleu, le prix régulier aussi.

Tout fonctionne sauf une chose, qui est majeure. La logique est la suivante:

- Lorsque la quantité requise =0 ou est plus petit que la première braquette, prendre le prix régulier pour calculer le prix tota.
- Lorsque la quantité requise est = ou plus grande que le premier seuil et inférieure au deuxième seuil, prendre prix spécial 1.
- Lorsque la quantité requise est = ou plus grande que le deuxième seuil et inférieur au troisième seuil, prendre prix spécial 2.
- Etc.

Il peut y avoir jusqu'à 6 strates différentes. Là ou est mon problème, c'est lorsque je n'ai pas 100% des strates. Par exemple, que le produit en question a un rabais volume à partir de 100, un autre à partir de 200 et un dernier à partir de 300 disons. Les calculs se font avec le prix original et c'est évidemment une erreur.

Voici la formule de la case D2 "Prix réduit":
=((SI(B16<C9;B8;SI(ET(B16>=C9;B16<C10);D9;SI(ET(B16>=C10;B16<C11);D10;SI(ET(B16>=C11;B16<C12);D11;SI(ET(B16>=C12;B16<C13);D12;SI(ET(B16>=C13;B16<C14);D13;SI(B16>=C14;D14)))))))))

Je ne sais pas quoi faire avec ça. Peut-être qu'il y a une autre façon de procéder complètement ou un ajout à mettre dans ma formule, je ne sais pas et je me retourne vers vous!

MERCI 1000 fois!
Produit.PNG
D
Del_Fine
Jeune membre
Jeune membre
Messages : 24
Inscrit le : 17 mai 2019
Version d'Excel : 2016 FR

Message par Del_Fine » 24 mai 2019, 16:32

Bonjour,

Avec un petit fichier ce serait plus facile!
Même un fichier "test" sans vos données confidentielles.
Del_Fine
u
unwind06
Jeune membre
Jeune membre
Messages : 11
Inscrit le : 24 mai 2019
Version d'Excel : 2016 MSO

Message par unwind06 » 24 mai 2019, 16:41

Voici!
Fichier_Test.xlsx
(16.26 Kio) Téléchargé 11 fois
u
unwind06
Jeune membre
Jeune membre
Messages : 11
Inscrit le : 24 mai 2019
Version d'Excel : 2016 MSO

Message par unwind06 » 27 mai 2019, 15:02

J’espère que ça fonctionne le fichier
D
Del_Fine
Jeune membre
Jeune membre
Messages : 24
Inscrit le : 17 mai 2019
Version d'Excel : 2016 FR

Message par Del_Fine » 27 mai 2019, 15:52

Bonjour,

Je pense avoir compris l'objet de ta demande, cependant je ne comprends bien l'utilité de tes cellules C3 et B13 car les valeurs entrées peuvent ne pas être comprises dans les mêmes plages, par conséquents les quantités requises ne sont pas en lien avec les quantités minimum pour un rabais...
Del_Fine
u
unwind06
Jeune membre
Jeune membre
Messages : 11
Inscrit le : 24 mai 2019
Version d'Excel : 2016 MSO

Message par unwind06 » 27 mai 2019, 16:31

C3: C'est une indication du seuil inférieur nécessaire pour obtenir le rabais volume du fournisseur. Par exemple, dans mon fichier test, si on demande 550 unités d'un produit, le chiffre affiché dans la cellule C3 sera de 500 car c'est le minimum requis pour obtenir le prix de 0.80$ l'unité (D11)

B13: C'est la même valeur que B8. Les cellules B9 à B14 se doivent d'être la même valeur que B8 car le prix régulier de l'item ne varie pas. Il est normal de voir le même prix d'une cellule à l'autre. Le chiffre de ces cellules est utilisé dans le calcul du % de rabais volume (F3). Ce n'est qu'un indicateur en fait.

Mon défi est au niveau de la formule de la cellule D3. Lors qu'il y a des cellules C10-C14 vides, la formule automatiquement va chercher la valeur de B8, ce qui est faux. Il faudrait que la formule garde la dernière cellule inscrite dans la colonne D. Par exemple, dans mon fichier, si la dernière valeur inscrite est 1000, donc C12, et que C13 - D13 - C14 - D14 sont vides, il faudrait que le calcul se fasse avec D12 et non B8.

Je n'arrive pas à le faire...
D
Del_Fine
Jeune membre
Jeune membre
Messages : 24
Inscrit le : 17 mai 2019
Version d'Excel : 2016 FR

Message par Del_Fine » 28 mai 2019, 11:08

Hello,

Dis moi si j'ai tout bien compris et si cela te convient!
Fichier_Test.xlsx
(16.46 Kio) Téléchargé 4 fois
Del_Fine
u
unwind06
Jeune membre
Jeune membre
Messages : 11
Inscrit le : 24 mai 2019
Version d'Excel : 2016 MSO

Message par unwind06 » 28 mai 2019, 18:34

Je crois qu'on est sur la bonne piste!

Regardez le nouveau fichier que j'ai mis en pièce jointe. C'est dans un cas comme celui-là ou je n'arrive pas à corriger l'erreur.
Fichier_Test (2).xlsx
(17.35 Kio) Téléchargé 2 fois
Avatar du membre
eriiic
Passionné d'Excel
Passionné d'Excel
Messages : 8'729
Appréciations reçues : 296
Inscrit le : 7 février 2010
Version d'Excel : 2010fr

Message par eriiic » 28 mai 2019, 18:58

Bonjour à tous,

un essai, j'ai changé les formules de C3 et D3.
eric

PS : c'est quoi une braquette ?
Fichier_Test.xlsx
(15.17 Kio) Téléchargé 4 fois
En essayant continuellement, on finit par réussir.
Donc plus ça rate, plus on a de chances que ça marche.
(les Shadoks)

En plus du merci (si si, ça se fait !!!), penser à mettre en résolu. Merci
u
unwind06
Jeune membre
Jeune membre
Messages : 11
Inscrit le : 24 mai 2019
Version d'Excel : 2016 MSO

Message par unwind06 » 28 mai 2019, 19:11

Ça semble bon!

J'ai essayé de mettre en B16 un chiffre inférieur à C9 et ça me retourne une erreur. Savez-vous comment régler ça?

Je ne savais pas trop comment nommer ma cellule, j'ai utilisé braquette pour désigner une marge ou une intervalle. Au Québec, on parle de braquette de prix ou de quantité quand on dit entre tant et tant, par exemple, "dans une braquette située entre 100 et 500".

Ce n'est peut-être pas la meilleure façon de le dire! :)
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message