Calculer les jours fériés du calendrier français

Comment calculer les jours fériés d'une année calendaire 

Il est souvent utile lorsque l'on travaille sur la gestion de plannings et de calendriers de gérer les jours fériés de l'année en cours. 

Je vais vous montrer comment réaliser le tableau ci-dessous qui regroupe les 13 jours fériés du calendrier français et ensuite l'utiliser dans des formules de calculs de jours ouvrés.

A) Calcul des jours fériés :

• Taper l'année en cours dans la cellule A1 : 2014 
• Calcul du jour de l'an (A3) : =DATE(A1;1;1)
• Calcul du jour de Pâques (A4) :
  =DATE(A1;3;29,56+0,979*MOD(204-11*MOD(A1;19);30)-JOURSEM(DATE(A1;3;28,56+0,979*MOD(204-11*MOD(A1;19);30))))
• Calcul du Lundi de Pâques (A5) : =A4+1
• Calcul du jour de la Fête du travail (A6) :=DATE(A1;5;1)
• Calcul du jour de la victoire (A7) : =DATE(A1;5;8)
• Calcul du jour de l'Ascension (A8) : =A4+39
• Calcul de la Pentecôte (A9) : =A4+49
• Calcul de Lundi de Pentecôte (A10) : =A4+50
• Calcul du jour de la Fête Nationale (A11) : =DATE(A1;7;14)
• Calcul du jour de l'Assomption (A12) : =DATE(A1;8;15)
• Calcul du jour de la Toussaint (A13) : =DATE(A1;11;1)
• Calcul du jour de l'Armistice (A14) : =DATE(A1;11;11)
• Calcul du jour de Noël (A15) : =DATE(A1;12;25)

B) Mise en forme en Gras des jours fériés tombant un Samedi ou un Dimanche :

• Sélectionner la plage de cellules : (A3:A15)
• Onglet Accueil / Groupe Style / Mise en forme conditionnelle/Nouvelle règle...
• Compléter la boite de dialogue comme suit :

C) Nommer la plage des jours fériés :

• Sélectionner la plage de cellules : (A3:A15)
• Onglet Formules/Groupe Noms définis/Définir un nom
• Compléter la boite de dialogue comme suit :

D) Utilisation des jours fériés dans des calculs :

• Exemple n° 1 : Calculer le nombre de jours ouvrés avec les jours fériés entre le
  mardi 1 avril 2014 et le jeudi 15 mai 2014 

• Exemple n° 2 : Calculer le nombre de jours ouvrés sans les jours fériés entre le
  mardi 1 avril 2014 et le jeudi 15 mai 2014 

Exclure des données hors-norme dans les calculs

Comment exclure d'un calcul des données hors-normes

Prenons l'exemple suivant pour illustrer notre calcul : 

Explications :

Pour le mois de janvier, la moyenne classique est de 17193. On peut observer que les chiffres de JACQUES et de HENRI sont hors normes. Pour éviter de fausser nos calculs, nous allons donc demander à Excel d'exclure de notre calcul les deux valeurs extrêmes (la plus petite et la plus grande).

Calculs :

Si nous prenons le mois de Janvier : 

• Notre plage contient 7 valeurs : (C34:C40)
• Nous allons utiliser un pourcentage de 0,3 : 7 valeurs *0,3 = 2,1
  - Le multiple de 2 le plus proche de 2,1 est 2
• Les critères seront (pour la ligne 42) :
  - matrice : C34:C40
  - pourcentage : 0,3

Formule : 

La formule sera : =MOYENNE.REDUITE(C34:C40;0,3)

Résultat : 

Excel va donc exclure du calcul la valeur la plus petite et la valeur la plus grande. 7000 et 49800 ne seront donc pas pris en compte dans le calcul de la moyenne réduite (ligne 42) du mois de janvier.
Pour comparaison, la moyenne incluant tous les montants est calculée en ligne 41. Elle est nettement supérieure.

Créer un graphique évolutif avec la fonction Decaler()

Comment créer un graphique évolutif avec la fonction Decaler()

Lors de précédent tutoriel, je vous ai montré des possibilités d'utilisation de  la fonction Decaler(). Aujourd'hui je vais vous expliquer comment réaliser un graphique évolutif et dynamique grâce à cette fonction. 

Pour illustrer notre explication, prenons l'exemple ci-dessous : 

Dans cet exemple, les données de mon graphique vont passer de Août à Septembre pour aller jusqu'en Décembre. Au lieu de mettre à jour à chaque fois notre graphique lors d'une saisie d'une nouvelle donnée, il serait plus simple d’avoir un graphique évolutif et dynamique qui grandit en même temps que ses données source. 

C’est ce que nous allons apprendre à faire: créer un graphique évolutif. 

I) Etape n° 1 : Créer l'axe horizontal et la série de données évolutifs

Pour créer un graphique dynamique de la sorte, il faut donc créer :

