Comment créer une base de données dans Microsoft Excel

Vous avez besoin de créer et d’utiliser une base de données ? Cet article va vous montrer comment créer une base de données dans Microsoft Excel.

Excel est l’outil de données le plus couramment utilisé dans les entreprises et la productivité personnelle à travers le monde.

Excel étant largement utilisé et disponible, il a tendance à être utilisé fréquemment pour stocker et gérer des données comme une base de données improvisée. C’est particulièrement vrai pour les petites entreprises, qui ne disposent pas du budget ou de l’expertise nécessaires pour utiliser des outils plus adaptés.

Cet article vous montre ce qu’est une base de données et les meilleures pratiques à suivre si vous souhaitez utiliser Excel comme base de données.

Obtenez les fichiers d’exemple utilisés dans cet article en cliquant sur le lien ci-dessus et suivez la procédure ci-dessous !

Qu’est-ce qu’une base de données ?

Une base de données est un ensemble structuré de données qui se présente souvent sous un format électronique et qui est utilisé pour organiser, stocker et récupérer des données.

Par exemple, une base de données peut être utilisée pour stocker les noms, adresses, commandes et informations sur les produits des clients.

Les bases de données présentent souvent des caractéristiques essentielles qui en font un endroit idéal pour stocker vos données.

Différence entre une base de données et un fichier Microsoft Excel

Une feuille de calcul Excel n’est pas une base de données, mais elle dispose d’un grand nombre de fonctions excellentes et faciles à utiliser pour travailler avec des données.

Il ne s’agit pas d’une liste exhaustive des caractéristiques d’une base de données, mais ce sont quelques-unes des principales caractéristiques qui font généralement d’une bonne base de données une option plus appropriée.

Les caractéristiques d’une base de données dépendent de la base de données dont il s’agit. Toutes les bases de données n’ont pas les mêmes caractéristiques et fonctionnalités.

Vous devrez déterminer quelles sont les caractéristiques essentielles pour votre situation afin de décider si vous devez utiliser Excel ou un autre outil de base de données.

💡 Conseil: si vous disposez d’Excel pour Microsoft 365, envisagez d’utiliser Dataverse for Teams comme base de données plutôt qu’Excel. Dataverse possède plusieurs des excellentes fonctionnalités de base de données mentionnées ci-dessus et il est inclus dans votre licence Microsoft 365 sans coût supplémentaire.

Conception de bases de données relationnelles

Il convient de bien réfléchir à la structure de votre base de données avant de commencer à la construire. Cela peut vous éviter bien des maux de tête par la suite.

La plupart des bases de données ont une conception relationnelle. Cela signifie que la base de données contient de nombreuses tables liées entre elles, au lieu d’une seule table qui contient toutes les données.

Supposons que vous vouliez suivre les commandes dans votre base de données.

Une option consiste à créer une seule table plate contenant toutes les informations sur la commande, les produits et le client qui a créé la commande.

Ce n’est pas très efficace car vous finissez par créer plusieurs entrées pour les mêmes informations sur le client, telles que le nom, l’adresse électronique et l’adresse. Vous pouvez voir dans les données ci-dessus que les colonnes G, H et I contiennent beaucoup de valeurs en double.

Une meilleure option consiste à créer des tables distinctes pour stocker les données relatives aux commandes, aux produits et aux clients.

Les données de la commande peuvent alors faire référence à un identifiant unique dans les données du produit et du client, ce qui permet de relier les tables et d’éviter la saisie de données en double.

Ce même exemple de données peut ressembler à ceci lorsqu’il est réorganisé en plusieurs tables liées.

1. Une table Commandes qui contient le champ ID de l’article et du client.
2. Une table Produits qui se rapporte aux champs Articles de la table Commandes.
3. Une table Clients qui se rapporte à l’ID du client dans la table Commandes.

Cela évite la saisie de données en double dans une structure de table unique et plate. Vous pouvez utiliser l’identifiant unique de l’article ou du client pour rechercher les données connexes dans les tables respectives des produits ou des clients.

Structure de données tabulaires dans Excel

Si vous utilisez Excel comme base de données, vous aurez besoin de vos données sous forme de tableaux. Il s’agit de la façon dont les données sont structurées.

