Un Tableau Croisé Dynamique (TCD) est la présentation d'une source de données sous forme de tableau. Il est dynamique car toute modification de la source entraîne la mise à jour du tableau (l'actualisation des données). Il permet de combiner et comparer rapidement un grand nombre de données.
Dans un rapport de tableau croisé dynamique, chaque colonne ou champ de données sources devient un champ de tableau croisé dynamique qui synthétise plusieurs lignes d'information. La présentation du tableau peut être paramétrée en personnalisant la position des champs de données, en fonction des résultats à visualiser.
Cet outil permet d'effectuer des calculs (somme, nombre, nb, moyenne, produit, max, min, ecarttype, var) et d'analyser de façon Dynamique la source de données. Il est possible de faire pivoter les lignes et colonnes pour afficher différentes synthèses des données sources.

Le TCD est un outil statistique qui repose avant tout sur une base de données bien structurée.
Il ne faut pas laisser de colonnes vides entre les données de la base.
Evitez de placer le TCD dans la feuille contenant la base de données.

Faites un clic droit dans le TCD.
Sélectionnez l'option Assistant dans le menu contextuel.
Cliquez sur le bouton Disposition.
Vous avez désormais accès à la fenêtre pour déplacer ou modifier les champs.
Cliquez sur les boutons OK et Terminer pour valider.

Sélectionnez la plage des cellules pouvant contenir des 0, à l'intérieur du TCD.
Faites un clic droit.
Sélectionnez l'option Format de cellule dans le menu contextuel.
Sélectionnez l'onglet Nombre, puis l'option Personnalisé dans la liste Catégorie.
Appliquez par exemple le format 0,0;-0,0;"" dans le champ Type. Cliquez sur le bouton OK pour valider.

Sélectionnez le tableau croisé dynamique.
Sélectionnez l'onglet "Options" dans le ruban.
Cliquez sur le bouton "Options du tableau croisé dynamique".
Options.
Sélectionnez l'onglet "Disposition et mise en forme".
Dans le champ "Pour les cellules vides, afficher:", assurez vous que l'option est cochée puis saisissez la valeur 0.
Cliquez sur le bouton OK pour valider.

En double cliquant sur le total d'un élément dans le TCD, vous obtiendrez dans un nouvel onglet la totalité des informations concernant cet élément.

Double cliquez sur l'entête d'un champ dans le TCD.
Dans la boîte de dialogue Champ dynamique, cliquez sur le bouton Avancé.
Sélectionnez l'option d'affichage des 10 premiers dans la boite de dialogue Options avancées de champ dynamique en choisissant l'option Activé.
Vous pouvez aussi préciser un tri automatique en paramétrant les options Croissant ou Décroissant.
Le tri du champ peut être effectué par rapport à lui-même ou par rapport à un autre champ (dans la liste déroulante Sur le champ).
Cliquez sur les boutons OK pour valider.

Lors de la création du TCD, à l'étape 3, il est possible d'ajouter une deuxième fois un même champ dans la zone de données, et de choisir un autre type de synthèse, par exemple en %:
Glissez une 2ème fois le champ dans la zone Données.
Double cliquez sur le champ que vous venez d'ajouter.
Cliquez sur le bouton Options dans la boite de dialogue Champ PivotTable.
Dans le champ Afficher les données, sélectionnez % du total.
Il est aussi possible de personnaliser le nom du champ à cette étape.
Cliquez sur le bouton OK pour valider.

Cette astuce est pratique pour redéfinir automatiquement la plage de données source, si vous utilisez un nombre de lignes ou de colonnes variables. La plage nommée est modifiée en fonction du nombre de cellules non vides.

Utilisez le Menu Insertion / Nom / Définir.
Dans le champ "Noms dans le classeur", nommez la plage de cellules (par exemple NomPlage).
Dans le champ "Fait référence à :", indiquez la formule suivante :
=DECALER($A$1;0;0;NBVAL($A$1:$A$2000);NBVAL($A$1:$H$1))
Cet exemple limite la base de données de la colonne A jusqu'à H, et jusqu'à 2000 lignes.
Cliquez sur le bouton sur OK pour valider.

Vous pouvez ensuite utiliser le nom pour définir l'emplacement des données (2ème étape lorsque vous créez le tableau croisé dynamique).
=NomPlage

IMPORTANT : Il doit impérativement y avoir des étiquettes dans toutes les colonnes spécifiées.

Pour utiliser la même formule sans limite de lignes et de colonnes :
=DECALER(Feuil1!$A$1;0;0;NBVAL(Feuil1!$A:$A);NBVAL(Feuil1!$1:$1))

