Question Comment puis-je joindre deux feuilles de calcul dans Excel, comme je le ferais dans SQL?


J'ai deux feuilles de calcul dans deux fichiers Excel différents. Ils contiennent tous deux une liste de noms, de numéros d’identification et de données associées. L'une est une liste maîtresse qui comprend des champs démographiques généraux et l'autre une liste qui inclut uniquement le nom et l'ID et une adresse. Cette liste a été extraite de la liste principale par un autre bureau.

Je veux utiliser la 2ème liste pour filtrer le premier. De plus, je souhaite que les résultats incluent d'autres champs de la feuille de travail principale à côté des champs d'adresse de la deuxième feuille de calcul. Je sais très bien comment faire cela avec une jointure interne de base de données, mais je suis moins clair sur la manière de le faire efficacement dans Excel. Comment joindre deux feuilles de calcul dans Excel? Des points bonus pour montrer comment faire des jointures externes également, et je préférerais grandement savoir comment faire cela sans avoir besoin d'une macro.


105
2018-05-04 16:20


origine


Je trouve la page suivante très utile: randomwok.com/excel/how-to-use-index-match - Tommy Bravo
randomwok.com/excel/how-to-use-index-match => surtout le "rappel" facile à la fin: =INDEX ( Column_I_want_a_return_value_from , ( MATCH ( My_Lookup_Value , Column_I_want_to_Lookup_against , 0 )) - Tommy Bravo


Réponses:


Pour 2007+ utilisation Data > From Other Sources > From Microsoft Query:

  1. choisir Excel File et sélectionnez votre 1er excel
  2. choisir des colonnes
     (Si vous ne voyez aucune liste de colonnes, assurez-vous de vérifier Options > System Tables)
  3. aller à Data > Connections > [choisissez la connexion que vous venez de créer]> Properties > Definition > Command text

Vous pouvez maintenant modifier ceci Command text en tant que SQL. Pas sûr de la syntaxe qui est suportée, mais j'ai essayé les jointures implicites, "join interne", "gauche jointe" et les unions qui fonctionnent toutes. Voici un exemple de requête:

SELECT *
FROM `C:\Users\Peter\Documents\Excel-to-excel\Source_1.xlsx`.`Sheet1$` a
LEFT JOIN `C:\Users\Peter\Documents\Excel-to-excel\Source_2.xlsx`.`Sheet1$` b
ON a.col2 = b.col2

146
2018-05-07 09:37



Y a-t-il un moyen d'éviter de coder en dur le chemin? Le chemin relatif fonctionnerait-il? - Rekin
En prime, Microsoft Query vous permet d’expérimenter des contrôles communs de 16 bits pour ouvrir le fichier Excel et les boîtes de message. De cette façon, vous pouvez vous rappeler quand vous étiez jeune. :-) - Edward Brey
Est-ce que cela fonctionne avec les fichiers CSV? J'utilise MS Office Professional Plus 2010 et je ne vois pas comment suivre l'étape 3 - est-ce que je laisse la boîte de dialogue à l'étape 2 en suspens? Il n'y a pas de "choisir votre nouvelle connexion" dans ma boîte de dialogue Connexions. - John Freeman
@JohnFreeman parce que vous devez choisir l'une des options disponibles en fonction de vos besoins (par exemple, "Ajouter ...") - Aprillion
Notez que vous pouvez obtenir une boîte de dialogue indiquant «Cette source de données ne contient pas de tables visibles», suivie d'une boîte de dialogue «Assistant de requête - Choisir des colonnes». C'est de ce Cliquez sur le bouton "Options", puis cochez la case "Tables système" pour voir les données que vous souhaitez interroger. - Tola Odejayi


Soutenez la réponse acceptée. Je veux juste mettre l'accent sur "choisir des colonnes (si vous ne voyez aucune liste de colonnes, assurez-vous de vérifier Options> Tables système)"

Une fois que vous sélectionnez le fichier Excel, vous verrez très probablement this data source contains no visible tables prompt, et les onglets et colonnes disponibles sont none. Microsoft admis que c'est un bug  que les onglets des fichiers Excel sont traités comme des "Tables système" et que l'option "Tables système" n'est pas sélectionnée par défaut. Donc, ne paniquez pas à cette étape, il vous suffit de cliquer sur "option" et cochez "Tables système", puis vous voyez les colonnes disponibles.


9
2017-12-09 18:20





VLOOKUP et HLOOKUP peuvent être utilisés pour rechercher des clés primaires correspondantes (stockées verticalement ou horizontalement) et renvoyer des valeurs à partir de colonnes / lignes d'attribut.


8
2018-05-04 16:22



très utile pour les feuilles d'un même classeur (mais je trouve les fonctions INDEX + MATCH encore plus utiles), un peu plus compliquées lors de la mise à jour de données à partir de classeurs externes fermés ... - Aprillion


