Partage pratique de compétences Excel : comparaison de données dans plusieurs situations différentes

Le travail quotidien nécessite de temps en temps de comparer des données, de trouver des différences, de trouver des valeurs en double, etc. Certains comparent les données d’une même feuille de calcul et d’autres comparent les données de différentes feuilles de calcul. Nous résumons ici les comparaisons de données dans une variété de situations différentes et fournissons des méthodes rapides afin que chacun puisse effectuer rapidement des comparaisons de données dans différentes situations.

Partie 1 : Comparaison des données dans le même tableau

---

1. Comparez strictement si les données de deux colonnes sont identiques

La comparaison dite stricte fait référence à la comparaison des données selon la position.

1) Comparaison des touches de raccourci Ctrl+

Comme le montre la figure ci-dessous, sélectionnez les deux colonnes de données à comparer, la colonne A et la colonne B, puis appuyez sur la touche de raccourci Ctrl+ Différentes données B5, B9, B10. , et B15 sera dans l'état sélectionné.

2) Comparaison des méthodes de positionnement (touche de raccourci F5 ou Ctrl+G)

En prenant le tableau suivant comme exemple, sélectionnez les en-têtes des colonnes A et B pour sélectionner rapidement deux colonnes de données, et puis appuyez sur la touche de raccourci F5 (ou Ctrl+G) pour afficher la fenêtre de positionnement, sélectionnez la condition de positionnement comme « cellules de différence de contenu de ligne », cliquez sur le bouton « OK », et les différentes données seront sélectionnées.

Remarque :

Les deux méthodes ci-dessus peuvent comparer rapidement les différences entre deux colonnes de données, mais elles ne feront pas la distinction entre les lettres majuscules et minuscules.

3) Comparaison des fonctions IF

(1) Comparaison des fonctions if qui ne nécessitent pas de lettres sensibles à la casse

Les deux colonnes A et B du tableau ci-dessous sont des chiffres, il n'y a pas de lettres, et il y a aucune sensibilité à la casse.

Vous pouvez saisir la formule =IF(A2=B2, "Idem", "Pas pareil") dans la cellule C2 Après la saisie, tirez la poignée et faites-la glisser vers le bas jusqu'à ce que les données de cette colonne se terminent. Les résultats identiques et différents sont clairs en un coup d’œil, comme le montre le tableau ci-dessous.

(2) Comparaison des fonctions if sensibles à la casse des lettres

Si les données de comparaison contiennent des lettres et doivent être sensibles à la casse, la formule ci-dessus ne peut pas être comparée avec précision. À ce stade, vous pouvez modifier la formule C2 en =IF(EXACT(A2,B2)=TRUE,"Identique", "Pas pareil"), puis dérouler pour remplir la formule, comme indiqué dans la figure ci-dessous. .

2. Rechercher les valeurs en double dans deux colonnes de données

1) Fonction IF + MATCH pour trouver les valeurs en double

Nous devons maintenant trouver la liste des gagnants pour deux trimestres consécutifs dans le tableau ci-dessous, et existe-t-il un moyen ?

En fait, il faut trouver les valeurs en double en comparant la colonne A et la colonne B. Nous pouvons utiliser la fonction IF+MATCH pour combiner la formule, saisir la formule =IF(ISERROR(MATCH(A2,$B:$B,0)),"",A2) dans la cellule C2, puis dérouler vers le bas pour copier la formule afin de terminer la tâche de recherche. Consultez le tableau ci-dessous pour comparer les résultats de recherche :

Analyse de formule :

MATCH permet de renvoyer la position de la donnée A2 à trouver dans la zone $B$2 :$B$25. S'il est trouvé, un numéro de ligne sera renvoyé (indiquant qu'il y a une duplication), s'il n'est pas trouvé, une erreur #N/A sera renvoyée (indiquant qu'il n'y a pas de duplication).

  Ajoutez la fonction ISERROR à la formule pour déterminer si la valeur renvoyée par MATCH est une erreur #N/A Si c'est une erreur #N/A, elle retournera TRUE Si ce n'est pas une erreur #N/A. , cela ira de FALSE.

La fonction IF la plus externe renvoie des valeurs différentes selon que ISERROR (MATCH ()) est VRAI ou FAUX. S'il est VRAI (c'est-à-dire qu'il n'y a pas de répétition), il renvoie vide ; s'il est FAUX, il renvoie A2.

Si nous voulons connaître la liste des gagnants au premier trimestre mais pas au deuxième trimestre, nous pouvons changer la formule de fonction ci-dessus en : =IF(ISERROR(MATCH(A2,$B$2:$ 25,0 B$ )), A2, ""). =IF(ISERROR(MATCH(A2,$B:$B,0)), A2, "")。

