Compter les jours de Week-End dans les mois

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Compter les Week-End entre deux dates

Compter les Samedis et les Dimanches d'un mois est l'un des défis de cette formation.



Mais comme le prouve l'exemple finalisé de la capture ci-dessus, ce n'est pas le seul. Au choix d'une année et d'un numéro de mois avec deux listes déroulantes, nous dressons un bilan assez complet. Nous définissons la date de début pour le mois défini et calculons sa date de fin. Nous renseignons sur les jours respectifs de semaine. Nous comptabilisons le nombre de jours entre les deux extrémités. Nous faisons de même en excluant les jours fériés puis les jours de Week-End. Ensuite, nous calculons le nombre de Samedis et de Dimanches dans le mois. Enfin, nous les listons. Et pour cette dernière étape, c'est une formule matricielle qui entre en jeu.

Source et présentation
Comme toujours, nous suggérons d'établir ces travaux à partir d'une source existante.

• Télécharger le classeur week-end-dans-mois.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous découvrons un classeur constitué de deux feuilles nommées respectivement Compter et Jour_feries. Deux listes déroulantes sont donc proposées en cellules C5 et C8 pour définir l'année et le numéro du mois. En fonction de ces deux paramètres, toutes les cases jaunes doivent se renseigner.

En colonne N, vous notez la présence d'une énumération de toutes les dates composant le mois choisi pour l'année définie. Le point de départ est calculé grâce à la fonction Date, comme suit :

=DATE(C5;C8;1)

Elle se nourrit bien de l'information sur l'année et le mois et considère nécessairement le premier jour. Les suivantes consistent en une incrémentation jusqu'au dernier jour du mois, selon la formule suivante :

=SIERREUR(SI(MOIS(N3+1)=MOIS(N3); N3+1; ''); '')

C'est le critère sur la comparaison des mois, imposé par la fonction Si, qui permet de stopper l'énumération sur le dernier jour du mois, afin de ne pas déborder.

• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Gestionnaire de noms,

Nous notons la présence de plusieurs références. Le nom Feries désigne l'ensemble des jours fériés de la seconde feuille. Nous l'exploiterons en temps utile.

• Cliquer sur l'intitulé Liste pour consulter sa zone Fait référence à,

Ce nom désigne l'énumération des dates du mois choisi. Il utilise la fonction Decaler pour ajuster la hauteur de la plage, en fonction du nombre de jours contenus :

=DECALER(Compter!$N$3; 0; 0; NB.SI(Compter!$N:$N;'>0'))

C'est la fonction Nb.Si en quatrième argument qui permet d'adapter cette hauteur. Chaque valeur positive, donc chaque date, est comptée. Nous l'utiliserons pour l'extraction des Samedis et Dimanches composant le mois défini.

• Cliquer sur le bouton Fermer du gestionnaire de noms pour revenir sur la feuille Excel,
• En bas de la fenêtre Excel, cliquer sur l'onglet Jour_feries pour activer sa feuille,

Un tableau référence tous les jours fériés sur trois années. Si vous les sélectionnez tous, vous constatez la présence du nom les identifiant dans la zone Nom, en haut de la feuille Excel.

Premier et dernier jour du mois
Les premiers résultats à livrer sont triviaux.

• En bas de la fenêtre Excel, cliquer sur l'onglet Compter pour revenir sur sa feuille,
• Avec la première liste déroulante en C5, choisir l'année 2021,
• Avec la seconde liste déroulante en C8, choisir le numéro 5 pour le mois de Mai,
• Sélectionner la cellule G4 pour le premier jour du mois,
• Construire et valider la formule suivante : =DATE(C5;C8;1),

En passant l'année, le numéro de mois et le chiffre pour le jour, nous obtenons bien la première date du mois.

• Sélectionner la cellule K4 pour le dernier jour du mois,
• Construire et valider la formule suivante : =FIN.MOIS(G4;0),

Nous exploitons la fonction Fin.Mois sur la précédente date restituée. Avec le chiffre 0 passé en second paramètre, nous lui demandons de restituer le dernier jour, pour le même mois que la date spécifiée en premier argument.

• En cellules G6 et K6, taper les égalités suivantes : =G4 et =K4,

Pour l'instant, nous nous contentons de reproduire les dates précédemment calculées. C'est un format personnalisé qui permet d'adapter l'apparence pour ne conserver que la précision sur le jour de la semaine.

• Sélectionner ensemble ces cellules G6 et K6 à l'aide de la touche CTRL,
• Dans la section Nombre du ruban Accueil, déployer la liste déroulante des formats,
• Tout en bas des propositions, choisir Autres formats numériques,
• Dans la boîte de dialogue qui suit, sélectionner la catégorie Personnalisée,
• Dans la zone type, saisir le code suivant : jjjj,

