Insérer automatiquement des données dans un graphique

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


Sujets que vous pourriez aussi aimer :


Insertion automatique de nouvelles données

Dans une précédente formation, nous avons appris à adapter automatiquement la vue d'un graphique Excel au choix d'un mois à représenter, par le biais d'une liste déroulante. Ici, nous souhaitons améliorer encore l'ergonomie.



Dans l'exemple illustré par la capture, la représentation graphique réagit toujours au changement de mois orchestré par l'utilisateur. Mais s'il venait à ajouter un nouveau commercial avec ses ventes à la suite du tableau, celui-ci serait automatiquement intégré dans la vue du graphique.

Source et présentation
Pour aboutir cette solution, nous devons récupérer les travaux précédents.

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

Le tableau des ventes est donc situé sur la gauche de la feuille. La représentation graphique de ses données est placée sur sa droite. Elle varie selon le choix du mois défini par l'utilisateur à l'aide d'une liste déroulante.

• Au-dessus du graphique, sélectionner la cellule K3,
• Puis, déployer sa liste déroulante,
• Choisir alors un mois différent,

Comme vous pouvez le voir, la représentation graphique se recompose aussitôt pour s'ajuster sur le mois choisi. Mais à ce stade, si les ventes de nouveaux commerciaux étaient ajoutées à la suite du tableau, elles ne seraient pas prises en compte. Et nous allons comprendre pourquoi.

• Cliquer sur l'une des barres du graphique pour sélectionner toute la série,

Dès lors, la barre de formule au-dessus de la feuille Excel, livre la syntaxe de sa source de données.

=SERIE(Ventes!$K$3; Ventes!$B$6:$B$13; 'insertion-automatique-graphique.xlsx'!Periode; 1)

Le troisième argument de la fonction Serie correspond aux données numériques représentées graphiquement. Il s'agit donc des ventes pour le mois choisi.

'insertion-automatique-graphique.xlsx'!Periode

Il fait appel à une plage de cellules nommée Periode et préfixée du nom du classeur. C'est elle que nous avions travaillée avec la fonction Decaler pour ajuster dynamiquement la vue sur le bon mois. Nous allons devoir la retravailler encore. Il s'agit de lui spécifier une hauteur variable, ajustée aux données effectivement contenues dans le tableau.

Mais ce n'est pas tout. Le deuxième argument désigne les noms des vendeurs de la première colonne, soit des informations placées sur l'axe des abscisses du graphique.

Ventes!$B$6:$B$13

Ainsi définie, cette plage fixe ne peut en aucun cas considérer de nouveaux potentiels commerciaux. Nous allons donc devoir remplacer cet argument par une plage de cellules variable que nous devons construire.

Plage variable pour les abscisses
Pour devenir dynamique, la plage des noms doit évoluer en hauteur au gré des commerciaux ajoutés à la suite du tableau. Pour cela, nous devons construire cette plage de cellules de toutes pièces.

• Cliquer sur une cellule de la feuille pour désélectionner la série du graphique,
• 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 la boîte de dialogue du gestionnaire, cliquer sur le bouton Nouveau,
• Dans la boîte de dialogue qui suit, nommer cette plage : Axe,
• Dans la zone Fait référence à, supprimer la syntaxe présente,
• 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 le premier des commerciaux en cliquant sur sa cellule B6, ce qui donne : Ventes!$B$6,

Nous définissons ainsi le point de départ de la plage de cellules. Elle doit désormais s'ajuster en hauteur.

• Inscrire trois points-virgules successifs, soit : ;;;,

De cette manière, nous ignorons les deux arguments suivants. Ils concernent les décalages en ligne et en colonne que nous ne souhaitons pas observer. De fait, nous nous retrouvons dans le quatrième argument de la fonction Decaler. Il concerne la hauteur à ajuster. Pour la calculer, il suffit de compter le nombre de commerciaux. C'est la fonction Excel NbVal qui permet de comptabiliser les cellules non vides sur une plage.

• Inscrire la fonction de dénombrement suivie d'une parenthèse, soit : NbVal(,
• Taper le nom de la feuille suivi d'un point d'exclamation, soit : Ventes!,

C'est ainsi dans la construction d'une plage dynamique. Toute cellule ou toute plage doit être préfixée du nom de sa feuille pour la localiser sans ambiguïté.

• Inscrire alors les coordonnées de la plage suivante : B6:B100,

De cette manière, nous prévoyons large pour intégrer suffisamment de nouveaux potentiels commerciaux.

• Sélectionner ces coordonnées sans le nom de la feuille,
• Puis, enfoncer la touche F4 du clavier pour figer la plage, ce qui donne : $B$6:$B$100,
• Cliquer à la fin de la syntaxe pour y replacer le point d'insertion,
• Fermer la parenthèse de la fonction NbVal,
• Puis, fermer la parenthèse de la fonction Decaler,
• Cliquer alors sur le bouton Ok pour valider la syntaxe,
• De retour sur le gestionnaire de noms, cliquer sur le bouton Fermer,

