Les segments sont parfaits pour les tableaux croisés dynamiques, car vous pouvez contrôler plusieurs tableaux croisés dynamiques à partir d'un ensemble de segments. Mais - c'est une sorte de mensonge. Vous pouvez contrôler plusieurs tableaux croisés dynamiques provenant du même ensemble de données. Lorsque vous avez des tableaux croisés dynamiques provenant de deux ensembles de données différents, c'est assez délicat. Je vais vous montrer quelques VBA qui vous permettront de réussir.
Regarder la vidéo
- Comment pouvez-vous avoir un slicer pour piloter deux tableaux croisés dynamiques?
- Si les deux tableaux croisés dynamiques provenaient du même ensemble de données: sélectionnez Slicer, Report Connections, Choose Other Pivot Tables
- Mais si les tableaux croisés dynamiques provenaient de différents ensembles de données:
- Utilisez Enregistrer sous pour changer l'extension du classeur en XLSM au lieu de XLSX
- Utilisez alt = "" + TMS et remplacez la sécurité des macros par le deuxième paramètre.
- Alt + F11 pour accéder à VBA
- Ctrl + R pour afficher l'explorateur de projet
- Recherchez la feuille de calcul contenant votre premier tableau croisé dynamique et votre premier segment
- Insérez le code pour Worksheet_Update
- Cachez la deuxième tranche pour qu'elle continue d'exister, mais personne ne pourra jamais choisir parmi cette tranche
Transcription vidéo
Apprenez Excel pour Podcast, épisode 2104: Synchronisez les segments à partir de différents ensembles de données.
Hé, bienvenue à nouveau sur le netcast, je m'appelle Bill Jelen, et la question d'aujourd'hui n'est pas de savoir comment prendre ces deux tableaux croisés dynamiques qui proviennent d'un ensemble de données et faire en sorte que le Slicer contrôle tous ces tableaux croisés dynamiques. Ce n'est pas de cela qu'il s'agit. C'est une chose facile à faire - Slicer, Tools, Options, que ce soit les connexions de rapport ou les connexions de segment dans l'ancienne version, et vérifiez que vous voulez que ce segment contrôle tous ces tableaux croisés dynamiques. Facile, non? Cette question concerne cette feuille de calcul, où nous avons deux ensembles de données différents et nous allons créer un tableau croisé dynamique à partir de cela, et à partir de là, permettez-moi maintenant d'accélérer la vidéo pendant que je crée ces tableaux croisés dynamiques. Très bien, maintenant, ce que vous allez voir, c'est que j'ai deux tableaux croisés dynamiques, ce tableau croisé dynamique est créé à partir d'un ensemble de données, et il y a un segment qui contrôle ce tableau croisé dynamique;puis j'ai un deuxième tableau croisé dynamique créé à partir d'un ensemble de données différent et un segment qui contrôle ce tableau croisé dynamique. Mais il n'y a absolument aucun moyen de faire en sorte que ce segment contrôle à la fois ce tableau croisé dynamique et ce tableau croisé dynamique construit à partir d'un ensemble de données différent. Bien. Mais je vais vous montrer comment faire cela aujourd'hui avec une macro.
Maintenant, c'est difficile à faire. Quand la question est arrivée, j'ai dit: «Maintenant, ceci, je ne pense pas que vous puissiez le faire. Mais j'ai travaillé dessus et expérimenté et je pense que je l'ai enfin compris. Je dois penser que j'ai enfin compris. Très bien, alors passons par là. Tout d'abord, il est enregistré sous forme de fichier xlsx. C'est un bon type de fichier, sauf qu'il s'agit d'un type de fichier horrible car c'est le seul type de fichier qui n'autorise pas les macros. Vous devez changer cela de xlsx à xlsm, ou tout votre travail vers le reste de la vidéo sera jeté par la fenêtre. Enregistrer sous, changez le type de fichier en xlsm ou, diable, xlsb, l'un ou l'autre fonctionnera. C'est celui qui est cassé - xlsx - et c'est la valeur par défaut, n'est-ce pas fou? Xlsm, cliquez sur Enregistrer. Si vous n'avez jamais fait de macros auparavant, Alt + T pour Tom, M pour Macro,S pour la sécurité et vous pourrez enregistrer toutes les macros sans notification. Besoin de changer cela pour le second, qui permettra à vos macros de fonctionner.
Très bien, nous avons maintenant deux trancheurs. Je parie que vous ne l'avez jamais su, mais les slicers ont des noms. Nous allons aller aux outils de slicer, aux options, aux paramètres de slicer, et voir que celui-ci s'appelle Slicer_Name. Comme ça. Allez au second, allez dans Outils de segment, Options, Paramètres de segment, celui-ci s'appelle Slicer_Name1 - pas Espace de nom 1, Nom1. Deux noms comme ça.
Voici ce que nous allons faire. Nous allons passer à VBA - Alt + F11. En VBA, si vous n'avez jamais fait de VBA, vous allez avoir ce grand écran gris. Nous allons venir ici et dire View, Project Explorer, dans l'Explorateur de projet, trouvez votre fichier - le mien s'appelle Podcast 2104. Ouvrez Microsoft Excel Objects, et la feuille où je veux que cela fonctionne s'appelle Dashboard. Je vais faire un clic droit là-bas et dire Afficher le code. Ce code que nous écrivons ne peut pas aller dans un module comme dans une macro ordinaire - cela doit être sur cette feuille de calcul. Ouvrez le menu déroulant en haut à gauche, Feuille de calcul, puis dans le menu déroulant en haut à droite, nous allons dire Mise à jour du tableau croisé dynamique. D'accord, c'est donc là que notre code va aller maintenant. J'ai déjà précuit ce code. Jetons un coup d'œil au code ici dans le bloc-notes. Alors on'vous allez avoir deux caches Slicer - SC1 et SC2 - un élément Slicer et ensuite, ici même, c'est là que vous allez devoir le personnaliser. Donc mes deux Slicers s'appelaient Name et Name1. D'accord, vous allez devoir y mettre les noms de vos trancheurs. Application.Screenupdating = False, Application.EnableEvents = False, puis Slicer Cache 2 - nous allons effacer le filtre, puis pour chaque élément SI1 et sc1.SlicerItems, s'il est sélectionné, nous allons faire le même élément dans Slicer Cache à sélectionner. Il s'agit d'une petite boucle qui parcourra le nombre d'éléments dans ce segment. Dans mon cas, j'en ai 11 ou 12; dans votre cas, vous pourriez en avoir plus.Donc mes deux Slicers s'appelaient Name et Name1. D'accord, vous allez devoir y mettre les noms de vos trancheurs. Application.Screenupdating = False, Application.EnableEvents = False, puis Slicer Cache 2 - nous allons effacer le filtre, puis pour chaque élément SI1 et sc1.SlicerItems, s'il est sélectionné, nous allons faire le même élément dans Slicer Cache à sélectionner. Il s'agit d'une petite boucle qui parcourra le nombre d'éléments dans ce segment. Dans mon cas, j'en ai 11 ou 12; dans votre cas, vous pourriez en avoir plus.Donc mes deux Slicers s'appelaient Name et Name1. D'accord, vous allez devoir y mettre les noms de vos trancheurs. Application.Screenupdating = False, Application.EnableEvents = False, puis Slicer Cache 2 - nous allons effacer le filtre, puis pour chaque élément SI1 et sc1.SlicerItems, s'il est sélectionné, nous allons faire le même élément dans Slicer Cache à sélectionner. Il s'agit d'une petite boucle qui parcourra le nombre d'éléments dans ce segment. Dans mon cas, j'en ai 11 ou 12; dans votre cas, vous pourriez en avoir plus.vous allez faire le même élément dans Slicer Cache pour être sélectionné. Il s'agit d'une petite boucle qui parcourra le nombre d'éléments dans ce segment. Dans mon cas, j'en ai 11 ou 12; dans votre cas, vous pourriez en avoir plus.vous allez faire le même élément dans Slicer Cache pour être sélectionné. Il s'agit d'une petite boucle qui parcourra le nombre d'éléments dans ce segment. Dans mon cas, j'en ai 11 ou 12; dans votre cas, vous pourriez en avoir plus.
Lorsque nous en avons terminé avec cela, réactivez l'activation des événements, réactivez la mise à jour de l'écran. Bien. Donc, nous allons prendre ce code, copier ce code et le coller ici au milieu de notre macro comme ça. Bon, maintenant, assurons-nous simplement que je vais appuyer sur Ctrl + G et que ma demande est Application.EnableEvents, on ou off - alors,? Application.EnableEvents - et c'est vrai. Si le vôtre est faux, alors vous voulez revenir ici et dire que c'est = Vrai - alors, alors, vous activez ces événements. Bien. Maintenant, voici ce qui va se passer. Donc notre coach devrait travailler ici, c'est sur la bonne feuille de calcul. Nous sommes enregistrés dans un fichier xlxm, et j'ai activé les macros et ce que nous allons voir, c'est que lorsque je choisis dans le slicer de gauche, ce Slicer Cache 1 - je 'Je vais choisir Andy via Della - l'autre Slicer va également être mis à jour. D'accord Et même si je choisirais juste Gloria - juste Gloria - on dirait que ça marche vraiment très bien. Même si je voulais CTRL + clic, quand je lâche Ctrl, ils se mettent tous les trois à jour.
Mais voici le piège - il y a toujours un piège - ce Slicer, il doit exister, mais vous ne pouvez pas utiliser ce Slicer - attendez, je veux dire que vous pouvez, vous pouvez utiliser un Slicer mais ça va dérouter les choses . Parce que ce qui va se passer, c'est que je vais changer cela en Hank et ils vont revenir à tout ce qui se trouve dans Slicer Cache 1, car j'ai changé le tableau croisé dynamique sur cette feuille. Maintenant, dans la vraie vie, allez-vous avoir deux tableaux croisés dynamiques sur la même feuille? Je ne sais pas si vous l'êtes ou si vous ne l'êtes pas, d'accord, mais les choses vont devenir un peu folles.
Maintenant, jetons un coup d'œil à ceci. La première chose que je veux faire, c'est que je vais insérer une nouvelle feuille de calcul - Alt + IW pour insérer la feuille de calcul - et je vais appeler cela un DarkCave. Tu peux appeler ça comme tu le veux. Je vais prendre ce tableau de bord qui ne fonctionnera pas, je vais copier ce tableau de bord et venir ici dans la grotte sombre et le coller là-bas, puis cliquer avec le bouton droit de la souris et cacher cette feuille pour que personne ne voie jamais ce Slicer. Et puis, à partir d'ici, nous devrions pouvoir le supprimer. Bien, d'accord. Et nous allons simplement vérifier pour nous assurer qu'ils fonctionnent toujours - choisissez Charlie via Eddie et ils sont tous les deux toujours à jour. Maintenant, que se passe-t-il? Le Slicer que nous ne pouvons pas voir, celui que nous avons caché, est également mis à jour, mais nous ne nous soucions pas qu'il soit mis à jour.
Now, what if you want to have your things on different sheets? I'll insert a new worksheet here--Alt+IW-- and I'll take one of these pivot tables-- maybe the second pivot table-- and move it to that other sheet-- so, Ctrl+C to copy the pivot table, Ctrl+V to paste the pivot table here. And if I need to have a slicer here-- don't insert a slice from this pivot table-- we have to come back to our dashboard, take the slicer that's the controlling Slicer, Ctrl+C to make a copy of it, and paste it here-- Ctrl+V. Alright? Now, we have no code on this sheet-- there's no code on Sheet4-- and I was thinking I was going to have to add some code to Sheet4, but here's the beautiful thing: When I change this slicer, what's happening is, on the dashboard that pivot table's updating even though that pivot table on that sheet that's not active is updating, they will run the code and this will update as well. Pretty darn amazing that that works.
Now, the whole key to this is, you can never use the slicer tied to the second pivot table. You have to have the slicer that's tied to the second pivot table but you cannot use it-- you have to use this slicer tied to the first pivot table. Alright? But in general, I think this is working fairly well.
Alright, now hey, Sal, the person who asks this question, wrote in and said, "Look, I have a disconnected pivot table-- disconnected slicer in the second pivot table only." So let's just add a new field here called Region, East, West, we'll refresh our second pivot table, cool, and I'll insert a slicer that is disconnected-- in other words, it's only in the second data set, not in the first data set, Alright, now, this is going to be tricky because when I choose East from here, we're not going to have anybody selected. Alright? So the pivot table goes away. I would have to clear this slicer on the left hand side and then East remains selected. And now things have gone to hell, right? So, you are choosing from a slicer tied to the second data set and, while the second one is updating, the first one is not going to respect that because it has no idea there's no region filled back in the other field. This is only going to work when you have the same field in both data sets. If you have some other situation like this, then it will not fly.
So here's what you're going to have to do: You're going to have to insert that field-- the Region field-- back in your original data set, refresh this pivot table, insert a new slicer that will control that first pivot table. Alright? Now, we have two different slicers now, and because I built them backwards their names are backwards-- this one's Slicer_Region 1, and the one that's going to be the controlling one is called Slicer_Region2. If I would have planned differently, we would have had a different result, but here we are. Alt+F11, I want to take a lot of cutting and pasting. I'm going to take those first three lines and paste them, change it to SlicerCache3, SlicerCache4, SlicerItem3. I'll initialize SlicerCache3, ClicerCache4 to be Region2, Region1, clear the manual filter on SC4-- so that was a copy and paste, take this entire loop here and paste it. There are a lot of places you have to change-- your SI3, SC3 and then SC4, SI3. SI3-- don't miss that one, I missed that one-- next SI3. Alright, so now this set of code will hopefully control two sets of slicers. If you had a third set of slicers you're going to do the same changes I just made, copying and pasting and changing things carefully. Carefully. And again, now, this this guy is the one that we will never see-- we never want to see that one work-- because the ones on the Pivot Table 1 are the controlling ones. So this, we have to copy this-- Ctrl+C-- go to our sheet where we're hiding things away-- so Home, Format, Hide and Unhide, Unhide that sheet (the DarkCave), Paste so it continues to exist, it has to live somewhere, and then once I know it's back there on the DarkCave I can delete it and then hide this sheet here. Alright, and so now we should have on our dashboard, one set of slicers, we choose Central, they both update; we choose Just Flow, they both update; I clear the filter and Central stays. That's actually good. I'm glad that works-- clear this filter and everybody comes back. But these all have to be driving off that first pivot table. What if you have a field in the second data set that's not in your first data set? Then all bets are off. We'll go back to "I don't know how to solve that".
Well, hey, Macros came to the solution today and Macros are amazing and awesome. If you want to learn all about Macros, Tracy Syestad and I have written this great book, "Excel 2016, VBA and Macros." Check that out, Click the "I" on the top right hand corner to get to a page where you can buy that book.
Alright, Episode recap. How can you have a slicer drive two pivot tables? If they both came from the data set it's simple-- Slicer, Report Connections, Choose Other pivot tables. But if a pivot table came from two data sets, lots of steps change-- xlsx to xlsm, change your macro security setting, Alt+F11 to get the VBA, Ctrl+R to display the Project Explorer, find the worksheet name that contains your first pivot table and slicer, right-click and say View Code, and then Insert code for worksheet, Update, then, really important, Hide that second slicer away on a hidden worksheet or far out to the right so no one can ever choose from that slicer. By the way, don't cut that slicer-- you have to copy it and paste and then delete the first one in order to get it to work.
Je tiens à vous remercier pour votre visite, nous vous verrons la prochaine fois pour une autre diffusion sur le net.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2104.xlsm
