Comment créer un graphique dynamique en quelques minutes sur Excel sans VBA ni TCD ?

Dans ce tutoriel, je vais vous montrer comment transformer vos données Excel en tableaux dynamiques et graphiques interactifs en seulement 10 minutes. Pour cela, nous découvrirons des outils puissants, mais simples à mettre en place, qui nous permettront de visualiser et d'analyser des données de manière plus efficace en les sélectionnant simplement dans une liste de données.

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

Pour illustrer ce tutoriel, nous allons partir du tableau suivant dans la plage des cellules A8 jusqu’à C17 :

Dans ce tableau, nous retrouvons les ventes mensuelles de trois catégories de produits : Électronique, Vêtements et Alimentation, sur les trois premiers mois de l'année d’une entreprise. Chaque ligne du tableau représente une catégorie de produit pour un mois donné, avec le montant total des ventes correspondant.

L’objectif de ce tutoriel sera donc de créer un tableau Excel dynamique qui nous permettra de sélectionner une catégorie spécifique ou de visualiser l'ensemble des catégories en un seul clic.

Cela nous permettra d'analyser les données plus facilement et de générer des graphiques dynamiques pour une meilleure présentation visuelle des informations.

Et pour commencer, nous allons préparer nos données en ajoutant une liste de sélection permettant d'inclure l'option « Toutes les catégories ».

2. Sélection d’une catégorie

Ici, nous allons donc souhaiter insérer une liste déroulante dans la cellule F9 pour sélectionner la catégorie à analyser.

Pour cela, nous allons utiliser un outil puissant d’Excel : la validation des données.

Cet outil permet en effet de contrôler les types de données que les utilisateurs peuvent entrer dans une cellule. Et pour cela, nous pouvons également créer des listes déroulantes.

Pour ajouter une validation des données sous forme de liste déroulante, nous procédons comme suit :

- Sélectionnez la cellule où vous souhaitez la validation (par exemple, F9).

- Allez dans l'onglet « Données », puis cliquez sur « Validation des données ».

- Dans la fenêtre qui s'ouvre, choisissez « Liste » comme critère de validation.

- Dans la case « Source », entrez la plage de votre liste combinée (par exemple, B9 à B17).

Ensuite, lorsque nous validons en appuyant sur le bouton OK, nous retrouvons une cellule intégrée directement dans la cellule F9 qui permet de sélectionner directement d’une des catégories de la colonne :

Ici, les doublons sont directement filtrés pour ne présenter que des valeurs uniques.

3. Ajouter un élément dans la liste

Pour ajouter une option « Toutes les catégories » à notre liste de catégories sans devoir la saisir dans la liste, nous allons pouvoir utiliser la fonction ASSEMB.V.

Cette fonction ASSEMB.V combine plusieurs plages de données en une seule plage verticale.

Cela sera très utile pour ajouter des éléments supplémentaires à une liste existante.

La fonction ASSEMB.V s'utilise de la manière suivante :

   =ASSEMB.V(Plage1;Plage2;...) 

Où :

- Plage1, Plage2, etc. sont les plages de cellules que nous souhaitons combiner verticalement.

Attention, cette fonction n’est disponible que dans les versions récentes d'Excel (Excel 2019 ou Excel pour Office 365).

Dans notre exemple, nous voulons ajouter l'option « Toutes les catégories » en haut de cette liste.

Il faut également savoir que nous ne pouvons pas directement saisir cette formule dans la validation des données.

Nous allons donc exploiter une plage de cellule provisoire et saisir la formule suivant par exemple dans la cellule

Nous écrivons donc la formule suivante directement dans la validation des données :

   =ASSEMB.V("Toutes les catégories";B9:B17)  

Comme vous pouvez le constater, cette formule crée une nouvelle liste avec « Toutes les catégories » suivie des catégories existantes.

De plus, il s’agit d’une formule matricielle, qui s’étend automatiquement sur toutes les cellules nécessaires, ce qui est très pratique ici.

Nous pouvons également constater cette fois-ci que les doublons sont repris, la suppression automatique que nous avons vu tout à l’heure est en effet une fonctionnalité de la validation des données.

Pour les supprimer, nous pouvons inclure cette formule comme argument de la fonction UNIQUE.

La fonction UNIQUE permet en effet d'extraire des valeurs uniques d'une plage ou d'un tableau, éliminant les doublons. Cette fonction est particulièrement utile pour créer des listes de valeurs distinctes à partir de grandes quantités de données.

