Discussion :

[codesite]

Bonjour à tous, j'aurais besoin de votre aide (mon niveau d'Excel est très basique).

Désolé d'avance du pavé, mais j'ai essayé d'être le plus clair et précis possible.

Je voudrais créer un classeur pour calculer mes heures de prises de congé, en fonction du type d'heures utilisées (heures supplémentaires : HE (en orange), vacances annuelles : VA (en vert) et vacances annuelles de l'année passée : VA-1 (en bleu)) et en tenant compte de contraintes (j'ai mis des couleurs pour pouvoir s'y retrouver).

Pour les heures VA-1 et VA, c'est déjà bon.

Pour les HE, elles sont comptabilisées dans un pot par mois (des HE faites en janvier sont comptabilisés dans le pot du mois de janvier, celles en février dans le pot de février etc... ;1 pot de HE par mois). Chaque pot à une validité de 3 mois (exemple : des HE faites en janvier peuvent être prises en février, mars ou avril, mais pas au-delà ; des HE faites en février peuvent être prise en mars, avril ou mai, mais pas au-delà ; etc...). Au-delà de 3 mois, elles sont perdues (utilisées ou pas).


Mon classeur est divisé en 2 parties : celle de gauche (le calendrier sur 3 années) et celle de droite (le tableau).


Mon contexte :

Des heures HE sont faites chaque mois (encodées moi-même pour chaque mois dans le tableau de droite : janvier, AP8 ; février, AP9 ; etc... ; décembre, AP19, etc...). Ces heures sont utilisables maximum 3 mois après le mois où elles ont été faites.


Dans la partie de gauche, j'ai une colonne (AH) où sont calculé les heures totales récupérées sur un mois :

Janvier : - total des heures HE récupérée en AH4
Février : - total des heures HE récupérée en AH8
etc...


Mon souhait :

Je voudrais que dans la colonne AQ (pour chaque mois) soit inscrit/calculé le nombre de HE déjà utilisé ; qui tient compte des heures déjà récupérées dans le/les mois suivant et qui tient compte de la date limite pour chaque mois (c'est-à-dire que si des heures n'ont pas été reprisent avant la date limite, celles-ci sont perdues/plus comptabilisées).

Et dans la colonne AR, je voudrais que soit calculé les heures HE qui restent à prendre avant la date limite pour chaque mois (exemple : janvier, maximum le 30 avril ; pour février, maximum le 31 mai ; pour mars, maximum le 30 juin etc...). Ça normalement, c'est bon.

Exemple pour les heures reprises en juillet :

Dans le tableau à droite :
- 20h30 d'heures HE sont encodées en AP11 pour le moi d'avril
- 8h00 d'heures HE sont encodés en AP12 pour le mois de mai
- 8h00 d'heures HE sont encodés en AP13 pour le mois de juin

Dans le calendrier à gauche, au mois de juillet, au total 34h12 d'HE ont été récupérées (AH28).

Dans le tableau à droite, dans la colonne AQ, pour le mois :
- d'avril, il sera indiqué 0h00 (car toute les heures ont été récupérées)
- de mai, il sera indiqué 0h00 (car toute les heures ont été récupérées)
- pour le mois de juin, il sera indiqué 2h18 (car il reste 2h18 à récupérés pour maximum le 30 septembre)


Je suis sous Excel 2021 FR.


En vous remerciant grandement d'avance


PS : si vous pensez qu'il faut ajouter des colonnes, des étapes intermédiaires supplémentaires (sur la même feuille ou sur une autre feuille), ou s'il faut utiliser du code VBA, n'hésitez pas à me le dire. Je m'adapterai.


Ci-dessous mon fichier Excel.

calendrier-conge - vraies HE encodées - vierge.xlsx

[EricDgn]

Voir si ceci est bien ce qui est recherché!
Cdt.

[codesite]

Bonjour EricDgn,
un grand merci pour ta réponse. C'est exactement ce dont j'avais besoin.

Pour ne pas mourir bête, peux-tu m'expliquer le fonctionnement des calculs que tu as mis en place (j'ai compris que AZ dépend de AY, qui dépend de AX, qui dépend de AW, mais pas le reste). Je ne comprends pas la logique des calculs.


En te remerciant d'avance

[EricDgn]

Bonjour,

Voilà le principe, en partant d'une cellule particulière:

en AW12:
ce qui me reste du mois précédent = AX11
ce que j'ai récupéré ce mois = AV12
si AX11 >AV12: je récupère AV12 et il me reste AX11-AV12
sinon je récupère entièrement ce qui reste de ce mois précédent, et il reste 0

