Problème de SOMMEPROD

0

Bonjour au Forum,

J'ai un petit problème avec ma formule sommeprod pour calculer un effectif moyen Equivalent Temps Plein.

Cette formule marche bien mais pour une seule ligne de mon tableau :

=SOMMEPROD(($H$60="")*($H$60>=FIN.MOIS(DATE(ANNEE($F$3);A77;1);0))+($F$60<=DATE(ANNEE($F$3);A77;JOUR(FIN.MOIS(DATE(ANNEE($F$3);A77;1);0))));(169/(DATE(ANNEE($F$3);A77;JOUR(FIN.MOIS(DATE(ANNEE($F$3);A77;1);0)))-DATE(ANNEE($F$3);A77;1))*MAX((MIN($H$60;DATE(ANNEE($F$3);A77;JOUR(FIN.MOIS(DATE(ANNEE($F$3);A77;1);0))))-MAX($F$60;DATE(ANNEE($F$3);A77;1)));0)/169))

Et la formule suivante ne fonctionne plus ou je ne fais que remplacer le h60 (date de sortie) et f60 (date d'entrée) par les colonnes de mon tableau de façon à avoir l'ensemble de mon effectif :

=SOMMEPROD((Tableau3[DATE DE SORTIE]="")*(Tableau3[DATE DE SORTIE]>=FIN.MOIS(DATE(ANNEE($F$3);A76;1);0))+(Tableau3[DATE D''ENTREE]<=DATE(ANNEE($F$3);A76;JOUR(FIN.MOIS(DATE(ANNEE($F$3);A76;1);0))));(169/(DATE(ANNEE($F$3);A76;JOUR(FIN.MOIS(DATE(ANNEE($F$3);A76;1);0)))-DATE(ANNEE($F$3);A76;1))*MAX((MIN(Tableau3[DATE DE SORTIE];DATE(ANNEE($F$3);A76;JOUR(FIN.MOIS(DATE(ANNEE($F$3);A76;1);0))))-MAX(Tableau3[DATE DE NAISSANCE];DATE(ANNEE($F$3);A76;1)));0)/169))

Résultat : #VALEUR même en ayant remis mon tableau sous forme de plage avec à la place de Tableau3[DATE DE SORTIE] H5:H60.

Quelqu'un a t-il une idée.
Merci et bon week end
Pascalito67

Posté le 3 octobre 2014, 16:55
par pascalito67
Répondre
-1

"Cette formule marche bien mais pour une seule ligne de mon tableau " ... forcément, SOMMEPROD est à utiliser sur des plages de données et non pas une seule cellule en l'occurrence $H$60 ici !

Je propose de commencer par décomposer la formule, ensuite on y verra plus clair. Éventuellement envoie le fichier car en l'état c'est difficile de voir ce qui est variable et fixe !

Steelson Posté le 3 octobre 2014, 19:21
par Steelson
1

Bonjour,

Voilà le fichier dans son intégralité et le problème se situe dans l'effectif moyen mensuel de la 1ere feuille où il s'agit de déterminer l'effectif moyen mensuel en fonction des dates d'entrée sortie et du prorata par rapport à un temps plein.

http://cjoint.com/?0JghHt3pKqX
Janvier avec les plages de données ne fonctionne pas alors que février avec uniquement la ligne 60 fonctionne bien pour le test de la formule.

D'avance, merci.

Pascalito67

Posté le 6 octobre 2014, 07:37
par pascalito67
0

"Cette formule marche bien mais pour une seule ligne de mon tableau " : non cette formule marche pour toutes les lignes du tableau, aussi bien ou aussi mal que pour le mois de février. Comme elle ne s'applique qu'à la ligne 60 et que le salarié en question n'a été présent qu'en février, tu auras bien 0 pour les autres mois.

Le problème est que les lignes 5 à 59 ne sont jamais appelées par la formule !

Une solution consiste à détailler le calcul en colonne pour chaque mois. Je n'ai pas cherché à comprendre la formule, je l'ai juste transformée et appliquée au tableau suivant:

Tu mets les mois 1 à 12 en colonnes M à X ligne 4 (M4=1 jusque X4=12)

Tu recopies ensuite la formule suivante en M5 :

=SOMMEPROD(($H5="")*($H5>=FIN.MOIS(DATE(ANNEE($F$3);M$4;1);0))+($F5<=DATE(ANNEE($F$3);M$4;JOUR(FIN.MOIS(DATE(ANNEE($F$3);M$4;1);0))));(169/(DATE(ANNEE($F$3);M$4;JOUR(FIN.MOIS(DATE(ANNEE($F$3);M$4;1);0)))-DATE(ANNEE($F$3);M$4;1))*MAX((MIN($H5;DATE(ANNEE($F$3);M$4;JOUR(FIN.MOIS(DATE(ANNEE($F$3);M$4;1);0))))-MAX($F5;DATE(ANNEE($F$3);M$4;1)));0)/169))

Tu recopies ensuite la cellule M5 dans toute la plage M5:X60

Enfin, tu fais la somme par colonne, ce qui donne le résultat suivant :

47,0047,4147,0048,0047,6049,0050,0050,0053,0054,0053,0053,00

On peut aussi voir maintenant à condenser sans passer par un tableau, soit par SOMMEPROD, soit par une somme matricielle, mais c'est déjà assez complexe et on a ici au moins une solution qui fonctionne.

Steelson Posté le 7 octobre 2014, 01:19
par Steelson
0

Je te remercie. Oui du fait, tu as raison et cela marche en effet mais je voulais éviter de rajouter ce fameux tableau des 12 mois en utilisant les possibilités avancées de sommeprod.

Merci et bonne journée

Pascalito67

Posté le 7 octobre 2014, 08:46
par Pascalito67
0

Je pense qu'il faut aller dans ce cas au-delà de sommeprod et créer une fonction matricielle plus élaborée que sommeprod.

Steelson Posté le 7 octobre 2014, 08:58
par Steelson
0

Pour revenir à ta demande initiale d'avoir quelque chose de plus condensé, tape ceci en C76 ... voir ci-dessous avec

  • entrees = $F$5:$F$60
  • sorties = $H$5:$H$60
  • debut = DATE(ANNEE($F$3);A76;1)
  • fin = DATE(ANNEE($F$3);A76+1;0)

Valide en faisant Ctl+Maj+Entrée pour avoir une fonction matricielle (Excel mettra des {} ... ne pas les taper à la main).

Et ensuite recopie la formule vers le bas pour atteindre le mois de décembre.

Steelson Posté le 7 octobre 2014, 12:16
par Steelson
0

Merci encore mais cela ne marche pas. J'ai #VALEUR sur Janvier et des résultats faux jusqu'à décembre. J'ai bien utilisé Ctl+Maj+Entrée.

Ne te casse pas trop, j'ai utilisé un tableau sup de 12 mois avec toutes les personnes donc c'est OK.

A+ et bonne journée

Pascalito67

Posté le 8 octobre 2014, 14:57
par Pascalito67
0

Merci pour la remise en forme, j'utiliserai dorénavant le cadre.

@Pascalito : je viens de re-tester le code, cela fonctionne bien. Il faut la mettre en face de janvier et pas février.


Steelson Posté le 8 octobre 2014, 17:05
par Steelson
0

Voici une version plus lisible, donc plus maintenable :

=SOMME(taux*(entrees<=debut)*((sorties>=fin)+(sorties=0)))+SOMME(taux*(entrees>debut)*(entrees<fin)*(fin-entrees))/(fin-debut)+SOMME(taux*(sorties>debut)*(sorties<fin)*(sorties-debut))/(fin-debut)-SOMME(taux*(entrees>debut)*(entrees<fin)*(sorties>debut)*(sorties<fin))

Et plus raffinée car elle tient compte des emplois à temps partiel ! formule validée par Ctrl+Maj+Entrée pour être prise en compte comme formule matricielle.

Dans cette formule :

  1. debut est la date de début,  peut être modifiée pour tenir compte d'une variable comme le mois par exemple date(2014;mois;1)
  2. fin la date de fin de la période, peut être modifiée pour tenir compte d'une variable comme le mois par exemple date(2014;mois+1;0)
  3. taux, entrees, sorties sont 3 matrices (par exemple 3 colonnes) de même taille comportant respectivement le taux d'emploi (<100% pour les temps partiels), les dates d'entrée et les dates de sortie

Pour comprendre, il faut la décomposer :

  • Les présents en début et fin de période (soit la sortie est postérieure, soit elle n'est pas renseignée) : SOMME(taux*(entrees<=debut)*((sorties>=fin)+(sorties=0)))
  • Ceux qui sont rentrés en cours de période : SOMME(taux*(entrees>debut)*(entrees<fin)*(fin-entrees))/(fin-debut)
  • Ceux qui sont sortis en cours de période : SOMME(taux*(sorties>debut)*(sorties<fin)*(sorties-debut))/(fin-debut)
  • Moins une correction si la personne est rentrée puis sortie en cours de période pour ne pas compter 2 fois la période : SOMME(taux*(entrees>debut)*(entrees<fin)*(sorties>debut)*(sorties<fin))

Petite maquette : http://cjoint.com/?DJkmRqhyioS

Steelson Posté le 9 octobre 2014, 13:15
par Steelson
0

Merci beaucoup,

Je teste tout cela et surtout j'essaye de comprendre ta formule car ce sont mes premiers pas en calculs matriciels alors c'est pas glorieux mais cela m'intéresse énormément. Je te tiens au courant d'içi la semaine prochaine et au pire, je te renvoie mon fichier qui est en train d'évoluer grâce à toi, et tu vois où est-ce que j'en suis ?.

Encore merci

Pascalito67

Posté le 9 octobre 2014, 16:49
par pascalito67

Si vous n'êtes pas inscrit sur le site, vous pouvez poster librement (en tant qu'invité).
Pour cela, indiquez un pseudonyme (nom d'utilisateur) et une adresse email :