L’une des fonctionnalités les plus utiles d’un tableau croisé dynamique est la possibilité de combiner des éléments en groupes. Vous pouvez regrouper les éléments qui apparaissent dans la section Lignes ou Colonnes du volet Office Champs de tableau croisé dynamique. Excel propose deux manières de regrouper des éléments :
■ Manuellement : après avoir créé le tableau croisé dynamique, sélectionnez les éléments à regrouper, puis choisissez Outils de tableau croisé dynamique ,Analyser , Groupe ,Sélection de groupe. Ou vous pouvez sélectionner les éléments, cliquer avec le bouton droit de la souris et choisir Groupe dans le menu contextuel.
■ Automatiquement : si les éléments sont numériques (ou des dates), utilisez la boîte de dialogue Regroupement pour spécifier comment vous souhaitez regrouper les éléments. Sélectionnez un élément unique, puis choisissez Outils de tableau croisé dynamique ,Analyser ,Groupe ,Champ de groupe. Ou cliquez avec le bouton droit sur un seul élément et choisissez Groupe dans le menu contextuel. Dans les deux cas, la boîte de dialogue Grouper s’affiche. Utilisez cette boîte de dialogue pour spécifier comment regrouper les éléments.
REMARQUE
Si vous envisagez de créer plusieurs tableaux croisés dynamiques utilisant différents regroupements, assurez-vous de lire l’encadré “Plusieurs groupes à partir de la même source de données”.
Un exemple de regroupement manuel
La figure ci-dessous montre l’exemple d’un tableau croisé dynamique, avec deux groupes créés à partir des étiquettes de ligne. Pour créer le premier groupe, j’ai maintenu la touche Ctrl enfoncée tout en sélectionnant Arizona, Californie et Washington dans le tableau croisé dynamique. Ensuite, j’ai cliqué avec le bouton droit de la souris et choisi Groupe dans le menu contextuel. Ensuite, j’ai sélectionné les trois autres États et créé un deuxième groupe. J’ai remplacé les noms de groupe par défaut (Groupe 1 et Groupe 2) par des noms plus significatifs (Région de l’ Ouest et Région de l’Est).
Vous pouvez créer n’importe quel nombre de groupes et même créer des groupes de groupes.
Excel fournit un certain nombre d’options pour afficher un tableau croisé dynamique. Vous pouvez tester ces options lorsque vous utilisez des groupes. Ces commandes se trouvent dans l’onglet Outils de tableau croisé dynamique , Conception du ruban ou création. Il n’y a pas de règles pour choisir une option particulière. La clé est d’en essayer quelques-unes et de voir laquelle rend votre tableau croisé dynamique le meilleur. De plus, essayez diverses options dans l’onglet Outils de tableau croisé dynamique , Conception. Souvent, le style que vous choisissez peut grandement améliorer la lisibilité.
Cette figure montre des tableaux croisés dynamiques utilisant diverses options pour afficher les sous-totaux, les totaux généraux et les styles.
Exemples de regroupement automatique
Lorsqu’un champ contient des nombres, des dates ou des heures, Excel peut créer automatiquement des groupes. Les deux exemples de cette section illustrent le regroupement automatique.
Regroupement par date
Cette fifure montre une partie d’un tableau simple avec deux champs : Date et Ventes. Ce tableau comporte 731 lignes et couvre les dates entre le 1er janvier 2015 et le 31 décembre 2016. L’objectif est de résumer les informations sur les ventes par mois.
La figure ci-dessous montre une partie d’un tableau croisé dynamique créé à partir des données. Le champ Date se trouve dans la section Lignes et le champ Ventes se trouve dans la section Valeurs. Sans surprise, le tableau croisé dynamique ressemble exactement aux données d’entrée car les dates n’ont pas été regroupées.
Vous pouvez utiliser un tableau croisé dynamique pour résumer les données de ventes par mois.
Pour regrouper les éléments par mois, sélectionnez une date et choisissez Outils de tableau croisé dynamique ,Analyser ,Groupe , grouper le champ (ou cliquez avec le bouton droit et choisissez Groupe dans le menu contextuel). La boîte de dialogue Grouper, s’affiche.
Excel fournit des valeurs pour les champs Début à et Fin à. Les valeurs couvrent toute la plage de données et vous pouvez les modifier si vous le souhaitez.
Dans la zone de liste Par, sélectionnez Mois et Années et vérifiez que les dates de début et de fin sont correctes pour vos données. Cliquez sur OK.
Les éléments Date du tableau croisé dynamique sont regroupés par années et par mois.
REMARQUE
Si vous sélectionnez uniquement Mois dans la zone de liste Par de la boîte de dialogue Regroupement, les mois de différentes années se combinent. Par exemple, l’élément de janvier afficherait la somme des ventes pour 2015 et 2016.
Cette figure montre une autre vue des données, regroupées par trimestre et par année.
le tableau croisé dynamique montre les ventes par trimestre et par année.
Plusieurs groupes à partir de la même source de données
Si vous créez plusieurs tableaux croisés dynamiques à partir de la même source de données, vous avez peut-être remarqué que le regroupement d’un champ dans un tableau croisé dynamique affecte les autres tableaux croisés dynamiques. Plus précisément, tous les autres tableaux croisés dynamiques utilisent automatiquement le même regroupement. Parfois, c’est exactement ce que vous voulez. D’autres fois, ce n’est pas du tout ce que vous voulez. Par exemple, vous souhaiterez peut-être voir deux rapports de tableau croisé dynamique : un qui résume les données par mois et par année, et un autre qui résume les données par trimestre et par année.
Le regroupement affecte les autres tableaux croisés dynamiques car tous les tableaux croisés dynamiques utilisent le même “cache” de tableau croisé dynamique. Malheureusement, il n’existe aucun moyen direct de forcer un tableau croisé dynamique à utiliser un nouveau cache. Mais il existe un moyen d’inciter Excel à utiliser un nouveau cache. L’astuce consiste à donner plusieurs noms de plage aux données source.
Par exemple, nommez votre plage source Table1, puis donnez à la même plage un second nom : Table2. Le moyen le plus simple de nommer une plage consiste à utiliser la zone Nom, à gauche de la barre de formule. Sélectionnez la plage, saisissez un nom dans la zone Nom et appuyez sur Entrée. Ensuite, avec la plage toujours sélectionnée, tapez un nom différent et appuyez sur Entrée. Excel n’affichera que le prénom, mais vous pouvez vérifier que les deux noms existent en choisissant l’onglet Formules puis sur Définir les noms ensuite sur Gestionnaire de noms.
Lorsque vous créez le premier tableau croisé dynamique, spécifiez Table1 comme Table/Plage. Lorsque vous créez le deuxième tableau croisé dynamique, spécifiez Table2 comme table/plage. Chaque tableau croisé dynamique utilisera un cache distinct et vous pouvez créer des groupes dans un tableau croisé dynamique, indépendamment de l’autre tableau croisé dynamique.
Vous pouvez utiliser cette astuce avec des tableaux croisés dynamiques existants. Assurez-vous de donner un nom différent à la source de données. Sélectionnez ensuite le tableau croisé dynamique et choisissez Outils de tableau croisé dynamique ,Analyser , Données ,Modifier la source de données. Dans la boîte de dialogue Modifier la source de données du tableau croisé dynamique, tapez le nouveau nom que vous avez donné à la plage. Cela entraînera Excel pour créer un nouveau cache de pivot pour le tableau croisé dynamique.
Regroupement par temps
La figure ci-dessous montre un ensemble de données dans les colonnes A:B. Chaque ligne est une lecture d’un instrument de mesure, prise à des intervalles d’une minute tout au long d’une journée entière. Le tableau comporte 1 440 lignes, chacune représentant une minute. Le tableau croisé dynamique (dans les colonnes D:G) résume les données par heure.
Ce tableau croisé dynamique est regroupé par heures.
Voici les paramètres que j’ai utilisés pour ce tableau croisé dynamique :
■ La zone Valeurs comporte trois instances du champ Lecture et chaque instance affiche une méthode de synthèse différente (Moyenne, Minimum et Maximum). Pour modifier la méthode de synthèse d’une colonne, cliquez avec le bouton droit sur n’importe quelle cellule de la colonne et choisissez l’option Résumer les valeurs par, puis l’option appropriée.
■ Le champ Heure se trouve dans la section Lignes et j’ai utilisé la boîte de dialogue Regroupement pour regrouper par heures.
Création d’une distribution de fréquence
Excel fournit un certain nombre de façons de créer une distribution de fréquence, mais aucune de ces méthodes n’est plus simple que d’utiliser un tableau croisé dynamique.
La figure ci-dessous montre une partie d’un tableau de 221 étudiants et le résultat du test pour chacun. L’objectif est de déterminer combien d’élèves se trouvent dans chaque plage de dix points (1-10, 11-20, etc.).
Le tableau croisé dynamique est simple :
■ Le champ Score va se trouvé dans la section Lignes (groupées).
■ Une autre instance du champ Score vont se trouvée dans la section Valeurs (récapitulée par Nombre).
La boîte de dialogue Regroupement qui a généré les bacs spécifiait que les groupes commençaient à 1, se terminaient à 100 et étaient incrémentés de 10.
REMARQUE
Par défaut, Excel n’affiche pas les éléments avec un nombre de zéro. Dans cet exemple, aucun score de test n’est inférieur à 21, donc les éléments 1-10 et 11-20 ont été masqués lorsque j’ai créé le tableau croisé dynamique. Pour forcer l’affichage des bacs vides, cliquez avec le bouton droit sur n’importe quelle cellule et choisissez Paramètres de champ dans le menu contextuel. Dans la boîte de dialogue Paramètres de champ, cliquez sur l’onglet disposition et Mise en forme, puis sélectionnez pour les cellules vides, afficher.
La figure 34.12 montre la distribution de fréquence des résultats des tests, ainsi qu’un tableau croisé dynamique. J’ai filtré les scores afin que le tableau croisé dynamique (et le graphique) n’affichent pas la catégorie <1 et la catégorie >101.
Le tableau croisé dynamique et le graphique croisé dynamique montrent la distribution de fréquence pour les résultats des tests.
REMARQUE
Cet exemple utilise la boîte de dialogue Groupement d’Excel pour créer automatiquement les groupes. Si vous ne souhaitez pas regrouper dans des bacs de taille égale, vous pouvez créer vos propres groupes. Par exemple, vous pouvez attribuer des notes alphabétiques en fonction du résultat du test. Sélectionnez les lignes du premier groupe, cliquez avec le bouton droit, puis choisissez Groupe dans le menu contextuel. Répétez ces étapes pour chaque groupe supplémentaire. Remplacez ensuite les noms de groupe par défaut par des noms plus significatifs.
Regroupement des champs de date par semaine
La boîte de dialogue Grouper offre des choix pour regrouper par seconde, minute, heure, jour, mois, trimestre et année. Il est également possible de se regrouper à la semaine ou à la quinzaine.
La première étape consiste à trouver un calendrier papier ou un calendrier électronique, tel que la fonctionnalité Calendrier dans Outlook, pour l’année en question. Si vos données commencent le 1er janvier 2024, il est utile de savoir que le 1er janvier est un lundi cette année-là. Vous devez décider si les semaines doivent commencer le dimanche ou le lundi ou tout autre jour. Par exemple, vous pouvez consulter le calendrier papier ou électronique pour savoir que le dimanche de départ le plus proche est le 31 décembre 2023.
Sélectionnez n’importe quel en-tête de date dans votre tableau croisé dynamique. Sélectionnez ensuite Grouper le champ dans l’onglet Analyser le tableau croisé dynamique. Dans la boîte de dialogue Regroupement, désactivez toutes les options Par et sélectionnez uniquement le champ Jours. Cela active le bouton de rotation pour le nombre de jours. Pour produire un rapport par semaine, augmentez le nombre de jours de 1 à 7.
Ensuite, vous devez configurer la date de début. Si vous deviez accepter la valeur par défaut de commencer le 1er janvier 2015, toutes vos périodes hebdomadaires s’étendraient du jeudi au mercredi. En vérifiant un calendrier avant de commencer, vous savez que vous voulez que le premier groupe commence le 29 décembre 2014, pour avoir des semaines allant du lundi au dimanche.