Editions ENI Livres | Vidéos | e-Formations
Tableau de bord avancé suivi des heures
Présentation
Les données sources
Dirigeant d’une petite entreprise de construction de maisons à ossatures bois, vous souhaitez suivre les heures effectuées par vos employés. Trois catégories de personnels travaillent dans l’entreprise : des ouvriers, des charpentiers et éventuellement des intérimaires.
Dans la feuille Heures du classeur SuiviChantiers.xlsx, votre assistante mémorise quotidiennement les heures effectuées par chaque personne.
Vous souhaitez connaître en temps réel :
-
le nombre d’heures mensuelles travaillées sur chaque chantier,
-
le nombre d’heures d’intempéries mensuelles,
-
le nombre d’heures effectuées par chaque catégorie d’employé.
Nombre d’heures mensuelles travaillées sur chaque chantier
La feuille StatMens contient des tableaux dans lesquels les formules sont à insérer.
Chaque jour, notre plage de données source va voir sa taille augmenter par l’ajout de nouvelles lignes. Il est judicieux dans ce cas d’utiliser, pour les calculs, des plages dynamiques dont les tailles vont correspondre au nombre exact de lignes saisies. Ceci évite de sélectionner dans les formules des plages beaucoup plus importantes afin d’être certain que les données ajoutées seront prises en compte par les formules.
La technique de définition de plages dynamiques consiste à utiliser la fonction de calcul DECALER pour définir une plage nommée.
La syntaxe de la fonction est détaillée ci-dessous.
=DECALER(Cellule;Nb Li;Nb Col;Hauteur;Largeur)
Cellule est la plage à partir de laquelle opérer le décalage.
Nb Li correspond au nombre de lignes vers le bas (si positif) ou vers le haut (si négatif) dont la cellule doit être...
Le tableau de bord
Présentation
Notre objectif est de représenter graphiquement :
-
Le cumul des heures travaillées par rapport aux heures prévues, le choix du chantier représenté doit pouvoir être effectué à l’aide d’une liste déroulante.
-
La répartition des heures entre les différentes catégories d’employés.
Le tableau de bord devra en outre afficher le nombre total d’heures travaillées ainsi que le taux des heures travaillées par rapport aux heures prévues.
Enfin, deux "boutons" seront à insérer afin de permettre la navigation entre les feuilles du classeur.
Nous considérerons ici que l’entreprise est fermée en août, les heures prévisionnelles sont donc à répartir sur 11 mois.
Tableaux préparatoires
Avant de concevoir les graphiques, les tableaux vont devoir être préparés.
La feuille TB contient déjà la liste déroulante (onglet Données - Validation des données) en C5 et la structure des tableaux sources des graphiques.
Les formules :
La formule de la cellule E28 doit afficher le total des heures prévues pour le chantier sélectionné en C5.
Cliquez en E28.
Saisissez la formule : =INDEX(StatMens!$C$4:$D$4;1;EQUIV(TB!C5;StatMens!$C$9:$D$9;0))
Saisissez en F28 :=SI(E28=0;0;C42/E28) et formatez la cellule avec le format Pourcentage)
HEURES TRAVAILLÉES :
|
C30 |
{=SOMME((MOIS(Dates)=EQUIV($B30;$B$30:$B$41;0))*(Chantiers=C$5)*(NBHTrav))} (formule à valider par CtrlShiftEntrée) |
CUMUL RÉEL :
|
D30 |
=SI(C30=0;NA();SOMME($C$30:C30)) La fonction NA() va permettre de ne pas afficher dans le graphique les valeurs relatives aux mois non encore écoulés. |
HEURES PRÉVISIONNELLES :...
Navigation dans les feuilles
Les boutons de la feuille TB
Dans l’onglet Insertion - groupe Illustrations, déroulez le menu Formes puis cliquez sur la forme Rectangle à coins arrondis.
Tracez un rectangle au niveau des lignes 1 et 2.
Dans l’onglet Format de la forme - groupe Styles de formes, déroulez le menu Autres puis sélectionnez Effet intense - Rouge, 2 accentué.
Saisissez le texte STATISTIQUES MENSUELLES puis mettez-le en forme.
Effectuez un clic droit sur la forme, sélectionnez Lien.
Dans la zone Lier à sélectionnez Emplacement dans ce document, cliquez sur le nom de feuille StatMens puis OK.
Reproduisez ces opérations pour le deuxième bouton en effectuant un lien vers la feuille Heures.
Le bouton de la feuille StatMens
Chacune des deux feuilles StatMens et Heures doit contenir un lien vers la feuille TB (Tableau de bord). Afin de ne pas surcharger ces feuilles, le lien va être présenté sous forme d’une flèche orientée vers la gauche.
Dans l’onglet Insertion - groupe Illustrations, déroulez le menu Formes puis, dans Flèches pleines, cliquez sur la forme Flèche gauche.
Tracez une petite flèche au niveau de la cellule B1.
Dans l’onglet Format de la forme- groupe Styles de formes, déroulez le menu Autres puis sélectionnez Effet intense - Vert d’eau, 5 accentué.
Effectuez un clic droit sur la flèche, sélectionnez Lien.
Dans la zone Lier à sélectionnez Emplacement dans ce document, cliquez sur le nom de feuille TB puis OK.
Pour terminer, copiez-collez cette flèche dans la feuille Heures.
Les premières lignes de la feuille...
Protection des tableaux
Intérêt
Comme évoqué dans le chapitre Définir le tableau de bord, la protection des tableaux Excel peut être effectuée à deux niveaux :
-
Protection des éléments clés des feuilles de calcul : titres, formules, graphiques, mises en forme, etc. pour empêcher toute modification non souhaitée.
-
Protection du classeur par mot de passe pour empêcher son ouverture par toute personne non autorisée.
La construction de vos tableaux de bord va vous demander un certain investissement en temps. Ce travail ne doit pas être annihilé par de mauvaises manipulations. En effet, tant qu’une protection n’a pas été mise en œuvre, il est toujours possible de saisir une donnée à la place d’une formule ou bien d’effacer une formule.
Protéger vos feuilles de calcul consiste à définir quelles actions sont autorisées à effectuer par un utilisateur.
Initialement, toutes les cellules d’une feuille sont verrouillées, cependant cette protection n’est active que lorsque la feuille est protégée. Avant toute protection, il faut donc déverrouiller toutes les zones de saisie de données.
La démarche de protection d’une feuille de calcul est donc la suivante :
-
Déverrouiller toutes les plages de saisie.
-
Protéger la feuille.
Protéger la feuille TB
Positionnez-vous dans la feuille TB.
La seule cellule modifiable dans cette feuille est la cellule contenant la liste déroulante des chantiers, positionnez-vous donc en C5.
Dans l’onglet Accueil - groupe Nombre, ouvrez le menu Nombre, dans l’onglet Protection, décochez l’option Verrouillée puis validez.
Dans l’onglet Révision - groupe Protéger, cliquez sur Protéger...