L’exemple ci-dessus montre un ensemble de données de commande de produits au format tabulaire. Un format de données tabulaire convient mieux à Excel en raison de la structure en lignes et en colonnes d’une feuille de calcul.

Voici quelques règles à respecter pour que vos données soient présentées sous forme de tableaux.

• La première ligne doit contenir les titres des colonnes. Il s’agit simplement d’un nom court et descriptif pour les données contenues ci-dessous.
• Pas d’en-têtes de colonne vides. Chaque colonne de données doit avoir un nom.
• Pas de colonnes ou de lignes vides. Les valeurs vides dans un champ sont acceptables, mais les colonnes ou les lignes qui sont entièrement vides doivent être supprimées.
• Pas de sous-totaux ou de totaux généraux dans les données.
• Une ligne doit représenter exactement un enregistrement de données.
• Une colonne doit contenir exactement un type de données.

Dans l’exemple de données de commandes ci-dessus, vous pouvez voir que B2:E2 contient les en-têtes de colonne d’un ID de commande, ID de client, date de commande, article et quantité.

L’ensemble de données ne comporte pas de lignes ou de colonnes vides, et aucun sous-total n’est inclus.

Chaque ligne de l’ensemble de données représente une commande pour un type de produit.

Vous remarquerez également que chaque colonne contient un seul type de données. Par exemple, la colonne Article contient uniquement des informations sur le nom du produit et ne comprend pas d’autres informations sur le produit, comme le prix.

⚠️ A ttention: Si vous n’adhérez pas à ce type de structure pour vos données, alors la synthèse et l’analyse de vos données seront plus difficiles par la suite. Les outils tels que les tableaux croisés dynamiques nécessitent des données tabulaires !

Utiliser des tableaux Excel pour stocker des données

Excel dispose d’une fonction qui permet de stocker spécifiquement vos données tabulaires.

Un tableau Excel est un conteneur pour vos ensembles de données tabulaires. Ils permettent de conserver toutes les mêmes données dans un seul objet et présentent de nombreux autres avantages.

💡 Conseil: Consultez ce post pour en savoir plus sur toutes les fonctionnalités étonnantes des tableaux Excel.

Vous voudrez certainement utiliser une table pour stocker et organiser tout tableau de données qui fera partie de votre ensemble de données.

Comment créer un tableau Excel

Suivez ces étapes pour créer une table à partir d’un ensemble de données existant.

1. Sélectionnez n’importe quelle cellule dans votre ensemble de données.
2. Allez dans l’onglet Insertion du ruban.
3. Sélectionnez la commande Table.

Cela ouvrira le menu Créer un tableau où vous pourrez sélectionner la plage contenant vos données.

Lorsque vous sélectionnez une cellule à l’intérieur de vos données avant d’utiliser la commande Tableau, Excel devine la plage complète de votre ensemble de données.

Vous verrez une ligne de tirets verts entourant vos données, qui indique la plage sélectionnée dans le menu Créer un tableau. Vous pouvez cliquer sur le bouton de sélection à droite de l’entrée de la plage pour ajuster cette plage si nécessaire.

1. Cochez l’option Ma table a des en-têtes.
2. Appuyez sur le bouton OK.

📝 Remarque: l’option Mon tableau a des en-têtes doit être cochée si la première ligne de votre ensemble de données contient des titres de colonne. Sinon, Excel créera un tableau avec des titres d’en-tête de colonne génériques.

Vos données Excel se trouvent maintenant dans un tableau ! Vous verrez immédiatement qu’elles se trouvent dans un tableau, car Excel appliquera un format par défaut qui rendra la plage du tableau très évidente.

💡 Conseil: vous pouvez choisir parmi une variété d’options de format pour votre tableau à partir de l’onglet Conception du tableau dans la section Styles de tableau.

La prochaine chose à faire avec votre tableau est de lui donner un nom approprié. Le nom de la table sera utilisé pour faire référence à la table dans les formules et autres outils, donc lui donner un nom court et descriptif vous aidera plus tard lors de la lecture des formules.

1. Allez dans l’onglet Table Design.
2. Cliquez sur le champ de saisie Nom de la table.
3. Tapez le nom de votre nouvelle table.
4. Appuyez sur la touche Entrée pour accepter le nouveau nom.

