Comment regrouper des données dans un tableau croisé dynamique Excel. Exemples de regroupement de champs de dates, de nombres et de texte. Comment résoudre les problèmes de regroupement et éviter les problèmes.
Regroupement des données
Dans un tableau croisé dynamique, vous pouvez regrouper des dates, des nombres et des champs de texte. Par exemple, regroupez les dates de classement par année et par mois, ou regroupez les résultats des tests par tranches de 10. Vous pouvez sélectionner manuellement des éléments de texte dans un champ de tableau croisé dynamique et regrouper les éléments sélectionnés. Cela vous permet de voir rapidement les sous-totaux pour un ensemble spécifique déléments dans votre tableau croisé dynamique.
Cette vidéo montre comment regrouper les dates par mois et par année, et comment regrouper manuellement des éléments de texte. Il y a des étapes écrites sous la vidéo.
Regroupement automatique des dates
Dans Excel 2016 et les versions ultérieures, les dates sont automatiquement regroupées lorsque vous ajoutez un champ de date à un tableau croisé dynamique. Vous pouvez annuler le regroupement dans Excel 2016 et désactiver cette fonctionnalité dans les versions ultérieures.
Désactiver le regroupement automatique des dates
Dans Excel 2019 et Excel 365, vous pouvez modifier une option Excel pour activer ou désactiver cette fonction de regroupement de dates
REMARQUE: Il sagit dun paramètre au niveau de lapplication et affectera tous vos classeurs Excel.
Pour activer la fonction de regroupement automatique de dates off:
- Sur le ruban, cliquez sur longlet Fichier, puis sur Options
- À gauche, cliquez sur la catégorie Données
- À la fin de dans la section Options de données, cochez la case « Désactiver le regroupement automatique des colonnes Date / Heure dans les tableaux croisés dynamiques »
- Cliquez sur OK pour appliquer les nouveaux paramètres.
Annuler le regroupement de dates
Pour toutes les versions (Excel 2016 et versions ultérieures), vous pouvez dissocier manuellement les dates, avec lune de ces méthodes :
Raccourci clavier
- Ajoutez le champ Date au tableau croisé dynamique
- Appuyez immédiatement sur Ctrl + Z pour annuler le regroupement automatique des dates
- Les champs groupés sont supprimés et le champ Date dorigine reste dans le tableau croisé dynamique
Commande Dissocier
- Ajoutez le champ Date au tableau croisé dynamique
- À tout moment, cliquez avec le bouton droit de la souris sur lun des éléments ou en-têtes du champ de date
- Dans le menu contextuel, cliquez sur Dissocier
- Les champs groupés sont supprimés et le champ Date dorigine reste dans le tableau croisé dynamique
Dates de regroupement
Dans un tableau croisé dynamique, il existe plusieurs dans les options de regroupement des dates – Secondes, Minutes, Heures, Jours, Mois, Trimestres et Années.
Vous pouvez sélectionner une ou plusieurs de ces options pour créer le type de regroupement de dates dont vous avez besoin. Les exemples ci-dessous montrent comment regrouper les dates:
– Par mois et année
– Par semaine
– Par période de 4 semaines
Regrouper les dates par mois et par année
Pour regrouper les éléments dans un champ de date
- Cliquez avec le bouton droit de la souris sur lune des dates du tableau croisé dynamique.
- Dans le menu contextuel, cliquez sur Groupe
- Dans la boîte de dialogue Regroupement, sélectionnez une ou plusieurs options dans la liste « Par ».
- Pour limiter les dates qui sont regroupées, vous pouvez définir une date de début et de fin, en tapant les dates dans les cases « Début à » et « Fin à »
- Cliquez sur OK pour fermer la boîte de dialogue.
Dates de groupe par semaine
Pour regrouper les éléments dun champ Date par semaine
- Cliquez avec le bouton droit de la souris sur lune des dates du tableau croisé dynamique.
- Dans le menu contextuel, cliquez sur Groupe
- Dans la boîte de dialogue Regroupement, sélectionnez Jours dans la liste « Par ».
- Pour « Nombre de jours », sélectionnez 7
- La plage de semaines est déterminée à la date indiquée dans la case «À partir de», ajustez-la si nécessaire. Dans lexemple ci-dessous, le 1er janvier 2012 a été saisi comme date de début.
- Cliquez sur OK
Regrouper les dates par périodes de 4 semaines
Regardez cette vidéo pour découvrir comment regrouper le champ de date par périodes de 4 semaines, et définir la date de début. Il y a des instructions écrites sous la vidéo.
Dates de groupe par période de 4 semaines
Dans cet exemple, notre entreprise a 13 périodes de vente chaque année, avec 4 semaines dans chaque période. Dans la boîte de dialogue Regroupement du tableau croisé dynamique, aucune option de regroupement par semaines n’est disponible. Au lieu de cela, nous pouvons utiliser loption Days.
Avec un peu darithmétique simple, nous pouvons calculer le nombre de jours par période:
- 4 semaines par période
- 7 jours par semaine
- 7 x 4 = 28 jours par période
Suivez ces étapes pour regrouper les dates en périodes de 4 semaines:
- Faites un clic droit une des dates, et cliquez sur Groupe
- Dans la fenêtre de regroupement, cliquez sur Jours, pour le sélectionner
- Si dautres types de regroupement sont sélectionnés, cliquez dessus, pour les désélectionner
- Définissez le nombre de jours sur 28
- REMARQUE: pour regrouper les dates en périodes dune semaine, utilisez 7 comme nombre de jours.
Utilisation de la date de début par défaut
En tant que date de début, Excel sélectionne automatiquement la première date dans vos données source. Dans la capture décran ci-dessus, vous pouvez voir que le 1er janvier 2013 est la date de début de ces données source.
Si je menregistre dans Outlook, cette date tombe un mardi.
Si je clique sur OK dans la fenêtre de regroupement, Excel créera des périodes qui commencent un mardi. Dans les dates groupées ci-dessous, la première période va du mardi 1er janvier 2013 au lundi 28 janvier 2013.
Fixer la date de début
Au lieu dutiliser la date de début par défaut, vous pouvez entrer une date différente, pour obtenir la date de début dont vous avez besoin.
Dans cet exemple, nous Je changerai la date de début au 31 décembre 2012. Cette date tombe un lundi, ce qui oblige tous les regroupements à commencer un lundi, qui correspond aux périodes de vente de lentreprise.
Pour fixer la date de début:
- Faites un clic droit sur lune des cellules de date groupées dans le tableau croisé dynamique
- Cliquez sur Groupe
- Dans la fenêtre de regroupement, modifiez la date de début – 31 décembre 2012
- REMARQUE: la case à cocher Auto pour « À partir de » est automatiquement désactivée – laissez-la décochée.
- Cliquez sur OK
Les périodes sont désormais regroupées correctement, avec un lundi comme date de début pour chaque période.
Le regroupement par date ajoute des éléments supplémentaires
Regardez cette courte vidéo pour voir comment masquer le texte sur les éléments supplémentaires qui sont créés lorsque vous groupez un champ de date, ou utilisez un chronologie dans Excel 2013, pour filtrer par période. Il y a des instructions écrites sous la vidéo.
Téléchargez le fichier dexemple pour les éléments supplémentaires dans le regroupement de dates. Le fichier compressé est au format xlsx et ne contient pas de macros.
Le regroupement par date ajoute des éléments supplémentaires
Il ny a aucun moyen de supprimer ces éléments supplémentaires, mais vous pouvez modifier les légendes, pour remplacer le texte par des espaces. Ou, ajoutez de nouveaux champs dans les données source, pour calculer lannée et lannée / le mois, pour chaque date, et utilisez les nouveaux champs du tableau croisé dynamique au lieu de regrouper.
Dans Excel 2013, vous pouvez utilisez le filtre Chronologie, pour filtrer rapidement par année, trimestre, mois ou jour.
Téléchargez le fichier dexemple pour le supplément éléments dans le regroupement de dates. Le fichier compressé est au format xlsx et ne contient pas de macros.
Regroupement des numéros
Utilisez le regroupement pour créer un résumé concis, au lieu dafficher une longue liste de numéros individuels. Cette vidéo vous montre les étapes, et les instructions écrites se trouvent sous la vidéo.
Regroupement des numéros
Au lieu de lister les numéros individuellement dans un champ Ligne, vous pouvez les regrouper, pour créer un résumé concis des données.
Dans cet exemple, le tableau croisé dynamique dorigine montre les unités vendues, par produit. Dans la colonne C, il indique le nombre de commandes où cette quantité a été vendue.
Pour récapituler les commandes, regroupez les unités par dizaines. Ainsi, au lieu de 3 et 9 apparaissant dans des lignes séparées, ils « seront résumés dans le groupement 1-10.
Pour regrouper les nombres
- Faites un clic droit sur un des numéros dunité dans le tableau croisé dynamique.
- Dans le menu contextuel, cliquez sur Groupe
- Dans la boîte de dialogue Regroupement, saisissez 1 dans la zone À partir de
- Dans cet exemple, le nombre dunités le plus élevé est 50 et vous pouvez saisir un nombre plus élevé, si nécessaire.
- Cliquez sur OK pour appliquer le regroupement
Dans le tableau croisé dynamique, les unités apparaissent désormais dans les groupes – 1 – 10, 11 – 20, etc.
Pour créer une mise en page plus concise, vous pouvez déplacer le champ des unités groupées vers la zone Colonnes, comme en-têtes.
Regroupement des éléments de texte
La boîte de dialogue de regroupement nest pas disponible si vous essayez de regrouper des éléments dans un champ de texte. Au lieu de cela, vous pouvez regrouper manuellement les éléments.
Regardez cette vidéo pour voir les étapes de création de groupes à partir déléments de texte. Ensuite, renommez un groupe ou ajoutez dautres éléments. Plus tard, vous pouvez dissocier un ou plusieurs groupes. Des instructions écrites se trouvent sous la vidéo.
Regroupement d’éléments de texte
La boîte de dialogue de regroupement n’est pas disponible si vous essayez de regrouper des éléments dans un champ de texte. À la place, vous pouvez grouper manuellement les éléments.
- Sélectionnez les éléments que vous souhaitez regrouper
- Faites un clic droit sur lun des éléments sélectionnés, puis cliquez sur Groupe
Ceci crée un nouveau champ dans la liste des champs, avec le nom du champ dorigine et un numéro, tel que City2
Le nouveau champ est positionné au-dessus du champ dorigine dans la disposition du tableau croisé dynamique, et les éléments groupés ont un nom générique, tel que Group1.
Pour renommer le groupe:
- Cliquez sur les cellules den-tête avec le nom par défaut , par exemple Groupe1
- Saisissez un nouveau nom, tel que Campagne A.
Pour ajouter plus déléments dans un groupe:
- Sélectionnez tous les éléments du groupe
- Appuyez sur la touche Ctrl et sélectionnez les éléments que vous souhaitez ajouter au groupe
- Faites un clic droit sur lun des éléments sélectionnés, puis cliquez sur Groupe
- REMARQUE: si le groupe a été renommé, ce nom sera remplacé par un nom par défaut.
Dissociation déléments de texte
Après avoir groupé manuellement des éléments de texte, vous souhaiterez peut-être en dissocier certains, ou tous, des groupes dans un champ pivot.
Pour dissocier un seul groupe:
- Faites un clic droit sur un élément du groupe
- Cliquez sur Dissocier, et le groupe revient aux éléments individuels.
Pour dissocier un seul groupe:
- Cliquez en haut dun en-tête de groupe, ou cliquez en haut du champ pivot pour sélectionner tous les éléments
- Cliquez avec le bouton droit sur un élément du groupe
- Cliquez sur Dissocier, et tous les groupes sélectionnés reviennent à des éléments individuels.
Problèmes lors du regroupement des données
Si vous essayez de regrouper une date ou un nombre , vous pouvez voir un message derreur indiquant «Impossible de regrouper cette sélection». Cela peut être dû au modèle de données, à des cellules vides ou à des éléments précédemment groupés. Consultez les détails ci-dessous.
Modèle de données
Lorsque vous avez créé le tableau croisé dynamique, si vous avez coché la case « Ajouter ces données au modèle de données », vous ne pourrez pas regrouper les éléments. Au lieu dun tableau croisé dynamique normal, il sagit dun Power Pivot basé sur OLAP et la fonctionnalité de regroupement nest pas disponible.
Cellules ou texte vides
Dans un tableau croisé dynamique normal (pas dans le modèle de données), le regroupement problème survient généralement lorsque le champ contient des enregistrements avec
- une cellule vide dans un champ de date / numéro, ou
- une entrée de texte dans un champ de date / numéro.
Pour résoudre le problème
- Pour les cellules vides, indiquez la date / le numéro (utilisez une date / un numéro factice si nécessaire).
- Sil y a du texte dans le champ date / nombre, supprimez-le.
- Si les nombres sont reconnus comme du texte, utilisez lune des techniques pour changer le texte en nombres réels. Revenez ensuite à ce didacticiel sur le tableau croisé dynamique, et essayez les étapes de regroupement.
Regroupement précédent
Si vous navez pas de cellules vides ou de texte dans la colonne de date, il se peut quil reste un champ groupé de la dernière fois que vous avez groupé les données.
- Vérifiez la liste des champs, pour voir sil ya « une deuxième copie du champ de date, par exemple Date2.
- Sil y en a, ajoutez-le à la zone de ligne et dissociez-le.
- Ensuite, vous devriez pouvoir à nouveau regrouper le champ de date.
Le regroupement dans un tableau croisé dynamique en affecte un autre
Si vous créez deux tableaux croisés dynamiques basés sur le même tableau Excel dans Excel 2007, lorsque vous modifiez le regroupement dans un tableau croisé dynamique, le même regroupement apparaît dans lautre tableau croisé dynamique.
Parce que vous avez créé les deux tableaux croisés dynamiques à partir des mêmes données source, ils utilisent par défaut le même cache pivot, le groupe les éléments sont les mêmes dans les deux tableaux.
Pour utiliser un regroupement différent dans chaque tableau croisé dynamique, vous devez créer un cache pivot distinct pour chaque tableau croisé dynamique.
Utilisez la méthode suivante, suggérée dans les groupes de discussion Excel par Dave Peterson.
Pour créer un cache pivot distinct pour le deuxième tableau croisé dynamique:
- Coupez le deuxième tableau croisé dynamique et collez-le dans un nouveau classeur.
- Modifiez le regroupement du deuxième tableau croisé dynamique.
- Coupez le deuxième tableau croisé dynamique du nouveau classeur et collez-le de nouveau dans le classeur dorigine.
Téléchargez le Exemple de fichier
Téléchargez lexemple de fichier compressé pour ce didacticiel
Outils de tableau croisé dynamique
Pour gagner du temps lors de la construction, du formatage et de la modification de vos tableaux croisés dynamiques, utilisez les outils dans mon complément Pivot Power Premium. En quelques clics, vous pouvez:
- copier la mise en forme dun tableau croisé dynamique et lappliquer à un autre tableau croisé dynamique.
- changer toutes les valeurs de Count en Sum
- supprimer la « somme de » de tous les en-têtes
et bien plus encore!
Plus de didacticiels
Effacer les anciens éléments du tableau croisé dynamique
Champ calculé – Nombre
Calculé Éléments
Fonctions de résumé