XL 2016 Calcul automatique via grille tarifaire (poids et département)

[AD1234]

Bonjour,
Je souhaite connaitre le tarif (colonne F) que je vais payer pour mes commandes. 1 ligne = 1 commande. Le tarif va être différent sur chaque commande en fonction de deux données : le département et le poids.
Pour connaitre le tarif je dois me référer à la grille tarifaire dans une seconde feuille en croisant mon département et le poids (fourchette de différents poids).

J'ai essayé de coupler recherche V et recherche H mais en vain...

Auriez-vous une solution a m'apporter ?
Merci d'avance!!!
Loïs

 

[Hasco]

Bonjour,

Voici une solution par Index/Equiv:

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

Par contre dans votre grille tarifaire les numéros de département sont sous forme de texte et non numérique ce qui oblige à chercher (premier equiv) en rajoutant les guillemets.

Noter les 3èmes arguments des 2 equiv le premier pour une recherche exacte, l'autre pour une recherche de l'item inferieur (les poids doivent être dans l'ordre croissant)

Cordialement

 

[AD1234]

Merci pour votre réactivité ! Comment se compose la fonction index exactement ? Je n'arrive pas à comprendre comment elle se décompose. J'ai l'impression qu'il y a deux formules dans une seule ? Je me trompe peut-être...

Bonne soirée à vous !

 

[Hasco]

Re,

Sous cette forme la fonction index renvoie la référence de cellule de la plage du premier argument qui est à l'intersection d'une n ligne et n colonne.

Equiv va renvoyer un index de position d'une valeur dans une liste soit en ligne soit en colonne

EQUIV(TEXTE($C2;"00");'GRILLE TARIFAIRE'!$A$4:$A$99;0)

Va chercher la valeur exacte de son premier paramètre (le n° de département renvoyé sous forme de texte) dans la liste des départements et retourner sa position

EQUIV('FEUILLE 1'!$D2;'GRILLE TARIFAIRE'!$C$1:$P$1;1)

Va chercher la valeur approximative inférieure à son premier paramètre ($D2 le poids) dans la ligne des poids et retourner son index de position.

Je viens de modifier la formule pour utiliser la fonction texte qui renverra les départements 01 à 09 (numérique) sous forme de texte à deux chiffres.

=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:$P$1;1))

Mais vous devriez uniformiser le type de vos numéros de département, soit textuel soit numérique. C'est source de complexification et d'erreurs que de mélanger les deux.
cordialement

 

[Habs57]

Bonjour HASCO,

Tout d'abord merci pour nous avoir expliqué votre formule. Cela permet une meilleure compréhension.
Je cherchais également 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 également parti sur une fonction INDEX EQUIV mais je n'arrivais pas à la composer correctement. Il me manquait toujours un paramètre.
Votre formule m'a permis d'y voir plus clair et cela m'a beaucoup aidé.
Mais dans votre formule, il y a un paramètre qui manque et cela vient corser le tout.
Je ne comprends d'ailleurs pas que Lois ne l'ai pas vu, il a forcément dû le remarquer et il aurait pu vous l'expliquer. Je vous explique à tous les deux :
Dans la grille tarifaire, il y a des tranches de poids de 10 en 10 jusque 100kg.
Il y a ensuite 4 tranches tarifaires (de 100 à 250 - de 251 à 500 - de 501 à 1000 et de 1001 à 2999) dont 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). Et c'est comme cela pour ces 4 tranches tarifaires.
Un autre exemple, j'expédie une palette de 300kg dans le dpt 08, le prix est de 189.48 (63.16*3).

Je me retrouve donc avec ce même problème de variable à inclure dans la formule et je bloque dessus.
De plus, dans ma propre grille tarifaire, j'ai des tranches de poids de 10 en 10 jusque 100kg, une tranche de poids de 100 à 250kg et ensuite 5 tranche de poids avec un tarif à la palette (colonnes N à R, j'ai décalé les 3 colonnes de 251 à 2999 tout à droite pour l'adapter à ma grille tarifaire). J'y joins l'excel.

Est-ce que vous sauriez m'aider à inclure ces paramètres dans la formule de calcul et si possible nous 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

 

[Hasco]

Bonjour Christophe et bienvenue sur le forum,

Il serait préférable que vous ouvriez votre propre fil de discussion plutôt que de continuer sur celui-ci.
Oui je ne m'étais pas préoccupé de ces histoires de poids au 100kg. Et le demandeur non plus.
Je n'ai fait que répondre à la question telle qu'elle était posée.
Les réponses sont souvent à la hauteur des questions. Plus la question est claire et bien posée plus la réponse sera pertinente.

Cordialement