Décaler les données sources d'un graphique + moult questions

Vauclerc

XLDnaute Nouveau
Bonjour,

Je rencontre un petit problème qui me bloque depuis quelques jours.

Sur une feuille excel intitulée COFFRET, j'ai un tableau de valeurs en bas duquel j'ajoute une ligne de données chaque semaine.

ce tableau contient 3 colonnes:
- colonne H : numéro de la semaine
- colonne I : donnée 1
- colonne J : donnée 2

J'ai un graphique sur la même feuille qui présente les courbes de ces deux données sur les 12 dernières semaines. je désirerais automatiser le glissement des sources de données à chaque fois que j'active la macro, et je ne parviens pas à trouver la syntaxe adéquate.

Je souhaite que ma source de données glisse de sa position initiale

Ligne a
.
.
.
Ligne b


à une position définie par deux variables créées par mes soins

Code:
 [I]ProchaineLigne = Sheets("COFFRET").Range("H65536").End(xlUp).Offset(1, 0).Row[/I]
    [I]ProchaineLigne12 = ProchaineLigne - 12[/I]
ProchaineLigne12
.
.
.
ProchaineLigne


(si vous vous posez la question pour le offset, entre le moment ou je déclare cette variable et celui ou je ferai glisser le champs de données j'aurais inséré de nouvelles données donc cette ligne ne sera plus vide)

Grâce à l'enregistremen automatique de macros excel, j'ai pu déterminer que mon tableau est identifié par "Chart 2", et que les séries que je désire changer sont identifées aux index de séries 1 et 3.

Je tente une syntaxe du style

Code:
[I]Dim ProchaineLigne As Long, ProchaineLigne12 As Long

ProchaineLigne = Sheets("COFFRET").Range("H65536").End(xlUp).Offset(1, 0).Row
ProchaineLigne12 = ProchaineLigne - 12

[COLOR="Blue"]Sheets("COFFRET").ChartObjects("Chart 2").SeriesCollection(1).Values = Sheets("COFFRET").Range("I" & ProchaineLigne12 & ":I " & ProchaineLigne & "")
Sheets("COFFRET").ChartObjects("Chart 2").SeriesCollection(3).Values = Sheets("COFFRET").Range("J" & ProchaineLigne12 & ":J " & ProchaineLigne & "")[/COLOR][/I]
Il m'est retourné le message suivant "Erreur d'exécution '1004'; Erreur définie par l'application ou par l'objet".

Je suppose que le problème réside donc dans la mauvaise manipulation que je fais avec les format des valeurs.

devrais je plutot essayer par la manipulation des données sources directement ?

Code:
[COLOR="blue"][I]Sheets("COFFRET").ChartObjects("Chart 2").SetSourceData Source:=Sheets("COFFRET").Range("H" & ProchaineLigne12 & ":J " & ProchaineLigne & "")[/I][/COLOR]
quel est le lien entre données sources et séries dans le vba ? on peut directement bouger tout le bloc de données sources sans se préoccuper des séries individuellement et excel va tout comprendre tout seul ? de plus, si je ne déplace que mes deux séries une par une (colonne I et J), excel va-t-il mettre à jour les étiquettes de données (ma colonne H) ?

je suis également preneur de toute suggestion utilisant la fonction decaler, que j'ai découverte sur des forums mais que je ne parviens pas à utiliser. je n'arrive pas à définir une plage dynamique de 12 lignes de haut et 1 colonne, car mon tableau ne débute qu'en ligne 254, et il y a des valeurs parasites au dessus. Il me faudrait donc spécifier dans la formule non pas "NBVAL(H:H)" mais "NBVAL(H:H)-x", x le nbre de valeurs parasites. la question est, peut on intégrer une opération au sein même d'une formule? (du style DECALER(H254;NBVAL(H:H)-x;;) d'ailleurs, décaler peut il retourner une plage de valeurs ? j'avoue que l'usage de cette fonction n'est pas très clair pour moi...

Ah une dernière chose, même si on trouve une solution avec décaler, j'aimerais bien savoir ce que j'aurais du mettre dans mon code vba histoire de ne pas rester sur une frustration (et en plus je m'offre le luxe d'être chiant :D)

Bon voilà je crois que j'ai à peu près posé toutes mes questions... désolé pour le pavé ! si vous vous sentez de répondre à ne serait ce que quelques unes des questions ci-dessus vous me serez certainement d'une aide providentielle.

Cordialement
 
Dernière édition:

Odesta

XLDnaute Impliqué
Re : Décaler les données sources d'un graphique + moult questions

Bonjour

Je pense que vous n'écrivez pas comme il convient ce qu'attend excel pour les colonnes.
en effet, vous lui donnez une plage, définie par votre variable.
Hors il n'attend pas du tout une plage, mais une "string" (représentant certe une plage) !!

Il faut donc travailler à construire la string :
Code:
Sheets("COFFRET").ChartObjects("Chart 2").SeriesCollection(1).Values = "=COFFRET!R" & ProchaineLigne12 & "C9:R" & ProchaineLigne & "C9"
Sheets("COFFRET").ChartObjects("Chart 2").SeriesCollection(3).Values = "=COFFRET!R" & ProchaineLigne12 & "C10:R" & ProchaineLigne & "C10"

Dites moi si cela fonctionne :)

cdt
Olivier
 

phlaurent55

Nous a quittés en 2020
Repose en paix
Re : Décaler les données sources d'un graphique + moult questions

Bonjour à tous,

un exemple en pièce jointe .............. à adapter

à+
Philippe
 

Pièces jointes

  • 111.xls
    32.5 KB · Affichages: 311
  • 111.xls
    32.5 KB · Affichages: 317
  • 111.xls
    32.5 KB · Affichages: 319

Gael

XLDnaute Barbatruc
Re : Décaler les données sources d'un graphique + moult questions

Bonsoir à tous,

Pour la partie macro, La réponse de Odesta fonctionne. Si tu veux cependant définir un range, c'est possible de la façon suivante:

Code:
Sub Graph()
Dim ProchaineLigne As Long, ProchaineLigne12 As Long, plage As Range, plage2 As Range
ProchaineLigne = Sheets("COFFRET").Range("H65536").End(xlUp).Offset(1, 0).Row
ProchaineLigne12 = ProchaineLigne - 12
Set plage = Sheets("COFFRET").Range("I" & ProchaineLigne12 & ":I" & ProchaineLigne & "")
Set plage2 = Sheets("COFFRET").Range("J" & ProchaineLigne12 & ":J" & ProchaineLigne & "")
ActiveSheet.ChartObjects("Graphique 2").Activate
ActiveChart.SeriesCollection(1).Values = plage
ActiveChart.SeriesCollection(2).Values = plage2
End Sub

Pour la fonction décaler, j'ai défini dans l'exemple joint une plage "Abcisse" avec Insertion-nom-définir" et la formule suivante:

Code:
=DECALER(COFFRET!$H$1;EQUIV(100;COFFRET!$H:$H;1)-12;;12)

EQUIV(100;COFFRET!$H:$H;1) renvoie le n° de ligne de la colonne H dans laquelle se trouve la valeur la plus élevée inférieure ou égale à 100, en fait la ligne contenant le n° de semaine le plus élevé.

Si ta plage commence à la ligne 254, tu peux remplacer $H:$H par $H254:$H350 par exemple. Dans ce cas, il faut aussi remplacer $H1 par $H254.

La plage commence à partir de ligne contenant la valeur la plus élevée -12 et sur une hauteur de 12, ce qui fait bien les 12 dernières lignes.

2 autres plages sont définies à partir de de "Abcisse", "Plage1 et "Plage2" avec un décalage d'une ou 2 colonnes de "Abcisse".

Dans les données sources du graphique, les 2 séries et l'abcisse sont définies avec les noms de plages variables.

Si tu rajoutes une semaine et des données, le graphique se modifie automatiquement.

Pour visualiser les plages, tu vas dans insertion- nom-définir, tu sélectionnes une plage et tu cliques dans le champ "fait référence à", XL te montre le résultat.

@+

Gael
 

Pièces jointes

  • Vauclerc_V1.xls
    24.5 KB · Affichages: 161

ROGER2327

XLDnaute Barbatruc
Re : Décaler les données sources d'un graphique + moult questions

Bonsoir à tous
Un exemple sans VBA.​
ROGER2327
#4378


Mardi 17 Haha 138 (Saint Homais d'Aquin, prudhomme, SQ)
1er Brumaire An CCXIX
2010-W42-5T23:37:17Z
 

Pièces jointes

  • Graphique_dynamique_paramétrable.xls
    22.5 KB · Affichages: 290

Vauclerc

XLDnaute Nouveau
Re : Décaler les données sources d'un graphique + moult questions

Bonjour à tous,

Tout d'abord merci pour vos réponses. J'ai déja effectué un grand pas en avant en comprenant que R et C signifient Row et Column... Je me demandais vendredi pour quelle raison l'éditeur de macro automatique s'obstinait à me sélectionner ces fichues "colonnes".

Cependant je ne suis pas parvenu au bout de mes soucis, et je pense que je ne définis pas correctement le format des variables que j'utilise.

1) L'usage de la syntaxe suivante:

Code:
Dim ProchaineLigne As Long, ProchaineLigne1 As Long, ProchaineLigne2 As Long, ProchaineLigne12 As Long
    
ProchaineLigne = Sheets("COFFRET").Range("H65536").End(xlUp).Offset(1, 0).Row
ProchaineLigne1 = ProchaineLigne - 1
ProchaineLigne2 = ProchaineLigne - 2
ProchaineLigne12 = ProchaineLigne - 12

Sheets("COFFRET").ChartObject("Chart 2").SeriesCollection(1).Values = "=COFFRET!R" & ProchaineLigne12 & "C9:R" & ProchaineLigne & "C9"
Sheets("COFFRET").ChartObject("Chart 2").SeriesCollection(3).Values = "=COFFRET!R" & ProchaineLigne12 & "C10:R" & ProchaineLigne & "C10"

me retourne une "erreur d'exécution '438': Propriété ou méthode non gérée par cet objet" aux deux dernière lignes.

2) L'usage de la syntaxe suivante quant à elle

