joe.levrai

Bonjour,

je vous propose aujourd'hui une méthode que j'utilise sur des fichiers de reportings, pour permettre à l'utilisateur d'afficher un mois complet sur ses jours ouvrés sans tenir compte des jours fériés.
Ceci, peut importe le mois et l'année souhaitée.
Ce "masque" est utilisable en mode "humain" par un utilisateur, mais également pilotable par VBA dans le cadre d'automatisation de reportings.

Les postulats sont les suivants :

- nos jours ouvrés s'établissent du LUNDI au VENDREDI
- nos jours fériés sont les jours fériés FRANCAIS
- pour faire référence aux jours qui seront affichés dans notre masque, j'emploierai l'expression "calendrier"

Pour construire cette structure, nous avons besoin :

- de réserver deux cellules où l'utilisateur saisira l'année et le mois
- de créer une matrice qui calculera automatiquement nos 13 fériés de l'année choisie
- de préparer une structure dynamique où l'on pourra faire rentrer tous les jours du calendrier


Bien sûr, nous ne feront pas appel au VBA, seules le formules Excel seront employées




I) Obtenir le mois et l'année par l'utilisateur

Afin de cadrer la saisie de l'utilisateur, nous allons utiliser la Validation des données, accessible sous le menu DONNEES >> Onglet OUTILS DE DONNEES

Au préalable, on va nommer les deux cellules pour les utiliser plus facilement dans nos formules :

- se positionner sur la cellule qui accueillera le numéro du mois
- dans la barre de nom (située à côté de la barre de formule) nous allons écrire "Mois" et valider par la touche entrée
- notre cellule est maintenant accessible dans le gestionnaire de nom

nous procédons de même pour la cellule qui contiendra l'année, en l'appelant Annee

Pour contrôler le mois, nous pouvons, au choix, créer une liste de validation avec les 12 numéros de mois (1)
Nous pouvons également vérifier par formule que le chiffre est compris entre 1 et 12 (2)

Afin de présenter les deux méthodes, nous utiliseront (1) pour les Mois et (2) pour les Années.

Mise en place de la validation du Mois

En se positionnant sur la cellule Mois, ouvrons le menu de validation des données.
Nous souhaitons une "Liste" avec les 12 mois de l'année, qu'on obtient en saisissant les 12 numéros, séparés d'un point-virgule


Mise en place de la validation de l'année

Nous prenons pour exemple une plage d'année comprise entre 2002 et l'année courante.

En se positionnant sur la cellule Annee, ouvrons le menu de validation des données.
Nous souhaitons valider la saisie uniquement SI A LA FOIS(Annee > 2001) ET A LA FOIS (Annee <= AnneeEnCours)

Ceci ressemble étrangement à une formule excel ?

Nous n'utiliserons pas une liste déroulante dans ce cadre, nous allons autoriser une saisie personnalisée qui sera valide si la formule est VRAI :



Personnalisation de l'interaction avec l'utilisateur

Le but de ce billet n'étant pas de présenter la validation des données, il demeure cependant possible de renvoyer un message personnalisé si l'utilisateur ne saisis pas une valeur admise

Par exemple, pour une saisie non valide de l'année, on peut afficher un message personnalisé :


via l'onglet "Alerte d'erreur" du menu de Validation des données :




II) Construire la matrice des jours fériés

Nos jours fériés français peuvent se classer en deux catégories (si je chipote, il y en aurait en fait 3)

- les jours "fixes" tels Noel, Nouvel An, la Fête nationale, Armistice 1918 etc...
- les jours "mobiles" tels Lundi de Pâques, Lundi de pentecôte etc...

Malgré ces distinctions, elles sont toutes calculables par formule Excel !
Et il y a même plusieurs méthodes pour les fériés "mobiles".

Voici nos 13 jours fériés :


