Excel : concevoir un calendrier annuel des congés pour une équipe de 10 personnes (modèle vertical, jours fériés auto, sans VBA)

Créez un calendrier annuel des congés pour 10 personnes dans Excel : un modèle vertical clair, imprimable, sans VBA obligatoire, qui se met à jour automatiquement (dimanches & jours fériés) et révèle d’un coup d’œil les chevauchements d’absences.

Sommaire

Objectif et prérequis

Vous souhaitez saisir les absences de 10 collaborateurs (vacances, RTT, maladie, etc.) dans un seul onglet et les voir se projeter automatiquement sur un calendrier couvrant toute l’année — idéalement en mise en page verticale, compacte, lisible à l’écran et à l’impression, sans avoir à « pointer » chaque année les dimanches ni les jours fériés.

Version d’Excel conseillée : Microsoft 365 / Excel 2021 ou ultérieur (formules dynamiques telles que SEQUENCE, FILTRE, RECHERCHEX, LET, LAMBDA). Les formules ci‑dessous sont données en français ; les équivalents anglais sont indiqués entre parenthèses si utile.

Architecture recommandée

Parmi les approches possibles, la solution la plus robuste consiste à bâtir un calendrier matriciel vertical unique : 1 ligne par jour (365 ou 366), 1 colonne par employé, alimenté par une table structurée d’absences. Cette architecture :

rend immédiates les collisions (plusieurs colonnes colorées le même jour) ;
se met à l’échelle sans casser les formules (ajout d’employés, de nouveaux types d’absence) ;
automatise les dimanches et les jours fériés ;
imprime proprement en A3 (ou en deux semestres).

Schéma des feuilles

Paramètres : année, liste des employés, dictionnaire de types d’absence (code, libellé, priorité, poids), calcul des jours fériés, plages nommées.
Absences : table structurée Absences (Employé, Type, Date début, Date fin, Commentaire).
Calendrier : colonne de dates (toute l’année, générée), entêtes employés, grille d’indicateurs/colorations, totaux, alertes de collisions.

Comparaison rapide des approches

Approche	Idée directrice	Mise en œuvre dans Excel	Points forts	Limites / précautions
Modèle « Calendrier toute année » adapté (modèle *.xltx)	Partir d’un calendrier perpétuel et le personnaliser	Dupliquer la feuille « Janvier » pour les 11 mois. En B4, `=DATE($A$1;FEUILLE()-1;1)` pour faire avancer le mois. Ajouter colonnes par employé, saisir les codes (V, M…), totaux par `NB.SI`.	Rapide, sans VBA	Affichage horizontal trop étendu ; 132 colonnes pour 10 personnes × 12 mois
Calendrier vertical 365 × N	Une ligne par jour, une colonne par salarié	`A1` : année ; dates générées par `SEQUENCE` ; MFC pour colorer selon code	Lecture intuitive des chevauchements ; imprimable recto/verso	≈ 370 lignes × 11 colonnes : A3 recommandé ou zoom à l’écran
1 employé = 1 onglet	Chaque salarié a son calendrier	Copie d’un modèle mensuel ; consolidation via Power Query ou 3D‑références	Saisie claire par personne	Vue d’ensemble éclatée ; détection des collisions moins pratique
Tableau structuré + MFC (solution conseillée)	Stocker les absences en liste et projeter dans un calendrier	Table Absences (Employé, Début, Fin, Type). Formule matricielle pour tester « X est‑il absent le jour J ? ». Table Feries pour les jours fériés. MFC empilées.	Calendrier unique, clair, extensible, automatisé	Requiert Excel 365/2021 pour les formules dynamiques

Étapes détaillées — construire le calendrier matriciel

1) Feuille « Paramètres »

Année : en B1, tapez l’année (ex. 2026). Nommez la cellule Annee (Formules > Définir un nom).
Employés : créez une table Employes en E2:E11 (10 noms). Nommez la colonne Employes.
Types d’absence : créez une table Types avec colonnes Code (V, RTT, M, F, D…), Libellé, Priorité (1 = le plus important), Poids (1 jour, 0,5 jour…). Exemple :
CodeLibelléPrioritéPoids MMaladie11 VVacances21 RTTRTT31 ½VDemi‑journée Vacances20,5 FJour férié00 DDimanche00 Les codes F et D sont réservés au système (jours fériés et dimanches).

2) Jours fériés automatiques (France métropolitaine)

