Tutoriel

Planning perpétuel : Les données

Gérer ses tableaux sur Microsoft Excel

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

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

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

Dans cet épisode, 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.

Solution proposée

Il existe plusieurs pistes possibles et d’autres incontournables, l’informatique ayant ses propres codes et usages.

Ces passages obligés sont le résultat de réflexions d’experts dans leur domaine. Ils sont éprouvés depuis longtemps et appliqués par l’industrie du logiciel.
Ce sont des règles simples qui s’apparentent plus à du bon sens qu’à de la science de haut niveau.

La mise en place pourra paraître fastidieuse, mais l’exploitation au quotidien sera plus riche que le planning actuel.

Importance de la donnée

Les données sont les fondations d’un projet informatique. Comme elles, construites sur des sables mouvants, elles vont fléchir un jour.

Toute la stratégie de l’entreprise repose sur les données et leur analyse.

Il peut être très compliqué de réorganiser des données mal agencées. Et surtout les risques d’erreurs sont importants.

Organiser les données

C’est la première chose à définir et je raisonnerais par pôles d’intervention.

Les erreurs, le lot quotidien

Excel est un logiciel permissif et l’utilisateur peut réaliser ses projets comme bon lui semble.

La contrepartie, c’est justement que l’utilisateur peut réaliser ses projets comme bon lui semble.

Selon « Raymond Panko », on trouve jusqu’à 30,00 % d’erreurs par feuille de calcul.

Il annonce aussi un taux d’une erreur toutes les 100 lignes dans un tableau.

Pour les douanes américaines, le taux d’erreurs se situe plutôt vers 11,00 %.

Pour « Coopers and Lybrand », 90,00 % des tableaux de plus de 150 lignes comportent des erreurs.

Le sujet est si préoccupant qu’un groupe d’étude existe, eusprig.

Une étude édifiante

En 2013, une étude universitaire remet en cause un rapport de « Carmen M. Reinhart » et « Kenneth S. Rogoff ».

Ce rapport servira de base de négociations pour l’octroi de budgets auprès du FMI.
Il est orienté selon les résultats issus de feuilles de calculs.

« Carmen M. Reinhart » et « Kenneth S. Rogoff » conviendront que leurs résultats sont faux mais que cela n’a eu aucune incidence sur les décisions prises. Elle sert à quoi l’étude alors ?

Les erreurs fréquentes

Erreurs de saisi

Saisir dans la mauvaise cellule

Il est facile d’écrire une donnée dans la cellule du dessous ou dessus, dans celle de gauche ou droite de son emplacement réel.

Valeur erronée

Frapper le nombre 1000 en lieu et place de 100, n’est pas si rare que cela.

Faire une erreur d’arrondi semble mineur, sauf si l’on parle en K€.

Erreurs de formule

Pointer la mauvaise cellule

Une formule peut faire référence à la mauvaise cellule comme pour la saisie.

Plage invalide

Une formule peut faire référence à un tableau, mais ce tableau dans le temps s’est agrandi sans que la formule soit mise à jour.

Valeur en dur

Une valeur, par exemple un taux, peut exister dans une formule.

Il est possible que dans la réalité, sa valeur soit différente depuis longtemps.

Qui va aller vérifier dans une formule créée il y a 5 ans, qui semble fonctionnée, à laquelle personne ne comprend rien et dont l’auteur c’est évanoui dans la nature ?

Erreur d’arrondis

Un arrondi mal appliqué peut être catastrophique s’il implique de grands nombres.

Erreurs de logique

Une formule peut oublier un cas particulier, un test peut s’avérer faux.

Manque de contrôle

Les classeurs sont vérifiés à la conception et ils sont approuvés à vie.

Personne ne remet jamais en cause les feuilles de calculs, pourtant une entreprise ça bouge.

Une prise de conscience

Nous sommes ici au cœur de ce qui motive cette série d’articles.

Derrière les chiffres, tableaux, formules, macro-commandes, se cachent parfois des millions d’euros.

Messieurs, Mesdames les dirigeant(e)s, il serait temps de se pencher sérieusement sur l’emploi de la bureautique en entreprise.

Ça ne s’improvise pas, l’informatique peut être complexe sous une apparente simplicité.
Or trop souvent c’est une activité pratiquée en dilettante au sein de l’entreprise.

C’est un outil qui n’a pas l’attention qu’il nécessite :

  • L’information est saisie à la va vite par un opérateur.
    • Aucun contrôle n’est effectué.
  • Un chef de service va mettre en place des indicateurs.
    • En se basant sur des résultats faux.
  • Un décideur prendra des décisions stratégiques.
    • Sur la base d’analyses douteuses.

Analyse structurelle

C’est ici que je vais définir comment agencer mes tableaux et les informations qu’ils doivent contenir.

Dans un premier temps, j’identifie les données en jeu et les regroupe selon l’affinité qu’elles présentent.

Je vais réunir les données de même nature dans des groupes que j’appelle des pôles.

Pôles d’interventions

Les rubriques du planning sur Excel.

J’en ai détecté trois :

Entités

Ce sont les données à associées à des périodes.

Un tableau Excel, un projet.
  • Ce sont des informations sur l’employé pour de la gestion d‘absences ou autre.
  • Le planning peut gérer des listes d’affaires, de chantiers, autres projets, etc...

Selon la nature de cette entité, les informations diffèrent.

  • Pour un employé son nom, prénom, date de naissance, etc.
  • Pour un chantier son adresse, la nature, peut être un budget, etc.

Les entités représentent un projet

Il peut y avoir autant de planning (classeurs) que d’entités.

  • Gérer des absences.
  • Suivre un projet de construction.
  • Planifier un échéancier.
  • Etc.

Catégories

Le contenu des catégories dépend de la nature du projet.
Définir des catégories.
  • Pour des absences
    • Maladie ;
    • Congé ;
    • Formation.
  • Pour des projets
    • Étude de faisabilité ;
    • Prise d’informations ;
    • Prise de décisions ;
    • Choix des fournisseurs.

Une catégorie est identifié par :

  • un caractère (code) ;
  • un libellé ;
  • Le code de la couleur associé (optionnel).

Le pôle des catégories sera identique d’un projet à l’autre en ce sens que nous aurons toujours les mêmes informations contrairement aux entités.

C’est le contenu des rubriques qui changera.

Périodes

Une entité est occupée sur une ou plusieurs périodes.

Définir des catégories.

Une période possède :

  • L’entité concernée (bien sûr) ;
  • Une date de début ;
  • Une date de fin ;
  • Une catégorie ;
  • Un libellé (optionnel) ;
  • Etc.

Le pôle des périodes sera toujours identique d’un planning à l’autre. Il peut contenir d’autres colonnes d’informations ou calculées.

Organisation en tableaux

Les données seront organisées en tableaux. C’est la méthode la plus efficace pour conserver l’information.

