Slicers sunt minunate pentru tabelele pivot, deoarece puteți controla mai multe tabele pivot dintr-un set de slicers. Dar - asta este un fel de minciună. Puteți controla mai multe tabele pivot care provin din același set de date. Când aveți tabele pivot care provin din două seturi de date diferite, este destul de dificil. Vă voi arăta niște VBA care vă vor permite să scoateți acest lucru.
Urmăriți videoclipul
- Cum poți avea un slicer care să conducă două tabele pivot?
- Dacă ambele tabele pivot provin din același set de date: Selectați Slicer, Raportați conexiuni, Alegeți alte tabele pivot
- Dar dacă tabelele pivot provin din seturi de date diferite:
- Utilizați Salvare ca pentru a schimba extensia registrului de lucru în XLSM în loc de XLSX
- Utilizați alt = "" + TMS și schimbați securitatea macrocomenzii la a doua setare.
- Alt + F11 pentru a ajunge la VBA
- Ctrl + R pentru a afișa exploratorul de proiecte
- Găsiți foaia de lucru care conține primul tabel pivot și feliatorul
- Introduceți codul pentru Worksheet_Update
- Ascundeți cea de-a doua felie, astfel încât să rămână existentă, dar nimeni nu poate alege vreodată din felia respectivă
Transcriere video
Aflați Excel pentru Podcast, Episodul 2104: Sincronizați secționatoarele din diferite seturi de date.
Bună, bine ați revenit la netcast, eu sunt Bill Jelen, iar întrebarea de astăzi nu este despre cum să luați aceste două tabele pivot care provin dintr-un singur set de date și să faceți Slicerul să controleze toate acele tabele pivot. Nu despre asta este vorba. Acesta este un lucru ușor de făcut - Slicer, Instrumente, Opțiuni, fie Raport conexiuni, fie Slicer Connections în versiunea veche și verificați dacă doriți ca acest Slicer să controleze toate aceste tabele pivot. Ușor, nu? Această întrebare se referă la această foaie de lucru, în care avem două seturi de date diferite și vom crea un tabel pivot din aceasta și din aceasta - acum permiteți-mi să accelerez videoclipul în timp ce creez aceste tabele pivot. Bine, acum, ceea ce veți vedea este, am două tabele pivot, acest tabel pivot este creat dintr-un singur set de date și există un feliator care controlează acel tabel pivot;și apoi am un al doilea tabel pivot creat dintr-un set de date diferit și un feliat care controlează acel tabel pivot. Dar nu există absolut nicio modalitate de a obține acest feliator pentru a controla atât acest tabel pivot, cât și acest tabel pivot care este construit dintr-un set de date diferit. Bine. Dar am să vă arăt cum să faceți asta astăzi cu un macro.
Acum, este dificil de făcut. Când a apărut întrebarea, am spus: „Acum, asta, nu cred că o poți face”. Dar am lucrat la asta și am experimentat și cred că am reușit în sfârșit. Trebuie să cred că în cele din urmă am dat-o jos. Bine, deci hai să trecem prin asta. În primul rând, acesta este salvat ca fișier xlsx. Acesta este un tip de fișier fin, cu excepția unui tip de fișier oribil, deoarece este singurul tip de fișier care nu permite macrocomenzile. Trebuie să schimbați acest lucru din xlsx în xlsm, sau toată munca dvs. cu restul videoclipului va fi aruncată pe fereastră. Salvați ca, schimbați tipul de fișier în xlsm sau, heck, xlsb, oricare dintre acestea va funcționa. Acesta este cel care este rupt - xlsx-- și este implicit, nebun, nu-i așa? Xlsm, faceți clic pe Salvare. Dacă nu ați făcut niciodată macrocomenzi, Alt + T pentru Tom, M pentru Macro,S for Security și veți putea salva toate macrocomenzile fără notificare. Trebuie să schimbați acest lucru cu cel de-al doilea, care va permite macro-urilor să funcționeze.
Bine, acum avem două felii. Pariez că nu ai știut niciodată acest lucru, dar feliatorii au nume. Vom merge la Instrumente pentru slicare, Opțiuni, Setări pentru slicer și vom vedea că acesta se numește Slicer_Name. Ca asta. Mergeți la al doilea, accesați Instrumente pentru secționare, Opțiuni, Setări pentru secționare, acesta se numește Slicer_Name1 - nu Spațiu nume 1, Nume1. Două nume de genul asta.
Iată ce vom face. Vom trece la VBA - Alt + F11. În VBA, dacă nu ați făcut niciodată VBA, veți avea acest ecran mare și gri. Vom veni aici și vom spune View, Project Explorer, în Project Explorer, găsiți fișierul dvs. - al meu se numește Podcast 2104. Deschideți Microsoft Excel Objects, iar foaia unde vreau să funcționeze se numește Dashboard. Voi face clic dreapta acolo și voi spune View Code. Acest cod pe care îl scriem nu poate merge într-un modul ca într-o macro obișnuită - acest lucru trebuie să fie în această foaie de lucru. Deschideți meniul derulant din stânga sus, Foaia de lucru, apoi în meniul derulant din dreapta sus, vom spune Actualizare tabel pivot. Bine, deci aici va merge codul nostru acum. Am pregătit deja acest cod. Să aruncăm o privire la codul din notepad. Deci, noiVei avea două cache-uri Slicer - SC1 și SC2-- un articol Slicer și apoi, chiar aici, aici va trebui să îl personalizezi. Așadar, cele două Slicere ale mele s-au numit Name și Name1. Bine, va trebui să-ți pui numele tăietorului acolo. Application.Screenupdating = False, Application.EnableEvents = False și apoi Slicer Cache 2 - vom șterge filtrul, apoi pentru fiecare element SI1 și sc1.SlicerItems, dacă este selectat, atunci vom face să fie selectat același element din Slicer Cache. Aceasta este o buclă mică care va rula prin oricât de multe se vor întâmpla să fie în acea feliată. În cazul meu, am 11 sau 12; în cazul tău, s-ar putea să ai mai multe.Așadar, cele două felii mele au fost numite Name și Name1. Bine, va trebui să-ți pui numele tăietorului acolo. Application.Screenupdating = False, Application.EnableEvents = False și apoi Slicer Cache 2 - vom șterge filtrul, apoi pentru fiecare element SI1 și sc1.SlicerItems, dacă este selectat, atunci vom face să fie selectat același element din Slicer Cache. Aceasta este o buclă mică care va rula prin oricât de multe se vor întâmpla să fie în acea feliată. În cazul meu, am 11 sau 12; în cazul tău, s-ar putea să ai mai multe.Așadar, cele două Slicere ale mele s-au numit Name și Name1. Bine, va trebui să-ți pui numele tăietorului acolo. Application.Screenupdating = False, Application.EnableEvents = False și apoi Slicer Cache 2 - vom șterge filtrul, apoi pentru fiecare element SI1 și sc1.SlicerItems, dacă este selectat, atunci vom face să fie selectat același element din Slicer Cache. Aceasta este o buclă mică care va rula prin oricât de multe se vor întâmpla să fie în acea feliată. În cazul meu, am 11 sau 12; în cazul tău, s-ar putea să ai mai multe.Voi face ca același element din Slicer Cache să fie selectat. Aceasta este o buclă mică care va rula prin oricât de multe elemente se vor afla în acea feliată. În cazul meu, am 11 sau 12; în cazul tău, s-ar putea să ai mai multe.Voi face să fie selectat același element în Slicer Cache. Aceasta este o buclă mică care va rula prin oricât de multe se vor întâmpla să fie în acea feliată. În cazul meu, am 11 sau 12; în cazul tău, s-ar putea să ai mai multe.
Când am terminat cu asta, reactivați activarea evenimentelor, reactivați Actualizarea ecranului. Bine. Deci, vom lua acest cod, vom copia acest cod și îl vom lipi aici în mijlocul macro-ului nostru așa. În regulă, acum, să ne asigurăm că voi apăsa Ctrl + G și că îmi cer aplicația. Application.EnableEvents - și este adevărat. Dacă al tău apare ca fals, atunci vrei să revii aici și să spui că este = Adevărat - deci, atunci activezi aceste evenimente. Bine. Acum, iată ce se va întâmpla. Deci, antrenorul nostru ar trebui să lucreze aici, este pe foaia de lucru potrivită. Suntem salvați într-un fișier xlxm și am activat Macros și ceea ce vom vedea este că atunci când aleg din Slicer-ul din stânga, acel Slicer Cache 1 - I 'Îl voi alege pe Andy prin Della-- și celălalt Slicer se va actualiza. Bine Și chiar dacă aș alege doar Gloria - doar Gloria - se pare că funcționează foarte, foarte bine. Chiar dacă aș face CTRL + clic, când voi da drumul la Ctrl, toate cele trei se vor actualiza.
Dar iată gotcha-- există întotdeauna un gotcha-- acest feliator, trebuie să existe, dar nu puteți folosi acest feliator - așteptați, vreau să spun că puteți, puteți utiliza un feliator, dar va confunda diavolul lucrurilor . Pentru că ceea ce se va întâmpla este că voi schimba acest lucru în Hank și vor reveni la orice este în Slicer Cache 1, pentru că am schimbat tabelul pivot de pe această foaie. Acum, în viața reală, veți avea două tabele pivot pe aceeași foaie? Nu știu dacă ești sau dacă nu ești, bine, dar lucrurile vor deveni puțin nebunești.
Acum, să aruncăm o privire la asta. Primul lucru pe care vreau să-l fac este să introduc o nouă foaie de lucru - Alt + IW pentru inserarea foii de lucru - și voi numi asta DarkCave. Îi poți numi orice vrei. Voi lua tabloul de bord care nu va funcționa, voi copia acel tablou de bord și vin aici în peștera întunecată și îl lipesc acolo și apoi faceți clic dreapta și ascundeți foaia, astfel încât nimeni să nu vadă vreodată Slicerul. Și apoi, de aici, ar trebui să îl putem șterge. Frumos, bine. Și vom verifica doar pentru a ne asigura că funcționează în continuare - alegeți Charlie prin Eddie și amândoi se actualizează în continuare. Acum, ce se întâmplă? Slicer-ul pe care nu-l putem vedea, cel pe care l-am ascuns, se actualizează și el, dar nu ne pasă că se actualizează.
Acum, dacă vrei să ai lucrurile pe diferite foi? Voi insera aici o nouă foaie de lucru - Alt + IW - și voi lua unul dintre aceste tabele pivot - poate al doilea tabel pivot - și îl voi muta în acea altă foaie - deci, Ctrl + C pentru a copia tabelul pivot, Ctrl + V pentru a lipi tabelul pivot aici. Și dacă trebuie să am un feliator aici - nu introduceți o felie din acest tabel pivot - trebuie să ne întoarcem la tabloul nostru de bord, luați feliatorul care este feliatorul de control, Ctrl + C pentru a face o copie a acestuia, și lipiți-l aici - Ctrl + V. Bine? Acum, nu avem niciun cod pe această foaie - nu există niciun cod pe foaia 4 - și mă gândeam că va trebui să adaug un cod la foaia 4, dar iată ce este frumos: când schimb acest feliator, pe tabloul de bord care pivotează tabelul 'Se actualizează, chiar dacă tabelul pivot din foaia respectivă care nu este activă se actualizează, vor rula codul și acesta se va actualiza și. Foarte dracului de uimitor că funcționează.
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.
Vreau să vă mulțumesc că ați trecut pe aici, ne vedem data viitoare pentru un alt netcast de la.
Descărcare fișier
Descărcați exemplarul de fișier aici: Podcast2104.xlsm
