Fusionner deux listes sans doublons

Une situation classique : vous avez deux listes qui doivent être fusionnées en une seule. De plus, dans les listes initiales, il peut y avoir à la fois des éléments uniques et des éléments correspondants (à la fois entre les listes et à l'intérieur), mais à la sortie, vous devez obtenir une liste sans doublons (répétitions):

Examinons traditionnellement plusieurs façons de résoudre un problème aussi courant - du primitif «sur le front» au plus complexe, mais élégant.

Méthode 1 : supprimer les doublons

Vous pouvez résoudre le problème de la manière la plus simple - copiez manuellement les éléments des deux listes en une seule, puis appliquez l'outil à l'ensemble résultant. Supprimer les doublons de l'onglet Données (Données - Supprimer les doublons):

Bien sûr, cette méthode ne fonctionnera pas si les données des listes source changent souvent - vous devrez répéter l'intégralité de la procédure après chaque modification. 

Méthode 1a. tableau croisé dynamique

Cette méthode est en fait la suite logique de la précédente. Si les listes ne sont pas très grandes et que le nombre maximum d'éléments qu'elles contiennent est connu à l'avance (par exemple, pas plus de 10), alors vous pouvez combiner deux tables en une par des liens directs, ajouter une colonne avec celles à droite et construire un tableau récapitulatif basé sur le tableau résultant :

Comme vous le savez, le tableau croisé dynamique ignore les répétitions, donc à la sortie nous obtiendrons une liste combinée sans doublons. La colonne auxiliaire avec 1 est nécessaire uniquement car Excel peut créer des tableaux récapitulatifs contenant au moins deux colonnes.

Lorsque les listes d'origine sont modifiées, les nouvelles données iront dans le tableau combiné via des liens directs, mais le tableau croisé dynamique devra être mis à jour manuellement (clic droit - Mettre à jour et enregistrer). Si vous n'avez pas besoin de recalculer à la volée, il est préférable d'utiliser d'autres options.

Méthode 2 : formule matricielle

Vous pouvez résoudre le problème avec des formules. Dans ce cas, le recalcul et la mise à jour des résultats se feront automatiquement et instantanément, immédiatement après les modifications des listes d'origine. Pour plus de commodité et de brièveté, donnons des noms à nos listes. Liste 1 и Liste 2en utilisant Gestionnaire de noms languette formule (Formules — Gestionnaire de noms — Créer):

Après avoir nommé, la formule dont nous avons besoin ressemblera à ceci :

À première vue, cela semble effrayant, mais, en fait, tout n'est pas si effrayant. Permettez-moi de développer cette formule sur plusieurs lignes en utilisant la combinaison de touches Alt+Entrée et d'indenter avec des espaces, comme nous l'avons fait, par exemple ici :

La logique ici est la suivante :

• La formule INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) sélectionne tous les éléments uniques de la première liste. Dès qu'ils sont épuisés, une erreur #N/A commence à se produire :

  

• La formule INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) extrait les éléments uniques de la deuxième liste de la même manière.
• Imbriquées l'une dans l'autre, deux fonctions IFERROR implémentent la sortie d'abord des uniques de la liste-1, puis de la liste-2 l'une après l'autre.

Notez qu'il s'agit d'une formule matricielle, c'est-à-dire qu'après avoir tapé, il faut la saisir dans une cellule qui n'est pas ordinaire Entrer, mais avec un raccourci clavier Ctrl+Shift+Entrer puis copiez (faites glisser) vers le bas jusqu'aux cellules enfants avec une marge.

Dans la version anglaise d'Excel, cette formule ressemble à :

=SIERREUR(SIERREUR(INDEX(Liste1, MATCH(0, NB.SI($E$1:E1, Liste1), 0)), INDEX(Liste2, MATCH(0, NB.SI($E$1:E1, Liste2), 0)) ), "") 

L'inconvénient de cette approche est que les formules matricielles ralentissent sensiblement le travail avec le fichier si les tables source ont un grand nombre (plusieurs centaines ou plus) d'éléments. 

Méthode 3. Power Query

Si vos listes source contiennent un grand nombre d'éléments, par exemple plusieurs centaines ou milliers, alors au lieu d'une formule matricielle lente, il est préférable d'utiliser une approche fondamentalement différente, à savoir les outils complémentaires Power Query. Ce complément est intégré à Excel 2016 par défaut. Si vous avez Excel 2010 ou 2013, vous pouvez le télécharger et l'installer séparément (gratuitement).

L'algorithme des actions est le suivant:

1. Ouvrir un onglet séparé du module complémentaire installé Requête d'alimentation (si vous avez Excel 2010-2013) ou allez simplement dans l'onglet Données (si vous avez Excel 2016).
2. Sélectionnez la première liste et appuyez sur le bouton À partir du tableau/plage (De la plage/du tableau). Interrogés sur la création d'une "table intelligente" à partir de notre liste, nous sommes d'accord :

   

3. La fenêtre de l'éditeur de requête s'ouvre, où vous pouvez voir les données chargées et le nom de la requête Tableau 1 (vous pouvez le remplacer par le vôtre si vous le souhaitez).
4. Double-cliquez sur l'en-tête du tableau (mot Liste 1) et renommez-le en un autre (par exemple Personnes). Ce qu'il faut nommer exactement n'est pas important, mais le nom inventé doit être rappelé, car. il devra être réutilisé ultérieurement lors de l'import de la seconde table. La fusion de deux tables à l'avenir ne fonctionnera que si leurs en-têtes de colonne correspondent.
5. Développer la liste déroulante dans le coin supérieur gauche fermer et télécharger et choisissez Fermez et chargez… (Fermer&Charger vers…):

   

6. Dans la boîte de dialogue suivante (elle peut sembler un peu différente - ne vous inquiétez pas), sélectionnez Créez simplement une connexion (Créer uniquement une connexion):

   

7. Nous répétons toute la procédure (points 2 à 6) pour la deuxième liste. Lorsque vous renommez un en-tête de colonne, il est important d'utiliser le même nom (Personnes) que dans la requête précédente.
8. Dans la fenêtre Excel sur l'onglet Données ou sur l'onglet Requête d'alimentation Selectionnez Obtenir des données - Combiner des demandes - Ajouter (Obtenir des données - Fusionner des requêtes - Ajouter):

   

9. Dans la boîte de dialogue qui s'affiche, sélectionnez nos demandes dans les listes déroulantes :

   

10. En conséquence, nous obtiendrons une nouvelle requête, où deux listes seront connectées l'une sous l'autre. Il reste à supprimer les doublons avec le bouton Supprimer les lignes - Supprimer les doublons (Supprimer les lignes - Supprimer les doublons):

    

11. La requête terminée peut être renommée sur le côté droit du panneau d'options, en lui donnant un nom sain (ce sera le nom de la table de résultats en fait) et tout peut être téléchargé sur la feuille avec la commande fermer et télécharger (Fermer&Charger):

    

À l'avenir, en cas de modification ou d'ajout aux listes d'origine, il suffira d'un clic droit pour mettre à jour le tableau des résultats.

• Comment collecter plusieurs tables à partir de différents fichiers à l'aide de Power Query
• Extraire des éléments uniques d'une liste
• Comment comparer deux listes entre elles pour les correspondances et les différences