Calcul de clé de répartition pour un budget

[KIM]

Bonjour le forum, Bonjour les ami(e)s,
Dès que j'ai besoin d'automatiser des tâches dans excel ou débloquer un problème je reviens vers ce site et je fais appel à votre aide. Merci d'avance
Automatiser le calcul d'une clé de répartition et le budget de chaque SERVice.
Pour chaque DIVision d'un DEPartement, Je souhaite automatiser la répartition du budget de chaque direction DIR (col D) d'une division (DIV) par service SERV (col E) au prorata du nombre d'agents de chaque service (col F).

1- Calculer la clé de répartition de chaque service dans la col M
2- Calculer le budget du service selon la clé de répartition dans la col N

Ci-joint un fichier xls avec un exemple du résultat attendu en col J et K

Merci de votre aide
KIM

 

[titiborregan5]

Re : Calcul de clé de répartition pour un budget

Bonjour Kim, le forum,

comment on sait que les cellules roses ne sont pas à prendre?
Au début je pensais que c'était parce que le serv était vide. Mais d'autres sont vides et il n'est pas marqué ne pas prendre (ligne 30)...
so?

 

[titiborregan5]

Re : Calcul de clé de répartition pour un budget

Et aussi, quelle version d'Excel as-tu?
j'ai fait avec Somme.si.ens mais il faut avoir au minimum 2007...

 

[Paf]

Re : Calcul de clé de répartition pour un budget

Bonjour à tous

J'ai fait comme si les lignes où SERV n'est pas renseigné n'étaient pas à prendre en copmte:

En J6

=F6/SOMMEPROD(($F$6:$F$49)*($E$6:$E$49<>"")*($C$6:$C$49=C6)*($B$6:$B$49=B6)*($A$6:$A$49=A6))

A+

 

[titiborregan5]

Re : Calcul de clé de répartition pour un budget

Hello Paf,
ma formule avec somme.si.ens que Kim n'a pas forcément donc sommeprod est mieux...
en J6
=F6/SOMME.SI.ENS($F$6:$F$49;$C$6:$C$49;C6;$G$6:$G$49;"")

je suis parti des infos en colonne G par contre pour savoir si on prenait ou pas!

 

[KIM]

Re : Calcul de clé de répartition pour un budget

Bonjour titiborregan5, Bonjour Paf et le forum,

Quand le SERV est vide, on ne prend pas en compte le nombre d'agents. En effet je ne l'ai pas marqué partout.
Je travaille avec Excel 2010 au bureau et Excel 2003 chez moi.

Mon fichier de travail fait plus de 2000 lignes. Je vais tester les 2 formules en espérant que l'exécution est rapide.

Quelques infos supplémentaires : Un SERVice est rataché à une DIRection et peut aussi être rattaché à une autre DIRection d'une même DIVision.
De même un SERVice peut se retrouver dans un autre DEPartement. Le calcul de la clé de répartition et la répartion du budget est à faire par DIRection car les agents d'un même service et le budget sont différents d'une DIRection à une autre. voir les formules des colonnes J et K


Merci d'avance
KIM

 

[KIM]

Re : Calcul de clé de répartition pour un budget

Bonjour titiborregan5, Paf et le forum,
J'ai testé les 2 formules.
@Paf : La formule SOMMEPROD ne prend pas en compte les cases vides de la col E. Voir les cellules M8, M13 etc.
@Titiborregan5 : La formule SOMME.SI/ENS génère une erreur #DIV/0!

Merci encore
KIM

 

[Paf]

Re : Calcul de clé de répartition pour un budget

Re

=SI(E6<>"";F6/SOMMEPROD(($F$6:$F$49)*($E$6:$E$49<>"")*($C$6:$C$49=C6)*($B$6:$B$49=B6)*($A$6:$A$49=A6));0)

 

[titiborregan5]

Re : Calcul de clé de répartition pour un budget

Chez moi ça marche...
voir PJ

 

[KIM]

Re : Calcul de clé de répartition pour un budget

Re le fil et le forum,
@titiborregan5 : Comment modifier ta formule pour prendre en compte la col E (SERV) et ne pas intégrer les cases vides de la col E.*
J'ai seulement saisi des commentaires dans la col G pour info.

@Paf : La nouvelle formule avec SI fonctionne. Par contre pourquoi la condition ($E$6:$E$49<>"") dans la formule SOMMEPROD n'a pas rendu le résutat souhaité et qu'on soit obligé de rajouter un SI.

Merci d'avance
KIM

 

[Paf]

Re : Calcul de clé de répartition pour un budget

re,

$E$6:$E$49<>"" permet de ne pas prendre en compte cette ligne dans le calcul de la somme

mais la recherche est faite pour chaque ligne y compris pour pour une ligne ou SERV n'est pas renseigné , et donc calcule la somme mais sans sa propre ligne . (d'où le rajout du test pour ne pas effectuer de calcul si SERV est non renseigné)

A+

 

[KIM]

Re : Calcul de clé de répartition pour un budget

Re,
Merci Paf et le forum,

@titiborregan5 : Merci d'avance si je peux avoir une nouvelle formule avec la prise en compte de la col E (SERV) et une explication sur la formule.

Bonne journée
KIM