Code:
Dim ProchaineLigne As Long, ProchaineLigne1 As Long, ProchaineLigne2 As Long, ProchaineLigne12 As Long
Dim Serie1 As Range

ProchaineLigne = Sheets("COFFRET").Range("H65536").End(xlUp).Offset(1, 0).Row
ProchaineLigne1 = ProchaineLigne - 1
ProchaineLigne2 = ProchaineLigne - 2
ProchaineLigne12 = ProchaineLigne - 12

Set Serie1 = Sheets("COFFRET").Range("I" & ProchaineLigne1 & ":I" & ProchaineLigne & "")

Sheets("COFFRET").ChartObjects("Chart 2").SeriesCollection(1).Values = Serie1

me retourne le même message d'erreur. En mon for intérieur, l'étonnement en dispute à l'agacement provoqué par la redondance des fautes de syntaxe dans mon code. Si j'étais audacieux et plein de fougue je m'écrierais "Diantres ! Il me semblait pourtant bien avoir vérifié la compatibilité méthodes / Objets dans l'aide de VBA"...

3) La solution DECALER répond à mon besoin, merci infiniment messieurs Gael et Roger pour ces explications et ces exemples très clairs.

Je me suis donc débloqué dans le travail que je souhaite réaliser, mais j'aimerais avec votre aide trouver le fin mot de l'histoire concernant le code VBA car je sais que je suis susceptible de rencontrer un problème similaire à l'avenir.