La lettre j signifie jour. Avec cette précision, nous ne conservons que l'information textuelle sur le jour de semaine pour la date ainsi formatée. D'ailleurs, c'est ce que confirme la zone Exemple juste au-dessus de la zone Type.

• Cliquer sur le bouton Ok de la boîte de dialogue pour revenir sur la feuille Excel,

Désormais, nous connaissons précisément les jours de semaine pour la première et la dernière date du mois. Et c'est un simple format personnalisé qui a suffi pour livrer le résultat.

Nombre de jours entre deux dates
Dans les deux cellules qui suivent, nous devons comptabiliser le nombre de jours composant le mois défini et ce même nombre en excluant les jours fériés. Le premier calcul consiste en une simple soustraction. Le second est plus spécifique.

• En cellule G8, écrire et valider la formule suivante : =K4-G4+1,

Nous effectuons la soustraction entre les deux dates et ajoutons une unité à ce résultat. En effet, le premier jour du mois qui est utilisé dans la soustraction doit être considéré dans le décompte.

Ensuite, pour exclure les jours fériés, nous devons vérifier ceux qui sont compris entre ces deux dates. Il s'agit donc de scruter le tableau de la seconde feuille. Et c'est un raisonnement matriciel exploitant la fonction Sommeprod qui permet de recouper ces conditions. Lorsqu'elles se croisent, elles sont dénombrées et additionnées.

• Sélectionner la cellule K8 et taper le symbole égal (=) pour initier la formule,
• Désigner la cellule G8 pour récupérer le nombre total de jours calculé,
• Taper le symbole moins (-) pour annoncer la soustraction,
• Inscrire la fonction matricielle suivie d'une parenthèse ouvrante, soit : SommeProd(,
• Ouvrir une première parenthèse pour accueillir la première matrice conditionnelle,
• Désigner la table des jours fériés par son nom, soit : Feries,
• Puis, écrire le critère suivant : >=G4,

Ainsi, dans ce raisonnement matriciel, nous cherchons à trouver toutes les dates recensées qui sont situées après le premier jour du mois défini.

• Fermer la parenthèse de cette première matrice conditionnelle,
• Taper le symbole de l'étoile (*) pour annoncer la condition matricielle à recouper,
• Ouvrir une nouvelle parenthèse pour accueillir la seconde matrice conditionnelle,
• Désigner de nouveau la table des jours fériés par son nom, soit : Feries,
• Puis, inscrire le critère suivant : <=K4,

De fait, en recoupement du premier critère, nous cherchons à isoler tous les jours fériés qui sont inclus entre les deux extrémités du mois choisi pour l'année définie.

• Fermer la parenthèse de cette seconde matrice conditionnelle,
• Fermer la parenthèse de la fonction SommeProd,
• Puis, valider le calcul matriciel à l'aide de la touche Entrée du clavier par exemple,

Comme vous pouvez le voir, ce décompte retranche quatre jours au précédent total.



En consultant le tableau de la seconde feuille, vous notez que le mois de Mai 2021 est en effet composé de quatre jours fériés. Si vous sélectionnez le numéro 6 à l'aide de la seconde liste déroulante, vous désignez le mois de Juin. Ce dernier n'offre aucun jour férié. De fait, les deux totaux sont identiques.

Jours ouvrés entre deux dates
Les jours ouvrés consistent à exclure du décompte les jours de Week-End. Nous avions découvert cette fameuse fonction au travers de la formation Excel sur les dates et les heures. Elle est très simple d'emploi.

• En cellule G10, construire et valider la formule suivante : =NB.JOURS.OUVRES(G4; K4),

Dans la version simplifiée de cette fonction, nous ne renseignons que les deux premiers paramètres. Il s'agit de la date de début et de la date de fin. Elle comptabilise alors tous les jours de semaine situés dans cet intervalle. Le résultat annonce un score inférieur de 8 unités au total de jours calculés pour le mois. Cette valeur semble cohérente. Nous pourrons la confirmer sans ambiguïté lorsque nous aurons comptabilisé les Samedis et les Dimanches.

Désormais, pour exclure les jours fériés de ce décompte, l'intervention d'une formule matricielle n'est pas nécessaire. La fonction Nb.Jours.Ouvres offre un troisième argument facultatif. C'est la raison pour laquelle nous n'avons pas eu besoin de le renseigner dans le calcul précédent. Il permet de spécifier la plage sur laquelle les jours fériés sont recensés. Par recoupement, la fonction les exclura du comptage.

• En cellule K10, inscrire et valider la formule suivante : =NB.JOURS.OUVRES(G4; K4; Feries),

Nous renseignons donc le tableau des jours fériés en troisième paramètre de la fonction. A validation, rien de surprenant, le résultat est identique au précédent sur les jours ouvrés. Rappelez-vous, le mois de Juin n'offre aucun jour férié.

