Utiliser Microsoft Query pour extraire des données externes

Microsoft Query vous permet d’extraire des données de sources externes. Lorsque vous utilisez Microsoft Query pour extraire des données de vos bases de données et fichiers d’entreprise, vous n’avez pas besoin de retaper les données à analyser dans Excel. Vous pouvez également actualiser automatiquement vos rapports et résumés Excel à partir de la base de données source d’origine chaque fois que celle-ci est mise à jour avec de nouvelles informations.

En savoir plus sur Microsoft Query

À l’aide de Microsoft Query, vous pouvez vous connecter à des sources de données externes, sélectionner des données provenant de ces sources externes, importer ces données dans votre feuille de calcul et actualiser les données si nécessaire pour maintenir les données de votre feuille de calcul 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 qui permettent de 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, case activée la documentation de la base de données ou contactez votre fournisseur de base de données.

Sélection de données dans une base de données Vous récupérez des données 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 extraire 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 actualiser les données pour mettre à jour votre analyse, sans avoir à recréer vos rapports de synthèse et graphiques. Par exemple, vous pouvez créer un résumé mensuel des ventes et l’actualiser tous les mois lorsque les nouveaux chiffres de vente arrivent.

Comment Microsoft Query utilise les sources de données Une fois que vous avez 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. Une fois que vous avez créé votre requête et renvoyé les données à Excel, Microsoft Query fournit au classeur Excel les informations relatives à la requête et à la source de données, de sorte que vous pouvez 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’entre elles étant décrite plus en détail dans les sections suivantes.

Se connecter à une source de données

Qu’est-ce qu’une source de données ?  Une source de données est un ensemble d’informations stocké 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 vous fournissez 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 de Microsoft Query.

   Remarque

   Excel 365 a déplacé Microsoft Query dans le groupe de menus des Assistants hérités .  Ce menu n’est pas affiché par défaut.  Pour activer cette option, accédez à Fichier, Options, Données et activez l’option 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, saisissez 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.

   Remarque

   • Si la base de données externe à laquelle vous voulez 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 la base de données pour obtenir les 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 le logiciel client.

6. Cliquez sur Se connecter, puis fournissez les informations nécessaires à la connexion à votre source de données. Pour les bases de données, classeurs Excel et fichiers texte, les informations que vous fournissez dépendent du type de source de données que vous avez sélectionné. Vous devrez peut-être fournir un nom d’utilisateur, 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 du 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. Une fois que vous avez 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 entrer votre nom d’utilisateur et votre mot de passe quand vous utilisez la source de données, activez la case à cocher Enregistrer mon identifiant utilisateur et mon mot de passe dans la case activée de la définition de source de données. Le mot de passe enregistré n’est pas chiffré. Si la case activée n’est pas disponible, contactez votre administrateur de base de données pour déterminer si cette option peut être disponible.

   Remarque

   É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 accéder aux informations pour compromettre la sécurité de la source de données.

Une fois ces étapes effectuées, le nom de votre source de données apparaît dans la boîte de dialogue Choisir une source de données .

Utiliser l’Assistant Requête pour définir une requête

Utiliser l’Assistant Requête pour la plupart des requêtes L’Assistant Requête permet de sélectionner et de regrouper facilement les données de différentes tables et champs de votre base de données. A 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 sur la base 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. À la dernière étape de l’Assistant, vous pouvez choisir de renvoyer les données à 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 de Microsoft Query.
2. Dans la boîte de dialogue Choisir une source de données, assurez-vous que la case Utiliser l’Assistant Requête pour créer/modifier des requêtes case activée est activé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 sur OK.

Travaillez directement dans Microsoft Query pour d’autres types de requêtes Si vous souhaitez créer une requête plus complexe que celle offerte par 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 dans un champ Dans une base de données volumineuse, vous pouvez choisir certaines données d’un champ et omettre celles dont vous n’avez pas besoin. Par exemple, si vous avez besoin de données pour deux des produits d’un champ qui contient des informations pour plusieurs produits, vous pouvez utiliser des critères pour sélectionner uniquement les données des deux produits souhaités.
• Récupérer des données en fonction de critères différents chaque fois que vous exécutez la requête Si vous devez créer le même rapport ou résumé Excel pour plusieurs domaines dans les mêmes données externes (par exemple, un rapport de ventes distinct pour chaque région), vous pouvez créer une requête avec paramètres. Lorsque vous exécutez une requête avec paramètres, vous êtes invité à entrer une valeur à utiliser comme critère lorsque la requête sélectionne des enregistrements. Par exemple, une requête avec paramètres 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égionales.
• Joindre des données de différentes manières Les jointures internes créées par l’Assistant Requête constituent le type de jointure le plus couramment utilisé pour créer des requêtes. Toutefois, il peut arriver que vous souhaitiez utiliser un autre type de jointure. Par exemple, si vous avez une table d’informations sur les ventes de produits et une table d’informations sur les clients, une jointure interne (du 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 de récupérer tous les enregistrements de clients, ainsi que les données de ventes des clients qui ont effectué des achats.

Pour démarrer Microsoft Query, effectuez les étapes suivantes.

1. Sous l’onglet Données , dans le groupe Obtenir des données externes , cliquez sur À partir d’autres sources, puis sur À partir de Microsoft Query.
2. Dans la boîte de dialogue Choisir une source de données, assurez-vous que la case Utiliser l’Assistant Requête pour créer/modifier des requêtes case activée 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 sur OK.

Réutilisation et partage de requêtes Tant dans l’Assistant Requête que dans 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 directement des fichiers .dqy, 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 de Microsoft Query. 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 est affichée 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 Microsoft Query, puis sur Ouvrir.

Si vous double-cliquez sur un fichier .dqy, Excel 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 fournir à d’autres utilisateurs un classeur contenant une plage de données externes, 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, de sorte que le fichier est plus petit. Les données externes sont récupérées lorsqu’un utilisateur ouvre le modèle de rapport.

Utiliser les données dans Excel

Une fois que vous avez créé une requête dans l’Assistant Requête ou dans Microsoft Query, vous pouvez renvoyer les données dans une feuille de calcul Excel. Les données deviennent ensuite une plage de données externe 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 synthétiser 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 répétées dans les lignes précédentes et les étendre à des lignes supplémentaires.

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

1. Cliquez surOptions> de fichier>avancées.
2. Dans la section Options d’édition, sélectionnez la case activée Étendre les formats et les formules de plage de données. Pour désactiver à nouveau la mise en forme automatique de la plage de données, désactivez cette case à case activée.

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 propose plusieurs options pour actualiser les requêtes, notamment l’actualisation des données chaque fois que vous ouvrez le classeur et l’actualisation automatique à intervalles réguliers. Vous pouvez continuer à travailler dans Excel pendant l’actualisation des données, de même que case activée le status pendant l’actualisation des données. Pour plus d’informations, consultez Actualiser une connexion de données externes dans Excel.

Haut de la page