Vous avez probablement l'habitude d'utiliser Excel pour des tâches telles que la préparation de rapports ou l'établissement de prévisions ou de budgets. Cependant, lorsque les besoins ne justifient pas le recours à un véritable système de gestion de base de données (SGBD), le tableur de Microsoft peut également être avantageusement utilisé pour créer rapidement des bases de données. Les fonctionnalités d'Excel vous permettent d'y gérer vos collections simples et de les interroger efficacement.
Comment créer une base de données Excel
Quelques étapes simples suffisent pour créer une base de données Excel dans laquelle gérer une collection de données. Une fois vos enregistrements créés, vous pouvez très simplement trier et filtrer votre collection, la mettre à jour et la réorganiser au besoin, ainsi qu'utiliser les puissantes fonctions de base de données d'Excel pour lui soumettre des requêtes et des calculs complexes.
1 - Définir vos en-têtes de colonne dans un nouveau classeur
Pour commencer, ouvrez un nouveau classeur et définissez vos en-têtes de colonne sur la ligne 1 de l'onglet Feuil1, compte tenu des différents types d'informations que vous jugez pertinent de consigner pour votre collection.
Dans cet exemple de collection d'appartements en gestion locative dont un agent immobilier s'est vu confier la responsabilité par son directeur d'agence, chaque ligne correspondra à la fiche d'un bien. Les en-têtes de colonne sont définis en fonction des informations que l'agent considère comme pertinentes sur chaque bien pour savoir rapidement ce qu'il peut proposer à la location parmi les biens qu'il gère lorsque des demandes lui sont adressées.
Cliquez sur la cellule A1 et entrez votre premier en-tête de colonne. Cliquez dans la cellule voisine, puis entrez le deuxième, et ainsi de suite jusqu'à ce que tous vos en-têtes soient définis.
2 - Transformer votre feuille en table de base de données
Notez qu'à ce stade, pour Excel, votre ligne 1 n'est rien de plus qu'une ligne de données ordinaires. Pour qu'il la considère comme la ligne définissant les champs de votre base de données, déclarez-là comme ligne d'en-têtes d'un tableau.
Pour ce faire, sélectionnez les cellules contenant vos en-têtes, et uniquement celles-ci, puis dans l'onglet Insertion, choisissez Tableau.
Dans la boîte de dialogue Créer un tableau, cochez la case Mon tableau comporte des en-têtes pour qu'Excel comprenne que la ligne 1 définit les champs de votre base de données. À défaut, il basculera les données de votre ligne 1 sur la ligne 2 et créera automatiquement une ligne d'en-têtes Colonne1, Colonne2, Colonne3, etc.
Cliquez sur OK pour valider. Les en-têtes que vous avez spécifiés en ligne 1 sont désormais reconnus par Excel en tant que champs de votre base de données, et flanqués d'une icône de liste déroulante qui vous sera utile ultérieurement pour trier et filtrer vos données.
Notez que la commande Mettre sous forme de tableau, dont il sera question un peu plus bas, vous permet d'obtenir le même résultat, en appliquant de surcroît un style donné à votre table.
Double-cliquez ensuite sur Feuil1, au bas de la feuille, et renommez l'onglet à votre convenance, par exemple Base de données, puis enregistrez votre base de données sous un nom explicite pour votre collection.
3 - Configurer les champs pour limiter les erreurs de saisie
Pour limiter les erreurs de saisie dans votre base de données, vous pouvez créer des règles de validation du contenu des cellules. Excel vérifiera alors en temps réel la conformité de vos saisies aux règles applicables, et vous informera de toute erreur de saisie détectée.
Vous pouvez par exemple utiliser de telles règles de validation pour limiter la saisie aux éléments d'une liste donnée, interdire la saisie de chiffres, dates ou heures non compris dans une plage de valeurs donnée ou encore limiter la longueur du texte entré. Vous pouvez même utiliser une formule pour contrôler la validité d'une saisie compte tenu de la valeur d'autres cellules.
Pour appliquer une règle de validation à une colonne, sélectionnez la colonne, puis dans l'onglet Données, sous Validation des données, choisissez Validation des données. Dans cet exemple, l'agent choisi d'interdire la saisie dans le champ Type d'une valeur autre que T2, T3, T4 ou T5 dans la mesure il n'a actuellement pas d'autres types de biens en portefeuille.
Vous pouvez prévoir un message d'alerte en cas d'erreur de saisie. À cette fin, dans la même fenêtre, ouvrez l'onglet Alerte d'erreur et spécifiez le message qui s'affichera le cas échéant. Sous Style, vous pouvez faire en sorte que la règle de validation empêche toute saisie autre qu'une valeur valide en choisissant Arrêt.
Validez votre règle en cliquant sur OK.
Dans cet exemple, seul un message d'avertissement s'affichera en cas d'erreur de saisie dans le champ Type, sans néanmoins empêcher in fine l'entrée de la valeur détectée comme non valide si l'utilisateur la confirme.
Configurez de la même manière des règles de validation pour chaque champ auquel vous souhaitez appliquer un contrôle de saisie, en veillant dans chaque cas à la pertinence de la règle compte tenu de la nature des informations.
4 - Créer un formulaire pour accélérer l'entrée des données
Une fois vos champs de base de données et vos règles de validation définis, vous pouvez consigner vos enregistrements en mode « classique », en renseignant successivement les différentes cellules de chaque ligne de données, et ce ligne après ligne.
Lorsque les champs sont nombreux et le nombre d'enregistrements important, il peut néanmoins devenir fastidieux de renseigner la base de cette façon. Pour faciliter et accélérer la saisie des données, vous pouvez vous aider d'un masque de saisie en créant un formulaire.
Pour rendre disponible la fonctionnalité Formulaire sur le ruban, sous Fichier, choisissez Options, puis dans la boîte de dialogue Options Excel, cliquez sur Personnaliser le ruban.
Choisissez les Commandes non présentes sur le ruban, sélectionnez la commande Formulaire, puis ajoutez-la dans un Nouveau groupe sous un Nouvel onglet.
Pour définir votre masque de saisie, sélectionnez les cellules définissant les champs de votre base de données, et uniquement ces cellules, puis dans votre Nouvel onglet sur le ruban, cliquez sur Formulaire.
Le formulaire qui s'affiche reprend automatiquement les différents champs de votre base de données dans un masque de saisie qui facilite l'entrée des données.
Une fois les champs de votre premier enregistrement renseignés, cliquez sur Nouvelle pour le valider. L'enregistrement est automatiquement inséré dans votre table de base de données, dont la zone de délimitation s'étend en conséquence vers le bas.
Saisissez de la même manière vos enregistrements suivants. Les boutons Suivante et Précédente du formulaire vous permettent de naviguer dans vos enregistrements pour les modifier ou en supprimer au besoin. Une fois votre saisie terminée, cliquez sur Fermer.
Dans votre table désormais renseignée, les icônes de liste déroulante associées aux noms de champ vous permettent de trier et de filtrer vos enregistrements.
Ici, par exemple, le filtre numérique Supérieur ou égal à appliqué sur le champ Surface permettra de dresser la liste de tous les appartements d'une surface supérieur ou égale à un nombre donné de mètres carrés. Et en combinant ce filtre avec un deuxième, appliqué sur le champ « Loué/Non loué » et défini sur Oui, l'agent pourra savoir lesquels parmi ces appartements il peut proposer à la location.
Vous pouvez ainsi combiner plusieurs filtres, appliqués sur différents champs, de manière à isoler précisément les informations dont vous avez besoin.
5 - Mettre les données en forme
Si vous souhaitez modifier la mise en forme par défaut appliquée à votre table de base de données, cliquez dans une cellule quelconque de la table, puis dans l'onglet Accueil, cliquez sur Mettre sous forme de tableau.
Survolez les différents styles proposés pour prévisualiser le rendu dans votre table, puis cliquez sur celui que vous souhaitez appliquer.
Les fonctions Excel utiles pour une base de données
Excel propose plusieurs fonctions de base de données puissantes que vous pouvez utiliser pour interroger votre base de données et effectuer des calculs sur des échantillons de données.
BDMOYENNE
Renvoie la moyenne des entrées de base de données sélectionnées.
BDNB
Compte le nombre de cellules de la base de données qui contiennent des nombres.
BDNBVAL
Compte les cellules non vides de la base de données.
BDLIRE
Extrait de la base de données un enregistrement unique répondant aux critères spécifiés.
BDMAX
Renvoie la valeur maximale des entrées de base de données sélectionnées.
BDMIN
Renvoie la valeur minimale des entrées de base de données sélectionnées.
BDPRODUIT
Multiplie les valeurs d'un champ particulier des enregistrements de la base de données, qui répondent aux critères spécifiés.
BDECARTYPE
Calcule l'écart type pour un échantillon d'entrées de base de données sélectionnées.
BDECARTYPEP
Calcule l'écart type pour l'ensemble d'une population d'entrées de base de données sélectionnées.
BDSOMME
Ajoute les nombres dans la colonne de champ des enregistrements de la base de données, qui répondent aux critères.
BDVAR
Calcule la variance pour un échantillon d'entrées de base de données sélectionnées.
BDVARP
Calcule la variance pour l'ensemble d'une population d'entrées de base de données sélectionnées.
Il y a toujours une bonne raison d'utiliser Excel. Tableaux, factures, reçus, plannings : découvrez ces modèles de graphiques Excel pour formater vos données et mettre en évidence vos résultats.
