Prorata nombre de mission en fonction du temps de présence

Bonjour,

Je voudrais créer un outil sur excel et je ne sais pas du tout comment faire :

Je suis chargé de tenir à jour un tableau de régulation d’attributions de situations entre des agents. Disons qu’ils ont des missions qui arrivent au compte-goutte chaque semaine et que mon job consiste à les attribuer de façon équilibrée. Comme ça ça paraît simple, chacun son tour sauf que :

• Certains agents travaillent à temps partiel, par conséquent ils traitent moins de situations par an
• Certains agents arrivent en court d’année, mon tableau est sur une année civile, mais si un agent à temps plein qui était là au 1^er janvier a 14 situations sur l’année, l’agent à temps plein arrivé en mars si il en a 14 c’est que j’ai mal équilibré la charge

Mon tableau aujourd’hui il est simple, en colonnes les semaines, en lignes les agents, je note « 1 » pour chaque situation que j’attribue. Aujourd’hui je voudrais améliorer l’outil en aillant une aide pour être à l’équilibre, un code couleur par exemple, qui prendrait en compte que si un agent à 100% de temps de travail et arrivé avant le 1^er janvier a au jour J 10 situations, pour être à l’équilibre un agent à 80% arrivé en janvier devra en avoir 8.

En sachant que je ne sais pas quel va être le nombre de missions total, certaines semaines j’ai 10 situations à attribuer, d’autre je n’en ai que 3.

Est-ce que vous pourriez m’aider ?

J'espère avoir été clair...

Merci !

Bonjour pourriez-vous joindre un extrait de votre fichier anonymisé pour avoir une base de réflexion ?

Oui bien sûr, voilà mon tableau 2023

Merci, je regarde ce soir pour tenter de vous débloquer mais le fichier en l'état sera difficilement adaptable, il faudra ajouter des informations (quotité de temps, date d'entrée, de sortie, etc...).

Re-bonsoir,

Ci-joint le fichier avec les modifs.

• Pour la gestion de la quotité de temps de travail aucun problème.

• Pour la gestion des entrées/sorties là c'est plus délicat...J'ai réussi à le faire prendre en compte mais le suivi étant annuel, le chiffre à l'année ne sera pas bon à moins d'anticiper avec une certaines précisions les missions à venir...

J'ai plusieurs solutions possibles mais il s'agirait de passer par des macros pour une actualisation au fur et à mesure de l'écoulement de l'année afin d'avoir des chiffres à jour. C'est tout à fait réalisable mais le fichier sera plus long à concevoir, si vous voulez que l'on voit ça ensemble vous pouvez me contacter par MP

Bonne soirée,

Bonjour,

Merci beaucoup c'est déjà énorme !! Je vais regarder un peu, faire des test et je vous dis si besoin.

ce n'est pas aussi compliqué à mon avis.

Le jobs dans une feuille et puis le reste dans l'autre. On calcule le nombre de jours, chaque personne travaille pendant une semaine (G7:BI20), on peut aussi calculer le nombre de "jobs_jours" par semaine (ligne 4).

Alors le nombre que vous voulez, c'est le produit en colonne BJ et BK.

Je n'aime pas autant les couleurs ... .

Dans votre fichier vous ne prenez pas en compte la quotité temps de travail, il faut un suivi à la journée pour qu'à chaque mission affectée le taux de prise de mission de chaque agent soit recalculés sans être affecté par les entrées et sorties

Bonjour KiKi_Grd, je pense que je prends en compte la quotité temps de travail, même mieux que dans votre exemple, agent 5, 80% pendant un mois, cela n'est pas 7% pour toute l'année.

La formule utilise le "quotitié" de la colonne C et le nombre de jours ouvrés dans cette semaine&année et tient compte des jours fériés (qui sont pour le moment un petit peu bête, mais simplement comme exemple).

=$C7*MAX(0;NB.JOURS.OUVRES.INTL(MAX($D7;J$3;$D$2);MIN($E7;K$3-1;$E$2);;TBL_Féries))

Le nombre de jobs/missions par semaine est un petit peu plus difficile, mais avec un USF c'est fait. Plus tard on peut même ajouter un poids à une mission, certaines missions on besoin de plus de resources (?)

Puis on a 3 chiffres, le nombre de missions par semaine(1, ligne 4), le nombre de jours ouvrés d'une personne par semaine(2, lignes 7-20) et le nombre de jours ouvrés de l'équipe par semaine(3, ligne 5). (1)*(2)/(3) = prorata demandé. Je cumules cela dans la colonne BJ. BJ5 = total de missions pour l'année 2022 et elles sont divisées en BJ7:BJ20

Il y avait encore une faute dans mon fichier, une nouvelle version (sans couleurs pour le moment)