Calcul de prix depuis intervalle de grandeur Fermé

Question

Bonjour à tous,

Je sollicite votre aide car je suis face à un problème que je n'arrive pas à résoudre.
Je vous explique en détail ce que je désire faire.

* Sur la feuille "calcul" j'ai :
- de D2 à D7 la plage de cellule correspondant à la dimension DIM 1.
- de E2 à E7 la plage de cellule correspondant à la dimension DIM 2.

* Sur la feuille "données" apparaissent les prix en fonction de la dimensions désirée en dim1 et dim2 comme ceci :
Fnissr	Désignation		Ref		DIM 1		DIM 2	Tarif
Arturo	Planche mela blc		510T996	                650-950         	500-1500	52,47
Arturo	Planche mela blc	                510T996	                650-1500         	500-950	52,47
Arturo	Planche mela blc	                510T996	                1050-1500         	1050-1500	74,22
Arturo	Planche mela blc	                510T996	                1501-2000		500-1500	100,46
Arturo	Planche mela blc		510T996		2001-2300		500-1500	113,46
Arturo	Planche mela nr		510T999		650-1500	                500-1500	52,47
Arturo	Planche mela nr		510T999	                1050-1500	                1050-1500	74,22
Arturo	Planche mela nr		510T999	                1501-2000	                500-1500	100,46
Arturo	Planche mela nr		510T999	                2001-2300	                500-1500	113,46
Arturo	Planche bois		510T998	                650-1500	                500-1500	47
Arturo	Planche bois		510T998	                1501-2000	                500-1500	51,49
Arturo	Planche bois		510T998	                2001-2300	                500-1500	76,83
Arturo	Planche laquée rouge	                510T997		650-1200	                500-1200   52,92
Arturo	Planche laquée rouge		510T997	                650-1200	                1201-1500	 74,94
Arturo	Planche laquée rouge	                510T997	                1201-1500	                500-1200	74,89
Arturo	Planche laquée rouge	                510T997	                1201-1500	                1201-1500	140,8
Arturo	Planche laquée rouge	                510T997	                1501-2000	                500-1200	101,36
Arturo	Planche laquée rouge	                510T997	                1501-2000	                1201-1500	189,24
Arturo	Planche laquée rouge	                510T997	                2001-2300	                500-1200	123,32
Arturo	Planche laquée rouge	                510T997	                2001-2300	                1201-1500	233,17
Arturo	Tablette	                	438AB999	                150-200	                240	25,46
Arturo	Tablette	                	438AB999	                201-245	                240	28,45
Arturo	Tablette	                	438AB999	                246-300	                240	32,96
Arturo	Tablette	                	438AB999	                301-402	                240	40,47
Arturo	Tablette	                	438AB999	                403-605	                240	55,48
Paul	Porte			403L		219,5		18,4
Paul	Porte			403L		229,5		18,87

Je ne sais pas si j'ai organisé les données correctement, en tout cas je suis bloqué sur ce problème.
Bien entendu l'exemple ne concerne que 6 références mais j'en ai en réalité plus de 500.

Un fichier explicitant ma demande est disponible à l'adresse ci-dessous :
https://www.cjoint.com/?hwqlgIVmmG

Merci d'avance pour votre aide.

Cordialement,
Sam357

eriiic · Answer

Bonjour,

heuuuu, elle est où la question ?
Et que représentent les 2 nombres dans 650-950 ? J'ai l'impression qu'il falloir séparer dans 2 colonnes...

gilou · Answer

bonjour
            je ne saisi pas tout  mais je te propose un principe de base  (entre les lignes violettes ) du lien 
 je suppose qu'il t'aidera ; meme si tu dois changer d'approche pour la resolution de ton probleme
a+
http://www.cijoint.fr/cjlink.php?file=cj200907/cijCPP7p0z.xls

gilou · Answer

re
   eventuellement ;ca aussi cela peut etre utile
a+
http://www.cijoint.fr/cjlink.php?file=cj200907/cijDX5F97n.xls

eriiic · Answer

Bonjour tout le monde,

J'ai démarré une solution par fonction personnalisée, par formule ça risque d'être un petit peu lourd...
La syntaxe sera: =tarif(A2;B2;C2;D2;E2;Données!$A$2:$F$28)

