Microsoft 365 Ancienneté salariés & Anniversaires professionnels

[Dje_Ejd]

Bonjour à tous,

Nous souhaitons célébrer les anniversaires professionnels de nos employés selon des paliers déterminés (1 an / 3 ans / 5 ans / 10 ans / 15 ans / 20 ans).
A chaque palier, nous comptons allouer une somme pour acheter un cadeau spécifique. Mon besoin: En partant d'un listing d'employés (incluant date d'arrivée), je souhaite anticiper le nombre d'employés atteignant chaque palier d'anniversaire pro en fonction d'une date (dynamique) afin d'anticiper le total des coûts des cadeaux à prévoir dans le temps.

Ainsi, je souhaite savoir au "15 décembre 2021" ou bien au "26 juin 2022", combien d'employés auront eu à célébrer dans l'année 1 an d'ancienneté, 3 ans d'ancienneté, 5 ans, etc. Par contre je souhaite exclure tous ceux qui ne seront pas sur l'année du palier (ie. exclure les 6 ans d'XP pro, les 7 ans, etc.) afin de ne garder que ceux célébrant leur anniversaire pro en lien avec le palier concerné.

Contraintes:

• Au niveau format, j'ai besoin de garder la base de données (le listing employé) dans un onglet séparé afin de pouvoir le remplacer par un autre onglet avec le même nom dans le futur sans impacter les formules, et avoir le dashboard avec toutes les formules dans un deuxième onglet.
• Je souhaite travailler avec des formules et non du code.

