Guide ultime – Comparer deux listes ou ensembles de données dans Excel – Le Club Excel

C’est donc la façon la plus basique de comparer deux listes dans Excel. C’est rapide, c’est simple et c’est efficace. Vous pouvez également appliquer un formatage conditionnel basé sur des formules, ce que nous verrons plus tard dans cet article.

Mise en correspondance de données dans Excel à l’aide de la fonction MATCH

Il existe de nombreuses formules de consultation que vous pouvez utiliser pour comparer deux plages ou listes dans Excel. La première que nous allons examiner est la fonction MATCH.

La fonction MATCH renvoie la position relative dans une liste. Un nombre basé sur sa position, si elle est trouvée, dans le tableau de lookup.

La syntaxe de MATCH est

=MATCH (valeur de lookup, tableau de lookup, type de correspondance)

Où la valeur de lookup est la valeur pour laquelle vous voulez trouver une correspondance. Lookup array est la liste dans laquelle vous recherchez une correspondance. Et le type de correspondance vous permet de choisir entre une correspondance exacte ou approximative.

Nous voulons écrire une formule de correspondance pour voir si les éléments de la liste 2 sont dans la liste 1.

Dans la cellule E3, nous pouvons entrer la formule

=MATCH(C2, $A$2:$A$21,0)

En remplissant cette formule, pour les valeurs où Excel trouve une correspondance, la position de cette correspondance sera retournée. Lorsqu’il n’y a pas de correspondance, la valeur de retour sera un #N/A.

Très souvent, la position relative ou le #N/A n’a aucune valeur pour nous et nous devons convertir ces valeurs en vrai ou faux. Pour ce faire, nous pouvons facilement développer notre formule Match en utilisant une fonction logique. Comme Match renvoie un nombre, nous pouvons utiliser la fonction ISNUMBER

=ISNUMBER(MATCH(C2, $A$2:$A$21,0))

Comparer 2 listes dans Excel 365 avec MATCH ou XMATCH comme fonction de tableau dynamique

Si vous utilisez Excel 365, vous avez d’autres alternatives lorsque vous utilisez MATCH pour comparer des listes ou des données. Comme Excel 365 raisonne en tableaux, nous pouvons maintenant passer un tableau comme valeur de consultation de MATCH et nos résultats se rempliront pour nous. Cela supprime le besoin de copier la formule vers le bas et avec seulement 1 formule, votre feuille de calcul sera moins sujette aux erreurs et plus compacte.

MATCH et tableaux dynamiques pour comparer 2 listes

Si vous n’êtes pas encore familier avec les tableaux dynamiques, je vous suggère de lire cet article : Excel Dynamic Arrays – Une nouvelle façon de modéliser vos feuilles de calcul Excel : pour mieux comprendre leur fonctionnement et les plages de déversement.

Le seul changement apporté à la formule de correspondance est qu’au lieu de sélectionner la cellule C2 comme valeur de consultation, nous sélectionnerons la plage C2:12

=ISNUMBER(MATCH (C2:C12,$A$2:$A$21,0))

Comme vous pouvez le voir, la formule unique déverse les résultats dans la colonne E.

XMATCH Excel 365 pour comparer deux listes

Excel 365 introduit également la nouvelle fonction XMATCH. Tout comme la fonction MATCH, XMATCH renvoie une position relative dans une liste. Maintenant vous êtes familier avec XLOOKUP, qui remplace l’ancienne fonction VLOOKUP, vous savez que XLOOKUP vient avec une puissance supplémentaire. Cela se présente sous la forme de nouvelles conditions dans la syntaxe de la formule, comme le mode de recherche et les types de correspondance. Eh bien, XMATCH aussi comme cette puissance supplémentaire par rapport à son prédécesseur MATCH.

La syntaxe de XMATCH est

XMATCH (Lookup Value, Lookup Array, ,)

Où

Lookup Value est la valeur dont vous cherchez à trouver la position relative

Lookup Array est la ligne ou la colonne qui contient la Lookup Value

Le mode de correspondance est facultatif. Contrairement à l’ancienne fonction MATCH, la correspondance par défaut est une correspondance exacte. Vous pouvez également choisir entre

1. Correspondance exacte ou la plus petite suivante
2. Correspondance exacte ou la plus grande suivante
3. Correspondance par caractère générique

