Graphiques en VBA

Version imprimable

12/06/2013, 11h26
Invité

Graphiques en VBA
Bonjour,
courte présentation : 57 ans, programmeur ayant étudié COBOL (ans 74), il me faut maintenant travailler en VBA pour Excel.
Je n'y excelle pas. En fait, je suis inscrit à une formation, je l'attends depuis un an, je ne l'aurai que dans 18 mois au plus tôt. Et le travail n'attend pas.

Voici le problème.

J'ai un classeur qui ne contient que des relevés de données pour 27 sites, les seuls calculs sont des totaux.
Une feuille par année plus une feuille "général" qui reprend simplement les valeurs. Une feuille par an pour avoir une présentation lisible et imprimable, un récapitulatif que je n'ai créé que pour avoir des graphiques.

Une image de la page :
http://farm8.staticflickr.com/7431/8...8af3dfaf_z.jpg
www.flickr.com/photos/brischri/8957799354/

En abscisse, les années (et les mois), en ordonnée, les sites (et les 5 types de données).
Chaque rectangle contient 60 valeurs.

J'aimerais sortir des graphiques semblables pour le nombre de dossiers (D), le nombre de pièces (P) et le volume de données (V) sur une page par site.
Plusieurs boutons devraient lancer plusieurs macros : 1 site 1 an (celui en cours), tous les sites 13 mois (de mai 2012 à mai 2013, par ex.) et tous les sites 24 mois (en 2 colonnes par mois : janvier 2012, janvier 2013, février 2012, février 2013, etc.)

Les macros à créer auront une structure semblable (je pense) :
- Créer un tableau des premières lignes de titres (aTitres).
  - aTitre(0)=nombre de données (1 ou 27)
  - aTitre(1)="ANT" -> "VEU" ou "BRG"
- Créer un tableau (aTitresGraph) des titre de graphiques (Dossiers, Pièces, Volume)
- Créer sa deuxième ligne de titre dans une variable.
- Créer un classeur temporaire ayant aTitre(0) feuilles
- Renommer les feuilles : aTitre(n) => NomFeuille(n)
- Répéter n=0 -> aTitre(0)
  - Ouvrir Feuille(n)
  - Placer les 2 lignes de titre de la page
  - Répéter 3 fois pour
    
    créer le graphique D
    créer le graphique P
    créer le graphique V
- Écraser les graphiques pour en faire une image (gain de place et impossibilité de modifier par après sans avoir accès au classeur original)
Des petites questions :
- Cette façon de penser doit être correcte pour une colonne par mois, je ne sais pas si ça convient pour deux colonnes par mois et, si oui, comment présenter les données de manière à n'avoir qu'une macro à écrire (et maintenir).
- Comment passer les coordonnées des graphiques ?
- Comment remplacer les nombres des mois par leurs valeurs (Janvier àld 1, Février àld 2, etc.) ?
- Faut-il vraiment un classeur temporaire ? Ou un classeur par macro ? (Stats.1.1.xlsx, Stats.1.13.xlsx, Stats.27.1.xlsx, Stats.27...)

Christian

édit : l'image ne s’affiche pas comme à la prévisualisation, je verrai plus tard comment régler ce petit problème.
12/06/2013, 11h57
mercatog

Bonjour
Vous êtes sur la bonne voie néanmoins avec plus de précisions nécessaires
Une explication de la présentation des données (le cas échéant un petit classeur exemple avec des données non confidentielles)
Vous n'aurez pas besoins de classeurs temporaires.

PS: Il est possible de faire beaucoup de choses avec excel sans macros

Rien de confidentiel dans une série de chiffres...

brischri.angelfire.com/textes/statistiques.xlxs

