Discussion :

[Invité]

J'avais un problème de graphique dynamique.
J'appelle dynamique le graphique dont la courbe change en fonction de la plage de données, plage qui doit pouvoir être choisie par l'utilisateur.

Je suis programmeur, je ne connais pas bien Excel, en fait, comme tout utilisateur lambda, je sais trier, imprimer et utiliser les fonctions courantes, bref, rien de transcandant.
La réponse de mercatog est arrivée : utilise la fonction decaler().
Je me suis aperçu que mes collègues, dont certains ont donné des formation Excel de base, ne connaissaient pas cette fonction.

Je vais essayer de l'expliquer au départ d'un cas pratique, je vais reprendre mon problème et montrer comment le résoudre.

Pour la question de départ : http://www.developpez.net/forums/d13...hiques-en-vba/

[Invité]

DECALER(PlageDepart ; DecalageLignes ; DecalageColonnes ; [NbrLignes] ; [NbrColonnes])

DECALER() est peu connue pour une raison qui me semble simple, parce qu'elle renvoie une plage et non une valeur cette fonction n'est pas utilisable dans une cellule.
Mais, si on fait un petit effort pour bien comprendre comment elle fonctionne, elle est puissante. La plage renvoyée pouvant être calculée et modifiée par calcul, cette fonction se prête très bien à la création de graphiques dynamiques.

Paramètres :

• PlageDepart :

ici, la plage de départ des calculs est une cellule.
cette cellule peut être dans le tableau ou en dehors. C'est un choix qui doit être fait avant de commencer ses calculs.
La feuille sur laquelle se trouve le tableau contient des titres, titres de colonnes et titres de lignes.
PlageDepart sera la cellule(1,1) ou la cellule(0,0) du tableau. N'oubliez pas que, perce qu'il y a des titres, la cellule(1,1) du tableau n'est pas la cellule(A1) de la feuille.

Personnellement, pour simplifier les calculs, je préfère une cellule en dehors du tableau.
Pour avoir moins à écrire, désormais j'écrirai ct(0,0) pour cellule(0,0) du tableau.

• DecalageLignes et DecalageColonnes :

Puisque mon graphique se veut dynamique il n'aura pas toujours les mêmes coordonnées. On calculera le nombre de lignes et le nombre de colonnes qui me permettront d'arriver à la ct(haut, gauche) de départ au graphique.

• [NbrLignes] et [NbrColonnes]

La zone de données d'un graphique est définie par ct(haut, gauche) et ct(bas, droite).
haut + NbrLignes = bas
gauche + NbrColonnes = droite

[Invité]

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

Si on veut calculer les décalage H et V, il faut que le maillage du tableau soit régulier.

Les feuilles 2013 à 2007 contiennent le relevé des données.
Comme, en 2007, le volume de données n'était pas accessible, il n'y a pas de ligne V.
La donnée est apparue en 2009 pour disparaître plus tard, lors d'un changement de matériel, et revenir en 2012.

Parce que ces données ont une présentation différente en fonction de l'année et que le calcul des déplacement impose un maillage unique, il y a une feuille de regroupement, General!.

Si votre fichier de départ a déjà un maillage unique, inutile d'ajouter une feuille.

• Les blocs de données

Chaque bloc contient les relevés mensuel pour cinq données différentes. Le nombre de dossiers (D) n'a rien à voir avec le volume qu'occupent les données sur la DB (V).
60 relevés par bloc, 12 mois x 5 types de données.

Rien de bien compliqué donc.

[Invité]

Ajouter une feuille au classeur : GR

• Décalage vertical :

DecalageLignes est fonction du site.
Il faut créer une zone de liste déroulante qui sera liée à une table de données. Le choix de l'utilisateur sera conservé dans une cellule, c'est cette valeur qui servira à calculer DecalageLignes.

Pour éviter d'avoir la table à l'écran (je crée la page mais c'est quelqu'un d'autre qui s'en servira), je mets les noms des sites assez loin sur la feuille.
ANT dans AA51, BELG dans AA78.
En AA50 on entre le nom de la table "tSites" (le nom des tables commence par la lettre t).
On sélectionne la plage de données sans le titre, clic droit, définir un nom...

Nom : tSites (déjà proposé par Excel)
Zone : GR
Commentaire : absent
Fait référence à : ne pas modifier la valeur qui est =GR!$AA$51:$AA$78

Même s'il ne s'agit que d'une zone de travail, j'essaie d'avoir une présentation claire.
Titre en gras, centré et bordure, données centrées et bordure extérieure.

