Extraction de tableaux croisés Excel, Microsoft Query KO toutes les fonctions

La première réaction de nombreux partenaires lors de l'extraction de données entre tables est des fonctions telles que RECHERCHEV ou la formule INDEX+SMALL+IF. En fait, si vous extrayez plusieurs colonnes de données, Microsoft Query, qui a longtemps été laissé de côté par de nombreuses personnes, est le roi ! Non seulement il est facile à utiliser et à résoudre facilement les problèmes « un à plusieurs », mais le tableau de résultats qu'il génère peut former un lien dynamique avec la source de données. Si la source de données change, les résultats seront mis à jour dynamiquement !

Aujourd'hui, j'aimerais partager avec vous une fonction que peu de gens utilisent mais qui a des effets miraculeux ---Microsoft Query pour vous aider à résoudre l'extraction de données "un à plusieurs" de deux tables, ou pour résoudre le problème de l'utilisation d'une table pour en faire correspondre une autre. Pratique consistant à générer des données spécifiques à partir d'une table.

Comme le montre l'image ci-dessous, il y a deux feuilles de calcul dans le même classeur. Le « Tableau d'informations sur le personnel du département » répertorie les noms des employés de chaque département et les superviseurs correspondants, et le « Tableau des données de ventes provinciales » répertorie les responsabilités de chacun. chaque employé. de plusieurs provinces et données de ventes sur trois mois des provinces correspondantes. Il est maintenant nécessaire de résumer les deux tables en une seule table en fonction de la colonne de nom.

Montre originale

Résultats requis

Comment utiliser Microsoft Query ?

ÉTAPE 01 Activez Microsoft Query et chargez les données

(1) Créez un nouveau classeur, cliquez sur le menu déroulant "À partir d'autres sources" dans le groupe "Obtenir des données externes" sous [Données ] onglet de Microsoft Requête".

Cliquez sur "Fichiers Excel" sous l'option "Base de données" dans la fenêtre [Sélectionner la source de données], cochez "Utiliser [Assistant de requête] pour créer/modifier des requêtes" ci-dessous, puis cliquez sur OK.

Recherchez l'emplacement de la source de données dans le répertoire sur le côté droit de la fenêtre [Sélectionner un classeur], recherchez le fichier dans le nom de la base de données à gauche et cliquez sur OK

(2) Parfois, le système affiche la fenêtre suivante : "La source de données n'est pas incluse. "Tableaux visibles", ne vous inquiétez pas, cliquez sur OK.

Entrez dans la fenêtre [Assistant de requête] à gauche ci-dessous, cliquez sur "Options". ci-dessous, ouvrez la fenêtre [Options de table] à droite et cochez "Tables système" "Cliquez sur OK.

La feuille de calcul dans la source de données apparaîtra dans la fenêtre [Assistant de requête]. En effet, Excel appelle sa feuille de calcul "table système". Après l'avoir vérifiée, vous pouvez la voir

Sélectionnez ensuite les deux feuilles de calcul et cliquez sur le bouton ">" au milieu pour ajouter les "Tableaux et colonnes disponibles" à gauche. les « Colonnes des résultats de la requête » à droite, puis cliquez sur Étape.

Une autre fenêtre apparaîtra indiquant « L'Assistant de requête ne peut pas continuer car le formulaire ne peut pas être lié à votre requête. Vous devez être chez Microsoft Faites glisser les champs entre les tables dans Query pour les lier manuellement. "Ne vous inquiétez pas pour cela, cliquez sur OK.

ÉTAPE 02 Faites correspondre les données en fonction des éléments requis

À ce stade, nous entrons dans la fenêtre Microsoft Query, avec une barre de menu de type EXCEL en haut et une zone de tableau au milieu, montrant les deux tables que nous avons actuellement ajoutées et les champs correspondants. La zone de données ci-dessous est le résultat de la fusion des deux tables. Le résultat de la zone de données est compliqué pour le moment car nous l'avons fait. ne donne pas les deux tables. Relation d'addition de table. Les deux tables sont en correspondance biunivoque via la colonne de nom

.

(1) Utilisez la souris pour sélectionner le « Nom » dans le « Tableau d'informations sur le personnel du département » à gauche, faites-le glisser vers le « Nom » dans le « Tableau de données de ventes provinciales » sur le tableau de droite, puis relâchez la souris. . A ce moment, une ligne de connexion avec de petits nœuds aux deux extrémités apparaît entre les champs « nom » des deux tables. La zone de données ci-dessous est mise à jour immédiatement.

(2) Puisqu'il y a deux colonnes portant le même nom, nous sélectionnons l'une des colonnes et cliquons sur "Supprimer la colonne" sous [Enregistrer] dans la barre de menu.

ÉTAPE 03 Renvoyez les données de résultat dans la feuille de calcul Excel

La dernière chose à faire est de renvoyer les résultats dans EXCEL.

(1) Cliquez sur le bouton à gauche de "SQL" dans la barre de menu pour renvoyer les données vers Excel.

(2) La fenêtre [Importer des données] apparaît dans EXCEL Nous choisissons de l'afficher sous forme de "Tableau" et de la placer dans la feuille de calcul existante.

Le résultat renvoyé est le suivant :

Au cours de ces 3 étapes simples, nous avons terminé la correspondance des données requise et généré un nouveau tableau de données.

Joie supplémentaire

Nous avons constaté que les données générées par Microsoft Query sont un super tableau, et vous pouvez également créer directement un tableau croisé dynamique ou un diagramme croisé dynamique.

En même temps, ce tableau est lié dynamiquement à la source de données. Par exemple, si nous modifions les données d'origine, cliquez sur Enregistrer pour fermer.

Cliquez avec le bouton droit sur les résultats renvoyés pour actualiser.

De cette façon, les données seront synchronisées.

Conditions d'application

Il est à noter que lors de l'utilisation de cette méthode, la standardisation de la source de données doit être assurée. Il est obligatoire que la feuille de calcul ne puisse pas contenir de données non liées à la source de données et que la première ligne du tableau soit constituée d'en-têtes de colonne. Si vous souhaitez implémenter des liaisons dynamiques, les noms et emplacements du classeur et de la feuille de calcul ne peuvent pas être modifiés.

Comment ça va, l'as-tu appris ? Est-ce plus simple que PQ ou plus simple qu'une fonction ?

Recommandations d'apprentissage associées : Tutoriel Excel

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!