Excel Planning

Commençons par la 1ère feuille de notre classeur :

excel planning 1

Les colonnes début, fin, pause, et total sont au format heure. Le total pour chaque ligne est le temps qui s’écoule entre le début et la fin, la pause en moins. Ce qui donne =F2-E2-G2 pour le 1er juin 2017. Puis vous pouvez utiliser la poignée de recopie pour recopier cette formule pour toutes les dates.

Lorsque vous faites la somme automatique du total du mois de juin, le total qui s’affiche ne correspond pas au total que vous attendez. Au lieu de 39h, nous avons seulement 15h. Nous sommes donc en dessous de 24h. En effet, le format heure fonctionne comme une horloge : lorsque 24h sont passées, nous recommençons une nouvelle journée à 0h. Pour que l’on puisse dépasser 24h, nous devons changer le type de format heure. Il en existe plusieurs dans Excel.

Puis pour calculer le salaire horaire, nous devons passer par une étape intermédiaire : le total au centième. En effet : un salaire est en base décimale (nous allons jusqu’à 99€ avant de passer à 100€), alors que le format heure est en base hexadécimale (nous allons jusqu’à 59 min avant de passer à 1h). Le total au centième est le total d’heure non pas en hexadécimal, mais en décimal. Pour faire le calcul, pas besoin de formule. Récupérons la valeur du total et changeons seulement le format de la cellule. Nous pouvons maintenant calculer le salaire en fonction du tarif horaire : =H18*H19

excel planning 2

 

Même chose pour la feuille du mois de juillet.

excel planning 3

Pour la feuille Bilan, récupérons les salaires des mois de juin et juillet et faisons une somme automatique. Pour récupérer le salaire de juin, commencer par taper le signe égale “=” pour commencer la formule, puis cliquez sur la feuille juin pour sur le salaire de juin. Ne cliquez pas ensuite sur la feuille Bilan, appuyez seulement sur la touche Entrée pour valider votre formule, et Excel reviendra automatiquement sur la feuille Bilan.

excel planning 4

 

Terminons par la feuille Base. Elle contient ce qu’il possible d’afficher dans les colonnes Missions, lieux et Véhicules des mois de juin et juillet, Par exemple dans la feuille de juin, lorsque vous cliquez en B2, il y a déjà le texte “Formation” dans la cellule, mais il s’affiche aussi sur la droite de la cellule la petite flèche d’un menu déroulant. Cliquez dessus, et dans une liste déroulante s’affiche la liste des missions qui existent dans la feuille Base.

Nous pouvons créer ce genre de menu déroulant grâce à la fonctionnalité “Validation des données”. Les colonnes Mission, Lieu et Véhicule des feuilles juin et juillet sont déjà remplies, il nous faut donc supprimer ces valeurs mes aussi les menus déroulants. Sélectionnez ces 3 colonnes de A2 à D16, puis allez dans le menu “Données” puis “Validation des données”. Une fenêtre s’ouvre. Dans le menu déroulant “Autoriser”, choisissez “Tout”.

Maintenant, nous allons remettre les menus déroulants les colonnes Mission, lieu et Véhicule. Sélectionnez ces 3 colonnes de A2 à D16, puis retournez dans le menu “Données” puis “Validation des Données”. Dans le menu déroulant “Autoriser” choisissez “Liste”. Puis dans la source, nous devons écrire la formule qui permet de récupérer les données de la feuille base. Si vous écrivez la formule =base!A2:A7 vous aurez un message d’erreur. Pour éviter ce problème, nous allons donner un nom à cette plage de données. Cependant nous allons dans un 1er temps supprimer les noms qui existent déjà. Allez dans le menu Formule puis Gestionnaire de noms. Sélectionnez tous les noms puis cliquez sur Supprimer. Maintenant nous allons recréer ces noms. Fermez cette fenêtre. Allez dans la feuille base et sélectionnez les missions de A2 à A7. Juste au-dessus de la colonne A et à gauche de fx, il y a écrit A2, c’est le nom de la 1ère cellule sélectionnée. Cliquez dessus et supprimer A2 pour écrire “mission” à la place (attention la casse minuscule majuscule compte). Validez en appuyant sur Entrée. maintenant vérifiez si le nom “mission” est bien dans le gestionnaire des noms.

Vous allez maintenant pouvoir utiliser ce nom pour la validation des données des feuilles juin et juillet. sélectionnez à nouveau les colonnes mission, lieu, véhicule de la feuille juin de B2 à D16. Puis cliquez sur le menu Données, puis Validation des données, et choisissez Liste dans le menu déroulant Autoriser. Dans la ligne source, vous pouvez ajouter =mission

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>