Répartition de montants en fonction de plafonds

[Rpmo]

Bonjour,

Je souhaite pouvoir répartir des montants attribués à des salariés en fonction de primes tout en respectant les plafonds financiers de ces primes.

La répartition se ferait pour chaque salarié selon un ordre de priorité par prime.

J'ai essayé les formules mais cela est trop compliqué pour moi.

Quelqu'un aurait-il la solution?

Je vous joins un fichier pour comprendre ce que je cherche à obtenir.
Le tableau contient des exemples, le nombre de salariés à traiter étant beaucoup plus important.

merci d'avance pour votre aide.

 

[CISCO]

Re : Répartition de montants en fonction de plafonds

Bonsoir

Il faudrait peut être que tu nous en dises un peu plus pour que nous puissions t'aider.

@ plus

 

[Rpmo]

Re : Répartition de montants en fonction de plafonds

C'est a dire?
Que voulez vous savoir de plus?
Cordialement

 

[CISCO]

Re : Répartition de montants en fonction de plafonds

Bonjour

Quel(s) critère(s) faut-il adopter pour répartir ces montants ? On ne fait pas ça au hasard, non ?

Bonjour,

des montants attribués à des salariés en fonction de primes tout en respectant les plafonds financiers de ces primes.

La répartition se ferait pour chaque salarié selon un ordre de priorité par prime.

merci d'avance pour votre aide.

@ plus

 

[Rpmo]

Re : Répartition de montants en fonction de plafonds

L'ordre de priorité est indiqué dans une colonne : 1, 2, 3 ,4.

Exemple :
- une prime A est versée pour 40€ alors que son plafond est de 30€
- une prime B, versée pour 30€ - plafond à 50€
- prime C, versée pour 5€ - plafond à 10€

Ordre de priorité défini :
- prime A : 3
- prime B : 2
- prime C : 1

La répartition est donc la suivante :
- la prime A dépassant de 10€ le plafond est réparti d'abord sur la prime C, pour 5€ et ensuite sur la prime B pour 5€

Au final on a :
- prime A : 30€
- prime B : 35€
- prime C : 10€

Est ce plus clair?

 

[CISCO]

Re : Répartition de montants en fonction de plafonds

Bonjour

Donc, ce n'est que la colonne U qu'il faut modifier, en tenant compte des plafonds de la colonne P, en gardant les mêmes totaux qu'en colonne Q, et en respectant les priorités de la colonne F ?

@ plus

 

[Rpmo]

Re : Répartition de montants en fonction de plafonds

L'ordre de priorité se trouve dans la colonne V et non F.
Sinon pour le reste c'est ça.

 

[CISCO]

Re : Répartition de montants en fonction de plafonds

Bonsoir

L'ordre de priorité se trouve dans la colonne V et non F.
Sinon pour le reste c'est ça.

Oui, mais comme on peut déduire le contenu de la colonne V à partir de celui de la colonne F...

Est-ce que tu as une idée du nombre max de lignes possibles par salarié, 6 dans ton exemple ?

@ plus

 

[ODVJ]

Re : Répartition de montants en fonction de plafonds

Bonsoir à tous,

voilà une formule qui devrait convenir.
en ligne 2 d'une colonne disponible :

SI($H2>=$P2;$P2;MIN($P2;$H2+SOMMEPROD(($A$2:$A$20=$A2)*($H$2:$H$20-$P$2:$P$20>0)*($H$2:$H$20-$P$2:$P$20))-MIN(SOMMEPROD(($A$2:$A$20=$A2)*($H$2:$H$20-$P$2:$P$20>0)*($H$2:$H$20-$P$2:$P$20));SOMMEPROD(($A$2:$A$20=$A2)*($V$2:$V$20<$V2)*($H$2:$H$20-$P$2:$P$20<0)*($P$2:$P$20-$H$2:$H$20)))))

à recopier jusqu'en ligne 20

Les résultats obtenus diffèrent des tiens pour les cellules suivantes : U12, U16 à U18 et U20
Il me semble que tes résultats ne respectent pas la logique que j'ai cru deviner de tes explications.

Si je prend le salariéE pour les cellules U12 et U16, j'ai 77€ (SOMMEPROD(($A$2:$A$20=$A11)*($H$2:$H$20-$P$2:$P$20>0)*($H$2:$H$20-$P$2:$P$20))) à répartir sur les primes inférieures aux plafonds de ce salarié à savoir celles de priorité 2, 5 et 6.
Les autres (1, 3 et 4) sont plafonnées à 10€, 5€ et 25€. Ce sont ces cellules qui fournissent les 77€ à répartir sur 2, 5 et 6.
D'abord 2 : pour aller au plafond de 20€, on prend 7€ sur les 77€. Il reste donc 70€ à répartir.
Ensuite 5 : pour aller au plafond de 45€, on prend 45€ sur les 70€. Il reste alors 25€ à répartir.
En fin, 6 : pour aller au plafond de 50€, il faudrait 38€. On ne peut ajouter que les 25€ restant à répartir aux 12€ de la prime initiale soit 37€. Il n'y a plus rien à répartir.

