Tutoriel

Planning perpétuel : Cahier des charges

Cahier des charges pour Microsoft Excel

Pourquoi un cahier des charges ?

Un voyage se prépare, la construction de sa résidence se planifie, il en est de même d’un projet informatique.

Trop souvent en entreprise (parce que l’utilisateur n’est pas informaticien et que ce n’est pas son travail premier), l’analyse se résume à dix minutes de son temps et trois phrases jetées sur un brouillon.

Exemple d'une analyse informatique

Hors c’est la phase la plus importante. La réalisation sur l’ordinateur n’en est que la concrétisation.

Je comprends bien cette précipitation, en entreprise on travaille toujours dans l’urgence et tout était à faire pour hier.

En vous posant des questions, vous, vous rendrez peut-être compte que c’est le demandeur qui détient l’information.
En demandant des éclaircissements, le temps qu’il vous réponde, vous aurez peut être gagné deux jours. Et si vous avez deux questions, vous envoyez un mail par question.
Et vous vous apercevrez que l’urgent ne l’était pas tant que ça quand il vous répondra deux jours plus tard.

Le demandeur, un supérieur, un collègue, vous-même n’avez pas une compétence très poussée en informatique. Même si vous pensez l’inverse, un tas de notions dans la conception d’un projet vous échappe (schéma relationnel, atomisation, ergonomie, compétences Excel, etc.).
Le demandeur peut vouloir une chose qui de prime abord semble simpliste « J’ai besoin de ça pour cette après-midi !». Vous recevez une information simple et clairement exprimée, vous penserez que la solution est elle aussi simpliste et vous lâcherez un grand « Pas de problème ! ».

Je ne compte pas les fois où un client m’a dit, « Je ne pensais pas que ma demande était si compliquée ! ». Mais il ne voit que son souci présent et pas les tenant et aboutissant de sa demande.

Bien sûr, il est impossible de prévoir l’imprévisible, mais l’analyse peut permettre d’éviter des pièges, des choses auxquelles on ne pense pas spontanément et sûrement pas en un quart d’heures.

Lors du changement des taux de TVA, j’ai été abondamment sollicité pour reprendre des projets pourtant réalisés par des étudiants en informatique stagiaires de l’entreprise.
Seulement, ils n’ont pas pensé que ces taux pouvaient changer.

Les outils

Papier crayon

Cela restera ma méthode préférée. Elle peut se faire n’importe où et donc à n’importe quel moment.

C’est facile de déchirer un brouillon et en reprendre un nouveau.

On reste concentré sur le besoin.

Utiliser les mockup

Il n’y a pas de connaissance particulière à posséder, contrairement à d’autres solutions.

Les autres outils comportent des pièges.
Tentation forte de vouloir mettre de la couleur ou changer la forme de l’objet. Il peut naître une tendance à privilégier la forme plutôt que le fond.
Selon l’outil choisi, les déplacements, insertion et suppression d’objets peuvent devenir pénibles.

Par contre les outils sont intéressants dans un second temps pour réaliser des schémas ou autres à inclure dans notre cahier des charges.

Éditeur de textes

La limite d’un simple éditeur de textes comme le bloc-notes de Windows est la création de schémas.

Il ne comprend que le texte brut. Il est utile pour réaliser un brouillon avec les points déjà acquis par l’analyse.

Traitements de textes

Un logiciel comme Microsoft Word est obligatoire pour réaliser son cahier des charges tant les fonctionnalités pour réaliser des documents lourds sont incontournables.

Mais dans une première approche, ils sont trop parasites. L’utilisateur est vite tenté de vouloir formater son document et définir sa structure qui sera sûrement remise en cause pendant que l’analyse se poursuit.

Outils de dessins d’Excel

C’est une solution à condition de bien maîtriser les outils proposés sinon c’est vite l’enfer.

Il faut connaître les méthodes d’alignement, de position, les connecteurs, etc. pour que l’usage soit confortable.

Outils de mind mapping

Le mind mapping c’est mettre des idées dans des boites reliées à d’autres idées de même niveau ou de niveau inférieur (hiérarchie).

Ils produisent des « cartes heuristiques » et ils sont parfaits pour la réalisation de brainstorming.

Les outils sont simples à utiliser et certains sont gratuits, d’autres vont jusqu’à gérer des tâches dans un calendrier comme Microsoft Outlook, insérer des images, définir des catégories, etc.

Là aussi le piège c’est de s’attacher à la forme plutôt qu’au fond.

Exemple de carte heuristique

Mais ce sont des outils que je n’hésite pas à utiliser pour structurer des idées et définir une hiérarchie et c’est idéal pour définir le plan de mon cahier des charges.

Si vous utilisez un produit conséquent, votre carte peut devenir un vrai centre de pilotage du projet.

Ils peuvent se décliner en modèles plus spécifiques comme des diagrammes de flux, gestion de tâches, etc.

Quelques logiciels

Gratuits
  • Freemind
  • MindMaster (EDraw)
    Version gratuite du logiciel EDrawMax.
    Possible de télécharger sans donner d’informations personnelles en cliquant sur le bouton « Skip and Download ».