-          Une série de données évolutive

-          Et un axe horizontal évolutif

Nous allons passer évidemment par la fonction DECALER (pour bien comprendre cette fonction, vous pouvez vous référez à mes articles précédents sur la fonction Decaler)

a) Nom pour série de données évolutive :

-          Dans le ruban : Onglet Formules/ Groupe Noms définis/Définir un nom

-          Dans la boite de dialogue Nouveau nom, dans Nom écrire Paul, Fait référence à écrire: =DECALER(Feuil1!$B$2;1;;NBVAL(Feuil1!$B$3:$B$14))

-          Valider

Faire de même pour les données concernant Pierre, la formule à taper dans Fait référence à sera : 
=DECALER(Feuil1!$C$2;1;;NBVAL(Feuil1!$C$3:$C$14)) et le nom : Pierre

b) Nom pour l'axe horizontal des mois évolutif :

-          Dans le ruban : Onglet Formules/ Groupe Noms définis/Définir un nom

-          Dans la boite de dialogue Nouveau nom, dans Nom écrire Axe, Fait référence à écrire: =DECALER(Feuil1!$A$2;1;;NBVAL(Feuil1!$A$3:$A$14))

-          Valider

II) Etape n° 2 : Insérer les noms créés dans le graphique

Maintenant que les noms ont été créés, on va les mettre comme données source dans le graphique.

-          Sélectionner le graphique;

-          Bouton droit de la souris, cliquer sur Sélectionner les données;

-          Cliquer sur Paul, puis sur Modifier

Dans la boite dialogue modifier la série, dans valeur écrire la formule =Feuil1!Paul et valider

Faire de même avec les données de Pierre, dans valeur écrire la formule =Feuil1!Pierre et valider

Dans la boite de dialogue Sélectionner la source de données, du côté de Etiquettes de l’axe horizontal, cliquer sur Modifier

Dans la boite de dialogue, écrire =Feuil1!Axe et valider 2 fois 

Et voilà, votre graphique est maintenant évolutif. Dès qu'une données sera ajoutée, par exemple celle du mois de Septembre, le graphique évoluera également dynamiquement comme ceci :

Réaliser des sommes conditionnelles

Trois façons de réaliser des sommes conditionnelles

Il existe plusieurs manières de réaliser des sommes conditionnelles. Je vais vous montrer trois façons différentes de réaliser des sommes à partir d'un critère.

Prenons l'exemple ci-dessous pour illustrer ces méthodes : 

L'objectif est de réaliser la somme des ventes effectuée par chacun des commerciaux, pour cela nous allons saisir les noms des commerciaux dans la plage de cellules E4:E99 comme ceci:

A) Méthode n° 1 : Utiliser la fonction Somme.si

• Se positionner sur la cellule F4 
• Taper la formule : =SOMME.SI($B$4:$B$21;E4;$C$4:$C$21)
• Copier cette formule sur la plage F5:F9

B) Méthode n° 2 : Utiliser la fonction SommeProd

• Se positionner sur la cellule H4 
• Taper la formule : =SOMMEPROD(($B$4:$B$21=E4)*$C$4:$C$21)
• Puis valider la formule avec les touches : Ctrl +Maj + Entrée
• Copier cette formule sur la plage H5:H9

B) Méthode n° 3 : Utiliser la fonction Somme

• Se positionner sur la cellule J4 
• Taper la formule : =SOMME(($B$4:$B$21=E4)*$C$4:$C$21)
• Puis valider la formule avec les touches : Ctrl +Maj + Entrée
• Copier cette formule sur la plage J5:J9

Nous obtenons le résultat suivant : 

Désormais vous savez réaliser des sommes conditionnelles de trois manières différentes. A vous de choisir celle qui vous convient le mieux.

La fonction SOMMEPROD() - Suite

Comment utiliser la fonction SOMMEPROD() pour compter et calculer avec des critères

Je vous ai présenté lors d'un précédent tutoriel l'utilisation de la fonction SOMMEPROD(), aujourd'hui nous allons voir que cette fonction permet de réaliser des calculs complexes en tenant compte de critères.

Prenons notre exemple précédent pour bien illustrer cette fonction : 

• Premier objectif : compter le nombre de fois que l'on retrouve un critère dans le tableau (l'équivalent serait la fonction NB.SI()
• Second objectif : faire en une seule formule les sommes et le produit à partir d'un critère

Exemple 1 : Compter le nombre de ventes réalisées par Jacques dans la région Ouest 

• En G3 taper : Jacques
• En I3 taper : Ouest 
• Se positionner sur la cellule I6 et taper la formule suivante :
   =SOMMEPROD((B2:B20=I3)*(C2:C20=G3))

Explications :

- Pour la colonne B2:B20 (qui correspond à nos régions), la fonction va vérifier pour chaque ligne d'enregistrement si le lieur en question correspond au lieu présent dans notre critère (ici Ouest en cellule I3)

