Tableaux d’aide à la Planification de Chantiers de Nettoyage

SOMMAIRE

I. INTRODUCTION .........................................................................3

II. CREATION D’UN NOUVEAU FICHIER ..........................................3

A. Copie ou modification du tableau des chantiers ................................. 3 1. Création d’un tableau des chantiers à partir d’un fichier vierge : ........................................3 2. Création d’un tableau des chantiers à partir d’un fichier existant : .....................................7

B. Modifications sur le planning annuel et vérifications : ...................... 8 1. Saisie dans la feuille Planning : ...........................................................................................8 2. Utiliser le mode plan (à partir d’Excel 2002)....................................................................10 3. Vérification des fréquences :..............................................................................................12 4. Impression du planning par quinzaine ...............................................................................13

III. CREATION D’UNE NOUVELLE ANNEE.......................................14

A. La saisie de la première semaine ...................................................... 14

B. Les modifications sur le tableau de respect des fréquences ............. 15

IV. LA CONCEPTION DU FICHIER....................................................16

A. Le tableau des chantiers .................................................................... 16 1. Saisie et mise en forme ......................................................................................................16 2. Création des noms..............................................................................................................17 3. Création des formules ........................................................................................................18 4. Mise en forme conditionnelle et règles de saisie...............................................................20 5. Protection de la feuille .......................................................................................................22

B. Le planning annuel ............................................................................ 23 1. Saisie et Mise en forme......................................................................................................23 2. Création de formules et listes déroulantes .........................................................................24 3. Mise en forme conditionnelle et règles de saisie...............................................................27 4. Création du Mode Plan ......................................................................................................28 5. Protection de cellules .........................................................................................................29

C. Le tableau de contrôle ....................................................................... 29 1. Saisie et Mise en forme......................................................................................................29 2. Création de formules..........................................................................................................29 3. Mise en forme conditionnelle et règles de saisie...............................................................30 4. Protection de cellules .........................................................................................................31

D. Améliorations possibles .................................................................... 31 1. Recherche automatique dans les zones de liste déroulante................................................31 2. Recherche du kilométrage entre deux chantiers ................................................................32

I. INTRODUCTION Ce fichier a pour but d’aider à la planification de chantiers de nettoyage. Il est composé de trois tableaux :

� Le premier représente un planning annuel découpé en 27 pages de Quinze jours chacun. � Le deuxième fait office de tableau de contrôle concernant le suivi mensuel des interventions. � Le troisième est un tableau récapitulatif des chantiers avec leur durée et leurs fréquences

mensuelles. Ce fichier a pu être créé grâce aux informations apportées par M. Robert PEREZ et M. Lionel PEREZ. Nous allons maintenant voir comment utiliser ce fichier selon trois opérations distinctes.

� La création d’un nouveau planning pour un salarié. � La mise à jour d’un planning au passage de la nouvelle année. � Et enfin les explications sur les formules qui constituent ces différents tableaux.

Bonne lecture !

II. CREATION D ’UN NOUVEAU FICHIER

A. Copie ou modification du tableau des chantiers

1. Création d’un tableau des chantiers à partir d’u n fichier vierge : Tout d’abord il va falloir saisir des informations dans le tableau des chantiers. Double-cliquez sur le fichier Planning et suivi des chantiers.xlt

Une fois le fichier ouvert, accepter l’activation des macros afin que les protections soient effectives, sélectionnez alors la feuille tablo_chantiers

Nota : Lors de l’ouverture du fichier, vous aurez quelques messages d’erreurs concernant la sécurité si vous disposez d’une version d’Excel inférieure à celle de 2002.

Une fois sur cette feuille, vous pouvez

1. Saisir le nom du chantier 2. Ses observations 3. Les durées du chantier en hiver et en été 4. Ses fréquences mensuelles et bimensuelles pour les mois de Juin et Septembre

Il n’est prévu pour l’instant que 22 chantiers. Si nécessaire, vous pouvez créer des chantiers supplémentaires. Pour cela fait, cliquez avec le bouton droit de la souris sur la dernière ligne du tableau puis sur Insertion. Vous pouvez de cette manière insérer autant de lignes que vous le désirez dans ce tableau. Il faudra respecter le même mode opératoire sur la feuille RESPECT DES FREQUENCES car les deux tableaux sont liés et doivent contenir le même nombre de lignes.

1 2 3 3 4 4 4 4 4

Une fois cela terminée, vous devez vérifier si les noms prédéfinis englobent les données que vous avez saisies ou non. En effet de nombreuses formules et listes déroulantes font appel à ces noms. Pour vérifier cela, cliquez sur la liste déroulante comme sur l’image sur le côté et sélectionnez le nom tablo_chantiers.

Normalement,

l’ensemble des chantiers doit être compris dans cette zone. Si ce n’est pas le cas, il faut