Objectif : générer la liste annuelle des jours fériés sans mise à jour manuelle. On calcule d’abord la date de Pâques avec un LAMBDA, puis on en déduit les fériés variables (Lundi de Pâques, Ascension, Lundi de Pentecôte). Les autres fériés sont fixes.

2.1 Définir la fonction personnalisée « Paques » (Meeus/Jones/Butcher)

Formules > Gestionnaire de noms > Nouveau : Nom = Paques.
Dans Fait référence à, collez : =LAMBDA(annee; LET( a;MOD(annee;19); b;QUOTIENT(annee;100); c;MOD(annee;100); d;QUOTIENT(b;4); e;MOD(b;4); f;QUOTIENT(b+8;25); g;QUOTIENT(b-f+1;3); h;MOD(19*a+b-d-g+15;30); i;QUOTIENT(c;4); k;MOD(c;4); l;MOD(32+2*e+2*i-h-k;7); m;QUOTIENT(a+11*h+22*l;451); mois;QUOTIENT(h+l-7*m+114;31); jour;MOD(h+l-7*m+114;31)+1; DATE(annee;mois;jour) ) )
Validez. Test : dans une cellule vide, =Paques(Annee) doit renvoyer la date du dimanche de Pâques.

2.2 Construire la table « Feries »

Dans Paramètres, créez une table à deux colonnes (Date, Libellé). Remplissez la colonne Date avec ces formules :

Jour de l’An : =DATE(Annee;1;1)
Lundi de Pâques : =Paques(Annee)+1
Fête du Travail : =DATE(Annee;5;1)
Victoire 1945 : =DATE(Annee;5;8)
Ascension : =Paques(Annee)+39
Lundi de Pentecôte : =Paques(Annee)+50
Fête nationale : =DATE(Annee;7;14)
Assomption : =DATE(Annee;8;15)
Toussaint : =DATE(Annee;11;1)
Armistice : =DATE(Annee;11;11)
Noël : =DATE(Annee;12;25)

Nommez la colonne des dates Feries (plage nommée). Ajoutez si besoin les spécificités Alsace‑Moselle (Vendredi Saint, Saint‑Étienne).

3) Feuille « Absences » — la table source

Insérez une table structurée Absences avec colonnes :

Employé (validation de données = liste =Employes)
Type (validation de données = liste de Types[Code])
Date début (date)
Date fin (date)
Commentaire (optionnel)

Chaque ligne décrit une période d’absence continue. Pour une demi‑journée, utilisez le code ½V (ou équivalent) et laissez les dates identiques (début = fin).

4) Feuille « Calendrier » — les dates

En B2:K2, affichez les 10 employés :
=TRANSPOSE(Employes)
En A3, titre « Date » (facultatif). En A4, générez toutes les dates de l’année :
=LET( y;Annee; j1;DATE(y;1;1); nb;DATE(y+1;1;1)-j1; SEQUENCE(nb;1;j1;1) ) Cette formule crée automatiquement 365 ou 366 lignes selon l’année.

5) Indicateur « dimanche / férié » (colonne auxiliaire)

En B4 (copiez vers le bas), renvoyez D pour dimanche, F pour férié, sinon vide :

=SI(NB.SI(Feries;$A4);"F";SI(JOURSEM($A4;2)=7;"D";""))

Vous pouvez masquer cette colonne si vous préférez n’afficher que la coloration.

6) La matrice des employés (présence/absence par jour)

En B4 (puis recopier sur la zone B4:K sur toute la hauteur), renvoyez le code d’absence applicable au jour et à l’employé, avec gestion d’éventuels doublons via une priorité :

=LET(
  d;$A4;             /* date du jour */
  emp;B$2;           /* employé (en entête) */
  prios;SI(
    (Absences[Employé]=emp)*
    (Absences[Date début]<=d)*
    (Absences[Date fin]>=d);
    RECHERCHEX(Absences[Type];Types[Code];Types[Priorité]);
    NA()
  );
  p;SIERREUR(AGREGAT(15;6;prios;1);"");  /* plus petite priorité présente */
  SI(p="";"";INDEX(Types[Code];EQUIV(p;Types[Priorité];0)))
)

Explications : on calcule la liste des priorités des types actifs ce jour‑là pour l’employé, on prend la plus haute priorité (valeur numérique la plus petite) et on renvoie son code. Si personne n’est absent ce jour, la cellule reste vide.

7) Mise en forme conditionnelle (MFC)

