XL 2016 Paramètre de calcul à inclure dans une fonction avec INDEX EQUIV

Habs57

XLDnaute Nouveau
Bonjour à toutes et à tous,

Dans le cadre de mon travail, je suis en train de créer un fichier Excel de gestion commerciale avec comme fonctionnalités :
l'édition de bons de livraison/ factures/ devis, la gestion de stock entrant et sortant.
Il inclut un fichier client et une grille tarifaire de transport par département et par tranches de poids.
(dans l'Excel joint, je l'ai volontairement allégé pour simplifier)
Dans cette grille tarifaire, il y a des tranches de poids de 10 en 10 jusque 100kg, puis une tranche de poids de 100 à 250, puis 5 tranches de poids avec tarif à la palette (par tranche de 800kg).

Je cherche une formule de calcul de prix automatique en fonction du poids et du département de destination d'après ma grille tarifaire.
J'étais parti sur une fonction INDEX EQUIV mais je n'arrivais pas à la composer correctement.
J'ai pu trouver une première aide sur ce forum avec la solution suivante (en passant merci HASCO !) :

=INDEX('GRILLE TARIFAIRE'!$C$4:$P$99;EQUIV(TEXTE($C2;"00");'GRILLE TARIFAIRE'!$A$4:$A$99;0);EQUIV('FEUILLE 1'!$D2;'GRILLE TARIFAIRE'!$C$1:$R$1;1))

Lorsque la recherche du tarif se fait dans la grille tarifaire, tout se passe bien jusque la colonne "L" 90 à 99kg.
Cela fonctionne bien également avec les colonnes "N à R" pour les tranches de poids à la palette.
C'est à la colonne "M" que cela se corse. En effet, dans cette colonne, les tarifs sont exprimés aux 100kg.
C'est à dire : Si on expédie une palette de 100kg dans le dpt 01, le prix est de 61.87 €.
Mais si on expédie une palette de 160kg dans ce même dpt, le prix doit être de 98.99 € (=61.87/100*160 ou si vous préférez =61.87*1.6).
Un autre exemple, si j'expédie une palette de 200kg dans le dpt 08, le prix sera de 132.84 (66.42*2).

Je me retrouve donc avec cette variable à inclure dans la formule et je bloque dessus.
Je pensais imbriquer la fonction ci-dessus dans une fonction SI.
Est-ce que mon raisonnement est bon et est-ce surtout applicable efficacement ?

Est-ce que quelqu'un saurait m'aider dans l'élaboration de cette formule et si besoin m'expliquer le cheminement ?
(comme ça je dormirais moins bête ce soir :) mais surtout cela nous servira encore).

Je vous remercie d'avance pour votre précieuse aide et pour votre temps.
Bien cordialement.

Christophe
 

Pièces jointes

  • TEST.xlsx
    21.8 KB · Affichages: 19

job75

XLDnaute Barbatruc
Bonjour Habs57,

Dans la 1ère feuille sélectionner E2 et définir le nom Prix par :
Code:
=INDEX('GRILLE TARIFAIRE'!$C$4:$R$99;EQUIV(TEXTE('FEUILLE 1'!$C2;"00");'GRILLE TARIFAIRE'!$A$4:$A$99;0);EQUIV('FEUILLE 1'!$D2;'GRILLE TARIFAIRE'!$C$1:$R$1;1))
Formule en E2 à tirer vers le bas :
Code:
=SI(EQUIV(D2;'GRILLE TARIFAIRE'!C$1:R$1;1)=11;Prix*D2/100;Prix)
A+
 

Pièces jointes

  • TEST(1).xlsx
    22.8 KB · Affichages: 21

Habs57

XLDnaute Nouveau
Merci beaucoup Job75 !
Sans vouloir abuser de votre temps, pourriez-vous s'il vous plait m'expliquer le cheminement de la formule ?
Car j'aimerais comprendre et savoir m'en servir dans le futur.
Encore merci pour votre temps.

Bien cordialement.
Christophe
 

job75

XLDnaute Barbatruc
Bonjour Habs57,

Le cheminement est évident.

Pour simplifier la formule en E2 il suffit de créer le nom Prix défini par une formule.

Cette formule dépend de la ligne, c'est pourquoi E2 doit être sélectionnée.

Quant à la formule en E2 vous devez la comprendre, non ?

A+
 

Habs57

XLDnaute Nouveau
Bonjour Job75,

Merci beaucoup pour votre explication. Je ne connaissais pas cela.
Je savais que l'on peut renommer une colonne mais pas une cellule en lui attribuant une formule.
Effectivement cela simplifie bien les choses.
Merci beaucoup !
A+
 

Discussions similaires

Statistiques des forums

Discussions
311 720
Messages
2 081 910
Membres
101 837
dernier inscrit
Ugo