faire correspondre le nom à la zone de cellules adéquate.

Pour pouvoir faire cette manipulation, nous devons d’abord enlever la protection de la feuille. Cliquez alors sur Outils/Protection/Ôter la protection de la feuille

Une fois cela fait, cliquez sur le Menu Insertion/Nom/Définir

Dans la nouvelle fenêtre, faites défilez les noms jusqu’à trouver celui qui vous intéresse puis sélectionnez le. Vous obtiendrez alors ses références dans la zone Fait référence à : . La formule qui apparaît est découpée en deux arguments : Le premier correspond au nom de la feuille et se termine par un point d’exclamation (Ex : tablo_chantiers !) Le deuxième correspond aux deux cellules définissant notre zone, la première étant en haut à gauche et la seconde en bas à droite (Ex : $A$2 :$S$23) Vous pouvez donc modifier la définition du nom en modifiant les cellules auxquelles il fait référence. Par exemple, si vous avez créer 24 chantiers, la dernière cellule sera donc $S$25. Une fois la modification faite, il ne vous reste plus qu’à cliquer sur ajouter puis sur OK. Il faudra procéder de la même manière avec le nom chantiers qui définit la liste des différents

chantiers. Une fois tout cela fait il ne vous reste plus qu’à protéger la feuille en utilisant le menu Outils/Protection/Protéger la feuille. Une fois toutes ces manipulations faites, il ne vous reste plus qu’à saisir les planifications dans la feuille planning (Cf. chapitre B) N’oubliez pas de sauvegardez votre fichier.

2. Création d’un tableau des chantiers à partir d’u n fichier existant :

Ouvrez le fichier contenant la tableau contenant vos chantiers déjà saisis. Dans notre cas, ce sera le fichier DEBIAIS 144 BANYULS PLANNING.xls.

Sélectionnez la feuille correspondante, ici la Feuill1. Puis sélectionnez les lignes correspondantes aux chantiers.

Cliquez alors avec le bouton droit de la souris sur une des lignes et cliquez sur Copier

Allez alors sur le fichier Planning et suivi des chantiers que vous avez auparavant ouvert. Sélectionnez la feuille tablo_chantiers et cliquez avec le bouton droit de la souris sur la première cellule vide située en haut à droite du tableau (soit la cellule A2). Cliquez alors sur coller. Si votre tableau initial contient plus de 22 chantiers, il faudra au préalable insérer des lignes de la même manière que dans précédemment. Une fois toutes ces manipulations faites, il ne vous reste plus qu’à saisir les planifications dans la feuille planning (Cf. chapitre B) N’oubliez pas de sauvegardez votre fichier.

B. Modifications sur le planning annuel et vérifications : Dans cette partie nous allons observer comment :

� Remplir la feuille planning � Imprimer les plannings par quinzaine � Utiliser le mode plan pour se déplacer dans l’année � Vérifier les fréquences des interventions

1. Saisie dans la feuille Planning : Tout d’abord, sélectionnez la feuille Planning. Dans la cellule F1 saisissez la nom du salarié.

Pour planifier des chantiers, il suffit de cliquer sur la première cellule vide d’une matinée ou d’une après-midi, il faut alors sélectionnez le nom du chantier désiré dans la liste déroulante. Cette dernière se place toujours par défaut sur les premières lignes vides, ce qui vous place souvent en fin de liste.

Une fois que vous avez fini une journée ou demi-journée type, il est possible de la dupliquer, pour cela il suffit de sélectionner la journée type est de cliquer sur une des cellules la composant avec le bouton droit de la souris. Cliquez alors sur Copier.

Placez-vous sur la première cellule d’une journée encore vierge ou vous pouvez coller la journée désirée. Attention, il n’est pas possible de dupliquer des semaines entières car les cellules contenant des formules ont été protégées. Une fois votre année faite, n’oubliez de sauvegarder votre fichier.

2. Utiliser le mode plan (à partir d’Excel 2002)

Dans le bord de la feuille PLANNING, vous avez pu remarquer la présence de boutons et

ainsi que . Ceux-ci servent à masquer ou pas le détails des semaines (dans la barre verticale) ou des journées (dans la barre horizontale). Ainsi si vous ne désirer avoir qu’une vision hebdomadaire des horaires, il suffit de cliquer sur le bouton

situé en haut de la barre verticale.

Vous obtenez alors la vision suivante qui permet de se déplacer plus facilement sur l’année. De plus si vous êtes intéressé par une semaine en particulier, il ne vous reste plus qu’à cliquez sur le bouton

lui faisant face avant de pouvoir en observer le détail.

Vous obtenez alors un écran similaire à celui-ci dessous

