Excel : suivre des dépenses dans un tableau croisé dynamique

Difficile la fonction Tableau croisé dynamique ? Pas si sûr ! Car Excel, c’est comme la grammaire. Soit on explique un concept simple à l’aide de mots savants et le lecteur perd son Latin ; soit on présente un exemple concret et des astuces pour retenir l’information. Prêt(e) à suivre des dépenses engagées ou réglées et les analyser dans un TCD ? GO !

Activ’Formation | Excel | Tableau croisé dynamique

Présentation d’un fichier de suivi des dépenses engagées ou réelles

tcd visuel1 table de donnees

Dans cet exemple, les Bons de commande (colonne F) sont enregistrés dans une Liste de données placée sur une feuille nommée Enregistrement des factures. Le Montant commandé est renseigné en colonne G.

La livraison est en attente (colonne H) et/ou la facture n’a pas été enregistrée (colonne I) ? Le montant de la commande initiale est réputé Engagé (colonne J). Les numéros du Bon de livraison et de la Facture sont enregistrés ? Le montant est considéré comme Facturé (colonne K).

Bon à savoir : dans une Liste de données, chaque Colonne est titrée et chaque titre s’appelle un Champ. Les données sont saisies en Ligne.

Création du tableau croisé dynamique initial

Pour analyser les dépenses Engagées (colonne J) et Facturées (colonne K) par Ligne budgétaire (colonne D) et par Type de dépenses (colonne C), un Tableau croisé dynamique via l’onglet Insertion.

tcd visuel2 tableau croise dynamique

Rappel de la méthodologie : cliquer dans la Liste de données (ex. les données saisies sur la feuille Enregistrement des factures) > activer l’onglet Insertion > repérer le groupe Tableaux > cliquer sur l’icône Tableau croisé dynamique > le volet Champs de tableau croisé dynamique s’affiche à droite de l’écran.

Glisser le champ Ligne Budgétaire dans la zone Lignes > glisser le champ Type de dépenses dans la zone Lignes > glisser le champ Montant engagé HT dans la zone Valeurs > glisser le champ Montant facture HT dans la zone Valeur.

tcd visuel3 placer les champs dans le TCD

Mise en forme du tableau croisé dynamique

Même si les outils de la fonction Tableau croisé dynamique sont intuitifs, découvrez quelques astuces pour mettre en forme les données.

>>> Réduire ou développer les niveaux de lecture

Pour réduire ou développer les Lignes budgétaires et les Types de dépenses, cliquer sur l’icône + ou – situées à droite des Étiquettes de lignes.

tcd visuel4 developper reduire les etiquettes de lignes

>>> Personnaliser les noms des étiquettes

Par défaut Excel utilise le langage des TCD pour nommer les colonnes. Pour remplacer des Étiquettes par les noms des champs du tableau de saisie d’origine : se placer dans le tableau croisé dynamique > activer, sur Ruban, l’onglet contextuel Création > repérer le groupe Disposition > cliquer sur la flèche de choix de l’icône Disposition du rapport > cliquer sur l’option Afficher en mode Plan > observer le TCD et régler la hauteur et la largeur des cellules de la ligne des titres (onglet Accueil > puis icônes du groupe Alignement).

tcd visuel5 renommer les etiquettes

>>> Afficher les valeurs en format Monétaire

Sélectionner les valeurs dans le TCD > activer l’onglet Accueil > repérer le groupe Nombre > sélectionner le format Monétaire dans la liste de l’icône Format > cliquer deux fois sur l’icône Réduire les décimales.

NB : il est également possible de passer par le menu contextuel Paramètres des champs de valeurs, mais le pas à pas est plus long.

>>> Dupliquer le TCD pour créer une nouvelle analyse

Sélectionner les cellules du premier TCD > cliquer sur le bouton droit de la souris, puis sur l’option Copier > se placer au point d’insertion (clic dans une cellule vide sous le premier tableau) > cliquer sur le bouton droit de la souris puis sur l’option Coller.

NB : le raccourci [Ctrl][C] suivi de [Ctrl][V] fonctionne également. Rappel de la méthodologie : sélectionner les cellules > appuyer sur la touche [Ctrl] du clavier et la maintenir enfoncée > appuyer sur la touche [C] pour copier la sélection. Ensuite, cliquer dans une cellule vierge > appuyer sur la touche [Ctrl] et la maintenir enfoncée > cliquer sur la touche [V]. Le contenu Copié est Collé.

Analyse des frais en valeur et en pourcentage

Pour affiner l’analyse, le champ Montant Cddé va être ajouté deux fois dans la zone Valeurs (volet droit Champs de tableaux croisés dynamiques). Ces ajouts vont permettre d’afficher : la Valeur totale des frais, et le % de répartition des frais.

>>> Ajouter les Champs d’analyse

Copier/Coller le premier TCD ou créer un nouveau TCD dans une Nouvelle feuille. Placer les Champs dans le volet Champs de tableau croisé dynamique. Ici, placer : Ligne budgétaire dans la zone Ligne , puis Montant engagé HT, Montant Facturé HT, Montant Cddé et Montant Cddé dans la zone Valeurs. Le second Montant Cddé devient Montant Cddé2.

tcd visuel6 ajouter des champs de valeurs

>>> Personnaliser les titres des champs

Se placer dans la cellule dont le titre doit être personnalisé. Saisir le nouveau titre dans la Barre de formule.

tcd visuel7 personnaliser les titres

>>> Afficher le % de répartition des dépenses par ligne budgétaire

Se placer dans l’une des cellules dont le format doit changer (ici, dans la colonne qui affiche une seconde fois le total des dépenses). Ensuite : cliquer sur le bouton droit de la souris > cliquer sur le menu contextuel Paramètres des champs de valeurs > observer la boîte de dialogue qui s’affiche. L’onglet Synthèse des valeurs par est actif.

tcd visuel8 afficher le pourcentage du total general

Repérer l’onglet Afficher les valeurs et l’activer > repérer la zone Afficher les valeurs, elle accueille par défaut la mention Aucun calcul > activer la flèche de choix située à droite de cette zone et cliquer sur % du total général > cliquer sur le bouton OK. Les valeurs en % s’affichent (ici : 25,62%, 1,32%, 17,52% et 55,54%) et le total est égal à 100%.

tcd visuel9 totaux et pourcentages

Enfantine la fonction Tableau croisé dynamique ? C’est sûr ! 

© Thomas R