4) Je pense m'être fait une idée par l'expérience mais je me demandais quelle était exactement la fonction des espelettes de part et d'autre d'une variable qu'on intègre en paramètre d'une méthode "=COFFRET!R" & ProchaineLigne12 & "C10:R" & ProchaineLigne & "C10".
Est ce que ça convertit la variable en un format spécifique ? peut on retrouver le même usage avec d'autres caractères ? (% --- %, @ --- @, # --- # ...)


Merci encore à tous pour votre aide.

Cordialement


PS: PhLaurent, je vous remercie pour le fichier que vous m'avez envoyé mais sauf erreur de ma part le code est inaccessible ! Je suis plus à la recherche d'un savoir faire qu'à celle d'un fichier livré clés en main. Mais merci tout de même :)
 
Dernière édition:

Vauclerc

XLDnaute Nouveau
Re : Décaler les données sources d'un graphique + moult questions

Aaaaccchh !

encore un petit souci avec la fonction DECALER... mes plages dynamiques sont correctement définies pour l'abscisse et chacune des deux séries.

Cependant mon graphique a tendance à ne pas se mettre à jour.

Lorsque je vais dans l'affichage des sources de données du graphique, sous l'onglet "plage de données", le champ éponyme reste fixe même lorsque les sources des séries dans l'onglet "Série" sont renseignées avec les noms des séries dynamiques...