en AX12:
si AW12=0, c'est que j'ai vidé tout ce était disponible sur le mois d'avant donc je regarde ce qui est prenable pour ce mois
et je vais donc comparer ce que j'ai récupéré ce mois, AV12, à ce qui était disponible pour les 2 mois avant, AX11+AY11
si AX11+AY11>AV12 alors je n'ai pas tout vidé, il me reste pour le 2e mois la différence AX11+AY11-AV12
sinon, j'ai tout récupéré et il reste 0
sinon je conserve ce qui était disponible, càd AY11

mêmes raisonnements pour les 2 mois suivants, la plage de mois à prendre ne compte s'étendant au fur et à mesure.

Bien cordialement.

[codesite]

Bonjour EricDgn,
merci beaucoup pour cette explication.

Grâce à toi, j'ai pu beaucoup avancer sur mon fichier. J'ai d'ailleurs changer un peu la présentation/utilisation de mon fichier suite à cela.

Après avoir essayé moi-même pendant plus d'1 semaine, j'aurai encore besoin de tes lumières. Je souhaiterai ajouter les "fonctions" suivantes (sans macro si possible) :

1°) Je voudrai ajouter une validation des données pour les HE encodées dans le calendrier (j'ai déjà réussi pour les VA et VA-1). Je voudrai que si je n'ai plus de HE disponible, un message s'affiche pour me le signaler et m'empêche d'encoder. J'ai déjà essayé différentes approches, mais rien n'y fait (je crois que ce qui pose problème, c'est au niveau du calcul des heures lorsqu'elles arrivent à 00:00 ; s'il reste 0:01, c'est OK. Mais si je passe à 00:00, ça pose problème (voir HE calendrier janvier)).

2°) Je souhaiterai ajouter une date limite de prise pour les VA-1. Je voudrais que si j'encode une date en AV29, on ne puisse pas encoder des VA-1 dans le calendrier au-delà de cette date (ex. j'encode la date du 12-05-2024 en AV29. Dans le calendrier, si j'encode des heures jusqu'au 12 mai inclus, OK tout fonction/se calcul normalement. Et si j'encode des heures après le 12 mai, un message m'indique que c'est pas possible car la date est dépassée (je suppose qu'on fait ça avec la validation de donnée, mais je ne sais pas comment combiner ça avec le fait de ne plus avoir d'heures disponibles). Il semblerait que j'ai aussi trouvé.

3°) Je voudrai aussi créer une feuilles "vierge" (20XX), où je n'ai qu'à encoder la date de la nouvelle année en A1, et que toutes les cellules qui dépendent de l'année d'avant soient "reliées/mises à jour" automatiquement. Par exemple, je fais une copie de la feuille vierge "20xx" et je la renomme "2025". Je change l'année 2025 en A1. Les cellules en AN5, AN6, AN7, AL25, AM25, AN25 sont automatiquement changées pour pointer les cellules de la feuille "2024".
Si ce n'est pas possible, alors une méthode "simple", sans devoir encoder des formules manuellement (je compte partager ce fichier avec mes collègues qui, pour certains, ne sont pas des pros d'Excel). J'ai trouvé (merci ChatGPT). J'ai utilisé les fonction Indirect et Concatener pour y arriver (une cellule avec le nom de l'onglet, une autre cellule pour la référence de la cellule et une 3ème qui synthétise le tout avec les bons caractères, avec le bon format de cellule). Pour ceux que ça pourrait intéresser, la formule est : =INDIRECT("'" & $AL$5 & "'!" & $BE$12) avec AL5 = année et BE12 = référence cellule. Je précise que mes onglets sont tous identiques, seuls les heures changes.


En te remerciant grandement d'avance pour le temps que tu prendras pour m'aider.

Cordialement.

récup HE - essai2.xlsx

[codesite]

Bonjour EricDgn,
je voulais éditer mon message précédant, mais je n'y arrive plus.

Le point 1°) est résolu lui aussi.
Pour contourner le problème des heures, j'ai créer 1 étape intermédiaire qui vérifie (pour le mois en cours) si : HE dispo - HE prises = HE dispo (renvoie 1 si vrai et 0 si faux). Et dans la validation des données, je demande si l'étape intermédiaire>0 (>0, écriture permise) pour le mois en cours (parce que je ne peux pas prendre des HE de mai pendant le mois de mai). J'ai également du adapter la validation de données pour chaque ligne de chaque mois.

