Comment comparer deux listes dans Excel

Excel est un programme de traitement de données efficace. Et l'une des méthodes d'analyse de l'information est la comparaison de deux listes. Si vous comparez correctement deux listes dans Excel, l'organisation de ce processus sera très simple. Il suffit de suivre certains des points qui seront discutés aujourd'hui. La mise en œuvre pratique de cette méthode dépend entièrement des besoins de la personne ou de l'organisation à un moment donné. Par conséquent, plusieurs cas possibles doivent être envisagés.

Comparer deux listes dans Excel

Bien sûr, vous pouvez comparer deux listes manuellement. Mais cela prendra beaucoup de temps. Excel possède sa propre boîte à outils intelligente qui vous permettra de comparer les données non seulement rapidement, mais également d'obtenir des informations qui ne sont pas si faciles à obtenir avec vos yeux. Supposons que nous ayons deux colonnes avec les coordonnées A et B. Certaines valeurs y sont répétées.

Formulation du problème

Nous devons donc comparer ces colonnes. La procédure de comparaison de deux documents est la suivante :

1. Si les cellules uniques de chacune de ces listes sont les mêmes, que le nombre total de cellules uniques est le même et que les cellules sont les mêmes, alors ces listes peuvent être considérées comme identiques. L'ordre dans lequel les valeurs de cette liste sont empilées n'a pas tellement d'importance.
2. On peut parler de coïncidence partielle des listes si les valeurs uniques elles-mêmes sont les mêmes, mais le nombre de répétitions est différent. Par conséquent, ces listes peuvent contenir un nombre différent d'éléments.
3. Le fait que les deux listes ne correspondent pas est indiqué par un ensemble différent de valeurs uniques.

Ces trois conditions sont simultanément les conditions de notre problème.

La solution du problème

Générons deux plages dynamiques pour faciliter la comparaison des listes. Chacun d'eux correspondra à chacune des listes.

Pour comparer deux listes, procédez comme suit :

1. Dans une colonne séparée, nous créons une liste de valeurs uniques spécifiques aux deux listes. Pour cela nous utilisons la formule : ЕСЛИОШИБКА(ЕСЛИОШИБКА( ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0)); ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0))); «»). La formule elle-même doit être écrite sous forme de formule matricielle.
2. Déterminons combien de fois chaque valeur unique apparaît dans le tableau de données. Voici les formules pour le faire : =COUNTIF(List1,D5) et =COUNTI(List2,D5).
3. Si le nombre de répétitions et le nombre de valeurs uniques sont les mêmes dans toutes les listes incluses dans ces plages, la fonction renvoie la valeur 0. Cela indique que la correspondance est de XNUMX%. Dans ce cas, les titres de ces listes acquerront un fond vert.
4. Si tout le contenu unique est dans les deux listes, alors renvoyé par des formules =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;»*?»;F5:F34;0) la valeur sera zéro. Si E1 ne contient pas zéro, mais qu'une telle valeur est contenue dans les cellules E2 et F2, alors dans ce cas les plages seront reconnues comme concordantes, mais seulement partiellement. Dans ce cas, les en-têtes des listes correspondantes deviendront orange.
5. Et si l'une des formules décrites ci-dessus renvoie une valeur non nulle, les listes seront complètement non concordantes.

C'est la réponse à la question de savoir comment analyser les colonnes pour les correspondances à l'aide de formules. Comme vous pouvez le voir, avec l'utilisation de fonctions, vous pouvez implémenter presque toutes les tâches qui, à première vue, ne sont pas liées aux mathématiques.

Exemple de test

Dans notre version du tableau, il existe trois types de listes de chaque type décrit ci-dessus. Il a partiellement et complètement correspondant, ainsi que non correspondant.

Pour comparer les données, nous utilisons la plage A5:B19, dans laquelle nous insérons alternativement ces paires de listes. A propos de ce que sera le résultat de la comparaison, nous comprendrons par la couleur des listes originales. S'ils sont complètement différents, ce sera un fond rouge. Si une partie des données est identique, alors jaune. En cas d'identité complète, les rubriques correspondantes seront en vert. Comment faire une couleur en fonction du résultat ? Cela nécessite une mise en forme conditionnelle.

Trouver des différences dans deux listes de deux manières

Décrivons deux autres méthodes pour trouver des différences, selon que les listes sont synchrones ou non.