- Pour la colonne C2:C20 (qui correspond à nos vendeurs) , la fonction va tester si pour chaque enregistrement la valeur correspond à notre critère (ici Jacques en celllule G3)

- Le fait d'avoir inscrit le * entre les deux tests permet de compter uniquement si les deux tests ont la valeur VRAI en même temps. En résumé, le "*" va se comporter ici comme l'opérateur logique "et"

Résultat :

Exemple 2 : Calculer le montant des ventes réalisées par Jacques et en région Ouest

• Se positionner sur la cellule I8 et taper la formule suivante :
  =SOMMEPROD(((B2:B20=I3)*(C2:C20=G3)*(D2:D20)*(E2:E20)))

Résultat :

Vous pouvez désormais modifier les valeurs dans les zones de critères afin d'obtenir les calculs correspondants. 

La fonction SOMMEPROD()

Comment utiliser la fonction SOMMEPROD

Je vais vous montrer comment utiliser la fonction SOMMEPROD qui est malheureusement très peu utilisée et qui vous allez voir est très puissante et permet de réaliser des calculs parfois très complexe.

Prenons l'exemple ci-dessous :

Si je vous demande de calculer le montant des ventes total, comment vous y prenez-vous ?

• Je suppose que vous allez dans une première étape ajouter un colonne supplémentaire
• Puis vous y ferez un calcul du genre Quantité * Prix Unitaire pour chacune des cellules de la colonne et ainsi vous obtiendrez le montant par jour des ventes.
• Enfin vous ajouterez une cellule Total dans laquelle vous ferez l'addition de chaque vente. 

Avec la fonction SOMMEPROD, vous pouvez éviter toutes ces étapes

• Se positionner sur la cellule : E22
• Ecrire la formule : =SOMMEPROD(D2:D20;E2:E20)

Explication : 

La fonction SOMMEPROD va multiplier les plages de cellules sélectionnées et ensuite faire la somme.  Elle peut ainsi réaliser ce même type de calcul pour plusieurs plages de cellules.

Résultat : 

J'aborderais dans un prochain tutoriel d'autres façons d'utiliser la fonction SOMMEPROD, vous serez certainement  surpris de la puissance de cette dernière

Comment utiliser la fonction INDIRECT ?

Comment animer un tableau ou un graphique avec la fonction INDIRECT()

Aujourd'hui je vais vous montrer comment rendre dynamique les données d'un tableau ou d'un graphique très facilement en utilisant la fonction INDIRECT.

Prenons l'exemple suivant représentant les ventes annuelles réalisées par plusieurs commerciaux  :

A) Etape 1 : Nommer les tableaux 

• Sélectionner la première plage de cellules : B5:E17
• Onglet Formules/Groupe Noms définis/Définir un nom /
• Compléter la boite de dialogue comme ci-dessous puis valider par OK

Faire de même pour les 2 autres tableaux :

• Sélectionner le deuxième tableau : G5:J17
• Nommer la zone des cellules  : Paul
• Sélectionner le troisième tableau : L5:O17
• Nommer la zone des cellules : Jacques

B) Etape 2 : Réaliser une validation de données 

Cette étape va consister de réaliser une liste déroulante affichant les noms des trois commerciaux  (Dans notre cas, il s'agira des 3 zones nommées précédemment)

• Sélectionner la cellule B20
• Onglet Données/Groupe Outils de données/Validation de données/Validation de données...
• Compléter la boite de dialogue comme suit puis valider par OK:

• Tester votre liste déroulante, vous devez voir apparaître la liste des trois commerciaux 

C) Etape 3 : Créer le tableau dynamique

Cette étape va consister à créer un tableau dynamique à partir de notre liste déroulante et de la fonction INDIRECT()

• Sélectionner la plage de cellules B24:E34  qui correspond  à notre trame de tableau qui possède 13 lignes (En-têtes + 12 mois de l'année) et 4 colonnes (Les mois + 3 produits)
• Dans la barre de formule taper : =INDIRECT(B20) puis valider par les touches : Ctrl + Maj + Entrée (permet de réaliser un calcul matriciel)
• Votre formule est désormais entre accolades { }, comme ceci : {=INDIRECT(B20)}
• Désormais, si vous changez le nom de votre liste déroulante en sélectionnant le nom d'un autre commercial, le tableau associé à ce dernier s'affichera.

D) Etape 4 : Créer le graphique dynamique

Cette dernière étape va consister à faire une représentation graphique du tableau créé précédemment

• Sélectionner la plage de cellules : B24:E34
• Onglet Insertion/Groupe Graphiques/Colonne/Histogramme Empilé
• Votre graphique est désormais dynamique, puisqu'il affichera les données du tableau matriciel qui lui-même est lié à notre liste déroulante.

Votre représentation devrait ressembler à celle-ci :