Calcul de braquettes de prix

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

Bonjour,

Avec un petit fichier ce serait plus facile!

Même un fichier "test" sans vos données confidentielles.

Voici!

16fichier-test.xlsx (16.26 Ko)

J’espère que ça fonctionne le fichier

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...

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...

Hello,

Dis moi si j'ai tout bien compris et si cela te convient!

5fichier-test.xlsx (16.46 Ko)

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.

Bonjour à tous,

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

eric

PS : c'est quoi une braquette ?

4fichier-test.xlsx (15.17 Ko)

Ç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!

enlève ton "-" de D8.

En france on appelle ça une fourchette, ou une tranche

eric

Impeccable! Tout fonctionne! Vous êtes très fort et merci 1000000x !

En même temps, je me demandais si y'avait pas possibilité de dupliquer une cellule sur deux feuilles différentes. Je voudrais que la case B16 puisse être modifiée à partir d'une autre feuille dans une cellule contenue dans une formule sans utiliser les macros (restriction du système au travail, milieu financier). Est-ce possible?

Bonjour,

en B16 =ta_cellule.

Mais une cellule ne peut contenir que soit une formule, soit une saisie, pas les deux.

eric

Je comprend!

Encore une fois merci!

J'ai rencontré une difficulté supplémentaire lorsque je finalisais le fichier. J'aimerais faire une table de comparaison sur une feuille synthèse que j'ai refaite en enlevant le superflu pour les biens de la cause.

Si on prend la ligne 9, la section jaune est modifiable (D9) par l'utilisateur futur et permet de créer plusieurs scénarios avec des quantités différentes. Le hic, c'est quand on tente d'entrer une quantité inférieure à C8 de la feuille "#6" ou lorsque dans la feuille "#6" il n'y a rien dans les cellules C8-C13.

Comment faire en sorte que le prix régulier (B7 ou B2) soit utilisé dans le calcul de la feuille "Synthèse" ?

Ça fait bientôt quelques heures que je me casse la tête pour ça et je n'y arrive pas

Je me dit que lorsque la cellule D2 ou C8-C13 de la feuille "#6" n'ont pas de valeur et/ou lorsque la cellule D9 de "Synthèse" < que C8 de "#6" le calcul devrait se faire avec B2 ou B7 de #6... mais je n'arrive pas à l'écrire en formule

Vous savez? J'ai mis le nouveau fichier en pièce jointe.

Bonjour,

il faut mettre le prix régulier (8) en #6!D8

Et B6:B13 ne sert à rien.

eric

Je ne suis pas certain de comprendre

Désolé :-/

dans la feuille en #6 en D8 tu mets le tarif sans réduc : 8 $

Comme ça pour une quantité inférieure au premier seuil il te ramènera ce prix

eric

Je vois mais mon but serait qu'au lieu de devoir mettre le prix régulier dans D8, que la formule prenne B7

Je n'arrive pas à le faire. Je ne peux pas faire D8 parce que j'ai beaucoup d'autres calculs dans mes feuilles qui utilisent D8 comme base de calcul et si j'entre le prix régulier là, je vais devoir refaire beaucoup trop de formules et je doute que ça fonctionne...

Merci !

Bonjour,

tu n'as pas le choix si tu veux que le index-equiv ramène la bonne valeur.

eric

Rechercher des sujets similaires à "calcul braquettes prix"