La fonction UNIQUE s'utilise de la manière suivante :

   =UNIQUE(plage; [par_col]; [exactement_une_fois]) 

Où :

- `plage` est la plage ou le tableau de données à partir duquel vous souhaitez extraire les valeurs uniques.

- `[par_col]` est un paramètre facultatif qui indique si la fonction doit rechercher les valeurs uniques par colonne (VRAI) ou par ligne (FAUX). La valeur par défaut est FAUX.

- `[exactement_une_fois]` est un paramètre facultatif qui, lorsqu'il est défini sur VRAI, fait que la fonction ne renvoie que les valeurs qui apparaissent exactement une fois. La valeur par défaut est FAUX.

Ici, seul le premier argument va nous intéresser, est le premier (« plage »), car nous souhaitons uniquement retraiter la base générée par la fonction ASSEMB.V.

 =UNIQUE(ASSEMB.V("Toutes les  catégories";$B$9:$B$17)) 

La fonction UNIQUE est extrêmement puissante et utile pour gérer de grandes quantités de données et extraire des listes de valeurs distinctes. Elle simplifie le processus de déduplication et permet de créer des analyses plus précises et lisibles.

Maintenant que nous disposons de notre liste des catégories, augmentée du choix « Toutes les catégories », nous allons pouvoir l’insérer dans la liste des données à partir de l’outil de validation des données. Pour cela, nous allons utiliser une nouveauté d’Excel : le symbole dièse (#).

Ce symbole dièse (#) permet de référencer automatiquement une plage dynamique résultant d'une formule générant un tableau. C'est particulièrement utile pour les fonctions telles que UNIQUE, qui peuvent produire une plage de taille variable. En utilisant le symbole dièse, nous pouvons nous assurer que notre validation des données s'adapte automatiquement à la taille de la plage générée par la fonction UNIQUE.

Pour insérer notre liste de catégories dans la validation des données en utilisant le symbole dièse, nous sélectionnons à nouveau la cellule où nous souhaitons la validation des données, ici la cellule F9.

Puis, depuis l'onglet « Données », puis cliquons sur « Validation des données » et nous modifions la formule du champ « Source », pour saisir la formule :

     =H9# 

En utilisant le symbole dièse, nous indiquons à Excel de prendre en compte toute la plage de la liste générée automatiquement par les fonctions UNIQUE et ASSEMB.V. Cette plage s'adaptera automatiquement si le nombre de catégories change.

4. Mise en place des formules dynamiques

Avec notre liste de sélection prête, nous allons mettre en place des formules dynamiques pour extraire les données correspondantes.

Pour commencer, nous allons souhaiter définir dynamiquement le titre du graphique.

Pour cela, nous saisissons la formule suivante dans la cellule K6 :

 ="Montant des ventes : "&F9 

Cette formule combine le texte "Montant des ventes :" avec la valeur de la cellule F9, qui contient la catégorie sélectionnée (ou "Toutes les catégories" si cette option est choisie) en utilisant l’esperluette pour concaténer le contenu de ces deux textes.

Le résultat sera un titre de graphique qui change en fonction de la sélection.

Ensuite, pour calculer dynamiquement les ventes mensuelles en fonction de la catégorie sélectionnée, nous allons utiliser une combinaison des fonctions SI, SOMME.SI et SOMME.SI.ENS. La formule suivante doit être saisie dans la plage L10 à L21 :

 =SI($F$9="Toutes les  catégories";SOMME.SI($A$9:$A$44;K10;$C$9:$C$44);SOMME.SI.ENS($C$9:$C$44;$A$9:$A$44;K10;$B$9:$B$44;$F$9))  

Pour commencer, nous utiliser la fonction SI qui permet d’effectuer un test afin de retourner une valeur différente en fonction du résultat de ce test.

Si le test retourne la valeur VRAI, alors la fonction SI va retourner la valeur du deuxième argument, et dans le cas contraire elle renverra la valeur du troisième.

Ici, nous l’utilisons pour vérifier si la valeur de la cellule F9 est "Toutes les catégories".

Si effectivement la valeur sélectionnée dans la cellule F9 est "Toutes les catégories", alors nous utiliserons la fonction SOMME.SI pour additionner les ventes pour chaque mois de l’année, sans nous soucier de la colonne des catégories.

En revanche, si une catégorie en particulier est sélectionnée, alors nous utiliserons plutôt la fonction SOMME.SI.ENS qui fonctionne d’une manière presque analogue, sauf qu’ici nous pourrons utiliser plusieurs critères de sélection, le mois en cours et la catégorie sélectionnée.