Utiliser Microsoft Query pour extraire des données externes

Microsoft Query vous permet d’extraire des données de sources externes. Microsoft Query vous évite de devoir retaper les données que vous avez extraites de vos base de données et fichiers professionnels pour les analyser dans Excel. Microsoft Query vous permet également de mettre à jour automatiquement vos rapports et synthèses Excel à partir de la base de données source d’origine chaque fois que celle-ci est mise à jour.

Que voulez-vous faire ?

Fonctionnalités de Microsoft Query

Pour se connecter à une source de données

Utilisation de l’Assistant Requête pour définir une requête.

Exploiter des données dans Excel.

Fonctionnalités de Microsoft Query

Microsoft Query vous permet de vous connecter à des sources de données, d’y sélectionner des données, d’importer ces données dans des feuilles de calcul, puis de les actualiser comme il convient pour que le contenu des feuilles de calcul soit synchronisé avec les données des sources externes.

Types de bases de données auxquels vous pouvez accéder     Vous pouvez extraire des données de diverses bases de données, comme Microsoft Office Access et Microsoft SQL Server, et les services OLAP de Microsoft SQL Server. Vous pouvez également extraire des données de classeurs Excel et de fichiers de texte.

Microsoft Office fournit des pilotes que vous pouvez utiliser pour extraire des données des source de données suivantes :

  • Microsoft SQL Server Analysis Services (fournisseur OLAP)

  • Microsoft Office Access

  • dBASE

  • Microsoft FoxPro

  • Microsoft Office Excel

  • Oracle

  • Paradox

  • Bases de données de fichier texte

Vous pouvez également utiliser des pilote ODBC (Open Database Connectivity) ou des pilote de source de données d’autres fabricants pour extraire des informations de sources de données qui ne sont pas mentionnées ici, notamment 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 mentionné ici, consultez la documentation de la base de données utilisée ou contactez son fabricant.

Sélectionner des données dans une base de données     Pour extraire des données d’une base de données, vous devez tout d’abord créer une requête, qui est une question que vous posez concernant des données stockées dans une base de données externes. Si vos données sont stockées dans une base de données Access, par exemple, les chiffres des ventes d’un produit donné par région, vous pouvez extraire une partie des données en ne sélectionnant que les données qui concernent le produit et la région que vous voulez analyser.

Microsoft Query vous permet de sélectionner uniquement les colonnes de données voulues et de les importer dans Excel.

Mettre à jour une feuille de calcul en une seule opération     Une fois que les données externes sont importées dans un classeur Excel, vous pouvez actualisation celles-ci en fonction des modifications apportées à la base de données pour disposer constamment de données fiables, sans avoir besoin de recréer des rapports récapitulatifs et des graphiques. Par exemple, vous pouvez créer une synthèse mensuelle des ventes et l’actualiser chaque mois, à partir des nouveaux chiffres.

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, utilisez-la à chaque fois que vous voulez créer une requête pour sélectionner des données dans cette base de données et les extraire et pour vous éviter de devoir retaper toutes les informations de connexion. Microsoft Query utilise la source de données pour se connecter à la base de données externe et pour afficher les données qui sont disponibles. Après que vous avez créé la requête et exporté les données dans Excel, Microsoft Query fournit le classeur Excel avec les informations relatives à la requête et à la source de données pour que vous puissiez vous reconnecter à la base de données afin d’en actualiser les données.

Diagramme illustrant la manière dont Query utilise les sources de données

Utiliser Microsoft Query pour importer des données     Suivez la procédure ci-dessous pour importer des données externes dans Excel avec Microsoft Query.

  1. Configurez une source de données pour la connecter à votre base de données.

  2. Utilisez l’Assistant Requête pour définir une requête.

  3. Exploiter des données dans Excel.

Haut de la page