• Avec la seconde liste déroulante, choisir le numéro 5 pour revenir sur le mois de Mai,

Cette fois, une différence surgit. Mais elle n'est que de deux jours. Pourtant et nous l'avons constaté, le mois de Mai 2021 propose quatre jours fériés. Certes, mais deux d'entre eux tombent un Samedi. Il s'agit du 1^er et du 8 Mai. Et les jours de Week-End sont déjà naturellement exclus. Donc, le résultat offert par la fonction Nb.Jours.Ouvres est tout à fait cohérent.



Compter les Samedis et les Dimanches
Pour comptabiliser les jours de Week-End dans le mois, nous pouvons exploiter la fonction Nb.Jours.Ouvres.Intl, avec une syntaxe tout à fait particulière dans son troisième paramètre, celui des jours de Week-End. Il s'agit d'une astuce tout à fait salvatrice dans ce contexte spécifique.

• En cellule G12, écrire et valider la formule suivante :

=NB.JOURS.OUVRES.INTL(G4; K4; '1111101')

Tout comme pour son homologue Nb.Jours.Ouvres, nous renseignons tout d'abord la fonction sur la date de début et la date de fin. Mais en guise de jours de Week-End, nous lui transmettons un code en troisième paramètre. Ce code est chiffré sur sept caractères. Ils correspondent aux Sept jours de la semaine. Le sixième est initialisé à zéro contrairement aux autres. Le sixième jour de la semaine est le Samedi. C'est ainsi, dans un usage dérivé que la fonction Nb.Jours.Ouvres.Intl peut compter un jour ou plusieurs entre deux dates.

• En cellule K12, adapter la précédente formule comme suit :

=NB.JOURS.OUVRES.INTL(G4; K4; '1111110')

Cette fois, grâce au zéro pointant sur le septième jour, nous engageons le décompte sur les Dimanches.



Nous obtenons des résultats identiques. Le mois de Mai 2021 serait constitué de 5 Samedis et de 5 Dimanches. En effet et nous l'avons déjà remarqué, il débute par le Samedi 1^er Mai. Donc, ces données sont parfaitement cohérentes. De plus, elles viennent recouper et confirmer les précédents calculs. Nous avions totalisé 31 jours pour le mois défini et seulement 21 jours ouvrés. La soustraction avec les 10 jours de Week-End amène bien au même résultat. Enfin, si vous changez de mois avec la seconde liste déroulante, vous pouvez apprécier le parfait ajustement de tous les calculs.

Extraire la liste des Samedis et des Dimanches
Il nous reste désormais à produire une solution un peu plus épineuse. Elle doit permettre d'identifier et de lister ces jours de Week-End précédemment dénombrés. Cette extraction doit s'opérer entre les lignes 14 et 18 pour les colonnes F et J. La première concerne l'énumération des Samedis et la seconde, celle des Dimanches. Il s'agit de travailler sur toutes les dates du mois, énumérées en colonne N. La formule doit identifier tous les jours de semaine valant 6 ou 7, soit les Samedis ou les Dimanches. Pour cela, la fonction Joursem est nécessaire. Mais pour une extraction regroupée et basée sur une unique formule, un calcul matriciel est nécessaire. Nous connaissons bien la technique désormais, elle consiste à imbriquer les fonctions Index, Petite.Valeur, Si et Equiv.

• Sélectionner les cellules pour les Samedis à extraire, soit la plage F14:F18,
• Taper le symbole égal (=) pour initier la formule matricielle,
• Inscrire la fonction de gestion des anomalies, suivie d'une parenthèse, soit : SiErreur(,

De nombreuses recherches seront en effet invalidées dans cette colonne N qui recense tous les jours du mois. Et comme vous le savez, en cas de recherche infructueuse, les fonctions d'extraction retournent des erreurs. Grâce à la fonction SiErreur, nous allons les neutraliser.

• Saisir la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner la plage dynamique des dates par son nom, soit : Liste,
• Taper un point-virgule (;) pour passer dans l'argument des positions à repérer,
• Inscrire la fonction des petites valeurs, suivie d'une parenthèse, soit : Petite.Valeur(,

En effet, les positions des Samedis doivent être décelées par la fonction Equiv. Grâce à la fonction Petite.Valeur qui va l'imbriquer, nous allons les réunir pour proposer une extraction groupée. Mais cette recherche est tout d'abord soumise à condition. Elle doit vérifier que le jour pointé est bien un Samedi.

• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Saisir la fonction donnant le jour de semaine, suivie d'une parenthèse, soit : Joursem(,
• Désigner de nouveau la colonne des dates du mois par son nom, soit : Liste,

Nous sommes en effet dans un raisonnement matriciel. Ce sont toutes les dates qui vont être analysées et extraites par une seule et même formule.