Vous pouvez procéder de la même manière avec les journées. Dans le cas d’un chantier à planifier tous les deuxième mardi du mois par exemple. Il suffit d’abord de cliquer sur

le bouton placé à l’extrême gauche de la barre horizontale. Vous obtenez alors la vue suivante si vous désirez voir le détail de tous les mardi.

3. Vérification des fréquences : Une fois votre planning terminé, si vous désirez vérifier que le nombre d’intervention planifié correspond à celui prévu, cliquez sur la feuille RESPECT DES FREQUENCES. Une fois sur ce tableau, vous pouvez vérifier grâce à un code de couleur si vous avez respecté ou non vos engagements. Ainsi, si le nombre d’intervention planifié et prévu sont identiques la cellule correspondante apparaîtra en vert. Si la différence entre le nombre d’intervention planifié et prévu ne dépasse pas une demi intervention (en positif et en négatif) la cellule correspondante apparaîtra en orange. Si la différence entre le nombre d’intervention planifié et prévu dépasse une demi intervention (en positif et en négatif) la cellule correspondante apparaîtra en rouge Si jamais votre cellule est remplie de dièses, il suffit d’adapter la largeur de la colonne afin de voir le contenu de la cellule.. Le nombre de la dernière colonne correspond à la différence entre le nombre annuel d’intervention planifié et prévu.

4. Impression du planning par quinzaine Une fois votre planification terminée et vérifiée, vous pouvez l’imprimer. Nous allons nous assurer néanmoins que l’impression par quinzaine prévu fonctionne bien. Pour cela cliquez sur le bouton d’Aperçu avant impression L’écran suivant devrait apparaître

Comme vous pouvez le remarquer, les premières lignes se répètent sur toutes les feuilles et vous devez disposer de 27 feuilles au total. Si ce n’est pas le cas, il faut vérifier la mise en page en cliquant sur le bouton Page de l’écran ci-dessus ou en en allant sur le menu Fichier/Mise en page Il faut alors soit moduler le nombre de pages que vous désirez en hauteur, soit jouer sur les hauteurs de lignes ou les largeurs de colonnes si jamais vous les avez modifié.

III. CREATION D ’UNE NOUVELLE ANNEE Pour chaque nouvelle année, il est nécessaire de modifier les feuilles PLANNING et RESPECT DES FREQUENCES.

A. La saisie de la première semaine Une fois le fichier ouvert, dans la feuille PLANNING, sélectionnez la cellule contenant la première date et modifier la. Faites de même pour les autres dates de cette première semaine. Vous devez alors obtenir un tableau similaire à celui ci-dessus, les autres dates s’adaptent automatiquement.

B. Les modifications sur le tableau de respect des fréquences Dans la feuille de RESPECT DES FREQUENCES, il va falloir modifier les formules permettant le décompte mensuel. Sélectionnez la feuille correspondante et placez-vous dans la cellule C3. Vous remarquez alors la formule qui compte le nombre de fois ou votre chantier est effectué sur le mois. Pour la modifier et l’adapter à la nouvelle année, il faut supprimer la partie de la formule située après le deuxième point virgule, soit toutes les fonctions NB.SI. Pour cela, ôter la protection de la feuille, puis cliquer sur la cellule C3. Placez vous dans la barre de formule et sélectionnez, tout ce qu’il va falloir supprimer.

Supprimer le, vous devez retrouver une formule simplifiée telle que ci-dessous. Pour insérer la fonction NB.SI, cliquez sur la liste déroulante située à gauche de la barre de formule. Si celle-ci n’apparaît pas dans la liste des dernières fonctions utilisées, cliquez sur Autres fonctions … . La fenêtre suivante apparaît, il ne vous reste plus qu’à sélectionner la fonction NB.SI désirée.

Lors de la saisie de cette formule, vous devrez lui fournir deux informations : 1° La zone où se situe l’information que vous recherchez, ici, sélectionnez la ou les semaines dans la feuille PLANNING. 2° Le nom du chantier recherché situé normalement dans la cellule B3. Répétez l’insertion de la fonction NB.SI autant de fois que nécessaire pour sélectionner l’ensemble des jours du mois. NB. : Ne pas oubliez que la fonction NB.SI ne fonctionne pas avec des zones disjointes. Une fois la formule achevée, recopiez-la sur la colonne. Il ne vous reste plus qu’à répéter ces opérations mois par mois.

IV. LA CONCEPTION DU FICHIER Maintenant que nous avons vu comment utiliser ces différents tableaux, nous allons apprendre à les concevoir. Avant leur création, nous allons nommer les trois feuilles de ce fichier :

� La feuille PLANNING � La feuille RESPECT DE FREQUENCES � La feuille tableau des chantiers

A. Le tableau des chantiers