Organisation en feuilles

Parfois, je trouve des classeurs où l’information est conservée dans des feuilles (par exemple un onglet par client ou par mois).

Je parle bien ici de la saisie des données. Il peut bien sûr y avoir des feuilles spécialisées, mais elles tireront les informations essentielles de nos tableaux.

Cette organisation est lourde, inexploitable et peu ergonomique.

Avec le principe d’une donnée égale une feuille, les formules sont complexes, car il faut les retoucher chaque fois qu’une feuille est rajoutée ou supprimée.

Organisation en classeurs

Au lieu de faire une feuille par client ou par mois, on fait un classeur par personnes ou mois.

Les conséquences sont les mêmes que pour le principe de feuilles mais en multipliant encore les contraintes.

Les tableaux

Avantages

Toute l’information est regroupée au même endroit.

Les traitements s’en trouvent allégés et les possibilités offertes sont plus nombreuses.

Ainsi pour synthétiser des données, plus besoin de chercher dans un grand nombre de feuilles.

Excel possède de précieux atouts pour manipuler et analyser les tableaux.

Un obstacle balayé

Comme toute l’information est disponible au même endroit, nous réglons le problème de suivi et de bascule annuelle.

Un simple filtre suffit à voir l’ensemble d’un projet.

Inconvénients

Les tableaux peuvent devenir volumineux et ralentir le fonctionnement d’Excel.

Avec le temps il faut prévoir de supprimer de vieilles données et les stocker dans un tableau historique dans un autre classeur.

Contenu des tableaux

Je vais présenter quelques notions sur l’organisation des données en tableaux.

Ce sont des règles importantes et qui s’appliquent à n’importe quel tableau (et pas seulement sur les tableurs).

Atomiser

C’est un terme barbare pour dire une chose simple. Je pourrais traduire par spécialisation.

Prenons une cellule qui contient le nom et le prénom d’une personne, ou son code postal et sa ville.

Qui me dit que l’information est donnée selon le modèle préconisé, disons le nom puis un espace et enfin le prénom.

Des cellules qui ne sont pas atomisées.

Si la personne s’appelle Louise Michel, qui m’assure que le nom c’est Louise ? Surtout que c’est Michel !

Il est préférable de conserver le nom ou le code postal dans une cellule et le prénom ou la ville dans une autre.

Des cellules atomisées.
Jusqu’où atomiser ?

Tout dépend le besoin.

  • Un statisticien détaillera une adresse jusqu’au numéro de l’étage.
  • Un artisan se contentera de l’adresse du chantier.

Malgré tout, nous considérons le code postal, la ville, voire la boite postal, etc. comme des données séparées.
L’entité adresse atomisée ou pas présente les informations suivantes :

  • Le numéro ;
  • Le type de voie (avenue, boulevard,…) ;
  • Le corps de l’adresse ;
  • Le numéro ou nom du bâtiment (si pertinent) ;
  • L’étage bâtiment (si pertinent) ;
  • Le numéro d’appartement bâtiment (si pertinent).

On peut trouver d’autres informations, mais nous sortons légèrement du cadre d’une adresse pour décrire le lieu :

  • Éventuellement un code de porte ;
  • Le type de logement (villa, appartement,…) ;
  • Etc.
Contrôle des données

Il y a plus de chance que le nom soit en bonne place puisque une colonne est clairement identifiée.

Je peux m’assurer que la colonne des codes postaux reçoit bien 5 chiffres à l’aide des « Validations de données ».

Filtres optimisés

Les filtres sont simplifiés et m’offrent plus de possibilités.

Quand tout est dans la même cellule, il faut passer par les filtres textuels et choisir une règle comme « Qui commence par… », ou, « Se termine par… ».

Des filtres avec des cellules non atomisées.

Quand l’information est dans deux cellules, il suffit de choisir la valeur dans la liste du filtre.

Des filtres avec des cellules atomisées.
Dissocier l’information

Il est difficile par formule ou par le menu « Données » => « Convertir… », d’extraire le nom du prénom. Il peut y avoir des noms compliqués avec des prénoms composés, des particules, des noms de jeune fille.

Avec deux cellules, c’est un jeu d’enfant de réunir les deux informations avec l’opérateur de concaténation « & » ou la fonction CONCATENE().

Concatener des cellules.

En informatique, il est toujours plus simple de réunir l’information que de tenter de la dissocier.

Cohérence de l’information

Outre le contrôle des données, l’atomisation m’ouvre la voie à une seconde règle importante, le dé-doublonnage qui va m’assurer qu’une valeur sera écrite toujours à l’identique.

Trop souvent, je vois des tableaux où la ville « Toulouse » est écrite de plusieurs façon tel que : toulouse – toul – toul. – Tls – tlse – etc.

Colonne non dedoublonne.

Comment réaliser des analyses fiables dans ces conditions ?

Le dé-doublonnage

C’est l’art de supprimer les valeurs saisies plus d’une fois. Une valeur saisie plusieurs fois à des chances d’être écrite avec plusieurs orthographes.

Là aussi, il existe un adage simple, une donnée ne sera écrite qu’une fois, si on en a besoin ailleurs, on la rappelle.

Si la valeur est erronée, il n’y a qu’un endroit où modifier.

La valeur est écrite partout à l’identique.

Comment dé-doublonner

Prenons le cas des villes. Dans le tableau des personnes, la même ville apparaît plusieurs fois.

Il est donc possible qu’elle ne soit pas écrite toujours pareil.

Je construis un tableau où la ville n’existe qu’une fois. Remarquez qu’il peut y avoir d’autres colonnes qui prolongent ce tableau.

Dans mon tableau des personnes, je peux poser une « Validation de données » sur la colonne des villes.
Je demande d’autoriser les valeurs qui proviennent d’une liste, et pour remplir la liste, je choisi la colonne des libellés du tableau des villes.

Validation de donnees et dedoublonnage.

Si le tableau des villes est absent, mais que celui des personnes existe, à l’aide du menu « Données » => « Supprimer les doublons », il est aisé de récupérer cette liste.

Avantage induit

Je vais reprendre mon tableau de contacts. Dans ce tableau, j’ai une colonne avec la ville.

Un tableau excel possedant des doublons.

Je voudrais savoir ce que représentent mes contacts par rapport à la population de la ville.

Cela m’impose de connaître la population par ville.

Il n’est pas envisageable d’écrire cette information dans le tableau des contacts.

Chaque fois qu’une ville apparaît, il faut écrire à nouveau cette valeur.

Un tableau excel avec la population.

Dans le tableau des villes, il suffit de rajouter une colonne avec sa population.

Population déportée dans le tableau excel des villes.

Mon nouveau tableau des villes, m’ouvre des horizons. Je peux l’alimenter en colonnes et ainsi accroitre mes possibilités d’analyses.

De plus, j’ouvre un dialogue entre les deux tableaux.

