Combiner basé sur une colonne commune - Conseils Excel

David de Floride pose la question d'aujourd'hui:

J'ai deux classeurs. Les deux ont les mêmes données dans la colonne A, mais les colonnes restantes sont différentes. Comment fusionner ces deux classeurs?

J'ai demandé à David s'il est possible qu'un classeur ait plus d'enregistrements que l'autre. Et la réponse est oui. J'ai demandé à David si le champ clé n'apparaît qu'une seule fois dans chaque fichier. La réponse est également oui. Aujourd'hui, je vais résoudre ce problème avec Power Query. Les outils Power Query se trouvent dans les versions Windows d'Excel 2016+ dans la section Obtenir et transformer de l'onglet Données. Si vous disposez de versions Windows d'Excel 2010 ou d'Excel 2013, vous pouvez télécharger le complément Power Query pour ces versions.

Voici le classeur 1 de David. Il contient Product et trois colonnes de données.

Le premier classeur

Voici le classeur 2 de David. Il contient le code produit et d'autres colonnes. Dans cet exemple, il existe des produits supplémentaires dans classeur2, mais les solutions fonctionneront si l'un des classeurs contient des colonnes supplémentaires.

Le deuxième classeur

Voici les étapes:

Sélectionnez Données, Obtenir des données, À partir d'un fichier, À partir d'un classeur:

Charger des données à partir d'un fichier
Accédez au premier classeur et cliquez sur OK
Dans la boîte de dialogue Navigateur, choisissez la feuille de calcul sur la gauche. (Même s'il n'y a qu'une seule feuille de calcul, vous devez la sélectionner.) Vous verrez les données sur la droite.
Dans la boîte de dialogue Navigateur, ouvrez la liste déroulante Charger et choisissez Charger vers…
Choisissez Créer une connexion uniquement et appuyez sur OK.
Répétez les étapes 1 à 5 pour le deuxième classeur.

Créer une connexion au classeur
Si vous avez fait les deux classeurs, vous devriez voir deux connexions dans le panneau Requêtes et connexions à droite de votre écran Excel.

Connexions aux deux classeurs
Continuez avec les étapes pour fusionner les classeurs:
Données, obtenir des données, combiner des requêtes, fusionner.

Fusionner deux requêtes avec des colonnes différentes
Dans le menu déroulant du haut de la boîte de dialogue Fusionner, choisissez la première requête.
Dans la deuxième liste déroulante de la boîte de dialogue Fusionner, choisissez la deuxième requête.
Cliquez sur l'en-tête Produit dans l'aperçu supérieur (il s'agit du champ clé. Notez que vous pouvez sélectionner plusieurs champs clés en appuyant sur Ctrl + Clic)
Cliquez sur l'en-tête Code produit dans le deuxième aperçu.
Ouvrez le type de jointure et choisissez Externe complet (toutes les lignes des deux)

Les étapes 8 à 12 illustrées ici
Cliquez sur OK. L'aperçu des données n'affiche pas les lignes supplémentaires et n'affiche que "Table" à plusieurs reprises dans la dernière colonne.

Cela ne semble pas prometteur
Notez qu'il y a une icône "Développer" dans l'en-tête de DavidTwo. Cliquez sur cette icône.
Facultatif, mais je désélectionne toujours "Utiliser le nom de colonne d'origine comme préfixe". Cliquez sur OK.

Développez les champs du classeur 2
Les résultats sont affichés dans cet aperçu:

Tous les enregistrements de l'un ou l'autre classeur
Dans Power Query, utilisez Accueil, Fermer et charger.

Voici la belle fonctionnalité: si les données sous-jacentes dans l'un ou l'autre classeur changent, vous pouvez cliquer sur l'icône Actualiser pour extraire de nouvelles données dans le classeur de résultats.

Refaites les étapes 1 à 16 en cliquant sur cette icône Actualiser.

Remarque

L'icône d'actualisation est généralement masquée. Faites glisser le bord gauche du volet Requêtes et connexions vers la gauche pour révéler l'icône.

Regarder la vidéo

Transcription vidéo

Apprenez Excel à partir du podcast, épisode 2216: Combinez deux classeurs basés sur une colonne commune.

Hé, bienvenue à nouveau sur le netcast, je suis Bill Jelen. La question d'aujourd'hui est de David, qui participait à mon séminaire à Melbourne, en Floride, pour le chapitre Space Coast de l'IIA.

David a deux classeurs différents où la colonne A est en commun entre les deux. Alors, voici le classeur 1, voici le classeur 2 - les deux ont un code produit. Celui-ci contient des éléments que le premier n'a pas, ou vice versa, et David veut combiner toutes les colonnes. Donc, nous avons trois colonnes ici et quatre colonnes ici. Je mets les deux dans le même classeur, au cas où vous téléchargez le classeur pour travailler. Prenez chacun d'entre eux, déplacez-le vers son propre classeur et enregistrez-le.

D'accord, pour combiner ces fichiers, nous allons utiliser Power Query. Power Query est intégré à Excel 2016. Si vous utilisez la version Windows 10 ou 13, vous pouvez accéder à Microsoft et télécharger Power Query. Vous pouvez démarrer à partir d'un nouveau classeur vierge avec une feuille de calcul vierge. Vous allez enregistrer ce fichier - Enregistrer sous, vous savez, peut-être Workbook, pour afficher les résultats des fichiers combinés .xlsx. Bien? Et ce que nous allons faire, c'est que nous allons faire deux requêtes. Nous allons aller aux données, obtenir les données, à partir du fichier, à partir du classeur, puis nous choisirons le premier fichier. Dans un aperçu, sélectionnez la feuille contenant vos données et nous n'avons rien à faire pour ces données. Alors ouvrez simplement la boîte de chargement et choisissez Charger vers, Créer uniquement une connexion, cliquez sur OK. Parfait. Maintenant, nous allons répéter cela pour le deuxième élément - Données, à partir d'un fichier,À partir d'un classeur, choisissez DavidTwo, choisissez le nom de la feuille, puis ouvrez le chargement, Charger vers, créer uniquement une connexion. Vous verrez ici dans ce panneau, nous avons les deux connexions présentes. Bien.

Maintenant, le travail réel - Données, Obtenir des données, Combiner des requêtes, Fusionner, puis dans la boîte de dialogue Fusionner, choisissez DavidOne, DavidTwo, et cette étape suivante est complètement peu intuitive. Tu dois faire ca. Choisissez la ou les colonnes en commun - donc Produit et Produit. Bien. Et puis, soyez très prudent ici avec le type de jointure. Je veux toutes les lignes des deux parce que l'une peut avoir une ligne supplémentaire et j'ai besoin de voir cela, puis nous cliquons sur OK. Bien. Et voici le résultat initial. Cela ne semble pas avoir fonctionné; il ne semble pas avoir ajouté les éléments supplémentaires qui étaient dans le fichier 2. Et nous avons cette colonne 5 - elle est nulle maintenant. Je vais faire un clic droit sur la colonne 5 et dire, Supprimer cette colonne. Alors ouvrez cette icône de développement et décochez cette case pour Utiliser le nom de colonne d'origine comme préfixe, et BAM! Ça marche. Donc, les éléments supplémentaires qui étaient dans le fichier 2, qui ne sont pas dans le fichier 1,apparaissent.

Bien. Maintenant, dans le fichier d'aujourd'hui, il semble que cette colonne de code produit soit meilleure que cette colonne de produit, car elle contient des lignes supplémentaires. Mais il pourrait y avoir un jour dans le futur où le classeur 1 contient des éléments que le classeur 2 n'a pas. Je vais donc les laisser tous les deux là, et je ne vais pas me débarrasser des nulls parce que, comme, même si cette ligne en bas semble être complètement nulle, il pourrait y avoir à l'avenir une situation où nous avons quelques valeurs nulles ici parce qu'il manque quelque chose. Bien? Donc, enfin, Close & Load, et nous avons nos seize lignes.

Maintenant, à l'avenir, disons que quelque chose change. Très bien, nous reviendrons donc à l'un de ces deux fichiers et je changerai la classe pour Apple en 99, et nous allons même insérer quelque chose de nouveau et enregistrer ce classeur. Bien. Et puis, si nous voulons que notre fichier de fusion soit mis à jour, venez ici - maintenant, faites attention, lorsque vous faites cela pour la première fois, vous ne pouvez pas voir l'icône Actualiser - vous devez saisir cette barre et la faire glisser . Et nous ferons Refresh, et 17 lignes chargées, la pastèque apparaît, la pomme passe à 99 - c'est une belle chose. Maintenant, tu veux en savoir plus sur Power Query? Achetez ce livre de Ken Puls et Miguel Escobar, M est pour (DATA) MONKEY. Je vais vous mettre au courant.

Récapitulatif aujourd'hui: David de Floride a deux classeurs qu'il veut combiner; ils ont tous les deux les mêmes champs dans la colonne A, mais les autres colonnes sont toutes différentes; un classeur peut contenir des éléments supplémentaires qui ne sont pas dans l'autre et David les veut; il n'y a aucun doublon dans les deux fichiers; nous allons utiliser Power Query pour résoudre ce problème, alors commencez dans un nouveau classeur vierge sur une feuille de calcul vierge; vous allez effectuer trois requêtes, la première: Données, À partir du fichier, Classeur, puis Charger uniquement dans la connexion créée; la même chose pour le deuxième classeur, puis Data, Get Data, Merge, sélectionnez les deux connexions, sélectionnez la colonne commune aux deux - dans mon cas, Product - puis à partir du type de jointure, vous souhaitez une jointure complète tout du fichier 1, tout du fichier 2. Et puis la belle chose est que si les données sous-jacentes changent,vous pouvez simplement actualiser la requête.

Pour télécharger le classeur à partir de la vidéo d'aujourd'hui, accédez à l'URL dans la description YouTube.

Eh bien, je veux comme David d'être venu à mon séminaire, je tiens à vous remercier d'être passé. Je vous verrai la prochaine fois pour une autre diffusion sur Internet.

Télécharger le fichier Excel

Pour télécharger le fichier Excel: combine-based-on-common-column.xlsx

Power Query est un outil incroyable dans Excel.

Excellente pensée du jour

J'ai demandé à mes amis Excel Master leurs conseils sur Excel. Pensée d'aujourd'hui à méditer: