Excel : créer un planning d’astreinte automatique et équitable
Créez dans Excel un planning d’astreinte avec rotation d’équipes, jours fériés et répartition équitable via INDEX, EQUIV, MOD et NB.SI.
- Difficulté
- Intermédiaire
Le plus simple pour automatiser une rotation d’astreinte dans Excel consiste à séparer le fichier en trois zones : cycle d’équipes, calendrier et jours fériés. Vous obtenez ainsi un planning qui tourne seul, signale les dates spéciales et permet de vérifier si la charge reste équilibrée avec INDEX, EQUIV, MOD et NB.SI.
Ce montage convient si vos équipes suivent un ordre fixe. En revanche, il ne gère pas à lui seul les échanges de dernière minute, les absences ni les règles locales complexes sur les fériés : ces cas doivent être ajoutés explicitement dans votre fichier.
Prérequis
Préparez un classeur avec une feuille de planning, une petite table de rotation et une liste de jours fériés au format date Excel. Vérifiez aussi que toutes les dates sont de vraies dates, pas du texte, sinon les tests sur les fériés et les comptages seront faux.
Matériel nécessaire
- Excel avec fonctions INDEX, EQUIV, MOD et NB.SI
- Liste des équipes ou des personnes dans l’ordre du cycle
- Liste des jours fériés dans une plage dédiée
Étapes
Créer la table de rotation
Placez dans une colonne la rotation de base, par exemple une équipe par ligne dans l’ordre normal d’astreinte. Cette table doit rester stable, car tout le planning ira chercher les noms dans cette plage.
Ajoutez si besoin un numéro d’ordre en face de chaque équipe. Cela aide à contrôler le cycle et à repérer vite un doublon ou un oubli.
Saisir le calendrier du planning
Créez une colonne avec toutes les dates à couvrir, une ligne par jour ou une ligne par période selon votre organisation. À côté, prévoyez au moins une colonne pour l’équipe affectée et une autre pour le statut du jour.
Si vous travaillez par jour, gardez le même pas sur toute la plage. Une structure régulière simplifie le calcul de rotation et les contrôles d’équité.
Lister les jours fériés
Inscrivez les jours fériés dans une plage séparée, sur la même feuille ou sur une feuille dédiée. Cette plage servira de référence pour marquer automatiquement les dates particulières dans le planning.
Conservez uniquement des dates Excel valides. Si un férié est stocké comme texte, Excel ne le reconnaîtra pas dans vos recherches ou comparaisons.
Calculer la rotation avec MOD
Dans la colonne d’affectation, calculez d’abord la position dans le cycle avec MOD. Le principe est de transformer le numéro de ligne ou l’écart depuis la date de départ en un rang qui repart au début quand la fin du cycle est atteinte.
Vous obtenez ainsi un index répétitif : 1, 2, 3, puis retour à 1 si votre cycle comporte trois équipes. C’est le cœur de l’automatisation.
Récupérer l’équipe avec INDEX et EQUIV
Utilisez ensuite INDEX pour renvoyer le nom de l’équipe correspondant au rang calculé. Si votre table contient un numéro d’ordre, EQUIV permet de retrouver la bonne ligne avant de renvoyer la valeur avec INDEX.
Ce duo est utile si votre table de rotation est structurée avec des identifiants plutôt qu’avec un simple ordre visuel. Il reste aussi plus robuste qu’une recherche faite manuellement.
Marquer les jours fériés dans une colonne dédiée
Ajoutez une colonne qui teste si la date du planning figure dans la liste des jours fériés. Vous pourrez y afficher un libellé simple, puis filtrer ou mettre en forme ces lignes séparément.
Cette séparation est préférable à une correction manuelle dans la colonne d’équipe. Le planning reste lisible et la logique de rotation n’est pas mélangée avec les exceptions calendaires.
Compter les attributions avec NB.SI
Créez une petite zone de contrôle avec la liste des équipes, puis utilisez NB.SI pour compter combien de fois chaque équipe apparaît dans la colonne d’affectation. Vous voyez aussitôt si la répartition reste équilibrée sur la période choisie.
Ce contrôle n’ajuste pas le planning à votre place. En revanche, il permet de vérifier objectivement qu’aucune équipe n’est surchargée après vos réglages.
Tester le planning sur un exemple court
Prenez un exemple simple avec quelques équipes, une date de départ et une petite période de test. Vérifiez que la rotation repart correctement après la dernière équipe et que les jours fériés sont bien repérés dans la colonne dédiée.
Contrôlez ensuite les totaux avec NB.SI. Si les comptes ne correspondent pas à votre logique attendue, cherchez d’abord une erreur de référence absolue, de format de date ou de point de départ du cycle.
Erreurs fréquentes & dépannage
Erreur fréquente : utiliser des dates au format texte. Les jours fériés ne seront alors ni reconnus ni comptés correctement.
Erreur fréquente : recopier une formule sans figer la plage du cycle ou la liste des fériés. Le planning semble fonctionner au début, puis dérive ligne après ligne.
Erreur fréquente : confondre équité théorique et équité réelle. Un cycle équilibré sur le papier peut devenir inégal si vous appliquez ensuite des changements manuels non suivis dans les comptages.
Astuces & pour aller plus loin
Gardez une colonne séparée pour les exceptions plutôt que d’écraser la formule d’affectation. Vous pouvez ainsi conserver la logique automatique et identifier clairement ce qui a été modifié.
Ajoutez une zone de contrôle compacte : nombre total d’astreintes par équipe et repérage des jours fériés. Cette vue suffit souvent pour valider rapidement le planning avant diffusion.
Questions fréquentes
Peut-on gérer les week-ends avec le même principe ?
Oui, si votre règle reste cyclique. Il faut simplement décider si les week-ends suivent la même rotation que les autres jours ou une règle distincte dans une colonne dédiée.
INDEX et EQUIV sont-ils obligatoires si la liste est très simple ?
Non. Si le rang calculé pointe directement sur la bonne ligne, INDEX seul peut suffire. Le couple INDEX/EQUIV devient utile dès que vous cherchez par identifiant ou que la position visuelle peut changer.
NB.SI rend-il la répartition automatiquement équitable ?
Non. NB.SI sert à contrôler la répartition, pas à la corriger. L’équité vient du cycle choisi et des éventuelles règles que vous appliquez aux jours fériés et aux exceptions.
Conclusion
Pour un planning d’astreinte fiable dans Excel, construisez une rotation simple avec MOD, récupérez l’équipe avec INDEX et EQUIV, puis contrôlez les écarts avec NB.SI. Si vous ajoutez ensuite des exceptions, faites-le dans des colonnes séparées pour préserver la logique et faciliter la vérification.