Comment créer un tableau de bord commercial intelligent avec Excel ?

Dans ce tutoriel, je vais vous montrer comment créer un filtre dynamique pour analyser des ventes sur une période glissante de 6 mois dans Excel.

Ainsi, nous allons voir comment mettre en place un tableau de bord interactif qui nous permettra d'actualiser vos rapports en un clic.

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 tableau, nous allons utiliser le tableau suivant dans lequel sont reprises les ventes d'une boutique de vêtements réalisée au cours de l’année 2024 :

Dans ce tableau de base, nous allons ajouter une nouvelle colonne pour classifier nos données selon la période glissante de 6 mois.

Pour ce faire, nous nous plaçons sur la cellule E8 située juste à droite du tableau, et nous saisissons le titre « Période glissante » dans cette cellule.

Dans cette nouvelle colonne, nous allons insérer une formule qui va automatiquement classifier nos ventes comme faisant partie des « 6 derniers mois » ou de « Plus de 6 mois ».

Cette classification se basera sur la différence entre la date de vente et la date actuelle.

Voici la formule :

 =SI(AUJOURDHUI()-A9<=180;"6 derniers  mois";"Plus de 6 mois") 

Dans cette formule, nous retrouvons les arguments suivants :

- SI() : cette fonction vérifie si la condition est vraie ou fausse et renvoie le résultat approprié.

- AUJOURDHUI()-A9 : calcule le nombre de jours entre la date de vente (située sur la cellule A9) et la date d’aujourd'hui obtenue avec la fonction Excel AUJOURDHUI()

- 180 : représente approximativement 6 mois (30 jours x 6 mois).

Ainsi, si la différence entre aujourd'hui et la date de vente est inférieure ou égale à 180 jours, la formule renverra « 6 derniers mois », sinon, elle renverra « Plus de 6 mois ».

Une fois la formule entrée dans la cellule E9, nous devons la copier sur toute la colonne pour qu'elle s'applique à toutes nos données de vente.

Pour ce faire, plaçons notre curseur sur le coin inférieur droit de la cellule e2, jusqu’à ce que le curseur se transforme en croix noire.

Puis, nous cliquons et faisons glisser vers le bas jusqu'à la dernière ligne de nos données.

Il est également de double-cliquer sur le coin inférieur droit de la cellule E9 pour qu’Excel copie automatiquement la formule jusqu'à la dernière ligne contenant des données.

2. Création du tableau croisé dynamique

   2.1. Sélection des données

Maintenant que nos données sont prêtes, nous allons créer un tableau croisé dynamique pour les analyser plus facilement.

Pour faciliter la gestion et l’actualisation des données, nous allons convertir la base en tableau de données structuré.

Cela va en effet permettre de rendre le tableau croisé dynamique plus souple et réactif, car un tableau structuré est automatiquement étendu lorsque de nouvelles lignes ou colonnes sont ajoutées, ce qui permet d’éviter d’avoir à ajuster manuellement la plage des cellules utilisées par le tableau croisé dynamique.

Pour cela, nous commençons par sélectionner l'une des cellules de la plage de données, puis nous nous rendons dans l'onglet « Insertion » du ruban Excel et nous cliquons sur le bouton « Tableau » :

Une boîte de dialogue s'ouvre, nous demandant de confirmer la plage de données du tableau, et de valider que cette plage a effectivement des en-têtes, c’est-à-dire que la première ligne de notre tableau contient des intitulés clairs et distincts pour chaque colonne (par exemple, « Produit », « Date », « Montant », etc.).

Nous validons ensuite en appuyant sur le bouton [OK].

Nous pouvons nous rendre compte que le format des données a changé : les lignes et colonnes de notre tableau sont maintenant automatiquement mises en forme, avec des filtres ajoutés aux en-têtes pour faciliter la gestion et la navigation dans les données.

Maintenant, la plage des cellules du tableau est dynamique, ce qui signifie que toute nouvelle ligne ou donnée ajoutée sera automatiquement incluse dans le tableau structuré.

Ensuite, nous allons faire reposer le tableau croisé dynamique sur ce tableau structuré en revenant dans le menu « Insertion » et en choisissant cette fois-ci « Tableau croisé dynamique ». En faisant cela, nous assurons que le tableau croisé dynamique sera également dynamique, se mettant à jour automatiquement dès que de nouvelles données sont ajoutées au tableau structuré. Nous pourrons ainsi continuer à analyser efficacement nos données sans crainte de manquer des informations ajoutées après coup.

Une boîte de dialogue s'ouvre, nous demandant de confirmer la plage de données et l'emplacement du nouveau tableau croisé dynamique.

Après avoir vérifié que le nom du tableau est correct, nous choisissons d'insérer le tableau croisé dynamique dans une nouvelle feuille de calcul pour plus de clarté.

Excel crée alors une nouvelle feuille avec notre tableau croisé dynamique vide, prêt à être configuré.

   2.2. Configuration des champs

