Récemment, un de mes amis s'est interrogé sur les boutons d'exploration vers le bas et d'exploration vers le haut dans l'onglet Outils de tableau croisé dynamique du ruban. Pourquoi sont-ils grisés en permanence? Ils prennent beaucoup de place dans le ruban. Comment quelqu'un est-il censé les utiliser?
Après quelques recherches, il existe un moyen de les utiliser, mais vous devez utiliser le modèle de données et utiliser la vue de diagramme Power Pivot pour créer une hiérarchie. Si vous n'avez pas l'onglet Power Pivot dans votre ruban, vous devrez trouver un collègue qui possède le bouton afin de créer la hiérarchie. (Ou, si vous voulez simplement essayer la fonctionnalité, téléchargez le fichier Excel que j'ai créé: Hierarchy.xlsx)
Première étape - convertissez votre ensemble de données source pivot en un tableau à l'aide de l'option Accueil - Mettre en forme en tant que tableau ou Ctrl + T. Assurez-vous que l'option Ma table comporte des en-têtes est sélectionnée.
Utilisez Insertion - Tableau croisé dynamique. Dans la boîte de dialogue Créer un tableau croisé dynamique, cochez la case Ajouter ces données au modèle de données.
Voici les champs de tableau croisé dynamique avant de créer la hiérarchie.
Cliquez sur l'icône Gérer dans l'onglet Power Pivot dans le ruban. (De nombreuses instances d'Excel 2013 et 2016 n'ont pas cet onglet. Il n'apparaît pas sur le Mac.)
Dans la fenêtre Power Pivot pour Excel, cliquez sur l'icône Vue Diagramme. Il se trouve près du côté droit de l'onglet Accueil.
Utilisez la poignée de redimensionnement dans le coin inférieur droit de Table1 pour agrandir Table1 afin que vous puissiez voir tous vos champs. Cliquez sur le premier élément de votre hiérarchie (Continent dans mon exemple). Shift-Cliquez sur le dernier élément de la hiérarchie (Ville dans mon exemple). Vous pouvez également cliquer sur un élément et Ctrl-clic sur d'autres si les champs de la hiérarchie ne sont pas adjacents. Une fois que vous avez sélectionné les champs, cliquez avec le bouton droit sur l'un des champs et choisissez Créer une hiérarchie.
Hierarchy1 est créé et attend que vous saisissiez un nouveau nom. Je nommerai ma hiérarchie Géographie. Si vous cliquez en dehors de Power Pivot, Hierarchy1 n'est plus en mode Renommer. Cliquez avec le bouton droit sur Hierachy1 et choisissez Renommer.
Fermez Power Pivot et revenez à Excel. Les champs du tableau croisé dynamique affichent désormais la hiérarchie géographique et plus de champs. Votre champ Ventes est masqué sous Plus de champs. Je comprends un peu pourquoi ils cachent Continent, Pays, Région, Territoire, Ville sous Plus de champs. Mais je ne comprends pas pourquoi ils cachent les ventes sous Plus de champs.
Pour créer le tableau croisé dynamique, cochez la case de la hiérarchie Géographie. Ouvrez plus de champs en cliquant sur le triangle à côté. Choisissez Ventes.
Il y a beaucoup de choses à remarquer dans l'image ci-dessus. Lorsque vous créez initialement le tableau croisé dynamique, la cellule active est sur A3 et l'icône d'exploration vers le bas est grisée. Toutefois, si vous déplacez le pointeur de cellule vers l'Amérique du Nord dans A4, vous verrez que l'exploration vers le bas est activée.
Avec le pointeur de cellule sur l'Amérique du Nord, cliquez sur Explorer vers le bas et le continent est remplacé par le pays.
Avec le pointeur de cellule sur le Canada, cliquez sur Explorer vers le bas et vous verrez l'Est du Canada et l'Ouest du Canada. Notez qu'à ce stade, les boutons Explorer vers le bas et Explorer vers le haut sont activés.
J'ai cliqué sur Explorer pour revenir au pays. Sélectionnez États-Unis. Drill Down trois fois et je me retrouve dans les villes de la région des Carolines. À ce stade, le bouton Explorer vers le bas est grisé.
Notez qu'à partir du niveau Continent, vous pouvez cliquer sur Développer le champ pour afficher les continents et les pays. Ensuite, à partir du premier pays, choisissez Développer le champ pour révéler les régions. À partir de la première région, utilisez Développer le champ pour afficher les territoires. À partir du premier territoire, cliquez sur Développer le champ pour révéler la ville.
Toutes les captures d'écran ci-dessus montrent le tableau croisé dynamique dans ma vue par défaut de Afficher sous forme tabulaire. Si vos tableaux croisés dynamiques sont créés sous forme compacte, vous verrez la vue ci-dessous. (Pour savoir comment faire démarrer tous vos futurs tableaux croisés dynamiques sous forme de tableau, regardez cette vidéo).
Quel est l'avantage de la Hiérarchie? J'ai essayé de créer un tableau croisé dynamique régulier sans hiérarchie. J'ai toujours la possibilité de développer et réduire les champs. Mais si je veux afficher uniquement les régions du Canada, je devrais ajouter un segment ou un filtre de rapport.
Regarder la vidéo
Transcription vidéo
Apprenez Excel à partir du podcast, épisode 2196: Explorer en avant et en arrière dans les tableaux croisés dynamiques.
Salut, bon retour sur le netcast, je suis Bill Jelen. Il y a un mystère dans les tableaux croisés dynamiques. Si j'insère un tableau croisé dynamique ici, vous voyez que nous avons des champs d'exploration en haut et en bas, mais ils ne s'allument jamais. Qu'est-ce qui se passe avec ça? Pourquoi en avons-nous? Comment les faire fonctionner? Très bien, c'est une très bonne question et malheureusement, je me sens mal à ce sujet. J'essaie de faire toute ma vie dans Excel sans jamais utiliser l'onglet Power Pivot. Je ne veux pas que vous ayez à payer les 2 $ supplémentaires par mois pour la version Pro Plus d'Office 365, mais c'est celui-ci - c'est celui-ci - où nous devons dépenser les 2 $ supplémentaires par mois ou trouver quelqu'un qui a les 2 $ supplémentaires par mois pour mettre cela en place.
Je vais prendre ce format de données comme un tableau. Peu importe le format que je choisis, le format n'est pas important; simplement nous fournir une table est la partie importante. Power Pivot, nous allons ajouter ce tableau à notre modèle de données, puis cliquer sur Gérer. Très bien, voici donc notre tableau dans le modèle de données. Nous devons aller à la vue Diagramme, maintenant nous allons l'élargir un peu afin que nous puissions voir tous les champs. Je vais choisir Continent; Je vais Shift + cliquez sur Ville. Maintenant, cela constitue mon Drill Down, Drill Up, la hiérarchie. Et puis nous ferons un clic droit et dirons Create Heirarchy. Et ils nous donnent un nom - je vais taper "Géographie" pour ma Hiérarchie, comme ça. Parfait, maintenant, avec ce changement, nous insérons un tableau croisé dynamique - et ce sera un tableau croisé dynamique de modèle de données - et vous voyez que nous pouvons ajouter la géographie comme sa propre hiérarchie.
Maintenant, la seule chose que je n'aime pas particulièrement à ce sujet, c'est que tout le reste passe à More Fields. Bien? Nous choisissons donc la géographie et elle vole vers la gauche. Et même si c'est génial, je dois également choisir Revenus, et ils ont pris les champs qui ne faisaient pas partie de la hiérarchie et les ont déplacés vers Plus de champs. Donc, je comprends, ils essaient de masquer les champs que je ne suis pas censé choisir, mais ce faisant, ils ont également caché plus de champs - le revenu ou les ventes ici. Bien. Donc, un peu frustrant, nous devons aller dans plus de domaines pour obtenir les champs qui ne font pas partie de la géographie, mais c'est comme ça que ça se passe.
Bien. Alors, maintenant, maintenant que nous avons cela, jetons un coup d'œil à ce qui fonctionne ici. Je suis assis sur Continent, je vais dans l'onglet Analyser et rien ne s'allume, ça n'a pas fonctionné. Tirer! Non, cela a fonctionné, il vous suffit de venir en Amérique du Nord et ensuite je peux Drill Down et cela remplace Continent par Country. Et puis, à partir du Canada, je peux creuser et obtenir l'est du Canada et l'ouest du Canada. De l'est du Canada, j'obtiens l'Ontario et le Québec. Ontario, j'obtiens ces villes, je peux explorer les niveaux Drill Up, Drill Up et choisir les États-Unis; Explorer en avant, en arrière, en profondeur. D'accord, c'est comme ça que ça marche.
Essayez-le, vous devez avoir l'onglet Power Pivot ou trouver quelqu'un avec un onglet Power Pivot. Si vous voulez simplement l'essayer, regardez dans la description YouTube, il y aura un lien vers la page Web et il y a un endroit là-bas sur la page Web où vous pouvez télécharger ce fichier, et vous devriez pouvoir utiliser la hiérarchie même si vous n'avez pas l'onglet Power Pivot. Si vous êtes dans Excel 2016 ou Office 365, cela devrait fonctionner.
Maintenant, vous savez, voyez, je suppose que la chose dont je ne suis pas sûr d'être fan est le fait qu'ils se débarrassent des autres informations, par opposition à l'utilisation de l'icône Développer, qui se développerait ensuite en le groupe suivant, et le groupe suivant, et le groupe suivant. Nous avons toujours eu l'icône Développer, mais même dans ce cas, cela fonctionne un peu différemment. Ici, si je le souhaite, je peux m'asseoir là-bas en Amérique du Nord et développer un niveau à la fois sans avoir à choisir chacun d'entre eux dans le modèle de données. Il semble que nous devons déplacer le pointeur de la cellule, un peu à la fois.
Très bien, maintenant, cette astuce était vraiment juste, en quelque sorte, découverte. Les MVP Excel ont eu une conversation avec l'équipe Excel à propos de ces boutons, donc non couverts dans ce livre. Mais beaucoup d'autres bons conseils sont abordés dans LIVe, les 54 meilleurs conseils de tous les temps.
Récapitulatif pour aujourd'hui: pourquoi l'exploration ascendante et descendante sont-elles constamment grisées? Eh bien, vous devez créer une hiérarchie. Pour créer une hiérarchie, vous devez accéder à Power Pivot; dans la vue Diagramme; sélectionnez les champs de l'hérarchie; puis faites un clic droit; et créer une hiérarchie.
Je tiens à vous remercier d'être passé, je vous verrai la prochaine fois pour une autre diffusion sur Internet.
