Graphique Excel auto-ajusté à la période et au vendeur

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Graphique sur période dynamique

Dans cette formation, nous apportons la solution pour adapter la vue d'une représentation graphique, en fonction de plusieurs contraintes recoupées, émises dynamiquement par l'utilisateur.



Dans l'exemple illustré à l'écran, nous travaillons sur un tableau des ventes réalisées par les commerciaux de l'entreprise. Ces chiffres sont représentés sur le premier semestre, soit de Janvier à Juin. L'utilisateur choisit un mois de début et un mois de fin à l'aide de deux listes déroulantes respectives. Puis, il choisit le nom du commercial à représenter avec une dernière liste déroulante. Aussitôt, la représentation graphique s'ajuste sur la période délimitée pour le vendeur sélectionné. Dans le même temps, une mise en forme conditionnelle met en lumière les données correspondantes dans le tableau. Cette solution est donc particulièrement efficace pour comparer les chiffres, les interpréter rapidement et analyser les évolutions.

Source et présentation de la problématique
Pour réaliser ces travaux, il est nécessaire de les appuyer sur un tableau des ventes.

• Télécharger le classeur graphique-choix-periode.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Le tableau des ventes est placé sur la gauche de la feuille. Sa représentation graphique est placée sur sa droite. Mais pour l'instant, elle est totalement figée. Elle considère la période complète et un vendeur en particulier, choisi arbitrairement. Mais c'est bien à partir de ce moule que nous allons pouvoir enclencher le mécanisme d'ajustement dynamique.

Au-dessus du graphique, les trois critères peuvent être réglés grâce à des listes déroulantes, en cellules respectives K3, L3 et N3.

• Cliquer sur la courbe de la représentation graphique pour la sélectionner,

Dès lors, la barre de formule livre la syntaxe de sa construction.

=SERIE(Ventes!$B$10; Ventes!$C$5:$H$5; Ventes!$C$10:$H$10; 1)

C'est elle que nous allons devoir retravailler par calculs dynamiques, prenant en compte les contraintes émises. Le premier argument de la fonction Serie est le suivant : Ventes!$B$10. Nous devons le rendre dynamique en désignant la cellule N3 du choix utilisateur. Le deuxième argument (Ventes!$C$5:$H$5) représente la période, soit les mois dans la ligne de titre du tableau. Nous allons devoir l'ajuster en fonction des mois choisis. Pour cela, il est nécessaire de créer une plage nommée et de la retravailler avec la fonction Excel Decaler. Le troisième argument (Ventes!$C$10:$H$10) fait référence aux données numériques, soit les ventes, à représenter graphiquement sur cette période. Là encore, nous allons créer une plage nommée que nous allons retravailler avec la fonction Excel Decaler. A titre informatif, le dernier argument (1) indique simplement qu'il ne s'agit pas d'un graphique empilé. Les données ne sont pas cumulées et seule une série est représentée.

Ajuster la période du graphique
Pour ajuster la période sur la ligne de titre des mois du tableau, nous devons initialiser la plage sur le mois de début choisi et l'étendre jusqu'au mois de fin défini. Pour cela nous devons exploiter la fonction Excel Decaler afin d'opérer un décalage à l'horizontale par rapport au point de départ et afin de retravailler sa largeur.

• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Gestionnaire de noms,
• Dans le gestionnaire qui apparaît, cliquer sur le bouton Nouveau,
• Dans la zone Nom de la boîte de dialogue qui suit, taper l'intitulé : Periode,
• En bas de la boîte de dialogue, supprimer la syntaxe statique de la zone Fait référence à,
• Taper le symbole égal (=) pour initier la nouvelle syntaxe dynamique,
• Inscrire la fonction d'ajustement suivie d'une parenthèse, soit : Decaler(,
• Sélectionner le premier mois en cliquant sur sa cellule C5, ce qui donne : Ventes!$C$5,

Nous définissons ainsi le point de départ arbitraire de la plage. Celui-ci doit se déplacer pour correspondre avec le mois effectivement choisi par le biais de la première liste déroulante. Il s'agit donc d'opérer un décalage en colonne.

• Taper deux points-virgules, soit : ;;, pour ignorer l'argument du décalage en ligne,

De fait, nous sommes placés dans l'argument du décalage en colonne. Celui-ci se définit en trouvant la position du mois choisi dans la ligne de titre du tableau. Pour la trouver, il suffit de la chercher avec la fonction Excel Equiv.

• Inscrire le nom de la fonction suivi d'une parenthèse, soit : Equiv(,
• Désigner le mois à chercher en cliquant sur sa cellule K3, ce qui donne : Ventes!$K$3,
• Taper un point-virgule (;) pour passer dans l'argument de la ligne de recherche,
• Désigner la ligne de titre du tableau, soit la plage Ventes!$C$5:$H$5,
• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour amorcer une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, retrancher une unité à ce résultat, soit : -1,

En effet, la position de départ est déjà incluse dans le décompte. Pour calculer la largeur, cette position doit être exclue.

• Taper un point-virgule (;) pour passer dans l'argument de la hauteur de la plage,
• Saisir le chiffre 1 pour désigner seulement la ligne de la période,
• Taper un point-virgule (;) pour passer dans l'argument de la largeur variable,

Le calcul de la largeur consiste à réaliser la différence entre la position du mois de fin et la position du mois de début. Toutes deux peuvent être trouvées grâce à la fonction Equiv, selon la syntaxe que nous venons d'employer. Il est opportun de copier cette syntaxe pour la reproduire et l'adapter.

• De fait, construire la syntaxe suivante :

Equiv(Ventes!$L$3; Ventes!$C$5:$H$5; 0)-Equiv(Ventes!$K$3; Ventes!$C$5:$H$5; 0)

Nous cherchons la position du mois de début (Ventes!$L$3) dans la ligne de titre. Nous lui retranchons la position du mois de fin (Ventes!$C$5:$H$5).

• Ajouter une unité à ce résultat, soit : +1,

Du fait du décalage opéré, la position de départ est incluse dans le décompte. Mais elle ne doit pas être enlevée. Nous ajustons donc la largeur en corrigeant ce défaut.

• Fermer la parenthèse de la fonction Decaler,
• Avant de valider, sélectionner et copier (CTRL + C) l'intégralité des trois derniers arguments :

EQUIV(Ventes!$K$3; Ventes!$C$5:$H$5; 0)-1; 1; EQUIV(Ventes!$L$3; Ventes!$C$5:$H$5; 0)-EQUIV(Ventes!$K$3; Ventes!$C$5:$H$5; 0)+1

• Puis, cliquer sur le bouton Ok de la boîte de dialogue,
• De retour sur le gestionnaire, cliquer sur le bouton Fermer pour revenir sur la feuille,

La syntaxe que nous avons construite pour la période dynamique du graphique est la suivante :

=DECALER(Ventes!$C$5;; EQUIV(Ventes!$K$3; Ventes!$C$5:$H$5; 0)-1; 1; EQUIV(Ventes!$L$3; Ventes!$C$5:$H$5; 0)-EQUIV(Ventes!$K$3; Ventes!$C$5:$H$5; 0)+1)

Adapter le vendeur à représenter
Avant d'exploiter cette plage, nous devons construire celle permettant de désigner les chiffres du vendeur en fonction du nom choisi avec la troisième liste déroulante. Le principe est similaire. Le décalage en colonne et la largeur sont identiques. Les plages doivent en effet correspondre pour être représentées graphiquement. C'est la raison pour laquelle nous avons prélevé la syntaxe précédemment construite. Ce qui change, c'est l'argument du décalage en ligne. Nous devons nous positionner sur la ligne du bon vendeur en recherchant sa position, une fois encore grâce à la fonction Equiv.

• Dans la section Noms définis du ruban Formules, cliquer sur le bouton Gestionnaire de noms,
• Dans la boîte de dialogue du gestionnaire, cliquer sur le bouton Nouveau,
• Dans la zone Nom de la nouvelle boîte de dialogue, taper l'intitulé : Vendeur,
• Supprimer la syntaxe statique de sa zone Fait référence à,
• Puis, taper le symbole égal (=) pour initier la nouvelle syntaxe dynamique,
• Inscrire la fonction d'ajustement suivie d'une parenthèse, soit : Decaler(,
• Désigner la première vente comme point de départ en cliquant sur C6, soit : Ventes!$C$6,
• Taper un point-virgule (;) pour passer dans l'argument du décalage en ligne,
• Inscrire la fonction cherchant la position, suivie d'une parenthèse, soit : Equiv(,
• Désigner le vendeur choisi en cliquant sur sa cellule N3, ce qui donne : Ventes!$N$3,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Sélectionner tous les noms du tableau, ce qui donne : Ventes!$B$6:$B$19,
• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, retrancher une unité à ce résultat, soit : -1,

Comme précédemment, ce décalage ne doit pas tenir compte de la position de départ qui est déjà incluse dans la plage. Nous ajustons donc ce déplacement.

• Taper un point-virgule (;) pour passer dans l'argument du décalage en colonne,

C'est à partir de ce point que la syntaxe est identique à la précédente. Nous devons désigner les ventes sur la même période que celle désignée pour les mois du tableau.

• Coller (CTRL + V) la syntaxe précédemment copiée, soit :

EQUIV(Ventes!$K$3; Ventes!$C$5:$H$5; 0)-1; 1; EQUIV(Ventes!$L$3; Ventes!$C$5:$H$5; 0)-EQUIV(Ventes!$K$3; Ventes!$C$5:$H$5; 0)+1

• Fermer alors la parenthèse de la fonction Decaler,
• Cliquer sur le bouton Ok de la boîte de dialogue pour valider la syntaxe,
• Enfin, cliquer sur le bouton Fermer du gestionnaire pour revenir sur la feuille,

La syntaxe que nous avons construite pour représenter les ventes du commercial est la suivante :

=Decaler(Ventes!$C$6; Equiv(Ventes!$N$3; Ventes!$B$6:$B$19; 0)-1; EQUIV(Ventes!$K$3; Ventes!$C$5:$H$5; 0)-1; 1;EQUIV(Ventes!$L$3; Ventes!$C$5:$H$5; 0)-EQUIV(Ventes!$K$3; Ventes!$C$5:$H$5; 0)+1)

Ajuster la série du graphique
Nous devons désormais exploiter ces plages dynamiques dans la source de données du graphique afin que ce dernier réagisse aux contraintes émises par l'utilisateur.

• Cliquer sur la courbe du graphique pour la sélectionner,
• Dans la barre de formule, remplacer le premier argument Ventes!$B$10 de la fonction par :

Ventes!$N$3

En pointant désormais sur la cellule du choix de l'utilisateur, nous adaptons le nom du graphique au nom du commercial choisi. Les deux arguments suivants doivent être remplacés par les noms de plages que nous avons créés. Mais comme nous l'avons appris dans la formation précédente, dans une série, le nom d'une plage doit être préfixé du nom du classeur stipulé entre simples côtes.

• Remplacer le deuxième argument par le suivant :

'graphique-choix-periode.xlsx'!Periode

• Puis, remplacer le troisième argument par le suivant :

'graphique-choix-periode.xlsx'!Vendeur

• Enfin, valider avec la touche Entrée du clavier,

Maintenant, si vous changez les mois ainsi que le nom du vendeur, vous constatez que la représentation graphique s'ajuste en cohérence automatiquement.

Surligner les ventes du graphique
Comme nous le disions en préambule, pour plus de pertinence et d'efficacité, nous souhaitons surligner les ventes représentées sur le graphique. La règle de mise en forme conditionnelle doit vérifier que les cellules sont bien situées entre les colonnes des mois désignés. Nous devons donc comparer le résultat retourné par la fonction Colonne avec les positions retournées par la fonction Equiv. Dans le même temps, nous devons nous assurer que ces cellules sont bien placées sur la ligne du vendeur. Nous devons donc comparer les résultats retournés par la fonction Ligne avec la position fournie par la fonction Equiv.

• Sélectionner toutes les ventes du tableau, soit la plage de cellules C6:H19,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir la commande Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone de saisie du dessous pour l'activer,
• Dès lors, reproduire la syntaxe suivante :

=ET(COLONNE(C6)>=EQUIV($K$3; $C$5:$H$5; 0)+2; COLONNE(C6)<=EQUIV($L$3; $C$5:$H$5; 0)+2;LIGNE(C6)=EQUIV($N$3; $B$6:$B$19; 0)+5)

Trois conditions sont à honorer, deux sur la colonne pour l'intervalle et une sur la ligne. Nous exploitons donc la fonction Et pour les énumérer. Comme l'analyse d'une mise en forme conditionnelle est chronologique, nous initialisons les conditions sur la première vente, soit la cellule C6. Ainsi, elles seront toutes passées en revue. Nous nous assurons que la cellule est bien située entre les bornes de la période définie. Sa colonne doit être supérieure ou égale à la position du mois de début (EQUIV($K$3; $C$5:$H$5; 0)+2) et inférieure ou égale à la position du mois de fin (EQUIV($L$3; $C$5:$H$5; 0)+2). La fonction Colonne raisonne en valeur absolue. En revanche, la fonction Equiv raisonne relativement à la sélection. C'est la raison pour laquelle nous ajoutons deux unités dans chaque comparaison. Ainsi, nous annihilons le décalage opéré par les deux colonnes situées avant les ventes. Enfin, nous vérifions dans le même temps que la ligne de la cellule est bien identique à la position en ligne du vendeur choisi (LIGNE(C6)=EQUIV($N$3; $B$6:$B$19; 0)+5). Cette fois, cinq unités supplémentaires sont nécessaires pour corriger le décalage induit par les 5 lignes placées au-dessus des noms des vendeurs. Attention de bien exploiter les références absolues dans les recherches de la fonction Equiv. En effet, en même temps que l'analyse se déplace de cellule à cellule, les contraintes elles restent figées dans leurs cases.

• Cliquer sur le bouton Format en bas de la boîte dedialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un orange proche de celui du graphique,
• Valider ces attributs en cliquant sur le bouton Ok,
• De retour sur la première boîte de dialogue, valider la création de la règle,

Désormais, à chaque changement de période et de vendeur, en même temps que la représentation graphique s'ajuste, les données exploitées sont automatiquement surlignées dans le tableau.