À l’aide des fonctions de recherche comme la RECHERCHEV(), je récupère dans les contacts des informations sur les villes.

C’est pour cette raison que la fonction RECHERCHEV() est si importante sur Microsoft Excel car elle établit un lien entre plusieurs tableaux.

dedoublonnage et recherchev.

Dans les villes, je peux réaliser des calculs de regroupement issus des données des contacts comme par exemple le nombre de contacts par ville.

dedoublonnage et somme.si.

Des fonctions comme les NB.SI(), SOMME.SI(), etc. seront d’un grand secours.

L’unicité

Que se passe-t-il si j’ai deux villes qui portent le même nom ?

unicite et Excel.

Ma RECHERCHEV() prendra toujours la première occurrence.

unicite et recherchev dans Excel.

De toute façon rien ne me dit laquelle je souhaite récupérer.

Là aussi il existe une technique éprouvée pour pallier cet inconvénient.

Postulat
  • Dans tous les tableaux, il faut pouvoir identifier un enregistrement sans équivoque, afin de pouvoir récupérer des informations de ce tableau dans un autre.
  • Tout tableau doit posséder une colonne qui ne contiendra aucun doublon. Toutes les données seront uniques dans cette colonne.
  • Si de façon naturelle aucune colonne ne répond à cette exigence, le concepteur rajoutera une colonne dans laquelle il s’assure qu’il y a toujours une valeur unique.

Il est aussi possible de faire l’unicité à l’aide de plusieurs colonnes par exemple Nom + Prénom + N° de portable.

Il faut donc renseigner trois colonnes pour retrouver les informations sur cette personne.

Je ne conseille pas cette technique lourde et ne l’ai jamais mise en œuvre dans un projet concret.

Notion de clé

Cette colonne particulière porte le nom spécifique de « clé primaire ». Elle est aussi appelée identifiant ou code.

Au plus simple, elle comporte un nombre incrémenté de 1 à chaque insertion de lignes.

Plus souvent elle est construite selon un algorithme spécifique à l’usage comme un numéro de sécurité sociale ou un code client.

notion de clef primaire dans Excel.

Pour les communes françaises, c’est le code INSEE qui joue ce rôle.

La plupart des fichiers issus de l’informatique de l’entreprise et exportés dans Microsoft Excel, possède une colonne similaire.

Maintenant ma zone de liste dans le tableau des contacts, reprend ce code et non plus le libellé.

clef primaire et validation de donnee dans Excel.

Pour récupérer le nom de la ville, il faut le récupérer comme la population.

clef primaire et recherchev dans Excel.

Pour les tableaux ne possédant pas cette colonne, un numéro incrémenté fera l’affaire.

Même en cas de suppression de données, ce numéro ne changera jamais. Une bonne clé primaire est pérenne dans le temps.

numero auto dans Excel.
Un inconvénient gênant

Maintenant, dans le tableau des contacts, c’est le code de la ville qu’il faut insérer et le nom de la ville est récupéré avec une des fonctions de recherche intégrées de la même manière que la population.

Du coup, notre liste de choix est peuplée avec des codes peu explicites.

Une solution est de faire une colonne calculée dans le tableau des villes qui concatène le code INSEE et le nom de la ville et d’en faire la colonne source de la liste de choix.

Liste de choix et clef primaire dans excel.

Si cette colonne est au début du tableau des villes, alors la fonctions RECHERCHEV() suffira sinon il faudra se tourner vers des fonctions comme INDEX(), EQUIV(), etc.

Retrouver la ligne d’une valeur

La fonction EQUIV() permet de trouver la ligne où se situe une valeur dans un tableau.

fonction equiv dans excel.
Retrouver une valeur sur cette ligne

La fonction INDEX() retrouve une valeur à une ligne donnée.

L’avantage en rapport avec la RECHERCHEV() c’est que la colonne de recherche n’est pas obligatoirement la première de la sélection.

fonction index dans excel.
Réunir des fonctions

Quand je construis des formules imbriquées, comme ici, dans un premier temps, je les écris dans des cellules différentes.

En procédant ainsi, elles sont plus faciles à mettre au point et à vérifier.

Ensuite, je les réunis par copie.

reunion de fonctions dans excel.

Des relations

Grace au code des communes, j’ai établi un lien entre les deux tableaux, dans mes contacts, je récupère des informations sur les villes.

On parle de relations entre tables. L’ensemble de toutes les relations entre les tableaux est appelé schéma relationnel.

Un à plusieurs

Une commune du tableau des villes peut exister plusieurs fois dans le tableau des contacts.
En effet je peux avoir de 1 à plusieurs contacts dans la même ville.

C’est une notion importante qui me précise ce que je dois mettre dans mes tableaux.

Quand je dis une ville apparaît sur plusieurs contacts, alors j’aurais une table ville et une avec les contacts.

Si je dis un immeuble possède 1 ou plusieurs appartements, c’est que j’ai une table immeuble, et une table appartement
Le lien est établie entre les deux au travers du code attribué à l’immeuble.

Un client peut passer plusieurs commandes. J’ai mes tables clients et commandes relié par le code client.
Mais la commande se composent de lignes de commandes (nombre d’articles différents achetés). Ainsi j’ai une table Ligne de commandes reliée à la table commande par le numéro de commande.

Vous devez toujours raisonner de cette manière pour déterminer le contenu des tableaux.

D’autres relations

Il existe d’autres organisations relationnelles comme les relations de un à un ou de un à plusieurs. Mais ces formes sont inutiles dans notre projet de planning.

Il y a d’autres modèles plus complexes et difficiles à mettre en place sur Excel.

Stockage physique

Mon planning va gérer des absences en entreprise.

Ma table des entités concerne donc des employés, ma table des périodes les absences, enfin les catégories représentent la raison de l’absence (formation – maladie – congés – etc.).

Terminologie

Il existe un langage pour désigner les tableaux en gestion de données. Microsoft Excel emploi certains de ces mots.

Table

Un tableau s’appelle aussi une table. Microsoft Excel emploi aussi le terme de « Base de données ».

Champ

Une colonne d’un tableau porte le nom de champs, le champ nom, le champ prénom, etc.

Nom de champ

Le libellé en haut d’un champ est appelé nom du champ, Excel utilise le mot d’étiquette.

Enregistrement

Nous parlerons d’enregistrements pour évoquer les lignes du tableau.

Donnée

La cellule à l’intersection du champ et de l’enregistrement s’appelle la donnée.

Positionnement des tableaux

Dans des classeurs différents

Ce serait la solution idéale, mon tableau des employés isolé dans un classeur permettrait d’utiliser toujours le même classeur quand j’ai besoin de ses données.

Les problèmes arrivent au moment où il faut transmettre le document à une personne tierce. Il faut transformer les formules qui font références au classeur source pour les remplacer par leur résultat ou alors il faut aussi livrer le classeur des employés et reproduire l’arborescence du poste d’origine.

