EX05 | Analyser les ventes avec un TCD
Document fictif à usage pédagogique.
Énoncé
Contexte
- Entreprise : Brévalis Industries (fictive).
- Demande : la direction commerciale pose trois questions : quel est le CA mois par mois, quels sont les 5 produits qui rapportent le plus, quelle part du CA réalise chaque type de client (canal de vente) ?
- Durée : 45 min | Niveau : intermédiaire.
- Départ : EX05_depart.xlsx (tblVente, 500 ventes réparties sur toute l'année 2025, Produits, Clients) | Fin : EX05_corrige.xlsx.
Travail demandé
- TCD CA mensuel : dates groupées par mois et trimestres, CA HT au format monétaire et panier moyen.
- TCD Top produits : les 5 désignations au plus fort CA, triées.
- TCD Canaux : part de chaque canal en % du total, filtrée par un segment Region et une chronologie.
- Graphique croisé dynamique du CA mensuel.
- Ajouter une vente et actualiser les TCD.
Simulation
- Simulation interactive EX05 (11 étapes guidées, validation automatique) : voir la sous-page EX05 | Simulation.
Corrigé
E00 | Ouvrir le fichier de départ
- Fichier > Ouvrir > Parcourir >
Documents\excel-lab\depart\EX05_depart.xlsx.
Capture : boîte Ouvrir, dossier depart, fichier EX05_depart.xlsx sélectionné
E01 | Insérer le TCD
- Cliquer dans tblVente > Insertion > Tableau croisé dynamique > Tableau/Plage : tblVente, Nouvelle feuille de calcul > OK.
- Renommer la nouvelle feuille CA mensuel.
Capture : boîte Créer un tableau croisé dynamique
E02 | Placer les champs
- Volet Champs : cocher Date (Lignes) puis MontantHT (Valeurs, Somme).
Capture : volet Champs de tableau croisé dynamique
E03 | Grouper les dates
- Clic droit sur une date > Grouper > sélectionner Mois et Trimestres > OK.
- Selon la version, Excel groupe déjà les dates automatiquement : vérifier ou ajuster.
Capture : boîte Grouper
E04 | Nom et format de la somme
- Clic droit sur un montant > Paramètres des champs de valeurs > Nom personnalisé : CA HT > Format de nombre > Monétaire > OK > OK.
Capture : boîte Paramètres des champs de valeurs
E05 | Panier moyen
- Faire glisser MontantHT une deuxième fois dans Valeurs > Paramètres des champs de valeurs > Moyenne, nom Panier moyen, format Monétaire.
Capture : TCD avec les deux colonnes CA HT et Panier moyen
E06 | Top 5 produits
- Depuis Ventes, nouveau TCD sur une nouvelle feuille nommée Top produits : Designation en Lignes, MontantHT en Valeurs.
- Flèche Étiquettes de lignes > Filtres s'appliquant aux valeurs > 10 premiers > 5 > OK.
- Même flèche > Trier du plus grand au plus petit.
Capture : boîte Filtre des 10 premiers
E07 | Part de chaque canal
- Nouveau TCD sur une feuille Canaux : Canal en Lignes, MontantHT en Valeurs.
- Clic droit sur un montant > Afficher les valeurs > % du total général.
- Le canal tient lieu de type de client (Commercial, Salon, Appel d'offres, Portail B2B).
Capture : menu Afficher les valeurs
E08 | Segment
- Analyse du tableau croisé dynamique > Insérer un segment > Region > OK, puis cliquer sur Bretagne.
Capture : segment Region avec Bretagne sélectionnée
E09 | Chronologie
- Analyse du tableau croisé dynamique > Insérer une chronologie > Date > OK.
- Cliquer sur janvier puis Maj+clic sur mars : les pourcentages portent sur le 1er trimestre.
Capture : chronologie
E10 | Graphique croisé dynamique
- Feuille CA mensuel, clic dans le TCD > Analyse du tableau croisé dynamique > Graphique croisé dynamique > Histogramme groupé > OK.
Capture : graphique final
E11 | Ajouter une vente et actualiser
- Feuille Ventes, coller en A502 : CMD-25-03001, 30/12/2025, CLI-002, ART-0033, Contrat maintenance Or, P4, Bretagne, 10, 1442,58, 0, Appel d'offres, S-03.
- Données > Actualiser tout (ou flèche d'Actualiser > Actualiser tout) : le CA de décembre augmente de 14 425,80 €.
- F12 > EX05_corrige.
Capture : TCD actualisé
Points de vigilance
- Un TCD travaille sur une copie des données (le cache) : il faut actualiser après chaque modification de la source.
- Construire le TCD sur un tableau (tblVente) et non sur une plage : les nouvelles lignes sont prises en compte à l'actualisation.
- L'extrait d'EX03 et EX04 ne couvre que janvier à mars : EX05 utilise un extrait réparti sur l'année.