Pas de soucis, je prends note de tout ça.
En effet, c’est un fichier à destination professionnel qui va permettre comme je l’expliquai au départ d’établir le budget en masse salariale à partir des effectifs projetés. C’est donc une utilisation commune entre RH et managers. Le RH fourni les informations de base et le manager via les formulaires va compléter sa projection de l’année suivante ce qui permettra ensuite au RH d’établir le budget de masse salariale. Pour l’entité, je préfère rester anonyme.
Encore une fois, merci de votre aide, c’est tout à fait le résultat souhaité. Merci
Je me permets une dernière question :
Je voudrais faire la somme des « Open Position 2021 » dont les dates « Hire » sont comprises entre janvier et mars 2021, quelle est la formule adéquate?
Merci d’avance.
Bonjour
Tout d’abord merci pour les détails fournis concernant le devenir de ce projet
Ci-joint ta dernière version avec en colonne AM la somme souhaitée
A chaque ligne l’analyse des conditions avec le chiffre1 si elles sont réunies sinon le chiffre 0 et en fin ligne 36 le total
Pour des enregistrements supplémentaires il faut déplacer le total vers le bas en rajoutant dans celui-ci les lignes rajoutées et reporter dans toutes celles-ci sur cette colonne la formule d’analyse des conditions (copier/coller)
Si les conditions sont fluctuantes il est possible de mettre des variables (Status, mois début mois fin) dans des cellules avec liste déroulantes en tête d’onglet et que les formules y fassent référence
Ainsi des études différentes pourront être réalisées par simple manipulation de ces listes sans intervention au coeur même des formules
A toi de voir et dans cette option me fournir les cellules à utiliser
Ci-joint déjà une première approche
Testes et dis moi
Budget prévisionnel 4 (1).xlsm (131,7 Ko)
« En fait, j’aurais besoin de faire la somme de chaque « Status » par trimestre. Donc votre solution fonctionne mais si je veux le faire dans un autre onglet, est-ce possible ? »
Aucun souci pour utiliser une autre onglet mais il impératif d’analyser ligne par ligne si chacune réunit les conditions pour entrer dans le total
Donc il faut pour chaque statut autant de ligne que celles de l’onglet Budget
De plus la formule dois cibler une ligne de cet onglet et non la colonne donc on ne peut pas mettre ceci :
=SI(ET(MOIS(Budget!M:M)>=1;MOIS(Budget!M:M)<=3;ANNEE(Budget!M:M)>=2021;Budget!A:A=« Open Position 2021 »);1;0)
qui fait référence à des colonnes de l’onglet « Budget » (Budget!M:M))
Mais comme ceci :
=SI(ET(MOIS(Budget!M9)>=1;MOIS(Budget!M9)<=3;ANNEE(Budget!M9)>=2021;Budget!A9=« Open Position 2021 »);1;0)
Qui fais référence à une seule cellule (Budget!M9))*
Puis après la dernière ligne faire un total de tous les résultats pour avoir la somme
Ci-joint ton fichier paramétré ainsi onglet « Summary » pour le status « Open position Q1 - 2021 »
Les formules sont en lien avec l’onglet « Budget » ligne 9 à 29 et en lien avec l’onglet « Paramètres » cellules B29 et C29
Regardes et dis moi
Budget prévisionnel _ Headcount.xlsm (123,6 Ko)
Bonjour,
Merci de votre retour, on ne peut pas faire la somme de toutes les lignes dans une seule cellule ?
Car dans le fichier global il n’y aura pas qu’une 15aine de lignes, il y aura au moins 450 lignes. Donc, il faudrait plutôt faire directement la somme de toutes les lignes.
Dites moi si cela est possible.
Merci,
Bonsoir
Le pb c est qu il ne faut réaliser la somme que des cellules dont l enregistrement répond aux critères de date de la donnée Attrition et pour cela pas d autre choix que de le faire ligne par ligne et de mettre une somme de toutes celles qui répondent à ces conditions
On peut pour le contrôle utiliser un onglet spécifique créé pour le besoin puis réaliser la somme dans la cellule de l onglet « Summary »
Sinon il faut passer par un traitement VBA avec un déclenchement par bouton
A toi de voir
Bonjour,
En effet, si on pouvait passer par un code VBA ce serait pas mal car ça ne sera pas faisable d’avoir 450 lignes. Est-ce possible pour vous ?
En vous remerciant d’avance,
Bonjour
Ci-joint ma proposition
onglet « Summary » un bouton « Bilan » en regard des lignes à compléter colonne AU à AX
Cliques sur le bouton une boîte de dialogue demande à saisir l’année (ici 2021)
Puis le traitement sur la base de ces status exactement libellés ainsi onglet « Budget » colonne A :
Open position 2021
Exit 2021
Mobility of …
Mobility to …
et de la date saisie en colonne M (Hire) en rapport avec le paramétrage colonne B/C lignes 29 à 32 onglet « Paramètres »
alimente les lignes 7/10/13/16 colonne AU à AX onglet « Summary » en effectuant la somme des enregistrements répondant aux critères de date
Le traitement fonctionne testes à l’appuis mais est tributaire des libellés des status correctement saisis
Attention il y a un enregistrement incorrect pour cette donnée ligne 15 onglet « Budget » colonne A :
Mobility of…
il manque un espace après le of avant les 3 petits points
Ce détaille est important pour la prise en compte de l’enregistrement par le traitement
La saisie doit être sans faille pour un résultat attendu en rapport
Fais des essais et dis moi
Budget prévisionnel _ Headcount.xlsm (130,0 Ko)
Bonjour,
Merci, l’idée est super et ça correspond totalement à ce que j’aimerai avoir en résultat. Par contre, j’ai fait quelques tests en changeant les dates insérées et status, mais je n’ai pas l’impression que tout fonctionne. Est-ce que vous pouvez regarder à partir de l’exemple ci-après où j’ai modifié les dates pour que ça colle?
Merci d’avance,
Budget prévisionnel _ Headcount (2).xlsm (129,9 Ko)
Bonjour
Pour moi tout est correct
Dis moi ce qui ne conviens pas dans le résultat obtenu
Bonjour,
Dans l’exemple ci joint, j’ai par exemple :
- 3 Exit 2021 : dont 1 le 30/11/2021, 1 le 31/12/2021 et 1 le 29/05/2021 donc je devrais avoir dans l’onglet summary avoir un : 1 dans Exit Q4 - 2021 et un 1 dans Exit Q2 - 2021. Pour le 31/12/2021, on ne doit le compter en sortie que en 2022.
Or il y a seulement un 1 dans Exit Q1 - 2021.
D’ailleurs, j’ai revu les dates dans l’onglet Paramètres pour bien prendre en compte les bons créneaux surtout pour les sorties (Attriton).
Pour les Open positions 2021 et mobility of, c’est bon ça m’a l’air de bien fonctionner.
Pour les mobility to, j’ai des dates renseignées comme telles :
- 31/01/2021 donc je devrais avoir un 1 dans Mobility to Q1 - 2021
- 30/06/2021 donc je devrais avoir un 1 dans Mobility to Q3 - 2021
- 31/12/2021 donc rien en Q4 car impact 2022
Merci de votre retour encore une fois ! 
désolé je n’ai rien de tout ce que tu décris :
« 3 Exit 2021 : dont 1 le 30/11/2021, 1 le 31/12/2021 et 1 le 29/05/2021 »
je ne trouve que 2 Exit 2021 surligné en jaune :
si je filtre sur la colonne A colonne Status (Exit 2021) je n’ai bien que 2 enregistrements :
de plus les dates ne sont pas celles que tu mentionnes :
selon ta demande que tu as précédemment formulée :
« Je me permets une dernière question :
Je voudrais faire la somme des « Open Position 2021 » dont les dates « Hire » sont comprises entre janvier et mars 2021, quelle est la formule adéquate? »
c’est donc la colonne Hire qui est à considérer pour l’adéquation des dates soit colonne M
Pour les 2 Exit 2021 surlignées en jaune j’ai les dates en colonne M pour l’une 01/01/2017 pour l’autre 01/01/2012
Donc aucune ne rentre dans le champ des conditions c’est pour cela que tous les résultats obtenus onglet « Summary » ligne 10 colonne AU à AX sont à 0 :
or toi tu me dis ceci :
« Or il y a seulement un 1 dans Exit Q1 - 2021. »
Il y a un souci entre ton fichier et le mien que j’ai récupéré de ton dernier échange
Merci d’analyser et me dire on est pas du tout sur la même longueur d’onde
J’ai fais un essais en rajoutant 3 enregistrement Exit 2021 et en mettant les dates que tu as citées en colonne M Hire ainsi :
« 3 Exit 2021 : dont 1 le 30/11/2021, 1 le 31/12/2021 et 1 le 29/05/2021 »
j’ai obtenu ce résultat pour les Exit :
J’ai bien pour Exit Q2 le calcul attendu soit 1
Effectivement pour Exit Q4 j’ai obtenu 2 au lieu de 1 car ton paramétrage des dates onglet « Paramètres » en cellule C32 inclus le 31/12/2021
Si tu ne veux pas que cette date soit dans la condition il faut l’adapter pour l’exclure et mettre par exemple : 30/11/2021
Si des jours de décembre doivent être pris en compte pour 2021 il faut m’en donner le détail pour que je filtre en fonction les jours acceptés pour l’année traitée et ceux qui ne le sont pas
Je n’ai eu sur ce point aucune instruction
Merci de m’en dire plus sur ce sujet
Avec en C32 onglet Paramètres cette date : 30/11/2021 j’obtiens ton résultat attendu :
Je répète donc que le traitement pour l’instant pour moi est correct reste l’évolution pour le mois de décembre pour laquelle j’attends tes directives
C’est exactement le même avec seulement 2 enregistrement Exit 2021 :
Les dates en colonne M Hire ne sont pas celles que tu mentionnes :
01/01/2017 et 01/01/2012
au lieu de :
31/01/2021 30/06/2021 et 31/12/2021
As tu pris connaissance de ma réponse concernant le résultat de mon test sur 3 enregistrements Exit 2021 rajoutés avec en colonne M les dates que tu mentionnes ?
Pour moi le traitement répond exactement à ta demande suivante qui concerne les Open Position 2021 :
« Je me permets une dernière question :
Je voudrais faire la somme des « Open Position 2021 » dont les dates « Hire » sont comprises entre janvier et mars 2021, quelle est la formule adéquate? »
Ici on traite les Exit 2021 dans le même esprit en se basant sur les dates onglet « Paramètres » colonne B/C lignes 29 à 32
Seule le traitement du mois de Décembre est à préciser
Je répète donc que le traitement pour l’instant pour moi est correct reste l’évolution pour le mois de décembre pour laquelle j’attends tes directives
Dans l’attente
Oui, j’ai bien pris connaissance de vos retours.
En effet, j’ai précisé seulement de faire la somme des Open Position 2021, mais il s’agit de faire la somme de tous les autres « Status » c’est pourquoi la présentation dans l’onglet « Summary » à savoir :
- Open Position 2021 (donc ici la date hire est à prendre en compte)
- Exit 2021 (donc ici la date attrition est à prendre en compte)
- Mobility of (donc ici la date hire est à prendre en compte)
- Mobility to (donc ici la date attrition est à prendre en compte)
Comme indiqué, j’ai changé les dates de prise en compte dans l’onglet paramètres.
Pour le mois de décembre c’est ainsi pris en compte, c’est à dire on ne calcule pas si date au 31/12/2021. car si on a une date de fin au 31/12 c’est à dire que la personne sort des effectifs le lendemain donc impact 2022.
dans l’exemple du fichier je devrais avoir :
Pour les mobility to :
- 31/01/2021 donc je devrais avoir un 1 dans Mobility to Q1 - 2021
- 30/06/2021 donc je devrais avoir un 1 dans Mobility to Q3 - 2021
- 31/12/2021 donc rien en Q4 car impact 2022
Pour les Exit 2021 :
- 1 le 30/11/2021 donc 1 dans Exit Q4 - 2021
- 1 le 31/12/2021 --> impact 2022
- 1 le 29/05/2021 donc 1 dans Exit Q2 - 2021
Or il y a seulement un 1 dans Exit Q1 - 2021 quand je clique sur bilan à partir de ce dernier fichier…
Merci d’avance.
Budget prévisionnel _ Headcount (2).xlsm (129,9 Ko)
Bonjour
Pour obtenir ce résultat attendu :
Pour les Exit 2021 :
- 1 le 30/11/2021 donc 1 dans Exit Q4 - 2021
- 1 le 31/12/2021 --> impact 2022
- 1 le 29/05/2021 donc 1 dans Exit Q2 - 2021
Il faudrait 3 enregistrements Exit 2021 onglet « Budget » or il n’y en a que 2
Si je comprends ceci :
- Open Position 2021 (donc ici la date hire est à prendre en compte)
- Exit 2021 (donc ici la date attrition est à prendre en compte)
- Mobility of (donc ici la date hire est à prendre en compte)
- Mobility to (donc ici la date attrition est à prendre en compte)
Les dates à prendre en compte sont les dates Hire colonne M pour les status Open Position 2021 et Mobility of et les dates Attrition colonne N pour les status Exit 2021 et Mobility to
Je découvre ces impératifs que je n ai jamais eu jusqu à lors heureux de l apprendre
Merci de me confirmer que je suis bien en possession de toutes les exigences et que je les aient bien comprises
Je suis désolé il y a quelque chose qui ne colle pas entre tes indications et ce que je trouve dans ton fichier onglet « Budget »
Tu me dis :
Exit 2021 (donc ici la date attrition est à prendre en compte)
donc c’est la colonne « Attrition » colonne N qui est à prendre en compte
Dans cette colonne de ton fichier j’ai ces dates :
les 2 dates surlignées en jaunes sont les 2 seules dont les status colonne A correspondent à Exit 2021
Je n’en est pas d’autre
Je n’ai pas donc 3 enregistrement Exit 2021
de plus
Trouves mois parmi ces dates dans cette copie le 30/11/2021 que tu cites je ne la vois pas
Pour celle du 29/05/2021 elle est bien présente mais correspond à un enregistrement au status Exit 2020 et non Exit 2021 colonne A c’est la ligne 19
Je ne peux pas t’aider dans cet imbroglio incompréhensible
Il me faut la logique qui de tes données onglets « Budget » avec les conditions clairement établit permettent d’obtenir le résultat que tu attends
Déjà ceci :
- Open Position 2021 (donc ici la date hire est à prendre en compte)
- Exit 2021 (donc ici la date attrition est à prendre en compte)
- Mobility of (donc ici la date hire est à prendre en compte)
- Mobility to (donc ici la date attrition est à prendre en compte)
aurait dû dés le départ m’être fournis
Or dans ce domaine pour comprendre je n’ai eu que cela :
« Je me permets une dernière question :
Je voudrais faire la somme des « Open Position 2021 » dont les dates « Hire » sont comprises entre janvier et mars 2021, quelle est la formule adéquate? »
Bien loin donc de ces derniers impératifs que tu me fournis que seulement maintenant
Toujours est il que je n’ai pas encore compris ce qui amène ton résultat
Donnes moi enregistrement par enregistrement onglet « Budget » les explications appropriées qui conduisent à ton analyse :
Pour les mobility to :
- 31/01/2021 donc je devrais avoir un 1 dans Mobility to Q1 - 2021
- 30/06/2021 donc je devrais avoir un 1 dans Mobility to Q3 - 2021
- 31/12/2021 donc rien en Q4 car impact 2022
Je ne suis pas de ton avis pour cette conclusion :
30/06/2021 donc je devrais avoir un 1 dans Mobility to Q3 - 2021
Dans l’onglet « Paramètres » pour Q3 - de juillet à septembre j’ai les balises suivantes :
Date de début : 01/07/2021
Date de fin : 30/09/2021
la date 30/06/2021 ne peut pas en faire parti
Elle se trouve en Q2 - de avril à juin qui a ces balises suivantes :
Date de début : 01/04/2021
Date de fin : 30/06/2021
Donc le résultat doit être
30/06/2021 donc je devrais avoir un 1 dans Mobility to Q2 - 2021
Ou nous n’avons pas du tout la même logique et il me faut alors plus de précision pour expliquer ton résultat
Pour les Exit 2021 :
- 1 le 30/11/2021 donc 1 dans Exit Q4 - 2021
- 1 le 31/12/2021 --> impact 2022
- 1 le 29/05/2021 donc 1 dans Exit Q2 - 2021
Sinon je ne te serais d’aucune utilité cars à défaut de compréhension je ne peut réaliser un traitement adapté qui corresponde à ton attente
Bonjour,
Dans un précédent post, je disais que ce que je voulais faire c’est la somme de chaque status donc la somme des :
- Open Position 2021
- Exit 2021
- Mobility of
- Mobility to
Dans l’onglet « Paramètres », j’ai définis de nouvelle dates à tenir compte pour savoir si un « status » dont les dates « hires » et « attrition » sont comprises entre celles renseignées et donc si il s’agit de Q1 / Q2 / Q3 / Q4.
Je vais effectivement bien reposer mon souhait. Je n’ai pas été très claire et en suis désolée.
L’idée c’est de calculer les effectifs à partir du 31/12/2020 avec les entrées/sorties prévues.
Premièrement, il serait bien d’exclure les « type of contract » = Intérim + Prestataires.
Comme ça on a seulement les effectifs directs de l’entreprise c’est à dire CDI, CDD, Alternants.
De ce fait, il faudrait calculer toutes les entrées cad les Open Position 2021. En effet, si une ligne ayant un status « Open Position » et en fonction de la date renseignée dans la colonne « Hire » et de ce qui est renseigné dans l’onglet « Paramètres » mettre un 1 dans le trimestre concerné : Q1 / Q2 / Q3 ou Q3 (Q voulant dire quarter = trimestre en français)
Ensuite, si une Open Position est un CDD ou Alternant, il faudra en effet tenir compte de la date « Hire » dans le calcul de la somme des Open Position mais également tenir compte de la date « Attrition » s’il sort la même année dans les « Exit 2021 » du trimestre concerné (malgré que le « status » Exit n’apparaîtra pas puisqu’on l’aura simplement renseigné en Open Position)
Pour les status « Present 09/30/2020 » surligné en orange dans l’exemple. Il faudra tenir compte de la date renseignée dans « Attrition » pour les « type of contract » = CDD et Alternant.
dans l’exemple du fichier je devrais avoir :
Pour les mobility to :
- 31/01/2021 donc je devrais avoir un 1 dans Mobility to Q1 - 2021
- 30/06/2021 donc je devrais avoir un 1 dans Mobility to Q3 - 2021
- 31/12/2021 donc rien en Q4 car impact 2022
Pour les Exit 2021 :
- 1 le 30/11/2021 donc 1 dans Exit Q4 - 2021
- 1 le 31/12/2021 → impact 2022
- 1 le 29/05/2021 donc 1 dans Exit Q2 - 2021
- 1 le 31/07/2021 (en status « Present 30/09/20 ») donc 1 dans Exit Q3 - 2021
- 1 le 31/12/2020 (en status « Exit 2020 ») donc 1 dans Exit Q1 -2021 → en effet ici petite particularité, il faudrait prendre les Exit 2020 dont la date est seulement le 31/12/2020 pour en tenir compte en Exit Q1 - 2021 dans Summary
Pour les Open Position 2021, je devrais avoir dans Summary:
- 1 le 01/03/2021 et 1 le 05/01/2021 donc 2 dans Open position Q1 - 2021
- 1 le 01/08/2021 donc 1 dans Open Position Q3 - 2021
Or, en cliquant sur bilan j’ai 1 dans Q2 et 1 dans Q3
Voici le fichier à partir duquel j’ai réalisé mes tests.
Budget prévisionnel _ Headcount (2) (1).xlsm (130,2 Ko)
Merci bcp d’avance.
« Dans l’onglet « Paramètres », j’ai définis de nouvelle dates à tenir compte pour savoir si un « status » dont les dates « hires » et « attrition » sont comprises entre celles renseignées et donc si il s’agit de Q1 / Q2 / Q3 / Q4. »
Donc pour tous les status si une date Hires ou/et Attrition est saisi on doit les comparer aux dates dans l’onglet « Paramètres » ?
Pb si les 2 sont saisis dont l’une est dans les conditions et pas l’autre quelle conduite à tenir ?.
« Premièrement, il serait bien d’exclure les « type of contract » = Intérim + Prestataires. »
Pour tous les Status à analyser ? ( * Open Position 2021* Exit 2021* Mobility of* Mobility to)
« Ensuite, si une Open Position est un CDD ou Alternant, il faudra en effet tenir compte de la date « Hire » dans le calcul de la somme des Open Position mais également tenir compte de la date « Attrition » s’il sort la même année dans les « Exit 2021 » du trimestre concerné (malgré que le « status » Exit n’apparaîtra pas puisqu’on l’aura simplement renseigné en Open Position) »
Ce n’est pas claire du tout merci de donner des exemples (je ne suis pas dans le métier donc ce qui évident pour toi ne l’est pas du tout pour moi)
« Pour les status « Present 09/30/2020 » surligné en orange dans l’exemple. Il faudra tenir compte de la date renseignée dans « Attrition » pour les « type of contract » = CDD et Alternant. »
Je nage complètement je n’ai jamais vu ce type de statut merci de me donner un exemple
- 1 le 30/11/2021 donc 1 dans Exit Q4 - 2021 (Ok dans ce nouveau classeur dans le précédent il n’y était pas)
- 1 le 31/12/2021 --> impact 2022 (Ok)
- 1 le 29/05/2021 donc 1 dans Exit Q2 - 2021 (Ok dans ce nouveau classeur ligne 19 dans le précédent à cette ligne en colonne A il y avait Exit 2020 et non comme dans celui-ci Exit 2021)
- 1 le 31/07/2021 (en status « Present 30/09/20 ») donc 1 dans Exit Q3 - 2021 (Ce statut comme par enchantement est présent dans ce nouveau classeur rien dans les précédents ils sont automatiquement à mettre avec le traitement des Status Exit ???)
- 1 le 31/12/2020 (en status « Exit 2020 ») donc 1 dans Exit Q1 -2021 --> en effet ici petite particularité, il faudrait prendre les Exit 2020 dont la date est seulement le 31/12/2020 pour en tenir compte en Exit Q1 - 2021 dans Summary (ligne 10 Ok)
Merci pour tes compléments une simplification serait souhaitable car toutes ses particularités seront difficiles à mettre en musique
Déjà pas commode de les comprendre je ne parle pas donc de la codification
Pas simple ton affaire
Je te fais quand même cette proposition sans trop rien comprendre
Elle semble donner tes résultats
Pour les Statuts Present pour les Type of contract CDD / Fixed-term et Alternant / Apprentice j’ai rapporté leur résultat en les cumulant avec ceux des Exit (Exit Q1 Q2 Q3 Q4)
Je ne sais si cela correspond à tes attends car j’ai beaucoup de mal à les cerner
Fais des essais et dis moi