Outil de tarification avec plein de paramètres

[toiletking]

Bonjour à tous!

Je suis actuellement en train de réaliser ma première application Excel : un outil permettant de générer un tarif en se basant sur :

- Le type de produit,
- Une durée de prêt : c'est un produit que l'on prête : la durée de prêt possible n'est pas la même suivant le type de produit!
- un coefficient multiplicateur du prix si il y a une taxe,
- Un type de livraison (c'est plus chère en express),

La formule devrait donc faire un truc dans ce genre :

((Prix du produit en fonction du type de produit (1 ou 2) de la durée et de la zone ) + (Prix à ajouter en fonction de la quantité prêté))*Taxe si "oui"+ prix de la livraison standart ou express.

Ensuite on doit pouvoir faire une ristourne sur le prix...

Jusqu'ici, à priori, rien de bien compliqué à réaliser avec des calculs, simplement, pour ajuster le prix du produit en fonction de la zone ou il sera envoyé, j'ai dû faire plusieurs gros tableaux qui se basent sur des prix de références et avec des coefficient pour la durée (en année) et des coefs pour la zone...

Je tiens à des coef car comme cela je peux ajuster au mieux les prix par rapport au marché!

Simplement, voilà : ça fait des sacrés gros tableaux et je dois répondre rapidement à mes clients, c'est pourquoi, j'ai pensé faire une application avec des listes de choix!

Au départ je voulais utiliser des formulaires déroulants mais je crois qu'il faut s'y connaitre un peu en visual basic... alors je suis parti sur des listes de choix...

Simplement, même avec des listes de choix, je vois pas trop comment m'y prendre

Ce que je ne sais pas faire : Un choix qui renvoie à une valeur dans un tableau... Ce qui complique vraiment le truc, je crois que c'est que je veux faire une liste pour la durée, mais cette liste ne sera pas la même en fonction du type de produit... Donc, je crois que je suis coincé : c'est de la macro qu'il me faut?

En tout cas, quelques exemples valent mieux que de long discours, vous trouverez ci-joint une bonne ébauche de mon outil si vous pouviez me pister pour sa réalisation, ça serait très gentil!

 

[mth]

Re : Outil de tarification avec plein de paramètres

Bonjour,

Ci-joint un essai avec ce que j'ai cru comprendre ... ,

@ +

mth

 

[toiletking]

Re : Outil de tarification avec plein de paramètres

Bonjour!

Merci mth pour votre réponse : quel efficacité!


Vous avez parfaitement compris ma requête et je vous remercie pour avoir pris le temps de l'étudier!

Il y a un tout petit "mais" : Au début je trouvais que les résultat était faux car il y a une petite erreurs : Quand on choisit une valeur "x" pour la durée du prêt, la requête sélectionne "x+1" (4 au lieu de 3).

J'ai beau chercher, d'où vient le problème, je ne trouve pas!

Sinon, avec l'exemple, j'ai bien compris la procédure et la syntaxe pour choisir une plage de donnée, sur une autre feuille et effectué des recherches...


J'ai vu que pour les petits tableaux, on utilise la fonction (que je n'ai pas encore bien compris) RECHERCHEV ! exemple :

=RECHERCHEV(E7;Données!J8:K9;2;FAUX)

Mais pourquoi donc ne pas toujours utiliser la fonction INDEX?

Dernière chose, sur le site, j'ai trouvé un tuto sur les formulaire dynamiques que je mets en joint, et c'est vrai que, à terme, j'aimerais bien pouvoir faire la même chose avec des formulaires plutôt que des listes de choix... (c'est plus pro )

C'est faisable, ou faut-il faire des macros pour les formulaires?

Encore merci pour tout!

 

[mth]

Re : Outil de tarification avec plein de paramètres

Bonjour,

correction pour la formule en H9 (désolée):

=INDEX(Données!E24:G64;EQUIV(E3;Données!A24:A64;1)+EQUIV(E5;DuréeProduit2;0)[COLOR=darkred][B]-1[/B][/COLOR];EQUIV(E4;Données!E20:G20;0))+(H6*H7)+H8

Pour recherchev(), fonction adaptée à la recherche d'une valeur dans une colonne, alors que index/equiv est utilisé pour rechercher une valeur dans un tableau à l'intersection d'une ligne et d'une colonne.

formulaires dynamique: pas joint, mais tu parlais peut-être du célèbre site de J Boisgontier ICI ? comme tu dis, c'est clair que ç'est pro, mais il faut effectivement des macros.

Bien à toi,

mth

 

[toiletking]

Re : Outil de tarification avec plein de paramètres

Super ! Merci pour cette correction! Et aussi pour cette réponse pour la différence entre INDEX et VRECHERCHE !

