Utiliser Microsoft Query pour extraire des données externes

À l’aide de Microsoft Query, vous pouvez vous connecter à des sources de données externes, sélectionner des données à partir de ces sources externes, importer ces données dans votre feuille de calcul et actualiser les données si nécessaire pour que vos données de feuille de calcul restent synchronisées avec les données des sources externes.

Types de bases de données auxquelles vous pouvez accéder     Vous pouvez récupérer des données à partir de plusieurs types de bases de données, notamment Microsoft Office Access, Microsoft SQL Server et Microsoft SQL Server OLAP Services. Vous pouvez également récupérer des données à partir de classeurs Excel et de fichiers texte.

Microsoft Office fournit des pilotes que vous pouvez utiliser pour récupérer des données à partir des sources de données suivantes :

• Microsoft SQL Server Analysis Services (fournisseur OLAP )

• Microsoft Office Access

• dBASE

• Microsoft FoxPro

• Microsoft Office Excel

• Oracle

• Paradoxe

• Bases de données de fichiers texte

Vous pouvez également utiliser des pilotes ODBC ou des pilotes de source de données d’autres fabricants pour récupérer des informations à partir de sources de données qui ne sont pas répertoriées ici, y compris d’autres types de bases de données OLAP. Pour plus d’informations sur l’installation d’un pilote ODBC ou d’un pilote de source de données qui n’est pas répertorié ici, consultez la documentation de la base de données ou contactez le fournisseur de votre base de données.

Sélection de données à partir d’une base de données     Vous récupérez des données à partir d’une base de données en créant une requête, qui est une question que vous posez sur les données stockées dans une base de données externe. Par exemple, si vos données sont stockées dans une base de données Access, vous souhaiterez peut-être connaître les chiffres de vente d’un produit spécifique par région. Vous pouvez récupérer une partie des données en sélectionnant uniquement les données du produit et de la région que vous souhaitez analyser.

Avec Microsoft Query, vous pouvez sélectionner les colonnes de données souhaitées et importer uniquement ces données dans Excel.

Mise à jour de votre feuille de calcul en une seule opération     Une fois que vous avez des données externes dans un classeur Excel, chaque fois que votre base de données change, vous pouvez actualisation les données pour mettre à jour votre analyse, sans avoir à recréer vos rapports et graphiques de synthèse. Par exemple, vous pouvez créer un récapitulatif des ventes mensuelles et l’actualiser chaque mois lorsque les nouveaux chiffres de ventes arrivent.

Utilisation des sources de données par Microsoft Query     Après avoir configuré une source de données pour une base de données particulière, vous pouvez l’utiliser chaque fois que vous souhaitez créer une requête pour sélectionner et récupérer des données de cette base de données, sans avoir à retaper toutes les informations de connexion. Microsoft Query utilise la source de données pour se connecter à la base de données externe et vous montrer les données disponibles. Après avoir créé votre requête et retourné les données à Excel, Microsoft Query fournit au classeur Excel les informations de requête et de source de données afin que vous puissiez vous reconnecter à la base de données lorsque vous souhaitez actualiser les données.

Utilisation de Microsoft Query pour importer des données     pour importer des données externes dans Excel avec Microsoft Query, suivez ces étapes de base, chacune d’elles étant décrite plus en détail dans les sections suivantes.

Qu’est-ce qu’une source de données ?     Une source de données est un ensemble stocké d’informations qui permet à Excel et Microsoft Query de se connecter à une base de données externe. Lorsque vous utilisez Microsoft Query pour configurer une source de données, vous donnez un nom à la source de données, puis indiquez le nom et l’emplacement de la base de données ou du serveur, le type de base de données, ainsi que vos informations de connexion et de mot de passe. Les informations incluent également le nom d’un pilote OBDC ou d’un pilote de source de données, qui est un programme qui établit des connexions à un type spécifique de base de données.

Pour configurer une source de données à l’aide de Microsoft Query :

