Planning de production sur Excel
Un exercice corrigé pour bâtir un planning de production avec SERIE.JOUR.OUVRE, calculer les dates de livraison en jours ouvrés et détecter les retards, avec le fichier Excel à télécharger.
Planifier la production sans outil adapté, c'est jongler avec des post-its, et un jour férié oublié suffit à promettre une commande pour une date qui n'existe pas. Dans cet exercice, on va voir ensemble comment calculer les délais en jours ouvrés et faire signaler les retards par Excel lui-même.
L'objectif est de transformer une date de commande et un délai en une date de livraison fiable, puis de croiser cette échéance avec la charge des machines pour anticiper les goulots. Une fois ce réflexe de calcul en jours ouvrés acquis, il ressert dès qu'une échéance doit sauter les week-ends et les fermetures, du carnet de commandes au rétroplanning projet !
Ce que tu vas construire
- Calculer la date de livraison prévisionnelle de chaque ordre de fabrication en tenant compte des jours ouvrés et des jours fériés.
- Détecter automatiquement les retards et afficher le nombre de jours ouvrés de décalage.
- Récupérer les caractéristiques produit (machine, temps de fabrication) depuis une table de gammes.
- Calculer la charge de chaque machine sur la période et identifier les surcharges avant qu'elles ne bloquent la production.
À connaître avant de commencer
- Savoir figer une plage de référence avec les $ avant de recopier la formule.
- Avoir déjà travaillé avec des dates dans Excel (les dates sont des nombres pour Excel).
Voici les données de départ, réparties sur 4 onglets comme dans le fichier (clique sur un onglet sous le tableau pour changer de feuille). Copie-les ou , puis entraîne-toi avant de regarder le corrigé.
| A | B | C | D | E | F | G | H | I | J | K | L | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Réf OF | Produit | Qté | Date commande | Délai (j.o.) | Date souhaitée | Priorité | Livraison prévue | Retard | Machine | Charge en heures | Alerte capacité |
| 2 | OF-2026-001 | Pièce A | 500 | 27/04/2026 | 8 | 06/05/2026 | Haute | |||||
| 3 | OF-2026-002 | Pièce B | 200 | 28/04/2026 | 5 | 07/05/2026 | Normale | |||||
| 4 | OF-2026-003 | Assemblage C | 100 | 28/04/2026 | 12 | 13/05/2026 | Haute | |||||
| 5 | OF-2026-004 | Pièce D | 350 | 30/04/2026 | 6 | 12/05/2026 | Normale | |||||
| 6 | OF-2026-005 | Sous-ensemble E | 80 | 04/05/2026 | 15 | 05/06/2026 | Basse | |||||
| 7 | OF-2026-006 | Pièce A | 300 | 05/05/2026 | 8 | 22/05/2026 | Haute | |||||
| 8 | OF-2026-007 | Composant F | 1 000 | 06/05/2026 | 3 | 13/05/2026 | Normale | |||||
| 9 | OF-2026-008 | Assemblage G | 50 | 07/05/2026 | 10 | 20/05/2026 | Haute | |||||
| 10 | OF-2026-009 | Pièce B | 400 | 11/05/2026 | 5 | 26/05/2026 | Basse | |||||
| 11 | OF-2026-010 | Kit finition H | 150 | 12/05/2026 | 7 | 21/05/2026 | Normale |
| A | B | |
|---|---|---|
| 1 | Date | Motif |
| 2 | 01/01/2026 | Jour de l'an |
| 3 | 06/04/2026 | Lundi de Pâques |
| 4 | 01/05/2026 | Fête du Travail |
| 5 | 08/05/2026 | Victoire 1945 |
| 6 | 14/05/2026 | Ascension |
| 7 | 15/05/2026 | Pont de l'Ascension (fermeture usine) |
| 8 | 25/05/2026 | Lundi de Pentecôte |
| 9 | 14/07/2026 | Fête nationale |
| 10 | 15/08/2026 | Assomption |
| 11 | 01/11/2026 | Toussaint |
| 12 | 11/11/2026 | Armistice 1918 |
| 13 | 25/12/2026 | Noël |
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Produit | Machine | Temps unitaire (h/pièce) | Taux de rebut |
| 2 | Pièce A | Tour CN | 0,1 | 0,02 |
| 3 | Pièce B | Presse | 0,05 | 0,01 |
| 4 | Assemblage C | Poste montage | 0,5 | 0,03 |
| 5 | Pièce D | Tour CN | 0,12 | 0,02 |
| 6 | Sous-ensemble E | Poste montage | 0,75 | 0,04 |
| 7 | Composant F | Presse | 0,02 | 0,05 |
| 8 | Assemblage G | Poste montage | 1,2 | 0,03 |
| 9 | Kit finition H | Poste montage | 0,2 | 0,01 |
| A | B | |
|---|---|---|
| 1 | Machine | Capacité (h) |
| 2 | Tour CN | 105 |
| 3 | Presse | 70 |
| 4 | Poste montage | 200 |
Résoudre l'exercice étape par étape
Coche chaque étape au fur et à mesure. Tente-la dans ton fichier, puis déplie le corrigé.
Astuces pour aller plus loin
Ajoute les fermetures usine dans la liste des fériés
SERIE.JOUR.OUVRE et NB.JOURS.OUVRES acceptent une liste de dates à exclure en troisième argument. L'onglet Feries du fichier contient déjà le pont de l'Ascension. Fais de même avec les congés d'été et les fermetures annuelles. Comme les formules lisent la plage A2:A30, une date ajoutée au bas de la liste est prise en compte sans toucher aux formules.
Trie par priorité avant d'affecter les machines
Quand une machine est en surcharge, la priorité décide quels ordres passent en premier et lesquels décaler. Trie la liste par priorité puis par date de commande avec Données > Trier, en choisissant l'ordre Haute, Normale, Basse dans une liste personnalisée, car un tri alphabétique rangerait Basse avant Haute.
Intègre le taux de rebut dès le lancement
La colonne D de la table des gammes donne le taux de rebut de chaque produit, la part des pièces fabriquées que le contrôle écarte. Pour obtenir la quantité commandée en pièces bonnes, divise-la par (1 - taux) et arrondis au-dessus avec =ARRONDI.SUP(C2/(1-RECHERCHEV(B2;Gammes!$A:$D;4;FAUX));0). Pour OF-2026-001, il faut lancer 511 pièces A, et non 510 comme le donnerait une multiplication par (1 + taux), car 510 pièces avec 2 % de rebut ne donnent que 499,8 pièces bonnes en moyenne. Tu évites ainsi le retour en production de dernière minute.
S'entraîner sur 3 exercices similaires au planning de production
Gestion des stocks et réappro
Construire un tableau de gestion des stocks qui calcule le stock disponible, déclenche les alertes de réappro et identifie les surstocks. Gérer 200 références sans stress.
Voir l'exercice
Calcul de rentabilité projet
Calculer la rentabilité de tes projets en comparant les coûts réels avec le budget initial. Savoir en temps réel si un projet gagne ou perd de l'argent.
Voir l'exercice
Créer un graphique d'évolution dans Excel
Transformer un tableau de chiffre d'affaires mensuel en trois graphiques clairs, un histogramme, une courbe d'évolution et un camembert de répartition.
Voir l'exercice
Tu as peut-être encore une question
Utilise SERIE.JOUR.OUVRE(date_début;nb_jours;fériés). Elle ajoute exactement le nombre de jours ouvrés demandé en sautant les week-ends et les jours fériés de ta liste. Ne confonds pas avec NB.JOURS.OUVRES, qui compte les jours entre deux dates au lieu d'en calculer une.
Crée un tableau avec un ordre de fabrication par ligne : référence, produit, date de commande, délai. SERIE.JOUR.OUVRE calcule la date de livraison, SI signale les retards, SOMME.SI totalise la charge par machine. Ajoute une table des jours fériés dans un onglet séparé.
Liste toutes les dates à exclure dans un onglet dédié (11 fériés nationaux + tes fermetures usine). Passe cette plage en troisième argument de SERIE.JOUR.OUVRE. Les dates de livraison sautent automatiquement ces jours sans aucune correction manuelle.
Ajoute une colonne qui calcule les heures de charge de chaque ordre (quantité x temps unitaire). Totalise par machine avec SOMME.SI, puis compare avec la capacité disponible dans un SI. Dans l'exercice, =SI(SOMME.SI($J:$J;J2;$K:$K)>RECHERCHEV(J2;Machines!$A:$B;2;FAUX);"SURCHARGE";"OK") signale ainsi les trois OF du Tour CN.
Le plus souvent, une date de départ ou une date de la liste des fériés est un texte qu'Excel ne sait pas lire comme une date, par exemple 27.04.2026 écrit avec des points. Une vraie date s'affiche à droite dans la cellule. Une date calée à gauche est un texte, à convertir avant de calculer, même si Excel parvient encore à lire certains textes, comme 27/04/2026.
Clique sur le bouton de téléchargement au-dessus du corrigé. Le fichier contient les ordres de fabrication, la table des gammes, la capacité des machines et la liste des fériés 2026. Change les références produit et les capacités machines pour l'adapter à ton atelier.
Pour aller plus loin
Tout ce dont tu as besoin pour continuer à t’entraîner se trouve ici.
Récupère notre modèle de gestion de stock
Télécharger le fichier
Ton cours Excel gratuit
3 chapitres pour apprendre les bases et découvrir l'automatisation.
Voir le cours
Télécharge notre cours Excel complet en PDF
Télécharger
Le quiz qui révèle ton vrai niveau
Faire le test
Le dictionnaire des formules Excel
Chercher une formule