Il existe aussi des assistances dans Excel en utilisant les connections à des sources de données.

Sur la même feuille

C’est possible pour de petits tableaux et en petit nombre.

À l’usage, des limitations se font vite sentir avec des tableaux conséquents.

Sur des feuilles différentes

C’est la solution que je privilégie pour notre planning.

C’est un compromis acceptable, un tableau par onglet dans le même classeur, même si cette solution n’est pas satisfaisante.

Le projet est livré à but pédagogique. Il est plus simple pour mes lecteurs d’avoir un seul fichier à télécharger.
Pour un projet professionnel, je réviserais sûrement ce jugement.

L’essentiel sur Microsoft Excel

Un tableau au sens d’Excel

Définition, d’un tableau

Un tableau est un principe de données organisées en lignes et en colonnes.

Sens de lecture

Excel lit un tableau de haut en bas et de gauche à droite.

Dans les formules et fonctionnalités qui s’adressent à un tableau, Microsoft Excel respecte ce postulat.

Incidence

Pour Excel chaque donnée est conservée dans une colonne. La colonne contient donc toujours le même type d’information.

Ainsi une colonne recevra le nom des personnes tandis qu’une autre un chiffre d’affaire.

Tableau inversé

Un tableau où les données sont en lignes, ne satisfait pas Excel.

Tableau structure avec les donnees en colonne.

Un simple filtre est inutilisable car le tableur ne permet pas d’indiquer que le tableau doit être lu dans l’autre sens.

Transposer

Une commande existe pour retourner un tableau.

Tout d’abord, copiez votre tableau.

Ensuite à l’emplacement où réaliser la copie, choisir de faire un collage spécial.

collage transposer sur le tableur.

Comme type de collage sélectionner l’option « Transposer ».

Des étiquettes

Pour Excel, la première ligne du tableau est une ligne de titre. Elle identifie le rôle de la colonne.

Excel appelle l’information de cette cellule une étiquette, là où en terminologie base de données on parle de Nom de champs.

Rôle des étiquettes

Excel n’oblige en rien à commencer un tableau avec une ligne de titre et un tableau peut commencer par une ligne de données.

Pourtant, nombre d’opérations les utilise.

Sans étiquettes, vous ne pourrez pas réaliser de « tableau croisé dynamique » ni de filtres avancés comme nous l’avons vu au chapitre précédent.

Les fonctions de bases de données seront inopérantes. Ces fonctions permettent d’analyser des données d’après des critères. Comme toutes opérations avec critère, elle demande un nom de colonne.

Données et présentation

Souvent pour l’utilisateur, le tableau réponds à tous les besoins aussi bien de stockage d’informations, de lieu d’analyse, de présentation.

Construire un tableau en privilégiant l’aspect visuel est une erreur.

En règle générale, il faut dissocier le stockage de l’information et son affichage.

Un bon exemple de ce principe c’est la conception de « Dashboard » qui va piocher des informations dans les tableaux.

Tableau de bord (dashboard).

Zone homogène

Pour Microsoft Excel, un tableau est une zone d’un seul tenant. Excel emploi plus précisément le terme de plage pour un ensemble de cellules contigües.

Pour qu’une plage existe, il faut donc qu’il y ait toujours une continuité dans les lignes et colonnes même si certaines cellules peuvent être vides.

un tableau excel homogene.

Une plage s’arrête quand le tableur rencontre une ligne ou une colonne vierge ou si nous sommes aux limites de la feuille comme la ligne 1 et/ou la colonne 1.

Ligne de rupture

Certains utilisateurs pour faciliter la lecture, insèrent une ligne vierge dans les tableaux.

un tableau excel avec insertion de ligne de rupture.

Comme je viens de le montrer, c’est une habitude à perdre.

De plus les tris deviennent le prétexte à manipulation inutiles.
Les lignes vierges vont se regrouper, il faut les supprimer et ensuite les refaire.

Calculs intermédiaires

De la même manière, il arrive de trier sur une colonne précise. Chaque fois que l’information de cette colonne change, on fait la somme des données du dessus.

un tableau excel avec insertion de ligne de calculs.

Le problème avec le tri est identique aux lignes vierges, mais en plus, il faut refaire les calculs.

Sous-totaux

Un autre élément m’incite à ne pas insérer de telles lignes de calculs. Excel possède une fonctionnalité dédiée à cette tâche, les « Sous-totaux ».

En premier lieu, il faut trier le tableau sur la colonne de rupture comme trier sur les noms de personnes.

Ensuite en restant dans le tableau, on lance le menu « Données » => « Sous-total ».

fenetre des parametre des sous total.

On commence par donner la colonne de rupture, puis le calcul à réaliser, et enfin la ou les colonnes sur la ou lesquels réaliser le calcul.

Excel rajoute tout seul une ligne de calcul à chaque changement de personne.

Il met aussi en place un plan pour réduire le tableau.

resultat des sous totaux.

Pour retrouver son tableau d’origine, retourner dans le menu « Sous-total » et cliquer sur le bouton « Supprimer tout ».

Ligne de totalisation

Souvent nous rajoutons une ligne de totaux en bas de colonnes.

Elle est souvent formatée différemment du reste du tableau.

Je déconseille de coller cette ligne aux données du tableau.
En cas d’exportation par exemple, cette ligne va suivre comme si c’était des données comme les autres.

Total colonne sur excel.

Il est préférable de laisser au moins une ligne vierge avant celle-ci.

Total colonne isolé du reste du tableau.

Fusion de cellules

Les fusions dans un tableau c’est la plaie. Le tableau devient impossible à restructurer. L’information n’est plus cohérente.

fusion de cellule dans un tableau.

Les fusions de cellules dans les tableaux sont tout bonnement à proscrire.

Sélection d’une zone

Manuelle

En générale, l’utilisateur part de la première cellule du tableau et glisse la souris vers la fin du même tableau.

Sur de longs tableaux, l’opération débute lentement puis la souris s’emballe et il devient difficile de s’arrêter au bon endroit.

Je dirais qu’en partant du bas du tableau et en remontant, on s’arrêtera forcément à la première ligne de la feuille.

Automatique

Il existe un raccourci clavier bien pratique. En appuyant simultanément sur les touches [Ctrl] + [*], Excel va sélectionner la plage en cours.
Rien qu’à elle seule, cette possibilité plaide en faveur de tableaux bien structuré.

Quand on lance une commande du logiciel qui nécessite l’usage d’une plage, si une seule cellule de cette plage est sélectionnée avec de lancer la commande, alors Excel sélectionnera tout seul la plage.

Ainsi pour réaliser un tri, si une cellule de la colonne sur laquelle porte le tri est sélectionnée, alors Excel sélectionnera le tableau pour moi et lancera le tri.

Zone isolée

