I. Introduction▲
Une question revient régulièrement sur le forum Excel de DVP: Comment saisir des données dans une feuille "formulaire" et stocker ces données dans une feuille.
Cette question en appelle une autre: Comment récupérer des données dans une feuille "base de données" et les placer dans une feuille à des fins de modifications ou d'impression.
Je vous invite à découvrir les réponses au travers d'un tutoriel progressif, détaillé et illustré.
I-A. Prérequis▲
Ce cours requiert des connaissances de base en Excel. Vous devez être à l'aise avec la terminologie d'Excel. Vous savez saisir des données, manipuler des onglets. Les techniques de copier-coller dans Excel vous sont connues.
La partie Automatisation ne requiert pas de connaissances en VBA, le but du cours étant justement de réaliser les opérations d'automatisation en utilisant (presqu'exclusivement) l'enregistreur de macros.
Toutefois, j'ai développé une annexe à ce cours, dans laquelle nous aménagerons le code VBA créé par l'enregistreur. Cette partie, facultative pour l'objectif principal de ce tutoriel, requiert des connaissances de base dans la manipulation des objets Excel en VBA.
I-B. Niveau▲
Ce cours est de niveau intermédiaire
I-C. Objectifs du cours▲
Ce cours a pour objectif de vous apprendre à saisir des données au moyen d'une feuille de calcul, de stocker ces données dans une feuille de données et de récupérer une donnée de cette feuille pour remplir le formulaire d'impression.
Nous réfléchirons aux différentes étapes à réaliser, en fixant quelques règles de base de la gestion de données.
- La validation des données
- Le verrouillage des saisies
- Le parcours au sein des cellules de saisie
- Ordonnancement des données
- Entêtes de colonnes (Intitulés des champs)
- Une ligne de feuille de calcul pour le stockage d'un enregistrement
- Une plage nommée pour trouver la ligne qui stockera la nouvelle donnée
- Comprenant ce qui est effectivement enregistré
- Plaçant Excel dans la configuration requise pour le démarrage de l'enregistrement
- Utilisant l'enregistrement des déplacements vers des cellules absolues ou relatives
I-D. Avertissement▲
Pour qui n'a qu'un marteau, tous les problèmes ont la forme d'un clou...
Excel n'est pas un gestionnaire de bases de données, et donc, Excel n'est pas adapté pour gérer des données...
Pour une gestion professionnelle des données, je recommande vivement de vous tourner vers des outils tels que Access, par exemple. Ces outils vous permettront de créer rapidement et facilement des tables pour contenir vos données, des formulaires de saisie pour entrer de nouvelles donnéees ou modifier des données existantes, des rapports pour imprimer des listes de données.
Les requêtes, les filtres et les tris vous permettront des manipulations aisées sur l'ensemble de vos données, ou sur certaines d'entre elles seulement.
Le présent tutoriel n'a pas pour ambition de permettre à Excel d'égaler les outils dédiés à la gestion des données, mais de vous permettre les manipulations basiques des données avec Excel.
II. Analyse des données et mise en place dans Excel▲
Le but de ce tutoriel n'est pas de réaliser une analyse approfondie d'une table de données. Il me semble toutefois intéressant de démarrer par une analyse rapide des données que l'on souhaite gérer avec Excel
Pour illustrer ce tutoriel, nous allons gérer des données liées à des contacts, ce qui nous permettra de rester très généraliste.
- Identifiant: Code mnémonique défini par l'utilisateur
- Nom du contact
- Prénom du contact
- Localité: Choisie dans une liste déroulante
- Date de naissance
Pour illustrer ce cours, nous n'avons pas besoin de données supplémentaires.
II-A. Préparation de la feuille de stockage, la "table des contacts"▲
De façon assez classique, nous calerons les données dans le coin supérieur gauche de la feuille.
II-B. Règles pour une bonne gestion des données dans Excel▲
II-B-1. Une seule table par entité▲
Bien qu'Excel possède 256 colonnes et 65 536 lignes (16 384 colonnes et 1 048 576 lignes pour Excel 2007), il faut garder à l'esprit la règle suivante:
Une feuille EXCEL PAR TABLE DE DONNEES
La seule dérogation éventuellement acceptable à mes yeux consiste à stocker sur la même feuille des petites tables annexes consistant en une seule colonne de données par table.
II-B-2. Calage des données dans le coins supérieur gauche de la feuille▲
Les feuilles qui servent de table de données ne sont en général pas imprimées. On évitera donc les fioritures, les colonnes et lignes masquées pour améliorer la présentation, les lignes multiples d'entête et autres mises en page qui empêcheront une gestion correcte des données.
Nous prendrons donc soin de placer l'entête du premier champ en A1, et nous utiliserons une seule ligne, la première, pour placer les intitulés des colonnes, c'est-à-dire les noms des champs de la table.
Cette ligne pourra éventuellement être pourvue d'un filtre automatique
II-B-3. Pas de cellule vide en ligne 1 entre la colonne A et la dernière colonne de la table▲
Cela revient à dire: Pas de solution de continuité entre les colonnes de la table. Cela veut dire aussi que chaque colonne possède un intitulé de champ.
On comprendra aisément que si des colonnes entièrement vides sont intercalées entre des colonnes de données, il sera malaisé de faire comprendre à Excel où se trouve le dernier champ de la table, et l'on risquera donc de "perdre" certaines colonne, c'est-à-dire de faire en sorte qu'Excel les considère comme étrangères à la table.
Le fait de ne pas avoir de colonne sans intitulé permet évidemment de connaître la nature du champ, permet à Excel d'utiliser ce nom de champ dans la gestion de la table (fonction de base de données, tableau croisé dynamique, filtre élaboré, ...)
II-B-4. Pas de cellule vide en colonne A entre le premier et le dernier enregistrement de la table▲
A nouveau, pour permettre à Excel d'interpréter correctement les limites de la table, on s'assurera que chaque enregistrement possède au moins une donnée, stockée en A.
Dans certains cas, il arrive que l'on puisse déroger à la règle, notamment lorsque l'on souhaitera se servir de la feuille de stockage pour l'impression de rapports. Il sera alors parfois utile de pouvoir "orner" la feuille de lignes de titres, et donc de déroger à certaines des règles que j'expose ici. Dans ces cas-là, on veillera à ce qu'il n'y ait pas de cellules vides entre le premier et de dernier enregistrement de la table, et on s'arrangera pour définir la table de manière à ce qu'Excel comprenne les limites de la table.
II-B-5. Clé primaire en colonne A▲
Pour les connaisseurs, la clé primaire est le champ qui permet de sélectionner sans équivoque un enregistrement au sein de la table. Il s'agit d'une donnée qui ne peut pas apparaître en doublon au sein de la table.
Il peut s'agir du numéro de registre national, d'une plaque d'immatriculation, d'un numéro d'ordre...
Excel n'étant pas un gestionnaire de données, c'est au développeur
du classeur de mettre en place, éventuellement par VBA, un système de
rejet ou à tout le moins de détection des doublons (format conditionnel,
contrôle VBA à la saisie ou lors du transfert, fonction NB.SI, ...
Excel n'a nativement aucune fonction de contrôle d'intégrité référentielle,
et il incombera également au développeur de mettre éventuellement un tel
système en place (je lui souhaite bon courage...)
II-C. Préparation de la feuille de données▲
Après cet exposé un peu théorique et rébarbatif pour certains, passons à la pratique en créant la feuille qui servira de "table" pour les données.
N'oubliez pas de toujours nommer vos onglets. Il m'est déjà arrivé d'ouvrir des classeurs dans lesquels je trouvais une cinquantaine de feuilles nommées Feuil1, Feuil2, ..., Feuil50.
Sur base de la structure développée plus haut, voici comment nous créerons notre feuille de données
II-C-1. Utilisation du formulaire Excel de saisie de données▲
Comme annoncé plus haut, nous pouvons utiliser un formulaire créé par Excel pour la saisie de nos données.
Après avoir activé une cellule d'entête de la feuille de données, allons sur le menu Données/Formulaire...
Ce qui donne parfois un beau message de la part de notre tableur préféré.
En substance, ce message nous indique qu'Excel a des doutes sur la plage qui va servir de données, et nous propose de choisir ce qui lui semble le plus adéquat.
Cliquons sur OUI et Excel va créer le formulaire de saisie selon les intitulés des champs de notre "table de données
Il suffit alors de remplir les champs
puis de cliquer sur
pour ajouter de
nouvelles données à la table. Excel repérera automatiquement
la nouvelle ligne, videra les champs de saisie et vous pourrez
continuer la saisie.
Comme vous l'aurez sans doute constaté, ce formulaire de saisie est assez rudimentaire. Il n'existe aucun moyen de contrôler la saisie des données avec un formulaire de ce type.
Ainsi, nous pouvons sans problème entrer une donnée erronée dans le champ de date de naissance, ce qui n'est pas souhaitable.
Nous voyons donc très vite les limites de ce formulaire, qui sera donc utilisé avec toutes les réserves d'usage.
II-D. Préparation du formulaire de saisie▲
Au vu des limites du formulaire créé par Excel, nous allons créer nous-même un formulaire de saisie qui utilisera une feuille de calcul.
Cette feuille sera préparée pour faciliter et canaliser la saisie des informations de notre contact, notamment grâce aux possibilités de validation des données dans les cellules.
Il est possible, avec Excel, de créer un formulaire de saisie à l'aspect professionnel avec un userform et de la programmation en VBA. Ces techniques dépassent le cadre de ce logiciel, et ne seront donc pas abordées.
Je suis loin d'être un expert en esthétique. Dès lors, le lecteur excusera la pauvreté artistique de mon formulaire de saisie. Je me contenterai de faire en sorte qu'il soit fonctionnel.