Niveau requis : simple utilisateur Excel
Bonjour,
Voici les explications concernant l'ensemble des modifications apportées au Planning Mensuel qui était déjà perpétuel pour la partie des pointages quotidiens mais pas du tout pour la partie des bilans hebdomadaires.
Je partage aussi une version vierge et corrigée du planning mensuel perpétuel qui pourrait être adapté et repris pour les personnes qui auraient besoin d'un modèle simple car il n'y a aucune macro et avec les explications ci-dessous cela reste tout à fait abordable pour des personnes qui n'ont pas beaucoup de compétence en développement ou codage VB.
Pour simplifier l'écriture d'une formule hebdomadaire perpétuelle, il y a aussi un outil permettant de faire la conversion automatique d'une simple formule hebdomadaire en formule perpétuelle.
Enfin, il y a ci-dessous au point 5 la procédure pour étendre sa formule hebdomadaire sur toute l'année et mieux comprendre ainsi l'utilisation des différentes méthodes de Copier-Coller (par formule et par valeur).
DecSem : valeur de décalage des semaines.
=0, commence le lundi de S1; =1, le mardi, =2 le mercredi, =3 le jeudi
=-1, commence le Dimanche de S52(ou 53) de l'année d'avant; =-2, le Samedi, =-3 le vendredi
JRS1ABS : nombre de jour de la Semaine 1 absent de l'année en cours (D14)
JrSem : indexe des jours de la semaine nommées normalement et en abrégés (déjà existant mais déplacé en haut et nommé)
Mois : indexe des mois de l'année nommées normalement (déjà existant mais déplacé en haut et nommé)
NotBix : état = 1, si l'année en cours n'est pas une année bissextile, sinon 0
Les indexes des années acceptées, des mois et des jours ont été déplacés et insérés en haut de la feuille. En effet, il ne faut rien à côté de la zone de pointage car il y a toujours possibilité d'ajout ou de suppression d'un collaborateur et donc potentiellement d'une ligne => suppression de référence ou insertion de blanc si celles-ci sont placées sur les colonnes à la suite du planning.
Remarque : les lignes masquées hébergeant des données de paramétrage sont balisées en colonne 'A' avec des motifs quadrillés sur fond jaune vif.
Les semaines se trouvant à cheval entre les années et les mois, il n'est pas possible d'aligner le bilan hebdomadaire sur les jours sans quoi il faut refaire le modèle chaque année pour respecter le nouveau découpage !
Voici 7 règles de gestion (rien de biblique ici) permettant de faciliter la modélisation d'un bilan perpétuel :
R.G.1 : 1 semaine appartient à une année si elle comporte au moins 4 jours dans cette année.
R.G.2 : 1 semaine appartient à un mois si elle comporte au moins 4 jours dans ce mois.
R.G.3 : 1 année comporte 52 ou 53 semaines.
R.G.4 : 1 mois comporte 4 ou 5 semaines.
R.G.5 : le 29 février appartient toujours à la semaine 9 quelque soit l'année.
R.G.6 : un bilan perpétuel doit toujours permettre la saisie quotidienne jusqu'au 3 janvier de l'année suivante. En effet, la dernière semaine peut dépasser de 3 jours maxi de l'année en cours.
R.G.7 : un bilan perpétuel doit prévoir une procédure pour la copie par valeur du bilan hebdomadaire de la semaine 1 appartenant à l'année suivante si elle commence sur l'année en cours. La copie se fera au moment de l'archivage de l'année en cours en fin d'exercice.
Le choix d'avoir un classeur sans macro (.xlsx) permet d'éviter tout problème de code malveillant et est moins difficile à maintenir. La contrainte posée au départ étant d'avoir un modèle perpétuel sans code VBA avec uniquement des formules.
Le compromis trouvé est de toujours positionner les bilans (ou formules) des semaines sur des positions figées. Pour savoir comment attribuer les semaines à une année ou à un mois, il suffit que la semaine comporte au moins 4 jours dans cette année ou ce mois (R.G.1 & 2). Pour simplifier la visibilité dans le bilan avec le scrolling horizontal, les semaines appartenant à un mois commence sur la 1ère colonne du mois et chaque champ du bilan contenant une formule contient 4 cellules fusionnées. Du coup sur chaque mois il y a 5 colonnes de semaine (R.G.4) de 4 cellules soit un 4x5 = 20 colonnes réelles.
La 5ème semaine du mois est invisibilisé si celui-ci n'en comporte que 4. Dans ce bilan hebdomadaire il n'y a que des données positives ce qui simplifie le problème par l'utilisation d'un format n'affichant que les valeurs positives : 0,0;"" (rappel : Val>0;Val<0;Val=0;Texte).
Les formules sont protégées au travers de la fonction du menu Données->Validation des données avec, dans l'onglet Option les paramètres Autoriser à Personnalisé et Formule à FAUX pour empêcher toute modification.
Reprenons la formule du Nbr Heures CDI : =SOMME(E46:I53)
Comme les semaines sont physiquement décalé en fonction de l'année, la zone E46:I53 doit être calculée différement car les lignes sont bonnes mais pour les colonnes cela dépend du décalage et du nombre de jour de la semaine (7 jours sauf la semaine 1 de l'année en cours et de l'année suivante). C'est pour cela que chaque semaine comporte 3 paramètres (L67 à L69) : Décalage par rapport à la colonne du 1er Janvier, Total de jour de la semaine et l'état 1 ou 0 d'invisibilité.
La formule doit être modifiées ainsi pour tenir compte du décalage :
=SOMME(DECALER($E$41:$E$41;;J$67;;J$68))
On part de la colonne E à E(1er Janvier) avec un décalage de J$67 colonne(s) et J$68 nombre de colonne.
La formule doit être modifiées ainsi pour tenir compte de l'invisibilité (si <0, la valeur ne s'affiche pas) :
=SOMME(E46:I53) devient =SI(AC$69;-1;SOMME(E46:I53)) avec AC$69 l'état 1 ou 0 d'invisibilité
Résultat :
=SI(J$69;-1;SOMME(DECALER($E$41:$E$41;;J$67;;J$68)))
Un classeur sans macro ci-joint permet la conversion automatique d'une formule simple (comportant jusqu'à 12 zones) avec les paramètres adéquates spécifiques à 'Reporting Agence.xlsx' en Méta-Formule Perpétuelle. Les paramètres devront être modifiés si des lignes ou des colonnes de la feuille 'Planning mensuel' sont insérées ou supprimées.
Remarque : le "programme" par formule remonte le résultat grâce à l'"Ascenceur" de la colonne 'V'. Déployez le volet en colonne 'W' si ce modèle vous intéresse.
L'utilisation d'une formule perpétuelle implique une contrainte pour les étendre à l'ensemble des semaines de l'année :
- Etablir la formule "normale" dans la colonne "S1 BRUT".
- A partir de l'outil ConversionFormuleHebdo la convertir en formule perpétuelle qui sera copiée par valeur dans la colonne "S1 en 2025" si l'année 2025 est l'année en cours.
- Copiez par Formule la cellule depuis la colonne "S1 en 2025" vers la colonne "S2" puis l'étendre jusqu'au bout du mois de Janvier, y compris sur la 5ème semaine si elle n'est pas visible.
- Copiez la cellule depuis la semaine 2 à 5 (au choix) vers la 1ère semaine du mois de Février puis l'étendre jusqu'à la fin du mois, y compris sur la 5ème semaine du mois si elle n'est pas visible.
- Copiez les cellules des 5 semaines du mois de Février vers le mois Mars. Validez le message d'avertissement d'Excel (Excel ne gère pas très bien les cellules fusionnées).
- Répétez 9 fois l'opération de Collage pour les mois d'Avril à Décembre.
Une formule de lien hypertexte avec la date du jour a été ajoutée en cellule 'C14' qui positionne la sélection sur le jour actuel et sur le 1er Janvier si l'année en cours n'est pas l'année actuelle.
Formule de lien hypertexte avec les paramètres cible et intitulé : C14=LIEN_HYPERTEXTE($C$13&"!"&$D$18;$D$13)
$C$13 : [{Nom du Fichier}]'{Nom de la feuille}'
$D$18 : {Cellule ou Zone}. Exemple : CK18 ou K18:O22
$D$13 : Date du jour formatée
Comment obtenir [{Nom du Fichier}]'{Nom de la feuille}' :
C11=CELLULE("nomfichier";$D$13) avec le 2ème paramètre optionnel mais très important (Risque d'erreur si omis). Il suffit de choisir une cellule appartenant à la feuille cible.
Puis il faut supprimer le chemin du fichier mais heureusement le nom du fichier est entre crochets :
B11=CHERCHE("[";$C$11) B12=CHERCHE("]";$C$11)+1 B13=NBCAR($C$11)
C12=STXT($C$11;B11;B12-B11)
Enfin rajout du nom de la feuille entre simple quote :
C13=C12&"'"&STXT(C11;B12;B13)&"'"
Formule pour avoir la date du jour formatée :
D13=NOMPROPRE(TEXTE(AUJOURDHUI();"[$-x-sysdate]jjjj, mmmm jj, aaaa"))
Déterminez la cellule du jour ciblé :
Convertir la date en nombre de ligne de décalage (=1 si on n'est pas sur l'année actuelle) :
D17=SI(an=ANNEE(AUJOURDHUI());JOURS(AUJOURDHUI();DATE(an;1;1))+1;1)
Convertir le décalage en libellé de la cellule du jour actuel (prise en compte de l'année bissextile) :
D18=SUBSTITUE(CELLULE("adresse";DECALER(D18;;D17+SI(D17>59;NotBix;0)));"$";"")
