XL 2019 Modifier la plage de données d'un graphique selon valeur d'une cellule (sans macro)

[Astyanax]

Bonjour,
j'ai besoin de créer un petit fichier excel de relevés puis d'en tirer un graphique.
Je ne bute que sur un problème et je ne suis pas sûr que cela soit possible sans
l'utilisation d'une macro.

Dans une année, il y a des mois de 4 semaines et d'autres de 5 et j'aimerais que la taille du graphique s'adapte
automatiquement selon la valeur d'une cellule (Cellule vide ou non, une certaine valeur dans une cellule, ...)
Si j'utilise 3 relevés par semaine, il faudrait que la plage de données du graphique passe de
A1:F12 pour 4 semaines à A1:F15 pour 5 semaines.
Je vous remercie de me donner votre avis et une solution si possible.
Je vous souhaite un bon week-end.
Astyanax

 

[Staple1600]

Bonjour Astyanax, le fil

• En passant en coup de vent •
Un fichier exemple serait tout de suite plus parlant
Et un élément facilitateur pour résoudre la question posée.

 

[Astyanax]

Bonjour Staple 1600,
je ne l'ai pas clairement exprimé mais mon fichier n'existe pas.
En réfléchissant à ce que je voulais faire, je me suis aperçu
que la gestion automatique de la plage de données du graphique
me posait un problème sans la traiter par macro.
Je viens d'en créer un vite fait. Il ne correspond pas tout à fait
au descriptif de mon premier post mais j'ai fais au plus simple.
La semaine 5 n'est pas toujours présente. C'est donc cette semaine
que je dois éliminer dans la plage de données.
Cette plage de données est A13 pour 5 semaines et A1:M3 pour 4 semaines.
La commande pourrait être la détection d'une cellule vide à l'emplacement fusionné
de la semaine 5(N11).
J'espère que c'est plus clair malgré le changement de structure.

 

[Staple1600]

Re

Une 1ère ébauche
En A1
=AUJOURDHUI()
En B1
=ENT(JOUR(DATE(ANNEE(A1);MOIS(A1)+1;1)-1)/7)
En F1
=SI(A2>4;"Sem 5";"")

 

[Staple1600]

Re

