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 bases de données (base de données : collection de données relatives à un sujet ou à un objectif particulier. Dans une base de données, les informations sur une entité particulière, par exemple, un employé ou une commande, sont classés en tables, enregistrements et champs.) 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

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 sources de données (source de données : ensemble d’informations « sources » stockées qui sont utilisées pour établir la connexion à une base de données. Une source de données peut contenir le nom et l’emplacement d’un serveur de base de données, le nom du lecteur de base de données, ainsi que les informations nécessaires à la base de données pour votre connexion.) suivantes :

Vous pouvez également utiliser des pilotes ODBC (pilote ODBC (Open Database Connectivity) : fichier de programme utilisé pour établir une connexion vers une base de données particulière. Chaque programme de base de données (par exemple, Access ou dBASE) ou système de gestion de base de données (comme SQL Server) nécessite un pilote différent.) ou des pilotes de source de données (pilote de source de données : fichier programme qui permet de se connecter à une base de données spécifique. Chaque programme ou système de gestion de base de données requiert un pilote différent.) 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 actualiser (actualisation : permet de mettre à jour les données issues d’une source de données externes. Chaque fois que vous actualisez des données, les informations les plus récentes de la base de données s’affichent, y compris toutes les modifications qui ont été apportées aux données.) 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 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 :
  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.

  1. À l’étape 1, tapez un nom pour identifier la source de données.
  2. À 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.

  1. 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.

  1. 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.
  2. Si votre base de données comporte des tables (table : collection de données relatives à un sujet donné, stockées dans des enregistrements (lignes) et des champs (colonnes).) 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.
  3. 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 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 :

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 (modèle : classeur que vous créez et utilisez pour créer d’autres classeurs similaires. Vous pouvez créer des modèles de classeurs ou de feuilles de calcul. Le modèle utilisé par défaut pour les classeurs s’appelle Classeur.xlt. Le modèle utilisé par défaut pour les feuilles de calcul s’appelle Feuille.xlt.). 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 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 externe (plage de données externes : plage de données qui est importée dans une feuille de calcul mais ne provenant pas de Microsoft Excel, telle qu’une base de données ou un fichier texte. Dans Excel, vous pouvez mettre en forme les données ou les utiliser dans des calculs comme toute autre donnée.) ou dans un rapport de tableau croisé dynamique (rapport de tableau croisé dynamique : rapport interactif sous forme de tableau croisé Microsoft Excel qui synthétise et analyse les données telles que des enregistrements de base de données, de diverses sources y compris celles externes à Excel.) 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, cliquez sur Options Excel, puis sur la catégorie Options avancées.
  1. 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 Haut de la page

 
 
S'applique à :
Excel 2007