Comment comparer facilement deux listes avec une simple formule Excel ?

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

Dans ce tutoriel, je vais vous montrer comment comparer deux listes dans Excel et extraire les valeurs communes en quelques étapes simples.

Cette méthode va vous permettre de pour des listes de produits, de clients, ou même de commandes, et que vous voulez savoir rapidement quels éléments apparaissent dans les deux listes.

Et pour cela, nous utiliserons des formules dynamiques introduites sur les dernières versions d’Excel.

 

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

Tutoriel Vidéo

 

 

1. Présentation

pour illustrer ce tutoriel, nous allons comparer deux listes de produits d’une entreprise possédant deux entrepôts.

L’objectif est de comparer les produits disponibles dans les deux entrepôts, afin de repérer les produits communs aux deux lieux pour mieux pour les stocks et optimiser les approvisionnements.

Excel formation - 0022-Comparer deux listes - 01

pour simplifier les formules à venir, nous allons nommer les plages de cellules contenant ces produits.

pour cela, nous sélectionnons la plage des références de l’entrepôt principal, puis nous nous rendons dans le menu « Formules » et cliquons sur « Définir un nom ».

Dans le champ « Nom », nous tapons « Liste1 » et validons avec « Ok ».

Excel formation - 0022-Comparer deux listes - 02

Nous faisons de même pour le magasin secondaire en lui donnant le nom « Liste2 ».

pour aller plus vite, nous également saisir ce nom directement la zone des noms (à gauche de la barre des formules) :

Excel formation - 0022-Comparer deux listes - 03

En nommant ainsi nos plages, nous facilitons la lecture et la compréhension de nos formules. Par exemple, au lieu de référencer les cellules A9:A16 ou C2:C15 dans chaque formule, nous pourrons simplement écrire « Liste1 » ou « Liste2 ».

Cela rendra nos calculs bien plus intuitifs et limitera les erreurs en cas de modification du contenu de nos listes.

Cette préparation nous permet donc de construire des formules plus lisibles et compréhensibles, essentielles pour une utilisation rapide et efficace d’Excel, notamment si le fichier est destiné à être partagé avec d’autres collaborateurs.

 

2. Extraire les valeurs communes avec une formule Excel

Maintenant que nos listes de produits sont prêtes et que nous avons nommé les plages de données pour faciliter la lecture des formules, nous allons utiliser la fonction FILTRE pour extraire les produits communs aux deux entrepôts.

La fonction FILTRE est extrêmement flexible et permet d’afficher uniquement les valeurs correspondant aux critères que nous avons définis.

Dans notre exemple, nous souhaitons afficher les produits qui sont présents dans les deux listes, c’est-à-dire les références qui apparaissent à la fois dans « Liste1 » (Entrepôt principal) et dans « Liste2 » (Magasin secondaire).

pour cela, nous allons combiner FILTRE avec une autre fonction, NB.SI, qui nous aidera à vérifier la présence de chaque produit de « Liste1 » dans « Liste2 ».

Dans la cellule E9, nous saisissons la formule suivante

 =FILTRE(Liste1;NB.SI(Liste2;Liste1)>0;"Aucun produit  commun") 

Où :

  • L’argument « Liste1 » : Il s’agit de la plage de valeurs que nous voulons filtrer. En saisissant « Liste1 » comme argument principal de FILTRE, nous indiquons que nous souhaitons afficher uniquement les produits de cette liste qui répondent à notre critère.
  • La condition NB.SI(Liste2;Liste1)>0 : C’est ici que la magie opère pour extraire les valeurs communes. La fonction NB.SI parcourt « Liste2 » et compte le nombre de fois où chaque produit de « Liste1 » apparaît dans « Liste2 ». En ajoutant la condition « > 0 », nous filtrons uniquement les produits qui apparaissent au moins une fois dans « Liste2 ». Si la fonction NB.SI renvoie une valeur supérieure à zéro pour un produit de « Liste1 », cela signifie qu’il est présent dans les deux listes, et donc il sera affiché par FILTRE.
  • Message personnalisé « Aucun produit commun » : Cet argument final est utilisé pour personnaliser le message affiché si aucun produit de « Liste1 » n’est trouvé dans « Liste2 ». Dans notre cas, si aucun produit commun n’existe entre les deux listes, le texte « Aucun produit commun » sera affiché dans la cellule E9, au lieu de laisser celle-ci vide. Ce type de personnalisation améliore la lisibilité et rend l’analyse plus conviviale pour les utilisateurs, en affichant un message explicite en cas d’absence de correspondance.

Nous validons ensuite la formule en appuyant sur la touche [Entrée], et notre liste de produits communs s’affiche automatiquement en commençant à partir de la cellule E9.

Excel formation - 0022-Comparer deux listes - 04

En effet, la fonction FILTRE est ce que l’on appelle une fonction matricielle dans Excel, ce qui signifie qu’elle peut générer une série de valeurs dans les cellules adjacentes à celle contenant la formule, formant ainsi une « matrice » de résultats.

