Comparer des données

Comparer des données dans Excel s’avère indispensable pour gérer des listes

Cette comparaison peut se faire avec des listes se trouvant sur la même feuille, sur des feuilles différentes d’un même classeur ou de deux classeurs différents.

Pour notre exemple, nous effectuerons la comparaison des deux listes sur la même feuille

Les données

Nous avons donc deux listes ayant chacune le même nombre et les mêmes étiquettes de colonnes. L’une que l’on nommera Data 1 occupe la plage B7:C32 et l’autre que l’on nommera Data 2 sont en J7:K27 (voir image ci-dessous).

Pour nous faciliter la lecture, une mise en forme conditionnelle a été placée en colonne A de chaque liste pour mettre en rouge les références qui ne se trouvent pas dans l’autre liste.

Matcher les données (Show 0)

Le scénario

Pour notre exemple, nous allons comparer si

  • Les références (colonne A de Data 1) sont présentes dans la même colonne de Data 2
  • Les références (colonne A de Data 1) ne sont pas présentes dans la même colonne de Data 2

La formule

La formule que nous allons utiliser pour obtenir le résultat souhaité sera constitué de trois fonctions d’excel qui sont EQUIV (MATCH), ESTNA (ISNA), NON (NOT).

Les fonctions

Pour illustrer nos propos, nous allons découper en trois étapes la formule et l’illustration ci-dessous montre les différentes étapes et une vue partielle des données (quelques lignes)

Matcher les données (Formule)Les étapes décrites ci-dessous sont en colonne E (étape 1), colonne F (étape 2), colonne G (étape 3). Les formules sont en ligne 3, 4 et 5 pour les étapes 1, 2 et 3 et leurs résultats en lignes 7 et suivantes

Etape 1

Nous écrivons la fonction suivante en cellules E7:E32

=EQUIV(B7;$J$7:$J$27;0)

EQUIV

La fonction EQUIV (MATCH pour la version anglaise) renvoie la position dans une colonne ou dans une ligne d’une valeur cherchée.

La valeur renvoyée sera soit la position de la valeur cherchée soit 2 pour la cellule E7 (B7 contient R001 et se trouve en 2ème position dans la plage J7:J27) soit #N/A si la référence n’est pas trouvée comme par exemple en cellule E8. En effet la valeur R002 qui se trouve en cellule B8 n’est pas présente dans la plage J7:27 comme on peut le voir d’ailleurs dans l’image ci-dessus.

Avec cette étape là, nous pourrions déjà avec un filtre simple masquer les lignes qui contiennent la valeur #N/A pour pouvoir visualiser les lignes dont les références de la liste de Data 1 se retrouvent bien en Data 2

Il est évident qu’il serait préférable d’avoir une valeur comme VRAI ou FAUX pour pouvoir e comme nous ne pouvons pas avoir une bonne visions

Etape 2

Nous écrivons la formule suivante en cellules F7:F32

=ESTNA(EQUIV(B7;$J$7:$J$27;0))

Cette formule contient deux fonctions ESTNA et EQUIV (EQUIV étant imbriquée dans la fonction ESTNA)

Ayant déjà expliqué dans l’étape 1 ce que renvoyait la fonction EQUIV, nous allons donc maintenant expliquer ce que renvoie la fonction ESTNA

ESTNA

ESTNA (ISNA pour la version anglaise) comme toutes les fonctions d’excel préfixée EST (pour la version anglaise IS) renvoie la valeur booléenne VRAI ou FAUX (TRUE/FALSE pour la version anglaise). Donc dans notre exemple, si la fonction EQUIV renvoie #N/A, la fonction ESTNA renverra VRAI et dans le cas contraire, renverra FAUX comme nous pouvons le voir dans l’illustration ci-dessous.

Autrement dit, cette deuxième étape nous renvoie VRAI si la référence qui se trouve en 1ère colonne de la liste de Data 1 n’est pas présente dans la 1ère colonne de Data 2.

Etape 3

Comme nous cherchons les références présentes dans les deux listes,  il serait plus logique d’obtenir VRAI. C’est ce que la fonction NON (NOT pour la version anglaise) que nous allons ajouter dans la troisième et dernière étape va faire

NON

La fonction NON renvoie également une valeur booléenne et ne fait que l’inverser NON(VRAI) renvoie FAUX et NON(FAUX) renvoie VRAI. Autrement dit en bon français ce qui n’est pas FAUX est VRAI et ce qui n’est pas FAUX est VRAI. Logique non ?

Voici donc la formule complète placée en cellules G7:G32

=NON(ESTNA(EQUIV(B7;$J$7:$J$27;0)))

Conclusion

  1. Pour savoir si les références de la liste Data 1 sont présentes en liste Data 2 la formule est : =NON(ESTNA(EQUIV(B7;$J$7:$J$27;0)))
  2. Pour savoir si elles ne sont pas présentes : =ESTNA(EQUIV(B7;$J$7:$J$27;0))
  3. Si nous voulons savoir maintenant si les références contenues en liste de Data 2 sont présentes en Data 1 la formule est : =NON(ESTNA(EQUIV(E7;$B$7:$B$32;0)))
  4. Pour savoir si elles ne sont pas présentes : =ESTNA(EQUIV(E7;$B$7:$B$32;0))

C.Q.F.D

Dans un autre billet titré Exporter les données comparées (« Matchées »), je vous montre comment exporter les données répondant aux critères décrits ci-dessus à l’aide des filtres avancés d’excel