Discussion :

[Pahcixam]

Bonjour.

J'ai actuellement un classeur excel avec en colonne A des dates, et en colonne B à F des valeurs.
Je dessine actuellement un graphique en sélectionnant manuellement mes données toutes les 7 lignes (schéma répétitif).

Pour faire simple, j'ai donc un graphique avec en abscisse des dates qui sont des cellules fusionnées de 7 lignes de hauteur: A2, A9, A16, A23 ...
Les séries de données (1 pour chaque colonne de B à F) sont définies comme l'ensemble des dernières lignes de chaque groupe de 7 lignes:
Série 1: B8, B15, B22, B29 ...
Série 2: C8, C15, C22, C29 ...
Série 3: D8, D15, D22, D29 ...
Série 4: E8, E15, E22, E29 ...
Série 5: F8, F15, F22, F29 ...

Le fichier est mis à jour très régulièrement (plusieurs fois par semaine), par l'ajout d'un nouveau bloc de 7 lignes en fin de tableau car il y a d'autres données qui permettent de calculer le résultat de la 7ème ligne.

J'aimerai pouvoir définir des plages de données automatiques pour ne pas avoir à modifier manuellement le graphique à chaque nouvelle insertion.

J'ai regardé du côté de la fonction DECALER et INDIRECT sans succès.

Auriez-vous une solution à ce problème ?

D'avance merci.

[antonysansh]

Bonjour,

Je sais pas s'il est possible de construire une formule Excel qui permettre de renvoie une plage comme tu la veux.

Un code VB qui ajoute des plages nommé comme tu le souhaites est envisageable ?

[Pahcixam]

Bonjour.

Je pensais à une formule mais effectivement ce n'est peut-être pas possible.

Un code VBA est tout à fait envisageable avec une mise à jour à l’événement change de la feuille mais je n'ai aucune idée de comment m'y prendre.

J'ai essayé une autre approche en utilisant une plage nommée, définie par une formule matricielle, mais mon code me retourne une matrice avec des 0 pour les lignes qui ne correspondent pas et ça ne va pas pour le graphique.

[antonysansh]

Je cherchais aussi une formule matricielle du style : {=CONCATENER(SI(MOD(LIGNE(E1:E36);7)=1;ADRESSE(LIGNE(E1:E36);5);""))} mais la concaténation ne se fait pas. Dommage car en ajoutant les ";" on avait une plage comme voulu en texte.

C'est pas mon fort la formule matricielle :s

Pour ce qui est du VB, tu peux lancer la MAJ des plages nommées sur les évènements suivants : Workbook_Open du module ThisWorkbook et Worksheet_Activate du module de la feuille en question.

[antonysansh]

Dans un Module les deux codes suivant :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
'-----------------------------------------------------------------------------------+
'   Ajoute une plage nommée correspondant à la série de la colonne numéro [colonne] |
'-----------------------------------------------------------------------------------+
Sub Ajouter_Serie(colonne As Long)
    Dim rg As Range, i&, derniere_ligne&, nom$                                      ' Déclaration des variables (& pour Long et $ pour String)
        nom = "Serie_" & colonne - 4                                                ' -4 car la premier série est en colonne E
        With ThisWorkbook.ActiveSheet
            derniere_ligne = .Cells(Application.Rows.Count, colonne).End(xlUp).Row  ' Calcule de la dernière ligne de la colonne
            Set rg = .Cells(8, colonne)                                             ' initialise rg sur la ligne 8
            For i = 8 To derniere_ligne Step 7
                Set rg = Application.Union(rg, .Cells(i, colonne))                  ' Union des cellules toutes les 7 lignes
            Next
                ActiveWorkbook.Names.Add Name:=nom, RefersToR1C1:=rg                ' Ajout du nom (pas besoin de delete car si même nom il est remplacé)
        End With
        Set rg = Nothing                                                            ' liberation de la variable rg
End Sub

Code :

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

1
2
3
4
5
6
7
8
9
'-------------------------------+
'   Ajoute toutes les séries    |
'-------------------------------+
Sub Ajouter_Series()
    Dim i&
        For i = 5 To 9              ' Ajoute les série des colonnes 5 à 9
            Call Ajouter_Serie(i)
        Next i
End Sub

Dans le Module ThisWorkbook :

Code :

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

1
2
3
Private Sub Workbook_Open()
    Call Ajouter_Series
End Sub

Dans le Module de la feuille comportant les données :

Code :

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

1
2
3
Private Sub Worksheet_Activate()
    Call Ajouter_Series
End Sub

Avec ça, à chaque ouverture du classeur ou activation de la feuille contenant les données, tes 5 série seront mises à jour.

[Pahcixam]

Super merci beaucoup !

J'ai adapté le script pour mon cas (plusieurs feuilles de données et recherche sur une valeur de colonne plutôt que le saut de 7 lignes (plus de souplesse) et ça fonctionne parfaitement.

Je passe le sujet en résolu.