Création de graphiques dynamiques à l’aide de la fonction OFFSET et de plages nommées dans Excel 2007 et 2010

Robbie écrit principalement sur Skyrim, mais met également occasionnellement en lumière les chances des applications Microsoft telles qu’Excel et Outlook.

Bonjour et bienvenue dans mon article sur l’utilisation de DÉCALAGE fonction. Dans l’article d’aujourd’hui, je vais étudier comment créer des graphiques dynamiques dans Excel en utilisant DÉCALAGE aussi bien que Noms définis pour créer des plages nommées.

Cela vous permettra de créer des graphiques à partir de vos données qui se mettront à jour automatiquement lorsque vous ajouterez ou supprimerez des données. Avec un graphique statique, lorsque vous ajoutez des données, vous devez mettre à jour manuellement votre graphique afin qu’il affiche les nouvelles données. Un graphique dynamique le fait automatiquement pour vous.

Dans mon précédent article sur le DÉCALAGE fonction, j’ai étudié comment l’utiliser avec le COUNTA fonction pour créer des plages de données dynamiques afin que vous puissiez, par exemple, l’utiliser avec SOMME pour additionner des factures, des données de ventes ou de trafic web. Si vous supprimez ou ajoutez des données à la plage dynamique, votre formule sera automatiquement recalculée pour refléter les nouvelles données.

Création de plages nommées dans Excel 2007 et 2010

La première étape consiste à définir des plages nommées à partir de nos données.

Les plages nommées seront ensuite utilisées pour définir les données que nous utiliserons pour créer les graphiques dynamiques.

Nous devons créer deux plages nommées, une pour les données elles-mêmes et une autre pour les étiquettes du graphique. Tout d’abord, nous allons créer la plage des données qui apparaîtront dans notre graphique.

• Sélectionnez le Définir le nom bouton sur le Formules onglet dans le Noms définis groupe.
• définir un Nom (sans espaces) vous pouvez voir. J’ai utilisé DailyVisits.

• dans le Fait référence à saisir la formule OFFSET suivante :

=OFFSET(DynamicCharts!$B$2,1,0,COUNTA(DynamicCharts!$B:$B),1)

La formule ci-dessus crée une plage dynamique à partir de la cellule B2. Il décompte le même nombre de lignes que le nombre de lignes contenant des données par COUNTA.

Donc si COUNTA trouve 23 cellules contenant des données, la plage commencera à B2 et se terminera à B25.

COUNTA est dynamique. Si des données sont ajoutées ou supprimées, elles sont automatiquement mises à jour, ce qui modifie la plage. la DÉCALAGE fonction est expliquée plus en détail dans mon premier article sur DÉCALAGE (le lien se trouve dans l’introduction).

Faites défiler pour continuer

Noter: Lorsque vous apportez des modifications à la Fait référence à boîte de dialogue, n’utilisez pas les touches fléchées pour vous déplacer dans cette boîte de dialogue ou cela ajoutera des références de cellule à la formule. Déplacez-vous en cliquant sur la souris à la place.

Noter: pour modifier vos plages nommées une fois qu’elles sont créées, utilisez le Gestionnaire de noms à gauche du bouton Définir le nom que vous avez utilisé pour le créer.

• Ensuite, nous devons répéter le processus ci-dessus, cette fois en utilisant Label pour le Nom et la formule ci-dessous dans le Fait référence à boîte pour créer notre plage dynamique pour les étiquettes de graphique.

