Consultant pour Datashop
Vous n'êtes plus stagiaire : Datashop vous mandate comme consultant. On vous ouvre le système complet — en-têtes de commande et lignes de détail, produits avec coûts d'achat, retours, promotions. À ce niveau, on ne vous demande plus de calculer : on vous demande de fiabiliser, automatiser et conclure.
📒 Votre fichier de travail
Tout se passe dans datashop-niveau4.xlsx, plus deux exports CSV pour Power Query : export-commandes.csv et export-lignes-commande.csv.
La nouveauté de ce niveau, c'est la structure relationnelle : une commande n'est plus une ligne, c'est un en-tête plus plusieurs lignes de détail.
⚠️ 966 commandes mais 2 140 lignes : une commande contient en moyenne 2,2 produits. Confondre les deux niveaux fausse tout.
🧭 Environnement & méthodes
Protéger le fichier de production
Sur « Bon_de_commande », laissez modifiables uniquement les colonnes Code produit et Quantité ; verrouillez tout le reste, puis protégez la feuille. Vérifiez qu'on ne peut plus écraser une formule.
🔐 Feuille : « Bon_de_commande »
Voir la solution
Calcul multi-feuilles
Créez une feuille « Synthèse » et calculez-y, par formules pointant vers les autres feuilles : le nombre de commandes, le nombre de lignes de détail, le nombre de clients, le nombre de produits et le nombre de retours. Vérifiez la cohérence des ordres de grandeur.
🗂️ Toutes les feuilles du classeur
Voir la solution
=NBVAL(Commandes!A2:A967) → 966 · =NBVAL(Lignes_commande!A:A)-1 → 2 140 · Clients 140 · Produits 46 · Retours 42. Le ratio 2 140 / 966 = 2,22 lignes par commande est votre premier contrôle de cohérence.Enregistrer une macro de mise en forme
Affichez l'onglet Développeur, puis enregistrez une macro MiseEnFormeEntete qui applique à la ligne sélectionnée : gras, fond vert foncé, police blanche, centré et bordure. Exécutez-la sur une autre feuille.
Voir la solution
.xlsm, sinon la macro est perdue à la fermeture.Validation de données : verrouiller la saisie
Sur « Bon_de_commande » : transformez la colonne Code produit en liste déroulante alimentée par les références de la feuille Produits, et imposez sur Quantité un nombre entier entre 1 et 100 avec un message d'erreur personnalisé. Testez une saisie interdite.
🛡️ Feuilles : « Bon_de_commande » & « Produits »
XXX-999 du bon de commande : avec une liste déroulante, aurait-il pu être saisi ?Voir la solution
Un bouton pour la macro
Insérez une forme (rectangle arrondi) portant le texte « Nouvelle commande » et affectez-lui une macro qui efface les zones de saisie du bon de commande. Testez-la.
🔘 Feuille : « Bon_de_commande »
Voir la solution
🧮 Calculs : fonctions expertes
Fonctions de date sur les livraisons
Sur « Commandes », ajoutez trois colonnes : Mois (nom du mois de la commande), Jour semaine (lundi = 1) et Délai de livraison en jours (date de livraison − date de commande). Attention aux commandes non livrées.
📅 Feuille : « Commandes »
Voir la solution
=TEXTE(B2;"mmmm"). Jour : =JOURSEM(B2;2). Délai : =SI(K2="";"";K2-B2) — le test évite #VALEUR! sur les commandes annulées ou en préparation, qui n'ont pas de date de livraison.Fonctions de texte sur les clients
Sur « Clients », extrayez le domaine de chaque email (ce qui suit le @) et créez un identifiant court composé des 3 premières lettres du nom en majuscules suivies des 4 chiffres de l'ID client.
🔤 Feuille : « Clients »
Voir la solution
=DROITE(D2;NBCAR(D2)-TROUVE("@";D2)). Identifiant : =MAJUSCULE(GAUCHE(C2;3))&DROITE(A2;4). Comptez ensuite les domaines distincts : les clients professionnels utilisent @pro.fr, les particuliers @mail.fr — un moyen rapide de vérifier la cohérence du fichier.ARRONDI & ENT sur les remises
Sur « Lignes_commande », calculez le montant de la remise de chaque ligne (Prix × Quantité × Remise%), arrondi à 2 décimales. Créez à côté une colonne avec ENT et comparez les totaux des deux colonnes.
🔢 Feuille : « Lignes_commande »
ARRONDI et jamais une troncature.Voir la solution
=ARRONDI(E2*D2*F2/100;2) contre =ENT(E2*D2*F2/100). ENT tronque vers le bas systématiquement : sur des milliers de lignes, le manque à gagner devient significatif et, surtout, le total ne correspond plus à la somme des factures individuelles.Recherche INDEX / EQUIV
Créez une petite zone de consultation : on saisit une référence produit et la formule retrouve sa désignation, sa catégorie et son prix de vente avec INDEX/EQUIV. Changez la référence et vérifiez la mise à jour.
🔎 Feuille : « Produits »
Voir la solution
=INDEX(B:B;EQUIV($J$1;A:A;0)) pour la désignation. EQUIV trouve le numéro de ligne de la référence, INDEX y lit la valeur de la colonne voulue. Avantage sur RECHERCHEV : la colonne cherchée peut être à gauche de la colonne de recherche.RECHERCHEV : compléter le bon de commande
Sur « Bon_de_commande », la colonne Prix unitaire doit aller chercher automatiquement le prix dans la feuille Produits à partir du code, puis Total HT calcule la ligne. Ajoutez en bas le total HT et le total TTC (TVA 20 %).
🔎 Feuilles : « Bon_de_commande » & « Produits »
#N/A. Laquelle, et pourquoi ? Expliquez en quoi c'est une bonne nouvelle qu'Excel refuse de calculer plutôt que d'afficher 0.Voir la solution
C2 : =RECHERCHEV(A2;Produits!$A:$E;5;FAUX) — le FAUX impose une correspondance exacte. Résultats : STO-302 99,00 € ×3 = 297,00 € · ECR-201 149,00 € ×2 = 298,00 € · PER-105 45,00 € ×5 = 225,00 € · XXX-999 → #N/A · ACC-501 42,90 € ×4 = 171,60 € · CON-603 7,90 € ×10 = 79,00 €. Total 1 070,60 € HT, soit 1 284,72 € TTC. Le code XXX-999 n'existe pas au catalogue : un 0 silencieux aurait faussé la facture sans alerter personne.SIERREUR : blinder la recherche
Améliorez la formule précédente avec SIERREUR : si le code est introuvable, affichez « Code inconnu » au lieu de #N/A. Faites de même sur la colonne Total pour éviter l'erreur en cascade, et vérifiez que le total général reste juste.
🛡️ Feuille : « Bon_de_commande »
0, le total général devient faux sans que personne ne s'en aperçoive. Quand choisir un texte d'alerte, quand choisir 0 ?Voir la solution
=SIERREUR(RECHERCHEV(A2;Produits!$A:$E;5;FAUX);"Code inconnu"). Règle d'or : un texte d'alerte quand une action humaine est attendue (corriger le code) ; un 0 uniquement quand l'absence de valeur est normale et documentée. Ici, un 0 masquerait une erreur de saisie.La jointure : calculer les vraies marges
C'est l'exercice clé du niveau. Sur « Lignes_commande », ajoutez une colonne Coût_achat qui va chercher le coût dans la feuille Produits (RECHERCHEV), puis Marge_ligne = Total ligne − (Coût × Quantité). Calculez la marge totale et le taux de marge par catégorie.
💰 Feuilles : « Lignes_commande » & « Produits »
Voir la solution
=RECHERCHEV(C2;Produits!$A:$D;4;FAUX). Marge : =G2-(H2*D2). Résultats globaux : CA 435 184,05 € − coût 236 840,00 € = marge 198 344,05 €, soit un taux de 45,6 %. Par catégorie : Services 69,3 % · Accessoires 49,9 % · Périphériques 48,9 % · Consommables 47,2 % · Réseau 44,0 % · Stockage 41,9 % · Écrans 38,8 %. Les écrans font 26 % du CA mais affichent la pire marge ; les services ne pèsent que 6 % du CA mais rapportent proportionnellement le plus.Analyser les retours produits
Sur « Retours » : calculez le montant total remboursé, le taux de retour rapporté au CA, et le classement des motifs (NB.SI). Identifiez ensuite le produit le plus retourné.
↩️ Feuille : « Retours »
Voir la solution
=SOMME(G:G) → 7 815,10 €, soit 1,79 % du CA (435 900,85 €). Motifs : Colis endommagé 11 · Erreur de référence 9 · Produit défectueux 7 · Ne correspond pas à l'attente 5 · Rétractation 4 · Délai 3 · Doublon 3. Le premier motif relève de l'emballage et du transport, pas de la qualité produit : la recommandation va au service logistique, et le gain potentiel est de l'ordre de 2 000 € par an.Ancienneté et primes : DATEDIF + SI imbriqués
Sur « Salaries », calculez l'ancienneté en années au 31/12/2026 avec DATEDIF, puis la prime d'ancienneté : 6 % du salaire si ≥ 10 ans, 3 % si ≥ 5 ans, 0 sinon. Terminez par le coût total et l'ancienneté moyenne.
💼 Feuille : « Salaries »
Voir la solution
=DATEDIF(E2;DATE(2026;12;31);"y"). Prime : =SI(H2>=10;F2*6%;SI(H2>=5;F2*3%;0)) — du plus exigeant au moins exigeant. Résultats : 5 salariés ≥ 10 ans → 846,00 € · 4 salariés de 5 à 9 ans → 267,30 € · 3 salariés < 5 ans → 0 €. Coût total : 1 113,30 €. Ancienneté moyenne : 8 ans. Dans l'ordre inverse, SI s'arrête au premier test vrai : 15 ans étant « ≥ 5 », le salarié touche 3 %.Nettoyer un import de contacts
La feuille « Import_a_nettoyer » contient 8 contacts mal saisis (espaces parasites, casse incohérente). Créez trois colonnes propres avec SUPPRESPACE, NOMPROPRE et MINUSCULE, puis une colonne Fiche assemblant « Nom — Ville » avec l'opérateur &.
🧹 Feuille : « Import_a_nettoyer »
NB.SI sur « paris » : combien de villes distinctes Excel croit-il voir ? C'est LA raison pour laquelle un analyste nettoie toujours avant de croiser.Voir la solution
=NOMPROPRE(SUPPRESPACE(A2)). Ville : =NOMPROPRE(SUPPRESPACE(B2)). Email : =MINUSCULE(SUPPRESPACE(C2)). Fiche : =D2&" — "&E2. Après nettoyage, il ne reste que 5 villes réelles : Lille, Lyon, Marseille, Nantes, Paris. Avant, un TCD en aurait compté le double à cause des espaces et des majuscules. Finalisez par un collage spécial Valeurs.🎨 Mise en forme
Mise en forme conditionnelle par formule
Sur « Commandes », colorez en rouge toute la ligne des commandes annulées et en orange celles retournées, à l'aide de règles par formule. Puis surlignez en vert les commandes dont le montant dépasse 1 500 €.
🌈 Feuille : « Commandes »
Voir la solution
=$F2="Annulée" › remplissage rouge. Le $ devant F fige la colonne ; l'absence de $ devant 2 laisse la ligne s'adapter. Répétez avec =$F2="Retournée". Contrôle : 30 lignes rouges, 42 oranges.MFC par formule : repérer les livraisons hors délai
Toujours sur « Commandes », surlignez en orange les commandes dont le délai de livraison dépasse 5 jours (colonne calculée à l'exercice 6), avec une seule règle par formule. Comptez-les.
⏱️ Feuille : « Commandes »
Voir la solution
=ET($L2<>"";$L2>5) (en supposant le délai en colonne L) › remplissage orange. Le premier test évite de colorer les lignes sans date de livraison. Seuls 3 retours invoquent le délai : le problème logistique de Datashop est l'emballage, pas la rapidité.📊 Gestion des données
Power Query : la vraie jointure
Dans un classeur vierge : Données › Obtenir des données › À partir d'un fichier texte/CSV pour importer export-commandes.csv et export-lignes-commande.csv. Dans l'éditeur Power Query, fusionnez les deux requêtes sur N_Commande, développez les colonnes utiles, puis chargez le résultat.
⚡ Fichiers : les deux exports CSV
Voir la solution
;, encodage UTF-8). Accueil › Fusionner les requêtes › sélectionnez N_Commande dans les deux tables › type de jointure Externe gauche. Développez la colonne fusionnée en décochant les colonnes inutiles. Vous obtenez 2 140 lignes enrichies du canal et du statut de leur commande — impossible à faire proprement avec des RECHERCHEV en cascade.Mission Vos deux livrables
Le tableau de bord de rentabilité
La direction ne veut plus un tableau de bord du chiffre d'affaires, mais de la marge :
- Jointure Lignes_commande × Produits pour obtenir coût et marge de chaque ligne.
- Un TCD « CA et marge par catégorie » avec le taux de marge en champ calculé.
- Un graphique croisé dynamique comparant CA et marge par catégorie.
- Deux segments (Catégorie, Canal) et une chronologie.
- Une MFC par formule signalant les catégories sous 45 % de marge.
- Feuille protégée, en-tête/pied de page, export PDF.
💰 Fichier : datashop-niveau4.xlsx
Indices
L'outil de facturation fiable
Datashop vous commande un outil de facturation utilisable par quelqu'un qui ne connaît pas Excel. Cahier des charges :
- L'utilisateur choisit un produit dans une liste déroulante et saisit une quantité — tout le reste se calcule seul (RECHERCHEV + SIERREUR, remise, total HT, TVA, TTC arrondi à 2 décimales).
- Les cellules de formules sont verrouillées, la feuille protégée ; seules les saisies restent libres.
- Une MFC par formule signale en orange toute ligne dont la quantité dépasse 10 (validation d'un responsable requise).
- Un bouton macro « Nouvelle facture » qui vide les zones de saisie.
- Une feuille « Historique » alimentée par Power Query depuis les exports CSV.
- Testez comme un utilisateur maladroit : code inconnu, quantité négative, saisie dans une cellule verrouillée, suppression d'une formule. Rien ne doit casser. Export d'un exemple en PDF.
🧾 Feuilles : « Bon_de_commande », « Produits », « Import_a_nettoyer »
Indices
=ARRONDI(total*1,2;2). Déverrouillez les cellules de saisie avant de protéger la feuille (exercice 1). Pour la liste déroulante, pointez vers la colonne Référence de la feuille Produits. Contrôle sur le bon fourni : 1 070,60 € HT et 1 284,72 € TTC, avec la ligne XXX-999 correctement signalée.Vous avez tout fait ? Direction le QCM final (40 questions) pour valider les 4 compétences — et clore le parcours Excel. Vous savez désormais faire ce qu'on attend d'un profil TOSA Expert : relier des tables, calculer une rentabilité réelle et livrer un outil que d'autres peuvent utiliser.