Trier, filtrer et synthétiser une base de données sur Excel
Un exercice corrigé pour exploiter une base de données Excel, tri multicritère, filtres, sous-totaux et liste déroulante de validation, avec le fichier Excel à télécharger.
Une longue liste de données non exploitée, c'est des dizaines de lignes où l'on scrolle et où l'on compte à la main qui a vendu quoi et dans quelle région. Une fois la base triée et filtrée, elle devient pilotable, et on répond en quelques secondes à des questions qui prenaient dix bonnes minutes !
L'objectif est d'apprendre à interroger une liste plutôt que de la lire, en combinant tri, filtre et synthèse, puis à fiabiliser la saisie en amont pour que les réponses restent justes. C'est le réflexe qui change la façon de travailler dès qu'on reçoit régulièrement des exports à exploiter.
Ce que tu vas construire
- Trier une base de données sur plusieurs critères simultanément sans désaligner les lignes.
- Filtrer des enregistrements selon des critères croisés (statut et montant).
- Calculer des sous-totaux automatiques à chaque changement de catégorie.
- Créer une liste déroulante de validation pour éviter les erreurs de saisie.
- Compter les enregistrements qui remplissent une condition avec NB.SI.
À connaître avant de commencer
- Savoir sélectionner une plage et reconnaître une ligne d'en-têtes.
- Avoir déjà saisi une formule simple.
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 | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Commercial | Région | Produit | Montant | Statut |
| 2 | 05/01/2026 | Dupont | Nord | Pack Pro | 4 500 | Gagné |
| 3 | 08/01/2026 | Martin | Sud | Pack Starter | 1 200 | En cours |
| 4 | 14/01/2026 | Dupont | Nord | Pack Enterprise | 12 000 | Gagné |
| 5 | 20/01/2026 | Garcia | Est | Pack Pro | 4 500 | Perdu |
| 6 | 02/02/2026 | Martin | Sud | Pack Pro | 4 500 | Gagné |
| 7 | 09/02/2026 | Garcia | Est | Pack Starter | 1 200 | En cours |
| 8 | 15/02/2026 | Dupont | Nord | Pack Starter | 1 200 | Gagné |
| 9 | 21/02/2026 | Martin | Sud | Pack Enterprise | 12 000 | Perdu |
| 10 | 03/03/2026 | Garcia | Est | Pack Pro | 4 500 | Gagné |
| 11 | 10/03/2026 | Dupont | Nord | Pack Pro | 4 500 | En cours |
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
Le Tableau (Ctrl+L), pour tout sauf les sous-totaux
Un Tableau structuré active les filtres automatiquement, étend les formules aux nouvelles lignes et permet de référencer les colonnes par leur nom ([@Montant] au lieu de E2). En revanche, Données > Sous-total ne fonctionne pas sur un Tableau. Garde donc une plage simple pour l'étape des sous-totaux, ou reconvertis le Tableau avec Création de tableau > Convertir en plage avant de les insérer.
Le filtre avancé pour extraire les données
Le bouton Avancé, dans le groupe Trier et filtrer de l'onglet Données, ouvre le filtre avancé. Avec l'option Copier vers un autre emplacement, il recopie les lignes qui remplissent tes critères dans une autre zone de la feuille, sans toucher à la base d'origine. Les critères s'écrivent dans une petite plage à part qui reprend les en-têtes de la base, par exemple Statut et Montant, avec Gagné et >4000 sur la ligne du dessous.
SOUS.TOTAL ignore les lignes filtrées
Contrairement à SOMME, SOUS.TOTAL(9;plage) n'additionne que les lignes que le filtre laisse affichées. Si tu poses un filtre sur la base après avoir écrit ta formule SOUS.TOTAL, le résultat se met à jour tout seul. Une ligne masquée à la main, en revanche, reste comptée avec le code 9, et il faut le code 109 pour l'écarter aussi.
S'entraîner sur 3 exercices similaires à trier, filtrer et synthétiser une base de données
Prioriser une roadmap produit avec un score RICE
Construis un score RICE dans Excel pour classer les features de ta roadmap sur des critères chiffrés et sortir la priorité numéro un de façon objective.
Voir l'exercice
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
Tu as peut-être encore une question
Clique sur une cellule à l'intérieur de la plage, puis va dans Données > Trier. La boîte de dialogue permet d'empiler plusieurs niveaux avec le bouton Ajouter un niveau, par exemple la Région de A à Z, puis le Montant du plus grand au plus petit. Coche « Mes données ont des en-têtes » pour que la ligne d'en-têtes reste en haut et ne soit pas triée avec les données.
Active le filtre automatique (Données > Filtrer), puis utilise la flèche de chaque en-tête de colonne. Pour croiser deux critères, par exemple un Statut égal à Gagné et un Montant supérieur à 4 000, applique les filtres colonne par colonne. Excel n'affiche alors que les lignes qui remplissent tous les filtres à la fois, et masque les autres sans les supprimer.
Trie d'abord la base par la colonne de regroupement, la Région par exemple. Va ensuite dans Données > Sous-total, choisis la colonne de regroupement dans À chaque changement de, la fonction (Somme, Moyenne, Nombre…) et la colonne à calculer. Excel insère tout seul les lignes de total et leurs formules SOUS.TOTAL. L'outil ne fonctionne pas sur un Tableau (Ctrl+L), qu'il faut d'abord convertir en plage. Pour retirer les totaux, retourne dans Données > Sous-total et clique sur Supprimer tout.
Sélectionne les cellules à contrôler, puis va dans Données > Validation des données. Dans Autoriser, choisis Liste. Dans Source, saisis les valeurs séparées par des points-virgules dans un Excel en français (Gagné;En cours;Perdu), ou sélectionne une plage qui contient les valeurs autorisées. Séparées par des virgules, elles ne formeraient qu'un seul choix. Chaque cellule affiche ensuite une flèche, et Excel refuse une valeur absente de la liste, comme Gagne sans accent. Une espace en trop autour d'une valeur passe pourtant le contrôle.
SOMME additionne toutes les cellules de la plage, qu'elles soient visibles ou masquées par un filtre. SOUS.TOTAL(9;plage) n'additionne que les lignes que le filtre laisse affichées, et ignore les autres SOUS.TOTAL de sa plage, ce qui évite de compter deux fois un sous-total. Sur ce journal filtré sur les affaires gagnées au-dessus de 4 000, SOMME renvoie 50 100 et SOUS.TOTAL 25 500. Pour écarter aussi les lignes masquées à la main, il faut le code 109 au lieu de 9.
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 CRM simple
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