Trois classeurs, trois situations d'expert
Ici, on ne coche plus de cases : on travaille dans Excel, on enregistre, et on dépose son fichier. Le classeur est lu dans ton navigateur — il ne part sur aucun serveur — et chaque point du barème est vérifié un par un, avec le geste exact à refaire quand ce n'est pas bon.
Comment se passe un atelier. ① Tu télécharges le classeur de départ. ② Tu fais le travail demandé dans Excel, et tu enregistres en .xlsx. ③ Tu déposes le fichier dans le cadre prévu : la correction s'affiche aussitôt, ligne par ligne. Tu peux redéposer autant de fois que nécessaire — c'est la meilleure tentative qui compte. ④ Tu rends ton classeur à ton formateur, avec le bouton sous l'atelier.
Le catalogue relié
Tableau nommé, références structurées, INDEX et EQUIV, ESTNA, REMPLACER, Consolider, arrière-plan · séances 1 et 2Un client professionnel envoie un bon de douze références, dont une qui n'existe pas au catalogue. Il faut un vrai Tableau, des recherches qui ne casseront pas quand le catalogue changera, une consolidation des ventes des trois magasins par catégorie, et une colonne de numéros de commande réécrits pour la nouvelle année.
Ce qu'on attend de toi
| # | À faire | Où |
|---|---|---|
| 1 | Transformer le bon en Tableau nommé Bon (Ctrl+L, puis Nom du tableau) — à faire en premier | Bon!A1:E13 |
| 2 | La ligne Total du Tableau, avec la somme de Montant | Bon, ligne 14 |
| 3 | Le montant de chaque ligne en références structurées (=[@Prix_HT]*[@Quantité]) | Bon!E2:E13 |
| 4 | La désignation, par INDEX et EQUIV dans Catalogue (colonne B) | Bon!B2:B13 |
| 5 | Le prix, par INDEX et EQUIV (colonne E, Prix_vente_HT) | Bon!C2:C13 |
| 6 | La référence inconnue de la dernière ligne affiche « inconnu » : SI(ESTNA(…)) autour de la recherche | Bon!B13 |
| 7 | Les numéros de commande réécrits avec l'année 2026 par REMPLACER (caractères 5 à 8) | Import!B2:B31 |
| 8 | Consolider les ventes de Lille, Nantes et Lyon par catégorie, à partir de A1 | Synthese |
| 9 | Un arrière-plan (image) sur la feuille de synthèse | Synthese |
| 10 | Prix et montants au format monétaire | Bon!C2:E14 |
Commence par le Tableau. Une fois le bon converti, Excel écrit lui-même [@Référence] quand tu cliques une cellule de la ligne dans une formule, et chaque formule se propage à toute la colonne. Le correcteur accepte aussi les références classiques (A2) dans les recherches, mais exige les références structurées pour le montant.
La consolidation lit les étiquettes. Les trois magasins n'ont pas leurs catégories dans le même ordre, et il en manque une à Nantes : coche Colonne de gauche (et Ligne du haut), sinon les totaux s'additionnent ligne à ligne, dans le désordre.
Le tableau de bord des ventes
Tableau croisé à deux calculs, segment et chronologie, nom défini, table de données, Solveur, scénarios, format personnalisé · séances 3 et 4La direction veut un tableau de bord des ventes 2026 filtrable par statut et par période, le chiffre d'affaires sous un nom parlant, et deux simulations : la mensualité d'un prêt selon le taux, et le meilleur plan d'achat possible avec un budget de 20 000 € sur vingt produits, par le Solveur. Les hypothèses du prêt seront enregistrées en scénarios.
Ce qu'on attend de toi
| # | À faire | Où |
|---|---|---|
| 1 | Un tableau croisé sur le Tableau Ventes, nouvelle feuille : Canal en Lignes, Mois en Colonnes, Total_HT deux fois en Valeurs (Somme et Moyenne) | nouvelle feuille |
| 2 | Un segment Statut sur ce tableau croisé | la feuille du tableau croisé |
| 3 | Une chronologie sur Date | la feuille du tableau croisé |
| 4 | Un nom CA_2026 sur la colonne Total_HT de Ventes, et =SOMME(CA_2026) | Synthese!B1 |
| 5 | Le CA 2026 doit être juste (le correcteur vérifie le résultat) | Synthese!B1 |
| 6 | Une table de données à une entrée : la mensualité (formule en D1) pour chaque taux de C2:C8, cellule d'entrée en colonne B1 | Modele!C1:D8 |
| 7 | Le Solveur : quantités à commander entières, de 0 au stock maximum, pour une dépense qui ne dépasse pas le budget | Achat!F2:F21 |
| 8 | …et qui maximise la marge totale (H23) : le correcteur compare à l'optimum | Achat |
| 9 | Trois scénarios sur le taux et la durée du prêt (B1:B2), puis la Synthèse avec la mensualité en cellule résultante | Modele |
| 10 | Le CA 2026 affiché en k€ par un format personnalisé (# ##0 " k€") | Synthese!B1 |
Le Solveur. Charge le complément si besoin (Fichier › Options › Compléments › Atteindre). Objectif : H23 à maximiser. Variables : F2:F21. Contraintes : G23 <= J2, F2:F21 <= E2:E21, F2:F21 = ent, variables non négatives. Méthode Simplex PL. La marge optimale est de 18 585,70 €.
La table de données ne se tape pas. Sélectionne C1:D8 (formule comprise), Données › Analyse de scénarios › Table de données, cellule d'entrée en colonne : B1. Des VPM recopiés à la main ne sont pas acceptés.
Le plan de financement
VPM, NPM, INTPER, PRINCPER, NB.JOURS.OUVRES, SERIE.JOUR.OUVRE, DATEDIF, ESTNA, Power Query, pyramide des âges, contrôle de formulaire · séances 4 et 5Datashop emprunte 15 000 € pour renouveler son matériel : il faut la mensualité, le tableau d'amortissement, et la durée si l'on remboursait 400 € par mois. Les livraisons se mesurent en jours ouvrés, les salariés ont un âge et une ancienneté, les références saisies se contrôlent, l'export du mois s'importe par Power Query, et la direction veut une pyramide des âges et une case à cocher pour piloter la synthèse.
Ce qu'on attend de toi
| # | À faire | Où |
|---|---|---|
| 1 | La mensualité par VPM (taux mensuel, capital en négatif) | Pret!B5 |
| 2 | Le nombre de mois pour 400 € par mois, par NPM | Pret!B6 |
| 3 | Les intérêts de chaque période par INTPER | Pret!B9:B56 |
| 4 | Le capital remboursé de chaque période par PRINCPER | Pret!C9:C56 |
| 5 | Le délai en jours ouvrés entre commande et livraison, fériés de Feries déduits | Livraisons!D2:D41 |
| 6 | L'échéance de paiement, 30 jours ouvrés après la commande | Livraisons!E2:E41 |
| 7 | L'âge et l'ancienneté en années révolues, par DATEDIF | Salaries!G2:H41 |
| 8 | La désignation de chaque référence saisie, « inconnu » si elle n'existe pas (SI(ESTNA(…))) | Codes!B2:B13 |
| 9 | Importer atelier-mastere-3r-export.csv par Power Query (Obtenir des données › Texte/CSV) | nouvelle feuille |
| 10 | Une pyramide des âges : effectifs hommes et femmes par tranche sur Effectifs, barres empilées, hommes en négatif | Effectifs |
| 11 | Une case à cocher (contrôle de formulaire) liée à B1 | Parametres |
Les signes des fonctions financières. Excel raisonne en flux : le capital reçu et les mensualités versées ont des signes opposés. =VPM(B1/12;B2;-B3) donne une mensualité positive ; =NPM(B1/12;-400;B3) compte les mois. Et le taux est toujours par période : B1/12.
Télécharge aussi l'export (atelier-mastere-3r-export.csv) et garde-le à côté du classeur : la requête Power Query en a besoin. Le correcteur vérifie la présence de la requête, pas son chemin.
Les trois ateliers validés ? Il reste le QCM — 40 questions sur les quatre domaines — puis l'examen blanc, chronométré comme le jour de la certification.