@Astyanax
Lance cette macro sur une feuille vierge
(c'est juste pour créer un exemple)

Sub exemple()
Range("A1").FormulaR1C1 = "8/1/2022"
Range("A2").Formula2R1C1 = "=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(EOMONTH(R[-1]C,-1)+1&"":""&EOMONTH(R[-1]C,0))))=2)*1)-1+(WEEKDAY(EOMONTH(R[-1]C,0),2)>0)*1"
[B1] = "Sem 1": [E1] = "Sem 2": [H1] = "Sem 3": [K1] = "Sem 4"
Range("N1") = "=IF(R[1]C[-13]=5,""Sem 5"","""")"
Range("N2").FormulaR1C1 = "=IF(LEFT(R1C14)=""S"",""Lundi"","""")"
Range("O2").FormulaR1C1 = "=IF(LEFT(R1C14)=""S"",""Mardi"","""")"
Range("P2").FormulaR1C1 = "=IF(LEFT(R1C14)=""S"",""Mercredi"","""")"
Range("B2").Select
jours = Array("Lundi", "Mardi", "Mercredi")
[B2:D2] = jours: [E2:G2] = jours: [H2:J2] = jours: [K2:M2] = jours
[A1].Interior.Color = vbYellow: [A1].Font.Bold = -1
End Sub

Une fois, la macro lancée, change manuellement la date en A1
(par exemple saisis : 01/07/22)

NB: La formule en A2 est de la regrettée Monique (une grande formuliste du forum)

 

[Astyanax]

Staple,
j'ai beau relire les 2 réponses, je ne vois rien qui modifie la plage de données du graphique car c'est la seule chose que je veux faire.

 

[Staple1600]

Re

Il faut relire et surtout tester la macro comme indiqué dans le message#5
Si tu suis correctement les directives, tu verras forcément un changement sur ton écran
(notamment en colonne N,O et P)
Je peux l'affirmer puisque c''est ce qui se passe sur mon écran.

 

[Phil69970]

bonjour à tous

@Staple1600
Une petite coquille ici

Range("A2").Formula2R1C1

Sinon cela fonctionne bien..

@Phil69970

 

[Staple1600]

Bonsoir @Phil69970

Pas de coquille cette fois-ci
Syntaxe issue de l'enregistreur de macros d'Excel 365

Sub Macro1()
    Range("A2").Select
    ActiveCell.Formula2R1C1 = _
        "=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(EOMONTH(R[-1]C,-1)+1&"":""&EOMONTH(R[-1]C,0))))=2)*1)-1+(WEEKDAY(EOMONTH(R[-1]C,0),2)>0)*1"
End Sub

Et pour le pourquoi de la chose

Formula2R1C1 Property
Gets or sets the formula of the cells represented by this IRange in R1C1-style array notation.

 

[Phil69970]

Re

Sur excel 2010 64 bits j'ai ce message

@Phil69970

 

[Staple1600]

RE

Oui mais comme je l'écrivais, je suis sur Office 365

 

[Staple1600]

Re

Et comme ceci, cela donne quoi sur Excel 2010 ?

Sub exemple_bis()
Range("A1").FormulaR1C1 = "8/1/2022"
'Office 365
'Range("A2").Formula2R1C1 = "=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(EOMONTH(R[-1]C,-1)+1&"":""&EOMONTH(R[-1]C,0))))=2)*1)-1+(WEEKDAY(EOMONTH(R[-1]C,0),2)>0)*1"
'Versions antériereures
Range("A2").FormulaArray = "=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(EOMONTH(A1,-1)+1&"":""&EOMONTH(A1,0))))=2)*1)-1+(WEEKDAY(EOMONTH(A1,0),2)>0)*1"
[B1] = "Sem 1": [E1] = "Sem 2": [H1] = "Sem 3": [K1] = "Sem 4"
Range("N1") = "=IF(R[1]C[-13]=5,""Sem 5"","""")"
Range("N2").FormulaR1C1 = "=IF(LEFT(R1C14)=""S"",""Lundi"","""")"
Range("O2").FormulaR1C1 = "=IF(LEFT(R1C14)=""S"",""Mardi"","""")"
Range("P2").FormulaR1C1 = "=IF(LEFT(R1C14)=""S"",""Mercredi"","""")"
Range("B2").Select
jours = Array("Lundi", "Mardi", "Mercredi")
[B2:D2] = jours: [E2:G2] = jours: [H2:J2] = jours: [K2:M2] = jours
[A1].Interior.Color = vbYellow: [A1].Font.Bold = -1
End Sub

 

[Phil69970]

Re

Cela met la formule en A2 en matricielle avec ton post#12

Et en non matricielle avec ton post#5
Rectifiée avec mon post#8 (sinon j'ai une erreur)

@Phil69970

 

[Staple1600]

Re

Comme je le disais dans mon second post, c'est une formule de Monique
(et en relisant le message d'origine, il semble qu'il n'y ait pas besoin de validation matricielle)

Donc un simple Formula devrait suffire

Sub exemple_ter()
Range("A1").FormulaR1C1 = "8/1/2022"
Range("A2").Formula = "=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(EOMONTH(A1,-1)+1&"":""&EOMONTH(A1,0))))=2)*1)-1+(WEEKDAY(EOMONTH(A1,0),2)>0)*1"
[B1] = "Sem 1": [E1] = "Sem 2": [H1] = "Sem 3": [K1] = "Sem 4"
Range("N1") = "=IF(R[1]C[-13]=5,""Sem 5"","""")"
Range("N2").FormulaR1C1 = "=IF(LEFT(R1C14)=""S"",""Lundi"","""")"
Range("O2").FormulaR1C1 = "=IF(LEFT(R1C14)=""S"",""Mardi"","""")"
Range("P2").FormulaR1C1 = "=IF(LEFT(R1C14)=""S"",""Mercredi"","""")"
Range("B2").Select
jours = Array("Lundi", "Mardi", "Mercredi")
[B2:D2] = jours: [E2:G2] = jours: [H2:J2] = jours: [K2:M2] = jours
[A1].Interior.Color = vbYellow: [A1].Font.Bold = -1
End Sub

Maintenant pour se rapprocher du problème initiale: plage nommée dynamique.

 

[Staple1600]

RE

Toujours à tester sur une feuille vierge

Sub test_A()
Dim ligne&
Range("A1").FormulaR1C1 = "8/1/2022"
Range("A2").Formula = "=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(EOMONTH(A1,-1)+1&"":""&EOMONTH(A1,0))))=2)*1)-1+(WEEKDAY(EOMONTH(A1,0),2)>0)*1"
Rows("3:500").Clear
Range("B3:P" & Application.RandBetween(3, 25)).Formula = "=RaND()"
a = [A2]
ligne = Cells(Rows.Count, 2).End(3).Row
Set plage_graph = Range(Cells(3, 2), Cells(ligne, IIf(a = 4, 13, 16)))
MsgBox plage_graph.Address
End Sub
Sub test_B()
Dim ligne&
Range("A1").FormulaR1C1 = "7/1/2022"
Range("A2").Formula = "=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(EOMONTH(A1,-1)+1&"":""&EOMONTH(A1,0))))=2)*1)-1+(WEEKDAY(EOMONTH(A1,0),2)>0)*1"
Rows("3:500").Clear
Range("B3:P" & Application.RandBetween(3, 25)).Formula = "=RaND()"
a = [A2]
ligne = Cells(Rows.Count, 2).End(3).Row
Set plage_graph = Range(Cells(3, 2), Cells(ligne, IIf(a = 4, 13, 16)))
MsgBox plage_graph.Address
End Sub

Ensuite reprendre cette logique pour avoir une plage dynamique selon qu'il y ait 4 ou 5 semaines dans un mois donné.


NB: Si on avait un fichier Excel exemple avec des données et un graphique déjà mis en place, on aurait déjà résolu cette question, non ?