Chaque table de votre base de données devra être dans une table séparée, vous devrez donc répéter le processus ci-dessus pour chacune d’elles.

Comment ajouter de nouvelles données à votre tableau

Vous aurez probablement besoin d’ajouter de nouveaux enregistrements à votre base de données. Cela signifie que vous devrez ajouter de nouvelles lignes à vos tables.

L’ajout de lignes à un tableau Excel est très facile et vous pouvez le faire de plusieurs manières différentes.

Vous pouvez ajouter de nouvelles lignes à votre tableau à partir du menu contextuel.

1. Sélectionnez une cellule dans votre tableau.
2. Cliquez avec le bouton droit de la souris sur la cellule.
3. Sélectionnez Insérer dans le menu.
4. Sélectionnez Table Rows Above dans le sous-menu.

Cette opération permet d’insérer une nouvelle ligne vierge directement au-dessus des cellules sélectionnées dans votre tableau.

Vous pouvez ajouter une ligne vide au bas de votre tableau avec la touche Tab.

1. Placez le curseur de la cellule active dans la cellule inférieure droite du tableau.
2. Appuyez sur la touche Tab.

Une nouvelle ligne vierge sera ajoutée au bas du tableau.

Mais la façon la plus simple d’ajouter de nouvelles données à un tableau est de taper directement sous le tableau. Les données saisies directement sous le tableau sont automatiquement absorbées dans le tableau !

Mise en page du classeur Excel

Si vous voulez créer une base de données Excel, vous devez la garder simple.

Votre fichier de base de données Excel doit contenir uniquement les données et rien d’autre. Cela signifie que tout rapport, analyse, visualisation de données ou autre travail lié aux données doit être effectué dans un autre fichier Excel.

Votre fichier de base de données Excel ne doit être utilisé que pour ajouter, modifier ou supprimer les données stockées dans le fichier. Cela permettra de réduire les risques de modification accidentelle de vos données, car la seule raison d’ouvrir le fichier sera de modifier intentionnellement les données.

Chaque table de la base de données doit être stockée dans une feuille de calcul distincte et rien d’autre que la table ne doit se trouver dans cette feuille. Vous pouvez ensuite nommer la feuille de calcul en fonction de la table qu’elle contient afin de faciliter la navigation dans votre fichier.

💡 Conseil: Placez votre tableau en commençant par la cellule A1, puis masquez les autres colonnes. De cette façon, il est clair que la feuille ne doit contenir que le tableau et rien d’autre.

La seule autre feuille que vous pouvez éventuellement inclure est une table des matières pour aider à organiser le fichier. Vous pouvez y répertorier chaque table avec les champs qu’elle contient et une description de ces champs.

💡 Conseil: vous pouvez créer un lien hypertexte entre le nom du tableau figurant dans la table des matières et sa feuille associée. Sélectionnez le nom et appuyez sur Ctrl + K pour créer un lien hypertexte. Cela peut vous aider à naviguer dans le classeur lorsque vous avez beaucoup de tables dans votre base de données.

Utiliser la validation des données pour éviter les données non valides

La validation des données est une fonctionnalité très importante pour toute base de données. Elle vous permet de vous assurer que seuls certains types de données sont autorisés dans une colonne.

Vous pouvez créer des règles de validation des données telles que.

• Seuls les nombres entiers positifs sont saisis dans une colonne de quantité.
• Seuls certains noms de produits sont autorisés dans la colonne des produits.
• Une colonne ne peut pas contenir de valeurs en double.
• Les dates sont comprises entre deux dates données.

Les outils de validation des données d’Excel vous permettront de vous assurer que les données saisies dans votre base de données respectent ces règles.

Suivez ces étapes pour ajouter une règle de validation des données à n’importe quelle colonne de votre tableau.

1. Cliquez avec le bouton gauche de la souris sur l’en-tête de la colonne pour sélectionner la colonne entière.

Lorsque vous passez le curseur de la souris sur le haut de l’en-tête de votre colonne dans un tableau, le curseur se transforme en une flèche noire pointant vers le bas. Cliquez avec le bouton gauche de la souris et la colonne entière sera sélectionnée.

La validation des données se propage automatiquement à toutes les nouvelles lignes ajoutées au tableau.

