I filtri dei dati sono fantastici per le tabelle pivot perché puoi controllare più tabelle pivot da un set di filtri dei dati. Ma questa è una specie di bugia. Puoi controllare più tabelle pivot provenienti dallo stesso set di dati. Quando hai tabelle pivot provenienti da due diversi set di dati, è piuttosto complicato. Ti mostrerò alcuni VBA che ti permetteranno di farlo.
Guarda un video
- Come puoi fare in modo che un'affettatrice guidi due tabelle pivot?
- Se entrambe le tabelle pivot provengono dallo stesso set di dati: seleziona Slicer, Connessioni report, Scegli altre tabelle pivot
- Ma se le tabelle pivot provenivano da diversi set di dati:
- Utilizzare Salva con nome per modificare l'estensione della cartella di lavoro in XLSM anziché in XLSX
- Utilizzare alt = "" + TMS e modificare la sicurezza della macro sulla seconda impostazione.
- Alt + F11 per arrivare a VBA
- Ctrl + R per visualizzare Esplora progetti
- Trova il foglio di lavoro che contiene la tua prima tabella pivot e filtro dei dati
- Inserisci il codice per Worksheet_Update
- Nascondi il secondo filtro dei dati in modo che continui a esistere ma nessuno potrà mai scegliere da quell'affettatrice
Trascrizione del video
Impara Excel per Podcast, episodio 2104: sincronizza i filtri dei dati da diversi set di dati.
Ehi, bentornato al netcast, sono Bill Jelen e la domanda di oggi non riguarda come prendere queste due tabelle pivot che provengono da un set di dati e fare in modo che Slicer controlli tutte quelle tabelle pivot. Non è di questo che si tratta. È una cosa facile da fare: filtro dei dati, strumenti, opzioni, connessioni report o connessioni filtro dei dati nella vecchia versione e verificare che si desidera che questo filtro dei dati controlli tutte quelle tabelle pivot. Facile, vero? Questa domanda riguarda questo foglio di lavoro, in cui abbiamo due diversi set di dati e creeremo una tabella pivot da questo, e da questo - ora fammi accelerare il video mentre creo queste tabelle pivot. Va bene, ora, quello che vedrai è che ho due tabelle pivot, questa tabella pivot viene creata da un set di dati e c'è un'affettatrice che controlla quella tabella pivot;e poi ho una seconda tabella pivot creata da un set di dati diverso e un filtro dei dati che controlla quella tabella pivot. Ma non c'è assolutamente alcun modo per far sì che questo filtro dei dati controlli sia questa tabella pivot che questa tabella pivot creata da un set di dati diverso. Tutto a posto. Ma ti mostrerò come farlo oggi con una macro.
Ora, questo è difficile da fare. Quando è arrivata la domanda, ho detto: "Ora, questo, non credo che tu possa farlo". Ma ci ho lavorato e sperimentato e penso di aver finalmente capito. Devo pensare di aver finalmente capito. Va bene, quindi esaminiamo questo. Prima di tutto, questo viene salvato come file xlsx. Questo è un bel tipo di file, tranne per il fatto che è un tipo di file orribile perché è l'unico tipo di file che non consente le macro. Devi cambiarlo da xlsx a xlsm, o tutto il tuo lavoro per il resto del video verrà buttato fuori dalla finestra. Salva con nome, cambia il tipo di file in xlsm o, diamine, xlsb, uno di questi funzionerà. Quello è quello che è rotto, xlsx, ed è l'impostazione predefinita, pazzo non è vero? Xlsm, fai clic su Salva. Se non hai mai fatto macro prima d'ora, Alt + T per Tom, M per Macro,S per sicurezza e sarai in grado di salvare tutte le macro senza notifica. Devi cambiarlo con il secondo, che consentirà alle tue macro di funzionare.
Bene, ora abbiamo due affettatrici. Scommetto che non l'hai mai saputo, ma le affettatrici hanno un nome. Andremo su Strumenti Slicer, Opzioni, Impostazioni Slicer e vedremo che questo si chiama Slicer_Name. Come quello. Vai al secondo, vai su Strumenti Slicer, Opzioni, Impostazioni Slicer, questo è chiamato Slicer_Name1 - non Name space 1, Name1. Due nomi così.
Ecco cosa faremo. Passeremo a VBA: Alt + F11. In VBA, se non hai mai fatto VBA, avrai questo grande schermo grigio. Verremo qui e diremo Visualizza, Esplora progetti, in Esplora progetti trova il tuo file, il mio si chiama Podcast 2104. Apri gli oggetti di Microsoft Excel e il foglio in cui voglio che funzioni si chiama Dashboard. Faccio clic con il pulsante destro del mouse e dico Visualizza codice. Questo codice che stiamo scrivendo non può essere inserito in un modulo come in una normale macro: deve essere su questo foglio di lavoro. Apri il menu a discesa in alto a sinistra, Foglio di lavoro, quindi nel menu a discesa in alto a destra, diremo Aggiornamento tabella pivot. Va bene, quindi è qui che andrà il nostro codice ora. Ho già precotto questo codice. Diamo un'occhiata al codice qui nel blocco note. Quindi, noi 'avrai due cache Slicer: SC1 e SC2, un elemento Slicer e poi, proprio qui, è qui che dovrai personalizzarlo. Quindi i miei due Slicer si chiamavano Name e Name1. Va bene, dovrai inserire i nomi delle tue affettatrici lì. Application.Screenupdating = False, Application.EnableEvents = False, quindi Slicer Cache 2: cancelleremo il filtro, quindi per ogni elemento SI1 e sc1.SlicerItems, se selezionato, faremo lo stesso elemento in Slicer Cache da selezionare. Questo è un piccolo ciclo che attraverserà tutti gli elementi che si trovano in quell'affettatrice. Nel mio caso, ne ho 11 o 12; nel tuo caso, potresti averne di più.Quindi i miei due Slicer si chiamavano Name e Name1. Va bene, dovrai inserire i nomi delle tue affettatrici lì. Application.Screenupdating = False, Application.EnableEvents = False e quindi Slicer Cache 2: cancelleremo il filtro, quindi per ogni elemento SI1 e sc1.SlicerItems, se è selezionato, faremo lo stesso elemento in Slicer Cache da selezionare. Questo è un piccolo ciclo che verrà eseguito attraverso tutti gli elementi che si trovano in quell'affettatrice. Nel mio caso, ne ho 11 o 12; nel tuo caso, potresti averne di più.Quindi i miei due Slicer si chiamavano Name e Name1. Va bene, dovrai inserire i nomi delle tue affettatrici lì. Application.Screenupdating = False, Application.EnableEvents = False e quindi Slicer Cache 2: cancelleremo il filtro, quindi per ogni elemento SI1 e sc1.SlicerItems, se è selezionato, faremo lo stesso elemento in Slicer Cache da selezionare. Questo è un piccolo ciclo che verrà eseguito attraverso tutti gli elementi che si trovano in quell'affettatrice. Nel mio caso, ne ho 11 o 12; nel tuo caso, potresti averne di più.farai selezionare lo stesso elemento in Slicer Cache. Questo è un piccolo ciclo che verrà eseguito attraverso tutti gli elementi che si trovano in quell'affettatrice. Nel mio caso, ne ho 11 o 12; nel tuo caso, potresti averne di più.farai selezionare lo stesso elemento in Slicer Cache. Questo è un piccolo ciclo che verrà eseguito attraverso tutti gli elementi che si trovano in quell'affettatrice. Nel mio caso, ne ho 11 o 12; nel tuo caso, potresti averne di più.
Quando abbiamo finito, riattiva gli eventi di abilitazione, riattiva l'aggiornamento dello schermo. Tutto a posto. Quindi, prenderemo questo codice, lo copieremo e lo incolleremo qui nel mezzo della nostra macro in questo modo. Va bene, ora, assicuriamoci solo che prema Ctrl + G e che la mia richiesta sia Application.EnableEvents, on o off-- quindi,? Application.EnableEvents-- ed è vero. Se il tuo risulta falso, allora vuoi tornare qui e dire che è = Vero-- quindi, allora, stai attivando quegli eventi. Tutto a posto. Ora, ecco cosa succederà. Quindi il nostro allenatore dovrebbe lavorare qui, è sul foglio di lavoro giusto. Siamo salvati in un file xlxm e ho attivato le macro e quello che vedremo è che quando scelgo dall'affettatrice di sinistra, quell'affettatrice Cache 1 - io "Sceglierò Andy tramite Della-- anche l'altro Slicer si aggiornerà. Va bene E anche se scegliessi solo Gloria, solo Gloria, sembra che funzioni davvero, davvero bene. Anche se premessi CTRL + clic, quando lascio andare Ctrl, si aggiorneranno tutti e tre.
Ma ecco il trucco-- c'è sempre un trucco-- questo Slicer, deve esistere, ma non puoi usare questo Slicer-- aspetta, voglio dire che puoi, puoi usare un Slicer ma confonderà le cose . Perché quello che succederà è che lo cambierò in Hank e torneranno a tutto ciò che è in Slicer Cache 1, perché ho cambiato la tabella pivot su questo foglio. Ora, nella vita reale, avrai due tabelle pivot sullo stesso foglio? Non so se lo sei o se non lo sei, va bene, ma le cose diventeranno un po 'pazze.
Ora, diamo solo un'occhiata a questo. La prima cosa che voglio fare è inserire un nuovo foglio di lavoro - Alt + IW per inserire il foglio di lavoro - e lo chiamerò DarkCave. Puoi chiamarlo come vuoi. Prenderò quella dashboard che non funzionerà, copierò quella dashboard e verrò qui nella caverna oscura e incollarla lì, quindi fare clic con il pulsante destro del mouse e nascondere quel foglio in modo che nessuno veda mai quell'affettatrice. E poi, da qui, dovremmo essere in grado di eliminarlo. Bello, va bene. E controlleremo solo per assicurarci che funzionino ancora: scegli Charlie tramite Eddie e si stanno ancora aggiornando. Cosa sta succedendo adesso? Anche lo Slicer che non possiamo vedere, quello che abbiamo nascosto, si sta aggiornando, ma non ci interessa che si aggiorni.
Ora, cosa succede se vuoi avere le tue cose su fogli diversi? Inserirò un nuovo foglio di lavoro qui - Alt + IW-- e prenderò una di queste tabelle pivot, forse la seconda tabella pivot, e la sposterò su quell'altro foglio, quindi Ctrl + C per copiare la tabella pivot, Ctrl + V per incollare qui la tabella pivot. E se ho bisogno di avere un filtro dei dati qui, non inserire una fetta da questa tabella pivot, dobbiamo tornare alla nostra dashboard, prendere il filtro dei dati che è il controllo dei dati, Ctrl + C per farne una copia, e incollalo qui - Ctrl + V. Tutto a posto? Ora, non abbiamo codice su questo foglio - non c'è codice su Sheet4 - e stavo pensando che avrei dovuto aggiungere del codice a Sheet4, ma ecco la cosa bella: quando cambio questo slicer, quello che sta succedendo è, sulla dashboard quella tabella pivot "s si aggiorna anche se quella tabella pivot su quel foglio che non è attivo si sta aggiornando, eseguiranno il codice e anche questo si aggiornerà. È davvero incredibile che funzioni.
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.
Voglio ringraziarti per essere passato, ci vediamo la prossima volta per un altro netcast da.
Download file
Scarica il file di esempio qui: Podcast2104.xlsm