Pour le point 2, pour ceux que ça pourraient intéresser, j'ai inscrit la date limite voulue en AW29 et utilisé la fonction Décaler (encore une fois, merci ChatGPT pour l'idée). La formule que j'ai inséré dans la validation des données (pour chaque ligne de chaque mois) est : =ET(DECALER(C9;-3;0)<=$AW$29;$AM$30>=0).
Ici, Décaler va voir la date située 3 lignes au-dessus (-3) et dans la même colonne (0) (Excel en système A1 et pas L1C1), et va vérifier si la date est antérieure ou égale à celle inscrite en AW29. Si oui, écriture permise. La formule vérifie aussi s'il reste de heures à prendre en AM30.
J'ai essayé avec la fonction Indirect. Seule, elle fonctionne. Mais combiné avec AM30>0, elle ne marchait pas.


J'aurais quand même une question sur le calcul des HE que tu m'as fait : pourquoi y-a-t-il un calcul qui se fait plus loin que les -3 mois d'un mois concerné (en BK16, BK18 et BK20) ? Pourquoi c'est pas 00:00 qui s'affiche/se calcul ?


En te remerciant d'avance

Cordialement



récup HE - essai2.xlsx

[EricDgn]

Pourquoi y-a-t-il un calcul qui se fait plus loin que les -3 mois d'un mois concerné (en BK16 à BK20) ?

Il ne faut plus faire de calcul en-dessous de ce qui correspond à décembre, dernier mois de l'année. Par contre quand on va sur la feuille de l'année suivante, il faut y reprendre les 3 derniers mois de l'année précédente, au-dessus des calculs correspondant au mois de janvier.

Bien cordialement.

[codesite]

Il ne faut plus faire de calcul en-dessous de ce qui correspond à décembre, dernier mois de l'année.

D'accord, mais si j'inscris 00:00 en février, ça me calcul quand même des heures incorrectes/imaginaires (cf. fichier joint). Comment résoudre ce problème ?

Par contre quand on va sur la feuille de l'année suivante, il faut y reprendre les 3 derniers mois de l'année précédente, au-dessus des calculs correspondant au mois de janvier.

C'est déjà le cas. Je compte créer par la suite une feuille "vierge" (20XX) où simplement en inscrivant le nom de l'année comme onglet et l'année en A1, tout se calcul automatiquement (les formules sont déjà créée en ce sens).


récup HE - essai2b.xlsx

[EricDgn]

D'accord, mais si j'inscris 00:00 en février, ça me calcul quand même des heures incorrectes/imaginaires (cf. fichier joint).

Désolé, je ne vois pas l'erreur produite. Dans quelle cellule exactement ces 00:00 sont-ils entrés?

[codesite]

Désolé, je vais essayer d'être le plus claire et précis possible.

Pour l'exemple (cf. fichier joint) :
Dans la cellule AO8, j'encode 11h48 d'HE faites (valeur fixe).
Je prends 00h48 au mois de février. Il reste 11h00 à prendre pour le mois de janvier (AQ8).
Si je ne reprends pas ces 11h00 (elles sont perdues au bout des 3 mois) et/ou que je ne fais pas d'heures supplémentaires (AO9 = 00:00, AO10=00:00, etc...), la feuille calcul des "HE imaginaires" en AQ10 (22h00), AQ12 (44h00), etc... .

J'ai remarqué que si toutes les HE n'ont pas été reprises à temps (avant les 3 mois) ou si pour un mois je n'ai pas de HE (00:00 en AO), la feuille "bug".

J'ai remarqué que c'est la colonne BN qui "pose problème". C'est elle qui calcul ces "HE imaginaire", mais je ne comprends pas pourquoi.


Cordialement

récup HE - essai2b.xlsx

[EricDgn]

Il faut corriger les formules au niveau des cellules de la plage BM7:BN18 pour avoir Si tous les mois "avant" = 0 alors ... (et pas le seul mois "avant").

[codesite]

Bonjour EricDgn,
merci pour ta réponse précédente.

J'aurais une autre question : est-il possible, sans VBA, de créer une zone d'impression dynamique qui s'adapte à la longueur de la plus grande des colonnes (feuilles "Récapitulatif").
Je voudrais pouvoir imprimer les colonne A à O (fixe, de A1 à O15), mais que la longueur s'adapte en fonction du nombre de lignes (à partir de la ligne 16) pour les colonnes D, G, J et M.

Je voudrais aussi que mon fichier garde en mémoire les paramètres d'impression (grandeur, zoom, zone d'impression dynamique etc...) et qu'on ne puisse rien modifier une fois tous les paramètres définis.


Si ce n'est possible qu'en VBA, quel serait le(s) code(s) à utiliser ?


Un grand merci d'avance.

Cordialement

récup HE - essai2d - Copie v3 impression.xlsm

[EricDgn]

