Slicer sangat bagus untuk jadual pangsi kerana anda dapat mengawal beberapa jadual pangsi dari satu set pemotong. Tetapi - itu semacam pembohongan. Anda boleh mengawal beberapa jadual pangsi yang berasal dari set data yang sama. Apabila anda mempunyai jadual pangsi yang berasal dari dua set data yang berbeza, agak sukar. Saya akan menunjukkan kepada anda beberapa VBA yang akan membolehkan anda melakukannya.
Tonton Video
- Bagaimana anda boleh menggunakan slicer drive dua jadual pangsi?
- Sekiranya kedua-dua jadual pangsi berasal dari kumpulan data yang sama: Pilih Pemotong, Laporkan Sambungan, Pilih Jadual Pangsi Lain
- Tetapi jika jadual pangsi datang dari set data yang berbeza:
- Gunakan Save As untuk menukar peluasan buku kerja kepada XLSM dan bukannya XLSX
- Gunakan alt = "" + TMS dan ubah keselamatan makro ke tetapan kedua.
- Alt + F11 untuk sampai ke VBA
- Ctrl + R untuk memaparkan projek peneroka
- Cari lembaran kerja yang mengandungi jadual pangsi dan pemotong pertama anda
- Masukkan kod untuk Lembaran Kerja_Update
- Sembunyikan alat pemotong kedua sehingga ia tetap ada tetapi tidak ada yang dapat memilih dari alat pemotong itu
Transkrip Video
Belajar Excel untuk Podcast, Episod 2104: Segerakkan Slicers dari Kumpulan Data yang berbeza.
Hai, selamat datang kembali ke netcast, saya Bill Jelen, dan soalan hari ini bukan mengenai bagaimana mengambil dua jadual pangsi ini yang berasal dari satu set data dan menjadikan Slicer mengawal semua jadual pangsi tersebut. Bukan itu maksudnya. Itu adalah perkara yang mudah dilakukan - Slicer, Tools, Options, baik Report Connection atau Slicer Connections dalam versi lama, dan pastikan anda mahu Slicer ini mengawal semua jadual pangsi tersebut. Mudah, bukan? Soalan ini adalah mengenai lembaran kerja ini, di mana kami mempunyai dua set data yang berbeza dan kami akan membuat jadual pangsi dari ini, dan dari ini - sekarang izinkan saya mempercepat video semasa saya membuat jadual pangsi ini. Baiklah, sekarang, yang akan anda lihat ialah, saya mempunyai dua jadual pangsi, jadual pangsi ini dibuat dari satu set data, dan ada alat pemotong yang mengawal jadual pangsi itu;dan kemudian saya mempunyai jadual pangsi kedua yang dibuat dari set data yang berbeza, dan alat pemotong yang mengawal jadual pangsi itu. Tetapi sama sekali tidak ada cara untuk mendapatkan alat pemotong ini untuk mengawal jadual pangsi ini dan jadual pangsi ini yang dibina dari set data yang berbeza. Baiklah. Tetapi saya akan menunjukkan kepada anda bagaimana untuk melakukannya hari ini dengan makro.
Sekarang, ini sukar dilakukan. Ketika soalan itu masuk, saya berkata, "Sekarang, saya rasa anda tidak boleh melakukannya." Tetapi saya telah mengusahakannya dan bereksperimen dan saya rasa akhirnya saya berjaya. Saya harus fikir saya akhirnya berjaya menurunkannya. Baiklah, jadi mari kita selesaikan ini. Pertama, ini disimpan sebagai fail xlsx. Itu jenis fail yang bagus, kecuali jenis fail yang mengerikan kerana ia adalah satu-satunya jenis fail yang tidak membenarkan makro. Anda harus menukarnya dari xlsx ke xlsm, atau semua kerja anda ke selebihnya video akan dibuang ke luar tingkap. Simpan sebagai, ubah jenis fail ke xlsm atau, heck, xlsb, salah satu daripadanya akan berfungsi. Itulah yang rosak-- xlsx-- dan itu lalai, gila bukan? Xlsm, klik Simpan. Sekiranya anda belum pernah melakukan makro sebelumnya, Alt + T untuk Tom, M untuk Makro,S untuk Keselamatan dan anda akan dapat menyimpan semua makro tanpa pemberitahuan. Perlu mengubahnya menjadi yang kedua, yang membolehkan makro anda berfungsi.
Baiklah, sekarang kita mempunyai dua alat pemotong. Bertaruh anda tidak pernah tahu ini, tetapi pemotong mempunyai nama. Kita akan pergi ke Slicer Tools, Options, Slicer Settings, dan melihat yang ini disebut Slicer_Name. Macam itu. Pergi ke yang kedua, pergi ke Slicer Tools, Options, Slicer Settings, yang disebut Slicer_Name1-- bukan Nama ruang 1, Nama1. Dua nama seperti itu.
Inilah yang akan kami lakukan. Kita akan beralih ke VBA-- Alt + F11. Di VBA, jika anda tidak pernah melakukan VBA, anda akan mempunyai skrin kelabu yang besar ini. Kami akan datang ke sini dan mengatakan Lihat, Project Explorer, di Project Explorer cari fail anda - saya dipanggil Podcast 2104. Buka Objek Microsoft Excel, dan helaian di mana saya mahu ini berfungsi dipanggil Dashboard. Saya akan klik kanan di sana dan mengatakan Lihat Kod. Kod ini yang kita tulis tidak boleh masuk dalam modul seperti dalam makro biasa - ini mesti ada di lembaran kerja ini. Buka drop-down kiri atas, Lembaran Kerja, kemudian di drop-down kanan atas, kita akan mengatakan Pivot Table Update. Baiklah, jadi di sinilah kod kita akan pergi sekarang. Saya sudah membuat kod ini terlebih dahulu. Mari lihat kodnya di notepad. Jadi, kita 'akan mempunyai dua cache Slicer - SC1 dan SC2 - satu item Slicer dan kemudian, di sini, di sinilah anda perlu menyesuaikannya. Oleh itu, dua Slicers saya dipanggil Nama dan Nama1. Baiklah, anda mesti meletakkan nama pemotong anda di sana. Application.Screenupdating = False, Application.EnableEvents = False, dan kemudian Slicer Cache 2-- kita akan membersihkan penapis, dan kemudian untuk setiap item SI1 dan sc1.SlicerItems, jika ia dipilih, maka kita akan membuat item yang sama dalam Slicer Cache untuk dipilih. Ini adalah gelung kecil yang akan dilalui namun banyak item yang terdapat dalam alat pemotong itu. Dalam kes saya, saya mempunyai 11 atau 12; dalam kes anda, anda mungkin mempunyai lebih banyak.Oleh itu, dua Slicers saya dipanggil Nama dan Nama1. Baiklah, anda mesti meletakkan nama pemotong anda di sana. Application.Screenupdating = False, Application.EnableEvents = False, dan kemudian Slicer Cache 2-- kita akan membersihkan penapis, dan kemudian untuk setiap item SI1 dan sc1.SlicerItems, jika ia dipilih, maka kita akan membuat item yang sama dalam Slicer Cache untuk dipilih. Ini adalah gelung kecil yang akan dilalui namun banyak item yang terdapat dalam alat pemotong itu. Dalam kes saya, saya mempunyai 11 atau 12; dalam kes anda, anda mungkin mempunyai lebih banyak.Oleh itu, dua Slicers saya dipanggil Nama dan Nama1. Baiklah, anda mesti meletakkan nama pemotong anda di sana. Application.Screenupdating = False, Application.EnableEvents = False, dan kemudian Slicer Cache 2-- kita akan membersihkan penapis, dan kemudian untuk setiap item SI1 dan sc1.SlicerItems, jika ia dipilih, maka kita akan membuat item yang sama dalam Slicer Cache untuk dipilih. Ini adalah gelung kecil yang akan dilalui namun banyak item yang terdapat dalam alat pemotong itu. Dalam kes saya, saya mempunyai 11 atau 12; dalam kes anda, anda mungkin mempunyai lebih banyak.akan membuat item yang sama di Slicer Cache untuk dipilih. Ini adalah gelung kecil yang akan dilalui namun banyak item yang terdapat dalam alat pemotong itu. Dalam kes saya, saya mempunyai 11 atau 12; dalam kes anda, anda mungkin mempunyai lebih banyak.akan membuat item yang sama di Slicer Cache untuk dipilih. Ini adalah gelung kecil yang akan dilalui namun banyak item yang terdapat dalam alat pemotong itu. Dalam kes saya, saya mempunyai 11 atau 12; dalam kes anda, anda mungkin mempunyai lebih banyak.
Setelah selesai, hidupkan semula acara aktifkan, hidupkan semula Kemas kini Skrin. Baiklah. Oleh itu, kita akan mengambil kod ini, menyalin kod ini dan menampalnya di tengah makro kita seperti itu. Baiklah, sekarang, mari kita pastikan saya akan menekan Ctrl + G dan yang saya minta adalah Application. EnableEvents, on or off-- jadi,? Aplikasi. EnableEvents-- dan itu benar. Sekiranya anda muncul sebagai tidak benar, maka anda ingin kembali ke sini dan mengatakan bahawa itu = Benar - jadi, maka, anda menghidupkan acara tersebut. Baiklah. Sekarang, inilah yang akan berlaku. Oleh itu, jurulatih kami harus bekerja di sini, ia berada di lembaran kerja yang betul. Kami disimpan dalam fail xlxm, dan saya menghidupkan Makro dan apa yang akan kita lihat, adalah apabila saya memilih dari Slicer kiri, Slicer Cache 1-- I 'Saya akan memilih Andy melalui Della - Slicer lain akan dikemas kini juga. Baiklah Dan walaupun saya memilih hanya Gloria - hanya Gloria - nampaknya ia berfungsi dengan sangat baik. Walaupun saya klik CTRL +, semasa saya melepaskan Ctrl, ketiga-tiganya akan dikemas kini.
Tetapi inilah gotcha - selalu ada gotcha - Slicer ini, ia mesti ada, tetapi anda tidak boleh menggunakan Slicer ini - tunggu, maksud saya anda boleh, anda boleh menggunakan Slicer tetapi ia akan membingungkan . Kerana apa yang akan berlaku adalah saya akan mengubahnya menjadi Hank dan mereka akan kembali kepada apa sahaja yang ada di Slicer Cache 1, kerana saya menukar jadual pangsi pada helaian ini. Sekarang, dalam kehidupan sebenar, adakah anda akan mempunyai dua jadual pangsi pada helaian yang sama? Saya tidak tahu sama ada anda atau tidak, tidak mengapa, tetapi perkara akan menjadi sedikit gila.
Sekarang, mari kita lihat ini. Perkara pertama yang ingin saya lakukan, adalah saya akan memasukkan lembaran kerja baru - Alt + IW untuk memasukkan lembaran kerja - dan saya akan memanggilnya sebagai DarkCave. Anda boleh memanggilnya apa sahaja yang anda mahukan. Saya akan mengambil papan pemuka yang tidak berfungsi, saya akan menyalin papan pemuka itu dan datang ke sini ke gua gelap dan tampal di sana dan kemudian klik kanan dan sembunyikan helaian itu sehingga tidak ada yang melihat Slicer itu. Dan dari sini, kita seharusnya dapat menghapusnya. Bagus, baiklah. Dan kami akan memeriksa untuk memastikan mereka masih berfungsi - pilih Charlie melalui Eddie dan mereka berdua masih mengemas kini. Sekarang, apa yang berlaku? Slicer yang tidak dapat kita lihat, yang kita sembunyikan, ia juga akan dikemas kini, tetapi kita tidak peduli bahawa ia akan dikemas kini.
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.
Ingin mengucapkan terima kasih kerana berhenti, kami akan berjumpa dengan anda untuk lain-lain netcast dari.
Muat turun fail
Muat turun fail sampel di sini: Podcast2104.xlsm
