Les graphiques dynamiques utilisant les fonctions OFFSET et les noms de plage d’Excel se redimensionnent lorsque des données sont ajoutées ou supprimées. Contrairement à l’utilisation de tableaux pour les graphiques dynamiques, cette méthode est rétrocompatible avec les premières versions d’Excel.
Utilisez les fonctions OFFSET et COUNTA d’Excel pour créer des noms de plages dynamiques qui s’ajustent automatiquement lorsque des données sont ajoutées ou supprimées.
Cliquez pour télécharger le fichier d’exemple du tutoriel sur le redimensionnement dynamique des graphiques à l’aide de OFFSET et des noms de plages. L’exemple comprend des données horizontales et verticales.
La méthode OFFSET et nom de plage demande plus de travail que les deux clics utilisés pour un tableau, mais elle vous donne le contrôle sur tout ce qui concerne les données du graphique comme le montre mon livre « Balanced Scorecards and Operational Dashboards with Microsoft Excel ». Ce site, CriticalToSuccess.com, contient des exemples d’autres façons d’utiliser OFFSET, par exemple pour créer un graphique défilant, contrôler les graphiques avec des menus déroulants, etc. Faites une recherche sur CriticalToSuccess.com en utilisant le terme « OFFSET » ou « plage dynamique » pour voir des exemples.
Création d’un graphique statique
Débutez votre graphique dynamique en créant d’abord un graphique statique comme suit,
1. Cliquez à l’intérieur de la plage B7:D12 pour qu’Excel sache où se trouvent les données du graphique.
2. Sur l’onglet Insertion, dans le groupe Graphiques, sélectionnez un graphique simple comme un graphique à colonnes, à barres ou linéaire.
Déplacez ce graphique pour avoir la place d’ajouter des données sous la plage de données.
Création de noms de plage dynamiques avec OFFSET
Les différentes versions d’Excel ont différentes méthodes de création et d’édition des formules de nom de plage. Les instructions suivantes concernent Excel 2007, 2010 et 2013.
1. Dans l’onglet Formules, dans le groupe Nom défini, cliquez sur Définir le nom. La boîte Définir le nom apparaît.
Créer des noms de plage dans la boîte Définir le nom.
2. Dans la zone de texte Nom, entrez rngVDate. Les conventions d’appellation sont,
rng Nom de plage
V Orientation verticale des données en B7:B16
Date Le type de données dans la plage
3. Écrivez un commentaire dans la zone de texte de commentaire qui vous aidera ou aidera d’autres personnes à savoir à quoi sert ce nom de plage.
4. Dans la zone Réfère à, tapez la formule OFFSET qui calcule la plage de données Date.
=OFFSET(wrksht!$B$7,0,0,COUNTA(wrksht!$B$7:$B$16),1)
5. Cliquez sur Ok.
Avec cette formule OFFSET, vous pouvez entrer des dates supplémentaires n’importe où dans la plage B7:B16 tant que les nouvelles dates sont au bas de l’ancienne liste de dates sans cellules vides entre les dates.
Maintenant, répétez le même processus pour créer des noms de plage pour les données Ventes et Unités.
Les noms et les formules que vous allez ajouter sont,
Nom de la plage de données se réfère à
Ventes rngVSales =OFFSET(wrksht!$C$7,0,0,COUNTA(wrksht !$C$7:$C$16),1)
Unités rngVUnits =OFFSET(wrksht!$D$7,0,0,COUNTA(wrksht!$D$7:$D$16),1)
Cliquez ici pour apprendre comment OFFSET calcule la taille du nom de la plage dynamique. Cet articles vous donne également des conseils pour vérifier les noms de plages dynamiques.
Cliquez ici pour apprendre l’erreur que vous ne voulez jamais faire avec OFFSET. Faites-vous cette erreur ?
Cliquez pour télécharger le fichier d’exemple du tutoriel sur le redimensionnement dynamique des graphiques en utilisant OFFSET et les noms de plage. L’exemple inclut des données horizontales et verticales.
Insertion d’un nom de plage dans la formule de date du graphique
Vous pouvez maintenant rendre vos graphiques dynamiques en insérant les noms de plage que vous avez créés dans les formules que les graphiques utilisent pour référencer les données. Ne vous inquiétez pas. C’est très simple. En fait, apprendre à le faire vous donne des capacités mystiques et le pouvoir de plier n’importe quel graphique à votre volonté. (Cela vous permet également de réparer les graphiques qui se dérèglent.)
Vous avez créé ci-dessus trois noms de plages dynamiques qui font référence aux données à utiliser par le graphique,
rngVDate
rngVSales
rngVUnits
Les éléments suivants remplacent les références de cellules utilisées par le graphique par les noms de plages dynamiques que vous avez créés,
1. Sélectionnez le graphique en cliquant sur son bord.
2. Sur l’onglet Conception des outils graphiques, dans le groupe Données, cliquez sur l’outil Sélectionner les données pour afficher la boîte de dialogue Sélectionner la source de données.
La boîte de dialogue Sélectionner la source de données vous donne un contrôle magique sur les données qu’un graphique utilise.
3. Sous les étiquettes d’axe horizontal (catégorie), cliquez sur Modifier pour afficher la boîte d’étiquettes d’axe. Il est difficile de voir la formule dans cette boîte de dialogue, alors cliquez sur l’icône du sélecteur à droite de la référence de la cellule. Cela affiche une boîte Étiquettes d’axe plus large dans laquelle vous pouvez référencer la formule. SOYEZ PRUDENT avant d’éditer!
La boîte Axis Labels montre la référence pour l’axe horizontal.
4. SOYEZ PRUDENT ! Avant d’essayer de modifier la formule, cliquez à l’intérieur de la formule et appuyez sur la touche d’édition, F2, afin de pouvoir modifier la formule. Appuyez sur la touche Fin pour aller à la fin de la formule. Remplacez les références de cellule après le signe ! par le nom de la plage, comme le montre cette image.
Votre formule devrait maintenant ressembler à,
=’Redimensionnement dynamique des graphiques aux données.xlsx’ !rngVDate
La boîte Axis Labels ressemble à ceci après avoir appuyé sur F2 et modifié la formule pour inclure le nom de la plage de dates.
TIP : si vous faites une erreur en modifiant la formule, appuyez sur Ctrl+Z (Annuler), pour revenir en arrière.
5. Cliquez sur Ok pour revenir à la boîte de dialogue Sélectionner la source de données afin que vous puissiez ajouter les noms des ventes et des unités.
Insertion d’un nom de plage dans la formule des ventes et des unités du graphique
Maintenant, remplacez les formules de l’axe des ventes et des unités par des noms de plage en utilisant presque le même processus.
1. Dans la boîte de dialogue Sélectionner la source de données, cliquez sur Ventes et sur le bouton Modifier pour afficher la boîte de dialogue Modifier la série.
Remplacez la référence de cellule Salesdata par son nom de plage.
2. Cliquez sur l’icône de sélection à droite de la formule Valeur de la série qui fait référence à la série Ventes.
3. Cliquez à l’intérieur de la formule, appuyez sur F2 pour modifier, et changez les références de cellule pour les données de Ventes avec le nom de plage, rngVSales.
Le graphique utilise maintenant le nom de plage dynamique pour référencer les données de Ventes.