2）IF+COUNTIF函数查找重复值

    下表A、B两列都是客户的姓名，需要找到两列重复的客户名称，并在C列标识出来。

    操作方法为在C2单元格输入公式=IF(COUNTIF(A:A,B2)=0,"",B2)，然后下拉完成excel两列数据对比。请看下面演示！

    COUNTIF函数是对指定区域中符合指定条件的单元格计数的一个函数。

考考你：

    如果上述中对比的数值超过15位，比如对比的是身份证号，上述公式是否还可以用？如果上述公式不能用了，改换成以下公式呢？

    =IF(COUNTIF(A: A,B2&"*")=0," ",B2)

或者

    =IF(SUMPRODUCT(1*(A:A=B2)),B2,"")

    如果不知道答案，欢迎观看教程《卡号离奇减少表哥冤枉被罚——Excel，原来你有真假重复！》。

3）IF+VLOOKUP函数查找重复值

    如下表所示，有这样两组员工号。不知道哪些是A、B两组都有的。我们也可以用if+VLOOKUP函数公式来完成比对。

    在C2单元格中输入公式：=IF(ISNA(VLOOKUP(A2,$B:$B,1,))," ",A2)

2) Fonction IF+COUNTIF pour trouver les valeurs en double

Les deux colonnes A et B du tableau ci-dessous sont les noms des clients. Vous devez trouver les noms des clients en double dans les deux colonnes et. identifiez-les dans la colonne C.

La méthode de fonctionnement est : entrez la formule =IF(COUNTIF(A:A,B2)=0,"",B2) dans la cellule C2, puis déroulez vers le bas pour terminer la comparaison des deux colonnes de données Excel. Veuillez voir la démo ci-dessous !

La fonction COUNTIF est une fonction qui compte les cellules d'une plage spécifiée qui correspondent à des critères spécifiés.

Testez-vous : Si la valeur numérique comparée ci-dessus dépasse 15 chiffres, par exemple, la comparaison est un numéro d'identification, la formule ci-dessus peut-elle toujours être utilisée ? Si la formule ci-dessus ne peut pas être utilisée, que diriez-vous de la remplacer par la formule suivante ?

🎜 =IF(COUNTIF(A: A,B2&"*")=0," ",B2)🎜🎜ou🎜🎜 =IF(SUMPRODUCT(1*(A:A =B2)),B2,"")🎜🎜 Si vous ne connaissez pas la réponse, n'hésitez pas à regarder le tutoriel "Cousin dont le numéro de carte est étrangement réduit Injustement accusé et puni - Excel, il s'avère que vous avez des doublons de vrai et de faux ! 》. 🎜

🎜3) Fonction IF+VLOOKUP pour rechercher les valeurs en double 🎜

🎜 Comme le montre le tableau ci-dessous, il existe deux ensembles de numéros d'employé. Je ne sais pas lesquels sont communs aux groupes A et B. Nous pouvons également utiliser la formule de la fonction if+VLOOKUP pour compléter la comparaison. 🎜🎜 🎜🎜 Entrez la formule dans la cellule C2 : =IF(ISNA(VLOOKUP(A2,$B$2:$B$25,1,))," ",A2), puis déroulez vers le bas pour copier le formule, Vous pouvez trouver des valeurs en double dans deux colonnes de données dans Excel. 🎜🎜🎜🎜🎜🎜Analyse de formule : 🎜🎜🎜 La fonction ISNA est utilisée pour déterminer si la valeur est une valeur d'erreur #N/A (c'est-à-dire que la valeur n'existe pas). il renvoie FAUX. 🎜🎜 Dans la formule, vous devez ajouter le signe $ avant les données dans la zone de recherche pour corriger la zone de recherche. Sinon, la zone de recherche changera également lorsque la liste déroulante sera remplie, ce qui affectera les résultats de la recherche et de la comparaison. 🎜🎜🎜Extension d'application : trouvez les différences avec Vlookup🎜🎜

Cette formule peut être légèrement ajustée pour trouver des valeurs différentes, ou des valeurs manquantes ou des valeurs erronées (pas une comparaison stricte, sans prêter attention à la position ou à l'ordre). Par exemple, le groupe B ci-dessus est constitué de données standard. Pour connaître les valeurs du groupe A qui sont différentes du groupe B, la formule peut être écrite comme suit :

=IF(ISNA(VLOOKUP(A2,$B:$B,1,)), A2, " ")

Partie 2 : Comparaison des données croisées

---

1. Comparez strictement les deux. Les données de chaque tableau sont-elles les mêmes ? Lorsque deux tableaux ayant exactement le même format sont comparés pour trouver les différences, vous pouvez utiliser la méthode ci-dessous.
1) Méthode de formatage conditionnel pour comparer les différences entre deux tableaux

Prenons maintenant les deux tableaux suivants comme exemple pour comparer et mettre en évidence quelles valeurs sont différentes.

