Excel : exercices de synthèse sur tableaux croisés dynamiques (TCD) avec jeu de données complet et correction

Scénario fil rouge et livrables attendus

Vous allez réaliser un classeur complet à partir d’un jeu de données de ventes simulées (3 000 à 10 000 lignes) et de deux tables de référence (Produits, Régions). L’objectif est de produire 4 TCD répondant à des questions métier, d’ajouter des regroupements, des segments/chronologie, puis de construire un mini-dashboard. À la fin, vous devez disposer de : (1) un classeur final structuré (Données + Référentiels + TCD + Dashboard) et (2) une grille d’auto-vérification basée sur des résultats attendus (structures, champs, filtres, formats, contrôles).

Organisation recommandée du classeur

• Feuille Ventes : données brutes puis Tableau Excel (nommé tblVentes).
• Feuille Produits : table de référence (nommée tblProduits).
• Feuille Regions : table de référence (nommée tblRegions).
• Feuille TCD_1 à TCD_4 : un TCD par question.
• Feuille Dashboard : synthèse visuelle + KPI.
• Feuille AutoCheck : grille d’auto-vérification.

Jeu de données final (fourni) : structure et dictionnaire

Le jeu de données est composé d’une table de faits Ventes et de deux tables de référence. Les champs ci-dessous sont ceux attendus pour réaliser les exercices (les noms peuvent varier légèrement dans votre fichier : adaptez en conservant la logique).

Table Ventes (3 000–10 000 lignes)

Si CA et/ou CA_Net ne sont pas fournis, vous les calculerez dans la phase “Nettoyage et mise en Tableau” (voir plus bas).

Table Produits

Table Regions

Consignes générales (à respecter pour tous les exercices)

• Les TCD doivent être basés sur des Tables Excel (pas sur des plages “en dur”).
• Les mesures principales à utiliser : CA_Net (ou CA si CA_Net absent), Quantité, Remise_% (en moyenne).
• Les filtres globaux doivent être pilotables via segments/chronologie (au moins : Date, Région, Canal, Catégorie).
• Les résultats doivent être lisibles : formats monétaires, pourcentages, tri pertinent, et noms de champs clairs.

Parcours de réalisation (pas à pas)

1) Nettoyage et mise en Tableau (sans re-décrire la théorie)

Objectif : obtenir une base fiable et “TCD-ready” dans tblVentes, et des référentiels propres (tblProduits, tblRegions).

• Vérifiez les types : Date en Date, Quantité en Nombre, Remise_% en Pourcentage, Prix_Unitaire en Nombre.
• Contrôlez la granularité : 1 ligne = 1 ligne de vente (commande/produit/date). Repérez les doublons sur ID_Commande + ProduitID + Date.
• Créez (si nécessaire) des colonnes calculées dans tblVentes :
  • CA : = [@Quantité] * [@Prix_Unitaire]
  • CA_Net : = [@CA] * (1 - [@Remise_%])
• Vérifiez les valeurs aberrantes : remises négatives, remises > 100%, quantités nulles, prix unitaires à 0.
• Nommez les tables : tblVentes, tblProduits, tblRegions.

Point de contrôle : le nombre de lignes de tblVentes doit rester identique avant/après mise en Tableau (sauf suppression volontaire de lignes invalides, à documenter).

Continuez dans notre application.

Vous pouvez écouter le livre audio écran éteint, recevoir un certificat gratuit pour ce cours et accéder également à 5 000 autres cours en ligne gratuits.

Ou poursuivez votre lecture ci-dessous...

Téléchargez l'application

2) Création des 4 TCD (questions métier)

Vous allez créer 4 TCD distincts. Chaque TCD doit répondre à une question précise et être compatible avec des segments/chronologie communs. Les champs “Produit”, “Catégorie”, “Région” proviennent des tables de référence : assurez-vous qu’ils sont disponibles dans la liste de champs (selon votre modèle, via relations ou via colonnes déjà enrichies).

