Classifique a tabela dinâmica do Excel por porcentagem de contagem

Question

Tenho dados de origem que mostram aprovações de quadros de horários no seguinte formato (para cerca de 850 funcionários e 200 gerentes):

  Employee Name  Manager Name  TS Approved? 
  Employee 1     Manager 1     No
  Employee 2     Manager 2     Yes
  Employee 3     Manager 3     Yes
  Employee 4     Manager 1     No
  Employee 5     Manager 3     No

Fiz uma tabela dinâmica da seguinte maneira (O % de não aprovados é apenas uma fórmula que tenho ao lado da tabela dinâmica):

                 Count TS Approved? 
  Manager Name   No    Yes   Total  % Unapproved
  Manager 1      11          11     100%
  Manager 2      6     10    16     38%
  Manager 3      7     18    25     28%
  Manager 4      5     8     13     38%
  Manager 5      5     4     9      56%
  Manager 6            3     3      0%
  Manager 7      5           5      100%

Preciso classificar para obter os 5 piores aprovadores por contagem - mas apenas 5. Meus problemas são:

• Se eu usar a tabela dinâmica 'Top 10' na coluna 'Não', ela mostrará 6 valores, pois não diferencia os três 5s
• Tentei adicionar a porcentagem para poder classificar o Maior-Menor em%, depois o Maior-Menor na contagem e, em seguida, apenas pegar os 5 primeiros manualmente - já que 5/5 (100%) não aprovados é pior que 5/8 (38%) - mas não sei como classificar%.
• Se eu adicioná-lo como uma fórmula fora da tabela dinâmica (como acima), o Excel não me permitirá classificar a tabela dinâmica com base nesses dados. 'Você não pode mover parte de um relatório de tabela dinâmica....'
• Se eu adicionar os dados para mostrar como "% do total da linha pai" na tabela, ele ainda será classificado apenas na contagem

Alguém pode pensar como posso fazer com que ele faça o que quero, ou seja?

                 Count TS Approved? 
  Manager Name   No    Yes   Total  % Unapproved
  Manager 1      11          11     100%
  Manager 3      7     18    25     28%
  Manager 2      6     10    16     38%
  Manager 7      5           5      100%
  Manager 5      5     4     9      56%
  Manager 4      5     8     13     38%
  Manager 6            3     3      0%

Observação: posso fazer isso com bastante facilidade usando countifs em vez de uma tabela dinâmica, mas o ideal é que eu queira o formato da tabela dinâmica, se possível.

Obrigado!

Luísa

Answer 1

Desafio interessante. Alguns dos problemas incluem:

• Os cálculos de campo não têm flexibilidade suficiente para obter o que você precisa
• Embora você possa exibir números como% do total, e parece que você pode classificá-los - ele realmente classifica os números subjacentes.

Tenho uma solução que faz uso de Tabelas e Tabela Dinâmica. Pode haver uma solução mais simples disponível. As etapas são (feitas no Excel 2016):

1. Selecione dentro de seus dados brutos. Selecione a faixa "Inserir" e clique em "Tabela"
2. Na sua nova tabela, insira um cálculo para %NotApproved
3. Selecione a faixa "Ferramentas de tabela" "Design" e clique em "Resumir com tabela dinâmica"
4. Construa uma tabela dinâmica simples com Nome do gerente como linhas e %NotApproved como valores.
5. Classifique os nomes dos gerentes em ordem decrescente por% NotApproved

Aqui está um exemplo. A seguir está um trecho de 30 linhas de "dados brutos" semelhantes aos descritos em sua pergunta...

Selecione a faixa "Inserir" e clique em "Tabela" ...

Você obtém dados melhor formatados. Selecione D1, próximo ao último título da coluna e digite "%No" - isso cria uma nova coluna na tabela com um novo título. Na célula D2, digite a seguinte fórmula ...

=IF([@[TS Approved?]]="No",1,0)/COUNTIF([Manager Name],"="&[@[Manager Name]])*100

Ao pressionar Enter, ele é automaticamente preenchido na tabela. Esta fórmula faz:

1. IF([@[TS Approved?]]="No",1,0)Se a planilha de horas aprovada for “Não”, obtenha o valor 1.
2. COUNTIF([Manager Name],"="&[@[Manager Name]])Determina quantas vezes o gerente desta linha aparece na tabela.
3. Resultado de 1 dividido pelo resultado de 2 vezes 100

A tabela agora está assim...

Selecione a faixa "Ferramentas de tabela", "Design" e clique em Resumir com tabela dinâmica. Construa a tabela dinâmica para ficar assim ...

... e classifique-o ...

... para conseguir esta ...

Embora pareçam muitas etapas para configurar, é muito fácil manter a Tabela e isso mantém automaticamente a Tabela Dinâmica.

Answer 2

Isso pode ser antigo, mas acho que encontrei uma solução para esse problema.

1. A primeira etapa é simplesmente mostrar a% de não aprovados pelo clique com o botão direito convencional, em seguida, "Mostrar valor como" e depois "% do total da linha"
2. Em seguida, clique no botão suspenso próximo a "Rótulos de linha"
3. Escolha "Decrescente" por "Count TS Approved"
4. Selecione "Mais opções de classificação" e clique em "Mais opções" na caixa de diálogo
5. Desmarque a caixa "Classificar automaticamente sempre que o relatório for atualizado" (Este é o passo fundamental)
6. Escolha "Valores na coluna selecionada" para ser a primeira célula da coluna "Não"
7. Clique OK
8. Para atualizar automaticamente, repita as etapas 2, 4 e 5, mas desta vez marque "Classificar automaticamente sempre que o relatório for atualizado".

Answer 3

Não sei por que, mas percebi duas coisas durante o café da manhã esta manhã...

1. Usar uma tabela é bom, mas talvez apenas complique o problema.
2. Embora você calcule a % de planilhas de horas não aprovadas como a % de planilhas de horas pelas quais o gerente é responsável, talvez você queira calculá-la como a % de todas as planilhas de horas não aprovadas.

Então pensei em postar uma resposta alternativa.

Ao lado dos seus dados brutos, coloque um cabeçalho %Noe este cálculo abaixo (e preencha).

=IF(C2="No",1,0)/COUNTIF($C$2:$C$31,"="&C2)*100

A fórmula calcula, se esta planilha de horas não for aprovada, a porcentagem de todas as planilhas de horas não aprovadas.

Seus dados brutos agora se parecem com isto...

Construa sua tabela dinâmica e classifique% Não.

Se você ainda quiser que %Não aprovados seja % das planilhas de horas pelas quais o gerente é responsável, use esta equação na Coluna D.

=IF(C2="No",1,0)/COUNTIF($B$2:$B$31,"="&B2)*100