Maintenant, le salariéF pour les cellules U17, U18 et U20.
Je trouve qu'il y a 19.77€ à répartir
Les primes de priorité 1 et 4 sont plafonnées respectivement à 20€ et 45€.
Les primes de priorité 2 et 3 sont inférieures aux plafonds et vont a priori bénéficier des 19.77€ à répartir.
D'abord 2 : pour aller au plafond de 50€, il faudrait 27.50€. On ne peut ajouter que les 19.77€ à répartir aux 22.50€ de la prime initiale soit 42.27€. Il n'y a plus rien à répartir.
Donc 3 ne bénéficie d'aucune augmentation et reste à 6€.

cordialement

 

[CISCO]

Re : Répartition de montants en fonction de plafonds

Bonsoir à tous, bonsoir ODJV

@ ODJV : Je cherchais comment traiter ce "problème", et c'est déjà presque fait, avec une formule à rallonge il est vrai. Encore une fois, chapeau.
ODJV, c'est normal que tu ne trouves pas les mêmes résultats que Rpmo, car il ni y a pas, il me semble, une solution, mais plusieurs. Une petite remarque toutefois : Il me semble que les priorités fonctionnent du nombre le plus grand vers le nombre le plus petit, à savoir que la prime de priorité 6 doit être la plus proche possible, ou égale à son plafond, celle de priorité 5, un peu moins proche, ou égale..., et celle de priorité 1 la moins proche, ou égale de son plafond. Est-ce bien la règle que tu as respectée ?

@ plus

 

[ODVJ]

Re : Répartition de montants en fonction de plafonds

Bonsoir CISCO,

Je n'avais pas envisagé l'ordre des priorités dans le sens décroissant.
Il suffit dans la formule de remplacer le signe < par > dans ($V$2:$V$20<$V2).

Mais les écarts subsistent, même s'ils ne sont pas toujours aux mêmes endroits.

Pour l'unicité de la solution, cela dépend si on maximise le comblement des écarts à chaque étape (c'est ce que j'ai fait) ou pas.
Si on maximise, la solution doit être unique.
Sinon, il faut une règle de répartition.... que je n'ai pas vue.

En tout état de cause, je ne comprend pas, dans le tableau de Rpmo, comment on peut diminuer une prime calculée (SalariéF prime G qui passe de 6€ à O€)

Ou alors, le salariéF n'est pas aimé.....


Pour la formule, voilà quelques commentaires pour la rendre plus digeste :

________Formule_____________________________ Commentaires_______________________

SI(					Si
	$H2>=$P2			la prime calculée est supérieure au plafond,
;					alors
	$P2					la limiter au plafond
;					sinon
	MIN(				en la limitant
		$P2					au plafond en cours
	;						,
		$H2					prendre la prime calculée (inférieure au plafond)
		+					et lui ajouter
		SOMMEPROD(			le total,
			($A$2:$A$20=$A2)				pour le salarié en cours de traitement,
			($H$2:$H$20-$P$2:$P$20>0)				des dépassements de plafond
			($H$2:$H$20-$P$2:$P$20)				des primes calculées
		)
		-					puis lui soustraire,
		MIN(					en le plafonnant
			SOMMEPROD(				à la somme,
				($A$2:$A$20=$A2)				pour le salarié en cours de traitement,
				($H$2:$H$20-$P$2:$P$20>0)				des dépassements de plafond
				($H$2:$H$20-$P$2:$P$20)				des primes calculées
			)
		;
			SOMMEPROD(			le total
				($A$2:$A$20=$A2)			pour le salarié en cours de traitement
				($P$2:$P$20-$H$2:$H$20)			des écarts "plafond - prime"
				($V$2:$V$20<$V2)			déjà (dans le respect des priorités)
				($H$2:$H$20-$P$2:$P$20<0)			comblés
			)
		)
	)
)					fin de si

Cordialement

 

[CISCO]

Re : Répartition de montants en fonction de plafonds

Bonjour ODJV

A Rpmo à nous dire ce qu'il veut exactement.

@ plus

P.S : Peut être que le salariéF n'est pas très aimé !