TCD 1 — Top 5 produits contributeurs au CA sur la période filtrée

Question : Quels sont les 5 produits les plus contributeurs au CA sur la période filtrée ?

• Lignes : Produit
• Valeurs : Somme de CA_Net (format Monétaire)
• Filtres (facultatif si segments) : Catégorie, Région, Canal
• Tri : décroissant sur Somme de CA_Net
• Filtre Top N : Top 5 par Somme de CA_Net

Point de contrôle : le TCD doit afficher exactement 5 produits (sauf égalités si votre Excel inclut des ex-aequo selon paramètres).

TCD 2 — Région avec la remise moyenne la plus élevée

Question : Quelle Région a la remise moyenne la plus élevée ?

• Lignes : Région
• Valeurs : Moyenne de Remise_% (format Pourcentage, 1 ou 2 décimales)
• Tri : décroissant sur Moyenne de Remise_%
• Option utile : ajoutez en 2e valeur Somme de CA_Net pour contextualiser (une forte remise sur faible CA peut être trompeuse).

Point de contrôle : vérifiez que l’agrégation est bien Moyenne (erreur typique : Somme de remise).

TCD 3 — Canal le plus performant par Catégorie

Question : Quel Canal est le plus performant par Catégorie ?

• Lignes : Catégorie
• Colonnes : Canal
• Valeurs : Somme de CA_Net (format Monétaire)
• Affichage conseillé : ajoutez une 2e mesure “% du total de la ligne” sur CA_Net pour voir la répartition par canal au sein de chaque catégorie.

Point de contrôle : pour chaque catégorie, identifiez le canal max (visuellement ou via mise en forme conditionnelle).

TCD 4 — Évolution du CA mensuel et détection de mois atypiques

Question : Comment évolue le CA mensuel et quels sont les mois atypiques ?

• Lignes : Date (regroupée en Années + Mois)
• Valeurs : Somme de CA_Net
• Valeur additionnelle : Variation vs mois précédent (si disponible via “Afficher les valeurs en tant que” : différence ou % de différence par rapport à l’élément précédent)
• Filtre : Région, Canal, Catégorie

Détection “atypique” (méthode simple) : considérez atypique un mois dont le CA_Net est très éloigné des mois voisins (ex. chute ou hausse forte). Appuyez-vous sur la colonne de variation et/ou une mise en forme conditionnelle (barres de données ou échelle de couleurs).

3) Regroupements (dates + tranches)

Vous devez appliquer au moins deux regroupements : un sur les dates et un sur une mesure numérique.

Regroupement des dates (TCD 4)

• Regroupez Date en Années et Mois (ou Trimestres si demandé).
• Vérifiez que la chronologie (si ajoutée ensuite) pilote bien le champ date source.

Regroupement en tranches (exercice sur la remise ou le CA)

Créez un TCD “technique” (ou utilisez un des TCD existants si pertinent) pour segmenter les ventes en tranches de remise.

• Lignes : Remise_% puis appliquez un regroupement en classes (ex. 0%–5%, 5%–10%, 10%–20%, 20%+).
• Valeurs : Somme de CA_Net et/ou Nombre de lignes (compte) pour voir la distribution.

Point de contrôle : les tranches doivent être ordonnées et couvrir 100% des lignes (pas de valeurs “hors groupe” sauf données invalides).

4) Segments et chronologie (pilotage global)

Objectif : piloter l’ensemble des TCD avec les mêmes contrôles.

• Ajoutez des segments pour : Région, Canal, Catégorie.
• Ajoutez une chronologie sur Date.
• Connectez chaque segment/chronologie aux 4 TCD (et aux graphiques du dashboard si vous en créez).

Point de contrôle : un clic sur une Région doit mettre à jour simultanément TCD 1 à 4 (sinon, connexion manquante ou sources différentes).

5) Construction d’un mini-dashboard (synthèse)

Vous allez assembler sur une feuille Dashboard une vue compacte, lisible, pilotée par segments/chronologie.

Composants minimum attendus

