XL 2010 Formule prix par tranche

[zoubida95]

Bonjour à tous,

j'aurais besoin de votre aide s'il vous plait pour une formule qui afficherait le prix en fonction de la tranche.

Je ne sais pas si je m'exprime suffisamment clairement alors je vous mets un exemple
soit
quantité totale (somme de D4058) 182 334 quantité
il y a un prix par tranche =
de 120 000 à 140 000 = 4.47 euros
de 140 000 à 150 000 = 4.17 euros
Il y a 7 tranches indiqué dans la feuille tranche tarif 2018.
Le fichier excel ci-joint
Vous est il possible de m'aide?
En vous remerciant
Zoubida

 

[chris]

Bonjour
Il faut mettre la 1ere tranche de 0 à 140 000 car là ta plage en vert est de 78 591 dont en dessous de 120 000 et D2 est égale à la première tranche

En E40 à recopier en dessous

=RECHERCHEV(SOMMEPROD((ANNEE($B$2:$B$58)=ANNEE(B40))*($C$2:$C$58=C40);$D$2:$D$58);'Tranche Tarif 2018'!$A$1:$C$7;3;1)

 

[zoubida95]

Bonsoir Chris,
Merci du temps consacré et de votre réponse.
Cela ne fonctionne pas je me suis surement mal exprimée.
En colonne E à partir de la cellule 40, je souhaite que soit calculé un prix par tranche de quantité de la colonne D soit le total de la cellule D40 à D58.

pour les 140 000 quantité le prix est de 4.45 €
de 140 000 à 150 000 le prix est de 4.17€

 

[chris]

RE

J'ai bien compris mais avec une tranche qui commence à 120 000 alors que la somme de D40à D51 (plage qui était dans ta formule) ne fait que 78 591 si on doit respecter l'année.

Comme je l'ai dit c'est un non sens de commencer la 1ère tranche à 120 000 et non a 0 ou 1...

Par ailleurs tu as des valeurs sur 2 années : on va chercher les prix de 2019 sur les tranches de 2018 ou 2019 ?
C'est bien là l’ambiguïté...

Si on prend 2018 quoi qu'il arrive

=RECHERCHEV(SOMMEPROD(($C$2:$C$58=C40)*1;$D$2:$D$58);'Tranche Tarif 2018'!$A$1:$C$7;3;1)

Sinon

=RECHERCHEV(SOMMEPROD(($C$2:$C$58=C40)*1;$D$2:$D$58);INDIRECT("'Tranche Tarif "&ANNEE(B40)&"'!$A$1:$C$7");3;1)

 

[zoubida95]

Merci Chris pour la réponse
C'est mon petit coté blonde qui n'a pas tout imprimé! ;-)
Colonne E ce sont les prix 2018 et sur la colonne G les prix 2019.
j'ai corrigé comme indiqué mais cela ne fonctionne pas.
ci-joint le tableau corrigé avec la tranche à 0 ;-)

 

[chris]

RE

Donc colonne D, 2018

=RECHERCHEV(SOMMEPROD(($C$2:$C$58=C40)*1;$D$2:$D$58);'Tranche Tarif 2018'!$A$1:$C$7;3;1)
donne 3,96 qui correspond à la tranche de 180 001 à 200 000 pour un total de 182 234 de $D$2:$D$58

Donc quel est le problème ?

 

[zoubida95]

Merci de ton retour
Donc je me suis mal exprimée! partons sur un total de 160 000 quantité
Le calcule est le suivant :
0 à 140 000 = 140 000 x 4.45 euros
140 0001 à 150 000 = 10 000 x 4.17 euros
150 001 à 160 000 = 10 000 x 4.11 euros

 

[chris]

RE

Cela pose un problème car sur un même mois on pourrait avoir une partie dans une tranche et un autre dans une autre. Donc en théorie il faudrait faire un prix moyen pondéré...

Sinon

=RECHERCHEV(SOMMEPROD(($C$2:$C40=C40)*1;$D$2:$D40);'Tranche Tarif 2018'!$A$1:$C$7;3;1)

 

[zoubida95]

Hello Chris,
Merci pour votre temps et votre patience.
Vous avez raison mon côté blonde et obstinée ni a pas pensé!
Je vous souhaite une belle journée