Recentemente, um amigo meu questionou sobre os botões Drill-Down e Drill-Up na guia Pivot Table Tools da Ribbon. Por que eles ficam perpetuamente acinzentados? Eles ocupam muito espaço na Faixa de Opções. Como alguém deve usá-los?
Depois de alguma pesquisa, há uma maneira de usá-los, mas você precisa usar o Modelo de Dados e a exibição do diagrama Power Pivot para criar uma hierarquia. Se você não tiver a guia Power Pivot em sua faixa de opções, terá que encontrar um colega de trabalho que tenha o botão para criar a hierarquia. (Ou, se você quiser apenas experimentar o recurso, baixe o arquivo Excel que criei: Hierarchy.xlsx)
Primeira etapa - converta seu conjunto de dados de origem dinâmica em uma tabela usando Home - Format as Table ou Ctrl + T. Certifique-se de que a opção My Table Has Headers está selecionada.
Use Inserir - Tabela Dinâmica. Na caixa de diálogo Criar Tabela Dinâmica, escolha a caixa para Adicionar estes dados ao modelo de dados.
Aqui estão os campos da tabela dinâmica antes de criar a hierarquia.
Clique no ícone Gerenciar na guia Power Pivot na Faixa de Opções. (Muitas instâncias do Excel 2013 e 2016 não têm essa guia. Ela não aparece no Mac.)
Na janela do Power Pivot para Excel, clique no ícone Exibir Diagrama. Ele está próximo ao lado direito da guia Página inicial.
Use a alça de redimensionamento no canto inferior direito da Tabela1 para ampliar a Tabela1 para que você possa ver todos os seus campos. Clique no primeiro item da sua hierarquia (Continente no meu exemplo). Clique com a tecla Shift no último item da hierarquia (Cidade no meu exemplo). Você também pode clicar em um item e clicar com a tecla Ctrl em outros se os campos de hierarquia não forem adjacentes. Depois de selecionar os campos, clique com o botão direito em qualquer um deles e escolha Criar Hierarquia.
Hierarquia1 é criada e está esperando que você digite um novo nome. Vou nomear minha hierarquia Geografia. Se você clicar fora do Power Pivot, Hierarquia1 não estará mais no modo Renomear. Clique com o botão direito em Hierachy1 e escolha Renomear.
Feche o Power Pivot e volte ao Excel. Os Campos da tabela dinâmica agora mostram a hierarquia geográfica e mais campos. Seu campo de vendas está oculto em Mais campos. Eu entendo um pouco por que eles escondem Continente, País, Região, Território, Cidade em Mais Campos. Mas não entendo por que eles ocultam Vendas em Mais campos.
Para construir a tabela dinâmica, marque a caixa da hierarquia Geografia. Abra Mais campos clicando no triângulo ao lado deles. Escolha Vendas.
Há muito o que notar na imagem acima. Quando você cria inicialmente a tabela dinâmica, a célula ativa está em A3 e o ícone Drill Down está esmaecido. No entanto, se você mover o ponteiro da célula para a América do Norte em A4, verá que o Drill Down está ativado.
Com o ponteiro da célula na América do Norte, clique em Drill Down e Continent é substituído por Country.
Com o ponteiro da célula no Canadá, clique em Drill Down e você verá o Leste do Canadá e o Oeste do Canadá. Observe que, neste ponto, os botões Drill Down e Drill Up estão habilitados.
Cliquei em Drill Up para retornar ao Country. Selecione Estados Unidos. Drill Down três vezes e acabo nas cidades da região das Carolinas. Neste ponto, o botão Drill Down está acinzentado.
Observe que no nível Continente, você pode clicar em Expandir campo para mostrar os continentes e os países. Em seguida, no primeiro País, escolha Expandir campo para revelar as regiões. Na primeira região, use Expandir campo para mostrar Territórios. No primeiro Território, clique em Expandir Campo para revelar a Cidade.
Todas as capturas de tela acima estão mostrando a tabela dinâmica em minha visualização padrão de Mostrar na forma tabular. Se suas tabelas dinâmicas forem criadas em formato compacto, você verá a exibição abaixo. (Para saber como fazer com que todas as suas tabelas dinâmicas futuras comecem na forma tabular, veja este vídeo).
Qual é a vantagem da Hierarquia? Tentei criar uma tabela dinâmica regular sem hierarquia. Ainda consigo expandir e recolher campos. Mas se eu quiser mostrar apenas as regiões do Canadá, terei que adicionar uma segmentação ou filtro de relatório.
Assistir vídeo
Transcrição de vídeo
Aprenda Excel com o Podcast, Episódio 2196: Drill Up and Drill Down in Pivot Tables.
Ei, bem-vindo de volta ao netcast, sou Bill Jelen. Há um mistério nas tabelas dinâmicas. Se eu inserir uma tabela dinâmica aqui, você verá que temos os campos Drill Up e Drill Down, mas eles nunca acendem. O que há com isso? Por que temos isso? Como os fazemos funcionar? Certo, essa é uma ótima pergunta e, infelizmente, me sinto mal por isso. Estou tentando fazer toda a minha vida no Excel, sem nunca usar a guia Power Pivot. Não quero que você pague $ 2 extras por mês pela versão Pro Plus do Office 365, mas este é um - este é um - onde temos que gastar os $ 2 extras por mês ou encontrar alguém que tenha os $ 2 extras por mês para configurar isso.
Vou usar esse formato de dados como uma tabela. Não importa o formato que eu escolher, o formato não é importante; apenas conseguir uma mesa para nós é a parte importante. Power Pivot, vamos adicionar esta tabela ao nosso modelo de dados e, em seguida, clicar em Gerenciar. Tudo bem, aqui está nossa tabela no Modelo de Dados. Temos que ir para a vista de diagrama, agora vamos alargar um pouco mais para que possamos ver todos os campos. Vou escolher Continent; Vou pressionar Shift e clicar em Cidade. Agora isso compõe meu Drill Down, Drill Up, a hierarquia. Em seguida, clicaremos com o botão direito e diremos Criar hierarquia. E eles nos dão um nome - vou digitar "Geografia" para minha Hierarquia, assim. Ótimo, agora, com essa mudança, inserimos uma tabela dinâmica - e esta será uma tabela dinâmica do modelo de dados - e você verá que podemos adicionar Geografia como sua própria hierarquia.
Agora, a única coisa que eu particularmente não gosto sobre isso, é que todo o resto muda para More Fields. Tudo bem? Então, escolhemos Geografia e ela voa para o lado esquerdo. E embora isso seja ótimo, eu também preciso escolher Receita, e eles pegaram os campos que não faziam parte da Hierarquia e os moveram para Mais Campos. Então é como, eu entendi, eles estão tentando ocultar os campos que eu não deveria escolher, mas no processo de fazer isso eles também esconderam Mais campos - a receita ou vendas aqui. Tudo bem. Então, é um pouco frustrante ter que ir a mais campos para pegar os campos que não fazem parte da Geografia, mas é assim que funciona.
Tudo bem. Então, agora que temos isso, vamos dar uma olhada no que funciona aqui. Estou sentado no Continente, vou para a guia Analisar e nada acende, não funcionou. Atire! Não, funcionou, você apenas tem que vir para a América do Norte e então posso Drill Down e substitui Continent por Country. E então, do Canadá, posso fazer um Drill Down e obter o Leste e o Oeste do Canadá. A partir do detalhamento do Leste do Canadá, obtenho Ontário e Quebec. Ontário, eu consigo essas cidades, posso perfurar Drill Up, Drill Up e escolher Estados Unidos; Drill Down, Drill Down, Drill Down. Tudo bem, é assim que funciona.
Experimente, você precisa ter a guia Power Pivot ou encontrar alguém com uma guia Power Pivot. Se você apenas quiser tentar, olhe na descrição do YouTube, haverá um link para a página da web e há um lugar lá na página da web onde você pode baixar este arquivo, e você deve ser capaz de usar a Hierarquia mesmo que você não tem a guia Power Pivot. Se você estiver no Excel 2016 ou Office 365, deve funcionar.
Agora, você sabe, veja, eu acho que não tenho certeza se sou um fã é o fato de que eles estão eliminando as outras informações, em vez de usar o ícone Expandir, que então se expandiria o próximo grupo, e o próximo grupo, e o próximo grupo. Sempre tivemos o ícone Expandir, mas mesmo assim ele está funcionando um pouco diferente. Aqui, se eu quiser, posso realmente sentar na América do Norte e expandir um nível de cada vez, sem precisar escolher cada um adicional do modelo de dados. Parece que temos que mover o ponteiro da célula, um bit de cada vez.
Certo, agora, essa dica foi realmente, meio que descoberta. Os MVPs do Excel conversaram com a equipe do Excel sobre esses botões, portanto, não abordados neste livro. Mas muitas outras ótimas dicas abordadas em LIVe, as 54 melhores dicas de todos os tempos.
Conclusão de hoje: Por que Drill Up e Drill Down estão constantemente esmaecidos? Bem, você tem que criar uma Hierarquia. Para criar uma Hierarquia, você deve entrar no Power Pivot; na visualização do diagrama; selecione os campos para a hierarquia; e clique com o botão direito; e criar hierarquia.
Quero agradecer a sua visita, vejo vocês na próxima vez para outro netcast de.