Privilégiez l'utilisation des références absolues, sinon le résultat peut être très déconcertant.
La base de données ne doit pas contenir des Noms de champ vides.
Un enregistrement peut être vide, sauf dans la première colonne de gauche.
Il est préférable de démarrer la base de données dans la cellule A1.
Ne créez pas deux tableaux sur la même ligne.
Vous pouvez utiliser la même feuille pour plusieurs tables "mono-champs" (sur une colonne), en passant 1 comme cinquième paramètre de la fonction DECALER. Vous déterminez de cette manière une plage d'une seule colonne. Ainsi, si vous devez gérer des catégories de produits et des catégories de clients, par exemple, indiquez les catégories de produits en colonne A, les catégories de clients en colonne B, puis utilisez :
Catégories de produits =DECALER($A$1;1;0;NBVAL($A:$A)-1;1)
Catégories de clients =DECALER($B$1;1;0;NBVAL($B:$B)-1;1)

Il aussi envisageable de remplacer les plages nommées par les Listes (Excel 2003) et les Tableaux (Excel 2007).

Cet exemple applique la fonction Moyenne au champ "Somme de CA".

Sub AppliquerFonctionTCD()
 
Feuil2.PivotTables("Mon TCD").PivotFields("Somme de CA").Function = xlAverage
 
'Les autres constantes disponibles pour la propriété Function :
'xlAverage - Moyenne
'xlCountNums - Nb
'xlMin - Min
'xlStDev - Ecartype
'xlSum - Somme
'xlVar - Var
'xlCount - Nombre
'xlMax - Max
'xlProduct - Produit
'xlStDevP - Ecartypep
'xlUnknown - 
'xlVarP - Varp
 
End Sub

Vous pouvez utiliser la fonction LIREDONNEESTABCROISDYNAMIQUE.

Cet exemple renvoie la valeur correspondant à 'Somme ca 2002', pour le champ 'reference' égal à 35.
=LIREDONNEESTABCROISDYNAMIQUE($A$4;"35 'Somme ca 2002'")
Le résultat est 100 dans le tableau ci-dessous.

La même extraction par macro :

Sub requeteTCD()
    Dim Pvt As PivotTable
 
    'Définit le TCD dans la feuille
    Set Pvt = Worksheets("Résultats").PivotTables("Tableau croisé dynamique1")
 
    'Renvoie la valeur 'Somme ca 2002', pour le champ 'reference' égal à 35
    MsgBox Pvt.GetData("'Somme ca 2002' 'reference' '35'")
End Sub

Cette question revient souvent, suite au passage à Excel 2007.
En fait il suffit d'ajouter le bouton "Assistant tableau croisé dynamique" dans la barre d'outils accès rapide.

Cliquez sur le bouton "Office".
Cliquez sur le bouton "Options Excel".
Sélectionnez l'option "Personnaliser".
Dans le menu déroulant "Choisir les commandes dans les catégories...", sélectionnez l'option "Commandes non présentes dans le ruban".
Recherchez la commande "Assistant tableau croisé dynamique".
Sélectionnez la commande et cliquez sur le bouton "Ajouter" pour qu'il apparaisse dans la barre d'outils d'accès rapide.
Cliquez sur le bouton OK pour valider.

Vous pouvez ensuite utiliser l'assistant pour créer des TCD, depuis la barre d'outils d'accès rapide :
Sélectionnez "Plages de feuilles de calcul avec étiquettes".

En utilisant l'assistant de création des TCD (testé sous Excel 2007) :
Les données de chaque classeur sont supposées dans une plage nommée "PlageSource".
Etape 1, sélectionnez l'option "Plages de feuilles de calcul avec étiquettes".
Cliquez sur le bouton "Suivant" (2 fois jusqu'a l'étape 2b).

Vous pouvez sélectionner vos classeurs source en cliquant sur le bouton "Parcourir".
Complétez le nom de la plage nommée avant de cliquer sur le bouton "Ajouter", pour obtenir une syntaxe dans la style :
'C:\Documents and Settings\mimi\dossier\Classeur1.xls'!PlageSource
Sélectionnez les classeurs, complétez la plage nommée et cliquez sur le bouton "Ajouter" autant de fois que vous avez de classeurs sources.
Ensuite, cliquez sur le bouton "Suivant".
Précisez la cellule de destination pour le tableau croisé.
Cliquez sur le bouton "Terminer".

Le TCD qui s'affiche contient notamment un champ de page permettant de filtrer les classeurs sources.

La propriété SourceData renvoie une référence de style R1C1 (Feuil2!L1C1:L9C2).
Pour retrouver l'équivalence en référence xlA1 (Feuil2!$A$1:$B$9), utilisez le code suivant :

Option Explicit
Option Compare Text
 