1. Saisie et mise en forme Dans ce tableau, nous allons saisir l’ensemble des informations vous intéressant sur les chantiers d’une personne ou d’un groupe. Une fois cela fait, nous allons donner des noms à des zones de ce tableau, de manière à ce que cela nous serve pour les deux autres tableaux que nous créerons par la suite. Et pour finir, bous créerons quelques formules qui nous permettront de calculer des horaires mensuels théoriques et des nombres d’interventions annuels.

Pour commencer, saisissez les intitulés désirés en première ligne.

Sélectionnez les cellules saisies, puis cliquez sur le menu Format/Cellule et choisissez votre mise en forme. Dans notre exemple, l’ensemble des cellules sélectionnées est mis en gras sur fond gris avec un alignement centré horizontalement et verticalement.

De plus pour les cellules contenant le nom des mois, nous avons choisi une orientation à 45 degrés pour une meilleure visibilité. N’oubliez pas d’adapter la largeur des colonnes au contenu. De la même manière, pensez à quadriller l’ensemble des cellules qui constitueront votre tableau. Dans notre exemple, nous avons prévu 50 chantiers donc 50 lignes. Par commodité, nous avons aussi figé les volets en se basant sur la cellule E2, afin de visualiser en permanence le nom du chantier, ses observations et durées.

2. Création des noms

Nous allons maintenant donner des noms aux différentes zones de ce tableau. Sélectionnez l’ensemble des cellules qui contiendront le nom des chantiers, de la cellule A2 jusqu’à la cellule A51 dans notre cas.

Pour nommer cette zone, nous allons précéder comme suit : En cliquant sur le menu Insertion/Nom/définir La fenêtre suivante apparaît, et il ne vous reste plus

qu’à saisir le nom de la zone. Remarquez que le nom ne peut contenir des espaces.

Nous allons répéter cette opération sur les autres noms nécessaires à nos formules futures. A savoir la durée été, la durée hiver, la fréquence janvier, février, … , décembre et le tableau global nommé tableau_chantiers. NB : Dans notre exemple, vous remarquerez que les mois de Juin et Septembre on été découpés en deux quinzaines. Cela a nécessité deux noms pour chacun de ces mois.

3. Création des formules Nous allons commencer par calculer le nombre d’interventions annuelle chantier par chantier. Pour cela sélectionnez la cellule S2 et cliquez sur le bouton somme automatique.

Une fois cela fait sélectionnez la zone que vous voulez additionner. Vous devez visualiser un écran similaire à celui ci-dessous.

Il faut maintenant recopier cette formule sur l’ensemble des chantiers. Placez-vous sur la cellule S2, saisissez la poignée de recopie (le curseur de la souris devient une croix noire lorsque vous vous positionnez dessus) et descendez jusqu’à la dernière cellule de votre tableau. Excel adaptera la formule de ligne en ligne.

La deuxième formule va nous permettre de calculer le temps mensuel théorique moyen. Pour cela nous allons multiplier la durée d’un chantier avec sa fréquence mensuelle. Pour éviter de faire cela cellule par cellule, nous allons utiliser la fonction SOMMEPROD. Pour commencer, placer-vous

dans la cellule E53, cliquez alors le bouton Insérer une fonction. Sélectionnez alors la fonction SOMMEPROD, la fenêtre suivante apparaît : Le terme matrice ne doit pas vous effrayer, dans notre cas il représente la colonne des durées ou la colonne des fréquences du mois. Comme nous les avons nommé précédemment, nous n’avons pas besoin de

les sélectionner dans le tableau, il suffit de saisir le nom adéquat. Excel nous permet même d’éviter cette saisie et de le sélectionner parmi la liste des noms créés. Pour faire appel à cette liste, placez vous dans la Matrice 1 et appuyez sur la touche F3. Il ne vous reste plus qu’à choisir le nom désiré dans la fenêtre suivante. Procéder de même pour saisir le nom de la fréquence du mois dans la Matrice2. Vous devez obtenir alors l’affichage suivant, il ne vous reste alors plus qu’à cliquer sur le bouton

OK et à répéter cette opération pour les mois suivants. NB : Pour information, la fonction SOMMEPROD permet de faire une somme de produits. Dans notre cas, ligne par ligne nous multiplions la durée du chantier par sa fréquence et nous additionnons le résultat avec la le résultat de la multiplication du chantier suivant et cela jusqu’au dernier chantier.

La formule suivante est celle qui nous donnera le total horaire annuel théorique. Nous nous plaçons dans la cellule E54 et nous procédons de la même manière que pour la première formule de ce tableau. La dernière formule va nous permettre de calculer l’horaire mensuel moyen. Pour cela, plaçons-nous dans la cellule E55, dans la barre de formule saisissez la formule suivante =E54/12.

