Comparer des listes (Partie 4)
- 13. SIERREUR(…) ou SI(ESTERREUR(…))
- 14. Comparer avec les fonctions EQUIV ou RECHERCHEV
- 15. Supprimer les doublons
- 16. Attention aux formats (Texte par rapport aux nombres) – Utilisation de copier / collage spécial addition
Nous avons vu dans l’article précédent comment calculer le nombre de valeurs différentes, comment utiliser les tableaux d’Excel qui peuvent servir pour comparer.Quels types de comparaisons souhaitons-nous mettre en œuvre ?
- Lignes en plus ou en moins
- Valeurs de cellules différentes
Nécessité de comparer des valeurs ayant le même format (dates au format français ou anglais, formats texte ou nombre).
Un autre moyen de comparer est de le faire à l’œil nu en mettant deux feuilles côte à côte (voir article précédent)
13. SIERREUR(…) ou SI(ESTERREUR(…))
En anglais: IFERROR and IF
La gestion des erreurs est très utile notamment lorsque nous souhaitons mettre en avant des enregistrements que nous ne retrouvons pas dans une autre liste.
Par exemple, nous avons la liste suivante :
La formule utilisée dans la colonne D est
=RECHERCHEV([@Prénom];T.Liste2;1;FAUX)
Nous pouvons voir que lorsque nous ne trouvons pas, nous avons la valeur #N/A. Afin d’éviter cette valeur, nous pourrons utiliser la fonction SIERREUR :
=SIERREUR(RECHERCHEV([@Prénom];T.Liste2;1;FAUX); »Non Trouvé »)
En anglais: VLOOKUP (RECHERCHEV)
Et si nous ajoutons une mise en forme conditionnelle sur la valeur « Non Trouvé », nous pouvons avoir le résultat suivant :
Les fonctions imbriquées SI(ESTERREUR()) permettent de faire la même manipulation si ce n’est qu’il faut définir la valeur si le retour est VRAI :
=SI(ESTERREUR(RECHERCHEV([@Prénom];T.Liste2;1;FAUX)); »Non Trouvé »;[@Prénom])
14. Comparer avec les fonctions EQUIV ou RECHERCHEV
Dans l’exemple ci-dessus, nous avons utilisé la fonction RECHERCHEV afin de savoir si un enregistrement (d’après la clé) existe dans la liste 2.
Une autre fonction permet d’avoir un résultat similaire si ce n’est qu’elle renvoie le numéro de ligne du tableau :
En anglais: MATCH (EQUIV)
=SIERREUR(EQUIV([@Prénom];T.Liste2[Prénom];0); »Non Trouvé »)
Dans cet exemple, nous pouvons trouver les enregistrements qui n’existent pas dans la liste 2. Nous pouvons faire la même chose depuis la liste 2.
Si la clé est sur plusieurs colonnes, vous pouvez voir l’article sur l’utilisation de RECHERCHEV sur plusieurs colonnes.
15. Supprimer les doublons
Pour supprimer les doublons, il suffit de sélectionner la plage choisie et de cliquer sur Supprimer les doublons dans Ruban -> Outils de données -> Supprimer les doublons.
Néanmoins, vous souhaitez parfois vérifier si une valeur est en double ou en triple sans pour autant la supprimer.
Voici comment faire :
Dans le tableau, nous allons ajouter une colonne (UNIQUE ?) dans laquelle nous allons appliquer la formule suivante :
=SI(NB.SI([Prénom];[@Prénom])>1; »Multiple »; »Unique »)
En anglais: COUNTIF (NB.SI)
Nous comptons le nombre de fois où on trouve le prénom de la ligne (@Prénom) dans la colonne Prénom. Si la valeur est supérieure à 1, alors, nous avons une valeur qui n’est pas unique et donc multiple.
Il est également parfois utile d’avoir le nombre d’occurrences pour chaque ligne. Nous utiliserons donc la formule :
= NB.SI([Prénom];[@Prénom])
Dans les exemples ci-dessus, nous avons une clé qui est sur une seule cellule. La clé est ici le prénom.
Mais lors des extractions SAP, nous avons régulièrement affaire à une clé multi-colonne. Si nous reprenons notre exemple, considérons que notre clé d’unicité est Prénom + Catégorie. Pour les lignes 94 et 96, nous constatons donc que les deux lignes ne devraient pas être considérées comme multiple :
En anglais: COUNTIFS (NB.SI.ENS)
=SI(NB.SI.ENS([Prénom];[@Prénom];[Categorie];[@Categorie])>1; »Multiple »; »Unique »)
16. Attention aux formats (Texte par rapport aux nombres) – Utilisation de copier / collage spécial addition
Très souvent, lors d’un export de données depuis SAP, nous pouvons avoir des divergences sur les formats. Par exemple, des nombre au format texte.
Toutefois, il s’avère que ce n’est pas aussi simple car même en changeant le format en texte (en général par défaut à standard), il s’avère que le format reste inchangé et la comparaison avec une autre liste peut être impossible.
Une solution consiste à utiliser Copier / Collage spécial.
Tout d’abord, il vous faut sélectionner la colonne qui devrait être numérique mais qui ne l’est pas. Faites ensuite COPIER.
Aller sur une feuille vierge, et sur la première colonne (il faut qu’elle soit vide), faites COLLAGE SPECIAL
Puis sélectionner ensuite « Addition » comme indiqué ci-dessous:
Cette particularité va additionner les cellules sélectionnées (avec COPIER) avec les cellules cibles où le collage spécial est appliqué (nouvelle feuille, plage vierge).
Par exemple, vous pouvez utiliser cette fonctionnalité pour multiplier toute une plage de cellule par 2 (ou autre) sans pour autant utiliser la moindre formule.
Pour cela, vous écrivez deux dans une cellule, et vous faites COPIER sur cette cellule. Ensuite, vous sélectionnez la plage que vous souhaitez multiplier et vous faites COLLAGE SPECIAL, avec l’option Multiplication. Vous aurez alors le résultat attendu.