Pour 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ées qui permettent à Excel et Microsoft Query de se connecter à une base de données externes. Lorsque vous configurez une source de données à l’aide de Microsoft Query, vous devez d’abord nommer celle-ci, puis vous devez fournir le nom et l’emplacement de la base de données ou du serveur, le type de la base de données, votre nom d’utilisateur et votre mot de passe. Ces informations comprennent également le nom d’un pilote OBDC ou d’un pilote de source de données, qui est un programme permettant d’établir des connexions vers 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 Données externes, cliquez sur À partir d’autres sources, puis sur Provenance : Microsoft Query.

  2. Effectuez l’une des actions suivantes :

    • Pour désigner 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 la source de données d’un définition d’un cube OLAP, cliquez sur l’onglet Cubes OLAP. Cet onglet n’est disponible que 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 nouvelle 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 pour le type de base de données que vous utilisez en tant que source de données.

    Si la base de données externes à laquelle vous voulez accéder n’est pas prise en charge par les pilotes ODBC qui sont installés avec Microsoft Query, vous devez dans ce cas vous procurer un pilote ODBC compatible avec Microsoft Office auprès d’un fabricant tiers, comme le fabricant de la base de données. Contactez le fabricant de la base de données pour obtenir des instructions pour installer le pilote.

    Remarque   Les bases de données OLAP ne nécessitent pas de pilote ODBC. Lorsque vous installez Microsoft Query, les pilotes sont installés pour les bases de données qui ont été 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 Connecter, puis fournissez les informations nécessaires pour vous connecter à la 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é. Par exemple, il vous faudra peut-être fournir un nom de connexion, un mot de passe, la version de la base de données que vous utilisez, son emplacement ou d’autres informations spécifiques au type de la base de données.

    Utilisez des mots de passe forts qui associent des lettres majuscules et minuscules, des nombres et des symboles. Les mots de passe faibles ne mélangent pas ces éléments. Mot de passe fort : Y6dh!et5. Mot de passe faible : Maison27. Les mots de passe doivent contenir au moins 8 caractères. Une expression qui utilise au moins 14 caractères est conseillée. Pour plus d'informations, voir Protéger vos informations personnelles avec des mots de passe forts.

    Il est très important que vous vous rappeliez votre mot de passe. Si vous l'oubliez, Microsoft ne peut pas le récupérer. Stockez les mots de passe en les notant à un emplacement sécurisé loin des informations qu'ils protègent.

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

  8. Si votre base de données comporte des table et si vous voulez qu’une table particulière s’affiche automatiquement dans l’Assistant Requête, tapez le nom de la table comme il est indiquée à l’étape 4.

  9. Si vous ne souhaitez pas taper votre nom de connexion et votre mot de passe à chaque utilisation de la source de données, activez la case à cocher Enregistrer l’identité et le mot de passe de l’utilisateur. Le mot de passe enregistré n’est pas chiffré. Si la case à cocher n’est pas disponible, contactez l’administrateur de votre base de données pour savoir s’il est possible d’y accéder.

    Sécurité   Évitez d'enregistrer les informations d'ouverture de session que vous utilisez pour vous connecter à des sources de données. Ces informations peuvent être stockées en texte brut et un utilisateur malveillant peut y accéder pour compromettre la sécurité de la source de données.

À la fin de cette procédure, le nom de la source de données apparaît dans la boîte de dialogue Choisir une source de données.

Haut de la page

