Comment faire un filtrage des données et suppression des doublons dans Microsoft Excel

Joolas est une petite entreprise qui fabrique du maquillage. Ses responsables suivent chaque transaction de vente dans une feuille de calcul Microsoft Excel 2013. Parfois, ils veulent extraire ou filtrer un sous-ensemble de leurs données de vente. Par exemple, ils peuvent vouloir identifier les transactions de vente qui répondent aux questions suivantes:

■■ Comment puis-je identifier toutes les transactions dans lesquelles Jen a vendu du rouge à lèvres dans la région Est?

■■ Comment puis-je identifier toutes les transactions dans lesquelles Cici ou Colleen ont vendu du rouge à lèvres ou du mascara dans la région Est ou Sud?

■■ Comment puis-je copier toutes les transactions dans lesquelles Cici ou Colleen ont vendu du rouge à lèvres ou du mascara en Orient ou

Région Sud vers une autre feuille de calcul?

■■ Comment puis-je effacer les filtres d’une colonne ou d’une base de données?

■■ Comment puis-je identifier toutes les transactions impliquant des ventes supérieures à 280 $ et supérieures à 90 unités?

■■ Comment identifier toutes les ventes réalisées en 2005 ou 2006?

■■ Comment puis-je identifier toutes les transactions au cours des trois derniers mois de 2005 ou des trois premiers mois de 2006?

■■ Comment puis-je identifier toutes les transactions pour lesquelles le prénom du vendeur commence par C?

■■ Comment puis-je identifier toutes les transactions pour lesquelles la cellule contenant le nom du produit est colorée en rouge?

■■ Comment puis-je identifier toutes les transactions dans les 30 principales valeurs de revenus dans lesquelles Hallagan ou Jen était le vendeur?

■■ Comment puis-je obtenir facilement une liste complète des vendeurs?

■■ Comment puis-je afficher toutes les combinaisons de vendeurs, de produits et d’emplacements qui se produisent dans la base de données?

■■ Si mes données changent, comment puis-je réappliquer le même filtre?

■■ Comment puis-je extraire toutes les transactions de la fondation au cours des six premiers mois de 2005 pour lesquelles Emilee ou Jen était le vendeur et le prix unitaire moyen était supérieur à 3,20 $?

Excel  dispose de fonctionnalités de filtrage qui facilitent l’identification de tout sous-ensemble de données. Excel facilite également la suppression d’enregistrements en double d’une liste. Vous trouverez le travail de ce chapitre dans le fichier Makeupfilter.xlsx. Pour les 1 891 transactions de vente répertoriées dans ce fichier (la figure 1 montre un sous-ensemble des données), vous disposez des informations suivantes:

■■ Numéro de transaction

■■ Nom du vendeur

■■ Date de la transaction

■■ Produit vendu

■■ Unités vendues

■■ Montant en dollars de la transaction

■■ Lieu de transaction

FIGURE 1 Voici les données de vente de maquillage.

 

Dans ce qui suit, toute plage rectangulaire de cellules avec des en-têtes dans la première ligne de chaque colonne est appelée base de données. Chaque colonne (C à I) de votre base de données (plage de cellules C4: I1894) est appelée un champ. Chaque ligne de la base de données contenant des données est appelée un enregistrement. (Ainsi, les enregistrements de votre base de données sont contenus dans la plage de cellules C4: I1894.) La première ligne de chaque champ doit contenir un nom de champ.

Par exemple, le nom du champ dans la colonne F est Produit. En utilisant le filtre automatique Excel, vous pouvez interroger la base de données à l’aide de critères ET pour identifier un sous-ensemble d’enregistrements. Cela signifie que vous pouvez utiliser des requêtes du formulaire «Rechercher tous les enregistrements dans lesquels le champ 1 remplit certaines conditions, le champ 2 remplit certaines conditions et le champ 3 remplit certaines conditions». Les exemples de ce chapitre illustrent les capacités du filtre automatique Excel.

 

 