Cela se traduit par le fait que la liste des produits communs va automatiquement « se répandre » dans les cellules en dessous de la cellule E9.

Cela permet de traiter efficacement un grand nombre de données et d’afficher une liste dynamique et interactive.

De plus, les fonctions matricielles sont dynamiques, ce qui signifie que si nous ajoutons, supprimons ou modifions des produits dans « Liste1 » ou « Liste2 », le résultat dans la cellule E9 et les cellules adjacentes s’actualise automatiquement en fonction des nouvelles données.

Cela signifie que notre liste de produits communs est toujours à jour, sans nécessiter de recalculs ou de mises à jour manuelles, ce qui est particulièrement utile pour les listes fréquemment modifiées.

L’utilisation de FILTRE nous permet d’afficher uniquement les produits présents dans les deux listes de façon claire et rapide.

 

3. Éliminer les doublons

Une fois que nous avons extrait les valeurs communes entre nos deux listes avec la fonction FILTRE, il se peut que certains produits apparaissent plusieurs fois, surtout si un même modèle est enregistré plusieurs fois dans l’entrepôt principal.

La fonction UNIQUE permet donc de rendre notre liste plus lisible et concise en éliminant les doublons.

Nous modifions maintenant la formule pour éliminer les doublons, en encapsulant la formule précédente comme ceci :

  =UNIQUE(FILTRE(Liste1;NB.SI(Liste2;Liste1)>0;"Aucun produit  commun")) 

La fonction UNIQUE permet de ne conserver chaque valeur qu’une seule fois dans le résultat final.

Si nous voulons un contrôle encore plus précis sur la suppression des doublons, nous pourrions utiliser d’autres arguments de la fonction UNIQUE.

Par défaut, Excel supprime uniquement les doublons exacts, mais d'autres options permettent de supprimer les valeurs répétées horizontalement ou verticalement selon vos besoins.

En combinant UNIQUE et FILTRE, nous avons maintenant une liste nette et sans redondances des produits communs aux deux entrepôts.

 

4. Trier les résultats avec TRIER

Ensuite, pour rendre notre résultat encore plus clair et ordonné, nous allons utiliser la fonction TRIER.

Le tri est particulièrement utile si nous avons une longue liste et que nous souhaitons visualiser les produits dans un ordre spécifique.

Encore une fois, nous encapsulons notre formule dans cette nouvelle fonction TRIER :

  =TRIER(UNIQUE(FILTRE(Liste1;NB.SI(Liste2;Liste1)>0;"Aucun produit  commun"))) 

Excel formation - 0022-Comparer deux listes - 05

Cette formule trie les produits communs dans l'ordre alphabétique par défaut, de sorte que nous puissions les retrouver plus facilement.

Par défaut, TRIER organise les valeurs par ordre croissant. Mais nous pouvons aussi spécifier un ordre décroissant en ajoutant un argument supplémentaire dans la fonction TRIER. Par exemple, en ajoutant un 1 pour l’ordre décroissant :

  =TRIER(UNIQUE(FILTRE(Liste1;NB.SI(Liste2;Liste1)>0;"Aucun produit  commun"));1;-1) 

Ce choix dépend de nos préférences et de la façon dont nous souhaitons visualiser les informations.

Ainsi, en combinant les fonctions FILTRE, UNIQUE et TRIER, nous avons appris à comparer deux listes dans Excel de façon simple et efficace.

Ces formules nous permettent d’extraire les éléments communs, de supprimer les doublons et de trier les données.

Ce processus est particulièrement utile pour des cas concrets, comme la gestion d’inventaires ou la comparaison de listes de clients, et il nous évite de devoir trier manuellement nos informations.

 



Articles qui pourraient vous intéresser

Comment calculer les durées de travail complexes (à cheval sur deux jours) dans Excel ?
Comment comparer facilement deux listes avec une simple formule Excel ?
Comment effectuer des remplacements multiples avec une seule formule magique d'Excel que personne ne vous a jamais montrée !
Les erreurs de concaténation qui vous font perdre du temps sur Excel
Découvrez la nouvelle fonction Excel que 90% des experts ne connaissent pas !
Boostez vos investissements : maîtrisez le calcul du taux de rentabilité avec Excel en 10 minutes !
Les 8 (+1 incroyable) fonctions Excel à maîtriser absolument en 2024
Percez les secrets de la fonction SOMME (SOMME.SI/SOMMEPROD) et des ces déclinaisons pour boostez votre productivité sur Excel ?
TOP3 des méthodes pour extraire des données par date dans Excel
Comment identifier la date la plus ancienne ou la plus récente en fonction de critères sur Excel ?
Comment calculer une moyenne sans tenir compte des zéros sur Excel ?
Comment fusionner des tableaux, les trier et supprimer les doubles avec une seule formule Excel ? (ASSEMB.V et ASSEMB.H)

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2024 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.