1. Allez dans l’onglet Données.
2. Cliquez sur la commande Data Validation.

Cela ouvrira le menu Validation des données, dans lequel vous pourrez choisir parmi différents paramètres de validation. S’il n’y a pas de validation active dans la cellule, vous devriez voir l’option Toutes les valeurs sélectionnée dans le critère Autoriser.

N’autoriser que les valeurs positives des nombres entiers

Suivez ces étapes à partir du menu Validation des données pour n’autoriser la saisie que de valeurs entières positives.

1. Allez dans l’onglet Paramètres du menu Validation des données.
2. Sélectionnez l’option Nombre entier dans la liste déroulante Autoriser.
3. Sélectionnez l’option supérieure à dans la liste déroulante Données.
4. Entrez 0 dans le champ de saisie Minimum.
5. Appuyez sur le bouton OK.

💡 Conseil: Gardez l’option Ignorer les blancs cochée si vous voulez autoriser les cellules vides dans la colonne.

Cela appliquera la règle de validation à la colonne et lorsqu’un utilisateur tentera de saisir un nombre autre que 1, 2, 3, etc… il sera averti que les données ne sont pas valides.

Autoriser uniquement les éléments d’une liste

La sélection d’éléments dans une liste déroulante est un excellent moyen d’éviter la saisie de texte incorrect, comme les noms de clients ou de produits.

Suivez ces étapes à partir du menu Validation des données pour créer une liste déroulante permettant de sélectionner des valeurs de texte dans une colonne.

1. Allez dans l’onglet Paramètres du menu Validation des données.
2. Sélectionnez l’option Liste dans la liste déroulante Autoriser.
3. Cochez l’option « In-cell dropdown ».
4. Ajoutez la liste d’éléments à l’entrée Source.
5. Appuyez sur le bouton OK.

📝 Remarque: si vous placez la liste des options possibles à l’intérieur d’un tableau Excel et que vous sélectionnez la colonne complète pour la référence de la source, alors la référence de la plage se mettra à jour au fur et à mesure que vous ajouterez des éléments au tableau.

Maintenant, lorsque vous sélectionnez une cellule dans la colonne, vous verrez une poignée de liste déroulante à droite de la cellule. Cliquez dessus et vous pourrez choisir une valeur dans une liste.

N’autoriser que les valeurs uniques

Supposons que vous vouliez vous assurer que la liste des produits de votre table Produits est unique. Vous pouvez utiliser l’option personnalisée dans les paramètres de validation pour y parvenir.

1. Allez dans l’onglet Paramètres du menu Validation des données.
2. Sélectionnez l’option Personnalisée dans la liste déroulante Autoriser.

=(COUNTIFS(INDIRECT("Products[Item]"),A2)=1)

1. Saisissez la formule ci-dessus dans l’entrée Formule.
2. Appuyez sur le bouton OK.

La formule compte le nombre de fois que la valeur de la ligne actuelle apparaît dans la colonne Produits [Article] en utilisant la fonction COUNTIFS.

Il détermine ensuite si ce nombre est égal à 1. Seules les valeurs où la formule est égale à 1 sont autorisées, ce qui signifie que le nom du produit ne peut pas avoir déjà figuré dans la liste.

📝 Remarque: vous devez référencer la colonne par son nom à l’aide de la fonction INDIRECT afin que la plage puisse s’étendre au fur et à mesure que vous ajoutez des éléments à votre tableau !

Afficher le message de saisie lorsque la cellule est sélectionnée

Le menu de validation des données vous permet d’afficher un message à vos utilisateurs lorsqu’une cellule est sélectionnée. Cela signifie que vous pouvez ajouter des instructions sur les types de valeurs autorisées dans la colonne.

Suivez ces étapes pour ajouter un message de saisie dans le menu Validation des données.

1. Allez sur l’onglet Message d’entrée dans le menu Validation des données.
2. Gardez l’option Afficher le message de saisie lorsque la cellule est sélectionnée cochée.
3. Ajoutez du texte dans la section Titre.
4. Ajoutez du texte à la section Message d’entrée.
5. Appuyez sur le bouton OK.

Lorsque vous sélectionnez une cellule dans la colonne qui contient la validation des données, une petite fenêtre pop-up jaune apparaît avec votre titre et le texte du message de saisie.

