Remonter à partir d'un Tarif la tranche et le prix
Bonjour,
Je travail sur un fichier de consultation fournisseur (ci joint). J'ai volontairement allégé ce fichier et choisi un article afin de vous montrer le problème sur lequel je me heurte depuis plusieurs jours.
Ce fichier est composé de 2 onglets :
- Onglet TARIF : il est issu d'une table SQL de notre ERP. Il s'agit d''une liste de codes article associés à plusieurs colonnes. Les colonnes qui nous intéressent sont Tanche, PUHT, Fournisseur, Date MAJ.
- Onglet Consultation : On s'appuie sur cette onglet pour saisir les prix composants de nos fournisseurs.
Avant consultation chez nos fournisseurs, il est important de remonter les meilleures Tanches, PUHT, Fournisseurs, DateMAJ issus de l'onglet TARIF en fonction de la quantité de consultation qui est en cellule AT1.
Première étape.
Remonter en cellule AM5 la meilleure tranche par rapport à la quantité de consultation.
Si la quantité de consultation est 1, la tranche la plus proche de l'onglet tarif est 5
Si la quantité de consultation est 150, la tranche la plus proche de l'onglet tarif est 200
J'ai trouvé une solution avec des fonctions RECHERCHEX en colonne AL et AM qui ne me satisfait pas, je n'arrive pas à avoir dans certains cas un résultat juste
Deuxième étape.
Remonter en cellule AQ le prix le plus bas correspondant à la tranche. La difficulté est que je peux avoir plusieurs tranches qui se recoupent.
Par exemple, pour une quantité de consultation de 50, le prix le plus bas est 2,71 car le tarif de 99 de DIGIKEY est plus intéressant que le tarif de 50 de FARNELL qui est de 15,6.
J'ai essayé avec la fonction MIN(SI
Je remonte seulement le premier prix de la tranche.
Troisième étape
Remonter le fournisseur et la date MAJ en cellule AN et AO
Je pense qu'une fois que l'on aura obtenu la bonne tranche et le tarif le plus bas, ces informations seront facilement remontées avec une RECHERCHEX en fonction de la cellule AM et AQ.
N'ayant pas toutes les compétences requises pour aborder mon sujet, Je vous demande si vous pourriez me trouver une solution simple à mes difficultés rencontrées.
Cordialement.
SME
bonjour
dans ton fichier, dans la feuille tarif, la tranche en colonne G (code article+cc+tranche) ne correspond pas à la tranche en colonne I, est-ce normal?
Si cela correspond bien, il y a juste une mise en forme sur 10 caractères concaténée avec le code article + CC
bonjour,
pour moi ce n'est pas la même valeur
G4=499, i4=299
G5=999,i5=599
G6=1999,i6=899
G7=4999,i7=999
etc ...
Oui effectivement, excusez moi, je n'avais pas vu, c'est quand j'ai simplifié mon fichier, la formule a disparu
Il faut appliquer la formule =F2&TEXTE(I2;"0000000000") en colonne G
re-bonjour,
autre question, dans ton exemple :
imaginons que tu n'aies que mouser comme fournisseur, pour 599 pièces quel tarif vas-tu prendre ?
tarif D=599*4.1 = 2455.9 ou tarif E= (tu dois en acheter 899 pour bénéficier du tarif) 899*3.4=3056.6 ou F (tu dois en acheter 999 pour bénéficier du tarif) 999*2.4=2397.6
Je prends le tarif 599
La tranche commence à la tranche 299 jusqu'à la tranche de 599 prix 4,10
C'est pour cette raison que nous n'avons pas de tranche 1
Première tranche : 9 le prix de 18,9 est de 1 à 9 pièces
12,6 corespond de 10 à 99
etc
donc tu paies 2455.9 alors que tu pourrais ne payer que 2397.6 ?
Oui, si on ne veux pas stocker, et si ce composant n'est utiliser que sur ce produit, on paie le prix correspondant à la quantité de consultation.
Je viens de m'appercevoir d'une erreur en colonne AL, il ne faut pas concaténer le CC (colonne F)
Ce CC correspond au code fournisseur.
Si je veux détecter le meilleur tarif parmi tout les fournisseurs, il faut que je recherche sur le code article concaténé avec la quantité de consultation
Pour trouver la bonne tranche par rapport à la quantité de consultation , j'utilise la formule en colonne AM =RECHERCHEX(AL6;TARIF!G:G;TARIF!G:G;;1)
re-bonjour,
si on ne veut pas stocker, je ne comprend pas cette phrase :
Si la quantité de consultation est 150, la tranche la plus proche de l'onglet tarif est 200
Si on ne veut pas de stock, Il faut prendre la tranche de 100
Ensuite, c'est une négociation à avoir avec son fournisseur pour obtenir le prix de la tranche supérieur.
Mécaniquement, il faut raisonner comme cela
Effectivement, sur la formule =RECHERCHEX(AL6;TARIF!G:G;TARIF!G:G;;1), il faut mettre -1 à la fin de la rechercheX
Par contre, si consultation 1, ça ne fonctionne pas
bonjour,
une proposition via une fonction personnalisée (vba)
. fonction utilisée en AK5 de la feuille consultation.
Merci pour votre réponse
Comme je l'indiquait au message précédent, je me suis aperçu que j'ai fait une erreur de concaténer la colonne F dans la colonne AK, cela réduit les tranches à un seul fournisseur.
Le but est si qté de consultation = 1, tranche 5 FARNELL
si 6, tranche 8 FARNELL
si 9, tranche 9 MOUSER ou DIGIKEY prendre le fournisseur qui a le prix le plus petite
ect
si 300, tranche 299 MOUSER
Jai trouvé cette solution qui fonctionne
=SI(RECHERCHEX(CONCATENER(E5;TEXTE($AR$1;"0000000000"));TARIF!G:G;TARIF!G:G;;-1)="Code Article+Tranche";RECHERCHEX(CONCATENER(E5;TEXTE($AR$1;"0000000000"));TARIF!G:G;TARIF!G:G;;1);RECHERCHEX(CONCATENER(E5;TEXTE($AR$1;"0000000000"));TARIF!G:G;TARIF!G:G;;-1))
j'ai été obligé de faire une condition car dans le cas de recherchex avec -1, si qté consultation 1, ça me renvoie le titre de la colonne.
Effectivement, je suis OK, je ne l'ai pas vu
Bonjour,
Je vous remercie pour votre aide concernant mon problème tarifaire
J'ai une question, pour quelle raison mon tarif change à 55 pièces ? Le prix devrait changer à partir de 51 pièces à 2,71
De plus, pouvez vous m'explquer comment vous avez réaliser cette fonction ?
Si je veux utiliser votre fonction dans mon fichier original, comment je dois faire ?
Cordialement.
bonjour,
J'ai une question, pour quelle raison mon tarif change à 55 pièces ? Le prix devrait changer à partir de 51 pièces à 2,71
Je ne vois pas de quoi tu parles, si j'introduis 51 ou 55 j'ai FARNELL France 50 pièces à 15,60 avec tes données. Je vois bien un tarif à 2,71, donc je suppose que ta remarque s'applique au choix entre 9 et 99 pièces (digikey), l'écart entre 51 et 9 est de 42 et celui entre 51 et 99 est de 48, on choisit donc le tarif correspondant à 9 pièces.
pour mettre la fonction dans ton classeur :
1 copier le code sur le forum , (ctrl-C)
2 aller dans excel
3 faire alt-F11, pour ouvrir l'editeur VBA
4 faire insérer module
5 coller le code (ctrl-V)
6 sauver le classeur avec les macros (format xlsm)
7 la fonction est disponible dans ton classeur
Function meilleurchoix(tf, reference, quantite)
'retourne le meilleur tarif pour une quantité précise d'une référence
' tf = plage des données societe,code four,nom four,code art,cc,code art+cc,code art+cc+tranche,lettre,tranche,PUHT,tarif val,TAKT,DATMAJ
mt = 1E+99 'meilleur tarif
ecartfournisseur = 1E+99 'meilleur ecart (le plus petit)
For i = 1 To tf.Rows.Count
If reference = tf(i, "D") Then
tranche = tf(i, "i")
If tf(i, "B") <> fournisseur Then
fournisseur = tf(i, "B")
End If
If Abs(tranche - quantite) = ecartfournisseur Then
tarif = tf(i, "J") ' si même écart on choisit le meilleur tarif
If tarif < mt Then
ecartfournisseur = Abs(tranche - quantite)
mt = tarif: mti = i
End If
ElseIf Abs(tranche - quantite) < ecartfournisseur Then
'si ecart plus petit on prend le plus petit ecart
tarif = tf(i, "J")
ecartfournisseur = Abs(tranche - quantite)
mt = tarif: mti = i
End If
End If
Next i
meilleurchoix = tf(mti, "G")
End FunctionJe reviens sur ta réponse suite à ma question précédente
Pour moi, le tarif tranche 9 à 3,9 s'applique jusqu'à Tranche de 50 puisque le prix de la tranche de 50 est plus élevé.
A 51, la tranche passe à 99 au meilleur prix de 2,71.
Or ce changement se fait à consultation 55
On a le même problème pour la tranche 499 à 1,62 qui change pour une consultation de 750 alors que le prix devrait changer à consultation 500.