Comment créer une plage de graphique dynamique dans Excel

Lorsque vous créez un graphique dans Excel et que les données source changent, vous devez mettre à jour la source de données du graphique pour vous assurer qu'elle reflète les nouvelles données.

Si vous travaillez avec des graphiques fréquemment mis à jour, il est préférable de créer une plage de graphiques dynamique.

Qu'est-ce qu'une plage de graphique dynamique ?

Une plage de graphique dynamique est une plage de données qui se met à jour automatiquement lorsque vous modifiez la source de données.

Cette plage dynamique est ensuite utilisée comme données source dans un graphique. Au fur et à mesure que les données changent, la plage dynamique se met à jour instantanément, ce qui entraîne une mise à jour du graphique.

Vous trouverez ci-dessous un exemple de graphique utilisant une plage de graphique dynamique.

Notez que le graphique se met à jour avec les nouveaux points de données pour mai et juin dès que les données sont entrées.

Comment créer une plage de graphique dynamique dans Excel ?

Il existe deux manières de créer une plage de graphique dynamique dans Excel :

• Utilisation du tableau Excel
• Utiliser des formules

Dans la plupart des cas, l'utilisation d'Excel Table est le meilleur moyen de créer des plages dynamiques dans Excel.

Voyons comment chacune de ces méthodes fonctionne.

Cliquez ici pour télécharger le fichier exemple.

Utilisation du tableau Excel

L'utilisation d'Excel Table est le meilleur moyen de créer des plages dynamiques, car elle se met à jour automatiquement lorsqu'un nouveau point de données y est ajouté.

La fonction Tableau Excel a été introduite dans la version Excel 2007 de Windows et si vous possédez des versions antérieures, vous ne pourrez pas l'utiliser (voir la section suivante sur la création d'une plage de graphique dynamique à l'aide de formules).

Conseil de pro : Pour convertir une plage de cellules en un tableau Excel, sélectionnez les cellules et utilisez le raccourci clavier - Ctrl + T (maintenez la touche Ctrl enfoncée et appuyez sur la touche T).

Dans l'exemple ci-dessous, vous pouvez voir que dès que j'ajoute de nouvelles données, le tableau Excel se développe pour inclure ces données dans le tableau (notez que la bordure et la mise en forme se développent pour les inclure dans le tableau).

Maintenant, nous devons utiliser ce tableau Excel lors de la création des graphiques.

Voici les étapes exactes pour créer un graphique en courbes dynamique à l'aide du tableau Excel :

• Sélectionnez l'ensemble du tableau Excel.
• Allez dans l'onglet Insertion.
• Dans le groupe Graphiques, sélectionnez le graphique « Ligne avec marqueurs ».

C'est ça!

Les étapes ci-dessus inséreraient un graphique linéaire qui serait automatiquement mis à jour lorsque vous ajouteriez plus de données au tableau Excel.

Notez que même si l'ajout de nouvelles données met automatiquement à jour le graphique, la suppression de données ne supprimerait pas complètement les points de données. Par exemple, si vous supprimez 2 points de données, le graphique affichera un espace vide sur la droite. Pour corriger cela, faites glisser la marque bleue en bas à droite du tableau Excel pour supprimer les points de données supprimés du tableau (comme indiqué ci-dessous).

Bien que j'aie pris l'exemple d'un graphique en courbes, vous pouvez également créer d'autres types de graphiques tels que des graphiques à colonnes/à barres en utilisant cette technique.

Utiliser des formules Excel

Comme je l'ai mentionné, l'utilisation d'un tableau Excel est le meilleur moyen de créer des plages de graphiques dynamiques.

Cependant, si vous ne pouvez pas utiliser le tableau Excel pour une raison quelconque (éventuellement si vous utilisez Excel 2003), il existe un autre moyen (légèrement compliqué) de créer des plages de graphiques dynamiques à l'aide de formules Excel et de plages nommées.

Supposons que vous ayez l'ensemble de données comme indiqué ci-dessous :

Pour créer une plage de graphique dynamique à partir de ces données, nous devons :