Sub SourceTCD_Ref_xlA1_FR()
    Dim Pvt As PivotTable
    Dim Cible As String, NomFeuille As String
 
    'Définit le premier TCD de la feuille active.
    Set Pvt = ActiveSheet.PivotTables(1)
 
    'Extrait le nom de la feuille
    NomFeuille = Left(Pvt.SourceData, InStr(1, Pvt.SourceData, "!") - 1)
 
    'Pour gérer la version française d'Excel et remplacer le format xlL1C1 en xlR1C1.
    Cible = Replace(Mid(Pvt.SourceData, Len(NomFeuille) + 2), "L", "R")
 
    MsgBox NomFeuille & "!" & Replace(Application.ConvertFormula( _
                Formula:=Cible, _
                fromReferenceStyle:=xlR1C1, _
                toReferenceStyle:=xlA1, _
                toAbsolute:=xlAbsolute), _
                "[" & ThisWorkbook.Name & "]", "")
End Sub

Si vous utilisez une version anglaise d'Excel, vous pouvez écrire directement :

Sub SourceTCD_Ref_xlA1_EN()
    Dim Pvt As PivotTable
    Dim Cible As String
 
    'Définit le premier TCD de la feuille active.
    Set Pvt = ActiveSheet.PivotTables(1)
 
    Cible = Pvt.SourceData
 
    MsgBox Replace(Application.ConvertFormula( _
                Formula:=Cible, _
                fromReferenceStyle:=xlR1C1, _
                toReferenceStyle:=xlA1, _
                toAbsolute:=xlAbsolute), _
                "[" & ThisWorkbook.Name & "]", "")
End Sub

Un champ de page sert à afficher et filtrer des sous-groupes de données dans un tableau croisé dynamique.
Lors de la création, une zone "Déposez un champ de page ici" s'affiche en tête du TCD (Nota: dans Excel 2007, cette zone est nommée "Filtre du rapport").
Sélectionnez un champ, qui va servir de filtre, dans la liste et glissez-le dans cette zone.
Ensuite, à chaque fois que vous choisissez un élément dans la liste du champ de page, le tableau croisé est actualisé et affiche uniquement les données filtrées correspondantes.

Faites un clic droit dans le TCD.
Choisissez "Options du tableau croisé dynamique" dans le menu contextuel.
Sélectionnez l'onglet "Affichage".
Cochez l'option "Disposition classique du tableau croisé dynamique (glisser de champs dans la grille)".
Cliquez sur le bouton OK pour valider.

Sélectionnez la plage de cellules (par exemple : A2:A13)
Menu Edition
Atteindre (ou touche raccourci F5)
Cellules
Sélectionnez "Choisir Cellules Vides"
Tapez =A2
Validez par Ctrl+Entrée

Pour utiliser cette option dans Excel 2007 :
Sélectionnez la plage de cellules.
Sélectionnez l'onglet "Accueil".
Cliquez sur le bouton "Rechercher et sélectionner" dans le groupe "Edition".
Choisissez l'option "sélectionner les cellules".
Choisissez l'option "Cellules vides".
Cliquez sur le bouton OK pour valider.
Tapez =A2
Validez par Ctrl+Entrée

Lorsque vous faites référence à une cellule du rapport, la formule LIREDONNEESTABCROISDYNAMIQUE est créée automatiquement. Cette fonction permet d'extraire le contenu d'un TCD.

Pour désactiver cette option dans les versions antérieures d'Excel :

Dans la barre d'outils du Tableau croisé dynamique,
cliquez sur la flèche "Options de barre d'outils" (à l'extrémité droite de la barre d'outils).
Cliquez sur "Ajouter/Supprimer des boutons",
puis sélectionnez "Générer l'extraction de données croisées dynamiques".
Cliquez dans la feuille de calcul.
Cliquez sur le bouton "Générer l'extraction de données croisées dynamiques" qui apparaît à présent sur la barre d'outils Tableau croisé dynamique.
Lorsque vous utilisez le bouton, celui-ci prend l'attribut activé ou désactivé.
Si le bouton est désactivé, la formule LIREDONNEESTABCROISDYNAMIQUE n'est plus générée automatiquement.

Dans Excel 2007 :
Sélectionnez le TCD.
Sélectionnez l'onglet "Options" dans le ruban.
Cliquez sur le bouton "Options du tableau croisé dynamique".
Ouvrez le menu déroulant "Options".
Décochez l'option "Générer l'extraction de données croisées dynamiques".

Une autre solution pour désactiver cet outil dans Excel 2007 :
Cliquez sur le bouton "Office".
Cliquez sur le menu "Formules".
Décochez l'option "Utiliser les fonctions LIREDONNEESTABCROISDYNAMIQUE pour les références à des tableaux croisés dynamiques".
Cliquez sur le bouton OK pour valider.

