Filtrer, croiser, analyser
La compétence reine du niveau 3 : isoler l'information avec les filtres, et résumer des centaines de lignes en un clin d'œil avec le tableau croisé dynamique. Plus deux outils malins : Valeur cible et remplissage instantané.
Les filtres automatiques
N'afficher que ce qui nous intéresse📑 Fichier : exercices-excel-BA3.xlsx — feuille « Ventes » (22 ventes).
Un filtre masque temporairement les lignes qui ne correspondent pas à un critère (les données ne sont pas supprimées).
- Clique dans les données › onglet Données › Filtrer (des entonnoirs apparaissent sur les en-têtes).
- Clique sur l'entonnoir d'une colonne : coche/décoche les valeurs, ou utilise Filtres textuels / numériques.
- L'entonnoir devient « rempli » pour indiquer un filtre actif. Pour tout réafficher : Effacer le filtre.
Rechercher & remplacer
Ctrl+F pour rechercher, Ctrl+H pour remplacer. On peut remplacer dans toute la feuille, et même selon la mise en forme.
Les filtres complexes
Critères personnalisés & tri intégréAu-delà des cases à cocher, chaque entonnoir propose des filtres personnalisés :
- Filtres numériques : supérieur à, entre, 10 valeurs les plus grandes, au-dessus de la moyenne…
- Filtres textuels : commence par, contient, ne contient pas…
- Filtrer par couleur (si tu as utilisé une mise en forme conditionnelle).
- Le menu de l'entonnoir permet aussi de trier la colonne (croissant/décroissant).
Pour des critères vraiment avancés (plusieurs conditions sur plusieurs colonnes), Excel propose le Filtre avancé (Données › Avancé) avec une « zone de critères ».
Le tableau croisé dynamique (TCD)
Résumer des centaines de lignes en quelques clics📑 Fichier : exercices-excel-BA3.xlsx — feuille « Ventes ».
Le TCD est l'outil le plus puissant d'Excel. Il regroupe et synthétise automatiquement une grande table : « total des ventes par région », « ventes par produit et par mois »… le tout en glissant-déposant des champs.
- Clique dans les données › Insertion › Tableau croisé dynamique › OK (sur une nouvelle feuille).
- À droite, la liste des champs (Mois, Région, Produit, Vendeur, Montant). Fais glisser :
- Région dans Lignes
- Montant dans Valeurs (Excel fait la somme)
- Tu obtiens le total des ventes par région, instantanément. Ajoute Mois dans Colonnes pour croiser région × mois.
| Zone | Rôle |
|---|---|
| Lignes | Les catégories en lignes (ex. Région) |
| Colonnes | Les catégories en colonnes (ex. Mois) |
| Valeurs | Le calcul (Somme, Moyenne, Nombre…) |
| Filtres | Un filtre global au-dessus du tableau |
Pour changer le calcul (Somme → Moyenne, Nombre…) : clic sur le champ dans Valeurs › Paramètres des champs de valeurs. Et si les données changent : clic droit dans le TCD › Actualiser.
Analyse rapide & remplissage instantané
Deux raccourcis qui font gagner du tempsL'Analyse rapide
Sélectionne une plage de données : un petit bouton ⚡ Analyse rapide apparaît en bas à droite (ou Ctrl+Q). Il propose en un clic : mise en forme conditionnelle, graphiques, totaux, tableaux, graphiques sparkline.
Le remplissage instantané (Ctrl+E)
Excel devine la règle à partir d'un exemple. Tu as une colonne « Nom Prénom » et tu veux extraire le prénom ? Tape le 1ᵉʳ à la main, appuie sur Ctrl+E, et Excel remplit le reste.
Idéal pour : séparer nom/prénom, extraire un code, recoller des morceaux, mettre en majuscules… sans aucune formule.
La Valeur cible
Trouver l'entrée pour atteindre un résultat🎯 Fichier : exercices-excel-BA3.xlsx — feuille « Valeur cible ».
La question inversée. Au lieu de « quel résultat pour ces valeurs ? », la Valeur cible répond à « quelle valeur d'entrée pour atteindre ce résultat ? ».
Exemple : CA = Prix × Quantité. On veut un CA de 1 000 € : quelle quantité ?
- En
B3, écris d'abord la formule=B1*B2. - Onglet Données › Analyse de scénarios › Valeur cible.
- Renseigne : Cellule à définir =
B3, Valeur à atteindre =1000, Cellule à modifier =B2(la quantité). - OK : Excel trouve la quantité qui donne 1 000 €.