1. Créez deux plages nommées dynamiques à l'aide de la formule OFFSET (une pour chacune des colonnes "Valeurs" et "Mois"). L'ajout/suppression d'un point de données mettrait automatiquement à jour ces plages nommées.
2. Insérez un graphique qui utilise les plages nommées comme source de données.

Permettez-moi d'expliquer chaque étape en détail maintenant.

Étape 1 - Création de plages nommées dynamiques

Voici les étapes pour créer des plages nommées dynamiques :

• Allez dans l'onglet "Formules".
• Cliquez sur « Gestionnaire de noms ».
• Dans la boîte de dialogue Gestionnaire de noms, spécifiez le nom comme ChartValues et entrez la formule suivante dans Fait référence à la pièce : =OFFSET(Formula!$B$2,,,COUNTIF(Formula!$B$2:$B$100,""))
• Cliquez sur OK.
• Dans la boîte de dialogue Gestionnaire de noms, cliquez sur Nouveau.
• Dans la boîte de dialogue Gestionnaire de noms, spécifiez le nom comme GraphiqueMois et entrez la formule suivante dans Fait référence à la pièce : =OFFSET(Formula!$A$2,,,COUNTIF(Formula!$A$2:$A$100,""))
• Cliquez sur OK.
• Cliquez sur Fermer.

Les étapes ci-dessus ont créé deux plages nommées dans le classeur - ChartValue et ChartMonth (celles-ci font respectivement référence aux valeurs et à la plage de mois dans l'ensemble de données).

Si vous mettez à jour la colonne de valeur en ajoutant un point de données supplémentaire, la plage nommée ChartValue sera désormais automatiquement mise à jour pour afficher le point de données supplémentaire qu'elle contient.

La magie se fait ici par la fonction OFFSET.

Dans la formule de plage nommée « ChartValue », nous avons spécifié B2 comme point de référence. La formule OFFSET commence là et s'étend pour couvrir toutes les cellules remplies de la colonne.

La même logique fonctionne également dans la formule de plage nommée ChartMonth.

Étape 2 - Créez un graphique à l'aide de ces plages nommées

Il ne vous reste plus qu'à insérer un graphique qui utilisera les plages nommées comme source de données.

Voici les étapes à suivre pour insérer un graphique et utiliser des plages de graphique dynamique :

• Allez dans l'onglet Insertion.
• Cliquez sur « Insérer une ligne ou un graphique en aires » et insérez le graphique « Ligne avec marqueurs ». Cela insérera le graphique dans la feuille de calcul.
• Une fois le graphique sélectionné, accédez à l'onglet Conception.
• Cliquez sur Sélectionner les données.
• Dans la boîte de dialogue « Sélectionner la source de données », cliquez sur le bouton Ajouter dans « Entrées de légende (série) ».
• Dans le champ Valeur de la série, entrez =Formula!ChartValues ​​(notez que vous devez spécifier le nom de la feuille de calcul avant la plage nommée pour que cela fonctionne).
• Cliquez sur OK.
• Cliquez sur le bouton Modifier dans les « Étiquettes d'axe horizontal (catégorie) ».
• Dans la boîte de dialogue ‘Axis Labels’, entrez =Formula!ChartMonths
• Cliquez sur OK.

C'est ça! Votre graphique utilise désormais une plage dynamique et sera mis à jour lorsque vous ajouterez/supprimez des points de données dans le graphique.

Quelques points importants à savoir lors de l'utilisation de plages nommées avec des graphiques :

• Il ne doit pas y avoir de cellules vides dans les données du graphique. S'il y a un blanc, la plage nommée ne ferait pas référence au bon ensemble de données (car le nombre total conduirait à faire référence à moins de cellules).
• Vous devez suivre la convention de dénomination lorsque vous utilisez le nom de la feuille dans la source du graphique. Par exemple, si le nom de la feuille est un mot unique, tel que Formula, vous pouvez utiliser =Formula!ChartValue. Mais s'il y a plus d'un mot, comme Formula Chart, alors vous devez utiliser ='Formula Chart'!ChartValue.