Le mode de recherche est également facultatif. Par défaut (et seule option dans l’ancienne fonction MATCH), la recherche se fait de haut en bas. Vous pouvez également sélectionner les recherches de la dernière à la première et les recherches binaires. Si vous travaillez avec des recherches binaires. L’option de correspondance joker ne fonctionne pas.

Avec XMATCH, nous pouvons utiliser soit des tableaux dynamiques, soit des références de cellules pour créer la formule, tout comme nous l’avons examiné avec MATCH. Pour cet exemple, nous allons utiliser des tableaux dynamiques. La formule est très similaire à celle utilisée avec MATCH, sauf que nous n’avons pas à sélectionner 0 pour une correspondance exacte, car dans XMATCH, c’est le paramètre par défaut. Mélangeons un peu les choses cette fois-ci un regard sur la recherche d’éléments dans la liste 2 pas dans la liste un.

Dans ce cas nous pouvons utiliser la formule

=NOT(ISNUMBER(XMATCH(C2:C12,A2:A21)))

Où not transformera les vrais en faux et les faux en vrais.

Tableaux – Comparer des listes dans Excel où les tailles des plages pourraient changer

Dans chacune des formules que nous avons examinées jusqu’à présent, nous avons sélectionné une plage de cellules dans nos fonctions Match qui n’est pas dynamique. Cela signifie que si nous ajoutons de nouvelles données à l’une des listes, nous avons une étape manuelle pour mettre à jour notre formule afin d’inclure les nouvelles données.

Pour convertir les listes en tableaux, sélectionnez l’une des listes et appuyez sur CTRL. C’est le raccourci clavier pour convertir en tableau. Si vous avez sélectionné l’en-tête dans la plage de cellules, assurez-vous de cocher la case pour confirmer que votre tableau a des en-têtes.

Les tableaux utilisent par nature un nommage structuré. Par conséquent, lorsque vous écrivez une formule et que vous sélectionnez une colonne dans un tableau, elle n’affichera pas les références des cellules, mais le nom de la colonne.

En regardant notre formule précédente utilisant XMATCH pour trouver les éléments de la liste 2 qui ne sont pas dans la liste 1, nous pouvons réécrire cette fonction maintenant en utilisant nos références de table

=NOT (ISNUMBER(XMATCH(list2,list1)))

Maintenant, comme nous avons utilisé des tables, si nous ajoutons une nouvelle ligne à l’une des tables, notre plage de déversement augmentera également pour inclure les nouvelles données.

Souligner les différences dans les listes en utilisant le formatage conditionnel personnalisé

Plus tôt dans cet article, nous avons examiné une façon très rapide de comparer ces deux listes en utilisant une règle prédéfinie pour les doublons. Nous pouvons cependant aussi utiliser la mise en forme conditionnelle personnalisée. Si vous n’êtes pas familier avec la mise en forme conditionnelle personnalisée, je vous suggère de consulter cet article : Les astuces de mise en forme conditionnelle dynamique d’Excel :

Nous devons examiner deux approches différentes ici pour mettre en évidence les différences. Lorsque nous n’utilisons pas de tableaux et que nous avons créé une formule vrai/faux pour identifier les différences, nous pouvons prendre une copie de la formule et l’ajouter à notre mise en forme personnalisée. Cependant, avec les tableaux, nous devons forcer l’utilisation des références de cellule.

Copier la formule dans la mise en forme conditionnelle personnalisée

Commencez par prendre une copie de la formule. Comme nous avons testé la formule dans la feuille de calcul, nous pouvons voir qu’elle fonctionne avant de l’utiliser dans la mise en forme conditionnelle. C’est la meilleure pratique car très souvent avec les références de cellules relatives et absolues, il peut être difficile d’obtenir la formule correcte.

Sélectionnez les cellules auxquelles vous voulez appliquer la mise en forme personnalisée. Ensuite, à partir du ruban Accueil, sélectionnez le menu déroulant de formatage conditionnel et sélectionnez Nouvelle règle

La boîte de configuration de la nouvelle règle de formatage s’ouvre et sélectionnez Utiliser une formule pour déterminer les cellules à formater. Ensuite, collez la formule et définissez le type de formatage

Il en résultera que toutes les cellules qui sont dans les deux listes seront formatées au format que vous avez choisi.

