O filtro dos 10 principais da tabela dinâmica dá um total das linhas visíveis
As tabelas dinâmicas oferecem um filtro dos 10 principais. É legal. É flexível. Mas eu odeio isso, e vou te dizer por quê.
Aqui está uma tabela dinâmica que mostra a receita por cliente. A receita total é de US $ 6,7 milhões.
E se meu gerente tiver a capacidade de atenção de um peixinho dourado e quiser ver apenas os cinco principais clientes?
Para começar, abra a lista suspensa em A3 e selecione Filtros de valor, 10 principais.
A caixa de diálogo Super-flexível Top 10 Filter permite Top / Bottom. Ele pode fazer 10, 5 ou qualquer outro número. Você pode pedir os cinco itens principais, os 80% principais ou clientes suficientes para chegar a US $ 5 milhões.
Mas aqui está o problema: o relatório resultante mostra cinco clientes e o total desses clientes, em vez dos totais de todos.
Mas, primeiro, algumas palavras importantes sobre o AutoFiltro
Eu percebo que esta parece uma pergunta fora da parede. Se você deseja ativar os menus suspensos de Filtro em um conjunto de dados regular, como você faz isso? Aqui estão três maneiras realmente comuns:
- Selecione uma célula em seus dados e clique no ícone Filtro na guia Dados.
- Selecione todos os seus dados com Ctrl + * e clique no ícone Filtro na guia Dados.
- Pressione Ctrl + T para formatar os dados como uma tabela.
Estas são três maneiras realmente boas. Contanto que você conheça algum deles, não há absolutamente nenhuma necessidade de conhecer outro caminho. Mas aqui está uma maneira incrivelmente obscura, mas mágica de ativar o filtro:
- Vá para sua linha de cabeçalhos, vá para a célula de cabeçalho mais à direita. Mova uma célula para a direita. Por alguma razão desconhecida, quando você está nesta célula e clica no ícone Filtro, o Excel filtra o conjunto de dados à sua esquerda. Não tenho ideia de por que isso funciona. Realmente não vale a pena falar sobre isso porque já existem três maneiras realmente boas de ativar os menus suspensos de Filtro. Eu chamo essa célula de célula mágica.
E agora, de volta às tabelas dinâmicas …
Portanto, existe uma regra que diz que você não pode usar os AutoFiltros quando estiver em uma tabela dinâmica. Ver abaixo? O ícone de filtro está esmaecido porque selecionei uma célula na tabela dinâmica.
Eu nunca realmente considerei por que a Microsoft esmaece isso. Deve ser algo interno que diga que o AutoFiltro e a Tabela Dinâmica não podem coexistir. Portanto, há alguém na equipe do Excel que é responsável por esmaecer o ícone do Filtro. Essa pessoa nunca ouviu falar da célula mágica. Selecione uma célula na tabela dinâmica e o filtro ficará esmaecido. Clique fora da tabela dinâmica e o Filtro é habilitado novamente.
Mas espere. E a célula mágica de que acabei de falar? Se você clicar na célula à direita do último título, o Excel esquece de cinza o ícone do Filtro!
Com certeza, o Excel adiciona menus suspensos AutoFiltro à linha superior de sua tabela dinâmica. E o AutoFiltro opera de maneira diferente dos filtros de tabela dinâmica. Vá para a lista suspensa Receita e escolha Filtros de número, 10 principais …
Na caixa de diálogo Top 10 AutoFilter, escolha Top 6 Items. Isso não é um erro de digitação…. Se quiser cinco clientes, escolha 6. Se quiser 10 clientes, escolha 11.
Para o AutoFiltro, a linha de total geral é o maior item nos dados. Os cinco principais clientes estão ocupando as posições 2 a 6 nos dados.
Cuidado
Claramente, você está abrindo um buraco no tecido do Excel com esse truque. Se, posteriormente, você alterar os dados subjacentes e atualizar sua tabela dinâmica, o Excel não atualizará o filtro, porque, pelo que a Microsoft sabe, não há como aplicar um filtro a uma tabela dinâmica!
Nota
Nosso objetivo é manter isso em segredo da Microsoft, porque é um recurso muito legal. Ele está “quebrado” há algum tempo, então muitas pessoas podem estar contando com ele agora.
Uma solução totalmente legal no Excel 2013+Se você quiser uma tabela dinâmica mostrando os cinco principais clientes, mas o total de todos os clientes, terá que mover seus dados para fora do Excel. Se você tiver o Excel 2013 ou 2016, há uma maneira muito conveniente de fazer isso. Para mostrar isso, excluí a tabela dinâmica original. Escolha Inserir, Tabela Dinâmica. Antes de clicar em OK, selecione a caixa que diz Adicionar estes dados ao modelo de dados.
Construa sua tabela dinâmica normalmente. Use a lista suspensa em A3 para selecionar Filtros de valor, 10 principais, e pergunte pelos cinco clientes principais. Com uma célula da tabela dinâmica selecionada, vá para a guia Design na faixa de opções e abra a lista suspensa Subtotais. A escolha final na lista suspensa é Incluir itens filtrados nos totais. Normalmente, essa opção fica acinzentada. Mas como os dados são armazenados no Modelo de Dados em vez de em um cache dinâmico normal, essa opção agora está disponível.
Escolha a opção Incluir itens filtrados no total e seu Total geral agora inclui um asterisco e o total de todos os dados.
Esse truque veio originalmente de Dan em meu seminário na Filadélfia. Obrigado a Miguel Caballero por sugerir este recurso.
Assistir vídeo
- O filtro dos 10 principais da tabela dinâmica dá um total das linhas visíveis
- Incluir itens filtrados nos totais está esmaecido
- Maneira estranha de invocar o Filtro de Dados da célula mágica
- Filtros de dados não são permitidos em tabelas dinâmicas
- O Excel não consegue esmaecer o Filtro de Dados da célula mágica
- Peça os 6 primeiros para obter os 5 melhores mais o Grande Total
- Útil para filtrar por um item de pivô específico
- Excel 2013 ou mais recente: maneira diferente de obter o verdadeiro total
- Envie seus dados através do Modelo de Dados
- Incluir itens filtrados nos totais estará disponível
- Obtenha total com asterisco
- Aprendi esse truque há mais de 10 anos com Dan na Filadélfia
Transcrição de vídeo
Aprenda Excel para Podcast, Episódio 1999 - Tabela Pivot True Top Five
Estou enviando um podcast para este livro inteiro. Há uma lista de reprodução, clique em I no canto superior direito para seguir essa lista de reprodução. Bem-vindo de volta ao netcast. Sou Bill Jelen.
Tudo bem, então vamos criar uma Tabela Dinâmica e queremos mostrar, não todos os clientes, mas apenas os cinco principais clientes. INSERT, Tabela Dinâmica. Ok, colocarei Cliente no lado esquerdo e Receita. Certo, então aqui está nossa lista completa de clientes avaliados em 6,7 milhões de dólares. O Excel facilita a classificação dos cinco primeiros. Vá para rótulos de linha, filtros de valor, top 10. Não precisa ser o primeiro. Pode ser superior ou inferior. Não precisa ter cinco anos. Pode ser vinte, quarenta, pode ser qualquer coisa. Oitenta por cento superior, me dê registros suficientes para chegar a três milhões de dólares ou quatro milhões de dólares, mas aqui vamos nós. Os cinco principais itens. Agora lembre-se de 6,7 milhões de dólares, clique em OK e meu grande problema aqui é que esse total geral não é de 6,7 milhões. Quando eu der isso para o vice-presidente de vendas, ele vai pirar, dizendo, espere um segundo,Eu sei que fiz mais de 3,3 milhões de dólares. Certo, então vamos desfazer, desfazer e voltar aos dados originais.
Agora, este próximo truque que aprendi durante um de meus seminários Power Excel na Filadélfia. Um cara chamado Dan na linha dois me mostrou isso. Faz mais de dez anos que ele me mostrou esse truque, e primeiro temos que falar sobre os Filtros. Então, normalmente, se você vai usar o Filtro normal, este Filtro aqui, você escolhe qualquer célula em seu conjunto de dados e clica no ícone do Filtro, ou algumas pessoas escolhem todo o Conjunto de Dados, CONTROL * e clica no ícone do Filtro, mas há uma terceira via. Uma forma com a qual ninguém liga. Se você for até a última célula de título, no meu caso, é o custo em L1, e vá uma célula à direita. Eu chamo isso de célula mágica, não tenho ideia do motivo, mas por alguma razão desconhecida, a partir dessa célula, posso filtrar o conjunto de dados adjacente. Tudo bem, é uma forma estranha e ninguém se importa com isso.
Certo, como há duas outras maneiras realmente boas de invocar um Filtro, ninguém precisa saber sobre a célula mágica, mas aqui está a coisa, veja dentro de uma Tabela Dinâmica, ela está acinzentada. Você não tem permissão para usar esses filtros. É contra as regras. Agora, se eu vier aqui, serei mais que bem-vindo para usar o Filtro, mas por dentro eles nivelam. Eu não sei quem é a pessoa que grava isso, mas eles nunca ouviram minha pequena conversa sobre a célula mágica, porque se eu for até a última célula principal e for uma célula para a direita, olhe para isso, eles se esquecem de esmaecer o Filtro e agora acabei de adicionar os antigos Filtros Automáticos à Tabela Dinâmica. Então eu vim aqui, vá para Filtros de número, que é diferente de Filtros de valor. Ainda é chamado de Top Ten. Um pouco diferente, vou pedir os cinco primeiros, não os seis primeiros.Os seis primeiros porque, para este Filtro, o Total Geral é apenas outra linha e o Total Geral é o maior item e, então, quando questionado sobre os itens 2 a 6, recebo os cinco itens principais.
Tudo bem, então aí estamos. Um hack de filtro legal, que nos dá os cinco itens principais e o total real de todos. Tudo bem agora, algumas coisas. Não se esqueça da célula mágica. Tudo bem, não há como desligar esse filtro, a menos que você volte para a célula mágica. Tudo bem, então você precisa se lembrar da célula mágica. Além disso, se você alterar os dados subjacentes e atualizar a Tabela Dinâmica, eles não irão atualizar o Filtro porque, pelo que a Microsoft sabe, você não tem permissão para ter um Filtro.
Isso é útil para outras coisas. Às vezes, temos produtos indo para o topo. Vamos aqui em uma forma tabular. Não é necessário, só gosto de obter cabeçalhos de verdade. Gizmo, Widget, Gadgets, Doodads. Tudo bem e talvez você seja o gerente de Doodads e precise ver apenas os clientes que tinham um valor específico e Doodads. Então, vou para a célula mágica, ligo o Filtro e, em Doodads, posso solicitar itens maiores que zero. Clique OK. Tudo bem, esse tipo de filtragem não seria possível em uma tabela dinâmica regular, mas é possível usando a célula mágica.
Tudo bem, agora vamos desfazer a lista. Vamos desligar esse filtro e remover a tabela dinâmica e, se você estiver no Excel 2013 ou novo, vou mostrar uma maneira completamente legal de obter o total correto na parte inferior. Inserir Tabela Dinâmica, aqui embaixo na parte inferior, começando no Excel 2013 esta caixa muito inócua, não parece muito empolgante, adicione esses dados ao Modelo de Dados. Isso envia os dados, nos bastidores, para o Power Pivot Engine. Crie exatamente o mesmo relatório. Clientes descendo pelo lado esquerdo. Receita no centro da Tabela Dinâmica. Em seguida, vá para Filtros regulares, os 10 principais Filtros de valor. Pergunte pelos cinco principais. Observe novamente que temos 6,7 milhões de dólares depois de fazer isso, 3,3 milhões de dólares, mas aqui está a diferença. Quando vou para a guia Design, em Subtotais, este recurso chamado Incluir itens filtrados nos totais,não está mais esmaecido. Em uma tabela dinâmica regular não está disponível. Temos um pequeno asterisco aí e é o total de tudo. Tudo bem, agora é claro que isso só funciona no Excel 2013 ou mais recente.
Tudo bem, vou levar seis semanas para lançar este livro inteiro aqui no YouTube. Existem tantas dicas boas aqui. Dicas que podem começar a economizar tempo imediatamente. Compre o livro inteiro agora mesmo e você terá acesso a todas as 40 dicas. Na verdade, são muito mais que 40 dicas. Teclas de atalho do Excel. Todos os tipos de coisas excelentes neste livro.
Tudo bem, recapitule. Portanto, quando fazemos um filtro Pivot Table Top 10, ele nos dá o total, mas apenas as linhas visíveis, não o material que foi filtrado. Sim, se formos para a segunda guia e procurarmos por Subtotais, Itens Filtrados e Totais, ficará acinzentado, mas há uma maneira estranha de invocar o Filtro de Dados Antigo a partir da célula mágica. A última célula de título, vá uma célula para a direita, você não pode usar Filtros e Tabelas Dinâmicas, mas se você for para a célula mágica, eles se esquecem de acinzentá-la. Agora, no Filtro numérico, você pede que os seis primeiros obtenham os cinco primeiros, mais o total geral. Também é útil para filtrar para um item de pivô específico: Doodads, qualquer coisa que tivesse mais que 0 em Doodads ou os 5 principais Doodads. Excel 2013 ou mais recente, há uma maneira diferente de obter o True Total.Marque essa caixa para o Modelo de Dados e, em seguida, incluir Itens Filtrados nos Totais estará disponível. Você obtém o total com um asterisco. E obrigado a Dan, da Filadélfia, que me mostrou em um dos meus Power Excel Seminars, há mais de dez anos, e me deu esse ótimo truque. Uma maneira de o filtro se esgueirar pela parede da mesa dinâmica do clube. Eles normalmente não permitem esse filtro automático.
Ei, eu quero agradecer por você passar por aqui. Nos vemos na próxima, para outro netcast do MRExcel.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast1999.xlsx