S’il ne doit pas y avoir de rupture au sein du tableau, à contrario, une séparation est nécessaire entre le tableau et d’autres informations présentes sur la feuille.

excel reconnait le filtre quand le tableau est isole des donnees.

En cas contraire Excel ne reconnait plus le tableau et sélectionne une mauvaise plage.

excel perd le filtre si des donnees touchent le tableau.

Catégories d’étiquettes

Souvent le besoin se fait sentir de rajouter une ligne de titre au-dessus de la ligne d’étiquettes.

Excel comprend ce fonctionnement, mais dans certaines circonstances il peut être gênant.

Je préfère isoler cette ligne du reste du tableau en insérant une ligne entre celle-ci et la ligne d’étiquettes. C’est d’autant plus important de l’isoler que souvent cette ligne est fusionnée.

inserer une ligne de categorie au-dessus des etiquette.

Ensuite je masque cette ligne vierge pour donner l’impression d’un unique tableau.

masquer uneligne sur excel.

Positionner plusieurs tableaux

Il n’est pas conseillé de produire plusieurs tableaux sur la même feuille.

Pour les manipuler confortablement, il est mieux de les décaler l’un de l’autre par une ligne et une colonne vierge.

plusieurs tableaux sur une feuille.

Ainsi on peut supprimer et insérer dans un tableau en étant sûr de ne pas toucher la structure des autres.

Les noms de cellules

J’aborde ici les fondements des cellules nommées.

Le but est de substituer une adresse d’une cellule ou d’une plage par un nom évocateur.

Une formule avec des noms

Un calcul simple

Imaginons une cellule d’adresse A1, je possède un montant.

La cellule B1 contient un taux quelconque.

En C1, je multiplie le contenu de la cellule A1 avec celui de B1.

formule avec adresse de cellule.

J’obtiens la formule « =A1 * B1 ».

Remplacer avec un nom

Ici A1 et B1 ne m’indiquent pas vraiment sur quoi porte le calcul. La formule est simple et fait référence à la même feuille, mais pour une formule plus compliquée, il devient ardu de la comprendre.

La formule pourrait se transformer en quelque chose de plus parlant : « =MONTANT * TAUX ».

formule avec nom de cellule.

Le nom « MONTANT » représentant la cellule A1 et le nom « TAUX » l’adresse B1.

La barre d’adresse

La barre d’adresse en haut à gauche de la feuille de calcul affiche l’adresse de la cellule sélectionnée.

barre d'adresse du tableau.

Utiliser la barre d’adresse

Il est possible de saisir directement une adresse dans cette zone et d’appuyer sur la touche [Entrée]. Excel nous positionne sur cette adresse.

En écrivant la lettre « C » (majuscule ou minuscule), Excel sélectionne la colonne où se trouve le curseur et avec la lettre « L » (majuscule ou minuscule), la ligne.

En positionnant le curseur à droite de la petite flèche, vous pouvez redimensionner cette zone.

Créer un nom

La première étape consiste à sélectionner la ou les cellules qui doivent porter ce nom. Je sélectionne donc ma cellule A1.

Ensuite, direction la barre d’adresse et là saisir le nom à donner à la sélection. Je nomme ma cellule « MONTANT ».

barre d'adresse avec un nom.

Attention : Il faut valider avec la touche [Entrée] et non pas en cliquant dans la feuille.

Caractères interdits

Les caractères accentués, les noms réservés par Excel sont interdits ainsi que l’espace.

Le plus adapté est d’écrire en majuscule avec l’underscore [_] pour simuler l’espace.

Utilisation basique des noms

Quand la cellule qui contient le nom est sélectionnée, la barre d’adresse affiche le nom et non plus l’adresse.

La barre d’adresses est une liste avec une petite flèche à sa droite pour la dérouler ce qui permet de voir l’ensemble des noms existants.

liste des nom dans la barre d'adresse.

Sauter vers un nom

En sélectionnant un nom de la liste, Excel va sélectionner la zone sous-jacente au nom.

C’est donc un moyen pratique de naviguer dans le classeur.

Nom de cellule et formules

Après avoir saisi le symbole égal (=) ou plus (+) ou moins (-), Excel affiche dans la barre d’adresses la liste des fonctions intégrées.

liste des fonctions integrees dans la barre d'adresse.

Pour faire dans une formule référence au nom plusieurs techniques sont possibles. Elles se réalisent pendant l’édition de la formule.

  • Il est bien sûr possible de saisir le nom à la main.

Sélection de cellule

Sélectionner la ou les cellules concernées par le nom.

Excel inscrira dans la formule le nom et non pas l’adresse.

liste des fonctions integrees dans la barre d'adresse.

Liste des noms

Dans l’onglet « Formules », vous trouverez l’icône « Utiliser dans formule ». En déroulant la liste vous aurez l’ensemble des noms connus.

Il suffit de choisir celui à insérer.

liste des fonctions integrees dans la barre d'adresse.

Gérer les noms

La gestion des noms se réalise dans l’onglet « Formules » et l’entrée « Gestionnaire de noms ».

gestionnaire de nom.

En sélectionnant le nom, on peut le modifier.

Supprimer un nom

La commande est sur la boite de sélection des noms.

message d'erreur #NOM

Attention : Quand un nom est supprimé, Microsoft Excel ne le remplace pas dans les formules par l’adresse associée. Vous recevrez dans ces cellule le message d’erreur #NOM.

Modifier l’adresse

Il suffit dans la zone du bas de d’effectuer une autre sélection.

Modifier un nom

Là aussi rien, de plus simple, il faut modifier le nom dans la boite et valider.

Juste une remarque. Vous pourriez penser que sélectionner la plage de cellules d’origine et redonner un nom dans la barre d’adresse fera l’affaire.

Vous ne faites qu’ajouter un second nom à la sélection et Excel continuera toujours d’utiliser le premier nom créé.
Donc toujours passer par la fenêtre des noms pour le changer.

Portée des noms

Il existe deux types de noms

Niveau de portée feuilles

Un même nom peut-être créé dans toutes ou parties des feuilles du classeur.

definir un nom associe a une feuille
Portée niveau classeur

Le nom ne peut exister qu’une fois dans le classeur.

Quoi nommer ?

Chaque fois que dans une formule vous utiliser des adresses absolues alors la question mérite de se poser si ces adresses doivent être nommées.

Des cellules individuelles

Quand dans un calcul qui s’applique à toute une colonne vous utilisez la même adresse, alors cette adresse doit être un nom.

Par exemple dans une colonne qui calcule le montant de TVA avec un montant hors taxe et un taux de TVA, alors la cellule qui contient le taux doit être nommée.

nommer une adresse absolue.

Des plages

Quand dans une formule, vous faite référence à une plage, alors la plage doit être nommée.

