TOP

Diagramme interactif

Description

La visualisation de haute qualité d'une grande quantité d'informations est presque toujours une tâche non triviale. Par conséquent, l'affichage de toutes les données conduit souvent à une surcharge du diagramme, à sa confusion et, par conséquent, à une perception et des conclusions incorrectes.


Voici par exemple des données sur les taux de change sur plusieurs mois :

Comme vous pouvez le constater, ce n’est pas une bonne idée de tracer l’ensemble du tableau. Une belle solution dans une situation similaire peut être la création d'un diagramme interactif que l'utilisateur peut adapter à lui-même et à la situation. À savoir:

Cela peut ressembler à ceci :

Aimez-vous? Ensuite nous sommes allés ...

Étape 1. Nous créons un tableau supplémentaire pour le diagramme

Dans la plupart des cas, une technique simple mais puissante est utilisée pour mettre en œuvre l'interactivité du diagramme - le diagramme n'est pas construit selon l'original, mais selon un tableau séparé spécialement créé avec des formules, qui affiche uniquement les données nécessaires. Dans notre cas, les données de sortie seront transférées vers cette table supplémentaire uniquement pour les devises que l'utilisateur a sélectionnées à l'aide des cases à cocher :

Dans Excel 2007/2010, vous pouvez appliquer une commande aux plages créées Formater sous forme de tableau (Format as Table) depuis l'onglet Principal (Home) :

Cela nous apportera les avantages suivants :

Étape 2. Ajoutez des cases à cocher pour les devises

Dans Excel 2007/2010, vous devez afficher l'onglet correspondant Développeur (Developer) , et dans Excel 2003 et versions antérieures - la barre d'outils Formes (Forms) . Pour ça:

Ce qui est apparu dans les barres d'outils ou les onglets Développeur (Developer) dans la liste déroulante Insérer (Insert) choisir un outil Drapeau (Checkbox) et dessinez deux coches pour activer/désactiver chacune des devises :

Vous pouvez modifier le texte des drapeaux en cliquant dessus avec le bouton droit de la souris et en sélectionnant la commande Changer le texte (Edit text) .

Lions maintenant nos drapeaux à n'importe quelle cellule pour déterminer si le drapeau est activé ou non (dans notre exemple, ce sont les deux cellules jaunes en haut du tableau supplémentaire). Pour ce faire, faites un clic droit sur chaque drapeau ajouté tour à tour et sélectionnez une commande Format d'objet (Format Control) , puis défini dans la fenêtre Communication avec la cellule (Cell link) .

Notre objectif est que chaque case à cocher soit liée à la cellule jaune correspondante au-dessus de la colonne des devises. Lorsque la case est cochée dans la cellule associée, elle sera affichée VÉRITÉ (TRUE) , à l'arrêt - MENSONGE (FALSE) . Cela permettra, à l'avenir, de vérifier les cellules connectées à l'aide de formules et d'afficher dans un tableau supplémentaire ou la valeur du taux de change du tableau d'origine pour construire un graphique, ou #N / A (#N/A) , de sorte que le graphique ne soit pas construit.

Étape 3. Nous traduisons les données dans un tableau supplémentaire

Remplissons maintenant le tableau secondaire avec une formule qui traduira les données de sortie du tableau principal si l'indicateur de devise correspondant est activé et que la cellule associée contient le mot VÉRITÉ (TRUE) :

Notez que lorsque vous utilisez la commande Formater sous forme de tableau (Format as Table) dans un premier temps, la formule doit utiliser le nom de la table et le nom de la colonne. Dans le cas d’une gamme régulière, la formule semblera plus familière :