La plage V4:V16 utilisée dans la formule de l'Ascension correspond à la seconde colonne de la copie écran (la plage qui contient le "nom" des jours fériés"

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
=DATE(Annee;1;1) >>	Nouvel An
=PLANCHER(DATE(Annee;5;JOUR(MINUTE(Annee /38)/2+56));7)-34 >>	Pâques
=PLANCHER(DATE(Annee;5;JOUR(MINUTE(Annee/38)/2+56));7)-33 >>	Lundi de Pâques
=DATE(Annee;5;1) >>	Fête du travail
=DATE(Annee;5;8) >>	Victoire
=SI(NB.SI(V4:V16;PLANCHER(DATE(Annee;5;JOUR(MINUTE(Annee /38)/2+56));7)+5);"0";PLANCHER(DATE(Annee;5;JOUR(MINUTE(Annee /38)/2+56));7)+5) >>	Ascension
=PLANCHER(DATE(Annee;5;JOUR(MINUTE(Annee /38)/2+56));7)+15 >>	Pentecôte
=PLANCHER(DATE(Annee;5;JOUR(MINUTE(Annee /38)/2+56));7)+16 >>	Lundi de pentecôte
=DATE(Annee;7;14) >>	Fête nationale
=DATE(Annee;8;15) >>	Assomption
=DATE(Annee;11;1) >>	Toussaint
=DATE(Annee;11;11) >>	Armistice
=DATE(Annee;12;25) >>	Noël

Il ne nous reste qu'à "présenter" cette matrice sous forme d'un tableau qu'on peut parfaitement présenter sur notre masque de Reporting :


Le titre est écrit dans la première cellule via la formule : ="Fériés " & Annee
J'ai "centré sur plusieurs colonnes" les deux cellules de titre pour éviter une fusion des cellules
Les formules sont écrites en dessous de "Date" et le nom des jours fériés est écrit en dur en dessous de "Nom"

Nous allons nommer la plage des dates (uniquement les dates, sans le titre) comme on la fait pour le Mois et l'Annee
==> son nom est "Feries"


III) Créer la structure dynamique de notre calendrier

Vouloir créer une structure dynamique impose de connaître la limite maximale qu'occupera le calendrier.
Cette limite, c'est tout simplement le nombre maximal de jours ouvrés qu'on pourrait obtenir sur nos postulats de départ (pas de jours fériés, et jours ouvrés du lundi au vendredi)

La réponse est de 23 jours ouvrés maximums possibles.
Je ne détaillerai pas l'algorithme permettant de vérifier ce résultat, ce n'est pas le but du billet, et sur internet de nombreux sites vous le présenteront.

Nous allons donc créer une plage de deux colonnes sur 23 lignes ... puisqu'on va en profiter pour indiquer le jour de la semaine en plus de la date


Pour la suite de notre exercice, nous allons fixer la zone des jours/dates sur la plage B11:C33 dans l'écriture de nos formules

Pour obtenir le nom du jour de la semaine, nous pourrions aisément "recopier" la date dans la cellule, et utiliser le format personnalisé "jjjj".
L'inconvénient, c'est que nos jours sont écrits en minuscule, j'ai donc opté pour une autre méthode.

Saisir en B11 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SIERREUR(CHOISIR(JOURSEM($C11;2);"LUNDI";"MARDI";"MERCREDI";"JEUDI";"VENDREDI");"")

Etirer la formule jusqu'en bas "B33"
Ainsi, nous avons le jour de la semaine ... ou rien du tout s'il n'y a pas de date à côté (ou que c'est un samedi ou dimanche)

Pour calculer nos jours ouvrés, nous avons deux cas à gerer :

- définir le premier jour ouvré du mois

Ecrire en C11 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SERIE.JOUR.OUVRE(DATE(Annee;Mois;1)-1;1;Feries)

- lister les jours ouvrés suivants du mois sans déborder sur le mois suivant (si par exemple il n'y avait que 21 jours ouvrés sur le mois observé)

Ecrire en C12 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(C11="";"";SI(MOIS(SERIE.JOUR.OUVRE(C11;1;Feries))<>Mois;"";SERIE.JOUR.OUVRE(C11;1;Feries)))

étirer la formule jusqu'en C33


Voici notre calendrier dynamique en place !





NB : la cellule "TOTAL" est un parasite que j'ai oublié de masquer avant de capturer l'écran ...


Il ne vous reste plus qu'à insérer ces éléments dans vos masques de reportings.
Vous pouvez également ajouter un peu de fantaisie, par exemple en utilisant une mise en forme conditionnelle qui va "griser" le bas de votre calendrier quand il ne contient pas de jours.

Tout est permis.



IV) Conclusion et ouverture

Les postulats de départ ont permit de dérouler ce billet exemple.
Mais bien sûr, ils peuvent être adaptés à d'autres contraintes, comme intégrer le Samedi dans notre listing des jours fériés, ou carrément imposer un weekend sur le Mardi et Mercredi (par exemple)

Il suffit de modifier la formule qui affiche le nom du jour, et les deux formules qui affichent la date.

Mais ça, c'est un bon exercice pour se mettre dans le bain non ?