Parce que lors des recopie incrémentale, sur les lignes suivantes, vous voulez faire référence au même tableau, pas à une plage qui se décale à chaque ligne.

nommer une plage.
Calcul avec des plages nommées

Plutôt que d’utiliser les adresses de la plage, je peux utiliser le nom.

plage nommee dans une somme.

Mais il est aussi possible d’utiliser le nom dans un calcul qui s’adresse à l’enregistrement.

utiliser une plage nommee dans un calcul en colonne.

Même si c’est la colonne qui est nommée, sur chaque ligne, Excel prend la bonne valeur.

Un terme impropre

Un nom peut représenter plus que des adresses de cellules.

Dans ce cas, ils ne sont plus saisis dans la barre d’adresses mais par le menu « Formules » => « Définir un nom… ».

Il faut donner un nom et renseigner la zone « Fait référence à » en laissant toujours le égal (=) au début.

Valeur constante

Un nom peut aussi représenter une valeur (constante).

un nom qui reference une valeur.

C’est une solution plus propre pour masquer une valeur que de l’écrire en blanc sur fond blanc dans un coin de la feuille.

Résultat de formules

Un nom peut représenter une formule de calcul.

un nom qui reference le resultat d'une formule.

Quand on utilise ce nom dans une cellule, Excel affiche le résultat de la formule cachée sous le nom.

utiliser un nom qui reference le resultat d'une formule.

C’est pratique si on donne une feuille à un utilisateur novice. Il peut utiliser les fonctions que vous avez déjà préparées.
C’est quand même une technique à éviter car les utilisateurs d’un niveau moyen risquent de ne pas comprendre d’où sortent ces éléments non standards.

L’intérêt principal c’est surtout de masquer des calculs intermédiaires compliqués à la vue de l’utilisateur.

Formater un tableau

Les mises en forme se font quand on est sûr que le tableau ne sera plus restructuré (ajout de colonnes) pour éviter de passer son temps à refaire les mises en forme.

On peut réaliser un quadrillage de base pour voir où l’on se situe.

Éléments de design

Le choix des couleurs est important car il influe directement sur la santé de l’utilisateur et plus précisément sa vue.

Typographie

Évitez les polices exotiques trouvées de-ci de-là. Un autre utilisateur ne la possède pas forcément et votre document sera illisible.

Préférez les polices classiques de type :

  • Arial ;
  • Times ;
  • Verdana ;
  • Lucida ;
  • Etc.

D’autres polices, sont livrées avec la gamme Microsoft Office. Elles sont présentes sur tous les postes équipés de la suite de Microsoft.

Donc des polices comme « Calibri » proposées par défaut conviennent parfaitement.

Limitez le choix à deux polices par documents.

Choisir une police pour les titres comme l’Arial et une pour les contenus comme le Times New Roman.

Enrichissements typographiques

Pour renforcer l’attention, vous pouvez mettre du contenu en gras, italique, ou jouer sur la taille des caractères.

Le souligné dans un document informatique me pose un souci.

Avec l’avènement du Web, j’ai souvent tendance à cliquer dessus le prenant pour un lien hypertexte.

Polices de symboles

Il existe des polices ou le caractère est remplacé par un symbole.

La difficulté est de savoir quel caractère représente quel symbole.

Le mieux et de passer par le menu « Insertion » et l’icône « Symbole ».

choisir un symbole dans la table des caractere speciaux.

Bibliothèques standards :

  • Wingding ;
  • Webdings.

Couleurs

Luminosité

Évitez les couleurs trop vives ou les couleurs fluo.

Préférez des couleurs plus neutres.

Par exemple pour la feuille, je casse le blanc du fond trop agressif par un blanc plus atténué. Pour la police, je choisi un noir moins profond.

Contraste

On n’écrit pas en jaune citron sur fond blanc ni en noir sur un bleu foncé.

Le contenu doit être clairement identifié.

Sémantique

La couleur doit donner une information.

Dans le classeur exemple, je distingue les zones de saisies des formules de calculs par la couleur de fond des cellules.

Dans mes projets professionnels, je préfère jouer sur la couler de police plutôt que le fond.

Avec cette optique, il ne devrait pas y avoir plus de deux ou trois couleurs dominantes dans le projet.

Les motifs de cellules

Je banni les motifs sur des cellules. En général, le seul effet obtenu c’est un texte illisible, sauf à avoir un fort contraste.

De toutes façons, les treillis et autres petits points font mal à la tête en fin de journée.

Éléments graphiques

Les éléments graphiques alourdissent la feuille. Il est conseillé de limiter leur usage.

Nous trouvons plusieurs types d’éléments graphiques.

  • Les images ;
  • Les formes ;
  • Les diagrammes (graphiques à proprement parlé);
  • Les sparklines
    Des petits graphiques qui s’affichent dans une cellule.

Par défaut, l’objet va se redimensionner lors de l’insertion, suppression ou changement de largeur des cellules. Ce n’est pas toujours souhaitable et vous pouvez modifier ce comportement dans les propriétés de l’objet.

fenetre propriete des objets sur excel.

Un objet est posé librement sur la feuille, mais, vous pouvez le lier à une ou plusieurs cellules.
Pour se faire il faut « dessiner » l’objet en maintenant la touche [Alt] enfoncée.
Si vous le déplacez en maintenant cette touche, il se positionne dans les cellules au plus proche.

Charte de l’entreprise

Votre entreprise possède peut-être une charte graphique d’un certain coût.

Elle a été réalisée par des professionnelles, alors pourquoi ne pas en profiter ?

Vos documents seront uniformes et vous ne vous poserez pas de questions.

Préparer sa feuille de calculs

Je commence toujours par modifier la couleur de fond pour un blanc moins brillant de la feuille et la police par défaut pour un noir plus clair, en sélectionnant toute la feuille.

Le quadrillage

Les feuilles possèdent un quadrillage que je conseille de supprimer dans le menu « Affichage ».

Excel prend forcément du temps pour le dessiner et vos zones seront de toute façon identifiées par des bordures.

Améliorer la vision

Figer les volets

Quand on se trouve en bas d’une grande feuille de calcul, il faut souvent remonter pour savoir dans qu’elle colonne est positionné le curseur.
Cela provoque de nombreux allers/retours dans la feuille.

Avec la commande « Affichage » => « Figer les volets », il est possible de bloquer une zone qui restera toujours visible à l’écran.

Pour faire simple, tout ce qui se trouve à gauche et au-dessus du curseur reste affiché même quand la feuille défile.

Bloquer toute la feuille

Si je souhaite empêcher le défilement de la feuille, je positionne mon curseur sur la dernière cellule visible à droite et en bas.

Ensuite je fige les volets.

Enfin j’insère des colonnes et des lignes pour repousser au loin, la plage libre.

La feuille d’accueil du classeur exemple exploite cette astuce.

Positionner ses données

Adresse de départ

J’aurais tendance à commencer un tableau en cellule A1.