4. Mise en forme conditionnelle et règles de saisie Pour créer un chantier, il suffit de saisir son nom, les observations éventuelles, les durées été et hiver et les fréquences. Lors de la saisie des durées, n’oubliez de saisir les chiffres au format heure (ie : pour saisir 08h30 saisir 08:30). Les fréquences sont saisies au format numérique, ainsi un chantier qui doit être effectué une fois par semaine aura une fréquence mensuelle de 4,33, deux fois par semaine donnera 8,66. Par commodité, nous avons préféré visualiser rapidement le fait de ne pas avoir d’interventions à effectuer sur un chantier. Pour cela, rien n’est saisi dans la cellule correspondante mais celle-ci doit apparaître alors avec un fond noir. Nous avons alors utilisé l’outil Mise en forme conditionnelle.

Sélectionnez donc l’ensemble des cellules ou nous saisirons les fréquences des chantiers, soit de la cellule E2 à la cellule R51 dans notre exemple.

Cliquez sur le Menu Format/Mise en forme conditionnelle. La fenêtre suivante apparaît

Cliquez alors sur la liste déroulante et sélectionnez alors la comparaison égale à

Nous allons vérifier si les cellules sont vides. Saisissez alors dans la zone de comparaison la valeur ="". Maintenant, nous allons spécifier à Excel la mise en forme que nous désirons. Cliquez sur le bouton Format, sélectionnez l’onglet Motifs et la couleur désirée. Validez en cliquant sur OK sur chaque fenêtre afin de

revenir sur le tableau des chantiers pour visualiser un résultat similaire à celui ci-contre.

5. Protection de la feuille Il ne nous reste plus maintenant qu’à protéger les formules saisies dans cette feuille. Pour cela nous devons d’abord indiquer à Excel quelles seront les cellules que nous pourrons modifier. Sélectionnez l’ensemble des cellules que vous souhaitez pouvoir modifier, dans notre exemple cela ira de la cellule A2 à la cellule R51, puis cliquer sur le menu Format/cellule. Sélectionnez l’onglet Sécurité et décochez le bouton Verrouillée. Validez en cliquant sur le bouton OK

Une fois cela fait, il ne nous reste plus qu’à protéger la feuille. Sélectionnez le menu Outils/Protection/Prot

éger la feuille

Dans la fenêtre suivante, vous pouvez saisir un mot de passe de protection (facultatif), et choisir, malgré la protection, quelles opérations pourront effectuer les utilisateurs du tableau. Une fois cela fait, il ne vous reste plus qu’à valider en cliquant sur le bouton OK.

B. Le planning annuel Ce tableau représente semaine par semaine, les différentes interventions sur les chantiers. Chaque journée est découpée en deux périodes sur lesquelles il est prévu 10 interventions en première période et 8 en deuxième. Pour chaque intervention, la première colonne doit permettre de visualiser les différents chantiers par une liste déroulante. La deuxième colonne doit afficher la durée du chantier par le biais d’une formule. La troisième colonne calcule l’heure de fin d’intervention. La quatrième colonne permet de saisir le kilométrage éventuel entre les différents chantiers. De plus, nous avons décidé, afin de faciliter la visualisation de ce tableau que les mois impairs et pairs disposent d’une couleur de fond différente. Cela sera fait par le biais d’une mise en forme conditionnelle. NB cette dernière colonne sera automatisée dans un deuxième temps.

1. Saisie et Mise en forme Ce tableau va nous permettre de saisir au jour le jour les différentes interventions relatives aux chantiers saisis dans la feuille tableau des chantiers. Commençons par saisir quelques informations nécessaires dans la première ligne du tableau. Nous avons saisi dans la cellule B2 l’intitulé : MISE A JOUR afin de pouvoir écrire dans la cellule C2 la date de mise à jour du document. De la même manière, nous avons saisi le nom de la personne correspondante à ce tableau. Les premières informations concernant le planning proprement dit, seront dans la cellule B4 la première date de votre calendrier. La mise en forme de cette cellule est particulière.

Nous saisissons la date selon le format jj/mm/aaaa mais nous souhaitons voir le nom du jour abrégé, son numéro, le nom du mois abrégé ainsi que l’année.

Pour ce faire, sélectionnez le menu Format/cellule, cliquez sur l’onglet Nombre s’il n’est pas sélectionné et choisissez la

catégorie personnalisée.

Dans la zone de saisie, nous allons taper le format suivant : jjj jj mmm aa, ce qui nous permet d’obtenir l’affichage désiré. Il ne nous reste plus qu’à confirmer cette mise en forme en cliquant sur le bouton OK.