J'ai donc tenté de définir une zone "plage de données" dynamique, toujours avec la fonction Décaler et qui corresponde aux zones de mes abscisses, séries 1 et 2 mises côte à côte. Projet dans lequel je réussissai, mais lorsque je renseigne la plage de données avec cette nouvelle plage dynamique, les zones de données de l'onglet "série" se réinitialisent... si je n'étais déja fou je le deviendrais volontiers.

What happens dudes ?

EDIT:
Bonjour,

J'ai posté de manière un peu précipitée hier soir, veuillez m'en excuser.
Voici donc ce qui figure dans les champs de zones dynamiques que j'ai définies pour les besoins du graphique.

Abscisse:
=DECALER(COFFRET!$H$254;NBVAL(COFFRET!H:H)-14;;12)

Serie1:
=DECALER(COFFRET!$I$254;NBVAL(COFFRET!H:H)-14;;12)

Serie2:
=DECALER(COFFRET!$J$254;NBVAL(COFFRET!H:H)-14;;12)

=> premier fait étonnant, lorsque je retourne dans Insertion>Nom>Définir, les champs passés en gras ci-dessus étaient devenus (COFFRET!P:p) pour une raison que j'ignore, lorsque j'ai ouvert le fichier ce matin.

Après correction de ce problème, l'affichage de mes graphiques semble se mettre à jour. J'ai figé les colonnes H avec '$', et j'espère que ce décalage ne se produira plus à l'avenir.

Avez vous trouvé ce qui clochait avec mon code VBA ?
 
Dernière édition:

Gael

XLDnaute Barbatruc
Re : Décaler les données sources d'un graphique + moult questions

Bonjour à tous,

Pour les 2 codes VBA, si tu veux modifier les données source d'une série pour un graphique donné, il faut activer le graphique, ce qui correspond à l'action de cliquer dedans pour le sélectionner.

Par la suite il faut utiliser "Activechart" qui fait référence au graphe sélectionné, soit:

Code:
ActiveSheet.ChartObjects("Graphique 2").Activate
ActiveChart.SeriesCollection(1).Values = etc.

Les guillemets servent à définir une chaîne de caractères. L'intruction:

Code:
"=COFFRET!R" & ProchaineLigne12 & "C9:R" & ProchaineLigne & "C9"

Va donner en final:
Code:
=COFFRET!$I$24:$I$36
résultat qui s'affichera dans le champ "Valeurs" des données source de la série que l'on veut modifier. C'est ce que précise l'instruction:

Code:
ActiveChart.SeriesCollection(1).Values

Activechart = Graphique sélectionné
SeriesCollection(1) = Première série
Values = Champ Valeurs

C'est l'équivalent de:
Sélectionner le graphique
Clic droit puis "Données source"
Sélection de la série 1
Modification du champ "Valeurs" en mettant par exemple:
Code:
]=COFFRET!$I$24:$I$36

@+

Gael
 

Discussions similaires

Réponses
0
Affichages
83