ais quelques compromis sont possibles pour permettre un minimum de convivialité comme rajouter un titre, certains calculs de regroupement, une zone pour définir des critères, etc.

Dans ce cas, qu’elle est la meilleure adresse pour débuter le tableau ?

Un choix pour le futur

Cela semble triviale d’autant s’intéresser à cette question, mais je pense toujours au moment où je vais vouloir faire évoluer mes projets.

Un jour, j’en viendrais à automatiser mon projet.

Plus on fait toujours pareil, plus c’est facile d’automatiser, donc j’essaye d’avoir un maximum de cohérences entre tous mes tableaux.

Titre

Ma feuille va posséder un titre.

C’est pratiquement le seul endroit où les fusions de cellules sont utiles.

Rappel des calculs

En figeant les volets, en bas de la feuille, nous voyons toujours les étiquettes de colonnes.

Mais en haut de la feuille, je ne vois pas les résultats qui sont en bas.

Plutôt que de mettre la ligne de totaux en bas, il est possible de la poser au-dessus du tableau.

Rien n’empêche d’avoir une ligne en haut et en bas.

Zone de critères et résultats

Je peux vouloir rajouter une zone de critère en haut de feuille, afficher des analyses, etc.

Ligne d’isolation

Il ne faut pas oublier la ligne vierge qui isole le tableau de toutes autres informations.

Cellule de début

Avec toute cette masse d’informations, j’ai besoin d’espace en haut de feuille.

C’est pourquoi quand mon tableau sert aussi à de la présentation de données, je commence à la ligne 9.

Ce nombre est issu de mon expérience personnelle et n’a rien de scientifique.

Colonne de début

Je débute en colonne B pour me laisser une liberté à gauche du tableau.

Mise à jour des tableaux

Par habitude, l’opérateur, insère les données en fin de tableau, ce qui ne va pas sans de nombreux déboires.

Mises en forme

En ajoutant les données à la suite de l’existant, forcément, il faut refaire tous ses formats.

les bordures ne suivent pas lors de l'ajout de donnees.

Même si Excel essaye de faire de son mieux, il y aura des retouches à apporter.

Formules

De même les formules qui font référence à la plage ne seront plus mises à jour.

les formules restent inchangees lors de l'ajout de donnees.

Ici, le résultat devrait donner 15000.

Stratégies d’insertion

Insérer n’importe où

C’est certainement la méthode la plus simple et efficace.

Si le tableau doit être lu dans un ordre précis, il y aura forcément une colonne pour me donner cet ordre, comme une date, un numéro d’ordre, etc.

inserer n'importe où dans le tableau.

Si la ligne est insérée à l’intérieur de la plage, alors le format reste identique et Excel ajuste les formules.

Insérer en bas

Pourtant deux réflexions m’incitent à rajouter au bas des tableaux.

Respect de l’utilisateur

L’utilisateur par habitude ou par réflexe ou simplement parce que c’est naturel, rajoute à la fin.

Des automatismes

Le jour où je vais automatiser des actions et principalement l’insertion de lignes et la copie des formules au sein des tableaux, toujours me positionner au même endroit facilitera les macro-commandes.

Préparer à l’avance

Des opérateurs lors de la construction, prévoient déjà un nombre de lignes pour le tableau toutes formatées.

Cette solution est peu viable, mais sans gros problème non plus.

preparer le tableau à l'avance.

Si les données atteignent la limite fixée, alors on se retrouve à rajouter en dehors du tableau.

La vision de la feuille est mauvaise et il n’est pas rare de trouver au fin fond du tableau de vieilles données qui trainent.

Ligne d’insertion

C’est une évolution de la solution précédente.

Au lieu de prévoir à l’avance plusieurs lignes, il n’y en a qu’une, bien sûr décorée comme les autres.

Elle fait aussi office de ligne séparatrice avec la ligne de total.

ligne d'insertion en fin de tableau.

C’est une ligne d’insertion et chaque fois que je fais référence à la plage dans des formules ou pour la nommer, je sélectionne cette ligne vierge.

mise à jour de formule suite insertion en fin de tableau.

Cette ligne ne doit rien comporter, même pas une formule qui n’afficherait rien.

Avantages

Je respecte le fonctionnement de l’opérateur.

Je fais toujours pareil, ce qui facilite la productivité, car je ne me pose pas la question d’où insérer.

Lors de l’insertion, les mises en forme sont reprises sur la nouvelle ligne, les formules sont mises à jour puisque je suis toujours dans les coordonnées des plages, les noms sont étendus.

Inconvénients

Ils sont peu nombreux.

Les opérations qui s’adressent au tableau comme les graphiques et les tableaux croisés fonctionnent puisqu’Excel ne sélectionne pas de ligne vierge.

Les fonctions comme les moyennes marchent puisqu’Excel ne comptabilise pas les cellules vides.

Usage de l’insertion

Pour rajouter une ligne, il faut insérer à partir de celle d’insertion, et ensuite recopier les formules de la ligne du dessus sur la nouvelle ligne, et c’est tout.

Chaque fois que je sélectionne une plage, j’ajoute cette ligne d’insertion.

Mettre sous forme de tableaux

C’est une fonction apparue dans les dernières versions du tableur.

L’utilisateur souvent se méprend et pense qu’il s’agit uniquement de choisir une apparence pour le tableau.
Il peut de plus être désorienté. En effet, si le tableau possède déjà une mise en forme personnelle, le tableau de reflètera pas le formatage choisi.
Donc toujours appliquer cette commande sur un tableau ne possédant aucune mise en forme.

Mais c’est plus que ça, la plupart des services offerts reprennent les concepts que j’ai évoqués jusqu’à présent.

Les concepts diffèrent de ce qui est familier à l’utilisateur. Les colonnes sont nommées et les formules peuvent paraître étranges.

Je ne déconseille pas d’utiliser cette fonctionnalité, qui représente sans doute l’avenir dans la façon de gérer les tableaux sur Excel.

Attention simplement à ne pas perdre des utilisateurs qui ne sont pas habitués à ce fonctionnement.

Un tableau minimal

Un tableau vierge possède au minimum trois lignes

tableau minimal pour un modele excel.
  • La ligne d’étiquettes (nom de champs).
  • La première ligne de données avec les formules de calcul.
    Avec les zones de saisie vides.
  • La ligne d’insertion.

Construction du projet

Il y aura un tableau par onglet.

Conventions de nommage

Dans un projet informatique nous nommons beaucoup d’éléments. Il est important d’avoir des règles de nommage. Peu importe les règles choisies, l’important c’est de s’y tenir. Cela facilite la maintenance du projet.

Voici comment je défini mes noms :

Tous les noms sont écrits en majuscules. Il n’y a pas d’espaces qui sont simulés par l’underscore [_].

J’interdis les caractères accentués et je rejette les caractères d’iatriques (ç, oe, etc.).

