Excel - Power Query - Power Pivot - VBA

Excel - Power Query - Power Pivot - VBA

Temps 4 | Présenter et synthétiser

Tableaux croisées dynamiques

Les fameux TCD d'Excel !

TCD multi tables

Création

Pour une gestion facilitée des TCD, les données source doivent se trouver dans un Tableau Excel : consultez la fiche correspondante pour apprendre à créer un Tableau Excel.

Cliquez n’importe où dans un tableau Excel (cf. la fiche correspondante). Ce tableau sera la source du TCD.

⁠

Pensez à renommer le tableau (Onglet Création -> Zone Nom du tableau)

⁠

Onglet Insertion -> TblCroiséDynamique

⁠

Dans la boîte Créer un tableau croisé dynamique, cliquez sur OK.

⁠

Excel a créé une nouvelle feuille et a positionné un TCD prêt à recevoir des champs.

Placer des champs

Depuis la liste des champs de TCD (à droite de la fenêtre), faites glisser les champs dans l’un des 4 zones :

Zone Étiquettes de ligne : chaque occurrence (ou « élément ») du champ représentera une ligne du TCD

Zone Étiquettes de colonnes : idem pour les colonnes

Zone Valeurs : les éléments calculés (SOMME…)

Zone Filtre du rapport : le TCD est filtré.

Notez que l’on peut glisser plusieurs champs dans chaque zone.

⁠

Correspondances des zones

À chacune des 4 zones de la liste de champs correspond une zone du TCD. Après avoir fait glisser des champs dans chacune des 4 zones, le TCD affiche les données synthétisées des champs :

⁠

Actualiser un TCD

Si vous modifiez le Tableau Excel qui à la source (ajout / modification / suppression de lignes / colonnes), vous devez actualiser les TCD pour qu’ils prennent en compte les changements.

Cliquez droit n’importe où sur un des TCD du classeur > Actualiser

Onglet Options -> Actualiser -> Actualiser tout

Tous les TCD de tout le classeur (même s’ils sont de sources différentes)

La zone valeurs

Modifier la fonction de synthèse

Quand on place un champ dans la zone Valeurs, Excel décide de la fonction par défaut : SOMME ou NB. Pour changer cette fonction, faites un clic droit sur un des nombres de la zone valeurs du TCD puis choisissez Synthétiser les valeurs par puis l’une des fonctions du menu.

Formater les nombres

Clic droit sur une des cellules de la colonnne du champ de la zone Valeur à modifier dans le TCD puis Format de nombres.

Choisissez un format de nombre (nombre, %, monétaire…) et cliquez sur OK.

⁠

Calculer la répartition en %

Ajoutez le champ sur lequel vous voulez faire le calcul du %

Clic droit dans la zone Valeurs du TCD puis Afficher les valeurs puis % du total de colonne (ou de la ligne).

Afficher l’évolution entre 2 périodes en %

Ajouter un champ de durée (par exemple Année en colonne ou en ligne).

⁠

Clic droit sur n’importe quel nombre de la zone Valeurs puis Afficher les valeurs puis Différence en % par rapport.

⁠

Choisissez le champ à comparer, sélectionnez (précédent) et cliquez sur OK.

⁠

Options du TCD

Quelques options pratiques du TCD :

Clic droit n’importe où sur le TCD puis Options du TCD.

Onglet Disposition et mise en forme :

Pour masquer les #DIV/0 ou autre message d’erreur, cochez Pour les valeurs d’erreur afficher et laissez la zone de saisie vide.

Pour que la largeur des colonnes ne soit par ajusté à chaque actualisation, décochez Ajuster automatiquement la largeur des colonnes lors de la mise à jour

Onglet Totaux et filtres :

Au besoin, décochez Afficher les totaux des lignes

Au besoin, décochez Afficher les totaux des colonnes

Onglet Données :

Cochez Actualiser les données lors de l’ouverture du fichier

Créer d’autres TCD

Si vous avez déjà créé un premier TCD, le plus simple pour en créer d’autre est de faire un simple copier/coller du 1er tableau. Sélectionnez bien tout le TCD, y compris les champs de filtres.

Finaliser le TCD

Zone Étiquettes

Pour masquer les zones « Étiquettes de lignes » et « Étiquettes de colonnes » :

Clic dans le TCD

Onglet Analyse > En-têtes de champ

⁠

Changer le nom des zones de valeur

Cliquer sur le nom à modifier

Saisissez le nouveau nom (ne pas tenter de l’effacer). Si le nom existe déjà dans la liste des champs, Excel affiche un message. Une astuce consiste à ajouter un espace après le nouveau nom.

⁠

Segments

Un segment permet d’appliquer un filtre sur plusieurs TCD en même temps.

Cliquez dans un TCD

Insertion > Segments

Cocher le ou les champs pour lesquels vous souhaitez appliquer des filtres et OK.

Pour chaque segment : clic droit > Connections de tableau croisé dynamique [2010] | Signaler les connections [2016] | Connexion de rapport [>2019] puis cochez les TCD à relier au segment.

Notez qu’à partir de la version 2013 d’Excel, vous pouvez créer un segment pour un Tableau (cf. la fiche

Tableaux⁠

)