Dans ce tutoriel, je vais vous montrer comment afficher, dans un même tableau croisé dynamique, le montant de chaque dépense et sa part dans le budget total.
C’est une information très utile, mais les tableaux croisés dynamiques affichent généralement uniquement des sommes lors de leur création. Nous obtenons donc des montants corrects, sans forcément savoir quels postes pèsent réellement dans le total.
À la fin de ce tutoriel, nous saurons ajouter deux fois le même champ dans un TCD, conserver le premier en euros et transformer le second en pourcentage du total général.
Nous verrons également comment trier les catégories, réduire le détail des fournisseurs et éviter les principales erreurs de calcul et de mise en forme.
Téléchargement
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous retrouvons les différentes dépenses engagées pour organiser un salon professionnel.
Chaque ligne correspond à une dépense. Nous retrouvons sa date, sa catégorie, le fournisseur concerné, son montant et son mode de paiement.
Avant de créer notre tableau croisé dynamique, nous allons transformer cette plage en tableau structuré.
Nous cliquons dans l’une des cellules de la base, puis nous utilisons le raccourci [Ctrl]+[L].
Dans la fenêtre qui apparaît, nous vérifions que la plage commence bien en A6 et se termine sur la dernière ligne de données. Nous cochons ensuite « Mon tableau comporte des en-têtes », puis nous validons avec « OK ».
Cette transformation est importante, car le tableau structuré pourra s’agrandir lorsque nous ajouterons de nouvelles dépenses. Nous n’aurons donc pas besoin de modifier manuellement la source du TCD à chaque ajout.
Nous cliquons dans le tableau, puis nous ouvrons l’onglet « Création de tableau ». Dans la zone « Nom du tableau », nous remplaçons le nom proposé par « Depenses ».
Nous évitons les espaces et les caractères accentués dans les noms techniques, même si les en-têtes visibles peuvent naturellement en contenir.
2. Créer le tableau croisé dynamique
Maintenant que notre base est correctement préparée, nous allons créer notre synthèse.
Nous cliquons dans une cellule du tableau « Depenses », puis nous nous rendons dans l’onglet « Insertion ». Nous cliquons sur « Tableau croisé dynamique ».
Excel sélectionne automatiquement le tableau structuré comme source. Nous choisissons « Nouvelle feuille de calcul », puis nous validons en cliquant sur « OK ».
Une nouvelle feuille apparaît avec un tableau croisé dynamique vide. Sur la droite, le volet « Champs de tableau croisé dynamique » présente les cinq colonnes de notre base.
Nous faisons glisser le champ « Catégorie » dans la zone « Lignes ». Excel affiche immédiatement la liste des différentes catégories, comme la location, la restauration ou la technique.
Nous faisons ensuite glisser le champ « Fournisseur » sous « Catégorie », toujours dans la zone « Lignes ». Nous obtenons ainsi deux niveaux d’analyse : les catégories principales et, sous chacune d’elles, le détail des fournisseurs.
Enfin, nous faisons glisser « Montant » dans la zone « Valeurs ».
Excel doit normalement afficher « Somme de Montant ». Si nous obtenons « Nombre de Montant », cela signifie qu’Excel considère au moins une valeur comme du texte.
Dans ce cas, nous revenons dans la base et nous vérifions que les montants ne contiennent pas de symbole euro saisi manuellement, d’espace parasite ou d’apostrophe. Nous pouvons également cliquer sur la flèche du champ dans la zone « Valeurs », sélectionner « Paramètres des champs de valeurs », puis choisir « Somme ».
Nous disposons maintenant d’une synthèse des dépenses par catégorie et par fournisseur.
Pour rendre les montants plus lisibles, nous effectuons un clic droit sur une valeur, puis nous choisissons « Paramètres des champs de valeurs ». Nous cliquons sur « Format de nombre », sélectionnons « Monétaire », choisissons le symbole « € » et conservons zéro décimale.
Il vaut mieux appliquer le format depuis les paramètres du champ plutôt que depuis l’onglet « Accueil ». De cette manière, le format reste attaché au TCD après son actualisation.
À ce stade, nous connaissons les montants dépensés, mais nous ne savons pas encore quelle part chaque catégorie représente dans le budget global. C’est ce que nous allons ajouter maintenant.
3. Afficher les montants et les pourcentages
Pour afficher deux calculs différents, nous pouvons ajouter plusieurs fois le même champ dans la zone « Valeurs ».
Dans le volet des champs, nous faisons donc glisser une seconde fois le champ « Montant » dans la zone « Valeurs ».
Notre tableau contient maintenant deux colonnes identiques. La première affiche les dépenses en euros et la seconde affiche, pour le moment, les mêmes montants.
Nous effectuons un clic droit sur une valeur de la deuxième colonne, puis nous choisissons « Afficher les valeurs » et « % du total général ».
Selon la version d’Excel, nous pouvons également ouvrir « Paramètres des champs de valeurs », sélectionner l’onglet « Afficher les valeurs », puis choisir « % du total général » dans la liste.
Excel divise alors chaque montant par le total de toutes les dépenses.
Par exemple, les dépenses de location s’élèvent à 3 530 €, tandis que le budget total atteint 12 290 €. Excel affiche donc environ 28,7 % pour cette catégorie.
Le calcul effectué en arrière-plan correspond au principe suivant :
=3530/12290
Nous n’avons pas besoin de saisir cette formule dans la feuille. Elle permet simplement de comprendre le résultat obtenu.
Nous allons maintenant renommer nos deux champs. Nous cliquons sur l’en-tête « Somme de Montant », puis nous ouvrons les paramètres du champ de valeurs.
Dans la zone « Nom personnalisé », nous saisissons « Dépenses ». Pour le deuxième champ, nous saisissons « Part du budget ».
Si Excel refuse un nom parce qu’il existe déjà dans la base source, nous pouvons choisir un intitulé différent ou ajouter une espace à la fin. Cet espace ne sera pratiquement pas visible, mais Excel considérera le nom comme unique.
Si cela n’a pas été fait automatiquement, nous pouvons appliquer un format en pourcentage à la seconde colonne. Nous ouvrons ses paramètres, cliquons sur « Format de nombre », choisissons « Pourcentage » et conservons une décimale.
4. Trier, réduire et actualiser l’analyse
Notre tableau est fonctionnel, mais nous pouvons encore améliorer sa lecture.
Pour identifier rapidement les postes les plus importants, nous cliquons sur un montant correspondant à une catégorie (en grais), puis nous effectuons un clic droit. Nous sélectionnons « Trier », puis « Trier du plus grand au plus petit ».
Les catégories sont maintenant classées selon leur montant total. La catégorie la plus coûteuse apparaît en haut du tableau.
Nous évitons de déplacer manuellement les catégories, car leur ordre risquerait de ne plus correspondre aux montants après l’ajout de nouvelles données. Le tri automatique sera recalculé lors de l’actualisation.
Pour obtenir une vue plus synthétique, nous pouvons masquer le détail des fournisseurs.
Nous effectuons un clic droit sur le nom d’une catégorie, puis nous choisissons « Développer/Réduire » et « Réduire le champ entier ».
Excel n’affiche plus que les catégories, leurs dépenses et leur part dans le budget. Cette présentation est idéale pour une réunion ou une synthèse destinée à la direction.
Nous pouvons ensuite cliquer sur le petit symbole « + » placé devant une catégorie pour afficher uniquement ses fournisseurs. Si nous souhaitons tout réafficher, nous effectuons un clic droit et choisissons « Développer le champ entier ».
Pour améliorer la présentation, nous pouvons également ouvrir l’onglet « Création », puis sélectionner « Disposition du rapport » et « Afficher sous forme tabulaire ». Les catégories et les fournisseurs seront ainsi placés dans deux colonnes distinctes.
Enfin, retournons dans la base et ajoutons une nouvelle dépense sous la dernière ligne :
Comme la source est un tableau structuré, cette nouvelle ligne est automatiquement intégrée à « Depenses ».
Nous revenons dans le TCD, effectuons un clic droit, puis sélectionnons « Actualiser ». Le raccourci [Alt]+[F5] permet d’obtenir le même résultat.
Excel recalcule alors le montant de la communication, le total général et tous les pourcentages.