Prévenir les données non valides avec un message d’erreur

Lorsque vous avez mis en place une règle de validation des données, vous souhaitez généralement empêcher la saisie de données non valides.

Cela peut être réalisé en utilisant la fonction de message d’erreur dans le menu Validation des données.

1. Allez sur l’onglet Alerte d’erreur dans le menu Validation des données.
2. Ne décochez pas l’option Afficher une alerte d’erreur après la saisie de données non valides.
3. Sélectionnez l’option Stop dans la liste déroulante Style.
4. Ajoutez du texte dans la section Titre.
5. Ajoutez du texte à la section Message d’erreur.
6. Appuyez sur le bouton OK.

📝 Remarque: l’option Arrêter est essentielle si vous voulez empêcher la saisie des données non valides plutôt que de simplement avertir l’utilisateur que les données ne sont pas valides.

Lorsque vous essayez de saisir une valeur répétée dans la colonne, votre message d’erreur personnalisé s’affiche et empêche la saisie de la valeur dans la cellule.

Formulaire de saisie de données pour votre base de données Excel

Excel ne dispose pas de méthodes infaillibles pour garantir que les données sont saisies correctement, même lorsque les techniques de validation des données mentionnées précédemment sont utilisées.

Lorsqu’un utilisateur copie et colle ou coupe et colle des valeurs, cela peut annuler la validation des données dans une colonne et entraîner l’entrée de données incorrectes dans votre base de données.

L’utilisation d’un formulaire de saisie de données peut contribuer à éviter les erreurs de saisie et il existe plusieurs options différentes.

• Utilisez un tableau pour la saisie des données.
• Utilisez le formulaire de saisie de données de la barre d’outils à accès rapide.
• Utilisez Microsoft Forms pour la saisie des données.
• Utilisez l’application Microsoft Power Automate pour la saisie des données.
• Utilisez Microsoft Power Apps pour la saisie des données.

💡 Conseil: Consultez ce post pour plus de détails sur les différentes options de formulaire de saisie de données pour Excel.

Des outils tels que Microsoft Forms, Power Automate et Power Apps vous donneront davantage de contrôles de validation des données, d’accès et de sécurité sur votre saisie de données par rapport aux options de base d’Excel.

Accès et sécurité pour votre base de données Excel

Excel n’est pas une option sûre pour vos données.

Quelles que soient les mesures que vous mettez en place dans votre fichier Excel pour empêcher les utilisateurs de modifier les données par accident ou à dessein, elles ne seront pas infaillibles.

Toute personne ayant accès au fichier pourra créer, lire, mettre à jour et supprimer des données de votre base de données si elle est déterminée. Il n’existe pas non plus d’options permettant d’attribuer certains privilèges à certains utilisateurs dans un fichier Excel.

Cependant, vous pouvez gérer l’accès et la sécurité du fichier à partir de SharePoint.

💡 Conseil: Consultez ce billet de Microsoft sur les recommandations pour sécuriser les fichiers SharePoint pour plus de détails.

Lorsque vous stockez votre fichier Excel dans SharePoint, vous pouvez également voir les modifications récentes.

1. Allez à l’onglet Révision.
2. Cliquez sur la commande Afficher les modifications.

Cela ouvrira le volet des modifications sur le côté droit de la feuille Excel.

Il vous montrera une liste chronologique de tous les changements récents dans le classeur, qui a fait ces changements, quand il a fait le changement, et quelle était la valeur précédente.

Vous pouvez également cliquer avec le bouton droit de la souris sur une cellule et sélectionner Afficher les modifications. Cela ouvrira le volet des modifications filtré pour ne montrer que les modifications de cette cellule particulière.

C’est un excellent moyen de trouver la cause de toute erreur potentielle dans vos données.

Interroger votre base de données Excel avec Power Query

Votre fichier de base de données Excel ne doit être utilisé que pour ajouter, modifier ou supprimer des enregistrements dans vos tables.

Alors comment utiliser les données pour autre chose, par exemple pour créer des rapports, des analyses ou des tableaux de bord ?

C’est la magie de Power Query ! Il vous permet de vous connecter à votre base de données Excel et d’interroger les données en lecture seule. Vous pouvez construire tous vos rapports, analyses et tableaux de bord dans un fichier séparé qui peut facilement être rafraîchi avec les dernières données de votre fichier de base de données Excel.

