Planning d'équipe dynamiqueCas pratique Excel pas à pas
Le contexte
Construis un planning d'équipe visuel et dynamique qui s'adapte automatiquement aux congés, aux jours fériés et aux rotations.
Les données de départ
Crée un tableau avec les collaborateurs en lignes et les jours du mois en colonnes. Les en-têtes de colonnes affichent la date et le jour de la semaine.
| 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 |
Solution pas à pas
2Coder les statuts
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.
3Colorer automatiquement les cellules
Utilise la mise en forme conditionnelle pour colorer les cellules selon le statut : vert pour P, bleu pour TT, rouge pour CP, orange pour M. Va dans Accueil > Mise en forme conditionnelle > Nouvelle règle, puis crée une règle par code. Le planning devient lisible en un coup d'œil.
4Calculer les compteurs par collaborateur
Utilise NB.SI pour compter le nombre de jours de chaque type par collaborateur. Place ces compteurs dans des colonnes à droite du planning (une colonne par type d'absence).
=NB.SI(B2:AF2;"CP")Résultat attendu : Le nombre de jours de congé payé pour le collaborateur sur le mois (ex: 5 jours de CP).
Attention
- La plage B2:AF2 doit couvrir exactement les jours du mois. Si ton mois a 28 jours, ne compte pas les colonnes vides sinon NB.SI pourrait inclure des cellules d'un autre mois.
5Afficher l'effectif présent par jour
Utilise NB.SI pour compter le nombre de personnes présentes (P ou TT) chaque jour. Additionne les deux comptages. Place ce total en bas de chaque colonne jour pour voir l'effectif disponible.
=NB.SI(B2:B20;"P")+NB.SI(B2:B20;"TT")Résultat attendu : Le nombre de personnes présentes (au bureau ou en télétravail) ce jour-la (ex: 8 sur 10).
Attention
- Si tu ajoutes un nouveau code (ex: "D" pour déplacement), pense à l'ajouter dans ce comptage si la personne est considérée comme disponible.
6Gérer les jours fériés
Crée une table de jours fériés dans un onglet séparé (liste les dates dans la colonne A) et utilise RECHERCHEV pour marquer automatiquement les jours fériés dans le planning. La formule vérifie si la date de l'en-tête existe dans la liste des fériés.
=SI(SIERREUR(RECHERCHEV(B1;Feries!$A:$A;1;FAUX);0)<>0;"FÉRIÉ";"")Résultat attendu : "FÉRIÉ" si la date est un jour férié, sinon une cellule vide.
Questions fréquentes
Excel gère sans problème un planning de 50 à 100 collaborateurs. Au-delà, les formules de comptage peuvent ralentir le fichier. Pour plus de 100 personnes, un outil SIRH dédié est plus adapté.
Crée un onglet par mois (Jan, Fev, Mar...). Duplique la structure et mets à jour les dates en en-têtes. Les compteurs annuels dans un onglet récapitulatif tirent les données de chaque mois avec des formules comme =Jan!AG2+Fev!AG2+Mar!AG2.
Utilise JOURSEM(B1;2) pour détecter le jour de la semaine. Les valeurs 6 et 7 correspondent à samedi et dimanche. Colore ces colonnes en gris avec une mise en forme conditionnelle basée sur =JOURSEM(B$1;2)>5.
Utilise le mode co-édition de OneDrive/SharePoint si tu es en Microsoft 365. Sinon, protège les cellules déja remplies (Protection de feuille) et laisse chaque responsable modifier uniquement les lignes de son équipe.
Oui. Crée un onglet 'Soldes' avec le droit initial et le cumul des jours pris (via NB.SI sur le planning). Affiche le solde restant : =Droit_initial - NB.SI(Planning!B2:AF2;"CP"). Le collaborateur voit en temps réel combien il lui reste.
Remplace les codes P/CP par les codes d'équipe (M pour matin, S pour soir, N pour nuit). Ajoute une rotation automatique avec MOD() pour alterner les équipes chaque semaine. Le principe reste le même.
Cas pratiques similaires
Suivi des congés et absences
Mets en place un suivi complet des congés et absences de ton équipe : soldes en temps réel, calcul d'ancienneté et alertes automatiques quand un collaborateur approche de la limite.
Analyse de la masse salariale
Analyse ta masse salariale en détail : répartition par service, évolution mensuelle, coût moyen par salarié et projections. Tu passes de la paie brute à une vision stratégique.
Comparaisons liées
RECHERCHEV vs RECHERCHEX
RECHERCHEX remplace RECHERCHEV mais n'est pas disponible partout.
INDEX vs RECHERCHEV
INDEX/EQUIV est plus flexible que RECHERCHEV, mais plus complexe à écrire.
SI vs SI.CONDITIONS
SI.CONDITIONS remplace les SI imbriqués par une syntaxe plus lisible.
NB.SI vs NB.SI.ENS
NB.SI.ENS gère plusieurs critères là où NB.SI n'en accepte qu'un seul.
SOMME.SI vs NB.SI
SOMME.SI additionne des valeurs selon un critère, NB.SI se contente de les compter.
CONCATENER vs CONCAT
CONCAT remplace CONCATENER avec en bonus la prise en charge des plages.
CONCATENER vs JOINDRE.TEXTE
JOINDRE.TEXTE fait tout ce que CONCATENER fait, avec un séparateur intégré et la gestion des vides.
EXACT vs SI
EXACT compare deux textes en respectant la casse, SI teste une condition et renvoie un résultat.
NB.SI vs FILTRE
NB.SI compte les lignes qui matchent, FILTRE les extrait toutes dans un tableau.
Envie de pratiquer plus ?
Rejoins Le Dojo Club pour accéder à des dizaines de cas pratiques corrigés, des formations complètes et une communauté de pros qui s'entraident.
Essayer pendant 30 jours