Dans la cellule C4, apparaîtra le numéro de semaine correspondant au jour saisi précédemment. Ce numéro de semaine est mensuel, en ce sens qu’il nous permettre de savoir si nous sommes le 1er, le 2ème, le 3ème, le 4ème ou le 5ème mardi du mois. La formule nécessaire à cette information sera détaillée dans le deuxième point. La cellule D4 contient l’heure de démarrage des interventions, dans notre exemple, 7:00. Enfin dans la cellule E4, nous saisirons uniquement le terme KM, les cellules situées dessous contiendront éventuellement les distances entre les chantiers d’interventions. Les différents autres jours de la semaine seront remplis avec les mêmes informations. Dans notre exemple, le dimanche n’est pas saisi car il n’est jamais travaillé. La cellule A4 contient quant à elle, le mois correspondant au troisième jour de la semaine soit le Mercredi. Au niveau de la mise en forme, nous avons fusionné de la cellule A4 à la cellule A24 et demandé à ce que le texte soit aligné verticalement. Pour cela nous avons sélectionné le menu format/cellule, cliquez sur l’onglet alignement et choisi une orientation du texte adéquate. Dans la cellule B15, nous avons saisi le nom de la deuxième partie de journée, dans notre cas nous avons tapé A.M. (pour Après-midi). Dans la cellule D15, nous avons saisi l’heure de démarrage de cette deuxième partie de journée (14:00 dans notre exemple). Les autres cellules vont être détaillées dans le deuxième point de cette partie car elles contiennent soit des formules, soit une liste déroulante.

2. Création de formules et listes déroulantes La première formule se situe en C4 et permet l’affichage de numéro de semaine correspondant à la date. Pour cela, nous allons utiliser trois fonctions, CONCATENER(), JOUR() et ENT(). La fonction JOUR() nous permet de connaître le numéro du jour de la date saisie en B4, dans notre exemple cela donne 31. Nous divisons ce nombre par et retranchons 1/7 au chiffre obtenu. Nous demandons alors la partie entière de cette opération grâce à la fonction ENT(). Enfin nous ajoutons 1 à cette valeur. Une fois tout cela fait, nous utilisons la fonction CONCATENER() pour mettre bout à bout le texte SEM et le chiffre obtenu.

Dans un deuxième temps, nous allons créer dans les cellules B5 à B14 et B16 à B24 une liste déroulante qui permettra de choisir le chantier parmi ceux saisis précédemment. Pour cela nous sélectionnez les cellules et nous cliquons sur le menu Données/Validation. La fenêtre suivante apparaît. Dans la liste déroulante sous le champ Autoriser, sélectionnons une Liste. Il faut maintenant dire à Excel le nom de la liste contenant les chantiers.

Placer vous dans la zone Source et appuyer sur la touche F3 du clavier, cela va faire apparaître les différents noms déjà créés. Sélectionnons celui correspondant aux chantiers et validons notre choix en cliquant sur le bouton OK.

Nous arrivons sur la fenêtre suivante. Remarquons que deux cases à cocher sont activés. La première ne prend pas en compte cette règle de validation si la cellule est vide. La deuxième fera apparaître une liste déroulante dans chaque cellule sélectionnée. Les deux autres onglets disponibles permettent de faciliter la saisie de l’utilisateur, soit en le guidant dans la saisie, soit en affichant un message d’erreur explicite. Cela ne sera pas notre cas aujourd’hui, il ne nous reste plus qu’à valider nos choix en cliquant sur le bouton OK.

Ainsi, nous pouvons maintenant profiter d’une liste déroulante des chantiers dans chacune de nos cellules de saisie des interventions. Il ne faut pas oublier de répéter cela sur les autres jours de la semaine. Nous allons maintenant créer la formule qui permettra d’afficher la durée du chantier saisi en B4. Pour cela nous allons utiliser deux fonctions SI() et RECHERCHEV(). La première fonction va nous permettre de tester l’existence d’une intervention dans la cellule B5. En effet, si nous ne saisissons aucun chantier en B5, nous désirons qu’aucune durée n’apparaisse en C5. La deuxième permet de faire recherche à Excel la valeur contenue en B5 dans le tableau général des chantiers et de nous retourner la valeur correspondante dans la colonne contenant les durées. NB : Ce numéro de colonne est à modifier selon que nous sommes en période été ou hiver. La fonction terminée est donc celle-ci : =SI(B5="";"";RECHERCHEV(B5;tablo_chantiers;3;FAUX)). La prochaine fonction va permettre le calcule de l’heure de fin de l’intervention. Nous avons besoin de connaître l’heure de départ et la durée de celle-ci. Les fonctions utilisées sont la fonction SI() et l’addition. Ainsi la fonction SI() nous permet d’afficher ou non l’heure de fin en fonction de la présence d’un chantier en B5 et l’addition de la cellule C5 et D4 nous donne l’heure de fin. La fonction terminée est donc celle-ci : =SI(B5="";"";D4+C5)