N’oubliez pas que nous avons sélectionné une plage de cellules pour appliquer ce formatage conditionnel et qu’il n’est pas dynamique. Si nous utilisions des tableaux, cela se mettrait à jour sans qu’il soit nécessaire de modifier quoi que ce soit !

Autres formules utilisées pour comparer deux listes dans Excel

Il existe de nombreuses formules que vous pouvez utiliser pour comparer deux listes dans Excel. Nous avons déjà examiné MATCH et XMATCH, mais nous allons maintenant en examiner quelques autres. N’importe laquelle des fonctions de consultation fonctionnera vraiment ainsi que quelques autres!

VLOOKUP pour comparer deux listes dans Excel

Si vous n’êtes pas familier avec VLOOKUP, vous pouvez lire à son sujet ici. En termes simples, VLOOKUP renverra une valeur correspondante d’une cellule, s’il n’y a pas de valeur correspondante, une erreur #N/A sera retournée. Dans notre exemple, nous travaillons avec du texte. Nous pouvons donc exécuter un VLOOKUP et vérifier s’il renvoie du texte. Si nous utilisions des chiffres, nous pourrions remplacer ISTEXT par ISNUMBER.

Nous pourrions utiliser la fonction =ISTEXT(VLOOKUP(C2, $A$2:$A$21,1,FALSE))

Ou si nous utilisions des tableaux dynamiques dans Excel 365, nous pourrions utiliser la fonction

=ISTEXT(VLOOKUP (C2 :C12,$A$2:$A$21,1,FALSE))

XLOOKUP pour comparer deux listes dans Excel

XLOOKUP a été introduit dans Excel 365 et vous pouvez en savoir plus à son sujet ici. Très similaire à VLOOKUP, XLOOKUP retournera une valeur correspondante à partir d’une cellule, et vous pouvez définir un résultat si la valeur n’est pas trouvée. En utilisant des tableaux dynamiques, la fonction serait

=ISTEXT(XLOOKUP (C2:C12,A2:A21,A2:A21))

COUNTIF pour comparer deux listes dans Excel

La fonction COUNTIF comptera le nombre de fois qu’une valeur, ou un texte est contenu dans une plage. Si la valeur n’est pas trouvée, 0 est renvoyé. Nous pouvons combiner cela avec une instruction IF pour renvoyer nos valeurs vraies et fausses.

=IF(COUNTIF (A2:A21,C2:C12)<>0, « True », « False »)

Comment comparer 2 ensembles de données dans Excel

Comparer deux listes est assez facile et nous avons examiné maintenant plusieurs façons de le faire. Mais la comparaison de deux ensembles de données peut être un peu plus difficile.

Regardons un exemple. Nous avons deux tableaux de données, chacun contenant les mêmes en-têtes de colonnes. En regardant l’image, nous pouvons voir que faire correspondre ces deux tableaux nécessiterait de regarder plus d’une colonne.

Lorsque vous devez regarder plus d’une colonne, la solution serait de créer une colonne composite combinant les données en une seule colonne. Cela créera une colonne unique pour chaque ligne que nous pouvons ensuite utiliser comme colonne correspondante

Dans cet exemple, nous pourrions combiner le Nom et la DoB pour donner à chaque tableau un identifiant unique

Il existe de nombreuses façons de joindre le contenu d’une cellule, dans ce cas nous ferons une simple concaténation. Comme nous utilisons des tableaux, la formule va ferrer le format de dénomination des tableaux.

= & »-« &

Répétez les étapes sur le deuxième tableau.

Maintenant, nous pouvons utiliser n’importe lequel des exemples ci-dessus pour faire correspondre ces deux nouvelles colonnes de données. Lorsqu’elles correspondent, nous savons qu’elles correspondent à la ligne entière.

Comparer des listes ou des ensembles de données à l’aide de Power Query

Vous pouvez également comparer des listes et des ensembles de données à l’aide d’Excels Power Query. En se connectant aux tables puis en fusionnant les tables, en utilisant différents types de jointure, nous pouvons comparer les deux listes.

Dans cette vidéo, vous apprendrez à comparer ou à réconcilier deux ensembles de données différents en utilisant Excels Power query

Il y a un ensemble de données pour accompagner cette vidéo et une pratique le long de laquelle vous pouvez vous saisir de cet article.