Excel Avancé · Cours interactif
Exercices pratiques · module 15 h · TOSA 726 à 875

Vous devenez l'analyste de Datashop

Fini les extraits : on vous confie le fichier complet — 966 commandes sur deux exercices, tous canaux confondus. La direction attend des réponses chiffrées à des questions qu'elle se pose vraiment. Les encadrés 🔍 Analyse vous font passer du calcul à la conclusion : c'est exactement ce qui différencie ce niveau du précédent.

🏋️ 18 exercices 🧩 + 2 missions 🏢 966 commandes ✅ Auto-suivi

📒 Votre fichier de travail

Tout se passe dans datashop-niveau3.xlsx — feuilles Commandes (966 lignes, 2025 et 2026), Produits (46 références avec coût d'achat et prix de vente), Clients (140), Objectifs (les cibles de CA par canal) et Valeur_cible.

⚠️ Piège n° 1 de ce niveau : 30 commandes sont annulées. Elles ne doivent jamais entrer dans un calcul de chiffre d'affaires.

🧭 Environnement & méthodes

Exercice 1 · ⭐ facile

Cartographier le processus de commande

Sur une feuille « Processus », insérez un SmartArt « Processus » en 5 étapes représentant le parcours d'une commande chez Datashop : Commande → Préparation → Expédition → Livraison → (Retour éventuel). Ajoutez une forme flèche qui pointe l'étape où un problème peut survenir.

Voir la solution
Insertion › SmartArt › catégorie Processus. Utilisez le volet texte (flèche à gauche) pour saisir les étapes : Entrée crée une forme. Pour la flèche : Insertion › Formes › Flèches. Ces cinq étapes correspondent exactement aux valeurs de la colonne Statut de vos données.
Exercice 2 · ⭐⭐ moyen

Un sommaire de classeur avec liens hypertexte

Créez une feuille « Sommaire » en première position. Listez les feuilles du classeur et transformez chaque nom en lien hypertexte vers la feuille correspondante. Ajoutez sur chaque feuille un lien « ← Retour au sommaire » en A1.

🔍 Analyse : avec 5 feuilles, le sommaire semble superflu. Les classeurs de gestion que vous recevrez en entreprise en comptent 15 à 30. À partir de combien un sommaire devient-il indispensable ?
Voir la solution
Clic droit sur la cellule › Lien (Ctrl+K) › Emplacement dans ce document › choisissez la feuille. Astuce professionnelle : colorez l'onglet Sommaire différemment pour le repérer instantanément.
Exercice 3 · ⭐⭐ moyen

L'organigramme de Datashop

Insérez un SmartArt « Hiérarchie » représentant l'organisation : une Direction, puis les services E-commerce, Magasin, Logistique, Administration et Télévente. Ajoutez puis supprimez une forme pour vous entraîner, et changez la palette de couleurs.

Voir la solution
Insertion › SmartArt › Hiérarchie › Organigramme. Dans le volet texte : Entrée crée une forme au même niveau, Tab la descend d'un niveau. Onglet Création SmartArt › Modifier les couleurs.

🧮 Calculs : formules & fonctions

Exercice 4 · ⭐⭐ moyen

Le prix TTC avec une référence absolue

Sur « Produits », placez le taux de TVA (20 %) dans une cellule isolée en haut. Calculez le Prix TTC de la première ligne avec une référence absolue au taux, puis recopiez sur les 46 produits. Changez ensuite le taux à 5,5 % et vérifiez que tout se met à jour.

🔒 Feuille : « Produits »

🔍 Analyse : essayez volontairement sans les $ et regardez ce qui se passe à la ligne 5. Pourquoi le résultat n'est-il pas seulement faux, mais silencieusement faux ? C'est le type d'erreur qui passe en production.
Voir la solution
Taux en H1. En I2 : =E2*(1+$H$1) — touche F4 pour poser les $. Sans eux, la formule irait chercher le taux en H2, H3… qui sont vides : le prix TTC serait égal au prix HT, sans aucun message d'erreur. Une cellule de paramètre isolée + référence absolue = la bonne pratique.
Exercice 5 · ⭐⭐ moyen

Segmenter les commandes (SI imbriqués)

Sur « Commandes », créez une colonne Segment selon le Total_HT : ≥ 1 500 € « Très grand compte », ≥ 800 € « Grand compte », ≥ 300 € « Courant », sinon « Petit panier ».

🎯 Feuille : « Commandes »

🔍 Analyse : testez volontairement l'ordre inverse (le seuil de 300 € en premier). Que se passe-t-il pour une commande de 2 000 € ? Pourquoi l'ordre des conditions est-il vital dans un SI imbriqué ?
Voir la solution
=SI(M2>=1500;"Très grand compte";SI(M2>=800;"Grand compte";SI(M2>=300;"Courant";"Petit panier"))). On teste toujours du plus exigeant au moins exigeant : SI s'arrête à la première condition vraie. Dans l'ordre inverse, une commande de 2 000 € serait classée « Courant » dès le premier test.
Exercice 6 · ⭐⭐⭐ difficile

SOMME.SI et NB.SI : le CA par canal

Sans tableau croisé dynamique, uniquement par formules : calculez le CA de chaque canal avec SOMME.SI et le nombre de commandes avec NB.SI. Comptez aussi les commandes dont le Total_HT dépasse 500 €.

🧾 Feuille : « Commandes »

🔍 Analyse : additionnez vos quatre CA de canaux. Le total dépasse-t-il le CA réel de Datashop ? Il le dépasse forcément si vous n'avez pas exclu les 30 commandes annulées. Comment corriger — et pourquoi SOMME.SI.ENS devient nécessaire ?
Voir la solution
CA d'un canal : =SOMME.SI(G:G;"Site web";M:M). Pour exclure les annulations, il faut deux critères : =SOMME.SI.ENS(M:M;G:G;"Site web";J:J;"<>Annulée"). Résultats corrects : Site web 160 835,80 € (382 cmd) · Magasin 134 865,29 € (278) · Téléphone 82 086,00 € (123) · Marketplace 58 113,76 € (153). Total : 435 900,85 € sur 936 commandes valides. Commandes > 500 € : 321 (34,3 %).
Exercice 7 · ⭐⭐⭐ difficile

MOYENNE.SI : le panier moyen par profil

Calculez le panier moyen par canal, puis par type de client (Particulier / Professionnel), avec MOYENNE.SI.ENS en excluant les annulations. Présentez le tout dans un petit tableau et mettez le plus élevé en gras.

🧾 Feuille : « Commandes »

🔍 Analyse : le téléphone ne pèse que 123 commandes sur 936, mais son panier moyen écrase tous les autres canaux. Cherchez pourquoi dans la colonne Type_client. Si vous étiez directeur commercial, renforceriez-vous la télévente ou le site web ?
Voir la solution
=MOYENNE.SI.ENS($M:$M;$G:$G;"Téléphone";$J:$J;"<>Annulée"). Paniers moyens : Téléphone 667,37 € · Magasin 485,13 € · Site web 421,04 € · Marketplace 379,83 €. Par profil : Professionnel 730,18 € (327 cmd, 54,8 % du CA) contre Particulier 323,70 € (609 cmd). L'explication tient en une ligne : la télévente vend au B2B, la marketplace au B2C.
Exercice 8 · ⭐⭐ moyen

SI + ET : la remise ciblée

La direction veut relancer la marketplace : remise de 5 % aux commandes qui remplissent les deux conditions — canal « Marketplace » et montant supérieur à 400 €. Créez la colonne Remise et calculez le coût total de l'opération.

🎁 Feuille : « Commandes »

🔍 Analyse : remplacez ET par OU et observez le nombre de lignes éligibles. Le coût de l'opération explose : mesurez-le. C'est la différence entre une promotion ciblée et une promotion ruineuse.
Voir la solution
En O2 : =SI(ET(G2="Marketplace";M2>400);M2*5%;0). Résultat : 51 commandes éligibles pour un coût de 1 942,76 €. Avec OU, toutes les commandes marketplace plus toutes celles supérieures à 400 € deviendraient éligibles — plusieurs centaines de lignes, pour un coût sans commune mesure.

🎨 Mise en forme

Exercice 9 · ⭐⭐ moyen

Transformer en tableau structuré

Convertissez « Commandes » en tableau structuré (Ctrl+L), nommez-le T_Commandes, appliquez un style et affichez une ligne des totaux avec la somme des montants.

📑 Feuille : « Commandes »

🔍 Analyse : après conversion, écrivez une formule qui référence une colonne : elle devient T_Commandes[Total_HT] au lieu de M2:M967. En quoi est-ce plus robuste si Datashop ajoute 200 commandes demain ?
Voir la solution
Cliquez dans les données › Ctrl+L › OK. Onglet Création de tableau › nom du tableau + style + cochez « Ligne des totaux ». Un tableau structuré s'étend automatiquement : les nouvelles lignes entrent dans toutes les formules et tous les TCD qui s'y réfèrent, sans rien modifier.
Exercice 10 · ⭐ facile

Mise en forme conditionnelle sur les objectifs

Sur « Objectifs », ajoutez une colonne CA réalisé (reprenez vos SOMME.SI de l'exercice 6) et une colonne Atteinte %. Surlignez en vert les canaux qui atteignent leur objectif et en rouge ceux qui échouent, puis ajoutez des barres de données sur l'atteinte.

🌈 Feuille : « Objectifs »

🔍 Analyse : un seul canal atteint son objectif — mais les trois autres en sont à 96-98 %. Diriez-vous que Datashop a raté ses objectifs, ou qu'ils étaient fixés trop haut ? Comment présenteriez-vous ce résultat à la direction sans être alarmiste ni complaisant ?
Voir la solution
Atteinte : =C2/B2 en format Pourcentage. MFC › Règles de surbrillance › Supérieur à 100 % (vert), Inférieur à 100 % (rouge). Résultats : Téléphone 82 086 € / 75 000 € → ATTEINT (109 %) · Site web 97,5 % · Marketplace 96,9 % · Magasin 96,3 %. Trois canaux frôlent la cible : l'écart global est faible, l'analyse honnête consiste à le dire.
Exercice 11 · ⭐⭐ moyen

Traquer les doublons & poser des icônes

Sur « Commandes », mettez en évidence les valeurs en double de la colonne N_Commande — il ne doit y en avoir aucun. Faites de même sur Client : là, les doublons sont normaux. Puis appliquez un jeu d'icônes (3 flèches) sur les montants.

🌈 Feuille : « Commandes »

🔍 Analyse : sur quelle colonne un doublon est-il une anomalie à corriger, et sur laquelle est-il normal ? Cette distinction est le premier contrôle qualité que fait un analyste avant toute analyse.
Voir la solution
Sélection de la colonne › MFC › Règles de surbrillance › Valeurs en double. Sur N_Commande (identifiant unique), aucun doublon ne doit apparaître : s'il y en avait, cela signalerait une double saisie qui gonflerait artificiellement le CA. Sur Client, les doublons prouvent simplement la fidélité des clients.
Exercice 12 · ⭐⭐ moyen

Faire ressortir le top des commandes

Sur la colonne Total_HT, appliquez une règle « 10 % les plus élevées » (fond orange) puis des nuances de couleurs (dégradé vert). Comptez combien de commandes ressortent et estimez leur part du CA.

🌈 Feuille : « Commandes »

🔍 Analyse : environ 94 commandes ressortent dans le top 10 %. Quelle part du chiffre d'affaires pèsent-elles ? Vérifiez la fameuse règle des 80/20 sur les données réelles de Datashop — et dites ce qu'elle implique pour la relation client.
Voir la solution
MFC › Règles des valeurs plus/moins élevées › 10 % les plus élevées. Les règles s'empilent : gérez leur ordre dans MFC › Gérer les règles. Le top 10 % des commandes pèse près d'un tiers du CA total : perdre dix de ces clients coûterait plus cher que perdre cent petits paniers. C'est l'argument qui justifie un suivi commercial dédié.

📊 Gestion des données

Exercice 13 · ⭐ facile

Filtrer les commandes

Activez les filtres sur « Commandes » et affichez successivement : les commandes du canal Magasin, puis celles de 2026 uniquement, puis celles d'un montant supérieur à 1 000 €. Notez le nombre de lignes affichées à chaque étape (barre d'état en bas).

📑 Feuille : « Commandes »

Voir la solution
Données › Filtrer. Entonnoir de Canal › décochez tout sauf « Magasin ». Pour l'année : entonnoir de Date › Filtres chronologiques. Pour le montant : entonnoir de Total_HT › Filtres numériques › Supérieur à › 1000. La barre d'état affiche « X sur 966 enregistrements trouvés ».
Exercice 14 · ⭐⭐ moyen

Votre premier tableau croisé dynamique

Créez un TCD montrant le CA par canal, puis croisez Canal (lignes) × Année (colonnes). Filtrez pour exclure les commandes annulées.

📑 Feuille : « Commandes »

🔍 Analyse : comparez vos résultats de TCD avec vos SOMME.SI de l'exercice 6. Ils doivent être identiques. S'ils divergent, c'est presque toujours le filtre des annulations qui manque d'un côté.
Voir la solution
Cliquez dans les données › Insertion › Tableau croisé dynamique › OK. Glissez Canal dans Lignes, Total_HT dans Valeurs (Somme), Statut dans Filtres et décochez « Annulée ». Pour l'année : faites un clic droit sur une date › Grouper › Années, puis glissez Années en Colonnes.
Exercice 15 · ⭐⭐ moyen

Remplissage instantané & Analyse rapide

Sur « Clients », créez une colonne « Ville (MAJ) » : tapez la première ville en majuscules puis Ctrl+E. Puis, sur « Commandes », sélectionnez la colonne des montants et utilisez l'Analyse rapide (Ctrl+Q) pour ajouter un total et une mini-courbe.

📑 Feuilles : « Clients » & « Commandes »

Voir la solution
Tapez « LILLE » en face de Lille, puis Ctrl+E : Excel devine la règle et remplit les 140 lignes. Ctrl+Q ouvre l'Analyse rapide : onglets Mise en forme, Graphiques, Totaux, Tableaux, Graphiques sparkline.
Exercice 16 · ⭐⭐ moyen

La Valeur cible

Sur « Valeur_cible », la cellule CA contient =B2*B3 (prix moyen × nombre de commandes). Utilisez la Valeur cible pour trouver combien de commandes seraient nécessaires pour atteindre un CA de 250 000 € à prix moyen constant.

🎯 Feuille : « Valeur_cible »

🔍 Analyse : refaites l'opération en cherchant cette fois le prix moyen nécessaire à nombre de commandes constant. Des deux leviers — vendre plus, ou vendre plus cher — lequel semble le plus réaliste pour Datashop ?
Voir la solution
Données › Analyse de scénarios › Valeur cible. Cellule à définir : la cellule du CA. Valeur à atteindre : 250000. Cellule à modifier : le nombre de commandes (puis, au second essai, le prix moyen). Excel résout l'équation à votre place — c'est un mini-solveur.
Exercice 17 · ⭐⭐⭐ difficile

TCD avancé : % du total, tri et hiérarchie

Créez un TCD CA par catégorie de produit à partir de la feuille « Produits » croisée avec vos données. Affichez les valeurs en % du total général, triez de la plus forte à la plus faible, puis ajoutez le canal en second niveau de lignes.

🧾 Feuilles : « Commandes » & « Produits »

🔍 Analyse : les Écrans pèsent 26 % du CA — de loin la première catégorie. Regardez maintenant la colonne Coût_achat_HT de la feuille Produits : leur marge est-elle à la hauteur de leur poids ? Vous tenez là le sujet du niveau Expert.
Voir la solution
Clic droit dans les valeurs › Afficher les valeurs › % du total général. Tri : clic droit sur une valeur › Trier › Du plus grand au plus petit. Répartition : Écrans 26,1 % · Stockage 18,2 % · Périphériques 17,3 % · Réseau 14,3 % · Accessoires 11,7 % · Services 6,3 % · Consommables 6,2 %.
Exercice 18 · ⭐⭐ moyen

Sous-totaux automatiques par magasin

Copiez la feuille « Commandes », filtrez sur le canal Magasin, triez par Point_de_vente, puis utilisez Données › Sous-total pour obtenir le CA de chaque magasin. Explorez les boutons 1 / 2 / 3 en marge gauche.

🧾 Feuille : « Commandes » (copie)

🔍 Analyse : les trois magasins ont des paniers moyens très proches (472 à 496 €), mais Nantes fait 25 % de commandes de plus que Lille. Est-ce une question de performance commerciale, ou de zone de chalandise ? Quelles données vous manqueraient pour trancher ?
Voir la solution
Clic droit sur l'onglet › Déplacer ou copier › Créer une copie. Triez impérativement par Point_de_vente avant le sous-total. Données › Sous-total › À chaque changement de : Point_de_vente / Somme / Total_HT. Résultats : PDV-NAN 50 948,05 € (108 cmd) · PDV-LYO 43 248,71 € (88) · PDV-LIL 40 668,53 € (82). Il manque la population de la zone et le trafic en magasin pour conclure.

Mission Vos deux livrables

Mission 1 · ⭐⭐⭐ tout le niveau Avancé

Le tableau de bord commercial

Construisez le tableau de bord que la direction consultera chaque mois :

  1. Données converties en tableau structuré nommé, avec ligne de totaux.
  2. Un TCD « CA par canal et par année », annulations exclues, valeurs en % du total.
  3. Une mise en forme conditionnelle (jeu d'icônes ou nuances) sur le TCD.
  4. Un graphique croisé dynamique avec un titre explicite.
  5. Un segment sur le Canal et une chronologie sur la Date pour filtrer d'un clic.
  6. Le tableau « Objectifs vs Réalisé » avec MFC verte/rouge.
  7. En-tête, pied de page et export PDF.

📑 Fichier : datashop-niveau3.xlsx

Indices
Contrôle de cohérence : le total de votre TCD doit afficher 435 900,85 € sur 936 commandes. Si vous lisez 966 commandes, le filtre des annulées manque. La chronologie s'insère par Analyse du TCD › Insérer une chronologie.
Mission 2 · ⭐⭐⭐ mission analyste

La revue commerciale annuelle

La directrice commerciale prépare sa revue et vous confie une question précise : « Où faut-il concentrer nos efforts en 2027 ? » Elle attend une page de conclusions chiffrées, pas un tableau brut.

  1. Un bloc de formules d'analyse : CA total, CA et panier moyen par canal et par type de client (SOMME.SI.ENS, MOYENNE.SI.ENS, NB.SI.ENS), annulations exclues.
  2. Un TCD Canal × Année montrant l'évolution 2025 → 2026 de chaque canal.
  3. Un TCD Catégorie en % du total, trié.
  4. Une MFC « 10 % les plus élevées » pour identifier les commandes stratégiques.
  5. Un graphique croisé dynamique et un segment.
  6. Une zone « Conclusions » avec trois constats chiffrés et une recommandation argumentée.

📊 Feuilles : « Commandes », « Produits », « Clients », « Objectifs »

🔍 Analyse finale : un fait vous saute aux yeux si vous faites le TCD Canal × Année : la télévente explose (+71 %) pendant que le magasin recule (−6 %). Votre recommandation doit trancher : faut-il investir sur ce qui monte, ou sauver ce qui baisse ? Les deux réponses se défendent — mais il faut choisir et l'argumenter.
Indices
Évolutions 2025 → 2026 : Téléphone 30 277 € → 51 809 € (+71,1 %) · Marketplace 24 665 € → 33 449 € (+35,6 %) · Site web 77 044 € → 83 791 € (+8,8 %) · Magasin 69 624 € → 65 241 € (−6,3 %). Le canal le plus rentable au panier (téléphone, 667 €) est aussi celui qui croît le plus vite : c'est l'argument le plus solide. Vérifiez que la somme de vos canaux fait bien 435 900,85 €.

Vous avez tout fait ? Direction le QCM final (40 questions), puis le niveau 4, Expert, où vous relierez les commandes à leur détail ligne par ligne pour calculer les vraies marges de Datashop.