Option 1. Listes synchrones

C'est une option facile. Supposons que nous ayons de telles listes.

Pour déterminer combien de fois les valeurs n'ont pas convergé, vous pouvez utiliser la formule : =SOMMEPROD(—(A2:A20<>B2:B20)). Si nous obtenons 0 comme résultat, cela signifie que les deux listes sont identiques.

Option 2 : Listes mélangées

Si les listes ne sont pas identiques dans l'ordre des objets qu'elles contiennent, vous devez appliquer une fonctionnalité telle que la mise en forme conditionnelle et coloriser les valeurs en double. Ou utilisez la fonction COUNTIF, à l'aide duquel nous déterminons combien de fois un élément d'une liste apparaît dans la seconde.

Comment comparer 2 colonnes ligne par ligne

Lorsque nous comparons deux colonnes, nous devons souvent comparer des informations qui se trouvent dans des lignes différentes. Pour ce faire, l'opérateur nous aidera SI. Voyons comment cela fonctionne dans la pratique. Pour ce faire, nous présentons plusieurs situations illustratives.

Exemple. Comment comparer 2 colonnes pour les correspondances et les différences dans une ligne

Pour analyser si les valeurs qui sont dans la même ligne mais des colonnes différentes sont les mêmes, nous écrivons la fonction IF. La formule est insérée dans chaque ligne placée dans la colonne auxiliaire où les résultats du traitement des données seront affichés. Mais il n'est pas du tout nécessaire de le prescrire dans chaque ligne, il suffit de le copier dans les cellules restantes de cette colonne ou d'utiliser le marqueur de saisie semi-automatique.

Nous devrions écrire une telle formule pour comprendre si les valeurs des deux colonnes sont identiques ou non : =SI(A2=B2, "Correspondance", ""). La logique de cette fonction est très simple : elle compare les valeurs des cellules A2 et B2, et si elles sont identiques, elle affiche la valeur "Coincide". Si les données sont différentes, elle ne renvoie aucune valeur. Vous pouvez également vérifier les cellules pour voir s'il y a une correspondance entre elles. Dans ce cas, la formule utilisée est : =SI(A2<>B2, "Ne correspond pas", ""). Le principe est le même, d'abord le contrôle est effectué. S'il s'avère que les cellules répondent au critère, la valeur "Ne correspond pas" s'affiche.

Il est également possible d'utiliser la formule suivante dans le champ de formule pour afficher à la fois "Correspond" si les valeurs sont identiques, et "Ne correspondent pas" si elles sont différentes : =SI(A2=B2 ; "Correspond", "Ne correspond pas"). Vous pouvez également utiliser l'opérateur d'inégalité au lieu de l'opérateur d'égalité. Seul l'ordre des valeurs qui seront affichées dans ce cas sera légèrement différent : =SI(A2<>B2, "Ne correspond pas", "Coïncide"). Après avoir utilisé la première version de la formule, le résultat sera le suivant.

Cette variation de la formule est insensible à la casse. Par conséquent, si les valeurs d'une colonne ne diffèrent des autres que par le fait qu'elles sont écrites en majuscules, le programme ne remarquera pas cette différence. Pour rendre la comparaison sensible à la casse, vous devez utiliser la fonction dans les critères EXACT. Le reste des arguments reste inchangé : =SI(EXACT(A2,B2), "Correspond", "Unique").

Comment comparer plusieurs colonnes pour les correspondances dans une ligne

Il est possible d'analyser les valeurs dans les listes selon tout un ensemble de critères :

1. Trouvez les lignes qui ont les mêmes valeurs partout.
2. Trouvez les lignes où il y a des correspondances dans seulement deux listes.

Voyons quelques exemples de la marche à suivre dans chacun de ces cas.

Exemple. Comment trouver des correspondances sur une ligne dans plusieurs colonnes d'un tableau

Supposons que nous ayons une série de colonnes contenant les informations dont nous avons besoin. Nous sommes confrontés à la tâche de déterminer les lignes dans lesquelles les valeurs sont les mêmes. Pour ce faire, vous devez utiliser la formule suivante : =SI(ET(A2=B2,A2=C2), "concordance", " ").

S'il y a trop de colonnes dans le tableau, il vous suffit de l'utiliser avec la fonction IF opérateur COUNTIF: =SI(NB.SI($A2:$C2,$A2)=3 ;"correspond" ;" "). Le nombre utilisé dans cette formule indique le nombre de colonnes à vérifier. Si cela diffère, vous devez écrire autant que cela est vrai pour votre situation.