La procédure fait apparaitre en couleur les mises à jour effectuées dans le TCD, lorsque vous utilisez le bouton "Actualiser".
Les données initiales sont stockées dans un tableau lors de l'ouverture du classeur. La procédure va comparer le contenu du tableau et le TCD à chaque réactualisation et personnaliser la couleur des cellules modifiées.

Remarque :
Dans cet exemple, tout le TCD change de couleur lorsqu'un champ ou une étiquette est supprimée ou ajoutée.

'--- Dans un module standard ---
Option Explicit
 
Public Tableau As Variant
'---

'--- Dans le module objet ThisWorkbook ---
 
Option Explicit
 
Private Sub Workbook_Open()
    Dim Pvt As PivotTable
 
    'Le TCD est dans la Feuil4
    Set Pvt = Worksheets("Feuil4").PivotTables("Tableau croisé dynamique1")
 
    Tableau = Pvt.TableRange1.Value
End Sub
'---

'--- Dans le module objet de la feuille contenant le TCD ---
Option Explicit
Option Base 1
 
'L'évènement est déclenché à chaque mise à jour du TCD
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim Cell As Range
    Dim i As Long
    Dim j As Integer
 
    i = Target.TableRange1.Cells(1, 1).Row - 1
    j = Target.TableRange1.Cells(1, 1).Column - 1
 
    'Vérifie si un champ ou une étiquette a été ajouté ou supprimé
    If UBound(Tableau, 2) <> Target.TableRange1.Columns.Count Or _
            UBound(Tableau, 1) <> Target.TableRange1.Rows.Count Then
 
        Erase Tableau
        Tableau = Target.TableRange1.Value
 
        'Tout le TCD change de couleur lorsqu'un champ ou une étiquette
        'est supprimé ou ajouté.
        Target.TableRange1.Cells.Interior.ColorIndex = 4
 
        Exit Sub
    End If
 
    'Boucle sur les cellules du rapport pour comparer le contenu du tableau et du TCD
    For Each Cell In Target.TableRange1
        'Si la donnée a été modifiée
        If Cell.Value <> Tableau(Cell.Row - i, Cell.Column - j) Then
            'Personnalise la couleur de la cellule
            Cell.Interior.ColorIndex = 4
            Else
            Cell.Interior.ColorIndex = 9
        End If
    Next Cell
 
    'Efface le contenu du tableau
    Erase Tableau
    'Stocke les nouvelles données dans le tableau
    Tableau = Target.TableRange1.Value
End Sub
'----

Dans Excel 2007, les limites de capacité des tableaux croisés dynamiques ont été repoussées et les paramètres de mises en forme améliorés. La puissance et les nombreuses options disponibles en font un outil incontournable pour l'analyse et la synthèse de vos données.

Consultez le tutoriel.

Lorsque vous utilisez un tableau croisé dynamique, Excel crée une copie masquée (mise en mémoire cache) de la source de données, afin de remplir les agrégations et les fonctions du rapport.
L'application peut ainsi effectuer les calculs rapidement dans les tableaux croisés dynamiques en utilisant ce cache, et en même temps maintenir l'intégrité des données d'origine.

Sous Excel 2007, il suffit de faire un double clic sur la dernière cellule de total, en bas à droite du TCD.
Le résultat s'affiche sous forme de table dans une nouvelle feuille de calcul.

Lorsque vous utilisez un cube OLAP pour créer un tableau croisé dynamique, il peut arriver que vous ayez un message d'erreur lors de l'actualisation :
"Les options de protection du client ne permettent pas que des instructions soient transmises directement à la source de données."

La solution consiste à modifier la base de registre :

Le complément OLAP PivotTable Extensions pour Excel 2007 ajoute des fonctions dans les tableaux croisés dynamiques utilisant des cubes OLAP comme source de données.
Une transaction est insérée dans le menu contextuel du TCD pour :
     Créer des champs calculés.
     Déclencher des requêtes MDX.
     Le complément dispose d'un outil de recherche dans le cube.

Téléchargez le complément OLAP PivotTable Extensions.

Après la mise à jour d'un tableau croisé dynamique, il peut arriver que des valeurs qui ne sont plus dans la source de données, continuent d'apparaitre dans la liste des éléments des champs.

Les paramètres Excel permettent de définir la manière dont sont conservés les éléments dans le cache.
Pour modifier ce paramètre :
Ouvrez la boîte de dialogue des options du TCD.
Onglet 'Données'.
Sélectionnez 'Aucun' dans le menu déroulant qui permet de définir le 'nombre d'éléments à retenir par champ'.
Cliquez sur le bouton OK pour valider.

Par macro :

Dim Pt As PivotTable
 
Set Pt = ActiveSheet.PivotTables(1)
Pt.PivotCache.MissingItemsLimit = xlMissingItemsNone