Macro stat.01.12 (1 site, 1 an)
Sélectionner une cellule, cliquer sur le bouton (pas encore créé le bouton, bah, la macro non plus).
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 Option Explicit ' Constantes Const nColPrec As Integer = 2 ' nombre de colonnes avant le tableau Const nRowPrec As Integer = 3 ' nombre de lignes avant le tableau ' Variables Public nRow As Integer Public nCol As Integer ' Adresse du range Public nTop As Integer Public nLeft As Integer Public nBottom As Integer Public nRight As Integer ' Index et autres Public n As Integer Sub StatS01D12() ' ' S01D12 ' Site : 1 ' Durée : 12 mois, de janvier à décembre ' nRow = ActiveCell.Row nCol = ActiveCell.Column ' Calcul de nTop & nBottom nRow = nRow - nRowPrec ' enlever les titres de colonnes nTop = Int((nRow - 1) / 5) nTop = (nTop * 5) + 1 nTop = nTop + nRowPrec ' remettre les lignes du titre nBottom = nTop + 4 ' Calcul de nLeft & nRight nCol = nCol - nColPrec ' enlever les lignes du titre nCol = Int((nCol - 1) / 12) nCol = (nCol * 12) + 1 nLeft = nCol + nColPrec ' remettre les lignes du titre nRight = nLeft + 11 ' Vérification Range(Cells(nTop, nLeft), Cells(nBottom, nRight)).Select End Sub
Je ne sais pas quelles valeurs je dois passer à la macro qui créera les graphiques, j'attends un peu mais, normalement, cette macro devrait fonctionner.
Il faudra ajouter une ligne avant le tableau, au-dessus des titres de colonnes pour mettre les boutons. Et adapter la valeur de nRowPrec en conséquence.

Je ne suis pas certain que la définition des variables soit correcte, il me semble avoir eu quelques problèmes la dernière fois que j'ai utilisé cette façon de faire (mais c'était il y a un an au moins).

Pour vérifier que le range calculé soit correct, le sélectionner. D'où le
Range(Cells(nTop, nLeft), Cells(nBottom, nRight)).Select
qui me semble grammaticalement correct, mais j'inverse peut-être nTop et nLeft.

édit :
après deux ou trois petites corrections, ça fonctionne bien comme je le voudrais.
Il reste à remplir les titres et appeler la macro qui crée la page.

12/06/2013, 16h28
mercatog

La formule DECALER d'excel est très puissante pour ton cas.

Ci-joint proposition sans vba qui fonctionne avec la définition de l'étendue de la plage de cellule à prendre en considération dans le graphique en fonction du titre choisi et de la période choisie.

Le tout est géré par l’insertion de noms dynamique à l'aide de la fonction DECALER.

http://cjoint.com/13jn/CFmqIBWSmtf.htm

PS. ça reste perfectible
13/06/2013, 08h26
Invité

Joli travail que je vais essayer de comprendre.

Malheureusement, je ne pourrai utiliser ta feuille telle quelle.
Il me faut 3 graphiques par page pour envoyer les feuilles aux responsables, qu'ils puissent contrôler le travail effectué.
Et, à moins de créer la feuille site par site, je ne vois pas d'autre manière que créer 81 graphiques sur 27 pages, ce qui alourdirait fameusement le classeur. Sauf à transformer le graphique en image, ce qui impose (d'après ce que je connais d'Excel, c'est à dire peu) l'emploi de macros.

Bonjour

Citation:

Malheureusement, je ne pourrai utiliser ta feuille telle quelle.

Le but n'était pas d'utiliser le fichier exemple tel mais l'idée est là.

De la même manière, on peut créer les graphiques souhaités (3 graphiques) avec les données variables et faire enfin un petit code qui parcourt les 27 sites. change la source de données des graphiques et enregistre-les en images.

Exemple avec le fichier précédent avec un seul graphique par site à exporter. La structure du fichier est laissée telle.

Code (appelé par le bouton sur la feuille GR "Export Charts"):
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Sub TransferChart() Dim Rep As String, Fich As String Dim i As Byte Dim Tb Rep = ThisWorkbook.Path CreerSousRep Rep With Worksheets("GR") Tb = .Range("CODE") For i = 1 To UBound(Tb, 1) .Range("NTitre").Value = i Fich = Rep & Tb(i, 1) & "_" & Format(Now, "yyyymmdd-hhnn") & ".jpg" .ChartObjects(1).Chart.Export Filename:=Fich, FilterName:="jpg" DoEvents Next i .Range("NTitre").Value = 1 End With MsgBox "Export terminé" End Sub 'Sub pour créer un répertoire par jour Private Sub CreerSousRep(ByRef Rep As String) Rep = Rep & "\Charts_" & Format(Now, "yyyymmdd") & "\" If Dir(Rep, vbDirectory) = "" Then MkDir Rep End Sub
Fichier:
http://cjoint.com/13jn/CFnk1h2qSpp.htm

L'approche étant d'exploiter au maximum les possibilités d'Excel tout en restant raisonnable dans les ressources et ne faire appel à vba que si c'est absolument nécessaire.

13/06/2013, 11h51
Invité

Il me reste à trouver l'erreur : j'ai bien de 1 à 12 (jan-dec) pour ANT, j'ai le volume du site précédent pour les autres.