Ces deux formules sont à recopier sur les différentes interventions possibles de la journée. La dernière formule va servir à connaître le nombre d’heures effectuées sur la journée. Nous allons la saisir dans la cellule C24 et elle effectue la somme des cellules C5 à C23. De la même manière une fois l’ensemble de la semaine effectuée, il est possible de calculer le nombre d’heures effectuées sur la semaine.

3. Mise en forme conditionnelle et règles de saisie Afin d’améliorer la lisibilité de ce tableau, les employeurs ont décidé de différenciés par une couleur les mois impairs et les mois pairs. Nous avons pour cela utiliser la mise en forme conditionnelle. Ainsi pour le premier jour, nous avons sélectionné les cellules correspondant aux lignes d’en-tête de début de matinée et d’après-midi (B4 à E4 et B15 à E15). Nous avons comparé le numéro du mois de la cellule B4 avec les numéros de mois pairs et ceux impairs et décidé d’une mise en forme correspondante.

Une fois la première semaine terminée, il ne reste plus qu’à la dupliquer et à modifier les formules qui permettent le calcul des dates : en B25, la date est calculée en ajoutant 7 à la date saisie en B4. Ainsi la deuxième semaine est créée rapidement et peut-être dupliquée pour constituer une année complète.

Il ne faut pas oublier quelques règles élémentaires lors de la saisie des interventions dans ce tableau.

• Tout d’abord, il est obligatoire de saisir les interventions els unes après les autres. • Si nécessaire vous pouvez modifier les heures de démarrage des demi-journées.

4. Création du Mode Plan Afin d’améliorer encore la lecture de ce tableau assez complexe, nous avons mis en place un plan tel que le permet le logiciel Ms Excel.

Pour cela sélectionner votre tableau de manière complète puis cliquer sur le menu Données/Grouper et créer un plan/Grouper. Des boutons de regroupement apparaissent alors en haut à

gauche des numéros de ligne. En cliquant sur le bouton 1vous ne disposez plus que d’une vision hebdomadaire synthétique et en cliquant sur le bouton 2, vous récupérez la vision globale du tableau. Le fait d’être en vision hebdomadaire ne vous empêche nullement d’afficher le détail d’une semaine en cliquant sur le bouton + correspondant.

5. Protection de cellules De la même manière que lors du précédent tableau, nous allons protéger toutes les cellules où nous avons saisi des formules.

C. Le tableau de contrôle

1. Saisie et Mise en forme Ce tableau est relativement simple dans sa conception. Il reprend la mise en forme du premier tableau. La première colonne recense les différents chantiers, une formule reprend automatiquement les valeurs saisies dans le tableau « tableau des chantiers ». La première ligne reprend le nom des périodes, dans notre exemple les mois, et reprend la mise en forme du même tableau.

2. Création de formules Seules deux formules sont utilisées dans ce tableau. La première reprend la valeur saisie dans le tableau « tableau des chantiers ». Ainsi dans la cellule B3 nous saisissons la formule suivante ; ='tableau des chantiers'!A2 et la recopions jusqu’à la cellule B53. Si la cellule correspondante ne contient aucun chantier, la formule nous retourne la valeur 0. La deuxième formule effectue un comptage du nombre d’interventions saisies dans le tableau Planning et le compare pour la période donnée avec le nombre saisie dans le « tableau des chantiers ». Cette formule n’est pas complètement automatisée dans le sens ou la saisie des références des périodes dans le tableau planning est manuelle pour chacune d’entre elles. In extenso, pour le mois de janvier, le tableau « planning » comprend les cellules F4 à Y24, B25 à Y87 et B88 à Q108. Ces valeurs changent d’un mois à un autre et d’une année sur l’autre (Cf. le Chapitre III-B). Il faudra créer la formule sur toute la ligne et la recopier alors sur l’ensemble des chantiers.

Pour cela, nous allons utiliser les fonctions SI(), NB.SI() et RECHERCHEV() . La première fonction va nous permettre de vérifier si un chantier existe dans la cellule B3, si ce n’est pas le cas nous afficherons la valeur zéro sinon nous allons compter grâce à la fonction NB.SI(). Celle-ci sera utilisée autant de fois que nécessaire suivant les zones où le comptage doit être effectué (Cf le chapitre In extenso ci-dessus).

Puis nous additionnons les différentes valeurs trouvées par la fonction NB.SI et nous allons y soustraire la valeur de la fréquence correspondant au chantier et à la période à laquelle nous nous trouvons. La formule finale, pour le mois de janvier est donc : =SI(B3=0;0;NB.SI(PLANNING!$F$4:$Y$24;B3)+NB.SI(PLANNING!B$25:Y$87;B3)+NB.SI(PLANNING!B$88:Q$108;B3)-RECHERCHEV(B3;tablo_chantiers;5;FAUX)) La valeur obtenue va représenter le nombre de fois ou nous sommes intervenus pour un chantier auquel nous avons soustrait le nombre d’intervention prévu sur la période. Si ce chiffre est négatif, il nous manque des interventions à effectuer, si ce chiffre est positif, nous avons effectué plus d’interventions que prévues. Pour visualiser cela plus simplement nous allons utiliser la mise en forme conditionnelle, ce qui sera détaillé dans le prochain chapitre.