J’évite le pluriel.

Mes tables sont préfixées par T suivi d’underscore [_] et le nom de la table. Pour une table qui gère des contacts, elle se nommera [T_CONTACT], pour les ventes [T_VENTE].

Mes noms de champs reprennent le nom de la table suivi d’underscore [_] et le nom du champ.

Pour le nom cela donne [CONTACT_NOM] pour la date de la vente [VENTE_DATE].

Si le nom de la table est long, exceptionnellement je l’abrège.

J’évite les noms trop générique comme [DATE] ou [TYPE]. Ils ne précisent pas assez la nature du contenu. Une date peut représenter une foule de choses, date de naissance, de ventes, de visite, etc.

Mon champ clé primaire est toujours construit de la même façon. C’est le nom de la table puis l’underscore [_] et enfin la mention ID pour identifiant.

Pour la table [T_PERIODE], cela donne : [PERIODE_ID].

Les champs de liaison gardent le même nom que dans la table d’origine. Dans la table des périodes, je laisse le champ nommé [CATEGORIE_ID] et non pas [PERIODE_CATEGORIE]. Comme ça je sais de quelle table vient l’information de ce champ.

Table T_ENTITY

Conserve mes employés. Elle pourrait porter un nom moins générique comme [T_EMPLOYE] dans la réalité.

Elle pourrait aussi gérer des affaires [T_AFFAIRE].

Liste des champs

  • ENTITY_ID.
  • ENTITY_NOM.
  • ENTITY_PRENOM.
  • ENTITY_NOTE.
exemple de table employes.

J’ai pris les champs importants mais je peux en rajouter bien d’autres. En fait tout informations qui concernent l’employé comme son service, son métier, etc.

Le champ [ENTITY_ID] est un numéro incrémenté.

Table T_CATEGORIE

Elle conserve les éléments de la catégorie d’absence.

Liste des champs

  • CATEGORIE_ID.
  • CATEGORIE_LIBELLE.
  • CATEGORIE_LEGENDE.
exemple de table categorie.

Le champ [CATEGORIE _ID] est le caractère qui sera affiché dans le planning.

Le champ [CATEGORIE_LEGENDE] est vide. Il affiche simplement la couler associée à la catégorie.

Il devrait être renseigné avec le code couleur.

Table T_PERIODE

Elle contient donc les informations à afficher dans le planning.

Liste des champs

  • PERIODE_ID.
  • ENTITY_ID.
  • CATEGORIE_ID.
  • PERIODE_LIB.
  • PERIODE_DEBUT.
  • PERIODE_FIN.
  • PERIODE_NOTE.
exemple de table periode.

Le champ [PERIODE _ID] est un numéro incrémenté.

Le champ [ENTITY_ID] accepte une valeur de la table [T_ENTITY].

Il y a avantage à mettre dessus ce champ une liste de choix.

Le champ [CATEGORIE _ID] accepte une valeur de la table [T_ CATEGORIE].

Il y a avantage à mettre dessus ce champ une liste de choix.

Les champs [PERIODE_DEBUT] et [PERIODE_FIN] prenne une date.

Premières analyses

Dès lors, nous allons voir les avantages de notre système puisque nous pouvons réaliser des calculs difficiles voire impossibles à réaliser avec la simple grille de départ.

Écart en jours

C’est enfantin de connaitre la durée d’une période, puisque c’est la date de fin moins la date de début plus 1.

soustraction de date sur excel.

Écart en jours ouvrés

Il suffit d’utiliser la fonction consacrée NB.JOURS.OUVRES().

Elle prend deux paramètres, la date de début et la date de fin.

exemple de fonction nb.jours.ouvres.

Nombre de périodes par entité

calculer le nombre de periode par entite.

Nombre de jours d’absence par entité

calculer le nombre de periode par categorie.

Où en sommes-nous ?

Petit à petit, notre projet prend forme.

Les bases de notre projet

Définir les fondements du projet

J’ai décidé de fonctionner avec des tableaux pointant des lacunes dans l’organisation des données en feuille ou classeur.

  • Problème de consolidation de l’information.
  • Formules à reprendre.

Définir l’architecture du planning

Je respecte les règles de construction des tableaux.

  • Atomisation.
  • Unicité.
  • Dé-doublonnage.
  • Clé primaire.

Par la même, j’ai induis le fonctionnement futur.

Améliorer la lisibilité

La police

  • Deux polices maximum par documents.
    • Une pour les titres.
    • Une pour le contenu.
  • Des polices standards installées sur tous les postes.

Les couleurs

Là aussi, le concepteur restera mesuré.

On préfèrera jouer sur la couler de police plutôt que sur la coloration des fonds pour limiter la consommation d’encre lors de l’impression.

Attention aux contrastes et couleurs trop vives qui fatiguent l’œil.

Objets graphiques

Les objets alourdissent le chargement du document. Ils ne doivent pas distraire la lecture de la feuille.

Les apports sur Excel

Des alternatives pour la recherche

J’ai utilisé les fonctions EQUIV() et INDEX() pour rechercher une valeur quelconque dans un tableau et dans le sens que je souhaite.

Comment le tableur comprend un tableau

  • La première ligne contient des étiquettes de colonnes.
  • Pas de ruptures dans les données.
    • Ligne vierge.
    • Calculs intermédiaires.
    • Fusion de cellules.
  • Le tableau est isolé du reste de l’information.

Stratégies d’insertion

Sûrement le plus gros point négatif avec le tableur est l’insertion d’informations qui oblige à reprendre les mises en forme et surtout les formules.

Pourtant le postulat est simple :

Insérer dans la plage existante, plutôt qu’à la fin du tableau règle ce souci.

Mais pour respecter le fonctionnement naturel de l’utilisateur, il existe d’autres méthodes.

  • Préparer des lignes à l’avance.
  • Créer une seule ligne d’insertion.
  • Utiliser le menu « Mettre sous forme de tableaux ».

Les noms de cellules

Nommer des cellules offre des avantages.

  • Formules plus explicites.
  • Possibilité d’atteindre rapidement une zone.
  • Masquer des traitements internes.

Figer les volets

Cette commande favorise l’exploitation du tableau et offre une meilleure vision.

Elle limite les risques d’écrire dans la mauvaise cellule.

Des choix assumés

Ce que je laisse en route

La simplicité d’utilisation.

Sans devenir compliquée et exagérément lourde, ma nouvelle architecture va induire plus de manipulations pour saisir l’information.

Les gains obtenus

En contrepartie, je privilégie la robustesse, m’ouvrir des horizons, limiter les incohérences.

Et la suite ?

La prochaine étape consistera à mettre en place notre grille.

Ce sera l’occasion de revenir sur les dates.

J’aborderais la construction de formules un peu plus complexes.

Je mettrais en place le principe de planning perpétuel.

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.

>