Le tableau croisé dynamique Top 10 Filter donne un total des lignes visibles
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 montrant les revenus par client. Le total des revenus est de 6,7 millions de dollars.
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.
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.
- 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, 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 donc une règle qui dit que vous ne pouvez pas utiliser les filtres automatiques 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 n'ai jamais vraiment réfléchi à la raison pour laquelle Microsoft grise cela. Il doit s'agir d'un élément interne indiquant 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 Magic. 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 des filtres 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 gros é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 2016, 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, sélectionnez la case indiquant 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 le total, et votre total général comprend désormais un astérisque et le total de toutes les données.
Cette astuce m'est venue à l'origine de Dan lors de mon séminaire à Philadelphie. Merci à Miguel Caballero d'avoir suggéré cette fonctionnalité.
Regarder la vidéo
- Le tableau croisé dynamique Top 10 Filter donne un total des lignes visibles
- Inclure les éléments filtrés dans les totaux est grisé
- Une manière étrange d'appeler le filtre de données à partir de la cellule magique
- Les filtres de données ne sont pas autorisés dans les tableaux croisés dynamiques
- Excel ne parvient pas à griser le filtre de données de la cellule magique
- Demandez le top 6 pour obtenir le top 5 plus Grand Total
- Utile pour filtrer par un élément de pivot spécifique
- Excel 2013 ou plus récent: une manière différente d'obtenir le vrai total
- Envoyez vos données via le modèle de données
- Inclure les éléments filtrés dans les totaux sera disponible
- Obtenez Total avec astérisque
- J'ai appris cette astuce il y a plus de 10 ans de Dan à Philadelphie
Transcription vidéo
Apprendre Excel pour Podcast, Episode 1999 - Tableau croisé dynamique True Top Five
Je balado ce livre entier. Il y a une liste de lecture, cliquez sur le I dans le coin supérieur droit pour suivre cette liste de lecture. Bienvenue à nouveau sur le netcast. Je suis Bill Jelen.
Très bien, nous allons donc créer un tableau croisé dynamique et nous voulons afficher non pas tous les clients, mais seulement les cinq premiers clients. INSÉRER, tableau croisé dynamique. D'accord, je vais mettre Client sur le côté gauche et Revenus. Très bien, voici notre liste complète de clients notés 6,7 millions de dollars. Excel, il est facile de faire un top cinq. Allez dans Étiquettes de ligne, Filtres de valeur, top 10. Il n'est pas nécessaire d'être en haut. Cela peut être en haut ou en bas. N'a pas besoin d'être cinq. Ça peut être vingt, quarante, ça peut être n'importe quoi. Top quatre-vingts pour cent, donnez-moi assez de records pour arriver à trois ou quatre millions de dollars, mais c'est parti. Les cinq principaux éléments. Souvenez-vous maintenant de 6,7 millions de dollars, cliquez sur OK et mon gros problème ici, c'est que ce grand total n'est pas de 6,7 millions. Quand je donne ça au vice-président des ventes, il va paniquer, en disant, attendez une seconde,Je sais que j'ai fait plus de 3,3 millions de dollars. Bon, nous allons donc annuler, annuler cela et revenir aux données d'origine.
Maintenant, j'ai appris cette prochaine astuce lors de l'un de mes séminaires Power Excel à Philadelphie. Un gars nommé Dan dans la deuxième rangée m'a montré ça. Il y a plus de dix ans, il m'a montré cette astuce, et il faut d'abord parler des filtres. Donc, normalement, si vous allez utiliser le filtre normal, ce filtre ici, vous choisissez une cellule de votre ensemble de données et cliquez sur l'icône de filtre, ou certaines personnes choisissent l'ensemble de données, CONTROL * et cliquez sur l'icône de filtre, mais il y a une troisième voie. Une façon dont personne ne se soucie. Si vous allez à la toute dernière cellule d'en-tête, dans mon cas, c'est le coût en L1, et allez d'une cellule à droite. J'appelle cela la cellule magique, je ne sais pas pourquoi mais pour une raison inconnue, à partir de cette cellule, je peux filtrer l'ensemble de données adjacent. D'accord, c'est comme une manière étrange et personne ne s'en soucie.
Bien, parce qu'il existe deux autres très bonnes façons d'appeler un filtre, personne n'a besoin de connaître la cellule magique, mais voici le truc, voir à l'intérieur d'un tableau croisé dynamique, il est grisé. Vous n'êtes pas autorisé à utiliser ces filtres. C'est contre les règles. Maintenant, si je viens ici, je suis plus que bienvenu pour utiliser le filtre, mais à l'intérieur, ils notent. Je ne sais pas qui est la personne qui grise ça, mais ils n'ont jamais entendu mon petit discours sur la cellule magique, parce que si je vais à la toute dernière cellule d'en-tête et que je vais d'une cellule à droite, regarde ça, ils oublient de griser le filtre et maintenant je viens d'ajouter les anciens filtres automatiques au tableau croisé dynamique. Alors je viens ici, allez aux filtres numériques, c'est différent des filtres de valeur. Il s'appelle toujours Top Ten. Légèrement différent, je vais demander les cinq premiers, pas les six premiers.Les six premiers parce que pour ce filtre, le total général n'est qu'une autre ligne, et le total général est l'élément le plus important, puis lorsqu'on me demande les éléments 2 à 6, j'obtiens les cinq premiers éléments.
Très bien, nous y sommes. Un hack de Filter cool, qui nous donne les cinq meilleurs éléments et le vrai total de tout le monde. Très bien maintenant, quelques choses. N'oubliez pas la cellule magique. D'accord, il n'y a aucun moyen de désactiver ce filtre, à moins que vous ne retourniez à la cellule magique. D'accord, vous devez donc vous souvenir de la cellule magique. De plus, si vous modifiez les données sous-jacentes et que vous actualisez le tableau croisé dynamique, ils n'actualiseront pas le filtre car, à la connaissance de Microsoft, vous n'êtes pas autorisé à avoir un filtre.
Ceci est utile pour d'autres choses. Parfois, nous avons des produits au sommet. Allons-y ici sous une forme tabulaire. Pas nécessaire, j'aime juste avoir de vrais titres. Gizmo, Widget, Gadgets, Doodads. D'accord et peut-être que vous êtes le responsable des Doodads et que vous avez besoin de voir uniquement les clients qui avaient une valeur particulière et les Doodads. Je vais donc dans la cellule magique, j'active le filtre puis sous Doodads je peux demander des éléments supérieurs à zéro. Cliquez sur OK. D'accord, ce type de filtrage ne serait pas possible sur un tableau croisé dynamique normal, mais il est possible d'utiliser la cellule magique.
Très bien maintenant, annulons la liste. Désactivons ce filtre et supprimons le tableau croisé dynamique, et si vous êtes dans Excel 2013 ou nouveau, je vais vous montrer un moyen tout à fait légal d'obtenir le total correct en bas. Insérez le tableau croisé dynamique, ici en bas, à partir d'Excel 2013, cette boîte très inoffensive ne semble pas très excitante, ajoutez ces données au modèle de données. Cela envoie les données, dans les coulisses, au moteur Power Pivot. Créez exactement le même rapport. Clients sur le côté gauche. Chiffre d'affaires au cœur du tableau croisé dynamique. Ensuite, allez aux filtres classiques, les filtres de valeur en haut 10. Demandez les cinq premiers. Remarquez encore une fois que nous avons 6,7 millions de dollars après avoir fait cela, 3,3 millions de dollars, mais voici la différence. Lorsque je vais dans l'onglet Conception, sous Sous-totaux, cette fonctionnalité appelée Inclure les éléments filtrés dans les totaux,n'est plus grisé. À un tableau croisé dynamique régulier n'est pas disponible. Nous obtenons un petit astérisque là-bas et c'est le total de tout. Très bien, maintenant bien sûr cela ne fonctionne que dans Excel 2013 ou plus récent.
D'accord, il va me falloir six semaines pour que tout ce livre soit diffusé ici sur YouTube. Il y a tellement de bons conseils ici. Des conseils qui pourraient commencer à vous faire gagner du temps, tout de suite. Achetez le livre entier maintenant et vous aurez accès à tous les 40, c'est en fait beaucoup plus de 40 conseils. Touches de raccourci Excel. Toutes sortes de choses intéressantes dans ce livre.
Très bien, récapitulez. Ainsi, lorsque nous faisons un filtre du tableau croisé dynamique des 10 premiers, cela nous donne le total, mais uniquement les lignes visibles, pas les éléments qu'il a filtrés. Oui, si nous allons au deuxième onglet et recherchons des sous-totaux, des éléments filtrés et des totaux, il est grisé, mais il existe une façon étrange d'appeler l'ancien filtre de données à partir de la cellule magique. La toute dernière cellule d'en-tête, allez d'une cellule à droite, vous ne pouvez pas utiliser de filtres et de tableaux croisés dynamiques, mais si vous allez dans la cellule magique, ils oublient de la griser. Maintenant, dans le filtre numérique, vous demandez aux six premiers d'obtenir les cinq premiers, plus le total général. Également utile pour filtrer vers un élément de pivot spécifique: les Doodads, tout ce qui avait plus de 0 dans les Doodads ou les 5 meilleurs Doodads. Excel 2013 ou plus récent, il existe une manière différente d'obtenir le vrai total.Cochez cette case pour le modèle de données, puis inclure les éléments filtrés dans les totaux sera disponible. Vous obtenez le total avec un astérisque. Et merci à Dan à Philadelphie qui m'a montré à l'un de mes séminaires Power Excel, il y a plus de dix ans, et m'a donné ce super petit truc. Un moyen pour le filtre de se faufiler à travers le mur de la table pivotante du club. Ils n'autorisent normalement pas ce filtre automatique.
Hé, je tiens à vous remercier d'être passé. Nous vous reverrons la prochaine fois, pour un autre netcast de MRExcel.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast1999.xlsx