À droite de l’écran, nous voyons apparaître la liste des champs du tableau croisé dynamique, c'est ici que nous allons définir comment nous voulons organiser nos données.

Commençons par glisser-déposer le champ « date » dans la zone « lignes ».

Excel va automatiquement regrouper nos dates par mois, ce qui est parfait pour notre analyse.

Ensuite, faisons glisser le champ « produit » dans la zone « colonnes ».

Cela nous permettra de voir nos ventes par type de produit.

Pour finir, glissons le champ « quantité » dans la zone « valeurs ».

Par défaut, Excel va calculer la somme des quantités vendues, ce qui est exactement ce que nous voulons.

3. Ajout du filtre dynamique

Pour rendre notre analyse vraiment dynamique, nous allons ajouter un segment basé sur notre colonne « période glissante ».

Les segments sont des filtres visuels et interactifs qui nous permettront de basculer facilement entre les données des 6 derniers mois et celles plus anciennes.

Pour ajouter un segment, cliquons n'importe où dans notre tableau croisé dynamique pour faire apparaître l'onglet « outils de tableau croisé dynamique » dans le ruban.

Dans cet onglet, cherchons le bouton « insérer un segment » et cliquons dessus.

Une boîte de dialogue s'ouvre, nous montrant tous les champs disponibles pour créer un segment.

Cochons la case à côté de « période glissante » et cliquons sur « ok ».

Une nouvelle boîte apparaît sur notre feuille de calcul avec notre segment.

Par défaut, il affiche les deux options que nous avons définies : « 6 derniers mois » et « Plus de 6 mois ».

Maintenant que notre segment est en place, essayons-le !

Pour cela, nous cliquons sur l'option « 6 derniers mois » afin de voir notre tableau croisé dynamique se mettre à jour pour n'afficher que les données des 6 derniers mois.

Ensuite, nous pouvons cliquer sur « Plus de 6 mois » pour voir les données plus anciennes.

Nous pouvons également cliquer sur les deux options en maintenant la touche [Ctrl] enfoncée pour voir toutes les données en même temps.

Ce segment nous permet de basculer rapidement entre différentes périodes d'analyse, rendant notre tableau de bord vraiment interactif et puissant.

4. Automatisation et mise à jour

Le principe d’un tableau croisé dynamique, c’est de stocker les données de base dans une zone tampon afin de faciliter la synthèse et l’analyse de grandes quantités d’informations. Cette zone tampon, aussi appelée "cache", permet de manipuler les données de manière flexible sans affecter directement les données sources. En effet, le tableau croisé dynamique extrait et regroupe ces données pour offrir une vue d'ensemble plus concise et organisée, en fonction des critères sélectionnés (colonnes, lignes, valeurs, filtres).

Il est donc nécessaire de l’actualiser après chaque modification pour prendre en compte les nouvelles données ou les ajustements apportés aux données sources. Sans cette actualisation, les données présentées dans le tableau croisé dynamique ne refléteraient plus fidèlement la réalité, ce qui pourrait entraîner des erreurs d’interprétation.

Pour cela, nous pouvons soit actualiser en cliquant sur le bouton "Actualiser" depuis le menu « Données » du le ruban Excel, soit effectuant un clic-droit sur l’une des cellules du tableau croisé dynamique :

Il serait également possible de mettre en place un processus d’actualisation automatique en développant une petite macro VBA si les modifications des données sont fréquentes

Ici, même après actualisation, les données restent inchangées, car nous n’avons pas encore modifié les données sources.

De plus, pour nous assurer que notre tableau de bord affiche toujours les données les plus récentes, nous allons configurer Excel pour qu'il rafraîchisse automatiquement notre tableau croisé dynamique et notre graphique à chaque ouverture du classeur.

Pour cela, nous cliquons avec le bouton droit sur notre tableau croisé dynamique et sélectionnons « options du tableau croisé dynamique ».

Dans la fenêtre qui s'ouvre, allons dans l'onglet « données », nous cochons la case « Actualiser les données à l'ouverture du fichier ».

Cette option garantit que notre analyse prend en compte toutes les nouvelles données que nous aurions pu ajouter depuis la dernière utilisation du fichier.

Maintenant, pour tester le tableau croisé dynamique, ajoutons quelques nouvelles lignes de données en dessous de notre tableau original.

Par exemple, ajoutons ces lignes à la fin de notre tableau (sans oublier d’étendre la formule de la dernière colonne) :

Après avoir ajouté ces nouvelles lignes, retournons à notre feuille contenant le tableau croisé dynamique.

Nous effectuons un clic droit sur le tableau croisé dynamique et nous cliquons sur « Actualiser ».

Nous pouvons maintenant voir notre tableau se mettre à jour pour inclure ces nouvelles données.

Essayons maintenant notre filtre dynamique.

Pour cela, cliquons sur « 6 derniers mois » dans notre segment pour que notre tableau affiche maintenant uniquement les données des 6 derniers mois, y compris nos nouvelles entrées.