Il me reste à trouver comment choisir les données pour me limiter à D, P ou V.

Il me reste à coller les jpg dans un fichier, mais ça, ça va vite (gimp et une feuille modèle).

Il me reste 24 heures pour y arriver.
Ça devrait être possible... non, je rêve, mais le départ est superbe.
13/06/2013, 12h57
mercatog

C'est une erreur de ma part. Il fallait mettre dans le définition du nom MM

Code:

=DECALER(General!DD;2-LIGNE(General!DD);)

Pour amélioration, j'ai ajouté en ligne 138 la formule donnant le mois et l'année (qu'on va prendre pour abscisse)

En C138, la formule que je tire vers la droite

Code:

=TEXTE(DATEVAL("01/"&C2&"/"&DECALER($A$1;;12*(ENT((COLONNE(C1)-3)/12))+2));"mmm aaaa")

Ensuite, je redéfinit le nom MM par la formule

Code:

=DECALER(General!DD;138-LIGNE(General!DD);)

Cf fichier http://cjoint.com/13jn/CFnno11situ.htm
13/06/2013, 14h12
Invité

Un grand merci.

Je dois partir, je regarderai plus à l'aise demain.
J'espère avoir des questions (pas trop) pour bien comprendre ce que tu as fait.

Et je vais voir pour créer 5 graphiques différents pas site, je n'aurai plus qu'à choisir ceux qui me seront nécessaire quand on me les demandera.

:merci:
14/06/2013, 11h53
Invité

Voilà ce que j'obtiens sur mon PC :

http://farm8.staticflickr.com/7406/9...1b50bd1e_b.jpg

Jan Tuesday et Feb Friday des étiquettes et le jj.06.Friday du titre ne sont pas corrects.
Une erreur de format est possible.
Pas grave puisque j'essaie de comprendre ce qui a été fait et qu'il faut bien commencer quelque part, autant commencer par là.

Pour le titre, no problemo, cette partie là peut disparaître, elle n'est pas indispensable.
J'obtiens :

http://farm3.staticflickr.com/2881/9...b02f3a01_b.jpg

Si la différence n'est pas bien grande, le résultat est tout aussi compréhensible.

Pour les étiquettes, je lis ta formule :
=TEXTE(DATEVAL("01/"&C2&"/"&DECALER($A$1;;12*(ENT((COLONNE(C1)-3)/12))+2));"mmm aaaa")
et je la refait, morceau par morceau.
En prenant la cellule AB1 (février 2009),
je calcule le décalage, =(12*(ENT((COLONNE(AB1)-3)/12)))+2, j'arrive à 26. Correct.
je recrée la date, ="01/"&AB2&"/"&DECALER($A$1;0;(12*(ENT((COLONNE(AB1)-3)/12)))+2), j'obtiens 01/02/2009. Correct
=DATEVAL("01/"&AB2&"/"&DECALER($A$1;0;(12*(ENT((COLONNE(AB1)-3)/12)))+2)) me donne 39845 que je suppose être correct. Un contrôle simple =ANNEE(G148) donne 2009 et =MOIS(G148) donne 2. Jusque là, tout est bon.

Les problèmes commencent quand j'utilise la fonction TEXTE()
=TEXTE(G148;"mmm aaaa") me donne Feb Sunday...
Je suis content, j'ai bien compris comment ça a été écrit et, si ma formule est légèrement différente (je n'ai pas le même nombre de parenthèses, j'en ai plus), j'obtiens bien le même résultat.
'fin, content, c'est une façon de parler, j'aimerais avoir Fév 2009.

Qu'est-ce qui cloche dans l'emploi de TEXTE() ?
Pourquoi un résultat en anglais ?

=TEXTE(DATEVAL("01/"&AB2&"/"&DECALER($A$1;0;(12*(ENT((COLONNE(AB1)-3)/12)))+2));"mmm") & " " & CTXT(ANNEE(DATEVAL("01/"&AB2&"/"&DECALER($A$1;0;(12*(ENT((COLONNE(AB1)-3)/12)))+2)));0;1)

me donne un bon résultat, mais ets, peut-être, un peu long pour un usage journalier
14/06/2013, 13h27
mercatog

Bonjour

Bon courage et ravi pour ton analyse.

Pour la question du format donnée par excel, tes dates sont en anglais. je ne peux me prononcer.

Mais, il y a toujours des mais.

dans la feuille GR tu as une liste de mois (nommée MOI) qu'on pourra facilement utiliser

Formule en C138 de la feuille General

Code:

=GAUCHE(INDEX(GR!MOI;C2);4)&" "&DECALER($A$1;;12*(ENT((COLONNE(C1)-3)/12))+2)

A copier vers la droite
17/06/2013, 08h49
Invité

C'est plus court et ça fonctionne bien.
Merci

Reprenons la feuille GR de ton dernier fichier en prenant BRU, de juin 2012 à mai 2013.

En I2, choix du site : BRU.
BRU est le 4ème site (NTitre), K2=4, L2=5*K2-2 (facile, c'est écrit) = 18
I4, année de début : 2012.
2012, 6ème dans la liste des années, K4=6. Nom de K4 : NAD
I5, mois de début : juin.
Juin, 6ème de la liste des mois, K5=6. Nom de K5 : MAD
En L5, j'ai ajouté une valeur L5=12*NAD-10+MAD. Pas de nom à L5, mais on pourrait l'appeler CD (colonne de début) pour autant que cette donnée soit nécessaire.
Même chose pour la date de fin avec NAF, MAF et CF (ajouté en L8).
En N2 tu crées le titre du graphique.

Ça, ça va.
Où ça ne va pas, c'est comment lier le graphique et ces données ?
Pourrais-tu m'expliquer, pas par pas, comment faire le graphique pour F, C ou P? (je ne prends ni D ni V, premier et dernier type de données, de manière à avoir des séries non adjacentes).
J'essaierai de faire les 4 autres graphiques, de manière à avoir les données séparées et des couleurs différentes par type de données.
Il ne me restera qu'à (j'adore ces yaka) modifier la macro...
Fusionner les 5 graphiques de la page en un seul fichier jpg (voir http://excel.developpez.com/faq/?pag...ursgraphsImage ).

Je vais m'aider de http://fauconnier.developpez.com/rel...nctiondecaler/
17/06/2013, 10h43
mercatog

Bonjour

Commence par analyser la formule du nom DD (C'est la base, toutes les autres en découlent)

Code:

=DECALER(General!$C$1;GR!N-1;12*GR!NAD+GR!MAD-13;;12*(GR!NAF-GR!NAD)+GR!MAF-GR!MAD+1)

Pour trouver la série D, on décaler C1 de la feuille General de:
N-1 lignes (avec N=5K2-2 et K2 l'index de la série choisie).

12NAD+MAD-13 colonnes (NAD: l'index de l'année de début et MAD: l'index du mois de début).

La plage recherchée aura:
1 ligne comme hauteur (Cf. omission du 3ème paramètre parce que la place de départ C1 a une seule ligne)

12(NAF-NAD)+MAF-MAD+1 colonnes comme largeur (NAF: l'index de l'année de fin, NAD: l'index de l'année de début et MAF: l'index du mois de fin, MAD: l'index du mois de début).

Les autres séries seront décalées facilement par rapport à la série nommée DD

Exemple pour la série F (nommée FF)

Code:

=DECALER(General!DD;2;)

On décaler la série D (nommée DD) de 2 lignes vers le bas.

Idem pour les autres.

La série des abscisses (écrites en ligne 138) est nommée MM

Code:

=DECALER(General!DD;138-LIGNE(General!DD);)

On décale la série DD de 138 - la ligne de DD.
Remarque qu'on pouvait aussi utiliser le N.

Pour la construction des graphiques, on construit un graphique aléatoire. Ensuite on sélectionne la série de données sur le graphique.
Dans la barre des formules on change la formule par celle ci (par exemple pour la série D)

Code:

=SERIE("D";General!MM;General!DD;1)

SERIE a 4 paramètres: le nom de la série, la plage des abscisses,la plage des ordonnées et enfin l'index de la série (pour les graphiques comportant plusieurs séries).
18/06/2013, 16h15
Invité
J'approche doucement de la fin.

Je suis reparti du fichier de départ, pour comprendre, c'est mieux. Et je préfère comprendre que d'ânoner.

J'ai ajouté un bloc de données, BELG pour les totaux.
Et j'ai tenu compte d'une des spécificités de chez nous, la Belgique est un pays trilingue. Il me faut donc utiliser décembre, december et dezember en fonction de la langue du site. Pas trop compliqué (une colonne langue, une table des noms, la fonction index() et roulez).
J'espère finaliser le travail demain et mettre le fichier en ci-joint.

Presque résolu :D
J'ai bien mes 5 graphiques, un par donnée.
Il reste un problème, le titre du premier graphique doit être différent des autres.
"site date début -- date fin" retour chariot D, C, F, P et V. J'ai mon titre particulier en F24, je n'arrive pas à le coller dans mon graphique.

Je suis occupé avec les macros : créer le fichier pour un site, créer les fichier pour tous les sites.

J'espère pouvoir mettre un mode d'emploi complet avant 16h.

D'ors et déjà un grand merci à mercatog pour son aide plus que précieuse.

Le fichier presque complet.
Déjà utilisable en l'état.
http://cjoint.com/13jn/CFsp0FdfYHt.htm

Il me reste deux choses à faire :
- modifier le titre du premier graphique (y mettre le contenu de la cellule A1), je ne sais pas encore comment, je trouverai, même s'il faudra du temps, mais j'y arriverai.
- améliorer la qualité du jpg produit en sortie. Ça, c'est une autre paire de manches.
Je pense que l'usage de la fonction DEPLACER() m'est plus claire.

Si la colonne A est large, ce'st qu'il y a deux raisons pratiques.
En modifiant la macro qui fusionne les graphiques de la page, j'ai commis une erreur, la macro s'est plantée après avoir créé le nouveau graphique qui est mis en position 0,0 (soit en cellule A1), ce qui rendait la feuille inaccessible. Et je n'aime pas ça.
De plus, il est possible de n'imprimer que la première page de la feuille (format A4). Un chgt dans la valeur de la cellule A1 pour enlever le "D" qui termine le titre et le tour est joué. On a une feuille avec les cinq graphiques qui correspondent à un site.
18/06/2013, 16h50
mercatog

Citation:

modifier le titre du premier graphique (y mettre le contenu de la cellule A1), je ne sais pas encore comment, je trouverai, même s'il faudra du temps, mais j'y arriverai.

Sélectionne le titre (dans le graphique) et dans la barre des formule insère la simple formule

Code:

=GR!$A$1

Le titre changera avec le changement de la cellule A1
19/06/2013, 14h53
Invité

Citation:

Envoyé par mercatog

Sélectionne le titre (dans le graphique) et dans la barre des formule insère la simple formule

Code:

=GR!$A$1

Le titre changera avec le changement de la cellule A1

Une fois de plus merci.

Citation:

Envoyé par BrisChri

Il me reste deux choses à faire :

modifier le titre du premier graphique (y mettre le contenu de la cellule A1), je ne sais pas encore comment, je trouverai, même s'il faudra du temps, mais j'y arriverai.
améliorer la qualité du jpg produit en sortie. Ça, c'est une autre paire de manches.

La première chose est réglée grâce à mercatog.
La seconde aussi, grâce à une astuce à la ligne 8 :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Sub PrtSite() For j = 1 To 5 Tableau(j) = ActiveSheet.ChartObjects(j).Name Next j Set Sh = ActiveSheet.Shapes.Range(Tableau).Group Sh.CopyPicture Dim ochart As ChartObject Set ochart = ActiveSheet.ChartObjects.Add(0, 0, 3.4 * Sh.Width, 3.4 * Sh.Height) With ochart.Chart .Paste Fich = Rep & Tb(i, 1) & "_" & Format(Now, "yyyymmdd") & ".jpg" .Export Filename:=Fich, FilterName:="jpg" DoEvents End With ActiveSheet.ChartObjects(ochart.Name).Delete Sh.Ungroup End Sub
En cherchant à gauche et à droite la manière d'améliorer la qualité du jpg produit, j'ai trouvé un info assez simple : les graphiques Excel sont du dessin vectoriel. Et qui dit vectoriel dit redimensionnable à souhait.

A la création, la ligne est celle-ci :

Code:

Set ochart = ActiveSheet.ChartObjects.Add(0, 0, Sh.Width, Sh.Height)

On ouvre le jpg produit pour en connaître les dimensions.
Dans mon cas, après avoir élargi mes graphiques (ils sont plus larges que dans le fichier joint plus haut), j'arrive à 720 * 1020pxl.
Une feuille A4 a pour dimensions : 2480 * 3508pxl. Un calcul rapide me donne les rapports : A4 est 3.4... plus large et 3.4... plus haut.
J'agrandis simplement la taille des fichiers en multipliant Sh.Width et Sh.Height par 3.4
Régler l'impression de manière à avoir le meilleurs remplissage proportionnel. Le tour est joué et la différence de qualité entre les impressions est flagrante.