Trier et filtrer une collection sur Excel
Un exercice corrigé pour trier et filtrer une collection dans Excel sur plusieurs niveaux et compter par catégorie avec NB.SI, avec le fichier Excel à télécharger.
Une liste de livres, de films ou de bouteilles qui s'allonge devient vite illisible, et retrouver un titre en faisant défiler à l'aveugle vire au casse-tête ! Le tri et le filtre remettent de l'ordre en quelques clics. Dans cet exercice, on va ranger ensemble une collection, puis l'interroger comme une vraie base de données.
L'objectif est de maîtriser le tri sur plusieurs niveaux et le filtre, deux gestes qui servent sur n'importe quel tableau. Une fois le réflexe acquis, tu réorganises et tu isoles l'information utile sans jamais abîmer tes données sources, et tu sais compter combien d'éléments répondent à un critère donné.
Ce que tu vas construire
- Organiser une collection en colonnes propres prêtes à être triées et filtrées.
- Trier la collection sur plusieurs niveaux, par exemple catégorie puis note.
- Appliquer un filtre pour isoler une catégorie ou une plage de valeurs.
- Combiner deux critères de filtre sans perdre le tri en place.
- Compter combien de titres appartiennent à une catégorie avec NB.SI.
À connaître avant de commencer
- Savoir sélectionner un tableau avec ses en-têtes.
- Savoir où se trouve l'onglet Données du ruban.
Voici les données de départ de cet exercice. Copie-les ou , puis entraîne-toi avant de regarder le corrigé.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Titre | Catégorie | Année | Note | Prix |
| 2 | Le Mystère du phare | Polar | 2019 | 4 | 12,90 |
| 3 | Voyage en Toscane | Voyage | 2021 | 5 | 24,00 |
| 4 | La Théorie du chaos | Science | 2018 | 3 | 18,50 |
| 5 | Recettes du Sud | Cuisine | 2022 | 4 | 29,90 |
| 6 | Ombres sur la ville | Polar | 2020 | 5 | 14,50 |
| 7 | Carnets d'Islande | Voyage | 2017 | 3 | 21,00 |
| 8 | L'Univers expliqué | Science | 2023 | 5 | 26,00 |
| 9 | Pâtisserie maison | Cuisine | 2019 | 4 | 32,00 |
| 10 | Le Dernier Témoin | Polar | 2022 | 2 | 11,90 |
| 11 | Routes d'Asie | Voyage | 2020 | 4 | 23,50 |
Résoudre l'exercice étape par étape
Coche chaque étape au fur et à mesure. Tente-la dans ton fichier, puis déplie le corrigé.
Astuces pour aller plus loin
Trie toujours avec les en-têtes
Dans la fenêtre de tri, garde la case Mes données ont des en-têtes cochée. Sinon Excel traite ta ligne de titres comme un livre de plus. Sur un tri par titre de A à Z sans la case, la ligne Titre, Catégorie, Année… se retrouve entre Routes d'Asie et Voyage en Toscane. Sur le tri par catégorie de l'exercice, tu ne verrais rien bouger, car le mot Catégorie se classe juste avant Cuisine, et la case n'en reste pas moins indispensable.
S'entraîner sur 3 exercices similaires à trier et filtrer une collection
Suivi des notes de frais
Mets en place un suivi des notes de frais sur Excel, avec le total par catégorie, le contrôle du plafond des repas et le total remboursable d'un relevé de dépenses.
Voir l'exercice
Préparer une base de contacts pour un publipostage
Nettoie une base de contacts à la casse incohérente avec NOMPROPRE, puis construis les champs de fusion (formule d'appel, identifiant) prêts à être repris dans un publipostage.
Voir l'exercice
Planning d'équipe dynamique
Construire un planning d'équipe visuel et dynamique qui s'adapte automatiquement aux congés, aux jours fériés et aux rotations.
Voir l'exercice
Tu as peut-être encore une question
Sélectionne ton tableau, ouvre Données puis Trier, et ajoute autant de niveaux que nécessaire avec le bouton Ajouter un niveau. Excel applique les critères dans l'ordre, le premier regroupe, le second départage à l'intérieur de chaque groupe.
Trier réorganise l'ordre des lignes selon une ou plusieurs colonnes, en gardant tout affiché. Filtrer masque les lignes qui ne répondent pas à un critère, sans changer leur ordre. On combine souvent les deux sur un même tableau.
Clique dans ton tableau puis va dans Données et active Filtrer. Une flèche apparaît sur chaque en-tête de colonne. Tu cliques dessus pour cocher les valeurs à garder ou appliquer un filtre par texte, nombre ou couleur.
Utilise NB.SI avec la colonne des catégories comme plage et le nom de la catégorie comme critère. La formule renvoie le nombre de lignes correspondantes. Attention, elle compte aussi les lignes masquées par un filtre éventuel.
Un tri personnalisé range une colonne dans un ordre que tu choisis, par exemple Voyage avant Cuisine au lieu de l'ordre alphabétique, grâce à une liste personnalisée. Ouvre Données, Trier, choisis la colonne Catégorie, puis dans Ordre sélectionne « Liste personnalisée » et tape tes valeurs dans l'ordre voulu, une par ligne. Excel garde cette liste dans ses options, elle resservira pour les tris suivants et pour la recopie incrémentée. Les niveaux de tri suivants, la note ou l'année, se combinent avec ce premier niveau comme dans l'exercice.
La fonction FILTRE, disponible dans Excel 365 et Excel 2021, renvoie dans une autre zone de la feuille les lignes qui répondent à un critère, sans masquer quoi que ce soit, et =FILTRE(A2:E11;B2:B11="Voyage") affiche par exemple les trois titres de la catégorie Voyage là où tu écris la formule, la collection occupant les colonnes A à E. Le résultat se met à jour tout seul quand la collection change, et il se combine avec TRIER pour classer les lignes extraites, par note décroissante par exemple. Le bouton Filtrer, lui, agit sur place et masque les lignes, ce qui reste plus simple pour une question d'un instant.
Pour aller plus loin
Tout ce dont tu as besoin pour continuer à t’entraîner se trouve ici.
Récupère notre modèle de planning d'équipe
Télécharger le fichier
Ton cours Excel gratuit
3 chapitres pour apprendre les bases et découvrir l'automatisation.
Voir le cours
Télécharge notre cours Excel complet en PDF
Télécharger
Le quiz qui révèle ton vrai niveau
Faire le test
Le dictionnaire des formules Excel
Chercher une formule