Las segmentaciones son increíbles para tablas dinámicas porque puede controlar varias tablas dinámicas desde un conjunto de segmentaciones. Pero eso es una especie de mentira. Puede controlar varias tablas dinámicas que provienen del mismo conjunto de datos. Cuando tiene tablas dinámicas que provienen de dos conjuntos de datos diferentes, es bastante complicado. Te mostraré algunos VBA que te permitirán lograrlo.
Ver video
- ¿Cómo puede hacer que una cortadora controle dos tablas dinámicas?
- Si ambas tablas dinámicas provienen del mismo conjunto de datos: seleccione Slicer, Report Connections, Choose Other Pivot Tables
- Pero si las tablas dinámicas provienen de diferentes conjuntos de datos:
- Use Guardar como para cambiar la extensión del libro de trabajo a XLSM en lugar de XLSX
- Utilice alt = "" + TMS y cambie la seguridad de macros a la segunda configuración.
- Alt + F11 para llegar a VBA
- Ctrl + R para mostrar el explorador de proyectos
- Busque la hoja de trabajo que contiene su primera tabla dinámica y segmentación
- Inserte el código de Worksheet_Update
- Oculte la segunda cortadora para que siga existiendo, pero nadie podrá elegir de esa cortadora
Transcripción del video
Aprenda Excel para Podcast, Episodio 2104: Sincronización de segmentaciones de diferentes conjuntos de datos.
Oye, bienvenido de nuevo al netcast, soy Bill Jelen, y la pregunta de hoy no se trata de cómo tomar estas dos tablas dinámicas que provienen de un conjunto de datos y hacer que el Slicer controle todas esas tablas dinámicas. No se trata de eso. Eso es algo fácil de hacer: Slicer, Tools, Options, Report Connections o Slicer Connections en la versión anterior, y verifique que desea que Slicer controle todas esas tablas dinámicas. Fácil, ¿verdad? Esta pregunta es sobre esta hoja de trabajo, donde tenemos dos conjuntos de datos diferentes y vamos a crear una tabla dinámica a partir de esto, y a partir de esto, ahora permítanme acelerar el video mientras creo estas tablas dinámicas. Muy bien, ahora, lo que van a ver es que tengo dos tablas dinámicas, esta tabla dinámica se crea a partir de un conjunto de datos y hay un selector que controla esa tabla dinámica;y luego tengo una segunda tabla dinámica que se crea a partir de un conjunto de datos diferente y una cortadora que controla esa tabla dinámica. Pero no hay absolutamente ninguna manera de hacer que esta cortadora controle tanto esta tabla dinámica como esta tabla dinámica construida a partir de un conjunto de datos diferente. Bien. Pero te mostraré cómo hacerlo hoy con una macro.
Ahora, esto es complicado de hacer. Cuando surgió la pregunta, dije: "Bueno, esto, no creo que puedas hacerlo". Pero he estado trabajando en ello y experimentando y creo que finalmente lo conseguí. Tengo que pensar que finalmente lo logré. Muy bien, repasemos esto. En primer lugar, esto se guarda como un archivo xlsx. Ese es un buen tipo de archivo, excepto que es un tipo de archivo horrible porque es el único tipo de archivo que no permite macros. Tienes que cambiar esto de xlsx a xlsm, o todo tu trabajo en el resto del video se tirará por la ventana. Guardar como, cambiar el tipo de archivo a xlsm o, diablos, xlsb, cualquiera de ellos funcionará. Ese es el que está roto, xlsx, y es el predeterminado, ¿no es una locura? Xlsm, haga clic en Guardar. Si nunca ha hecho macros antes, Alt + T para Tom, M para Macro,S de seguridad y podrá guardar todas las macros sin notificación. Necesita cambiar eso al segundo, que permitirá que sus macros funcionen.
Muy bien, ahora tenemos dos rebanadores. Apuesto a que nunca lo supo, pero los rebanadores tienen nombres. Vamos a ir a Slicer Tools, Options, Slicer Settings, y veremos que este se llama Slicer_Name. Como eso. Vaya al segundo, vaya a Slicer Tools, Options, Slicer Settings, este se llama Slicer_Name1, no el espacio de nombres 1, Name1. Dos nombres así.
Esto es lo que vamos a hacer. Vamos a cambiar a VBA: Alt + F11. En VBA, si nunca ha hecho VBA, tendrá esta gran pantalla gris. Vamos a venir aquí y decir Ver, Explorador de proyectos, en el Explorador de proyectos, busque su archivo, el mío se llama Podcast 2104. Abra Objetos de Microsoft Excel, y la hoja donde quiero que esto funcione se llama Tablero. Voy a hacer clic derecho allí y decir Ver código. Este código que estamos escribiendo no puede ir en un módulo como en una macro normal, esto tiene que estar en esta hoja de trabajo. Abra el menú desplegable superior izquierdo, Hoja de trabajo, luego, en el menú desplegable superior derecho, vamos a decir Actualización de tabla dinámica. Muy bien, aquí es donde irá nuestro código ahora. Ya preparé este código. Echemos un vistazo al código aquí en el bloc de notas. Así que nosotros'Tendrás dos cachés de Slicer, SC1 y SC2, un elemento de Slicer y luego, aquí mismo, aquí es donde tendrás que personalizarlo. Entonces mis dos rebanadores se llamaron Nombre y Nombre1. Muy bien, tendrás que poner los nombres de tus segmentadores allí. Application.Screenupdating = False, Application.EnableEvents = False, y luego Slicer Cache 2 - vamos a borrar el filtro, y luego para cada elemento SI1 y sc1.SlicerItems, si está seleccionado, entonces vamos a hacer el mismo elemento en Slicer Cache para ser seleccionado. Este es un pequeño bucle que se ejecutará sin importar cuántos elementos haya en esa cortadora. En mi caso, tengo 11 o 12; en su caso, es posible que tenga más.Entonces mis dos rebanadores se llamaron Nombre y Nombre1. Muy bien, tendrás que poner los nombres de tus segmentadores allí. Application.Screenupdating = False, Application.EnableEvents = False, y luego Slicer Cache 2 - vamos a borrar el filtro, y luego para cada elemento SI1 y sc1.SlicerItems, si está seleccionado, entonces vamos a hacer el mismo elemento en Slicer Cache para ser seleccionado. Este es un pequeño bucle que se ejecutará sin importar cuántos elementos haya en esa cortadora. En mi caso, tengo 11 o 12; en su caso, es posible que tenga más.Entonces mis dos rebanadores se llamaron Nombre y Nombre1. Muy bien, tendrás que poner los nombres de tus segmentadores allí. Application.Screenupdating = False, Application.EnableEvents = False, y luego Slicer Cache 2 - vamos a borrar el filtro, y luego para cada elemento SI1 y sc1.SlicerItems, si está seleccionado, entonces vamos a hacer el mismo elemento en Slicer Cache para ser seleccionado. Este es un pequeño bucle que se ejecutará sin importar cuántos elementos haya en esa cortadora. En mi caso, tengo 11 o 12; en su caso, es posible que tenga más.vamos a hacer que el mismo elemento en Slicer Cache sea seleccionado. Este es un pequeño bucle que se ejecutará sin importar cuántos elementos haya en esa cortadora. En mi caso, tengo 11 o 12; en su caso, es posible que tenga más.vamos a hacer que el mismo elemento en Slicer Cache sea seleccionado. Este es un pequeño bucle que se ejecutará sin importar cuántos elementos haya en esa cortadora. En mi caso, tengo 11 o 12; en su caso, es posible que tenga más.
Cuando terminemos con eso, vuelva a activar los eventos de habilitación, vuelva a activar la Actualización de pantalla. Bien. Entonces, tomaremos este código, copiaremos este código y lo pegaremos aquí en medio de nuestra macro así. Bien, ahora, asegurémonos de presionar Ctrl + G y mi solicitud es Application.EnableEvents, activada o desactivada, entonces,? Application.EnableEvents, y es cierto. Si el tuyo resulta falso, entonces querrás volver aquí y decir que es = Verdadero, entonces, estás activando esos eventos. Bien. Ahora, esto es lo que va a pasar. Entonces nuestro entrenador debería estar trabajando aquí, está en la hoja de trabajo correcta. Estamos guardados en un archivo xlxm, encendí Macros y lo que vamos a ver es que cuando elijo del Slicer izquierdo, ese Slicer Cache 1-- I 'Elegiré a Andy a través de Della; el otro Slicer también se actualizará. Muy bien E incluso si eligiera solo a Gloria, solo a Gloria, parece que está funcionando muy, muy bien. Incluso si hiciera CTRL + clic, cuando suelte Ctrl, los tres se actualizarán.
Pero aquí está el problema, siempre hay un problema, este cortador, tiene que existir, pero no puedes usar este cortador, espera, quiero decir, puedes usar un cortador, pero va a confundir muchísimo las cosas. . Porque lo que va a pasar es que voy a cambiar esto a Hank y ellos volverán a lo que sea que esté en Slicer Cache 1, porque cambié la tabla dinámica en esta hoja. Ahora, en la vida real, ¿vas a tener dos tablas dinámicas en la misma hoja? No sé si lo estás o no, está bien, pero las cosas se van a poner un poco locas.
Ahora, echemos un vistazo a esto. Lo primero que quiero hacer es insertar una nueva hoja de trabajo (Alt + IW para insertar la hoja de trabajo) y la voy a llamar DarkCave. Puedes llamarlo como quieras. Voy a tomar ese tablero que no va a funcionar, voy a copiar ese tablero y venir aquí a la cueva oscura y pegarlo allí y luego hacer clic derecho y ocultar esa hoja para que nadie vea ese Slicer. Y luego, desde aquí, deberíamos poder eliminarlo. Bien, está bien. Y solo vamos a verificar para asegurarnos de que todavía estén funcionando: elija a Charlie a través de Eddie y ambos todavía se están actualizando. Ahora, ¿qué está pasando? El Slicer que no podemos ver, el que hemos escondido, también se está actualizando, pero no nos importa que se esté actualizando.
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.
Quiero agradecerle por pasar, nos vemos la próxima vez para otro netcast de.
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2104.xlsm
