Os Slicers são fantásticos para tabelas dinâmicas porque você pode controlar várias tabelas dinâmicas a partir de um conjunto de Slicers. Mas - isso é uma espécie de mentira. Você pode controlar várias tabelas dinâmicas provenientes do mesmo conjunto de dados. Quando você tem tabelas dinâmicas provenientes de dois conjuntos de dados diferentes, é bastante complicado. Eu vou te mostrar um pouco de VBA que vai deixar você fazer isso.
Assistir vídeo
- Como você pode fazer com que uma segmentação de dados direcione duas tabelas dinâmicas?
- Se as duas tabelas dinâmicas vierem do mesmo conjunto de dados: Selecione Slicer, Report Connections, Choose Other Pivot Tables
- Mas se as tabelas dinâmicas vierem de conjuntos de dados diferentes:
- Use Salvar como para alterar a extensão da pasta de trabalho para XLSM em vez de XLSX
- Use alt = "" + TMS e altere a segurança de macro para a segunda configuração.
- Alt + F11 para chegar ao VBA
- Ctrl + R para exibir o explorador de projetos
- Encontre a planilha que contém sua primeira tabela dinâmica e segmentação de dados
- Insira o código para Worksheet_Update
- Esconda o segundo fatiador para que ele continue existindo, mas ninguém pode escolher aquele divisor
Transcrição de vídeo
Aprenda Excel para Podcast, Episódio 2104: Sincronizar Slicers de Diferentes Conjuntos de Dados.
Ei, bem-vindo de volta ao netcast, sou Bill Jelen, e a pergunta de hoje não é sobre como pegar essas duas tabelas dinâmicas que vieram de um conjunto de dados e fazer com que o Slicer controle todas essas tabelas dinâmicas. Não é disso que se trata. Isso é uma coisa fácil de fazer - Slicer, Ferramentas, Opções, Conexões de Relatório ou Conexões de Slicer na versão anterior, e verifique se você deseja que este Slicer controle todas essas tabelas dinâmicas. Fácil, certo? Esta questão é sobre esta planilha, onde temos dois conjuntos de dados diferentes e vamos criar uma tabela dinâmica a partir disso, e a partir disso - agora vou acelerar o vídeo enquanto crio essas tabelas dinâmicas. Tudo bem, agora, o que você verá é, eu tenho duas tabelas dinâmicas, esta tabela dinâmica é criada a partir de um conjunto de dados e há um fatiador que controla essa tabela dinâmica;e então eu tenho uma segunda tabela dinâmica que é criada a partir de um conjunto de dados diferente e uma segmentação que controla essa tabela dinâmica. Mas não há absolutamente nenhuma maneira de fazer com que esse segmentador de dados controle essa tabela dinâmica e essa tabela dinâmica criada a partir de um conjunto de dados diferente. Tudo bem. Mas vou mostrar a você como fazer isso hoje com uma macro.
Agora, isso é complicado de fazer. Quando a pergunta veio, eu disse: "Agora, isso, eu não acho que você pode fazer isso." Mas tenho trabalhado nisso e experimentado e acho que finalmente entendi. Eu tenho que pensar que finalmente consegui entender. Tudo bem, então vamos examinar isso. Primeiro, ele é salvo como um arquivo xlsx. Esse é um tipo de arquivo bom, exceto por ser um tipo de arquivo horrível, porque é o único tipo de arquivo que não permite macros. Você tem que mudar isso de xlsx para xlsm, ou todo o seu trabalho para o resto do vídeo vai ser jogado pela janela. Salve como, mude o tipo de arquivo para xlsm ou, diabos, xlsb, qualquer um deles funcionará. Esse é o que está quebrado-- xlsx-- e é o padrão, louco não é? Xlsm, clique em Salvar. Se você nunca fez macros antes, Alt + T para Tom, M para Macro,S para Segurança e você poderá salvar todas as macros sem notificação. Precisa mudar isso para o segundo, que permitirá que suas macros funcionem.
Tudo bem, agora temos dois fatiadores. Aposto que você nunca soube disso, mas os fatiadores têm nomes. Iremos para Ferramentas do Slicer, Opções, Configurações do Slicer e ver que este se chama Slicer_Name. Curtiu isso. Vá para o segundo, vá para Ferramentas do Slicer, Opções, Configurações do Slicer, este se chama Slicer_Name1-- não Name space 1, Name1. Dois nomes assim.
Aqui está o que vamos fazer. Vamos mudar para VBA-- Alt + F11. No VBA, se você nunca fez VBA, você terá uma grande tela cinza. Vamos chegar aqui e dizer View, Project Explorer, no Project Explorer localize seu arquivo - o meu é chamado Podcast 2104. Abra o Microsoft Excel Objects e a planilha onde quero que isso funcione é chamada Dashboard. Vou clicar com o botão direito lá e dizer Exibir código. Este código que estamos escrevendo não pode ir em um módulo como em uma macro regular - isso tem que estar nesta planilha. Abra a lista suspensa superior esquerda, Planilha, e, na lista suspensa superior direita, vamos dizer Atualização da Tabela Dinâmica. Tudo bem, então é para onde nosso código irá agora. Eu já preparei este código. Vamos dar uma olhada no código aqui no bloco de notas. Então, nós 'você terá dois caches do Slicer - SC1 e SC2 - um item do Slicer e então, bem aqui, é aqui que você terá que personalizá-lo. Portanto, meus dois Slicers se chamavam Nome e Nome1. Tudo bem, você vai ter que colocar os nomes dos fatiadores lá. Application.Screenupdating = False, Application.EnableEvents = False e, em seguida, Slicer Cache 2 - vamos limpar o filtro e, em seguida, para cada item SI1 e sc1.SlicerItems, se estiver selecionado, vamos fazer o mesmo item no Cache do Slicer a ser selecionado. Este é um pequeno loop que percorrerá todos os itens que houver naquele divisor. No meu caso, tenho 11 ou 12; no seu caso, você pode ter mais.Portanto, meus dois Slicers foram chamados de Name e Name1. Tudo bem, você vai ter que colocar os nomes dos fatiadores lá. Application.Screenupdating = False, Application.EnableEvents = False e, em seguida, Slicer Cache 2 - vamos limpar o filtro e, em seguida, para cada item SI1 e sc1.SlicerItems, se estiver selecionado, vamos fazer o mesmo item no Cache do Slicer a ser selecionado. Este é um pequeno loop que percorrerá todos os itens que houver naquele divisor. No meu caso, tenho 11 ou 12; no seu caso, você pode ter mais.Portanto, meus dois Slicers se chamavam Nome e Nome1. Tudo bem, você vai ter que colocar os nomes dos fatiadores lá. Application.Screenupdating = False, Application.EnableEvents = False e, em seguida, Slicer Cache 2 - vamos limpar o filtro e, em seguida, para cada item SI1 e sc1.SlicerItems, se estiver selecionado, vamos fazer o mesmo item no Cache do Slicer a ser selecionado. Este é um pequeno loop que percorrerá todos os itens que houver naquele divisor. No meu caso, tenho 11 ou 12; no seu caso, você pode ter mais.vamos fazer com que o mesmo item no Cache do Slicer seja selecionado. Este é um pequeno loop que percorrerá todos os itens que houver naquele divisor. No meu caso, tenho 11 ou 12; no seu caso, você pode ter mais.vamos fazer com que o mesmo item no Cache do Slicer seja selecionado. Este é um pequeno loop que percorrerá todos os itens que houver naquele divisor. No meu caso, tenho 11 ou 12; no seu caso, você pode ter mais.
Quando terminarmos com isso, ative os eventos de ativação novamente, ative a Atualização de tela novamente. Tudo bem. Então, vamos pegar esse código, copiar esse código e colá-lo aqui no meio de nossa macro assim. Certo, agora, vamos apenas ter certeza de que pressionarei Ctrl + G e minha solicitação é Application.EnableEvents, ligado ou desligado - então,? Application.EnableEvents - e é verdade. Se o seu for falso, você deseja voltar aqui e dizer que é = Verdadeiro - então, você está ativando esses eventos. Tudo bem. Agora, aqui está o que vai acontecer. Portanto, nosso treinador deve estar trabalhando aqui, está na planilha certa. Estamos salvos em um arquivo xlxm e eu ativei as macros e o que veremos é que, quando eu escolher no Slicer esquerdo, aquele Slicer Cache 1--Vou escolher Andy por meio de Della - o outro Slicer também será atualizado. Tudo bem E mesmo que eu escolhesse apenas Gloria - apenas Gloria - parece que está funcionando muito, muito bem. Mesmo se eu pressionasse CTRL + clique, quando eu soltar Ctrl, todos os três serão atualizados.
Mas aqui está a pegadinha-- sempre há uma pegadinha-- este Slicer, tem que existir, mas você não pode usar este Slicer - espere, quero dizer, você pode, você pode usar um Slicer, mas vai confundir muito as coisas . Porque o que vai acontecer é que vou mudar isso para Hank e eles vão voltar para o que quer que esteja no Cache 1 do Slicer, porque eu mudei a tabela dinâmica nesta folha. Agora, na vida real, você terá duas tabelas dinâmicas na mesma planilha? Não sei se você é ou não, tudo bem, mas as coisas vão ficar um pouco malucas.
Agora, vamos apenas dar uma olhada nisso. A primeira coisa que quero fazer é inserir uma nova planilha - Alt + IW para inserir a planilha - e vou chamar isso de DarkCave. Você pode chamá-lo do que quiser. Vou pegar aquele painel que não vai funcionar, vou copiar esse painel e vir aqui para a caverna escura e colá-lo ali e, em seguida, clicar com o botão direito e ocultar essa folha para que ninguém nunca veja aquele Slicer. E então, a partir daqui, devemos ser capazes de excluí-lo. Legal, certo. E vamos apenas verificar se eles ainda estão funcionando - escolha Charlie através de Eddie e ambos ainda estão atualizando. Agora, o que está acontecendo? O Slicer que não podemos ver, aquele que escondemos, está atualizando também, mas não nos importamos se ele está atualizando.
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.
Quero agradecer a sua visita, nos vemos na próxima vez para outro netcast de
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2104.xlsm