Reste le cas particulier de 'Porte' sans dim2. Y-a-t'il une seule désignation comme ça ? Ou bien mettre 1 en dim2 des 2 cotés t'irais comme solution ?
Je ne pourrais reprendre que dans l'après-midi...

eric

eriiic · Answer

Re,

donc avec une fonction personnalisée :
Intervalle_de_dim.xls
Reste peut-être à fignoler les cas particuliers.

eric

eriiic · Answer

Re,

Je vais détailler un peu plus, je ne connaissais pas ton niveau en excel et c'est vrai que je suis un avare d'explication et j'attends les questions...

Ton lien pour les fonctions personnalisée est bon, c'est bien de ça qu'il s'agit.

A partir de mon classeur fait un clic-droit le nom d'un onglet et choisis 'visualiser le code'.
La fenetre de code de la feuille s'affiche, mais une fonction personnalisée doit être dans un module.
Toujours dans l'éditeur tu as sur la gauche le nom du classeur (c'est le projet VBA) et en dessous les feuilles du projet et les modules. 
Déplie 'Modules' et double-clique sur 'Module1', tu vas y trouver le code de la fonction personnalisée à copier/coller dans ton classeur. Copie tout.
Créer un module dans ton classeur : clic-droit dans ton projet puis 'insertion / module', tu n'as plus qu'à y coller le code.

La fonction est maintenant disponible dans tes feuilles. 
Dans une cellule 'insertion / fonction...', dans rubrique choisir 'personnalisées' et tu dois avoir 'tarif'.
OK et enqsuite c'est comme une fonction excel, tu remplis les différents paramètres.

Il y a un ascenseur, ne pas oublier 'GrilleTarif' en bas. 
Pour remplir ce paramètre tu le sélectionnes, tu actives la feuille 'Données' et tu sélectionnes la plage (sans la ligne de titre, ça sera tjs une ligne de moins à lire).
Ou bien tu fais une formule dynamique à la place, si ton nombre de lignes de tarif augmente ou baisse la plage s'adaptera toute seule sans que tu aies les formules à reprendre.

Je te laisse contrôler que le bon tarif est toujours ramené, je n'ai contrôlé que sur 2...
Si tu as un doublon de tarif la fonction ramène le 1er.

eric

eriiic · Answer

Bonjour,

une petite modif, :
Intervalle_de_dim.xls
utilise cette version et dis moi si ça te parait ok
eric

eriiic · Answer

oui, des changements dans la macro.
Mais la formule tu peux la mettre où tu veux.
Ce qu'il faut respecter absolument c'est l'ordre des colonnes dans la grille de tarif (6 champs consécutifs et tjs dans le même ordre).
La syntaxe de la fonction :
=tarif(Fnissr, Désignation, Ref , dim1, dim2 , GrilleTarif )

eric

eriiic · Answer

Re,

J'ai actualisé ta proposition avec 3 articles "simples", comment dois je faire si je souhaite que ma colonne DIM1 serve pour le calcul des longueurs des articles "simples" et que mon prix sorte toujours dans la colonne prix de vente ? 
En clair est il possible d'avoir une seule et même formule pour trouver le prix de vente dans la feuille "données" ? 
Est ce réalisable est si oui, par quel moyen ?
Oui, mais il va falloir revoir la structure de la fonction personnalisée et ça sera difficile pour moi aujourd'hui.
Estce que pour le cable saisir 1 dans dim1, rien dans dim2 et la longueur dans 'quantité' t'irait ?
Sinon, il faudrait ajouter une colonne dans la grille de tarif pour coder les saisies attendues selon la référence...

Les titres et l'ordre des colonnes doivent ils être les mêmes que dans la macro ?
Dans la grille tarif ils doivent être obligatoirement dans l'ordre que tu m'as donné :
Fnissr	Désignation	Ref	DIM 1	DIM 2	Tarif
Dans la formule ils sont attendus dans cet ordre :
Fnissr, Désignation, Ref , dim1, dim2 , GrilleTarif , il suffit de mettre les références des cellules qui correspondent.
Dans ton exemple tu dois avoir la désignation en F105 et la Ref en E105 etc :
=tarif(D105;F105;E105;V105;W105;DECALER('Données calcul des barèmes'!$B$2;;;NBVAL('Données calcul des barèmes'!$B:$B)-1;6)) 
Par contre je vois qu'il manque le 6 (en gras) que j'avais oublié dans un précédent fichier

