Plan de formation

Plan de formation

Vous êtes en charge du plan de formation de l'entreprise, et disposez d'un budget de 130.000€. Certaines des formations ont déjà été acceptées lors d'une première réunion. Vous travaillez maintenant à la production d'indicateurs sur ce plan de formation avant de le terminer. Les différents indicateurs produits vous permettront de produire plusieurs scénarios en fonction de ce que vous souhaitez optimiser.

La feuille Excel suivante vous donne le plan de formation temporaire que vous allez étoffer :

SyntheseRH.xlsx [xls]

Question

0. Nommez les plages de cellule du tableau D:J

(Aide : https://support.office.com/fr-fr/article/D%C3%A9finir-et-utiliser-des-noms-dans-les-formules-4D0F13AC-53B7-422E-AFD2-ABD7FF379C64)

E2:E42 -> Sexe

F2:F42 -> Formation

etc.

Question

1. A l'aide d'une formule, renseignez les colonnes G, H etJ. Pour cela, vous disposez du tableau L1:O11. Dans le cas de la colonne J, le prix est fixé à 0 si la formation n'est pas acceptée.

Indice

Utiliser la fonction RechercheV()

Question

2. En utilisant une formule, renseigner le nombre de formations acceptées en B1.

Indice

Utiliser la fonction NB.SI : voir le cours de première année si besoin de se rafraîchir la mémoire.

Question

3. En utilisant une formule, renseigner le coût du plan de formation.

Indice

Utiliser la fonction Somme.SI : voir le cours de première année si besoin de se rafraîchir la mémoire.

Question

4. Renseigner B4 et B5 à l'aide de formules.

Indice

Utiliser les fonctions NB.SI.ENS et NB.SI : voir le cours de première année si besoin de se rafraîchir la mémoire.

Solution

Pour les femmes

=NB.SI.ENS(sexe; "F"; acceptee; "Oui")/NB.SI(acceptee;"Oui")

Question

5. En utilisant une formule, renseigner le nombre de formations différentes acceptées.

Indice

Utiliser NB.SI.ENS().

Solution

=(NB.SI.ENS(Formation;L2; acceptee;1) > 0) +(NB.SI.ENS(Formation;L3; acceptee;1) > 0)+(NB.SI.ENS(Formation;L4; acceptee;1) > 0)+(NB.SI.ENS(Formation;L5; acceptee;1) > 0)+(NB.SI.ENS(Formation;L6; acceptee;1) > 0)+(NB.SI.ENS(Formation;L7; acceptee;1) > 0)+(NB.SI.ENS(Formation;L8; acceptee;1) > 0)+(NB.SI.ENS(Formation;L9; acceptee;1) > 0)+(NB.SI.ENS(Formation;L10; acceptee;1) > 0)+(NB.SI.ENS(Formation;L11; acceptee;1) > 0)

Question

6. En utilisant les tableaux croisés dynamiques, répondez au questions suivantes :

- Le nombre de formation acceptée respecte-t-il la parité homme femme ?

- Quelles sont les trois formations les plus demandées ?

- Quels sont les organismes les plus sollicités ?

- Quel est le prix total des formations acceptées pour chaque organisme ?

- Quel est le nombre d'heures de formation qui sera fourni par organisme ?

- Quelle est la moyenne du nombre d'heures de formation par sexe ?

Question

7. Réaliser une mise en forme conditionnelle pour mettre en couleur les lignes du tableau D1:J42 si celles-ci concernent une formation délivrée par l'organisme 2.

Question

8. Réaliser une mise en forme conditionnelle en B4 et B5 pour les mettre en rouge si le rapport de parité est inférieur à 40/60, et en vert sinon (Exemple : 30/50 -> rouge, 45/55 -> vert).

Question

9. A l'aide du solveur, nous allons déterminer si les formations demandés par les employés 30 à 41 sont acceptées.

Nous envisageons plusieurs solutions mais qui sont régies par les contraintes suivantes :

- Le budget disponible ne doit pas être dépassé

- Le rapport de parité ne doit pas être inférieur à 40/60

Dans un premier scénario, nous cherchons à minimiser le coût du plan de formation mais le nombre de formations acceptés ne doit pas être inférieur à 35.

Dans un second scénario, nous cherchons à minimiser le coût du plan de formation mais le nombre de formations acceptés ne doit pas être inférieur à 35, et le nombre de formations différentes acceptées doit être égal à 10.

Indice

Contraintes à ne pas oublier : les valeurs de I31 à I42 sont binaires, soit 0, soit 1.