XL 2016 Recherche et calcul dans un sous-ensemble de feuilles selon condition du nom de la feuille

KIM

XLDnaute Accro
Bonsoir le forum, Bonsoir les ami(e)s,
Avant tout je tiens à vous présenter tous mes voeux de bonheur, de santé et de réussite pour cette nouvelle année 2021. Qu'elle soit porteuse d'Espérance en ces moments difficiles.
Je reviens vers vous, comme d'habitude, pour demander de l'aide et vous en remercie par avance.
Pour calculer le coût du transport des articles de différents pays de l'europe, j'utilise un fichier par pays BE, D, FR, NL. Je souhaite regrouper ces fichiers en un seul car ils ont le même format des données.
Le fichier joint calcule le coût pour la Belgique BE.
Ref : Feuille des références des destinations en France;
Les feuilles suivantes sont utilisées pour chaque pays avec le code pays devant le nom de chaque transporteur.
Pour la Belgique :
BE_Articles : Base des articles
BE_Azer : coût transport Azer pour la France par Dep/Ville
BE_Qwert : coût transport Qwertpour la France par Dep/Ville
Calcul_Tarif : permet de calculer le coût de transport par destination et par poids du produit commandé:
A5 : Actuellement le pays est figé =BE
B5 : Je sélectionne le département =>C5 et D5 sont remplies via formule RechercheV
E5 : Je sélectionne le code article => F5 Libellé du code article par RechercheV dans feuille BE_Articles
G5: Je saisis le nombre de palette commandé
Le poids total à transporter est calculé dans H5 selon les données de l'article dans BE_Articles
Ensuite Dans D9 et D10 le coût du transport est calculé selon les données de chaque transporteur BE_Azer et BE-Qwert.
Une macro extrait les données de l'article concerné de chaque transporteur BE pour un contrôle visuel.
Ces calculs fonctionnent correctement dans des fichiers séparés pour chaque pays comme pour la BE dans le fichier joint.
J'ai intégré les données pour l'Allemegne : Base DE_Articles, Transporteurs DE_RTY, DE_HIJLM
Il peut y avoir un ou plusieurs transporteurs par pays.
Mon problème : est-il possible de calculer le poids total commandé et le calcul par transporteur selon le pays sélectionné en A5?
et si oui, comment modifier les formules ou faut-il passer par du vba.
et comment faire une liste liée Pays/Transporteur
Je reste à votre disposition pour toute info souhaitée et Je vous remercie par avance de toute l'aide apportée.
Bonne soirée
KIM
 

Pièces jointes

  • KIM_Grille_Art.xlsm
    48.3 KB · Affichages: 20

KIM

XLDnaute Accro
Bonjour Job75, Bonjour le Forum,
Peut-être je dois ouvrir une nouvelle discussion, mais comme la formule du prix est complexe, je fais appel à vous et vous en remercie d'avance.
Une nouvelle contrainte par transporteur :
-Camion sans grue, par défaut, poids max 25 tonnes
- ou Camion avec grue, poids max 22 tonnes.
Le poids du camion étant codé en dur dans la formule du prix,
Comment remplacer le poids du camion, figé dans la formule, par une variable à 2 valeurs.
Par défaut, le camion utilisé est de 25 tonnes max.
J'ai rajouté aussi le calcul du nombre de camion à commander et le reste à transporter avec camion de 25 tonnes. voir fichier ci-joint
Merci de votre aide.
KIM
 

Pièces jointes

  • KIM_Grille_Art(4).xlsx
    36.1 KB · Affichages: 3

job75

XLDnaute Barbatruc
Bonjour KIM, le forum,

Je regarderai plus tard votre dernière demande.

Pour l'instant voyez ce fichier (3 bis) avec une formule bien plus simple en D9 qui utilise RECHERCHEV.

Pour cela j'ai modifié la définition du nom Prix qui englobe maintenant la colonne C :
Code:
=INDIRECT(Calcul_Tarif!$A9&"_"&Calcul_Tarif!$B9&"!C15:P1000")
A+
 

Pièces jointes

  • KIM_Grille_Art(3 bis).xlsx
    36.2 KB · Affichages: 2

KIM

XLDnaute Accro
Merci pour votre retour.
C'est le calcul du prix en D9 qui est basé sur un poids de camion à 25tonnes, voir les différentes conditions de H$5
VB:
=SIERREUR(SI(ET(H$5<=[B]25[/B];H$5<>"");RECHERCHEV(D$5;Prix;EQUIV(H$5;TR_1;1)+1;0);SI(H$5>[B]25[/B];ENT((ARRONDI.INF(H$5/[B]25[/B];0)))*RECHERCHEV(D$5;Prix;EQUIV(H$5;TR_1;1)+1;0)+RECHERCHEV(D$5;Prix;EQUIV(H$5-([B]25[/B]*ENT((ARRONDI.INF(H$5/[B]25[/B];0))));TR_1;1)+1;0);0));"")

Camion avec Grue, le poids max est de 22 tonnes.
Comment intégrer dans cette formule la condition de H$5 :
par défaut 25 sinon si Grue à "Oui" 22

Merci beaucoup de votre aide.
KIM
 

KIM

XLDnaute Accro
Re-bonjour,
Je reviens vers toi concernant le calcul du Prix avec Grue à "Oui". Je ne retrouve pas le bon résultat. Ce qui est troublant c'est que, sans grue, le calcul est correct.
Ex : voir fichier ci-joint
De BE à Nice
1 camion 22 tonnes = 70€
3,7 tonnes = 25€
total Prix = 75€

Merci pour ton aide et Désolé pour le dérangement,
KIM
 

Pièces jointes

  • KIM_Grille_Art(5).xlsx
    35.9 KB · Affichages: 2

job75

XLDnaute Barbatruc
Bah j'aurais dû vérifier votre formule en D9 car elle était fausse !

Pour le 2ème RECHERCHEV ce n'est pas H$5 qu'il faut utiliser dans EQUIV mais le poids du camion donc utilisez ce fichier (6) avec EQUIV(SI(E9="Oui";22;25);TR_1;1) au lieu de EQUIV(H$5;TR_1;1).

Pour le prix en D10 il est facile de comprendre pourquoi il n'y avait pas d'erreur avec un camion de 25 T.
 

Pièces jointes

  • KIM_Grille_Art(6).xlsx
    35.9 KB · Affichages: 3

KIM

XLDnaute Accro
Bonjour Job75, bonjour le Forum,
Je reviens vers vous concernant le calcul du prix du transport, une nouvelle contrainte que j'ai oubliée concernant un camion avec grue.
Sans grue, aucun problème
Avec Grue "Oui"
1/ Nombre de camion est basé sur un camion de 22t max
=SI(ESTNUM(D9+H$5);ARRONDI.SUP(H$5/SI(E9="Oui";22;25);0);"")
OK
2/ Pour le calcul du prix par transporteur et par destination, le prix du transport camion à considérer est celui de 25t quelque soit le poids de la marchandise dans un camion (<=22t)
càd
Avec Grue "Oui"
le poids à transporter est de H$5 = 27t :
2 camions : 1 à 22t et l'autre à 5t
Prix du transport : 2 camions * prix du camion de 25t
J'ai essayé de modifier la formule du prix, je ne retrouve pas le bon résultat.
Est-il possible d'intégrer cette contrainte dans le calcul du prix ?

Merci d'avance pour votre aide.

KIM
 

Pièces jointes

  • KIM_Grille_Art(7).xlsx
    36.5 KB · Affichages: 7

Discussions similaires