Un planning d'équipe géré dans un fichier bricolé devient vite ingérable, avec des congés saisis à la main et des compteurs que personne ne met à jour. Du coup, le planning court toujours après la réalité. Dans cet exercice, on va voir ensemble comment le rendre vraiment dynamique !
L'objectif est d'apprendre à confier à Excel ce qui se fait d'habitude à la main, faire réagir la couleur à la saisie et laisser les compteurs se calculer tout seuls. Une fois ce réflexe acquis, tu adaptes le planning à la taille de ton équipe sans toucher aux formules, et la logique se transpose à tout suivi récurrent sur une grille.
Ce que tu vas construire
Créer une structure de planning mensuel avec les collaborateurs en lignes et chaque jour du mois en colonne.- Saisir les statuts de présence via des listes déroulantes pour éviter les fautes de frappe.
- Colorer automatiquement chaque cellule selon le statut (présent, congé, télétravail, maladie).
- Compter le nombre de jours de chaque type par collaborateur avec NB.SI.
- Afficher l'effectif disponible par jour et détecter automatiquement les jours fériés.
À connaître avant de commencer
- Savoir recopier une formule vers le bas et vers la droite (toute la grille du planning).
- Avoir déjà utilisé la mise en forme conditionnelle au moins une fois.
Voici les données de départ de cet exercice. Copie-les ou télécharge le fichier Excel, puis entraîne-toi avant de regarder le corrigé.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Collaborateur | Lun 01/03 | Mar 02/03 | Mer 03/03 | Jeu 04/03 | Ven 05/03 |
| 2 | Dupont Marie | P | P | P | P | P |
| 3 | Martin Paul | P | CP | CP | CP | CP |
| 4 | Bernard Sophie | TT | P | P | TT | P |
| 5 | Lefèvre Thomas | P | P | F | F | P |
| 6 | Moreau Julie | M | M | M | M | M |
| 7 | Petit Romain | P | TT | P | P | TT |
| 8 | Garcia Laura | P | P | RTT | P | P |
| 9 | Dubois Antoine | CP | CP | CP | P | P |
| 10 | Rousseau Camille | P | P | P | P | F |
| 11 | Lambert Hugo | TT | TT | P | P | P |
Exercice guidé
Coche chaque étape au fur et à mesure. Tente-la dans ton fichier, puis déplie le corrigé.
Définis les codes : P (présent), CP (congé payé), RTT, M (maladie), TT (télétravail), F (formation). Utilise la validation de données (Données > Validation) pour limiter les saisies à cette liste. Cela évite les fautes de frappe qui faussent les compteurs.
Astuces pour aller plus loin
Grise les week-ends automatiquement
Utilise JOURSEM(B$1;2) dans une règle de mise en forme conditionnelle : si la valeur est supérieure à 5, la colonne est grisée. Tu n'as plus à colorier samedi et dimanche à la main chaque mois.
Gère plusieurs mois sans tout recréer
Duplique l'onglet du mois en cours et mets à jour la date de départ en A1. Toutes les formules qui calculent les jours se recalculent automatiquement. Un onglet récapitulatif peut additionner les compteurs de chaque mois avec des formules comme =Jan!AG2+Fev!AG2.
Questions fréquentes
Crée un tableau avec les collaborateurs en lignes et les jours en colonnes. Utilise JOURSEM() pour afficher le jour de la semaine sous chaque date. Ajoute une liste déroulante de codes (P, CP, RTT, TT) et une mise en forme conditionnelle pour colorier chaque statut. Le fichier corrigé de cet exercice est téléchargeable.
NB.SI est la formule de base : =NB.SI(B2:AF2;"CP") compte le nombre de cellules contenant CP sur la ligne du collaborateur. Répète la formule pour chaque code (RTT, M, TT) dans des colonnes séparées. La plage doit couvrir exactement les jours du mois.
Via Accueil > Mise en forme conditionnelle > Nouvelle règle. Crée une règle par code : si la cellule est égale à "CP", fond rouge ; si égale à "TT", fond bleu. Excel applique la couleur dès la saisie. C'est plus rapide que de colorier à la main et ça résiste aux modifications.
Crée un onglet Feries avec la liste des 11 jours fériés en colonne A. Dans le planning, ajoute une règle de mise en forme conditionnelle basée sur =NB.SI(Feries!$A:$A;B$1)>0 pour griser les colonnes fériées. RECHERCHEV peut aussi afficher "FÉRIÉ" sous la date.
En bas de chaque colonne, additionne deux NB.SI : =NB.SI(B2:B20;"P")+NB.SI(B2:B20;"TT"). Tu obtiens le nombre de personnes présentes ou en télétravail ce jour-là. Si tu ajoutes un code Déplacement, inclus-le dans la somme si la personne est disponible.
Le fichier corrigé de cet exercice est disponible en téléchargement en haut de page. Il contient la structure mensuelle, les codes validés, la mise en forme conditionnelle et les compteurs NB.SI déjà configurés. Remplace les noms et les dates pour l'adapter à ton équipe.
3 exercices similaires au planning d'équipe dynamique
Bulletin de paie simplifié : du brut au net
Pars d'un salaire brut, calcule chaque cotisation salariale ligne par ligne avec ARRONDI, totalise-les avec SOMME et déduis le salaire net en une seule passe.
Voir l'exercice
Suivi des notes de frais
Mets en place un suivi des notes de frais sur Excel : total par catégorie, contrôle des plafonds et total remboursable, à partir d'un relevé de dépenses.
Voir l'exercice
Préparer une base de contacts pour un publipostage
Nettoie une base de contacts à la casse incohérente avec NOMPROPRE, puis construis les champs de fusion (formule d'appel, identifiant) prêts à être repris dans un publipostage.
Voir l'exercice
Envie de t'entraîner plus ?
Des dizaines de cas pratiques Excel corrigés pour progresser pour de vrai.
Voir tous les exercices