Comment comparer facilement deux listes avec une simple formule Excel ?
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 :
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.
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 ».
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) :
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.
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")))
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.