Tutoriel Excel : comment faire une jointure
Formation Excel en ligne par vidéo
205 leçons vidéos + 20 Livres PDF + 20 TP + Sous supervision + Certificat de réussite à la fin du cours
Dans ce tutoriel, vous découvrirez quelques astuces pour fusionner des tableaux Excel en faisant correspondre des données dans une ou plusieurs colonnes, ainsi qu'en combinant des feuilles de calcul basées sur des en-têtes de colonne.
Lors de l'analyse de données dans Excel, à quelle fréquence réunissez-vous toutes les informations nécessaires dans une seule feuille de calcul ? Presque jamais! Il est très courant que différentes données soient dispersées sur de nombreuses feuilles de calcul et classeurs. Heureusement, il existe différentes manières de combiner des données de plusieurs tables en une seule, et ce tutoriel vous apprendra comment le faire plus efficacement.
Table des matières
Comment faire une jointure dans Excel
Comment fusionner deux tableaux dans Excel avec des formules
Comment joindre des tables avec RECHERCHEV
Comment fusionner des tableaux dans Excel avec INDEX EQUIV
Comment combiner des tables en faisant correspondre plusieurs colonnes
Joignez plusieurs tables en une seule avec Excel Power Query
Créer des connexions Power Query
Fusionner deux connexions dans une seule table
Sélectionnez les colonnes à ajouter dans la deuxième table
Comment fusionner deux tableaux dans Excel avec des formules
Quelle que soit la tâche que vous devez effectuer dans vos feuilles de travail, où cherchez-vous une solution ? Comme beaucoup d'utilisateurs, je vais généralement dans l'onglet Formules et j’ouvre une liste de fonctions. La fusion des tables ne fait pas exception.
Comment joindre des tables avec RECHERCHEV
Si vous voulez fusionner deux tables basées sur une colonne, RECHERCHEV est la bonne fonction à utiliser.
Supposons que vous ayez deux tables dans deux feuilles différentes : la table principale contient les noms du vendeur et des produits, et la table de consultation contient les noms et les montants. Vous souhaitez combiner ces deux tables en faisant correspondre les données de la colonne Vendeur :
Comme vous le voyez, l'ordre des noms dans la table principale ne correspond pas à celui de la table de consultation; par conséquent, une simple technique de copier / coller ne fonctionnera pas.
Pour combiner deux tables par une colonne correspondante (Vendeur), vous entrez cette formule dans C2 dans la table principale :
=RECHERCHEV($A2 ; 'Table de consultation'!$A$2:$B$8 ;2; FAUX)
Où:
- $A$2 est la valeur que vous recherchez.
- 'Table de consultation'! $A$2:$B$8 est le tableau dans lequel effectuer la recherche (veuillez faire attention à ce que nous bloquions la plage avec des références de cellules absolues).
- 2 est le numéro de la colonne à partir de laquelle récupérer la valeur.
Copiez la formule dans la colonne pour obtenir une table fusionnée composée de la table principale et des données correspondantes extraites de la table de recherche :
Notez que la fonction Excel RECHERCHEV a plusieurs limitations, dont les plus critiques sont :
1) l’impossibilité d'extraire les données d'une colonne à gauche de la colonne de recherche et
2) un numéro de colonne codé en dur rompt une formule lorsque vous ajoutez ou supprimez des colonnes dans la table de recherche. Du bon côté des choses, vous pouvez facilement réorganiser les colonnes retournées en modifiant simplement le nombre dans l'argument col_index_num.
Comment fusionner des tableaux dans Excel avec INDEX EQUIV
Si vous recherchez une alternative plus puissante et plus polyvalente à la fonction RECHERCHEV, optez pour cette combinaison INDEX EQUIV :
INDEX(Matrice; EQUIV(valeur_cherché; table_recherche ; 0))
Je vais vous montrer comment utiliser cette formule pour lever les yeux de droite à gauche, ce que RECHERCHEV est incapable de faire.
Supposons que vous ayez une autre table de recherche avec les ID de commande dans la première colonne et que vous souhaitez copier ces ID dans la table principale en faisant correspondre les noms du vendeur. Pour une meilleure visualisation, les deux tableaux sont placés sur la même feuille :
Pour accomplir la tâche, vous fournissez les arguments suivants à la formule de correspondance d'index :
Matrice - $E$2:$E$8
valeur_cherché - $A2
table_recherche - $F$2:$F$8
Veuillez noter le signe $ qui verrouille les plages pour les empêcher de changer lorsque vous copiez la formule dans le tableau.
La formule complétée se présente comme suit :
=INDEX($E$2:$E$8; EQUIV($A2; $F$2:$F$8; 0))
… Et combine parfaitement les données de deux tables :
Comment combiner des tables en faisant correspondre plusieurs colonnes
Si les deux tables que vous souhaitez joindre n'ont pas d'identifiant unique, tel qu'un identifiant de commande, vous pouvez faire correspondre les valeurs dans deux colonnes ou plus en utilisant cette formule :
INDEX(Matrice; EQUIV(1; (valeur_cherché1 = plage_cherché1) * (valeur_cherché2 = plage_cherché2) ; 0) ; numéro_col_retour)
Remarque : C'est une formule matricielle, donc n'oubliez pas d'appuyer sur Ctrl + Shift + Entrée pour la saisir correctement.
En supposant que vous ayez les deux tableaux suivants à combiner en un seul. Étant donné que la colonne ID de commande est manquante dans la table de recherche, la seule méthode permettant de faire correspondre les commandes est par vendeur et par produit :
Sur la base de la capture d'écran ci-dessus, définissons les arguments de notre formule :
- Matrice - $F$2:$H$8
- valeur_cherché1 - $B2
- plage_cherché1 - $F$2:$F$8
- valeur_cherché2 - $C2
- plage_cherché2 - $G$2:$G$8
- Numéro_colonne_retour - 3
Encore une fois, veillez à fixer toutes les plages avec des références de cellules absolues afin qu'elles ne changent pas lorsque vous copiez la formule:
=INDEX($F$2:$H$8;EQUIV(1; ($B2 =$F$2:$F$8) * ($C2 =$G$2:$G$8);0); 3)
Entrez la formule dans D3, appuyez sur Ctrl + Shift + Entrée, copiez-la dans les lignes ci-dessous et vérifiez le résultat :
Joignez plusieurs tables en une seule avec Excel Power Query
Dans les cas où vous devez combiner plusieurs tables avec un nombre différent de lignes et de colonnes, Excel Power Query peut s'avérer utile. Cependant, sachez que la jonction de tables avec Power Query ne peut pas être réalisée en quelques clics, je vais donc en décrire brièvement les principales caractéristiques :
- Power Query peut fusionner deux tables en faisant correspondre une ou plusieurs colonnes.
- Les tables source peuvent être sur la même feuille ou dans des feuilles de calcul différentes.
- Les tables d'origine ne sont pas modifiées. Les données sont combinées dans une nouvelle table pouvant être importée dans une feuille de calcul existante ou nouvelle.
- Dans Excel 2016 et Excel 2019, Power Query est une fonctionnalité intégrée. Dans Excel 2010 et Excel 2013, il peut être téléchargé en tant que complément.
Créer des connexions Power Query
Pour ne pas encombrer votre classeur avec des copies de vos tables d'origine, nous allons les convertir en connexions, effectuer la fusion dans l'éditeur Power Query, puis charger uniquement la table résultante.
Pour enregistrer une table en tant que connexion dans Power Query, procédez comme suit :
- Sélectionnez votre première table (Commande) ou n'importe quelle cellule de cette table.
- Accédez à l'onglet Données> Groupe Récupérer et transformer, puis cliquez sur à partir d’un tableau.
- Dans l'éditeur de requête Power Query qui s'ouvre, cliquez sur la flèche de la liste déroulante Fermer et charger (et non sur le bouton lui-même!), Puis sélectionnez l'option Fermer et charger dans….
- Dans la boîte de dialogue Importer des données, sélectionnez l'option Créer uniquement la connexion, puis cliquez sur Charger.
Cela créera une connexion avec le nom de votre table / plage et l'affichera dans le volet Requêtes qui apparaît à droite de votre classeur.
- Répétez les étapes ci-dessus pour toutes les autres tables que vous souhaitez fusionner (deux autres tables, Produits et Commissions, dans notre cas).
Lorsque vous avez terminé, vous verrez toutes les connexions dans le volet Requêtes de classeur.
Fusionner deux connexions dans une seule table
Avec les connexions en place, voyons comment vous pouvez joindre deux tables en une seule :
Dans l'onglet Données, dans le groupe Récupérer et transformer, cliquez sur le bouton Nouvelle requête, choisissez Combiner les requêtes dans la liste déroulante, puis cliquez sur Fusionner :
Dans la boîte de dialogue Fusionner, procédez comme suit :
- Sélectionnez votre 1ère table (Commandes) dans le premier menu déroulant.
- Sélectionnez votre 2ème table (Commissions) dans la deuxième liste déroulante.
- Dans les deux aperçus, cliquez sur la colonne correspondante (Vendeur) pour la sélectionner. La colonne sélectionnée sera surlignée en vert.
- Dans la liste déroulante Type de lien, laissez l'option par défaut : Extérieur gauche (toutes à partir de la première, correspondant à partir de la deuxième).
- Cliquez sur OK.
Une fois les étapes ci-dessus terminées, l'éditeur Power Query affiche votre première table (Commandes) avec une colonne supplémentaire nommée comme votre deuxième table (Commissions) ajoutée à la fin. Cette colonne supplémentaire n'a pas encore de valeur, juste le mot "Table" dans toutes les cellules. Mais ne vous découragez pas, vous avez tout fait correctement et nous allons y remédier dans un instant!
Sélectionnez les colonnes à ajouter dans la deuxième table
À ce stade, vous disposez d'un tableau ressemblant à celui de la capture d'écran ci-dessous. Pour terminer le processus de fusion, procédez comme suit dans l'éditeur Power Query:
- Dans la colonne ajoutée (Commissions), cliquez sur la flèche double dans l'en-tête.
- Dans la boîte qui s'ouvre, procédez comme suit :
- Laissez le bouton radio Développer sélectionné.
- Désélectionnez toutes les colonnes, puis sélectionnez uniquement les colonnes à copier de la seconde table. Dans cet exemple, nous sélectionnons uniquement la colonne Montant car notre première table contient déjà un vendeur et un nom de produit.
- Décochez la case Utiliser le nom de la colonne d'origine comme préfixe (à moins que vous ne préfixiez le nom de colonne avec le nom de la table d'où provient cette colonne).
- Cliquez sur OK.
En conséquence, vous obtiendrez une nouvelle table contenant tous les enregistrements de votre première table et les colonnes supplémentaires de la seconde table :
