Titre du sujet : Re: Formule pour faire un cumul annuel mobile dans un TCD
par Mth le 24/11/2012 14:59:01
Bonjour Eric, bonjour le forum,
Grâce à tes nouvelles explications j'ai avancé un peu sur une piste légèrement différente, car je pense que mes premières indications ne conviendraient pas pour les TCD (il manquerait la ligne d'en-tête).
Tu trouveras mon essai dans le fichier joint en espérant que cela pourra t'être utile.
Dans ce fichier l'onglet "Base" contient les données structurées comme ce que tu as décrit, et des onglets TCD sans grand intérêt pour l'instant mais qui sont juste présents pour l'exemple de rafraichissement automatique que j'ai inscrit dans le code.
J'utilise un filtre élaboré pour extraire de la base les 12 derniers mois. Le résultat de ce filtre élaboré sert de plage de données source à l'un des TCD, onglet "TCD Glissant".
Cette plage est dynamique et tu la trouveras dans le fichier sous le nom "ZoneTCD"
(Onglet "Formules", "Gestionnaire de noms", ZoneTCD fait référence à la plage calculée par cette formule:
=DECALER(Base!$J$1;;;NBVAL(Base!$J:$J);6)
J'ai mis tous les commentaires dans le code que voici:
Option Explicit
Sub Macro1()
'Déclaration des variables:
Dim vDerlig As Long, vPlage As Range, c As Range
'Désactive le rafraichissement de l'écran
'Pour tester en pas à pas et voir le résultat à l'écran il faut
'désactiver cette ligne:
Application.ScreenUpdating = False
'Travail sur l'onglet BAse
With Sheets("Base")
'vDerlig = dernière ligne remplie colonne A (soit la colonne numéro 1)
'Rows.count indique le nombre de lignes de la feuille (soit 1048576 à partir d'Excel 2007)
'.end(xlUp) permet de remonter de la dernière ligne de la feuille à la dernière ligne remplie de la colonne
'.Row permet d'indiquer le numéro de cette ligne
vDerlig = .Cells(Rows.Count, 1).End(xlUp).Row
' Décrit la variable vPlage qui couvre la zone de travail, des colonnes
' A à F, de la ligne 1 à la dernière ligne remplie
Set vPlage = .Range("A1:F" & vDerlig)
'Remplit la cellule F1 avec le libellé "Date"
.Range("F1") = "Date"
'Pour chaque cellule de la colonne F, de la ligne 2 à la dernière ligne de travail
For Each c In .Range("F2:F" & vDerlig)
'calucle une date à partir des informations colonnes A et B
'Dateserial équivaut à la fonction DATE() dans notre Excel français
'Elle s'écrit DATE(Année;Mois;Jour) est renvoie une date
'Ici année est indiquée en colonne 1, soit sur la même ligne mais 5 colonnes avant
'd'où l'expression c.offset(0,-5)
'Même raisonnement pour le mois, et le jour est renseigné à 1
'Pour calculer la fin du mois correspondant à cette date, la fonction EoMonth() équivaut
'à FIN.MOIS() dans la feuille Excel francisée.
c = WorksheetFunction.EoMonth(DateSerial(c.Offset(0, -5), c.Offset(0, -4), 1), 0)
Next c
'*-*-*-*-*-*-*-*
' Préparation du filtre élaboré:
'*-*-*-*-*-*-*-*
'Inscription du libellé "Date" dans les cellules H1 et I1
.Range("H1") = "Date"
.Range("I1") = "Date"
'Préparation des critères de iltre en H2 et I2
'En H2 Excel écrit ="<="&MAX(F:F), ce qui indique la date la plus récente
'En I2 on inscrit =">="&FIN.MOIS(MAX(F:F);-12), ce qui permet d'avoir la date 12 mois avant
'Le filtre va donc pouvoir extraire les lignes dont les dates sont comprises entre ces deux bornes
'soit les 12 derniers mois glissants
.Range("H2").FormulaR1C1 = "=""<=""&MAX(C[-2])"
.Range("I2").FormulaR1C1 = "="">=""&EOMONTH(MAX(C[-3]),-12)"
'Lancement du filtre: la plage de cellules vPlage est filtrée, selon les critères définis
' en cellules H1:I2, l'extrait de la base d'origne est copié en J1
vPlage.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range _
("H1:I2"), CopyToRange:=.Range("J1:O1"), Unique:=False
'Permet de rafraichir les TCD: la zone nommée "ZoneTCD" dans le fichier
'est la source des TCD, cette "ZoneTCD" correspond au filtre qui vient d'être éxécuté ci-dessus
ActiveWorkbook.RefreshAll
'Efface les données des colonnes F à O pour ne pas garder de données inutiles
.Columns("H:O").ClearContents
End With
'Rétablit le rafraichissement écran
Application.ScreenUpdating = True
End Sub
Pour le TCD de l'onglet "TCD Annuel" la zone source est la zone nommée "Base" (dynamique également).
En espérant que cela pourra t'aider,
Bon week end,
mth
|
Didier FOURGEOT