=ЕСЛИ(F$1; B4; #Н/Д)

Notez l'ancrage partiel de la référence à la cellule jaune (F$1), elle doit donc se déplacer vers la droite, mais pas vers le bas, lors de la copie de la formule dans toute la plage.

Désormais, lors de la vérification des drapeaux, notre table supplémentaire est remplie soit avec les données de la table d'origine, soit avec une erreur créée artificiellement # N/A, qui ne donne pas de ligne sur le graphique.

Étape 4. Nous créons des barres de défilement pour l'axe du temps et la mise à l'échelle

Ajoutons maintenant des barres de défilement à la feuille Excel, à l'aide desquelles l'utilisateur peut facilement déplacer le graphique le long de l'axe du temps et modifier l'échelle de son augmentation.

Barre de défilement (Scroll bar) on le prend au même endroit que les drapeaux - sur la barre d'outils Formes (Forms) ou sur un onglet Développeur (Developer) :

Nous dessinons deux bandes une à une sur la feuille à n'importe quel endroit approprié - pour un décalage dans le temps et dans l'échelle :

Chaque barre de défilement doit être associée à sa propre cellule (cellules bleues et vertes dans l'image), où sera affichée la valeur numérique de la position du curseur. Nous l'utiliserons ensuite pour déterminer l'échelle et le décalage. Pour cela, faites un clic droit sur la bande dessinée et sélectionnez une commande dans le menu contextuel Format d'objet (Format control) . Dans la boîte de dialogue, vous pouvez définir la cellule associée et le minimum-maximum, dans toutes les limites où le curseur se déplacera :

Ainsi, après avoir fait tout ce qui précède, vous devriez avoir deux barres de défilement, lorsque vous déplacez les curseurs, le long desquelles les valeurs dans les cellules associées doivent changer dans la plage de 1 à 307.

Étape 5. Créez une plage nommée dynamique

Pour afficher les données sur le graphique uniquement pour un certain intervalle de temps, nous allons créer une plage nommée qui fera référence uniquement aux cellules requises dans le tableau supplémentaire. Cette gamme sera caractérisée par deux paramètres :

Nous utiliserons plus tard cette plage nommée comme données brutes pour créer le graphique.

Pour créer une telle plage, nous utiliserons la fonction СМЕЩ (OFFSET) de la catégorie Références et tableaux (Lookup and Reference) - cette fonction est capable de créer une référence à une plage d'une taille donnée à un endroit donné de la feuille et possède les arguments suivants :

Une cellule de départ est prise comme point de départ, puis un décalage par rapport à elle d'un nombre donné de lignes vers le bas et de colonnes vers la droite est défini. Les deux derniers arguments de cette fonction sont la hauteur et la largeur de la plage dont nous avons besoin. Ainsi, par exemple, si nous souhaitons référencer une plage de données avec des tarifs sur 5 jours commençant le 4 janvier, nous pourrions utiliser notre fonction СМЕЩ (OFFSET) avec les arguments suivants :

=СМЕЩ(A3;4;1;5;2)

L'astuce est que les constantes de cette formule peuvent être remplacées par des références à des cellules à contenu variable - dans notre cas, les cellules bleues et vertes. Vous pouvez le faire en créant une plage nommée dynamique avec une fonction СМЕЩ (OFFSET) . Pour ça:

Cliquez sur le bouton pour créer une nouvelle plage nommée Créer (Create) et entrez le nom de la plage et les références de cellules dans la fenêtre.

Tout d'abord, créons deux plages nommées statiques simples avec des noms, par exemple Shift et Zoom , qui fera référence respectivement aux cellules bleues et vertes :

       

Maintenant, c'est un peu plus compliqué : créons une plage avec un nom Euros , qui sera référencé par la fonction СМЕЩ (OFFSET) sur les données des taux de change de l'euro pour la période sélectionnée, en utilisant les plages créées précédemment Changement et Zoom et une cellule E3 comme point de référence :

Notez que le nom de la feuille actuelle est utilisé avant le nom de la plage - cela réduit la portée de la plage nommée, c'est-à-dire qu'elle la rend disponible dans les limites de la feuille actuelle, et non dans l'ensemble du classeur. Cela est nécessaire pour que nous puissions construire un diagramme à l'avenir. Dans les versions plus récentes de Excel, vous pouvez utiliser la liste déroulante pour créer un nom de feuille locale Région .

De même, une plage nommée est créée Dollars pour les données sur le taux de change du dollar :

Et la gamme complète le tableau Labels , qui indique les signatures sur l'axe X, c'est-à-dire les dates du segment sélectionné :

L’image générale devrait être approximativement la suivante :

Étape 6. Nous construisons un diagramme

Sélectionnons plusieurs lignes dans la partie supérieure de la table auxiliaire, par exemple la plage E3:G10 et construisons un diagramme de type basé sur celle-ci. Calendrier (Line) . Pour ce faire, dans Excel 2007/2010, vous devez vous rendre dans l'onglet Insérer (Insert) et dans le groupe Graphique (Chart) sélectionner un type Calendrier (Line) , et dans les anciennes versions, sélectionnez dans le menu Encart - diagramme (Insert - Chart) . Si vous mettez en surbrillance une des lignes du schéma créé, la fonction sera visible dans la barre de formule РЯД (SERIES) , qui sert une série sélectionnée de données :

Cette fonction définit les plages de données et les étiquettes pour la série de graphiques sélectionnée. Notre tâche est de remplacer les plages statiques de ses arguments par les plages dynamiques que nous avons créées précédemment. Cela peut être fait directement dans la barre de formule en modifiant :

=РЯД(Лист1!$F$3; Лист1!$E$4:$E$10 ; Лист1!$F$4:$F$10 ; 1)

sur:

=РЯД(Лист1!$F$3; Лист1! Labels ; Лист1! Euros ; 1)

Après avoir effectué cette procédure séquentiellement pour les séries de données du dollar et de l'euro, nous obtiendrons ce que nous voulions - le graphique sera construit selon des plages dynamiques Dollars et Euros , et les signatures sur l'axe X seront extraites de la même plage dynamique Labels . Changer la position des curseurs modifiera les plages et, par conséquent, le graphique. Lorsque vous activez et désactivez les drapeaux, seules les devises dont nous avons besoin sont affichées.

Ainsi, nous disposons d’un graphique entièrement interactif où nous pouvons afficher exactement les données dont nous avons besoin pour l’analyse.

Articles sur le sujet :