• Fermer la parenthèse de la fonction Joursem,
• Puis, taper l'égalité suivante : =7,

En l'absence d'indication contraire, la fonction Joursem raisonne sur le calendrier américain. Dans ce cas, le Dimanche est le premier jour de la semaine tandis que le Samedi est le septième. Nous cherchons donc bien à isoler les Samedis.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Inscrire la fonction cherchant les positions en ligne, suivie d'une parenthèse, soit : Equiv(,
• En guise de valeur cherchée, désigner la colonne des dates par son nom, soit : Liste,
• Taper un point-virgule (;) pour passer dans l'argument suivant de la fonction Equiv,
• En guise de tableau de recherche, désigner de nouveau la colonne des dates, soit : Liste,

Dans ce croisement matriciel, toutes les dates seront comparées dans leur propre colonne. Elles seront donc toutes trouvées et repérées. Mais seules les positions des Samedis seront conservées du fait du critère émis pour effectuer cette recherche.

• Taper un point-virgule (;) suivi du chiffre zéro, soit : ;0, pour effectuer une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, fermer la parenthèse de la fonction Si,

Nous ne prenons pas le soin de renseigner la branche Sinon de cette dernière. Seuls les Samedis nous intéressent. De plus, la fonction SiErreur englobant le calcul se chargera de gérer les erreurs.

• Taper un point-virgule (;) pour passer dans l'argument du rang de la fonction Petite.Valeur,

Ce n'est pas un rang que nous devons prélever mais tous les rangs potentiels coïncidant avec les positions repérées pour les Samedis. Nous allons donc créer une matrice virtuelle énonçant ces rangs. Nous pourrions définir sa hauteur sur celle de la plage Liste. Mais nous savons pertinemment qu'il ne peut pas y avoir plus de 5 Samedis dans un mois. Donc, nous pouvons nous contenter de construire une matrice d'une hauteur de 5 lignes pour les 5 rangs potentiels des Samedis trouvés.

• Inscrire la fonction donnant la ligne d'une cellule, suivie d'une parenthèse, soit : Ligne(,

Bien évidemment, en guise de cellule, nous allons lui passer notre matrice de 5 lignes pour retourner ces 5 rangs potentiels. Cette matrice est virtuelle puisque construite de toutes pièces. Nous devons donc l'interpréter.

• Saisir la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Inscrire la plage de la matrice entre guillemets, comme suit : '1:5',

Nous débutons ainsi à partir de la première ligne pour représenter le premier rang. Et nous poursuivons jusqu'à la cinquième ligne pour les énoncer tous.

• Fermer la parenthèse de la fonction Indirect,
• Fermer la parenthèse de la fonction Ligne,
• Fermer la parenthèse de la fonction Petite.Valeur,
• Fermer la parenthèse de la fonction Index,

Nous le répétons à chaque occasion. La matrice de recherche passée en premier argument de la fonction Index ne possède qu'une seule colonne. Il n'est donc pas utile de renseigner le troisième argument de cette fonction, celui de l'indice de colonne.

• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Inscrire deux guillemets ('') pour ignorer les résultats en cas d'échec,
• Fermer la parenthèse de la fonction SiErreur,
• Enfin, valider nécessairement la formule matricielle par le raccourci CTRL + MAJ + Entrée,

Les cinq Samedis semblent parfaitement extraits. Il manque néanmoins la précision sur le jour exacte de la semaine. Tout est une question de format. Nous allons profiter de la plage encore sélectionnée.

• Dans la section Nombre du ruban Accueil, déployer la liste déroulante des formats,
• Dans les suggestions, cliquer sur le format Date longue,

Aussitôt, nous obtenons la confirmation de la bonne extraction des Samedis pour le mois et l'année désignés. De plus, ils sont parfaitement regroupés et tout cela grâce à une unique formule matricielle.

{=SIERREUR(INDEX(Liste; PETITE.VALEUR(SI(JOURSEM(Liste)=7; EQUIV(Liste; Liste; 0)); LIGNE(INDIRECT('1:5')))); '')}

Pour l'extraction des Dimanches, l'adaptation est triviale. Il suffit d'ajuster le critère sur le jour de semaine dans la même syntaxe.

• Copier la syntaxe de cette formule matricielle,
• Sélectionner la plage de cellules J14:J18,
• Coller et adapter la formule comme suit :

{=SIERREUR(INDEX(Liste; PETITE.VALEUR(SI(JOURSEM(Liste)=1; EQUIV(Liste; Liste; 0)); LIGNE(INDIRECT('1:5')))); '')}

• Puis, valider le calcul matriciel par le raccourci CTRL + MAJ + Entrée,
• Enfin, appliquer le format Date longue sur la plage encore sélectionnée,

Seuls quatre Dimanches sont restitués. Cette extraction vient corroborer le résultat du dessus les dénombrant.