Les tableaux croisés dynamiques proposent un filtre Top 10. C'est cool. C'est flexible. Mais je déteste ça, et je vais vous dire pourquoi.
Voici un tableau croisé dynamique qui montre les revenus par client. Le total des revenus est de 6,7 millions de dollars. Notez que le plus gros client, Roto-Rooter, représente 9% du chiffre d'affaires total.
Et si mon responsable a la capacité d'attention d'un poisson rouge et ne souhaite voir que les cinq principaux clients? Pour commencer, ouvrez la liste déroulante en A3 et sélectionnez Filtres de valeur, Top 10.
La boîte de dialogue Super-flexible Top 10 Filter permet Haut / Bas. Il peut faire 10, 5 ou tout autre nombre. Vous pouvez demander les cinq meilleurs articles, les 80% les plus importants ou suffisamment de clients pour atteindre 5 millions de dollars.
Mais voici le problème: le rapport résultant montre cinq clients et le total de ces clients au lieu des totaux de tout le monde. Roto-Rooter, qui représentait auparavant 9% du total, représente 23% du nouveau total.
Mais d'abord, quelques mots importants sur le filtre automatique
Je me rends compte que cela semble être une question décalée. Si vous souhaitez activer les listes déroulantes Filtrer sur un ensemble de données normal, comment procédez-vous? Voici trois méthodes très courantes:
- Sélectionnez une cellule dans vos données et cliquez sur l'icône Filtre dans l'onglet Données ou appuyez sur Ctrl + Maj + L.
- Sélectionnez toutes vos données avec Ctrl + * et cliquez sur l'icône Filtre dans l'onglet Données.
- Appuyez sur Ctrl + T pour formater les données sous forme de tableau.
Ce sont trois très bons moyens. Tant que vous connaissez l'un d'entre eux, il n'est absolument pas nécessaire d'en connaître une autre. Mais voici une façon incroyablement obscure mais magique d'activer le filtre:
- Accédez à votre rangée d'en-têtes, puis accédez à la cellule d'en-tête la plus à droite. Déplacez une cellule vers la droite. Pour une raison inconnue, lorsque vous êtes dans cette cellule et cliquez sur l'icône Filtre, Excel filtre l'ensemble de données à votre gauche. Je n'ai aucune idée de pourquoi cela fonctionne. Cela ne vaut vraiment pas la peine d'en parler car il existe déjà trois très bonnes façons d'activer les listes déroulantes Filtre. J'appelle cette cellule la cellule magique.
Et maintenant, retour aux tableaux croisés dynamiques
Il existe une règle qui dit que vous ne pouvez pas utiliser le filtre automatique lorsque vous êtes dans un tableau croisé dynamique. Voir ci-dessous? L'icône Filtre est grisée car j'ai sélectionné une cellule dans le tableau croisé dynamique.
Je ne sais pas pourquoi Microsoft grise cela. Ce doit être quelque chose d'interne qui dit que le filtre automatique et un tableau croisé dynamique ne peuvent pas coexister. Donc, il y a quelqu'un dans l'équipe Excel qui est en charge de griser l'icône de filtre. Cette personne n'a jamais entendu parler de la cellule magique. Sélectionnez une cellule dans le tableau croisé dynamique et le filtre est grisé. Cliquez en dehors du tableau croisé dynamique et le filtre est à nouveau activé.
Mais attendez. Et la cellule magique dont je viens de vous parler? Si vous cliquez dans la cellule à droite du dernier titre, Excel oublie de griser l'icône Filtre!
Effectivement, Excel ajoute des listes déroulantes de filtre automatique à la ligne supérieure de votre tableau croisé dynamique. Et le filtre automatique fonctionne différemment d'un filtre de tableau croisé dynamique. Accédez à la liste déroulante Revenus et choisissez Filtres numériques, Top 10….
Dans la boîte de dialogue Top 10 des filtres automatiques, choisissez 6 principaux éléments. Ce n'est pas une faute de frappe… si vous voulez cinq clients, choisissez 6. Si vous voulez 10 clients, choisissez 11.
Pour le filtre automatique, la ligne du total général est le plus grand élément des données. Les cinq principaux clients occupent les positions 2 à 6 dans les données.
Mise en garde
De toute évidence, vous déchirez un trou dans le tissu d'Excel avec cette astuce. Si vous modifiez ultérieurement les données sous-jacentes et actualisez votre tableau croisé dynamique, Excel n'actualisera pas le filtre car, à la connaissance de Microsoft, il n'y a aucun moyen d'appliquer un filtre à un tableau croisé dynamique!
Remarque
Notre objectif est de garder cela secret pour Microsoft car c'est une fonctionnalité plutôt intéressante. Il a été «cassé» pendant un certain temps, donc il y a beaucoup de gens qui pourraient compter dessus maintenant.
Une solution entièrement légale dans Excel 2013+
Si vous voulez un tableau croisé dynamique vous montrant les cinq premiers clients mais le total de tous les clients, vous devez déplacer vos données en dehors d'Excel. Si vous avez Excel 2013 ou une version plus récente sous Windows, il existe un moyen très pratique de le faire. Pour vous montrer cela, j'ai supprimé le tableau croisé dynamique d'origine. Choisissez Insertion, Tableau croisé dynamique. Avant de cliquer sur OK, cochez la case Ajouter ces données au modèle de données.
Construisez votre tableau croisé dynamique comme d'habitude. Utilisez la liste déroulante en A3 pour sélectionner Filtres de valeur, Top 10, et demandez les cinq meilleurs clients. Avec une cellule du tableau croisé dynamique sélectionnée, accédez à l'onglet Conception dans le ruban et ouvrez la liste déroulante Sous-totaux. Le choix final dans la liste déroulante est Inclure les éléments filtrés dans les totaux. Normalement, ce choix est grisé. Mais comme les données sont stockées dans le modèle de données au lieu d'un cache pivot normal, cette option est désormais disponible.
Choisissez l'option Inclure les éléments filtrés dans les totaux, et votre total général comprend désormais un astérisque et le total de toutes les données, comme indiqué ci-dessous.
Ce tour de magie cellulaire m'est venu à l'origine de Dan lors de mon séminaire à Philadelphie et a été répété 15 ans plus tard par un autre Dan de mon séminaire à Cincinnati. Merci à Miguel Caballero d'avoir suggéré cette fonctionnalité.
