http://tssperformance.com/wp-admin/theme-editor.php?file=content-archive-portfolio.php&theme=canvas

Comment ai-je fait l’outil du planning du personnel de la boutique

Bonjour,

Dans l’article précédent je vous ai présenté un « sympathique » outil de planning dans ma boutique

 Je vais essayer succinctement de vous expliquer comment j’ai fait cet outil sans VBA de manière plutôt simple.

Mon principe est le même, j’ai utilisé la méthode des 3T (présentée dans mon livre: « Booster votre reporting financier ») juste dans ce cas, je n’ai même pas eu besoin d’utiliser de tableau croisé dynamique.

La feuille donnée

Dans cette feuille, on y a inséré les données de base: l’année en cours, la liste de département, le détail sur les employés, la liste des jours fériés, le samedi Off ou pas.

Dans cette feuille, il faut juste noter certains outils Excel:

1.    Nommer les plages de cellules

Liste de département D5:D11,

Liste des employés F5:I57

Liste des jours fériés K5:K11

L’année en cours B5

Et le Samedi Off M5

Ici la technique est de créer des noms dynamiques de telle manière que le nombre de lignes du nom s’augmente avec le nombre d’enregistrements. Pour y arriver, j’ai utilisé la fonction DECALER et NBVAL comme je l’ai fait pour créer des axes élastiques dans le graphique élastique ou pour créer une liste déroulante dynamique

2.    Créer des listes déroulantes:

Il existe des listes déroulantes dans le tableau sur les détails des employés pour la colonne « Départements » et aussi en dessous de Samedi OFF (on retrouve Oui ou Non)

Pour en savoir plus sur les listes déroulantes vous pouvez cliquer ici: Les experts de la validation de données

 

La feuille Enregistrement

La feuille est plutôt simple, nous y retrouvons juste principalement 2 types de formules:

Les formules de recherche pour retrouver le matricule et le département de l’employé et une formule pour calculer le nombre de jour entre deux dates en tenant compte des jours fériés et des week-ends.

·         La formule de recherche : j’ai utilisé les fonctions INDEX et EQUIV qui restent les plus appropriées pour ces recherches

=INDEX(Données!$F$5:$F$800; EQUIV(Enregistrement!D5;Données!$G$5:$G$800;0))

Pour en savoir plus sur la fonction INDEX, cliquer ici

Pour en savoir plus sur la fonction EQUIV, cliquer ici

 

·         La formule pour retrouver le nombre de jour entre 2 dates en tenant compte des week-ends et des jours fériés

J’imagine déjà certains d’entre vous se demander si ce n’est pas la fonction DATEDIF (cliquer ici pour en savoir plus sur DATEDIF). He bien non, ce n’est pas cette fonction. Il s’agit plutôt de la fonction

NB.JOURS.OUVRES.INTL. Cette fonction tient compte de tous les éléments dont nous avons besoin.

=NB.JOURS.OUVRES.INTL(H5;I5; SI(SAM_OFF= »NON »;11;1);Listes_Jours_fériés)

 

La feuille Rapport calendrier

Je suppose que la principale question est de savoir comment faire changer les couleurs du calendrier selon le choix des employés…

Déjà, pour avoir une idée de comment mettre en place un calendrier dans Excel, vous pouvez vous référer aux articles Calendrier Excel. Le principe pour les couleurs est de mettre plusieurs mises en forme conditionnelles pour tester les congés, les absences, les permissions et les sanctions.

Avant de vous donner le type de formule, je tiens à vous préciser qu’il s’agit de formule matricielle plus ou moins compliquées:

=SOMME((F18<> » »)*(Noms1=$J$10)* (Date_debut1<=F18)*(Date_fin1>=F18)* (Situation1= »Congés »))=1

 

Petites explications:

·         (F18<> » ») permet de tester si il y a une date

·         (Noms1=$J$10) permet de sélectionner dans la feuille enregistrement uniquement les lignes pour lesquelles le nom correspond au nom choisi dans le calendrier. En effet Nom1 correspond à la plage de cellules des noms dans la feuille enregistrement

·         (Date_debut1<=F18)*(Date_fin1>=F18) pour s’assurer que la date dans le calendrier est comprise dans les dates début et fin des enregistrements

·         (Situation1= »Congés ») c’est pour s’assurer que l’enregistrement en question est bel et bien un congé

 

Ok je vous avais prévenu que la formule est un peu compliqué (je vais peut être préparé un cours sur les formules matricielles). Mais déjà dans www.excel-academie.com il y a plusieurs vidéos sur les formules matricielles voici le programme de formation: Formule Matricielle

Pour les autres couleurs, il suffit de remplacer dans la formule Congés par Permission ou Absences…

Vous pouvez directement achetez l’outil en cliquant ici.

 

Si vous avez des questions n’hésitez surtout pas à utiliser les commentaires.

One Response to Comment ai-je fait l’outil du planning du personnel de la boutique

  1. Abdoul 11 mai 2017 at 15 h 40 min #

    Cher Stéphane,

    Je vous remercie de ce tuto. Cependant j’aimerais comprendre pour la formule  »=SOMME((F18 » »)*(Noms1=$J$10)* (Date_debut1=F18)* (Situation1= »Congés »))=1  », ce que F18 représente et pourquoi (Date_debut1=F18),

    Je vous remercie d’avance.

    A bientôt

Laisser un commentaire