Voici comment utiliser Power Query pour importer rapidement vos données dans n’importe quel fichier Excel.

1. Allez dans l’onglet Données.
2. Cliquez sur la commande Get Data.
3. Choisissez l’option From File.
4. Choisissez l’option From Excel Workbook dans le sous-menu.

Cela ouvrira un menu de sélection de fichiers dans lequel vous pourrez naviguer jusqu’à votre fichier de base de données Excel.

1. Sélectionnez votre fichier de base de données Excel.
2. Cliquez sur le bouton Importer.

⚠️ A ttention: Assurez-vous que votre fichier de base de données Excel est fermé ou le processus d’importation affichera un avertissement indiquant qu’il ne peut pas se connecter au fichier car il est en cours d’utilisation !

En cliquant sur le bouton Importer, vous ouvrez le menu Navigateur. C’est là que vous pouvez sélectionner les données à charger et l’endroit où elles doivent être chargées.

Le menu Navigateur répertorie tous les tableaux et toutes les feuilles du fichier Excel. Vos tableaux peuvent être listés avec un suffixe sur le nom si vous avez nommé les feuilles et les tableaux de la même façon.

1. Cochez l’option Sélectionner plusieurs éléments si vous voulez charger plus d’une table de votre base de données.
2. Sélectionnez les tableaux à charger.
3. Cliquez sur la petite flèche à côté du bouton Charger.
4. Sélectionnez l’option Charger vers dans le sous-menu Charger.

Cela ouvrira le menu Importer des données où vous pouvez choisir d’importer vos données dans un tableau, un tableau croisé dynamique, un graphique croisé dynamique, ou seulement créer une connexion aux données sans les charger.

1. Sélectionnez l’option Table.
2. Cliquez sur le bouton OK.

Vos données sont alors chargées dans un tableau Excel dans le nouveau classeur.

La meilleure partie est que vous pouvez toujours obtenir les dernières données du fichier de base de données source. Allez dans l’onglet Données et appuyez sur le bouton Rafraîchir tout pour rafraîchir la connexion Power Query et importer les dernières données.

💡 Conseil: vous pouvez faire bien plus que simplement charger des données avec Power Query. Vous pouvez également transformer vos données d’à peu près toutes les manières imaginables en utilisant le bouton Transformer les données dans le menu Navigateur. Consultez ce billet sur l’utilisation de Power Query pour plus de détails sur cet outil étonnant.

Analysez et résumez votre base de données Excel avec Power Pivot

Power Query n’est pas le seul outil de base de données dont dispose Excel. Le modèle de données et le complément Power Pivot vous aideront à découper des données relationnelles dans vos tableaux croisés dynamiques Excel.

Lorsque vous chargez vos données avec Power Quer, il y a une option pour Ajouter ces données au modèle de données dans le menu Importer des données.

Cette option vous permettra d’établir des relations entre les différentes tables de votre base de données. Ainsi, vous pourrez analyser vos commandes par catégorie même si ce champ n’apparaît pas dans la table Commandes.

Vous pouvez construire vos relations de table à partir de l’onglet Données.

1. Allez dans l’onglet Données.
2. Cliquez sur la commande Relations ou Gérer le modèle de données.

Vous pourrez désormais analyser plusieurs tableaux de votre base de données dans un seul tableau croisé dynamique !

Conclusions

Les données sont un élément essentiel de toute entreprise ou organisation. Si vous devez assurer le suivi des clients, des ventes, des stocks ou de toute autre information, vous avez besoin d’une base de données.

Si vous devez créer une base de données en respectant votre budget avec les outils dont vous disposez, Microsoft Excel est peut-être la meilleure option. Il convient parfaitement à toutes les données tabulaires en raison de sa structure en lignes et en colonnes.

Il y a beaucoup d’éléments à prendre en compte lorsqu’on utilise Excel comme base de données, notamment qui aura accès aux fichiers, quel type de données sera stocké et comment vous utiliserez les données.

Des fonctionnalités telles que Tables, validation des données, Power Query et Power Pivot sont toutes essentielles pour stocker, gérer et accéder correctement à vos données.