Les tableaux croisés dynamiques sont un incroyable outil de création de rapports intégré dans Excel. Bien que généralement utilisés pour résumer les données avec des totaux, vous pouvez également les utiliser pour calculer le pourcentage de changement entre les valeurs. Mieux encore: c’est simple à faire.
Vous pouvez utiliser cette technique pour faire toutes sortes de choses, à peu près partout où vous souhaitez voir comment une valeur se compare à une autre. Dans cet article, nous allons utiliser l’exemple simple de calcul et d’affichage du pourcentage selon lequel la valeur totale des ventes change mois par mois.
Voici la feuille que nous allons utiliser.
C’est un exemple assez typique de feuille de vente qui montre la date de la commande, le nom du client, le représentant commercial, la valeur totale des ventes et quelques autres choses.
Pour faire tout cela, nous allons d’abord mettre en forme notre plage de valeurs sous forme de tableau dans Excel, puis nous allons créer un tableau croisé dynamique pour effectuer et afficher nos calculs de variation en pourcentage.
Table des matières
Formatage de la plage sous forme de tableau
Si votre plage de données n’est pas déjà formatée sous forme de tableau, nous vous encourageons à le faire. Les données stockées dans des tableaux présentent plusieurs avantages par rapport aux données des plages de cellules d’une feuille de calcul, en particulier lors de l’utilisation de tableaux croisés dynamiques (en savoir plus sur les avantages de l’utilisation des tableaux).
Pour mettre en forme une plage sous forme de tableau, sélectionnez la plage de cellules et cliquez sur Insérer> Tableau.
Vérifiez que la plage est correcte, que vous avez des en-têtes dans la première ligne de cette plage, puis cliquez sur « OK ».
La plage est maintenant formatée sous forme de tableau. Le nom du tableau facilitera la référence à l’avenir lors de la création de tableaux croisés dynamiques, de graphiques et de formules.
Cliquez sur l’onglet « Conception » sous Outils de tableau, et entrez un nom dans la case prévue au début du ruban. Ce tableau a été nommé « Ventes ».
Vous pouvez également modifier le style du tableau ici si vous le souhaitez.
Créer un tableau croisé dynamique pour afficher la variation en pourcentage
Passons maintenant à la création du tableau croisé dynamique. Dans le nouveau tableau, cliquez sur Insérer> Tableau croisé dynamique.
La fenêtre Créer un tableau croisé dynamique apparaît. Il aura détecté automatiquement votre table. Mais vous pouvez sélectionner la table ou la plage que vous souhaitez utiliser pour le tableau croisé dynamique à ce stade.
Regrouper les dates en mois
Nous allons ensuite faire glisser le champ de date que nous voulons regrouper dans la zone des lignes du tableau croisé dynamique. Dans cet exemple, le champ est nommé Date de commande.
À partir d’Excel 2016, les valeurs de date sont automatiquement regroupées en années, trimestres et mois.
Si votre version d’Excel ne le fait pas ou si vous souhaitez simplement modifier le regroupement, cliquez avec le bouton droit sur une cellule contenant une valeur de date, puis sélectionnez la commande «Groupe».
Sélectionnez les groupes que vous souhaitez utiliser. Dans cet exemple, seuls les années et les mois sont sélectionnés.
L’année et le mois sont désormais des champs que nous pouvons utiliser pour l’analyse. Les mois sont toujours nommés comme date de commande.
Ajouter les champs de valeur au tableau croisé dynamique
Déplacez le champ Année des lignes vers la zone Filtre. Cela permet à l’utilisateur de filtrer le tableau croisé dynamique pendant un an, plutôt que d’encombrer le tableau croisé dynamique avec trop d’informations.
Faites glisser le champ contenant les valeurs (Valeur totale des ventes dans cet exemple) que vous souhaitez calculer et présenter la modification deux fois dans la zone Valeurs.
Cela ne ressemble peut-être pas encore à beaucoup. Mais cela changera très bientôt.
Les deux champs de valeur auront par défaut la somme et n’ont actuellement aucune mise en forme.
Les valeurs de la première colonne que nous souhaitons conserver sous forme de totaux. Ils nécessitent cependant un formatage.
Cliquez avec le bouton droit de la souris sur un nombre dans la première colonne et sélectionnez «Formatage des nombres» dans le menu contextuel.
Choisissez le format «Comptabilité» avec 0 décimale dans la boîte de dialogue Format des cellules.
Le tableau croisé dynamique ressemble maintenant à ceci:
Créer la colonne de changement de pourcentage
Cliquez avec le bouton droit sur une valeur dans la deuxième colonne, pointez sur «Afficher les valeurs», puis cliquez sur l’option «% de différence par rapport à».
Sélectionnez «(Précédent)» comme élément de base. Cela signifie que la valeur du mois en cours est toujours comparée à la valeur des mois précédents (champ Date de commande).
Le tableau croisé dynamique affiche désormais à la fois les valeurs et le pourcentage de changement.
Cliquez dans la cellule contenant les étiquettes de ligne et saisissez «Mois» comme en-tête de cette colonne. Cliquez ensuite dans la cellule d’en-tête de la deuxième colonne de valeurs et tapez «Variance».
Ajouter des flèches de variance
Pour vraiment peaufiner ce tableau croisé dynamique, nous aimerions mieux visualiser le pourcentage de changement en ajoutant des flèches vertes et rouges.
Ceux-ci nous fourniront une belle façon de voir si un changement a été positif ou négatif.
Cliquez sur l’une des valeurs de la deuxième colonne, puis sur Accueil> Mise en forme conditionnelle> Nouvelle règle. Dans la fenêtre Modifier la règle de mise en forme qui s’ouvre, procédez comme suit:
Sélectionnez l’option «Toutes les cellules affichant les valeurs« Variance »pour la date de commande».
Sélectionnez «Jeux d’icônes» dans la liste Style de format.
Sélectionnez les triangles rouge, orange et vert dans la liste Style d’icône.
Dans la colonne Type, modifiez l’option de liste pour dire «Nombre» au lieu de Pourcentage. Cela changera la colonne Valeur en 0. Exactement ce que nous voulons.
Cliquez sur « OK » et la mise en forme conditionnelle est appliquée au tableau croisé dynamique.
Les tableaux croisés dynamiques sont un outil incroyable et l’un des moyens les plus simples d’afficher la variation en pourcentage au fil du temps pour les valeurs.