Objectif: Sur la base d'une liste d'employés situé dans un différent onglet, je souhaite remplir le tableau suivant (et pouvoir tirer le nombre d'année ( n + x) en fonction de mes besoins de modélisation):

Paliers / Date cible​	15/12/2021 (date dynamique définie en B1)	15/12/2022 (n+1)	15/12/2023 (n+2)
1​	xx employés	yy employés	zz employés
3​	xx employés	yy employés	zz employés
5​	xx employés	yy employés	zz employés
10​	xx employés	yy employés	zz employés
15​	xx employés	yy employés	zz employés
20​	xx employés	yy employés	zz employés​
25​	xx employés	yy employés	zz employés​

A date :

• J'ai un onglet "Dashboard" qui regroupe le tableau de "répartition par pallier" (cf. ci-dessus) mais également la date dynamique à définir. La date dynamique est en "B1" de l'onglet "Dashboard". Et à titre d'exemple cette cellule B1 comporte la date 15/12/2021
• J'ai un onglet "BDD" qui comporte tous les employés avec leur date d'arrivée dans la société. La date d'arrivée est en colonne "F" de l'onglet "BDD"
• Dans mon onglet "BDD", j'ai fait apparaître en colonne "J" l'ancienneté théorique de chaque employé en fonction de la date dynamique déterminée en "B1" de mon onglet "Dashboard", soit 15/12/2021. Par exemple, pour l'employé situé sur la 5eme ligne de ma liste, j'utilise la formule =(Dashboard!B$1-$F5)/365. soit 15/12/2021- 04/01/2021. J'obtiens 0.95. J'affiche dans la colonne "K", précisément en "K5" l'ancienneté du même collaborateur un an après soit 1.95 le 15/12/2022, je répète en colonne L avec la cellule "L5" et j'obtiens 2.95 le 15/12/2023. Je répète l'opération autant de fois que d'années nécessaires à ma modélisation. J'ai donc un énorme tableau avec autant de ligne que d'employés, et autant de colonnes que de nombre d'années d'ancienneté que je souhaite afficher dans ma modélisation... Et ce tableau s'affiche dans l'onglet BDD, onglet que je souhaite pouvoir remplacer dans le temps!
• Dans mon onglet "Dashboard": J'ai réussi à compter les employés concernés par chaque palier, et exclure les autres, via la formule =NB.SI.ENS cependant je repose toute mes formules sur le gros tableau d'ancienneté généré dans mon onglet "BDD". Je pourrais déporter ce gros tableau dans mon onglet "Dashboard" mais ça fait beaucoup de "raw data" pour pas grand chose... Ma formule située dans l'onglet "Dashboard" pour calculer les employés qui vont célébrer leurs 5 ans est =NB.SI.ENS(BDD!J$5:J$172;">5";BDD!J$5:J$172;"<6"). Je tire horizontalement pour appeler les futures années n+1, n+2, n+x de ce même palier, soit =NB.SI.ENS(BDD!K$5:K$172;">5";BDD!K$5:K$172;"<6") et =NB.SI.ENS(BDD!L$5:L$172;">5";BDD!L$5:L$172;"<6") etc. Je change les valeurs pour chaque palier et j'ai terminé!

Je souhaite donc supprimer la génération de ce gros tableau intermédiaire dans mon onglet "BDD" et tout traiter directement dans mon tableau de "répartition" dans mon onglet "Dashboard".
Il me faut donc l'expertise de la communauté svp afin de supprimer mon tableau intermédiaire et ainsi remplacer le BDD!J$5:J$172 dans ma formule =NB.SI.ENS(BDD!J$5:J$172;">5";BDD!J$5:J$172;"<6" en puisant toutes les infos directement depuis l'année d'arrivée (colonne F de mon onglet BDD) et la date dynamique (cellule B1 de mon onglet Dashboard.

J'espère avoir été clair dans mes explications. Dites moi si jamais svp?
Je vous remercie du fond du coeur!!
Jerome

 

[Victor21]

Bonjour, @Dje_Ejd et bienvenue sur XLD.

Pensez-vous que chacun des intervenants prêts à vous apporter leur aide devraient passer des heures à tenter de reconstituer le fichier que vous décrivez si longuement, ou au contraire attendre que vous illustriez votre demande d'un extrait à la fois :
- représentatif (même organisation des lignes et des colonnes, mêmes libellés, mêmes noms de feuilles...)
- anonymisé (pas de données personnelles réelles tels nom, n° sécu, adresse ...)
- simplifié (une quinzaine de lignes reproduisant l'ensemble des différents cas envisageables)
qui leur permettrait de tester leurs propositions ?

 

[Dje_Ejd]

Bonjour, @Dje_Ejd et bienvenue sur XLD.

Pensez-vous que chacun des intervenants prêts à vous apporter leur aide devraient passer des heures à tenter de reconstituer le fichier que vous décrivez si longuement, ou au contraire attendre que vous illustriez votre demande d'un extrait à la fois :
- représentatif (même organisation des lignes et des colonnes, mêmes libellés, mêmes noms de feuilles...)
- anonymisé (pas de données personnelles réelles tels nom, n° sécu, adresse ...)
- simplifié (une quinzaine de lignes reproduisant l'ensemble des différents cas envisageables)
qui leur permettrait de tester leurs propositions ?

Bonjour @Victor21 merci beaucoup pour votre retour.
Je suis bien d'accord, mon post était très fastidieux à écrire cependant je n'ai pas trouvé la fonctionnalité pour joindre mon excel... D'où cette longue élancée lyrique!!!

Je vois maintenant "Joindre un fichier" dans ce post, et j'ai vu ce même bouton en bas de mon post, j'ai du rater quelque chose au moment de le soumettre. Un extrait est maintenant en pièce jointe!

Je vous remercie et reste à disposition ,
Jerome

 

[Dje_Ejd]

Si le calcul direct est trop compliqué, peut être que je finirai par créer un autre onglet afin d'y mettre ce gros tableau qui regroupe l'ancienneté de chaque staff dans le temps.
L'onglet "BDD" restant ainsi vierge de changements/modifications/formules

 

[Victor21]

Bonsoir.
J'ai lié les dates d'entrée de BDD2 à BDD, seules nécessaires pour ce calcul, et ai calculé la partie entière dans le tableau de BDD2 :

=ENT((C$4-$B5)/365.25)

recopiée sur l'ensemble du tableau
J'ai calculé le nombre de matricules concernés à partir de BDD2 avec la formule :

=NB.SI('BDD (2)'!C$5:C$62;Dashboard!$A5)

recopiée sur l'ensemble du premier tableau de Dashboard.
Les résultats dans le fichier joint semblent correspondre à ce que j'ai cru comprendre de votre demande.

 

[Dje_Ejd]

Bonsoir.
J'ai lié les dates d'entrée de BDD2 à BDD, seules nécessaires pour ce calcul, et ai calculé la partie entière dans le tableau de BDD2 :

=ENT((C$4-$B5)/365.25)

recopiée sur l'ensemble du tableau
J'ai calculé le nombre de matricules concernés à partir de BDD2 avec la formule :

=NB.SI('BDD (2)'!C$5:C$62;Dashboard!$A5)

recopiée sur l'ensemble du premier tableau de Dashboard.
Les résultats dans le fichier joint semblent correspondre à ce que j'ai cru comprendre de votre demande.

Merci beaucoup pour votre réactivité et vos explications!
Effectivement je vois que vous êtes passé par un deuxième onglet (BDD2). J'imagine que le recensement des différentes personnes aux anciennetés d'un même "palier" nécessite cette étape intermédiaire. A la base j'imaginais faire ce recensement directement depuis la date d'entrée mais cela n'est peut être pas possible.