Comment automatiser la saisie d’informations dynamiques sur des graphiques Excel ?

Dans ce tutoriel, je vais vous montrer comment réaliser un graphique en barres personnalisé pour représenter les CA TTC journaliers d'un restaurant, tout en mettant en évidence les événements spéciaux du mois de mars.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation des données et préparation du tableau

   1.1. Présentation des données

Pour illustrer ce tutoriel, nous allons pouvoir utiliser les deux tableaux suivants :

• Le tableau « suiviCATTC » contient les CA TTC par jour pour le mois de mars, avec les colonnes « Date » et « CATTC ».

• Le tableau « Évènements », situé sur la feuille « Évènements », qui comporte les colonnes « Date » et « Événement » avec des informations sur les événements spéciaux du mois.

Ces tableaux sont présentés sous la forme de tableau structuré, ce qui permet va nous permettre des formules de manière plus lisible et plus simplement.

Si vos données ne sont pas déjà présentées sous la forme d’un tableau structuré, voici comment les convertir en un tableau structuré :

• Nous commençons par cliquer sur n'importe quelle cellule à l'intérieur de la plage de données que nous souhaitons convertir en tableau structuré.
• Ensuite, depuis le menu « Accueil » du ruban d'Excel, nous cliquons sur « Format en tant que tableau » dans le groupe « Styles » afin de choisir l’un des styles proposés.
• Sur la boîte de dialogue « Créer un tableau » affichée par Excel, nous vérifions que la plage de données sélectionnée est correcte et que la case « Ma plage de données a des en-têtes » est cochée.
• Il ne reste plus qu’à cliquer sur « OK » pour confirmer la création du tableau structuré.

   1.2. Identifier les évènements

Maintenant, nous allons souhaiter faire une nouvelle colonne que nous allons appeler « Évènements » dans le tableau « suiviCATTC ».

Pour cela, nous commençons par saisir les titres de ces colonnes à la suite du tableau pour qu’Excel les intègre automatiquement au tableau structuré.

Dans cette colonne, nous allons souhaiter retrouver les évènements du second tableau, directement sur la ligne qui correspond à chaque journée.

Pour cela, nous saisissons la formule suivante :

  =INDEX(evenements[Événement];EQUIV([@Date];evenements[Date];)) 

Cette formule permet d'extraire la description de l'événement pour chaque date sur les données du tableau « Évènements ».

Pour cela, nous utilisons les fonctions INDEX et EQUIV, ainsi que des références structurées, qui permettent comme nous l’avons déjà vu dans un tutoriel précédent de récupérer une valeur dans une plage de cellule en fonction d’un critère donné.

• La fonction EQUIV recherche la position de la date de la ligne en cours dans le tableau « suiviCATTC » au sein de la colonne « Date » du tableau « Évènements ».
• Si la date est trouvée, EQUIV renvoie la position relative de cette date dans la colonne « Date » du tableau « Évènements ».
• La fonction INDEX utilise cette position pour récupérer la valeur correspondante dans la colonne « Événement » du tableau « Évènements ».
• Si la date n'est pas trouvée, EQUIV renvoie une erreur « #N/A », et la fonction INDEX renvoie également une erreur « #N/A ». Comme nous l’avons vu plus tôt, cela permet de ne pas afficher de texte d'étiquette pour les barres sans événements dans le graphique

Attention toutefois, si plusieurs événements ont lieu le même jour, la fonction EQUIV ne renverra que la position du premier événement trouvé.

L'utilisation de « #N/A » pour les jours sans événement est un choix qui permet d'éviter l'affichage de données non pertinentes dans le graphique.

Pour finir, nous allons masquer ces #N/A, en encapsulant cette formule à l’intérieur de la fonction SIERREUR.

Cette dernière permet en effet de retourner une valeur alternative lorsque le résultat d’un résultat est une erreur.

Pour ne rien afficher dans ce cas, nous retourner une chaîne vide si le résultat de la colonne « Évènement » est effectivement une erreur.

  =SIERREUR(INDEX(evenements[Événement];EQUIV([@Date];evenements[Date];));"")  

2. Création du graphique en barres

Maintenant que nos données sont prêtes, nous allons pouvoir ajouter un graphique en barres.

Il s’agit d’un type de graphique qui représente les données sous forme de barres verticales, permettant une comparaison visuelle facile des valeurs entre différentes catégories ou périodes.

Pour ajouter un graphique à partir des données du tableau, nous commençons par sélectionner les deux premières colonnes de celui-ci, afin de ne pas inclure la colonne « Évènements » dans sa construction :

Puis, nous cliquons sur l'onglet « Insertion » et choisissons le type de graphique « Barre verticale ».

Excel ajoute alors le graphique sur la feuille de calcul, et nous pouvons constater que les données sont automatiquement représentées sous forme de barres verticales pour chaque jour du mois de mars.

Nous pouvons appliquer un style à ce graphique pour modifier rapidement les paramètres de son apparence à partir du menu « Création de graphique » :

3. Personnalisation du graphique

   3.1. Superposition des séries et ajustement de la largeur d'intervalle

Maintenant, nous allons personnaliser l’apparence du graphique pour qu’il corresponde au résultat attendu.

Pour commencer, nous allons superposer les séries à 100% et réduire la largeur d'intervalle à 10% pour faciliter la comparaison visuelle des CA TTC et des événements spéciaux.

Pour ce faire, nous cliquons avec le bouton droit de la souris sur l'une des barres du graphique et choisissons « Options des séries ». Une fois la fenêtre des options ouverte, nous définissons le pourcentage de superposition des séries à 100%. Cela permet de superposer les barres représentant les événements spéciaux sur les barres représentant les CA TTC.

Ensuite, nous réduisons la largeur d'intervalle à 10% pour resserrer les barres et faciliter la lecture du graphique. Pour cela, dans la même fenêtre des options des séries, nous ajustons la valeur du champ « Largeur d'intervalle » à 10%. Nous validons ces modifications en cliquant sur le bouton « Ok ».

   3.2. Ajout et personnalisation des étiquettes de données

Maintenant, nous allons ajouter des étiquettes de données pour afficher les descriptions des événements spéciaux sur le graphique.

Tout d'abord, nous sélectionnons la barre « Y évènement » sur le graphique.

Ensuite, nous cliquons sur le bouton « + » qui apparaît à côté du graphique afin de cocher la case « Étiquettes de données » :

Les étiquettes de données apparaissent alors sur les barres d'événements.

Pour afficher les descriptions des événements plutôt que les valeurs numériques, nous sélectionnons l'une des étiquettes de données, puis faisons un clic droit et choisissons « Options d'étiquettes de données ».

Dans la fenêtre qui s'ouvre, nous sélectionnons l'option « Valeur à partir de cellules » et, dans la boîte de dialogue qui apparaît, nous sélectionnons la plage de cellules de la colonne « Évènements » du tableau « suiviCATTC » :

Ensuite, nous décochons les autres options de texte d'étiquette, comme « Valeur » et « Catégorie », pour n'afficher que les descriptions des événements.

   3.3. Personnalisation des étiquettes de données

Pour mettre en forme les étiquettes de données des barres, nous procédons comme suit :

• Nous sélectionnons l'une des étiquettes de données dans le graphique, ce qui permet de sélectionner toutes les étiquettes de cette série.
• Ensuite, depuis le menu « Mise en forme » du ruban, nous sélectionnons l'un des styles proposés, par exemple le style gris.

• Les étiquettes de données prendront immédiatement l'apparence du style choisi, ce qui les rendra plus visibles et attrayantes sur le graphique.