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. Observe que o maior cliente, Roto-Rooter, é 9% da receita total.
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. A Roto-Rooter, que antes era 9% do total, é 23% do novo total.
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 ou pressione Ctrl + Shift + L.
- 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 a linha de cabeçalhos e, em seguida, 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
Existe uma regra que diz que você não pode usar o AutoFiltro quando estiver em uma tabela dinâmica. Ver abaixo? O ícone de filtro está esmaecido porque selecionei uma célula na tabela dinâmica.
Não sei por que a Microsoft esmaece isso. Deve ser algo interno que diga que o AutoFiltro e uma 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 AutoFilter opera de maneira diferente de um filtro 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 você 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 quiser uma tabela dinâmica mostrando os cinco principais clientes, mas o total de todos os clientes, você deve mover seus dados para fora do Excel. Se você tiver o Excel 2013 ou mais recente em execução no Windows, 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, marque a caixa de seleção Adicionar esses 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 na 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 nos totais, e seu Total geral agora inclui um asterisco e o total de todos os dados, conforme mostrado abaixo.
Este truque da célula mágica veio originalmente de Dan em meu seminário na Filadélfia e foi repetido 15 anos depois por um Dan diferente de meu seminário em Cincinnati. Obrigado a Miguel Caballero por sugerir este recurso.