Tout d'abord, sélectionnez un tableau, créez une nouvelle règle et sélectionnez "Utiliser des formules pour déterminer les cellules à formater", puis entrez =A9A1 et jugez les cellules correspondantes pour déterminer si elles sont égales. Veuillez voir la démo ci-dessous !

Rappel chaleureux :

Si vous souhaitez effacer le formatage conditionnel, sélectionnez d'abord la plage de cellules pour effacer le format, puis exécutez "Démarrer" - "Formatage conditionnel" - "Effacer les règles" - "Effacer la sélection Cellules" "Règles pour les cellules" (ou effacez les règles pour l'ensemble de la feuille de calcul).

2) Comparez les différences entre les deux tableaux en utilisant la méthode spéciale de collage (cette méthode ne convient que pour la comparaison de nombres)

Comme le montre la figure ci-dessous, les deux tableaux ont le même format et le même ordre de nom . Il est nécessaire de trouver rapidement les différences de données entre les deux tableaux.

Copiez l'une des plages numériques, puis appuyez sur la touche de raccourci Ctrl+Alt+V pour coller sélectivement, réglez-la sur l'opération "soustraction", cliquez sur "OK", la partie non-0 est la différence. Veuillez voir la démo ci-dessous !

Cette méthode ne convient que pour localiser rapidement des données différentielles, d'un simple coup d'œil, car elle détruira le tableau de données d'origine.

3) La fonction SI compare les différences entre les deux tableaux

Comme le montre la figure ci-dessous, le tableau a et le tableau b sont des tableaux avec exactement le même format. Il est maintenant nécessaire de vérifier si les valeurs​​ dans les deux tableaux sont tout à fait cohérents et les différences doivent être affichées visuellement.

La méthode de fonctionnement consiste à créer une nouvelle feuille de calcul vierge et à saisir la formule =IF(Table a!A1 Table b!A1, "Table a:"& Table a!A1&" vs Table b:"& dans la cellule A1 Tableau b!A1,""), puis copiez la formule de remplissage dans la plage. Veuillez voir la démo ci-dessous !

2. Trouvez la différence entre les données des deux tableaux par condition

1) Trouvez la différence entre les données des deux tableaux en utilisant une seule condition

Par exemple, ce qui suit est un tableau de scores résumé par deux personnes, le format du tableau est le même, mais l'ordre des noms est différent. Vous devez maintenant comparer les deux tableaux pour vérifier si les résultats récapitulatifs sont corrects.

Ce type de vérification des données est une vérification à condition unique. Parce qu'il est compilé par différentes personnes, en plus de vérifier les scores par nom, il est également nécessaire de marquer ceux dont les noms ne correspondent pas. Nous faisons cela en utilisant un formatage conditionnel.

Vous devez créer deux formats conditionnels.

Le premier format : recherchez la différence de nom

(1) Sélectionnez les données de la colonne de nom de la deuxième table, sélectionnez "Nouvelle règle" dans "Mise en forme conditionnelle" et sélectionnez "Utiliser des formules pour déterminer les exigences" dans la fenêtre contextuelle. -up boîte de dialogue "Formater les cellules", puis entrez la formule =COUNTIF($A$2:$A$10,A14)=0

(2) Cliquez sur le bouton Format et sélectionnez une couleur de remplissage.

Après confirmation, nous finaliserons le premier paramétrage du format.

Le deuxième format : Trouver la différence des scores pour un même nom.

(1) Sélectionnez toutes les cellules de score dans le deuxième tableau, créez une nouvelle règle et utilisez la formule pour déterminer la règle. La formule saisie est =. =RECHERCHEV($A14,$A$1:$I$10,COLUMN(B1),0)-B14

(2) Cliquez sur le bouton de format et sélectionnez une couleur de remplissage.

Complétez la vérification du score après confirmation. Les résultats globaux de la vérification sont les suivants :

Orange indique que le nom « Liu Xiaoguang » ne correspond pas à un autre tableau, et le nom peut être mal écrit en bleu-vert indique le score chinois de Yang Wenwen, le score anglais de He Congliang ; et Le score chinois de Hou Manman ne correspond pas, et il peut y avoir des erreurs.

2) Trouvez la différence de données entre deux tableaux en utilisant plusieurs conditions

Comme le montre la figure ci-dessous, il est nécessaire de vérifier la différence de quantité du même produit dans le même entrepôt dans les deux tableaux, et les résultats sont affichés dans la colonne D. De quelle manière cela peut-il être réalisé ? Quel mal de tête !

Entrez la formule suivante dans la cellule D15 :

=SUMPRODUCT(($A:$A=A15)*($B:$B=B15)*$C:$C)-C15

Déroulez ensuite vers le bas pour terminer la comparaison des valeurs. S'il vous plaît, voyez, s'il vous plaît! !

Ce qui précède est le partage d'aujourd'hui, pratiquons ensemble

Recommandations d'apprentissage associées : Tutoriel Excel

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!