Tutoriel

Un planning « perpétuel » sur Microsoft Excel

Capture écran Planning réalisé avec Microsoft Excel
Figure 1 : Exemple de planning Excel.

Cet article est le premier d’une série consacrée à la création d’un planning perpétuel à l’aide de Microsoft Excel.

Dans ce premier billet j’explique ce qui motive ma démarche à entamer cette série d’articles.

Le second article analyse un planning Excel tel que l’utilisateur moyen le conçoit. J’y explique en quoi ce fonctionnement est limitatif et peu fiable.

Dans le troisième, je propose une alternative plus élégante, stable et exploitable.

Le quatrième article propose la mise en place de notre planning.

Enfin dans un dernier billet, je proposerais des pistes d’évolutions.

Introduction

Un planning est un outil important en entreprise et nombre d’employés en gère un sur le tableur.

Vous trouverez sur Internet un large choix de modèles prêts à l’emploi et de tutoriels sur leur construction

Alors, pourquoi un article de plus ?

J’aurais une vision plus large et profiterais de l’occasion pour montrer comment construire des feuilles de calculs efficaces, faciles à maintenir et exploiter.

L’objectif est de partir d’un exemple simple comme pourrait le concevoir un utilisateur pas forcément aguerri au tableur et le transformer en un projet complet sans posséder de compétences poussées sur Microsoft Excel.

C’est l’occasion d’approfondir des connaissances sur le logiciel, mais plus largement apprendre à organiser ces feuilles de calculs pour en tirer le potentiel maximal.

Une priorité

Contrairement à l’usage, ma préoccupation première portera sur l’organisation des données et non pas l’affichage.

De l’affichage et rien d’autre

La grille d’affichage ne s’occupera de rien d’autres que ce pour quoi elle est faite, afficher des données et non pas les écrire comme on le voit majoritairement.

L’aspect « perpétuel »

Il est rare de trouver des exemples de plannings perpétuels. En général, ils sont prévus pour une année avec parfois des débordements de quelques semaines en début ou fin de planning.

Objectifs

Gérer efficacement ses données et sécuriser l’information

Les données sont les fondations d’un programme informatique. À quoi servirait l’informatique s’il n’y avait pas de données à gérer !

Selon l’organisation de vos données :

  • elles seront faciles à manipuler (ajouter, supprimer, modifier, lire), simples à analyser et synthétiser. L’information sera cohérente (les erreurs de saisie sur Excel sont fréquentes).
  • au contraire, la manipulation et l’exploitation de l’information peut devenir un enfer, allant jusqu’à de grands copier/coller « sauvages », voire la suppression et modification d’un grand nombre de données.

Plus l’utilisateur manipule l’information, plus les risques d’erreur augmentent.

L’exemple

J’ai choisi d’utiliser un jeu de données concernant des absences en entreprise. Le planning pourrait gérer des chantiers, projets, faire un planning des vacances scolaires ou les siennes, voire les deux au sein du même classeur.

Plusieurs versions du planning pourront être générées.

Méthodologie de travail

J’ai choisi pour illustrer mon propos, la gestion d’un planning. Mais la façon de gérer ses feuilles de calculs sera identique pour d’autres classeurs.

Nous apprendrons à organiser nos classeurs selon trois critères :

  • La gestion des données.
  • La présentation de l’information.
  • Les analyses, statistiques et calculs divers.

Optimiser l’utilisation d’Excel

Des astuces et recettes essentielles dans l’usage d’Excel seront abordées.

Prés-requis

Tout utilisateur moyen du tableur, comprendra aisément les parties sur l’organisation des données, les bonnes règles de construction des tableaux, la philosophie générale de travail.

Des parties plus techniques s’adressent aux utilisateurs plus avancés d’Excel. C’est les cas des formules de calcul rencontrées dans le projet.
Il n’est pas nécessaire de comprendre ces éléments avancés.

Bien sûr, j’utiliserais le jargon Excel et l’utilisateur qui souhaite suivre confortablement cet article devra connaître des concepts comme les mises en forme conditionnelles, les mises en forme de cellules, les tris, etc.

J’approfondirais certains points clés ou techniques incontournables avec le tableur.

Vous pouvez vous contenter de télécharger et utiliser le modèle mis à disposition (Excel 2010 Windows 32 bits) en fin de page.

Ce projet est réalisé avec Microsoft Excel 2010 version 32 bits. Avec quelques ajustements il est possible d’appliquer ces techniques à tout tableur.

Quelques incompatibilités existent entre les versions 2003, 2007 et celles supérieures à 2007.
Je le préciserais dans l’article.

Le classeur source

Mode d’emploi

L’utilisation restera simple et se déroulera en deux étapes :

  • La saisie des données dans les tableaux ;
  • L’affichage dans la grille.

Organisation de l’article

