Comment Faire Une Table De Correspondance Excel

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 tableaux en un seul, et ce tutoriel vous apprendra comment le faire plus efficacement.

Table des matières

Comment faire une jointure dans Excel

Annotate 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 tabular array 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, fifty'ordre des noms dans la table principale ne stand for 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 ;ii; FAUX)

Où:

$A$two est la valeur que vous recherchez.
'Tabular array 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).
ii est le numéro de la colonne à partir de laquelle récupérer la valeur.

Copiez la formule dans la colonne cascade obtenir une table fusionnée composée de la tabular array 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

two) united nations 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.

Annotate 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 :

Cascade accomplir la tâche, vous fournissez les arguments suivants à la formule de correspondance d'index :

Matrice - $Due east$two:$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$two:$Eastward$eight; EQUIV($A2; $F$2:$F$8; 0))

… Et combine parfaitement les données de deux tables :

Annotate combiner des tables en faisant correspondre plusieurs colonnes

Si les deux tables que vous souhaitez joindre n'ont pas d'identifiant unique, tel qu'united nations identifiant de commande, vous pouvez faire correspondre les valeurs dans deux colonnes ou plus en utilisant cette formule :

INDEX(Matrice; EQUIV(one; (valeur_cherché1 = plage_cherché1) * (valeur_cherché2 = plage_cherché2) ; 0) ; numéro_col_retour)

Remarque : C'est une formule matricielle, donc due north'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$ii:$H$8
valeur_cherché1 - $B2
plage_cherché1 - $F$2:$F$eight
valeur_cherché2 - $C2
plage_cherché2 - $G$ii:$K$8
Numéro_colonne_retour - iii

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$two:$H$8;EQUIV(1; ($B2 =$F$2:$F$8) * ($C2 =$G$two:$G$eight);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 united nations 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 :

Ability 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 Ability Query

Cascade 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 connection dans Power Query, procédez comme accommodate :

Sélectionnez votre première table (Commande) ou n'importe quelle cellule de cette tabular array.
Accédez à l'ongletDonnées> Groupe Récupérer et transformer, puis cliquez surà partir d'united nations tableau.
Dans fifty'éditeur de requête Power Query qui s'ouvre, cliquez sur la flèche de la liste déroulanteFermer et charger (et not sur le bouton lui-même!), Puis sélectionnez 50'optionFermer et charger dans….
Dans la boîte de dialogue Importer des données, sélectionnez l'optionCréer uniquement la connexion, puis cliquez surCharger.

Cela créera une connection avec le nom de votre tabular array / plage et 50'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 tabular array

Avec les connexions en place, voyons comment vous pouvez joindre deux tables en une seule :

Dans l'ongletDonnées, dans le groupeRécupérer et transformer, cliquez sur le boutonNouvelle requête, choisissezCombiner les requêtes dans la liste déroulante, puis cliquez surFusionner :

Dans la boîte de dialogue Fusionner, procédez comme suit :

Sélectionnez votre 1ère tabular array (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éroulanteType 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, 50'éditeur Power Query affiche votre première tabular array (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'united nations tableau ressemblant à celui de la capture d'écran ci-dessous. Pour terminer le processus de fusion, procédez comme conform dans l'éditeur Power Query:

Dans la colonne ajoutée (Commissions), cliquez sur la flèche double dans fifty'en-tête.
Dans la boîte qui due south'ouvre, procédez comme suit :

Laissez le bouton radioDéveloppersé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 united nations 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 tabular array 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 :