1. Sous l’onglet Données , dans le groupe Obtenir des données externes , cliquez sur À partir d’autres sources, puis sur À partir d’une requête Microsoft.

   Remarque : Excel 365 a déplacé Microsoft Query dans le groupe de menus Assistants hérités.  Ce menu n’est pas affiché par défaut.  Pour activer, accédez à Fichier, Options, Données, puis activez dans la section Afficher les Assistants d’importation de données héritées .

2. Effectuez l’une des opérations suivantes :

   • Pour spécifier une source de données pour une base de données, un fichier texte ou un classeur Excel, cliquez sur l’onglet Bases de données .

   • Pour spécifier une source de données de cube OLAP, cliquez sur l’onglet Cubes OLAP . Cet onglet est disponible uniquement si vous avez exécuté Microsoft Query à partir d’Excel.

3. Double-cliquez sur <nouvelle source de données>.

   -ou-

   Cliquez sur <nouvelle source de données>, puis sur OK.

   La boîte de dialogue Créer une source de données s’affiche.

4. À l’étape 1, tapez un nom pour identifier la source de données.

5. À l’étape 2, cliquez sur un pilote correspondant au type de base de données que vous utilisez comme source de données.

   Remarques : 

   • Si la base de données externe à laquelle vous souhaitez accéder n’est pas prise en charge par les pilotes ODBC installés avec Microsoft Query, vous devez obtenir et installer un pilote ODBC compatible Microsoft Office auprès d’un fournisseur tiers, tel que le fabricant de la base de données. Contactez le fournisseur de base de données pour obtenir des instructions d’installation.

   • Les bases de données OLAP ne nécessitent pas de pilotes ODBC. Lorsque vous installez Microsoft Query, les pilotes sont installés pour les bases de données créées à l’aide de Microsoft SQL Server Analysis Services. Pour vous connecter à d’autres bases de données OLAP, vous devez installer un pilote de source de données et un logiciel client.

6. Cliquez sur Se connecter, puis fournissez les informations nécessaires pour vous connecter à votre source de données. Pour les bases de données, les classeurs Excel et les fichiers texte, les informations que vous fournissez dépendent du type de source de données que vous avez sélectionné. Vous pouvez être invité à fournir un nom d’ouverture de session, un mot de passe, la version de la base de données que vous utilisez, l’emplacement de la base de données ou d’autres informations spécifiques au type de base de données.

   Important : 

   • Utilisez un mot de passe fort qui associe des lettres majuscules et minuscules, des nombres et des symboles. Les mots de passe faibles ne combinent pas ces éléments. Mot de passe fort : Y6dh!et5. Mot de passe faible : Maison27. Les mots de passe doivent comporter au moins 8 caractères. Nous vous conseillons d’en utiliser au moins 14.

   • Il est très important de ne pas oublier votre mot de passe. Si cela se produit, Microsoft ne pourra pas le récupérer. Stockez les mots de passe que vous écrivez dans un endroit sûr, éloigné des informations qu’ils sont censés protéger.

7. Après avoir entré les informations requises, cliquez sur OK ou sur Terminer pour revenir à la boîte de dialogue Créer une source de données .

8. Si votre base de données contient des tables et que vous souhaitez qu’une table particulière s’affiche automatiquement dans l’Assistant Requête, cliquez sur la zone de l’étape 4, puis cliquez sur la table souhaitée.

9. Si vous ne souhaitez pas taper votre nom d’ouverture de session et votre mot de passe lorsque vous utilisez la source de données, activez la case à cocher Enregistrer mon id utilisateur et mon mot de passe dans la définition de la source de données . Le mot de passe enregistré n’est pas chiffré. Si la case à cocher n’est pas disponible, consultez votre administrateur de base de données pour déterminer si cette option peut être rendue disponible.

   Note de sécurité : Évitez d’enregistrer les informations de connexion lors de la connexion à des sources de données. Ces informations peuvent être stockées sous forme de texte brut et un utilisateur malveillant peut y accéder pour compromettre la sécurité de la source de données.

Une fois ces étapes terminées, le nom de votre source de données s’affiche dans la boîte de dialogue Choisir une source de données .

Utiliser l’Assistant Requête pour la plupart des requêtes     L’Assistant Requête facilite la sélection et la collecte de données à partir de différentes tables et champs de votre base de données. À l’aide de l’Assistant Requête, vous pouvez sélectionner les tables et les champs que vous souhaitez inclure. Une jointure interne (opération de requête qui spécifie que les lignes de deux tables sont combinées en fonction de valeurs de champ identiques) est créée automatiquement lorsque l’Assistant reconnaît un champ de clé primaire dans une table et un champ portant le même nom dans une deuxième table.

Vous pouvez également utiliser l’Assistant pour trier le jeu de résultats et effectuer un filtrage simple. Dans la dernière étape de l’Assistant, vous pouvez choisir de retourner les données dans Excel ou d’affiner davantage la requête dans Microsoft Query. Après avoir créé la requête, vous pouvez l’exécuter dans Excel ou dans Microsoft Query.

Pour démarrer l’Assistant Requête, procédez comme suit.

1. Sous l’onglet Données , dans le groupe Obtenir des données externes , cliquez sur À partir d’autres sources, puis sur À partir d’une requête Microsoft.

2. Dans la boîte de dialogue Choisir une source de données , vérifiez que la case à cocher Utiliser l’Assistant Requête pour créer/modifier des requêtes est cochée.

3. Double-cliquez sur la source de données que vous souhaitez utiliser.

   -ou-

   Cliquez sur la source de données que vous souhaitez utiliser, puis cliquez sur OK.

Travailler directement dans Microsoft Query pour d’autres types de requêtes     Si vous souhaitez créer une requête plus complexe que l’Assistant Requête, vous pouvez travailler directement dans Microsoft Query. Vous pouvez utiliser Microsoft Query pour afficher et modifier les requêtes que vous commencez à créer dans l’Assistant Requête, ou vous pouvez créer de nouvelles requêtes sans utiliser l’Assistant. Travaillez directement dans Microsoft Query lorsque vous souhaitez créer des requêtes qui effectuent les opérations suivantes :

• Sélectionner des données spécifiques à partir d’un champ     Dans une base de données volumineuse, vous pouvez choisir certaines des données d’un champ et omettre les données dont vous n’avez pas besoin. Par exemple, si vous avez besoin de données pour deux des produits dans un champ qui contient des informations pour de nombreux produits, vous pouvez utiliser critères pour sélectionner des données pour les deux produits souhaités uniquement.

• Récupérer des données en fonction de différents critères chaque fois que vous exécutez la requête     Si vous avez besoin de créer le même rapport Excel ou le même résumé pour plusieurs zones dans les mêmes données externes, par exemple un rapport de ventes distinct pour chaque région, vous pouvez créer un requête avec paramètres. Lorsque vous exécutez une requête de paramètre, vous êtes invité à indiquer une valeur à utiliser comme critère lorsque la requête sélectionne des enregistrements. Par exemple, une requête de paramètre peut vous inviter à entrer une région spécifique, et vous pouvez réutiliser cette requête pour créer chacun de vos rapports de ventes régionaux.

• Joindre des données de différentes manières     Les jointures internes créées par l’Assistant Requête sont le type de jointure le plus courant utilisé dans la création de requêtes. Toutefois, vous souhaitez parfois utiliser un autre type de jointure. Par exemple, si vous disposez d’une table d’informations sur les ventes de produits et d’une table d’informations client, une jointure interne (type créé par l’Assistant Requête) empêche la récupération des enregistrements client pour les clients qui n’ont pas effectué d’achat. À l’aide de Microsoft Query, vous pouvez joindre ces tables afin que tous les enregistrements clients soient récupérés, ainsi que les données de vente pour les clients qui ont effectué des achats.

Pour démarrer Microsoft Query, procédez comme suit.

1. Sous l’onglet Données , dans le groupe Obtenir des données externes , cliquez sur À partir d’autres sources, puis sur À partir d’une requête Microsoft.

2. Dans la boîte de dialogue Choisir une source de données , vérifiez que la case à cocher Utiliser l’Assistant Requête pour créer/modifier des requêtes est désactivée.

3. Double-cliquez sur la source de données que vous souhaitez utiliser.

   -ou-

   Cliquez sur la source de données que vous souhaitez utiliser, puis cliquez sur OK.

Réutilisation et partage de requêtes     Dans l’Assistant Requête et Microsoft Query, vous pouvez enregistrer vos requêtes sous la forme d’un fichier .dqy que vous pouvez modifier, réutiliser et partager. Excel peut ouvrir des fichiers .dqy directement, ce qui vous permet, à vous ou à d’autres utilisateurs, de créer des plages de données externes supplémentaires à partir de la même requête.

Pour ouvrir une requête enregistrée à partir d’Excel :

1. Sous l’onglet Données , dans le groupe Obtenir des données externes , cliquez sur À partir d’autres sources, puis sur À partir d’une requête Microsoft. La boîte de dialogue Choisir une source de données s’affiche.

2. Dans la boîte de dialogue Choisir une source de données , cliquez sur l’onglet Requêtes .

3. Double-cliquez sur la requête enregistrée que vous souhaitez ouvrir. La requête s’affiche dans Microsoft Query.

Si vous souhaitez ouvrir une requête enregistrée et que Microsoft Query est déjà ouvert, cliquez sur le menu Fichier de requête Microsoft, puis cliquez sur Ouvrir.

Si vous double-cliquez sur un fichier .dqy, Excel s’ouvre, exécute la requête, puis insère les résultats dans une nouvelle feuille de calcul.

Si vous souhaitez partager un résumé ou un rapport Excel basé sur des données externes, vous pouvez donner à d’autres utilisateurs un classeur qui contient une plage de données externe, ou vous pouvez créer un modèle. Un modèle vous permet d’enregistrer le résumé ou le rapport sans enregistrer les données externes afin que le fichier soit plus petit. Les données externes sont récupérées lorsqu’un utilisateur ouvre le modèle de rapport.

Après avoir créé une requête dans l’Assistant Requête ou Microsoft Query, vous pouvez retourner les données dans une feuille de calcul Excel. Les données deviennent alors un plage de données externes ou un rapport de tableau croisé dynamique que vous pouvez mettre en forme et actualiser.

Mise en forme des données récupérées     Dans Excel, vous pouvez utiliser des outils, tels que des graphiques ou des sous-totaux automatiques, pour présenter et résumer les données récupérées par Microsoft Query. Vous pouvez mettre en forme les données et votre mise en forme est conservée lorsque vous actualisez les données externes. Vous pouvez utiliser vos propres étiquettes de colonne au lieu des noms de champs et ajouter automatiquement des numéros de ligne.

Excel peut automatiquement mettre en forme les nouvelles données que vous tapez à la fin d’une plage pour qu’elles correspondent aux lignes précédentes. Excel peut également copier automatiquement les formules qui ont été répétées dans les lignes précédentes et les étendre à des lignes supplémentaires.

Vous pouvez activer cette option (ou désactiver à nouveau) à tout moment :

1. Cliquez sur Fichier > Options > Options avancées.

2. Dans la section Options de modification , cochez la case Étendre les formats et formules de plage de données . Pour désactiver à nouveau la mise en forme automatique des plages de données, décochez cette case.

Actualisation des données externes     Lorsque vous actualisez des données externes, vous exécutez la requête pour récupérer toutes les données nouvelles ou modifiées qui correspondent à vos spécifications. Vous pouvez actualiser une requête dans Microsoft Query et Excel. Excel fournit plusieurs options pour actualiser les requêtes, notamment l’actualisation des données chaque fois que vous ouvrez le classeur et son actualisation automatique à intervalles réguliers. Vous pouvez continuer à travailler dans Excel pendant l’actualisation des données, et vous pouvez également vérifier l’état pendant l’actualisation des données. Pour plus d’informations, consultez Actualiser une connexion de données externes dans Excel.

Haut de la page