=INDEX(Données!E24:G64;EQUIV(E3;Données!A24:A64;1)+EQUIV(E5;DuréeProduit2;0)[COLOR=darkred][B]-1[/B][/COLOR];EQUIV(E4;Données!E20:G20;0))+(H6*H7)+H8

Mais au risque de t'embêter : pourquoi le décalage? Pourquoi ajouter -1 ?
C'est un détail qui nuit un peu à ma compréhension de la formule

Sinon : OOOOups, c'est tout moi ca d'oublier une pièce jointe : op, je corrige le tire

Ehhhh mais il a l'air super véloce le site de J Boisgontier! Merci!

Edit : Tapes tu tes formules à la main, ou y a-t-il des assistants pour jongler avec tout ca?

 

[mth]

Re : Outil de tarification avec plein de paramètres

Bonjour toiletking,

J'ai redéposé le fichier à jour (dans le fil au dessus) avec quelques détails sur la formule pour que tu t'y retrouves plus facilement (j'en ai aussi profité pour éliminer une ligne vide au milieu du tableau). Avec le résultat des fonctions equiv, tu vois que si on les aditionne comme j'ai fait ici, on compte en quelque sorte plusieurs fois la ligne 1 du départ, il faut donc l'enlever pour ne pas avoir de décalage de ligne.

Pour les formules, je les tape en partie à la main, en grosse partie avec la souris (en cours d'écriture de formule, il suffit de cliquer sur une cellule pour que ses références s'inscrivent tout seul). Par ailleurs, en fonction de ta version excel, tu as un guide des fonctions utilisées qui s'affiche en même temps que tu saisis ta formule.

Quant à ton fichier joint, merci c'est un fichier qui est très bien fait, et très bien expliqué, bravo à son auteur ! pas de macros donc.
Tu peux faire un clic droit sur le formulaire pour voir le menu "Format de contrôle", ça t'aidera à comprendre comment cela fonctionne pour faire la même chose sur ton fichier.

Bonne soirée,

mth

 

[toiletking]

Re : Outil de tarification avec plein de paramètres

Coucou Mth!

Merci pour la réorganisation, mais j'ai un petit souci : j'ai rajouté pleins de produits à mon tableau, et je préfère avoir les coefficients juste au dessus de chaque produits, c'est plus facile avec tous les produits Cela posera-t-il un problème?

J'ai essayer de reprendre le tout depuis le début sur une feuille vierge, avec tout les tableaux, mais ça ne fonctionne pas!

Je ne comprend pas comment tu a réussi à lier le formulaire de la durée de prêt avec le type de produit... Y a-t-il une manipulation supplémentaire (avec un assistant) pour les lier?

C'est embêtant, en plus j'ai risque d'avoir plusieurs type de durée en fonction du produit : 5, 10, 15, 20, 30 ans...

Avant de me lancer dans les formulaires dynamiques, je vais déjà essayé de maîtriser la liste de choix

Edit : pourquoi avoir ajouter les chiffres en colonne H et I?

 

[mth]

Re : Outil de tarification avec plein de paramètres

Bonjour,

Pas de problème pour ta ligne de coefficient,

Pour lier la durée du prêt et le produit, j'ai fait une zone nommée par produit, "duréeproduit1" et "duréeproduit2", et ensuite dans la liste de validation j'ai mis un simple si(produit1; duréeproduit1;duréeproduit2). Clairement, si tu ajoutes plein de produits cette solution ne sera plus valable et il faudra sans doute utiliser la fonction indirect().

Pour les colonnes H et I, tu peux tout retirer, c'était juste pour détailler les fromules afin que tu voies ce que donnaient les equiv() par rapport à la position dans le tableau, ces chiffres n'on aucune utilité dans les calculs.

Si tu es en panne, joins ta nouvelle structure de fichier avec tes nouveaux produits, ça sera plus facile pour adapter les formules.

Très bonne journée à toi et à bientôt,

mth

 

[toiletking]

Re : Outil de tarification avec plein de paramètres

Merci pour tes explications!

Malheureusement, je suis déjà en panne

J'ai beau chercher, je ne trouve pas du tout l'endroit où tu as saisi ta formule avec le si... C'est tout de même super intéressant de connaitre cette possibilité pour des applications futurs! Y a-t-il un endroit où l'on peut voir les paramètres pour les listes de validations? Quand je vais dans Insertion->Nom->Définir, je peux voir toutes les listes créées, mais pas les formules qui y sont rattachées...

Pour la fonction indirect(), j'ai regardé l'aide, mais je ne vois pas du tout comment utiliser cette fonction pour la durée relatives aux différents produits...

je suis donc super doublement en panne

Je te joins le fichier avec les produits supplémentaires, les durées ne fonctionnent qu'avec les deux premiers produit donc...

Encore merci pour ta patience Mth, c'est super de pouvoir aider les newbies comme ca

A très bientôt

Toilet

EDIT : C'est bon j'ai trouvé pour la formule de validation : il suffit de cliquer sur la liste et de faire validation! Bon je vais essayé de comprendre la formule :

=SI($E$3="produit 1 ";DuréeProduit1;DuréeProduit2)

Bon je pense que "si" remplace une zone par une autre dans ce cas... le problème, c'est donc de mettre plus de deux conditions...

 

[mth]

Re : Outil de tarification avec plein de paramètres

re

Tu trouveras ton fichier ci-joint avec des listes de validation pour les durées de prêt des produits (duréeproduit1, duréeproduit2, etc ... ) comme tu connais je te laisse voir leur définition)