est-il possible, sans VBA, de créer une zone d'impression dynamique qui s'adapte à la longueur de la plus grande des colonnes

Pour cela je pense qu'il suffit de déclarer que la zone d'impression correspond aux colonnes entières, soit A:O pour la feuille Récapitulatif (ou A:N vu que la colonne O est vide).

garder en mémoire les paramètres d'impression et qu'on ne puisse rien modifier une fois tous les paramètres définis

A mon avis ce n'est pas possible sans VBA. Avec VBA on peut utiliser la routine Workbook_BeforePrint() disponible au niveau de l'objet ThisWorkbook pour forcer à reprendre une mise en page définie dans une macro, par exemple ainsi:

Code :

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

1
2
3
Private Sub Workbook_BeforePrint(Cancel As Boolean)
    MiseEnPage      '--- macro dans un module
End Sub

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Sub MiseEnPage()
    If ActiveSheet.Name = "Recapitulatif" Then
        Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .PrintArea = "$A:$N"
            .PrintTitleRows = "$14:$15"
            .Zoom = 85
            '... etc. ...
        End With
        Application.PrintCommunication = True
    End If
End Sub

Cordialement.

[codesite]

Bonjour EricDgn,
merci pour ta réponse.

En fait, je souhaite utiliser le moins possible de macro, car je me dit (ce n'est que mon avis/mon ressenti, je peux me tromper. Corrige-moi si je fais erreur.) que tout le monde ne peut pas exécuter de macro sur son PC (si son Excel est trop vieux ou si son Excel à des formats de fichiers différents (.xls au lieu de .xlsx ; .xlsm si macro)).

De plus, j'ai déjà lu/entendu que les macros pouvaient être source de programmes malveillants (les macros sans d'ailleurs désactivées par défaut si un fichier qui en contient est téléchargé d'internet).

C'est pour ça que je ne suis pas chaud pour utiliser les macros et que je m'obstine à tout faire par formule.


Qu'en penses-tu ?

Si ce n'est pas le cas, je fonce sur les macros alors.


Cordialement

[EricDgn]

Il n'est pas possible de "protéger" la "mise en page" d'une feuille Excel (tout ce qui est accessible à partir groupe "mise en page"). Il sera donc toujours possible pour un utilisateur de modifier les marges, la zone d'impression, le format du papier, etc., que la feuille ou le dossier soit protégé ou non.

Cependant si une feuille est protégée alors il ne sera plus possible pour l'utilisateur de modifier la présentation de la feuille: pas possible de changer la largeur d'une colonne ou d'une ligne, de changer la couleur d'une cellule, de déplacer un élément, etc., sauf si cela a été explicitement autorisé au moment de la protection (en cochant les cases prévues pour cela). Il n'y a alors que le contenu des cellules "non verrouillées" qui peut être modifié.

Bien cordialement.

[codesite]

Bonjour EricDgn,
j'avais bien compris avec ton message précédant qu'on pouvait toujours modifier les paramètres d'impression.

Cependant, je souhaiterai savoir si l'utilisation des macros était "niche" (utilisée par un nombre "relativement restreint" de personne). Car je me pose la question : est-ce que tout le monde peut exécuter des macros sur son PC ? Est-ce que Excel ne doit pas être trop vieux (utilisation de formats de fichiers différents (.xls au lieu de .xlsx ; .xlsm si macro)).

J'ai déjà lu/entendu que les macros pouvaient être source de programmes malveillants (les macros sont d'ailleurs désactivées par défaut si un fichier qui en contient est téléchargé d'internet).

C'est pour ça que je ne suis pas motivé pour utiliser les macros et que j'insiste pour tout faire par formule.


Qu'en penses-tu ? Est-ce que ma vision des choses est erronées ? Car, si ce n'est pas le cas, c'est plus "facile" d'utiliser les macros alors.


Cordialement

[EricDgn]

Les fichiers .xlsm sont arrivés avec Excel 2007. Les versions antérieures à Excel 2007 ne sauront pas ouvrir un fichier .xlsm ni .xlsx, mais seulement des fichiers .xls

Les fichiers .xls peuvent contenir des macros, mais normalement Excel le signale à l'ouverture.

Qu'il y ait des macros malveillantes ... sans doute, mais personnellement je n'en ai pas encore été victime. Par contre, il y en a qui contiennent des erreurs, ou qui ne font pas exactement ce que l'on pense ... aussi faut-il veiller, prendre le temps, à comprendre ce que l'on installe.

Dans Excel, les formules s'exécutent toujours plus rapidement que les codes, mais parfois le code est plus facilement compris de l'utilisateur que les formules! Arbitrage entre rapidité et compétence.

Avis personnel.
Cordialement.