3. Mise en forme conditionnelle et règles de saisie Comme nous venons de le voir, les cellules de suivi des interventions peuvent prendre plusieurs valeurs numériques. Nous avons décidé de définir trois cas de figure possible.

• Le zéro va correspondre à la parfaite adéquation entre le nombre d’interventions prévues et effectuées.

• Si la valeur se situe entre -0.5 et 0.5, cela veut dire que nous sommes proche de la perfection. Il ne faut pas oublier que lors de la saisie des fréquences dans le tableau « tableau des chantiers », nous avons donné une valeur théorique au nombre de semaines par mois : 4.33. Hors il arrive que sur trois mois alors que nous devons assumer 13 (4.33*3) interventions sur un chantier, nous effectuons réellement 4 interventions sur deux mois et 5 sur le troisième. C’est pour cette raison que les valeurs comprises entre -0.5 et 0.5 pour ces cellules sont tolérées.

• Enfin, si nous devons plus d’une demi intervention ou si nous avons effectué plus d’une demi intervention de trop, nous considérons ce cas de figure comme critique et à observer et corriger si nécessaire.

La mise en forme conditionnelle correspondante à ces trois cas de figure est donc retranscrite comme dans la figure ci-contre. Il n’y a pas de règles de saisie dans ce tableau car celui-ci se remplit automatiquement au fur et à mesure de la saisie des autres tableaux.

4. Protection de cellules De la même manière que lors des précédents tableaux, nous allons protéger toutes les cellules où nous avons saisi des formules.

D. Améliorations possibles Ces tableaux ne représentent pas (et loin s’en faut) la panacée pour la gestion de la planification de chantiers à fréquence variable. Nous avons donc commencé quelques pistes de réflexions afin de pouvoir les améliorer

1. Recherche automatique dans les zones de liste dé roulante Si vous disposez d’une longue liste de chantier, l’utilisation d’une liste déroulante peut s’avérer fastidieuse à l’usage. Il est donc possible d’aider dans la saisie afin que vous puissiez commencer à saisir et que la liste déroulante vous propose les noms de chantiers correspondants à vos débuts de saisie. Pour mettre cela en place, vous pouvez vous aider du lien ci-dessous qui détaille la mise en œuvre de cette possibilité. http://www.commentcamarche.net/faq/sujet-8288-liste-deroulante-avec-saisie-semi-automatique-pour-excel

2. Recherche du kilométrage entre deux chantiers Une des demandes de la société pour qui j’ai effectué cette formation était de pouvoir obtenir le kilométrage automatique entre les différents chantiers. La base de référence étant les valeurs données par ViaMichelin. Le mode opératoire que j’ai suivi a été :

1. Constitution d’une mini-base de données des différents itinéraires possibles (un fichier csv) à partir des tableaux de chantiers auxquels nous avons ajouté les renseignements géographiques (adresse, CP, ville). Cela a pu être automatisé par deux petites macros, une pour constituer les différents itinéraires possibles, l’autre pour exporter le résultat au format Csv.

2. Installation d’un outil de type EasyPhp ou Wamp (environnement Windows oblige). 3. Crée une base de données à une table avec les différents champs (NumItineraire, Ville

départ, adresse départ, CP départ, Ville arrivée, Adresse arrivée, Cp arrivée, distance), la clé primaire étant évidemment le premier champ.

4. Utiliser l’API fournit par ViaMichelin pour aller chercher les différentes distances en fonction des données contenues dans la table et remplir les champs distances correspondants. Adresse ou vous trouverez des informations sur l’API de ViaMichelin : http://dev.viamichelin.fr/wswebsite/fra/jsp/vmdn/VMDNHomePage.jsp

5. Renvoyer ces informations dans la base de données au format Excel 6. Grâce à la fonction RECHERCHEV(), afficher automatiquement le kilométrage

correspondant aux chantiers saisi dans le tableau PLANNING. L’utilisation de tout cela étant loin d’être automatisé et demandant quelques autres connaissances de l’outil informatique. Nous travaillons actuellement à chercher des solution splus imples côté utilisateur. Merci de votre lecture, si jamais vous souhaitez me contacter afin de pouvoir discuter sur les améliorations à apporter à ces tableaux, sur tout ce que nous n’avons pas vu ou si ces tableaux vous ont aidé. gilles.jol@ifrance.com