Payants
En ligne

Il existe des solutions gratuites en ligne. En générale, derrière il y a une solution payante. La limite réside principalement dans le nombre de cartes possibles avec un compte gratuit.

Certaines solutions mettent à disposition des outils collaboratifs pour partager les cartes.

Vous trouverez sur roget.biz un comparatif succinct de quelques solutions existantes.

Voici une petite liste d’outils pertinents.

  • Mindmeister
    • 3 cartes gratuites par comptes.
  • Mindomo
    • 7 cartes gratuites par comptes.
  • Wisemapping
    • Complètement gratuit. Nombre de cartes illimité.

Générateurs de diagrammes

Ils sont très proches des logiciels de mind mapping qui souvent permettent leur création comme Visio.

Exemple de diagramme

Ne pas oublier Microsoft PowerPoint souvent disponible dans les entreprises.

Capture d’écran

Vous aurez peut-être besoin de réaliser des captures d’écran et pas seulement pour la phase d’analyse mais aussi si vous devez rédiger le cahier des charges ou un mode d’emploi du projet.

L’outil Windows

Il est vraiment trop limité puisqu’il permet uniquement de capturer l’écran, la fenêtre active ou une zone à l’écran.

Des outils plus évolués

Des logiciels plus complets permettent par exemple de capturer une icône particulière, sans avoir à dessiner un cadre autour de la zone à copier. La capture est nette et n’oblige pas à retailler les captures réalisées à la « main ».

Il existe de nombreux logiciels gratuits et payants comme Snagit la Rolls de la capture, Camtasia, Screenpresso.
Ils sont souvent affublés de fonctions complémentaires pour envoyer la capture sur les réseaux sociaux, utiliser un éditeur d’images, mettre des tampons sur le captures, etc.).
Je ne porte pas mon choix sur ces accessoires, car je peux réaliser ces opérations avec d’autres moyens.
Je m’attarde sur les possibilités de sélection de la zone à copier.

Pour ma part, j’utilise la version gratuite de picpick, un des rare à pourvoir capturer les icônes de la barre des tâches.

Screencast

Le screencast c’est de petites vidéos qui vous présentent des actions réalisées sur l’écran de l’ordinateur. Ce sont les vidéos que vous voyez sur les sites de tutoriels informatiques.

Les logiciels de screencast, enregistrent les actions que vous faites sur votre écran. Ensuite il est possible de réaliser un montage plus fin.

Sans contexte le plus connu des logiciels gratuits est CamStudio, mais il en existe bien d’autres.

Détecter le besoin

Il est important de connaître la situation actuelle.

Détecter les failles et les avantages

Elle permet de faire le tri entre les bonnes idées à garder, ce qui est bancal, superflu, limitatif, etc.

  • Le principe de grille pour visualiser est appréciable.
  • Saisir dans la grille est fastidieux et source d’erreurs.
  • Avec ce principe, l’exploitation des données est pauvre.
  • La bascule annuelle est franchement hasardeuse et peu ergonomique.

Analyse fonctionnelle

C’est celle à laquelle on pense spontanément car au plus proche des préoccupations du moment.

C’est la phase où l’on répond aux besoins énoncés comme « je souhaite », « j’ai besoin », « il me manque ».

Le concepteur va énumérer les services attendus et existants, ainsi que les prérequis obligatoires (par exemple l’utilisation d’un classeur de données existant).

  • Saisir des plages de dates occupées.
  • Identifier l’entité concernée par l’occupation (affaire, projets divers, personnes, matériel, etc.).
  • Réaliser quelques analyses sur les données (personnes absente sur une période, nombre de personnes absentes ou présentes pour une période données, etc.).
  • Faciliter la mise à jour annuelle.

Analyse technique

Représente les conditions dans lesquels le projet s’exécute.

L’infrastructure informatique peut impacter sur le projet (fichier sur le réseau).

Le volume de données à exploiter peut déterminer si Excel est adapté. Si je dois manipuler des tableaux de 2 GO, je me tournerais vers des solutions plus adaptées.

Projet privatif

Si je suis le seul utilisateur du projet, même si je fini par un copier/coller « sauvage » parce que le programme n’est pas finalisé, ce n’est pas grave.

Si une fonctionnalité n’est pas tout à fait au point, je peux la contourner en travaillant en manuel.

La pression est minimale, tant que les résultats sont produis.

Version en test

Pour l’heure notre projet est embryonnaire. Il sera développé en local.

Une version peut déjà posée dans le dossier de production principalement si celui-ci est sur un réseau.
Cela permet de voir comment se comporte le fichier dans ce cas.

Mais ce n’est pas prioritaire.

Projet multi-utilisateurs

En multi-utilisateurs, il en va tout autrement. Les contraintes sont décuplées.

C’est à relativiser à l’ampleur du public.
Si cela concerne les deux collègues du bureau d’à côté, les charges seront moindre (mais jamais nul) que si toute l’entreprise bénéficie du projet.

Un projet multi-utilisateurs accroit les contraintes
Vous devenez référent du projet.

C’est-à-dire qu’à tout moment vous pouvez être dérangé pour un bug.

