Mise en pratique

Dans l'exercice à suivre, nous allons étudier des problèmatiques fréquentes : éliminer des doublons, fusionner des listes, calculer à partir de données distribuées. Tout en proposant des méthodologies, cet exercice va nous amener à réviser plusieurs notions (fonction SI, filtres, RECHERCHEV).

Téléchargez le classeur exempleFusionListes.ods ou exempleFusionListes.xls. Ce fichier contient le stock de deux magasins fictifs. L'exercice consiste à remplir la feuille « Stock global » recensant l'inventaire total des deux magasins.

Observez que tous les produits ne sont pas recensés dans les 2 magasins. Certains produits ne sont recensés que dans un magasin. Nous avons donc deux problématiques à gérer :

• Récupérez la liste des produits apparaissant dans au moins un des deux magasins.
• Récupérez, pour chaque produit, la quantité totale d'articles.

La liste des produits

Il faut tout d'abord remplir la colonne A de la feuille « Stock global » de manière à y obtenir la liste de tous les identifiants contenus dans les colonnes A des feuilles « Magasin_1 » et « Magasin_2 ». Mais attention ! chaque identifiant ne doit y apparaître qu'une fois.

Dans ce qui suit, nous allons déterminer les produits du magasin 1 qui n'apparaissent pas dans le magasin 2. La liste totale des produits sera constituée par ces produits plus ceux du magains 2.

1. En colonne C de la feuille « Magasin_1 », utilisez la fonction RECHERCHEV pour afficher, pour chaque identifiant de produit, cet identifiant s'il apparaît dans la feuille « Magasin_2 » ou une erreur (#N/A avec Microsoft Excel, #N/D avec Calc).
2. à l'aide d'un filtre, sélectionnez les éléments de de la feuille « Magasin_1 » qui n'apparaissent pas dans la feuille « Magasin_2 »).
3. Mettre dans la colonne A de la feuille « Stock global » la liste des identifiants des articles du magasin 2 suivie de la liste des produits sélectionnés précédemment.

Remarques :

• Vous devriez avoir 3201 identifiants de produit.
• Dans l'approche précédente, il est possible d'utiliser la fonction NB.SI (que nous verrons au TD prochain) à la place de la fonction RECHERCHEV). Cette fonction permet de déterminer le nombre d'apparitions d'une valeur dans une plage de cellule. Si la valeur est absente, le résultat est le nombre 0.

La quantité totale de chaque produit

Maintenant que nous avons la liste des identifiants des produits en colonne A de la feuille « Stock global », il ne reste plus qu'à calculer pour chacun la quantité totale d'articles en faisant la somme des quantités de chaque magasin. Réalisez cela en utilisant la fonction RECHERCHEV. Pensez à vous assurer que, lors de la recopie, la plage de cellules reste correcte.

Remarques.

• La fonction RECHERCHEV sera à utiliser 2 fois : une fois pour aller chercher la valeur du stock dans le magasin 1 (0 si erreur parce-que le produit n'est pas recensé dans le magasin) et une fois pour aller chercher la valeur du stock dans le magasin 2).
• Pour vérifier que vous ne vous êtes pas trompé·e, vous pouvez calculer le nombre total de produits. Il vaut 222 497.

---

Calculer la liste des produits par suppression des doublons

Dans la technique utilisée ci-avant pour calculer la liste des numéros de produits, nous avons cherché à déterminer les produits d'une première liste qui n'apparaissaient pas dans une deuxième liste et nous avons obtenu la liste complète en mettant bout à bout la deuxième liste et les éléments n'y apparaissant pas de la première.

Une autre approche consiste à mettre bout à bout les 2 listes puis à supprimer les doublons. Nous étudions deux approches pour cela ci-après.

Technique 1 : suppression des doublons à l'aide de filtre

Dans Calc et Excel, il est possible de supprimer les doublons grâce aux filtres.

Rappelons qu'un filtre modifie les informations affichées mais ne suppriment pas les informations non affichées. Ainsi pour pouvoir récupérer le « Stock global », il faut utiliser une feuille temporaire.

1. Créez-la.
2. Copiez-y la colonne A de la feuille Magasin 1 puis, en dessous, les valeurs de la colonne A de la feuille Magasin 2.
3. Appliquez le filtre en utilisant les informations de l'encadré suivant.
4. Copiez les valeurs s'affichant après filtrage dans la feuille temporaire et collez-les dans la feuille « Stock global ».

Remarques.

• Le magasin 1 contient 2341 lignes de produits et le magasin 2 en contient 1110. Au total, il y a donc 3450 lignes de produits. Après suppression des doublons, il doit rester 3201 lignes.
• Dans les dernières versions de Microsoft Excel, il existe un bouton Supprimer des doublons qui permet de supprimer les doublons sur place.

Technique 2 : suppression des doublons sans filtre

Nous proposons ci-après une approche plus algorithmique pour supprimer des doublons. On préférera en pratique la technique 1. Toutefois cette technique permet de réviser la fonction Si et les tris. Elle pourrait aussi s'avérer intéressante dans certains contextes (cas d'un tableur autres que ceux étudiés ne prévoyant pas la suppression des doublons ; automatisation avec macros) et peut aussi suggérer des traitements dans d'autres situations.

Comme le stock global a déjà été calcul avec la technique 1, vous pouvez appliquez ce qui suit dans une nouvelle feuille.

1. Recopiez le contenu des colonnes A des feuilles « Magasin_1 » et « Magasin_2 » en colonne C de la feuille « Stock global ». Certains identifiants de produits apparaissent plusieurs fois, nous allons supprimer les doublons.
2. Triez la colonne C. Une fois cela fait, les doublons apparaissent sur des lignes contigües.
3. En colonne D, pour chaque cellule Dn (n désignant le numéro de ligne), ajoutez une formule dont le résultat vaut la chaîne vide ("") si les valeurs en Cn et C(n+1) sont égales, et vaut Cn sinon. Une fois cela fait, chaque identifiant n'apparaît qu'une fois en colonne D.
4. Effectuez un collage spécial des valeurs de la colonne D dans la colonne A.
5. Triez la colonne A pour avoir des valeurs contiguës.
6. Effacez le contenu des colonnes C et D (ou conservez-les pour vous pour mémoire).