Comment puis-je identifier toutes les transactions dans lesquelles Jen a vendu du rouge à lèvres dans la région Est?

Pour commencer, placez votre curseur n’importe où dans la base de données et sélectionnez Filtrer dans le groupe Trier et filtrer sous l’onglet Données du ruban. Comme le montre la figure 2, chaque colonne de la base de données a désormais une flèche dans la ligne d’en-tête (voir la feuille de calcul des données).

FIGURE 2 :Cette figure montre les flèches de titre du filtre automatique.

Après avoir cliqué sur la flèche de la colonne Nom, vous voyez les choix indiqués dans la figure 49-3. Vous pouvez choisir des filtres de texte, qui vous permettent de filtrer en fonction des caractéristiques du nom de la personne (plus à ce sujet plus tard). Pour l’instant, vous voulez simplement travailler avec des données pour Jen, désactivez d’abord la case à cocher Sélectionner tout, cochez la case pour Jen, puis cliquez sur OK. Vous ne voyez maintenant que les enregistrements pour lesquels Jen était Le vendeur. Ensuite, dans la colonne Produit, cochez la case rouge à lèvres. Dans la colonne Emplacement, cochez la case Est. Vous ne voyez maintenant que les transactions pour lesquelles Jen a vendu du rouge à lèvres dans la région Est. (Voir la figure 49-4 et la feuille de calcul du rouge à lèvres Jen Est.) Notez que la flèche est devenue un entonnoir dans les colonnes dans lesquelles vous définissez les critères de filtrage.

FIGURE 3 Ce sont les choix pour filtrer la colonne Nom.

FIGURE 4 Jen vend du rouge à lèvres dans la région Est.

Comment puis-je identifier toutes les transactions dans lesquelles Cici ou Colleen ont vendu du rouge à lèvres ou du mascara dans la région Est ou Sud?

Vous sélectionnez Cici et Colleen dans la liste de la colonne Nom, le rouge à lèvres et le mascara dans la liste de la colonne Produit et l’Est et le Sud dans la colonne Emplacement    liste. Les enregistrements répondant à ces critères de filtrage sont illustrés à la figure 5. (Voir la feuille de travail Cici Colleen Lipstick And Masc.)

FIGURE 5 Voici les transactions dans lesquelles Cici ou Colleen ont vendu du rouge à lèvres ou du mascara dans la région East ou South.

Comment puis-je copier toutes les transactions dans lesquelles Cici ou Colleen ont vendu du rouge à lèvres ou du mascara en Orient ou Région Sud vers une autre feuille de calcul?

L’astuce consiste d’abord à appuyer sur F5, à cliquer sur Spécial, puis à sélectionner Cellules visibles uniquement. Désormais, lorsque vous copiez vos données, Excel inclut uniquement les lignes visibles (dans ce cas, les lignes sélectionnées par les critères de filtrage). Sélectionnez toutes les cellules filtrées dans la feuille de calcul (Cici, Colleen, rouge à lèvres et mascara) et collez-les dans une feuille de calcul vierge. Vous pouvez créer une feuille de calcul vierge dans votre classeur en cliquant avec le bouton droit sur un onglet de feuille de calcul, en cliquant sur Insérer, en sélectionnant Feuille de calcul, puis en cliquant sur OK. La feuille de calcul Copies visibles des cellules contient les enregistrements où Cici ou Colleen ont vendu du rouge à lèvres ou du mascara en Orient.

Comment effacer les filtres d’une colonne ou d’une base de données?

Cliquez sur Filtre dans l’onglet Données pour supprimer tous les filtres. Cliquez sur l’entonnoir pour n’importe quelle colonne pour laquelle vous avez créé un filtre affiche une option pour effacer le filtre de cette colonne.

Comment puis-je identifier toutes les transactions impliquant des ventes supérieures à 280 $ et supérieures à 90 unités?