Vous devez respecter les délais de livraison annoncés.

C’est une question de crédibilité auprès des collègues.

C’est chronophage !

Ce n’est pas le métier de cœur de l’utilisateur. Souvent il est comptable, chef d‘atelier, secrétaire, etc.

Certaine notion fondamentale de l’informatique lui échappe. Il n’a même pas conscience qu’elles existent.

Ainsi souvent il fonce vers des impasses qui l’obligent à reprendre une partie du projet.

Accessibilité

Un utilisateur vous demandera d’augmenter la taille de la police, d’autre qu’il lit mal le bleu sur le fond rouge ou encore qu’il faudrait une navigation à l’aide du clavier.

Ces demandes pouvant être légitimes, certaines personnes ont des problèmes de vue, d’autres des difficultés à utiliser la souris.

L'accessibilité est à prévoir dans un projet Excel

Vous devez prendre en compte l’accessibilité dans vos projets.

Documentation

Vous devrez produire une documentation sur le fonctionnement du projet ce qui peut prendre du temps.

Il faut définir un format de diffusion.

  • page web ;
  • document Microsoft Word ;
  • fichier PDF ;
  • vrai système d’aide (réagit à l’appui de la touche [F1] ;
  • etc.

Il faut réaliser des captures d’écran et rédiger le contenu.

Il est de bon ton de maintenir une documentation technique dont fait partie le cahier des charges mais aussi un historique du projet (discussions, bugs corrigés, évolutions réalisées, etc.).

D’autres contraintes

En multi-utilisateurs, les documents sont sur le réseau. Rien que ça apporte son lot de questions.

Faut-il implémenter des mesures de sécurité (comme des droits d’accès) ?
Comment seront gérés ces droits ?

Les fichiers incriminés supportent la charge réseau ?

Et l’envoi de fichier vers l’extérieur (mail), comment faire si les classeurs contiennent des liaisons ?

Analyse des services

Des services annexes peuvent devenir importants principalement en multi-utilisateurs.

  • Si le projet nécessite la saisie de dates, il me faudra peut-être rajouter un calendrier.
  • Si une zone doit recevoir une valeur numérique, je devrais programmer cette zone pour qu’elle n’accepte que des nombres.
  • Peut-être que sur un traitement, j’aurais besoin d’une barre de progression.

Analyse environnementale

Nous utilisons comme support à notre projet un outil Microsoft Excel.

Cet outil à son propre fonctionnement, mais aussi ses limites.

Nous devons prendre en compte son fonctionnement pour produire des projets faciles à maintenir.

C’est toujours mieux d’aller dans le sens de la vague que de lutter contre. Aller dans le sens d’Excel quitte à dégrader ses prétentions est une composante importante dans la réussite du projet.

Pour Excel, un tableau se construit de haut en bas, avec en haut les titres de colonnes.
Utiliser des tableaux où les titres sont à gauche et les données en colonnes, c’est s’exposer à des limitations dans l’usage du tableur.
Il est pourtant simple d’inverser un tableau (transposer) et de s’habituer à ce fonctionnement.

Analyse structurelle

Il existe des règles de gestion de données éprouvées pour les stocker de manière optimale.

Ces règles sont implémentées dans les moteurs de bases de données comme Microsoft Access ou autres.

Les tableaux excel se structurent
Figure 1 : Exemple de schéma relationnel.

Bien que Microsoft Excel n’est pas un gestionnaire de données, de fait, il sert souvent à ça et même si certaines ne sont pas utilisables, d’autres sont faciles à mettre en œuvre.

La façon de structurer ses tableaux de données et bien choisir les informations à inclure va fortement impacter sur la facilité d’utilisation du projet au quotidien.

Définir l’organisation des données est la première chose à faire suite à l’analyse.

Détecter les alternatives

Peut-être que des démarches différentes amènent à une solution plus élégante.

La première solution envisagée de manière aléatoire (issue de notre expérience) est peut-être la plus mauvaise.

Une bonne analyse permet de choisir une route sur des critères objectifs.

Durée de conception

L’analyse permet de comprendre l’ampleur du projet.

Souvent une tâche qui semble simpliste de prime abord, s’avère chronophage en temps et énervements.
L’utilisateur fonce sur son logiciel, plein d’allant, il pianote sur son clavier tel un forcené.
Jusqu’au moment où il s’aperçoit qu’il est dans une impasse ou qu’une information manquante remette en question ce qui vient d’être réalisé.

L’imprévisible peut survenir à tout moment

Une formule plus complexe à mettre en œuvre que prévu.

Un message d’erreur ou un calcul erroné qui sont incompréhensibles.

C’est surtout vrai avec les macro-commandes et codes V.B.A.
Souvent on dit que pour accélérer une macro-commande, il faut éviter de faire dedans des sélections de feuilles ou cellules.
Seulement dans certains cas, c’est une étape obligatoire. De plus, le message renvoyé par Excel n’est pas des plus précis et vous pouvez chercher un bon moment pourquoi ça plante.

Principe de dégradation

La durée de conception me dit aussi si la fonctionnalité que je souhaite demande une énergie, des compétences, une lourdeur en adéquation avec mes attentes.
Si une mise en œuvre demande trop d’investissement et peu d’apport, autant abandonner et se tourner vers d’autres voies.

La dégradation c’est le taux entre la difficulté de conception et l’attente, entre les inconvénients et les avantages.

Pour améliorer la visibilité de mes tableaux Excel, je fusionne des cellules, mais en faisant cela la gestion et le maintien des tableaux se compliquent.
Alors tant pis, je ne fusionne pas dans les tableaux !

Pas de fusion de cellules dans les tableaux Microsoft Excel

Analyser l’existant

Définition d’un planning

Des entités à quantifier en jours

Ce peut-être des personnes, des véhicules, des tâches, des évènements, etc. occupés sur une période.

Comme la nature de cette donnée n’est pas définie (ressources, évènements, personnes physique - morales), j’emploierais le terme générique d’ « entités » pour y faire référence.

Des périodes

Représente la ou les dates pour lesquelles l’entité est concernée.

Les périodes pour une même entité ne se chevauchent pas.

Une entité en congé du 3 mai 2017 au 15 mai 2017, ne peut être en formation dans cette période.

Exemples

L’entité « employé » a une période « absence ».

L’entité « employé » a une période « déplacement ».

L’entité « véhicule » a une période « réparation ».

L’entité « travaux » a une période « pose électricité ».

L’entité « vacances scolaire » a une période « Toussaint ».

Etc.

Notion de calendrier

Un calendrier est une version simplifiée d’un planning.

Il ne présente que les années, mois, jours.

Il peut être complété avec des informations sur l’éphéméride ou les fêtes du jour.

Il n’a pas d’entité associée ni de notion de périodes occupées.

Exemple de calendrier

En plus de gérer des périodes, il s’occupe aussi de plages horaires.

Un planning peut se présenter sous toutes les formes possibles et inimaginables.

Néanmoins, le principe reste inchangé, il existe un principe de cases représentant des jours ou mois dans lesquels on saisit la ou les tâches à effectuer.

Un calendrier journalier

Une grille pour le planning

Il est possible en informatique de calquer la même philosophie, mais l’exploitation sera réduite au minimum.

Un des avantages de l’informatique c’est la possibilité d’exploiter les données existantes.

Mais pour cela, les données doivent être un minimum organisées et l’affichage d’un planning est pratiquement immuable sur un écran. De plus Les outils de bureautique offrent peu de libertés dans les mises en forme et nous sommes souvent réduits à utiliser un tableau.

Liste des jours en ligne

La première ligne du planning, affiche les dates incrémentées entre le premier jour du planning au dernier jour prévu.

La ligne titre sous forme de date du tableau excel
Figure 2 : Ligne de titre du planning Excel.

En général, cette ligne couvre une plage de dates représentant une année complète.
Mais parfois, l’utilisateur « déborde » pour prendre en compte des périodes à cheval sur deux années.

Planning statique

Dans la première cellule de la ligne des dates, l’utilisateur saisi celle qui débute son planning.
Typiquement c’est le premier janvier de l’année à manipuler.

Ensuite, il incrémente (soit par de la saisie soit par recopie incrémentale), cette valeur sur l’ensemble de la ligne, jusqu’à arriver à la date de fin.
Généralement le 31 décembre de l’année du planning.

Planning dynamique

Bien sûr, il est préconisé d’utiliser cette méthode d’autant plus qu’elle n’est pas compliquée à mettre en œuvre.

Dans une cellule, on saisit l’année concernée par le planning.

La première cellule de la ligne de titre avec les dates est renseignée par formule de calcul.

La fonction DATE() permet de reconstituer une date valide en donnant l’année, le mois et le jour.

Je pars sur la construction d’un planning annuel. Pour faciliter les choses, je prendrais le postulat que l’année commence au premier janvier, ce qui n’est pas toujours le cas.

Utiliser la fonction microsoft excel date()
Figure 3 : Construire une date à l'aide de la fonction Excel DATE().

Quand une année est rentrée dans la zone prévue à cet effet, la ligne de dates se met à jour avec l’année concernée.

Pour générer les autres dates, il suffit d’incrémenter la suivante avec la valeur de la précédente.

Formule pour incrémenter une date sur le tableur
Figure 4 : Une date s'incrémente comme un nombre.

Il est possible de formater les dates ou ajouter une ligne pour identifier le jour de semaine, le but étant d’éviter d’écrire sur les week-ends et jours fériés.

Liste des entités en colonne

Une ou plusieurs colonnes, affichent des informations sur l’entité (comme son nom pour une personne ou son code pour une affaire).

Colonne des entités
Figure 5 : Les données s'écrivent dans des colonnes.

Ces données sont saisies dans les cellules ou récupérées depuis un autre tableau à l’aide d’une fonction RECHERCHEV().

Une valeur

Une valeur à l’intersection d’un jour et d’une entité est inscrite.
Elle marque le jour comme occupé par l’entité.

Je donnerais le nom de « marque » à cette valeur.

Une marque pour identifier un rendez-vous
Figure 6 : Les marques s'inscrivent dans les cellules du planning.

Le choix de la marque paraît anodin, mais il aura des répercussions importantes sur les calculs réalisables à des fins d’analyses.

Caractère quelconque

Cette marque peut-être un caractère (souvent un « X ») pour afficher l’occupation de la case.

Une marque textuelle
Figure 7 : Une marque sous forme de texte.

Avec ce principe nous savons qu’une entité est occupée sur une date sans connaître la raison.

Comme la valeur est textuelle, les calculs ne seront pas tous réalisables ou du moins pas aussi simplement qu’avec un nombre.

Il est possible avec une « Validation de données » d’empêcher l’écriture d’un autre caractère comme marque.

Nombre

Une marque numérique est possible. C’est souvent la valeur « 1 » qui est choisi et qui semble la plus appropriée.

Une marque numérique
Figure 8 : Une marque sous forme de nombre.

Là aussi, je n’identifie pas la raison de l’occupation.

Avec une valeur numérique, les calculs sont plus faciles à mettre au point.

Cette méthode semble préférable à l’insertion d’un caractère.

Catégories

C’est une extension à l’usage d’un caractère.

Une marque qui identifie une catégorie
Figure 9 : Plusieurs marques associées à une catégorie.

Au lieu d’utiliser un seul caractère pour indiquer que le jour est occupé, la catégorie apporte un niveau d’information complémentaire.

  • Sur un planning d’absences, le « M » peut représenter la catégorie « Maladie ».
  • Pour un planning de travaux, on peut imaginer la catégorie « Gros œuvres » identifiée par le code « G ».

Elle présente du coup les mêmes inconvénients qu’utiliser un caractère unique.

Il faudra trouver une manière plus pertinente d’utiliser la marque.

Utilisation du planning

Au quotidien, l’utilisation du planning est basique.

Créer une entité

  1. Dans les colonnes prévues pour cet usage, l’utilisateur renseigne les informations sur l’entité.
    • Il y aura dans le planning autant de lignes que d’entités.
  2. À l’intersection de l’entité et la période, on saisit la marque choisie.

Bascule annuelle

C’est le point noir du système.

L’utilisateur génère en général tous les ans une nouvelle grille.

Seulement, je peux être amené à gérer des périodes à cheval sur deux années.

Solutions envisageables

Selon la compétence du concepteur, la démarche appliquée sera différente.

  • l’utilisateur prend un document existant.
    Il supprime le contenu et enregistre sous un nouveau nom le classeur.
  • l’utilisateur se sert d’un document vierge. C’est une trame existante.
  • l’utilisateur a créé un modèle au sens Excel.
    C’est une extension de la trame.
Utiliser un modèle de document

Les modèles sont préférables aux trames utilisées habituellement.

Les modèles sont pris en charge par Excel, et sont stockés dans un dossier particulier.

C’est la manière d’enregistrer le fichier qui fait la différence.

enregistrer un modèle excel
Figure 10 : COnvertir un classeur en modèle Excel.

Donc dans la boite « Enregistrer sous… », choisir le type « Modèle Excel (*.xltx).

Pour utiliser le modèle, aller dans le menu « Fichier » et choisir l’option « Nouveau ».

Cliquer sur « Mes modèles ».

utiliser un modèle excel.
Figure 11 : Convertir un classeur en modèle Excel.

Excel génère une copie de notre modèle qui se comporte comme le classeur par défaut d’Excel.

Discuter le modèle

Éléments favorables

Simplicité

  • La solution est facile à mettre en place ;
  • L’utilisation est immédiate ;
  • Les compétences nécessaires sont minimales.

Il n’y a rien de compliqué à rajouter dans la grille une « Validation de données », pour rejeter les marques interdites.

Une « Mise en forme conditionnelle » peut être appliquée aux cellules de la grille pour identifier les marques.

mise en forme conditionnelle sur excel.
Figure 12 : Mise en forme conditionnelle appliquée aux catégories.

Il suffit de créer autant de mises en forme que de codes.

Insertions et suppressions

Les insertions et suppressions d’entités sont triviales. Il suffit d’insérer ou supprimer une ligne dans le tableau. Éventuellement il faut retoucher la mise en forme et/ou recopier des formules de calculs.

Analyse des données

Nombre de jours occupés pour une entité

Un des premiers besoins est de connaître pour une entité (par exemple un employé), le nombre de jours d’occupation (par exemple des absences).

Il suffit de compter le nombre de cellules de la grille qui sont renseignées.

Il est possible de calculer ce nombre pour une période déterminée du planning.
Dans ce cas, il suffit de réduire le nombre de colonnes sélectionnées dans les formules.

Marque numérique

Si la marque est la valeur « 1 », une simple SOMME() de la ligne du planning suffira.

=SOMME(C5:G371)

Pour une période spécifique, il faut réduire aux colonnes de la plage souhaitée.

faire une somme numérique sur le tableur.
Figure 13 : Somme des marques numériques.

=SOMME(C12:C19)

Marque textuelle

Le calcul est un peu plus compliqué.

Il faut compter le nombre de cellule de la ligne (365 ou 366) qui contiennent une marque, ce qui s’obtient avec la fonction NBVAL() de la ligne du planning.

utiliser la fonction nbval sur microsoft excel.
Figure 14 : Somme des marques textuelles.

= NBVAL (C5:G371)

Nombre d’entités occupés

Nous venons de calculer dans une colonne, le nombre de marques par ligne.

Il suffit d’effectuer la somme de cette colonne pour connaître le nombre total de jours marqués (par exemple le nombre de jours total d’absences).

somme des colonnes sur excel.
Figure 15 : Somme des Somme des employés occupés.

C’est exactement les mêmes fonctions que ci-dessus mais appliquées aux colonnes.

Calculs avec les catégories

Tous ces calculs possèdent un critère qui est le code de la catégorie.

Nombre de jours marqués par catégorie

La fonction NB.SI() pourra ici jouer son rôle. Si je veux connaitre le nombre de jours marqués ”M”, la fonction suivante suffit :

=NB.SI(PLAGE_GRILLE ; ”M”)

Jours pour une entité particulière

Nous limitons la formule à la ligne de l’entité. Pour l’entité en ligne 5, nous aurons la formule :

=NB.SI(G5 :G371 ; ”M”)

Il est bien sûr possible de limiter à une période donnée.

=NB.SI(C12 :G19; ”M”)

Jours pour une entité et une catégorie

Il est possible de réaliser la fonction NB.SI() sur une seule ligne du planning et pour les colonnes qui nous intéressent.

=NB.SI(C12 :C19; ”M”)

Il est aussi possible d’utiliser les fonctions d’ensemble d’Excel comme SOMME.SI.ENS() ou NB.SI.ENS().

Sur les version de Microsoft Office inférieures à la 2007, vous pouvez utiliser la fonction SOMMEPROD().

Identifier une période

Un employé a été absent 3 jours consécutif pour une formation.
Je souhaite retrouver les dates de début et de fin de cette période.

Le mieux est de coupler une fonction EQUIV() qui cherche dans la ligne de l’employé la colonne contenant la catégorie avec la FONCTION DECALER() pour retrouver la date en haut du planning.

Avec ce système, je récupère la première cellule qui contient notre code catégorie.
Pour trouver les cellules suivantes ou du moins la dernière de la série, les choses se compliquent.

S’il existe plusieurs séries avec le même code de catégorie pour le même employé (il a été deux fois en formation à des périodes différentes), le problème s’amplifie.

J’élude volontairement le cas où la série est coupée par un week-end ou un jour férié.
Comment lui dire que ce ne sont pas deux séries distinctes mais une rupture de la série ?
Car rien ne me dit si ce ne sont pas effectivement deux séries différentes.

Compter le nombre de périodes

Mon employé est absent deux fois comment déterminer ce nombre avec une formule ?
Je n’ai même pas envie de savoir.

Filtrer

Les « Filtres automatiques » fonctionnent correctement.

L’ergonomie est réduite puisqu’il faut dans le planning se positionner sur la bonne date pour réaliser le filtre sur une catégorie.

De plus, il faudra répéter le filtre sur l’ensemble des dates de la période.
Si la période contient dix dates, alors il faudra répéter le filtre dix fois et l’annuler autant de fois aussi.

Filtres avancés

C’est une fonctionnalité importante d’Excel pour filtrer finement les données et surtout pour récupérer une copie du résultat ailleurs.

Il devient facile de livrer une copie du résultat du filtre qui peut ne comporter que certaines colonnes du tableau. À noter aussi que les formules sont remplacées par leur valeur dans le résultat du filtre.

C’est en tout cas une méthode plus élégante que de masquer les colonnes et ligne inutiles, de copier cette zone et d’en coller la valeur ailleurs.

filtre avancé sur excel.
Figure 16 : Utiliser les filtres avancés d'Excel sur notre planning.

Vous trouverez cette fonction dans l’onglet « Données », l’icône appelée « Avancé » à droite de l’icône « Filtrer ».

Des critères limités

Les libellés des critères sont des dates et on ne peut récupérer que la catégorie ou les informations de l’entité. C’est pauvre !

Comment réaliser la requête qui demande de récupérer les informations entre deux dates ?

Une extraction tout aussi bridée

Je ne peux récupérer que le code de la catégorie.

Limites

Ce fonctionnement est idéal tant que le planning est considéré comme un simple pense-bête.

Si les besoins en gestion de planning sont plus importants, des limitations vont vite apparaître.

Convivialité

Il faut prévoir 365 voire 366 colonnes pour une année complète.

Gérer la longueur du planning

C’est difficile de suivre un long tableau, alors l’utilisateur va passer beaucoup de temps à afficher/masquer des colonnes.

Créer des affichages

Quand on masque régulièrement les mêmes colonnes il est intéressant d’enregistrer cet affichage et Excel le permet.

  • Je pourrais avoir un affichage avec toutes les colonnes.
    Et voir l’ensemble de l’année.
  • Prévoir un affichage avec 7 colonnes.
    Soit une semaine.
  • Un autre pourrait afficher 31 colonnes pour un mois.

Attention, la commande suivante est grisée (inaccessible) si vous avez créé des « Tableaux croisés dynamiques » ou utilisez le menu « Mettre sous forme de tableau ».

La commande se trouve dans l’onglet « Affichage » et c’est l’icône »Personnalisé ».

affichage personnalisé excel.
Figure 17 : Utiliser les affichages personnalisés.

Commencez par crée un affichage (par exemple annuel) avec toutes les colonnes affichées en cliquant sur le bouton « Ajouter… ».

créer un affichage personalisés avec excel.
Figure 18 : Créer des affichages pour le planning.

Ensuite masquez les colonnes inutiles.
Et enregistrez un nouvel affichage (par exemple semaine).

Pour passer d’un affichage à l’autre :

  • se rendre dans le menu « Affichage » => « Personnalisé ».
  • choisir l’affichage à appliquer.
  • Cliquer sur le bouton « Afficher ».
    Ou faire un double-clic sur le nom de l’affichage.

C’est quand même plus confortable que de jongler en permanence avec le masquage des colonnes.

Contrôle d’écriture

S’il est possible de restreindre le contenu des cellules à l’aide des « Validations de données », il est vite fait de saisir dans la mauvaise cellule et principalement un jour de week-end ou jour férié.

Si je ne peux pas empêcher l’accès à certaines cellules, sauf par un travail manuel fastidieux, je peux identifier une partie de ces zones.

Identifier les week-ends

Avec la fonction JOURSEM(), il est possible de savoir si une date tombe un lundi, mardi, etc.

Elle retourne un nombre qui représente le jour. Le premier jour de la semaine prend la valeur 1, le second jour la valeur 2 et ainsi de suite.

La fonction JOURSEM() prend deux paramètres

Date à calculer

Elle a besoin en premier de la date dont on souhaite retrouver le jour de semaine.

Mode de calcul

Le second paramètre est un code numérique.

Il précise comment déterminer le premier jour de semaine.

Si le paramètre est 1 alors le premier jour de la semaine sera un dimanche. Dans ce cas Dimanche vaut 1 et samedi 7.

Si le paramètre est 2, la semaine commence lundi. C’est le système utilisé en France. Dans ce cas, Lundi prend la valeur 1 et Dimanche la valeur 7.

utiliser la fonction excel joursem.
Figure 19 : Utiliser la fonction Excel JOURSEM.

Dans la capture, le 13 janvier 2018 est un samedi, et il porte la valeur 6, le 14 est bien un dimanche de valeur 7, et le 15 janvier est un lundi puisqu’il prend la valeur 1.

Mise en forme conditionnelle

Il devient facile maintenant de faire une mise en forme pour identifier le week-end.

Il suffit que le résultat de la fonction JOURSEM() soit supérieure à 5.

mise en forme conditionnelle et joursem.
Figure 20 : Identifier les week-ends avec JOURSEM.

On identifie bien les jours marqués le week-end.

Identifier les jours fériés

Pour les fériés, c’est plus compliqué car il n’y a pas de séquence logique.

L’utilisateur la plupart du temps, fera une mise en forme manuelle tous les ans.

C’est une des lacunes à combler dans l’évolution de notre planning.

Vision d’ensemble

Si une entité courre à cheval sur deux plannings, il devient difficile d’avoir une vision complète de la vie de l’entité.
Il faudra récupérer des données dans d’autres classeurs et souvent c’est le règne du copier/coller sauvage ou des macros douteuses.

Maintenance

Bascule annuelle

Il faut générer un planning pour la nouvelle année et récupérer dedans les périodes encore en cours de l’ancien planning.

C’est peu ergonomique et les risques d’erreurs sont plus importants.

Structure du tableau

Entêtes de colonnes

Il n’est jamais bon que les entêtes de colonnes soient des données comme ici les dates de l’année.

Pour commencer mon tableau n’a pas un nombre fixe de colonnes.
Les années normales, j’ai 365 dates, et les années bissextiles 366.

Un exemple parlant

Imaginons un tableau qui stocke une fratrie.

Dans ce tableau je créé une première colonne pour le prénom du premier enfant, puis une seconde pour son nom, enfin une troisième pour avoir sa date de naissance.
Je pourrais ainsi rajouter pour un enfant beaucoup d’autres informations.

Je répète cette séquence de colonnes pour pouvoir conserver douze enfants par famille. Cela me donne un tableau de 12 enfants multipliés par 3 colonnes soient 36 colonnes.

Tableau avec les données en entete de colonne.

Je peux vite me retrouver avec un tableau gigantesque plutôt difficile à manipuler.

Nous verrons des méthodes de construction plus efficaces, moins redondantes et mieux adaptées au tableur.

Pour l’heure, sachez simplement, que les données ne servent jamais d’étiquettes de colonnes.
Elles doivent se présenter en ligne (enregistrements).

Une information parcellaire

Avec un tel système, j’ai peu d’informations sur une période. Je connais uniquement la catégorie.

Pour une période, je peux avoir besoin d’informations complémentaires.

Nous finirons forcément par construire un tableau avec ces informations.

Il faudra trouver comment faire un lien entre le planning et la période. Et comment le faire ?
En commençant par rajouter les dates de début et de fin dans ce tableau.
De toute façon, cela va se compliquer par la suite, donc c’est une voie à abandonner.

Conclusions

Saisie pas sécurisée

Il est facile de saisir dans la mauvaise cellule.

Réalisation des calculs

Comme nous l’avons vu, les calculs restent pour certains simples à construire.

Pour d’autres par contre, il faudra au préalable prévoir un déplacement visuel pour identifier la plage à prendre en compte

Certaines formule seront délicates à mettre au point voire irréalisables sans passer par un traitement manuel pénible.

Ergonomie douteuse

L’utilisateur va passer un temps infini à masquer et afficher les colonnes. Nous avons vue qu’il est possible de régler ce problème avec les « Affichages personnalisés ».

L’identification des jours fériés peut être lourde à faire.

Quelques améliorations

Les dates peuvent être générées automatiquement et le calendrier devient plus facile à réutiliser.

Il est faisable de formater les catégories dans les cellules.

Il est possible d’identifier les jours de week-end à l’aide de la fonction JOURSEM().

Exploitation des données

Les filtres sont pauvres en possibilité de critères et de résultats.

Les filtres avancés bien pratiques n’ont aucune utilité avec un tel tableau si pauvre en informations.

De gros points noirs

Ne pas voir l’ensemble d’une période qui chevauche sur plusieurs plannings est une réelle préoccupation.

Se priver d’informations complémentaires pour une période est gênant d’autant plus que le besoin va vite apparaître.

Sous une apparence simplicité, beaucoup de difficultés à prévoir

En l’état ce planning est simple à construire et à renseigner.

Il devient vite impossible à exploiter et les possibilités du tableur sont sous-exploitées.

Réalisation de l’étude

Il est indispensable d’utiliser un traitement de texte pour réaliser ce document et de bien le connaître.

Mon cahier des charges peut allègrement atteindre sa centaine de pages. Les outils de mise en pages, structuration du contenu, opérations automatiques vont être d’une grande aide.

Il reprend l’ensemble de sa réflexion agrémentée d’images, schémas et tableaux.

Il identifie les manques et faiblesses de l’existant.

Il propose une solution adaptée en tenant comptes des contraintes et de l’environnement.

Version allégée

Il est possible de livrer une version allégée qui reprend les grandes lignes. Elle peut être diffusée à un plus large public sans les noyer sous une masse d’informations techniques non essentielles à la compréhension du projet.

Préconisations

Vous trouverez dans cette section, une sorte de résumé de cet article.

Il permet de vérifier que le projet reste clair, simple et réaliste.

Ce que nous avons appris

Des fonctions Excel importantes

Dans ce document nous avons aperçu certaines possibilités d’Excel essentielles.

  • Validation de données ;
  • Filtre avancés ;
  • Affichages personnalisés ;
  • Formats conditionnels ;
  • Les modèles de documents.

Des formules sont incontournables dans tout projet

  • Comptage
    • NBVAL ;
  • Fonctions de recherche
    • RECHERCHEV ;
    • EQUIV ;
    • INDEX ;
    • DECALER.
  • Calcul de regroupement
    • NB.SI – SOMME.SI ;
    • NB.SI.ENS – SOMME.SI.ENS – etc. ;
    • SOMMEPROD.

Les formules sur les dates sont utiles dans nombre de projets.

  • Calculs de dates
    • DATE ;
    • JOURSEM.

L’analyse d’un projet

Nous avons dégagé l’essentiel du superflu.

Nous avons vu que l’analyse fonctionnelle n’est qu’une partie d’un projet.

L’analyse structurelle défini la façon dont seront structurées les données.

Nous avons compris que les contraintes étaient bien plus conséquentes en mode multi-utilisateurs.

Il peut y avoir des tâches annexes chronophages en temps, voire des logiciels à prendre en main.

La notion de planning est bien cernée.

Les failles du modèle standard sont identifiées.

Ce que nous gardons

Le principe d’affichage en grille avec des dates en haut de la grille, les données à gauche et dans les cellules la catégorie de la marque.

La construction automatique des dates de la ligne de titre selon l’année choisie.

L’identification des week-ends à l’aide de JOURSEM().

Ce que nous jetons

La saisie des informations directement sur le planning.
Il sera rétrogradé au rôle d’affichage.

La création d’un nouveau planning chaque années.

Ce que nous ajoutons

Un autre support de stockage de l’information.

Un principe permettant de contourner l’obstacle des bascules annuelles.

Ce que nous perdrons

La simplicité d’utilisation.

Mais les opérations seront accessibles à un néophyte et peu nombreuses ou du moins toujours identiques.

Ce qu’il reste à faire

Dans le prochain article, j’aborderais l’analyse structurelle.

Nous verrons comment organiser nos tableaux et comment établir le dialoguer entre eux.

Ce sera l’occasion d’expliquer comment Excel conçoit un tableau et les choses à ne pas faire.

Ce sera le prérequis à des données fiables et exploitables.

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.

>