Exemple. Comment trouver des correspondances dans une ligne dans 2 colonnes d'un tableau

Disons que nous devons vérifier si les valeurs d'une ligne correspondent dans deux colonnes à celles du tableau. Pour ce faire, vous devez utiliser la fonction comme condition OR, où écrivez alternativement l'égalité de chacune des colonnes à l'autre. Voici un exemple.

Nous utilisons cette formule: =ЕСЛИ(ИЛИ(A2=B2;B2=C2;A2=C2);”Совпадают”;” “). Il peut y avoir une situation où il y a beaucoup de colonnes dans le tableau. Dans ce cas, la formule sera énorme et la sélection de toutes les combinaisons nécessaires peut prendre beaucoup de temps. Pour résoudre ce problème, vous devez utiliser la fonction COUNTIF: =IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0 ; "Chaîne unique" ; "Chaîne non unique")

On voit qu'au total on a deux fonctions COUNTIF. Avec le premier, nous déterminons alternativement combien de colonnes ont une similitude avec A2, et avec le second, nous vérifions le nombre de similitudes avec la valeur de B2. Si, à la suite du calcul par cette formule, nous obtenons une valeur nulle, cela indique que toutes les lignes de cette colonne sont uniques, si plus, il y a des similitudes. Par conséquent, si à la suite du calcul de deux formules et de l'ajout des résultats finaux, nous obtenons une valeur nulle, la valeur de texte «Chaîne unique» est renvoyée, si ce nombre est supérieur, il est écrit que cette chaîne n'est pas unique.

Comment comparer 2 colonnes dans Excel pour les correspondances

Prenons maintenant un exemple. Disons que nous avons un tableau avec deux colonnes. Vous devez vérifier s'ils correspondent. Pour ce faire, vous devez appliquer la formule, où la fonction sera également utilisée IF, et l'opérateur COUNTIF: =IF(COUNTIF($B:$B,$A5)=0, "Aucune correspondance dans la colonne B", "Il y a des correspondances dans la colonne B")

Aucune autre action est nécessaire. Après avoir calculé le résultat par cette formule, on obtient si la valeur du troisième argument de la fonction IF allumettes. S'il n'y en a pas, alors le contenu du deuxième argument.

Comment comparer 2 colonnes dans Excel pour les correspondances et mettre en évidence avec la couleur

Pour faciliter l'identification visuelle des colonnes correspondantes, vous pouvez les mettre en évidence avec une couleur. Pour ce faire, vous devez utiliser la fonction "Formatage conditionnel". Voyons en pratique.

Recherche et mise en surbrillance des correspondances par couleur dans plusieurs colonnes

Pour déterminer les correspondances et les mettre en évidence, vous devez d'abord sélectionner la plage de données dans laquelle la vérification sera effectuée, puis ouvrir l'élément «Formatage conditionnel» de l'onglet «Accueil». Là, sélectionnez "Valeurs en double" comme règle de sélection de cellule.

Après cela, une nouvelle boîte de dialogue apparaîtra, dans laquelle dans la liste contextuelle de gauche, nous trouvons l'option «Répéter», et dans la liste de droite, nous sélectionnons la couleur qui sera utilisée pour la sélection. Après avoir cliqué sur le bouton "OK", l'arrière-plan de toutes les cellules présentant des similitudes sera sélectionné. Ensuite, comparez simplement les colonnes à l'œil nu.

Recherche et mise en surbrillance des lignes correspondantes

La technique pour vérifier si les chaînes correspondent est légèrement différente. Tout d'abord, nous devons créer une colonne supplémentaire, et là nous utiliserons les valeurs combinées à l'aide de l'opérateur &. Pour ce faire, vous devez écrire une formule de la forme : =A2&B2&C2&D2.

Nous sélectionnons la colonne qui a été créée et contient les valeurs combinées. Ensuite, nous effectuons la même séquence d'actions que celle décrite ci-dessus pour les colonnes. Les lignes en double seront mises en surbrillance dans la couleur que vous spécifiez.

On voit qu'il n'y a rien de difficile à chercher des répétitions. Excel contient tous les outils nécessaires pour cela. Il est important de pratiquer juste avant de mettre toutes ces connaissances en pratique.