Préparez votre certification
Microsoft Office Specialist
Trier et filtrer des tableaux
Dès que les données contenues dans un tableau dépassent une certaine quantité, la lecture et surtout la recherche d'une information deviennent fastidieuses et peuvent durer un grand moment. Excel dispose de deux fonctions très pratiques, susceptibles de vous donner une aide très précieuse et vous faire gagner du temps : le Tri et le Filtrage.
À la fin de ce cours, vous serez capable d'effectuer les manipulations suivantes :
- trier des données
- Modifier l'ordre de tri
- Trier sur plusieurs colonnes
- Filtrer des enregistrements
- Supprimer les doublons
Recevez ce cours au format PDF en envoyant un message ici
Pour mettre en pratique les compétences traitées dans ce cours, je vous invite à télécharger le fichier de travail ici. Le classeur contient une seule feuille de calcul : Ventes2019, qui renferme un assez grand nombre de lignes (plus de 1000 lignes).
Un peu de vocabulaire
Ce chapitre forme un premier pas dans le traitement des bases de données, ce qui nécessite un minimum de vocabulaire.
Base de données : Une base de données dans sa forme la plus simple est une liste de données contenant des lignes et des colonnes. La première ligne renferme les titres des colonnes indiquant la nature des informations de chaque colonne.
Enregistrement : On appelle enregistrement, chacune des lignes de cette liste de données. La première ligne qui affiche les titres des colonnes n'est pas considérée comme enregistrement.
Champ : Chaque titre de colonne forme un champ. Le contenu de la cellule formée par l'intersection d'une ligne et d'une colonne, forme la valeur du champ.
Trier les données
Trier le contenu d'un tableau c'est classer ses enregistrements selon un ou plusieurs critères, dans un ordre croissant ou bien décroissant. Les différentes commandes de tri et de filtrage sont disponibles dans le groupe Trier et filtrer de l'onglet Données.
Le tri simple
Le tri simple permet d'ordonner, dans l'ordre croissant ou bien décroissant, tous les enregistrements d'un tableau en fonction d'un seul critère c'est à dire selon la valeur d'un seul champ.
La méthode la plus simple pour trier les éléments d'un tableau est de cliquer dans une cellule du champ selon lequel on veut faire le tri puis dans le groupe Trier et filtrer, cliquer sur le bouton Trier de A à Z pour un tri croissant ou bien Trier de Z à A pour un tri décroissant.
Dans la capture ci-dessous, on est sur le point d'effectuer un tri croissant du tableau selon le champ Désignation. Ce champ étant de nature Texte, le tri va donc se faire par ordre alphabétique croissant des articles.
Un autre moyen de faire des tris simples consiste à ouvrir le petit bouton flèche situé à droite du titre du champ à utiliser comme critère, et de choisir le tri voulu. La figure suivante permet de faire un tri croissant selon le champ Quantité.
Une fois l'opération de tri effectuée, le petit bouton indique à l'aide d'une flèche le sens du classement, vers le bas pour un tri décroissant et vers le haut pour un tri croissant.
Encore une méthode pour faire. Clic droit sur la colonne à utiliser comme critère de tri et dans le menu contextuel qui apparait, pointer sur Trier puis choisir l'ordre du tri. Ainsi, pour trier les données du tableau par ordre croissant des Prix, on effectue un clic droit sur la colonne Prix puis on choisit l'opération Trier du plus petit au plus grand dans le menu contextuel.
Avant d'effectuer toute opération de tri, il est prudent de toujours avoir une colonne Ordre où sont numérotés les éléments du tableau de 1 jusqu'à n (n étant le nombre d'enregistrements dans le tableau) pour pouvoir retrouver l'ordre initial du tableau.
Travail à faire :
Sur la feuille Vantes2019 du classeur téléchargé, effectuer les tâches suivantes :
- Activer les boutons fléchés (si nécessaire)
- À l'aide de ces boutons, trier les données du tableau par ordre croissant de Quantité
Le tri sur plusieurs critères
Avec le tri simple, on obtient souvent des enregistrements ex-aequo qui occupent le même rang, qu'il faut départager en utilisant un autre critère de tri.
Dans un tri croissant par Date des articles vendus (des ventes les plus anciennes aux ventes les plus récentes), on voit qu'il y a plusieurs articles qui sont vendus à la même date. Dans le but de faciliter la lecture, on peut utiliser, par exemple, l'ordre alphabétique du champ Désignation comme deuxième critère de tri. Les boutons fléchés ne permettant pas des tris multicritères, on va se servir de la fonctionnalité Trier en activant son bouton qui se trouve dans le groupe Trier et filtrer de l'onglet Données et suivre les étapes :
- Dans la boite de dialogue Tri qui s'affiche, on a déjà le premier critère activé Date par ordre croissant (Du plus ancien au plus récent)
- Cliquer sur le bouton Ajouter un niveau pour ajouter le deuxième critère
- Dans la zone Puis par ouvrir la liste et choisir le champ selon lequel on veut effectuer le deuxième niveau de tri. Désignation pour le cas présent
- Dans la troisième zone de la même ligne, choisir l'Ordre du tri Croissant ou bien Décroissant
- Valider
Littéralement, la consigne que Excel va exécuter sera : Trier les ventes du tableau selon leurs Dates de la plus ancienne jusqu'à la plus récente, et dans le cas où plusieurs ventes sont effectuées à la même date, les trier par ordre alphabétique croissant de leurs Désignations.
Résultat de cette opération : des ventes triées par ordre croissant de leurs Dates puis par ordre alphabétique croissant de leurs Désignations.
On peut utiliser plusieurs niveaux de tri, il suffit à chaque fois de cliquer sur le bouton Ajouter un niveau et de renseigner les champs puis valider.
Il est à noter qu'on peut trier les données d'un tableau pas que sur le contenu des cellules, mais aussi sur la couleur des cellules, la couleur de la police ou encore sur les icônes de mise en forme conditionnelle. Pour faire, après avoir cliqué dans le tableau, ouvrir la boite de dialogue Tri comme vu ci-dessus, puis choisir dans la zone Trier sur l'option qui convient et valider.
Cette même boite de dialogue permet de modifier l'ordre du tri d'un critère, en passant par la zone Ordre.
Travail à faire :
Sur la feuille Ventes2019 du classeur téléchargé, trier les données par ordre alphabétique croissant de Désignation puis par ordre décroissant de prix Total.
Filtrer les enregistrements
Filtrer les données d'un tableau veut dire ne laisser afficher que celles qui répondent à un critère (critère simple ou composé). Pour avoir accès aux filtres, il faut les activer en cliquant sur le bouton Filtrer du groupe Trier et filtrer de l'onglet Données, action qui a pour effet de faire apparaitre les petits boutons fléchés dans les en-têtes des colonnes.
On peut choisir les éléments à afficher dans la liste ou bien utiliser un critère qui peut être numérique, chronologique ou textuelle.
Choisir dans la liste les éléments à afficher
En cliquant sur le bouton fléché, on fait apparaitre la liste des éléments de la colonne. On doit alors cocher les items à montrer et décocher ceux qui doivent être masqués
On peut aussi utiliser la zone Rechercher pour taper le nom (ou une partie du nom) des éléments à afficher
En validant ce choix, on va afficher tous les claviers vendus au cours de l'année 2019 (indépendamment de la marque).
Filtre numérique
Le Filtre numérique permet de filtrer les données de nature numérique, en utilisant différents comparatifs (Est égal à, Est supérieur à, etc.)
Le choix de la commande Est égal à ... ouvre la boite de dialogue suivante qui permet de saisir la valeur de comparaison pour le champ Quantité et de valider
Filtre chronologique
Le Filtre chronologique fonctionne de la même façon avec une adaptation à la nature des données Date et Heure.
Pour filtrer selon la date des ventes, on ouvre le bouton fléché de la colonne Date puis pointer sur la ligne Filtres chronologiques et choisir le filtre qui convient.
Le filtre ci-dessus affiche uniquement les ventes effectuées pendant le mois Juillet.
Travail à faire :
Utiliser les boutons fléchés pour afficher uniquement les ventes effectuées pendant le trimestre 2 de l'année 2019.
Filtre textuel
Les filtres textuels s'appliquent aux colonnes dont le contenu est de nature texte. Ils utilisent des commandes qui manipulent les chaines de caractères telles que Commence par, Se termine par, Contient, Ne contient pas etc.
Pour afficher toutes les souris vendues au cours de l'année 2019, on applique sur la colonne Désignation, le filtre textuel Contient... et on saisit le texte Souris puis on valide.
Résultat de l'opération, la liste des ventes, de souris toutes catégories confondues, qui ont été faites l'année 2019.
Note 1 :
On reconnait un tableau filtré par les en-têtes de ses lignes qui se mettent en bleu avec des sauts de numéros, preuve qu'il y a des lignes masquées (lignes ne répondant pas au critère), en plus un petit entonnoir apparait dans le bouton fléché de la colonne selon laquelle le filtre est appliqué.
Note 2 :
On peut appliquer plusieurs filtres imbriqués sur les données d'un tableau ; exemple on applique d'abord un premier filtre sur la colonne Désignation pour n'afficher que les souris, puis un deuxième filtre sur la colonne Quantité pour n'afficher que les ventes de souris supérieures ou égales à 40.
Note 3 :
Pour effacer les filtres et réafficher la totalité du tableau, il suffit de cliquer sur le bouton Effacer du groupe Trier et filtrer sous l'onglet Données.
Filtre personnalisé
Avec les filtres personnalisés on peut appliquer sur une colonne, un critère composés deux sous-critères liés par des liens logiques, ET ou bien OU.
L'opérateur logique ET veut dire que les deux sous-critères doivent être vérifiés en même temps, alors que l'opérateur logique OU veut dire que l'un ou l'autre sous-critère doit être vérifié.
Par exemple on veut afficher uniquement les ventes concernant les souris et les claviers ; la colonne Désignation doit donc contenir ou bien le mot souris, ou bien le mot clavier avec l'opérateur logique OU.
Travail à faire :
Utiliser les filtres personnalisés pour afficher uniquement les ventes dont la quantité est inférieure à 15 et celles dont la quantité est supérieure à 35.
Utilisation des segments
Un autre moyen, plus rapide et plus facile, pour filtrer les données consiste à insérer des Segments. Cette fonctionnalité n'est disponible que si la plage de cellules est transformée en tableau. Pour insérer un segment, il faut cliquer dans le tableau pour l'activer ce qui a pour effet de faire apparaitre l'onglet Outils de tableau/Création. Dans le groupe Outils de cet onglet contextuel, on clique sur le bouton Insérer un segment.
Un clic sur ce bouton ouvre la fenêtre Insérer des segments qui permet de choisir les critères (les colonnes) selon lesquels on aimerait faire les filtres. On doit alors cocher les cases correspondantes à ces critères puis valider.
Dans la capture ci-dessus les colonnes : Date, Désignation et Quantité ont été insérées.
En cliquant sur un segment on fait apparaitre l'onglet Outils Segment/Options qui contient des outils de personnalisation du segment.
Pour filtrer les données du tableau via les segments, il suffit de cliquer sur un bouton (ou des boutons) pour voir s'afficher dans le tableau uniquement les items correspondants à ce bouton (à ces boutons).
Pour supprimer un segment il suffit de le sélectionner et d'appuyer sur la touche Suppr du clavier.
Travail à faire :
Utiliser les segments pour afficher uniquement les Clavier Dell KB216 USB Noir AZERTY vendus le 16/12/2019.
Supprimer les doublons
Dans un tableau, il est fréquent de voir des items se répéter plusieurs fois dans une même colonne. Ces valeurs qui réapparaissent sont appelées des doublons. Dans certaines situations, on a besoin d'avoir une liste avec des valeurs uniques (sans doublons), ce qui nécessite de débarrasser la liste de ces valeurs redondantes. À ce niveau il faut différencier entre deux opérations distinctes : Supprimer les doublons et Extraire sans doublons.
Pour mettre ces opérations en action, on va essayer de supprimer les doublons dans la colonne Désignation qui contient 1018 lignes en plus de la ligne d'en-tête. Pour cela on va en faire une copie dans une autre feuille.
Supprimer les doublons
Pour supprimer les doublons d'un tableau on suit les étapes ci-dessous :
- Cliquer dans le tableau
- Dans le groupe Outils, de l'onglet Outils de tableau, cliquer sur Supprimer les doublons
- Dans la boite de dialogue qui s'ouvre cocher la case Mes données ont des en-têtes et la case correspondant à la colonne dont on veut supprimer les doublons
- Valider
Résultat, une liste qui ne contient plus que des éléments uniques (14 lignes au lieu de 1018) avec un message montrant le nombre des valeurs en double qui ont été supprimées.
Cette méthode présente un grand inconvénient qui est la perte des données contenues dans les lignes supprimées
Extraire sans doublons
Cette perte de données causée par la suppression des doublons est évitée par la deuxième méthode qui est l'extraction sans doublons ; pour cela, on n'a même pas besoin de faire une copie de la colonne, il suffit de la sélectionner.
- Dans le groupe Trier et filtrer de l'onglet Données, cliquer sur le bouton Avancé.
- Dans la boite de dialogue, choisir l'option Copier vers un autre emplacement
- Effacer le contenu de la zone Plage et sélectionner la totalité de la colonne concernée en cliquant dans son en-tête
- Dans la zone Copier dans, renseigner la cellule cible
- Cocher la case Extraction sans doublon
- Valider
Résultat :
- aucune perte de données
- extraction propre sans doublons
- sans toucher à la colonne source
Travail à faire :
Extraire sans doublon les Désignations avec les Prix et les copier dans la cellule J1.
Recevez ce cours au format PDF en envoyant un message ici