Vous ne pouvez pas préformer des jointures de style SQL sur des tables Excel à partir d'Excel. Cela dit, il y a plusieurs façons d'accomplir ce que vous essayez de faire.

Dans Excel, comme le dit Reuben, les formules qui fonctionneront probablement le mieux sont VLOOKUP et HLOOKUP. Dans les deux cas, vous faites correspondre une ligne unique et la valeur de la colonne \ row donnée est renvoyée vers le bas à partir de l'ID trouvé.

Si vous voulez seulement ajouter quelques champs supplémentaires à la deuxième liste, ajoutez les formules à la deuxième liste. Si vous voulez une table de style "join join", ajoutez la VLOOKUP formule à la première liste avec ISNA pour tester si la recherche a été trouvée. Si l'aide d'Excel ne vous donne pas suffisamment de détails sur la manière de les utiliser dans votre cas particulier, faites-le nous savoir.

Si vous préférez utiliser SQL, liez les données à votre programme de base de données, créez votre requête et exportez les résultats dans Excel. (Dans Access, vous pouvez importer des feuilles de calcul Excel ou des plages nommées en tant que table liée.)


4
2018-05-04 17:29



en fait, vous pouvez - il s'appelle Microsoft Query - Aprillion


Vous pouvez utiliser Microsoft Power Query, disponible pour les versions plus récentes d'Excel (similaire à la réponse acceptée, mais beaucoup plus simple et plus facile). Les appels Power Query rejoignent les «fusions».

Le plus simple est d'avoir vos 2 feuilles Excel sous forme de tableaux Excel. Ensuite, dans Excel, accédez à l’onglet Ruban Power Query, puis cliquez sur le bouton «Excel». Une fois que vous avez importé les deux tables dans Power Query, sélectionnez-en une et cliquez sur "Fusionner".


4
2018-05-17 14:09



Tellement plus facile que les autres options proposées. Cela devrait être la réponse! Requête de puissance est maintenant inclus dans Excel 2016 sous le Les données languette. - SliverNinja - MSFT
Une autre fonctionnalité qui ne semble pas exister sur le Mac - juandesant


Chez XLTools.net, nous avons créé une bonne alternative pour que MS Query fonctionne en particulier avec les requêtes SQL sur des tableaux Excel. On l'appelle Requêtes SQL XLTools. Il est beaucoup plus facile à utiliser que MS Query et fonctionne très bien si vous devez simplement créer et exécuter SQL - pas de VBA, pas de manipulations complexes avec MS Query ...

Avec cet outil, vous pouvez créer n'importe quelle requête SQL sur des tables dans un ou plusieurs classeurs Excel à l'aide d'un éditeur SQL intégré et l'exécuter immédiatement avec la possibilité de placer le résultat sur une feuille de calcul nouvelle ou existante.

Vous pouvez utiliser presque n'importe quel type de jointure, y compris LEFT OUTER JOIN (seuls RIGHT OUTER JOIN et FULL OUTER JOIN ne sont pas pris en charge).

Voici un exemple:

XLTools SQL Queries - Query Builder


3
2017-07-04 22:25





Pour Excel 2007 Utilisateur: Données> À partir d'autres sources> À partir de Microsoft Query> accédez au fichier Excel

Selon Cet article, l'interrogation à partir de la version XLS 2003 peut entraîner une "Cette source de données ne contient aucune table visible". erreur car vos feuilles de calcul sont traitées comme table SYSTEM. Donc, vérifiez les "Tables système" dans les options de la boîte de dialogue "Assistant Requête - Choisir les colonnes" lorsque vous créez la requête fonctionnera.

Pour définir votre entrée: Boîte de dialogue Microsoft Query> Menu Tableau> Jointures ...

Pour renvoyer des données dans votre feuille Excel d'origine, choisissez "Renvoyer les données dans une feuille Excel" dans la boîte de dialogue Microsoft Query> Menu Fichier.


2
2017-07-16 02:41





Bien que je pense que la réponse d’Aprillion à l’aide de Microsoft Query est excellente, cela m’a inspiré Microsoft Access rejoindre les fiches techniques que j'ai trouvées beaucoup plus faciles.

Bien entendu, vous devez installer MS Access.

Pas:

  • Créez une nouvelle base de données Access (ou utilisez une base de données scratch).
  • Utilisation Get External Data importer vos données Excel en tant que nouvelles tables.
  • Utilisation Relationships pour montrer comment vos tables sont jointes.
  • Définissez le type de relation pour qu'il corresponde à ce que vous voulez (représentant la jointure gauche, etc.)
  • Créez une nouvelle requête qui joint vos tables.
  • Utilisation External Data->Export to Excel pour générer vos résultats.

Je n'aurais vraiment pas pu faire cela sans la bonne réponse d'Aprillion.


2
2018-02-12 11:00