XL 2019 planning mensuel et menu deroulant

[geraldb]

Bonjour

je crée un planning par ville et activité ( J G N ) mais souhaiterai que quand je change de mois , les indications du mois précèdent ne se reportent pas sur les mpis suivants
et enfin , je voudrias pouvoir mettre les colonnes des jours feries en couleurs

merci de votre aide

 

[fanfan38]

Bonjour et bienvenu sur le forum
Ci joint ma solution

A+ François

 

[job75]

Bonjour geraldb, François,

Avec le fichier du post #1 il faut créer une feuille par mois pour chaque ville, c'est très lourd et même rédhibitoire.

Avec le fichier du post #2 il faut créer une feuille par année pour chaque ville, c'est encore bien lourd.

A+

 

[fanfan38]

Autre solution
Quand tu saisie des données sur la feuille Calendrier elles sont recopiées sur la feuille données
et quand tu change d'année, de mois ou de lieux le Calendrier se met à jour

A+ François

 

[job75]

Plutôt que créer des feuilles pour chaque ville il vaut mieux stocker les données de la plage D10:AH14 dans des noms définis :

Private Sub WorkSheet_Change(ByVal Target As Range)
Dim flag1 As Boolean, flag2 As Boolean, annee$, mois$, ville$, matrice
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error Resume Next
'---stockage des données---
If Intersect(Target, [D10:AH14]) Is Nothing Then flag1 = True: Application.Undo 'annule la modification
If Application.CountA([C2:C4]) = 3 Then
    flag2 = True
    annee = [C2]
    mois = [C3]
    ville = Replace([C4], " ", "_")
    matrice = [D10:AH14].Formula
End If
If flag1 Then Application.Undo 'rétablit la modification
If flag2 Then ThisWorkbook.Names.Add ville & "_" & annee & "_" & mois, matrice 'nom défini
'---récupération des données---
[D10:AH14].ClearContents 'RAZ
matrice = Evaluate(Replace([C4], " ", "_") & "_" & [C2] & "_" & [C3])
If Not IsError(matrice) Then [D10:AH14] = matrice
Application.EnableEvents = True
End Sub

Pour tester modifiez le mois en C3 puis revenez en FEVRIER.

J'ai aussi complété la MFC pour les jours fériés (il faudra compléter la plage pour toutes les années).

 

[job75]

Par curiosité j'ai créé avec cette macro 1728 (12 x 12 x 12) noms définis :

Sub Test()
Dim matrice, ville, annee, mois
matrice = Sheets("Calendrier").[D10:AH14].Formula
With Sheets("Sources")
    For Each ville In .Range("M3:M14")
        For Each annee In .Range("F3:F14")
            For Each mois In .Range("H3:H14")
                ThisWorkbook.Names.Add Replace(ville, " ", "_") & "_" & annee & "_" & mois, matrice 'nom défini
    Next mois, annee, ville
End With
End Sub

Le fichier ne pèse que 43 Ko.

Bien sûr tous les noms contiennent la même matrice mais le résultat resterait du même ordre avec des données différentes.

 

[job75]

Après création des 1728 noms définis je me pose la question : pourquoi le fichier ne pèse que 43 Ko ?

Le calcul montre que chaque nom défini ne prendrait que 8 octets...

C'est incroyable, mais c'est peut-être dû au fait que les fichiers .xlsm sont des fichiers compressés.

Si quelqu'un peut m'éclairer...

 

[TooFatBoy]

Après création des 1728 noms définis je me pose la question : pourquoi le fichier ne pèse que 43 Ko ?

Le calcul montre que chaque nom défini ne prendrait que 8 octets...

C'est incroyable, mais c'est peut-être dû au fait que les fichiers .xlsm sont des fichiers compressés.

Si quelqu'un peut m'éclairer...

Je ne sais pas de quel calcul tu parles ni comment tu l'effectues, mais ce que je peux te dire c'est qu'une fois décompressé tout est bien là (chaque Nom est présent et défini totalement), et la taille est alors > 1 Mo.

Extrait :

<definedName name="AMBERT_THIERS_2025_AOUT">{"G","","","","","","","J","","","","","","N","","","","","","","","","","","","J","","","","","";"N","","","","","","J","","","","","","","","","","","","G","","","","","","","","","","","","";"","J","","","","","","","G","","","G","","","","","","","","","","","","","","","","","","","";"","","","","","","","","","","","","","","G","","","","","","G","","","","","","","","","","";"","","","","","J","","","","","","","J","","","","","","","","","","","","","","","","","",""}</definedName>
<definedName name="AMBERT_THIERS_2025_AVRIL">{"G","","","","","","","J","","","","","","N","","","","","","","","","","","","J","","","","","";"N","","","","","","J","","","","","","","","","","","","G","","","","","","","","","","","","";"","J","","","","","","","G","","","G","","","","","","","","","","","","","","","","","","","";"","","","","","","","","","","","","","","G","","","","","","G","","","","","","","","","","";"","","","","","J","","","","","","","J","","","","","","","","","","","","","","","","","",""}</definedName>

 

[job75]

Bonjour le forum, TooFatBoy,

Je ne sais pas de quel calcul tu parles ni comment tu l'effectues, mais ce que je peux te dire c'est qu'une fois décompressé tout est bien là (chaque Nom est présent et défini totalement), et la taille est alors > 1 Mo.

Bon j'ai revu la macro créant les 1728 noms définis, les entrées sont maintenant aléatoires :

Sub Test()
Dim a, ub%, F As Worksheet, ville, annee, mois, dercol%, c As Range
a = Array("J", "G", "N", "")
ub = UBound(a)
Set F = Sheets("Calendrier")
Application.ScreenUpdating = False
Application.EnableEvents = False
With Sheets("Sources")
    For Each ville In .Range("M3:M14")
        F.Range("C4") = ville
        For Each annee In .Range("F3:F14")
            F.Range("C2") = annee
            For Each mois In .Range("H3:H14")
                F.Range("C3") = mois
                With F.Range("D10:AH14")
                    dercol = 3 + Application.CountIf(.Rows(0), "><")
                    .ClearContents
                    For Each c In .Cells
                        If c.Column <= dercol Then c = a(Application.RandBetween(0, ub)) 'ALEA.ENTRE.BORNES
                    Next c
                    ThisWorkbook.Names.Add Replace(ville, " ", "_") & "_" & annee & "_" & mois, .Formula 'nom défini
                End With
    Next mois, annee, ville
End With
Application.EnableEvents = True
End Sub

Le fichier pèse alors 204 Ko, comment fais-tu pour obtenir plus de 1 Mo ? Que veux-tu dire par "décompressé" ?

A+

 

[job75]

comment fais-tu pour obtenir plus de 1 Mo ? Que veux-tu dire par "décompressé" ?

OK c'est simple j'ai converti le fichier .xlsm en .xls il pèse alors 1,37 Mo.

 

[TooFatBoy]

comment fais-tu pour obtenir plus de 1 Mo ? Que veux-tu dire par "décompressé" ?

C'est incroyable, mais c'est peut-être dû au fait que les fichiers .xlsm sont des fichiers compressés.

Comme tu l'as dit, un classeur Excel est un fichier compressé.

C'est plus précisément un dossier, contenant plusieurs dossiers et fichiers, qui est zippé.
Certains de ces fichiers sont des XML, donc du texte. Et le texte se zippe très bien, en particulier quand il y a des morceaux de texte qui se répètent.


Donc pour voir le contenu exact d'un fichier Excel, ce n'est pas en xls qu'il faut modifier son extension mais en zip. 😉

Et une fois que tu as décompressé le fichier zip dans un dossier du même nom, tu peux voir la taille totale dudit dossier ou celle de chacun des éléments qu'il contient.

 

[job75]

Donc pour voir le contenu exact d'un fichier Excel, ce n'est pas en xls qu'il faut modifier son extension mais en zip. 😉

Merci de nous dire comment tu fais.

 

[patricktoulon]

Bonjour
c'est exactement ça
les names sont enregistré dans un xml (fichier texte qui pese moins lourd que x plage qui complique et grossisse les xml des feuilles

modifiez l'extension directement sur le fichier à la main remplacer ".xlsm" par ".zip"
si votre explorateur n'affiche que les noms sans extension allez a affichage en haut dans le ruban de la fenêtre de l'explorer
et choisissez affichage details elargissez les colonne pour voir le nom en entier si nécessaire

ci joint deux zips qui sont en fait des classeurs
classeur 1 : 2 names
classeur 2 : 2tableaux structurés
et il sont petit les tableaux structuré un vingtaine de ligne pour l'exemple

c'est la que l'on voit le prix que coûte les tableaux structurés
une différence de 30 kilos pour deux fichiers vierges identique
on constate des xml supplémentaires
les sheets.xml dans le dossier xl sont modifiés

Avec de gros tableaux structurés la différence est exponentielle
tandis que le même nombre de names que de TS avec pour ref les même plages pèseront beaucoup moins lourd

c'est le prix du coté pratique du tableau structuré beaucoup plus lourd mais géré en memoire donc accessible dans l'instante
j'ai tout dit
Patrick

 

[job75]

modifiez l'extension directement sur le fichier à la main remplacer ".xlsm" par ".zip"

C'est ce que j'ai fait avec le fichier planning.xlsm mais ensuite je n'obtiens pas de tailles > 1 Mo...

 

[TooFatBoy]

C'est ce que j'ai fait avec le fichier planning.xlsm mais ensuite je n'obtiens pas de tailles > 1 Mo...

J'ai pris le classeur de #1, je lui ai ajouté un module standard avec ta macro de #6, j'ai lancé la macro et j'ai sauvegardé le classe, que j'ai ensuite dézippé.

Ceci dit, j'ai peut-être lancé au moins deux fois ladite macro...
(je n'ai pas regardé si ça augmente le nombre de Nom à chaque exécution)