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

Bonjour à tous, c'est Alex d'excel-formation.fr et dans ce tutoriel, je vais vous montrer comment créer un filtre dynamique qui va permettre d'analyser des ventes sur une période glissante de 6 mois dans Excel. Nous allons partir d'une base de données qui reprend les ventes réalisées par une entreprise et obtenir ici un tableau qui synthétise ces ventes en sachant que nous pourrons filtrer automatiquement les ventes des 6 derniers mois ou celles qui appartiennent à la période des 6 derniers mois ou alors les ventes qui datent de plus de 6 mois. Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne alors pensez à vous abonner et surtout à activer la clochette de notification afin d'être sûr de ne manquer aucune astuce et de devenir rapidement un pro d'Excel. Comme d'habitude, si vous voulez être sûr de bien comprendre tout ce que nous allons voir dans le cadre de cette vidéo, vous pouvez télécharger le support de ce tutoriel en suivant le lien disponible dans la description de la vidéo ce qui vous permettra de réaliser les opérations. Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau que nous avons actuellement à l'écran dans lequel sont repris les ventes d'une boutique de vêtements réalisées au cours de l'année 2024. Dans ce tableau de base, nous allons également souhaiter ajouter une nouvelle colonne qui va permettre de classifier les données selon la période glissante de 6 mois. Pour cela, nous allons afficher la colonne et bien entendu, si vous êtes sur votre propre tableau, il suffira de l'ajouter à droite du tableau sources de données. Ensuite, nous allons nous placer sur la première cellule, c'est-à-dire ici la cellule E9 et nous allons insérer une formule qui va automatiquement classifier les ventes comme faisant partie des 6 derniers mois ou alors de plus de 6 mois. Cette classification se basera bien entendu sur la différence entre la date de vente que nous avons dans la première colonne et la date actuelle que nous allons pouvoir obtenir grâce à la formule Excel aujourd'hui. Nous commençons par taper le symbole égal qui va permettre d'informer Excel que nous allons venir saisir une formule. Puis, nous appelons la fonction si qui permet de vérifier si une condition est vraie ou fausse et de renvoyer le résultat approprié en fonction de ce résultat. Ici, ce test va consister à compter le nombre de jours entre aujourd'hui et la date de la ligne, c'est-à-dire ici la valeur de la cellule A9, et vérifier si ce résultat est inférieur ou égal à 180 jours. 180 jours qui correspond à 6 fois 30, donc approximativement 6 mois. Lorsque ce sera le cas, nous pourrons afficher comme résultat 6 derniers mois. En revanche, lorsque ce ne sera pas le cas, c'est-à-dire que le nombre de jours entre aujourd'hui et la date de vente est supérieur à 180, alors nous afficherons plus de 6 mois. Nous fermons les guillemets, la parenthèse, et avant de valider, bien sûr, vous aurez bien compris qu'ici, il manque encore des guillemets. Cela étant fait, nous pouvons valider en appuyant sur OK pour que Excel nous informe que la première ligne qui a été faite le 1er janvier 2024 a plus de 6 mois, étant donné qu'aujourd'hui, nous sommes le 30 septembre. Cela étant fait, il va nous suffire de dupliquer la formule sur toutes les cellules de la colonne en la sélectionnant et en plaçant le curseur de la souris en bas à droite pour que ce curseur prenne la forme d'une croix. Ensuite, soit nous descendons manuellement jusqu'en bas de la colonne, soit pour aller encore plus vite, nous pouvons tout simplement double-cliquer pour qu'Excel étende automatiquement la formule sur toute la colonne. Nous pouvons voir que la dernière vente qui a plus de 6 mois date du 30 mars. À partir du 14 avril, les ventes entrent dans la catégorie des 6 derniers mois. Maintenant que nos données sont prêtes, nous allons pouvoir créer un tableau croisé dynamique qui va permettre de les analyser plus facilement. Et pour faciliter la gestion et l'actualisation des données, nous allons tout d'abord souhaiter convertir la base en un tableau de données structuré. Cela va en effet nous 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. Donc pour effectuer cette transformation, nous commençons par sélectionner l'une des cellules du tableau, puis nous nous rendons dans l'onglet Insertion afin de cliquer sur le bouton Tableau. Une boîte de dialogue va alors s'ouvrir pour nous demander de confirmer la plage des données du tableau et de valider que cette plage a effectivement des entêtes, c'est-à-dire que la première ligne du tableau contient les intitulés ici, c'est bien le cas, donc nous pouvons laisser l'option Cocher, et après avoir vérifié que toutes les cellules sont bien encadrées par la sélection automatique, nous pouvons cliquer sur OK. Excel effectue la transformation, ce que nous pouvons voir par le fait que les formats de données ont changé. Nous avons des lignes et des colonnes dans notre tableau automatiquement mis en forme, avec des filtres ajoutés au niveau des entêtes qui vont permettre de faciliter la gestion et la navigation dans les données. Donc ensuite, pour faire reposer le tableau croisé dynamique sur ce tableau structuré, nous allons tout d'abord nous rendre dans le menu Création de tableau, afin de donner un nom clair à ce tableau, que nous allons appeler tout simplement Base. Cela étant fait, nous revenons dans le menu Insertion, et nous allons maintenant cliquer sur Tableau croisé dynamique. Nous voyons que, automatiquement, Excel va utiliser la base Base en tant que source de données du tableau croisé dynamique. Nous pouvons ensuite choisir si nous voulons insérer ce tableau croisé dynamique sur une nouvelle feuille, ou sur une plage de cellules de la feuille existante. Ici, pour bien séparer les données et que les choses soient plus claires, nous allons choisir Nouvelle feuille de calcul. Excel crée alors une nouvelle feuille, qu'il a ici appelée Feuille 1, avec notre tableau croisé dynamique vide, prêt à être configuré. Pour cela, lorsque nous sélectionnons une des cellules du tableau croisé dynamique, nous pouvons voir que nous avons sur la droite un panneau latéral qui va permettre de personnaliser le tableau croisé dynamique. Si jamais celui-ci n'est pas présent, il vous suffit tout simplement de vous rendre dans Analyse du tableau croisé dynamique, et de cliquer sur Liste de champs. Le bouton permet de masquer et d'afficher la liste des champs. Ensuite, nous allons pouvoir créer notre tableau, en commençant par glisser le champ Date, que nous avons tout en haut, dans la zone des lignes. Excel va alors automatiquement regrouper nos dates, et ensuite nous allons faire glisser le champ Produit, cette fois-ci, au niveau des colonnes. Nous avons donc maintenant une matrice, avec chacune des dates de notre base, et chaque produit qui seront situés au niveau des colonnes. Ce tableau va donc nous permettre de voir nos ventes, par type de produit et par jour. Pour finir, nous allons faire glisser le champ Quantité, dans la zone des valeurs, afin que, ici, Excel nous place le nombre de produits vendus, par type de produit et par date. Maintenant, pour pouvoir rendre notre analyse vraiment dynamique, nous allons ajouter un segment basé sur notre colonne période glissante. Les segments sont en effet des filtres visuels et interactifs, qui nous permettent de basculer facilement entre les données des six derniers mois, et celles qui sont plus anciennes. Pour ajouter un segment, nous sélectionnons une des cellules du tableau croisé dynamique, nous revenons à nouveau dans Analyse du tableau croisé dynamique, au niveau du menu ruban, et nous cliquons sur Insérer un segment. Excel nous affiche alors une mini boîte de dialogue, qui nous permet de sélectionner la liste des champs, à partir desquels nous allons vouloir générer notre segment. Ici, nous choisissons période glissante, afin d'avoir une boîte qui va nous permettre de sélectionner rapidement les ventes des six derniers mois, ou alors celles de plus de six mois, en cliquant tout simplement sur les boutons. Pour réafficher toutes les données, nous cliquons donc ici sur l'entonnoir avec la croix rouge. Alors, si jamais vous ne connaissez pas les tableaux croisés dynamiques, sachez que le principe d'un tableau croisé dynamique, c'est de stocker les données de base dans une zone tampon de l'ordinateur, afin de faciliter la synthèse et l'analyse des grandes quantités d'informations. Cette zone tampon, que nous pouvons également appeler le cache, permet de manipuler les données de manière flexible, sans affecter directement les données sources. Ainsi, 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, comme nous l'avons vu juste avant. Il est donc nécessaire d'actualiser ce cache 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 donc entraîner des erreurs d'interprétation. Et donc, pour actualiser les données, il y a deux possibilités. Soit nous nous rendons dans le menu Données, et nous cliquons tout simplement sur Actualiser tout. Soit, si nous voulons uniquement actualiser notre tableau croisé dynamique, nous effectuons un clic droit sur celui-ci, afin de cliquer sur Actualiser. Bien entendu, étant donné que les données sources n'ont pas été modifiées, aucune modification ne va être mise en place sur notre tableau. Alors évidemment, il serait également possible de mettre en place un processus d'actualisation automatique, en développant une petite macro VBA. Si vous souhaitez en savoir davantage, vous trouverez dans la description de la vidéo le lien vers un tutoriel qui explique pas à pas comment mettre en place cette fonctionnalité. Voilà donc, pour tester les modifications du tableau croisé dynamique, nous allons nous rendre sur notre base de données, nous placer juste en dessous du tableau, en nous rappelant bien que, étant donné que nous avons mis en place ici un tableau structuré, le fait d'ajouter une nouvelle ligne à la suite va permettre de l'intégrer directement dans la plage des cellules du tableau. Donc ici, nous allons ajouter par exemple une nouvelle ligne le 24 octobre. Ici, nous avons vendu un t-shirt, plus exactement 55 t-shirts, des t-shirts d'une valeur unitaire de 15,99€. Bien entendu, étant donné qu'il s'agit de la date la plus récente, celle-ci fait bien partie des 6 derniers mois. Maintenant, comme je vous l'expliquais juste avant, si nous revenons sur le tableau croisé dynamique, nous pourrons constater que nous n'avons pas encore cette ligne intégrée dans le tableau. En revanche, lorsque nous allons actualiser les données, une nouvelle ligne va bien être insérée en dessous avec la vente qui correspond à la ligne que nous venons d'ajouter. Cela étant fait, si nous filtrons uniquement les ventes des 6 derniers mois, bien entendu, une nouvelle ligne sera également intégrée dans la base de données. Donc, comme d'habitude, vous pouvez télécharger le support de secours en suivant le lien que vous trouverez dans la description de la vidéo. Si la vidéo vous a plu, n'oubliez pas de la liker et de la partager à vos collègues et amis sur l'ensemble de vos réseaux sociaux. Et vous pouvez également me laisser vos impressions dans l'espace des commentaires. Je lirai chacun d'entre eux et y répondrai avec plaisir. Evidemment, si ce n'est pas encore fait, abonnez-vous à la chaîne pour ne manquer aucune astuce. C'est également très important pour m'aider à gagner en visibilité afin de pouvoir développer la chaîne et continuer de vous proposer des tas de nouvelles vidéos. Merci d'avoir suivi cette vidéo et à très bientôt pour un prochain tuto !