Vous avez de grandes quantités de données et vous souhaitez les synthétiser dans un rapport ? Ou peut-être les analyser selon différents points de vue et critères de recherche ? Excel dispose d’un outil spécialement conçu pour ces tâches : le tableau croisé dynamique !
Lorsque vous travaillez avec de grandes quantités de données, il est courant de vouloir les synthétiser, que ce soit pour obtenir une vision globale et facilement compréhensible d’un ensemble de valeurs, ou pour étudier plus précisément un sous-ensemble répondant à des critères spécifiques.
Vous pourriez bien sûr prendre le temps de trier, filtrer et copier manuellement vos précieuses données dans un nouveau tableau qui répond à vos besoins. Cependant, chaque fois que vous ajouterez ou supprimerez des données à votre liste initiale, il y a un risque que votre tableau “oublie” ces modifications. De plus, si vous souhaitez regrouper et synthétiser vos données selon différents critères, vous devrez alors modifier manuellement la structure de votre tableau de destination, voire créer plusieurs tableaux de synthèse si vous souhaitez conserver différents points de vue sur vos données. Ces manipulations peuvent rapidement devenir fastidieuses et sources d’erreurs.
Heureusement, l’outil de tableau croisé dynamique d’Excel vous permet d’accomplir toutes ces tâches en seulement quelques clics, sans avoir à taper une seule formule. Dans ce guide, nous vous présenterons les différentes notions nécessaires à l’utilisation des tableaux croisés dynamiques. Pour vous accompagner, nous mettons à votre disposition un classeur exemple, que vous pouvez télécharger gratuitement [ici](lien de téléchargement). Toutes les manipulations décrites dans ce guide s’appuient sur ce fichier, nous vous recommandons donc vivement de l’utiliser en même temps que votre lecture.
Pourquoi utiliser un tableau croisé dynamique ?
Vous travaillez sur une importante liste de données, par exemple un journal des ventes, et vous souhaitez rapidement connaître la somme des éléments de cette liste en les regroupant selon plusieurs critères, par exemple par catégorie de produit et mois de vente. Dans ce cas, l’utilisation d’un tableau croisé dynamique vous permettra d’obtenir instantanément ce résultat, de choisir la disposition des données synthétisées et de modifier cette disposition à chaque besoin. L’exemple ci-dessous illustre comment un tableau croisé dynamique synthétise des données et les organise visuellement.
On parle de tableau “croisé” car les catégories utilisées pour regrouper les données peuvent être réparties à la fois en lignes et en colonnes. Sur l’exemple de tableau croisé dynamique en haut à droite de l’image, la cellule jaune au “croisement” des champs “Janvier” et “Catégorie 1” renvoie la somme de tous les éléments de la liste de données initiale qui contenaient les valeurs “Janvier” et “Catégorie 1” dans les colonnes “Mois” et “Catégorie”. Dans le cas présent, un seul élément répond à ces deux critères, mais la liste de données pourrait contenir des milliers d’éléments traités automatiquement par le tableau croisé dynamique, sans avoir besoin de trier ou de filtrer les données au préalable.
On parle également de tableau “dynamique” car, d’une part, sa disposition peut être modifiée instantanément via de simples manipulations à la souris ou au clavier, et d’autre part, toute modification des données dans la liste initiale est automatiquement prise en compte dans le résultat des regroupements.
Comment créer un tableau croisé dynamique avec Excel ?
Avant de générer un tableau croisé dynamique, vous devez vous assurer que les données de votre liste initiale respectent les prérequis suivants :
- Les données d’une même colonne doivent toutes être du même type (nombre, date, texte, etc.).
- La première ligne de votre liste de données doit contenir les en-têtes de colonnes.
- Votre liste ne doit pas contenir de cellules fusionnées.
- Votre liste ne doit pas contenir de cellules vides (Excel est capable de reconnaître et de gérer les cellules vides le cas échéant, mais il est préférable de les éviter autant que possible).
Une fois ces conditions remplies, vous pouvez vous lancer dans la création de votre tableau croisé dynamique.
Commencez par cliquer quelque part dans la liste de données, puis dans l’onglet Insertion du ruban, cliquez sur Tableau croisé dynamique.
Dans la boîte de dialogue Créer un tableau croisé dynamique, vérifiez que le champ Tableau/Plage contient bien les références de la liste de données. Par défaut, si vous avez cliqué quelque part dans la liste et qu’elle ne contient ni cellule vide ni cellule fusionnée, Excel aura correctement déterminé sa taille. Si ce n’est pas le cas, cliquez sur l’icône de flèche vers le haut à droite du champ Tableau/Plage et sélectionnez manuellement l’ensemble de la liste de données.
Dans la section Choisissez l’emplacement de votre rapport de tableau croisé dynamique, vérifiez que la case Nouvelle feuille de calcul est bien cochée. Vous pouvez également cocher la case Feuille de calcul existante et sélectionner manuellement un emplacement dans la même feuille que la liste de données, mais la lisibilité de l’ensemble risque d’être moins bonne.
Validez la création du tableau croisé dynamique en cliquant sur le bouton OK de la boîte de dialogue Créer un tableau croisé dynamique.
Une nouvelle feuille de calcul est insérée par Excel dans le classeur, et un volet nommé Champs de tableau croisé dynamique apparaît à droite de votre écran.
Le volet Champs de tableau croisé dynamique est composé de cinq cadres. Le premier vous présente la liste des champs de données disponibles, qui correspondent aux en-têtes de colonnes de la liste de données initiale. Les quatre autres cadres représentent les zones du tableau croisé dynamique : c’est en déplaçant les champs de données disponibles dans les différentes zones que vous allez créer la disposition du tableau croisé dynamique.
Dans la liste des champs de données disponibles, cochez les cases à gauche des mots Région, Produit et Ventes. Un tableau croisé dynamique apparaît dans votre feuille de calcul et, si vous examinez les quatre cadres inférieurs, vous verrez que les mots Région et Produit ont été ajoutés dans la zone Lignes et que les mots Somme de Ventes sont apparus dans la zone Valeurs.
À ce stade, vous avez créé un tableau croisé dynamique qui regroupe vos données par région puis par nom de produit et présente un sous-total pour chaque catégorie, ainsi qu’un total général en bas du tableau. Vous constaterez également que les catégories du tableau croisé dynamique sont agencées dans le même ordre que les champs de données dans le cadre Lignes du volet Champs de tableau croisé dynamique. Retenez ce principe, car il est essentiel pour organiser efficacement un tableau croisé dynamique.
Dans le cadre Lignes du volet Champs de tableau croisé dynamique, cliquez sur la flèche grise à droite du nom Région et, dans le menu contextuel qui apparaît, cliquez sur Descendre, ou faites un clic-gauche maintenu sur le nom Région et faites-le descendre sous le nom Produit avant de relâcher le bouton de la souris.
Dans le cadre Lignes, le nom Produit est désormais en première position et le nom Région en deuxième. Du côté du tableau croisé dynamique, vous constaterez que l’ordre des regroupements et des sous-totaux de données s’est également inversé.
Cette manipulation simple vous donne un aperçu de la puissance et de la souplesse d’un tableau croisé dynamique. En quelques secondes, vous avez obtenu un tout nouveau point de vue sur vos données sans avoir eu besoin de les trier ou de les réorganiser dans la liste initiale.
Dans le volet Champs de tableau croisé dynamique disponible à droite de votre écran, vous pouvez déplacer les catégories de données disponibles vers différentes zones pour obtenir différents regroupements et sous-totaux. Par exemple, vous pouvez déplacer la catégorie Mois de la zone Lignes vers la zone Colonnes pour afficher les données synthétisées par mois dans l’axe horizontal du tableau croisé dynamique.
Continuez à explorer les différentes options de l’outil de tableau croisé dynamique en déplaçant les champs de données disponibles dans les différentes zones et en observant les résultats obtenus dans le tableau croisé dynamique. Vous remarquerez que les changements sont automatiquement reflétés dans le tableau croisé dynamique, sans que vous ayez à taper une seule formule ou à restructurer votre liste de données initiale.
Comment mettre en forme un tableau croisé dynamique avec Excel ?
Pour rendre votre tableau croisé dynamique plus facile et agréable à lire, surtout s’il contient un grand nombre de catégories et de données, Excel offre de nombreuses options de mise en forme. Vous pouvez choisir une disposition différente pour les catégories, des formats de nombres personnalisés pour les données de la zone des valeurs, des couleurs pour les en-têtes et les bordures, et bien d’autres choses encore.
Comment modifier le format des nombres des valeurs ?
Vous remarquerez que les données de la zone Valeurs du tableau croisé dynamique apparaissent comme des nombres à deux décimales, sans séparateur de milliers, ce qui peut ne pas être très lisible pour des valeurs monétaires. Vous pouvez choisir d’afficher ces données dans n’importe quel format de nombre disponible dans Excel.
- Dans le volet Champs de tableau croisé dynamique, dans la zone Valeurs, cliquez sur le nom Somme de Ventes puis sur Paramètres des champs de valeurs.
- Dans la boîte de dialogue Paramètres des champs de valeurs, cliquez sur le bouton Format de nombre.
- La boîte de dialogue Format de cellule s’ouvre. Vous pouvez y sélectionner n’importe quel format de cellule existant dans Excel ou créer un format personnalisé en cliquant sur la catégorie Personnalisé. Dans le cas présent, cliquez sur la catégorie Monétaire puis validez votre choix en cliquant sur le bouton OK.
De retour dans la boîte de dialogue Paramètres des champs de valeurs, cliquez sur le bouton OK en bas à droite pour appliquer votre modification au tableau croisé dynamique. Les données de la zone Valeurs sont désormais affichées comme des valeurs monétaires, avec un séparateur de milliers, deux décimales et le symbole €.
Comment modifier la largeur des colonnes ?
Lors de la création d’un tableau croisé dynamique, la largeur des colonnes s’adapte automatiquement au contenu des cellules, ce qui peut ne pas donner le résultat souhaité si vous souhaitez, par exemple, que toutes les colonnes aient la même largeur. Heureusement, il est très facile de modifier manuellement la largeur des colonnes et de la conserver lors des mises à jour du tableau croisé dynamique.
Pour donner la même largeur à plusieurs colonnes, sélectionnez celles que vous souhaitez modifier en cliquant sur leur étiquette (les lettres A, B, C, D, etc. situées au-dessus de la première ligne de la feuille de calcul). Par exemple, faites un clic-gauche maintenu sur l’étiquette de la colonne B, déplacez votre curseur jusqu’à l’étiquette de la colonne E, puis relâchez le bouton gauche de la souris. Toutes les colonnes de B à E sont alors sélectionnées.
Ensuite, déplacez votre souris sur la ligne de séparation entre l’étiquette des colonnes E et F jusqu’à ce que le curseur se transforme en double flèche noire. Faites alors un clic-gauche maintenu et déplacez votre curseur vers la droite pour augmenter la taille des colonnes ou vers la gauche pour la réduire. Lorsque vous relâchez le bouton gauche de la souris, toutes les colonnes précédemment sélectionnées adoptent la même largeur.
Il est également possible de modifier la largeur des colonnes en faisant un clic-droit sur une cellule du tableau croisé dynamique, puis en sélectionnant Largeur de colonne dans le menu contextuel. Une boîte de dialogue Largeur de colonne s’ouvre, dans laquelle vous pouvez entrer directement un nombre pour définir la largeur des colonnes sélectionnées.
Une fois que vous avez attribué les largeurs souhaitées à vos colonnes, il est nécessaire de passer aux options du tableau croisé dynamique pour que ces paramètres soient conservés lors de la mise à jour. Par défaut, Excel ajuste automatiquement la largeur des colonnes à leur contenu lors de l’actualisation du tableau croisé dynamique. Heureusement, cette option peut être modifiée en quelques clics.
- Dans la feuille de calcul, cliquez n’importe où à l’intérieur du tableau croisé dynamique. Cela fait apparaître l’onglet Analyse du tableau croisé dynamique dans le ruban. Cliquez dessus, puis sur le menu Options du tableau croisé dynamique, et enfin sur le bouton Options.
Comment modifier le style et la disposition du tableau croisé dynamique ?
Excel offre de nombreuses possibilités pour modifier la façon dont le tableau croisé dynamique organise visuellement vos données. Vous pouvez choisir une disposition différente pour les catégories, des formats de nombres personnalisés pour les données de la zone des valeurs, des couleurs pour les en-têtes et les bordures, et bien d’autres choses encore.
Pour afficher les sous-totaux de chaque colonne, commencez par sélectionner la plage correspondante. Cliquez ensuite sur le menu Données du ruban, puis sur le bouton Sous-total dans la section Outils de tableau croisé dynamique.
Dans la boîte de dialogue Sous-totaux qui s’ouvre, vérifiez que la sélection automatique proposée est correcte. Si nécessaire, vous pouvez ajuster la plage de données manuellement.
Choisissez les opérations de sous-total souhaitées pour chaque colonne, telles que la somme, la moyenne ou le comptage des valeurs. Cliquez sur le bouton OK pour fermer la boîte de dialogue Sous-totaux.
Les sous-totaux sont automatiquement ajoutés à la plage sélectionnée, en fonction des opérations que vous avez choisies. Vous pouvez désormais développer ou masquer les sous-totaux de chaque colonne en cliquant sur les symboles + ou – à gauche des en-têtes de colonnes.