Après avoir cliqué sur Filtrer dans l’onglet Données, cliquez d’abord sur la flèche de la colonne Unités pour afficher les options illustrées à la figure 6.

FIGURE 6 Cette figure montre les options de filtrage pour une colonne numérique.

Vous pouvez sélectionner n’importe quel sous-ensemble de valeurs d’unité numérique (par exemple, toutes les transactions pour lesquelles les ventes étaient de –10 ou –8 unités). Les filtres numériques sont choisis ici pour afficher les choix de la figure 7.

FIGURE 7 Voici les options des filtres numériques.

La plupart des options que vous voyez sur la figure sont explicites. Ici, vous choisissez Est supérieur à puis remplissez la boîte de dialogue comme le montre la figure 8.

FIGURE 8 Sélectionnez tous les enregistrements dont les unités vendues sont supérieures à 90.

Ensuite, dans la colonne Dollars, cliquez pour inclure uniquement les enregistrements dont le montant est supérieur à 280 $. Vous obtenez les enregistrements illustrés à la figure 9. (Voir la feuille de travail Ventes> 90 unités dollars> 280 $.) Notez que tous les enregistrements sélectionnés ont à la fois des unités supérieures à 90 et des dollars supérieurs à 280 $.

FIGURE 9 Ceci montre les transactions où plus de 90 unités ont été vendues pour un total de plus de 280 $.

Comment identifier toutes les ventes réalisées en 2005 ou 2006?

Après avoir cliqué sur Filtrer dans le ruban, cliquez sur la flèche de la colonne Date pour afficher les choix dans Figure 10.

FIGURE  10 Ce sont des options de filtrage pour la colonne Date.

Après avoir sélectionné 2005 et 2006, vous ne voyez que les enregistrements impliquant des ventes en 2005 ou 2006, comme le montre la figure 11. (Voir la feuille de travail Ventes en 2005 et 2006.)

FIGURE 11 Cette figure montre les ventes en 2005 et 2006.

Notez que vous pourriez également avoir sélectionné Filtres de date pour afficher les options illustrées à la figure 12. La plupart de ces options sont également explicites. Le filtre personnalisé vous permet de sélectionner n’importe quelle plage des dates comme critères de filtrage.

FIGURE 12 Voici les options de filtrage par date.

 

Comment identifier toutes les transactions des trois derniers mois de 2005 ou des trois premiers mois de 2006?

Après avoir cliqué sur la flèche de la colonne Date, vous voyez la liste des années illustrée à la figure 10. Cliquez sur le signe + à gauche de l’année pour afficher une liste de mois. Vous pouvez sélectionner d’octobre à décembre 2005 puis de janvier à mars 2006 pour afficher toutes les ventes au cours de ces mois. (Voir Figure 13 et la feuille de calcul Filtrer par mois.)

FIGURE 13 Cette figure montre toutes les ventes d’octobre 2005 à mars 2006.

Comment puis-je identifier toutes les transactions pour lesquelles le prénom du vendeur commence par C?

Cliquez sur la flèche de la colonne Nom et choisissez Filtres de texte. Sélectionnez Commence par et, dans la boîte de dialogue illustrée à la figure 14, choisissez Commence par C.

FIGURE 14 Il s’agit de la boîte de dialogue Filtre automatique personnalisé configurée pour sélectionner tous les enregistrements pour lesquels le nom du vendeur commence par C.

Comment identifier toutes les transactions pour lesquelles la cellule contenant le nom du produit est colorée en rouge?

Cliquez sur la flèche de la colonne Produit, puis choisissez Filtrer par couleur. Vous pouvez maintenant sélectionner la couleur à utiliser comme filtre. Comme le montre la figure 49-15, seules les lignes pour lesquelles le produit est coloré en rouge sont incluses ici. La figure 16 montre les enregistrements résultants (voir la feuille de calcul Filtrer par couleur).

