Les utilisateurs utilisent rarement les tableaux croisés dynamiques Excel en raison de leur complexité apparente. Cependant, les efforts et le temps qui seront consacrés à leur apprentissage seront extrêmement rentables grâce à l’utilisation de cet outil. Les tableaux croisés dynamiques sont souvent indispensables lors de l’analyse des données de très grandes tables sous divers aspects. Ainsi, les tableaux croisés dynamiques doivent être utilisés s’il existe un registre (résumé, déclaration, etc.) avec des centaines de lignes et que vous devez analyser les données avec leur sélection et regroupement et présenter ces données dans toutes sortes de sections pour créer des rapports.
Préparation de la table source
Créer des tableaux croisés dynamiques dans Excel
Comment ajouter des champs à un tableau croisé dynamique
Trier le tableau croisé dynamique
Filtrer dans le tableau croisé dynamique
Formules dans les tableaux croisés dynamiques
Champs calculés dans les tableaux croisés dynamiques
Objets calculés dans les tableaux croisés dynamiques
Masquer les sous-totaux et les totaux généraux du tableau croisé dynamique
Préparation de la table source
Avant de commencer à créer votre tableau croisé dynamique, vous devez effectuer quelques travaux sur le tableau d’origine. Le fait est que toutes les structures de données ne conviennent pas pour créer un tableau croisé dynamique basé sur elles.
- La table source ne doit pas avoir de colonnes vides vides. Il est impossible de générer un tableau croisé dynamique correct si au moins une colonne de la source n’est pas intitulée.
- Le tableau d’origine ne doit pas contenir de cellules vides dans la ligne de valeurs, sinon le tableau croisé dynamique comptera le nombre, pas la somme des valeurs de cellule.
- La table source ne doit pas avoir de lignes ou de colonnes vides. Sinon, lors de la création d’un tableau croisé dynamique, seules les données précédant la première colonne vide ou la première ligne vide seront prises en compte. Si vous souhaitez rendre la colonne vide, laissez l’en-tête de colonne visible et donnez au texte des cellules la même couleur que l’arrière-plan des cellules.
- Chaque colonne doit contenir des données du même format. Par exemple, une colonne avec des données au format texte ne doit pas contenir de données de type DATE.
- Créez une colonne distincte pour les données du même type qui seront placées dans le tableau croisé dynamique. Par exemple, vous pouvez créer les colonnes “Mois” et “Année”. Cela vous permettra d’afficher les données globales dans le tableau croisé dynamique soit par mois, soit par année.
- Il est recommandé de placer le tableau avec les données source sur une feuille du classeur Excel et le tableau croisé dynamique sur une autre. Dans tous les cas, la table d’origine doit être séparée du reste des données, par exemple les autres tables, par au moins une colonne et au moins une ligne.
- Si la table source contient des colonnes masquées, assurez-vous qu’elles ont toutes des en-têtes.
- Vous devez supprimer les totaux de la table source. Sinon, Excel assumera le résultat final comme la valeur de cellule de la colonne spécifiée et les données du tableau croisé dynamique seront doublées.
Une fois que la source de données a été revue et ajustée si nécessaire, vous pouvez commencer à créer le tableau croisé dynamique.
Créer des tableaux croisés dynamiques dans Excel
Le tableau croisé dynamique est un outil pour une analyse facile des données. Le processus de création est le plus simple à expliquer avec un exemple. Ainsi, il existe un tableau des ventes de biens indiquant le nom du produit, la date de vente et le montant pour lequel tel ou tel produit a été vendu. 
- Sélectionnez le tableau entier, une seule cellule ou une plage de cellules, en fonction des informations à analyser.
- Allez dans l’onglet “Insert” et cliquez sur le bouton “PivotTable”.
- Dans la fenêtre “Create PivotTable” qui s’affiche, spécifiez la source de données. Si le tableau ou les cellules ont déjà été sélectionnés, vous n’avez rien à spécifier. Dans la même fenêtre, vous devez spécifier où le tableau croisé dynamique sera affiché – sur la même feuille, ou sur une nouvelle feuille.
- Après avoir cliqué sur “OK”, une disposition de tableau croisé dynamique vide sera créée.
- Sur le côté droit de la feuille, il y a un bloc avec une liste de champs. Dans ce bloc, sélectionnez les champs à inclure dans le rapport. Les champs peuvent également être déplacés avec la souris vers la partie souhaitée du tableau croisé dynamique.
- Deux nouveaux onglets apparaîtront dans le menu du haut: “Options” et “Design”.
Une fois que la disposition initiale du tableau croisé dynamique a été créée, vous devez commencer à la personnaliser afin d’obtenir le rapport du type souhaité.
Comment ajouter des champs à un tableau croisé dynamique
Les champs du tableau croisé dynamique sont ajoutés dans le bloc “PivotTable Field List”.
Si les champs de ce bloc sont simplement cochés, Excel les insérera dans le tableau conformément à son algorithme de présentation. Les champs non numériques sont ajoutés à la zone Lignes, les champs numériques sont ajoutés à la zone Valeurs et la date et l’heure sont ajoutées à la zone Colonnes. 
Pour placer un champ dans la zone souhaitée du tableau, faites un clic droit sur son nom et sélectionnez l’une des sections: “Add to Report Filter”, “Add to Row Labels”, “Add to Column Labels” ou “Add to Values”.
Les champs sélectionnés peuvent être échangés en les faisant glisser avec la souris dans la section des zones. 
La section de zone se compose de 4 blocs.
- Report Filter. Ce bloc spécifie les filtres nécessaires pour les valeurs du tableau croisé dynamique.
- Column Labels. Cette section place les valeurs spécifiées du champ sélectionné dans des colonnes. En règle générale, ce sont des valeurs qualitatives plutôt que quantitatives.
- Line names. Ce sont les noms de ligne de la colonne la plus à gauche. Vous pouvez créer un tableau croisé dynamique à plusieurs niveaux en plaçant plusieurs champs dans cette zone. Les valeurs qualitatives sont généralement placées ici aussi, par exemple, année ou mois, nom du produit, etc.
- Values. C’est la partie principale du tableau croisé dynamique. Voici les valeurs qui seront obtenues par la méthode sélectionnée. Le plus souvent, les résultats de sommation sont placés ici.
Trier le tableau croisé dynamique
Comme avec n’importe quel tableau Excel, un tableau croisé dynamique peut être trié. Cette sélection peut se faire soit par ligne, soit par colonne. Vous pouvez distribuer les données par ordre alphabétique ou par ordre croissant et décroissant.
Afin de trier les données, vous devez sélectionner leur plage, puis aller dans l’onglet “Data” du menu principal.
Pour distribuer les données dans la direction souhaitée, appuyez sur les flèches haut ou bas dans le bloc “Sort & Filter”. Il est également recommandé d’utiliser le bouton “Sort”. Après avoir cliqué dessus, une fenêtre contextuelle avec les paramètres de tri des données apparaîtra. Il existe un bouton similaire dans l’onglet “Design” du bloc “PivotTable Tools”.
Il existe une autre façon de trier les données dans un tableau croisé dynamique. S’il y a un bouton triangulaire dans les cellules “Row Labels” ou “Column Labels”, vous pouvez cliquer dessus et faire apparaître le menu contextuel. Dans celui-ci, sélectionnez le paramètre de tri souhaité et cliquez sur “OK”.
S’il y a des cellules avec des espaces de début dans la plage en cours de tri, cela peut affecter les résultats du tri. Ces espaces doivent être supprimés au préalable.
Il n’est pas possible de trier les données en fonction de la casse de la langue définie, de trier par couleur d’arrière-plan de cellule, police sélectionnée et autres options de formatage.
Les tableaux croisés dynamiques ont la possibilité de trier les cellules manuellement. Pour ce faire, cliquez sur une cellule de la colonne à trier. Cliquez ensuite sur le triangle dans le champ “Row Labels” et sélectionnez “More Sort Options”.
Une fenêtre apparaîtra avec un choix de différents paramètres.
Dans la même fenêtre, vous devez sélectionner le paramètre “Manual” et trier vous-même les cellules en les faisant glisser vers l’emplacement souhaité. Les exceptions sont les champs de la plage. Vous ne pouvez pas les faire glisser.
Dans la même boîte de dialogue, vous pouvez cliquer sur le bouton “More Options” et définir des options de tri supplémentaires.
Ici, vous pouvez activer ou désactiver le tri automatique des données après chaque mise à jour de la table, ainsi que définir d’autres paramètres de tri.
Filtrer dans le tableau croisé dynamique
Un tableau croisé dynamique peut avoir un grand nombre de colonnes et de lignes. Parfois, ces informations sont redondantes et vous ne devez extraire qu’une partie de ce tableau de données sans modifier le contenu du tableau croisé dynamique. Les filtres offrent cette opportunité. Les informations affichées à l’aide de filtres peuvent être modifiées à tout moment et pour différents utilisateurs. Dans ce cas, le tableau croisé dynamique reste inchangé, seule la visibilité des données qu’il contient change.
Pour mettre un filtre sur un tableau croisé dynamique, procédez comme suit.
- Ouvrez le tableau croisé dynamique et recherchez le bloc “Report Filter” dans le coin inférieur droit.
- Dans la liste des champs de tableau croisé dynamique, faites glisser l’en-tête de champ requis vers le bloc de filtre.
- Une liste de tous les champs sur lesquels le filtre est défini apparaîtra au-dessus du tableau croisé dynamique. Cliquez sur le bouton triangulaire à côté du nom du champ et sélectionnez l’option de filtre souhaitée.
- Cliquez sur “OK”.
Vous pouvez définir un filtre sur plusieurs éléments de tableau. Par exemple, filtrez le nombre de ventes pour les dates spécifiées.
Si vous cliquez sur le triangle à côté du nom du champ dans le tableau croisé dynamique, un menu contextuel apparaît, qui contient les éléments “Label Filters”, “Value Filters”, “Date Filters”.
Ils organisent des groupes d’enregistrements qui correspondent à certains critères. En utilisant des filtres par signature, vous pouvez filtrer les données selon des critères spécifiés de la valeur de texte. Par exemple, dans la liste des articles de papeterie, sélectionnez les produits contenant le mot «crayon» dans leur nom. Ici, vous pouvez également spécifier des caractères génériques dans les paramètres de filtre.
Les filtres de valeur ordonnent les données numériques. Par exemple, en les utilisant, vous pouvez afficher le même type de produits avec les ventes les plus élevées.
Les filtres de date ne peuvent être sélectionnés que pour les champs de format de date. Cette section propose une large sélection de filtres de données basés sur une date spécifique. Par exemple, “Next Month”, “Last Quarter”, “This Week”. Vous pouvez filtrer les données dans une plage de dates.
Vous pouvez définir plusieurs filtres pour le même champ de tableau croisé dynamique.
Formules dans les tableaux croisés dynamiques
Les tableaux croisés dynamiques offrent non seulement la possibilité d’afficher des données dans différentes sections. Vous pouvez également effectuer divers calculs sur ces tableaux à l’aide de formules Excel. Par exemple, à l’aide de ces calculs, vous pouvez calculer le revenu moyen d’une entreprise par jour ou calculer le pourcentage des ventes de produits pour différentes régions. Les tableaux croisés dynamiques utilisent les concepts “Calculated Field” et “Calculated Item” lors de l’exécution de ces calculs.
Champs calculés dans les tableaux croisés dynamiques
Un champ calculé est créé en fonction des calculs effectués sur les champs de tableau croisé dynamique existants. La colonne avec les champs calculés n’est pas incluse dans les données source et contient les résultats des calculs à l’aide de formules Excel. La colonne contenant les données récupérées par le champ calculé devient une partie du tableau croisé dynamique et les données peuvent interagir avec les données du tableau croisé dynamique.
L’insertion de champs calculés dans un tableau croisé dynamique plutôt que dans une source de données est souvent la meilleure solution. Avec cette méthode de calcul, vous n’avez pas besoin d’ajuster les formules lors des modifications de la source de données, car le tableau croisé dynamique est automatiquement ajusté. En outre, dans ce cas, vous pouvez modifier la structure de la table d’origine ou modifier les champs de données utilisés dans les calculs, tout en vous assurant qu’il n’y a aucune erreur dans les formules.
Les calculs dans les champs calculés sont toujours effectués sur des totaux et non sur des éléments de données individuels.
Comment créer un champ calculé?
- Cliquez sur le tableau croisé dynamique pour activer les onglets “PivotTable Tools”.
- Sous l’onglet “Options”, cliquez sur le bouton “Formulas”.
- Ouvrez la section “Calculated Field”.
- Dans la fenêtre “Insert Calculated Field” qui apparaît, spécifiez le nom du champ calculé et écrivez la formule requise pour ce champ.
- Cliquez sur “OK”.
Par défaut, le champ “Formula” contient l’expression “= 0”. Avant d’écrire la formule, le zéro doit être supprimé. Lors de la création d’une formule, ses éléments sont sélectionnés dans le bloc “Fields”. Lorsque vous créez un champ calculé, la colonne supplémentaire n’apparaît pas dans la source de données. Ces champs imitent simplement les champs explicitement spécifiés de la table d’origine.
Les paramètres du champ calculé que vous créez peuvent être modifiés de la même manière que pour les autres éléments du tableau croisé dynamique. Vous pouvez modifier le nom du champ, la couleur, la police, etc. Bien que la formule utilisée pour créer un champ calculé soit similaire à une formule Excel standard, il existe une différence fondamentale entre les deux. Une formule Excel standard utilise des références de cellule fortes ou des valeurs numériques, tandis que les formules dans les tableaux croisés dynamiques utilisent des références de champ de données de tableau croisé dynamique pour calculer.
Objets calculés dans les tableaux croisés dynamiques
Un objet calculé est essentiellement une ligne virtuelle qui est ajoutée à une table en effectuant des calculs sur d’autres lignes dans le même champ. Par exemple, il peut s’agir des ventes moyennes calculées pour une certaine période. Vous pouvez souvent obtenir le même résultat en regroupant les données.
Comment créer un objet calculé?
- Cliquez sur n’importe quelle cellule du champ sélectionné et allez dans l’onglet “Options” dans le bloc “PivotTable Tools”.
- Cliquez sur le bouton “Formulas” et sélectionnez la section “Calculated Item”.
- Dans la fenêtre qui apparaît, écrivez le nom de l’objet calculé, sélectionnez les champs et éléments requis, établissez une formule de calcul.
- Après avoir cliqué sur “OK”, les lignes avec l’élément calculé sont ajoutées au tableau croisé dynamique.
Notez que les objets calculés peuvent affecter les sous-totaux et les totaux généraux dans le tableau croisé dynamique. Masquez ces éléments de données pour obtenir des totaux corrects. En outre, lors du calcul des champs et des objets calculés, vous ne pouvez pas utiliser de références de cellule ou de plages nommées. Par conséquent, vous ne pouvez pas utiliser des fonctions qui prennent des références de cellule ou des objets nommés comme argument. En même temps, vous pouvez utiliser librement les fonctions COUNT, AVERAGE, IF, AND, NO, OR. Toutes les constantes peuvent être utilisées dans les calculs.
Les formules de tableau croisé dynamique doivent prendre en compte l’ordre des calculs et la priorité de certaines opérations par rapport à d’autres.
Vous pouvez modifier ou même supprimer des formules que vous créez dans des champs ou des objets calculés. Pour ce faire, cliquez sur l’un des champs du tableau croisé dynamique, cliquez sur le bouton “Formulas” et allez dans la section “Calculated field”. Cliquez sur la flèche à côté du nom du champ et faites apparaître une liste de tous les champs calculés.
Sélectionnez le champ dont vous souhaitez modifier la formule et corrigez ou supprimez la formule. Dans les objets calculés, les formules sont modifiées et supprimées de la même manière.
Masquer les sous-totaux et les totaux généraux du tableau croisé dynamique
Si le tableau croisé dynamique contient des sommes de valeurs, il affichera automatiquement les sous-totaux et les totaux généraux. Vous pouvez masquer ces totaux et les afficher à nouveau plus tard si nécessaire. Pour afficher ou masquer les deux types de totaux, cliquez n’importe où dans le tableau croisé dynamique et dans le bloc “PivotTable Tools”, ouvrez l’onglet “Design”. Cliquez ensuite sur le bouton “Subtotals”. Dans le menu qui s’ouvre, vous pouvez choisir d’afficher ou non les sous-totaux.
À côté du bouton “Subtotals” se trouve le bouton “Grand Totals”.
À l’aide du menu contextuel de ce bouton, vous pouvez activer ou désactiver l’affichage des totaux généraux.
Ainsi, alors que les tableaux croisés dynamiques sont l’un des outils les plus puissants pour analyser les informations des feuilles de calcul Excel, ils sont également l’un des outils les plus sous-estimés de cette application. Les tableaux croisés dynamiques vous permettent non seulement de résumer rapidement, mais offrent également la possibilité de changer instantanément la façon dont vous analysez en faisant glisser et en déposant des champs d’une zone à une autre. Pour maîtriser cet outil, il suffit de ne consacrer que peu de temps à la théorie et à la pratique.
Les tableaux croisés dynamiques présentent l’analyse des données brutes en un coup d’œil. Il est souvent indésirable pour des personnes non autorisées de voir ces informations, il est donc recommandé de mettre une protection sur ces documents. Cette règle importante ne doit pas être négligée. Même si vous oubliez le chiffrement, vous pouvez retirer mot de passe Excel oublié à l’aide de programmes spéciaux.