Microsoft 365 Mettre un tableau à plat et calcul selon 2 critères

ivan27 · 16 Mars 2021

Bonsoir à tous,

Je souhaite mettre à plat une grille tarifaire afin de l'intégrer dans un logiciel métier.

La première difficulté, c'est la mise en forme (voir fichier joint) qui doit être conforme à la feuille ''BD'' à partir de la feuille ''FR'' et d'une liste variable de départements de destination sur la feuille ''DEP''.
Pour chaque département et chaque tranche de poids de la feuille ''FR'' nous avons une ligne correspondante vers un département de la feuille ''DEP'' sur la feuille ''BD''.

La deuxième difficulté, c'est le calcul du prix à afficher colonne E de la feuille ''BD''. Ce calcul sera différent si le département de la feuille ''DEP'' est un département de départ ou de destination.

Si un département de la feuille ''DEP'' est un département de départ il faut prendre 50% du montant sur la grille ''FR'' (ligne de ce département) auquel on ajoute ''100%'' du montant du département de destination. Dans tous les autres cas on prend 100% du prix du département de départ + 100% du prix du département d'arrivée.

A titre d'exemple et pour vous montrer le résultat attendu, je renseigne à la main les départements 01 vers 75, 75 vers 01 et 75 vers 75

Concrètement :
Pour calculer le prix du 01 vers le 75 pour 820 kg :
Feuille FR, ligne département 01, tranche 800-899 = 23 €
Feuille FR, ligne département 75, tranche 800-899 = 13 €
Total : = 36 €

Pour calculer le prix du 75 vers le 01 pour 820 kg :
Feuille FR, ligne département 75, tranche 800-899 = 13 € * 50 % = 6.50 €
Feuille FR, ligne département 01, tranche 800-899 = 23 €
Total : = 29,5 €

Pour calculer le prix du 75 vers le 75 pour 820 kg :
Feuille FR, ligne département 75, tranche 800-899 = 13 € * 50 % = 6.50 €
Feuille FR, ligne département 75, tranche 800-899 = 13 €
Total : = 19,5 €

N'hésitez-pas à revenir vers moi si je n'ai pas été suffisamment clair !

Merci d'avance pour votre aide.

Ivan

Hasco · 17 Mars 2021

Bonjour,

Les trois feuilles idoines et seulement quelques lignes représentatives de la bd, pour faire maigrir votre fichier, eut été suffisant

Dans le fichier joint, vous trouverez en colonne E la formule qui donne les mêmes résultatq que vous.
Et la décomposition de la formule dans trois colonnes à droite du tableau afin que vous puissiez la décortiquer plus facilement.

Les Noms :

Départements : colonne de gauche du tableau de prix
Tranches : ligne du haut du tableau
Dép_Départ : la liste des départements de départs de la feuille Dep

Prix départ :

=INDEX(FR_PRIX;EQUIV(TEXTE($A2;"00");Départements;0);EQUIV(TEXTE(H2;"###0-")& TEXTE(I2;"###0");Tranches;0))

Multiplié par 1 ou 0.5 :

=(INDEX({1;0,5};--ESTNUM(EQUIV($A2;Dép_Départ;0))+1))

Prix arrivée :

=INDEX(FR_PRIX;EQUIV(TEXTE($C2;"00");Départements;0);EQUIV(TEXTE($H2;"###0-")& TEXTE($I2;"###0");Tranches;0))

Pour trouver les lignes de départements dans la feuille FR on est obligé d'employer la formule Texte($A2;"00") car il ne sont pas numériques mais textuels, ce qui n'est pas le cas dans la feuille dep. Et comme il n'y a pas de 96 et 97 on ne peut pas les prendre comme n° de ligne pour la fonction index.

Cordialement

ivan27 · 17 Mars 2021

Bonjour le forum, Roblochon,

Merci beaucoup pour cette proposition qui exécute parfaitement le calcul demandé.

Les départements de la grille FR sont amenés à évoluer ainsi que les tranches tarifaires et il en va de même pour la liste de la feuille DEP.

Est-ce qu'il existerait une possibilité d'automatiser la constitution de la feuille BD selon les éléments précités ?

Bien cordialement

Hasco · 17 Mars 2021

Re,

De quoi parlez-vous exactement, de l'adaptation des formules aux nouvelles donnes ? Ce sera à vous d'adapter. Ce n'est pas bien sorcier que de changer les références d'une formule de cellules ou d'un nom.

Si vous parlez de l'entièreté de la feuille BD, il faut ouvrir une nouvelle discussion en précisant d'où proviennent les données.

Cordialement

Microsoft 365 Mettre un tableau à plat et calcul selon 2 critères

ivan27

XLDnaute Occasionnel

Pièces jointes

Hasco

XLDnaute Barbatruc

Pièces jointes

ivan27

XLDnaute Occasionnel

Hasco

XLDnaute Barbatruc

Discussions similaires