FIGURE 15 Il s’agit de la boîte de dialogue de filtrage par couleur de cellule.

FIGURE 16 Voici tous les enregistrements où la couleur des cellules du produit est rouge.

Comment puis-je identifier toutes les transactions dans le top 30 de toutes les valeurs de revenus pour lesquelles Hallagan ou Jen était la vendeuse?

Après avoir cliqué sur Filtrer dans le ruban, vous cliquez sur la flèche de la colonne Nom, puis cochez les cases pour Hallagan et Jen. Cliquez sur la flèche de la colonne Dollar et choisissez Filtres numériques. Dans la liste Filtres numériques, sélectionnez Top 10, puis remplissez la boîte de dialogue comme indiqué dans Figure 17. Vous avez maintenant filtré tous les enregistrements dans les 30 premières valeurs de revenus dont Jen ou Hallagan est le vendeur. Voir les résultats dans la figure 18 (et la feuille de calcul 30 $ avec Hallagan ou Jen). Notez que seulement cinq des 30 meilleures ventes avaient Hallagan ou Jen comme vendeur. Vous pouvez également sélectionner Top 5 pour cent, Bottom 20 pourcent, et ainsi de suite pour toute colonne numérique.

FIGURE 17 Il s’agit de la boîte de dialogue dans laquelle sélectionner les 30 premiers enregistrements par valeur monétaire.

FIGURE 18 Ce sont les 30 premiers enregistrements en valeur monétaire pour lesquels Jen ou Hallagan était le vendeur.

Comment obtenir facilement une liste complète des vendeurs?

Ici, vous voulez une liste de tous les vendeurs sans que le nom de personne ne soit répété. Commencez par sélectionner Supprimer les doublons dans l’onglet Données du ruban pour afficher la boîte de dialogue Supprimer les doublons illustrée à la figure 19. Après avoir choisi Tout désélectionner, activez uniquement la case à cocher Nom, puis cliquez sur OK. Ce paramètre filtre les données uniquement pour le premier enregistrement impliquant le nom de chaque vendeur. Consultez les résultats de la figure 20 et de la feuille de calcul Suppression des doublons de nom.

        Important Étant donné que la sélection de Supprimer les doublons supprime certaines de vos données, je vous recommande de faire une copie de vos données avant de sélectionner Supprimer les doublons.

FIGURE 19 Il s’agit de la boîte de dialogue Supprimer les doublons avec la colonne Nom sélectionnée.

FIGURE 20 Cette figure montre la liste des noms des vendeurs.

Comment puis-je afficher toutes les combinaisons de vendeur, produit et emplacement qui se produisent dans la base de données ?

Là encore, vous devez cliquer sur Supprimer les doublons dans l’onglet Données pour commencer. Remplissez le Supprimer la Boîte de dialogue Doubles, comme illustré à la figure 21.

La figure 22 répertorie le premier enregistrement pour chaque combinaison de personne, de produit et d’emplacement se produisant dans la base de données. (Voir la feuille de calcul Emplacement unique du produit.) Cent quatre-vingt combinaisons uniques se sont produites. Notez que le vingtième record a été omis car il incluait Zaret vendant du brillant à lèvres dans le Midwest, et le cinquième record avait déjà repris cette combinaison.

FIGURE 21 Trouvez des combinaisons uniques de vendeurs, de produits et d’emplacements.

FIGURE 22 Il s’agit d’une liste de combinaisons uniques de vendeurs, de produits et d’emplacements.

 

Si mes données changent, comment puis-je réappliquer le même filtre?

Cliquez avec le bouton droit sur une cellule de vos résultats filtrés, pointez sur Filtrer, puis cliquez sur Réappliquer. Toute modification de votre liste des données sont reflétées dans les données filtrées.

Comment puis-je extraire toutes les transactions de la fondation au cours des six premiers mois de 2005 pour lesquelles Emilee ou Jen était le vendeur et le prix unitaire moyen était supérieur à 3,20 $?

