Pondérations de résultats

Question

Nouveau sur le forum, je n'arrive pas à venir à bout d'une fonction qui me permettrait le calcul d'un effectif de personnel sur un chantier de construction à un mois donné. Cela a pour but à terme d'obtenir la courbe d'effectif d'un chantier en fonction des différents corps d'états engagés. Le classeur est divisé en plusieurs onglets qui me permettent de définir l'effectif moyen de chaque corps d'état (dans le fichier joint, ils sont rassemblés sur la même feuille) et j'ai un planning donné que j'insère dans ma feuille de calcul. Vous pouvez voir dans le premier tableau le planning où les cases colorées correspondent à un mois travaillé. En dessous, un second planning a pour but de définir l'effectif correspondant pour le même mois, pour le même corps d'état. Par exemple, si une case est colorée dans le tableau supérieur, un effectif apparait dans la même case du tableau inférieur, ce grâce à l'appel de l'effectif moyen déterminer dans les différents onglets. Là où ça se complique (au cas où j'aurais réussi à être un minimum clair jusqu'ici), c'est qu'à chaque fois, l'effectif moyen est pondéré par un coefficient selon l'avancement de la tâche. Je m'explique. L'effectif de chaque tâche suit une courbe parabolique, qu'on peut diviser en 10 périodes. A chaque période on peut assigner un pourcentage d'effectif: pour la première 15%, la seconde 30%, la troisième 50%, de la 4ème à la 7ème 100%, puis on redescend sur les 3 dernières périodes : 50%, 30%, 15%. Le problème est donc d'assigner ces pourcentages à l'effectif à partir du premier tableau, en sachant que les périodes sont rarement divisibles par 10 (cf. nbre de mois) et que le démarrage de chaque période est différent. Le but est que je puisse moduler le planning et que l'effectif varie en fonction. Je ne sais pas si vous visualiser ce que je recherche, et encore moins si cela est possible de le faire de cette façon. J'ai essayé tout un assortiment de fonctions SI() mais le fait de bouger la date de démarrage, ou la longueur de la période, fausse totalement la pondération et donc le résultat. Peut être qu'il est nécessaire de passer par des tableaux intermédiaires, mais pour l'instant je n'ai pas trouvé d'issues. Je vous remercie de votre aide...

Amadéus · Answer

Avant de me lancer plus avant sur un sujet un peu long à résoudre, j'aimerai avoir ton avis sur l'approche et la méthode proposée à partir d'un exemple précis. Etude exemple cellule S45 Ici, nous ne parlons que du calcul déterminant l'effectif hommes, pas des formules qui feront que ce calcul sera affiché ou non, suivant les cellules car ce 2ème point ne pose pas de problème particulier. Par rapport aux 10 périodes de base, on peut avoir EX 1ère ligne 7 périodes Le rapport dans ce cas pour 1 période est 10/7 ou 10/durée_infra soit dans ce cas, presque 1,43 On cherche dans le dernier tableau (Pourcentages) la valeur supérieure à ce 1,43 qui est 2 et la valeur inférieure qui est 1. Deux formules matricielles (Validation avec les 3 touches Ctrl+Maj+Entrée) =MIN(SI(($A$71:$A$80&gt;(10/durée_infra*SOMME($F19:S19)));$A$71:$A$80)) dans l'exemple 2 =MAX(SI(($A$71:$A$80&lt;(10/durée_infra*SOMME($F19:S19)));$A$71:$A$80)) dans l'exemple 1 A ces deux valeurs correspondent des pourcentages (de mobilisation d'hommes) Avec RECHERCHEV, nous obtenons ces pourcentages et donc l'écart en % entre la valeur supérieure et la valeur inférieure Toujours en saisie matricielle =RECHERCHEV(MIN(SI(($A$71:$A$80&gt;(10/durée_infra*SOMME($F19:S19)));$A$71:$A$80));$A$71:$B$80;2;FAUX)-RECHERCHEV(MAX(SI(($A$71:$A$80&lt;(10/durée_infra*SOMME($F19:S19)));$A$71:$A$80));$A$71:$B$80;2;FAUX) qui dans l'exemple renvoie 0,15 soit 15% (en effet, 30%-15%=15%) Faute de pouvoir traduire en Excel la courbe "pseudo parabolique", l'erreur minimum pour une solution approchée est ici de répartir ce 15% de façon linéaire au dela du 1 de la valeur mini, soit pour 1,43 une répartition équivalente à: Le 1 (valeur inférieure) + (1,43-1)=0,43 ou 0,43% de 15% à rajouter au 15% correspondant à 1 dans le tableau. Notre nouvelle valeur est 15%+((1,43-1)*15%) soit ici 21,45% (de l'effectif hommes) qui donnerait 7,51 à arrondir bien sur pour obtenir un entier. Pour minimiser encore l'écart du au calcul linéaire, nous pourrions aussi partir de la valeur la plus proche d'une borne (de 1 à 10) et soit ajouter à la borne inférieure, soit déduire de la borne supérieure. Je me souviens d'avoir traité un sujet similaire ici https://forum.excel-pratique.com/post131677.html J'attends ton avis pour poursuivre Cordialement Edit 11/06 20heures Pas de problème particulier si le nombre de périodes est &lt;=10, par contre, une incohérence apparaît lorsque nous avons plus de 10 périodes car dans ces cas, il faut calculer en dessous du 1 servant de point de départ et cela devient impossible car l'étalement n'est plus connu. Il serait peut être intéressant de prévoir un tableau du type de celui période-Pourcentage pour 10, 20, 30 périodes pour finaliser les % de répartition. Cordialement...

Amadéus · Answer

Petit problème d'Internet qui se déconnecte à tout va. Finalement, j'ai abandonné les matricielles, un peu trop compliqué (surtout pour reprendre le raisonnement) et j'ai opté pour des formules plus simples et rajouté un certain nombre de tableaux nommés. Ceci offre l'avantage de modifier la pondération à ta convenance simplement en changeant les pourcentages. Les graphiques ne sont là que pour harmoniser les pondérations. Cordialement...