L’avancée du projet sera détaillée dans plusieurs articles :

  • Article 1 :
    • C’est le présent article. Il présente les grandes lignes du projet.
  • Article 2 :
    • Discussion sur un modèle classique où les données sont directement saisies dans la grille du planning et la plage de dates limitée à une année.
    • Trouver une philosophie de conception et de fonctionnement plus efficace.
  • Article 3 :
    • Définir l’organisation des différents tableaux.
  • Article 4 :
    • La construction physique des tableaux sur Excel.
    • Quelques éléments fondamentaux d’Excel comme les validations de cellules ou les modèles de documents.
  • Article 5 :
    • Construction de la grille du planning.
    • Principe de navigation dans le planning.
  • Article 6 :
    • Utilisation du planning au quotidien.
    • Personnalisation du classeur.
  • Article 7 :
    • Limitations du modèle et évolutions possibles.

Cahier des charges

Tout projet un tant soit peu sérieux commence par une phase d’analyse.

En informatique perdre du temps au début, c’est en gagner beaucoup dans le temps.

L’utilisateur

Je partirais de l’exemple classique glané sur Internet et sur le fonctionnement habituel de l’utilisateur.

Limites du modèle

Ce modèle est très limitatif et possède de nombreux défauts que j’énumèrerais.

Solutions envisagées

J’envisagerais différentes alternatives et j’en déterminerais la plus souple et efficace.

Sachant qu’il n’y a jamais de meilleure solution, mais toujours des moins pires que d’autres.

Les données

Les données sont le socle de mon programme. Je vais donc déterminer un espace de stockage avec minutie.

Il existe des règles simples de construction pour optimiser ses données (et donc les fiabiliser) qui s’appliquent à pratiquement tous les tableaux et petites applications.

Construction des dates

Les calculs sur des dates seront nombreux. Mais les fonctions sur les dates sont souvent utiles dans un grand nombre de projets.

Capture écran Tableau Excel avec calculs des dates nécessaires
Figure 2 : Tableau des paramètres de dates.

Nous chercherons entre autre, à déterminer si une date est un week-end ou un jour férié.
Le calendrier des jours fériés est celui de la France.

Capture écran Tableau Excel avec calculs des jours fériés
Figure 3 : Tableau des paramètres des jours fériés.

L’interface

Une grille

Elle ne contient pratiquement que des formules de calculs qui vont « piocher » les données dans nos différents tableaux.

L’information sera présentée et identifiée essentiellement à l’aide de mises en forme conditionnelles.

Système de navigation dans les dates

Le planning étant pratiquement perpétuel, il nous faut un système de navigation. Il doit rester souple, simple à mettre en œuvre et un tant soit peu ergonomique.

Charte graphique

J’aborderais quelques règles basiques pour faciliter la lecture du classeur.

Je ne discuterais pas essentiellement de l’harmonie des couleurs, mais plutôt des règles de confort visuel.

Récupérer les informations métier

Un évènement sera associé à une entité comme un employé, un projet, etc.

Capture écran Tableau Excel d'exemple
Figure 4 : Tableau exemple renseigné avec des personnes.

Nous devons récupérer certaines de ces informations dans notre planning comme les noms et prénoms ou la description du projet par exemple.

Les périodes

Conservent les dates d’occupations. Nous pouvons employer indifféremment les termes de taches, évènements, etc.

Capture écran Tableau Excel de gestion des évènements
Figure 5 : Tableau exemple renseigné avec des absences.

Les catégories

Un évènement inscrit dans le planning appartiendra à une catégorie identifiée par une lettre (comme « C » pour « Congé » ou « M » pour « Maladie »).

Capture écran Tableau Excel des catégories
Figure 6 : Tableau exemple renseigné avec les catégories d'absences.

Ici aussi une mise en forme conditionnelle affichera l’évènement dans une couleur spécifique.

Réaliser des analyses des données

Quelques exemples de calculs complémentaires d’aide à l’analyse seront proposés.

Utilisation du classeur

Il existe plusieurs tâches à réaliser :

  • Manipuler les données (ajouter, supprimer, modifier).
  • Mettre à jour et visualiser le planning (consulter).
  • Personnaliser le classeur.

Limites

Nous sommes tributaires des limites inhérentes à l’outil et certaines ne peuvent être franchies qu’à l’aide de macro-commandes.

Évolutions envisageables

Un programme informatique vie et se transforme avec le temps. Une fois la version de base opérationnelle d’autres besoins secondaires peuvent être satisfait.

Plus le projet se développe, plus le besoin d’utiliser les macro-commandes se fait sentir.
Vous ne trouverez aucune trace de macro-commandes dans le projet. Je prévois une suite à cette série consacrée à la programmation.

Téléchargement

Vous pouvez utiliser le classeur vierge mis à disposition.
Il a été réalisé à l’aide de Microsoft Excel 2010 en version 32 bits avec Microsoft Windows 7 lui aussi en 32 bits.

Ce classeur a été peu testé en situation réelle. Ainsi il est conseillé de l’utiliser avec prudence pour une utilisation professionnelle.

Je n’assure pas le SAV de ce projet qui n’est que prétexte à étude.

De la même manière je ne serais responsable en aucune manière de l’usage que vous faites du classeur ni des dommages qu’il pourrait occasionner.

>