La fonction AutoFilter (même avec Personnalisé) est limitée aux requêtes ET sur les colonnes. Cela signifie, par exemple, que vous ne pouvez pas trouver toutes les transactions pour les ventes de rouge à lèvres par Jen en 2005 ou les ventes de fondations par Zaret en 2004. Pour effectuer des requêtes plus complexes telles que celle-ci, vous devez utiliser la fonction de filtrage avancé. Pour utiliser le filtre avancé, vous définissez une plage de critères qui spécifie les enregistrements que vous souhaitez extraire. (Ce processus est décrit en détail  dans l’ article 48, «Synthèse des données avec les fonctions statistiques de la base de données».) Après avoir spécifié la plage de critères, vous dites à Excel si vous souhaitez enregistrements extraits vers l’emplacement actuel ou vers un emplacement différent. Pour identifier toutes les opérations de fondation au cours des six premiers mois de 2005 pour lesquelles Emilee ou Jen était le vendeur et pour lesquelles le prix unitaire moyen était supérieur à 3,20 $, vous pouvez utiliser la plage de critères indiquée dans la plage O4: S6 dans Figure23. (Voir la feuille de calcul Jen + Emilee dans le fichier .)

FIGURE 23 Configurez une plage de critères à utiliser avec un filtre avancé.

Dans les cellules R5 et R6, entrez la formule = (L5 / K5)> 3.2. Rappelons du chapitre 48 que cette formule crée des critères calculés qui marquent chaque ligne dans laquelle le prix unitaire est supérieur à 3,20 $. N’oubliez pas non plus que votre en-tête pour un critère calculé ne doit pas être un nom de champ. Le prix a été utilisé ici comme en-tête de champ. Les critères dans O5: S5 signalent tous les enregistrements dans lesquels Jen est le vendeur, la date est comprise entre 1/1/2005 et 6/30/2005, le produit vendu est un fond de teint et le prix unitaire est supérieur à 3,20 $. Les critères de O6: S6 signalent tous les enregistrements dans lesquels Emilee est le vendeur, la date est comprise entre le 1/1/2005 et le 30/06/2005, le produit vendu est le fondement et le prix unitaire est supérieur à 3,20 $. La plage de critères O4: S6 marque exactement les enregistrements souhaités. N’oubliez pas que les critères des différentes lignes sont joints par OR.

Vous pouvez désormais sélectionner n’importe quelle cellule dans la plage de la base de données et sélectionner Avancé dans la fonction Trier et filtrer dans l’onglet Données. Remplissez la boîte de dialogue comme illustré à la figure 24.

FIGURE 24 Voici les paramètres de la boîte de dialogue Filtre avancé.

Avec ces paramètres, vous dites à Excel d’extraire tous les enregistrements de la base de données (la plage de cellules G4: M1895) qui répondent aux critères spécifiés dans O4: S6. Ces enregistrements doivent être copiés dans une plage dont le coin supérieur gauche est la cellule O14. Les enregistrements extraits sont indiqués dans la cellule O14: U18 a sonné  e. Seuls les quatre enregistrements illustrés à la figure 25 répondent aux critères définis. (Voir à nouveau la feuille de calcul Jen + Emilee.)

FIGURE 25 Voici les résultats du filtre avancé.

Si vous sélectionnez Uniquement les enregistrements uniques dans la boîte de dialogue Filtre avancé, aucun enregistrement en double n’est renvoyé. Par exemple, si Jen avait une autre transaction de fondation dans la région Est le 19/03/2005 pour une unité pour 4,88 $, une seule de ces transactions serait extraite.

S’abonner
Notifier de
0 Commentaires
Inline Feedbacks
Voir tous les commentaires

Initiation à Excel

Fonctions Excel

Excel VBA

Macros VBA Utiles

Plus d'outils

Sur Facebook

Sur YouTube

0
Nous aimerions avoir votre avis, veuillez laisser un commentaire.x