Créez des règles MFC dans l’ordre suivant et cochez « Arrêter si vrai » pour les deux premières :

Jour férié : plage $A$4:$K$400, formule :
=NB.SI(Feries;$A4)>0 → remplissage rouge clair, texte blanc (optionnel).
Dimanche : même plage, formule :
=JOURSEM($A4;2)=7 → gris clair.
Codes d’absence (une règle par code)
- Vacances (V) : « Format des cellules contenant » > Spécifique > Texte contenant V → vert pâle.
- Maladie (M) : Texte contenant M → jaune/orangé.
- RTT : Texte contenant RTT → bleu pâle.
- Demi‑journée (½V) : Texte contenant ½ → motif ou bordure spéciale.
Collisions : ajoutez en L4 la formule =SOMMEPROD(--(B4:K4<>"")) (total d’absents du jour). Créez une MFC sur la plage lignes $A$4:$L$400, formule =$L4>=3 → rouge/orange pour signaler les jours à risque (seuil ajustable).

8) Totaux par employé et tableaux de synthèse

Totaux annuels (ligne sous la matrice, ex. rang 410) : en B410 :
=SIERREUR(SOMME(RECHERCHEX(B4:B409;Types[Code];Types[Poids]));0) puis recopiez jusqu’à K410.
Cette formule additionne les poids (1 pour un jour, 0,5 pour une demi‑journée) selon les codes.
Totaux par type (tableau croisé ou SOMME.SI.ENS) : ex. jours de vacances de B2 :
=SOMME.SI.ENS(Types[Poids];Types[Code];"V") appliqué au vecteur des codes B4:B409. Pour un résumé global, préférez un Tableau croisé dynamique basé sur la table Absences.

9) Impression et ergonomie

Orientation : Portrait, A3, marges étroites.
Répéter les lignes d’entête : Mise en page > Titres à imprimer > $2:$2.
Figer les volets : figer la première colonne (dates) et la ligne des entêtes d’employés.
Deux semestres : dupliquez la feuille et filtrez les dates (janvier–juin / juillet–décembre) pour deux pages lisibles en A4.

Automatisation des dimanches et jours fériés (rappel rapide)

Dimanches : =SI(JOURSEM(Date;2)=7;"D";"") (ou MFC avec la même condition).
Jours fériés : plage nommée Feries, test : =SI(NB.SI(Feries;Date);"F";"").
Couleurs (ordre conseillé) :
1. Jour férié (rouge clair)
2. Dimanche (gris clair)
3. Codes d’absence (verts/jaunes/bleus…)

Gestion des demi‑journées et quarts

Pour les demi‑journées, créez un code dédié (ex. ½V) avec Poids = 0,5 dans la table Types. Le calcul des totaux annuels se contente alors d’additionner les poids renvoyés par RECHERCHEX.

Filtrage, recherche et compatibilité

Recherche par employé : activez les filtres sur la ligne des entêtes (B2:K2) si vous avez beaucoup de colonnes, ou utilisez une case liste (validation de données) pour masquer dynamiquement les autres colonnes avec une MFC « Police blanche ».
Compatibilité : si RECHERCHEX/FILTRE ne sont pas disponibles, remplacez par INDEX/EQUIV/SOMMEPROD. La logique reste identique (un poil plus verbeuse).

Option VBA (facultatif) : bouton « Nouvelle année »

La solution fonctionne sans macro. Si vous voulez accélérer le passage à l’année N+1 (dupliquer feuille, mettre à jour Annee, recalculer), voici un exemple de macro minimaliste :

Sub Nouvelle_Annee()
  Dim y As Long
  With ThisWorkbook.Worksheets("Paramètres")
    y = .Range("B1").Value
    .Range("B1").Value = y + 1
  End With
  Worksheets("Calendrier").Copy After:=Worksheets(Worksheets.Count)
  ActiveSheet.Name = "Calendrier " & CStr(y + 1)
  Application.CalculateFull
End Sub

Enregistrez en *.xlsm*, signez les macros si votre organisation l’exige.

Conseils de maintenance

Geler les noms de codes et leur priorité (ne changez pas la sémantique en cours d’année).
Protéger les cellules de structure (dates, entêtes, formules) et laisser éditables les cellules de la table Absences.
Contrôles de saisie : validation de données sur les dates (début ≤ fin), message d’erreur explicite.
Conventions : imposez le même format pour les noms d’employés (Prénom Nom) partout, sinon les égalités échoueront.