J'ai profité de l'occasion pour modifier un peu les listes que tu avais créées, en éliminant la 1ère ligne de titre.

Pour la liste de validation "durée du prêt" évolutive en fonction du produit:

1: je souhaite faire référence au nom des zones créées, qui s'apellent toutes Duréenomduproduit (duréeproduit1 duréeproduit2, etc)
Afin de faire le lien avec l'info de la cellule E3, j'utilise la formule:

="Durée"&SUBSTITUE(E3;" ";"")

,
cette formule élimine les blancs et écrit Durée devant le nom du produit. Ainsi si en cellule E3 j'ai Produit 2, la formule va me donner DuréeProduit2, ... qui est comme par hasard le nom que j'ai donné à la zone des durées de prêt du produit 2

2: la fonction indirect va permettre d'aller lire ce qui ce trouve dans cette zone nommée, et dans la liste de validation qui se trouve en cellule E5, j'ai indiqué autoriser LISTE, et dans la source j'ai mis cette formule:

=INDIRECT("Durée"&SUBSTITUE(E3;" ";""))

J'espère que cela pourra t'aider, bon courage et à bientôt,

mth

 

[toiletking]

Re : Outil de tarification avec plein de paramètres

Géniaaaaaale! Merci beaucoup!

Ça fonctionne comme sur des roulettes!

Mais je crois que cela ne fonctionne pas si l'on nome les produits différemment comme par exemple :

"Container d'acier - model galvanisé, avec jointures soudées"

C'est vrai je chipote mais, ca m'éviterai de refaire une colonne...

C'est possible?

 

[mth]

Re : Outil de tarification avec plein de paramètres

re

non, tu ne chipotes pas tu as tout à fait raison, mais c'est vrai qu'avec des "Container d'acier - model galvanisé, avec jointures soudées" question zone nommée c'est pas top.

Au lieu d'un libellé au charme littéraire indiscutable, si tu utilisais basiquement un code article ?
Tu te fais quelque part une liste code article / libellé, puis les zones nommées pourraient utiliser ce code (qu'un petit recherchev pourrait nous aider à trouver éventuellement) ?

Je te laisse réfléchir à cette idée et nous dire si on poursuit en ce sens ou pas.

@ +

mth

 

[toiletking]

Re : Outil de tarification avec plein de paramètres

Super re


Et si (j'ai bien compris) ta solution permet de garder les intitulés intactes dans les listes (aux charmes littéraire indiscutables, certes ) pourquoi pas!

Mais a ce moment là, comment faire la passerelle?

Super Edit : J'ai trouvé sur le forum un exemple sur une autre façon de faire, mais je voulais ton avis, par rapport à celui-ci Tu le trouveras ci-joint

 

[mth]

Re : Outil de tarification avec plein de paramètres

pour illustrer l'idée de tout à l'heure, ton fichier joint avec des codes articles, les listes de validation restent attachées aux libellés donc claires et compréhensibles, mais les codes articles permettent de faire la passerelle entre le produit et la durée de prêt (formule recherchev en H3 mais que l'on peut mettre ailleurs dans la feuille)

Je te laisse voir si l'idée te convient ou pas du tout,

@ +

 

[mth]

Re : Outil de tarification avec plein de paramètres

oups ... la suite... je n'avais pas rafraichi et pas vu ta pièce jointe.

Il est très bien ce fichier joint, ceci dit, pour notre cas, c'est la même chose que le fichier proposé la semaine dernière, avec des si dans la liste de validation.
C'est justement ce que nous voulons éviter, sachant qu'on ne peut pas imbriquer plus de 7 si(), cela te limiterait au niveau du nombre de produits.
Avec la solution en cours, il y a peut-être des contraintes (il faut un code article), mais tu n'as pas cette limite.

à plus tard ...

mth