La désignation doit elle etre identique, ou la réf suffit elle ?
Actuellement les 2 doivent être identiques puisque ça figurait dans ta demande. On peut supprimer le test sur l'un ou sur l'autre.
Pour que les libellés soient exacts je te conseille de mettre une validation par liste sur les fournisseurs et sur les désignations par fournisseur. Et si la référence est unique sur ces 2 critères elle peut être ramenée en même temps que le tarif.

Il y a un ascenseur, ne pas oublier 'GrilleTarif' en bas. 
Pour remplir ce paramètre tu le sélectionnes, tu actives la feuille 'Données' et tu sélectionnes la plage (sans la ligne de titre, ça sera tjs une ligne de moins à lire). 
Ou bien tu fais une formule dynamique à la place, si ton nombre de lignes de tarif augmente ou baisse la plage s'adaptera toute seule sans que tu aies les formules à reprendre.

Je ne comprends pas comment appliquer ça à ce qui est écrit dans le module, je ne trouve pas dans le texte ci-dessous où renseigner la plage. 
C'est ce que tu as fait dans la formule (il n'y a pas à toucher au code).
Il y avait 2 possibilités, tu as choisi la 2nde avec ...;DECALER('Données calcul des barèmes'!$B$2;;;NBVAL('Données calcul des barèmes'!$B:$B)-1;))

Je sais que ça fait beaucoup de questions
Je confirme ;-)

eric

eriiic · Answer

Dans ton exemple il y avait désignation et ensuite ref dans la grille des tarifs et là tu me donnes un fichier où c'est l'inverse.
Normal que ça ne marche pas...
Et clairement : il faut se baser sur la ref, la désignation ou les 2 ?

D'autre part dans ce fichier dim1 et dim2 est entièrement vide,  un seul fnissr présent, les références dans la feuille habillage sont absentes des données etc  
Là c'est un peu exagéré, disons que je n'ai rien vu....

Fais un fichier exemple comme l'original mais avec des valeurs cohérentes et diverses où tous les cas sont représentés pour avoir qcq chose de solide. 
Qcq lignes de chaque cas, 15-20 devraient suffire.
Et tache de bien réfléchir que ce soit exhaustif, pas sûr que j'aurais le courage de reprendre ça x fois...

eric

eriiic · Answer

Bonsoir,

ce que j'avais demandé :
Fais un fichier exemple comme l'original mais avec des valeurs cohérentes et diverses où tous les cas sont représentés pour avoir qcq chose de solide.
Qcq lignes de chaque cas, 15-20 devraient suffire. 

Ce que j'obtiens :
Je viens d'étoffer le fichier il comporte :
- plus de lignes
- plus de feuilles (une feuille de données d'où proviennent les lignes qui sont copiées vers 3 feuilles où les références sont réparties par famille et enfin il y a la source d'information pour les tarifs).

5 feuilles dont 3 avec 28 colonnes où il faut que j'essaie de deviner ce qui sert, ne sert pas, où sont les données à utiliser.
Je me force un peu pour comprendre, résultat : dans 'cable' je vois apparaitre un champ 'dimension souhaitée' dont je suppose qu'il doit servir, dim1 et dim2 avec des valeurs dont je ne vois pas à quoi ça correspond et donc au final un truc incompréhensible même si j'avais le courage de relire les 20 posts qui s'allongent de plus en plus avec des infos dispersées à droite et à gauche. 
Et la demande qui évolue au fil des posts, qui se complique de plus en plus.
J'ai répondu à ta 1ère demande, ce qui demandait un certain travail, et tout ça pour rien puisque d'autres critères apparaissaient, des paramètres devenaient optionnels etc

Et un fichier exemple c'est un fichier de qcq ko avec d'un coté les données utiles et de l'autre les différents cas à traiter avec ce que tu veux obtenir
Fait bien l'évaluation de tes besoins, des données en entrées et des données en sortie et repose dans un nouveau post une demande claire et complète.

J'abandonne celui là
Eric

Calcul de prix depuis intervalle de grandeur

11 réponses

Discussions similaires