=OFFSET(DynamicCharts!$A$2,1,0,COUNTA(DynamicCharts!A$3:$A$33),1

Création d’un graphique dynamique dans Excel 2007 et 2010

Maintenant que nous avons les plages nommées, nous devons créer le graphique :

• Sélectionnez le colonne bouton dans le graphiques groupe sur le Insérer languette

Cela créera un graphique complètement vide.

• Avec le graphique sélectionné, cliquez sur le Sélectionnez des dates bouton dans le Rendez-vous groupe sur le motif onglet sous Outils graphiques.
• Modifiez la plage de données du graphique afin que le nom que vous avez donné à votre plage nommée ci-dessus pour les données (j’ai utilisé DailyVisits) soit entré après le nom de l’onglet, comme indiqué ci-dessous.

Donc dans mon exemple, j’ai changé :

=Graphiques dynamiques !$F$7

à

=DynamicCharts!DailyVisits

Le graphique ressemblera maintenant à ceci.

Pour ajouter nos libellés dynamiques :

• Sélectionnez à nouveau le graphique.
• Clique sur le Sélectionnez des dates bouton dans le Rendez-vous groupe sur le motif onglet sous Outils graphiques.
• En dessous de Étiquettes d’axe horizontal (catégorie)Cliquez sur Éditer.
• dans le Etiquette de l’axe gamme, entrez le nom de votre gamme nommée pour vos étiquettes (j’ai choisi Labels pour la mienne).

• Cliquez sur D’ACCORD deux fois et Excel aura maintenant ajouté vos étiquettes au graphique.

Enfin, nous devons nous assurer que le graphique utilise les plages nommées. Pour faire ça:

• Assurez-vous que le graphique est à nouveau sélectionné.
• Cliquez sur la série de données.
• Dans la barre de formule, vous verrez une formule commençant par =SERIES.

La formule de mon tableau est :

=SERIES(,’Analytics Overview.xlsm’!Labels,DynamicCharts!$B$3:$B$39.1)

Avant de modifier cette formule, examinons sa syntaxe.

La formule est composée de quatre parties.

• Le titre du graphique (ceci est facultatif)
• L’emplacement des étiquettes pour l’axe horizontal
• L’emplacement des données pour la série
• L’ordre dans lequel les séries apparaissent sur le graphique (1 pour le premier, 2 pour le second, etc.)

Il y a trois parties de la formule que vous devez modifier.

• La première consiste à ajouter le titre du graphique si vous le souhaitez à partir de l’en-tête de données (si vous cliquez à gauche de la première virgule dans la formule, puis sélectionnez la cellule qui contient votre en-tête de données (qui est B2 dans mon exemple) Excel va ajoutez-le automatiquement pour vous) afin que la première partie de la formule devienne :

=SERIES(DynamicCharts!$B$2,

• La deuxième partie est la partie qui contient les étiquettes dynamiques. Ceux-ci seront mis à jour automatiquement si les étiquettes de votre feuille de calcul changent.

‘Analytics Overview.xlsm’!Étiquettes,

• La troisième partie consiste à s’assurer que le graphique est mis à jour lorsque vous ajoutez ou supprimez des données de manière dynamique. Pour faire ça:

Remplacez la dernière partie de la formule $B$3:$B$39 par votre plage de données nommée pour les données (la mienne est DailyVisits).

Graphiques dynamiques !$B$3 :$B$39,

La formule devient :

=SERIES(DynamicCharts!$B$2,’Analytics Overview.xlsm’!Labels,DynamicCharts!DailyVisits,1)

Maintenant que le tableau est terminé, une dernière chose à noter lors de la saisie des données. Si vous entrez des données sans les étiquettes correspondantes dans la ligne adjacente, Excel ne les ajoutera pas à votre graphique. Il n’affichera les nouvelles données qu’une fois les étiquettes ajoutées. Si vous ajoutez un certain nombre d’étiquettes sans données, Excel affichera la première étiquette sans données mais n’ajoutera plus d’étiquettes sans données au graphique.

conclusion

À mesure que nos vies deviennent plus occupées et que la quantité d’informations avec lesquelles nous travaillons augmente, il est toujours très utile, tant du point de vue du gain de temps que de la réduction des erreurs, d’automatiser autant que possible et DÉCALAGE La fonction est en effet très utile pour nous permettre d’automatiser à la fois les formules et les graphiques dans Excel. En particulier pour les feuilles de calcul qui sont souvent mises à jour, l’automatisation de la mise à jour de vos graphiques est en effet très pratique.

la DÉCALAGE La fonction dans Excel vous permet de créer des plages dynamiques et des graphiques dynamiques qui se mettent à jour automatiquement lorsque des données sont ajoutées ou supprimées dans la plage. Dans cet article, nous avons créé des plages nommées et les avons utilisées avec le DÉCALAGE fonction pour créer des graphiques dynamiques.

Merci beaucoup d’avoir lu et j’espère que vous appréciez maintenant vos graphiques mis à jour dynamiquement et que vous passez votre nouveau temps libre à quelque chose de plus agréable. Veuillez laisser vos commentaires ci-dessous.

Cet article est exact et fidèle au meilleur de la connaissance de l’auteur. Le contenu est uniquement à des fins d’information ou de divertissement et ne remplace pas un conseil personnel ou un conseil professionnel en matière commerciale, financière, juridique ou technique.

© 2013 Robbie C Wilson

Douglas le 27 janvier 2018 :

Excellent article, merci pour le partage qui m’aide beaucoup !

Clinton le 01 septembre 2017 :

Merci beaucoup pour cet article. Cela m’a vraiment aidé.