Utilisation de 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 rassembler facilement des données de différentes tables et de différents 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 voulez inclure. Une jointure interne (une opération de requête qui permet de combiner les lignes de deux tables à partir 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 avec le même nom dans la seconde table.

Vous pouvez également utiliser l’Assistant pour trier les résultats et pour effectuer un filtrage simple. Dans l’étape finale de l’Assistant, vous pouvez choisir entre exporter les données dans Excel et affiner la requête dans Microsoft Query. Une fois la requête créée, 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 Données externes, cliquez sur À partir d’autres sources, puis sur Provenance : Microsoft Query.

  2. Dans la boîte de dialogue Choisir une source de données, assurez-vous que la case à cocher Utiliser l’Assistant Requête pour créer et/ou modifier vos requêtes est activée.

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

    -ou-

    Cliquez sur la source de données que vous utilisez, puis sur OK.

Travailler directement dans Microsoft Query pour d’autres types de requêtes     Si vous voulez créer une requête trop complexe pour l’Assistant Requête, faites-le directement dans Microsoft Query. Microsoft Query vous permet d’afficher et de modifier les requêtes que vous créez dans l’Assistant Requête ou de créer de nouvelles requêtes sans avoir recours à l’Assistant. Travaillez directement dans Microsoft Query lorsque vous créez des requêtes qui exécutent les tâches suivantes :

  • sélectionner des données spécifiques dans un champ        Il peut arriver que, dans le cas d’une base de données volumineuse, vous n’ayez besoin que de certaines données d’un champ. Par exemple, si vous avez besoin de données pour deux des produits d’un champ contenant des informations sur de nombreux produits, utilisez des critères afin de sélectionner des données uniquement pour les deux produits souhaités.

  • récupérer des données en fonction de divers critères chaque fois que vous exécutez la requête        Si vous devez créer le même rapport ou la même synthèse Excel pour plusieurs zones des mêmes données externes (par exemple un rapport sur les ventes distinct pour chaque région), créez pour cela une requête avec paramètres. Lorsque vous exécutez une requête Paramètre, vous devez préciser la valeur à utiliser comme critère pour la sélection des enregistrements, par exemple, une région spécifique ; vous pouvez ensuite réutiliser cette requête pour créer des rapports de ventes pour les autres régions.

  • joindre des données de différentes manières     Les jointures internes que crée l’Assistant Requête sont les jointures les plus couramment utilisées pour créer des requêtes. Cependant, vous pouvez parfois avoir besoin d’utiliser un autre type de jointure. Par exemple, si vous disposez d’une table d’informations sur les ventes d’un produit et d’une table d’informations sur les clients, une jointure interne (le type de jointure créée par l’Assistant Requête) empêchera l’extraction des enregistrements clients pour les clients qui n’ont effectué aucun achat. Microsoft Query vous permet de joindre ces tables pour extraire tous les enregistrements de clients, ainsi que les données de vente des clients ayant effectué un achat.

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

  1. Sous l’onglet Données, dans le groupe Données externes, cliquez sur À partir d’autres sources, puis sur Provenance : Microsoft Query.

  2. Dans la boîte de dialogue Choisir une source de données, assurez-vous que la case à cocher Utilisez l’Assistant Requête pour créer et/ou modifier vos requêtes est désactivée.

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

    -ou-

    Cliquez sur la source de données que vous utilisez, puis sur OK.

Réutilisation et partage des requêtes        Dans l’Assistant Requête et dans Microsoft Query, il vous est possible d’enregistrer vos requêtes en tant que fichier de requête .dqy que vous pouvez modifier, réutiliser et partager. Excel peut ouvrir directement les fichiers .dqy, ce qui vous permet, ainsi qu’à 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 Données externes, cliquez sur À partir d’autres sources, puis sur Provenance : 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 voulez ouvrir. La requête s’affiche dans Microsoft Query.

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

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

Pour partager une synthèse ou un rapport Excel créé à partir de données externes, vous pouvez fournir à d’autres utilisateurs un classeur qui contient une plage de données externes ou créer un modèle. Ce dernier vous permet d’enregistrer la synthèse ou le rapport sans enregistrer les données externes afin de réduire la taille du fichier. Les données externes sont extraites lorsqu’un utilisateur ouvre le modèle de rapport.

Haut de la page

Exploiter des données dans Excel.

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

Mise en forme des données extraites      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 extraites par Microsoft Query. Vous pouvez mettre en forme les données comme vous le souhaitez et votre mise en forme est conservée lorsque vous actualisez les données externes. En outre, vous pouvez utiliser vos propres étiquettes de colonne à la place des noms de champ et ajouter automatiquement des numéros de ligne.

Excel peut automatiquement mettre en forme les données que vous ajoutez à la fin d’une plage pour qu’elles respectent la mise en forme des lignes précédentes ; il peut également copier automatiquement des formules qui ont été répétées dans les lignes précédentes et les étendre à d’autres lignes.

Remarque   Pour que les mises en forme et les formules soient étendues aux nouvelles lignes de la plage, elles doivent apparaître dans au moins trois des cinq lignes précédentes.

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

  1. Cliquez sur le bouton Microsoft Office Image du bouton Office, cliquez sur Options Excel, puis sur la catégorie Options avancées.

  2. Dans la section Édition, activez la case à cocher Étendre les formules et formats de plage de données pour désactiver cette option. Pour désactiver de nouveau la mise en forme automatique des plages de données, désactivez cette case à cocher.

Actualisation des données externes       Lorsque vous actualisez des données externes, vous devez exécuter la requête afin d’extraire les nouvelles données ou les données modifiées correspondant à vos critères. Vous pouvez actualiser une requête dans Microsoft Query et Microsoft Excel. Excel propose différentes options permettant d’actualiser les requêtes, notamment l’actualisation des données à l’ouverture du classeur et l’actualisation automatique à intervalles réguliers. Vous pouvez continuer à travailler dans Excel, tandis que les données sont actualisées et vérifier l’état de l’actualisation. Pour plus d’informations, consultez la rubrique Actualiser des données connectées (importées).

Haut de la page

S’applique à : Excel 2007



Ces informations vous ont-elles été utiles ?

Oui Non

Comment pouvons-nous nous améliorer ?

255 caractères restants

Afin de protéger votre vie privée, n’indiquez aucune information de contact dans vos commentaires. Lisez notre déclaration de confidentialité.

Nous vous remercions pour vos commentaires.

Ressources de support

Changer la langue