Remarque : Microsoft Access ne prend pas en charge l’importation de données Excel avec une étiquette de confidentialité appliquée. Pour contourner ce problème, vous pouvez supprimer l’étiquette avant l’importation, puis la réappliquer après l’importation. Pour plus d’informations, voir Appliquer des étiquettes de confidentialité à vos fichiers et e-mails dans Office.
Cet article explique comment déplacer vos données d’Excel vers Access et convertir vos données en tables relationnelles afin de pouvoir utiliser Microsoft Excel et Access ensemble. Pour résumer, Access est idéal pour capturer, stocker, interroger et partager des données, et Excel est idéal pour le calcul, l’analyse et la visualisation des données.
Deux articles, Utilisation d’Access ou d’Excel pour gérer vos données et Les 10 principales raisons d’utiliser Access avec Excel, expliquent quel programme est le mieux adapté à une tâche particulière et comment utiliser Excel et Access ensemble pour créer une solution pratique.
Lorsque vous déplacez des données d’Excel vers Access, le processus comporte trois étapes de base.
Remarque : Pour plus d’informations sur la modélisation des données et les relations dans Access, consultez Concepts de base de la conception de base de données.
Étape 1 : Importer des données d’Excel vers Access
L’importation de données est une opération qui peut se passer beaucoup plus facilement si vous prenez un certain temps pour préparer et nettoyer vos données. L’importation de données revient à migrer vers une nouvelle maison. Si vous nettoyez et organisez vos biens avant de déménager, s’installer dans votre nouvelle maison est beaucoup plus facile.
Nettoyer vos données avant l’importation
Avant d’importer des données dans Access, dans Excel, il est judicieux de :
-
Convertissez les cellules qui contiennent des données non atomiques (autrement dit, plusieurs valeurs dans une cellule) en plusieurs colonnes. Par exemple, une cellule d’une colonne « Compétences » qui contient plusieurs valeurs de compétence, telles que « Programmation C# », « Programmation VBA » et « Conception web » doit être divisée en colonnes distinctes qui contiennent chacune une seule valeur de compétence.
-
Utilisez la commande TRIM pour supprimer les espaces de début, de fin et de plusieurs espaces incorporés.
-
Supprimez les caractères qui ne sont pas imprimables.
-
Recherchez et corrigez les fautes d’orthographe et de ponctuation.
-
Supprimez les lignes en double ou les champs dupliqués.
-
Assurez-vous que les colonnes de données ne contiennent pas de formats mixtes, en particulier des nombres au format texte ou des dates mises en forme en tant que nombres.
Pour plus d’informations, consultez les rubriques d’aide Excel suivantes :
Remarque : Si vos besoins en matière de nettoyage des données sont complexes, ou si vous n’avez pas le temps ou les ressources nécessaires pour automatiser le processus par vous-même, vous pouvez envisager d’utiliser un fournisseur tiers. Pour plus d’informations, recherchez « logiciel de nettoyage des données » ou « qualité des données » par votre moteur de recherche favori dans votre navigateur Web.
Choisir le meilleur type de données lors de l’importation
Pendant l’opération d’importation dans Access, vous souhaitez faire de bons choix afin de recevoir peu (le cas échéant) d’erreurs de conversion qui nécessitent une intervention manuelle. Le tableau suivant récapitule la façon dont les formats numériques Excel et les types de données Access sont convertis lorsque vous importez des données d’Excel vers Access, et fournit des conseils sur les meilleurs types de données à choisir dans l’Assistant Importation d’une feuille de calcul.
Format numérique Excel |
Type de données Access |
Commentaires |
Bonne pratique |
---|---|---|---|
Texte |
Texte, mémo |
Le type de données Texte Access stocke les données alphanumériques jusqu’à 255 caractères. Le type de données Mémo Access stocke les données alphanumériques jusqu’à 65 535 caractères. |
Choisissez Mémo pour éviter de tronquer des données. |
Nombre, Pourcentage, Fraction, Scientifique |
Nombre |
Access a un type de données Number qui varie en fonction d’une propriété Taille de champ (Octet, Entier, Entier long, Simple, Double, Décimal). |
Choisissez Double pour éviter toute erreur de conversion de données. |
Date |
Date |
Access et Excel utilisent le même numéro de série pour stocker les dates. Dans Access, la plage de dates est plus grande : de -657 434 (1er janvier 100 après J.-C.) à 2 958 465 (31 décembre 9999 après J.-C.). Étant donné qu’Access ne reconnaît pas le système de dates 1904 (utilisé dans Excel pour Macintosh), vous devez convertir les dates dans Excel ou Access pour éviter toute confusion. Pour plus d’informations, voir Modifier le système de dates, le format ou l’interprétation de l’année à deux chiffres et Importer ou lier des données dans un classeur Excel. |
Choisissez Date. |
Heure |
Heure |
Access et Excel stockent les valeurs de temps à l’aide du même type de données. |
Choisissez Heure, qui est généralement la valeur par défaut. |
Devise, Comptabilité |
Devise |
Dans Access, le type de données Currency stocke les données sous forme de nombres de 8 octets avec une précision à quatre décimales, et est utilisé pour stocker des données financières et empêcher l’arrondi des valeurs. |
Choisissez Devise, qui est généralement la valeur par défaut. |
Booléen |
Oui/non |
Access utilise -1 pour toutes les valeurs Oui et 0 pour toutes les valeurs Non, tandis qu’Excel utilise 1 pour toutes les valeurs TRUE et 0 pour toutes les valeurs FALSE. |
Choisissez Oui/Non, ce qui convertit automatiquement les valeurs sous-jacentes. |
Lien hypertexte |
Lien hypertexte |
Un lien hypertexte dans Excel et Access contient une URL ou une adresse web sur laquelle vous pouvez cliquer et suivre. |
Choisissez Lien hypertexte, sinon Access peut utiliser le type de données Texte par défaut. |
Une fois les données dans Access, vous pouvez supprimer les données Excel. N’oubliez pas de sauvegarder d’abord le classeur Excel d’origine avant de le supprimer.
Pour plus d’informations, consultez la rubrique d’aide d’Access Importer ou lier des données dans un classeur Excel.
Ajouter automatiquement des données de manière simple
Un problème courant rencontré par les utilisateurs d’Excel est l’ajout de données avec les mêmes colonnes dans une grande feuille de calcul. Par exemple, vous disposez peut-être d’une solution de suivi des ressources qui a démarré dans Excel, mais qui s’est maintenant développée pour inclure des fichiers provenant de nombreux groupes de travail et services. Ces données peuvent se trouver dans des feuilles de calcul et des classeurs différents, ou dans des fichiers texte qui sont des flux de données provenant d’autres systèmes. Il n’existe aucune commande d’interface utilisateur ni moyen simple d’ajouter des données similaires dans Excel.
La meilleure solution consiste à utiliser Access, où vous pouvez facilement importer et ajouter des données dans une table à l’aide de l’Assistant Importation d’une feuille de calcul. En outre, vous pouvez ajouter un grand nombre de données dans une table. Vous pouvez enregistrer les opérations d’importation, les ajouter en tant que tâches Microsoft Outlook planifiées et même utiliser des macros pour automatiser le processus.
Étape 2 : Normaliser les données à l’aide de l’Assistant Analyseur de table
À première vue, le processus de normalisation de vos données peut sembler une tâche ardue. Heureusement, la normalisation des tables dans Access est un processus beaucoup plus facile grâce à l’Assistant Analyseur de table.
1. Faire glisser les colonnes sélectionnées vers une nouvelle table et créer automatiquement des relations
2. Utilisez les commandes de bouton pour renommer une table, ajouter une clé primaire, faire d’une colonne existante une clé primaire et annuler la dernière action
Vous pouvez utiliser cet Assistant pour effectuer les opérations suivantes :
-
Convertissez une table en un ensemble de tables plus petites et créez automatiquement une relation de clé primaire et de clé étrangère entre les tables.
-
Ajoutez une clé primaire à un champ existant qui contient des valeurs uniques, ou créez un champ ID qui utilise le type de données NuméroAuto.
-
Créez automatiquement des relations pour appliquer l’intégrité référentielle avec des mises à jour en cascade. Les suppressions en cascade ne sont pas ajoutées automatiquement pour empêcher la suppression accidentelle des données, mais vous pouvez facilement ajouter des suppressions en cascade ultérieurement.
-
Recherchez dans de nouvelles tables des données redondantes ou dupliquées (par exemple, le même client avec deux numéros de téléphone différents) et mettez-les à jour comme vous le souhaitez.
-
Sauvegardez la table d’origine et renommez-la en ajoutant « _OLD » à son nom. Ensuite, vous créez une requête qui reconstruit la table d’origine, avec le nom de la table d’origine afin que tous les formulaires ou états existants basés sur la table d’origine fonctionnent avec la nouvelle structure de table.
Pour plus d’informations, consultez Normaliser vos données à l’aide de l’analyseur de table.
Étape 3 : Se connecter aux données Access à partir d’Excel
Une fois que les données ont été normalisées dans Access et qu’une requête ou une table a été créée pour reconstruire les données d’origine, il s’agit simplement de se connecter aux données Access à partir d’Excel. Vos données se trouvent désormais dans Access en tant que source de données externe et peuvent donc être connectées au classeur via une connexion de données, qui est un conteneur d’informations utilisé pour localiser, ouvrir une session et accéder à la source de données externe. Les informations de connexion sont stockées dans le classeur et peuvent également être stockées dans un fichier de connexion, tel qu’un fichier ODC (Office Data Connection) (extension de nom de fichier .odc) ou un fichier nom de source de données (extension .dsn). Après vous être connecté à des données externes, vous pouvez également actualiser (ou mettre à jour) automatiquement votre classeur Excel à partir d’Access chaque fois que les données sont mises à jour dans Access.
Pour plus d’informations, consultez Importer des données à partir de sources de données externes (Power Query).
Obtenir vos données dans Access
Cette section vous guide tout au long des phases suivantes de normalisation de vos données : cassage des valeurs dans les colonnes Salesperson et Address en leurs éléments les plus atomiques, séparation des sujets connexes dans leurs propres tables, copie et collage de ces tableaux d’Excel dans Access, création de relations clés entre les tables Access nouvellement créées et création et exécution d’une requête simple dans Access pour retourner des informations.
Exemples de données sous forme non normalisée
La feuille de calcul suivante contient des valeurs non atomiques dans la colonne Salesperson et la colonne Address. Les deux colonnes doivent être divisées en deux colonnes distinctes ou plus. Cette feuille de calcul contient également des informations sur les vendeurs, les produits, les clients et les commandes. Ces informations doivent également être fractionnées, par objet, dans des tables distinctes.
Vendeur |
Réf commande |
Date de commande |
Réf produit |
Qté |
Prix |
Nom du client |
Address (Adresse) |
Téléphone |
---|---|---|---|---|---|---|---|---|
Li, Yale |
2349 |
3/4/09 |
C-789 |
3 |
7,00 $ |
Fourth Coffee |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Li, Yale |
2349 |
3/4/09 |
C-795 |
6 |
9,75 $ |
Fourth Coffee |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Adams, Ellen |
2350 |
3/4/09 |
A-2275 |
2 |
16,75 $ |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Adams, Ellen |
2350 |
3/4/09 |
F-198 |
6 |
5,25 $ |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Adams, Ellen |
2350 |
3/4/09 |
B-205 |
1 |
4,50 $ |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Hance, Jim |
2351 |
3/4/09 |
C-795 |
6 |
9,75 $ |
Contoso, Ltd. |
2302 Harvard Ave Bellevue, WA 98227 |
425-555-0222 |
Hance, Jim |
2352 |
3/5/09 |
A-2275 |
2 |
16,75 $ |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Hance, Jim |
2352 |
3/5/09 |
D-4420 |
3 |
7,25 $ |
Adventure Works |
1025 Columbia Circle Kirkland, WA 98234 |
425-555-0185 |
Koch, Reed |
2353 |
3/7/09 |
A-2275 |
6 |
16,75 $ |
Fourth Coffee |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Koch, Reed |
2353 |
3/7/09 |
C-789 |
5 |
7,00 $ |
Fourth Coffee |
7007 Cornell St Redmond, WA 98199 |
425-555-0201 |
Informations dans leurs plus petites parties : données atomiques
En utilisant les données de cet exemple, vous pouvez utiliser la commande Texte en colonne dans Excel pour séparer les parties « atomiques » d’une cellule (telles que l’adresse postale, la ville, l’état et le code postal) en colonnes discrètes.
Le tableau suivant montre les nouvelles colonnes de la même feuille de calcul après avoir été fractionnées pour rendre toutes les valeurs atomiques. Notez que les informations de la colonne Vendeur ont été divisées en colonnes Nom et Prénom, et que les informations de la colonne Adresse ont été divisées en colonnes Adresse postale, Ville, État et Code postal. Ces données sont sous « première forme normale ».
Nom |
Prénom |
|
Rue |
Ville |
État |
Code postal |
---|---|---|---|---|---|---|
Li |
Yale |
2302 Harvard Ave |
Bellevue |
WA |
98227 |
|
Adams |
Ellen |
1025 Columbia Circle |
Strasbourg |
WA |
98234 |
|
Hance |
Jim |
2302 Harvard Ave |
Bellevue |
WA |
98227 |
|
Koch |
Roseau |
7007 Cornell St Redmond |
Redmond |
WA |
98199 |
Fractionner des données en sujets organisés dans Excel
Les plusieurs tableaux d’exemples de données qui suivent affichent les mêmes informations de la feuille de calcul Excel une fois qu’elle a été divisée en tableaux pour les vendeurs, les produits, les clients et les commandes. La conception de la table n’est pas finale, mais elle est sur la bonne voie.
La table Salespersons contient uniquement des informations sur le personnel de vente. Notez que chaque enregistrement a un ID unique (SALESPERSON ID). La valeur de l’ID salesperson sera utilisée dans la table Commandes pour connecter les commandes aux vendeurs.
Vendeurs |
||
---|---|---|
ID du vendeur |
Nom |
Prénom |
101 |
Li |
Yale |
103 |
Adams |
Ellen |
105 |
Hance |
Jim |
107 |
Koch |
Roseau |
La table Products contient uniquement des informations sur les produits. Notez que chaque enregistrement a un ID unique (ID de produit). La valeur ID de produit sera utilisée pour connecter les informations sur le produit à la table Détails de la commande.
Produits |
|
---|---|
Réf produit |
Prix |
A-2275 |
16.75 |
B-205 |
4.50 |
C-789 |
7,00 |
C-795 |
9.75 |
D-4420 |
7.25 |
F-198 |
5.25 |
La table Customers contient uniquement des informations sur les clients. Notez que chaque enregistrement a un ID unique (ID client). La valeur Id client sera utilisée pour connecter les informations client à la table Commandes.
Clients |
||||||
---|---|---|---|---|---|---|
Réf consommateur |
Nom |
Rue |
Ville |
État |
Code postal |
Téléphone |
1001 |
Contoso, Ltd. |
2302 Harvard Ave |
Bellevue |
WA |
98227 |
425-555-0222 |
1003 |
Adventure Works |
1025 Columbia Circle |
Strasbourg |
WA |
98234 |
425-555-0185 |
1005 |
Fourth Coffee |
7007, rue Cornell |
Redmond |
WA |
98199 |
425-555-0201 |
La table Commandes contient des informations sur les commandes, les vendeurs, les clients et les produits. Notez que chaque enregistrement a un ID unique (ID de commande). Certaines informations de cette table doivent être divisées en une table supplémentaire qui contient les détails de la commande afin que la table Commandes ne contienne que quatre colonnes : l’ID de commande unique, la date de commande, l’ID du vendeur et l’ID client. La table présentée ici n’a pas encore été divisée dans la table Détails de la commande.
Commandes |
|||||
---|---|---|---|---|---|
Réf commande |
Date de commande |
ID du vendeur |
Réf consommateur |
Réf produit |
Qté |
2349 |
3/4/09 |
101 |
1005 |
C-789 |
3 |
2349 |
3/4/09 |
101 |
1005 |
C-795 |
6 |
2350 |
3/4/09 |
103 |
1003 |
A-2275 |
2 |
2350 |
3/4/09 |
103 |
1003 |
F-198 |
6 |
2350 |
3/4/09 |
103 |
1003 |
B-205 |
1 |
2351 |
3/4/09 |
105 |
1001 |
C-795 |
6 |
2352 |
3/5/09 |
105 |
1003 |
A-2275 |
2 |
2352 |
3/5/09 |
105 |
1003 |
D-4420 |
3 |
2353 |
3/7/09 |
107 |
1005 |
A-2275 |
6 |
2353 |
3/7/09 |
107 |
1005 |
C-789 |
5 |
Les détails de la commande, tels que l’ID de produit et la quantité, sont déplacés hors de la table Commandes et stockés dans une table nommée Détails de la commande. Gardez à l’esprit qu’il y a 9 ordres, il est donc logique qu’il y ait 9 enregistrements dans ce tableau. Notez que la table Orders a un ID unique (ID de commande), auquel fait référence à partir de la table Détails de la commande.
La conception finale de la table Orders doit se présenter comme suit :
Commandes |
|||
---|---|---|---|
Réf commande |
Date de commande |
ID du vendeur |
Réf consommateur |
2349 |
3/4/09 |
101 |
1005 |
2350 |
3/4/09 |
103 |
1003 |
2351 |
3/4/09 |
105 |
1001 |
2352 |
3/5/09 |
105 |
1003 |
2353 |
3/7/09 |
107 |
1005 |
La table Détails de la commande ne contient aucune colonne qui nécessite des valeurs uniques (c’est-à-dire, il n’y a pas de clé primaire). Il est donc acceptable que toutes les colonnes contiennent des données « redondantes ». Toutefois, deux enregistrements de cette table ne doivent pas être complètement identiques (cette règle s’applique à n’importe quelle table d’une base de données). Dans ce tableau, il doit y avoir 17 enregistrements, chacun correspondant à un produit dans une commande individuelle. Par exemple, dans l’ordre 2349, trois produits C-789 comprennent l’une des deux parties de la commande entière.
La table Order Details doit donc se présenter comme suit :
Détails de la commande |
||
---|---|---|
Réf commande |
Réf produit |
Qté |
2349 |
C-789 |
3 |
2349 |
C-795 |
6 |
2350 |
A-2275 |
2 |
2350 |
F-198 |
6 |
2350 |
B-205 |
1 |
2351 |
C-795 |
6 |
2352 |
A-2275 |
2 |
2352 |
D-4420 |
3 |
2353 |
A-2275 |
6 |
2353 |
C-789 |
5 |
Copie et collage de données d’Excel dans Access
Maintenant que les informations sur les vendeurs, les clients, les produits, les commandes et les détails des commandes ont été réparties dans des sujets distincts dans Excel, vous pouvez copier ces données directement dans Access, où elles deviendront des tables.
Création de relations entre les tables Access et exécution d’une requête
Une fois que vous avez déplacé vos données vers Access, vous pouvez créer des relations entre les tables, puis créer des requêtes pour retourner des informations sur différents sujets. Par exemple, vous pouvez créer une requête qui retourne l’ID de commande et les noms des vendeurs pour les commandes entrées entre le 09/05/03 et le 08/09/3.
En outre, vous pouvez créer des formulaires et des rapports pour faciliter l’entrée des données et l’analyse des ventes.
Vous avez besoin d’une aide supplémentaire ?
Vous pouvez toujours poser des questions à un expert de la Communauté technique Excel ou obtenir une assistance dans la Communauté de support.