• KPI 1 : CA total (Somme de CA_Net) sur la sélection courante.
• KPI 2 : Remise moyenne (Moyenne de Remise_%) sur la sélection courante.
• Visuel 1 : Top 5 produits (issu du TCD 1) sous forme de tableau ou graphique.
• Visuel 2 : Évolution mensuelle du CA (issu du TCD 4) sous forme de graphique.
• Visuel 3 : Performance Canal par Catégorie (issu du TCD 3) sous forme de tableau lisible ou graphique empilé.

Règles de mise en page (simples mais évaluées)

• Placez les segments/chronologie en haut ou à gauche, alignés.
• Gardez une zone KPI en haut (2 à 4 cartes).
• Évitez les éléments redondants (légendes inutiles, quadrillages trop visibles).
• Appliquez des formats cohérents : monétaire, %, séparateurs de milliers.

Questions de synthèse (à traiter dans le classeur)

Q1 — Top 5 produits contributeurs au CA sur la période filtrée

• Livrable : TCD 1 + (option) graphique en barres.
• Attendu : tri décroissant, filtre Top 5, format monétaire.

Q2 — Région avec la remise moyenne la plus élevée

• Livrable : TCD 2.
• Attendu : moyenne de remise, tri décroissant, format %.

Q3 — Canal le plus performant par Catégorie

• Livrable : TCD 3.
• Attendu : matrice Catégorie x Canal, CA_Net en valeur, lecture du canal max par catégorie.

Q4 — Évolution mensuelle du CA et mois atypiques

• Livrable : TCD 4 + graphique (courbe/colonnes).
• Attendu : regroupement Année/Mois, repérage des mois atypiques via variation et/ou mise en forme conditionnelle.

Correction structurée (ce qui est évalué)

Critères attendus par TCD

Points de contrôle (checklist rapide)

• PC1 : tblVentes contient bien toutes les lignes attendues et aucune colonne critique vide (Date, ProduitID, RegionID, Quantité).
• PC2 : CA_Net est cohérent : si Remise_% augmente, CA_Net diminue à CA constant.
• PC3 : TCD 1 affiche 5 produits (Top 5) et se met à jour avec la chronologie.
• PC4 : TCD 2 est en “Moyenne de Remise_%” (pas Somme) et trié.
• PC5 : TCD 3 permet d’identifier le canal dominant par catégorie (visuel ou mise en forme conditionnelle).
• PC6 : TCD 4 est mensuel (Année/Mois) et met en évidence au moins un mois atypique sur la période complète.
• PC7 : Tous les segments/chronologie pilotent tous les TCD (test : sélection Canal = Web, puis Région = une région, puis un intervalle de dates).

Erreurs fréquentes et correctifs

• Les dates ne se regroupent pas : la colonne Date est en texte. Convertissez-la en Date puis actualisez le TCD.
• Remise moyenne incohérente : le champ Remise_% est stocké en 10 au lieu de 0,10. Corrigez l’échelle (division par 100) puis reformatez en %.
• Top 5 “instable” : le tri est manuel ou non basé sur la valeur CA. Réappliquez tri décroissant + filtre Top 10/Top 5.
• Segments ne filtrent pas tous les TCD : TCD créés depuis des sources différentes. Recréez les TCD depuis la même source/modèle ou connectez correctement via “Connexions de rapport”.
• CA trop élevé : double comptage (doublons) ou CA calculé deux fois (colonne CA déjà nette + remise appliquée à nouveau). Vérifiez les formules et la définition de CA_Net.

Grille d’auto-vérification (à reproduire dans la feuille AutoCheck)

Copiez/collez cette grille dans votre feuille AutoCheck et cochez au fur et à mesure (colonne “OK”).

Livrables à remettre

• Classeur final contenant : Ventes (tblVentes), Produits (tblProduits), Regions (tblRegions), TCD_1 à TCD_4, Dashboard, AutoCheck.
• Grille d’auto-vérification complétée (colonne OK + commentaires sur écarts).