Problème de SOMMEPROD

"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 !

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

"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 :

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.

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

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

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.

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

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.

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

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