Tant qu'on est de ce côté, autant ajouter la cellule qui recevra le choix de l'utilisateur. J'entre "iSite" en gras en AA84 (i, ce n'est plus un titre mais un indice), une valeur quelconque en AA85, sélection de AA84 et AA85, centrer, "toutes les bordures".
Sélectionner AA85, clic droit, définir un nom...

Nom : iSite (déjà proposé par Excel)
Zone : GR

Revenir au début de la feuille [CTRL]+[Home] pour créer sa première zone de liste déroulante.
Onglet développeur, insérer, zone de liste déroulante.
Tracer le rectangle (perso : colonnes L & M, ligne 6), clic droit dans le rectangle, format de contrôle...

Plage d'entrée : tSites
Cellule liée : iSite

Clic en dehors de la zone de liste (J6 dans mon cas) entrer "Site : ", alignement à droite.
Tester : sélectionner BRU. En AA85 on doit avoir 4.
DecalageLignes = 5 * (iSite -1)
En AA90 j'entre "DecalageLignes" et la formule en AA91 (valeur obtenue : 15). Il reste à donner un nom à la cellule.

tSites
ANT
ARL
BRG
BRU
CHA
DEN
DIN
EUP
GEN
HAS
HUY
IEP
KOR
LEU
LIE
MAR
MEC
MON
NAM
NEU
NIV
OUD
TON
TOU
TUR
VER
VEU
BELG

[Invité]

Si DeacalageLignes dépend du site, DecalageColonnes dépend de la date de début. Année et mois.
On crée une tAnnees en AC, titre ligne 50, valeurs à partir de AC51. Perso, j'en ai mis 28, ma colonne a la même longueur que celle des sites. On arrive à 2034. Plus que suffisant, il y a de forte chances que, d'ici-là, tout soit modifié
Ne pas oublier de donner un nom de zone

AC84 "iAnDeb", entrer 1 (ou tout autre chiffre) en AC85. Soigner sa présentation. Donner un nom de zone.
AC87 "iAnFin", entrer 1 en AC88. Soigner sa présentation. Donner un nom de zone.

Retour en début de page pour créer sa zone de liste.

Faire son rectangle en ligne 9, colonnes L & M.
Clic droit, format de contrôle... : tAnnees et iAnDeb
La valeur iAnDeb est vite vérifiée.

Il faut faire la même chose avec les mois.
La Belgique est un pays trilingue. Envoyer les documents dans la langue de son interlocuteur est le minimum de politesse.
La tMois sera une table de trois colonnes (DE - FR - NL).
Et pour s'en servir, il faut une tLangues.
Insérer 2 colonnes avant tAnnees, créer tLangues.
tLangues
3
2
3
2
2
3
2
1
3
3
2
3
3
3
2
2
3
2
2
2
2
3
3
2
3
2
3
2

tMois (AG50)
Januar Janvier Januari
Februar Février Februari
März Mars Maart
April Avril April
Mai Mai Mei
Juni Juin Juni
Juli Juillet Juli
August Août Augustus
September Septembre September
Oktober Octobre Oktober
November Novembre November
Dezember Décembre December

Par facilité, lorsque vous avez créé les tables et que vous leur avez attribué un nom, il faut sélectionner la colonne des titres en français et lui donner un nom : tMoisFR.

Il reste à créer la zone de liste déroulante pour le choix des mois.
Vérifiez que ça fonctionne.

Il reste à calculer DecalageColonnes.
DecalageColonnes = (décalage dû à l'année) + (décalage dû au mois)
DecalageColonnes = (12 * (iAnDeb -1)) + iMoisDeb
On entre DecalageColonnes en AE90, la formule en AE91 et on soigne sa présentation.

[Invité]

Puisque je vais tracer cinq graphiques ne reprenant, chacun, qu'une donnée, NbrLignes = 1, tout simplement.

NbrColonnes dépend de la date de fin sélectionnée. Année et mois.
On a déjà créé et nommé iAnFin et iMoisFin, il reste à les remplir. Pour ça, on crée les deux zones de liste déroulantes correspondantes.
Et on calcule en colonnes.
NbrColonnes = déplacement( date de fin ) - déplacement (date de début ) + 1
NbrColonnes = ((12 * (iAnFin -1)) + iMoisFin) - ((12 * (iAnDeb -1)) + iMoisDeb) + 1
NbrColonnes = ((12 * (iAnFin -1)) + iMoisFin) - DecalageColonnes + 1
On crée la zone (toujours deux lignes dont une de titre), on recopie la formule et on donne un nom à la cellule.