Nous sommes ainsi de retour sur la feuille Excel. La syntaxe de la plage de cellules que nous venons de construire est donc la suivante :

=DECALER(Ventes!$B$6;;; NBVAL(Ventes!$B$6:$B$100))

Comme nous le disions, ce nom doit désormais être exploité pour la construction dynamique de l'axe des abscisses du graphique.

• Cliquer sur l'une des barres du graphique pour sélectionner toute la série,
• Dans la barre de formule, modifier le deuxième argument de la fonction comme suit :

=SERIE(Ventes!$K$3; 'insertion-automatique-graphique.xlsx'!Axe; 'insertion-automatique-graphique.xlsx'!Periode; 1)

Attention, le nom de la plage doit impérativement être préfixé du nom du classeur. Celui-ci doit être mentionné entre guillemets et doit être suivi d'un point d'exclamation pour annoncer la plage.

• Valider la modification par la touche Entrée du clavier,

Avant d'ajouter un nouveau commercial, nous devons retravailler la plage dynamique désignant les ventes.

Plage variable pour les données du graphique
Le nom de cette plage est période. Elle est déjà intégrée dans la syntaxe de la source de données du graphique. En effet, elle permet déjà d'ajuster la colonne à représenter en fonction du mois choisi. Mais pour l'instant, sa hauteur est fixe. Elle est limitée aux huit commerciaux que compte actuellement ce tableau des ventes.

• Dans la section Noms définis du ruban Formule, cliquer sur le bouton Gestionnaire de noms,
• Dans la boîte de dialogue du gestionnaire, sélectionner la plage nommée Periode,

Nous pouvons ainsi consulter son actuelle syntaxe dans la zone Fait référence à :

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

C'est le dernier argument de la fonction Decaler qui nous intéresse. Il concerne la hauteur de cette plage pour désigner les ventes. Elle aussi doit s'adapter, au même titre que celles des noms des commerciaux pour l'axe des abscisses. Il suffit d'exploiter la même technique avec la fonction NbVal.

• Remplacer le dernier argument de la syntaxe comme suit :

=DECALER(Ventes!$C$6;; EQUIV(Ventes!$K$3; Ventes!$C$5:$H$5; 0)-1; NbVal(Ventes!$B$6:$B$100))

Il ne faut surtout pas oublier de figer les bornes de la plage de cellules

• Cliquer sur le bouton à la coche verte à gauche de l'expression pour la valider,
• Puis, cliquer sur le bouton Fermer du gestionnaire pour revenir sur la feuille Excel,

Maintenant, si vous ajoutez un nouveau commercial à la suite du tableau des ventes, celui-ci est automatiquement et dynamiquement intégré dans le graphique Excel. Et bien entendu, si vous changez de mois avec la liste déroulante, la représentation s'adapte toujours mais en intégrant ce nouvel enregistrement.

Repérer les données dans le tableau
Pour une application encore plus pertinente, nous proposons de surligner dynamiquement la colonne du mois représenté dans le graphique. Cet indicateur visuel viendra parfaitement recouper les résultats pour une interprétation encore plus évidente. Cette règle de mise en forme conditionnelle doit elle aussi être en mesure d'intégrer les nouveaux potentiels commerciaux ajoutés.

• Dans la zone Nom, en haut à gauche de la feuille Excel, taper les coordonnées : C6:H100,
• Puis, valider avec la touche Entrée du clavier,

Ainsi, nous désignons les ventes à mettre en valeur, tout en sélectionnant une hauteur suffisamment importante afin de prévoir les nouveaux enregistrements.

• 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, construire la syntaxe suivante pour la règle : =ET(C6<>''; C$5=$K$3),

Nous recoupons donc deux conditions. Avec la première, en partant de la cellule C6 non figée, nous cherchons à savoir si la vente est bien renseignée. Souvenez-vous, l'analyse d'une mise en forme conditionnelle est chronologique. Donc, toutes les cellules de la plage sélectionnée vont être passées en revue. Avec le deuxième critère, nous cherchons à établir la correspondance entre la colonne du mois dans le tableau et le mois sélectionné avec la liste déroulante. Le mois du tableau est naturellement figé en ligne et libéré en colonne. Ainsi, lors de la progression de l'analyse, tous les mois vont être passés en revue sur cette ligne fixée. Le mois de la liste déroulante est conservé totalement figé. En effet, il est la référence de comparaison à ne pas déplacer.

Lorsque ces contraintes sont vérifiées, la colonne complète doit être surlignée.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un orange semblable à celui du graphique,
• Valider ces attributs par le bouton Ok,
• Sur la première boîte de dialogue, valider la règle par le bouton Ok,

De retour sur la feuille, vous notez que les ventes sont strictement mises en valeur pour le mois désigné. Si vous changez de mois, la mise en forme se déplace pour repérer les ventes représentées. Et si vous ajoutez un nouveau commercial à la suite du tableau, en même temps qu'il est automatiquement intégré dans la représentation graphique, il est englobé dans la mise en valeur dynamique.