FAQ express

Q : Peut‑on intégrer les week‑ends complets ?
R : Oui. Ajoutez une règle MFC pour JOURSEM(Date;2)>=6 si vous voulez également griser le samedi.

Q : Comment afficher « AM / PM » sur la même ligne ?
R : Utilisez deux colonnes par employé (AM / PM) ou un code composite (« AM », « PM ») avec Poids = 0,5 et une MFC qui coupe visuellement la cellule en diagonale (bordure).

Q : Peut‑on générer deux semestres automatiquement ?
R : Oui. Filtrez A4:A par mois (1–6 / 7–12) et dupliquez la feuille. Les formules s’ajustent.

Q : Et si deux absences se chevauchent le même jour pour un employé ?
R : Le mécanisme de priorité renvoie le code le plus important (ex. maladie > vacances). Ajustez la table Types.

Modèle de données synthétique (à recopier)

Paramètres

Annee : Paramètres!B1
Employes : tableau 1 colonne (10 lignes)
Types : colonnes Code, Libellé, Priorité, Poids
Feries : colonnes Date, Libellé

Absences

Employé	Type	Date début	Date fin	Commentaire
Nom 1	V	01/07/2026	12/07/2026	–
Nom 2	RTT	15/03/2026	15/03/2026	AM
Nom 3	M	02/02/2026	05/02/2026	–

Calendrier : formules prêtes à coller

Dates (A4) : =LET(y;Annee;j1;DATE(y;1;1);nb;DATE(y+1;1;1)-j1;SEQUENCE(nb;1;j1;1))
Entêtes employés (B2) : =TRANSPOSE(Employes)
Dimanche/férié (B4) : =SI(NB.SI(Feries;$A4);"F";SI(JOURSEM($A4;2)=7;"D";""))
Code d’absence (B4 → K…) : =LET( d;$A4;emp;B$2; prios;SI((Absences[Employé]=emp)*(Absences[Date début]<=d)*(Absences[Date fin]>=d); RECHERCHEX(Absences[Type];Types[Code];Types[Priorité]);NA()); p;SIERREUR(AGREGAT(15;6;prios;1);""); SI(p="";"";INDEX(Types[Code];EQUIV(p;Types[Priorité];0))) )
Total absents par jour (L4) : =SOMMEPROD(--(B4:K4<>""))
Totaux annuels par employé (B410) : =SIERREUR(SOMME(RECHERCHEX(B4:B409;Types[Code];Types[Poids]));0)

Bonne pratique : dictionnaire des couleurs

Consignez au même endroit (sur Paramètres) la légende des couleurs : V = Vert, M = Jaune, RTT = Bleu, F = Rouge clair, D = Gris clair. Reproduisez exactement ces codes dans la MFC pour éviter toute ambiguïté.

Alternative : autres architectures (détaillées)

Si votre contrainte principale est l’impression mensuelle sur A4, la variante « modèle annuel par mois » suffit souvent. Pour une vision d’équipe et la détection des collisions, la matrice verticale restera plus efficace et plus stable dans le temps.

Partage et collaboration

Excel Online : permet la saisie simultanée dans la table Absences avec l’historique des modifications.
Export PDF : imprimez le calendrier (A3) avec les couleurs. Deux versions : semestre 1 et semestre 2.
Governance : limitez l’édition de Paramètres à l’administrateur RH ; ouvrez Absences à l’équipe.

Contrôles qualité (check‑list)

Les dates de Pâques, Ascension et Pentecôte se recalculent‑elles quand Annee change ?
La MFC « Férié » est‑elle au‑dessus des règles d’absence ?
Les totaux par employé reflètent‑ils les demi‑journées via Poids ?
La formule des dates génère‑t‑elle 366 lignes pour une année bissextile ?

Conclusion et recommandation

Pour une équipe de 10 personnes, le calendrier matriciel vertical (365 × 10) piloté par une table structurée est le meilleur compromis entre simplicité de saisie, lisibilité et robustesse. Les dimanches et jours fériés se gèrent de manière fiable via des formules et une petite fonction LAMBDA pour Pâques. L’ensemble reste sans VBA (sauf optionnellement un bouton « Nouvelle année ») et s’imprime parfaitement en A3. En bonus, vous disposez d’un socle évolutif (ajout de nouveaux employés ou types d’absence, totaux pondérés, alertes de collisions) qui s’adapte aux besoins RH de votre organisation.